MySQL

一、表关系

请创建如下表，并创建相关约束

C:\Users\Administrator>mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.39 MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> create database db charset utf8;Query OK, 1 row affected (0.01 sec)mysql> use db;Database changedmysql> create table class_grade( -> gid int primary key auto_increment, -> gname varchar(16) not null unique);Query OK, 0 rows affected (1.14 sec)mysql> create table class( -> cid int primary key auto_increment, -> caption varchar(16) not null, -> grade_id int not null, -> foreign key(grade_id) references class_grade(gid));Query OK, 0 rows affected (0.95 sec)mysql> create table student( -> sid int primary key auto_increment, -> sname varchar(16) not null, -> sex enum(‘女‘,‘男‘), -> class_id int not null, -> foreign key(class_id) references class(cid));Query OK, 0 rows affected (0.64 sec)mysql> create table teacher( -> tid int primary key auto_increment, -> tname varchar(16) not null);Query OK, 0 rows affected (0.59 sec)mysql> create table course( -> cid int primary key auto_increment, -> cname varchar(16) not null, -> teacher_id int not null, -> foreign key(teacher_id) references teacher(tid));Query OK, 0 rows affected (0.53 sec)mysql> create table score( -> sid int primary key auto_increment, -> student_id int not null, -> course_id int not null, -> score int not null, -> primary key(student_id,course_id), -> foreign key(student_id) references student(sid) -> on delete cascade -> on update cascade, -> foreign key(course_id) references course(cid) -> on delete cascade -> on update cascade);ERROR 1068 (42000): Multiple primary key definedmysql> create table score( -> sid int not null unique auto_increment, -> student_id int not null, -> course_id int not null, -> score int not null, -> primary key(student_id,course_id), -> foreign key(student_id) references student(sid) -> on delete cascade -> on update cascade, -> foreign key(course_id) references course(cid) -> on delete cascade -> on update cascade -> );Query OK, 0 rows affected (1.11 sec)mysql> create teach2cls( -> tcid int not null unique auto_increment, -> tid int not null, -> cid int not null, -> primary key(tid,cid), -> foreign key(tid) references teacher(tid) -> on delete cascade -> on update cascade, -> foreign key(cid) references class(cid) -> on delete cascade -> on update cascade);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘teach2cls(tcid int not null unique auto_increment,tid int not null,cid int no‘ at line 1mysql> create table teach2cls( -> tcid int not null unique auto_increment, -> tid int not null, -> cid int not null, -> primary key(tid,cid), -> foreign key(tid) references teacher(tid) -> on delete cascade -> on update cascade, -> foreign key(cid) references class(cid) -> on delete cascade -> on update cascade -> );Query OK, 0 rows affected (0.87 sec)mysql>

mysql> desc teach2cls;+-------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+----------------+| tcid | int(11) | NO | UNI | NULL | auto_increment || tid | int(11) | NO | PRI | NULL | || cid | int(11) | NO | PRI | NULL | |+-------+---------+------+-----+---------+----------------+3 rows in set (0.10 sec)mysql> desc score;+------------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+---------+------+-----+---------+----------------+| sid | int(11) | NO | UNI | NULL | auto_increment || student_id | int(11) | NO | PRI | NULL | || course_id | int(11) | NO | PRI | NULL | || score | int(11) | NO | | NULL | |+------------+---------+------+-----+---------+----------------+4 rows in set (0.01 sec)mysql> desc course;+------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+----------------+| cid | int(11) | NO | PRI | NULL | auto_increment || cname | varchar(16) | NO | | NULL | || teacher_id | int(11) | NO | MUL | NULL | |+------------+-------------+------+-----+---------+----------------+3 rows in set (0.02 sec)mysql> desc teacher;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| tid | int(11) | NO | PRI | NULL | auto_increment || tname | varchar(16) | NO | | NULL | |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.01 sec)mysql> desc student;+----------+-------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------------+------+-----+---------+----------------+| sid | int(11) | NO | PRI | NULL | auto_increment || sname | varchar(16) | NO | | NULL | || sex | enum(‘女‘,‘男‘) | YES | | NULL | || class_id | int(11) | NO | MUL | NULL | |+----------+-------------------+------+-----+---------+----------------+4 rows in set (0.08 sec)mysql> desc class;+----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+----------------+| cid | int(11) | NO | PRI | NULL | auto_increment || caption | varchar(16) | NO | | NULL | || grade_id | int(11) | NO | MUL | NULL | |+----------+-------------+------+-----+---------+----------------+3 rows in set (0.01 sec)mysql> desc class_grade;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| gid | int(11) | NO | PRI | NULL | auto_increment || gname | varchar(16) | NO | UNI | NULL | |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.01 sec)

二、插入数据

