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

DQL查询语言

子查询

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

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

1. -- 查询比小虎年龄大的所有学生
2. -- 标量子查询
3. SELECT
4.         *
5. FROM
6.         student
7. WHERE
8.         age > ( SELECT age FROM student WHERE NAME = '小虎' );

复制代码

1. -- 查询有一门学科分数大于90分的学生信息
2. -- 列子查询
3. SELECT
4.         *
5. FROM
6.         student
7. WHERE
8.         id IN (
9.         SELECT
10.                 s_id
11.         FROM
12.                 scores
13. WHERE
14.         score > 90);

复制代码

1. -- 查询男生且年龄最大的学生
2. -- 行子查询
3. SELECT
4.         *
5. FROM
6.         student
7. WHERE
8.         age = (
9.         SELECT
10.                 max( age )
11.         FROM
12.                 student
13.         GROUP BY
14.                 gender
15.         HAVING
16.         gender = '男'
17.         )
18.        
19. -- 优化
20. SELECT
21.         *
22. FROM
23.         student
24. WHERE
25.         ( age, gender ) = (
26.         SELECT
27.                 max( age ),
28.                 gender
29.         FROM
30.                 student
31.         GROUP BY
32.                 gender
33.         HAVING
34.         gender = '男'
35.         )

复制代码

总结：

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

1. -- 取排名数学成绩前五的学生，正序排列
2. SELECT
3.         *
4. FROM
5.         (
6.         SELECT
7.                 s.*,
8.                 sc.score score,
9.                 c.NAME 科目
10.         FROM
11.                 student s
12.                 LEFT JOIN scores sc ON s.id = sc.s_id
13.                 LEFT JOIN course c ON c.id = sc.c_id
14.         WHERE
15.                 c.NAME = '数学'
16.         ORDER BY
17.                 score DESC
18.                 LIMIT 5
19.         ) t
20. WHERE
21.         t.gender = '男';

复制代码

经验分享：

• 分析需求
  
• 拆步骤
  
• 分步写sql
  
• 整合拼装sql
  

1. -- 查询每个老师的代课数
2. SELECT t.id, t.NAME,( SELECT count(*) FROM course c WHERE c.id = t.id ) AS 代课的数量
3. FROM
4.         teacher t;
5. ----------------------------------------------------------------------------
6. SELECT
7.         t.id,
8.         t.NAME,
9.         count(*) '代课的数量'
10. FROM
11.         teacher t
12.         LEFT JOIN course c ON c.t_id = t.id
13. GROUP BY
14.         t.id,
15.         t.NAME;

复制代码

1. -- exists
2. SELECT
3.         *
4. FROM
5.         teacher t
6. WHERE
7.         EXISTS ( SELECT * FROM course c WHERE c.t_id = t.id );
8. ----------------------------------------------------------------------------SELECT
9.         t.*,
10.         c.`name`
11. FROM
12.         teacher t
13.         INNER JOIN course c ON t.id = c.t_id;       

复制代码

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

需求

1. -- 3.查询每个同学的最高成绩和科目名称****
2. SELECT
3.         t.id,
4.         t.NAME,
5.         c.id,
6.         c.NAME,
7.         r.score
8. FROM
9.         (
10.         SELECT
11.                 s.id,
12.                 s.NAME,(
13.                 SELECT
14.                         max( score )
15.                 FROM
16.                         scores r
17.                 WHERE
18.                         r.s_id = s.id
19.                 ) score
20.         FROM
21.                 student s
22.         ) t
23.         LEFT JOIN scores r ON r.s_id = t.id
24.         AND r.score = t.score
25.         LEFT JOIN course c ON r.c_id = c.id;
26. -- 5.查询每个课程的最高分的学生信息*****
27. SELECT
28.         *
29. FROM
30.         student s
31. WHERE
32.         id IN (
33.         SELECT DISTINCT
34.                 r.s_id
35.         FROM
36.                 (
37.                 SELECT
38.                         c.id,
39.                         c.NAME,
40.                         max( score ) score
41.                 FROM
42.                         student s
43.                         LEFT JOIN scores r ON r.s_id = s.id
44.                         LEFT JOIN course c ON c.id = r.c_id
45.                 GROUP BY
46.                         c.id,
47.                         c.NAME
48.                 ) t
49.                 LEFT JOIN scores r ON r.c_id = t.id
50.         AND t.score = r.score
51.         )

