mysql多表查询（内连接，外连接，子查询）

-- 1.多表查询
-- 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept(NAME) VALUES ('表演部'),('策划部'),('鸡脚部');
-- 创建员工表
CREATE TABLE emp01(
	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 emp01(NAME,gender,salary,join_date,dept_id) VALUES ('蔡徐坤','男',10000,'2020-10-3',3);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('蔡旭','男',8000,'2002-1-1',2);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('坤哥','男',6000,'2010-1-30',2);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('鸡坤','男',12000,'2016-4-3',2);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('鸡哥','男',8000,'2010-12-3',2);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('只因哥','男',9000,'2008-5-3',1);
INSERT INTO emp01(NAME,gender,salary,join_date,dept_id) VALUES ('太美哥','男',22000,'2022-4-3',1);
--  查询
SELECT *FROM emp01;
SELECT *FROM dept;
-- 如果这样查询,出现的是笛卡尔积形式
SELECT *FROM emp01,dept;
--  多表查询消除无用数据
--  1.内连接查询
--     1).隐式内连接:使用where消除无用句子；
	SELECT *FROM emp01,dept WHERE emp01.dept_id = dept.id;
	-- 查询员工表的名称，性别，部门表的名称
	SELECT emp01.name,emp01.gender,dept.name FROM emp01,dept WHERE emp01.dept_id = dept.id;
	-- 简化一下
	SELECT t1.`name`,t1.`gender`,t2.name FROM emp01 t1 ,dept t2 WHERE t1.`dept_id` = t2.id;
	--     2).显示内连接
SELECT *FROM emp01 JOIN dept ON emp01.dept_id = dept_id;
--  2.外连接查询
	--    1).左外连接(查询左表所有数据以及交集部分)
	--         select 字段列表 from 表1 left join 表2 on 条件;
	SELECT t1.*,t2.name FROM emp01 t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.id;
	--    2).右外连接（右表所有记录以及交集部分）
	--         select 字段列表 from 表1 right join 表2 on 条件;
	SELECT t1.*,t2.name FROM emp01 t1 RIGHT JOIN dept t2 ON t1.`dept_id` = t2.id;

--  3.子查询（查询中嵌套查询，嵌套的查询为子查询）
	-- 查询最高的工资员工信息(先查询最高工资多少在查询信息)
	SELECT MAX(salary) FROM emp01;
	SELECT  * FROM emp01 WHERE emp01.salary = 22000;
	-- 简化
	SELECT  * FROM emp01 WHERE emp01.salary = (SELECT MAX(salary) FROM emp01);
	-- 子查询作为条件去判断
	SELECT * FROM emp01 WHERE emp01.`salary` < (SELECT AVG(salary) FROM emp01);
	-- 查询表演部的所有员工信息
	SELECT id FROM dept WHERE NAME = '表演部' OR NAME = '鸡脚部';
	SELECT * FROM emp01 WHERE dept_id = 3 OR dept_id= 2 ;
	-- 简化
	 SELECT *FROM emp01 WHERE dept_id IN (3,2);
	 --  查询入职日期是2011-11-11之后的员工和部门信息
	 SELECT *FROM emp01 WHERE emp01.join_date > '2011-11-11';
	 -- 子查询（多行多列）作为虚拟表进行查询
	 SELECT *FROM dept t1 ,(SELECT *FROM emp01 WHERE emp01.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
)