mysql> select * from class;+-----+-----------------+----------+| cid | caption | grade_id |+-----+-----------------+----------+| 1 | 一年级一班 | 1 || 2 | 一年级二班 | 1 || 3 | 一年级三班 | 1 || 4 | 二年级一班 | 2 || 5 | 二年级二班 | 2 || 6 | 二年级三班 | 2 || 7 | 三年级一班 | 3 || 8 | 三年级二班 | 3 || 9 | 三年级三班 | 3 |+-----+-----------------+----------+9 rows in set (0.00 sec)mysql> select * from class_grade;+-----+-----------+| gid | gname |+-----+-----------+| 1 | 一年级 || 3 | 三年级 || 2 | 二年级 || 4 | 四年级 |+-----+-----------+4 rows in set (0.00 sec)mysql> select * from course;+-----+--------+------------+| cid | cname | teacher_id |+-----+--------+------------+| 1 | 生物 | 1 || 2 | 体育 | 1 || 3 | 物理 | 2 || 4 | 化学 | 3 || 5 | 英语 | 3 || 6 | python | 4 || 7 | linux | 5 |+-----+--------+------------+7 rows in set (0.00 sec)mysql> select * from student;+-----+-----------+------+----------+| sid | sname | sex | class_id |+-----+-----------+------+----------+| 1 | 乔丹 | 女 | 1 || 2 | 艾弗森 | 女 | 1 || 3 | 科比 | 男 | 2 || 4 | alex | 女 | 2 || 5 | egon | 男 | 3 || 6 | egon2 | 女 | 3 || 7 | kris | 男 | 4 || 8 | wxx | 女 | 5 || 9 | 杜拉拉 | 女 | 6 || 10 | 姗姗 | 女 | 7 || 11 | 丹丹 | 男 | 8 || 12 | simith | 男 | 9 |+-----+-----------+------+----------+12 rows in set (0.00 sec)mysql> select * from teacher;+-----+-----------+| tid | tname |+-----+-----------+| 1 | 张三 || 2 | 李四 || 3 | 王五 || 4 | 老男孩 || 5 | 小女孩 |+-----+-----------+5 rows in set (0.00 sec)mysql> select * from score;+-----+------------+-----------+-------+| sid | student_id | course_id | score |+-----+------------+-----------+-------+| 1 | 1 | 1 | 60 || 2 | 1 | 2 | 59 || 3 | 2 | 2 | 99 || 4 | 3 | 3 | 88 || 5 | 3 | 4 | 76 || 6 | 4 | 5 | 34 || 7 | 5 | 6 | 68 || 8 | 6 | 6 | 93 || 9 | 7 | 7 | 77 || 10 | 8 | 6 | 86 || 11 | 9 | 3 | 90 || 12 | 10 | 4 | 100 || 13 | 11 | 5 | 52 || 14 | 12 | 7 | 27 |+-----+------------+-----------+-------+14 rows in set (0.00 sec)mysql> select * from teach2cls;+------+-----+-----+| tcid | tid | cid |+------+-----+-----+| 1 | 1 | 1 || 2 | 1 | 2 || 3 | 2 | 1 || 4 | 3 | 2 || 5 | 4 | 3 || 6 | 4 | 4 || 7 | 5 | 6 || 8 | 5 | 5 || 9 | 3 | 7 || 10 | 2 | 8 || 11 | 4 | 9 |+------+-----+-----+11 rows in set (0.00 sec)

三、操作表

1、自行创建测试数据；见上

2、查询学生总人数；

mysql> select count(sid) as count_student from student;+---------------+| count_student |+---------------+| 12 |+---------------+1 row in set (0.08 sec)

3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；

mysql> select sid,sname from student -> where sid in( -> SELECT score.student_id from score inner join course on score.course_id = course.cid -> where course.cname in (‘生物‘, ‘物理‘) and score.score > 60 -> );+-----+-----------+| sid | sname |+-----+-----------+| 1 | 乔丹 || 3 | 科比 || 9 | 杜拉拉 |+-----+-----------+

4、查询每个年级的班级数，取出班级数最多的前三个年级；

mysql> select class_grade.gname from class_grade inner join( -> select grade_id,count(cid) as count_cid from class -> group by grade_id -> order by count_cid desc -> limit 3) -> as t1 -> on class_grade.gid = t1.grade_id;+-----------+| gname |+-----------+| 一年级 || 三年级 || 二年级 |+-----------+3 rows in set (0.00 sec)

5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；

mysql> select student.sid,student.sname,t1.avg_score from student -> inner join(select student_id,avg(score) as avg_score from score -> group by student_id -> having avg(score) in (( -> select avg(score) as max_avg_score from score -> group by student_id -> order by avg(score) desc -> limit 1),( -> select avg(score) as min_avg_score from score -> group by student_id -> order by avg(score) asc -> limit 1) -> )) as t1 -> on student.sid = t1.student_id;+-----+--------+-----------+| sid | sname | avg_score |+-----+--------+-----------+| 10 | 姗姗 | 100.0000 || 12 | simith | 27.0000 |+-----+--------+-----------+2 rows in set (0.05 sec)

6、查询每个年级的学生人数；

