DQL查询数据（重点）

UohzZ

已于 2023-02-25 16:48:39 修改

阅读量142

点赞数

文章标签： sql 数据库 mysql

于 2023-02-25 14:58:45 首次发布

原文链接：https://www.bilibili.com/video/BV1NJ411J79W?p=16&vd_source=08cd0134b3d6a4ec343d112a2c9a0894

版权

DQL

(Data Query LANGUAGE：数据查询语言)

所有的查询操作都用它 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,] ROW_COUNT | row_countOFFSET OFFSET}];
    -- 指定查询的记录从哪条至哪条

注意：[ ]括号代表可选的，{ }括号代表必选的

指定查询字段

语法格式：

SELECT 字段,... FROM 表名

有的时候，列名字不是那么见名知意，可以起别名，用AS

字段名 AS 别名

表名 AS 别名

-- 查询
-- 查询全部学生
SELECT * FROM `student`

-- 查询指定字段
SELECT `id`,`name` FROM `student`

-- 别名，给结果取个名字，用AS  可以给字段起别名，也可以给表起别名
SELECT `id` AS 学号,`name` AS 学生姓名 FROM `student`

-- 函数concat(a,b)
SELECT CONCAT('姓名：',`name`) AS 学生姓名 FROM `student`

去重 distinck

作用：去除select查询出来的重复结果，只保留显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNo` FROM result  -- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result  -- 发现重复数据，去重

数据库的列（表达式）

-- 查询系统版本
SELECT VERSION() AS 版本

-- 查询计算结果
SELECT 105*2-1 AS 计算结果

-- 查询自增的步长
SELECT @@auto_increment_increment

-- 学生年龄加一
SELECT `age`+1 FROM `student`

where条件子句

作用：检索数据中符合条件的值

搜索的条件有一个或多个表达式组成

逻辑运算符（尽量使用英文字母的）

运算符	语法	描述
and &&	A and B A&& B	逻辑与
or \|\|	A or B A \|\| B	逻辑或
not ！	not A !A	逻辑非

-- 查询年龄在18到20之间的学生
SELECT `name`,`age` FROM `student` WHERE `age`>=18 AND `age`<=20

SELECT `name`,`age` FROM `student` WHERE `age` BETWEEN 18 AND 20

-- 查询年龄是18或19的学生
SELECT `name`,`age` FROM `student` WHERE `age`=18 OR `age`=19

-- 查询除了年龄在19岁的学生姓名
SELECT `name`,`age` FROM `student` WHERE `age`!=18 

SELECT `name`,`age` FROM `student` WHERE NOT `age`=18

模糊查询：比较运算符

运算符	语法	描述
IS NULL	A IS NULL	如果操作符为NUI，则结果为真
NOT IS NULL	A NOT IS NULL	如果操作符不为null，则结果为真
BETWEEN...AND	A BETWEEN B AND C	若A在B和C之间，则结果为真
LIKE	A LIKE B	SQL匹配，如果A匹配B，则结果为真
IN	A IN('值1','值2',... )	假如A在值1或值2...某一个值中，则结果为真

-- 模糊查询

-- ======== 使用like 结合  %(代表任意个字符)  _(代表一个字符) __(两个_,代表两个字符) ====
-- 查询名字中第一个字是小的学生
SELECT `id`,`name` FROM `student`
WHERE `name` LIKE '小%'

-- 查询名字中第一个字是小的学生，名字后面只有一个字的
SELECT `id`,`name` FROM `student`
WHERE `name` LIKE '小_'

-- 查询名字中第一个字是小的学生，名字后面只有两个字的
SELECT `id`,`name` FROM `student`
WHERE `name` LIKE '小__'

-- 查询名字中有星字的学生 %星%
SELECT `id`,`name` FROM `student`
WHERE `name` LIKE '%星%'


-- =======================  in（具体的一个或多个值）， 不能用%  ================
-- 查询年龄在19岁,18岁的学生
SELECT `id`,`name` FROM `student`
WHERE `age` IN('19','18')

-- =========================== IS NULL  =========================
-- 查询地址为空的学生   
SELECT `id`,`name` FROM `student`
WHERE `adress` IS NULL

-- ================================= IS NOT NULL ================
-- 查询地址不为空的学生
SELECT `id`,`name` FROM `student`
WHERE `adress` IS NOT NULL

-- ====================BETWEEN... AND =========================
-- 查询年龄在18到20之间的学生
SELECT `name`,`age` FROM `student` WHERE `age` BETWEEN 18 AND 20

联表查询

jion 对比

从表student 和表result中查询学生信息

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

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


-- INNER JOIN
SELECT s.studentNO,`studentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.studentNO = r.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

