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

最新推荐文章于 2022-09-21 17:05:39 发布

工一木子

最新推荐文章于 2022-09-21 17:05:39 发布

阅读量174

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/Prince140678/article/details/116113812

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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

4.1、DQL

（Data Query Language：数据查询语言）

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

select完整语法：

SELECT [ALL|DISTINCT]
{*|table *|[table.field1[as a]ias1][,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 `StudentNo` AS 学号,`StudentName` AS 学生姓名FROM `student` AS s

-- 函数 Concat(a,b)
SELECT CONCAT('姓名: ',StudentName) 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-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;

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

-- and &&
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;

-- != not
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,…其中的一个值中，结果为真

-- =============模糊查询============
-- 查询姓刘的同学
-- like 结合 %(代表0到任意个字符) _(一个字符)
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('安徽','河南洛阳');

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

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

在这里插入图片描述

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

-- ==============联表查询 join =============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
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` AS s
RIGHT JOIN `result` AS r 
WHERE s.studentNo = r.studentNo;

-- Left Join
SELECT  s.studentNo,studentName,SubjectNo,StudentResult
FROM `student` AS s
LEFT JOIN `result` AS r 
WHERE s.studentNo = r.studentNo;

-- 查询缺考的同学
SELECT  s.studentNo,studentName,SubjectNo,StudentResult
FROM `student` AS s
LEFT JOIN `result` AS r 
WHERE s.studentNo = r.studentNo
WHERE StudentResult IS NULL;

-- 思考题（查询参加考试的同学信息：学号，学生姓名，课目明，分数）
/* 思路
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

自连接

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

父类：

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

-- 查询学生所属的年级
SELECT studentNo,studentName,GradeName
FROM `student` s 
INNER JOIN 	`grade` g
ON s.`GradeID` =  g.`GradeID`;

-- 查询科目所属的年假（科目名称，年级名称）
SELECT `SubjectName`,`GradeName`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`GradeID` = g.`GradeID`;

-- 查询参加了 数据库结构-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';

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;

分页

-- 100万
-- 为什么要分页
-- 缓解数据库压力，给人的体验更好，瀑布流

-- 分页，煤业只显示5条数据
-- 语法： limit 起始值，页面的大小
-- 网页应用：当前，总的页数，页面的大小
-- LIMIT 0，5 1~5
-- LIMIT 1，5 2~6
-- LIMIT 6，5 7~11

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 0,5;

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

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

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

4.6、子查询

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

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

---- ==============where===============
-- 1.查询 数据库结构-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
-- 方式2，使用子查询（由里及外）
SELECT StudentNo,SubjectNo,StudentResult
FROM  result 
WHERE SubjectNo =(SELECT SubjectNo FROM subject WHERE SubjectName = '数据库结构-1');

-- 分数不小于80分的学生的学号和姓名
SELECT s.StudentNo,StudentName 
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
WHERE r.StudentResult >= 80;

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

SELECT s.StudentNo,StudentName 
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 = '高等数学-2');

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

-- 练习：查询C语言-1 前5名同学的成绩的信息（学号，姓名，分数）
-- 使用子查询~
SELECT StudentNo,StudentName,StudentResult FROM
student s
INNNER JOIN result r
ON s.StudentNo = r.StudentNo
WHERE SubjectNo = (SELECT SujectNo FROM subject WHERE SubjectName = 'C语言-1')
ORDER BY StudentResult DESC
LIMIT 0,5;

4.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
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 r.`SubjectNo` -- 通过什么字段来分组
HAVING 平均分>80;

执行报错：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.sub.SubjectName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

参考

临时解决：

set @@GLOBAL.sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

4.8、select小结

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wMF3KIlq-1636185383460)(C:\Users\Administrator\Desktop\学习笔记\mysql\初识MySQL.assets\image-20211106155348350.png)]$

彩蛋

1.淘宝，千人千面

每个人看到的不一样，登录与不登录看到的也不一样

评论，商品图片，商品视频存储也不同

工一木子

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
4、DQL查询数据（最重点）

4、DQL查询数据（最重点）4.1、DQL（Data Query Language：数据查询语言）所有的查询操作都用它 select简单的查询，复杂的查询它都能做数据库中最核心的语言，最重要的语句使用频率最高的语句select完整语法：SELECT [ALL|DISTINCT]{*|table *|[table.field1[as a]ias1][,table.field2[as alias2]][.....]}FROM table_name[as table_alias]
复制链接

扫一扫