部门表
create table dept(id int primary key auto_increment,dname varhcar(50),address varchar(50)); insert into dept(dname,address) values ('教研部','北京'), ('学工部','上海'), ('销售部','广州'), ('财务部','深圳');员工表
create table emp(id int primary key auto_increment,ename varchar(50),job_id int,mgr int,joindate date,salary decimal(7,2),bonus decimal(7,2),dept_id int, constraint emp_job_fk foreign key (job_id) references job(id), constraint emp_dep_fk foreign key(dept_id) references dept(id); insert into emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) values (1001,'孙悟空',4,1004,'2000-12-17','8000.00',null,2), (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',3), (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',3), (1004,'唐僧',2,1009,'2001-04-02','29750.00',null,2), (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',3), (1006,'宋江',2,1009,'2001-05-01','28500.00',null,3), (1007,'刘备',2,1009,'2001-09-01','24500.00',null,1), (1008,'猪八戒',4,1004,'2007-04-19','30000.00',null,2), (1009,'罗贯中',1,null,'2001-11-17','50000.00',null,1), (1010,'吴用',3,1006,'2001-09-06','15000.00','0.00',3), (1011,'沙僧',4,1004,'2007-05-23','11000.00',null,3), (1012,'李逵',4,1006,'2001-12-03','9500.00',null,3), (1013,'小白龙',4,1006,'2001-12-03','3000.00',null,2), (1014,'关羽',4,1007,'2002-01-23','1300.00',null,1);职位表
create table job(id int primary key auto_increment,jname varchar(20),des varchar(50)); insert into job(jname,des) values ('董事长','管理整个公司，接单'), ('经理','管理部门员工'), ('销售人员','向客人推销产品'), ('文员','使用办公软件');薪资等级表
create table salarygrade(grade int primary key,losalary int,hisalary int); insert into salarygrade(grade,losalary,hisalary) values (1,7000,12000), (2,12000,14000), (3,14000,20000), (4,20000,32000), (5,32000,39000);需求： 1:查询所有员工信息，查询员工编号，员工姓名，工资，职务名称，职务描述
select t1.id,t1.ename,t1.salary,t2.jname,t2.des from emp t1, job t2 where t1.job_id = t2.id;2:查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
select t1.id,t1.ename,t1.salary,t2.jname,t2.des,t3.dname,t3.address from emp t1, job t2, dept t3 where t1.job_id = t2.id and t1.dept_id=t3.id;3:查询员工姓名，工资，工资等级
select t1.ename,t1.salary,t2.grade from emp t1, salarygrade t2 where t1.salary between t2.losalary and t2.hisalary;4:查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
select t1.ename,t1.salary,t4.grade,t2.jname,t2.des,t3.dname,t3.address 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 t1.id,t1.dname,t1.address ,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:查询所有员工的姓名及其直接上级的姓名，没有领导的员工也要查询
select t1.ename,t1.mgr,t2.id,t2.ename from emp t1, emp t2 where t1.id = t2.mgr;