高级操作 - 《【后端】mysql》

多表联查
外键约束的使用
特殊形式的查询

多表联查

准备：员工表和部门表
要求：查询 emp id username age 部门名称 dep depName

CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET 'UTF8';
USE test2;
CREATE TABLE emp(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
  age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
  sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
  addr VARCHAR(20) NOT NULL DEFAULT '北京',
  depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号'
)ENGINE=INNODB CHARSET=UTF8;
INSERT emp(username,age,depId) VALUES('king',24,1),
('queen',25,2),
('imooc',26,1),
('lily',27,1),
('rose',28,3),
('john',29,3);
INSERT emp(username,age,depId) VALUES('test',30,6);
CREATE TABLE dep(
  id TINYINT UNSIGNED AUTO_INCREMENT KEY,
  depName VARCHAR(50) NOT NULL UNIQUE,
  depDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB CHARSET=UTF8;
INSERT dep(depName,depDesc) VALUES('PHP教学部','研发PHP课件'),
('JAVA教学部','研发JAVA课件'),
('WEB前端教学部','研发WEB前端课件'),
('IOS教学部','研发IOS课件');

笛卡尔积

--笛卡尔积：一般不用，不符合这次的要求
SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;

内连接

--内连接：只会显示两张表的交集
SELECT e.id,e.username,e.age,d.depName
FROM emp AS e
INNER JOIN dep AS d
ON e.depId=d.id;

--INNER可以省略
SELECT e.id,e.username,e.age,e.addr,
d.id,d.depName,d.depDesc
FROM dep AS d
JOIN emp AS e
ON d.id=e.depId;

--这个的查询结果和上面是一样的
SELECT e.id,e.username,e.age,e.addr,
d.id,d.depName,d.depDesc
FROM emp AS e
JOIN dep AS d
ON d.id=e.depId;

外连接（单个）

--外连接
--OUTER可以省略

--左外连接:在这里emp是左表，以左表为主，右表没有记录的话就显示为NULL
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depId=d.id;

--右外连接:在这里dep是右表，以右表为主，左表没有记录的话就显示为NULL
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
RIGHT JOIN dep AS d
ON e.depId=d.id;

外连接（多个）

UPDATE material
left join product_model  on material.id = product_model.material_id 
left join product  on product_model.product_id = product.id 
left join mcd_material_type  on product.material_type_id = mcd_material_type.id
SET material.material_type_id = product.material_type_id, 
material.material_type_name = mcd_material_type.type_name;

外键约束的使用

只有InnoDB存储引擎才支持外键
外键的属性必须和主键完全一致
如果外键字段没有添加索引，mysql会自动帮我们添加索引
子表的外键关联的必须是父表的主键

1，建表时指定外键：不指定外键名称

--添加外键形式

--先创建主表
CREATE TABLE news_cate(
  id TINYINT UNSIGNED AUTO_INCREMENT KEY,
  cateName VARCHAR(50) NOT NULL UNIQUE,
  cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);

--再创建子表的外键约束
CREATE TABLE news(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  title VARCHAR(100) NOT NULL UNIQUE,
  content VARCHAR(1000) NOT NULL,
  cateId TINYINT UNSIGNED NOT NULL,
  FOREIGN KEY(cateId) REFERENCES news_cate(id)
);

INSERT news_cate(cateName) VALUES('国内新闻'),('国际新闻'),('娱乐新闻'),('体育新闻');

INSERT news(title,content,cateId) VALUES('a','aaaaa',1),
('b','bbbbb',2),
('c','ccccc',3),
('d','ddddd',4),
('e','eeeee',3);

--测试非法记录
INSERT news(title,content,cateId) VALUES('f','fffff',8);--会报错

--测试删除父表中的记录 和 删除父表
DELETE FROM news_cate WHERE id=1;--会报错

UPDATE news_cate SET id=10 WHERE id=1;--会报错

DROP TABLE news_cate;--会报错

--先删除子表，再删除父表是可以的
DROP TABLE news;
DROP TABLE news_cate;

2，建表时指定外键：指定外键名称

--添加外键名称
CREATE TABLE news_cate(
  id TINYINT UNSIGNED AUTO_INCREMENT KEY,
  cateName VARCHAR(50) NOT NULL UNIQUE,
  cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);

CREATE TABLE news(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  title VARCHAR(100) NOT NULL UNIQUE,
  content VARCHAR(1000) NOT NULL,
  cateId TINYINT UNSIGNED NOT NULL,
  CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
);

3，动态添加外键

--添加外键
--动态添加外键之前表中的记录一定是合法的记录，没有脏值，否则外键添加不成功
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);

ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);

