【MySQL】4. MySQL基础 - 表联结
1. MySQL别名
通过使用 SQL,可以为表名称或列名称指定别名。基本上,创建别名是为了让列名称的可读性更强。
列别名
SELECT column_name AS alias_name
FROM table_name;
表别名
SELECT column_name(s)
FROM table_name AS alias_name;
- 大部分情况下,用表名的简写可以增加可读性
- 在不影响理解的情况下,AS 关键字可以忽略
2. 连接 JOIN
JOIN 用于把来自两个或多个表的行结合起来。常用到的有四种连接:INNER JOIN、LEFT JOIN、RIGHT JOIN、OUTER JOIN 。加上筛选可以达到一下 7 种用法。
除了以上四种之外,还有一个用于计算笛卡尔积的 CROSS JOIN 后面一个个叙述。
内连接 INNER JOIN
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
- 用于返回两表均包含的数据。
- INNER JOIN 与 JOIN 是相同的。
左连接 LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
- LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。
- 在某些数据库中,LEFT JOIN 称为 LEFT OUTER JOIN。
右连接 RIGHT JOIN
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
- RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。
- 在某些数据库中,RIGHT JOIN 称为 RIGHT OUTER JOIN。
全连接 FULL JOIN
SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name=table2.column_name;
- FULL JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行.
- FULL JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
- FULL JOIN 与 FULL OUTER JOIN 效果一样
交叉连接 CROSS JOIN
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
- CROSS JOIN子句从连接的表返回行的笛卡儿乘积。
- 返回行数等于 table1 行数 乘上 table2 行数。
- CROSS JOIN 后加条件只能用where,不能用on。
- 在 FROM 子句中使用逗号间隔连接也可以实现同样功能,如:
SELECT column_name(s) FROM table1, table2;
自连接
自连接其实是交叉连接的一种特殊用法,在自连接中,被连接的双方都是同一个表,所以要通过别名使之区分开。
SELECT column_name(s)
FROM table t1, table t2
[WHERE condition];
3. 合并 UNION
通用语法
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
- UNION 操作符合并两个或多个 SELECT 语句的结果。
- UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。
- 默认地,UNION 操作符会筛选出不重复的值。如果允许重复的值,请使用 UNION ALL。
- UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
实例:
1. 组合两张表
在数据库中创建表1和表2,并各插入三行数据(自己造);编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:FirstName, LastName, City, State
Person表结构:
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| PersonId | int(11) | NO | PRI | NULL | |
| FirstName | varchar(20) | YES | | NULL | |
| LastName | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
Address表结构:
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| AddressId | int(11) | NO | PRI | NULL | |
| PersonId | int(11) | NO | | NULL | |
| City | varchar(20) | YES | | NULL | |
| State | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
代码:
-- 创建 Person 表
CREATE TABLE
IF NOT EXISTS Person (
PersonId INT NOT NULL,
FirstName VARCHAR (20),
LastName VARCHAR (20),
PRIMARY KEY (PersonId)
);
-- 插入信息
INSERT INTO Person
VALUES
(1, 'AA', 'aa'),
(2, 'BB', 'bb'),
(3, 'CC', 'cc'),
(4, 'DD', 'dd'),
(5, 'EE', 'ee');
-- 创建 Address 表
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 Address
VALUES
(1, 1, 'X', 'x'),
(2, 3, 'Y', 'y'),
(3, 5, 'Z', 'z')
;
-- 查询信息
SELECT
FirstName,
LastName,
City,
State
FROM
Person p
LEFT JOIN Address a ON p.PersonId = a.PersonId;
结果:
+-----------+----------+------+-------+
| FirstName | LastName | City | State |
+-----------+----------+------+-------+
| AA | aa | X | x |
| CC | cc | Y | y |
| EE | ee | Z | z |
| BB | bb | NULL | NULL |
| DD | dd | NULL | NULL |
+-----------+----------+------+-------+
2. 删除重复的邮箱
编写一个 SQL 查询,来删除 email 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
-- 原表
+----+---------+
| Id | Email |
+----+---------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+---------+
-- 删除后
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | [email protected] |
| 2 | [email protected] |
+----+------------------+
代码:
DELETE e1
FROM email e1, email e2
WHERE e1.Email = e2.Email
AND e1.Id > e2.ID;
参考:
- SQL 教程 | 菜鸟教程
- 《SQL必知必会》