数据库学习纪要（二十七）：MySQL-7-外联接、自联接与联合

九、外联接、自联接与联合
外联接：可以在表中没有匹配记录的情况下返回记录
自联接：可以联接表本身
联合：可以合并查询结果

1、外联接
//LEFT OUTER JOIN（左外联接）接收左表的所有行，并用这些行与右表的行匹配。当左表与右表具有一对多关系时，左外联接特别有用。
//LEFT OUTER JOIN会匹配左表中的每一行及右表中符合条件的行。
//理解外联接的最大秘密在于知道表在左边还是在右边，在LEFT OUTER JOIN中，出现在FROM后、联接前的表称为左表，而出现在联接后的表就是右表。

SELECT g.gril, t.toy FROM girls g
LEFT OUTER JOIN toys t
ON g.toy_id = t.toy_id;
//LEFT OUTER JOIN会取得左表(girls)的所有行，并把这些行与右表(toys)的行进行匹配
//本例中左外联接的查询结果与使用内联接时一样，区别是：外联接一定会提供数据行，无论该行能否在另一个表中找出相匹配的行
//左外联接的结果中出现NULL表示右表中没有找到与左表相符的记录

2、自联结
在规范的数据库中，相同的表不会出现两次。因此可以用自联接(self-join)以模拟联接两张表的效果
注：自联接能把单一表当成两张具有完全相同的信息的表进行查询

SELECT c1.name, c2.name AS boss FROM clown_info c1
INNER JOIN clown_info c2
ON c1.boss_id = c2.id;
//本例中使用了clown_info表两次，第一次设定它的别名为c1，第二次则设定别名为c2。然后利用内联接来连接boss_id（来自c1）与头领的姓名（name，来自c2）

3、UNION
UNION根据我们在SELECT中指定的列，把两张表或更多张表的查询结果合并至一个表中。可以把UNION的查询结果想成“重叠了”每个SELECT的查询结果

SELECT title FORM job_current
UNION
SELECT title FROM job_desired
UNION
SELECT title FROM job_listings
ORDER BY title;

4、INTERSECT(交集)与EXCEPT(差集)

SELECT title FROM job_current
INTERSECT/EXCEPT
SELECT title FROM job_desired;

5、关键字ANY、ALL
案例：
SELECT name, rating FROM restaurant_ratings
WHERE rating IN
(SELECT rating FROM restaurant_ratings WHERE rating > 3 AND rating < 9);

使用ALL:
SELECT name, rating FROM restaurant_ratings
WHERE rating >/</>=/<= ALL
(SELECT rating FROM restaurant_ratings WHERE rating > 3 AND rating < 9);
//大于加上ALL可以找出任何大于集合中最大值的值
//小于加上ALL可以找出任何小于集合中最小值的值

使用ANY：
SELECT name, rating FROM restaurant_ratings
WHERE rating >/< ANY
(SELECT rating FROM restaurant_ratings WHERE rating > 3 AND rating < 9);
//大于加上ANY可以找出任何大于集合中最小值的值
//小于加上ANY可以找出任何小于集合中最大值的值

6、DATE与TIME类型
DATE  YYYY-MM-DD
DATETIME  YYYY-MM-DD HH:MM:SS
TIMESTAMP YYYYMMDDHHMMSS
TIME  HH:MM:SS

7、创建临时表
CREATE TEMPORARY TABLE my_temp_table
(
some_id INT,
some_data VARCHAR(50)
);

查询创建临时表：
CREATE TEMPORARY TABLE my_temp_table AS 
SELECT * FROM my_permanent_table;

8、转换数据类型
CAST(your_column, TYPE)
TYPE可以是：
CHAR()
DATE
DATETIME
DECIMAL
SIGNED[INTEGER]
TIME
UNSIGNED[INTEGER]
举例：
SELECT CAST('2005-01-01' AS DATE);
SELECT CAST(2 AS DECIMAL);