mysql实战题目练习

1、创建和管理数据库

• 创建一个名为school的数据库。

• 列出所有的数据库，并确认school数据库已经创建。

• 如果school数据库已经存在，删除它并重新创建。

• mysql> create database school;
  Query OK, 1 row affected (0.01 sec)mysql> 
  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | school             |
  | sys                |
  | zabbix             |
  +--------------------+
  6 rows in set (0.01 sec)

  2、创建数据表

• 在school数据库中，创建一个名为students的表，包含以下字段：id（整数，主键，自增）、name（字符串，最大长度50）、age（整数）和grade（字符串，最大长度10）。

• 创建一个名为courses的表，包含course_id（整数，主键，自增）、course_name（字符串，最大长度100）和teacher（字符串，最大长度50）。

• mysql> create table students(id int auto_increment primary key,name varchar(50),age int,grade varchar(10));
  Query OK, 0 rows affected (0.06 sec)
  mysql> create  table courses(course_id int auto_increment primary key,course_name varchar(100),teacher varchar(50));
  Query OK, 0 rows affected (0.01 sec)

  3、插入数据

• 向students表中插入一些示例数据。

• 向courses表中插入一些示例数据

• mysql> insert into students(name,age,grade)values('zhsan',20,1);
  Query OK, 1 row affected (0.00 sec)
  mysql> INSERT INTO students (name, age, grade) VALUES ('李四', 19, '二年级');
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from courses;
  Empty set (0.00 sec)mysql> select * from students;
  +----+--------+------+-----------+
  | id | name   | age  | grade     |
  +----+--------+------+-----------+
  |  1 | zhsan  |   20 | 1         |
  |  2 | 李四   |   19 | 二年级    |
  +----+--------+------+-----------+
  2 rows in set (0.00 sec)
  mysql> update  students set name='张三',grade='一年级' where id=1;
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from students;
  +----+--------+------+-----------+
  | id | name   | age  | grade     |
  +----+--------+------+-----------+
  |  1 | 张三   |   20 | 一年级    |
  |  2 | 李四   |   19 | 二年级    |
  +----+--------+------+-----------+
  2 rows in set (0.00 sec)
  mysql> insert into courses(course_name,teacher)values('数学','张老师');
  Query OK, 1 row affected (0.00 sec)mysql> insert into courses(course_name,teacher)values('语文','王老师';
  Query OK, 1 row affected (0.00 sec)mysql> select * from courses;
  +-----------+-------------+-----------+
  | course_id | course_name | teacher   |
  +-----------+-------------+-----------+
  |         1 | 数学        | 张老师    |
  |         2 | 语文        | 王老师    |
  +-----------+-------------+-----------+
  2 rows in set (0.00 sec)mysql> 

  4、基本查询

• 查询students表中的所有数据。

• 查询年龄大于或等于20岁的学生信息。

• 查询姓名为"张三"的学生的所有信息。

• mysql> select * from students;
  +----+--------+------+-----------+
  | id | name   | age  | grade     |
  +----+--------+------+-----------+
  |  1 | 张三   |   20 | 一年级    |
  |  2 | 李四   |   19 | 二年级    |
  +----+--------+------+-----------+
  2 rows in set (0.00 sec)
  mysql> select * from students where age>=20;
  +----+--------+------+-----------+
  | id | name   | age  | grade     |
  +----+--------+------+-----------+
  |  1 | 张三   |   20 | 一年级    |
  +----+--------+------+-----------+
  1 row in set (0.00 sec)
  mysql> select * from students where name='张三';
  +----+--------+------+-----------+
  | id | name   | age  | grade     |
  +----+--------+------+-----------+
  |  1 | 张三   |   20 | 一年级    |
  +----+--------+------+-----------+
  1 row in set (0.00 sec)
  

  5、更新和删除数据

• 将名为"张三"的学生的年龄更新为21岁。

• 删除年龄小于18岁的学生信息。

• mysql> update students set age=21 where name='张三';
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  mysql> select * from students ;
  +----+--------+------+-----------+
  | id | name   | age  | grade     |
  +----+--------+------+-----------+
  |  1 | 张三   |   21 | 一年级    |
  |  2 | 李四   |   19 | 二年级    |
  +----+--------+------+-----------+
  2 rows in set (0.00 sec)
  mysql> insert into students(name,age,grade)values('王五',17,'一年级');
  Query OK, 1 row affected (0.01 sec)
  mysql> select * from students;
  +----+--------+------+-----------+
  | id | name   | age  | grade     |
  +----+--------+------+-----------+
  |  1 | 张三   |   21 | 一年级    |
  |  2 | 李四   |   19 | 二年级    |
  |  4 | 王五   |   17 | 一年级    |
  +----+--------+------+-----------+
  3 rows in set (0.00 sec)mysql> delete from students where age<18;
  Query OK, 1 row affected (0.00 sec)mysql> select * from students;
  +----+--------+------+-----------+
  | id | name   | age  | grade     |
  +----+--------+------+-----------+
  |  1 | 张三   |   21 | 一年级    |
  |  2 | 李四   |   19 | 二年级    |
  +----+--------+------+-----------+
  2 rows in set (0.00 sec)mysql> 

  6、多表查询

• （假设存在一个关联表student_courses，表示学生和课程的关联关系）

• 查询所有选修了"数学"课程的学生信息。

• 查询每个学生的选课数量。

• mysql> create table student_courses(student_id int,course_id int,foreign key (student_id) references student(id),foreign key(course_id)references courses(course_id));
  Query OK, 0 rows affected (0.01 sec)
  mysql> insert into student_courses(student_id,course_id)values(1,2);
  Query OK, 1 row affected (0.01 sec)mysql> insert into student_courses(student_id,course_id)values(2,2);
  Query OK, 1 row affected (0.01 sec)mysql> insert into student_courses(student_id,course_id)values(2,1);
  Query OK, 1 row affected (0.00 sec)mysql> SELECT s.name, s.age, s.grade  -> FROM student s  -> JOIN student_courses sc ON s.id = sc.student_id  -> JOIN courses c ON sc.course_id = c.course_id  -> WHERE c.course_name = '数学';
  +--------+------+-----------+
  | name   | age  | grade     |
  +--------+------+-----------+
  | 张三   |   21 | 一年级    |
  | 李四   |   19 | 二年级    |
  +--------+------+-----------+
  2 rows in set (0.00 sec)

  7、事务处理

• 编写一个事务，将学生"李四"选修的"数学"课程更改为"物理"，并确保如果更改失败，则回滚到之前的状态。

• BEGIN TRANSACTION 或 START TRANSACTION：开始一个新的事务。

• COMMIT：提交事务，将事务中的所有更改保存到数据库。

• ROLLBACK：撤销事务，回滚到事务开始之前的状态，撤销所有在事务中所做的更改

  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  mysql> update student_courses  set course_id=(select course_id from courses where course_name='物理')  where student_id=(select id from student where name="李四") and course_id=(select course_id from courses where course_name='数学') and course_id=(select course_id from courses where course_name='数学');
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  mysql> commit;
  Query OK, 0 rows affected (0.00 sec)

  8、索引和约束

  在students表的name字段上创建一个唯一索引，确保每个学生都有一个唯一的姓名。

  在courses表的course_name字段上创建一个普通索引，以优化查询性能。

• mysql> create unique index idx_name on student(name);
  Query OK, 0 rows affected (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 0mysql> create index idx_course_name on courses(course_name);
  Query OK, 0 rows affected (0.05 sec)
  Records: 0  Duplicates: 0  Warnings: 0

  9、聚合函数和分组

• 查询每个年级的学生数量。

• 查询年龄最大的学生信息。

• mysql> select grade,count(*) as student_count from student group by grade;
  +-----------+---------------+
  | grade     | student_count |
  +-----------+---------------+
  | 一年级    |             1 |
  | 二年级    |             1 |
  +-----------+---------------+
  2 rows in set (0.01 sec)mysql> 
  mysql> select * from student where age=(select max(age) from student);
  +----+--------+------+-----------+
  | id | name   | age  | grade     |
  +----+--------+------+-----------+
  |  1 | 张三   |   21 | 一年级    |
  +----+--------+------+-----------+
  1 row in set (0.00 sec)mysql> 

10、视图和存储过程

• 创建一个视图，显示年龄大于20岁的学生及其选课信息。

• 编写一个存储过程，用于添加新的学生和课程关联记录。

• mysql> create view view_age20 as-> select s.id,s.name,s.age,sc.course_id,c.course_name-> from student s-> join student_courses sc on s.id=sc.student_id-> join courses c on sc.course_id=c.course_id-> where s.age >20;
  Query OK, 0 rows affected (0.01 sec)mysql> delimiter //
  mysql> create procedure addsc(in student_id int,in course_id int)-> begin insert into student_courses(student_id,course_id)values (student_id,course_id);-> -> end  //
  Query OK, 0 rows affected (0.02 sec)
  mysql> delimiter ;