4、DQL查询数据（最重点）
4.1、DQL
（Data query Language：数据查询语言）
- 所有的查询操作都用它 Select
- 简单的查询，复杂的查询它都能做
- 数据库中最核心的语言，最重要的语句
- 使用频率最高的语句
select完整语法
select语法
SELECT[ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]rom_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
4.2、指定查询字段
-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM `student`
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名，给结果换一个名字 AS 可以给字段起别名，也可以给表起别名
-- SELECT 字段 AS 别名 FROM 表 AS 表名；
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生
-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student
语法：SELECT 字段1,...FROM 表
有的时候，列名字不是那么的见名知意。我们起别名 AS ；字段名 A 别名 表名 AS 别名
去重 distinct
作用：去除 select 语句查询出来的结果重复的数据，重复的数据只显示一条
-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM `result` -- 查询全部的考试成绩
SELECT `StudentNo` FROM `result` -- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM `result` -- 发现重复数据，可以去重
数据库的列
SELECT VERSION() -- 查询本系统的版本 （函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算 （表达式）
SELECT @@auto_increment_increment -- 查询自增的步长 （变量）
-- 学院考试成绩加一分
SELECT `StudentNo`,`StudentResult`+80 AS '加分后' FROM `result`
数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量…
select表达式 from 表
4.3、where 条件字句
作用：检索数据中符合条件的值
搜索的条件有一个或者多个表达式注成！结果 布尔值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与，两个结果都为真，结果为真 |
| or || | a or b a||b | 逻辑或，任意一个为真，则结果为真 |
| Not ! | not a !a | 逻辑非，真为假，假为真！ |
尽量使用英文字母
-- ========================= where ==============
SELECT `StudentNo`,`StudentResult` FROM `result`
-- 查询成绩在8-9之间的
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`>=8 AND `StudentResult`<=9
-- && AND
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`>=8 && `StudentResult`<=9
-- 模糊查询（区间）
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult` BETWEEN 7 AND 9
-- 查询学号不等于1000的学生的成绩
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentNo`!=1000;
-- != not
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE NOT `StudentNo`=1000;
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果操作符为 null，结果为真 |
| IS NOT NULL | a is null | 如果操作符不为 null，结果为真 |
| BETWEEN | a between b and c | 若a在b和c之间，则结果为真 |
| like | a like b | SQL匹配，如果a匹配b，结果为真 |
| In | a in （a1,a2,a3…） | 假设a在a1，或者a2…其中的某一个值中，结果为真 |
-- ========================= 模糊查询 =========================================
-- 查询名字中带白的同学
-- like结合 %(代表0到任意个字符) _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '%白' OR `StudentName`LIKE'%黑'
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE'_黄'
-- ==== in(具体的一个或者多个值) =====================
-- 查询101 102 103
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentNo` IN(101,202,303)
-- 查询在河南的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `address` IN ('河南省周口市西华县东夏镇')
-- ======null not null============
-- 查询地址为空的学生 null''
SELECT `StudentNo`,`StudentName`,`Address` FROM `student`
WHERE `Address`='' OR `Address` IS NULL
-- 查询有出生日期的同学 不为空
SELECT `StudentNo`,`StudentName`,`BornDate`FROM`student`
WHERE `BornDate` IS NOT NULL
-- 查询没有出生日期的同学 为空
SELECT `StudentNo`,`StudentName`,`BornDate`FROM`student`
WHERE `BornDate`IS NULL
4.4、联表查询
JOIN 对比
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-no4oQXsr-1625484993577)(MySQL.assets/oscimg.oschina.net&app=2002&size=f9999,10000&q=a80&n=0&g=0n&fmt=jpeg)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q4jAikfc-1625484993580)(MySQL.assets/www.west.cn&app=2002&size=f9999,10000&q=a80&n=0&g=0n&fmt=jpeg)]
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
-- ============联表查询 join =====================
-- 查询参加了考试的同学（学号，姓名，课程编号，分数s)
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
-- 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.分析需求，分析查询的字段来自哪些表，result student subject result（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断条件：学生表中的 studentNo = 成绩表中的 student
*/
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 b right Join a 以b表为基准
==========================================================================
/*
1.首先确定需要查询三张表 result student subject
2.要查询的数据为 s.StudentNo,`StudentName`,`SubjectName`,`StudentResult`
使用from student s Left Join result r 以右表result为基准 --student与result都有studentName
*/
-- 先查询两张表，哪个表和第三张表也有相同数据，就以哪个表为基准
-- result表中与另外两张表都有相同的数据,并且要查询参加考试的同学，所以以result成绩表为基准
-- 先查询student 与 result。
SELECT s.studentNo,studentName,studentResult,subjectName -- 要查询的数据
FROM student s -- 从表
RIGHT JOIN result r -- 主表 右
ON s.studentNo = r.studentNo -- 交叉点
INNER JOIN `subject` sub -- 再查询 result 与 subject。用inner join 如果表中至少有一个匹配，就返回行
ON r.subjectNo = sub.subjectNo -- 交叉点
自连接（了解即可）
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
父类：
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类：
| pid | categoryid | categorName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | wed开发 |
| 5 | 7 | ps设计 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | wed开发 |
| 美术设计 | ps技术 |
4.5、分页和排序
排序
-- 排序：升序 ASC ，降序 DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询的结果根据 成绩降序 排序
SELECT s.`StudentNo`,`StudentName`,`StudentResult`,`SubjectName`
FROM student s
RIGHT JOIN result r
ON s.`StudentNo` = r.studentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
ORDER BY `StudentResult` ASC
分页
-- 当数据过多时，比如100万条
-- 根据实际情况分页？
-- 缓解数据库压力，给人的体验更好，常见的瀑布流（抖音），刷不完
-- 分页，每页只显示五条数据
-- 语法：limit 起始值，页面的大小
SELECT s.`StudentNo`,`StudentName`,`StudentResult`,`SubjectName`
FROM student s
RIGHT JOIN result r
ON s.`StudentNo` = r.studentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
ORDER BY `StudentResult` ASC
LIMIT 1,5
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页 limit n,5 (n-1)*5,5
-- 【pagesize:页面大小-p; n：当前页】
-- 【(n-1)p,p 】
-- 【数据总数/页面大小 = 总页数 有余数取整】
语法：= limit (查询起始值，页面大小 )； limit （起始值，pagesize）
4.6、子查询
where（这个值是计算出来的）
本质：在 where 语句中嵌套一个子查询语句
where （select *from）
-- =========== 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
-- 方式二：使用子查询（由里到外）
-- 查询所有 高等数学-1 的学生学号
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
WHERE `SubjectNo` = (
SELECT `SubjectNo` FROM `subject`
WHERE`SubjectName`='高等数学-1'
)
ORDER BY `StudentResult` DESC
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT `StudentName`,s.`StudentNo`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult`>=80 AND`SubjectName`='高等数学-2'
-- 在这个基础上增加一个科目 高等数学-2
SELECT DISTINCT `StudentName`,s.`StudentNo`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult`>=80 AND`SubjectNo`=(
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` ='高等数学-2'
)
-- 查询科目为高等数学-2 分数不小于80 ，学号，姓名
SELECT s.`StudentNo`,`StudentName`,`StudentResult`,`SubjectName`
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
-- 再改造（由里及外）
SELECT StudentNo,StudentName FROM student WHERE studentNo IN(
SELECT StudentResult FROM result WHERE StudentResult >80 AND SubjectNo=(
SELECT SubjectNo FROM `subject` WHERE SubjectName = '高等数学-2'
)
)
练习：查询 高等数学-1，前五名同学的成绩的信息（学号，姓名，分数）
使用两种查询方式
-- 连表查询
SELECT s.`StudentNo`,`StudentName`,`StudentResult`,`SubjectName`
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
-- 子查询
SELECT r.StudentNo,StudentName,StudentResult
FROM result r
INNER JOIN student s
ON r.StudentNo = s.StudentNo
WHERE `SubjectNo` = (
SELECT `SubjectNo` FROM`subject` WHERE `SubjectName`='高等数学-1'
ORDER BY `StudentResult` DESC
LIMIT 0,5
)
4.7、分组和过滤
-- 查询不同课程的平均分，最高分，最低分，平均分大于80的
-- 核心：根据不同的课程分组
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