建表及数据
CREATE DATABASE emp;
USE emp;
CREATE TABLE emp (
`empno` int(4) NOT NULL PRIMARY KEY,-- 员工号
`ename` VARCHAR(10),-- 员工名
`job` VARCHAR(9),-- 工作类型
`mgr` int(4), -- 上级领导编号
`hiredate` DATE,-- 受雇日期
`sal` float(7,2),-- 月工资
`comm` float(7,2),-- 月奖金提成
`deptno` int(2), -- 部门编号
-- 外键关联
CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
CREATE TABLE dept(
`deptno` INT(2) NOT NULL,-- 部门号
`dname` VARCHAR(14),-- 部门名称
`loc` VARCHAR(13),-- 部门地址
CONSTRAINT pk_dept PRIMARY KEY(deptno)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
CREATE TABLE salgrade (
`grade` int,-- 工资等级
`losal` int,-- 最低工资
`hisal` int -- 最高工资) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
use emp;
基础篇
-- 1、 选择部门30中的雇员
select * from emp where deptno=30;
-- 2、 检索emp表中的员工姓名、月收入及部门编号
select ename,sal,deptno from emp;
-- 3、 检索emp表中员工姓名、及雇佣时间（雇佣时间按照yyyy-mm-dd显示）
select ename,hiredate from emp;
4、 检索emp表中的部门编号及工种，并去掉重复行
select distinct deptno,job from emp;
5、 检索emp表中的员工姓名及全年的月收入
select ename,sal from emp;
6、 用姓名显示员工姓名，用年收入显示全年月收入。
select ename 姓名,sal 年收入 from emp;
7、 检索月收入大于2000的员工姓名及月收入
select ename ,sal from emp where sal>2000;
8、 检索月收入在1000元到2000元的员工姓名、月收入及雇佣时间
select ename,sal,hiredate from emp where sal between 1000 and 2000;
9、 检索以S开头的员工姓名及月收入
select ename,sal from emp where ename like 'S%';
10、检索emp表中月收入是800的或是1250的员工姓名及部门编号
select ename,deptno from emp where sal in(800,1250)
11、显示在部门20中岗位是CLERK的所有雇员信息
select * from emp where deptno=20 and job='CLERK';
12、显示工资高于2500或岗位为MANAGER的所有雇员信息
select * from emp where sal>2500 and job='MANAGER';
13、检索emp表中有奖金的员工姓名、月收入及奖金
select ename,sal,comm from emp where comm is not null and comm!=0 ;
14、检索emp表中部门编号是30的员工姓名、月收入及提成，并要求其结果按月收入升序、然后按提成降序显示
select ename,sal,comm from emp where deptno=30 order by sal asc ,comm desc ;
15、列出所有办事员的姓名、编号和部门
select ename,empno,deptno from emp where job='CLERK';
16、找出佣金高于薪金的雇员
select * from emp where sal<comm;
17、找出部门10中所有经理和部门20中的所有办事员的详细资料
select * from emp where job='CLERK' or job='MANAGER';
18、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK' or deptno=20 and job!='CLERK' and job!='MANAGER' and sal>=2000;
19、找出收取奖金的雇员的不同工作
select distinct job from emp where comm>0;
20、找出不收取奖金或收取的奖金低于100的雇员
select * from emp where comm is null or comm=0 or comm<100 and comm!=0;
21、找出各月倒数第三天受雇的所有雇员
-- 思路：先找出每月最后一天转化为天数，入职时间转化为天数，相减为2则为每月倒数第三天入职的雇员
select ta.empno,ta.ename,ta.job,ta.hiredate,ta.comm,ta.sal,ta.deptno from
(select to_days(last_day(hiredate)) t1,to_days(hiredate) t2,empno,ename,sal,comm,deptno,job,mgr,hiredate from emp) as ta
where ta.t1-ta.t2=2;
22、获取当前日期所在月的最后一天
select last_day(now());
23、找出早于25年之前受雇的雇员
select * from emp where hiredate< date_add(now() ,interval -25 year);
24、显示正好为6个字符的雇员姓名
select * from emp where ename like '______';
25、显示不带有'R'的雇员姓名
select ename from emp where ename not like '%R%';
26、显示雇员的详细资料，按姓名排序
select * from emp order by ename asc ;
27、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename from emp order by hiredate;
28、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
select ename,job,sal from emp order by job desc ,sal asc ;
29、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面
select ename,hiredate from emp order by year(hiredate) ,month(hiredate);
30、显示在一个月为30天的情况下所有雇员的日薪金
select empno,ename,mgr,hiredate,job,sal,comm,deptno ,round(sal/30,2) from emp;
31、找出在（任何年份的）2月受聘的所有雇员
select * from emp where month(hiredate)=2;
32、对于每个雇员，显示其加入公司的天数
select ename,to_days(now())-to_days(hiredate) from emp;
33、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名
select ename from emp where ename like '%A%';
select ename from emp where ename like '%a%';
34、以年、月和日显示所有雇员的服务年限
select t.ename, concat(floor(t.days/365),'年',floor(t.days%365/30),'月',t.days%365%30,'天') from
(select ename,datediff(now(),hiredate) days from emp) as t;
35、选择公司中有奖金 (COMM不为空,且不为0) 的员工姓名，工资和奖金比例,按工资逆排序,奖金比例逆排序.
select ename 姓名,round(sal/comm) 比例 from emp where comm is not null and comm!=0 order by sal desc ,sal/emp.comm desc ;
36、选择公司中没有管理者的员工姓名及job
select ename,job from emp where mgr is null;
37、选择在1987年雇用的员工的姓名和雇用时间
select ename,hiredate from emp where year(hiredate)=1987;
38、选择在20或10号部门工作的员工姓名和部门号
select ename,deptno from emp where deptno=10 or deptno=20;
39、选择雇用时间在1981-02-01到1981-05-01之间的员工姓名，职位(job)和雇用时间,按从早到晚排序.
select ename,job,hiredate from emp where hiredate between str_to_date('1981-02-01','%Y-%m-%d') and str_to_date('1981-05-01','%Y-%m-%d');
40、选择工资不在5000到12000的员工的姓名和工资
select ename,sal from emp where sal between 5000 and 12000;
41、查询员工号为7934的员工的姓名和部门号
select ename,deptno from emp where empno=7934;
42、查询工资大于1200的员工姓名和工资
select ename,sal from emp where sal>1200;
复杂语句篇
1. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
select ename,d.dname,cc.c from emp
left join (select count(*) c,deptno from emp group by deptno) as cc on emp.deptno=cc.deptno
left join dept d on emp.deptno = d.deptnowhere job=(select job from emp where ename='scott') and ename!='scott';
2. 列出公司各个工资等级雇员的数量、平均工资。
select s.grade,count(*),avg(sal) from emp left join salgrade s on sal between s.losal and s.hisal group by s.grade order by grade;
3. 列出薪金高于在部门30工作的所有员工最高薪金的员工姓名和薪金、部门名称。
select e.ename,e.sal,d.dname from emp e left join dept d on e.deptno=d.deptno where sal>(select max(sal) from emp where deptno=30);
select ename,sal,dname from emp left join dept d on emp.deptno = d.deptno
where sal>(select max(sal) from emp where deptno=30 group by deptno);
4. 列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno, count(*), round(avg(sal),2),
concat(floor(round(avg(datediff(now(),hiredate)))/365),'年',floor(round(avg(datediff(now(),hiredate)))%365/30),'月',round(avg(datediff(now(),hiredate)))%365%30,'天')from emp group by deptno;
5. 列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal from emp e left join dept d on e.deptno=d.deptno;
6. 列出所有部门的详细信息和部门人数。
use emp;
select d.deptno,d.dname,d.loc,cou.c from dept d left join
(select deptno dno, count(*) c from emp group by deptno) as cou
on d.deptno=cou.dno;
7. 列出各种工作的最低工资及从事此工作的雇员姓名。
select e.job,ename,sal from emp e right join
(select job,min(sal) s from emp group by job) as m on e.sal=m.s and e.job=m.job;
8. 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
select ename,job,sal,s.m,d.dname,c.cc from emp e left join
(select deptno,min(sal) m from emp where job='manager' group by deptno) as s on e.deptno=s.deptno and s.m=e.sal and e.job='manager'
left join dept d on e.deptno = d.deptno
left join (select deptno,count(*) cc from emp group by deptno) as c on e.deptno=c.deptno
where s.m is not null ;
select e.ename 姓名,sal 薪资,d.dname 部门名称 from emp e right join
(select deptno,min(sal) s from emp where job='manager' group by deptno) as m on e.deptno=m.deptno and e.sal=m.s and e.job='manager'
left join dept d on e.deptno = d.deptno ;
9. 列出所有员工的年工资，所在部门名称，按年薪从低到高排序。
select ename,(sal+ifnull(comm,0))12 n,d.dname from emp e left join dept d on e.deptno = d.deptno order by (sal+ifnull(comm,0))12;
10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
select e.ename,m.ename from emp e right join emp m on e.mgr=m.empno and m.sal>3000;
select e.ename,e.deptno,ee.ename,d.dname from emp e left join dept d on e.deptno = d.deptno,emp ee where e.mgr=ee.empno and ee.sal>3000;
11. 求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。
select e.ename,sal+ifnull(comm,0),ee.c from emp e left join dept d on e.deptno = d.deptno,(select deptno dd, count(*) c from emp group by deptno) as ee
where d.dname like '%S%' and ee.dd=e.deptno ;
12. 给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，依次类推。
update emp set sal=(
case when deptno=10 then sal*1.1
when deptno=20 then sal*1.2
when deptno=30 then sal*1.3
else sal*1.4
end ) where year(now())-year(hiredate)>30 or year(hiredate)=1987;13. 列出至少有一个员工的所有部门的信息：
select d.dname,d.deptno,d.loc from dept d right join
(select deptno,count() from emp group by deptno having count()>1) as de on d.deptno=de.deptno;
14. 列出薪金比SMITH多的所有员工：
select * from emp where sal>(select sal from emp where ename='smith');
15. 列出所有员工的姓名以及其直接上级的姓名：
select e.ename,s.ename from emp e,emp s where e.mgr=s.empno;
16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno,emp s where e.mgr=s.empno and e.hiredate<s.hiredate;
17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select * from dept d left join emp e on d.deptno = e.deptno;
18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数
select ename from emp where job='clerk';
select e.ename,d.dname,c.co from emp e left join dept d on e.deptno = d.deptno,(select deptno, count(*) co from emp group by deptno) as c where e.job='clerk' and e.deptno=c.deptno;
19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
select job ,min(sal),count(*) from emp group by job having min(sal)>1500;
20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号
select ename from emp where deptno=(select dept.deptno from dept where dname='sales');
21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
select ename from emp where sal>(select avg(sal) from emp);
select e.ename,e.deptno,m.ename,s.grade from emp e left join emp m on e.mgr=m.empno left join salgrade s on e.sal between s.losal and s.hisal where e.sal>(select avg(sal) from emp);
22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。
select emp.deptno,d.dname ,round(avg(sal),2),min(sal) ,max(sal) from emp left join dept d on emp.deptno = d.deptno group by deptno having count(*)>1;
23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
select e.empno,e.ename,d.dname,m.ename from emp e left join dept d on e.deptno = d.deptno left join emp m on e.mgr=m.empno
where e.sal>(select sal from emp where ename='smith') and e.ename !='allen' or e.sal>(select sal from emp where ename='allen');
24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
select e.empno,e.ename,e.mgr,m.ename from emp e left join emp m on e.mgr=m.empno order by m.sal desc ;
25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
select emp.empno,emp.ename,d.dname,d.loc ,cou.c from emp left join emp m on emp.mgr=m.deptno left join dept d on emp.deptno = d.deptno
,
(select count(*) c,deptno from emp group by deptno) as couwhere emp.hiredate<m.hiredate;
26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。
select dname,c.a,c.cc from dept left join (select deptno, avg(sal) a ,count(*) cc from emp group by deptno) as c on dept.deptno=c.deptno;
27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。
select e.ename,de.dname,d.c,s.grade from emp e left join
(select deptno,count(*) c from emp group by deptno) as d on d.deptno=e.deptnoleft join dept de on e.deptno=de.deptno
left join salgrade s on e.sal between s.losal and s.hisal
where job='clerk';
28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。
select distinct j.* ,e.job,d.dname,d.loc from emp e right join
(select job,min(sal) m,avg(sal),count(*) a from emp e group by job having min(sal)>1500) as j on e.job=j.job
left join dept d on e.deptno = d.deptno;
29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。
select e.ename,e.sal,e.hiredate,d.dname from emp e right join dept d on e.deptno=d.deptno where d.dname='sales';
30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。
select e.ename,m.ename,d.dname,s.grade from emp e left join emp m on e.mgr=m.empno
left join dept d on e.deptno = d.deptno
left join salgrade s on e.sal between s.losal and s.hisal
where e.sal>(select avg(sal) from emp);
31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
select ename ,e.job,d.dname,c.cc from emp e
left join dept d on e.deptno = d.deptnoleft join (select deptno, count(*) cc from emp group by deptno) as c on d.deptno=c.deptno
where job=(select job from emp where ename='scott') and e.ename!='scott';
select e.ename,e.job,d.dname,cc.c from emp e join
(select job from emp where ename='scott') as j on e.job=j.job
left join dept d on e.deptno=d.deptno
left join (select deptno,count(*) c from emp group by deptno) as cc on cc.deptno=e.deptno
where e.ename!='scott';
32. 查询dept表的结构
show columns from dept;
33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段
select ename,concat('is a ',job) from emp;
34. 检索emp表中有提成的员工姓名、月收入及提成。
select ename,sal,comm from emp where comm is not null and comm>0;
注：mysql语句中不区分大小写 可使用二进制的方式区分
