MYSQL初级学习笔记五：连接查询！（视频序号：初级_37-41)

知识点七：连接查询(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','1381203974',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;

　　　　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;

　　　　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;