-- lift Join
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
LIFT 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	会从右表中返回所有的值，即使右表中没有匹配

从三个表，表student 、表result和表subject中查询参加考试的学生信息

-- 思考题（查询了参加考试的同学信息:学号，学生姓名，科目名，分数)
/*思路
1.分析需求，分析查询的字段来自哪些表，student、result、 subject（连接查询)
2．确定使用哪种连接查询   7种
3. 确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表的中 studentNo =成绩表studentNo
*/
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加


-- 第一步，先连接两个表
SELECT s.studentNo,studentName,SubjectNo, `StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON r.studentNo = s.studentNo

-- 第二步，将最后一个表与第一步再次连接
SELECT s.studentNo,studentName,SubjectName,`StudentResult`
FROM student AS s
RIGHT JOIN result AS r
ON r.studentNo = s.studentNo
INNER JOIN `subject` AS sub
ON r.SubjectNo = sub.SubjectNo

自连接（了解即可）

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

创建数据库，添加相关数据：

CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT'主题id',
`pid` INT(10) NOT NULL COMMENT'父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT'主题名字',
PRIMARY KEY (`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `category`( `categoryid`, `pid`, `categoryName`)
VALUES ('2','1','信息技术'),
( '3','1','软件开发'),
('4', ' 3','数据库'),
( '5', ' 1','美术设计'),
( '6','3', 'web开发'),
('7','5', 'ps技术'),
( '8', '2','办公信息');

父类

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

子类

pid	catagoryid	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`

查询结果：

排序和分页

排序

-- 排序  升序：asc  降序：desc
-- order by 通过哪个字段排序 怎么排
-- 升序排列

SELECT s.studentNo,studentName,SubjectName,`StudentResult`
FROM student AS s
RIGHT JOIN result AS r
ON r.studentNo = s.studentNo
INNER JOIN `subject` AS sub
ON r.SubjectNo = sub.SubjectNo
ORDER BY `StudentResult` ASC

分页

-- 为什么要分页?
-- 缓解数据库压力，给人的体验更好
-- 分页，每页只显示五条数据
-- 语法:limit起始值，页面的大小
-- 网页应用:当前，总的页数，页面的大小
-- LIMIT 0,5   1~5
-- LIMIT 1,5   2~6
-- Limit 5,5   6~10 

SELECT s.studentNo,studentName,SubjectName,`StudentResult`
FROM student AS s
RIGHT JOIN result AS r
ON r.studentNo = s.studentNo
INNER JOIN `subject` AS sub
ON r.SubjectNo = sub.SubjectNo
ORDER BY `StudentResult` ASC
LIMIT 1,5

-- 第一页7imit 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 :当前页】
-- 【数据总数/页面大小=总页数】|

练习题：

-- 查询：数据结构-1课程成绩排名前十的学生，并且分数要大于80 的学生信息（学号，姓名，课程名称，分数)

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据结构-1' AND`studentresult` >= '80'
ORDER BY `studentresult` DESC
LIMIT 0,10

子查询

-- 查询课程为高等数学-2且分数不小于80 的同学的学号和姓名
-- 常规方式
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 `SubjectName` = '高等数学-2' AND StudentResult>=80

-- 分数不小于80分的学生的学号和姓名
-- 子查询（第一步）
SELECT DISTINCT s.`studentNo`,`studentName`
FROM student s
INNER JOIN result r
ON r.studentNo = s.studentNo
WHERE `studentResult` >= 80

-- 在这个基础上增加一个科目，高等数学-2
-- 查询高等数学-2的编号（第二步）

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` = '高等数学-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'
    )
    )

分组和过滤

-- 查询不同课程的平均分，最高分，最低分,平均分大于90分
-- 核心:(根据不同的课程分组)
SELECT SubjectName,AVG(studentResult),MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectNo` = sub.`SubjectNo`
GROUP BY r.subjectNo -- 通过什么字段来分组
HAVING AVG(studentResult)>=90  -- 过滤分组的记录必须满足的次要条件