[关闭]
@TryLoveCatch 2021-01-27T03:27:16.000000Z 字数 3732 阅读 1241

SQL语句拾遗

sql


前提

Tab1

id size
1 10
2 20
3 30

Tab2

size name
10 AAA
20 BBB
20 CCC
40 DDD

左连接

  1. select * form tab1 left join tab2 on tab1.size = tab2.size

结果如下:

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 BBB
3 30 (null) (null)

内连接

  1. select * form tab1 inner join tab2 on tab1.size = tab2.size

结果如下:

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 BBB

右连接

  1. select * form tab1 right join tab2 on tab1.size = tab2.size

结果如下:

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 BBB
(null) (null) 40 DDD

普通where语句

  1. select * form tab1, tab2 where tab1.size = tab2.size

结果如下:

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 BBB

全链接

  1. select * form tab1 full join tab2 on tab1.size = tab2.size

结果如下:

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 BBB
3 30 (null) (null)
(null) (null) 40 DDD

结论

1、内连接和我们平时所用的where语句效果一致,即两个表的共同的部分
2、左连接,即已左边的表为主表,右边的表为副表,将主表中需要的字段全部列出,然后将副表中的数据按照on查询条件与其对应起来
3、右连接,与2相反
4、全连接,则是将两个表的需要的字段的数据全排列。



多表左连接

TAB3

name age
AAA 19
CCC 21
DDD 22
EEE 23

我们来看一个sql语句

  1. select * form tab1
  2. left join tab2
  3. on tab1.size = tab2.size
  4. left join tab3
  5. ont tab2.name = tab3.name

当多个表的时候,left join到底是怎么工作的?tab1是tab2的左表,然后tab2是tab3 的左表?还是tab1是左表,tab2和tab3都是右表?

结论:

tab1 左连接 tab2,以tab1为基础进行连接运算,得到新表即临时表(table)。
table 左连接 tab3, table为基础进行连接运算,得到最终结果。

关于on和where

我们再来看两个sql语句:

  1. 1select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA
  2. 2select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条语句

第一步:
on条件: tab1.size = tab2.size,生成一张中间表

  1. select * form tab1 left join tab2 on (tab1.size = tab2.size)

结果如下:

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 BBB
3 30 (null) (null)

第二步:
再对中间表 过滤where 条件:
tab2.name=’AAA’

  1. where tab2.name=’AAA

结果如下:

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA

第二条语句

on条件:
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录)

结果如下:

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)

结论:

1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

有一个需要注意的:
左连接中,必然会返回左表的所有记录,而on之后,如果右表有重复的数据,例如tab2,如果符合on的条件,中间表就会增加数据;如果不符合on的条件,中间表中的右表相关数据就都为null,也就是说on只会增加数据,不会减少数据,除非用where

CASE

TAB4:

country population
中国 600
美国 100
加拿大 100
英国 200
法国 300
日本 250
德国 200
墨西哥 50
印度 250

期望的结果如下:

人口
亚洲 1100
北美洲 250
其他 700

这时,我们可以使用CASE语句,如下:

  1. SELECT SUM(population),
  2. CASE country
  3. WHEN '中国' THEN '亚洲'
  4. WHEN '印度' THEN '亚洲'
  5. WHEN '日本' THEN '亚洲'
  6. WHEN '美国' THEN '北美洲'
  7. WHEN '加拿大' THEN '北美洲'
  8. WHEN '墨西哥' THEN '北美洲'
  9. ELSE '其他' END
  10. FROM tab4
  11. GROUP BY CASE country
  12. WHEN '中国' THEN '亚洲'
  13. WHEN '印度' THEN '亚洲'
  14. WHEN '日本' THEN '亚洲'
  15. WHEN '美国' THEN '北美洲'
  16. WHEN '加拿大' THEN '北美洲'
  17. WHEN '墨西哥' THEN '北美洲'
  18. ELSE '其他' END

我们再来看一个例子:
TAB5

country sex population
中国 1 340
中国 2 260
美国 1 45
美国 2 55
加拿大 1 51
加拿大 2 49
英国 1 40
英国 2 60

期望的结果如下:

国家
中国 340 260
美国 45 55
加拿大 51 49
英国 40 60

当然,继续使用CASE,如下:

  1. SELECT country,
  2. SUM( CASE WHEN sex = '1' THEN
  3. population ELSE 0 END), --男性人口
  4. SUM( CASE WHEN sex = '2' THEN
  5. population ELSE 0 END) --女性人口
  6. FROM tab5
  7. GROUP BY country;

group by

当有group by时,select后的字段,必须在gorup by里面或者在聚合函数里面。
先执行group by,然后执行聚合函数。

https://zhuanlan.zhihu.com/p/46869970

先来看下 TAB6:

id name number
1 aa 2
2 aa 3
3 bb 4
4 bb 5
5 cc 6
6 dd 7
7 ee 7
8 bb 5
9 cc 6

FROM TAB6 Group BY name:该句执行后,我们想象生成了虚拟表,如下所示,生成过程是这样的:group by name,那么找name那一列,具有相同name值的行,合并成一行,如对于name值为aa的,那么第一行和第二行数据就合并成1行,所有的id值和number值写到一个单元格里面。

虚拟表如下:

id name number
1
2
aa 2
3
3
4
8
bb 4
5
5
5
9
cc 6
6
6 dd 7
7 ee 7

接下来就要针对虚拟表执行Select语句了:

(1)如果执行select *的话,那么返回的结果应该是虚拟表,可是id和number中有的单元格里面的内容是多个值的,而关系数据库就是基于关系的,单元格中是不允许有多个值的,所以你看,执行select * 语句就报错了。

(2)我们再看name列,每个单元格只有一个数据,所以我们select name的话,就没有问题了。为什么name列每个单元格只有一个值呢,因为我们就是用name列来group by的。

(3)那么对于id和number里面的单元格有多个数据的情况怎么办呢?答案就是用聚合函数,聚合函数就用来输入多个数据,输出一个数据的。如cout(id)sum(number),而每个聚合函数的输入就是每一个多数据的单元格。

(4)例如我们执行select name,sum(number) sum from test group by name,那么sum就对虚拟表的number列的每个单元格进行sum操作,例如对name为aa的那一行的number列执行sum操作,即2+3,返回5,最后执行结果如下:

id sum
aa 5
bb 14
cc 12
dd 7
ee 7

(5)group by 多个字段该怎么理解呢:如group by name,number,我们可以把name和number 看成一个整体字段,以他们整体来进行分组的。

count

https://www.jianshu.com/p/61b9ae271cdc

count(*):所有行进行统计,包括NULL行
count(1):所有行进行统计,包括NULL行
count(column):对column中非Null进行统计

参考

多表联合查询(inner outer join 左右连接)详解
left join on和where条件的放置
探究SQL中Case语句用法

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注