（MySQL）数据库school-新手必学案例（含答案）

题目：

创建数据库

-- 如果存在名为school的数据库就删除它

drop database if exists `school`;

-- 创建名为school的数据库并设置默认的字符集和排序规则

create database `school` default character set utf8mb4;

-- 切换到school数据库上下文环境

use `school`;

-- 创建学院表

create table `colleges`

(

`col_id` int unsigned not null auto_increment comment '编号',

`col_name` varchar(50) not null comment '名称',

`col_intro` varchar(500) not null default '' comment '介绍',

primary key (`col_id`)

) engine innodb auto_increment 1 comment '学院表';

-- 创建学生表

create table `students`

(

`stu_id` int unsigned not null comment '学号',

`stu_name` varchar(20) not null comment '姓名',

`stu_sex` boolean default 1 not null comment '性别',

`stu_birth` date not null comment '出生日期',

`stu_addr` varchar(255) comment '籍贯',

`col_id` int unsigned not null comment '所属学院',

primary key (`stu_id`),

constraint `fk_student_col_id` foreign key (`col_id`) references `colleges` (`col_id`)

) engine innodb comment '学生表';

-- 创建教师表

create table `teachers`

(

`tea_id` int unsigned not null comment '工号',

`tea_name` varchar(20) not null comment '姓名',

`tea_title` varchar(10) not null default '助教' comment '职称',

`col_id` int unsigned not null comment '所属学院',

primary key (`tea_id`),

constraint `fk_teacher_col_id` foreign key (`col_id`) references `colleges` (`col_id`)

) engine innodb comment '老师表';

-- 创建课程表

create table `courses`

(

`cou_id` int unsigned not null comment '编号',

`cou_name` varchar(50) not null comment '名称',

`cou_credit` int not null comment '学分',

`tea_id` int unsigned not null comment '授课老师',

primary key (`cou_id`),

constraint `fk_course_tea_id` foreign key (`tea_id`) references `teachers` (`tea_id`)

) engine innodb comment '课程表';

-- 创建选课记录表

create table `records`

(

`rec_id` bigint unsigned not null auto_increment comment '选课记录号',

`stu_id` int unsigned not null comment '学号',

`cou_id` int unsigned not null comment '课程编号',

`sel_date` date not null comment '选课日期',

`score` decimal(4,1) comment '考试成绩',

primary key (`rec_id`),

constraint `fk_record_stu_id` foreign key (`stu_id`) references `students` (`stu_id`),

constraint `fk_record_cou_id` foreign key (`cou_id`) references `courses` (`cou_id`),

constraint `uk_record_stu_cou` unique (`stu_id`, `cou_id`)

) engine innodb auto_increment 1001 comment '选课记录表';

DML部分：

use `school`;

-- 01. 查询所有学生的所有信息

-- 02. 查询学生的学号、姓名和籍贯(投影和别名)

-- 03. 查询所有课程的名称及学分(投影和别名)

-- 04. 查询所有女学生的姓名和出生日期(数据筛选)

-- 05. 查询籍贯为“四川成都”的女学生的姓名和出生日期(数据筛选)

-- 06. 查询籍贯为“四川成都”或者性别是女的学生(数据筛选)

-- 07. 查询所有80后学生的姓名、性别和出生日期(数据筛选)

-- 08. 查询学分大于2分的课程名称和学分(数据筛选)

-- 09. 查询学分是奇数的课程的名称和学分(数据筛选)

-- 10. 查询选择选了1111的课程考试成绩在90分以上的学生学号(数据筛选)

-- 11. 查询名字叫“杨过”的学生的姓名和性别

   

-- 12. 查询姓“杨”的学生姓名和性别(模糊查询)

-- 13. 查询姓“杨”名字两个字的学生姓名和性别(模糊查询)

-- 14. 查询姓“杨”名字三个字的学生姓名和性别(模糊查询)

-- 15. 查询名字中有“不”字或“嫣”字的学生的姓名(模糊查询)

-- 16. 查询姓“杨”或姓“林”名字三个字的学生的姓名(正则表达式模糊查询)

