菜鸟的MySQL学习之旅（四）—表联结

1. MySQL别名

1.1 列别名

有时，列的名称是一些表达式，使查询的输出很难理解。要给列一个描述性名称，可以使用列别名。以下语句说明了如何使用列别名：

SELECT 
 [column_1 | expression] AS descriptive_name
FROM table_name;

如果别名中包含空格则使用如下格式：

SELECT 
 [column_1 | expression] AS `descriptive name`
FROM table_name;

此外表达式也可以使用别名，但注意，不能在WHERE子句中使用列别名。原因是当MySQL评估求值WHERE子句时，SELECT子句中指定的列的值可能尚未确定。

• 示例
  使用函数CONCAT_WS(separator,str1,str2,…)来组合employees表全名

SELECT CONCAT_WS(',',lastName,firstName) AS 'Full name'
FROM employees;

1.2 表别名

可以使用别名为表添加不同的名称。使用AS关键字在表名称分配别名，如下查询语句语法：

table_name AS table_alias

该表的别名称为表别名。像列别名一样，AS关键字是可选的，所以完全可以省略它。一般在包含INNER JOIN，LEFT JOIN，self join子句和子查询的语句中使用表别名。

• 示例

SELECT
 customerName,
 COUNT(o.orderNumber) total
FROM
 customers AS c
INNER JOIN orders AS o ON c.customerNumber = o.customerNumber
GROUP BY
 customerName
HAVING total >=5
ORDER BY
 total DESC;

2. 联结

2.1 INNER JOIN

MySQL INNER JOIN子句将一个表中的行与其他表中的行进行匹配，并允许从两个表中查询包含列的行记录。
INNER JOIN子句是SELECT语句的可选部分，它出现在FROM子句之后。
在使用INNER JOIN子句之前，必须指定以下条件：

• 首先，在FROM子句中指定主表
• 其次，表中要连接的主表应该出现在INNER JOIN子句中。理论上说，可以连接多个其他表。 但是，为了获得更好的性能，应该限制要连接的表的数量(最好不要超过三个表)。
• 第三，连接条件或连接谓词。连接条件出现在INNER JOIN子句的ON关键字之后。连接条件是将主表中的行与其他表中的行进行匹配的规则。
  具体语法如下：

ELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;

示例：

SELECT 
   productCode，
   productCodeName,
   textDescription
FROM 
      products AS t1
             INNER JOIN 
      productionlines AS t2 ON t1.productline = t2.productline;

注意：如果连接具有相同列名称的多个表，则必须使用表限定符引用SELECT和ON子句的列，以避免列错误。例如，如果t1和t2表都具有名为c的一个相同列名，则必须在SELECT和ON子句中使用表限定符，如使用t1.c或t2.c指定引用是那个表中的c列。当然也可以使用表别名。

2.2 lEFT JOIN

MySQL LEFT JOIN子句允许您从两个或多个数据库表查询数据。LEFT JOIN子句是SELECT语句的可选部分，出现在FROM子句之后。

SELECT 
    t1.c1, t1.c2, t2.c1, t2.c2
FROM
    t1
        LEFT JOIN
    t2 ON t1.c1 = t2.c1;

• 当使用LEFT JOIN子句将t1表加入t2表时，如果来自左表t1的行与基于连接条件(t1.c1 = t2.c1)的右表t2匹配，则该行将被包含在结果集中。
• 如果左表中的行与右表中的行不匹配，则还将选择左表中的行并与右表中的“假”行组合。“假”行对于SELECT子句中的所有相应列都包含NULL值
• 示例

SELECT 
    c.customerNumber, 
    c.customerName, 
    orderNumber, 
    o.status
FROM
    customers c
        LEFT JOIN
    orders o ON c.customerNumber = o.customerNumber;

注：可以在其后接如下where限定子句来筛选不匹配行

WHERE
    orderNumber IS NULL;

2.3 CROSS JOIN

1. CROSS JOIN子句从连接的表返回行的笛卡儿乘积
2. 假设使用CROSS JOIN连接两个表。 结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。 当连接的表之间没有关系时，会使用这种情况。
3. 下面说明连接两个表：T1和T2的CROSS JOIN子句的语法：

