【准备】创建boys girls 2张表，每个表中插入6个记录
create table boys ( b_name varchar(20), b_id int);
create table girls ( gb_name varchar(20), g_id int);
insert into boys values('Tom',1),('Jim',2),('Harry',3),('Snape',4),('Draco',5),('Lucius',6);
insert into girls values('Ada',1),('Bell',2),('Hermione',3),('Marrye',4),('Rose',5),('Cathy',6);
1【内连接】---交集
1.1 2种格式，效果相同
隐式内连接：SELECT * FROM tab1,tab2 WHRER.....;
显式内连接：SELECT * FROM tab1 [INNER] JOIN tab2 ON.....;
省略Inner时可以用where作为刷新条件的关键词
mysql> select * from boys,girls;
+--------+------+----------+------+
| b_name | b_id | gb_name | g_id |
+--------+------+----------+------+
| Tom | 1 | Ada | 1 |
| Jim | 2 | Ada | 1 |
| Harry | 3 | Ada | 1 |
| Snape | 4 | Ada | 1 |
| Draco | 5 | Ada | 1 |
| Lucius | 6 | Ada | 1 |
| Tom | 1 | Bell | 2 |
| Jim | 2 | Bell | 2 |
| Harry | 3 | Bell | 2 |
| Snape | 4 | Bell | 2 |
| Draco | 5 | Bell | 2 |
| Lucius | 6 | Bell | 2 |
| Tom | 1 | Hermione | 3 |
| Jim | 2 | Hermione | 3 |
| Harry | 3 | Hermione | 3 |
| Snape | 4 | Hermione | 3 |
| Draco | 5 | Hermione | 3 |
| Lucius | 6 | Hermione | 3 |
| Tom | 1 | Marrye | 4 |
| Jim | 2 | Marrye | 4 |
| Harry | 3 | Marrye | 4 |
| Snape | 4 | Marrye | 4 |
| Draco | 5 | Marrye | 4 |
| Lucius | 6 | Marrye | 4 |
| Tom | 1 | Rose | 5 |
| Jim | 2 | Rose | 5 |
| Harry | 3 | Rose | 5 |
| Snape | 4 | Rose | 5 |
| Draco | 5 | Rose | 5 |
| Lucius | 6 | Rose | 5 |
| Tom | 1 | Cathy | 6 |
| Jim | 2 | Cathy | 6 |
| Harry | 3 | Cathy | 6 |
| Snape | 4 | Cathy | 6 |
| Draco | 5 | Cathy | 6 |
| Lucius | 6 | Cathy | 6 |
+--------+------+----------+------+
36 rows in set (0.00 sec)
1.2 交换2张表的位置
mysql> select * from girls,boys;
+----------+------+--------+------+
| gb_name | g_id | b_name | b_id |
+----------+------+--------+------+
| Ada | 1 | Tom | 1 |
| Bell | 2 | Tom | 1 |
| Hermione | 3 | Tom | 1 |
| Marrye | 4 | Tom | 1 |
| Rose | 5 | Tom | 1 |
| Cathy | 6 | Tom | 1 |
| Ada | 1 | Jim | 2 |
| Bell | 2 | Jim | 2 |
| Hermione | 3 | Jim | 2 |
| Marrye | 4 | Jim | 2 |
| Rose | 5 | Jim | 2 |
| Cathy | 6 | Jim | 2 |
| Ada | 1 | Harry | 3 |
| Bell | 2 | Harry | 3 |
| Hermione | 3 | Harry | 3 |
| Marrye | 4 | Harry | 3 |
| Rose | 5 | Harry | 3 |
| Cathy | 6 | Harry | 3 |
| Ada | 1 | Snape | 4 |
| Bell | 2 | Snape | 4 |
| Hermione | 3 | Snape | 4 |
| Marrye | 4 | Snape | 4 |
| Rose | 5 | Snape | 4 |
| Cathy | 6 | Snape | 4 |
| Ada | 1 | Draco | 5 |
| Bell | 2 | Draco | 5 |
| Hermione | 3 | Draco | 5 |
| Marrye | 4 | Draco | 5 |
| Rose | 5 | Draco | 5 |
| Cathy | 6 | Draco | 5 |
| Ada | 1 | Lucius | 6 |
| Bell | 2 | Lucius | 6 |
| Hermione | 3 | Lucius | 6 |
| Marrye | 4 | Lucius | 6 |
| Rose | 5 | Lucius | 6 |
| Cathy | 6 | Lucius | 6 |
+----------+------+--------+------+
36 rows in set (0.00 sec)
1.3 用where连接筛选条件，对笛卡尔积进行筛选。
筛选出g_id > 4
mysql> select * from girls Join boys where g_id>4;
+---------+------+--------+------+
| gb_name | g_id | b_name | b_id |
+---------+------+--------+------+
| Rose | 5 | Tom | 1 |
| Cathy | 6 | Tom | 1 |
| Rose | 5 | Jim | 2 |
| Cathy | 6 | Jim | 2 |
| Rose | 5 | Harry | 3 |
| Cathy | 6 | Harry | 3 |
| Rose | 5 | Snape | 4 |
| Cathy | 6 | Snape | 4 |
| Rose | 5 | Draco | 5 |
| Cathy | 6 | Draco | 5 |
| Rose | 5 | Lucius | 6 |
| Cathy | 6 | Lucius | 6 |
+---------+------+--------+------+
12 rows in set (0.00 sec)
筛选 g_id>b_id
mysql> select * from girls Join boys where g_id>b_id;
+----------+------+--------+------+
| gb_name | g_id | b_name | b_id |
+----------+------+--------+------+
| Bell | 2 | Tom | 1 |
| Hermione | 3 | Tom | 1 |
| Marrye | 4 | Tom | 1 |
| Rose | 5 | Tom | 1 |
| Cathy | 6 | Tom | 1 |
| Hermione | 3 | Jim | 2 |
| Marrye | 4 | Jim | 2 |
| Rose | 5 | Jim | 2 |
| Cathy | 6 | Jim | 2 |
| Marrye | 4 | Harry | 3 |
| Rose | 5 | Harry | 3 |
| Cathy | 6 | Harry | 3 |
| Rose | 5 | Snape | 4 |
| Cathy | 6 | Snape | 4 |
| Cathy | 6 | Draco | 5 |
+----------+------+--------+------+
15 rows in set (0.00 sec)
筛选b_name=Harry
mysql> select * from girls Join boys where b_name='Harry';
+----------+------+--------+------+
| gb_name | g_id | b_name | b_id |
+----------+------+--------+------+
| Ada | 1 | Harry | 3 |
| Bell | 2 | Harry | 3 |
| Hermione | 3 | Harry | 3 |
| Marrye | 4 | Harry | 3 |
| Rose | 5 | Harry | 3 |
| Cathy | 6 | Harry | 3 |
+----------+------+--------+------+
6 rows in set (0.00 sec)
1.4 总结：逗号或者join连接的2张表，会将不满足筛选条件的2张表中的内容都删除，感觉像是一刀切。
2 【左/右连接】-----左表/右边+集合
2 .1 使用ON做为筛选的关键词，左右连接必须和ON语句同时使用，否则会报错
mysql> select * from girls left join boys;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
2.2 作用：
使用LEFT JOIN连接2张表：保持左边表格的完整性，空的部分用NULL填入
RIGHT JOIN连接2张表：保持右边边表格的完整性，空的部分用NULL填入
mysql> select * from boys,girls where b_id >4;
+--------+------+----------+------+
| b_name | b_id | g_name | g_id |
+--------+------+----------+------+
| Draco | 5 | Ada | 1 |
| Lucius | 6 | Ada | 1 |
| Draco | 5 | Bell | 2 |
| Lucius | 6 | Bell | 2 |
| Draco | 5 | Hermione | 3 |
| Lucius | 6 | Hermione | 3 |
| Draco | 5 | Marrye | 4 |
| Lucius | 6 | Marrye | 4 |
| Draco | 5 | Rose | 5 |
| Lucius | 6 | Rose | 5 |
| Draco | 5 | Cathy | 6 |
| Lucius | 6 | Cathy | 6 |
+--------+------+----------+------+
12 rows in set (0.00 sec)
mysql> select * from boys left join girls on b_id >4;
+--------+------+----------+------+
| b_name | b_id | g_name | g_id |
+--------+------+----------+------+
| Draco | 5 | Ada | 1 |
| Lucius | 6 | Ada | 1 |
| Draco | 5 | Bell | 2 |
| Lucius | 6 | Bell | 2 |
| Draco | 5 | Hermione | 3 |
| Lucius | 6 | Hermione | 3 |
| Draco | 5 | Marrye | 4 |
| Lucius | 6 | Marrye | 4 |
| Draco | 5 | Rose | 5 |
| Lucius | 6 | Rose | 5 |
| Draco | 5 | Cathy | 6 |
| Lucius | 6 | Cathy | 6 |
| Tom | 1 | NULL | NULL |
| Jim | 2 | NULL | NULL |
| Harry | 3 | NULL | NULL |
| Snape | 4 | NULL | NULL |
+--------+------+----------+------+
16 rows in set (0.00 sec)
mysql> select * from boys rigth join girls on b_id >4;
+--------+------+----------+------+
| b_name | b_id | g_name | g_id |
+--------+------+----------+------+
| Draco | 5 | Ada | 1 |
| Lucius | 6 | Ada | 1 |
| Draco | 5 | Bell | 2 |
| Lucius | 6 | Bell | 2 |
| Draco | 5 | Hermione | 3 |
| Lucius | 6 | Hermione | 3 |
| Draco | 5 | Marrye | 4 |
| Lucius | 6 | Marrye | 4 |
| Draco | 5 | Rose | 5 |
| Lucius | 6 | Rose | 5 |
| Draco | 5 | Cathy | 6 |
| Lucius | 6 | Cathy | 6 |
+--------+------+----------+------+
12 rows in set (0.00 sec)
3【UNION连接】
3.1 作用：将2个select的结果拼接起来，前提是2个select语句中要显示的列数和类型要相同
UNION：去除重复的行进行拼接
UNION ALL：不去除重复的行进行拼接
详情参考：http://www.itxm.net/a/shujuku/2016/1205/841.html
网友评论