mysql中select 的题型_MYSQL经典题型详情解析

学完了mysql后发现有很多地方不是很明白，于是总结了mysql的经典题型，不论是工作还是面试，我相信还是有一定帮助的。

例题一

​

在我的数据库中数据如下(排序有些差别，但是不影响结果)

​

分析：分两步解答

1)查询除了学号外其他数据重复时显示小的学号id

select MIN(id) id from tbl_students group by name,sax,age(6，7不应该显示，4要有注意，如果不按年龄分组就没有4出现)

​

2)最终答案如下(用到了子查询)

合并：delete from tbl_students

where id not in (select id from (select MIN(id) id from tbl_students group by name,sax) t )

扩展：

这个题型还有一点陷阱，初学者容易犯的两个错误

问题一：为什么这里需要加 别名t呢？

就是如果改成如下sql语句会怎么样呢？

delete from tbl_students

where id not in  (select id from (select MIN(id) id from tbl_students group by name,sax)  )

​

提示：每个派生表都必须有自己的别名。

与子查询不同，派生表必须具有别名，以便稍后在查询中引用其名称(不了解派生表的自己网上查，有很多文章)。

问题二：如果这样写会怎么样呢？

delete from tbl_students

where id not in  (select MIN(id) id from tbl_students group by name,sax )

这样的话会报一个异常如下

​

就是不能为FROM子句中的更新指定目标表“tbl_students”

在MySQL中，你不能修改在选择部分中使用的同一个表。你需要停止使用嵌套子查询，或者在两个部分执行操作，或者使用简单的where子句clause子句。

总结：如果是查询操作不会引发我说的这两个问题，有兴趣的可以尝试更新时会不会引发上面的问题。

题型二：

(要求查询出参加考试的各科成绩都高于60分，不管参加了多少科考试)

就是只显示及格的科目:

​

1.select username,score from tbl_score

where id not in (select id from tbl_score where score < 60)

扩展1：查询所有成绩都及格的学生

1)查出成绩不及格的学生名字

select  username  from tbl_score  where  score<60

​

结果：

select * from tbl_score where username not  in (select   username  from  tbl_score where score<60 )

​

题型三：

该题型涉及四张表 (注意：有些题型需要结合实际情况，适当添加或修改表中数据才显示最终效果，然后再改数据回来即可)

学生表 ：student

​      ​

sc成绩表

​              ​

1.查询“001”课程比“002”课程成绩高的所有学生的学号;

select  a.sid  from  (select sid,score from SC where cid='001') a,

(select sid,score  from SC where cid='002') b

where a.score>b.score  and  a.sid=b.sid; ( and  a.sid=b.sid 是进行过滤的，否则出现笛卡尔积)

2、查询平均成绩大于60分的同学的学号和平均成绩;

1)每一个学生的成绩

select sid ,avg(score)  from sc group by sid

​

2)having是在group by 的基础上进一步过滤

select  sid ,avg(score) from  sc  group by  sid   having  avg(score)>60

​

3、查询所有同学的学号、姓名、选课数、总成绩;

select   st.sid ,sName ,count(cid) as '选课数',sum(score) '总成绩'   from student st,sc  where st.sid=sc.sid group  by  st.sid

​

注意这是不完整的答案，应该用左连接或是右连接

select st.sid ,sName ,count(cid) as '选课数',sum(score) '总成绩' from  student st  LEFT JOIN  sc on  st.sid=sc.sid GROUP BY st.sid

​

4、查询姓“李”的老师的个数;

select count(tname) from teacher where tname like '李%' (如果查询含有李字的语句将'李%'  改为 '%李%' 即可)

如果考虑去掉名字一模一样的教师应该加distinct关键字

select count(distinct tname) from teacher where tname like '李%'

5、查询没学过“叶平”老师课的同学的学号、姓名

1)查询出叶平老师的课程cid

select   sc.sid   from   sc, course cc, teacher tt  where sc.cid=cc.cid and tt.tid=cc.tid and tt.tname='叶平'

​

2)select  st.sid,st.sname  from  student st where st.sid not in

