2022-08-17 DQL—-子查询，日期格式

DQL查询语言子查询

按照结果集的行列数不同，子查询可以分为以下几类：

• 标量子查询：结果集只有一行一列（单行子查询）
• 列子查询：结果集有一列多行
• 行子查询：结果集有一行多列
• 表子查询：结果集多行多列

-- 查询比小虎年龄大的所有学生-- 标量子查询SELECT* FROMstudent WHEREage > ( SELECT age FROM student WHERE NAME = '小虎' );

-- 查询有一门学科分数大于90分的学生信息-- 列子查询SELECT* FROMstudent WHEREid IN (SELECTs_id FROMscores WHEREscore > 90);

-- 查询男生且年龄最大的学生-- 行子查询SELECT* FROMstudent WHEREage = (SELECTmax( age ) FROMstudent GROUP BYgender HAVINGgender = '男' )-- 优化SELECT* FROMstudent WHERE( age, gender ) = (SELECTmax( age ),gender FROMstudent GROUP BYgender HAVINGgender = '男' )

总结：

• where型子查询，如果是where 列 = （内层sql），则内层的sql返回的必须是单行单列，单个值。
• where型子查询，如果是where (列1,列2) = （内层sql），内层的sql返回的必须是单列，可以是多行。

-- 取排名数学成绩前五的学生，正序排列SELECT* FROM(SELECTs.*,sc.score score,c.NAME 科目 FROMstudent sLEFT JOIN scores sc ON s.id = sc.s_idLEFT JOIN course c ON c.id = sc.c_id WHEREc.NAME = '数学' ORDER BYscore DESC LIMIT 5 ) t WHEREt.gender = '男';

经验分享：

1. 分析需求
2. 拆步骤
3. 分步写sql
4. 整合拼装sql

-- 查询每个老师的代课数SELECT t.id, t.NAME,( SELECT count(*) FROM course c WHERE c.id = t.id ) AS 代课的数量 FROMteacher t;----------------------------------------------------------------------------SELECTt.id,t.NAME,count(*) '代课的数量' FROMteacher tLEFT JOIN course c ON c.t_id = t.id GROUP BYt.id,t.NAME;

-- existsSELECT* FROMteacher t WHEREEXISTS ( SELECT * FROM course c WHERE c.t_id = t.id );----------------------------------------------------------------------------SELECTt.*,c.`name` FROMteacher tINNER JOIN course c ON t.id = c.t_id;

总结：如果一个需求可以不用子查询，尽量不使用。

sql可读性太低。

需求

-- 3.查询每个同学的最高成绩和科目名称****SELECTt.id,t.NAME,c.id,c.NAME,r.score FROM(SELECTs.id,s.NAME,(SELECTmax( score ) FROMscores r WHEREr.s_id = s.id ) score FROMstudent s ) tLEFT JOIN scores r ON r.s_id = t.id AND r.score = t.scoreLEFT JOIN course c ON r.c_id = c.id;-- 5.查询每个课程的最高分的学生信息*****SELECT* FROMstudent s WHEREid IN (SELECT DISTINCTr.s_id FROM(SELECTc.id,c.NAME,max( score ) score FROMstudent sLEFT JOIN scores r ON r.s_id = s.idLEFT JOIN course c ON c.id = r.c_id GROUP BYc.id,c.NAME ) tLEFT JOIN scores r ON r.c_id = t.id AND t.score = r.score )

-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。SELECTs.id,s.NAME sname,sc.score,c.NAME FROMstudent sLEFT JOIN scores sc ON s.id = sc.s_idLEFT JOIN course c ON sc.c_id = c.id WHEREs.NAME LIKE '%张%' OR s.NAME LIKE '%李%';-- 7.查询平均成绩及格的同学的信息。（子查询）SELECT* FROMstudent WHEREid IN (SELECTsc.s_id FROMscores sc GROUP BYsc.s_id HAVINGavg( sc.score ) >= 70 )-- 8.将学生按照总分数进行排名。（从高到低）SELECTs.id,s.NAME,sum( sc.score ) score FROMstudent sLEFT JOIN scores sc ON s.id = sc.s_id GROUP BYs.id,s.NAME ORDER BYscore DESC,s.id ASC;-- 9.查询数学成绩的最高分、最低分、平均分。SELECTc.NAME,max( sc.score ),min( sc.score ),avg( sc.score ) FROMcourse cLEFT JOIN scores sc ON c.id = sc.c_id WHEREc.NAME = '数学';-- 10.将各科目按照平均分排序。SELECTc.id,c.NAME,avg( sc.score ) score FROMcourse cLEFT JOIN scores sc ON c.id = sc.c_id GROUP BYc.id,c.NAME ORDER BYscore DESC;