mysql> select student.sid, class.grade_id from student,class -> where student.class_id = class.cid;+-----+----------+| sid | grade_id |+-----+----------+| 1 | 1 || 2 | 1 || 3 | 1 || 4 | 1 || 5 | 1 || 6 | 1 || 7 | 2 || 8 | 2 || 9 | 2 || 10 | 3 || 11 | 3 || 12 | 3 |+-----+----------+12 rows in set (0.00 sec)mysql> select t1.grade_id, count(t1.sid) as student_count from -> (select student.sid, class.grade_id from student,class -> where student.class_id = class.cid)as t1 -> group by t1.grade_id;+----------+---------------+| grade_id | student_count |+----------+---------------+| 1 | 6 || 2 | 3 || 3 | 3 |+----------+---------------+3 rows in set (0.00 sec)

7、查询每位学生的学号，姓名，选课数，平均成绩；

mysql> select student_id, count(course_id) as count_course, -> avg(score) as avg_score from score -> group by student_id;+------------+--------------+-----------+| student_id | count_course | avg_score |+------------+--------------+-----------+| 1 | 3 | 65.0000 || 2 | 1 | 99.0000 || 3 | 1 | 88.0000 || 4 | 1 | 34.0000 || 5 | 1 | 68.0000 || 6 | 1 | 93.0000 || 7 | 1 | 77.0000 || 8 | 1 | 86.0000 || 9 | 1 | 90.0000 || 10 | 1 | 100.0000 || 11 | 1 | 52.0000 || 12 | 1 | 27.0000 |+------------+--------------+-----------+12 rows in set (0.00 sec)mysql> select student.sid, student.sname,t1.count_course, t1.avg_score from student left join( -> select student_id, count(course_id) as count_course, -> avg(score) as avg_score from score -> group by student_id) -> as t1 -> on student.sid = t1.student_id;+-----+-----------+--------------+-----------+| sid | sname | count_course | avg_score |+-----+-----------+--------------+-----------+| 1 | 乔丹 | 3 | 65.0000 || 2 | 艾弗森 | 1 | 99.0000 || 3 | 科比 | 1 | 88.0000 || 4 | alex | 1 | 34.0000 || 5 | egon | 1 | 68.0000 || 6 | egon2 | 1 | 93.0000 || 7 | kris | 1 | 77.0000 || 8 | wxx | 1 | 86.0000 || 9 | 杜拉拉 | 1 | 90.0000 || 10 | 姗姗 | 1 | 100.0000 || 11 | 丹丹 | 1 | 52.0000 || 12 | simith | 1 | 27.0000 |+-----+-----------+--------------+-----------+12 rows in set (0.00 sec)

8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；

mysql> select student_id, course_id, score from score -> where student_id = 2 and score in (( -> select max(score) from score where student_id = 2),( -> select min(score) from score where student_id = 2));+------------+-----------+-------+| student_id | course_id | score |+------------+-----------+-------+| 2 | 2 | 99 |+------------+-----------+-------+1 row in set (0.03 sec)mysql> select student.sname,course.cname,t1.score from( -> select student_id, course_id, score from score -> where student_id = 2 and score in (( -> select max(score) from score where student_id = 2),( -> select min(score) from score where student_id = 2)) -> )as t1 inner join student on t1.student_id = student.sid -> inner join course on t1.course_id = course.cid;+-----------+--------+-------+| sname | cname | score |+-----------+--------+-------+| 艾弗森 | 体育 | 99 |+-----------+--------+-------+1 row in set (0.00 sec)

9、查询姓“李”的老师的个数和所带班级数；

mysql> select tid, count(cid) as count_cid from teach2cls -> where tid in ( -> select tid from teacher -> where tname like ‘李%‘) -> group by tid;+-----+-----------+| tid | count_cid |+-----+-----------+| 2 | 2 |+-----+-----------+1 row in set (0.00 sec)mysql> select teacher.tid, teacher.tname,t1.count_cid from teacher left join( -> select tid, count(cid) as count_cid from teach2cls -> where tid in ( -> select tid from teacher -> where tname like ‘李%‘) -> group by tid)as t1 -> on teacher.tid = t1.tid -> where teacher.tname like ‘李%‘;+-----+--------+-----------+| tid | tname | count_cid |+-----+--------+-----------+| 2 | 李四 | 2 |+-----+--------+-----------+1 row in set (0.00 sec)

10、查询班级数小于5的年级id和年级名；

mysql> select gid, gname from class_grade -> where gid in( -> select grade_id from class -> group by grade_id -> having count(class.grade_id) < 5 -> );+-----+-----------+| gid | gname |+-----+-----------+| 1 | 一年级 || 3 | 三年级 || 2 | 二年级 |+-----+-----------+3 rows in set (0.00 sec)

11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下；

mysql> select class.cid, class.caption, class_grade.gname,case -> when class_grade.gid between 1 and 2 then ‘低‘ -> when class_grade.gid between 3 and 4 then ‘中‘ -> when class_grade.gid between 5 and 6 then ‘高‘ -> else 0 -> end as ‘年级级别‘ from class, class_grade -> where class.grade_id = class_grade.gid;+-----+-----------------+-----------+--------------+| cid | caption | gname | 年级级别 |+-----+-----------------+-----------+--------------+| 1 | 一年级一班 | 一年级 | 低 || 2 | 一年级二班 | 一年级 | 低 || 3 | 一年级三班 | 一年级 | 低 || 7 | 三年级一班 | 三年级 | 中 || 8 | 三年级二班 | 三年级 | 中 || 9 | 三年级三班 | 三年级 | 中 || 4 | 二年级一班 | 二年级 | 低 || 5 | 二年级二班 | 二年级 | 低 || 6 | 二年级三班 | 二年级 | 低 |+-----+-----------------+-----------+--------------+9 rows in set (0.00 sec)

