mysql练习
MySQL
一、表关系
请创建如下表，并创建相关约束
| 班级表：class | 学生表：student | ||||||
| cid | caption | grade_id | sid | sname | gender | class_id | |
| 1 | 一年一班 | 1 | 1 | 乔丹 | 女 | 1 | |
| 2 | 二年一班 | 2 | 2 | 艾弗森 | 女 | 1 | |
| 3 | 三年二班 | 3 | 3 | 科比 | 男 | 2 | |
| 老师表：teacher | 课程表：course | ||||||
| tid | tname | cid | cname | teacher_id | |||
| 1 | 张三 | 1 | 生物 | 1 | |||
| 2 | 李四 | 2 | 体育 | 1 | |||
| 3 | 王五 | 3 | 物理 | 2 | |||
| 成绩表：score | 年级表：class_grade | ||||||
| sid | student_id | course_id | score | gid | gname | ||
| 1 | 1 | 1 | 60 | 1 | 一年级 | ||
| 2 | 1 | 2 | 59 | 2 | 二年级 | ||
| 3 | 2 | 2 | 99 | 3 | 三年级 | ||
| 班级任职表：teach2cls | |||||||
| tcid | tid | cid | |||||
| 1 | 1 | 1 | |||||
| 2 | 1 | 2 | |||||
| 3 | 2 | 1 | |||||
| 4 | 3 | 2 |
class_grade 一对多 >> class
class 一对多 >> student
teacher 一对多 >> course
student 一对多 >> score
course 一对多 >> score
teacher 多对多 >> class
一对多是在多的表中建立关联字段；一对一是在任意一张表中建立关联字段+unique；多对多是加id和两个关联字段，第三张表。
可参考： http://www.cnblogs.com/wupeiqi/articles/5748496.html
C:\Users\Administrator>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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 changed mysql> 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)); #class_grade表与class表是一对多的关系；在多的表中建立关联关系； 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)); #class和student表也是一对多的关系，在多的表中建立关联字段； 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)); #teacher和course是一对多关系，在多的表中建立关联字段； Query OK, 0 rows affected (0.53 sec) mysql> 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) #student和score是一对多的关系； -> on delete cascade -> on update cascade, -> foreign key(course_id) references course(cid) #course和score也是一对多的关系； -> on delete cascade -> on update cascade -> ); Query OK, 0 rows affected (1.11 sec) mysql> 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) #teacher和class是多对多关系，两个互相一对多； -> 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)
二、插入数据
（根据下面题目可自行插入数据，建议数据量应该大一些，使用navicat插入数据简便快捷，但要会使用在cmd终端下的操作）
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)
class（cid caption grade_id）； class_grade（gid gname）
student（sid sname sex class_id）；teacher（tid tname）
course（cid cname teacher_id）； score（sid student_id course_id score）
teach2cls（tcid tid cid）
三、操作表
1、自行创建测试数据；见上
2、查询学生总人数；
mysql> select count(sid) as count_student from student; +---------------+ | count_student | +---------------+ | 12 | +---------------+ 1 row in set (0.08 sec)
3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
查询 student的sid和sname
两个条件：从course表中查出“生物”和“物理”课程；从score表中查出score > 60的
mysql> select sid,sname from student -> where sid in( #带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、查询每个年级的班级数，取出班级数最多的前三个年级；
取出年级
内层select语句不能带有limit语句
mysql> select grade_id,count(grade_id) from class; +----------+-----------------+ | grade_id | count(grade_id) | +----------+-----------------+ | 1 | 9 | +----------+-----------------+ 1 row in set (0.00 sec) mysql> select grade_id,count(cid) as count_cid from class -> group by grade_id; +----------+-----------+ | grade_id | count_cid | +----------+-----------+ | 1 | 3 | | 2 | 3 | | 3 | 3 | +----------+-----------+ 3 rows in set (0.08 sec) mysql> select grade_id,count(cid) as count_cid from class -> group by grade_id -> order by count_cid desc -> limit 3; +----------+-----------+ | grade_id | count_cid | +----------+-----------+ | 1 | 3 | | 2 | 3 | | 3 | 3 | +----------+-----------+ 3 rows in set (0.07 sec)
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、查询每个年级的学生人数；
student的class_id和class的cid
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为高年级)，示例结果如下；
| 班级id | 班级名称 | 年级 | 年级级别 |
|---|---|---|---|
| 1 | 一年一班 | 一年级 | 低 |
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 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 );
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: 0 mysql> 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_id order 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)
