Mysql 查询练习
---创建班级表
create tableclass(
cidint auto_increment primary key,
captionvarchar(20)
)engine=innodb default charset=utf8;---创建学生表
create tablestudent(
sidint auto_increment primary key,
snamevarchar(20),
gendervarchar(10) default '男',
class_idint)engine=innodb default charset=utf8;insert into student(sname,gender,class_id) values('范冰冰','女','3');---创建老师表
create tableteacher(
tidint auto_increment primary key,
tnamevarchar(20)
)engine=innodb default charset=utf8;
mysql> insert into teacher(tname) values('孙子');
mysql> update teacher set tname='李时珍' where tid=3;---创建课程表
create tablecourse(
cidint auto_increment primary key,
cnamevarchar(20),
teache_idint)engine=innodb default charset=utf8;
mysql> insert into course(cname) value('本草纲目');---创建学生成绩表
create tablescore(
sidint auto_increment primary key,
student_idint,
corse_idint,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='001';+-----+------------+----------+--------+
| 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.00sec)---查处所有学生物理课 学生成绩
select * from score where corse_id='003';------查询“生物”课程比“物理”课程成绩高的所有学生的学号:
select a.student_id as '学号', a.number as '生物成绩',b.number as '物理成绩'
from (select * from score where corse_id='001') aleft join (select * from score where corse_id='003') bon a.student_id=b.student_idwhere 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.00sec)--------------------------------------------------
select score.student_id as '学号',
score.corse_idas '课程编号',course.cname as '课程名称',
score.number as '成绩'
fromscore,coursewhere corse_id=1 and score.corse_id =course.cid;---------------------------------------------------
selectscore.corse_idas '课程编号',course.cname as '课程名称'
fromscore,coursewhere score.corse_id =course.cidgroup bycorse_id;----为表起别名:
selecta.corse_idas '课程编号',b.cname as '课程名称'
fromscore a,course bwhere a.corse_id =b.cidgroup bycorse_id;+--------------+--------------+
| 课程编号 | 课程名称 |
+--------------+--------------+
| 1 | 生物 |
| 2 | 体育 |
| 3 | 物理 |
+--------------+--------------+---------------------------------------------------
3、查询平均成绩大于60分的同学的学号和平均成绩;
---由于成绩录入的较高,这里查询平均成绩大于80分的同学的学号和平均成绩;
select student_id as '学号',sum(number) as '总成绩',avg(number) as '平均成绩' from score where avg(number)>80 group bystudent_id;select student_id as '学号',sum(number) as '总成绩',avg(number) as '平均成绩'
from score group bystudent_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 bystudent_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.00sec)---关联学生表查出学生姓名:
select a.student_id,a.count(*),a.sum(number),b.sidfromscore a, student bgroup bya.student_idhaving a.student_id=b.sid;---OK
select student_id as '学号',student.sname as '学生姓名',count(*) as '选课数',sum(number) as '总成绩'
from score left joinstudenton score.student_id=student.sidgroup bystudent_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 bystudent_id;+------------+
| student_id |
+------------+
| 2 |
| 4 |
| 5 |
| 9 |
| 12 |
| 14 |
| 15 |
| 16 |
| 19 |
+------------+
9 rows in set (0.00sec)-------OK-------------------------------------------------
select a.student_id as '学号', b.sname as '学生姓名'
fromscore a ,student bwhere a.corse_id!=2 and a.student_id=b.sidgroup bya.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---------------------------------------
selectstudent_id,student.snamefrom score left join student on score.student_id=student.sidwhere corse_id=1
union
selectstudent_id,student.snamefrom score left join student on score.student_id=student.sidwhere 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.00sec)--OK-联合学生表,查找学生姓名
selecta.student_id, b.snamefromscore a,student bwhere (a.corse_id=3 or a.corse_id=1)and a.student_id =b.sidgroup bystudent_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 joinstudenton score.student_id=student.sid where corse_id=1
group bystudent_idhaving ;
10、查询所有课程成绩小于60分的同学的学号、姓名;
select student_id as '学号',student.sname as '学生姓名'
from score left joinstudenton score.student_id=student.sid where corse_id=1
group bystudent_idhaving;select student_id as '学号',student.sname as '学生姓名'
from score left joinstudenton score.student_id=student.sidwhere number<60;---OK---
select student_id as '学号',student.sname as '学生姓名',
course.cnameas '课程名称',score.number as '成绩 (小于60分)'
from score left join(student,course)on score.student_id=student.sid and score.corse_id=course.cidwhere number<60;+--------+--------------+--------------+----------------------+
| 学号 | 学生姓名 | 课程名称 | 成绩 (小于60分) |
+--------+--------------+--------------+----------------------+
| 5 | 张小丽 | 体育 | 57 |
| 2 | 王小龙 | 体育 | 53 |
| 1 | 王小虎 | 体育 | 58 |
| 8 | 刘德华 | 生物 | 40 |
+--------+--------------+--------------+----------------------+
4 rows in set (0.00sec)--课上老师要求测试练习--成绩表score 关联学生表student 和 课程表course
select score.sid,student_id as '学号',student.sname as '学生姓名',corse_id as '课程ID',
course.cnameas '课程名称',score.number as '成绩'
from score left join(student,course)on score.student_id=student.sid and score.corse_id=course.cidorder byscore.sid;
11、查询没有学全所有课的同学的学号、姓名;
---思路:首先从成绩表中查出:每位学生各自所学的课程总数。
select student_id as '学号',student.sname as '学生姓名',count(student_id) as '所学课程总数'
from score left joinstudenton score.student_id=student.sidgroup bystudent_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.00sec)---然后,增加判断条件,筛选出‘所学课程总数’小于3的同学。---最后得到:没有学全所有课的同学的学号、姓名。
select student_id as '学号',student.sname as '学生姓名',count(student_id) as '所学课程总数'
from score left joinstudenton score.student_id=student.sidgroup bystudent_idhaving 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_idfrom score left joinstudenton score.student_id=student.sidwhere student_id=1;+--------+--------------+----------+
| 学号 | 学生姓名 | corse_id |
+--------+--------------+----------+
| 1 | 王小虎 | 3 |
| 1 | 王小虎 | 2 |
| 1 | 王小虎 | 1 |
+--------+--------------+----------+
3 rows in set (0.00sec)---然后,我们用如下类似语法: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_idfrom score left joinstudenton score.student_id=student.sidwhere corse_id in(select corse_id from score where student_id=1) and student_id!=1
order bystudent_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 joinstudenton 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 bystudent_id/*分组条件:在上方查询条件的基础上,保证学习的课程总数相同*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)order bystudent_id;
先选出和1全部相同的,让后选个数大于等于01的;select a.student_id from(selectstudent_idfrom score left joinstudenton 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 bystudent_id/*分组条件:在上方查询条件的基础上,保证学习的课程总数相同*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)order bystudent_id) awhere (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 bystudent_id;--OK --查询至少学过学号为“001”同学所有课的其他同学学号和姓名;
select student_id,count(corse_id) fromscorewherestudent_idin(selectstudent_idfrom score left joinstudenton 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 bystudent_id/*分组条件:在上方查询条件的基础上,保证学习的课程总数相同*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)order bystudent_id)group bystudent_idhaving 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 joinstudenton score.student_id=student.sidwherestudent_idin(select student_id fromscoreleft join student on score.student_id =student.sidwhere student_id in(select student_id from score where student_id != 1
group bystudent_idHAVING
count(corse_id) = (select count(1) from score where student_id = 1)
)andcorse_idin (select corse_id from score where student_id = 1)group bystudent_idHAVING count(corse_id) = (select count(1) from score where student_id = 1)
)/*对成绩表score 按照student_id分组*/
group bystudent_idhaving 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 joinstudenton score.student_id=student.sidwherestudent_idin(selectstudent_idfrom score left joinstudenton 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 bystudent_id/*分组条件:在上方查询条件的基础上,限定条件学习的课程总数相同,取到和学生1所选课程全部相同的学生ID*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)
)/*对成绩表score 按照student_id分组*/
group bystudent_idhaving count(corse_id) >=(select count(corse_id) from score where student_id=1);+--------------+--------------+--------------+
| 学生学号 | 学生姓名 | 课程总数 |
+--------------+--------------+--------------+
| 8 | 刘德华 | 3 |
| 14 | 李晨 | 4 |
+--------------+--------------+--------------+
2 rows in set (0.00sec)select student_id as '学生学号',student.sname as '学生姓名',count(corse_id) as '课程总数'
from score left joinstudenton score.student_id=student.sidwherestudent_idin(selectstudent_idfrom score left joinstudenton score.student_id=student.sidwhere score.corse_id in(select corse_id from score where student_id=1)and student_id!=1
group bystudent_idhaving count(score.corse_id)=(select count(corse_id) from score where student_id=1)
)group bystudent_idhaving count(corse_id) >=(select count(corse_id) from score where student_id=1);
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;
---思路: 先查询了解一下【学号为“002”的同学所学的课程】
select student_id as '学号',student.sname as '学生姓名',corse_idfrom score left joinstudenton score.student_id=student.sidwhere student_id=2;+--------+--------------+----------+
| 学号 | 学生姓名 | corse_id |
+--------+--------------+----------+
| 2 | 王小龙 | 2 |
| 2 | 王小龙 | 3 |
+--------+--------------+----------+
2 rows in set (0.00sec)--OK-按照12题的思路继续编写:
select student_id as '学号',student.sname as '学生姓名'
from score left joinstudenton 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 bystudent_id/*分组条件:在上方查询条件的基础上,保证学习的课程总数相同*/
having count(score.corse_id)=(select count(corse_id) from score where student_id=1)order bystudent_id;select student_id as '学号',student.sname as '学生姓名',count(corse_id),count(student_id)from score left joinstudenton 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 bystudent_idhaving count(score.corse_id)=(select count(corse_id) from score where student_id=1)---laoshi wangquanxiangtong de sulu
select student_id,sname fromscoreleft joinstudenton score.student_id =student.sidwhere student_id in(/*查处学号不是1的学生 分组*/
select student_id from score where student_id != 1
group bystudent_idHAVING 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 bystudent_idHAVING count(corse_id) = (select count(1) from score where student_id = 1)--湘林 思路:------------------------------------------
selecte.student_id,f.snamefrom(select c.student_id from (select score.student_id,count(*) as count fromscore,
(select * from score where student_id=2) bwhere score.course_id=b.course_idand score.student_id!=2
group byscore.student_id
) c,
(select count(*) as count
from score where student_id=2) dwhere c.count=d.count) eleft join student f on e.student_id =f.sid;SELECT sid ASstudent_id,snameFROMstudentWHERE sid IN(SELECTstudent_idFROMscoreWHERE student_id IN(SELECTstudent_idFROMscoreGROUP BYstudent_idHAVING 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 BYstudent_idHAVING 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 joinstudenton score.student_id=student.sidwhere corse_id in(select corse_id from score where student_id=2) and student_id!=2
group bystudent_idorder by student_id;
--16题之后 用的数据库是老师的:
16、向score表中插入一些记录,这些记录要求符合以下条件:
①没有上过编号“002”课程的同学学号;
②插入“002”号课程的平均成绩;
--条件1
思路:先查出没有上过课程编号为'2'的学生学号select student_id fromscorewhere student_id not in(selectstudent_idfromscorewhere course_id='2')group bystudent_id;+------------+
| student_id |
+------------+
| 2 |
| 13 |
+------------+
2 rows in set (0.00sec)--再计算出课程‘2’的平均成绩
select avg(num) fromscoregroup bycourse_idhaving course_id=2;--最后
17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;
--思路: 用到的知识:1 字段子查询 2 自连接--字段子查询
select (select caption from class where cid=1),'生物',sid,num fromscore;--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 asOuterTbgroup byOuterTb.student_idorder 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) assw,
(select num from score as Inner_Tb where Inner_Tb.student_id=OuterTb.student_id and course_id=2) aswl,
(select num from score as Inner_Tb where Inner_Tb.student_id=OuterTb.student_id and course_id=3) asty,count(student_id) as '有效课程总数',
sw.num+wl.num+ty.numfrom score asOuterTbgroup byOuterTb.student_idorder 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 joinstudenton score.student_id=student.sidwhere corse_id in(select corse_id from score where student_id=2) and student_id!=2
group bystudent_idorder by student_id;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序;
--思路
先查询各科的平均成绩 升序select course_id,avg(num)from score left joincourseon score.course_id=course.cidgroup bycourse_idorder by avg(num);
在查询各科的及格率 降序selectcourse_id,sum(case when score.num>60
then 1
else 0 END)/count(course_id)*100 as percent
from score left joincourseon score.course_id=course.cidgroup bycourse_idorder by percent desc;--然后把上述两步合并
--老师思路
select course_id, avg(num) asavgnum,sum(case when score.num > 60
then 1
else 0 END)/count(1)*100 as percent
fromscoregroup bycourse_idorder by avgnum asc,percent desc;
20、课程平均分从高到低显示(显示任课老师);
--思路:先查出课程平均分 然后降序排列 (调用19题 思路1)
select course_id,avg(num)from score left joincourseon score.course_id=course.cidgroup bycourse_idorder 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.tidgroup bycourse_idorder by avg(num) desc;+--------------+-----------------+-----------------+
| 课程编号 | 任课教师 | 课程平均分 |
+--------------+-----------------+-----------------+
| 4 | 李平老师 | 85.2500 |
| 2 | 李平老师 | 65.0909 |
| 3 | 刘海燕老师 | 64.4167 |
| 1 | 张磊老师 | 53.4167 |
+--------------+-----------------+-----------------+
4 rows in set (0.00sec)--20题--老师思路:--存在问题 主表不应该是course 而应该是score
select avg(if(isnull(score.num),0,score.num)),teacher.tnamefromcourseleft join score on course.cid=score.course_idleft join teacher on course.teacher_id=teacher.tidgroup byscore.course_id;--正确写法:
select avg(if(isnull(score.num),0,score.num)),teacher.tnamefromscoreleft join course on score.course_id=course.cidleft join teacher on course.teacher_id=teacher.tidgroup bycourse_idorder 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) asA,
(select student_id,course_id,num from score where course_id =2 order by num desc limit 3) asB,
(select student_id,course_id,num from score where course_id =3 order by num desc limit 3) asC--union 连表方式:
select * from(select student_id,course_id,num from score where course_id =1 order by num desc limit 3) asAunion
select * from(select student_id,course_id,num from score where course_id =2 order by num desc limit 3) asBunion
select * from (select student_id,course_id,num from score where course_id =3 order by num desc limit 3) asC;+------------+-----------+-----+
| 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.00sec)--思路二 取出各科成绩的第一名 再取出第二名 。。。
select score.course_id,course.cname,max(num) as '第一名' fromscoreleft joincourseon score.course_id=course.cidgroup bycourse_id;+-----------+--------+-----------+
| course_id | cname | 第一名 |
+-----------+--------+-----------+
| 1 | 生物 | 91 |
| 2 | 物理 | 100 |
| 3 | 体育 | 87 |
| 4 | 美术 | 100 |
+-----------+--------+-----------+
4 rows in set (0.00sec)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 '第一名' fromscoreleft joincourseon score.course_id=course.cidgroup by course_id) asA;selectsid,
(select num from score ass2where s2.course_id =s1.course_idorder by num desc limit 0,1) asfirst_numfromscoreas s1 group byfirst_numselectsid,
(select num from score ass2where s2.course_id =s1.course_idorder by num desc limit 0,1) asfirst_numfromscoreass1--
+-----+-----------+-----+-----------+------------+
| 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 fromscoregroup bycourse_id;select student_id fromscoregroup bystudent_id;----课上
22、查询每门课程被选修的学生数;
select course_id, count(course_id) from score group by course_id;
23、查询出只选修了一门课程的全部学生的学号和姓名;
--老师的错误
select student.sid, student.sname, count(1) fromscoreleft joinstudenton score.student_id=student.sidgroup bycourse_idhaving count(1)=1
--正确应该 以学生ID分组
select student.sid, student.sname, count(1) fromscoreleft joinstudenton score.student_id=student.sidgroup byscore.student_idhaving count(1)=1
24、查询男生、女生的人数;
select * from(select count(1) asmanfromstudentwhere gender='男') asA ,
(select count(1) asfemanfromstudentwhere 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
fromscoregroup bycourse_idorder by avg asc,course_id desc;
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩;
select student_id,sname, avg(if(isnull(num), 0,num))fromscoreleft joinstudenton score.student_id =student.sidgroup by student_id;
29、查询课程名称为“生物”,且分数低于60的学生姓名和分数;
select student.sname,score.num fromscoreleft joincourseon score.course_id =course.cidleft joinstudenton score.student_id =student.sidwhere 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 fromcourseleft jointeacheron course.teacher_id = teacher.tid where tname='李平老师';+-----+
| cid |
+-----+
| 2 |
| 4 |
+-----+
2 rows in set (0.00sec)----再取出李平老师 所授课程的学生中 课程名称和最高成绩
select course_id,max(num) fromscoreleft join student on score.student_id =student.sidgroup bycourse_idhaving course_id in(select course.cid fromcourseleft jointeacheron course.teacher_id = teacher.tid where tname='李平老师');+-----------+----------+
| course_id | max(num) |
+-----------+----------+
| 2 | 100 |
| 4 | 100 |
+-----------+----------+
2 rows in set (0.00sec)--最后拼接学生表 分数对应到学生姓名
select course_id,num from score where course_id in (select course.cid fromcourseleft jointeacheron 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 fromcourseleft jointeacheron course.teacher_id = teacher.tid where tname='李平老师')group bycourse_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_idgroup bystudent_idhavingstudent_id)from score asOuterTbwhere course_id in (select course.cid fromcourseleft jointeacheron course.teacher_id = teacher.tid where tname='李平老师')group bycourse_id;select course_id,max(num),
(select student_id from score as Inner_Tb where Inner_Tb.course_id=OuterTb.course_idwhere ) as '学号'
from score asOuterTbwhere course_id in (select course.cid fromcourseleft jointeacheron course.teacher_id = teacher.tid where tname='李平老师')group bycourse_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.00sec)----32题另一种思路:查询选修“李平”老师所授课程的学生中,成绩最高的学生姓名及其成绩----考虑到并列第一的情况
select t1.sname as '学生姓名',t3.cname as '课程名称',t2.num as '该课程最高成绩',t4.tname as '授课教师'
fromstudent t1, score t2,course t3,teacher t4where t1.sid =t2.student_idand t2.course_id =t3.cidand t3.teacher_id =t4.tidand t4.tname = '李平老师'
and t2.num = (select max(num) from score where course_id=t3.cid );+--------------+--------------+-----------------------+--------------+
| 学生姓名 | 课程名称 | 该课程最高成绩 | 授课教师 |
+--------------+--------------+-----------------------+--------------+
| 张四 | 物理 | 100 | 李平老师 |
| 铁锤 | 物理 | 100 | 李平老师 |
| 张四 | 美术 | 100 | 李平老师 |
+--------------+--------------+-----------------------+--------------+
3 rows in set (0.00sec)select a.* fromscore awhere 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 '授课教师'
fromstudent t1, score t2,course t3,teacher t4where t1.sid =t2.student_idand t2.course_id =t3.cidand t3.teacher_id =t4.tidand t4.tname = '李平老师'
and t2.num = (select max(num) from score where course_id=t3.cid );--老师 思路 有问题
select student.sname,num fromscoreleft join student on score.student_id =student.sidwhere score.course_id in(select course.cid fromcourseleft jointeacheron 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