Mysql8

35、外连接查询
左外连接：LEFT [OUTER] JOIN - 显示左表的全部记录及右表符合连接条件的记录
右外连接：RITHT [OUTER] JOIN - 显示右表的全部记录及左表符合连接条件的记录
–插入错误的数据
INSERT cms_user(username,password,regTime,proId)
VALUES(‘TEST2’,‘TEST2’,988776668,20); —没有该省份

– 左外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p —provinces主表
LEFT JOIN cms_user AS u
ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
RIGHT JOIN cms_user AS u
ON u.proId=p.id;
内连接用的更多

36、外键操作
外键是表的一个特殊字段。被参照的表是主表，外键所在字段的表是子表。可以实现一对一或一对多的关系。
设置外键的原则：依赖数据库中已存在的表的主键。
作用：建立该表与其父表的关联关系。保持数据的一致性和完整性
注意：数据表的存储引擎只能为InnoDB
外键列和参照列必须具有相似的数据类型，数字长度或是否有符号位必须相同；而字符长度可不同
外键列和参照列必须创建索引。如果外键列不存在索引的话，M有SQL将自动创建索引
外键约束的参照操作：
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(‘教学部’),
(‘市场部’),
(‘运营部’),
(‘督导部’);

–创建员工表employee（子表）
–I’d,username,depId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
)ENIGIN=INNODB;
INSERT employee(username,depId) VALUES(‘king’,1),
(‘queen’’,2),
(‘章三’,3),
(‘李四’,4),
(‘王武’,1);

SELECT e.id,e.username,d.depName FROM
employee AS e
JOIN
department AS d
ON e.depId=d.id;

–删除督导部
DELECT FROM department WHERE depName=‘督导部’; —部门删除 但里面的employee还有

DELECT FROM employee WHERE depId; —先要将employee分配好，再删除部门
DELETE FROM department WHERE id=1;

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)
)ENIGIN=INNODB;
INSERT employee(username,depId) VALUES(‘king’,1),
(‘queen’’,2),
(‘章三’,3),
(‘李四’,4),
(‘王武’,1);

– 删除主表中的记录
DELECT FROM department WHERE id=1;

–删除员工表employee中的属于1部门的人
DELECT FROM employee WHERE depId=1;
DELECT FROM department WHERE id=1;