Mysql 查询练习
---创建班级表
create table class(
cid int auto_increment primary key,
caption varchar(20)
)engine=innodb default charset=utf8; ---创建学生表
create table student(
sid int auto_increment primary key,
sname varchar(20),
gender varchar(10) default '男',
class_id int
)engine=innodb default charset=utf8; insert into student(sname,gender,class_id) values('范冰冰','女',''); ---创建老师表
create table teacher(
tid int auto_increment primary key,
tname varchar(20)
)engine=innodb default charset=utf8; mysql> insert into teacher(tname) values('孙子');
mysql> update teacher set tname='李时珍' where tid=3; ---创建课程表
create table course(
cid int auto_increment primary key,
cname varchar(20),
teache_id int
)engine=innodb default charset=utf8; mysql> insert into course(cname) value('本草纲目'); ---创建学生成绩表
create table score(
sid int auto_increment primary key,
student_id int,
corse_id int,
number int
)engine=innodb default charset=utf8; insert into score(student_id,corse_id,number) values(1,1,90);
mysql> insert into score(student_id,corse_id,number) values(1,3,58); update score set corse_id=2 where student_id=3; ---设置主外键约束
---学生表中class_id 关联class表中的cid
alter table student add constraint fk_s_c foreign key student(class_id) references class(cid);
1、自行创建测试数据
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
---先查出所有的生物课 学生成绩
mysql> select * from score where corse_id='';
+-----+------------+----------+--------+
| sid | student_id | corse_id | number |
+-----+------------+----------+--------+
| 1 | 1 | 1 | 90 |
| 7 | 19 | 1 | 100 |
| 10 | 18 | 1 | 98 |
| 13 | 17 | 1 | 90 |
| 16 | 16 | 1 | 70 |
| 19 | 15 | 1 | 98 |
| 22 | 14 | 1 | 76 |
| 25 | 13 | 1 | 96 |
| 28 | 12 | 1 | 74 |
| 31 | 11 | 1 | 90 |
| 34 | 10 | 1 | 83 |
| 37 | 9 | 1 | 77 |
| 41 | 8 | 1 | 96 |
| 45 | 7 | 1 | 88 |
| 48 | 6 | 1 | 60 |
| 51 | 5 | 1 | 57 |
| 54 | 4 | 1 | 86 |
| 57 | 3 | 1 | 79 |
| 60 | 2 | 1 | 43 |
+-----+------------+----------+--------+
19 rows in set (0.00 sec)
---查处所有学生物理课 学生成绩
select * from score where corse_id=''; ------查询“生物”课程比“物理”课程成绩高的所有学生的学号:
select a.student_id as '学号', a.number as '生物成绩',b.number as '物理成绩'
from (select * from score where corse_id='') a
left join (select * from score where corse_id='') b
on a.student_id=b.student_id
where a.number>b.number; +--------+--------------+--------------+
| 学号 | 生物成绩 | 物理成绩 |
+--------+--------------+--------------+
| 1 | 90 | 58 |
| 18 | 98 | 96 |
| 17 | 90 | 80 |
| 15 | 98 | 86 |
| 13 | 96 | 72 |
| 11 | 90 | 76 |
| 10 | 83 | 81 |
| 9 | 77 | 66 |
| 8 | 96 | 84 |
| 3 | 79 | 53 |
+--------+--------------+--------------+
10 rows in set (0.00 sec) --------------------------------------------------
select score.student_id as '学号',
score.corse_id as '课程编号',course.cname as '课程名称',
score.number as '成绩'
from score,course
where corse_id=1 and score.corse_id = course.cid; ---------------------------------------------------
select
score.corse_id as '课程编号',course.cname as '课程名称'
from score,course
where score.corse_id = course.cid
group by corse_id; ----为表起别名：
select
a.corse_id as '课程编号',b.cname as '课程名称'
from score a,course b
where a.corse_id = b.cid
group by corse_id; +--------------+--------------+
| 课程编号 | 课程名称 |
+--------------+--------------+
| 1 | 生物 |
| 2 | 体育 |
| 3 | 物理 |
+--------------+--------------+
---------------------------------------------------
3、查询平均成绩大于60分的同学的学号和平均成绩；
---由于成绩录入的较高，这里查询平均成绩大于80分的同学的学号和平均成绩；
select student_id as '学号',sum(number) as '总成绩',avg(number) as '平均成绩' from score where avg(number)>80 group by student_id; select student_id as '学号',sum(number) as '总成绩',avg(number) as '平均成绩'
from score group by student_id; ---加having条件子句
select student_id as '学号',sum(number) as '总成绩',avg(number) as '平均成绩'
from score group by student_id having avg(number)>80 ;
+--------+-----------+--------------+
| 学号 | 总成绩 | 平均成绩 |
+--------+-----------+--------------+
| 4 | 99 | 99.0000 |
| 10 | 83 | 83.0000 |
| 12 | 164 | 82.0000 |
| 13 | 92 | 92.0000 |
| 14 | 165 | 82.5000 |
| 15 | 184 | 92.0000 |
| 16 | 166 | 83.0000 |
| 17 | 175 | 87.5000 |
| 19 | 200 | 100.0000 |
+--------+-----------+--------------+
9 rows in set (0.27 sec)
4、查询所有同学的学号、姓名、选课数、总成绩；
---先从成绩表中查询相关信息：
select student_id as '学号',count(*) as '选课数',sum(number) as '总成绩'
from score group by student_id;
+--------+-----------+-----------+
| 学号 | 选课数 | 总成绩 |
+--------+-----------+-----------+
| 1 | 2 | 148 |
| 2 | 2 | 146 |
| 4 | 1 | 99 |
| 5 | 2 | 133 |
| 8 | 2 | 149 |
| 9 | 2 | 152 |
| 10 | 1 | 83 |
| 12 | 2 | 164 |
| 13 | 1 | 92 |
| 14 | 2 | 165 |
| 15 | 2 | 184 |
| 16 | 2 | 166 |
| 17 | 2 | 175 |
| 19 | 2 | 200 |
+--------+-----------+-----------+
14 rows in set (0.00 sec)
---关联学生表查出学生姓名： select a.student_id,a.count(*),a.sum(number),b.sid
from score a, student b
group by a.student_id
having a.student_id=b.sid; ---OK
select student_id as '学号',student.sname as '学生姓名',
count(*) as '选课数',sum(number) as '总成绩'
from score left join student
on score.student_id=student.sid
group by student_id; +--------+--------------+-----------+-----------+
| 学号 | 学生姓名 | 选课数 | 总成绩 |
+--------+--------------+-----------+-----------+
| 1 | 王小虎 | 3 | 228 |
| 2 | 王小龙 | 2 | 146 |
| 4 | 赵小四 | 1 | 99 |
| 5 | 张小丽 | 2 | 133 |
| 8 | 刘德华 | 3 | 189 |
| 9 | 梁朝伟 | 2 | 152 |
| 10 | 张学友 | 1 | 83 |
| 12 | 张曼玉 | 2 | 164 |
| 13 | 章子怡 | 1 | 92 |
| 14 | 李晨 | 3 | 247 |
| 15 | 王祖蓝 | 2 | 184 |
| 16 | 陈赫 | 2 | 166 |
| 17 | 邓超 | 2 | 175 |
| 19 | 范冰冰 | 2 | 200 |
+--------+--------------+-----------+-----------+
14 rows in set (0.00 sec)
5、查询姓“李”的老师的个数；
mysql> select count(*) as '姓李 老师人数' from teacher where tname like '李%';
+---------------------+
| 姓李 老师人数 |
+---------------------+
| 6 |
+---------------------+
1 row in set (0.00 sec)
6、查询没学过“司马迁”老师课的同学的学号、姓名；
----先从成绩表中查出没有学过‘司马迁’老师课程的学生学号。
mysql> select student_id from score where corse_id!=2 group by student_id;
+------------+
| student_id |
+------------+
| 2 |
| 4 |
| 5 |
| 9 |
| 12 |
| 14 |
| 15 |
| 16 |
| 19 |
+------------+
9 rows in set (0.00 sec) -------OK-------------------------------------------------
select a.student_id as '学号', b.sname as '学生姓名'
from score a ,student b
where a.corse_id!=2 and a.student_id=b.sid
group by a.student_id; +--------+--------------+
| 学号 | 学生姓名 |
+--------+--------------+
| 2 | 王小龙 |
| 4 | 赵小四 |
| 5 | 张小丽 |
| 9 | 梁朝伟 |
| 12 | 张曼玉 |
| 14 | 李晨 |
| 15 | 王祖蓝 |
| 16 | 陈赫 |
| 19 | 范冰冰 |
+--------+--------------+
9 rows in set (0.00 sec)
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select student_id from score where corse_id=1
union
select student_id from score where corse_id=2; --------OK---------------------------------------
select student_id,student.sname
from score left join student on score.student_id=student.sid
where corse_id=1
union
select student_id,student.sname
from score left join student on score.student_id=student.sid
where corse_id=2;
+------------+-----------+
| student_id | sname |
+------------+-----------+
| 19 | 范冰冰 |
| 15 | 王祖蓝 |
| 14 | 李晨 |
| 1 | 王小虎 |
| 8 | 刘德华 |
| 17 | 邓超 |
| 16 | 陈赫 |
| 13 | 章子怡 |
| 12 | 张曼玉 |
| 10 | 张学友 |
| 9 | 梁朝伟 |
| 5 | 张小丽 |
| 2 | 王小龙 |
+------------+-----------+
13 rows in set (0.00 sec)
8、查询学过“李时珍”老师所教的所有课的同学的学号、姓名；
---首先：李时珍 老师教：生物 和 物理
---先从成绩表中查出学过‘李时珍’老师课的同学的信息；
mysql> select * from score where corse_id=3 or corse_id=1;
+-----+------------+----------+--------+
| sid | student_id | corse_id | number |
+-----+------------+----------+--------+
| 7 | 19 | 1 | 100 |
| 18 | 16 | 3 | 88 |
| 19 | 15 | 1 | 98 |
| 21 | 15 | 3 | 86 |
| 22 | 14 | 1 | 76 |
| 24 | 14 | 3 | 89 |
| 30 | 12 | 3 | 95 |
| 39 | 9 | 3 | 66 |
| 53 | 5 | 3 | 76 |
| 56 | 4 | 3 | 99 |
| 62 | 2 | 3 | 93 |
+-----+------------+----------+--------+
11 rows in set (0.00 sec) --OK-联合学生表，查找学生姓名
select a.student_id, b.sname
from score a,student b
where (a.corse_id=3 or a.corse_id=1)
and a.student_id = b.sid
group by student_id; +------------+-----------+
| student_id | sname |
+------------+-----------+
| 2 | 王小龙 |
| 4 | 赵小四 |
| 5 | 张小丽 |
| 9 | 梁朝伟 |
| 12 | 张曼玉 |
| 14 | 李晨 |
| 15 | 王祖蓝 |
| 16 | 陈赫 |
| 19 | 范冰冰 |
+------------+-----------+
9 rows in set (0.00 sec)
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select student_id as '学号',student.sname as '学生姓名'
from score left join student
on score.student_id=student.sid where corse_id=1
group by student_id
having ;
10、查询所有课程成绩小于60分的同学的学号、姓名；
select student_id as '学号',student.sname as '学生姓名'
from score left join student
on score.student_id=student.sid where corse_id=1
group by student_id
having ; select student_id as '学号',student.sname as '学生姓名'
from score left join student
on score.student_id=student.sid
where number<60; ---OK---
select student_id as '学号',student.sname as '学生姓名',
course.cname as '课程名称',score.number as '成绩 (小于60分)'
from score left join (student,course)
on score.student_id=student.sid and score.corse_id=course.cid
where number<60; +--------+--------------+--------------+----------------------+
| 学号 | 学生姓名 | 课程名称 | 成绩 (小于60分) |
+--------+--------------+--------------+----------------------+
| 5 | 张小丽 | 体育 | 57 |
| 2 | 王小龙 | 体育 | 53 |
| 1 | 王小虎 | 体育 | 58 |
| 8 | 刘德华 | 生物 | 40 |
+--------+--------------+--------------+----------------------+
4 rows in set (0.00 sec) --课上老师要求测试练习
--成绩表score 关联学生表student 和 课程表course select score.sid,student_id as '学号',student.sname as '学生姓名',corse_id as '课程ID',
course.cname as '课程名称',score.number as '成绩'
from score left join (student,course)
on score.student_id=student.sid and score.corse_id=course.cid
order by score.sid;
11、查询没有学全所有课的同学的学号、姓名；
---思路：首先从成绩表中查出：每位学生各自所学的课程总数。
select student_id as '学号',student.sname as '学生姓名',count(student_id) as '所学课程总数'
from score left join student
on score.student_id=student.sid
group by student_id;
+--------+--------------+--------------------+
| 学号 | 学生姓名 | 所学课程总数 |
+--------+--------------+--------------------+
| 1 | 王小虎 | 3 |
| 2 | 王小龙 | 2 |
| 4 | 赵小四 | 1 |
| 5 | 张小丽 | 2 |
| 8 | 刘德华 | 3 |
| 9 | 梁朝伟 | 2 |
| 10 | 张学友 | 1 |
| 12 | 张曼玉 | 2 |
| 13 | 章子怡 | 1 |
| 14 | 李晨 | 3 |
| 15 | 王祖蓝 | 2 |
| 16 | 陈赫 | 2 |
| 17 | 邓超 | 2 |
| 19 | 范冰冰 | 2 |
+--------+--------------+--------------------+
14 rows in set (0.00 sec) ---然后，增加判断条件，筛选出‘所学课程总数’小于3的同学。
---最后得到：没有学全所有课的同学的学号、姓名。 select student_id as '学号',student.sname as '学生姓名',count(student_id) as '所学课程总数'
from score left join student
on score.student_id=student.sid
group by student_id
having count(student_id)<3; +--------+--------------+--------------------+
| 学号 | 学生姓名 | 所学课程总数 |
+--------+--------------+--------------------+
| 2 | 王小龙 | 2 |
| 4 | 赵小四 | 1 |
| 5 | 张小丽 | 2 |
| 9 | 梁朝伟 | 2 |
| 10 | 张学友 | 1 |
| 12 | 张曼玉 | 2 |
| 13 | 章子怡 | 1 |
| 15 | 王祖蓝 | 2 |
| 16 | 陈赫 | 2 |
| 17 | 邓超 | 2 |
| 19 | 范冰冰 | 2 |
+--------+--------------+--------------------+
11 rows in set (0.26 sec)
12、查询至少有一门课与学号为“001”的同学所学的课程 相同的同学的学号和姓名；
---思路： 先查询了解一下【学号为“001”的同学所学的课程】
select student_id as '学号',student.sname as '学生姓名',corse_id
from score left join student
on score.student_id=student.sid
where student_id=1;
+--------+--------------+----------+
| 学号 | 学生姓名 | corse_id |
+--------+--------------+----------+
| 1 | 王小虎 | 3 |
| 1 | 王小虎 | 2 |
| 1 | 王小虎 | 1 |
+--------+--------------+----------+
3 rows in set (0.00 sec) ---然后，我们用如下类似语法：select * from 表 where id in (select nid from 表);
上边的语句改成如下语句 当作in后边的查询条件：
select corse_id from score where student_id=1; ---继续 where条件中：student_id!=1是除了自身的意思。
select student_id as '学号',student.sname as '学生姓名',corse_id
from score left join student
on score.student_id=student.sid
where corse_id in(select corse_id from score where student_id=1) and student_id!=1
order by student_id;
+--------+--------------+----------+
| 学号 | 学生姓名 | corse_id |
+--------+--------------+----------+
| 2 | 王小龙 | 2 |
| 2 | 王小龙 | 3 |
| 4 | 赵小四 | 3 |
| 5 | 张小丽 | 3 |
| 5 | 张小丽 | 2 |
| 8 | 刘德华 | 3 |
| 9 | 梁朝伟 | 2 |
| 17 | 邓超 | 1 |
| 19 | 范冰冰 | 2 |
| 19 | 范冰冰 | 1 |
+--------+--------------+----------+
25 rows in set (0.00 sec)
13、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
--思路 先取出学好为1的学生学过的课程：
select corse_id from score where student_id = 1;
+----------+
| corse_id |
+----------+
| 3 |
| 2 |
| 1 |
+----------+
--先取出和01完全相同的 然后再统计一遍选课个数大于01所选的个数；
select count(corse_id) from score where student_id=1; ---
select student_id as '学号',student.sname as '学生姓名'
from score left join student
on score.student_id=student.sid
/*查询条件：学生学习的课程在【学号为“002”的同学所学的课程科目】中*/
where score.corse_id in(select corse_id from score where student_id=1)
/*排除自身，即不包括002同学自己*/
and student_id!=1
group by student_id
/*分组条件：在上方查询条件的基础上，保证学习的课程总数相同*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)
order by student_id;
先选出和1全部相同的，让后选个数大于等于01的； select a.student_id from
(select student_id
from score left join student
on score.student_id=student.sid
/*查询条件：学生学习的课程在【学号为“002”的同学所学的课程科目】中*/
where score.corse_id in(select corse_id from score where student_id=1)
/*排除自身，即不包括002同学自己*/
and student_id!=1
group by student_id
/*分组条件：在上方查询条件的基础上，保证学习的课程总数相同*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)
order by student_id) a
where (select count(corse_id) from score where student_id in())
>=(select count(corse_id) from score where student_id=1); select count(corse_id) from score group by student_id; --OK --查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
select student_id,count(corse_id) from score
where student_id
in(select student_id
from score left join student
on score.student_id=student.sid
/*查询条件：学生学习的课程在【学号为“001”的同学所学的课程科目】中*/
where score.corse_id in(select corse_id from score where student_id=1)
/*排除自身，即不包括002同学自己*/
and student_id!=1
group by student_id
/*分组条件：在上方查询条件的基础上，保证学习的课程总数相同*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)
order by student_id)
group by student_id
having count(corse_id) >= (select count(corse_id) from score where student_id=1); --ok --最终版 查询至少学过学号为“001”同学所有课的其他同学学号和姓名； -- 思路：先取出和01完全相同的 然后再统计一遍选课总数大于01所选总数； select student_id as '学生学号',student.sname as '学生姓名',count(corse_id) as '课程总数'
/* 成绩表score 拼接 学生表student 用来取到：tudent.sname*/
from score left join student
on score.student_id=student.sid
where student_id
in(
select student_id from score
left join student on score.student_id = student.sid
where student_id in (
select student_id from score where student_id != 1
group by student_id
HAVING
count(corse_id) = (select count(1) from score where student_id = 1)
)
and
corse_id in (select corse_id from score where student_id = 1)
group by student_id
HAVING count(corse_id) = (select count(1) from score where student_id = 1)
)
/* 对成绩表score 按照student_id分组*/
group by student_id
having count(corse_id) >=(select count(corse_id) from score where student_id=1); select student_id as '学生学号',student.sname as '学生姓名',count(corse_id) as '课程总数'
/* 成绩表score 拼接 学生表student 用来取到：tudent.sname*/
from score left join student
on score.student_id=student.sid
where student_id
in(
select student_id
from score left join student
on score.student_id=student.sid
/*查询条件：学生学习的课程在【学号为“001”的同学所学的课程科目】中*/
where score.corse_id in(select corse_id from score where student_id=1)
/*排除自身，即不包括002同学自己*/
and student_id!=1
group by student_id
/*分组条件：在上方查询条件的基础上，限定条件学习的课程总数相同,取到和学生1所选课程全部相同的学生ID*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)
)
/* 对成绩表score 按照student_id分组*/
group by student_id
having count(corse_id) >=(select count(corse_id) from score where student_id=1); +--------------+--------------+--------------+
| 学生学号 | 学生姓名 | 课程总数 |
+--------------+--------------+--------------+
| 8 | 刘德华 | 3 |
| 14 | 李晨 | 4 |
+--------------+--------------+--------------+
2 rows in set (0.00 sec) select student_id as '学生学号',student.sname as '学生姓名',count(corse_id) as '课程总数'
from score left join student
on score.student_id=student.sid
where student_id
in(
select student_id
from score left join student
on score.student_id=student.sid
where score.corse_id in(select corse_id from score where student_id=1)
and student_id!=1
group by student_id
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)
)
group by student_id
having count(corse_id) >=(select count(corse_id) from score where student_id=1);
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
---思路： 先查询了解一下【学号为“002”的同学所学的课程】
select student_id as '学号',student.sname as '学生姓名',corse_id
from score left join student
on score.student_id=student.sid
where student_id=2;
+--------+--------------+----------+
| 学号 | 学生姓名 | corse_id |
+--------+--------------+----------+
| 2 | 王小龙 | 2 |
| 2 | 王小龙 | 3 |
+--------+--------------+----------+
2 rows in set (0.00 sec) --OK-按照12题的思路继续编写： select student_id as '学号',student.sname as '学生姓名'
from score left join student
on score.student_id=student.sid
/*查询条件：学生学习的课程在【学号为“002”的同学所学的课程科目】中*/
where score.corse_id in(select corse_id from score where student_id=1)
/*排除自身，即不包括002同学自己*/
and student_id!=1
group by student_id
/*分组条件：在上方查询条件的基础上，保证学习的课程总数相同*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)
order by student_id; select student_id as '学号',student.sname as '学生姓名',count(corse_id),count(student_id)
from score left join student
on score.student_id=student.sid
/*查询条件：学生学习的课程在【学号为“002”的同学所学的课程科目】中*/
where score.corse_id in(select corse_id from score where student_id=1)
/*排除自身，即不包括002同学自己*/
and student_id!=1
group by student_id
having count(score.corse_id)=(select count(corse_id) from score where student_id=1) ---laoshi wangquanxiangtong de sulu
select student_id,sname from score
left join student
on score.student_id = student.sid
where student_id in (
/*查处学号不是1的学生 分组 */
select student_id from score where student_id != 1
group by student_id
HAVING count(corse_id) = (select count(1) from score where student_id = 1)
) and corse_id in (select corse_id from score where student_id = 1)
group by student_id
HAVING count(corse_id) = (select count(1) from score where student_id = 1) --湘林 思路：------------------------------------------
select e.student_id,f.sname
from (
select c.student_id from (select score.student_id,count(*) as count from score,
(select * from score where student_id=2) b
where score.course_id=b.course_id
and score.student_id!=2
group by score.student_id
) c,
(select count(*) as count
from score where student_id=2) d
where c.count=d.count) e
left join student f on e.student_id = f.sid; SELECT sid AS student_id,sname
FROM student
WHERE sid IN (
SELECT student_id
FROM score
WHERE student_id IN (
SELECT student_id
FROM score
GROUP BY student_id
HAVING count(student_id) = (SELECT COUNT(*) FROM score WHERE student_id = 2
)
AND student_id != 2
) AND course_id IN (SELECT course_id FROM score WHERE student_id = 2)
GROUP BY student_id
HAVING COUNT(student_id) = (SELECT COUNT(*) FROM score WHERE student_id = 2
)
);
------------------------------------------------------------ +--------+--------------+
| 学号 | 学生姓名 |
+--------+--------------+
| 1 | 王小虎 |
| 5 | 张小丽 |
| 8 | 刘德华 |
| 9 | 梁朝伟 |
| 12 | 张曼玉 |
| 14 | 李晨 |
| 16 | 陈赫 |
+--------+--------------+
7 rows in set (0.00 sec)
15、删除学习“叶平”老师课的SC表记录；
select student_id as '学号',student.sname as '学生姓名',count(corse_id)
from score left join student
on score.student_id=student.sid
where corse_id in(select corse_id from score where student_id=2) and student_id!=2
group by student_id
order by student_id;
--16题之后 用的数据库是老师的：
16、向score表中插入一些记录，这些记录要求符合以下条件：
①没有上过编号“002”课程的同学学号；
②插入“002”号课程的平均成绩；
--条件1
思路：先查出没有上过课程编号为''的学生学号
select student_id from score
where student_id not in(
select student_id
from score
where course_id='')
group by student_id; +------------+
| student_id |
+------------+
| 2 |
| 13 |
+------------+
2 rows in set (0.00 sec) --再计算出课程‘2’的平均成绩
select avg(num) from score
group by course_id
having course_id=2; --最后
17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
--思路: 用到的知识：1 字段子查询 2 自连接
--字段子查询
select (select caption from class where cid=1),'生物',sid,num from score; --OK 自连接
select student_id as '学号',
(select num from score as Inner_Tb where Inner_Tb.student_id=OuterTb.student_id and course_id=1) as '生物',
(select num from score as Inner_Tb where Inner_Tb.student_id=OuterTb.student_id and course_id=2) as '物理',
(select num from score as Inner_Tb where Inner_Tb.student_id=OuterTb.student_id and course_id=3) as '体育',
count(student_id) as '有效课程总数',
/*根据OuterTb.student_id分组后 计算平均成绩 可以替换成:sum(num)/count(student_id)*/
/*注意这里求出的平均分是所有科目的平均分 */
avg(num) as '有效平均分（升序）'
from score as OuterTb
group by OuterTb.student_id
order by avg(num) asc; ---求 生物 物理 体育的平均分数
select student_id as '学号',
(select num from score as Inner_Tb where Inner_Tb.student_id=OuterTb.student_id and course_id=1) as sw,
(select num from score as Inner_Tb where Inner_Tb.student_id=OuterTb.student_id and course_id=2) as wl,
(select num from score as Inner_Tb where Inner_Tb.student_id=OuterTb.student_id and course_id=3) as ty,
count(student_id) as '有效课程总数',
sw.num+wl.num+ty.num
from score as OuterTb
group by OuterTb.student_id
order by avg(num) asc; --横向求和
select isnull([津贴],0)+isnull([补贴],0) as hj from gongzi where id=10 -- 方法二 行转列
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select student_id as '学号',student.sname as '学生姓名',count(corse_id)
from score left join student
on score.student_id=student.sid
where corse_id in(select corse_id from score where student_id=2) and student_id!=2
group by student_id
order by student_id;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
--思路
先查询各科的平均成绩 升序
select course_id,avg(num)
from score left join course
on score.course_id=course.cid
group by course_id
order by avg(num); 在查询各科的及格率 降序
select course_id,
sum(case when score.num>60
then 1
else 0 END)/count(course_id)*100 as percent
from score left join course
on score.course_id=course.cid
group by course_id
order by percent desc; -- 然后把上述两步合并 --老师思路
select course_id, avg(num) as avgnum,
sum(case when score.num > 60
then 1
else 0 END)/count(1)*100 as percent
from score
group by course_id
order by avgnum asc,percent desc;
20、课程平均分从高到低显示（显示任课老师）；
--思路：先查出课程平均分 然后降序排列 （调用19题 思路1）
select course_id,avg(num)
from score left join course
on score.course_id=course.cid
group by course_id
order by avg(num) desc; --然后，显示任课老师 说明要让成绩表关联课程表 score.course_id=course.cid
--同时课程表关联老师表 course.teacher_id=teacher.tid select course_id as '课程编号',teacher.tname as '任课教师',avg(num) as '课程平均分'
from score left join (course,teacher)
on score.course_id=course.cid and course.teacher_id=teacher.tid
group by course_id
order by avg(num) desc; +--------------+-----------------+-----------------+
| 课程编号 | 任课教师 | 课程平均分 |
+--------------+-----------------+-----------------+
| 4 | 李平老师 | 85.2500 |
| 2 | 李平老师 | 65.0909 |
| 3 | 刘海燕老师 | 64.4167 |
| 1 | 张磊老师 | 53.4167 |
+--------------+-----------------+-----------------+
4 rows in set (0.00 sec) --20题
--老师思路：
--存在问题 主表不应该是course 而应该是score
select avg(if(isnull(score.num),0,score.num)),teacher.tname
from course
left join score on course.cid=score.course_id
left join teacher on course.teacher_id=teacher.tid
group by score.course_id; --正确写法：
select avg(if(isnull(score.num),0,score.num)),teacher.tname
from score
left join course on score.course_id=course.cid
left join teacher on course.teacher_id=teacher.tid
group by course_id
order by avg(if(isnull(score.num),0,score.num)) desc;
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
--思路：先查出 所有学生生物成绩 然后按照成绩升序 后排序 最后分页limit 3 select * from score where course_id =1 order by num desc limit 3;
select * from score where course_id =2 order by num desc limit 3;
select * from score where course_id =3 order by num desc limit 3;
... ---产生笛卡尔集的方式
select * from
(select student_id,course_id,num from score where course_id =1 order by num desc limit 3) as A,
(select student_id,course_id,num from score where course_id =2 order by num desc limit 3) as B,
(select student_id,course_id,num from score where course_id =3 order by num desc limit 3) as C --union 连表方式：
select * from
(select student_id,course_id,num from score where course_id =1 order by num desc limit 3) as A
union
select * from
(select student_id,course_id,num from score where course_id =2 order by num desc limit 3) as B
union
select * from (select student_id,course_id,num from score where course_id =3 order by num desc limit 3) as C;
+------------+-----------+-----+
| student_id | course_id | num |
+------------+-----------+-----+
| 9 | 1 | 91 |
| 11 | 1 | 90 |
| 10 | 1 | 90 |
| 6 | 2 | 100 |
| 8 | 2 | 100 |
| 7 | 2 | 100 |
| 3 | 3 | 87 |
| 13 | 3 | 87 |
| 2 | 3 | 68 |
+------------+-----------+-----+
9 rows in set (0.00 sec) --思路二 取出各科成绩的第一名 再取出第二名 。。。
select score.course_id,course.cname,max(num) as '第一名' from score
left join course
on score.course_id=course.cid
group by course_id;
+-----------+--------+-----------+
| course_id | cname | 第一名 |
+-----------+--------+-----------+
| 1 | 生物 | 91 |
| 2 | 物理 | 100 |
| 3 | 体育 | 87 |
| 4 | 美术 | 100 |
+-----------+--------+-----------+
4 rows in set (0.00 sec) select num from score where course_id =1 order by num desc limit 1 offset 1;
select num from score where course_id =1 order by num desc limit 1 offset 1; select * from (select score.course_id,course.cname,max(num) as '第一名' from score
left join course
on score.course_id=course.cid
group by course_id) as A; select
sid,
(select num from score as s2
where s2.course_id = s1.course_id
order by num desc limit 0,1) as first_num
from
score as s1 group by first_num select
sid,
(select num from score as s2
where s2.course_id = s1.course_id
order by num desc limit 0,1) as first_num
from
score as s1
-- +-----+-----------+-----+-----------+------------+
| sid | course_id | num | first_num | second_num |
+-----+-----------+-----+-----------+------------+
| 34 | 1 | 91 | 91 | 90 |
| 38 | 1 | 90 | 91 | 90 |
| 42 | 1 | 90 | 91 | 90 |
| 46 | 1 | 90 | 91 | 90 |
| 23 | 2 | 100 | 100 | 88 |
| 27 | 2 | 100 | 100 | 88 |
| 31 | 2 | 100 | 100 | 88 | select course_id from score
group by course_id; select student_id from score
group by student_id; ----课上
22、查询每门课程被选修的学生数；
select course_id, count(course_id) from score group by course_id;
23、查询出只选修了一门课程的全部学生的学号和姓名；
--老师的错误
select student.sid, student.sname, count(1) from score
left join student
on score.student_id=student.sid
group by course_id
having count(1)=1 --正确应该 以学生ID分组
select student.sid, student.sname, count(1) from score
left join student
on score.student_id=student.sid
group by score.student_id
having count(1)=1
24、查询男生、女生的人数；
select * from
(select count(1) as man
from student
where gender='男') as A ,
(select count(1) as feman
from student
where gender='女') as B
25、查询姓“张”的学生名单；
select sname from student where sname like '张%';
26、查询同名同姓学生名单，并统计同名人数；
--老师的少一个条件 count(1)>1
select sname,count(1) as count from student group by sname having count(1)>1;
27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select course_id,avg(if(isnull(num), 0 ,num)) as avg
from score
group by course_id
order by avg asc,course_id desc;
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
select student_id,sname, avg(if(isnull(num), 0 ,num))
from score
left join student
on score.student_id = student.sid
group by student_id;
29、查询课程名称为“生物”，且分数低于60的学生姓名和分数；
select student.sname,score.num from score
left join course
on score.course_id = course.cid
left join student
on score.student_id = student.sid
where score.num < 60 and course.cname = '生物';
30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select * from score where score.student_id = 3 and score.num > 80
31、求选了课程的学生人数
select count(distinct student_id) from score;
32、查询选修“李平”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
33、查询各个课程及相应的选修人数；
select course.cname,count(1) from score
left join course on score.course_id = course.cid
group by course_id;
34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num
from score as s1, score as s2
where s1.num = s2.num and s1.course_id != s2.course_id;
+-----------+-----------+-----+-----+
| course_id | course_id | num | num |
+-----------+-----------+-----+-----+
| 1 | 2 | 9 | 9 |
| 2 | 4 | 66 | 66 |
| 2 | 1 | 77 | 77 |
| 4 | 2 | 66 | 66 |
| 4 | 3 | 87 | 87 |
| 2 | 4 | 100 | 100 |
| 2 | 1 | 9 | 9 |
| 4 | 2 | 100 | 100 |
| 2 | 4 | 88 | 88 |
| 4 | 2 | 88 | 88 |
| 1 | 2 | 77 | 77 |
| 3 | 4 | 87 | 87 |
+-----------+-----------+-----+-----+
12 rows in set (0.00 sec)
select DISTINCT s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.num,s2.num
from score as s1, score as s2
where s1.num = s2.num and s1.course_id != s2.course_id;
35、查询每门课程成绩最好的前两名；
--先查处李平老师所教的课程
select course.cid from course
left join teacher
on course.teacher_id = teacher.tid where tname='李平老师'; +-----+
| cid |
+-----+
| 2 |
| 4 |
+-----+
2 rows in set (0.00 sec) ----再取出李平老师 所授课程的学生中 课程名称和最高成绩
select course_id,max(num) from score
left join student on score.student_id = student.sid
group by course_id
having course_id in(select course.cid from course
left join teacher
on course.teacher_id = teacher.tid where tname='李平老师'); +-----------+----------+
| course_id | max(num) |
+-----------+----------+
| 2 | 100 |
| 4 | 100 |
+-----------+----------+
2 rows in set (0.00 sec) --最后拼接学生表 分数对应到学生姓名 select course_id,num from score where course_id in (select course.cid from course
left join teacher
on course.teacher_id = teacher.tid where tname='李平老师') order by course_id asc,num desc select course_id,max(num) from score where course_id in (select course.cid from course
left join teacher
on course.teacher_id = teacher.tid where tname='李平老师')
group by course_id; select student_id from score as Inner_Tb where Inner_Tb.course_id=OuterTb.course_id and select course_id,max(num),
(select student_id from score as Inner_Tb where Inner_Tb.course_id=OuterTb.course_id
group by student_id
having student_id)
from score as OuterTb
where course_id in (select course.cid from course
left join teacher
on course.teacher_id = teacher.tid where tname='李平老师')
group by course_id; select course_id,max(num),
(select student_id from score as Inner_Tb where Inner_Tb.course_id=OuterTb.course_id
where ) as '学号'
from score as OuterTb
where course_id in (select course.cid from course
left join teacher
on course.teacher_id = teacher.tid where tname='李平老师')
group by course_id; sid | student_id | course_id | num | update score set num=99 where sid=17; mysql> select max(num) from score where course_id=2 or course_id=4;
+----------+
| max(num) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec) ----32题另一种思路：查询选修“李平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
----考虑到并列第一的情况 select t1.sname as '学生姓名',t3.cname as '课程名称',t2.num as '该课程最高成绩',t4.tname as '授课教师'
from student t1, score t2,course t3,teacher t4
where t1.sid = t2.student_id
and t2.course_id = t3.cid
and t3.teacher_id = t4.tid
and t4.tname = '李平老师'
and t2.num = (select max(num) from score where course_id=t3.cid ); +--------------+--------------+-----------------------+--------------+
| 学生姓名 | 课程名称 | 该课程最高成绩 | 授课教师 |
+--------------+--------------+-----------------------+--------------+
| 张四 | 物理 | 100 | 李平老师 |
| 铁锤 | 物理 | 100 | 李平老师 |
| 张四 | 美术 | 100 | 李平老师 |
+--------------+--------------+-----------------------+--------------+
3 rows in set (0.00 sec) select a.* from score a
where course_id=(select max(num) from score where course_id=a.course_id limit 1); select t1.sname as '学生姓名',t3.cname as '课程名称',t2.num as '该课程最高成绩',t4.tname as '授课教师'
from student t1, score t2,course t3,teacher t4
where t1.sid = t2.student_id
and t2.course_id = t3.cid
and t3.teacher_id = t4.tid
and t4.tname = '李平老师'
and t2.num = (select max(num) from score where course_id=t3.cid ); --老师 思路 有问题
select student.sname,num from score
left join student on score.student_id = student.sid
where score.course_id in (
select course.cid from course
left join teacher
on course.teacher_id = teacher.tid where tname='李平老师')
order by num desc limit 1;
36、检索至少选修两门课程的学生学号；
37、查询全部学生都选修的课程的课程号和课程名；
select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);
38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
39、查询两门以上不及格课程的同学的学号及其平均成绩；
select student_id,count(1) from score where num < 60 group by student_id having count(1) > 2
40、检索“004”课程分数小于60，按分数降序排列的同学学号；
select student_id from score where num< 60 and course_id = 4 order by num desc;
41、删除“002”同学的“001”课程的成绩；
delete from score where course_id = 1 and student_id = 2