SQL基础 —— 查询(SELECT)、连接(JOIN)

SQL基础 —— 查询(SELECT)、连接(JOIN

自实习以来天天关注的都是业务,好久不写SQL了,感觉都要还给老师了,还是复习一下吧(;´д`)ゞ
以后工作中如果有写到SQL,也都记录在这里。


(一)基本查询语句

1
2
3
4
5
6
SELECT [DISTINCT] 属性列表
FROM 表名和视图列表(可以有多个)
[WHERE 条件表达式1]
[GROUP BY 属性名1 [HAVING 条件表达式2] [WITH ROLLUP]]
[ORDER BY 属性名2 [ASC|DESC] [, 属性名3 [ASC|DESC]]]
[LIMIT]

假设表结构如下:

1
2
3
4
5
6
7
8
> SELECT * FROM `employee`;

id name age sex addr
---------------------------------
1 张三 26 男 北京市海淀区
2 李四 25 女 北京市昌平区
3 王五 21 男 湖南长沙
4 Aric 100 男 狗窝

1. 过滤:WHERE

  • 比较:=<<=>>=!=!<!><>
  • 指定范围:BETWEEN ... AND ...NOT BETWEEN ... AND ...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
> SELECT * FROM `employee` 
WHERE employee.age BETWEEN 20 AND 28;

id name age sex addr
---------------------------------
1 张三 26 男 北京市海淀区
2 李四 25 女 北京市昌平区
3 王五 21 男 湖南长沙

######################################################

> SELECT * FROM `employee`
WHERE employee.age NOT BETWEEN 20 AND 28;

id name age sex addr
---------------------------------
4 Aric 100 男 狗窝
  • 指定集合:INNOT IN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
> SELECT * FROM `employee` 
WHERE employee.id IN (1, 2);

id name age sex addr
---------------------------------
1 张三 26 男 北京市海淀区
2 李四 25 女 北京市昌平区

######################################################

> SELECT * FROM `employee`
WHERE employee.name NOT IN ('张三', '李四');

id name age sex addr
---------------------------------
3 王五 21 男 湖南长沙
4 Aric 100 男 狗窝
  • 模糊匹配:LIKENOT LIKE
1
2
3
4
5
6
7
8
> SELECT * FROM `employee` 
WHERE employee.age LIKE '2%';

id name age sex addr
---------------------------------
1 张三 26 男 北京市海淀区
2 李四 25 女 北京市昌平区
3 王五 21 男 湖南长沙
  • 判空:IS NULLIS NOT NULL
  • 多条件:ANDOR

2. 分组:GROUP BY ... HAVING ... WITH ROLLUP

  • 根据GROUP BY后属性的值进行分组

  • 如果GROUP BY后面带有HAVING,那么只有满足HAVING条件的记录才能输出,一般是函数操作,比如max()/min()/sum()/avg()

    WHERE过滤的是分组(GROUP BY)之前的行,HAVING过滤的是分组(GROUP BY)之后的行。

  • 如果有WITH ROLLUP,会在所有记录的最后加一条记录,该记录是上面记录的总和

Demo

1
2
3
4
5
6
7
8
9
10
11
> select * from sc;

name course grade
-------------------
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
【每门分数都大于80分的学生姓名】
1
2
3
4
5
6
> select name from sc 
group by name
having min(grade) > 80;

# GROUP BY name,可以将不同的学生进行分组
# HAVING min(grade) > 80,可以判断同一个学生的成绩最小值是否大于80
【平均分大于80分的学生姓名】
1
2
3
4
5
6
> select name from sc
group by name
having avg(grade) > 80;

# GROUP BY name,可以将不同的学生进行分组
# HAVING avg(grade) > 80,可以判断同一个学生的成绩平均值是否大于80

3. 排序:ORDER BY

  • ASC:升序,默认
  • DESC:降序
1
2
3
4
5
6
7
8
9
> SELECT * FROM employee 
WHERE age <= 25
ORDER BY employee.id DESC;


id name age sex addr
---------------------------------
3 王五 21 男 湖南长沙
2 李四 25 女 北京市昌平区

4. 分页:LIMIT

使用LIMIT可以限制加载到内存的数据条数,否则会将全部满足条件的数据加载到内存。


(二)连接查询:(INNER) JOIN/LEFT JOIN/RIGHT JOIN/FULL OUTER JOIN/UNION (ALL)

参考《SQL的各种连接Join详解》

  • table1:
idname
1Google
2Alibaba
3Tencent
4蚂蚁金服
  • table2:
idaddress
1美国
3中国
5中国
6美国

1.内连接:(INNER) JOIN

返回两个表的笛卡尔乘积:即两表的每一行进行一一匹配,满足ON条件的行返回

语法格式:SELECT ... FROM table1 (INNER) JOIN table2 ON 条件;
如:select * from table1 inner join table2 on table1.id = table2.id;

idnameaddress
1Google美国
3Tencent中国

2.外连接

(1)LEFT JOIN:返回左表全部行

返回左表的全部行和右表满足ON条件的行,如果左表中的行在右表中没有匹配,那么这一行的右表对应数据用null代替

语法格式:SELECT ... FROM table1 LEFT JOIN table2 ON 条件;
如:select * from table1 left join table2 on table1.id = table2.id;

idnameaddress
1Google美国
2Alibabanull
3Tencent中国
4蚂蚁金服null

【驱动表的选择】:大表JOIN小表,哪个放左边?

【2019-12-18 华为云 面试被问到】
最开始我确实被懵了,因为在我的感知中,现今的数据库都会有CBO SQL优化引擎,根据代价自动选择最优的执行路径,哪里还需要你刻意进行这种低级的优化?所以我没有去深入了解过。但不管怎么说,既然不懂,就还是要学习一下。
我想可能是华为云的数据迁移团队面向的数据库没有优化引擎吧……而且那个面试官满口的存储过程、视图、外键(+_+)?
我总感觉他是上个世纪过来的,水平挺低的……

首先解释一下CBORBO

  1. RBO(Rule-Based Optimization,基于规则的优化):优化器在优化SQL时依据的是内部的一些预定规则,对数据不敏感,在这个时代LEFT JOIN的时候大表还是小表放在左表影响很大。

    在这个时期,MySQL中使用!=/IS NULL/IS NOT NULL/<>不会走索引,需要优化

  2. CBO(Cost-Based Optimization,基于代价的优化):现如今的优化器在优化SQL时,会根据代价引擎来估计每个执行计划所需要的代价I/O代价、CPU代价、网络代价……),根据这个代价选择最优的执行方案。

MySQl LEFT JOIN的原理:LEFT JOINMySQL中是从左加载到右,左表作为驱动表,会被加载进入内存,通过nested loop join算法,通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到右表中查询数据,然后合并结果。

所以我个人觉得在不考虑CBO优化的情况下,将小表作为LEFT JOIN左表的原因有二:

  1. 内存:左表是驱动表,需要加载到内存的,小表的内存压力比较小;
  2. 索引:右表不会加载进内存,保存在磁盘上,可以通过索引降低I/O代价。小表数据量少,走不走索引区别不大;但是大表数据量大,走索引会比不走索引快非常多。

如果是RIGHT JOIN的话,如果有上个世纪来的面试官一定要问你,那就是小表放在右边。


(2)RIGHT JOIN:返回右表全部行

返回右表的全部行和左表满足ON条件的行,如果右表中的行在左表中没有匹配,那么这一行的左表对应数据用null代替

语法格式:SELECT ... FROM table1 RIGHT JOIN table2 ON 条件;
如:select * from table1 right join table2 on table1.id = table2.id;

idnameaddress
1Google美国
3Tencent中国
5null中国
6null美国

(3)FULL OUTER JOIN:返回两表全部行

返回两表的全部行,如果其中一个表中的行在另一个表中没有匹配,那么对应数据用null代替

语法格式:SELECT ... FROM table1 FULL OUTER JOIN table2 ON 条件;
如:select * from table1 full outer join table2 on table1.id = table2.id;

idnameaddress
1Google美国
2Alibabanull
3Tencent中国
4蚂蚁金服null
5null中国
6null美国

3.UNION/UNION ALL

  • UNION去重
  • UNION ALL不去重
  • 用于合并两个或多个SELECT的结果集,注意UNION内部的SELECT语句必须有相同数量的列,且列必须有相似的数据类型

    JOIN不要求列结构一致,它会做列的拼接;但是UNION必须要列结构一致,它是行的拼接。


(三)SELECT执行顺序

  1. FROM + JOINJOIN驱动表加载到内存JION非驱动表(或者FROM表)使用磁盘索引
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT:窗口函数
  6. ORDER BY
  7. LIMIT
-------------本文结束感谢您的阅读-------------

本文标题:SQL基础 —— 查询(SELECT)、连接(JOIN)

文章作者:DragonBaby308

发布时间:2019年10月10日 - 21:27

最后更新:2020年02月09日 - 11:01

原始链接:http://www.dragonbaby308.com/SELECT/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

急事可以使用右下角的DaoVoice,我绑定了微信会立即回复,否则还是推荐Valine留言喔( ఠൠఠ )ノ
0%