DQL 数据查询语言

DQL(Data Query Language)数据查询语言：用来查询数据库中表的记录。关键字为:SELECT

基本语法

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件
GROUP BY 列名
HAVING 分组后条件
ORDER BY 列名
LIMIT 分页参数

执行顺序

1. FROM : 将硬盘上的表文件加载到内存
2. WHERE : 将符合条件的数据行摘取出来。生成一张新的临时表
3. GROUP BY : 根据列中的数据种类，将当前临时表划分成若干个新的临时表
4. HAVING : 可以过滤掉group by生成的不符合条件的临时表
5. SELECT : 对当前临时表迚行整列读取
6. ORDER BY : 对select生成的临时表，进行重新排序，生成新的临时表
7. LIMIT : 对最终生成的临时表的数据行，进行截取。

基础查询

SELECT 列名1, 列名2, ... FROM 表名; # 查询多个字段
SELECT * FROM 表名; # 实际开发中少用,影响效率
SELECT 列名1 AS 别名1, 列名2 别名2, ... FROM 表名; # 设置别名,AS 可省略
SELECT DISTINCT 列名,... FROM 表名; # 去除重复记录,返回指定列中所有不重复的值

条件查询

SELECT 列名,... FROM 表名 WHERE 条件; # 条件查询

条件运算符

比较运算符：
大于: >, 大于等于: >=, 小于: <, 小于等于: <=, 等于: =, 不等于: <>或!=
范围: BETWEEN ... AND ..., 列表值: IN, 模糊: LIKE, 是否NULL: IS NULL或IS NOT NULL
逻辑运算符:
并且: AND或&&, 或: OR或||, 非: NOT或!

SELECT id, name, birthday FROM tb_student WHERE name = '张三'; 
SELECT id, name, birthday FROM tb_student WHERE name LIKE '张%'; # 模糊查询
SELECT id, name, birthday FROM tb_student WHERE birthday BETWEEN '2015-05-01' AND '2015-08-01'; # 范围查询,包含前后值

聚合函数

将一列数据作为一个整体，进行纵向计算。
常见的聚合函数：COUNT(), SUM(), AVG(), MAX(), MIN()

SELECT SUM(列名) FROM 表名; # 求和, NULL 不参与聚合函数的运算
SELECT MAX(score) FROM tb_score; # 查询最高分
SELECT MIN(score) FROM tb_score; # 查询最低分
SELECT AVG(score) FROM tb_score; # 查询平均分

统计学生数量

SELECT COUNT(*) FROM tb_student; # 统计总记录数
SELECT COUNT(1) FROM tb_student; # 统计总记录数
SELECT COUNT(birthday) FROM tb_student; # 统计生日不为NULL的记录数

COUNT(*)、 COUNT(1)、 COUNT(索引字段)、 COUNT(非索引字段)

执行效率：COUNT() > COUNT(1) > COUNT(索引字段) > COUNT(非索引字段)
COUNT():对key_len最短的二级索引进行遍历，如果没有，那么就根据聚簇索引进行遍历(不读取聚簇索引的值)
COUNT(1):对聚簇索引进行遍历(不读取聚簇索引的值)
COUNT(索引字段):对聚簇索引进行遍历(读取索引的值)
COUNT(非索引字段):全表扫描

分组查询

SELECT 列名,... FROM 表名 WHERE 条件 GROUP BY 列名 [HAVING 分组后过滤条件];
SELECT c_id, MAX(score) FROM tb_score GROUP BY c_id; # 查询每科最高分

WHERE、HAVING区别

执行时机不同: WHERE是分组之前进行过滤，不满足WHERE条件，不参与分组；而HAVING是分组之后对结果进行过滤；
判断条件不同: WHERE不能对聚合函数进行判断，而HAVING可以。

注意事项

1. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义；
2. 执行顺序: WHERE > 聚合函数 > HAVING ；
3. 支持多字段分组, 具体语法为 : GROUP BY 列名1, 列名2
4. 能够在WHERE过滤的数据不要放到HAVING中进行过滤,否则影响SQL语句的执行效率;

排序查询

# 多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序. ASC表示升序(默认),DESC表示降序
SELECT 列名,... FROM 表名 ORDER BY 列名1 ASC|DESC , 列名2 ASC|DESC;
SELECT id, `name`, birthday FROM tb_student ORDER BY birthday DESC; # 根据生日倒序显示学生列表

分页查询

# offset: 偏移量(从哪一行开始返回),初始行偏移量为0,非必填
# rows: 返回具体行数,从 offset+1行开始
SELECT 列名,... FROM 表名 LIMIT 分页参数[offset, rows];

# 分页SQL,pageNo:第几页 pageSize:每页多少条记录
SELECT 列名,... FROM 表名 LIMIT (pageNo -1) * pageSize, pageSize

SELECT id, `name`, birthday FROM tb_student LIMIT 2,2; # 分页查询学生列表(第2页,每页2条)

连表查询

连接查询(跨表查询)按连接方式分为：内连接、外连接和全连接。内连接分为等值连接、非等值连接和自连接；外连接分为左外连接、右外连接。

# 内连接
SELECT 列名,... FROM 表1 t1 JOIN 表2 t2 ON t1.列名 = t2.列名; # 等值内连接
SELECT 列名,... FROM 表1 t1 JOIN 表2 t2 ON t1.列名 BETWEEN 值1 AND 值2; # 非等值内连接
SELECT 列名,... FROM 表1 t1 JOIN 表1 t2 ON t1.列名 = t2.列名; # 自连接内连接

# 外连接 : 左外连接和右外连接可以互换
SELECT 列名,... FROM 表1 t1 LEFT JOIN 表2 t2 ON t1.列名 = t2.列名; # 左外连接
SELECT 列名,... FROM 表1 t1 RIGHT JOIN 表2 t2 ON t1.列名 = t2.列名; # 右外连接

# 全连接 : 将左表和右表的所有行合并
SELECT 列名,... FROM 表1 t1 FULL JOIN 表2 t2 ON t1.列名 = t2.列名;

# 查询学生课程及成绩(内连接)
SELECT 
  t1.`name`, t3.`name` AS courseName, t2.score
FROM tb_student t1
JOIN tb_score t2 ON t1.id = t2.s_id
JOIN tb_course t3 ON t2.c_id = t3.id

# 查询学生课程及成绩(左外连接)
SELECT t1.`name`, t3.`name` AS courseName, t2.score
FROM tb_student t1
       LEFT JOIN tb_score t2 ON t1.id = t2.s_id
       LEFT JOIN tb_course t3 ON t2.c_id = t3.id

子查询

# 查询语文不及格的学生(子查询)
SELECT `name` FROM tb_student 
WHERE id IN (SELECT s_id FROM tb_score where score <60 AND c_id = '917fc339c60f4e39910491714afdde8f')

# 查询语文成绩大于90分的学生(子查询)
SELECT `name` FROM tb_student
WHERE id IN
(SELECT s_id FROM tb_score WHERE score > 90 AND c_id = '917fc339c60f4e39910491714afdde8f')

联合查询

# 查询职称为初级和高级的教师
SELECT id, name FROM tb_teacher WHERE title = '高级'
UNION 
SELECT id, name FROM tb_teacher WHERE title = '初级'

UNION和UNION ALL的区别

UNION 合并结果集，会对结果进行去重和排序。
UNION ALL 只是合并查询结果集，不会去重和排序操作，不需要去重时，效率高。