(select sc.sid from sc,course cc,teacher tt where sc.cid=cc.cid and tt.tid=cc.tid and tt.tname='叶平')

​

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

select sc.sid from sc where cid='001' and cid='002'

结果为多少呢？为null.

方式一：

select  st.sid,st.sName from sc ,student st，(select sid from sc where cid ='001') a,

(select sid from sc where cid ='002') b where sc.sid=st.sid and st.sid=a.sid and st.sid=b.sid group by st.sid

方式二：(推荐)

select  s.sid,s.sName

from student s,    (select  sid,count(cid) from scwhere  cid  in ('001','002')group by sid baving count(cid)>=2) t

where  s.sid = t.sid

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

1)叶平老师教的课程编号cid

select distinct sc.cid  from sc , course cou ,teacher tt where sc.cid =cou.cid and cou.tid=tt.tid and tt.tname='叶平'

​

2) 此处叶平老师只有一门课 可以写成如下

select  st.sid,st.sname  from  student st ,sc where st.sid=sc.sid and sc.cid=

(select distinct sc.cid from sc , course cou ,teacher tt where sc.cid =cou.cid and cou.tid=tt.tid and tt.tname='叶平')

但是假如叶平老师的课程是多个的话就不能这样做了，应该是

如下方法错在哪？

select st.sid,st.sname from student st ,sc ,(select distinct sc.cid from sc , course cou ,teacher tt where sc.cid =cou.cid and cou.tid=tt.tid and tt.tname='叶平')  a

where st.sid=sc.sid

and sc.cid in (a.cid)

group by st.sid having count(sc.cid)=count(a.cid)  ???????

方式二：

where sid in (

select sid from SC ,Course ,Teacher where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname='叶平' group by sid having count(SC.cid)=

(select count(cid) from Course,Teacher where Teacher.tid=Course.tid and Tname='叶平')

);

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

1)查出课程编号“002”的成绩比课程编号“001”课程低的学号sid

select  a.sid from (select sid ,score from sc where cid ='001') a,(select sid,score  from sc where cid ='002') b

where a.sid=b.sid

and  b.score

​

select st.sid,st.sname from student st ,sc

where st.sid=sc.sid

and st.sid in (select  a.sid from (select sid ,score from sc where cid ='001') a,(select sid,score  from sc where cid ='002') b

where a.sid=b.sid

and b.score

​

9、查询所有课程成绩小于60分的同学的学号、姓名;

结果答案很多为什么？

select tt.sid,tt.sname from student tt ,(select sid ,score from sc group by sid having score>60) a

where  tt.sid  not in (a.sid)

正确：

select tt.sid,tt.sname from student tt

where  tt.sid  not in (select sid  from sc where score>60 GROUP BY sid  )

10、查询没有学全所有课的同学的学号、姓名;

select  tt.sid,tt.sname  from student tt,sc

where tt.sid=sc.sid

group  by sid having count(sc.cid)

​

tt.sid=sc.sid有这个条件这里没有课的学生没有选上

正解

1)找出学生课程数等于总课程数的学生sid

select sid from sc   group  by sid having count(cid)=(select count(cid) from course)

综合：

select tt.sid,tt.sname from student tt,sc where tt.sid not in

(select sid from sc   group  by sid having count(cid)=(select count(cid) from course))

group  by  tt.sid

10、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;

select tt.sid,tt.sname from student tt,sc

where tt.sid=sc.sid

and sc.sid in (select sid from sc where cid='001')

group by tt.sid

11、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;

select tt.sid,tt.sname from student tt,sc where tt.sid=sc.sid

and sc.cid in (select cid from sc where sid='2')

group by tt.sid,tt.sName having count(sc.cid)=(select count(cid) from sc where sid='2')

存在问题：假如sid=2这个学生选了英语和数学，那么其他学生如果这两门课都选了还选了其他课程的话也会被加进来

​

执行SQL结果显示

​

12、删除学习“叶平”老师课的SC表记录;

delete from sc where cid in

(select cid from course cc ,teacher tt

where cc.tid=tt.tid

and tname='叶平'

)