-- 17. 查询没有录入籍贯的学生姓名(空值处理)

-- 18. 查询录入了籍贯的学生姓名(空值处理)

-- 19. 查询学生选课的所有日期(去重)

-- 20. 查询学生的籍贯(空值处理和去重)

-- 21. 查询男学生的姓名和生日按年龄从大到小排列(排序)

-- 22. 将上面查询中的生日换算成年龄(日期函数、数值函数)

-- 23. 查询年龄最大的学生的出生日期(聚合函数)

-- 24. 查询年龄最小的学生的出生日期(聚合函数)

-- 25. 查询编号为1111的课程考试成绩的最高分(聚合函数)

-- 26. 查询学号为1001的学生考试成绩的最低分(聚合函数)

-- 27. 查询学号为1001的学生考试成绩的平均分和标准差(聚合函数)

-- 28. 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分(聚合函数)

-- 29. 查询男女学生的人数(分组和聚合函数)

-- 30. 查询每个学院学生人数(分组和聚合函数)

-- 31. 查询每个学院男女学生人数(分组和聚合函数)

-- 32. 查询选课学生的学号和平均成绩(分组和聚合函数)

-- 33. 查询平均成绩大于等于90分的学生的学号和平均成绩(分组和聚合函数)

-- 34. 查询所有课程成绩大于80分的同学的学号和平均成绩(分组和聚合函数)

-- 35. 查询年龄最大的学生的姓名(嵌套查询)

-- 36. 查询选了两门以上的课程的学生姓名(嵌套查询/分组/数据筛选)

-- 37. 查询学生的姓名、生日和所在学院名称(连接查询)

-- 38. 查询学生姓名、课程名称以及成绩(连接查询)

-- 39. 上面的查询结果按课程和成绩排序取前5条数据(分页查询)

-- 40. 上面的查询结果按课程和成绩排序取第6-10条数据(分页查询)

-- 41. 上面的查询结果按课程和成绩排序取第11-15条数据(分页查询)

-- 42. 查询选课学生的姓名和平均成绩(嵌套查询和连接查询)

-- 43. 查询学生的姓名和选课的数量(嵌套查询和连接查询)

-- 44. 查询每个学生的姓名和选课数量(左外连接和嵌套查询)

-- 45. 查询没有选课的学生的姓名(左外连接和数据筛选)

-- 补充练习

-- 01. 查询课程1111比课程2222成绩高的所有学生的学号

-- 02. 查询没有学过张三丰老师的课程的同学的学号和姓名

-- 03. 查询学过课程1111和课程2222的同学的学号和姓名

-- 04. 查询学习了所有课程的同学的学号和姓名

-- 05. 查询至少有一门课跟学号1001同学所学课程相同的同学的学号和姓名

-- 06. 查询所学课程与1002同学完全相同的同学的学号和姓名

-- 07. 查询有课程不及格（60分以下）的同学的学号和姓名

-- 08. 查询每门课程的名称、最高分、最低分和平均分

-- 09. 查询每门课程各分数段（100-90，90-80，80-70，70-60，60以下）的人数占比

-- 10. 查询本周过生日的学生的姓名和生日

 答案：

use `school`;

-- 01. 查询所有学生的所有信息

select *

  from students;

select stu_id

     , stu_name

     , stu_sex

     , stu_birth

     , stu_addr

     , col_id

  from students;

-- 02. 查询学生的学号、姓名和籍贯(投影和别名)

select stu_id as 学号

     , stu_name as 姓名

     , stu_addr as 籍贯

  from students;

-- 03. 查询所有课程的名称及学分(投影和别名)

select cou_name as 课程名称

     , cou_credit as 学分

  from courses;

-- 04. 查询所有女学生的姓名和出生日期(数据筛选)

select stu_name as 姓名

     , stu_birth as 出生日期

  from students

 where stu_sex = 0;

-- 05. 查询籍贯为“四川成都”的女学生的姓名和出生日期(数据筛选)

select stu_name as 姓名

     , stu_birth as 出生日期

  from students

 where stu_sex = 0 and stu_addr = '四川成都';