12、查询学过“张三”老师2门课以上的同学的学号、姓名；

mysql> select student_id from score -> where course_id in ( -> select course.cid from teacher, course -> where teacher.tid = course.teacher_id and teacher.tname = ‘张三‘) -> group by student_id -> having count(course_id) > 2;Empty set (0.00 sec)mysql> select sid,sname from student -> where sid in( -> select student_id from score -> where course_id in ( -> select course.cid from teacher, course -> where teacher.tid = course.teacher_id and teacher.tname = ‘张三‘) -> group by student_id -> having count(course_id) > 2 -> );Empty set (0.00 sec)

13、查询教授课程超过2门的老师的id和姓名；

mysql> select tid, tname from teacher -> where tid in ( -> select teacher_id from course -> group by teacher_id -> having count(cid) >= 2 -> );+-----+--------+| tid | tname |+-----+--------+| 1 | 张三 || 3 | 王五 |+-----+--------+2 rows in set (0.00 sec)

14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；

mysql> select distinct student_id from score -> where course_id in (1, 2);+------------+| student_id |+------------+| 1 || 2 |+------------+2 rows in set (0.05 sec)mysql> select sid, sname from student -> where sid in( -> select distinct student_id from score -> where course_id in (1, 2) -> );+-----+-----------+| sid | sname |+-----+-----------+| 1 | 乔丹 || 2 | 艾弗森 |+-----+-----------+2 rows in set (0.23 sec)

15、查询没有带过高年级的老师id和姓名；

mysql> select tid,tname from teacher -> where tid not in ( -> select tid from teach2cls -> where cid in ( -> select t1.cid from( -> select class.cid,class.caption,class_grade.gname, -> case when class_grade.gid between 1 and 2 then ‘低‘ -> when class_grade.gid between 3 and 4 then ‘中‘ -> when class_grade.gid between 5 and 6 then ‘高‘ else 0 end as grade_layer -> from class,class_grade -> where class.grade_id = class_grade.gid) -> as t1 -> where t1.grade_layer = ‘高‘) -> );+-----+-----------+| tid | tname |+-----+-----------+| 1 | 张三 || 2 | 李四 || 3 | 王五 || 4 | 老男孩 || 5 | 小女孩 |+-----+-----------+5 rows in set (0.06 sec)

16、查询学过“张三”老师所教的所有课的同学的学号、姓名；

mysql> select student_id from score -> where course_id in ( -> select cid from course -> inner join teacher on teacher.tid = course.teacher_id -> where teacher.tname = ‘张三‘);+------------+| student_id |+------------+| 1 || 1 || 2 |+------------+3 rows in set (0.00 sec)mysql> select sid, sname from student -> where sid in ( -> select student_id from score -> where course_id in ( -> select cid from course -> inner join teacher on teacher.tid = course.teacher_id -> where teacher.tname = ‘张三‘) -> );+-----+-----------+| sid | sname |+-----+-----------+| 1 | 乔丹 || 2 | 艾弗森 |+-----+-----------+2 rows in set (0.00 sec)

17、查询带过超过2个班级的老师的id和姓名；

mysql> select tid, tname from teacher -> where tid in ( -> select tid from teach2cls -> group by tid -> having count(cid) > 2 -> );+-----+-----------+| tid | tname |+-----+-----------+| 4 | 老男孩 |+-----+-----------+1 row in set (0.00 sec)

18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

mysql> select t1.student_id from ( -> select student_id, score from score -> where course_id = 2 -> group by student_id) -> as t1,( -> select student_id, score from score -> where course_id = 1 -> group by student_id) -> as t2 -> where t1.student_id = t2.student_id -> and t1.score < t2.score ;+------------+| student_id |+------------+| 1 |+------------+1 row in set (0.00 sec)mysql> select sid, sname from student -> where sid in ( -> select t1.student_id from ( -> select student_id, score from score -> where course_id = 2 -> group by student_id) -> as t1,( -> select student_id, score from score -> where course_id = 1 -> group by student_id) -> as t2 -> where t1.student_id = t2.student_id -> and t1.score < t2.score -> );+-----+--------+| sid | sname |+-----+--------+| 1 | 乔丹 |+-----+--------+1 row in set (0.00 sec)

19、查询所带班级数最多的老师id和姓名；

mysql> select tid from teach2cls -> group by tid -> having count(cid) = ( -> select count(cid) from teach2cls -> group by tid -> order by count(cid) desc limit 1);+-----+| tid |+-----+| 4 |+-----+1 row in set (0.00 sec)mysql> select tid, tname from teacher -> where tid in ( -> select tid from teach2cls -> group by tid -> having count(cid) = ( -> select count(cid) from teach2cls -> group by tid -> order by count(cid) desc limit 1) -> );+-----+-----------+| tid | tname |+-----+-----------+| 4 | 老男孩 |+-----+-----------+1 row in set (0.00 sec)mysql>

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

