主键外键

#若系统中存在数据库ems，则删除数据库ems
DROP DATABASE IF EXISTS ems;

# 创建一个名称为ems，字符集为utf8的数据库
CREATE DATABASE IF NOT EXISTS ems CHARACTER SET utf8;

# 选择数据库ems
USE ems;

# 向数据库ems中创建员工表emp
# -----------------------------------------------------------------------------------------------
# 字段	类型		主键	外键	非空	唯一	自增	字段说明
# -----------------------------------------------------------------------------------------------
# empno	INT		是	否	是	是	否	员工号
# ename	VARCHAR(20)	否	否	是	是	否	员工名
# job	VARCHAR(20)	否	否	否	否	否	岗位
# mgr	INT		否	否	否	否	否	管理者
# sal 	DECIMAL(7,2)	否	否	否	否	否	工资
# comm	DECIMAL(7,2)	否	否	否	否	否	奖金
# deptno	INT		否	否	否	否	否	部门号
# -----------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS emp(
  empno INT PRIMARY KEY,
  ename VARCHAR(20) NOT NULL UNIQUE,
  job VARCHAR(20),
  mgr INT ,
  sal DECIMAL(7, 2),
  comm DECIMAL(7, 2),
  deptno INT
);

DESC emp;

#/*3.1插入数据  */
#------------------------------------------------------------------------------
#empno	ename	job	mgr	sal 	comm	deptno
#------------------------------------------------------------------------------
#9369	张三	保洁	9982	900.00	NULL	20
#9499	孙七	销售	9698	2600.00	300.00	30
#9521	周八	销售	9698	2258.00	500.00	30
#9566	李四	经理	9839	3995.00	NULL	20
#9654	吴九	销售	9698	2250.00	1400.00	30
#9839	刘一	董事长	NULL	6000.00	NULL	10
#9844	郑十	销售	9698	2500.00	0.00	30
#9900	箫十一	保洁	9698	1050.00	NULL	30
#9902	赵六	分析员	9566	4000.00	NULL	20
#9982	陈二	经理	9839	3450.00	NULL	10
#9988	王五	分析员	9566	4000.00	NULL	20
#-------------------------------------------------------------------------------
#/*3.1.1一次插入单条数据  */
#用简化形式将第一条记录插入到员工表emp中
INSERT INTO emp VALUES
(9369, '张三', '保洁', 9982, 900, NULL, 20);

SELECT * FROM emp;

#用一般形式将第二条记录插入到员工表emp中
INSERT INTO emp(empno, ename, job, mgr, sal, comm, deptno)
VALUES(9499, '孙七', '销售', 9698, 2600, 300, 30);

SELECT * FROM emp;

#/*3.1.2一次插入多条数据  */
# 用一条语句将剩下的记录插入到员工表emp中
INSERT INTO emp VALUES
(9521, '周八', '销售', 9698, 2258, 500, 30),
(9566, '李四', '经理', 9839, 3995, NULL, 20),
(9654, '吴九', '销售', 9698, 2250, 1400, 30),
(9839, '刘一', '董事长', NULL, 6000, NULL, 10),
(9844, '郑十', '销售', 9698, 2500, 0, 30),
(9900, '箫十一', '保洁', 9698, 1050, NULL, 30),
(9902, '赵六', '分析员', 9566, 4000, NULL, 20),
(9982, '陈二', '经理', 9839, 3450, NULL, 10),
(9988, '王五', '分析员', 9566, 4000, NULL, 20);

SELECT * FROM emp;

#/*3.2更新数据  */
# 将员工张三的sal字段的值在原来的基础上增加200
UPDATE emp
SET sal = sal + 200
WHERE ename = '张三';

SELECT * FROM emp;

# 将职位为保洁的员工涨薪300
 UPDATE emp
 SET sal = sal + 300
 WHERE job = '保洁';

SELECT * FROM emp;

# 将所有员工涨薪500
UPDATE emp
SET sal = sal + 500;

SELECT * FROM emp;

#/*3.3删除数据  */

# 删除员工表中孙七的数据
DELETE FROM emp
WHERE ename = '孙七';

SELECT * FROM emp;

# 删除员工表中职位为分析员的数据
DELETE FROM emp
WHERE job = '分析员';

SELECT * FROM emp;
# 将员工表中的所有数据全部删除
DELETE FROM emp;

SELECT * FROM emp;

#/*多学一招：使用关键字TRUNCATE删除表中数据  */
# 创建tab_truncate表
#-----------------------------------------------------
#字段	类型		主键	自动增长
# -----------------------------------------------------
# id 	INT 		是	是
# NAME 	VARCHAR(4)	否	否
# -----------------------------------------------------
CREATE TABLE tab_truncate(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(4)
);

