MySQL笔记——多表查询

多表查询不能使用 SELECT * from emp, dept; 会产生笛卡尔积。
笛卡尔积，有A，B两个集合，A中有5条信息，B中有4条信息，那么查询结果就是5*4=20条

一、内连接查询

		-- 隐式内连接
		SELECT 字段列表 FROM 表1,表2,… WHERE 条件;
		例：select emp.name,emp.age,dept.dep_name from emp,dept where emp.dep_id = dept.id;
			可以给表起别名将上面的语句转换成下面这种。
			select t1.name,t1.age,t2.dep_name from emp t1,dept t2 where t1.dep_id = t2.id;

		-- 显示内连接
		SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
		如：select emp.name,dept.dep_name from emp inner join dept on emp.dep_id = dept.id;
		可以给表起别名将上面的语句转换成下面这种。
		select t1.name,t2.dep_name from emp t1 inner join dept t2 on t1.dep_id = t2.id;

二、外连接查询

		（1）左外连接：相当于查询A表所有数据和交集部分数据
			左外连接：
			SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
		
		select * from emp left join dept on emp.dep_id = dept.id;

		（2）右外连接：相当于查询B表所有数据和交集部分数据
			右外连接：
				SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
				
		select * from emp right join dept on emp.dep_id = dept.id;

三、子查询

		（1）子查询概念
			查询中嵌套查询，称嵌套查询为子查询；
			
		（2）子查询根据查询结果不同，作用不同：
			①单行单列：作为条件值，使用 '='  '!='  '>'  '<'等进行条件判断
				SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
				
				查询年龄大于周八的
				select * from emp where age>(select age from emp where name = '周八');

			②多行单列：作为条件值，使用in关键字进行条件判断
				SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
				
				查询研发和销售的所有员工信息
				select * from emp where dep_id in (select id from dept where dep_name = '研发部' or dep_name = '销售部' );

			③多行多列：作为虚拟表
				SELECT 字段列表 FROM (子查询) WHERE 条件;
				
				-- 查询入职日期是‘1998-08-01’之后的员工信息和部门信息
				把（SELECT * from emp WHERE join_date>'1998-08-01'）这个多行多列表作为虚拟表查询放到from后面
				SELECT * FROM (SELECT * from emp WHERE join_date>'1998-08-01') t1,dept WHERE t1.dep_id = dept.id;

四、练习

新建员工表，部门表，职务表，工资等级表
1.查询所有员工信息。查询员工编号id，员工姓名ename，工资salary，职务名称jname，职务描述description
2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
3.查询员工姓名，工资，工资等级
4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
5.查询出部门编号、部门名称、部门位置、部门人数

具体步骤如下：
	首先新建四张表：

-- 部门表
CREATE TABLE dept ( id INT PRIMARY KEY auto_increment, -- 部门id
	dname VARCHAR ( 50 ), -- 部门名称
	loc VARCHAR ( 50 ) -- 部门所在地
);
-- 职务表，职务名称，职务描述
CREATE TABLE job ( 
	id INT PRIMARY KEY,  -- 职务id
	jname VARCHAR ( 20 ),  -- 职务名称
	description VARCHAR ( 50 ) -- 职务描述
);
-- 员工表
CREATE TABLE emp (
	id INT PRIMARY KEY,-- 员工id
	ename VARCHAR ( 50 ),-- 员工姓名
	job_id INT,-- 工作编号
	mgr INT,-- 上级领导
	joindate DATE,	-- 入职日期
	salary DECIMAL ( 7, 2 ),	-- 工资
	bonus DECIMAL ( 7, 2 ),-- 奖金
	dept_id INT,	-- 所在部门编号
	CONSTRAINT fk_emp_jobid_job_id FOREIGN KEY ( job_id ) REFERENCES job ( id ),
	CONSTRAINT fl_emp_deptid_dept_id FOREIGN KEY ( dept_id ) REFERENCES dept ( id ) 
);
-- 工资等级表
CREATE TABLE salarygrade ( 
	grade INT PRIMARY KEY, -- 界别
	losalary INT, -- 最低工资
	hisalary INT -- 最高工资
);

添加数据：

-- 添加4个部门
INSERT INTO dept ( id, dname, loc )
VALUES
	( 10, '教研部', '北京' ),
	( 20, '学工部', '上海' ),
	( 30, '销售部', '广州' ),
	( 40, '财务部', '深圳' );
-- 添加4个职务
INSERT INTO job ( id, jname, description )
VALUES
	( 1, '董事长', '管理整个公司，接单' ),
	( 2, '经理', '管理部门员工' ),
	( 3, '销售员', '向客人推销产品' ),
	( 4, '文员', '使用办公软件' );
