DQL:外键约束,级联更新 

  1 CREATE TABLE emp (-- 创建emp表
  2 id INT PRIMARY KEY AUTO_INCREMENT,
  3 NAME VARCHAR(30),
  4 age INT,
  5 dep_name VARCHAR(30),-- 部门名称
  6 dep_location VARCHAR(30)-- 部门地址
  7 );
  8 -- 添加数据
  9 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
 10 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
 11 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
 12 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
 13 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
 14 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
 15 
 16 SELECT *FROM emp;
 17 
 18 -- 创建部门表(id,dep_name,dep_location)
 19 -- 一方，主表
 20 CREATE TABLE department(
 21 id INT PRIMARY KEY AUTO_INCREMENT,
 22 dep_name VARCHAR(20),
 23 dep_location VARCHAR(20)
 24 );
 25 -- 创建员工表(id,name,age,dep_id)
 26 -- 多方，从表
 27 CREATE TABLE employee(
 28 id INT PRIMARY KEY AUTO_INCREMENT,
 29 NAME VARCHAR(20),
 30 age INT,
 31 dep_id INT -- 外键对应主表的主键
 32 )
 33 
 34 -- 添加 2 个部门
 35 INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
 36 
 37 -- 添加员工,dep_id 表示员工所在的部门
 38 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
 39 INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
 40 INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
 41 INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
 42 INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
 43 INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
 44 
 45 SELECT *FROM employee;
 46 
 47 SELECT *FROM department;
 48 
 49 DROP TABLE department;
 50 
 51 DROP TABLE employee;
 52 
 53 -- 创建部门表(id,dep_name,dep_location)
 54 -- 一方，主表
 55 CREATE TABLE department(
 56 id INT PRIMARY KEY AUTO_INCREMENT,
 57 dep_name VARCHAR(20),
 58 dep_location VARCHAR(20)
 59 );
 60 -- 添加 2 个部门
 61 INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
 62 
 63 -- 创建从表 employee 并添加外键约束 emp_depid_fk
 64 -- 多方，从表
 65 CREATE TABLE employee(
 66 id INT PRIMARY KEY AUTO_INCREMENT,
 67 NAME VARCHAR(20),
 68 age INT,
 69 dep_id INT, -- 外键对应主表的主键
 70 -- 创建外键约束
 71 CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
 72 )
 73 -- 正常添加数据
 74 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
 75 INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
 76 INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
 77 INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
 78 INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
 79 INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
 80 
 81 SELECT * FROM employee;
 82 
 83 SELECT *FROM department;
 84 
 85 -- 删除外键
 86 
 87 ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
 88 
 89 -- 添加外键
 90 ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);
 91 
 92 UPDATE employee SET dep_id=NULL WHERE dep_id=1;
 93 
 94 UPDATE employee SET dep_id=5 WHERE dep_id IS NULL;
 95 
 96 UPDATE employee SET dep_id=1 WHERE  dep_id=5;
 97 
 98 -- 添加外键,设置级联删除
 99 ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
100 ON DELETE CASCADE;
101 
102 -- 添加外键,设置级联更新
103 ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
104 ON UPDATE CASCADE;

 

posted @ 2021-03-30 12:20  丁帅帅dss  阅读(94)  评论(0)    收藏  举报