CREATE TABLE student( sno int NOT NULL PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20) NOT NULL, sage int NOT NULL, ssex char(2) check(ssex='男'or ssex='女') )character set = utf8;
CREATE TABLE teacher( tno int NOT NULL PRIMARY KEY AUTO_INCREMENT, tname VARCHAR(20) NOT NULL )character set = utf8;
CREATE TABLE course( cno int NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(20) NOT NULL, tno int, CONSTRAINT fk_couTotea FOREIGN KEY (tno) REFERENCES teacher(tno) )character set = utf8;
select a.sno as "学号",b.sname as "姓名", count(a.sno) as "选课数",sum(a.score) as "总成绩" from sc a,student b where a.sno = b.sno group by a.sno,b.sname
或者
select student.sno as "学号",student.sname as "姓名", count(sc.sno) as "选课数",sum(sc.score) as "总成绩" from student left join sc on student.sno = sc.sno group by student.sno,sname
4.查询姓“菜”的老师的个数
select count(tname) as "人数" from teacher where tname like '菜%'
或者
select tname as "姓名",count(tname) as "人数" from teacher where tname like '菜%' group by tname
5.查询没学过“张三”老师课的同学的学号、姓名
select student.sno,sname from student where sno not in (select sc.sno from sc,course,teacher where sc.cno = course.cno and teacher.tno = course.tno and teacher.tname='张三')
或者
select student.sno,sname from student where sno not in (select sc.sno from sc where cno in ( select cno from course where tno = ( select tno from teacher where tname='张三') ) )
6.查询同时学过课程1和课程2的同学的学号、姓名
select sno,sname from student where sno in (select sc.sno from sc where sc.cno = 1) and sno in (select sc.sno from sc where sc.cno = 2)
或者
select c.sno,sname from (select sno from sc where cno = 1) a, (select sno from sc where cno = 2) b, student c where a.sno = b.sno and a.sno = c.sno
或者
select student.sno,sname from student,sc where student.sno = sc.sno and sc.cno = 1 and exists (select * from sc as sc2 where sc2.sno = sc.sno and sc2.cno = 2)
注意,MySQL不支持INTERSECT(交集)操作符。
7.查询学过“李四”老师所教课程的同学的学号、姓名
select distinct student.sno,sname from student,sc where student.sno = sc.sno and sc.cno in ( select course.cno from course,teacher where course.tno = teacher.tno and tname='李四')
或者
select distinct a.sno,sname from student a, sc b, (select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '菜徐坤') e where a.sno = b.sno and b.cno = e.cno
8.查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名
select a.sno,sname from student a, (select sno,score from sc where cno = 1)b, (select sno,score from sc where cno = 2)c where a.sno = b.sno and b.sno = c.sno and b.score > c.score
9.查询所有课程成绩小于60分的同学的学号、姓名
select sno,sname from student where sno not in (select distinct sno from sc where score > 60)
10,查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名
select distinct a.sno, a.sname from student a, sc b where a.sno <> 1 and a.sno=b.sno and b.cno in (select cno from sc where sno = 1)
或者
select s.sno,s.sname from student s, (select sc.sno from sc where sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1 group by sc.sno)r1 where r1.sno=s.sno
11.把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩
思考思考中。。。
12.查询和编号为2的同学学习的课程完全相同的其他同学学号和姓名
select b.sno, b.sname from sc a, student b where b.sno <> 2 and a.sno = b.sno and a.sno not in ( select distinct sno from sc where cno not in (select cno from sc where sno =2 ) ) group by b.sno, b.sname having sum(cno) = (select sum(cno) from sc where sno = 2)
注: 这个sql语句是通过将sc中学号2所学课程的课程号(cno)进行求和作为判断的标志,课程号求和相等即为选课相同。后来发现了个问题,如果学生2选的课程为1,2,4,存在其他学生选课为2,5或者3,4时也会匹配成功被选中。后来增加限制条件a.sno not in (select distinct sno from sc where cno not in (select cno from sc where sno =2 ) 其中select distinct sno from sc where cno not in (select cno from sc where sno =2 )这句的意思是筛选出有选择除了1,2,4课程之外课程的学生学号(这些学生自然也就必定不符合要求)。
13.删除学习“王五”老师课的sc表记录
delete from sc where sc.cno in ( select course.cno from course,teacher where course.tno = teacher.tno and teacher.tname='王五')
14.向sc表中插入记录:将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩
INSERT INTO SELECT 语句从一个表复制数据,然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
insert into sc select sno, 3, (select avg(score) from sc where cno = 2) from student where sno not in (select sno from sc where cno = 3)
select sno as 学号 ,max(case when cno =1 then score end)as 企业管理 ,max(case when cno =2 then score end)as 马克思 ,max(case when cno =3 then score end)as UML ,max(case when cno =4 then score end)as 数据库 ,max(case when cno =5 then score end)as 物理 ,count(cno)as 课程数 ,avg(score) from sc group by sno order by avg(score) desc
16.查询各科成绩最高分和最低分:
以如下形式显示:课程号,课程名,最高分,最低分
select course.cno as 课程号 ,course.cname as 课程名 ,MAX(score) as 最高分 ,MIN(score) as 最低分 from sc,course where sc.cno=course.cno group by course.cno
17.按各科平均成绩从低到高和及格率的百分数从高到低顺序
select cno,avg(score), 100*sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率 from sc group by cno order by avg(score) ,及格率 desc;
18.查询如下课程平均成绩和及格率的百分数(用”1行”显示):
*企业管理(001),马克思(002),UML (003),数据库(004) *
select avg(case when cno = 1 then score end)as 平均分1, avg(case when cno = 2 then score end)as 平均分2, avg(case when cno = 3 then score end)as 平均分3, avg(case when cno = 4 then score end)as 平均分4, 100*sum(case when cno=1 and score >=60 then 1 else 0 end)/count(*) as 及格率1, 100*sum(case when cno=2 and score >=60 then 1 else 0 end)/count(*) as 及格率2, 100*sum(case when cno=3 and score >=60 then 1 else 0 end)/count(*) as 及格率3, 100*sum(case when cno=4 and score >=60 then 1 else 0 end)/count(*) as 及格率4 from sc
19.查询不同老师所教不同课程平均分, 从高到低显示
select t.tname as 教师,c.cname as 课程,avg(s.score) from teacher t,course c,sc s where t.tno = c.tno and c.cno = s.cno group by c.cno order by avg(s.score) desc
20.查询如下课程成绩均在第3名到第6名之间的学生的成绩:
– [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
用top
select top 6 max(a.sno) 学号, max(b.sname) 姓名, max(case when cno = 1 then score end) as 企业管理, max(case when cno = 2 then score end) as 马克思, max(case when cno = 3 then score end) as UML, max(case when cno = 4 then score end) as 数据库, avg(score) as 平均分 from sc a, student b where a.sno not in (select top 2 sno from sc where cno = 1 order by score desc) and a.sno not in (select top 2 sno from sc where cno = 2 order by scoredesc) and a.sno not in (select top 2 sno from sc where cno = 3 order by scoredesc) and a.sno not in (select top 2 sno from sc where cno = 4 order by scoredesc) and a.sno = b.sno group by a.sno
不过MySQL不支持Top,用的Limit代替
select max(a.sno)as 学号,max(b.sname)as 姓名, max(case when cno = 1 then score end) as 企业管理, max(case when cno = 2 then score end) as 马克思, max(case when cno = 3 then score end) as UML, max(case when cno = 4 then score end) as 数据库, avg(score) as 平均分 from sc a, student b where a.sno not in (select sc1.sno from (select sno from sc where cno = 1 order by score desc limit 2)as sc1) and a.sno not in (select sc2.sno from (select sno from sc where cno = 2 order by score desc limit 2)as sc2) and a.sno not in (select sc3.sno from (select sno from sc where cno = 3 order by score desc limit 2)as sc3) and a.sno not in (select sc4.sno from (select sno from sc where cno = 4 order by score desc limit 2)as sc4) and a.sno = b.sno group by a.sno limit 6
坑: MySQL子查询中直接写limit会报错This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'意思是,这版本的 MySQL 不支持使用 LIMIT 子句的 IN/ALL/ANY/SOME 子查询,即是支持非 IN/ALL/ANY/SOME 子查询的 LIMIT 子查询。
例如: select * from table where id in (select id from table limit 10) 是错误的写法
但是只要我们再裹一层就可以了(我真是无语啊) select * from table where id in (select t.id from (select * from table limit 10)as t)