/*
多表查询
f5刷新；f9执行选中的语句
*/

-- 部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);

-- 员工表
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)
);

INSERT INTO dept( NAME ) VALUE( '划水部' );
INSERT INTO dept( NAME ) VALUE( '打架部' );
INSERT INTO dept( NAME ) VALUE( '嘴炮部' );


-- 插入,dept_id不能插
-- insert into emp( name , gender , salary , join_date , dept_id) values("孙悟空",'男',7200,'2013-02-24',1);
INSERT INTO emp( NAME , gender , salary , join_date ) VALUES("孙悟空",'男',7200,'2013-02-24');
INSERT INTO emp(NAME,gender,salary,join_date) VALUES('猪八戒','男',3600,'2010-12-02');
INSERT INTO emp(NAME,gender,salary,join_date) VALUES('唐僧','男',9000,'2008-08-08');
INSERT INTO emp(NAME,gender,salary,join_date) VALUES('白骨精','女',5000,'2015-10-07');
INSERT INTO emp(NAME,gender,salary,join_date) VALUES('蜘蛛精','女',4500,'2011-03-14');
-- 在emp的中插入数据后，emp表的外检就可以插入了
INSERT INTO emp(NAME,gender,salary,join_date , dept_id) VALUES('牛魔王','男',8000,'2011-05-23' , 1);


-- 1. 隐式内连接
-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp e, dept d
WHERE e.dept_id = d.id;

-- 查询员工表的名称，性别。部门表的名称
SELECT e.name , e.gender , d.name
FROM emp e , dept d
WHERE e.dept_id = d.`id`

-- 2. 显示内连接
SELECT * 
FROM emp e INNER JOIN dept d ON e.dept_id = d.`id`;

SELECT * FROM emp e JOIN dept d ON e.dept_id = d.id; 

-- 3.左外链接：查询的是左表以及交集的数据
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT e.* , d.name 
FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;

-- 4. 右外连接：查询的是右表所有数据以及其交集部分。
SELECT * 
FROM dept d RIGHT JOIN emp e ON e.dept_id = d.id;

-- 5. 子查询:查询工资最高员工信息
SELECT * 
FROM emp e WHERE e.salary = (
	SELECT MAX(salary)
	FROM emp
	);
-- 查询员工工资小于平均工资的人
SELECT *
FROM emp e
WHERE e.salary < (
	SELECT AVG(salary)
	FROM emp
	);
	
-- 查询'划水部'和'嘴炮部'所有的员工信息
SELECT * 
FROM emp e 
WHERE e.dept_id IN (
	SELECT id 
	FROM dept 
	WHERE NAME = '划水部' OR NAME = '嘴炮部'
	);

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT *
FROM dept t1 , ( SELECT * FROM emp WHERE emp.join_date > '2011-11-11' ) t2
WHERE t1.id = t2.dept_id;
-- 内连接
SELECT * 
FROM emp e , dept d
WHERE e.dept_id = d.id AND e.join_date > '2011-11-11';