mysql> select sid, sname from student -> where sid in ( -> select distinct student_id from score -> where score < 60);+-----+--------+| sid | sname |+-----+--------+| 1 | 乔丹 || 4 | alex || 11 | 丹丹 || 12 | simith |+-----+--------+4 rows in set (0.00 sec)

21、查询没有学全所有课的同学的学号、姓名；

mysql> select student_id from score -> group by student_id -> having count(course_id) = ( -> select count(cid) from course);Empty set (0.00 sec)mysql> select sid, sname from student -> where sid not in ( -> select student_id from score -> group by student_id -> having count(course_id) = ( -> select count(cid) from course) -> );+-----+-----------+| sid | sname |+-----+-----------+| 1 | 乔丹 || 2 | 艾弗森 || 3 | 科比 || 4 | alex || 5 | egon || 6 | egon2 || 7 | kris || 8 | wxx || 9 | 杜拉拉 || 10 | 姗姗 || 11 | 丹丹 || 12 | simith |+-----+-----------+12 rows in set (0.00 sec)

22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

select sid, sname from studentwhere sid in (select student_id from score where course_id in (select course_id from scorewhere student_id = 1)group by student_id );

23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；

mysql> select sid,sname from student -> where sid in ( -> select student_id from score -> where course_id in ( -> SELECT course_id from score -> where student_id = 1) -> group by student_id ) -> and sid != 1;+-----+-----------+| sid | sname |+-----+-----------+| 2 | 艾弗森 || 3 | 科比 || 9 | 杜拉拉 |+-----+-----------+3 rows in set (0.00 sec)

24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；

mysql> select sid,sname from student -> where sid in ( -> select score.student_id from score,( -> select course_id from score -> where student_id = 2) as t1 -> where score.course_id = t1.course_id and score.student_id != 2 -> group by score.student_id -> having count(score.course_id) = (select count(course_id) from score -> where student_id = 2) -> );+-----+--------+| sid | sname |+-----+--------+| 1 | 乔丹 |+-----+--------+1 row in set (0.00 sec)

25、删除学习“张三”老师课的score表记录；

mysql> delete from score -> where course_id in ( -> select course.cid from course,teacher -> where course.teacher_id = teacher.tid and teacher.tname = ‘张三‘ -> );Query OK, 0 rows affected (0.17 sec)mysql> select * from score;+-----+------------+-----------+-------+| sid | student_id | course_id | score |+-----+------------+-----------+-------+| 5 | 1 | 3 | 76 || 4 | 3 | 3 | 88 || 6 | 4 | 5 | 34 || 7 | 5 | 6 | 68 || 8 | 6 | 6 | 93 || 9 | 7 | 7 | 77 || 10 | 8 | 6 | 86 || 11 | 9 | 3 | 90 || 12 | 10 | 4 | 100 || 13 | 11 | 5 | 52 || 14 | 12 | 7 | 27 |+-----+------------+-----------+-------+11 rows in set (0.00 sec)

26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；

mysql> select sid from student -> where sid not in ( -> select student_id from score where course_id = 2);+-----+| sid |+-----+| 1 || 2 || 3 || 4 || 5 || 6 || 7 || 8 || 9 || 10 || 11 |+-----+11 rows in set (0.00 sec)mysql> select avg(score) as avg_score from score -> where course_id=2;+-----------+| avg_score |+-----------+| 27.0000 |+-----------+1 row in set (0.00 sec)mysql> insert into score(student_id,course_id,score) -> select t1.sid,2,t2.avg_score from ( -> select sid from student -> where sid not in ( -> select student_id from score where course_id = 2) -> )as t1,( -> select avg(score) as avg_score from score -> where course_id=2) -> as t2;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from score;+-----+------------+-----------+-------+| sid | student_id | course_id | score |+-----+------------+-----------+-------+| 15 | 1 | 2 | 27 || 5 | 1 | 3 | 76 || 16 | 2 | 2 | 27 || 17 | 3 | 2 | 27 || 4 | 3 | 3 | 88 || 18 | 4 | 2 | 27 || 6 | 4 | 5 | 34 || 19 | 5 | 2 | 27 || 7 | 5 | 6 | 68 || 20 | 6 | 2 | 27 || 8 | 6 | 6 | 93 || 21 | 7 | 2 | 27 || 9 | 7 | 7 | 77 || 22 | 8 | 2 | 27 || 10 | 8 | 6 | 86 || 23 | 9 | 2 | 27 || 11 | 9 | 3 | 90 || 24 | 10 | 2 | 27 || 12 | 10 | 4 | 100 || 25 | 11 | 2 | 27 || 13 | 11 | 5 | 52 || 14 | 12 | 2 | 27 |+-----+------------+-----------+-------+22 rows in set (0.00 sec)

27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

