MySQL笔记4：DQL 查询数据
4. DQL 查询数据（最重要）
4.1 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}];
-- 指定查询的记录从哪条至哪条
[]：可选；{}：必选
4.2 指定字段查询
查询
语法：SELECT 字段, ... FROM 表
-- 查询全部学生 select 字段 from 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
别名
当结果不是那么见名知意时，使用as起别名
-- 别名，给结果起名字 as：可以给字段和表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s
-- 函数 concat(a,b)拼接字符串
SELECT CONCAT('姓名：',studentName) AS 新名字 FROM student
去重
去除select查询出的重复数据，重复的数据只取一条
-- 查询有考试成绩的人
SELECT * FROM result -- 查询全部考试成绩
-- 查询有参与考试的人
SELECT `StudentNo` FROM result
-- 对重复数据去重
SELECT DISTINCT `StudentNo` FROM result
数据库的列查询（表达式）
数据库中的表达式：文本值、列、Null、函数、计算表达式、系统变量……
select 表达式 from 表
-- 查询系统版本号（函数）
SELECT VERSION()
-- 计算（表达式）
SELECT 100*3-1 AS 计算结果
-- 查询自增步长（变量）
SELECT @@auto_increment_increment
-- 所有考试成绩+1分查看
SELECT `StudentNo`,`StudentResult` FROM result
SELECT `StudentNo`,`StudentResult`,`StudentResult`+1 AS 提分后 FROM result
4.3 where条件子句
用于减少符合条件的值。
搜索的条件由一个或多个表达式组成，结果返回布尔值
4.3.1 逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and, && | a and b, a && b | 逻辑与，两个都为真时结果为真 |
| or, || | a or b, a||b | 逻辑或，两个有一个为真时结果为真 |
| not, ! | not a, ! a | 逻辑非，取反，真为假，假为真 |
逻辑与
-- 查询成绩在95~100间的人
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100
-- and, &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100
-- 模糊查询（区间）between...and
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
4.3.2 模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| 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
like结合符号
%：代表0到任一个字符_：代表一个字符
-- 查询名字以'张'开头的人
-- 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
使用IN进行查询需要使用完全匹配的字符（可以通过嵌套进行模糊查询）
-- 查询1001,1002,1003号学生
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `StudentNo` IN (1001, 1002, 1003);
-- 查询地址在北京的人
-- 只写部分(如'北京'，无法查询，需要使用具体的位置，如('北京朝阳')
SELECT `StudentNo`, `StudentName`,`Address` FROM `student`
WHERE `Address` IN ('北京朝阳');
NULL, IS NULL
NULL与IS NULL字符查询使用的是允许为空时的空字符。在默认内容不为空时填充的空内容''无法进行查询。
-- 查询地址为空的学生 null, ''
SELECT `StudentNo`, `StudentName`,`Address` FROM `student`
WHERE `Address` = '' OR `Address` IS NULL;
-- 查询地址不为空的人（默认不为空时，会自动填入''，此时无法用null语句排除
-- 生日为空时能查出正确内容，推测是创建表格时设置的原因
SELECT `StudentNo`, `StudentName`,`Address` FROM `student`
WHERE `Address` IS NOT NULL;
-- 查询生日为空的人
SELECT `StudentNo`, `StudentName`,`BornDate` FROM `student`
WHERE `BornDate` IS NULL;
4.4 联表查询 join on
join on：连接查询，jion 连接的表 on 判断的条件where：等值查询
七种JOIN理论：MySQL中没有full outer join操作，因此在MySQL中，6 = 1 union 4; 7 = 2 union 5
inner join：用于查询两表都有的数据（共同的部分，类似交集）left join：用于查询左边部分（student）中的数据（即使右边没有）right join：用于查询右边部分（result）中的数据（即使左边没有）
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result
/*
思路：
1. 分析需求，分析查询字段的来源表，来自不同表时使用连接查询
2. 确定使用哪种连接查询
3. 确定交集（两个表中哪些数据相同，判断条件）
*/
-- inner join用于查询两表相交部分中的数据
SELECT s.`StudentNo`,`StudentName`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`;
-- left join用于查询左边部分（student）中的数据（即使右边没有）
SELECT s.`StudentNo`,`StudentName`,`StudentResult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`;
-- right join用于查询右边部分（result）中的数据（即使左边没有）
SELECT s.`StudentNo`,`StudentName`,`StudentResult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`;
多张表联表查询
按顺序找交集，依次联结多张表
-- 查询参加考试的同学信息：学号，姓名，科目名，分数（3表查询）
/*
1. 来源表，student, result, subject
2. 基准，参加考试的学号，result表中的学号
3. 将第一次查询出的结果，再次进行连接查询
*/
SELECT r.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON r.`StudentNo` = s.`StudentNo`
LEFT JOIN `subject` AS sub -- 连接第三张表
ON r.`SubjectNo` = sub.`SubjectNo`
自连接（了解）
自己的表和自己的表连接，核心：一张表拆成两张一样的表
例表：categoryid为表示自己的序号，pid为父id，关系如下图所示。
-- 查询父子信息：把一张表视为两张相同的表
-- a和b是同一张表的两个别名，将同一张表当作两张表，进行信息的查询
SELECT a.`categoryName` AS 'parent', b.`categoryName` AS 'kid'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`
4.5 分页和排序
排序 order by
排序： 升序ASC 降序DESC
ORDER BY 列名 ASC -- 升序
ORDER BY 列名 DESC -- 降序
SELECT `StudentName`,`IdentityCard`
FROM `student`
ORDER BY `StudentNo` ASC
分页 limit
分页的作用：缓解数据库压力，增加体验
-- 分页，每页显示3条数据
-- 语法： limit 起始值,页面长度pagesize
SELECT `StudentName`,`IdentityCard`
FROM `student`
ORDER BY `StudentNo` ASC
LIMIT 0,3 -- 从0开始，显示3条
应用
-- 查询高等数学第一学年 课程成绩排名前10，且分数大于等于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `StudentResult`>=80 && `SubjectName`='高等数学-1'
ORDER BY `StudentResult` DESC
LIMIT 0,10
4.6 子查询
使where语句查询的不是固定的
本质：在where语句中嵌套一个子查询语句
例1 ：连接查询+子查询
查询数据库结构-1的所有考试结果（学号、科目名字、成绩），降序
-- 1.查询数据库结构-1的所有考试结果（学号、科目名字、成绩），降序
-- 方法1：使用连接查询
-- 方法2：使用子查询(由里及外)
SELECT `StudentNo`, `SubjectNo`,`StudentResult`
FROM `result`
WHERE `SubjectNo` = (
SELECT `SubjectNo` FROM `subject`
WHERE `SubjectName` = '高等数学-1'
)
例2: 全部使用子查询
分数不小于80分的学生的学号和姓名
-- 由里及外
SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentNo` IN (
SELECT `StudentNo` FROM `result` WHERE `StudentResult` >= 80 AND `SubjectNo` = (
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = '高等数学-1'
)
)
练习
查询C语言-1 前五名同学的成绩信息（学号，姓名，分数）
SELECT `student`.`StudentNo`,`StudentName`,`StudentResult`
FROM `student`
LEFT JOIN `result`
ON `student`.`StudentNo` = `result`.`StudentNo`
WHERE `SubjectNo` = (
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = 'C语言-1'
)
ORDER BY `StudentResult` DESC
LIMIT 0,5
4.7 分组和过滤
group by 字段 ：根据字段进行分组
having 条件：对分组后的字段进行过滤
-- 查询不同课程的平均分，最高分，最低分
-- 核心：根据不同课程进行分组
SELECT `SubjectName`,AVG(`StudentResult`) AS 平均分,MAX(`StudentResult`) AS 最高分,MIN(`StudentResult`) AS 最低分
FROM result r
RIGHT JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.`SubjectNo` -- 通过什么字段分组
HAVING 平均分 >= 80 -- 分组后进行过滤
