mysql实战题目练习

最新推荐文章于 2024-06-11 18:40:56 发布

WWNY666

最新推荐文章于 2024-06-11 18:40:56 发布

阅读量1.4k

点赞数 44

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/WWNY666/article/details/138871716

版权

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: 0

mysql> 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 ;

WWNY666

关注

44
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
mysql实战题目练习

编写一个事务，将学生"李四"选修的"数学"课程更改为"物理"，并确保如果更改失败，则回滚到之前的状态。：撤销事务，回滚到事务开始之前的状态，撤销所有在事务中所做的更改。字段上创建一个唯一索引，确保每个学生都有一个唯一的姓名。创建一个视图，显示年龄大于20岁的学生及其选课信息。编写一个存储过程，用于添加新的学生和课程关联记录。：提交事务，将事务中的所有更改保存到数据库。将名为"张三"的学生的年龄更新为21岁。查询所有选修了"数学"课程的学生信息。查询姓名为"张三"的学生的所有信息。（字符串，最大长度50）。
复制链接

扫一扫