狂神数据库学习笔记

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