4 DQL（SELECT）各种查询 where联表 分页 排序 子查询

4. DQL查询数据（最重点）

4.1 DQL

（Data Query LANGUAGE:数据查询语言）

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询都可做
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

4.2 指定查询字段

语法：SELECT 字段… FROM 表

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

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

--别名，给结果起一个名字  AS    可以给字段起别名也可以给表起别名
SELECT `StudentNo` AS 学号，`StudentName` AS 学生姓名  FROM student

用于那些列名字不是那么见名知义

--函数 Concat（a，b）    拼接
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

去重 distinct

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

-- 查询一下有哪些同学参加了考试，成绩
SELECT  `StudentNo` FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result --发现重复数据，去重

数据库的列（表达式）

SELECT VERSION() --查询系统版本（函数）
SELECT 100*3-1 AS 计算结果    -- 用来计算（表达式）
SELECT @@auto_increment_increment --查询自增的步长 （变量）

-- 学号考试政绩——1分查看
SELECT  `StudentNo`,`StudentResult`+1 AS '提分后' FROM result

4.3 where 条件子句

作用：检索数据中符合条件的值

逻辑运算符

and && 两个等价用哪一个都可以
or ||
Not !=

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


除了100分的学生 
用!=时：  WHERE StudentResult != 100
用NOT时：  WHERE NOT StudentResult =100

模糊查询:比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为null，结果为真
IS NOT NULL	a is not 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到任意个字符   _(代表一个字符）  要和like一起用
SELECT `StudentNo`,`StudentName`  FROM `student`
WHERE StudentName Like ‘刘%’

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

--查询姓刘的同学，名字后面只有两个字
SELECT `StudentNo`,`StudentName`  FROM `student`
WHERE StudentName Like ‘刘__’

--查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName`  FROM `student`
WHERE StudentName Like ‘%嘉%’

----------------in（具体的一个或多个值）------------------
查询1001，1002，1003号学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
------查询地址为空的学生  null-------
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address='' OR address IS NULL

--查询有出生日期的同学  不会空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL

4.4 联表查询

JOIN 对比

– join （连接的表） on （条件判断） 连接查询
– where 等值查询

操作	描述
Inner join	如果表中至少有一个匹配就返回匹配的值
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从有表中返回所有的值，即使右表中没有匹配

思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用那种连接查询？  7种
确定交叉点（这两个表中哪个数据是相同的）
判断条件： 学生表中的studentNo   =   成绩表中的  studentNo

SELECT s.studentNo，studentName,SubjectNo,StudentResult            --（subjectNo在别的表中，因为studentNo这两个表中都有，所以要取别名来设置选择取哪里的别名）
FROM student  AS  s
INNER JOIN result  AS  r
ON  s.studentNo  =  r.studentNo

--查询缺考的同学
SELECT s.studentNo，studentName,SubjectNo,StudentResult        
FROM student  AS  s
left JOIN result  AS  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     (因为要查询参加考试的同学的信息，不是全部同学的信息，所以要以result表为基准，用right join)
on r.studentNo = s.studentNo     
INNER JOIN `subject`  sub      (将上面查询完的看成一个表然后使用INNER JOIN和subject表进行查询，要注意上面表查询时要现将SubjectName改成StudentNo，再改回来)
ON r.SubjectNo = sub.SubjectNo

查询了参加 数据库结构-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。。。
从哪几个表中查 FROM 表 XXX JOIN 连接的表 ON 交叉条件
假设存在一种多张表查询，慢慢来 ，先查询两张表然后再慢慢增加（如上面的思考题）

---

自连接(非重点)

自己的表和自己的表连接，核心：一张表拆成两张一样的表即可

4.5 分页和排序

–排序： 升序 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
LIMIT 0,5 是1~5
LIMIT 1,5 是2~6

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

--查询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 r.SubjectNo = sub.SubjectNo
WHERE StudentResult >= 80 and SubjectName = 'JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10

4.6 子查询

where （这个值是计算出来的）
本质： 在WHERE语句中嵌套一个子查询语句

1.查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一： 使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.StudentNo = sub.StudentNo
WHERE SubjectName = '数据库结构-1'
ORDER BY StudentResult  DESC

--方式二：使用子查询   (可以套娃，在SubjectName后面接着加括号)
SELECT StudentNo,SubjectNo,`subject`
FROM `result`
WHERE SubjectNo = (
             SELECT SubjectNo FROM 'subject'
             WHERE SubjectName = '数据结构-1'
)
ORDER BY StudentResult DESC

4.7 分组和过滤

查询不同课程的平均分，最高分，最低分
核心： （根据不同的课程分组）
SELECT SubjectName，AVG(StudentResult) AS 平均分，MAX(StudentResult) AS 最高分,MIN(StudentResult)
FROM result r
INNER JOIN subject sub
on r.SubjectNo = sub.SubjectNo
GROUP BY r.SubjectNo --通过什么字段来分组  group by
HAVING  平均分>80