-- 06. 查询籍贯为“四川成都”或者性别是女的学生(数据筛选)

select stu_name as 姓名

     , stu_birth as 出生日期

  from students

 where stu_sex = 0 or stu_addr = '四川成都';

-- 并集运算

select stu_name as 姓名

     , stu_birth as 出生日期

  from students

 where stu_sex = 0

 union

select stu_name as 姓名

     , stu_birth as 出生日期

  from students

 where stu_addr = '四川成都';

-- 07. 查询所有80后学生的姓名、性别和出生日期(数据筛选)

select stu_name

     , stu_sex

     , stu_birth

  from students

 where '1980-1-1' <= stu_birth and stu_birth <= '1989-12-31';

select stu_name

     , stu_sex

     , stu_birth

  from students

 where stu_birth between '1980-1-1' and '1989-12-31';

-- 08. 查询学分大于2分的课程名称和学分(数据筛选)

select cou_name

     , cou_credit

  from courses

 where cou_credit > 2;

-- 09. 查询学分是奇数的课程的名称和学分(数据筛选)

select cou_name

     , cou_credit

  from courses

 where cou_credit % 2 <> 0;

-- 10. 查询选择选了1111的课程考试成绩在90分以上的学生学号(数据筛选)

select stu_id

  from records

 where cou_id = 1111 and score > 90;

-- 11. 查询名字叫“杨过”的学生的姓名和性别

-- MySQL方言

select stu_name as 姓名

     , if(stu_sex, '男', '女') as 性别

  from students

 where stu_name = '杨过';

-- 标准SQL

select stu_name as 姓名

     , case stu_sex

            when 1 then '男'

            when 0 then '女'

            else '未知'

        end as 性别

  from students

 where stu_name = '杨过';

   

-- 12. 查询姓“杨”的学生姓名和性别(模糊查询)

-- 通配符 - wild card - % - 匹配零个或任意多个字符

select stu_name as 姓名

     , case stu_sex

            when 1 then '男'

            when 0 then '女'

            else '未知'

        end as 性别

  from students

 where stu_name like '杨%';

-- 13. 查询姓“杨”名字两个字的学生姓名和性别(模糊查询)

-- 通配符 - wild card - _ - 刚好匹配一个字符

select stu_name as 姓名

     , case stu_sex

            when 1 then '男'

            when 0 then '女'

            else '未知'

        end as 性别

  from students

 where stu_name like '杨_';

-- 14. 查询姓“杨”名字三个字的学生姓名和性别(模糊查询)

select stu_name as 姓名

     , case stu_sex

            when 1 then '男'

            when 0 then '女'

            else '未知'

        end as 性别

  from students

 where stu_name like '杨__';

-- 15. 查询名字中有“不”字或“嫣”字的学生的姓名(模糊查询)

select stu_name

  from students

 where stu_name like '%不%' or stu_name like '%嫣%';

select stu_name

  from students

 where stu_name like '%不%'

 union

select stu_name

  from students

 where stu_name like '%嫣%';

-- 16. 查询姓“杨”或姓“林”名字三个字的学生的姓名(正则表达式模糊查询)

-- regular expression

select stu_name

  from students

 where stu_name regexp '[杨林][\\u4e00-\\u9fa5]{2}';

-- 17. 查询没有录入籍贯的学生姓名(空值处理)

-- 三值逻辑 - true / false / unknown

select stu_name

  from students

 where stu_addr is null;

-- 18. 查询录入了籍贯的学生姓名(空值处理)

select stu_name

  from students

 where stu_addr is not null;

-- 19. 查询学生选课的所有日期(去重)

-- asending / descending

select distinct sel_date

  from records

 order by sel_date asc;

-- 20. 查询学生的籍贯(空值处理和去重)

select distinct stu_addr

  from students

 where stu_addr is not null;

-- 21. 查询男学生的姓名和生日按年龄从大到小排列(排序)

select stu_name

     , stu_birth

  from students

 where stu_sex = 1

 order by stu_birth asc;

-- 22. 将上面查询中的生日换算成年龄(日期函数、数值函数)

-- 日期时间函数

select current_date();

select curdate();

