MySQL入门（四）：表联结
一、MySQL别名
（一）为表取别名
在查询数据时，如果表名很长，使用起来会不方便，此时，就可以为表取一个别名，用这个别名来代替表的名称。通用语法如下：
SELECT * FROM 表名 [AS] 表别名;
（二）为列取别名
在查询数据时，为了使显示的查询结果看起来更直观。此时，可以为字段取一个别名，通用语法如下：
SELECT 列名 AS 列别名 FROM 表名;
注意：在指定别名的时AS可以省略不写
在下面的情况下，使用别名很有用：
- 在查询中涉及超过一个表
- 在查询中使用了函数
- 列名称很长或者可读性差
- 需要把两个列或者多个列结合在一起
二、SQL连接
SQL join 用于把来自两个或多个表的行结合起来。下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。
在这里插入图片描述
（一）INNER JOIN
1.语句解释
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录，是使用最多的一种连接类型。
内连接是完全匹配，即A表与B表一一对应，如果一张表里没有，则记录不会被查出。
在这里插入图片描述
2.通用语法
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
或
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
（二）LEFT JOIN / RIGHT JOIN
1.语句解释
LEFT JOIN 关键字从左表（table1）返回所有的行（即以左表为主表，显示左表的所有信息），即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
注：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。
在这里插入图片描述
2.通用语法
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
或
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
（三）CROSS JOIN
1.语句解释
CROSS JOIN 交叉连接，得到的结果是两个表的乘积把表A和表B的数据进行一个N*M的组合，即[笛卡尔积]
假设使用CROSS JOIN连接两个表。 结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。 当连接的表之间没有关系时，会使用这种情况。
要特别注意的是，如果每个表有1000行，那么结果集中就有1000 x 1000 = 1,000,000行，那么数据量是非常巨大的。
2.通用语法
SELECT * FROM A CROSS JOIN B;
如果添加了WHERE子句，如果T1和T2有关系，则CROSS JOIN的工作方式与INNER JOIN子句类似
四、自连接
1.语句解释
自连接本质上即将一张表看成是两张表。
2.示例语法
SELECT a.ename as '备注aname', b.ename as '备注bname'
FROM table1 AS a, table1 AS b
WHERE a.leaderid = b.eid;
五、UNION
1.语句解释
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。多个 SELECT 语句会删除重复的数据。
2.通用语法
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
- expression1, expression2, … expression_n: 要检索的列。
- tables: 要检索的数据表。
- WHERE conditions: 可选， 检索条件。
- DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
- ALL: 可选，返回所有结果集，包含重复数据。
注：UNION语句用于将不同表中相同列中查询的数据展示出来（不包括重复数据），UNION ALL语句包括重复数据。
小结
- INNER JOIN：全连接，求交集，是等值连接
- LEFT JOIN：左连接，以左表为主表，右表中若没有匹配则以null值取代
- CROSS JOIN：表A和表B的数据进行一个N*M的组合，即笛卡尔集
- 自连接：本质是把一张表当成两张表来用
- UNION：连接两个以上的SELECT语句
UNION和INNER JOIN的区别是一个是连接两张表，另一个是连接两个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 UNSIGNED AUTO_INCREMENT,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100) NOT NULL,
PRIMARY KEY (PersonId)
);
CREATE TABLE Address
(
AddressId INT UNSIGNED,
PersonId INT UNSIGNED,
City VARCHAR(100) NOT NULL,
State VARCHAR(100) NOT NULL,
PRIMARY KEY (AddressId)
);
--插入数据
INSERT INTO Person (PersonId ,FirstName, LastName) VALUES(1,"Ming","Li");
INSERT INTO Person (PersonId ,FirstName, LastName) VALUES(2,"Long","Zhang");
INSERT INTO Person (PersonId ,FirstName, LastName) VALUES(3,"Hong","Wang");
INSERT INTO Person (PersonId ,FirstName, LastName) VALUES(4,"Belen","Sonia");
INSERT INTO Address (AddressID, PersonID, City, State) VALUES(1,1,"Hangzhou","China");
INSERT INTO Address (AddressID, PersonID, City, State) VALUES(2,2,"Beijing","China");
INSERT INTO Address (AddressID, PersonID, City, State) VALUES(3,3,"Chongqing","China");
--查询信息
SELECT p.FirstName,p.LastName,a.City,a.State
FROM Person p
LEFT JOIN Address a
ON p.PersonId = a.PersonId
---------------------
在这里插入图片描述
项目六：删除重复的邮箱（难度：简单）
编写一个 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 |
+----+------------------+
--建立表格
CREATE TABLE email
(
id INT PRIMARY KEY,
Email VARCHAR(50) NOT NULL
);
--导入数据
INSERT INTO email (id,Email) VALUES(1,"a@b.com");
INSERT INTO email (id,Email) VALUES(2,"c@d.com");
INSERT INTO email (id,Email) VALUES(3,"a@b.com");
--自连接
DELETE e1
FROM email e1,email e2
WHERE e1.Email = e2.Email
AND e1.id > e2.id;
--查询信息
SELECT * FROM email;
在这里插入图片描述
参考资料：
1.SQL 教程
2.别名，连接查询，合并查询
3.MySQL-任务四（表联结）
4.MySQL 基础 （三）- 表联结
5.MySQL交叉连接（CROSS JOIN）
