MySQL学习笔记5：分页、排序、子查询与常用函数

1、前言

学习视频源自：【狂神说Java】MySQL最新教程通俗易懂

B站视频链接：https://www.bilibili.com/video/BV1NJ411J79W

【狂神说Java】MySQL最新教程通俗易懂

笔记为看视频自行撰写，仅限学习交流使用

1.1、上一篇文章

MySQL学习笔记4：MySQL的WHERE与JOIN语句

5、DQL查询数据

5.5、分页和排序

排序

-- limit  order by
-- 排序：升序ASC，降序DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 根据查询的结果 成绩降序 排序
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC

分页

-- 100万
-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好   瀑布流

-- 分页，每页只显示5条数据
-- 网页应用： 当前，总的页数，页面大小
-- 语法： LIMIT 起始值，页面的大小
-- LIMIT 0,5   1-5
-- LIMIT 1,5   2-6
-- LIMIT 6,5   7-12
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC
LIMIT 0,5

-- 第一页 LIMIT 0,5
-- 第二页 LIMIT 5,5
-- 第三页 LIMIT 10,5
-- 第N页  LIMIT pageSize*(N-1),pageSize
-- pageSize:页面大小
-- pageSize*(N-1)：起始值
-- n：当前页
-- 总页数：数据总数/页面大小

语法：LIMIT 查询起始下标,页面大小

-- 查询高等数学-1 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号、姓名、课程名称、分数）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE studentresult > 80 AND subjectname = '高等数学-1'
ORDER BY studentresult DESC
LIMIT 0,10

5.6、子查询

WHERE (值是固定的，这个值是计算出来的)

本质：在WHERE语句中嵌套一个子查询语句

WHERE （SELECT * FROM）

-- 1、查询高等数学-1的所有考试结果（学号、科目编号、成绩），，降序排列
-- 方式一：使用连接查询
SELECT studentno,r.subjectno,studentresult
FROM result r
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC

-- 方式二：使用子查询

-- 查询高等数学-1的科目编号
SELECT subjectno FROM subject WHERE subjectname = '高等数学-1'

SELECT studentno,subjectno,studentresult
FROM result
WHERE subjectno = (
	SELECT subjectno FROM subject 
    WHERE subjectname = '高等数学-1'
)
ORDER BY studentresult DESC

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE studentresult >= 80

-- 在这个基础上增加一个科目：高等数学-1
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE studentresult >= 80 AND subjectno = (
	SELECT subjectno FROM subject
    WHERE subjectname = '高等数学-1'
)

-- 查询课程为高等数学-1且分数不小于80分的同学的学号和姓名
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1' AND studentresult >= 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'
    )
)

-- 练习：查询课程为高等数学-1前5名同学的成绩的信息（学号、姓名、分数），使用子查询
SELECT s.studentno,studentname,studentresult FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE studentresult = (
	SELECT studentresult FROM result
    WHERE subjectno = (
    	SELECT subjectno FROM subject
        WHERE subjectname = '高等数学-1'
    )
)
ORDER BY studentresult DESC
LIMIT 0,5

5.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result r
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno --通过什么字段来分组
HAVING 平均分 > 80

6、MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

6.1、常用函数

-- 常用函数
-- 数学运算
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('我爱编程',1,2,'超级热爱') --替换，从某个位置开始替换某个长度
SELECT LOWER('Chen') --转小写字母
SELECT UPPER('Chen') --转大写字母
SELECT INSTR('chen','e') --返回第一次出现的字串的索引
SELECT REPLACE('方酱实质上是猪猪','猪猪','大猪猪') --替换出现的指定字符串
SELECT SUBSTR('方酱实质上是猪猪',4,2) -- 返回指定的子字符串(源字符串，截取的位置，截取的长度)
SELECT REVERSE('猪猪是上质实酱方') -- 反转

-- 查询姓张的同学，名字改为 章
SELECT REPLACE(studentname,'张','章') FROM student
WHERE studentname LIKE '张%'

-- 时间和日期函数 （记住）
SELECT CURRENT_DATE() --获取当前日期
SELECT CURDATE() --获取当前日期
SELECT NOW() --获取当前时间
SELECT LOCALTIME() --本地时间
SELECT SYSDATE() --系统时间

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()