MySQL 基础 （三）- 表联结

目录

1  MySQL别名

列别名

表别名

2  表联结

2.1 INNER JOIN

2.2 LEFT JOIN和RIGHT JOIN

2.3 CROSS JOIN

2.4 SELF JOIN

2.5 UNION

3  联结方式的区别和联系 

4  实战

4.1 组合两张表

4.2 删除重复的邮箱

---

1  MySQL别名

通过使用 SQL，可以为表名称或列名称指定别名。基本上，创建别名是为了让列名称的可读性更强。

列别名

SELECT column_name AS alias_name FROM table_name;

表别名

SELECT column_name(s) FROM table_name AS alias_name;

• 别名只是在程序中调用时作了改变，但是表的真实名并不改变
• 大部分情况下，用表名的简写可以增加可读性
• 在不影响理解的情况下，AS 关键字可以忽略

2  表联结

将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好，联结是一种机制，用来在一条 SELECT语句中关联表，因此称为联结。

2.1 INNER JOIN

等值联结（ equi join）结也称为内联结（inner join）基于两个表之间的相等条件，有两种形式，返回两个表中column列值相等的数据行，即取交集。

SELECT t1.column1,t2.column2 FROM t1,t2 WHERE t1.column = t2.column;

SELECT t1.column1,t2.column2 FROM t1 INNER JOIN t2 ON t1.column=t2.column;

2.2 LEFT JOIN和RIGHT JOIN

左联结和右联结，分别返回左边/右边表的所有行和联结后的关联行

SELECT t1.column1，t2.column2 FROM t1 LEFT OUTER JOIN t2 ON t1.column1=t2.column1;
SELECT t1.column1，t2.column2 FROM t1 RIGHT OUTER JOIN t2 ON t1.column1=t2.column1;

2.3 CROSS JOIN

由没有联结条件的表关系返回的结果为笛卡儿积（cartesian product），也称叉联结（cross join）。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT column1,column2 FROM table1,table2;

2.4 SELF JOIN

自联结，利用别名联结同一个表。

SELECT t1.c1,t1.c2,t1.c3 FROM table1 AS t1,table1 AS t2
WHERE t1.c1=t2.c1 AND t2.c3=something;

2.5 UNION

组合查询用于执行多条SELECT语句并将结果作为一个结果集返回，又称并（UNION）查询或复合查询（compound query），主要应用于：

1. 在一个查询中从不同的表返回结构数据
2. 对一个表执行多个查询，按一个查询返回数据

SELECT * FROM t1
UNION
SELECT * FROM t2

有以下使用规则：

• UNION必须由两条或两条以上的 SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。
• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。
• UNION返回满足所有查询的行，如果需要返回满足联结其中任一查询的所有行，需要使用UNION ALL。

 

3  联结方式的区别和联系 

 

4  实战

4.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。

算法：

CREATE TABLE IF NOT EXISTS Person(
PersonId INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
PRIMARY KEY(PersonId)
);
CREATE TABLE IF NOT EXISTS Address(
AddressId INT NOT NULL,
PersonId INT NOT NULL,
City VARCHAR(20) ,
State VARCHAR(20) ,
PRIMARY KEY(AddressId)
);
INSERT INTO Person
VALUES
(1,"LI","Ming"),(2,"Wu","Qian"),(3,"Su","Ping");
INSERT INTO Address
VALUES
(1,1,"Nan Tong","Jiang Su"),(2,2,"Wu Xi","Jiang Su"),(3,3,Null,Null);
#查询语句
SELECT FirstName,LastName,City,State
FROM Person LEFT OUTER JOIN Address
ON person.PersonId=address.PersonId;

结果：

 

4.2 删除重复的邮箱

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

Id	Email
1	a@b.com
2	c@d.com
3	a@b.com

Id 是这个表的主键。

算法：

CREATE TABLE IF NOT EXISTS email ( 
id INT PRIMARY KEY, 
Email VARCHAR (20) NOT NULL ); 
INSERT INTO email VALUES (1, "a@b.com");
INSERT INTO email VALUES (2, "c@d.com");
INSERT INTO email VALUES (3, "a@b.com");
#查询语句 
DELETE t1 FROM email AS t1, email  AS t2 
WHERE t1.Email = t2.Email 
AND t1.id > t2.id;
select * from email;

结果：