1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
# 部门表
CREATE TABLE `t_dept` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,# id
`deptName` VARCHAR ( 30 ) DEFAULT NULL,# 部门名称
`address` VARCHAR ( 40 ) DEFAULT NULL,# 部门地址
PRIMARY KEY ( `id` )
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
# 员工表
CREATE TABLE `t_emp` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,# id
`name` VARCHAR ( 20 ) DEFAULT NULL,# 员工姓名
`age` INT ( 3 ) DEFAULT NULL,# 年龄
`deptId` INT ( 11 ) DEFAULT NULL,# 所属部门id
empno INT NOT NULL,# 员工编号
PRIMARY KEY ( `id` ),
KEY `idx_dept_id` ( `deptId` )
# CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
# 数据准备 部门
INSERT INTO t_dept ( deptName, address )
VALUES
( '华山', '华山' );
INSERT INTO t_dept ( deptName, address )
VALUES
( '丐帮', '洛阳' );
INSERT INTO t_dept ( deptName, address )
VALUES
( '峨眉', '峨眉山' );
INSERT INTO t_dept ( deptName, address )
VALUES
( '武当', '武当山' );
INSERT INTO t_dept ( deptName, address )
VALUES
( '明教', '光明顶' );
INSERT INTO t_dept ( deptName, address )
VALUES
( '少林', '少林寺' );
# 数据准备 员工
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '风清扬', 90, 1, 100001 );
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '岳不群', 50, 1, 100002 );
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '令狐冲', 24, 1, 100003 );
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '洪七公', 70, 2, 100004 );
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '乔峰', 35, 2, 100005 );
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '灭绝师太', 70, 3, 100006 );
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '周芷若', 20, 3, 100007 );
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '张三丰', 100, 4, 100008 );
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '张无忌', 25, 5, 100009 );
INSERT INTO t_emp ( NAME, age, deptId, empno )
VALUES
( '韦小宝', 18, NULL, 100010 );
# 演示
select * from t_emp; # 10条
select * from t_dept; # 6 条
# INNER JOIN 9条
# 1.所有有门派人员的信息(要求显示门派名称)
SELECT e.`name`,d.`deptName` FROM t_emp e INNER JOIN t_dept d ON e.`deptId`=d.`id`;
# LEFT JOIN 10条
# 2. 列出所有人员及其门派信息
SELECT e.`name`,d.`deptName` FROM t_emp e LEFT JOIN t_dept d ON e.`deptId`=d.`id`;
# 3. 列出所有门派
SELECT * FROM t_dept;
# 4. 所有无门派人士
SELECT * FROM t_emp WHERE deptId IS NULL;
# RIGHT JOIN
# 5. 所有无人门派
SELECT d.* FROM t_emp e RIGHT JOIN t_dept d ON d.`id`=e.`deptId` WHERE e.`deptId` IS NULL;
# MySQL不支持FULL OUTER JOIN这种语法 可以改成 2+5
# 6. 所有人员和门派的对应关系
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId`=d.`id`
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.`deptId`=d.`id`;
# 7. 所有没有入门派的人员和没人入的门派
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId`=d.`id` WHERE e.deptId IS NULL
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.`deptId`=d.`id` WHERE e.`deptId` IS NULL;
|