1 -- 新建一个数据库
2 CREATE DATABASE db2;
3
4 -- 部门表
5 CREATE TABLE dept(
6 id
INT PRIMARY KEY ,
-- 部门id
7 dname
VARCHAR(
10),
8 loc
VARCHAR(
10)
9 );
10 -- 添加四个部门
11 INSERT INTO dept(id,dname,loc)
VALUES
12 (
10,
'教研部',
'北京'),
13 (
20,
'学工部',
'上海'),
14 (
30,
'销售部',
'广州'),
15 (
40,
'财务部',
'深圳');
16 -- 职务表
17 CREATE TABLE job(
18 id
INT PRIMARY KEY,
19 jname
VARCHAR(
20),
20 description
VARCHAR(
50)
21 );
22 -- 添加四个职务
23 INSERT INTO job()
VALUES
24 (
1,
'董事长',
'管理整个公司，接单'),
25 (
2,
'经理',
'管理部门员工'),
26 (
3,
'销售员',
'向客户推销产品'),
27 (
4,
'文员',
'使用办公软件');
28
29
30
31 -- 员工表
32 CREATE TABLE emp(
33 id
INT PRIMARY KEY,
34 ename
VARCHAR(
10),
35 job_id
INT,
36 mgr
INT,
-- 上级领导
37 joindate DATE,
38 salary
DECIMAL(
7,
2),
39 bonus
DECIMAL(
7,
2),
40 dept_id
INT,
41 CONSTRAINT emp_jobid_ref_job_id_fk
FOREIGN KEY (job_id)
REFERENCES job(id),
-- 添加两个外键
42 CONSTRAINT emp_deptid_ref_dept_id_fk
FOREIGN KEY (dept_id)
REFERENCES dept(id)
43 );
44
45 -- 添加员工信息
46 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id)
VALUES
47 (
1001,
'孙悟空',
4,
1004,
'2000-12-17',
'8000.00',
NULL,
20),
48 (
1002,
'卢俊义',
3,
1006,
'2001-02-18',
'16000.00',
3000.00,
30),
49 (
1003,
'林冲',
3,
1006,
'2001-02-17',
'12500.00',
5000,
30),
50 (
1004,
'唐僧',
2,
1009,
'2001-04-02',
'19750.00',
NULL,
20),
51 (
1005,
'李逵',
4,
1006,
'2001-09-28',
'12500.00',
14000.00,
30),
52 (
1006,
'宋江',
2,
1009,
'2001-05-01',
'28500.00',
NULL,
30),
53 (
1007,
'刘备',
2,
1009,
'2001-09-01',
'14500.00',
NULL,
10),
54 (
1018,
'猪八戒',
4,
1004,
'2007-04-19',
'30000.00',
NULL,
20),
55 (
1009,
'罗贯中',
1,
NULL,
'2001-12-17',
'50000.00',
NULL,
10),
56 (
1010,
'吴用',
3,
1006,
'2001-09-08',
'15000.00',
0.00,
30),
57 (
1011,
'沙僧',
4,
1004,
'2007-05-25',
'11000.00',
NULL,
20),
58 (
1012,
'李逵',
4,
1006,
'2001-12-17',
'9500.00',
NULL,
30),
59 (
1013,
'小白龙',
4,
1004,
'2001-12-17',
'30000.00',
NULL,
20),
60 (
1014,
'关羽',
4,
1007,
'2002-12-17',
'13000.00',
NULL,
10);
61
62 -- 工资等级表
63 CREATE TABLE salarygrade(
64 grade
INT PRIMARY KEY,
65 losalary
INT,
66 hisalary
INT
67 );
68
69 -- 添加5个工资等级
70 INSERT INTO salarygrade(grade,losalary,hisalary)
VALUES
71 (
1,
7000,
12000),
72 (
2,
12000,
14000),
73 (
3,
14000,
20000),
74 (
4,
20000,
30000),
75 (
5,
30000,
99999);
76
77
78
79 -- 要求一：查询所有员工信息
80 SELECT * FROM emp,job
WHERE emp.`job_id`
=job.`id`;
81 -- 要求二：查询所有员工编号，员工姓名，工资，职务名称，职务描述 规范化即：
82 SELECT
83 t1.`id`,
-- 挨个备注是什么
84 t1.`ename`,
85 t2.`jname`,
86 t2.`description`,
87 t1.`salary`
88 FROM
89 emp t1,job t2
90 WHERE
91 t1.`job_id`
=t2.`id`;
92
93
94
95 -- 要求三：查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
96 SELECT
97 t1.`id`,
-- 挨个备注是什么
98 t1.`ename`,
99 t2.`jname`,
100 t2.`description`,
101 t1.`salary`,
102 t3.`dname`,
103 t3.`loc`
104 FROM
105 emp t1,job t2,dept t3
106 WHERE
107 t1.`job_id`
=t2.`id`
AND t1.`dept_id`
=t3.`id`;
-- 注意是用and连接
108
109
110
111
112 -- 要求四：查询员工的姓名，工资和工资等级
113
114 SELECT
115 t1.`ename`,
116 t1.`salary`,
117 t2.`grade`
118
119 FROM emp t1,salarygrade t2
120 WHERE t1.`salary`
BETWEEN t2.`losalary`
AND t2.`hisalary`;
121
122 -- 要求五 ：查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
123 SELECT
124 t1.`ename`,
125 t1.`salary`,
126 t4.`jname`,
127 t4.`description`,
128 t3.`dname`,
129 t3.`loc`,
130 t2.`grade`
131 FROM emp t1,salarygrade t2,dept t3,job t4
132
133 WHERE
134 t1.`job_id`
=t4.`id`
135 AND t1.`dept_id`
=t3.`id`
136 AND t1.`salary`
BETWEEN t2.`losalary`
AND t2.`hisalary`;
137
138
139 -- 要求六：查询部门编号，部门名称，部门位置，部门人数
140 -- 1.部门编号，部门名称，部门位置在dept表，部门人数enp表
141 -- 2.使用分组查询，按照emp.dept_id完成分组，查询count（id）
142 -- 3.使用子查询将第2步的查询结果的demp表进行关联查询
143
144 -- 完成第2小步 ，将得到的表作为虚拟表进行子查询
145 SELECT dept_id,
COUNT(id) total
-- total给起别名
146 FROM
147 emp
148 GROUP BY dept_id
149
150 -- 完成要求
151 SELECT t1.id,t1.dname,t1.loc,t2.total
152 FROM dept t1,
153 (
SELECT dept_id,
COUNT(id) total
154 FROM
155 emp
156 GROUP BY dept_id) t2
157 WHERE t1.id
=t2.dept_id;
158
159 -- 要求七：查询所有员工及其直接上级的姓名及其直接上级的姓名，没有上级的员工也要查询
160 -- 1，姓名 emp 直接上级的姓名 emp
161 -- emp表的id和mgr是自关联
162 -- 2，条件emp.id=emp.mgr
163 -- 3,查询左表的所有数据，和交集数据
164 -- 使用左外连接查询
165
166 -- 查询所有员工及其直接上级的姓名及其直接上级的姓名
167 SELECT
168 t1.`ename`,
169 t1.`mgr`,
170 t2.`id`,
171 t2.`ename`
172
173 FROM emp t1,emp t2
174 WHERE t1.`mgr`
=t2.`id`;
175
176 -- 没有上级的员工也要查询 使用左外连接查询
177
178 SELECT
179 t1.`ename`,
180 t1.`mgr`,
181 t2.`id`,
182 t2.`ename`
183 FROM emp t1
LEFT JOIN emp t2
184 ON t1.`mgr`
=t2.`id`;
转载于:https://www.cnblogs.com/fangtingfei/p/11317914.html
相关资源：mysql 多表联接查询练习