4，动态删除外键

--删除外键
ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;

5，外键约束的参照操作

CASCADE：从父表删除或更新，子表也跟着删除或更新，级联的操作
SET NULL：从父表删除或更新，设置子表的外键列为NULL
NO ACTION | RESTRICT：拒绝对父表做更新或删除操作

--指定级联操作 DELETE CASCADE UPDATE CASCADE
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

特殊形式的查询

1，子查询
内层语句查询的结果可以作为外层语句查询的条件

由IN引出的子查询

SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);

SELECT * FROM emp
WHERE depId NOT IN (SELECT id FROM dep);

由比较运算符引出的子查询

SELECT id,username,score FROM stu
WHERE score>=(SELECT score FROM level WHERE id=1);

由EXISTS引发的子查询
#EXISTS后面的内层语句查询结果存在，那么就执行外层语句查询，反之则不执行

SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=1);

带有ANY SOME ALL关键字的子查询

SELECT * FROM stu
WHERE score>=ANY(SELECT score FROM level);--大于里面的最小值

SELECT * FROM stu
WHERE score>=SOME(SELECT score FROM level);--大于里面的最小值

SELECT * FROM stu
WHERE score>=ALL(SELECT score FROM level);--大于里面的最大值

CREATE … SELECT
INSERT … SELECT

--创建一个user1表，id username
--复制emp表中的id,username信息到user1表中
CREATE TABLE user1(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20)
)SELECT id,username FROM emp;

--将user表中的用户写入到user中
INSERT user1(username) SELECT username FROM user;

--将stu表中的tiancai用户名添加到user2表中
INSERT user2 SET username=(SELECT username FROM stu WHERE id=9);

2，联合查询

--将user1和user2数据合并到一起
--UNION 会去掉重复值
SELECT * FROM user1
UNION
SELECT * FROM user2;

--UNION ALL 简单合并，不会去掉重复值
SELECT * FROM user1
UNION ALL
SELECT * FROM user2;

3，自身连接查询
#无限级分类的实现形式

CREATE TABLE cate2(
  id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
  cateName VARCHAR(100) NOT NULL UNIQUE,
  pID SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT cate2(cateName,pId) VALUES('服装',0),('数码',0),('玩具',0);
INSERT cate2(cateName,pId) VALUES('男装',1),('女装',1),('内衣',1);
INSERT cate2(cateName,pId) VALUES('电视',2),('冰箱',2),('洗衣机',2);
INSERT cate2(cateName,pId) VALUES('爱马仕',3),('LV',3),('GUCCI',3);
INSERT cate2(cateName,pId) VALUES('夹克',4),('衬衫',4),('裤子',4);
INSERT cate2(cateName,pId) VALUES('液晶电视',7),('等离子电视',7),('背投电视',7);


--查询所有分类信息，得到其父分类
SELECT s.id,s.cateName AS sCateName,p.cateName AS pCateName
FROM cate2 AS s
LEFT JOIN cate2 AS p
ON s.pId=p.id;


--查询所有的分类及其子分类
SELECT p.id,p.cateName AS pCateName,s.cateName AS sCateName
FROM cate2 AS s
RIGHT JOIN cate2 AS p
ON s.pId=p.id;

--查询所有的分类并且得到子分类的数目
SELECT p.id,p.cateName AS pCateName,COUNT(s.cateName) AS count
FROM cate2 AS s
RIGHT JOIN cate2 AS p
ON s.pId=p.id
GROUP BY p.cateName
ORDER BY id;