黑马MySQL数据库学习day03 级联 多表查询 连接和子查询 表约束
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 存在外键的表 3 删表限制: 4 1.先删除从表,再删除主表。(不能直接删除主表,主表被从表引用,尽管实际可能还没有记录引用) 5 建表限制: 6 1.必须先建主表,再建从表(没有主表,从表无法建立外键关系) 7 */ 8 DROP TABLE IF EXISTS employee; 9 DROP TABLE IF EXISTS department; 10 11 CREATE TABLE department( 12 id INT PRIMARY KEY, 13 name varchar(50) UNIQUE NOT NULL 14 ); 15 -- 建表时,添加外键 16 CREATE TABLE employee( 17 id INT PRIMARY KEY, 18 name varchar(50) NOT NULL, 19 dep_id INT, 20 -- CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id) 21 -- CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id) ON DELETE CASCADE 22 -- CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE 23 CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE 24 ); 25 26 INSERT INTO department(id, name) 27 VALUES 28 (1, '技术部'), 29 (2, '测试部'); 30 31 INSERT INTO employee(id, name, dep_id) 32 VALUES 33 (1, '刘备', 1), 34 (2, '孙权', 1), 35 (3, '曹操', 2), 36 (4, '大乔', 2), 37 (5, '小乔', 2); 38 39 SHOW CREATE TABLE employee; 40 41 -- 级联删除,解决记录被引用时,删除记录不方便。 42 -- bug点: 删除记录时,引用该记录的记录也被删除 43 DELETE FROM department where id = 1; 44 45 -- 只想删除记录,不想删除引用该记录的记录。不应该用级联删除。先置null,清除引用,再删除记录。 46 UPDATE employee SET dep_id = NULL WHERE dep_id = 1; 47 DELETE FROM department where id = 1; 48 49 50 -- 级联更新,解决记录被引用时,更新记录不方便。 51 -- 更新记录时,引用该记录的记录也被更新 52 UPDATE department set id = 3 where id = 2;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 多表查询: 3 连接 4 1.内连接 5 表中符合连接条件的记录才会出现在结果中。 6 方式: 7 SELECT 查询目标 FROM tb_name1, tb_name2,... WHERE 查询条件 8 SELECT 查询目标 FROM tb_name1 9 [INNER] JOIN tb_name2 ON 查询条件 10 [INNER] JOIN tb_name3 ON 查询条件 11 ... 12 13 2.外连接 14 悬浮元组:不符合连接条件的记录。 15 左外连接 16 查询出左表的全部数据,悬浮元组跟null值连接。 17 SELECT 查询目标 FROM tb_name1 18 LEFT JOIN tb_name2 ON 查询条件 19 LEFT JOIN tb_name3 ON 查询条件 20 ... 21 22 右外连接 23 查询出右表的全部数据,悬浮元组跟null值连接。 24 SELECT 查询目标 FROM tb_name1 25 RIGHT JOIN tb_name2 ON 查询条件 26 RIGHT JOIN tb_name3 ON 查询条件 27 ... 28 29 全外连接(MySQL不支持) 30 查询出左表和右表的全部数据,左表和右表的悬浮元组跟null值连接。 31 32 注意特殊的数据: 33 新成立的部门测试部,没有员工 34 新入职的员工小白龙,没有部门 35 */ 36 drop table if exists emp; 37 drop table if exists dept; 38 39 create table dept( 40 id int primary key auto_increment, 41 name varchar(20) 42 ); 43 insert into dept (name) values ('开发部'),('市场部'),('财务部'),('测试部'); 44 # 创建员工表 45 create table emp ( 46 id int primary key auto_increment, 47 name varchar(10), 48 gender char(1), -- 性别 49 salary double, -- 工资 50 join_date date, -- 入职日期 51 dept_id int, 52 foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键) 53 ); 54 insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1); 55 insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2); 56 insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2); 57 insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3); 58 insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1); 59 insert into emp(name,gender,salary,join_date,dept_id) values('小白龙','男',3000,'2019-03-14',null); 60 61 -- 连接查询帮助 help join 62 63 -- 内连接不规范的写法,不容易阅读 64 SELECT emp.id, emp.name, emp.gender, dept.name FROM emp , dept WHERE emp.dept_id = dept.id; 65 66 -- 需求:查询员工和部门信息(不包括没有部门的员工) 67 -- 内连接,隐式 68 SELECT 69 t1.id, -- 员工id 70 t1.name, -- 员工姓名 71 t1.gender, -- 员工性别 72 t2.name -- 部门名称 73 FROM 74 emp AS t1, -- 员工表 75 dept AS t2 -- 部门表 76 WHERE 77 t1.dept_id = t2.id; 78 79 -- 内连接,显式 80 SELECT 81 t1.id, -- 员工id 82 t1.name, -- 员工姓名 83 t1.gender, -- 员工性别 84 t2.name -- 部门名称 85 FROM 86 emp AS t1 -- 员工表 87 JOIN dept AS t2 -- 部门表 88 ON 89 t1.dept_id = t2.id; 90 91 -- 需求2:查询所有员工的信息(包括没有部门的员工) 92 -- 方案一:左外连接 93 SELECT 94 t1.id, -- 员工id 95 t1.name, -- 员工姓名 96 t1.gender, -- 员工性别 97 t2.name -- 部门名称 98 FROM 99 emp AS t1 -- 员工表 100 LEFT JOIN 101 dept AS t2 -- 部门表 102 ON 103 t1.dept_id = t2.id; 104 105 -- 方案二:右外连接 106 SELECT 107 t1.id, -- 员工id 108 t1.name, -- 员工姓名 109 t1.gender, -- 员工性别 110 t2.name -- 部门名称 111 FROM 112 dept AS t2 -- 部门表 113 RIGHT JOIN 114 emp AS t1 -- 员工表 115 ON 116 t1.dept_id = t2.id;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 子查询: 3 1.子查询的结果是单行单列 4 即,子查询的结果是一个值,可以参与运算。 5 2.子查询的结果是多行单列 6 即,子查询的结果是一列,可以使用IN运算符。 7 3.子查询的结果是多行多列 8 即,子查询的结果是表,可以像实体表一样参与查询 9 10 注意特殊的数据: 11 测试部,新建立的部门,没有员工 12 小白龙,新入职的员工,没有部门 13 */ 14 drop table if exists emp; 15 drop table if exists dept; 16 17 create table dept( 18 id int primary key auto_increment, 19 name varchar(20) 20 ); 21 insert into dept (name) values ('开发部'),('市场部'),('财务部'),('测试部'); 22 # 创建员工表 23 create table emp ( 24 id int primary key auto_increment, 25 name varchar(10), 26 gender char(1), -- 性别 27 salary double, -- 工资 28 join_date date, -- 入职日期 29 dept_id int, 30 foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键) 31 ); 32 insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1); 33 insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',1); 34 insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2); 35 insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3); 36 insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1); 37 insert into emp(name,gender,salary,join_date,dept_id) values('小白龙','男',3000,'2019-03-14',null); 38 39 -- 需求1:查询工资最高的员工的信息(子查询的结果是一个值) 40 SELECT 41 * 42 FROM 43 emp 44 WHERE -- 先查出最高的工资,再根据最高工资,查询员工信息。 45 salary = ( 46 SELECT 47 MAX(salary) 48 FROM 49 emp 50 ); 51 52 -- 需求2:查询市场部和财务部的员工信息(子查询的结果是一列,使用IN运算符) 53 SELECT 54 * 55 FROM 56 emp 57 WHERE 58 dept_id IN ( 59 SELECT 60 id 61 FROM 62 dept 63 WHERE 64 name in('市场部', '财务部') 65 ); 66 67 -- 需求3:查询2011年后入职的员工号,姓名,入职日期和其部门名称(查询的结果是一张表,可以像实体表一样参与查询)(注意:没有部门的员工不需要查询) 68 -- 子查询解法 69 SELECT 70 t1.id, 71 t1.name, 72 t1.join_date, 73 t2.name 74 FROM 75 ( 76 SELECT 77 * 78 FROM 79 emp 80 WHERE 81 join_date >= '2011-01-01' 82 ) AS t1, 83 dept AS t2 84 WHERE 85 t1.dept_id = t2.id; 86 87 -- 连接解法 88 SELECT 89 t1.id, 90 t1.name, 91 t1.join_date, 92 t2.name 93 FROM 94 emp AS t1, 95 dept AS t2 96 WHERE 97 t1.dept_id = t2.id 98 AND t1.join_date >= '2011-01-01';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 连接练习: 3 bug点: 4 1.给表起别名后,不能使用原名称 5 2.分组对null值也看成一组。 6 约定: 7 每个员工都有部门和职务。 8 特殊数据: 9 财务部新成立,没有员工。 10 */ 11 DROP TABLE if EXISTS emp; 12 DROP TABLE if EXISTS dept; 13 DROP TABLE if EXISTS job; 14 DROP TABLE if EXISTS salarygrade; 15 16 -- 部门表 17 CREATE TABLE dept ( 18 id INT PRIMARY KEY PRIMARY KEY, -- 部门id 19 dname VARCHAR(50), -- 部门名称 20 loc VARCHAR(50) -- 部门所在地 21 ); 22 23 -- 添加4个部门 24 INSERT INTO dept(id,dname,loc) VALUES 25 (10,'教研部','北京'), 26 (20,'学工部','上海'), 27 (30,'销售部','广州'), 28 (40,'财务部','深圳'); 29 30 31 32 -- 职务表,职务名称,职务描述 33 CREATE TABLE job ( 34 id INT PRIMARY KEY, 35 jname VARCHAR(20), 36 description VARCHAR(50) 37 ); 38 39 -- 添加4个职务 40 INSERT INTO job (id, jname, description) VALUES 41 (1, '董事长', '管理整个公司,接单'), 42 (2, '经理', '管理部门员工'), 43 (3, '销售员', '向客人推销产品'), 44 (4, '文员', '使用办公软件'); 45 46 47 48 -- 员工表 49 CREATE TABLE emp ( 50 id INT PRIMARY KEY, -- 员工id 51 ename VARCHAR(50), -- 员工姓名 52 job_id INT, -- 职务id 53 mgr INT , -- 上级领导 54 joindate DATE, -- 入职日期 55 salary DECIMAL(7,2), -- 工资 56 bonus DECIMAL(7,2), -- 奖金 57 dept_id INT, -- 所在部门编号 58 CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id), 59 CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id) 60 ); 61 62 -- 添加员工 63 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 64 (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20), 65 (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30), 66 (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30), 67 (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20), 68 (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30), 69 (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30), 70 (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10), 71 (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20), 72 (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10), 73 (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30), 74 (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20), 75 (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30), 76 (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20), 77 (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10); 78 79 80 81 -- 工资等级表 82 CREATE TABLE salarygrade ( 83 grade INT PRIMARY KEY, -- 级别 84 losalary INT, -- 最低工资 85 hisalary INT -- 最高工资 86 ); 87 88 -- 添加5个工资等级 89 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 90 (1,7000,12000), 91 (2,12010,14000), 92 (3,14010,20000), 93 (4,20010,30000), 94 (5,30010,99990); 95 96 -- 需求:(约定所有的员工都有部门和工作) 97 98 -- 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述 99 /* 100 分析: 101 1.员工编号,员工姓名,工资 emp表,职务名称,职务描述 job表 102 2.使用连接,连接条件是 emp.job_id = job.id 103 */ 104 SELECT 105 t1.id, -- 员工编号 106 t1.ename, -- 员工姓名 107 t1.salary, -- 员工工资 108 t2.jname, -- 职务名称 109 t2.description -- 职务描述 110 FROM 111 emp AS t1 -- 员工表 112 JOIN 113 job AS t2 -- 职务表 114 ON 115 t1.job_id = t2.id; 116 117 118 -- 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置 119 /* 120 分析: 121 1.员工编号,员工姓名,工资 emp表,职务名称,职务描述 job表,部门名称,部门位置 dept表 122 2.使用连接查询,连接条件 emp.job_id = job.id, emp.dept_id = dept.id 123 */ 124 SELECT 125 t1.id, -- 员工编号 126 t1.ename, -- 员工姓名 127 t1.salary, -- 员工工资 128 t2.jname, -- 职务名称 129 t2.description, -- 职务描述 130 t3.dname, -- 部门名称 131 t3.loc -- 部门位置 132 FROM 133 emp AS t1 -- 员工表 134 JOIN 135 job AS t2 -- 职务表 136 ON 137 t1.job_id = t2.id 138 JOIN 139 dept AS t3 -- 部门表 140 ON t1.dept_id = t3.id; 141 -- 3.查询员工姓名,工资,工资等级 142 /* 143 分析: 144 1.员工姓名,工资 emp表,工资等级 salarygrade表 145 2.通过连接,连接条件是工资在区间内。emp.salary >= salarygrade.losalary <= salarygrade.hisalary 146 简化 emp.salary BETWEEN salarygrade.losalary AND salarygrade.hisalary 147 */ 148 SELECT 149 t1.ename, -- 员工姓名 150 t1.salary, -- 工资 151 t2.grade -- 工资等级 152 FROM 153 emp AS t1 -- 员工表 154 JOIN 155 salarygrade AS t2 -- 薪资等级表 156 ON 157 t1.salary BETWEEN t2.losalary AND t2.hisalary; 158 -- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级 159 /* 160 分析: 161 1.员工姓名,工资 emp表,职务名称,职务描述 job表,部门名称,部门位置 dept表,工资等级 salarygrade表 162 2.需要进行连接,连接条件: 163 emp.job_id = job.id, emp.dept_id = dept.id 164 emp.salary BETWEEN salarygrade.losalary AND salarygrade.hisalary 165 */ 166 SELECT 167 t1.ename, -- 员工姓名 168 t1.salary, -- 工资 169 t2.jname, -- 职务名称 170 t2.description, -- 职务描述 171 t3.dname, -- 部门名称 172 t3.loc, -- 部门位置 173 t4.grade -- 工资等级 174 FROM 175 emp AS t1 -- 员工表 176 JOIN 177 job AS t2 -- 职务表 178 ON 179 t1.job_id = t2.id 180 JOIN 181 dept AS t3 -- 部门表 182 ON 183 t1.dept_id = t3.id 184 JOIN 185 salarygrade AS t4 -- 薪资等级表 186 ON 187 t1.salary BETWEEN t4.losalary AND t4.hisalary; 188 189 -- 5.查询出部门编号、部门名称、部门位置、部门人数 190 /* 191 分析: 192 1.查询出部门编号、部门名称、部门位置 dept表、部门人数 emp表 193 2.员工表和部门表根据外键连接,左外连接进行连接,再统计部门人数 194 特殊数据: 195 财务部新成立,没有员工。 196 */ 197 SELECT 198 t1.id, 199 t1.dname, 200 t1.loc, 201 count(t2.id) -- 不能写count(*),因为它会统计悬浮元组,悬浮元组表示没有员工,不应该被统计 202 FROM 203 dept AS t1 -- 部门表 204 LEFT JOIN 205 emp AS t2 -- 员工表 206 ON 207 t1.id = t2.dept_id 208 GROUP BY 209 t1.id; -- 根据部门分组 210 211 /* 212 典型错误代码:不能查询出人数为0的部门 213 */ 214 SELECT 215 t1.id, 216 t1.dname, 217 t1.loc, 218 count(*) 219 -- count(*) count(t1.id) count(t2.id) 都得不到正确结果 220 FROM 221 dept AS t1, 222 emp AS t2 223 WHERE 224 t1.id = t2.dept_id 225 GROUP BY 226 t1.id; 227 228 -- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询 229 /* 230 分析: 231 1.所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询 emp表 232 2.自连接,连接条件 emp.mgr = emp.id,必须使用别名 233 */ 234 SELECT 235 t1.ename AS ename, -- 员工姓名 236 t2.ename AS manager -- 直接上级 237 FROM -- 对员工表进行自连接,左外连接。 238 emp AS t1 239 LEFT JOIN 240 emp AS t2 241 ON 242 t1.mgr = t2.id;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 约束分类: 4种约束的CRUD 3 注意点: 4 唯一 主键 外键 都可以约束多列。 5 4种约束,建表后,添加约束,如果表中有数据,则可能出bug,导致约束添加失败。 6 7 查询约束: 8 查询约束信息:SHOW CREATE TABLE 表名; 9 查询约束语法:help create talbe; help alter table 10 11 1.非空 12 NOT NULL 非空 13 建表时加非空,删除非空,建表后加非空 14 15 建表后添加非空约束: 16 bug: 如果表中有数据,并且指定列有null值,则非空约束添加失败。 17 18 2.唯一 19 UNIQUE 唯一,可以有多个null值 20 建表时加UNIQUE,删除UNIQUE,建表后加UNIQUE 21 22 建表后添加唯一约束: 23 bug: 如果表中有数据,并且指定的列有重复数据,则UNIQUE约束添加失败 24 25 注意点: 26 UNIQUE约束允许多个null值。 27 28 3.主键 29 PRIMARY KEY 唯一非空,一张表只能有一个主键约束 30 建表时加PRIMARY KEY,删除PRIMARY KEY,建表后加PRIMARY KEY 31 32 建表后添加主键约束: 33 bug: 如果表中有数据,并且指定列含有null值或者重复数据,则添加主键失败。 34 35 注意点: 36 1.PRIMARY KEY != UNIQUE + NOT NULL(因为主键只能有一个,而UNIQUE + NOT NULL可以多个) 37 2.删除PRIMARY KEY后还会有一个NOT NULL约束 38 39 4.外键 40 FOREIGN KEY 外键约束可以设置级联更新和级联删除 41 建表时加FOREIGN KEY,删除FOREIGN KEY,建表后加FOREIGN KEY 42 43 建表后添加外键约束: 44 bug: 如果从表有数据,并且外键列引用了被引用列没有的值,则添加外键约束失败 45 46 注意点: 47 1.添加外键约束,主表必须存在 48 2.表数据约束: 49 1.从表不能引用主表中不存在的值 50 2.主表不能删除被从表引用的记录 51 3.删表:从表引用主表,不能直接删除主表。 52 53 5.综合: 54 唯一 主键 外键 都可以约束多列。 55 4种约束,建表后,添加约束,如果表中有数据,则可能出bug,导致约束添加失败。 56 57 6.自动增长 58 AUTO_INCREMENT 59 1.一张表只能有一个自动增长列,该列必须是数值类型 60 2.自增的新值= 表数据最大值 + 1 61 7.默认值 62 DEFAULT 63 64 */ 65 DROP TABLE IF EXISTS student; 66 67 -- 建表时添加NOT NULL约束 68 CREATE TABLE student( 69 name VARCHAR(50) NOT NULL 70 ); 71 72 -- 删除NOT NULL约束 73 ALTER TABLE student MODIFY name VARCHAR(50); 74 75 -- 建表后添加NOT NULL约束 76 -- bug: 如果表中有数据,并且指定列有null值,则非空约束添加失败。 77 ALTER TABLE student MODIFY name VARCHAR(50) NOT NULL; 78 DELETE FROM student WHERE name IS NULL; 79 80 81 82 DROP TABLE IF EXISTS student; 83 84 -- 建表时,添加UNIQUE约束 85 CREATE TABLE student( 86 name VARCHAR(50) UNIQUE 87 ); 88 89 CREATE TABLE student( 90 name VARCHAR(50), 91 CONSTRAINT uni_name UNIQUE(name) 92 ); 93 94 -- 删除UNIQUE约束 95 ALTER TABLE student DROP INDEX name; 96 97 -- 建表后,添加UNIQUE约束 98 -- bug: 如果表中有数据,并且指定的列有重复数据,则UNIQUE约束添加失败 99 ALTER TABLE student MODIFY name VARCHAR(50) UNIQUE; 100 ALTER TABLE student ADD CONSTRAINT uni_name UNIQUE(name); 101 102 -- 查看表信息 103 SHOW CREATE TABLE student; 104 105 /* 106 PRIMARY KEY 主键约束 107 唯一非空,一张表只能有一个主键约束 108 注意点: 109 PRIMARY KEY != UNIQUE + NOT NULL(因为主键只能有一个,而UNIQUE + NOT NULL可以多个) 110 */ 111 DROP TABLE IF EXISTS student; 112 113 -- 建表时,添加PRIMARY KEY约束 114 CREATE TABLE student( 115 name VARCHAR(50) PRIMARY KEY 116 ); 117 118 CREATE TABLE student( 119 name VARCHAR(50), 120 PRIMARY KEY(name) 121 ); 122 123 -- 删除主键 124 ALTER TABLE student DROP PRIMARY KEY; -- 删除后还有一个NOT NULL约束 125 126 -- 创建完表后,添加主键 127 -- bug: 如果表中有数据,并且指定列含有null值或者重复数据,则添加主键失败。 128 ALTER TABLE student MODIFY name VARCHAR(50) PRIMARY KEY; 129 ALTER TABLE student ADD PRIMARY KEY (name); 130 131 -- 查看表:约束 132 SHOW CREATE TABLE student; 133 134 135 /* 136 外键约束: 137 注意点: 138 1.添加外键约束,主表必须存在 139 2.表数据约束: 140 1.从表不能引用主表中不存在的值 141 2.主表不能删除被从表引用的记录 142 3.删表:从表引用主表,不能直接删除主表。 143 */ 144 DROP TABLE IF EXISTS employee; 145 DROP TABLE IF EXISTS department; 146 147 CREATE TABLE department( 148 id INT PRIMARY KEY, 149 name varchar(50) UNIQUE NOT NULL 150 ); 151 -- 建表时,添加外键 152 CREATE TABLE employee( 153 id INT PRIMARY KEY, 154 name varchar(50) NOT NULL, 155 dep_id INT, 156 CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id) 157 ); 158 -- 删除外键 159 ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk; 160 161 -- 建表后,添加外键 162 -- bug: 如果从表有数据,并且外键列引用了被引用列没有的值,则添加外键约束失败 163 ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id); 164 165 SHOW CREATE TABLE employee;