mysql> select concat("学生ID: ",t1.sid," ", "课程ID: ",t2.course_id," ","平均成绩: ",avg(t2.score)) from student -> as t1 -> RIGHT JOIN score as t2 -> on t1.sid = t2.student_id -> GROUP BY student_id;+--------------------------------------------------------------------------------------------------+| concat("学生ID: ",t1.sid," ", "课程ID: ",t2.course_id," ","平均成绩: ",avg(t2.score)) |+--------------------------------------------------------------------------------------------------+| 学生ID: 1 课程ID: 2 平均成绩: 51.5000 || 学生ID: 2 课程ID: 2 平均成绩: 27.0000 || 学生ID: 3 课程ID: 2 平均成绩: 57.5000 || 学生ID: 4 课程ID: 2 平均成绩: 30.5000 || 学生ID: 5 课程ID: 2 平均成绩: 47.5000 || 学生ID: 6 课程ID: 2 平均成绩: 60.0000 || 学生ID: 7 课程ID: 2 平均成绩: 52.0000 || 学生ID: 8 课程ID: 2 平均成绩: 56.5000 || 学生ID: 9 课程ID: 2 平均成绩: 58.5000 || 学生ID: 10 课程ID: 2 平均成绩: 63.5000 || 学生ID: 11 课程ID: 2 平均成绩: 39.5000 || 学生ID: 12 课程ID: 2 平均成绩: 27.0000 |+--------------------------------------------------------------------------------------------------+12 rows in set (0.00 sec)

28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

mysql> select course.cid, max(score.score) as max_score, min(score.score) as min_score from course -> left join score on course.cid = score.course_id -> group by score.course_id;+-----+-----------+-----------+| cid | max_score | min_score |+-----+-----------+-----------+| 1 | NULL | NULL || 2 | 27 | 27 || 3 | 90 | 76 || 4 | 100 | 100 || 5 | 52 | 34 || 6 | 93 | 68 || 7 | 77 | 77 |+-----+-----------+-----------+7 rows in set (0.00 sec)

29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

mysql> select course_id, avg(score) as avg_score, -> sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as percent -> from score group by course_id -> order by avg(score) asc, percent desc;+-----------+-----------+----------+| course_id | avg_score | percent |+-----------+-----------+----------+| 2 | 27.0000 | 0.0000 || 5 | 43.0000 | 0.0000 || 7 | 77.0000 | 100.0000 || 6 | 82.3333 | 100.0000 || 3 | 84.6667 | 100.0000 || 4 | 100.0000 | 100.0000 |+-----------+-----------+----------+6 rows in set (0.00 sec)

30、课程平均分从高到低显示（现实任课老师）；

mysql> select course_id, avg(score) as avg_score from score -> group by course_id -> order by avg_score desc ;+-----------+-----------+| course_id | avg_score |+-----------+-----------+| 4 | 100.0000 || 3 | 84.6667 || 6 | 82.3333 || 7 | 77.0000 || 5 | 43.0000 || 2 | 27.0000 |+-----------+-----------+6 rows in set (0.00 sec)mysql> select t1.course_id,t1.avg_score,teacher.tname from course,teacher,( -> select course_id, avg(score) as avg_score from score -> group by course_id -> order by avg_score desc) -> as t1 -> where course.cid = t1.course_id and course.teacher_id = teacher.tid -> order by t1.avg_score desc;+-----------+-----------+-----------+| course_id | avg_score | tname |+-----------+-----------+-----------+| 4 | 100.0000 | 王五 || 3 | 84.6667 | 李四 || 6 | 82.3333 | 老男孩 || 7 | 77.0000 | 小女孩 || 5 | 43.0000 | 王五 || 2 | 27.0000 | 张三 |+-----------+-----------+-----------+6 rows in set (0.00 sec)

31、查询各科成绩前三名的记录(不考虑成绩并列情况)

mysql> select score.sid,score.student_id,score.course_id,score.score, -> t1.first_score,t1.second_score,t1.third_score from score -> inner join( -> select s1.sid,( -> select score from score as s2 where s1.course_id = s2.course_id order by score desc limit 0,1) -> as first_score,( -> select score from score as s3 where s1.course_id = s3.course_id order by score desc limit 1,1) -> as second_score,( -> select score from score as s4 where s1.course_id = s4.course_id order by score desc limit 2,1) -> as third_score from score as s1) -> as t1 on score.sid = t1.sid -> where score.score in (t1.first_score,t1.second_score,t1.third_score -> );+-----+------------+-----------+-------+-------------+--------------+-------------+| sid | student_id | course_id | score | first_score | second_score | third_score |+-----+------------+-----------+-------+-------------+--------------+-------------+| 4 | 3 | 3 | 88 | 90 | 88 | 76 || 5 | 1 | 3 | 76 | 90 | 88 | 76 || 6 | 4 | 5 | 34 | 52 | 34 | NULL || 7 | 5 | 6 | 68 | 93 | 86 | 68 || 8 | 6 | 6 | 93 | 93 | 86 | 68 || 9 | 7 | 7 | 77 | 77 | NULL | NULL || 10 | 8 | 6 | 86 | 93 | 86 | 68 || 11 | 9 | 3 | 90 | 90 | 88 | 76 || 12 | 10 | 4 | 100 | 100 | NULL | NULL || 13 | 11 | 5 | 52 | 52 | 34 | NULL || 14 | 12 | 2 | 27 | 27 | 27 | 27 || 15 | 1 | 2 | 27 | 27 | 27 | 27 || 16 | 2 | 2 | 27 | 27 | 27 | 27 || 17 | 3 | 2 | 27 | 27 | 27 | 27 || 18 | 4 | 2 | 27 | 27 | 27 | 27 || 19 | 5 | 2 | 27 | 27 | 27 | 27 || 20 | 6 | 2 | 27 | 27 | 27 | 27 || 21 | 7 | 2 | 27 | 27 | 27 | 27 || 22 | 8 | 2 | 27 | 27 | 27 | 27 || 23 | 9 | 2 | 27 | 27 | 27 | 27 || 24 | 10 | 2 | 27 | 27 | 27 | 27 || 25 | 11 | 2 | 27 | 27 | 27 | 27 |+-----+------------+-----------+-------+-------------+--------------+-------------+22 rows in set (0.01 sec)

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

