数据库_days3_连接查询（外键、联合查询）

外键的介绍

1. 外键是表的一个特殊字段。被参照的表是主表，外键所在字段的表为子表。设置外键的原则需要记住，就是依赖于数据库中已存在的表的主键。外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时，子表中与之对应的信息也应有相应的改变。
2. 外键的作用保持数据的一致性和完整性
3. 可以实现一一对一或一对多的关系
4. 注意

• 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
• 数据表的存储引擎只能为InnoDB。
• 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
• 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

5. 外键约束的参照操作

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

测试外键

• 创建部门表department(主表)；id depName

CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

• 加入外键 FOREIGN KEY
  创建员工表employee(子表)；id ,username ,depId

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);

• 直接删除主表记录失败

DELETE FROM department WHERE id=1;

mysql> DELETE FROM department WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))

• 先对子表进行操作
  删除employee中的属于1部门的人

DELETE FROM employee WHERE depId=1;

接下来在删除 id=1 的部门就能成功了

DELETE FROM department WHERE id=1;

mysql> SELECT * FROM department;
+----+---------+
| id | depName |
+----+---------+
|  2 | 市场部       |
|  4 | 督导部        |
|  3 | 运营部       |
+----+---------+

• 测试插入错误数据
  depId=11不在范围内，插入失败

INSERT employee(username,depId) VALUES('test',11);
mysql> INSERT employee(username,depId) VALUES('test',11);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cms`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))

外键的添加删除

• 删除员工表

DROP TABLE employee;

• 重新创建

CREATE TABLE IF NOT EXISTS cms.employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('king',3),
('queen',2),
('张三',3),
('李四',4),
('王五',2);

• 删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;

• 添加外键
  删除id=2

DELETE FROM department WHERE id=2;

添加外键失败

ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
提示错误：
mysql> ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cms`.`#sql-f18_7`, CONSTRAINT `emp_fk_dep` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))

删除对应的depId=2，成功

mysql> DELETE FROM employee WHERE depId=2;
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);

测试 CASCADE

• 删除之前的表，重新创建employee和department

CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
#创建员工表employee(子表)；id ,username ,depId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE

#对删除和更新操作都应用CASCADE

)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);

删除部门表中的第一个部门

DELETE FROM department WHERE id=1;

结果：department和employee中对应的部分和部分中的员工都被删除了

mysql> SELECT * FROM department;
+----+---------+
| id | depName |
+----+---------+
|  2 | 市场部       |
|  4 | 督导部        |
|  3 | 运营部       |
+----+---------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
|  2 | queen    |     2 |
|  3 | 张三         |     3 |
|  4 | 李四         |     4 |
+----+----------+-------+
3 rows in set (0.00 sec)

更新id

UPDATE department SET id=id+10;

测试 SET NULL

删除之前的表，重新创建employee和department

CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL

#删除和更新操作都加入SET NULL  

)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);

删除id=1

DELETE FROM department WHERE id=1;

结果：king和王五对应的depId变为NULL
mysql> SELECT * FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
|  1 | king     |  NULL |
|  2 | queen    |     2 |
|  3 | 张三         |     3 |
|  4 | 李四         |     4 |
|  5 | 王五         |  NULL |
+----+----------+-------+
5 rows in set (0.00 sec)

联合查询

简介

• UNION
• UNION ALL
• UNION和IUNION ALL区别是UNION去掉相同记录，UION ALL是简单的合并到一起

测试 UNION

SELECT username FROM employee UNION SELECT username FROM cms_user;

employee中有5条记录，cms_user中有12条记录，但是查询结果是15条，因为有两条记录重复

测试 UNION ALL

SELECT username FROM employee UNION ALL SELECT username FROM cms_user;

因为UION ALL是简单的合并到一起，所以查询结果可以重复，共有17条

字段个数需匹配

字段个数不匹配时会报错

SELECT id,username FROM employee UNION ALL SELECT username FROM cms_user;
报错：
mysql> SELECT id,username FROM employee UNION ALL SELECT username FROM cms_user;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

只要字段个数匹配就行

SELECT id,username FROM employee UNION ALL SELECT username,age FROM cms_user;