DESC tab_truncate;

# 向tab_truncate表中添加5条数据
# ------------------------
# id	NAME
# ------------------------
# 1	A
# 2	B
# 3	C
# 4	D
# 5	E
# ------------------------
INSERT INTO tab_truncate(`name`) VALUES
('A'),
('B'),
('C'),
('D'),
('E');

SELECT * FROM tab_truncate;

# 使用TRUNCATE语句删除tab_truncate表中的所有数据
TRUNCATE TABLE tab_truncate;

SELECT * FROM tab_truncate;

# 在表tab_truncate 中，重新添加5条数据
# ------------------------
# id	NAME
# ------------------------
# 1	F
# 2	G
# 3	H
# 4	I
# 5	J
# ------------------------
INSERT INTO tab_truncate(`name`) VALUES
('F'),
('G'),
('H'),
('I'),
('J');

SELECT *
FROM tab_truncate;

# 使用DELETE语句删除tab_truncate表中的所有数据
DELETE FROM tab_truncate;

# 向tab_truncate数据表中添加一条新数据
 INSERT INTO tab_truncate(NAME) VALUES('K');

 SELECT * FROM tab_truncate;

#-----------------------------------第五章第三节----------------------------------------------
#若数据库ems存在，则删除数据库ems
DROP DATABASE IF EXISTS ems;

# 创建一个名称为ems，字符集为utf8的数据库
CREATE DATABASE IF NOT EXISTS ems CHARACTER SET utf8;

# 选择数据库ems
USE ems;

#向数据库ems中创建部门表dept
#-----------------------------------------------------------------------------------------------
#字段	类型		主键	外键	非空	唯一	自增	字段说明
#-----------------------------------------------------------------------------------------------
#deptno	INT		是	否	是	是	否	部门号
#dname 	VARCHAR(20)	否	否	是	是	否	部门名
#------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dept(
  deptno INT PRIMARY KEY,
  dname VARCHAR(20) NOT NULL UNIQUE
);

DESC dept;

#向部门表中插入数据
#-------------------------
#deptno	dname
#-------------------------
# 10	总裁办
# 20	研究院
# 30	销售部
# 40	运营部
#-------------------------
INSERT INTO dept VALUES
(10, '总裁办'),
(20, '研究院'),
(30, '销售部'),
(40, '运营部');

SELECT * FROM dept;

# 向数据库ems中创建员工表emp
#-----------------------------------------------------------------------------------------------
#字段	类型		主键	外键	非空	唯一	自增	字段说明
#-----------------------------------------------------------------------------------------------
# empno	INT		是	否	是	是	否	员工号
# ename	VARCHAR(20)	否	否	是	是	否	员工名
# job	VARCHAR(20)	否	否	否	否	否	岗位
# mgr	INT		否	否	否	否	否	管理者
# sal 	DECIMAL(7,2)	否	否	否	否	否	工资
# comm	DECIMAL(7,2)	否	否	否	否	否	奖金
# deptno	INT		否	是	否	否	否	部门号
#-----------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS emp(
  empno INT PRIMARY KEY,
  ename VARCHAR(20) NOT NULL UNIQUE,
  job VARCHAR(20),
  mgr INT,
  sal DECIMAL(7, 2),
  comm DECIMAL(7, 2),
  deptno INT,
  CONSTRAINT FOREIGN KEY(deptno) REFERENCES dept(deptno)
);

DESC emp;

#/*向员工表插入数据  */
#------------------------------------------------------------------------------
empno	ename	job	mgr	sal 	comm	deptno
#------------------------------------------------------------------------------
# 9369	张三	保洁	9982	900.00	NULL	20
# 9499	孙七	销售	9698	2600.00	300.00	30
# 9521	周八	销售	9698	2258.00	500.00	30
# 9566	李四	经理	9839	3995.00	NULL	20
# 9654	吴九	销售	9698	2250.00	1400.00	30
# 9839	刘一	董事长	NULL	6000.00	NULL	10
# 9844	郑十	销售	9698	2500.00	0.00	30
# 9900	箫十一	保洁	9698	1050.00	NULL	30
# 9902	赵六	分析员	9566	4000.00	NULL	20
# 9982	陈二	经理	9839	3450.00	NULL	10
# 9988	王五	分析员	9566	4000.00	NULL	20
#-------------------------------------------------------------------------------
INSERT INTO emp VALUES
(9369, '张三', '保洁', 9982, 900, NULL, 20),
(9499, '孙七', '销售', 9698, 2600, 300, 30),
(9521, '周八', '销售', 9698, 2258, 500, 30),
(9566, '李四', '经理', 9839, 3995, NULL, 20),
(9654, '吴九', '销售', 9698, 2250, 1400, 30),
(9839, '刘一', '董事长', NULL, 6000, NULL, 10),
(9844, '郑十', '销售', 9698, 2500, 0, 30),
(9900, '箫十一', '保洁', 9698, 1050, NULL, 30),
(9902, '赵六', '分析员', 9566, 4000, NULL, 20),
(9982, '陈二', '经理', 9839, 3450, NULL, 10),
(9988, '王五', '分析员', 9566, 4000, NULL, 20);

