sql语句技巧一join的使用

常见的sql语句类型

• DDL数据定义语言
• TPL事务处理语言
• DCL数据空置语言
• DML数据操作语言

DML数据库操作语言

• insert
• select
• update
• delete

数据表创建

Create Table: CREATE TABLE `user1` (
  `id` int(11) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Create Table: CREATE TABLE `user2` (
  `id` int(11) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

join的类型

• 内连接 inner join
• 全外连接 full outer join（mysql中使用union all）
• 左外连接 left outer join
• 右外连接 right outer join
• 交叉连接 cross join

内连接

说明

内连接是基于连接谓词将两个表的列结合起来，产生新的结果表，取的是A，B的交集。

实例

mysql> select * from user1;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | 唐僧      |
|  2 | 猪八戒    |
|  3 | 孙悟空    |
|  4 | 沙和尚    |
+----+-----------+
4 rows in set (0.00 sec)

mysql> select * from user2;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | 孙悟空    |
|  2 | 牛魔王    |
|  3 | 蛟魔王    |
|  4 | 鹏魔王    |
|  5 | 狮魔王    |
+----+-----------+
5 rows in set (0.00 sec)

mysql> select A.user_name,B.user_name from user1 A inner join user2 B on A.user_name = B.user_name;
+-----------+-----------+
| user_name | user_name |
+-----------+-----------+
| 孙悟空     | 孙悟空     |
+-----------+-----------+
1 row in set (0.00 sec)

左外连接

说明

以左表为基准表，在右表中，有匹配的则关联，没有的补null

实例

mysql> select * from user1 A left join user2 B on A.user_name = B.user_name;
+----+-----------+------+-----------+
| id | user_name | id   | user_name |
+----+-----------+------+-----------+
|  3 | 孙悟空    |    1 | 孙悟空    |
|  1 | 唐僧      | NULL | NULL      |
|  2 | 猪八戒    | NULL | NULL      |
|  4 | 沙和尚    | NULL | NULL      |
+----+-----------+------+-----------+
4 rows in set (0.00 sec)

同时可以去掉A、B相同的部分,这里通常会使用子查询去实现，但是子查询的效率低，可以使用left join优化

mysql> select * from user1 A left join user2 B on A.user_name = B.user_name where B.id is null;
+----+-----------+------+-----------+
| id | user_name | id   | user_name |
+----+-----------+------+-----------+
|  1 | 唐僧      | NULL | NULL      |
|  2 | 猪八戒    | NULL | NULL      |
|  4 | 沙和尚    | NULL | NULL      |
+----+-----------+------+-----------+
3 rows in set (0.00 sec)

右外连接

说明

以右表为基准，左表无匹配的补null

实例

mysql> select * from user1 A right join user2 B on A.user_name = B.user_name;
+------+-----------+----+-----------+
| id   | user_name | id | user_name |
+------+-----------+----+-----------+
|    3 | 孙悟空    |  1 | 孙悟空    |
| NULL | NULL      |  2 | 牛魔王    |
| NULL | NULL      |  3 | 蛟魔王    |
| NULL | NULL      |  4 | 鹏魔王    |
| NULL | NULL      |  5 | 狮魔王    |
+------+-----------+----+-----------+
5 rows in set (0.00 sec)

全外连接

说明

A,B表全都匹配，A没有匹配B的部分补null，B没有匹配A的部分补null。mysql的全外连接需要借助左连接和右连接完成。

实例

有重复

mysql> select * from user1 A left join user2 B on A.user_name = B.user_name
    -> union all
    -> select * from user1 A right join user2 B on A.user_name = B.user_name;
+------+-----------+------+-----------+
| id   | user_name | id   | user_name |
+------+-----------+------+-----------+
|    3 | 孙悟空    |    1 | 孙悟空    |
|    1 | 唐僧      | NULL | NULL      |
|    2 | 猪八戒    | NULL | NULL      |
|    4 | 沙和尚    | NULL | NULL      |
|    3 | 孙悟空    |    1 | 孙悟空    |
| NULL | NULL      |    2 | 牛魔王    |
| NULL | NULL      |    3 | 蛟魔王    |
| NULL | NULL      |    4 | 鹏魔王    |
| NULL | NULL      |    5 | 狮魔王    |
+------+-----------+------+-----------+
9 rows in set (0.00 sec)

无重复

mysql> select * from user1 A left join user2 B on A.user_name = B.user_name
    -> union  distinct
    -> select * from user1 A right join user2 B on A.user_name = B.user_name;
+------+-----------+------+-----------+
| id   | user_name | id   | user_name |
+------+-----------+------+-----------+
|    3 | 孙悟空    |    1 | 孙悟空    |
|    1 | 唐僧      | NULL | NULL      |
|    2 | 猪八戒    | NULL | NULL      |
|    4 | 沙和尚    | NULL | NULL      |
| NULL | NULL      |    2 | 牛魔王    |
| NULL | NULL      |    3 | 蛟魔王    |
| NULL | NULL      |    4 | 鹏魔王    |
| NULL | NULL      |    5 | 狮魔王    |
+------+-----------+------+-----------+
8 rows in set (0.00 sec)

交叉外连接

说明

A表中数据与B表中数据做笛卡尔积

实例

mysql> select * from user1 A 
    -> cross join 
    -> user2 B;
+----+-----------+----+-----------+
| id | user_name | id | user_name |
+----+-----------+----+-----------+
|  1 | 唐僧      |  1 | 孙悟空    |
|  2 | 猪八戒    |  1 | 孙悟空    |
|  3 | 孙悟空    |  1 | 孙悟空    |
|  4 | 沙和尚    |  1 | 孙悟空    |
|  1 | 唐僧      |  2 | 牛魔王    |
|  2 | 猪八戒    |  2 | 牛魔王    |
|  3 | 孙悟空    |  2 | 牛魔王    |
|  4 | 沙和尚    |  2 | 牛魔王    |
|  1 | 唐僧      |  3 | 蛟魔王    |
|  2 | 猪八戒    |  3 | 蛟魔王    |
|  3 | 孙悟空    |  3 | 蛟魔王    |
|  4 | 沙和尚    |  3 | 蛟魔王    |
|  1 | 唐僧      |  4 | 鹏魔王    |
|  2 | 猪八戒    |  4 | 鹏魔王    |
|  3 | 孙悟空    |  4 | 鹏魔王    |
|  4 | 沙和尚    |  4 | 鹏魔王    |
|  1 | 唐僧      |  5 | 狮魔王    |
|  2 | 猪八戒    |  5 | 狮魔王    |
|  3 | 孙悟空    |  5 | 狮魔王    |
|  4 | 沙和尚    |  5 | 狮魔王    |
+----+-----------+----+-----------+
20 rows in set (0.00 sec)

开发中使用join的技巧

如何更新使用过滤条件中包括自身的表？

实例

mysql> update user1 set user_name = '美猴王' where user_name in (select A.user_name from user1 A join user2 B on A.user_name = B.user_name);
ERROR 1093 (HY000): You can't specify target table 'user1' for update in FROM clause

优化，使用内连接解决

mysql> update user1 a inner join (select A.user_name from user1 A join user2 B on A.user_name = B.user_name) b on a.user_name = b.user_name set a.user_name = '美猴王';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user1;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | 唐曾      |
|  2 | 猪八戒    |
|  3 | 美猴王    |
|  4 | 沙和尚    |
+----+-----------+
4 rows in set (0.00 sec)