数据库基础知识整理-连接表操作

数据库基础知识整理-连接表操作

表连接

SQL 的最有利的特征之一是能够组合来自两个或多个表的数据。
在SQL中，“表连接” 表示组合来自两个或多个表的数据。 表连接创建一个临时表，显示连接表中的数据。

SELECT 表1字段1, 表1字段2, 表2字段1..
FROM 表1,表2..
WHERE 主表.字段=附表.连接字段
-- 可选排序
--ORDER BY 主表.字段 DESC OR ASC;

-- 主表
mysql> select * from users;
+----+--------+-----------+------------+
| id | name   | password  | profession |
+----+--------+-----------+------------+
|  2 | 李四   | password2 | programer  |
|  3 | 王五   | password3 | librarian  |
|  4 | 赵六   | password4 | actor      |
+----+--------+-----------+------------+


-- 附表
mysql> select * from user;
+----+----------+------+----------+
| id | name     | age  | users_id |
+----+----------+------+----------+
|  1 | sss      |   20 |     NULL |
|  2 | 李四     |   22 |        2 |
|  4 | wangwu   |   19 |     NULL |
|  5 | 王五     |   19 |        3 |
|  6 | zhangsan |   26 |     NULL |
+----+----------+------+----------+

-- 有两个表，新增个列把他们关联上
mysql> select users.id, users.name,users.profession,user.age from users,user where users.id=user.users_id;
+----+--------+------------+------+
| id | name   | profession | age  |
+----+--------+------------+------+
|  2 | 李四   | programer  |   22 |
|  3 | 王五   | librarian  |   19 |
+----+--------+------------+------+

连接类型

• 自定义别名

通过使用 SQL，可以为表名称或列名称指定别名,创建别名是为了让列名称的可读性更强,不会改变原有的列名。
具体的用法在 上一篇总结 中的自定义列中已有示例

• 内连接（INNER JOIN）

INNER JOIN 与 JOIN 是相同的。

• 左连接（LEFT JOIN）

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。

• 右连接 （RIGHT JOIN）

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。

-- inner join
-- 相交的数据合并，两个表中name字段的数据中都有叫“李四”和“王五”的，profession是users中的数据，age是user中的数据
mysql> select users.name,users.profession, user.age from users inner join user on users.name=user.name;
+--------+------------+------+
| name   | profession | age  |
+--------+------------+------+
| 李四   | programer  |   22 |
| 王五   | librarian  |   19 |
+--------+------------+------+

-- left join
-- 1. 返回左表中所有的数据 ，右表可以没有匹配(右表中没有赵六) 2. left outer join  = left join
mysql> select users.name, users.profession,user.age from users left outer join user on users.name=user.name;
+--------+------------+------+
| name   | profession | age  |
+--------+------------+------+
| 李四   | programer  |   22 |
| 王五   | librarian  |   19 |
| 赵六   | actor      | NULL |
+--------+------------+------+

-- right join
-- 从右表返回所有的数据，左表可以没有匹配（左表中没有最后3条数据）。
mysql> select users.name, users.profession,user.age from users right join user on users.name=user.name;
+--------+------------+------+
| name   | profession | age  |
+--------+------------+------+
| 李四   | programer  |   22 |
| 王五   | librarian  |   19 |
| NULL   | NULL       |   20 |
| NULL   | NULL       |   19 |
| NULL   | NULL       |   26 |
+--------+------------+------+

UNION 操作符

有时候，你可能需要将多个表的数据合并为一个综合数据集。但可能出现相同的数据，就需要同数据库或跨数据库、甚至跨服务器上合并相似的数据。要完成此操作， 就得使用 UNION 和 UNION ALL 操作符。

UNION ALL 比 UNION 快，因为它不会对数据集执行重复删除操作。

• UNION 将多个数据集合并到单个数据集中，并删除任何现有的重复项。
• UNION ALL 将多个数据集合并到一个数据集中，但不会删除重复的行。

-- 先创建两个表并填充一点数据
mysql> select * from users_demo1;
+----+------+-----------+------------+
| id | name | password  | profession |
+----+------+-----------+------------+
|  1 | eric | pass_eric | teacher    |
|  2 | lucy | pass_lucy | student    |
+----+------+-----------+------------+

mysql> select * from users_demo2;
+----+------+-----------+------------+
| id | name | password  | profession |
+----+------+-----------+------------+
|  1 | hans | pass_hans | engineer   |
|  2 | bill | pass_bill | salesman   |
+----+------+-----------+------------+


-- UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
mysql> select * from users_demo1 union select * from users_demo2;
+----+------+-----------+------------+
| id | name | password  | profession |
+----+------+-----------+------------+
|  1 | eric | pass_eric | teacher    |
|  2 | lucy | pass_lucy | student    |
|  1 | hans | pass_hans | engineer   |
|  2 | bill | pass_bill | salesman   |
+----+------+-----------+------------+

上面是普通的合并，那么当数据重复的时候，就可以显示出union和union all不同的地方了，现在同时往users_demo1和users_demo1中添加一条相同的数据。

insert into users_demo1 values(3, 'alexander', 'pass_alexander', 'professor');
insert into users_demo2 values(3, 'alexander', 'pass_alexander', 'professor');

-- 现在再测试一下
mysql> select * from users_demo1 union select * from users_demo2;
+----+-----------+----------------+------------+
| id | name      | password       | profession |
+----+-----------+----------------+------------+
|  1 | eric      | pass_eric      | teacher    |
|  2 | lucy      | pass_lucy      | student    |
|  3 | alexander | pass_alexander | professor  |
|  1 | hans      | pass_hans      | engineer   |
|  2 | bill      | pass_bill      | salesman   |
+----+-----------+----------------+------------+

mysql> select * from users_demo1 union all select * from users_demo2;
+----+-----------+----------------+------------+
| id | name      | password       | profession |
+----+-----------+----------------+------------+
|  1 | eric      | pass_eric      | teacher    |
|  2 | lucy      | pass_lucy      | student    |
|  3 | alexander | pass_alexander | professor  |
|  1 | hans      | pass_hans      | engineer   |
|  2 | bill      | pass_bill      | salesman   |
|  3 | alexander | pass_alexander | professor  |
+----+-----------+----------------+------------+

提示：

1. UNION 内部的每个 SELECT 语句必须拥有相同数量的列。
2. 每个列必须拥有相同或相似的数据类型。
3. 每个 SELECT 语句中的列的顺序必须相同。
4. 如果你的列在所有查询中不完全匹配，可以使用其他值代替（例如NULL）。