#5.3.0 删除员工表的外键约束
#查看外键约束名 show create table emp;
ALTER TABLE emp DROP FOREIGN KEY emp_ibfk_1;

#查看表中所有索引show index from emp;
ALTER TABLE emp DROP INDEX deptno;

DESC emp;

# /*5.3.1添加外键约束 */
# 给员工表emp的deptno字段添加外键约束，关联部门表dept的deptno字段，约束名fk_deptno
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);

DESC emp;

#/*5.3.2操作关联表 */
## 主表dept添加数据
#-------------------------
#deptno	dname
#-------------------------
#50	人力资源部
#-------------------------
INSERT INTO dept VALUES
(50, '人力资源部');

SELECT * FROM dept;

# 在往员工表emp中添加数据
#------------------------------------------------------------------------------
#empno	ename	job		mgr	sal 	comm	deptno
#------------------------------------------------------------------------------
#9966	八戒	运营专员	9839	3000.00	2000	40
#9999	悟空	人事专员	9982	3000.00	NULL	50
#-------------------------------------------------------------------------------
INSERT INTO emp VALUES
(9966, '八戒', '运营专员', 9839, 3000, 2000, 40),
(9999, '悟空', '人事专员', 9982, 3000, NULL, 50);

SELECT * FROM emp;

# 查询人力资源部有哪些员工
SELECT *
FROM emp
WHERE deptno = (
    SELECT deptno
    FROM dept
    WHERE dname = '人力资源部'
);

# 删除从表emp中属于人力资源部的员工信息
DELETE FROM emp
WHERE deptno = (
    SELECT deptno
    FROM dept
    WHERE dname = '人力资源部'
);

SELECT * FROM emp;

# 查询属于人力资源部的员工信息
SELECT *
FROM emp
WHERE deptno = (
    SELECT deptno
    FROM dept
    WHERE dname = '人力资源部'
);

# 删除主表dept中人力资源部的数据
DELETE FROM dept
WHERE dname = '人力资源部';

SELECT * FROM dept;

# /*5.3.3删除外键约束 */
# 将员工表emp中的外键约束删除
ALTER TABLE emp DROP FOREIGN KEY fk_deptno;

#查看表中所有索引show index from emp;

ALTER TABLE emp DROP INDEX fk_deptno;

DESC emp;

#删除数据库ems
DROP DATABASE ems;

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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364

#若数据库ems存在，则删除数据库ems

DROP DATABASE IF EXISTS ems;

# 创建一个名称为ems，字符集为utf8的数据库
CREATE DATABASE ems CHARACTER SET utf8;

# 选择数据库ems
USE ems;

#向数据库ems中创建部门表dept
# -----------------------------------------------------------------------------------------------
# 字段	类型		主键	外键	非空	唯一	自增	字段说明
# -----------------------------------------------------------------------------------------------
# deptno	INT		是	否	是	是	否	部门号
# dname 	VARCHAR(20)	否	否	是	是	否	部门名
# ------------------------------------------------------------------------------------------------

CREATE TABLE dept(
  deptno INT PRIMARY KEY,
  dname VARCHAR(20) NOT NULL UNIQUE
);



#向部门表中插入数据
# -------------------------
# deptno	dname
# -------------------------
# 10	总裁办
# 20	研究院
# 30	销售部
# 40	运营部
# -------------------------

INSERT INTO dept VALUES
(10, '总裁办'),
(20, '研究院'),
(30, '销售部'),
(40, '运营部');

# 向数据库ems中创建员工表emp
# -----------------------------------------------------------------------------------------------
# 字段	类型		主键	外键	非空	唯一	自增	字段说明
# -----------------------------------------------------------------------------------------------
# empno	INT		是	否	是	是	否	员工号
# ename	VARCHAR(20)	否	否	是	是	否	员工名
# job	VARCHAR(20)	否	否	否	否	否	岗位
# mgr	INT		否	否	否	否	否	管理者
# sal 	DECIMAL(7,2)	否	否	否	否	否	工资
# comm	DECIMAL(7,2)	否	否	否	否	否	奖金
# deptno	INT		否	是	否	否	否	部门号
# -----------------------------------------------------------------------------------------------

