习题

创建表, 关联表, 插入数据

# 创建班级表, 插入数据
create table class (
    class_id int auto_increment primary key,
    caption varchar(32) not null default ""
    ) charset utf8;
insert into class (caption) values 
("三年二班"), ("一年三班"), ("三年一班");    

# 创建学生表, 使用外键关联班级表, 插入数据
create table student (
    student_id int auto_increment primary key,
    sname varchar(32) not null default "",
    gender varchar(32) not null default "",
    class_id int,
    constraint fk_cls_id foreign key (class_id)
    references class (class_id)
    ) charset utf8;
insert into student (sname, gender, class_id) values
("钢蛋", "女", 1), ("铁锤", "男", 1), ("山炮", "男", 2);

# 创建老师表, 插入数据   
create table teacher (
    t_id int auto_increment primary key,
    t_name varchar(32) not null default ""
    ) charset utf8;
insert into teacher (t_name) values
("波多"), ("苍井"), ("饭岛");

# 创建课程表, 使用外键关联老师表, 插入数据
create table course (
    course_id int auto_increment primary key,
    cname varchar(32) not null default "",
    teacher_id int,
    constraint fk_tea_id foreign key (teacher_id)
    references teacher (t_id)
    ) charset utf8;
insert into course (cname, teacher_id) values
("生物", 1), ("体育", 1), ("物理", 2);

# 创建成绩表, 使用两个外键分别关联学生表和课程表, 插入数据
create table score (
    score_id int auto_increment primary key,
    student_id int not null default 0,
    course_id int not null default 0,
    number int not null default 0,
    
    constraint fk_sco_stu foreign key (student_id)
    references student (student_id),
    
    constraint fk_sco_cou foreign key (course_id)
    references course (course_id)
    ) charset utf8;
insert into score (student_id, course_id, number) values
(1, 1, 60), (1, 2, 59), (2, 2, 100);

操作表

查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)

select distinct student.sname, student.student_id
from student left join score
on student.student_id = score.student_id 
where score.number > 60;

# 注意1: 去重的关键字distinct只写一次
# 注意2: 查询的关键字模糊时加表名, 例如student.student_id

查询每个老师教授的课程数量和老师信息

select teacher.*, count(course.teacher_id) as course_amount 
from teacher left join course 
on teacher.t_id = course.teacher_id
group by teacher.t_id;
+------+--------+---------------+
| t_id | t_name | course_amount |
+------+--------+---------------+
|    1 | 波多   |             2 |
|    2 | 苍井   |             1 |
|    3 | 饭岛   |             0 |
+------+--------+---------------+

# 注意: 联结表中分组的字段属于哪个表, 就只能查询这个表的字段, 不包含分组的字段的表的字段只能通过聚合函数进行统计

查询学生信息以及学生所在的班级信息

select student.*, class.caption from student left join class
on student.class_id = class.class_id;

学生中男生的个数和女生的个数

select gender, count(gender) as amount from student group by gender;  # student.* 表示student表中的所有字段

获取所有学习'生物'的学生的学号和成绩；姓名

select student.student_id, score.number, student.sname
from student left join score
on student.student_id = score.student_id
left join course
on score.course_id = course.course_id
where course.cname = "生物";

查询姓“波”的老师的个数

select count(t_name) as Li_amount from teacher where t_name like "波%";

查询课程成绩小于60分的同学的学号、姓名

select student.student_id, sname from student left join score
on student.student_id = score.student_id
where score.number < 60;

删除学习“波多”老师课的score表记录

delete from score
where course_id in (  # 嵌套子查询
    select course.course_id from course
left join teacher 
on course.teacher_id = teacher.t_id
where t_name = "波多"
    );

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
```
select course_id, max(number), min(number) from score group by course_id;
```

查询每门课程被选修的学生数

select course_id, count(student_id) from score group by course_id;

查询姓“钢”的学生名单

select * from student where sname like "钢%";

查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
```
select course_id, avg(number) as average from score
group by course_id
order by average, course_id desc;
```

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select student.student_id, sname, avg(number) as average from student left join score
on student.student_id = score.student_id
group by student.student_id
having average > 60;  # 对分组统计结果进行二次筛选

查询课程编号为2且课程成绩 >= 59 的学生的学号和姓名

select student.student_id, sname from student left join score
on student.student_id = score.student_id
where score.course_id = 2 and number >= 59;

查询各个课程及相应的选修人数

select course_id, count(student_id) as stu_amount from score 
group by course_id;

查询“2”课程分数小于60，按分数降序排列的同学学号
```
select student_id from score 
where course_id = 2 and number < 60;
```

删除学号为“2”的同学的“2”课程的成绩

delete from score where student_id = 2 and course_id = 2;