MySQL基础回顾 | 表连接与Group By

Posted on | In | | Visitors:

MySQL基础回顾第一篇，回顾4种表连接的含义、与笛卡尔积的区别以及group by的用途。

表连接

User表信息如下：

userid username orgid
1 jay 10
2 tom 11
3 shelly 12

Org表信息如下：

orgid orgname
10 gdut
12 gzut
15 skj

表连接语法

表连接的语法为：A [left|right|inner|full] join B on A.x = B.y。[]内的选项4选一，不填写时默认表示表示inner。4个选项的含义如下：

  • inner：内连接
  • left：左连接
  • right：右连接
  • full：全连接

表连接解释

下图为表连接的文氏图表示，中间蓝色部分表示A.x = B.y的A表与B表的行记录，即A的x字段与B的y字段相等，x与y称为连接字段。习惯上，我们把join左边的表称为左表，其右边的表称为右表，在本例中A是左表，B是右表。

image.png

对于join，可以理解为把A的所有列和B的所有列并在一起。以上面的表格为例，A表和B表的列并在一起变成了：

userid username orgid(user表) orgid(org表) orgname

此时由于orgid字段重名，我们需通过select挑出需要的字段。以上面的表格为例，可编写SQL如下挑出所需字段：

1
SELECT u.userid, u.username, u.orgid, o.orgname FROM user u join org o ON u.orgid = o.orgid

left join时，A表（左表）的所有行原封不动的保留，B表（右表）的行保留情况如下，

  • 上午文氏图的蓝色部分：B表的所有行原封不动的保留；
  • 上午文氏图的右白色部分：B表的所有行的值全部显示为NULL；
userid username orgid orgname
1 jay 10 gdut
2 tom 11 NULL
3 shelly 12 gzut

right join时，与right join刚好相反，B表（右表）的所有行原封不动的保留，A表（左表）仅有与B表orgid重叠的行原封不动的保留。

userid username orgid orgname
1 jay 10 gdut
3 shelly 12 gzut
NULL NULL 15 skj

inner join，仅保留A表与B表orgid重叠的行。

userid username orgid orgname
1 jay 10 gdut
3 shelly 12 gzut

full join，用集合运算可理解为(left join) + (right join) - (inner join)。

userid username orgid orgname
1 jay 10 gdut
2 tom 11 NULL
3 shelly 12 gzut
NULL NULL 15 skj

笛卡尔积Cross Join

这是一种特殊的Join，以上文的user表cross join org表为例，全连接SQL如下：

1
2
3
4
5
6
7
SELECT 
    u.userid,
    u.username,
    u.orgid,
    o.orgname
FROM
    user u cross join org o -- 注：cross join可用逗号表示

得到的结果如下：

userid username orgid orgname
1 jay 10 gdut
1 jay 10 gzut
1 jay 10 skj
2 tom 11 gdut
2 tom 11 gzut
2 tom 11 skj
3 shelly 12 gdut
3 shelly 12 gzut
3 shelly 12 skj

也就说，在cross join不设定where条件的情况下，得到的结果是一个笛卡尔积，总行数等于左表的行数乘以B表的行数。在指定where条件的情况下，下文的SQL等同于inner join。

1
2
3
4
5
6
7
8
9
SELECT 
    u.userid,
    u.username,
    u.orgid,
    o.orgname
FROM
    user u, org o -- 注：cross join可用逗号表示
WHERE
    u.orgid = o.orgid

group by

group by的语义是把行进行分组，如：

1
2
3
4
5
6
7
8
SELECT 
    u.userid,
    u.username,
    u.orgid,
    o.orgname
FROM
    user u cross join org o
GROUP BY u.userid

以上SQL，会把userid相同的分成一个组，然后仅保留组内的第一条记录，得到的结果为：

userid username orgid orgname
1 jay 10 gdut
2 tom 11 gdut
3 shelly 12 gdut

基于group by分组的特性，一般其会配置聚合函数使用，如count()和avg()等函数。修改SQL如下：

1
2
3
4
5
SELECT 
    u.userid, count(*)
FROM
    user u cross join org o
GROUP BY u.userid

将得到结果：

userid count
1 3
2 3
3 3

group by后跟一个字段，表示一维分组，如果跟两个字段则为二维分组，三个字段及以上以此类推。以二重group by为例，group by a, b可以通俗的理解为：所有a字段值相同，同时b字段值相同的行，会被分到同一组。