MySQL 基础 （三）- 表联结

MySQL别名

• AS：语法示例：SELECT 列名 FROM 表名 AS 别名。使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。

INNER JOIN

即内部联结。语法示例：
SELECT 表1.列名 FROM 表1 INNER JOIN 表2 ON 表1.列名 = 表2.列名
其最终效果等同于：
SELECT 表1.列名 FROM 表1 WHERE 表1.列名 = 表2.列名
作用显然，通过某一特定条件把两张表联结到一起，并显示最终符合条件的结果

LEFT JOIN

即左联结。用法与 INNER JOIN 类似，但最终效果与内部联结不同。左联结返回包括左表中的所有记录和右表中联结字段相等的记录。

CROSS JOIN

首先看一下这个语句的用法示例 CROSS JOIN 用法示例
这里面涉及到一个概念，叫笛卡尔积，即由没有联结条件的表关系返回的结果。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。这个概念与numpy 中的广播类似，有兴趣的可以看看这篇文章更深入地了解这一概念。

自连接

首先来看一下自连接的示例：

SELECT
	表 1.列1,
	表 1.列2 
FROM
	表 AS 表 1,
	表 AS 表 2 
WHERE
	表 1.列3 = 表 2.列3

从示例可以看出自连接其实就是把同一张表联结到一起。通常它被用作外部语句来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

UNION

即组合查询。通常用于将多条 SELECT 语句组合成一个结果集。使用情景如下：

1. 单个查询中从不同的表返回类似结构的数据
2. 单个表执行多个查询，按单个查询返回数据

通俗来说，就是将不同的查询语句的结果放到一起输出。使用也非常简单，只需要在两个或以上的查询当中加入 UNION 语句即可。但使用组合查询时有几点需要注意：

• 语句必须由两条或两条以上的SELECT语句组成
• 每个查询必须包含相同的列、表达式或聚集函数
• 列数据类型必须兼容

区别

此处总结一下几个联结的区别：内部联结相当于按照某个条件取两个不同表之间的相交部分，而左联结的作用也一样。区别在于两者最终查询的结果，前者只会显示交集，而后者最终显示的结果行数与第一张表的行数相同，若对应行数无法匹配，则显示为空；交叉联结的作用则是广播，最终显示的结果往往会扩充原来的维度；自连接的作用则是用来替代子查询；组合查询的作用则是把不同的查询结果合并到一起。

作业

作业五

• 组合两张表 （难度：简单）
  在数据库中创建表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

• 建表：

CREATE TABLE `Person` (
`PersonId` INT ( 4 ) NOT NULL AUTO_INCREMENT,
`FirstName` VARCHAR ( 10 ) NOT NULL,
`LastName` VARCHAR ( 10 ) NOT NULL,
PRIMARY KEY ( `PersonId` ) 
);

CREATE TABLE `Address` (
`AddressId` INT ( 4 ) NOT NULL AUTO_INCREMENT,
`PersonId` INT ( 4 ) NOT NULL,
`City` VARCHAR ( 20 ) NOT NULL,
`State` VARCHAR ( 20 ) NOT NULL,
PRIMARY KEY ( `AddressId` ) 
);

• 添加数据：

INSERT INTO `Person` ( `FirstName`, `LastName` )
VALUES
	( 'John', 'Adair' ),
	( 'Mike', 'Adair' ),
	( 'John', 'Adair' );

INSERT INTO `Address` ( `PersonId`, `City`, `State` )
VALUES
	( 2, 'Shenzhen', 'Guangdong' ),
	( 2, 'Guangzhou', 'Guangdong' ),
	( 1, 'Hangzhou', 'Zhenjiang' );

• 查询：

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

作业六

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

CREATE TABLE `email`  (
  `Id` int(4) NOT NULL AUTO_INCREMENT,
  `Email` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
);

• 添加数据：

INSERT INTO email ( `Email` )
VALUES
	( 'a@b.com' ),
	( 'c@d.com' ),
	( 'a@b.com' )

• 查询：

DELETE p1 
FROM
	Person AS p1,
	Person AS p2 
WHERE
	p1.Email = p2.Email 
	AND p1.Id > p2.Id