select current_time();

select curtime();

select current_timestamp();

select timestampdiff(year, '1998-10-5', '2000-10-3');

select timestampdiff(year, '1998-10-5', '2000-10-13');

select timestampdiff(month, '1998-10-5', '2000-10-3');

select timestampdiff(minute, '1998-10-5', '2000-10-3');

select stu_name

     , timestampdiff(year, stu_birth, current_date()) as stu_age

  from students

 where stu_sex = 1

 order by stu_age desc;

-- 聚合函数：max / min / sum / avg / count / var_pop / var_samp / stddev_pop / stddev_samp

-- 23. 查询年龄最大的学生的出生日期(聚合函数)

select min(stu_birth)

  from students;

-- 24. 查询年龄最小的学生的出生日期(聚合函数)

select max(stu_birth)

  from students;

-- 25. 查询编号为1111的课程考试成绩的最高分(聚合函数)

select max(score)

  from records

 where cou_id = 1111;

-- 26. 查询学号为1001的学生考试成绩的最低分(聚合函数)

select min(score)

  from records

 where stu_id = 1001;

-- 27. 查询学号为1001的学生考试成绩的平均分和标准差(聚合函数)

select avg(score) as 平均分

     , sqrt(var_pop(score)) as 标准差

     , stddev_pop(score) as 标准差

  from records

 where stu_id = 1001;

-- 查询学号为1001的学生选了几门课

select count(*)

  from records

 where stu_id = 1001;

-- 28. 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分(聚合函数)

select round(sum(score) / count(*), 1)

  from records

 where stu_id = 1001;

-- MySQL方言 - ifnull / Oracle方言 - nvl

select avg(ifnull(score, 0))

  from records

 where stu_id = 1001;

-- coalesce - 返回参数列表中的第一个非null值

select avg(coalesce(score, 0))

  from records

 where stu_id = 1001;

-- 29. 查询男女学生的人数(分组和聚合函数)

select stu_sex

     , count(*)

  from students

 group by stu_sex;

-- 30. 查询每个学院学生人数(分组和聚合函数)

select col_id

     , count(*)

  from students

 group by col_id;

-- 31. 查询每个学院男女学生人数(分组和聚合函数)

select col_id

     , stu_sex

     , count(*)

  from students

 group by col_id, stu_sex;

-- 32. 查询选课学生的学号和平均成绩(分组和聚合函数)

select stu_id

     , round(avg(score), 1) as avg_score

  from records

 group by stu_id;

-- 33. 查询平均成绩大于等于90分的学生的学号和平均成绩(分组和聚合函数)

-- Error Code: 1054. Unknown column 'avg_score' in 'where clause'

-- Error Code: 1111. Invalid use of group function

-- where子句实现分组前的数据筛选，having子句实现分组后的数据筛选

select stu_id

     , round(avg(score), 1) as avg_score

  from records

 group by stu_id

having avg(score) >= 90;

-- 34. 查询所有课程成绩大于80分的同学的学号和平均成绩(分组和聚合函数)

select stu_id

     , round(avg(score), 1) as avg_score

  from records

 group by stu_id

having min(score) > 80;

-- 35. 查询年龄最大的学生的姓名(嵌套查询) - subquery

-- 嵌套查询（子查询）：把一个查询的结果作为另外一个查询的一部分来使用

-- 用户变量前面需要加一个@ - set @x = 0;

select stu_name

  from students

 where stu_birth = (select min(stu_birth)

                      from students);

-- 36. 查询选了两门以上的课程的学生姓名(嵌套查询/分组/数据筛选)

select stu_name

  from students

 where stu_id in (select stu_id

                        from records

                       group by stu_id

                      having count(*) > 2);

select stu_name

  from students

 where stu_id = any(select stu_id

                          from records

                         group by stu_id

                        having count(*) > 2);

-- 37. 查询学生的姓名、生日和所在学院名称(连接查询)

select stu_name

     , stu_birth

     , col_name

  from students, colleges

 where students.col_id = colleges.col_id;

-- 内连接

select stu_name

     , stu_birth

     , col_name

  from students inner join colleges

       on students.col_id = colleges.col_id;

