Mysql-多表查询-入门教学

多表查询

- 隐式内连接和显式内连接

- 外连接

- 案例

CREATE TABLE dept ( 
	d_id INT PRIMARY KEY auto_increment, 
	d_name VARCHAR ( 20 ) 
);

CREATE TABLE emp (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR ( 20 ),
	gender CHAR ( 1 ),
	salary DOUBLE,
	join_date DATE,
	dept_id INT,
	CONSTRAINT FOREIGN KEY ( dept_id ) REFERENCES dept ( d_id ) 
);

INSERT INTO dept ( d_name )
VALUES
	( '研发部' ),
	( '市场部' ),
	( '财务部' ),
	( '销售部' );
	
INSERT INTO emp ( NAME, gender, salary, join_date, dept_id )
VALUES
	( '孙悟空', '男', 7200, '2013-02-24', 1 ),
	( '猪八戒', '男', 3600, '2010-12-02', 2 ),
	( '唐僧', '男', 9000, '2008-08-08', 2 ),
	( '白骨精', '女', 5000, '2015-10-07', 3 ),
	( '蜘蛛精', '女', 4500, '2011-03-14', 1 ),
	( '小白龙', '男', 2500, '2011-02-14', NULL );
	
SELECT
	* 
FROM
	dept;
	
SELECT
	* 
FROM
	emp;
	
-- 多表查询
-- 隐式内连接
SELECT
	b.id,
	b.NAME,
	b.gender,
	b.join_date,
	b.salary,
	a.d_name 
FROM
	dept a,
	emp b 
WHERE
	a.d_id = b.dept_id;
	
-- 显式内连接
SELECT
	* 
FROM
	emp a
	INNER JOIN dept b ON a.dept_id = b.d_id;
SELECT
	* 
FROM
	emp a
	JOIN dept b ON a.dept_id = b.d_id;
	
-- 左外连接
-- 查询emp表所有数据和对应的部门信息
SELECT
	* 
FROM
	emp a
	LEFT JOIN dept b ON a.dept_id = b.d_id;
	
-- 右外连接
-- 查询dept表所有数据和对应的员工信息
SELECT
	* 
FROM
	emp a
	RIGHT JOIN dept b ON a.dept_id = b.d_id;
	
-- 查询工资高于猪八戒的员工信息
-- 1、猪八戒的工资
SELECT
	salary 
FROM
	emp 
WHERE
	NAME = '猪八戒';
	
-- 2、比猪八戒工资高的员工信息
SELECT
	* 
FROM
	emp 
WHERE
	salary > ( SELECT salary FROM emp WHERE NAME = '猪八戒' );
	
-- 查询 '财务部' 和 '市场部' 所有的员工信息
-- 1、先查询 '财务部' 和 '市场部' 的部门id
SELECT
	d_id 
FROM
	dept 
WHERE
	d_name = '财务部' 
	OR d_name = '市场部';
	
-- 2、查询满足条件的员工信息
SELECT
	* 
FROM
	emp 
WHERE
	dept_id IN ( SELECT d_id FROM dept WHERE d_name = '财务部' OR d_name = '市场部' );
	
-- 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息
-- 1、查询入职日期是 '2011-11-11' 之后的员工信息
SELECT
	* 
FROM
	emp 
WHERE
	join_date > '2011-11-11';
	
-- 2、查询这些员工的部门信息
SELECT
	a.id,
	a.NAME,
	a.gender,
	a.salary,
	a.join_date,
	b.d_name 
FROM
	( SELECT * FROM emp WHERE join_date > '2011-11-11' ) a
	JOIN dept b ON a.dept_id = b.d_id;