xiaoyh 的个人博客

一个只会敲代码的咸鱼

0%

SQL 进阶（1）

发表于 更新于 分类于

先创建一个名为 website 的数据表作为示例使用。

1
2
3
4
5
6
7
8
9
10
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/  |  3    | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ | 0     | IND     |
+----+---------------+---------------------------+-------+---------+

SELECT TOP，LIMIT，ROWNUM 子句

SELECT TOP 子句用于规定要返回的记录的数目，不过并非所有的数据库系统都支持 SELECT TOP 语句。
MySQL 支持 LIMIT 语句来选取指定的条数数据
Oracle 可以使用 ROWNUM 来选取。

1
2
3
4
5
6
7
8
9
10
11
12
-- TOP 查询前 n 项指定字段的数据
select top n col1, col2 from table_name;

-- Mysql LIMIT
select col1, col2 from table_name limit n;

-- Oracle ROWNUM
select col1, col2 from table_name where ROWNUM <= n;

-- Advanced: SELECT TOP PERCENT 百分比
-- 选取前 50% 的数据
select top 50 percent col1, col2 from table_name;

别名

可以为表名称或列名称指定别名。大多数情况，是为了让列名称的可读性更强。

1
select country as co from website;

得到

1
2
3
4
5
6
7
8
| co  |
|-----|
| USA |
| CN  |
| CN  |
| CN  |
| USA |
| IND |

利用别名与 CONCAT 将查询结果的列合并

CONCAT 可以把查询结果的列合并，但是其合并的字段名没有意义，可以通过别名赋予其意义

1
select name, concat(url, ',', alexa, ',', country) as web_info from website;
1
2
3
4
5
6
7
8
| name         |     web_info                     |
|--------------|----------------------------------|
| Google       |  https://www.google.cm/,1,USA    |
| 淘宝         |  https://www.taobao.com/,13,CN   |
| 菜鸟教程     |  http://www.runoob.com/,4689,CN  |
| 微博         |  http://weibo.com/,20,CN         |
| Facebook     |  https://www.facebook.com/,3,USA |
| stackoverflow | http://stackoverflow.com/,0,IND |

JOIN 子句

JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。JOIN 一共有 7 种用法，最常见的 JOIN 类型为 INNER JOIN，意为从多个表中返回满足条件的所有行。

再创建一个新表 access_log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
|  10 |       6 |   333 | 2016-05-17 |
+-----+---------+-------+------------+

执行

1
2
3
4
-- 默认 join == inner join
select website.name, access_log.count
from website join access_log
on website.id = access_log.site_id;

结果

1
2
3
4
5
6
7
8
9
10
11
12
13
+--------------+---------+
| name         | count   |
+--------------+---------+
| Google       |  45     |
| 菜鸟教程      |  100    |
| Google       |  230    |
| 淘宝          | 10      |
| Facebook     | 205     |
| 微博          | 13      |
| 菜鸟教程      | 220     |
| Facebook     | 545     |
| 菜鸟教程      | 201     |
+--------------+---------+

外连接（OUTER JOIN）

取得两张表中满足存在连接匹配关系的记录，以及某张表（或两张表）中不满足匹配关系的记录。

外连接又分为左连接，右连接，全连接：

左外接（LEFT JOIN）

在内连接的基础之上还显示左表不满足匹配关系的记录，不存在的结果为 NULL 。

1
2
3
select website.name, access_log.count
from website left join access_log
on website.id = access_log.site_id;

得

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+--------------+---------+
| name         | count   |
+--------------+---------+
| Google       |  45     |
| 菜鸟教程      |  100    |
| Google       |  230    |
| 淘宝          | 10      |
| Facebook     | 205     |
| 微博          | 13      |
| 菜鸟教程      | 220     |
| Facebook     | 545     |
| 菜鸟教程      | 201     |
| stackoverflow| NULL    |  --> 右表没匹配也会记录
+--------------+---------+

右外接（RIGHT JOIN）

在内连接的基础之上还显示右表不满足匹配关系的记录，不存在的结果为 NULL 。

1
2
3
select website.name, access_log.count
from website right join access_log
on website.id = access_log.site_id;

得

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+--------------+---------+
| name         | count   |
+--------------+---------+
| Google       |  45     |
| 菜鸟教程      |  100    |
| Google       |  230    |
| 淘宝          | 10      |
| Facebook     | 205     |
| 微博          | 13      |
| 菜鸟教程      | 220     |
| Facebook     | 545     |
| 菜鸟教程      | 201     |
| NULL         | 333     |  --> 左表没匹配也会记录
+--------------+---------+

全外连（FULL OUTER JOIN）

在内连接的基础之上，左右表不满足匹配关系的记录都显示，不存在的结果为 NULL 。

1
2
3
4
-- MySQL 不支持全外连
select website.name, access_log.count
from website full outer join access_log
on website.id = access_log.site_id;

得

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+--------------+---------+
| name         | count   |
+--------------+---------+
| Google       |  45     |
| 菜鸟教程      |  100    |
| Google       |  230    |
| 淘宝          | 10      |
| Facebook     | 205     |
| 微博          | 13      |
| 菜鸟教程      | 220     |
| Facebook     | 545     |
| 菜鸟教程      | 201     |
| stackoverflow| NULL    |  --> 右表没匹配
| NULL         | 333     |  --> 左表没匹配
+--------------+---------+

交叉连接（CROSS JOIN）

交叉连接，又名笛卡儿积，其概念如下：

有两个集合 A = {0,1} , B = {2,3,4}

那么，集合 A 与 B 的笛卡儿积 A * B 得到的结果是

A * B = {(0,2)、(1,2)、(0,3)、(1,3)、(0,4)、(1,4)};

B * A = {(2,0)、{2,1}、{3,0}、{3,1}、{4,0}、(4,1)};

上面 A * B 和 B * A 的结果就可以称为两个集合相乘的笛卡尔积

我们可以得出结论，A 集合和 B 集合相乘，包含了集合 A 中的元素和集合 B 中元素之和，也就是 A 元素的个数 x B 元素的个数

对于 SQL，笛卡儿积就是把左表的每一条数据（设共有 m 条）与右表的每一条数据（设共有 n 条）做拼接，因此其结果总共有 m x n 条。

1
select * from left_table cross join right_table;

UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
其中，每个 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。

1
2
3
select id from website
union
select site_id from access_log;

得

1
2
3
4
5
6
7
8
9
| id |
|----|
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
| 6  |
| 7  |

UNION 操作符默认会选取不同的值。如果允许重复的值，请使用 UNION ALL。

1
2
3
select id from website
union all
select site_id from access_log;

得

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| id |
|----|
| 1  |
| 1  |
| 1  |
| 2  |
| 2  |
| 3  |
| 3  |
| 3  |
| 3  |
| 4  |
| 4  |
| 5  |
| 5  |
| 5  |
| 6  |
| 7  |