-- 11.查询老师的信息和他所带的科目的平均分SELECTt.id,t.NAME,c.id cid,c.NAME cname,avg( r.score ) FROMteacher tLEFT JOIN course c ON t.id = c.t_idLEFT JOIN scores r ON r.c_id = c.id GROUP BYt.id,t.NAME,c.id,c.NAME;-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分SELECTt.id,t.NAME,c.id cid,c.NAME cname,max( r.score ),min( r.score ) FROMteacher tLEFT JOIN course c ON t.id = c.t_idLEFT JOIN scores r ON r.c_id = c.id GROUP BYt.id,t.NAME,c.id,c.NAME HAVINGt.NAME IN ( 'Tom', 'Jerry' );-- 13.查询每个学生的最好成绩的科目名称（子查询）SELECTt.id,t.sname,r.c_id,c.NAME,t.score FROM(SELECTs.id,s.NAME sname,max( r.score ) score FROMstudent sLEFT JOIN scores r ON r.s_id = s.id GROUP BYs.id,s.NAME ) tLEFT JOIN scores r ON r.s_id = t.id AND r.score = t.scoreLEFT JOIN course c ON r.c_id = c.id;-- 14.查询所有学生的课程及分数SELECTs.id,s.NAME,c.id,c.NAME,r.score FROMstudent sLEFT JOIN scores r ON s.id = r.s_idLEFT JOIN course c ON c.id = r.c_id;-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）SELECT* FROMstudent s WHEREs.id IN (SELECTr.s_id FROMscores r WHEREr.c_id = 1 AND r.score > 60)--------------------------------------------------------SELECTs.*,r.* FROMstudent sLEFT JOIN scores r ON s.id = r.s_id WHEREr.c_id = 1 AND r.score > 60

-- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩SELECTs.id,s.NAME,t.score FROMstudent sLEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id WHEREt.score >= 70;-- 17.查询有不及格课程的学生信息SELECT* FROMstudent s WHEREid IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score )  65;

-- 21.查询有且仅有一门课程成绩在80分以上的学生信息SELECT* FROMstudent WHEREid IN ( SELECT r.s_id FROM scores r WHERE r.score > 80 GROUP BY r.s_id HAVING COUNT(*) = 1 );----------------------------------------------------------------------------SELECTs.id,s.NAME,s.gender FROMstudent sLEFT JOIN scores r ON s.id = r.s_id WHEREr.score > 80 GROUP BYs.id,s.NAME,s.gender HAVINGcount(*) = 1-- 22.查询出只有三门课程的学生的学号和姓名SELECT* FROMstudent s WHEREid IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING count(*) = 3 );----------------------------------------------------------------------------SELECTs.id,s.NAME,s.gender FROMstudent sLEFT JOIN scores r ON s.id = r.s_id GROUP BYs.id,s.NAME,s.gender HAVINGcount(*) = 3-- 23.查询有不及格课程的课程信息SELECT* FROMcourse c WHEREid IN (SELECTr.c_id FROMscores r GROUP BYr.c_id HAVINGmin( r.score ) < 60 )----------------------------------------------------------------------------SELECTc.id,c.NAME FROMcourse cLEFT JOIN scores sc ON c.id = sc.c_id GROUP BYsc.c_id,c.NAME HAVINGmin( sc.score ) = 4 )----------------------------------------------------------------------------SELECTs.id,s.NAME FROMstudent sLEFT JOIN scores r ON s.id = r.s_id GROUP BYs.id,s.NAME HAVINGcount(*) >= 4;-- 25.查询没有选全所有课程的同学的信息SELECT* FROMstudent WHEREid IN (SELECTr.s_id FROMscores r GROUP BYr.s_id HAVINGcount(*) != 5)

-- 26.查询选全所有课程的同学的信息SELECTs.id,s.NAME,count(*) number FROMstudent sLEFT JOIN scores r ON s.id = r.s_id GROUP BYs.id,s.NAME HAVINGnumber = ( SELECT count(*) FROM course );-- 27.查询各学生都选了多少门课SELECTs.id,s.NAME,count(*) number FROMstudent sLEFT JOIN scores r ON s.id = r.s_id GROUP BYs.id,s.NAME-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数SELECTs.id,s.NAME,r.score FROMstudent sLEFT JOIN scores r ON s.id = r.s_idLEFT JOIN course c ON r.c_id = c.id WHEREc.NAME = 'java' AND r.score < 60;-- 29.查询学过"Tony"老师授课的同学的信息SELECTs.id,s.NAME FROMstudent sLEFT JOIN scores r ON r.s_id = s.idLEFT JOIN course c ON c.id = r.c_idLEFT JOIN teacher t ON t.id = c.t_id WHEREt.NAME = 'Tom';-- 30.查询没学过"Tony"老师授课的学生信息SELECT* FROMstudent WHEREid NOT IN (SELECT DISTINCTs.id FROMstudent sLEFT JOIN scores r ON r.s_id = s.idLEFT JOIN course c ON c.id = r.c_idLEFT JOIN teacher t ON t.id = c.t_id WHEREt.NAME = 'Tom' )

日期格式