SELECT 
    *
FROM
    T1
        CROSS JOIN
    T2;

如果T1和T2有关系可以使用如下where子句限定，同INNER JOIN 子句相同

SELECT 
    *
FROM
    T1
        CROSS JOIN
    T2
WHERE
    T1.id = T2.id;

2.4 自联结

将表自身连接，这被称为自连接，要执行自联接操作必须使用表别名来帮助MySQL在单个查询中区分左表与同一张表的右表。

• 示例

SELECT 
    c1.city, c1.customerName, c2.customerName
FROM
    customers c1
        INNER JOIN
    customers c2 ON c1.city = c2.city
        AND c1.customername > c2.customerName
ORDER BY c1.city;

2.5 UNION

• 描述
  MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
• 语法

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

• 参数

1. expression1, expression2, … expression_n: 要检索的列。
2. tables: 要检索的数据表。
3. WHERE conditions: 可选， 检索条件。
4. DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
5. ALL: 可选，返回所有结果集，包含重复数据。

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

2.6 区别与联系

1. 首先UNION最好区别开来，MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中，语法与其他四个也截然不同。
2. 其次是CROSS JOIN联结，从连接的表返回行的笛卡儿乘积，但是当两张表之间有联系时，用where子句限定，同INNER JOIN。
3. 而INNER JOIN 和LEFT JOIN的关系可以用两张图来说明：
   INNER JOIN 结果集中的行必须出现在两个表中：t1和t2，如两个圆的交叉部分所示
   
   LEFT JOIN结果中两个圆圈之间的交点是两个表中匹配的行，左圆的剩余部分(白色部分)是t1表中不存在t2表中任何匹配行的行。 因此，左表中的所有行都包含在结果集中。
   
4. 至于自联结是将表自身连接，使用表别名来区分，可以用到前面的三种联结方式

3. 实践项目

3.1 组合两张表

在数据库中创建表1和表2，并各插入三行数据（自己造）
表1: Person
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主键

表2: Address
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State
第一步：建表
建立Person表

CREATE TABLE IF NOT EXISTS Person (
    PersonId INT(11) AUTO_INCREMENT,
    FirstName varchar(45) DEFAULT NULL,
    LastName VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY (PersonId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO Person(PersonId,FirstName,LastName)
VALUES (1,'Zhang','Hua'),
(2,'Li','Fang'),
(3,'Zou','Kai');
/*结果*/
SELECT *
FROM Person;
+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Zhang     | Hua      |
|        2 | Li        | Fang     |
|        3 | Zou       | Kai      |
+----------+-----------+----------+

建立Address表

CREATE TABLE IF NOT EXISTS Address (
    AddressId INT(11) AUTO_INCREMENT,
    PersonId INT(11) DEFAULT NULL,
    City varchar(45) DEFAULT NULL,
    State VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY (AddressId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO Address(AddressId,PersonId,City,State)
VALUES (66,1,'A','N'),
(77,2,'B','Y'),
(88,3,'C','U');
/*结果*/
SELECT *
FROM Address;
+-----------+----------+------+-------+
| AddressId | PersonId | City | State |
+-----------+----------+------+-------+
|        66 |        1 | A    | N     |
|        77 |        2 | B    | Y     |
|        88 |        3 | C    | U     |
+-----------+----------+------+-------+

第二步：编写查询命令

SELECT p.FirstName,p.LastName,a.City,a.State
FROM person AS p
      INNER JOIN
     address AS a ON p.PersonId = a.PersonId;

结果：

3.2 删除重复邮箱

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Email表应返回以下几行:
±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | a@b.com |
| 2 | c@d.com |
±—±-----------------+
第一步：建表
实践项目中的建表过程
第二步：编写查询

DELETE FROM email
WHERE ID NOT IN (
     SELECT minID
     FROM (
           SELECT MIN(Id) AS minID
           FROM email
           GROUP BY Email
     ) tab1
);

SELECT *
FROM email;

结果：