23
子查询
-- ========================where============================
-- 1.查询高等数学1所有考试结果(学号,科目编号,成绩),降序
-- 方式1:使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC
-- 方式2:使用子查询(由内到外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM result
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-1'
)
ORDER BY studentresult DESC
-- 查询科目为高等数学1,且成绩大于80分的学生(学号,姓名)
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE studentresult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1'
)
)
分组和过滤
-- 查询不同课程的平均分,最高分,最低分
-- 核心:根据不同课程分组
SELECT any_value(`subjectname`) AS 学科,AVG(studentresult) AS 平均分,max(studentresult) AS 最高分,min(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
GROUP BY r.subjectno -- 通过什么字段来分组
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:根据不同课程分组
SELECT any_value(`subjectname`) AS 学科,AVG(studentresult) AS 平均分,max(studentresult) AS 最高分,min(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分>80 -- 过滤分组记录必须满足的次要条件
MySQL函数
常用函数
-- =============常用函数==================
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 0到1之间随机数
SELECT SIGN(-10) -- 返回符号,0返回0,负数返回-1,正数返回1
-- 字符串
SELECT CHAR_LENGTH('我是说') -- 字符串长度
SELECT CONCAT('我','认识','你') -- 拼接字符串
SELECT INSERT('我认识你',2,2,'见过') -- 替换x位置长度为y的字符串‘我见过你’
SELECT INSERT('我认识你',2,0,'不') -- 插入x位置的字符串‘我不认识你’
SELECT LOWER(Ssr) -- 小写
SELECT UPPER(Ssr) -- 大写
SELECT INSTR('shanghai','h') -- 返回第一次出现子串的位置
SELECT REPLACE('那年十八','那','今') -- 替换出现的指定字符串
SELECT SUBSTR('12345678',3,3) -- 截取字符串,起始位置,截取数量
SELECT REVERSE('123') -- 反转
-- 查询姓周的同学,改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE student LIKE '周%'
-- 时间和日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT NOW() -- 时间
-- 系统
SELECT USER() -- 当前用户
SELECT VERSION() -- 当前版本
聚合函数
-- =================聚合函数=====================
-- 统计表中的数据
SELECT COUNT('studentno') FROM student; -- 会忽略所以的null值
SELECT COUNT(*) FROM student; -- 不会忽略null值,本质计算行数
SELECT COUNT(1) FROM student; -- 不会忽略null值,本质计算行数
SELECT SUM(`studentresult`) AS 总和 FROM result
SELECT avg(`studentresult`) AS 平均分 FROM result
SELECT max(`studentresult`) AS 最高分 FROM result
SELECT min(`studentresult`) AS 最低分 FROM result