DQL查询数据（最重点）

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