mysql> select cname "课程",count(student_id) "选修学生总数" from score LEFT JOIN -> course on score.course_id = course.cid -> GROUP BY course_id;+--------+--------------------+| 课程 | 选修学生总数 |+--------+--------------------+| 体育 | 12 || 物理 | 3 || 化学 | 1 || 英语 | 2 || python | 3 || linux | 1 |+--------+--------------------+6 rows in set (0.00 sec)

33、查询选修了2门以上课程的全部学生的学号和姓名；

mysql> select sid, sname from student -> where sid in ( -> select student_id from score -> group by student_id -> having count(course_id) > 2 -> );Empty set (0.00 sec)

34、查询男生、女生的人数，按倒序排列；

mysql> SELECT sex "性别", count(sid) "总人数" FROM student -> GROUP BY sex -> ORDER BY count(sid) DESC;+--------+-----------+| 性别 | 总人数 |+--------+-----------+| 女 | 7 || 男 | 5 |+--------+-----------+2 rows in set (0.00 sec)

35、查询姓“张”的学生名单；

mysql> select student.sid ,student.sname, student.sex, class.caption from student -> inner join class on student.class_id = class.cid -> where student.sname like ‘张%‘;Empty set (0.00 sec)

36、查询同名同姓学生名单，并统计同名人数；

mysql> select sname, count(sname) as count_sname from student -> group by sname -> having count(sname) > 1;Empty set (0.00 sec)

37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

select course_id,avg(score) as avg_score from score group by course_idorder by avg_score,course_id desc;

38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

mysql> select score.student_id, score.score from score -> inner join course on score.course_id = course.cid -> where course.cname = ‘英语‘ and score.score < 60 -> ;+------------+-------+| student_id | score |+------------+-------+| 4 | 34 || 11 | 52 |+------------+-------+2 rows in set (0.00 sec)mysql> select student.sname, t1.score from student -> inner join ( -> select score.student_id, score.score from score -> inner join course on score.course_id = course.cid -> where course.cname = ‘英语‘ and score.score < 60) -> as t1 -> on student.sid = t1.student_id;+--------+-------+| sname | score |+--------+-------+| alex | 34 || 丹丹 | 52 |+--------+-------+2 rows in set (0.00 sec)

39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；

mysql> select sid, sname from student -> where sid in ( -> select student_id from score where course_id = 3 and score > 80 -> );+-----+-----------+| sid | sname |+-----+-----------+| 3 | 科比 || 9 | 杜拉拉 |+-----+-----------+2 rows in set (0.00 sec)

40、求选修了课程的学生人数

mysql> SELECT course_id, count(student_id) as count_student from score -> group by course_id;+-----------+---------------+| course_id | count_student |+-----------+---------------+| 2 | 12 || 3 | 3 || 4 | 1 || 5 | 2 || 6 | 3 || 7 | 1 |+-----------+---------------+6 rows in set (0.00 sec)

41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；

mysql> select score.student_id,score.course_id,score.score,t1.max_score,t1.min_score from score,( -> select course_id,max(score) as max_score ,min(score) as min_score from score -> where course_id in ( -> select cid from course -> inner join teacher on course.teacher_id = teacher.tid -> where teacher.tname = ‘王五‘) -> group by course_id) as t1 -> where score.course_id = t1.course_id and score.score in (max_score,min_score) -> ;+------------+-----------+-------+-----------+-----------+| student_id | course_id | score | max_score | min_score |+------------+-----------+-------+-----------+-----------+| 10 | 4 | 100 | 100 | 100 || 4 | 5 | 34 | 52 | 34 || 11 | 5 | 52 | 52 | 34 |+------------+-----------+-------+-----------+-----------+3 rows in set (0.00 sec)mysql> select student.sname,t2.course_id,t2.score,t2.max_score,t2.min_score from student -> inner join ( -> select score.student_id,score.course_id,score.score,t1.max_score,t1.min_score from score,( -> select course_id,max(score) as max_score ,min(score) as min_score from score -> where course_id in ( -> select cid from course -> inner join teacher on course.teacher_id = teacher.tid -> where teacher.tname = ‘王五‘) -> group by course_id) as t1 -> where score.course_id = t1.course_id and score.score in (max_score,min_score) -> )as t2 -> on student.sid = t2.student_id;+--------+-----------+-------+-----------+-----------+| sname | course_id | score | max_score | min_score |+--------+-----------+-------+-----------+-----------+| 姗姗 | 4 | 100 | 100 | 100 || alex | 5 | 34 | 52 | 34 || 丹丹 | 5 | 52 | 52 | 34 |+--------+-----------+-------+-----------+-----------+3 rows in set (0.00 sec)

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

