mysql基础(二)练习

1、 创建人力资源管理数据库HR

create database hrdb default charset=utf8;

2、切换数据库

use hrdb;

3、创建部门表TbDept,字段有部门编号deptno，部门名称dname,部门所在地dloc

create table tbdept (
    deptno int not null,
    dname varchar(30) not null,
    dloc varchar(40) not null,
    primary key(deptno)
);

4、插入部门表数据

insert into Tbdept values(10,'会计部','北京');
insert into Tbdept values(20,'研发部','成都');
insert into Tbdept values(30,'销售部','重庆');
insert into Tbdept values(40,'运维部','深圳');

5、创建员工表TbEmp，员工编号etnpno， 员 工 姓 名 ename， 员 工 职 位 job
主管编号tngr， 员 工 月 薪 sal，所在部门编号dno

create table tbemp (
    empno int not null,
    ename varchar(30) not null,
    ejob varchar(30) default null,
    tngr varchar(20) default null,
    sal float default null,
    dno int default null,
    primary key(empno),
    foreign key(dno) references tbdept(deptno)
)

6、插入员工表数据

insert into Tbemp values(7800,'张三丰','总裁',null,19000,20);
insert into Tbemp values(2056,'乔峰','分析师',7800,15900,20);
insert into Tbemp values(3088,'李莫愁','设计师',2056,7590,20);
insert into Tbemp values(3211,'张无忌','程序员',2056,8200,20);
insert into Tbemp values(3233,'丘处机','程序员',2056,7400,20);
insert into Tbemp values(3251,'张翠山','程序员',2056,9800,20);
insert into Tbemp values(5566,'宋明月','会计师',7800,6800,10);
insert into Tbemp values(5234,'郭靖','出纳',5566,2800,10);
insert into Tbemp values(3344,'黄蓉','销售主管',7800,5880,30);
insert into Tbemp values(1359,'胡一刀','销售员',3344,1800,30);
insert into Tbemp values(4466,'苗人凤','销售员',3344,2580,30);
insert into Tbemp values(3244,'欧阳锋','程序员',3088,6200,20);
insert into Tbemp values(3577,'杨过','会计师',5566,3200,10);
insert into Tbemp values(3588,'朱九真','会计师',5566,3200,10);

练习：
1、查询薪水最高的员工姓名和工资

select * from tbemp t_emp order by -t_emp.sal limit 1;

2、 查询员工的姓名和年薪（月薪*12)

select t_emp.ename as '员工姓名', 
t_emp.sal*12 as '年薪' from tbemp t_emp order by -t_emp.sal*12;

3、查询有员工的部门的编号和人数

select count(*) as '总人数', 
dept.deptno as '部门编号' from tbemp emp join tbdept dept 
on emp.dno=dept.deptno group by dept.deptno; 

4、查询所有部门的名称和人数

select count(emp.dno) as '总人数', 
dept.dname as '部门名称' from tbdept dept left join tbemp emp on emp.dno=dept.deptno group by emp.dno;

5、查询薪水最高的员工（boss除外）的姓名和工资

select * from tbemp t_emp where t_emp.empno!=7800 order by -t_emp.sal limit 1;

6、查询薪水超过平均薪水的员工的姓名和工资

select t_emp.ename as '员工姓名', 
t_emp.sal as '工资' from tbemp t_emp where t_emp.sal>(
select avg(emp.sal) from tbemp emp
) order by -t_emp.sal;

7、 查询薪水超过其所在部门的平均薪水的员工的姓名，部门编号和工资

select t1.ename as '员工姓名', 
t1.dno as '部门编号', 
t1.sal as '工资' from (
select avg(t_emp.sal) as a, dept.deptno as num from tbemp t_emp join tbdept dept on t_emp.dno=dept.deptno group by dept.deptno
) t2 join tbemp t1 on t1.dno=t2.num where t1.sal>t2.a;

8、查询部门中薪水最高的人的姓名，工资和所在部门名称

select t2.max_sal as '最高薪水', 
t1.ename as '员工姓名', 
t2.num as '部门名称' from (
select max(t_emp.sal) as max_sal, dept.deptno as num from tbemp t_emp join tbdept dept on t_emp.dno=dept.deptno group by dept.deptno
) t2 join tbemp t1 on t2.num=t1.dno where t1.sal=t2.max_sal and t2.num=t1.dno;

9、 查询主管的姓名和职位

select t1.ename as '主管名字', 
t1.ejob as '主管职位' from (
select emp.tngr as tngr from tbemp emp group by emp.tngr
) t2 join tbemp t1 on t2.tngr=t1.empno;

10、查询薪水排名的前三的员工姓名和工资

select * from tbemp t_emp order by -t_emp.sal limit 1;

11、求薪水排在第4-8名的员工

select * from tbemp t_emp order by -t_emp.sal limit 4, 8;