-- 添加员工
INSERT INTO emp ( id, ename, job_id, mgr, joindate, salary, bonus, dept_id )
VALUES
	( 1001, '孙悟空', 4, 1004, '2000-12-17', '8000.00', NULL, 20 ),
	( 1002, '卢俊义', 3, 1006, '2001-02-10', '16000.00', 3000.00, 30 ),
	( 1003, '林冲', 3, 1006, '2001-02-22', '12500.00', 5000.00, 30 ),
	( 1004, '唐僧', 2, 1009, '2001-04-02', '29750.00', NULL, 20 ),
	( 1005, '李逵', 4, 1006, '2001-09-17', '12500.00', 14000.00, 30 ),
	( 1006, '宋江', 2, 1009, '2001-05-17', '28500.00', NULL, 30 ),
	( 1007, '刘备', 2, 1009, '2001-09-17', '24500.00', NULL, 10 ),
	( 1008, '猪八戒', 4, 1004, '2007-12-17', '30000.00', NULL, 20 ),
	( 1009, '罗贯中', 1, NULL, '2000-12-17', '50000.00', NULL, 10 ),
	( 1010, '吴用', 3, 1006, '2001-09-17', '15000.00', '0.00', 30 ),
	( 1011, '沙僧', 4, 1004, '2007-05-17', '11000.00', NULL, 20 ),
	( 1012, '李逵', 4, 1006, '2001-12-03', '9500.00', NULL, 30 ),
	( 1013, '小白龙', 4, 1004, '2001-12-03', '30000.00', NULL, 20 ),
	( 1014, '关羽', 4, 1007, '2002-01-12', '13000.00', NULL, 10 );
-- 添加工资等级
INSERT INTO salarygrade ( grade, losalary, hisalary )
VALUES
	( 1, 7000, 12000 ),
	( 2, 12010, 14000 ),
	( 3, 14010, 20000 ),
	( 4, 20010, 30000 ),
	( 5, 30010, 99990 );

1.查询所有员工信息。查询员工编号id，员工姓名ename，工资salary，职务名称jname，职务描述description

-- 1.查询所有员工信息。查询员工编号id，员工姓名ename，工资salary，职务名称jname，职务描述description
/*
分析：
1.员工编号id，员工姓名ename，工资salary 在emp表中
2.职务名称jname，职务描述description，在职务表中
3.职务表和员工表是一对多的关系 e.job_id = j.id
通过内连接可完成
*/
-- 隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description 
FROM
	emp,
	job 
WHERE
	emp.job_id = job.id;
-- 显示内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description 
FROM
	emp
	INNER JOIN job ON emp.job_id = job.id;

2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/*
分析：
1.员工编号，员工姓名，工资，在emp表中
2.职务名称，职务描述，在job表中
3.部门名称，部门位置，在dept表中
4.职务表和员工表是一对多的关系 e.job_id = j.id
5.部门表和员工表是一对多的关系 e.dept_id = d.id
*/
-- 隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
	emp,
	job,
	dept 
WHERE
	emp.job_id = job.id 
	AND emp.dept_id = dept.id;
	
-- 显示内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
	emp
	INNER JOIN job ON emp.job_id = job.id
	INNER JOIN dept ON emp.dept_id = dept.id;

3.查询员工姓名，工资，工资等级

-- 3.查询员工姓名，工资，工资等级
/*
	1.员工姓名,工资,emp
	2.工资等级,salarygrade
	3. salarygrade.losalary < emp.salary AND salarygrade.hisalary >  emp.salary
*/

SELECT
	emp.ename,
	emp.salary,
	s.grade 
FROM
	emp,
	salarygrade s 
WHERE
	emp.salary >= s.losalary 
	AND emp.salary <= s.hisalary;

4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
	1.员工姓名，工资,emp
	2.职务名称，职务描述,job
	3.部门名称，部门位置,dept
	4.工资等级,salarygrade
	5.职务表和员工表是一对多的关系 e.job_id = j.id
	6.部门表和员工表是一对多的关系 e.dept_id = d.id
	7.salarygrade.losalary < emp.salary AND salarygrade.hisalary >  emp.salary
*/
-- 隐式内连接
SELECT
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	s.grade 
FROM
	emp,
	job,
	dept,
	salarygrade s 
WHERE
	emp.job_id = job.id 
	AND emp.dept_id = dept.id 
	AND emp.salary >= s.losalary 
	AND emp.salary <= s.hisalary;
	
-- 显式内连接	
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
emp
	INNER JOIN job ON emp.job_id = job.id
	INNER JOIN dept ON emp.dept_id = dept.id
	INNER JOIN salarygrade s ON emp.salary >= s.losalary 
	AND emp.salary <= s.hisalary;

5.查询出部门编号、部门名称、部门位置、部门人数

-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*
	1.部门编号、部门名称、部门位置,dept
	2.部门人数,按照dept_id,进行GROUP BY分组，count(*) 统计人数
	
*/
-- 1.先在emp表中分组并统计人数，2.然后将1中产生的表作为子查询使用
SELECT
	dept.*,
	t1.total 
FROM
	dept,
	(SELECT dept_id, count( * ) total FROM emp GROUP BY dept_id ) t1 
WHERE
	t1.dept_id = dept.id;

-- 第二种方法，先把员工和部门关联起来，然后按照部门id分组，并统计人数
SELECT
	count( * ),
	t.* 
FROM
	( SELECT dept.* FROM emp, dept WHERE emp.dept_id = dept.id ) t 
GROUP BY
	t.id;