子查询、日期格式
DQL查询语言
子查询
按照结果集的行列数不同，子查询可以分为以下几类：
- 标量子查询：结果集只有一行一列（单行子查询）
- 列子查询：结果集有一列多行
- 行子查询：结果集有一行多列
- 表子查询：结果集多行多列
-- 查询比小虎年龄大的所有学生
-- 标量子查询
SELECT
*
FROM
student
WHERE
age > ( SELECT age FROM student WHERE NAME = '小虎' );
-- 查询有一门学科分数大于90分的学生信息
-- 列子查询
SELECT
*
FROM
student
WHERE
id IN (
SELECT
s_id
FROM
scores
WHERE
score > 90);
-- 查询男生且年龄最大的学生
-- 行子查询
SELECT
*
FROM
student
WHERE
age = (
SELECT
max( age )
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
-- 优化
SELECT
*
FROM
student
WHERE
( age, gender ) = (
SELECT
max( age ),
gender
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
总结：
- where型子查询，如果是where 列 = （内层sql），则内层的sql返回的必须是单行单列，单个值。
- where型子查询，如果是where (列1,列2) = （内层sql），内层的sql返回的必须是单列，可以是多行。
-- 取排名数学成绩前五的学生，正序排列
SELECT
*
FROM
(
SELECT
s.*,
sc.score score,
c.NAME 科目
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
c.NAME = '数学'
ORDER BY
score DESC
LIMIT 5
) t
WHERE
t.gender = '男';
经验分享：
- 分析需求
- 拆步骤
- 分步写sql
- 整合拼装sql
-- 查询每个老师的代课数
SELECT t.id, t.NAME,( SELECT count(*) FROM course c WHERE c.id = t.id ) AS 代课的数量
FROM
teacher t;
t.*,
c.name
FROM
teacher t
INNER JOIN course c ON t.id = c.t_id;
总结：如果一个需求可以不用子查询，尽量不使用。
sql可读性太低。
需求
-- 3.查询每个同学的最高成绩和科目名称****
SELECT
t.id,
t.NAME,
c.id,
c.NAME,
r.score
FROM
(
SELECT
s.id,
s.NAME,(
SELECT
max( score )
FROM
scores r
WHERE
r.s_id = s.id
) score
FROM
student s
) t
LEFT JOIN scores r ON r.s_id = t.id
AND r.score = t.score
LEFT JOIN course c ON r.c_id = c.id;
-- 5.查询每个课程的最高分的学生信息*****
SELECT
*
FROM
student s
WHERE
id IN (
SELECT DISTINCT
r.s_id
FROM
(
SELECT
c.id,
c.NAME,
max( score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
GROUP BY
c.id,
c.NAME
) t
LEFT JOIN scores r ON r.c_id = t.id
AND t.score = r.score
)
-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
SELECT
s.id,
s.NAME sname,
sc.score,
c.NAME
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON sc.c_id = c.id
WHERE
s.NAME LIKE '%张%'
OR s.NAME LIKE '%李%';
-- 7.查询平均成绩及格的同学的信息。（子查询）
SELECT
*
FROM
student
WHERE
id IN (
SELECT
sc.s_id
FROM
scores sc
GROUP BY
sc.s_id
HAVING
avg( sc.score ) >= 70
)
-- 8.将学生按照总分数进行排名。（从高到低）
SELECT
s.id,
s.NAME,
sum( sc.score ) score
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
GROUP BY
s.id,
s.NAME
ORDER BY
score DESC,
s.id ASC;
-- 9.查询数学成绩的最高分、最低分、平均分。
SELECT
c.NAME,
max( sc.score ),
min( sc.score ),
avg( sc.score )
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
WHERE
c.NAME = '数学';
-- 10.将各科目按照平均分排序。
SELECT
c.id,
c.NAME,
avg( sc.score ) score
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
c.id,
c.NAME
ORDER BY
score DESC;
-- 11.查询老师的信息和他所带的科目的平均分
SELECT
t.id,
t.NAME,
c.id cid,
c.NAME cname,
avg( r.score )
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
LEFT JOIN scores r ON r.c_id = c.id
GROUP BY
t.id,
t.NAME,
c.id,
c.NAME;
-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
SELECT
t.id,
t.NAME,
c.id cid,
c.NAME cname,
max( r.score ),
min( r.score )
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
LEFT JOIN scores r ON r.c_id = c.id
GROUP BY
t.id,
t.NAME,
c.id,
c.NAME
HAVING
t.NAME IN ( 'Tom', 'Jerry' );
-- 13.查询每个学生的最好成绩的科目名称（子查询）
SELECT
t.id,
t.sname,
r.c_id,
c.NAME,
t.score
FROM
(
SELECT
s.id,
s.NAME sname,
max( r.score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
GROUP BY
s.id,
s.NAME
) t
LEFT JOIN scores r ON r.s_id = t.id
AND r.score = t.score
LEFT JOIN course c ON r.c_id = c.id;
-- 14.查询所有学生的课程及分数
SELECT
s.id,
s.NAME,
c.id,
c.NAME,
r.score
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
LEFT JOIN course c ON c.id = r.c_id;
-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
SELECT
*
FROM
student s
WHERE
s.id IN (
SELECT
r.s_id
FROM
scores r
WHERE
r.c_id = 1
AND r.score > 60)
SELECT
c.id,
c.NAME,
count(*)
FROM
course c
LEFT JOIN scores r ON c.id = r.c_id
GROUP BY
c.id,
c.NAME;
-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
SELECT
c.id,
c.NAME,
avg( score ) score
FROM
course c
LEFT JOIN scores r ON c.id = r.c_id
GROUP BY
c.id,
c.NAME
ORDER BY
score DESC,
c.id ASC;
-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT
s.id,
s.NAME sname,
avg( r.score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
GROUP BY
s.id,
s.NAME
HAVING
score > 65;
-- 21.查询有且仅有一门课程成绩在80分以上的学生信息
SELECT
*
FROM
student
WHERE
id IN ( SELECT r.s_id FROM scores r WHERE r.score > 80 GROUP BY r.s_id HAVING COUNT(*) = 1 );
SELECT
s.id,
s.NAME,
s.gender
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME,
s.gender
HAVING
count(*) = 3
-- 23.查询有不及格课程的课程信息
SELECT
*
FROM
course c
WHERE
id IN (
SELECT
r.c_id
FROM
scores r
GROUP BY
r.c_id
HAVING
min( r.score ) < 60
)
SELECT
s.id,
s.NAME
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME
HAVING
count(*) >= 4;
-- 25.查询没有选全所有课程的同学的信息
SELECT
*
FROM
student
WHERE
id IN (
SELECT
r.s_id
FROM
scores r
GROUP BY
r.s_id
HAVING
count(*) != 5
)
-- 26.查询选全所有课程的同学的信息
SELECT
s.id,
s.NAME,
count(*) number
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME
HAVING
number = ( SELECT count(*) FROM course );
-- 27.查询各学生都选了多少门课
SELECT
s.id,
s.NAME,
count(*) number
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME
-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT
s.id,
s.NAME,
r.score
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
LEFT JOIN course c ON r.c_id = c.id
WHERE
c.NAME = 'java'
AND r.score < 60;
-- 29.查询学过"Tony"老师授课的同学的信息
SELECT
s.id,
s.NAME
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
LEFT JOIN teacher t ON t.id = c.t_id
WHERE
t.NAME = 'Tom';
-- 30.查询没学过"Tony"老师授课的学生信息
SELECT
*
FROM
student
WHERE
id NOT IN (
SELECT DISTINCT
s.id
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
LEFT JOIN teacher t ON t.id = c.t_id
WHERE
t.NAME = 'Tom'
)
日期格式
格式 描述 %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） %p AM或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
Original: https://www.cnblogs.com/YQuicksilver/p/16610950.html
Author: QuickSilver
Title: 2022-08-17 DQL—-子查询，日期格式
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/611097/
转载文章受原作者版权保护。转载请注明原作者出处！