复制代码

1. -- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
2. SELECT
3.         s.id,
4.         s.NAME sname,
5.         sc.score,
6.         c.NAME
7. FROM
8.         student s
9.         LEFT JOIN scores sc ON s.id = sc.s_id
10.         LEFT JOIN course c ON sc.c_id = c.id
11. WHERE
12.         s.NAME LIKE '%张%'
13.         OR s.NAME LIKE '%李%';
14. -- 7.查询平均成绩及格的同学的信息。（子查询）
15. SELECT
16.         *
17. FROM
18.         student
19. WHERE
20.         id IN (
21.         SELECT
22.                 sc.s_id
23.         FROM
24.                 scores sc
25.         GROUP BY
26.                 sc.s_id
27.         HAVING
28.         avg( sc.score ) >= 70
29.         )
30. -- 8.将学生按照总分数进行排名。（从高到低）
31. SELECT
32.         s.id,
33.         s.NAME,
34.         sum( sc.score ) score
35. FROM
36.         student s
37.         LEFT JOIN scores sc ON s.id = sc.s_id
38. GROUP BY
39.         s.id,
40.         s.NAME
41. ORDER BY
42.         score DESC,
43.         s.id ASC;
44. -- 9.查询数学成绩的最高分、最低分、平均分。
45. SELECT
46.         c.NAME,
47.         max( sc.score ),
48.         min( sc.score ),
49.         avg( sc.score )
50. FROM
51.         course c
52.         LEFT JOIN scores sc ON c.id = sc.c_id
53. WHERE
54.         c.NAME = '数学';
55. -- 10.将各科目按照平均分排序。
56. SELECT
57.         c.id,
58.         c.NAME,
59.         avg( sc.score ) score
60. FROM
61.         course c
62.         LEFT JOIN scores sc ON c.id = sc.c_id
63. GROUP BY
64.         c.id,
65.         c.NAME
66. ORDER BY
67.         score DESC;

复制代码

1. -- 11.查询老师的信息和他所带的科目的平均分
2. SELECT
3.         t.id,
4.         t.NAME,
5.         c.id cid,
6.         c.NAME cname,
7.         avg( r.score )
8. FROM
9.         teacher t
10.         LEFT JOIN course c ON t.id = c.t_id
11.         LEFT JOIN scores r ON r.c_id = c.id
12. GROUP BY
13.         t.id,
14.         t.NAME,
15.         c.id,
16.         c.NAME;
17. -- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
18. SELECT
19.         t.id,
20.         t.NAME,
21.         c.id cid,
22.         c.NAME cname,
23.         max( r.score ),
24.         min( r.score )
25. FROM
26.         teacher t
27.         LEFT JOIN course c ON t.id = c.t_id
28.         LEFT JOIN scores r ON r.c_id = c.id
29. GROUP BY
30.         t.id,
31.         t.NAME,
32.         c.id,
33.         c.NAME
34. HAVING
35.         t.NAME IN ( 'Tom', 'Jerry' );
36. -- 13.查询每个学生的最好成绩的科目名称（子查询）
37. SELECT
38.         t.id,
39.         t.sname,
40.         r.c_id,
41.         c.NAME,
42.         t.score
43. FROM
44.         (
45.         SELECT
46.                 s.id,
47.                 s.NAME sname,
48.                 max( r.score ) score
49.         FROM
50.                 student s
51.                 LEFT JOIN scores r ON r.s_id = s.id
52.         GROUP BY
53.                 s.id,
54.                 s.NAME
55.         ) t
56.         LEFT JOIN scores r ON r.s_id = t.id
57.         AND r.score = t.score
58.         LEFT JOIN course c ON r.c_id = c.id;
59. -- 14.查询所有学生的课程及分数
60. SELECT
61.         s.id,
62.         s.NAME,
63.         c.id,
64.         c.NAME,
65.         r.score
66. FROM
67.         student s
68.         LEFT JOIN scores r ON s.id = r.s_id
69.         LEFT JOIN course c ON c.id = r.c_id;
70. -- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
71. SELECT
72.         *
73. FROM
74.         student s
75. WHERE
76.         s.id IN (
77.         SELECT
78.                 r.s_id
79.         FROM
80.                 scores r
81.         WHERE
82.         r.c_id = 1
83.         AND r.score > 60)
84. --------------------------------------------------------
85. SELECT
86.         s.*,
87.         r.*
88. FROM
89.         student s
90.         LEFT JOIN scores r ON s.id = r.s_id
91. WHERE
92.         r.c_id = 1
93.         AND r.score > 60

