系列九外键的相关操作

外键操作

外键的创建、删除

联合查询
#第三十九课时
--外键操作
--作用：保证数据的一致性和完整性

--外键是表的一个特殊字段。被参照的表是主表，外键所在的字段的表为字表，是指外键的原则需要记住
--就是依赖于数据库中已存在的表的主键。外间的作用时间里该表与其附表的关联关系，
--附表中对记录做操作时，子表中与之对应的信息也应该有相应的改变。

--可以实现一对一或一对多的关系
--父表和子表必须使用相同的存储引擎，而且禁止使用临时表
--数据表的存储引擎智能为innodb
--外键列和参照列必须具有相似的数据类型。其中数字的长度或是否为有符号位必须相同；而字符长度可以不同
--外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL自动创建索引



--实践操作，创建部门表（主表）
CREATE TABLE IF NOT EXISTS department (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE

)ENGINE = INNODB;

INSERT department(depName)VALUES('teach'),
('market'),
('undergo'),
('watch');


--创建员工表（字表）
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
)ENGINE = INNODB;

INSERT employee(username ,depId)VALUES('kin',1),
('joe',2 ),

('est',3),
('bob',4),
('tom',5);

--内连接实现
SELECT e.id,e.username,d.depName FROM
employee AS e
JOIN 
department AS d 
ON e.depId = d.id;

--删除watch部门,虽然部门没有了，但是子表中部门下的人仍然存在。
DELETE FROM department WHERE depName = 'watch';
--所以以上内容都是不符合要求的
DROP TABLE department , employee;

--下面是通过外键常见的表
CREATE TABLE IF NOT EXISTS department (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE

)ENGINE = INNODB;

INSERT department(depName)VALUES('teach'),
('market'),
('undergo'),
('watch');


--创建员工表（字表）
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
FOREINGN KYE(depId)REFERENCES department(id)
)ENGINE = INNODB;

INSERT employee(username ,depId)VALUES('kin',1),
('joe',2 ),
('est',3),
('bob',4),
('tom',5);

--如果子表中有某个父表记录下的内容，则父表中不能执行删除操作
--如果父表中没有相应的外键，子表在插入时所使用的外键超出父表所使用的范围，会报错

#第四十课时
--添加和删除外键的操作

--添加外键
ALTER TABLE employee DROP FOREIGN KEY em_fk_dep; --em_fk_dep是外键的索引
--删除外键
ALTER TABLE employee ADD CONSTRANINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);


--CASCADE:从附表中珊瑚或更新且自动删除或更新子表中匹配行
--SET NULL:从附表中删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
--RESTRICT:拒绝对父表的删除和更新操作
--NO ACTION:在MySQL中，与restrict关键字相同


--创建员工表（字表）
--ON DELETE CASCADE在删除的时候是及连的 
--ON UPDATE CASCADE在更新的时候是及连的
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
FOREINGN KYE(depId)REFERENCES department(id) ON DELETE CASCADE ;
)ENGINE = INNODB;

INSERT employee(username ,depId)VALUES('kin',1),
('joe',2 ),
('est',3),
('bob',4),
('tom',5);

--删除部门表中的一个部门
DELETE FROM department WHERE id = 1;
--更新部门表中的数据
UPDATE department SET id = id = 10;


#第四十一课时
--联合查询
--union 和union all
--NNION 合并相同信息
SELECT username FORM employee UNION SELECT username from cms_user;
--UNION ALL 不合并相同信息
SELECT username FORM employee UNION ALL SELECT username from cms_user;
系列九 外键的相关操作

系列九外键的相关操作
