第61天学习打卡（MySQL 联表查询 分页和排序 子查询 分组和过滤 MySQL函数）
-- ================= 联表查询join=============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result
/*
思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentno = 成绩表 studentno
*/
-- join（连接的表） on（判断的条件） 连接查询
-- where 等值查询
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno
-- on 是先筛选后关联，where是先关联后筛选
-- on 用于批量，where用于单个
-- Right Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
-- Left Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL
-- 思考题（查询了参加考试的同学信息：学号 学生姓名 科目名 分数）
/*
思路
1.分析需求，分析查询的字段来自哪些表，student result subject（连接查询）
2.确定使用哪种连接查询 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentno = 成绩表 studentno
*/
SELECT s.studentno,studentname,subjectname,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
-- 我要查询哪些数据 select...
-- 从哪几个表中查 FROM 表 XXX Join 连接的表 on 交叉条件
-- 假设存在多张表查询，慢慢来，先查询两张表然后再慢慢增加
-- FROM a left join b 以a表为基准
-- FROM a right join b 以b表为基准
自连接
自己的表和自己的表连接。核心：一张表拆为两张一样的表即可
父类
| categoryid | categoryname |
|---|---|
| 2 | 信息技术（1为顶级id） |
| 3 | 软件开发（1为顶级id） |
| 5 | 美术技术（1为顶级id） |
子类
| pid(父id) | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
子类的pid 等于父类的categoryid
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术技术 | ps技术 |
-- ============== 自连接=====================
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`) )
ENGINE=INNODB AUTO_INCREMENT = 9 DEFAULT CHARSET=utf8 ;
INSERT IGNORE INTO `category` (`categoryid`, `pid`, `categoryname`)
VALUES ('9', '1', '信息技术'),
('3', '1', '软件开发'),
('5', '1', '美术设计'),
('4', '3', '数据库'),
('8', '2', '办公信息'),
('6', '3', 'web开发'),
('7','5', 'ps技术');
INSERT IGNORE INTO 要加 IGNORE 不然会报错
-- 查询父子信息 : 把一张表看为两个一模一样的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname`AS `子栏目`
FROM `category`AS a, `category`AS b
WHERE a.`categoryid` = b.`pid`
-- 查询学员所属的年纪（学号，学生的姓名，年纪名称）
SELECT studentno,studentname,`gradename`
FROM student AS s
INNER JOIN `grade` g
ON s.`gradeid` =g.`gradeid`
-- 查询科目所属的年纪（科目名称，年纪名称）
SELECT `subjectname`,`gradename`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`gradeid` = g.`gradeid`
-- 查询了参加数据库结构-1考试的同学信息：学号 学生姓名 科目名 分数）
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'
Select完整的语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field[as alias1][,table.field2[as alias2]][...]]}
FROM table_name[as table_alias]
[left | right | inner join table_nmae2]-- 联合查询
[WHERE ...] -- 指定结果需要满足的条件
[GROUP BY...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] --指定查询记录按一个或多个条件排序
[LIMIT{[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条到哪条
注意：[]括号代表可选的，{}括号代表必须选的 语法里面的顺序不能变
4.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万
-- 为什么要分页
-- 缓解数据库压力 给人的体验更好， 瀑布流（一般用于图片）
-- 语法： limit 起始值，页面的大小
-- 网页的应用：当前页，总的页数，页面的大小
-- LIMIT 0,5 1~5 从最开始的值到五号
-- LIMIT 1,5 1~5 从一到五号
-- LIMIT 5,5 从第二页开始值到第五号（第二页的五号）
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 ASC
LIMIT 5,5
-- 第1页 limit 0,5 (1-1)*5
-- 第2页 limit 5,5 (2-1)*5
-- 第3页 limit 10,5 (3-1)*5
-- 第4页 limit N,5 (n-1)* pagesize , pagesize
-- [pagesize: 页面大小， （n-1）*pagesize 起始值，n 当前页]
-- 【数据总数/页面大小 = 总页数】
语法: limit(查询起始下标，apgesize)
-- 查询JAVA第一学年课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE subjectname = 'JAVA第一学年' AND studentresult>=80
ORDER BY studentresult DESC
LIMIT 0,10
4.6子查询
where(值是固定的)
现在想要 where (这个值是计算出来的)
-- ================== where==========
-- 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
-- 方式二：使用子查询（由里及外）
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE subjectno = (
SELECT subjectno FROM `subject`
WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC
-- 查询所有数据库结构-1的学生学号
SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构-1'
-- 查询课程为高等数学-2 且分数不小于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` = '高等数学-2' AND studentresult>=80
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE `studentresult`>=80
-- 在这个基础上增加一个科目，高等数学-2
-- 查询 高等数学-2 的编号
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE `studentresult`>=80 AND `subjectno` = (
SELECT subjectno FROM `subject`
WHERE `subjectname` = '高等数学-2'
)
-- 嵌套再改造（由里及外） 没有报错就可以不用飘
SELECT DISTINCT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE studentresult>80 AND subjectno = (
SELECT subjectno FROM `subject` WHERE `subjectname` = '高等数学-2'
)
)
4.7分组和过滤
-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
SELECT ANY_VALUE (`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
5.MySQL 函数
官网：https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html
5.1常用函数
-- ============================ 常用函数 ===========
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1 之间的随机数
SELECT SIGN(-13) -- 判断一个数的符号 0返回0 负数返回-1，正数返回 1
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('顺','顺','利','利')
SELECT INSERT('我爱编程helloword',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('KUangShen') -- 大写转换为小写字母
SELECT UPPER('kuangshen') -- 小写字母转换为大写字母
SELECT INSTR('kuangshen','h') -- 返回第一次出现的子串的索引
SELECT REPLACE('狂神说坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('狂神说坚持就能成功',4,6) -- 返回指定的字符串（源字符串，截取的位置，截取的长度） 这里返回的是第4个开始 截取六个字
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()
5.2聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| AVG() | 平均值 |
| .... |
where 后的条件表达式不允许聚合函数
-- ==============聚合函数===========
-- 都能够统计 表中的数据 (想查询一个表中有多少个记录，就使用这个count())
-- COUNT(字段)， 会忽略所有的null值
-- count(*) 不会忽略所有的null值 本质计算行数
-- count(1) 不会忽略所有的null值 本质计算行数
SELECT COUNT(studentname) FROM student; -- count(指定列)
SELECT COUNT(*) FROM student; -- count(*)(可以查有多少人) 可以查总数据
SELECT COUNT(1) FROM student; -- count(1) (可以查有多少人)可以查总数据
SELECT COUNT(1) FROM result;
SELECT COUNT(borndate) FROM student;
SELECT SUM(`studentresult`) AS 总和 FROM result
SELECT AVG(`studentresult`) AS 平均分 FROM result
SELECT MAX(`studentresult`) AS 最高分 FROM result
SELECT MIN(`studentresult`) AS 最低分 FROM result
-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
-- SELECT ANY_VALUE (`subjectname`) 高版本的要加ANY_VALUE
SELECT ANY_VALUE (`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
浙公网安备 33010602011771号