Mysql学习之多表查询

注意：

书写sql语句时需要注意使用SQL92还是SQL99的语法，一般情况下MySQL使用SQL99语法，下列写法中不再赘述。

一、多表查询的分类

1、等值连接 VS 非等值连接

非等值连接：只要不是==，其余条件都是非等值连接

2、自连接 VS 非自连接

3、内连接 VS 外连接

（1）内连接（INNER JOIN）

合并具有同一列的两个以上的表的行，结果集中不包含
一个表与另一个表不匹配的行。（满足两个表的关联条件才会被展示），inner可省略

（2）外连接(OUTER JOIN)

合并具有同一列的两个以上的表的行，结果集中除了包含
一个表与另一个表不匹配的行，还查询到了左表或右表中不匹配的行。
outer可省略
外连接的分类：左外连接，右外连接，满外连接。

左外连接(LEFT OUTER JOIN)
两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。

SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

右外连接(RIGHT OUTER JOIN)
两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

满外连接(FULL OUTER JOIN)

SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

MYSQL不支持FULL OUTER JOIN，使用union，示例见下

4、 UNION和UNION ALL的使用

union会执行去重操作，union all不会执行去重操作。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

使用UNION关键字注意事项：

• UNION 和 UNION ALL 内部的 SELECT 语句必须拥有相同数量的列
• 每条 SELECT 语句中列的顺序必须相同（假如不相同会以union all 前面的表字段顺序为准。union all 后面的表的数据会按照顺序依次附在后面。注意：按照字段顺序匹配，而不是按照字段名称匹配。）错误示例见练习题7的错误示例。

5、七种SQL_JOINS的实现

中图：内连接

SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

左上图： 左外连接

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

右上图：右外连接

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

左中图：

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

右中图：

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

左下图：满外连接
方式1：左上图 UNION ALL 右中图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

方式2：左中图 UNION ALL 右上图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

右下图：左中图 UNION ALL 右中图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

拓展：

SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

二、练习

建表语句：

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null, PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

测试数据：

INSERT INTO t_dept(deptName,address) VALUES('华山','华山'); 
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳'); 
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山'); 
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山'); 
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶'); 
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001); 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002); 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003); 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004); 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(' 乔 峰 ',35,2,100005); 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006); 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007); 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008); 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009); 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);

【题目】

#所有有门派的人员信息
SELECT e.id,e.NAME,e.age,d.deptName,d.address from t_emp e join t_dept d on e.deptId = d.id;

#列出所有用户，并显示其机构信息
SELECT e.id,e.NAME,e.age,d.deptName,d.address from t_emp e LEFT JOIN t_dept d on e.deptId = d.id;

#列出所有门派
SELECT * from t_dept;

#所有不入门派的人员
SELECT e.name from t_emp e left JOIN t_dept d on e.deptId = d.id where d.id is null;

#所有没人入的门派
SELECT d.deptName from t_dept d LEFT JOIN t_emp e on e.deptId = d.id where e.deptId is null;
或者
SELECT d.deptName from t_emp e RIGHT JOIN t_dept d on e.deptId = d.id where e.deptId is null;

#列出所有人员和机构的对照关系
SELECT * from t_emp e left JOIN t_dept d on d.id = e.deptId WHERE e.deptId is null 
UNION ALL SELECT * from t_emp e RIGHT JOIN t_dept d on e.deptId = d.id;
或者
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id;

#列出所有没入派的人员和没人入的门派
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL 
UNION all SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;
错误写法:
SELECT * from t_emp e LEFT JOIN t_dept d on e.deptId = d.id WHERE e.deptId is null 
union all SELECT * from t_dept d LEFT JOIN t_emp e on e.deptId = d.id WHERE e.deptId is null;