复制代码

1. -- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
2. SELECT
3.         s.id,
4.         s.NAME,
5.         t.score
6. FROM
7.         student s
8.         LEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id
9. WHERE
10.         t.score >= 70;
11. -- 17.查询有不及格课程的学生信息
12. SELECT
13.         *
14. FROM
15.         student s
16. WHERE
17.         id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 );
18. -- 18.查询每门课程有成绩的学生人数
19. SELECT
20.         c.id,
21.         c.NAME,
22.         t.number
23. FROM
24.         course c
25.         LEFT JOIN ( SELECT r.c_id, count(*) number FROM scores r GROUP BY r.c_id ) t ON c.id = t.c_id;
26. ----------------------------------------------------
27. SELECT
28.         c.id,
29.         c.NAME,
30.         count(*)
31. FROM
32.         course c
33.         LEFT JOIN scores r ON c.id = r.c_id
34. GROUP BY
35.         c.id,
36.         c.NAME;
37. -- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
38. SELECT
39.         c.id,
40.         c.NAME,
41.         avg( score ) score
42. FROM
43.         course c
44.         LEFT JOIN scores r ON c.id = r.c_id
45. GROUP BY
46.         c.id,
47.         c.NAME
48. ORDER BY
49.         score DESC,
50.         c.id ASC;
51. -- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
52. SELECT
53.         s.id,
54.         s.NAME sname,
55.         avg( r.score ) score
56. FROM
57.         student s
58.         LEFT JOIN scores r ON r.s_id = s.id
59.         LEFT JOIN course c ON c.id = r.c_id
60. GROUP BY
61.         s.id,
62.         s.NAME
63. HAVING
64.         score > 65;

复制代码

1. -- 21.查询有且仅有一门课程成绩在80分以上的学生信息
2. SELECT
3.         *
4. FROM
5.         student
6. WHERE
7.         id IN ( SELECT r.s_id FROM scores r WHERE r.score > 80 GROUP BY r.s_id HAVING COUNT(*) = 1 );
8. ----------------------------------------------------------------------------
9. SELECT
10.         s.id,
11.         s.NAME,
12.         s.gender
13. FROM
14.         student s
15.         LEFT JOIN scores r ON s.id = r.s_id
16. WHERE
17.         r.score > 80
18. GROUP BY
19.         s.id,
20.         s.NAME,
21.         s.gender
22. HAVING
23.         count(*) = 1
24. -- 22.查询出只有三门课程的学生的学号和姓名
25. SELECT
26.         *
27. FROM
28.         student s
29. WHERE
30.         id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING count(*) = 3 );
31. ----------------------------------------------------------------------------
32. SELECT
33.         s.id,
34.         s.NAME,
35.         s.gender
36. FROM
37.         student s
38.         LEFT JOIN scores r ON s.id = r.s_id
39. GROUP BY
40.         s.id,
41.         s.NAME,
42.         s.gender
43. HAVING
44.         count(*) = 3
45. -- 23.查询有不及格课程的课程信息
46. SELECT
47.         *
48. FROM
49.         course c
50. WHERE
51.         id IN (
52.         SELECT
53.                 r.c_id
54.         FROM
55.                 scores r
56.         GROUP BY
57.                 r.c_id
58.         HAVING
59.         min( r.score ) < 60
60.         )
61. ----------------------------------------------------------------------------
62. SELECT
63.         c.id,
64.         c.NAME
65. FROM
66.         course c
67.         LEFT JOIN scores sc ON c.id = sc.c_id
68. GROUP BY
69.         sc.c_id,
70.         c.NAME
71. HAVING
72.         min( sc.score ) < 60;
73. -- 24.查询至少选择4门课程的学生信息
74. SELECT
75.         *
76. FROM
77.         student
78. WHERE
79.         id IN (
80.         SELECT
81.                 r.s_id
82.         FROM
83.                 scores r
84.         GROUP BY
85.                 r.s_id
86.         HAVING
87.         count(*) >= 4
88.         )
89. ----------------------------------------------------------------------------
90. SELECT
91.         s.id,
92.         s.NAME
93. FROM
94.         student s
95.         LEFT JOIN scores r ON s.id = r.s_id
96. GROUP BY
97.         s.id,
98.         s.NAME
99. HAVING
100.         count(*) >= 4;
101. -- 25.查询没有选全所有课程的同学的信息
102. SELECT
103.         *
104. FROM
105.         student
106. WHERE
107.         id IN (
108.         SELECT
109.                 r.s_id
110.         FROM
111.                 scores r
112.         GROUP BY
113.                 r.s_id
114.         HAVING
115.         count(*) != 5
116.         )

