查询所有的部门编号

select deptno from dept

查询所有有人的部门编号

select deptno from emp

查询所有岗位的名称

select DISTINCT job from emp

查询所有薪水超过两千的员工信息

select * from emp where sal >2000

查询所有20部门的员工姓名，编号及薪水

select ename,sal,empno from emp where deptno = 20

查询所有没有奖金的员工信息

select * from emp where comm is null
查询所有有奖金的员工信息
select * from emp where comm is not null
查询最高领导的员工信息
select * from emp where mgr is null
查询所有81年之后入职的员工信息
select * from emp where hiredate >= "1981-01-01"
查询所有薪水在2000-4000范围内的员工信息
select * from emp where sal between 2000 and 4000
查询所有部门编号是10或30的员工信息
select * from emp where deptno = 10 or deptno = 30
select * from emp where deptno in (10, 30 )
查询所有20部门并且薪水超过2000的员工信息
select * from emp where deptno = 20 and sal > 2000
查询所有薪水不在2000-4000范围内的员工信息
select * from emp where sal not between 2000 and 4000
查询所有部门编号不是10， 30 的员工信息
select * from emp where deptno not in (10,30)
查询用户名为scott的员工信息：注意区分大小写
SELECT * from emp where ename = "SCOTT"
查询姓名里面包含ALL的员工姓名
select ename from emp where ename like "%ALL%"
.查询所有以”S”开头的同学
select ename from emp where ename like "S%"
查询第二个字母为A的员工姓名
select ename from emp where ename like "_A%"
查询所有员工的编号、姓名、部门编号、职位、薪水，按照薪水降序排列
select empno,ename,deptno,job,sal from emp order by sal desc
查询所有员工信息，按照部门降序排列，部门内按照薪水升序排列
select * from emp order by deptno desc , sal asc
查询姓名中包含‘A’员工的姓名，编号，薪水，按照薪水降序排列
select ename,empno,sal from emp where ename like "%A%" ORDER BY sal desc
select * from emp
查询年收入超过10000的员工的姓名，编号，薪水，年收入，按照年收入降序排列
select ename,sal,(sal+ comm)12 as newsal from emp where (sal+ comm)12 >10000 order by newsal desc
查询年薪超过10000的员工的姓名，编号，薪水，年收入，按照年薪降序排列
SELECT ENAME,EMPNO,SAL,SAL12 AS YEARSAL FROM EMP WHERE SAL12 > 10000 ORDER BY YEARSAL DESC;
查询雇员表中，姓名为SMITH的雇员，截止到今天共工作了多少周，则可以使用如下的SQL语句
SELECT ROUND(DATEDIFF(SYSDATE(),hiredate)/7) FROM EMP WHERE ENAME = "SMITH";

时间差函数：datediff

语法：传入两个日期参数，比较DAY天数，第一个参数减去第二个参数的天数值

SELECT DATEDIFF('2013-01-13','2012-10-01'); # 104
一、时间差函数：timestampdiff
　　语法：timestampdiff(interval, datetime1,datetime2)

结果：返回（时间2-时间1）的时间差，结果单位由interval参数给出。

frac_second 毫秒（低版本不支持，用second，再除于1000）
second 秒
minute 分钟
hour 小时
day 天
week 周
month 月
quarter 季度
year 年
　　注意：MySQL 5.6之后才支持毫秒的记录和计算，如果是之前的版本，最好是在数据库除datetime类型之外的字段，再建立用于存储毫秒的int字段，然后自己进行转换计算。

复制代码

所有格式

SELECT TIMESTAMPDIFF(FRAC_SECOND,'2012-10-01','2013-01-13'); # 暂不支持
SELECT TIMESTAMPDIFF(SECOND,'2012-10-01','2013-01-13'); # 8985600
SELECT TIMESTAMPDIFF(MINUTE,'2012-10-01','2013-01-13'); # 149760
SELECT TIMESTAMPDIFF(HOUR,'2012-10-01','2013-01-13'); # 2496
SELECT TIMESTAMPDIFF(DAY,'2012-10-01','2013-01-13'); # 104
SELECT TIMESTAMPDIFF(WEEK,'2012-10-01','2013-01-13'); # 14
SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13'); # 3
SELECT TIMESTAMPDIFF(QUARTER,'2012-10-01','2013-01-13'); # 1
SELECT TIMESTAMPDIFF(YEAR,'2012-10-01','2013-01-13'); # 0
复制代码

二、时间差函数：datediff
　　语法：传入两个日期参数，比较DAY天数，第一个参数减去第二个参数的天数值。

SELECT DATEDIFF('2013-01-13','2012-10-01'); # 104

三、时间差函数：timediff
　　语法：timediff(time1,time2)

结果：返回两个时间相减得到的差值，time1-time2

SELECT TIMEDIFF('2018-05-21 14:51:43','2018-05-19 12:54:43');

49:57:00