-- 自然连接：通过“同名列+值相等”进行连接

select stu_name

     , stu_birth

     , col_name

  from students natural join colleges;

-- 38. 查询学生姓名、课程名称以及成绩(连接查询)

select stu_name

     , cou_name

     , score

  from students, courses, records

 where students.stu_id = records.stu_id

       and courses.cou_id = records.cou_id

       and score is not null;

select stu_name

     , cou_name

     , score

  from students

       inner join courses

       inner join records

       on students.stu_id = records.stu_id

           and courses.cou_id = records.cou_id

 where score is not null;

select stu_name

     , cou_name

     , score

  from students

       natural join courses

       natural join records

 where score is not null;

-- 39. 上面的查询结果按课程和成绩排序取前5条数据(分页查询)

select stu_name

     , cou_name

     , score

  from students

       natural join courses

       natural join records

 where score is not null

 order by cou_id asc, score desc

 limit 5;

-- 40. 上面的查询结果按课程和成绩排序取第6-10条数据(分页查询)

select stu_name

     , cou_name

     , score

  from students

       natural join courses

       natural join records

 where score is not null

 order by cou_id asc, score desc

 limit 5

offset 5;

-- 41. 上面的查询结果按课程和成绩排序取第11-15条数据(分页查询)

select stu_name

     , cou_name

     , score

  from students

       natural join courses

       natural join records

 where score is not null

 order by cou_id asc, score desc

 limit 5

offset 10;

-- 42. 查询选课学生的姓名和平均成绩(嵌套查询和连接查询)

-- Error Code: 1248. Every derived table must have its own alias

select stu_name

     , avg_score

  from students

       natural join (select stu_id

                          , round(avg(score), 1) as avg_score

                            from records

                      group by stu_id) as temp;

-- 43. 查询学生的姓名和选课的数量(嵌套查询和连接查询)

select stu_name

     , cou_count

  from students

       inner join (select stu_id

                              , count(*) as cou_count

                          from records

                         group by stu_id) as temp

            on students.stu_id = temp.stu_id;

-- 44. 查询每个学生的姓名和选课数量(左外连接和嵌套查询)

-- 左外连接：左表（写在join左边的表）不满足连表条件的数据也要取出来，右表对应的字段填充null

-- 右外连接：右表（写在join右边的表）不满足连表条件的数据也要取出来，左表对应的字段填充null

-- 全外连接：左表和右表不满足连表条件的数据全部取出来，对应的字段填充null（MySQL不支持）

select stu_name

     , coalesce(cou_count, 0)

  from students

       left join (select stu_id

                            , count(*) as cou_count

                         from records

                       group by stu_id) as temp

            on students.stu_id = temp.stu_id;

-- 45. 查询没有选课的学生的姓名(左外连接和数据筛选)

-- 左外连接求差集

select stu_name

  from students

       left join records

            on students.stu_id = records.stu_id

 where records.rec_id is null;

-- 集合运算

select stu_name

  from students

 where stu_id not in (select distinct stu_id

                        from records);

select stu_name

  from students

 where stu_id <> all(select distinct stu_id

                            from records);

-- 存在性判断

select stu_name

  from students

 where not exists (select 'x'

                          from records

                         where records.stu_id = students.stu_id);

-- 补充练习

-- 01. 查询课程1111比课程2222成绩高的所有学生的学号

-- 02. 查询没有学过张三丰老师的课程的同学的学号和姓名

-- 03. 查询学过课程1111和课程2222的同学的学号和姓名

-- 04. 查询学习了所有课程的同学的学号和姓名

-- 05. 查询至少有一门课跟学号1001同学所学课程相同的同学的学号和姓名

-- 06. 查询所学课程与1002同学完全相同的同学的学号和姓名

-- 07. 查询有课程不及格（60分以下）的同学的学号和姓名

-- 08. 查询每门课程的名称、最高分、最低分和平均分

-- 09. 查询每门课程各分数段（100-90，90-80，80-70，70-60，60以下）的人数占比

-- 10. 查询本周过生日的学生的姓名和生日