创建数据库
create database business;
use business;
创建表
create table dept(
id int(11) not null auto_increment,
deptName varchar(30) default null,
floor varchar(40) default null,
primary key(id)
) engine=innodb auto_increment=1 default charset=utf8;
create table emp(
id int(11) not null auto_increment,
name varchar(20) default null,
deptId int(11) default null,
primary key(id)
) engine=innodb auto_increment=1 default charset=utf8;
insert into dept(deptName,floor) values('Develop',1);
insert into dept(deptName,floor) values('HuamanResource',2);
insert into dept(deptName,floor) values('Market',3);
insert into emp(name,deptId) values('zs',1);
insert into emp(name,deptId) values('ls',1);
insert into emp(name,deptId) values('ww',1);
insert into emp(name,deptId) values('zl',2);
insert into emp(name,deptId) values('qq',4);
员工表
select * from emp;
+----+------+--------+
| id | name | deptId |
+----+------+--------+
| 1 | zs | 1 |
| 2 | ls | 1 |
| 3 | ww | 1 |
| 4 | zl | 2 |
| 5 | qq | 4 |
+----+------+--------+
5 rows in set (0.02 sec)
部门表
select * from dept;
+----+----------------+-------+
| id | deptName | floor |
+----+----------------+-------+
| 1 | Develop | 1 |
| 2 | HuamanResource | 2 |
| 3 | Market | 3 |
+----+----------------+-------+
3 rows in set (0.02 sec)
select * from dept,emp; -- 笛卡尔积
+----+----------------+-------+----+------+--------+
| id | deptName | floor | id | name | deptId |
+----+----------------+-------+----+------+--------+
| 1 | Develop | 1 | 1 | zs | 1 |
| 2 | HuamanResource | 2 | 1 | zs | 1 |
| 3 | Market | 3 | 1 | zs | 1 |
| 1 | Develop | 1 | 2 | ls | 1 |
| 2 | HuamanResource | 2 | 2 | ls | 1 |
| 3 | Market | 3 | 2 | ls | 1 |
| 1 | Develop | 1 | 3 | ww | 1 |
| 2 | HuamanResource | 2 | 3 | ww | 1 |
| 3 | Market | 3 | 3 | ww | 1 |
| 1 | Develop | 1 | 4 | zl | 2 |
| 2 | HuamanResource | 2 | 4 | zl | 2 |
| 3 | Market | 3 | 4 | zl | 2 |
| 1 | Develop | 1 | 5 | qq | 4 |
| 2 | HuamanResource | 2 | 5 | qq | 4 |
| 3 | Market | 3 | 5 | qq | 4 |
+----+----------------+-------+----+------+--------+
15 rows in set (0.00 sec)
select * from emp a inner join dept b on a.deptId=b.id; -- 公有
+----+------+--------+----+----------------+-------+
| id | name | deptId | id | deptName | floor |
+----+------+--------+----+----------------+-------+
| 1 | zs | 1 | 1 | Develop | 1 |
| 2 | ls | 1 | 1 | Develop | 1 |
| 3 | ww | 1 | 1 | Develop | 1 |
| 4 | zl | 2 | 2 | HuamanResource | 2 |
+----+------+--------+----+----------------+-------+
4 rows in set (0.01 sec)
select * from emp a left join dept b on a.deptId=b.id; -- 全a
+----+------+--------+------+----------------+-------+
| id | name | deptId | id | deptName | floor |
+----+------+--------+------+----------------+-------+
| 1 | zs | 1 | 1 | Develop | 1 |
| 2 | ls | 1 | 1 | Develop | 1 |
| 3 | ww | 1 | 1 | Develop | 1 |
| 4 | zl | 2 | 2 | HuamanResource | 2 |
| 5 | qq | 4 | NULL | NULL | NULL |
+----+------+--------+------+----------------+-------+
5 rows in set (0.01 sec)
select * from emp a right join dept b on a.deptId=b.id; -- 全b
+------+------+--------+----+----------------+-------+
| id | name | deptId | id | deptName | floor |
+------+------+--------+----+----------------+-------+
| 1 | zs | 1 | 1 | Develop | 1 |
| 2 | ls | 1 | 1 | Develop | 1 |
| 3 | ww | 1 | 1 | Develop | 1 |
| 4 | zl | 2 | 2 | HuamanResource | 2 |
| NULL | NULL | NULL | 3 | Market | 3 |
+------+------+--------+----+----------------+-------+
5 rows in set (0.00 sec)
select * from emp a left join dept b on a.deptId=b.id where b.id is null; -- 独a
+----+------+--------+------+----------+-------+
| id | name | deptId | id | deptName | floor |
+----+------+--------+------+----------+-------+
| 5 | qq | 4 | NULL | NULL | NULL |
+----+------+--------+------+----------+-------+
1 row in set (0.01 sec)
select * from emp a right join dept b on a.deptId=b.id where a.deptId is null; -- 独b
+------+------+--------+----+----------+-------+
| id | name | deptId | id | deptName | floor |
+------+------+--------+----+----------+-------+
| NULL | NULL | NULL | 3 | Market | 3 |
+------+------+--------+----+----------+-------+
1 row in set (0.00 sec)
select * from emp a left join dept b on a.deptId=b.id
union
select * from emp a right join dept b on a.deptId=b.id; -- 全有
+------+------+--------+------+----------------+-------+
| id | name | deptId | id | deptName | floor |
+------+------+--------+------+----------------+-------+
| 1 | zs | 1 | 1 | Develop | 1 |
| 2 | ls | 1 | 1 | Develop | 1 |
| 3 | ww | 1 | 1 | Develop | 1 |
| 4 | zl | 2 | 2 | HuamanResource | 2 |
| 5 | qq | 4 | NULL | NULL | NULL |
| NULL | NULL | NULL | 3 | Market | 3 |
+------+------+--------+------+----------------+-------+
6 rows in set (0.02 sec)
select * from emp a left join dept b on a.deptId=b.id where b.id is null
union
select * from emp a right join dept b on a.deptId=b.id where a.deptId is null; -- a、b两者独有
+------+------+--------+------+----------+-------+
| id | name | deptId | id | deptName | floor |
+------+------+--------+------+----------+-------+
| 5 | qq | 4 | NULL | NULL | NULL |
| NULL | NULL | NULL | 3 | Market | 3 |
+------+------+--------+------+----------+-------+
2 rows in set (0.00 sec)
