表a
CREATE TABLE `a_table` (
`a_id` int(11) DEFAULT NULL,
`a_name` varchar(10) DEFAULT NULL,
`a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表b
CREATE TABLE `b_table` (
`b_id` int(11) DEFAULT NULL,
`b_name` varchar(10) DEFAULT NULL,
`b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表a
mysql> SELECT * FROM table_a;
+------+--------+-----------+
| a_id | a_name | a_part |
+------+--------+-----------+
| 1 | 老潘 | 总裁部 |
| 2 | 老王 | 秘书部 |
| 3 | 老张 | 设计部 |
| 4 | 老李 | 运营部 |
+------+--------+-----------+
表b
mysql> SELECT * FROM table_b;
+------+--------+-----------+
| b_id | b_name | b_part |
+------+--------+-----------+
| 2 | 老王 | 秘书部 |
| 3 | 老张 | 设计部 |
| 5 | 老刘 | 人事部 |
| 6 | 老黄 | 生产部 |
+------+--------+-----------+
表a 和 表b内连接
也就是笛卡尔积
第一个表的每一行都与第二个表中的没每一行连接
mysql> select * from table_a inner join table_b;
+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_part | b_id | b_name | b_part |
+------+--------+-----------+------+--------+-----------+
| 1 | 老潘 | 总裁部 | 2 | 老王 | 秘书部 |
| 2 | 老王 | 秘书部 | 2 | 老王 | 秘书部 |
| 3 | 老张 | 设计部 | 2 | 老王 | 秘书部 |
| 4 | 老李 | 运营部 | 2 | 老王 | 秘书部 |
| 1 | 老潘 | 总裁部 | 3 | 老张 | 设计部 |
| 2 | 老王 | 秘书部 | 3 | 老张 | 设计部 |
| 3 | 老张 | 设计部 | 3 | 老张 | 设计部 |
| 4 | 老李 | 运营部 | 3 | 老张 | 设计部 |
| 1 | 老潘 | 总裁部 | 5 | 老刘 | 人事部 |
| 2 | 老王 | 秘书部 | 5 | 老刘 | 人事部 |
| 3 | 老张 | 设计部 | 5 | 老刘 | 人事部 |
| 4 | 老李 | 运营部 | 5 | 老刘 | 人事部 |
| 1 | 老潘 | 总裁部 | 6 | 老黄 | 生产部 |
| 2 | 老王 | 秘书部 | 6 | 老黄 | 生产部 |
| 3 | 老张 | 设计部 | 6 | 老黄 | 生产部 |
| 4 | 老李 | 运营部 | 6 | 老黄 | 生产部 |
+------+--------+-----------+------+--------+-----------+
16 rows in set (0.00 sec)
什么是内连接
内连接是指连接结果仅包含符合条件的行, 参与连接的两(或多)个表都应该符合条件.
内连接的语法
SELECT * FROM table_a INNER JOIN table_b ON condition
SELECT * FROM table_a, tabke_b where condition
Unknown column 'col_name' in 'on clause'
什么是外连接
连接结果不仅包含符合连接条件的行同时也会包含自身不符合条件的行. 包括左连外连接、有外连接和全外连接.
外连接分类
左连接语句
左连接将左表的记录全部表示出来, 而右表只会显示符合搜索条件的记录. 右表记录不足的的地方均为NULL
SELECT * FROM table_a LEFT JOIN table_b ON table_a.a_id = table_b.b_id;
结果
+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_part | b_id | b_name | b_part |
+------+--------+-----------+------+--------+-----------+
| 2 | 老王 | 秘书部 | 2 | 老王 | 秘书部 |
| 3 | 老张 | 设计部 | 3 | 老张 | 设计部 |
| 1 | 老潘 | 总裁部 | NULL | NULL | NULL |
| 4 | 老李 | 运营部 | NULL | NULL | NULL |
+------+--------+-----------+------+--------+-----------+
右连接语句
右连接将右表的纪录全部显示出来, 而左表只会显示符合搜素条件的记录. 左表记录不足的地方均为NULL
SELECT * FROM table_a RIGHT JOIN table_b ON table_a.a_id = table_b.b_id;
结果
+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_part | b_id | b_name | b_part |
+------+--------+-----------+------+--------+-----------+
| 2 | 老王 | 秘书部 | 2 | 老王 | 秘书部 |
| 3 | 老张 | 设计部 | 3 | 老张 | 设计部 |
| NULL | NULL | NULL | 5 | 老刘 | 人事部 |
| NULL | NULL | NULL | 6 | 老黄 | 生产部 |
+------+--------+-----------+------+--------+-----------+
MYSQL全连接的实现
返回左右表匹配的行, 并且返回左表不足的地方为NULL, 右表不足的地方为NULL, 其实就是左连接和右连接的集合
SELECT * FROM table_a LEFT JOIN table_b ON table_a.a_id = table_b.b_id UNION
SELECT * FROM table_a RIGHT JOIN table_b ON table_a.a_id = table_b.b_id;
结果
+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_part | b_id | b_name | b_part |
+------+--------+-----------+------+--------+-----------+
| 2 | 老王 | 秘书部 | 2 | 老王 | 秘书部 |
| 3 | 老张 | 设计部 | 3 | 老张 | 设计部 |
| 1 | 老潘 | 总裁部 | NULL | NULL | NULL |
| 4 | 老李 | 运营部 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | 老刘 | 人事部 |
| NULL | NULL | NULL | 6 | 老黄 | 生产部 |
+------+--------+-----------+------+--------+-----------+
什么是等值连接
在连接条件中使用等于号(=)运算符比较被连接的列, 其查询结果中列出被连接表中的所有列, 包括其中重复列.
等值连接是内连接的子集, 等级连接是特殊的内连接, 条件使用等号的内连接
等值连接语句
SELECT * FROM table_a INNER JOIN table_b ON table_a.a_id = table_b.b.id where table_a.a_id = table_b.b_id;
SELECT * FROM table_a , table_b where table_a.a_id = table_b.b_id;
结果
+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_part | b_id | b_name | b_part |
+------+--------+-----------+------+--------+-----------+
| 2 | 老王 | 秘书部 | 2 | 老王 | 秘书部 |
| 3 | 老张 | 设计部 | 3 | 老张 | 设计部 |
+------+--------+-----------+------+--------+-----------+
什么是自然连接
自然连接是一种特殊的等值连接, 他要求两个关系表中进行连接的必须是相同的属性列(名字相同), 无须添加连接条件, 并且在结果中消除重复的属性列.
所有相同名称的列的内容相同才会有结果
自然连接关键字
... NATURAL JOIN ...
自然连接语句
最上面两个表进行自然连接的结果为空, 因为没有相同名称的列, 将两个表的a_name、b_name变为name 可以进行自然连接
SELECT * FROM table_a NATURAL JOIN table_b
结果
+--------+------+-----------+------+-----------+
| name | a_id | a_part | b_id | b_part |
+--------+------+-----------+------+-----------+
| 老王 | 2 | 秘书部 | 2 | 秘书部 |
| 老张 | 3 | 设计部 | 3 | 设计部 |
+--------+------+-----------+------+-----------+