多表查询的简单练习

use db2;

-- 查看表
show tables;
desc dept;
desc emp;
desc job;
desc salarygrade;
SELECT * FROM `dept`;
SELECT * FROM emp;
SELECT * FROM job;
SELECT * FROM salarygrade;
-- 多表查询练习

-- 创建department部门表
create table dept(
  id int primary key auto_increment,
	dname varchar(20),
	loc varchar(20)
);
-- 插入数据
insert into dept values(10,'教研部',"北京");
insert into dept values(20,'学工部',"上海");
insert into dept values(30,'销售部',"广州");
insert into dept values(40,'财务部',"深圳");

-- 创建工作表
create table job(
    id int primary key auto_increment,
		jname varchar(20),
		description varchar(20)
);
insert into job values(1,'董事长',"管理整个公司，接单");
insert into job values(2,'经理',"管理部门员工");
insert into job values(3,'销售员',"向客人推销产品");
insert into job values(4,'文员',"使用办公软件");
-- 创建员工表employee
create table emp(
   id int primary key auto_increment,
	 ename varchar(20),
	 job_id int,
	 mgr int, -- 管理者编号
	 joindate date,
	 salary int,
	 bonus int, -- 奖金
	 dept_id int,
	 foreign key (job_id) references job (id),
	 foreign key (dept_id) references dept (id)
);
insert into emp values(1001,'孙悟空',4,1004,'2000-12-17',8000,null,20);
insert into emp values(1002,'卢俊义',3,1006,'2001-2-20',16000,3000,30);
insert into emp values(1003,'林冲',3,1006,'2001-2-22',12500,5000,30);
insert into emp values(1004,'唐僧',2,1009,'2001-4-2',29750,null,20);
insert into emp values(1005,'李迪',4,1006,'2001-9-28',12500,14000,30);
insert into emp values(1006,'宋江',2,1009,'2001-5-1',28500,null,30);
insert into emp values(1007,'刘备',2,1009,'2001-9-1',24500,null,10);
insert into emp values(1008,'猪八戒',4,1004,'2007-4-19',30000,null,20);
insert into emp values(1009,'罗贯中',1,null,'2001-11-17',50000,null,10);
insert into emp values(1010,'吴用',3,1006,'2001-9-8',15000,null,30);
insert into emp values(1011,'沙僧',4,1004,'2007-5-23',11000,null,20);
insert into emp values(1012,'李迪',4,1006,'2001-12-3',9500,null,30);
insert into emp values(1013,'小白龙',4,1004,'2001-12-3',30000,null,20);
insert into emp values(1014,'关羽',4,1007,'2002-1-13',13000,null,10);
-- 创建工资等级表
create table salarygrade(
    grade int primary key,
		losalary int,
		hisalary int
);
insert into salarygrade values(1,7000,12000);
insert into salarygrade values(2,12010,14000);
insert into salarygrade values(3,14010,20000);
insert into salarygrade values(4,20010,30000);
insert into salarygrade values(5,30010,99990);


-- 1.查询所有员工信息。员工的编号，姓名，工资，职务名称，职务描述
select  
      emp.id, -- 员工的编号
			emp.ename,-- 员工的姓名
			emp.salary,-- 员工的工资
			job.jname,-- 员工的职务名称
			job.description -- 员工的职务描述
from 
      emp,job
where 
      emp.job_id = job.id; 
	

-- 2.查询员工编号,员工姓名,工资，职务名称
select  
      emp.id,
			emp.ename,
			emp.salary,
			job.jname 
from 
      emp,job 
where 
      emp.job_id = job.id; 
			

-- 3.查询员工姓名,工资，工资等级
select 
      t1.ename,
			t1.salary,
			t2.*
from 
      emp t1, 
			salarygrade t2 
where 
      t1.salary between t2.losalary and t2.hisalary;
	
-- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置，工资等级
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 t4.hisalary;

-- 5。查出部门编号,名称,位置,人数。
select
     t2.id,
		 t2.dname,
		 t2.loc,
		 count(t2.id) as 人数
from
     emp t1,
	   dept t2
where t1.dept_id = t2.id
group by t2.id;

-- 6.查询所有员工的姓名及其上级的姓名，没有领导的员工也需要查询
select 
     t1.ename 员工姓名,
		 t2.ename 上级姓名
from 
     emp t1 
left join (select emp.id,emp.ename from emp) t2 on t1.mgr = t2.id
order by t1.job_id, t1.salary desc;