mysql学习笔记四多表查询，内连接，外连接，子查询

多表查询：查询的结果在多张不同的表里面，每张表取一列或多列。

-- 员工信息表
CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `join_date` date DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- dept部门表
CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

笛卡尔积：查询的结果是两张表的数据乘积。

-- 笛卡尔积
SELECT * from emp,dept;

消除笛卡尔积，设置过滤条件

-- 设置where条件
SELECT * from emp,dept where dept.id=emp.dept_id; 

内连接

• 隐式内连接：看不到Join关键字，使用where条件指定
  语法：SELECT 字段名 FROM 左表, 右表 WHERE 条件

SELECT * from emp,dept WHERE emp.dept_id=dept.id;-- 隐式内连接

• 显示内连接：使用 INNER JOIN … ON 语句, 可以省略 INNER
  语法：SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

SELECT * from emp e INNER JOIN dept d ON e.dept_id=d.id; -- 显式内连接
SELECT * from emp e INNER JOIN dept d ON e.dept_id=d.id where e.name='项羽';

外连接

• 左外连接：使用 LEFT OUTER JOIN … ON，OUTER 可以省略。会把左表的数据全查出来，右表符合条件显示，否则显示null。
  语法：SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

SELECT e.*,d.name from emp e LEFT JOIN dept d ON e.dept_id=d.id;-- 左外连接

• 右外连接：使用 RIGHT OUTER JOIN … ON，OUTER 可以省略。跟左外连接相反。
  语法：SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

SELECT
	e.id,
	e. NAME,
	e.gender,
	d.*
FROM
	emp e
RIGHT JOIN dept d ON e.dept_id = d.id;-- 右外连接

子查询

一个查询结果作为另一个查询的条件
子查询的三种情况：

• 子查询结果是单行单列
  语法：SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

-- 查询员工最高工资
SELECT max(salary) from emp;
-- 查询员工工资等于九千的
SELECT * from emp where salary=(SELECT max(salary) from emp);
-- 查询员工工资小于九千的
SELECT * from emp where salary<(SELECT max(salary) from emp);

• 子查询是多行单列
  语法：SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）

-- 查询员工工资大于5000的
SELECT name,dept_id FROM emp WHERE salary>5000;
-- 查询工资大于5000的员工所在部门名称
SELECT * from dept where id in(SELECT dept_id FROM emp WHERE salary>5000);

-- 查询开发部和市场部的id
SELECT id,name from dept WHERE name in('开发部','市场部');
-- 再查询这些部门下的员工
SELECT * from emp WHERE dept_id in(SELECT id from dept WHERE name in('开发部','市场部'));

• 子查询结果是多行多列
  语法：SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

-- 查询2011年以后入职得员工
select * from emp where join_date >='2011-1-1';
-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where
d.`id`= e.dept_id ;

子查询小结：

• 子查询结果只要是单列，则在 WHERE 后面作为条件
• 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询