MYSQL（多表查询） - G-holic/Java-Web GitHub Wiki

多表查询

* 查询语法：
	select
		列名列表
	from
		表名列表
	where...
* 准备sql
	# 创建部门表
	create table dept(
		id int primary key auto_increment,
		name varchar(20)		
	);
	insert into dept (name) values ('开发部'),('市场部'),('财务部');
	# 创建员工表
	create table emp(
		id int primary key not null auto_increment,
		name varchar(10),
		gender char(1),--性别
		salary double,--工资
		join_date date,--入职日期
		dept_id int,
		foreign key (dept_id) references dept(id)--+外键，关联部门表(部门表的主键)
	);
	INSERT INTO much_table.emp VALUES(1, '孙悟空', '男', 7200.0, '2013-02-24', 1);
	INSERT INTO much_table.emp VALUES(2, '猪八戒', '男', 3600.0, '2010-12-02', 2);
	INSERT INTO much_table.emp VALUES(3, '唐僧', '男', 9000.0, '2008-08-08', 2);
	INSERT INTO much_table.emp VALUES(4, '白骨精', '女', 5000.0, '2015-10-07', 3);
	INSERT INTO much_table.emp VALUES(5, '蜘蛛精', '女', 4500.0, '2011-03-14', 1);
	
* 笛卡尔积：
	* 有两个集合A，B，取这两个集合的所有组成情况。
	* 要完成多表查询，需要先消除无用的数据
* 多表查询的分类：
	1.内连接查询：
		1.隐式内连接：使用where条件消除无用的数据
			*例子：
			-- 查询所有员工信息和对应的部门信息

			select * from emp,dept where emp.dept_id = dept.id ;

			-- 查询员工表的名称，性别。部门表的名称
			select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id = dept.id ;
			select  
				e.name,
				e.gender,
				d.name 
			from 
				emp e ,
				dept d 
			where 
				e.dept_id =d.id ;
		2.显示内连接：
			* 语法：select 字段列表 from 表名1 inner(可省略) join 表名2 on 条件
			* 例如：
				* select * from emp inner join dept on emp.dept_id = dept.id ;
				* select * from emp  join dept on emp.dept_id = dept.id ;
		3.内连接查询：
			1.从哪些表中查询数据?
			2.条件是什么?
			3.查询哪些字段?
	2.外连接查询：
			* 例如：
			-- 查询所有员工信息，如果员工有部门则查询部门名称，如果没有部门，则不显是部门名称
				* select e.*,d.name from emp e left join dept d on e.dept_id = d.id ;
				* select e.*,d.name from dept d right join emp e on e.dept_id = d.id ;
				
		1.左外连接：
			* 语法：select 字段列表 from 表名1 left outer（可省略）join 表名2 on 条件;
			* 查询的是左表所有数据以及其交集部分。
		2.右外连接：
			* 语法：select 字段列表 from 表名1 right outer（可省略）join 表名2 on 条件;
			* 查询的是右表所有数据以及其交集部分。
	3.子查询：
		* 概念：查询中嵌套查询，称嵌套查询为子查询。
			-- 查询工资最高的员工信息
			-- 1.查询最高的工资是多少
			select max(salary) from emp ;
			-- 2.查询员工信息，并且工资等于9000的
			select * from emp where emp.salary = 9000;

			-- 一条sql就完成这个操作。（子查询）
			select * from emp where emp.salary = (select max(salary) from emp);
		
		* 子查询不同情况：
			1.子查询的结果是单行单列的：
				* 子查询可以作为条件，使用运算符去判断。
				* 例如：
				-- 查询员工工资小于平均工资的人
				select * from emp where emp.salary < (select avg(salary) from emp );
			2.子查询的结果是多行单列的：
				* 子查询可以作为条件，使用运算符in来判断。
				* 例如：
				-- 查询'财务部'和'市场部'所有的员工信息
				select id from dept where name = '财务部' or name = '市场部';
				select * from emp where dept_id = 3 or dept_id =2;
				-- 子查询
				select * from  emp where dept_id in (select id from dept where name = '财务部' or name = '市场部');
			3.子查询的结果是多行多列的：
				* 子查询可以作为一张虚拟表参与查询
				-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
				select * from dept d ,(select * from emp e where e.join_date > '2011-11-11') e where d.id = e.dept_id;
				-- 普通内连接
				select * from emp e ,dept d where e.dept_id = d.id and e.join_date > '2011-11-11';

1. 1. 1.