CREATE TABLE emp(
  empno INT PRIMARY KEY,
  ename VARCHAR(20) NOT NULL UNIQUE,
  job VARCHAR(20),
  mgr INT,
  sal DECIMAL(7, 2),
  comm DECIMAL(7, 2),
  deptno INT,
  FOREIGN KEY(deptno) REFERENCES dept(deptno)
);

DESC emp;

# /*向员工表插入数据  */
# ------------------------------------------------------------------------------
# empno	ename	job	mgr	sal 	comm	deptno
# ------------------------------------------------------------------------------
# 9369	张三	保洁	9982	900.00	NULL	20
# 9499	孙七	销售	9698	2600.00	300.00	30
# 9521	周八	销售	9698	2258.00	500.00	30
# 9566	李四	经理	9839	3995.00	NULL	20
# 9654	吴九	销售	9698	2250.00	1400.00	30
# 9839	刘一	董事长	NULL	6000.00	NULL	10
# 9844	郑十	销售	9698	2500.00	0.00	30
# 9900	箫十一	保洁	9698	1050.00	NULL	30
# 9902	赵六	分析员	9566	4000.00	NULL	20
# 9982	陈二	经理	9839	3450.00	NULL	10
# 9988	王五	分析员	9566	4000.00	NULL	20
# -------------------------------------------------------------------------------

INSERT INTO emp VALUES
(9369, '张三', '保洁', 9982, 900, NULL, 20),
(9499, '孙七', '销售', 9698, 2600, 300, 30),
(9521, '周八', '销售', 9698, 2258, 500, 30),
(9566, '李四', '经理', 9839, 3995, NULL, 20),
(9654, '吴九', '销售', 9698, 2250, 1400, 30),
(9839, '刘一', '董事长', NULL, 6000, NULL, 10),
(9844, '郑十', '销售', 9698, 2500, 0, 30),
(9900, '箫十一', '保洁', 9698, 1050, NULL, 30),
(9902, '赵六', '分析员', 9566, 4000, NULL, 20),
(9982, '陈二', '经理', 9839, 3450, NULL, 10),
(9988, '王五', '分析员', 9566, 4000, NULL, 20);

SELECT * FROM emp;

#5.3.0 删除员工表的外键约束

ALTER TABLE emp DROP FOREIGN KEY emp_ibfk_1;
ALTER TABLE emp DROP INDEX deptno;

# /*5.3.1添加外键约束 */
# 给员工表emp的deptno字段添加外键约束，关联部门表dept的deptno字段，约束名fk_deptno
ALTER TABLE emp ADD CONSTRAINT fk_deptno  FOREIGN KEY(deptno) REFERENCES dept(deptno);

#/*5.3.2操作关联表 */
# 主表dept添加数据
# -------------------------
# deptno	dname
# -------------------------
# 50	人力资源部
# -------------------------

INSERT INTO dept VALUES
(50, '人力资源部');

SELECT * FROM dept;

# 在往员工表emp中添加数据
# ------------------------------------------------------------------------------
# empno	ename	job	mgr	sal 	comm	deptno
# ------------------------------------------------------------------------------
# 9966	八戒	运营专员	9839	3000.00	2000	40
# 9999	悟空	人事专员	9982	3000.00	NULL	50
# -------------------------------------------------------------------------------

INSERT INTO emp VALUES
(9966, '八戒', '运营专员', 9839, 3000, 2000, 40),
(9999, '悟空', '人事专员', 9982, 3000, NULL, 50);

SELECT * FROM emp;

# 查询人力资源部有哪些员工
SELECT *
FROM emp
#where deptno = (人力资源的部门号);
WHERE deptno = (
    SELECT deptno
    FROM dept
    WHERE dname = '人力资源部'
);

# 删除从表emp中属于人力资源部的员工信息

DELETE FROM emp
#where deptno = (人力资源的部门号)
WHERE deptno = (
    SELECT deptno
    FROM dept
    WHERE dname = '人力资源部'
);

# 查询属于人力资源部的员工信息
SELECT *
FROM emp
WHERE deptno = (
    SELECT deptno
    FROM dept
    WHERE dname = '人力资源部'
);

# 删除主表dept中人力资源部的数据
DELETE FROM dept
WHERE dname = '人力资源部';

SELECT * FROM dept;

/*5.3.3删除外键约束 */
# 将员工表emp中的外键约束删除
ALTER TABLE emp DROP FOREIGN KEY fk_deptno;

ALTER TABLE emp DROP INDEX fk_deptno;

主键外键 ​

主键外键