mysql数据库(三) --解决多表查询的方法

本章讲述的如何去解决多表查询问题。
当我们进行多表查询时。简单粗暴得到将多张表直接交叉相乘的进行连接就会出现大量的数据错误，即出现笛卡尔积现象。

首先我们在数据库中添加员工信息表和部门表做案例，需要的数据可以自行添加：

-- 员工信息表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
); 
 -- 员工部门表
 CREATE TABLE dept(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

下面讲述如何去解决多表查询的问题
1、内连接：
(1) 隐式内连接

-- select 列名 from 左表,右表 where 从表.外键=主表.主键
SELECT * FROM emp , dept WHERE emp.`dept_id` = dept.`id`  

(2) 显示内连接

-- select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
-- inner永远可以省略不写 
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
-- 别名配置是多表查询常用形式
SELECT * FROM emp e JOIN dept d ON e.dept_id = d.id 

总结：
内连接的2种方式都可以解决笛卡尔积现象，得到正确的结果，
2种方式只是写法不同，功能效果一模一样。
2、外连接：
(1)左外连接（左连接）

-- 作用：在内连接的基础之上，保全左表的全部数据，右边没有的数据用NULL替代。
-- 格式：select 列名 from 左表 left join 右表 on 从表.外键=主表.主键
SELECT * FROM emp e LEFT JOIN dept d ON e.`dept_id` = d.`id`

(2)右外连接（右连接）

-- 作用：在内连接的基础之上，保全右表的全部数据，左边没有的数据用NULL替代。
-- 格式：select 列名 from 左表 right join 右表 on 从表.外键=主表.主键
SELECT * FROM emp e  RIGHT JOIN dept d ON e.`dept_id` = d.`id`

3、子查询：

-- a.子查询是单行单列的情况 原理：实际上就是子查询出来的结果是一个值,外部sql可以使用：= > >= < <= != ...

-- 查询工资大于5000的员工的部门id
SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000)

-- b.子查询: 多行单列的情况 原理：实际上就是子查询的结果是多个值，可以当数组使用  外部查询可以使用 IN / ANY / ALL
SELECT * FROM emp WHERE salary > (SELECT MAX(salary) FROM emp WHERE dept_id = 1) 

 -- c.多行多列的情况原理：子查询出来的结果实际上就是一个新表，可以继续当虚拟表使用
SELECT e.*  , d.name 部门名称 FROM (SELECT * FROM emp WHERE join_date >= '2011-01-01') e
LEFT JOIN dept d ON e.dept_id = d.id;

有不足之处请各位看官多多指出！！