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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
| -- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门 id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工 id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务 id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
/*
* 部门表 dept 和员工表 emp 是一对多的关系, 职务表 job 和员工表 emp 是一对多的关系
*/
-- 需求：
-- 1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT
emp.`id`,
emp.`ename`,
emp.`salary`,
job.`jname`,
job.`description`
FROM
emp,
job
WHERE emp.`job_id` = job.`id`;
-- 2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT
emp.`id`,
emp.`ename`,
emp.`salary`,
job.`jname`,
job.`description`,
dept.`dname`,
dept.`loc`
FROM
emp,
job,
dept
WHERE emp.`dept_id` = dept.`id` AND emp.`job_id` = job.`id`;
-- 3. 查询员工姓名，工资，工资等级
SELECT
t1.`ename`,
t1.`salary`,
t2.`grade`
FROM
emp t1,
salarygrade t2
WHERE t1.`salary` BETWEEN t2.`losalary`
AND t2.`hisalary`;
-- 4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT
t1.`ename`,
t1.`salary`,
t3.`jname`,
t3.`description`,
t2.`dname`,
t2.`loc`,
t4.`grade`
FROM
emp t1,
dept t2,
job t3,
salarygrade t4
WHERE t1.`job_id` = t3.`id`
AND t1.`dept_id` = t2.`id`
AND t1.`salary` BETWEEN t4.`losalary`
AND t4.`hisalary`;
-- 5. 查询出部门编号、部门名称、部门位置、部门人数
SELECT
t2.`id`,
t2.`dname`,
t2.`loc`,
COUNT(t1.`id`) AS ' 部门人数 '
FROM
emp t1,
dept t2
WHERE
t1.`dept_id` = t2.`id`
GROUP BY
t1.`dept_id`;
-- 使用子查询的结果是多行多列的, 可以作为一张「虚拟表」参与查询
SELECT
t2.`id`,
t2.`dname`,
t2.`loc`,
t1.` 部门人数 `
FROM
(SELECT
dept_id,
COUNT (id) AS " 部门人数 "
FROM
emp
GROUP BY emp.`dept_id`) AS t1,
dept AS t2
WHERE t1.`dept_id` = t2.`id`;
-- 6. 查询所有员工的姓名及其直接上级的姓名, 没有领导的员工也需要查询
SELECT
t1.`ename` AS " 员工姓名 ",
t2.`ename` AS " 直接上级 "
FROM
emp t1 -- emp t1, emp t2 成为「自相关」
LEFT JOIN emp t2 -- 查询左表的数据和其交集的数据, 左外连接
ON t1.`mgr` = t2.`id`;
|
