知识点七：连接查询(37-41)
什么是连接查询：
连接查询是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据。连接查询是同时查询两个或两个以上的表时使用的。当不同的表中存在相同意义的字段时，可以通过该字段连接这几个表。
-- 查询cms_user id,username
-- provinces,proName
SELECT cms_user.id,username,proName FROM cms_user,provinces; --笛卡儿积 -- cms_user的proId对应省份表中的id
SELECT cms_user.id,username,proName FROM cms_user,provinces
WHERE cms_user.proId=provinces.id;
连接查询测试
内连接查询：
CROSS JOIN | INNER JOIN | JOIN
通过ON 连接查询
显示两个表中符合连接条件的记录
-- 查询cms_user表中id,username,email,sex
-- 查询provinces表proName
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proId=p.id; SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
CROSS 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
JOIN cms_user AS u
ON u.proId=p.id; -- 查询cms_user id,username,sex
-- 查询provinces proName
-- 条件是cms_user的性别为男的用户
SELECT u.id,u.username,u.sex,p.proName
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'; -- 根据proName分组
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName; -- 对分组结果进行筛选,选出组中人数>=1的
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1; -- 按照id升序排列
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC; -- 限制显示条数 前2条
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC
LIMIT 0,2; -- 查询cms_news中的id,title,
-- 查询cms_cate 中的cateName
SELECT n.id,n.title,c.cateName FROM
cms_news AS n
JOIN
cms_cate AS c
ON n.cId=c.id; -- cms_news id,title
-- cms_admin username,role
SELECT n.id,n.title,a.username,a.role
FROM
cms_news AS n
JOIN
cms_admin AS a
ON n.aId=a.id; -- cms_news id ,title
-- cms_cate cateName
-- cms_admin username,role
SELECT n.id,n.title,c.cateName,a.username,a.role
FROM cms_cate AS c
JOIN
cms_news AS n
ON n.cId=c.id
JOIN
cms_admin AS a
ON n.aId=a.id;
内连接查询测试
外连接查询：
左外连接：LEFT [OUTER] JOIN
显示左表的全部记录及右表符合连接条件的记录
右外连接：RIGHT [OUTER] JOIN
显示右表的全部记录以及左表符合连接条件的记录
-- 插入错误的数据
INSERT cms_user(username,password,regTime,proId)
VALUES('TEST2','TEST2','',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
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; 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;
外连接查询测试
外键：
外键是表的一个特殊字段，被参照的是主表，外键所在字段的表为子表。设置外键的原则需要记住，就是依赖于数据库中已存在的表的主键。外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时，子表中与之对应的信息也应右相应的改变。
外键的作用时保持数据的一致性和完整性，可以实现一对一或一对多的关系。
注意：
父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
数据表的存储引擎只能是InnoDB
外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
外键列和参照列必须创建索引。如果外键列不存在索引的话 ，MySQL将自动创建索引。
外键约束的参照操作：
CASCADE：从父表删除或者更新且自动删除或更新子表中匹配的行。
--删除部门表和员工表重新创建
----------------
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB; INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部'); --（当父表进行删除或更新时子表自动删除或者更新） CASCADE
-- 创建员工表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
)ENGINE=INNODB; INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1); -- 删除部门表中的第一个部门
DELETE FROM department WHERE id=1; UPDATE department SET id=id+10;
CASCADE测试
SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定的NOT NULL。
--SET NULL
DROP TABLE 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 SET NULL ON UPDATE SET NULL
)ENGINE=INNODB; INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);
--删除部门一，查询员工表内容
DELETE FROM department WHERE id=1;
UPDATE department SET id=id+10;
SET NULL测试
RESTRICT：拒绝对父表的删除或更新操作。
NO ACTION：标准MSQL的关键字，在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(子表)
-- id ,username ,depId
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('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1); --查询员工表的id，username和所在部门名
SELECT e.id,e.username,d.depName FROM
employee AS e
JOIN
department AS d
ON e.depId=d.id; -- 删除督导部
DELETE FROM department WHERE depName='督导部'; --删除部门表和员工表
DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS employee; --没有外键时，各个表中的数据没有关联，当两个表有关联时，删除其中一个表中的数据时，对另一个表没产生影响。
没有外键测试
-- 创建部门表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(子表)（有外键）
-- 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); -- 删除主表中的记录（会报错，得先删除employee表中的depTd为1数据的删除）
DELETE FROM department WHERE id=1; -- 删除employee中的属于1部门的人，再删除主表中id为1的数据
DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id=1; --在子表中添加数据时，当与主表中关联的数据在主表中查不到时也会报错
INSERT employee(username,depId) VALUES('test',11);
有外键测试
-- 删除员工表
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)
)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; -- 添加外键(不成功以为刚才删除了部门表id为2的部门但员工表还有索引指向2部门）
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id); --删除员工表depId为2的员工才能添加上
DELETE FROM employee WHERE depId=2;
--再添加就成功了
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
删除外键测试
联合查询：
UNION：
UNION ALL：
UNION和UNION ALL区别是UNION去掉相同记录，UNION ALL是简单的合并到一起。
-- 联合查询（两个表查询数量必须一样）
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,age FROM cms_user;
联合查询测试