四、其他日期函数
now()函数返回的是当前时间的年月日时分秒
curdate()函数返回的是年月日信息
curtime()函数返回的是当前时间的时分秒信息
对一个包含年月日时分秒日期格式化成年月日日期，可以使用DATE(time)函数
复制代码

其他日期函数

SELECT NOW(); # 2018-05-21 14:41:00
SELECT CURDATE(); # 2018-05-21
SELECT CURTIME(); # 14:41:38
SELECT DATE(NOW()); # 2018-05-21
SELECT SYSDATE(); # 2018-05-21 14:47:11
SELECT CURRENT_TIME(); # 14:51:30
SELECT CURRENT_TIMESTAMP; # 2018-05-21 14:51:37
SELECT CURRENT_TIMESTAMP(); # 2018-05-21 14:51:43
复制代码
查询各部门的最高薪水、最低薪水、平均薪水….
select max(sal),MIN(sal),avg(sal) from emp
查询‘SMITH’的领导姓名
select ename from emp where empno = (select mgr from emp where ename = "SMITH")
查询部门名称是‘SALES’的员工信息
select * from emp where deptno = (select deptno from dept where dname="SALES")
select * from dept
查询公司中薪水最高的员工信息
select * from emp where sal = (select max(sal) from emp )
查询公司所有员工的个数
select count(ename) from emp
查询公司中最高薪水是多少
select max(sal) from emp
31查询公司中平均奖金是多少
select avg(nvl(comm,0)) from emp
32.查询公司中最晚入职的时间
SELECT max(HIREDATE) FROM EMP ;
33.查询公司中有奖金的人数
SELECT count(ENAME) FROM EMP WHERE (COMM IS NOT NULL) ;
34.查询20部门的最高薪水是多少
SELECT max(SAL) FROM EMP WHERE DEPTNO = 20 ;
35.查询各部门的平均薪水及部门编号，部门名称。
SELECT avg(SAL),DNAME,EMP.DEPTNO FROM EMP,DEPT where EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME,EMP.DEPTNO;
36.查询各部门中最高薪水的员工编号，姓名…
select empno , ename,DEPTNO from emp WHERE SAL in (select max(SAL) from EMP GROUP BY DEPTNO);
37.查询所有员工姓名中包含‘A’的最高薪水
SELECT max(SAL) FROM EMP WHERE ENAME LIKE "%A%" ;
38.查询各岗位的最高薪水，最低薪水。要求只统计薪水>1000的
SeLECT max(SAL),min(SAL) FROM EMP WHERE SAL > 1000 GROUP BY job;
39.查询各部门的平均薪水及部门编号，要求只列出平均薪水>2000
SELECT AVG(SAL),DEPtNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000;
40.查询各部门的平均薪水及部门编号，要求只有员工姓名中包含
‘A’才参与统计，只列出平均薪水>1500的，按照平均薪水降序排列

SELECT AVG(SAL),DEPTNO FROM EMP where ENAME LIKE "%A%" GROUP BY DEPTNO HAVING AVG(SAL) > 1500 ORDER BY AVG(SAL) DESC;

查询薪水大于该部门平均薪水的员工信息
select * from emp e1 where sal > ( select avg(sal) from emp e2 where e1.deptno=DEPTNO);
查询最高薪水的员工信息
SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP )
查询‘SMITH’的领导姓名
select ename from emp where empno= (select mgr from emp where ename="SMITH")
select * from emp
select * from dept
select * from salgrade
查询部门名称是‘SALES’的员工信息
select * from emp where deptno = (select deptno from dept where dname = "SALES")
查询公司中薪水最高的员工信息
SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP )

49查询薪水等级为4的员工信息
select * from emp where sal between (select losal from salgrade where grade = 4) and (select hisal from salgrade where grade = 4)

50.查询领导者是‘BLAKE’的员工信息
select * from emp where MGR = (select EMPNO from emp where ENAME = "BLAKE");

51.查询最高领导者的薪水等级
select grade FROM SALGRADE where (SELECT SAL from emp where mgr is NULL) BETWEEN LOSAL AND HISAL;

52.查询薪水最低的员工信息
select * from emp where SAL = (select MIN(sal) from emp);

53.查询和SMITH工作相同的员工信息
select * from emp where JOB = (select JOB from emp where ENAME = "SMITH") ;

54.查询不是领导的员工信息
select * from emp e1 where not EXISTS
(select * from emp e2 where e2.mgr = e1.empno);

55.查询平均工资比10部门低的部门编号
select deptno from emp group by deptno having avg(sal) <
(select avg(sal) from emp where deptno = 10);

56.查询在纽约工作的所有员工
select * from EMP where DEPTNO = (select DEPTNO from DEPT where LOC = ‘NEW YORK’);

57.查询‘SALES’部门平均薪水的等级
select grade from SALGRADE where (select AVG(SAL) from emp where DEPTNO = (select DEPTNO FROM DEPT WHERE dname = "SALES") ) BETWEEN LOSAL AND HISAL;

58.查询10号部门的员工在整个公司中所占的比例：
select (select COUNT(DEPTNO) from EMP where DEPTNO = 10) / (select COUNT(*) from EMP) FROM dual;