mysql> select course.cname,ifnull(t1.count_student,0) as count_student from course -> left join ( -> select course_id, count(student_id) as count_student from score -> GROUP BY course_id) -> as t1 -> on course.cid = t1.course_id;+--------+---------------+| cname | count_student |+--------+---------------+| 生物 | 0 || 体育 | 12 || 物理 | 3 || 化学 | 1 || 英语 | 2 || python | 3 || linux | 1 |+--------+---------------+7 rows in set (0.00 sec)

43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

mysql> select distinct s1.student_id,s2.student_id,s1.course_id as s1_course_id,s2.course_id -> as s2_course_id,s1.score,s2.score from score -> as s1, score as s2 -> where s1.course_id != s2.course_id and s1.score = s2.score;Empty set (0.00 sec)

44、查询每门课程成绩最好的前两名学生id和姓名；

mysql> select student.sid,student.sname,t2.course_id,t2.score,t2.first_score,t2.second_score from student -> inner join ( -> select score.student_id,score.course_id,score.score,t1.first_score,t1.second_score from score -> inner join ( -> select s1.sid,( -> select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,( -> select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score -> from score as s1) -> as t1 on score.sid = t1.sid -> where score.score in (t1.first_score,t1.second_score) -> ) as t2 -> on student.sid = t2.student_id;+-----+-----------+-----------+-------+-------------+--------------+| sid | sname | course_id | score | first_score | second_score |+-----+-----------+-----------+-------+-------------+--------------+| 1 | 乔丹 | 2 | 27 | 27 | 27 || 2 | 艾弗森 | 2 | 27 | 27 | 27 || 3 | 科比 | 3 | 88 | 90 | 88 || 3 | 科比 | 2 | 27 | 27 | 27 || 4 | alex | 5 | 34 | 52 | 34 || 4 | alex | 2 | 27 | 27 | 27 || 5 | egon | 2 | 27 | 27 | 27 || 6 | egon2 | 6 | 93 | 93 | 86 || 6 | egon2 | 2 | 27 | 27 | 27 || 7 | kris | 7 | 77 | 77 | NULL || 7 | kris | 2 | 27 | 27 | 27 || 8 | wxx | 2 | 27 | 27 | 27 || 8 | wxx | 6 | 86 | 93 | 86 || 9 | 杜拉拉 | 2 | 27 | 27 | 27 || 9 | 杜拉拉 | 3 | 90 | 90 | 88 || 10 | 姗姗 | 2 | 27 | 27 | 27 || 10 | 姗姗 | 4 | 100 | 100 | NULL || 11 | 丹丹 | 2 | 27 | 27 | 27 || 11 | 丹丹 | 5 | 52 | 52 | 34 || 12 | simith | 2 | 27 | 27 | 27 |+-----+-----------+-----------+-------+-------------+--------------+20 rows in set (0.00 sec)

45、检索至少选修两门课程的学生学号；

mysql> select student_id from score -> GROUP BY student_id -> having count(course_id) >= 2;+------------+| student_id |+------------+| 1 || 3 || 4 || 5 || 6 || 7 || 8 || 9 || 10 || 11 |+------------+10 rows in set (0.00 sec)

46、查询没有学生选修的课程的课程号和课程名；

mysql> SELECT cid, cname from course -> where cid not in ( -> SELECT course_id from score -> GROUP BY course_id);+-----+--------+| cid | cname |+-----+--------+| 1 | 生物 |+-----+--------+1 row in set (0.00 sec)

47、查询没带过任何班级的老师id和姓名；

mysql> SELECT tid, tname from teacher -> where tid not in( -> select tid from teach2cls -> GROUP BY tid);Empty set (0.00 sec)

48、查询有两门以上课程超过80分的学生id及其平均成绩；

mysql> select student_id, avg(score) as avg_score from score -> where student_id in ( -> select student_id from score -> where score > 80 -> group by student_id -> having count(course_id) > 2) -> group by student_id;Empty set (0.00 sec)

49、检索“3”课程分数小于60，按分数降序排列的同学学号；

mysql> select student_id, score from score -> where course_id = 3 and score < 60 -> ORDER BY score DESC;Empty set (0.00 sec)

50、删除编号为“2”的同学的“1”课程的成绩；

mysql> delete from score where sid = ( -> select t1.sid from( -> select sid from score -> where student_id = 2 and course_id = 1) as t1 -> );Query OK, 0 rows affected (0.00 sec)

51、查询同时选修了物理课和生物课的学生id和姓名；

mysql> select sid,sname from student -> where sid in ( -> select student_id from score -> where course_id in ( -> select cid from course where course.cname in (‘物理‘,‘生物‘)) -> GROUP BY student_id -> having count(course_id) = 2);Empty set (0.00 sec)