4.DQL查询数据

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