Mysql学习笔记-ch5-连接练习

– SQL的四种连接查询

– 内连接
inner join 或者 join

– 外连接
1，左连接 left join 或者left outer join

2，右连接 right join 或者 right outer join

3，完全外连接 full join 或者 full outer join

– 创建两个表：

create database testJoin;

person表
id,
name,
cardId

create table person(
id int,
name varchar(20),
cardId int
);

card表
id,
name

create table card(
id int,
name varchar(20)
);

– -- 解决 incorrect string value 问题
alter table card convert to character set utf8mb4 collate utf8mb4_bin;
alter table person convert to character set utf8mb4 collate utf8mb4_bin;

insert into card values(1,“饭卡”);
insert into card values(2,“建行卡”);
insert into card values(3,“农行卡”);
insert into card values(4,“工商卡”);
insert into card values(5,“邮政卡”);

insert into person values(1,“张三”,1);
insert into person values(2,“李四”,3);
insert into person values(3,“王五”,6);

mysql> select * from card;
±-----±----------+
| id | name |
±-----±----------+
| 1 | 饭卡 |
| 2 | 建行卡 |
| 3 | 农行卡 |
| 4 | 工商卡 |
| 5 | 邮政卡 |
±-----±----------+
5 rows in set (0.00 sec)

mysql> select * from person;
±-----±-------±-------+
| id | name | cardId |
±-----±-------±-------+
| 1 | 张三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
±-----±-------±-------+
3 rows in set (0.00 sec)

– 并没有创建外键，

– 1、inner join 查询（内连接）
select * from person inner join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
±-----±-------±-------±-----±----------+
2 rows in set (0.00 sec)

– 内联查询，其实就是两张表中的数据，通过某个字段相对，查询出相关记录数据。

– 将inner join 替换成 join，结果一样。
select * from person join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
±-----±-------±-------±-----±----------+
2 rows in set (0.00 sec)

– 2、left join（左外连接）

select * from person left join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
±-----±-------±-------±-----±----------+
3 rows in set (0.00 sec)

– 左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来
– 如果没有，就会补 NULL

select * from person left outer join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
±-----±-------±-------±-----±----------+
3 rows in set (0.00 sec)

– 3、right join（右外连接）
select * from person right join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
±-----±-------±-------±-----±----------+
5 rows in set (0.00 sec)

– 右外连接，会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来
– 如果没有，就会补 NULL

select * from person right outer join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
±-----±-------±-------±-----±----------+
5 rows in set (0.00 sec)

– 4、full join（全外连接）
select * from person full join card on person.cardId=card.id;

mysql> select * from person full join card on person.cardId=card.id;
ERROR 1054 (42S22): Unknown column ‘person.cardId’ in ‘on clause’

– -- mysql有时候不支持 full join 全外连接

– -- 尝试使用 union
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardId | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
±-----±-------±-------±-----±----------+
6 rows in set (0.01 sec)

– 连接的好处是，不用创建外键，可以通过两个表之间共同的字段数值进行匹配连接。