5:多表查询

一:概述

创建部门表:
create table dept(
	id int primary key auto_increment,
	name varchar(20)
)engine=innodb;


insert into dept (name) values('开发部'),('市场部'),('财务部');

创建员工表
create table emp(
	id int primary key auto_incrment,
	name varchar(20),
	gender char(10),--性别
	salary double, --工资
	join_date date, --入职日期
	dep_id int,
	foreign key  (dept_id) references dept(id) --外键，关联部门表(部门表的主键
)engine=innodb;
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-24',2);
insert into emp(name,gender,salary,join_date,dep_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);

dept表:

emp表:

基本操作。
1)笛卡儿积，有集合A,B,取两个集合的所有组成情况。

select *from emp,dept;

二:多表查询的分类:

1:内连接查询
隐式内连接:使用where条件消除无用数据。
1):查询所有员工信息和对应的部门信息。

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

2):查询员工表的名称,性别,部门表的名称。

 select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id=dept.id;

显示内连接：

语法: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;
注意事项:
1):从哪些表中查询数据
2):条件是什么
3):查询哪些字段

2:外连接查询
1)左外连接：查询的是左表所有数据以及其交集部分。

语法:select 字段列表 from 表1 left [outer] join 表2 on条件

查询所有员工信息，如果员工有部门，则查询部门，没有部门，则不显示部门名称。

select t1.*,t2.name from emp t1 left join dept t2 on t1.dept_id=t2.id;

2)右外连接：查询的是右表所有数据以及其交集部分。

语法:select 字段列表 from 表1 right [outer] join 表2 on条件

例子：`

select t1.*,t2.name from emp t1 right join dept t2 on t1.dept_id=t2.id;`

二:子查询

概念:查询中嵌套查询，被嵌套的为子查询。

1:查询工资最高的员工信息。
1):查询工资最高员工信息
2）查询员工工资等于最高工资的员工信息。

select*from emp where emp.salary=(select max(salary) from emp);

2:子查询的不同情况：
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 t1,(select*from emp where emp.join_date>'2011-11-11') t2 where t1.id =t2.dept_id;

–普通内连接

select*from emp t1 ,dept t2 where t1.dept_id=t2.id and t1.join_date >'2011-11-11';

三:练习

建表语句:

--部门表
create table dept(
	id int primary key,
	dname varchar(50),
	loc varchar(50)
)engine=innodb;
insert into dept(id,dname,loc) values 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

--职务表，职务名称，职务描述
create table job(
	id int primary key,
	jname varchar(20),
	description varchar(50)
)engine=innodb;

--添加4个职位
insert into job(id,jname,description) values
(1,'董事长','管理整个公司,接单'),
(2,'经理','管理部门员工'),
(3,'销售员','向客人推销产品'),
(4,'文员','使用办公软件');

--员工表
create table emp(
	id int primary key,
	ename varchar(80),
	job_id int,
	mgr int,
	joindate date,
	salary decimal(7,2),
	bonus decimal(7,2),
	dept_id int,
	constraint emp_jobid_ref_job_id_fk foreign key(job_id) references job(id),
	constraint emp_dept_id_ref_dept_id_fk foreign key(dept_id) references dept(id)
)engine=innodb;

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-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',20),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',null,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',null,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',null,10)，
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',null,20)，
(1009,'罗贯中',1,null,'2001-11-17','50000.00',null,10),
(10010,'吴用',3,1006,'2001-09-08','15000.00',0.00,30)，
(10011,'沙僧',4,1004,'2007-05-23','11000.00',null,20),
(10012,'李逵',4,1006,'2001-12-03','9500.00',null,30)，
(10013,'小白龙',4,1004,'2001-12-03','30000.00',null,20),
(10014,'关羽',4,1007,'2002-01-23','13000.00',null,10);

--工资等级表
create table salarygrade(
	grade int primary key,
	losalary int,
	hisalary int
)engine=innodb;
insert into salarygrade(grade,losalary,hisalary) values
(1,000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

问题1：
1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述。
分析:
1).员工编号，员工姓名，工资 需要查询emp表，职务名称，职务描述需要查询job表。
2):查询条件：emp.job_id=job.id;

 selectt1.id,t1.ename,t1.salary,t2.jname,t2.description from emp t1,job t2 where t1.job_id=t2.id;

2:查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
分析:
1):员工编号，员工姓名，工资 emp 职务名称，职务描述job 部门名称，部门位置dept
2):条件;dept.id=job.id and emp.dept_id =dept.id

select t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc from emp t1,job t2,dept t3 where t1.job_id=t2.id and t1.dept_id=t3.id;

3:查询员工姓名,工资，工资等级
分析：
1):员工姓名，工资 emp 工资等级 salarygrade
2):条件:emp.salary >=salarygrade.losalary and emp.salary <=salarygrade.hhisalary

select t1.ename,t1.salary,t2.grade from emp t1,salarygrade t2 where t1.salary between  t2.losalary and t2.hisalary;;

4:查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级。
分析：
1):员工姓名，工资 emp，职务名称，职务描述 job，部门名称，部门位置 dept，工资等级 salarygrade。
2):条件:emp.job_id = job.id and emp.dept_id =dept.id and emp.salary between salarygrade.losalary and salarygrade.hisalary;

select t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc,t4.grade from emp t1,job t2,dept t3,salarygrade t4 where t1.job_id =t2.id and t1.dept_id = t3.id and t1.salary between t4.losalary and hisalary;

5)查询出部门编号,部门名称,部门位置,部门人数.
分析:
1):部门编号，部门名称，部门位置 dept表，部门人数，emp表。
2):使用分组查询，按照emp.dept_id完成分组,查询count(id)
3):使用子查询将第二步的查询结果和dept表进行关联查询。

select t1.id,t1.dname,t1.loc,t2.total from dept t1,(select dept_id,count(id) total from emp group by dept_id) t2 where t1.id =t2.dept_id;

6:查询所有员工的姓名及其上级的姓名，。
分析:
1):姓名 emp,直接上级的姓名 emp
*emp表的id和mgr是子关联
2):条件:emp.id= emp.mgr
3):查询左表的所有数据，和交集。
*使用左外连接。

select t1.ename,t1.mgr,t2.id,t2.ename from emp t1,emp t2 where t1.mgr =t2.id

加上：没有领导的员工也需要查询

 select t1.ename,t1.mgr,t2.id,t2.ename from emp t1 left join emp t2 on t1.mgr=t2.id;