4. DQL查询数据（最重要）
4.1 DQL
（date query language：数据查询语言）
- 所有查询操作都用它，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,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
4.2 指定查询某个字段
-- 查询全部的学生 select 字段 from 表名
select * from student
-- 查询指定字段
select `studentNo`,`studentName` from student
-- 别名，给结果起一个名字 AS
-- 可以给字段起别名，也可以给表起别名
SELECT `name` AS 姓名,`gender` AS 性别 FROM student
-- 函数concat（a，b）
SELECT CONCAT('名字：',`name`) AS 新名字 FROM student
语法：select 字段…from表
有时候，列名字不是那么见名知意，可以起别名（AS）
字段名 as 别名 ， 表名 as 别名
去重 distinct：去掉select查询结果中重复的数据，重复的数据只显示一条
-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `studentNo` FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result -- 发现重复数据，去重
数据库的列（表达式）
-- 查询系统版本 （函数）
SELECT VERSION()
-- 用来计算 （表达式）
SELECT 100*3-6 AS 计算结果
-- 查询自增的步长 （变量）
SELECT @@auto_increment_increment
-- 学员考试成绩＋1分查看
SELECT `studentNo`,`studentResult`+1 AS '提分后' FROM result
4.3 where条件子句
作用：检索数据中符合条件的值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 与 |
| or || | a or b a||b | 或 |
| not ！ | not a ！a | 非 |
-- =========== where ====================
-- 查询考试成绩在95~100分之间
SELECT `studentNo`,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100
SELECT `studentNo`,`StudentResult` FROM result
WHERE StudentResult>=95 && StudentResult<=100
-- 模糊查询（区间）
SELECT `studentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 除了1000号学生之外的司学的成绩
SELECT `studentNo`, `StudentResult` FROM result
WHERE studentNo!=1000;
SELECT `studentNo`, `StudentResult` FROM result
WHERE NOT studentNo=1000;
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 操作符为null，结果为真 |
| IS NOT NULL | a is not 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，a3…中的某一个值，结果为真 |
-- ================ 模糊查询 ========================
-- 查询姓刘的同学
-- like结合 %（代表0到任意个字符） _（1个字符）
SELECT `name`, `major` FROM student
WHERE `name` LIKE '刘%'
-- 查询姓刘的同学，姓后面只有一个字的
SELECT `name`, `major` FROM student
WHERE `name` LIKE '刘_'
-- 查询姓刘的同学，姓后面只有两个字的
SELECT `name`, `major` FROM student
WHERE `name` LIKE '刘__'
-- 查询名字中间带有奇的同学，%奇%
SELECT `name`, `major` FROM student
WHERE `name` LIKE '%奇%'
-- 查询15,18,24号学生
SELECT `id`,`name` FROM student
WHERE `id` IN (15,18,24)
-- 查询在后港的同学
SELECT `id`,`name`,`address` FROM student
WHERE `address` IN ('后港镇','李市镇')
-- =========null , not null ====================
-- 查询地址为空的学生null ''
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE address='' OR address IS NULL
-- 查询有出生日期的同学不为空
SELECT `studentNo`, `studentName` FROM `student`
WHERE `BornDate` IS NOT NULL
-- 查询没有有出生日期的同学为空
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `BornDate` IS NULL
4.4 联表查询
JOIN对比
-- =================== 联表查询 join ================
-- 查询参加了考试的同学（学号，姓名，科目编号，分数)
SELECT * FROM student
SELECT * FROM result
/*思路
1．分析需求，分析查询的字段来自哪些表，(连接查询)
2．确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表的中studentNo =成绩表studentNo大
*/
-- JOIN （连接的表） ON（判断的条件）连接查询
-- where 等值查询
-- INNER JOIN
SELECT s.studentNO , studentName , subjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNO = r.studentNO
-- RIGHT JOIN // student为左表，result为右表
SELECT s.studentNO , studentName , subjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNO = r.studentNO
-- LEFT JOIN // student为左表，result为右表
SELECT s.studentNO , studentName , subjectNo,StudentResult
FROM student AS s
LEFT 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
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
-- 思考题（查询了参加考试的同学信息:学号，学生姓名，科目名，分数)
/*思路
1．分析需求，分析查询的字段来自哪些表，student、result、subject （连接查询）
2．确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的>
判断的条件:学生表的中 studentNo =成绩表studentNo
*/
SELECT s.studentNo,studentName,SubjectNo,`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
-- FROM A RIGHT JOIN B
自连接：自己的表和自己的表连接，核心：一张表拆成两张一样的表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RRb0BzvV-1641611984843)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20220105211321715.png)]
父类：
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类：
| categoryid | categoryName | pid |
|---|---|---|
| 4 | 数据库 | 3 |
| 8 | 办公信息 | 2 |
| 6 | web开发 | 3 |
| 7 | ps技术 | 5 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
4.5 分页和排序
排序
-- ============ 分页limit 和排序order by ===============
-- 排序 ：升序ASC ，降序DESC
-- 查询结果根据成绩降序排序
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.Student
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = '数据结构'
ORDER BY StudentResult DESC -- 根据成绩降序 排序
ORDER BY StudentResult ASC -- 根据成绩升序排序
分页：
-- 为什么要分页？
-- 缓解数据库的压力，给人更好的体验， 瀑布流
-- 分页：每页只显示五条数据
-- 语法： limit 起始值 ， 页面大小
-- 网页应用 ： 当前，总的页数，页面的大小
-- limit 0,5 显示的是第一个到第五个数据
-- limit 1,5 显示的是第二个到第六个数据
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.Student
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = '数据结构'
ORDER BY StudentResult DESC -- 根据成绩降序 排序
LIMIT 0,5
-- 第一页 limit 0,5 （1-1）*5
-- 第二页 limit 5,5 （2-1）*5
-- 第三页 limit 10,5 （3-1）*5
-- 第n页 limit （n-1）*5,5 （n-1）* pagesize ， pagesize
-- 【pagesize：页面大小】
-- 【（n-1）*pagesize：起始值】
-- 【n：当前页】
-- 【数据总数/页面大小=页面数】
语法：limit 起始下标，页面大小
-- 查询JAVA第一学年课程成绩排名前十的学生，并且分数要大于80 的学生信息（学号，姓名，课程名，分数）
SELECT `studentNo`,`studentname`,`subjectname`,`subjectresult`
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 `subjectresult` >= 80
ORDER BY studentresult DESC
LIMIT 0,10
4.6 子查询
where（这个值是计算出来的）
本质：在where语句中嵌套一个查询语句
where （select * from）
-- ====================== where ======================
-- 1.查询数据库结构的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：连接查询
SELECT `studentno`,`subjectname`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构'
ORDER BY sutdentresult DESC
-- 方式二：子查询（）
SELECT `studentno`,`subjectname`,`studentresult`
FROM `result` r
WHERE studentno = (
SELECT studentno FROM `subject`
WHERE `subjectname` = '数据库结构'
)
ORDER BY `studentresult` DESC
-- 查询所有数据库结构的学生学号
SELECT studentno FROM `subject`
WHERE `subjectname` = '数据库结构'
-- 分数不小于80分的学生的学号和姓名
SELECT s.`studentNo`,`studentName`
FROM student s
INNER JOIN result r
ON r.studentNo = s.studentNo
WHERE `studentResult`>=80
-- 查询课程为高等数学且分数不小于80的同学的学号和姓名
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`='高等数学'
)
SELECT s.studentNo, studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo工NNERJOIN`subject` sub
ON r.`subjectNo` = sub.`subjectNo`
WHERE `subjectName`='高等数学' AND StudentResult>=80