mysql经典案例带解析（你没见过的全新版本）55题

首先给出初始表格

表格创建命令

create table emp(id int primary key auto_increment,name varchar(20),job varchar(20),manager int,hiredate date,sal double(8,2),comm double(6,2),dept_id int)charset=utf8;

create table dept(id int primary key auto_increment,name varchar(20),loc varchar(30))charset=utf8;

insert  into `dept`(`id`,`name`,`loc`) values (1,'神仙','天庭'),(2,'妖怪','盘丝洞'),(3,'普通人','北京'),(4,'赛亚人','外星球');

insert  into `emp`(`id`,`name`,`job`,`manager`,`hiredate`,`sal`,`comm`,`dept_id`) values (1,'孙悟空','销售',4,'1980-12-17',800.00,NULL,1),(2,'猪八戒','销售',4,'1981-02-20',1600.00,300.00,1),(3,'沙僧','销售',4,'1981-02-22',1250.00,500.00,1),(4,'唐僧','销售经理',8,'1981-04-02',2975.00,NULL,1),(5,'刘备','项目经理',NULL,'1981-09-28',1250.00,1400.00,3),(6,'关羽','程序员',5,'1981-05-01',2850.00,NULL,3),(7,'张飞','程序员',5,'1981-06-09',2450.00,NULL,3),(8,'观音','CEO',NULL,'1981-11-17',5000.00,NULL,1),(9,'白骨精','人事',8,'1981-09-08',1500.00,0.00,2),(10,'蜘蛛精','人事',8,'1981-12-03',950.00,NULL,2),(11,'黑熊怪','市场',8,'1981-12-03',3000.00,NULL,2);
1
2
3
4
5
6
7

接下来是题目
查询工资大于等于3000的员工姓名和工资(salary)

SELECT name,sal
FROM emp
WHERE sal>= 3000;
1
2
3

查询1号部门的员工姓名和工作

SELECT name,job FROM emp WHERE dept_id= 1;
1

查询工资大于等于3000的员工姓名和工资

SELECT name,sal FROM emp WHERE sal>= 3000;
1

查询1号部门的员工姓名和工作

SELECT name,job FROM emp WHERE dept_id= 1;
1

查询奖金等于300的员工姓名,工资和工作

SELECT name,job,sal FROM emp WHERE comm= 300;
1

查询1号部门工资大于2000的员工信息

SELECT * FROM emp WHERE sal >2000 and dept_id=1;
1

查询3号部门或工资等于5000的员工信息

SELECT * FROM emp WHERE sal =5000 or dept_id=3;
1

查询出CEO和项目经理的名字

select name from emp where job='CEO' or job='项目经理';
1

查询工资为3000,1500和5000的员工信息

select * from emp where sal in (3000,5000,1500);
1

查询工资不等于3000,1500和5000的员工信息

select * from emp where sal NOT in (3000,5000,1500);
1

查询工资在1000到2000之间的员工信息

select * from emp where sal>=1000 and sal<=2000;
1

查询工资在1000到2000以外的员工信息

select * from emp where sal<=1000 OR sal>=2000;
1

查询有领导的员工姓名和领导id

select name,manager from emp where manager is not null;
1

查询没有领导的员工姓名和领导id

select name from emp where manager is null;
1

查询员工表中出现了哪几种不同的工作

select distinct job from emp;
1

查询员工表中出现了那几个部门的id

select distinct dept_id from emp;
1

查询姓孙的员工姓名

 select * from emp where name like “孙%”;
1

查询名字最后一个字是精的员工信息

SELECT * FROM emp WHERE name LIKE "%精";
1

查询工作中包含销售的员工信息

select * from emp where job like "%销售%";
1

查询工作中第二个字是售的员工信息

select * from emp where job like "_售%";
1

查询名字中包含僧的员工并且工资高于2000的员工信息

select* from emp where name like "%僧%" and sal>2000;
1

查询1号和2号部门中工作以市开头的员工信息

select * from emp where job like "市%" and dept_id in("1","2");
1

查询所有员工的姓名和工资 按照工资升序排序

select name,sal from emp order by sal;
1

查询所有员工的姓名和工资 按照工资降序排序

select name,sal from emp order by sal desc;
1

查询所有员工姓名 工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序

select name,sal from emp order by sal desc;
1

查询员工表中3号部门工资高于1500的员工信息

select*from emp sal>1500 and dept_id="3";
1

查询2号部门员工或者没有领导的员工信息

select*from emp where  dept_id="2" or manager is not null;
1

查询有领导的员工姓名,工资按照工资降序排序

select name,sal from emp where manager is null  order by sal desc;
1

查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序

select name,hiredate from emp where dept_id in("2","3") order by hiredate desc;
1

查询名字中包含僧和包含精的员工姓名

select name from emp where name like “%僧%"or“%精%”;
1

查询工资高于2000的工作有哪几种?

select distinct job from emp where sal>"2000";
1

查询工资最高的前三个员工

select * from emp order by sal desc limit 0,3;
1

查询员工表按照id排序, 第2页的5条数据

select * from emp order by id limit 2,5;
1

查询员工表按照id排序, 第3页的4条数据

select * from emp order by id limit 3,4;
1

查询3号部门工资最低的员工姓名和工资

select name,min(sal) from emp where dept_id=3;
1

查询工作不是人事的员工中工资降序第二页的3条数据

select * from emp  where job !="人事" order by sal desc limit 3,3 ;
1

查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)
select name,sal,sal*5 年终奖 from emp;
1
2

给3号部门所有员工涨薪5块钱

update emp set sal=sal+5 where dept_id=3;
1

查询没有领导的员工和3号部门的员工,工资降序取前三条

select*from emp where manager or dept_id="3" is null oder by sal desc limit 3;
1

查询2号部门的最高工资

select max(sal) from emp where dept_id=2;
1

查询有领导的员工中工资在1000到2000之间的人数

select count(*) from emp where manager is not null and sal between "1000" and "2000" ;
1

查询3号部门的工资总和

select sum(sal) from emp where dept_id=3;
1

查询程序员和销售的总人数

select count(*) from emp where job ="程序员" or job="销售" ;
1

查询1号部门有领导的员工的平均工资

select avg(sal) from emp where manager is null and dept_id="1";
1

查询1号部门的最低工资和最高工资

select max(sal) 最高工资,min(sal) 最低工资 from emp where dept_id=1;
1

查询和销售相关的工作人数

select job,count(*) c from emp group by job ="销售" ;
1

查询工资不是1500和3000的员工人数

select count(*) from emp where sal not between "1500" and "3000" ;
1

查询1号部门出现了哪几种工作

 select distinct job from emp where dept_id="1";
1

查询每个部门的平均工资

select dept_id,avg(sal) from emp group by dept_id;
1

查询每种工作的人数

select job,count(*) c from emp group by job ;
1

查询每个部门的最高工资

select dept_id,max(sal) from emp group by dept_id;
1

查询每种工作的最低工资

select job,min(sal) from emp group by job;
1

查询每个部门的工资总和

select dept_id,sum(sal) from emp group by dept_id;
1

查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400

select dept_id,sum(sal) from emp where manager is not null group by dept_id having sum(sal)>5400;
1