DQL查询数据（Selcet）

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

4.1、DQL

Data Query Language：数据查询语言-

• 所有查询操作都用它 select
• 简单的查询、复杂的查询都用它
• 数据库中最核心的语言
• 使用频率最高的语言

4.2、指定查询字段

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

SELECT * FROM result

SELECT `StudentNo`,`StudentName` FROM student

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

-- 函数  concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS '新名字' FROM student

语法：select 字段 as 新的字段名 from 表 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   -- 查询自增的步长

-- 学员考试成绩+1分
SELECT StudentNO,StudentResult+1 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

-- and
SELECT StudentNo,StudentResult FROM result
WHERE studentresult>=80 AND StudentResult<=100

-- 模糊查询（区间）
SELECT StudentNo,StudentResult FROM result
WHERE studentresult BETWEEN 70 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	如果操作符不为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结合
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)

-- 查询在北京的学生
SELECT studentno,studentname FROM student
WHERE address IN ('北京朝阳')

-- 查询在江苏的学生
SELECT studentno,studentname FROM student
WHERE address LIKE '江苏%'

-- ===============  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

4.4、联表查询

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

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用那种连接查询？  7种
确定交叉点（这两个表中哪个数据是相同的）
判断条件，学生表中的studentno = 成绩表中的studentno
*/

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 AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno

-- left join
SELECT r.studentno,studentname,subjectno,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno

操作	描述
inner join	都匹配
left join	左匹配（以左表为主）
right join	右匹配（以右表为主）

-- 思考题（查询参加考试的同学信息：学号、学生姓名、科目名、分数）
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

自连接

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

父类

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryname
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息
SELECT a.categoryname AS '父栏目',b.categoryname AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
(img-WxdqZic3-1647446992737)(C:\Users\Carl Zhou\AppData\Roaming\Typora\typora-user-images\image-20220313003423594.png)]

4.5、分页与排序

排序

-- 排序：升序ASC（ascending），降序DESC（descending）
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
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'
LIMIT 5,5

-- 第一页  limit 0,5                    (1-1)*5
-- 第二页  limit 5,5                    (2-1)*5
-- 第三页  limit 10,5                   (3-1)*5
-- 第N页   limit (N-1)*pageSize,5       (N-1)*pageSize,pageSize
-- 【pageSize：页面大小】
-- 【N:当前页】
-- 【数据总数/页面大小=总页数】

语法：limit 查询起始下标，pageSize

4.6、子查询

where里再套一个句子

-- 查询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 subjectname = 'Java程序设计-1' AND studentresult>=80
ORDER BY studentresult DESC
LIMIT 0,10

-- 查询数据库结构-1的所有考试结果（学号、科目编号、成绩），降序排列
-- 子查询
SELECT studentno,subjectno,studentresult
FROM result
WHERE subjectno = (
	SELECT subjectno FROM `subject`
	WHERE subjectname = '数据库结构-1' 
)
ORDER BY studentresult DESC

-- 分数不小于80分的学生的学号和姓名（去重distinct）
SELECT DISTINCT s.studentno,`studentname`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE studentresult >= 80

 -- 在这个基础上增加一个科目，高等数学-2     -- 子查询
SELECT DISTINCT s.studentno,`studentname`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE studentresult >= 80 AND subjectno=(
	SELECT subjectno FROM `subject`
	WHERE subjectname = '高等数学-2'
)

-- 练习：查询 C语言-1 前5名同学的成绩的信息（学号姓名、分数）
-- 使用子查询
SELECT s.studentno,studentname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE subjectno = (
	SELECT subjectno FROM `subject`
	WHERE subjectname = 'C语言-1'
)
LIMIT 0,5

4.7、分组与过滤

-- 查询不同课程的平均分、最高分、最低分
-- 核心（根据不同的课程分组）
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 subjectname
HAVING 平均分>80

4.8、select 小结

-- 顺序很重要！
select distinct 要查询的字段 from 表（注意：表和字段可以取别名）
xxx join 要连接的表 
on 等值判断
where （具体的值，子查询语句）
group by （通过哪个字段来分组）
having （过滤分组后的信息，条件和where是一样的，位置不同）
order by （通过那个字段排序） DESC/ASC
limit startindex,pageSize