经典mysql查询语言练习之拆分组合

上次去面试的时候，做笔试题发现mysql查询语句的题目写得很乱，涂涂改改的！！从现在开始，每天三条mysql查询语句，并且写下逻辑关系，找出逻辑规律进行拆分组合！！！！这素自己总结的方法哦

-----------------------------

表格如下：

student表

score表

course表

teacher表

#查询由“张旭“教师任课的课程的学生成绩。

拆解句子：主谓宾是（查询学生成绩）→select degree from score

这句话需要用到的表有，score（sno，cno，degree）、course（cno，cname，tno）、teacher（tno，tname）

流程：首先在teacher表中查张旭老师的tno，再根据tno去course里查cno，再根据cno去（查score里的degree）

句子:

①select tno from teacher where tname="张旭"

②select cno from course

③select degree from score

组合如下:(顺序是③②①)

 

select degree from score where cno in(select cno from course where tno in(select tno from teacher where tname="张旭"));

#查询选修某课程的同学人数多于5人的教师姓名 

拆解句子：主谓宾是（查询教师姓名）→select tname from teacher

这句话要用的表有score、course、teach

查询流程:在score表里计算选修cno的同学大于5的人数,找出了这些cno,再根据对应的cno去course里找到对应的tno,再根据tno(在teacher表里找tname)

①select cno from score group by cno having count(*)>5

②select tno from course 

③selct tname from teacher

组合:select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5));(顺序是③②①)

select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5));

 #查询出“计算机系“教师所教课程的成绩表

拆解句子：主谓宾（查询成绩）→select degree from score

查询流程：先在teacher表里找出计算机系老师对应的tno，再根据tno在course表里找到cno，再根据cno在score表里找degree

①select tno from teacher where depart="计算机系"

②select cno from course

③select degree from score

组合句子：select degree from score where cno in(select cno from course where tno in(select tno from teacher where depart="计算机系")

select degree from score where cno in(select cno from course where tno in(select tno from teacher where depart="计算机系")

#查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

拆解句子:主谓宾(查询cno,sno,degree)→select cno,sno,degree from student,score

查询流程:在score、student里查选修编号为“3-105”的同学的cno，sno，degree，把“3-105”同学的成绩大于所需要的成绩，要降序

①select cno，sno，degree from score，student where cno=“3-105”

②degree > any(select degree from score where cno="3-245")

③order by degree desc

组合：select cno，sno，degree from score where cno=“3-105” and degree > any(select degree from score where cno="3-245" order by degree desc;

select cno，sno，degree from score where cno=“3-105” and degree > any(select degree from score where cno="3-245" order by degree desc

#查询所有教师和同学的name、sex和birthday

分析：逻辑比较简单，主要用到一个功能：union

句子：select sname，sex，sbirthday from student union select tname，tsex，tbirthday from teacher；

select tname,tsex,tbirthday from Teacher union select sname,ssex,sbirthday from Student;

#查询所有“女”教师和“女”同学的name、sex和birthday.

select sname，ssex，sbirthday from student where ssex=“女” union select tname，tsex，tbirthday from teacher where tsex=“女”；

select sname,ssex,sbirthday from student where ssex="女" union select tname,tsex,tbirthday from teacher where tsex="女";

#查询成绩比该课程平均成绩低的同学的成绩表

拆解句子：主谓宾（查询成绩）→select degree from score

查询流程：先在score表里把sno一样的平均分算出来，再将score表里比cno平均分低的sno找出来，再在score表里把这些sno的degree找出来

②select avg（degree）from score

③select sno from score where degree < ..

④select degree from score 

组合：select degree from score where sno in(select sno from score where degree<(select avg(degree) from score);

select degree from score where sno in(select sno from score where degree<(select avg(degree) from score);

#查询所有未讲课的教师的Tname和Depart.

拆解句子：主谓宾（查询tname，depart）→select tname，depart from teacher

查询流程：先在course里看有哪些tno，再在score表里查看tno，不存在于score表的tno，在teacher表里查看这种tno的tname，depart

①select tno from course

②select tno from score

③tno from course where tno not in(select tno from score)

④select tname,depart from teacher

组合句子：select tname,depart from teacher where tno=(select tno from score));

select tname,depart from teacher where tno=(select tno from score));

#查询至少有2名男生的班号。

主谓宾（查询班号）→select class from student

流程：select class from student where ssex='男' group by class having count(*)>=1;

#查询Student表中不姓“王”的同学记录。

主谓宾（查询同学记录）→select * from student

逻辑：在student表中找出姓不是王的同学

句子：select * from student where sname not like “王%”

select * from student where sname not like “王%”

#询Student表中每个学生的姓名和年龄
select sname,year(now())-year(sbirthday) from student;

select sname,year(now())-year(sbirthday) from student;

#查询Student表中最大和最小的Sbirthday日期值。

select max(sbirthday),min(sbirthday) from student;

#查询“男”教师及其所上的课程 不太会用这种”=“的，需要好好了解下

主谓宾（查询教师姓名及课程）→select tname,cname from course

流程：先在teacher表里查询sex为男的tname和tno，再根据tno在course表里查找对应的cname

select Tname,Cname from course,teacher where course.tno= teacher.tno and teacher.Tsex='男'

select Tname,Cname from course,teacher where course.tno= teacher.tno and teacher.Tsex='男'

#查询最高分同学的Sno、Cno和Degree列

主谓宾（查询sno，cno和degree列）→select sno，cno，degree from score

逻辑：select sno，cno，degree from score where degree=(select max(degree) from score)

select sno，cno，degree from score where degree=(select max(degree) from score)

排序写法：

select sno,cno,degree from score order by degree desc limit 0,1;

select sno,cno,degree from score order by degree desc limit 0,1;

#查询和“李军”同性别的所有同学的Sname.

select sname from student where ssex=(select ssex from student where sname="李军");

select sname from student where ssex=(select ssex from student where sname="李军");

#查询和“李军”同性别并同班的同学Sname.

select sname from student where ssex=(ssex from student where sname="李军") and class=(select class from student where sname="李军");

select sname from student where ssex=(ssex from student where sname="李军") and class=(select class from student where sname="李军");

#查询所有选修“计算机导论”课程的“男”同学的成绩表。

主谓宾（查询成绩表）→select * from score

逻辑流程：在student表里找ssex为男的sno，在course表里找cname为计算机导论对应的cno，在score表里找sno和cno都符合的degree

①select sno from student where ssex=“男”

②select cno from course where cname=“计算机导论”

③select degree from score where ...

组合：select * from score where cno=(select cno from course where cname="计算机导论") and sno in(select sno from student where ssex="男");

select * from score where cno=(select cno from course where cname="计算机导论") and sno in(select sno from student where ssex="男");