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

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

表连接

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 操作符

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

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

  • 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_demo1users_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)。

About This Page

Made with bootstrap and jquery by TaoYuan.

Contact Me

Click! If you are interested