mysql9 添加删除外键操作+联合查询+子查询

37、添加删除外键操作
–删除员工表
DROP TABLE employee;

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

–删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;

–添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id); —不成功 上面已删除dep2
DELECT FROM employee WHERE depId=2；
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);

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

–删除部门表中的第一个部门
DELERE FROM department WHERE id=1;
UPDATE department SET id=id+10; —不成功
DELETE FROM employee;
UPDATE department SET id=id+10; —ok
外键约束的参照操作：
CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。
RESTRICT：拒绝对父表的删除或更新操作。
NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。
DROP TABLE employee,department;
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
)ENIGIN=INNODB;
INSERT employee(username,depId) VALUES(‘king’,1),
(‘queen’’,2),
(‘章三’,3),
(‘李四’,4),
(‘王武’,2);
SHOW CREATE TABLE employee;
DELECT FROM department WHERE id=1;
SELECT * FROM department;
SELECT * FROM employee;
UPDATE department SET id=id+10;
SELECT * FROM department;
SELECT * FROM employee;

---

DROP TABLE employee,department;
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
)ENIGIN=INNODB;
INSERT employee(username,depId) VALUES(‘king’,1),
(‘queen’’,2),
(‘章三’,3),
(‘李四’,4),
(‘王武’,2);
SHOW CREATE TABLE employee;
DELECT FROM department WHERE id=1;
SELECT * FROM employee;
UPDATE department SET id=id+10;
SELECT * FROM employee;

38、联合查询
UNION
UNION ALL
UNOIN和UNION ALL区别是UNION去掉相同记录，UNION ALL是简单的合并到一起。
SELECT username FROM employee;
SELECT username FROM cms_user;

–联合查询
SELECT username FROM employee UNION SELECT username FROM cms_user;
SELECT username FROM employee UNION ALL SELECT username FROM cms_user;
SELECT id,username FROM employee UNION ALL SELECT username FROM cms_user; —报错
SELECT id,username FROM employee UNION ALL SELECT username,age FROM cms_user; —ok

39、子查询
概念：将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以作为外层查询语句提供条件。
引发子查询的情况：
● 使用[NOT]IN 的子查询
DESC department;
SELECT * FROM department;
SELECT * FROM employee;
DROP TABLE employee,department;
–创建部门表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 * FROM department;
SELECT * FROM employee;
SELECT id,username FROM employee WHERE depId IN (1,2,3,4);

–由[NOT] IN引发的子查询
SELECT id FROM department;
SELECT id,username FROM employee WHERE depId IN(1,2,3,4);
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM depatment);
SELECT id,username FROM employee WHERE depId NOT IN(SELECT id FROM depatment);
INSERT employee(username,depId) VALUES(‘testtest’,8);
● 使用比较运算符的子查询：=、>、<、>=、<=、<>、！=、<=>
–创建学院表和奖学金表
–id,username , score
CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED
);
INSERT student(username,score) VALUES(‘king’,95),
(‘king’,35),
(‘king’,45),
(‘king’,55),
(‘king’,65),
(‘king’,75),
(‘king’,80),
(‘king’,90),
(‘king’,25);
–创建奖学金表scholarship
–id,level
CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
level TINYINY UNSIGNED
);
INSERT scholarship(Level) VALUES(90),(80),(70);
SELECT * FROM student;
SELECT * FROM scholarship;

–查询获得一等奖学金的学员有
SELECT level FROM scholarship WHERE id=1;
SELECT id,username FROM student WHERE score>=90;
SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1); —先查询括号中的内容
● 使用［NOT］EXISTS 的子查询 —相当于判断
SELECT * FROM department;
SELECT * FROM department WHERE id=5;

–查询部门表中
SELECT * FROM department WHERE id=5; —没有此部门
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5); --exists为真，才能到外层语句
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=4); —4存在，外层语句都被执行
SELECT id,username FROM employee WHERE NOT EXISTS(SELECT * FROM department WHERE id=41); —内层假，not exists假，假假得真，外层执行
● 使用ANY｜SOME 或ALL 的子查询