复制代码

1. -- 26.查询选全所有课程的同学的信息
2. SELECT
3.         s.id,
4.         s.NAME,
5.         count(*) number
6. FROM
7.         student s
8.         LEFT JOIN scores r ON s.id = r.s_id
9. GROUP BY
10.         s.id,
11.         s.NAME
12. HAVING
13.         number = ( SELECT count(*) FROM course );
14. -- 27.查询各学生都选了多少门课
15. SELECT
16.         s.id,
17.         s.NAME,
18.         count(*) number
19. FROM
20.         student s
21.         LEFT JOIN scores r ON s.id = r.s_id
22. GROUP BY
23.         s.id,
24.         s.NAME
25. -- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
26. SELECT
27.         s.id,
28.         s.NAME,
29.         r.score
30. FROM
31.         student s
32.         LEFT JOIN scores r ON s.id = r.s_id
33.         LEFT JOIN course c ON r.c_id = c.id
34. WHERE
35.         c.NAME = 'java'
36.         AND r.score < 60;
37. -- 29.查询学过"Tony"老师授课的同学的信息
38. SELECT
39.         s.id,
40.         s.NAME
41. FROM
42.         student s
43.         LEFT JOIN scores r ON r.s_id = s.id
44.         LEFT JOIN course c ON c.id = r.c_id
45.         LEFT JOIN teacher t ON t.id = c.t_id
46. WHERE
47.         t.NAME = 'Tom';
48. -- 30.查询没学过"Tony"老师授课的学生信息
49. SELECT
50.         *
51. FROM
52.         student
53. WHERE
54.         id NOT IN (
55.         SELECT DISTINCT
56.                 s.id
57.         FROM
58.                 student s
59.                 LEFT JOIN scores r ON r.s_id = s.id
60.                 LEFT JOIN course c ON c.id = r.c_id
61.                 LEFT JOIN teacher t ON t.id = c.t_id
62.         WHERE
63.         t.NAME = 'Tom'
64.         )

复制代码

日期格式

格式描述%a缩写的星期名%b缩写月名%c月，数值%D带有英文前缀的月中的天%d月的天，数值（00-31）%e月的天，数值（0-31）%f微秒%H小时（00-23）%h小时（01-12）%I小时（01-12）%i分钟，数值（00-59）%j年的天（001-366）%k小时（0-23）%l小时（1-12）%M月名%m月，数值（00-12）%pAM或PM%r时间，12-小时 （hh:mm:ss AM或PM）%S秒（00-59）%s秒（0-59）%T时间，24-小时（hh:mm:ss）%U周（00-53）星期日是一周的第一天%u周（00-53）星期一是一周的第一天%W星期名%Y年，2022%y年，22
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！