数据库MySQL之DQL（四）

DQL查询数据

数据查询语言：Data Query LANGUAGE
数据库中最核心的语言，最重要的语句

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做~
• 使用频率最高的语句

1.指定查询字段

语法：SELECT 字段,... FROM 表

-- 查询全部的学生   SELECT 字段 FROM 表
SELECT * FROM student

-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student

去重 distinct

作用：去除SELECT 查询出来的结果中重复的数据，重复的数据只显示一条

SELECT DISTINCT `StudentNo` FROM result -- 发现重复数据，去重

2.where 条件子句

作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成！结果：布尔值！

逻辑查询：（a and b a&&b）（a or b a|| b）（not a ！a）

-- ===================  where  ======================
SELECT studentNo,`StudentResult` FROM result

-- 查询考试成绩在 95~100 分之间
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100

模糊查询： 比较运算符（a is null）（a is not null）（a between b and c）（a like b）（a in (a1,a2,a3….)）

-- ===================  模糊查询  ======================
-- 查询姓刘的同学
-- like结合  %(代表0到任意个字符)   _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%'

-- 查询姓刘的同学，名字后面只有一个字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_'

3.连表查询

JOIN 对比

-- ============== 联表查询 join ==============

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON 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

-- 思考题（查询了参加考试的同学信息： 学号，学生姓名，科目名，分数）
/* 思路
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 a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

4.分页和排序

排序 ： 升序 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 ASC

分页：
语法：limit(查询起始下标，pageSize)

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
-- 【pageSize：页面大小】
-- 【(n-1)* pageSize:起始值】
-- 【n ：当前页 】
-- 【数据总数/页面大小 = 总页数】

5.子查询

本质 ：在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

-- 在这个基础上增加一个科目， 高等数学-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 StudentNo,StudentName FROM student WHERE StudentNo IN (
   SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo = (
	SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等数学-2'
   )
)

6、分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于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

7.小结