13、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，

按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

select sid as '学生ID'

,(select score from sc where sc.sid=tt.sid and cid='004') as '数据库'

,(select score from sc where sc.sid=tt.sid and cid='001') as '企业管理'

,(select score from sc where sc.sid=tt.sid and cid='005') as '英语'

,count(*) as '有效课程数', avg(tt.score) as '有效平均分'

from sc as tt

group by sid

order by avg(tt.score)

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

select cid as '课程ID',max(score) as '最高分',min(score) as '最低分' from sc  group by  cid

15.查询不同老师所教不同课程平均分从高到低显示 要求显示：教师ID,教师姓名，课程ID,课程名称，平均成绩

select tt.tid as '教师ID',tt.tname as '教师名称' ,cc.cid as '课程ID' ,cc.cname as '课程名称' ,avg(score)

from sc ,course cc ,teacher  tt

where sc.cid=cc.cid

and cc.tid=tt.tid

group by  tt.tid

order by  avg(score) desc

16.统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select cc.cid as '课程ID',cc.cname as '课程名称'

,(select count(*) from sc where score >=85 and score <=100 and sc.cid=cc.cid ) as '[100-85]'

,(select count(*) from sc where score >=70 and score <85 and sc.cid=cc.cid) as '[85-70]'

,(select count(*) from sc where score >=60 and score <70 and sc.cid=cc.cid) as '[70-60]'

,(select count(*) from sc where score <60 and sc.cid=cc.cid) as '[<60]'

from sc ,course cc where sc.cid =cc.cid group by cc.cid

17.查询每门课程被选修的学生数

select cid,count(*) from sc group by cid

18、查询出只选修了一门课程的全部学生的学号和姓名

select tt.sid ,tt.sname from sc ,student tt where sc.sid=tt.sid

group by sid

having count(cid)=(select cid from sc group by sid having count(cid)=1)

19、查询男生、女生人数

select sSex,count(*) as '人数' from student  group by sSex

20、查询同名同性学生名单，并统计同名人数

select sSex,sname from student group by sSex,sname having count(*)>1

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

select avg(score) from sc group by cid order by avg(score),cid desc

22、查询平均成绩大于75的所有学生的学号、姓名和平均成绩

select ss.sid,ss.sname,avg(score) from student ss,sc where ss.sid=sc.sid group by ss.sid having avg(score)>75

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

select ss.sname,sc.score from student ss ,sc ,course cc

where ss.sid=sc.sid

and sc.cid=cc.cid

and cc.cname='数据库'

and sc.score<60

24、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

select ss.sname,cc.cname ,sc.score from student ss ,sc ,course cc

where ss.sid=sc.sid

and sc.cid=cc.cid

and sc.score>=70

25、查询不及格的课程，并按课程号从大到小排列

select cid from sc where score<60 order by cid desc

26、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;

select tt.sid,tt.sname from student tt ,sc where tt.sid=sc.sid

and cid='003'

and sc.score>=70

27、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select tt.sid,tt.sname ,max(score) from student tt,sc

where tt.sid=sc.sid

and sc.cid=(select distinct cc.cid from sc,course cc ,teacher ss where sc.cid=cc.cid and cc.tid=ss.tid and ss.tname='叶平')

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

select cid, count(*) from sc group by cid

29、查询不同课程成绩相同的学生的学号、课程号、学生成绩

该题目用到了自联结查询

select a.sid,a.cid ,a.score from sc a ,sc b

where a.score=b.score

and a.cid <> b.cid

30、查询每门功课成绩最好的前两名

select  *

from sc t1

where (

select count(*)

from sc t2

where t1.cid=t2.cid

and  t2.score>=t1.score

) <=2

order  by  score desc

31、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select  cid ,count(*) from sc group by cid having count(*) >=2 order by count(*) desc ,cid

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

select sid ,count(*) from sc group by sid having count(*)>=2

33、查询两门以上不及格课程的同学的学号及其平均成绩

select sid ,avg(score) from sc where score <60 group by sid having count(cid) >=2