60.查询各部门工资大于该部门平均工资的员工信息：
select * from emp e1 where sal > ( select avg(sal) from emp e2 where e1.deptno = deptno );

61.查询各岗位工资小于该岗位平均工资的员工信息；
select * from emp e1 where sal < (select avg(sal) from emp e2 where e1.job= job group by job);

62.查询所有领导的信息：要求使用exists关键字
select * from emp e1 where EXISTS
(select * from emp e2 where e2.mgr = e1.empno);

63.查询所有员工的姓名，薪水，部门名称
select ename,sal,dname from emp,dept where EMP.DEPTNO = DEPT.DEPTNO;

64.查询所有员工的姓名，薪水，部门名称,薪水等级
select ename,sal,dname,grade from emp,dept,SALGRADE
where EMP.DEPTNO = DEPT.DEPTNO and SAL BETWEEN LOSAL AND HISAL;

65.查询员工姓名及领导者姓名
select a.ename AS 员工姓名 ,b.ename AS 领导姓名
from emp a LEFT JOIN emp b on a.mgr = b.empno;

66.查询所有员工的姓名，部门名称
select ename, dname from emp ,dept where EMP.deptno = DEPT.deptno;

查询员工表中工资大于1600的员工的姓名和工资
select ename,sal from emp where sal > 1600

查询员工表中员工号是17的员工的姓名和部门编号
select ename,deptno from emp where empno = 17
选择员工表中工资不在4000到5000内的员工的姓名和工资
select ename,sal from emp where sal not between 4000 and 5000
选择员工表中在20和30部门工作的员工的姓名和部门号
select ename,deptno from emp where deptno in (20,30)
选择员工表中没有管理者的员工姓名及职位，按职位排序
select ename,job from emp where mgr is null order by job asc
选择员工表中有奖金的员工姓名，工资和奖金，按工资倒序排列
select ename,sal,comm from emp where comm is not null order by sal desc
选择员工表中员工姓名的第三个字母是A的员工姓名
select ename from emp where ename like "__A%";
列出部门表中的部门名称和所在城市
select dname,loc from dept
显示员工表中的不重复的岗位job
select DISTINCT job from emp
连接员工表中的员工姓名、职位、薪水，列之间用逗号连接，列头显示成out_put 用concat#####################
select CONCAT(ename,",",job, "," ,sal) AS ename_job_sal from emp
查询员工表中员工号，姓名，工资，以及工资提高百分之20之后的结果
select empno ,ename,sal ,sal*1.2 as newsal from emp

查询员工的姓名和工资数，条件限定为工资数必须大于1200，并且查询结果按入职时间进行排序。早入职的员工排在前面
select empno ,sal,HIREDATE from emp where sal > 1200 ORDER BY HIREDATE

列出除了ACCOUNTING部门之外还有什么部门
select dname from dept where dname != "ACCOUNTING";
把雇员按部门分组，求最高薪水，部门号 要求过滤掉名字中第二个字母是’A’的员工， 并且部门的平均薪水 > 3000,按照部门编号倒序排列
select MAX(sal),deptno from emp where ename not like "_A%" GROUP BY deptno having avg(sal) > 3000 ;

求工作职位是’manager’的员工姓名，部门名称和薪水等级
select ename ,dname,grade from emp,dept,salgrade where job = "MANAGER" and (sal BETWEEN LOSAL and HISAL) and EMP.DEPTNO = DEPT.DEPTNO;

按照部门分组统计，求最高薪水，平均薪水，最低薪水，只有薪水是1200以上的员工才参与统计，
并且分组结果中只包含平均薪水在1500以上的部门，并且按照平均薪水倒序排列
select max(sal),min(sal),avg(sal) from emp where sal > 1200 group by deptno having avg(sal) > 1500 order by avg(sal) DESC;
求薪水最高的员工姓名
select ename from emp where sal = (select max(sal) from emp);
查询各部门平均薪水等级，并且按平均薪水等级的降序排列
select grade from salgrade s join (select avg(sal) avg_sal from emp e group by deptno) temp on TEMP.avg_sal between s.LOSAL and s.HISAL;
查询所有员工姓名以S或s开头的所有员工信息
select * from emp where ename like "S%" or ename like "s%"
查询20部门的所有员工的员工姓名，实际收入
select ename,sal + ifnull(comm, 0) from emp where deptno = 20 ;
查询10部门工资大于3000的员工信息，要求按员工的入职时间由前到后排序
select * from emp where deptno = 10 and sal > 3000 ORDER BY HIREDATE
查询10部门或20部门的所有员工的姓名，并截取前三位，按员工姓名升序排列
select substring(ename, 1, 3) from emp where deptno in (10,20) ORDER BY ename
查询部门名称是’ACCOUNTING’的员工姓名及薪水等级
select ename,grade from emp ,salgrade ,dept where dname="ACCOUNTING" and (sal between LOSAL and HISAL) and (EMP.deptno = DEPT.deptno)