DQL查询数据(最重点)

4、DQL查询数据(最重点)

4.1、DQL

(Data query Language:数据查询语言)

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

select完整语法

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,]rom_count | row_countOFFSET offset}];
	-- 指定查询的记录从哪条至哪条

4.2、指定查询字段

-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM `student`
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student

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

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

语法:SELECT 字段1,...FROM 表

有的时候,列名字不是那么的见名知意。我们起别名 AS ;字段名 A 别名 表名 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 -- 查询自增的步长 (变量)

-- 学院考试成绩加一分
SELECT `StudentNo`,`StudentResult`+80 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`
 
-- 查询成绩在8-9之间的 
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`>=8 AND `StudentResult`<=9

-- && AND
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`>=8 && `StudentResult`<=9

-- 模糊查询(区间)
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult` BETWEEN 7 AND 9

-- 查询学号不等于1000的学生的成绩
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentNo`!=1000;

-- != not
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE NOT `StudentNo`=1000;

模糊查询:比较运算符

运算符 语法 描述
IS NULL a is null 如果操作符为 null,结果为真
IS NOT NULL a is 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到任意个字符) _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '%白' OR `StudentName`LIKE'%黑'

SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE'_黄'

-- ==== in(具体的一个或者多个值) =====================
-- 查询101 102 103
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentNo` IN(101,202,303)

-- 查询在河南的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `address` IN ('河南省周口市西华县东夏镇')

-- ======null not null============
-- 查询地址为空的学生 null''
SELECT `StudentNo`,`StudentName`,`Address` FROM `student`
WHERE `Address`='' OR `Address` IS NULL


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

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

4.4、联表查询

JOIN 对比

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-no4oQXsr-1625484993577)(MySQL.assets/oscimg.oschina.net&app=2002&size=f9999,10000&q=a80&n=0&g=0n&fmt=jpeg)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q4jAikfc-1625484993580)(MySQL.assets/www.west.cn&app=2002&size=f9999,10000&q=a80&n=0&g=0n&fmt=jpeg)]

操作 描述
inner join 如果表中至少有一个匹配,就返回行
left join 会从左表中返回所有的值,即使右表中没有匹配
right join 会从右表中返回所有的值,即使左表中没有匹配
-- ============联表查询 join =====================

-- 查询参加了考试的同学(学号,姓名,课程编号,分数s)
SELECT * FROM `student`
SELECT * FROM `result`


/*思路
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的)
判断条件:学生表中的 studentNo = 成绩表中的 studentNo
*/

-- join(连接的表) on(判断条件) 连接查询
-- where   等值查询

SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
WHERE 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

-- 查询缺考的同学
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student s
LEFT JOIN result r 
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL


-- 思考题(查询参加考试的同学信息:学号,学生姓名,科目名,分数)
/*思路
1.分析需求,分析查询的字段来自哪些表,result student subject result(连接查询)
2.确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的)
判断条件:学生表中的 studentNo = 成绩表中的 student
*/
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...
-- 从那几个表中查 FROM 表 XXX Join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加

-- From a Left Join b	以a表为基准
-- From b right Join a	以b表为基准
==========================================================================
/*
1.首先确定需要查询三张表 result student subject
2.要查询的数据为 s.StudentNo,`StudentName`,`SubjectName`,`StudentResult`
使用from student s Left Join result r 以右表result为基准 --student与result都有studentName
*/
-- 先查询两张表,哪个表和第三张表也有相同数据,就以哪个表为基准
-- result表中与另外两张表都有相同的数据,并且要查询参加考试的同学,所以以result成绩表为基准
-- 先查询student 与 result。
SELECT s.studentNo,studentName,studentResult,subjectName -- 要查询的数据	
FROM student s 			-- 从表
RIGHT JOIN result r 		-- 主表 右
ON s.studentNo = r.studentNo	-- 交叉点
INNER JOIN `subject` sub        -- 再查询 result 与 subject。用inner join 如果表中至少有一个匹配,就返回行
ON r.subjectNo = sub.subjectNo  -- 交叉点

自连接(了解即可)

自己的表和自己的表连接,核心:一张表拆为两张一样的表即可

父类:

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

子类:

pid categoryid categorName
3 4 数据库
2 8 办公信息
3 6 wed开发
5 7 ps设计

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

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

4.5、分页和排序

排序

-- 排序:升序 ASC ,降序 DESC
-- ORDER BY 通过哪个字段排序,怎么排
-- 查询的结果根据 成绩降序 排序
SELECT s.`StudentNo`,`StudentName`,`StudentResult`,`SubjectName`
FROM student s
RIGHT JOIN result r
ON s.`StudentNo` = r.studentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
ORDER BY `StudentResult` ASC

分页

-- 当数据过多时,比如100万条
-- 根据实际情况分页?
-- 缓解数据库压力,给人的体验更好,常见的瀑布流(抖音),刷不完

-- 分页,每页只显示五条数据
-- 语法:limit 起始值,页面的大小
SELECT s.`StudentNo`,`StudentName`,`StudentResult`,`SubjectName`
FROM student s
RIGHT JOIN result r
ON s.`StudentNo` = r.studentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
ORDER BY `StudentResult` ASC
LIMIT 1,5

-- 第一页 limit 0,5	
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页  limit n,5  (n-1)*5,5
-- 【pagesize:页面大小-p; n:当前页】
-- 【(n-1)p,p 】
-- 【数据总数/页面大小 = 总页数 有余数取整】

语法:= limit (查询起始值,页面大小 ); limit (起始值,pagesize)

4.6、子查询

where(这个值是计算出来的)

本质:在 where 语句中嵌套一个子查询语句

where (select *from)

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


-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT `StudentName`,s.`StudentNo`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult`>=80 AND`SubjectName`='高等数学-2'

-- 在这个基础上增加一个科目 高等数学-2
SELECT DISTINCT `StudentName`,s.`StudentNo`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult`>=80 AND`SubjectNo`=(
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` ='高等数学-2'
)

-- 查询科目为高等数学-2 分数不小于80 ,学号,姓名
SELECT s.`StudentNo`,`StudentName`,`StudentResult`,`SubjectName`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '高等数学-2' AND `StudentResult`>=80

-- 再改造(由里及外)
SELECT StudentNo,StudentName FROM student WHERE studentNo IN(
   SELECT StudentResult FROM result WHERE StudentResult >80 AND SubjectNo=(
      SELECT SubjectNo FROM `subject` WHERE SubjectName = '高等数学-2'  
  )
)

练习:查询 高等数学-1,前五名同学的成绩的信息(学号,姓名,分数)

使用两种查询方式

-- 连表查询
SELECT s.`StudentNo`,`StudentName`,`StudentResult`,`SubjectName`
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` DESC
LIMIT 0,5

-- 子查询
SELECT r.StudentNo,StudentName,StudentResult
FROM result r
INNER JOIN student s
ON r.StudentNo = s.StudentNo
WHERE `SubjectNo` = (
   SELECT `SubjectNo` FROM`subject` WHERE `SubjectName`='高等数学-1' 
   ORDER BY `StudentResult` DESC
   LIMIT 0,5
)

4.7、分组和过滤

-- 查询不同课程的平均分,最高分,最低分,平均分大于80的
-- 核心:根据不同的课程分组
SELECT any_value(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
上一篇:MySQL学习之数据库详解~2021


下一篇:联表查询Join On