#选择部门30中的雇员
SELECT * from emp WHERE deptno = ‘30’mysql
#列出全部办事员（clerk）的姓名、编号和部门
SELECT empno,ename,emp.deptno,dept.dname from emp LEFT JOIN dept on emp.deptno = dept.deptno WHERE job = ‘clerk’web
#找出佣金（comm）高于薪金（sal）的雇员
SELECT * from emp WHERE comm > salsql
#找出部门10中全部经理和部门20中的全部办事员的详细资料
SELECT * FROM emp WHERE job = ‘manager’ and deptno = ‘10’ or job = ‘clerk’ and deptno = ‘20’ ORDER BY deptnosvg
#找出部门10中全部经理、部门20中全部办事员，既不是经理又不是办事员但其薪金>=2000的全部雇员的详细资料
SELECT * FROM emp WHERE job = ‘manager’ and deptno = ‘10’ or job = ‘clerk’ and deptno = ‘20’ or job not in (‘manager’,‘clerk’) and sal >= 2000函数
#找出收取佣金的雇员的不一样工做
SELECT DISTINCT job FROM emp where comm > 0
SELECT DISTINCT job FROM emp where comm is not nullxml
#找出不收取佣金或收取的佣金低于100的雇员
SELECT * from emp WHERE comm <100 or comm is null排序
#找出各月最后一天受雇的全部雇员
SELECT * from emp WHERE hiredate = LAST_DAY(hiredate)rem
#找出早于25年以前受雇的雇员
select * from emp where hiredate < date_add(NOW(), interval -25 year)it
#显示只有首字母大写的全部雇员的姓名
#这题mysql好像没直接的函数啊 不作了哈哈哈基础
#十二、显示正好为6个字符的雇员姓名
SELECT * from emp where LENGTH(ename) = 6
#显示不带有’R’的雇员姓名
SELECT * from emp where ename not like ‘%R%’
#显示全部雇员的姓名的前三个字符
SELECT SUBSTR(ename FROM 1 FOR 3) as ‘3’ from emp
SELECT LEFT(ename,3) as ll , ename FROM emp
#显示全部雇员的姓名，用a替换全部’A’
SELECT REPLACE(ename,‘A’,‘a’) FROM emp
#显示全部雇员的姓名以及满10年服务年限的日期
SELECT ename,hiredate,DATE_ADD(hiredate,INTERVAL 10 year) as ‘十年’ from emp
#显示雇员的详细资料，按姓名排序
SELECT * from emp ORDER BY ename
#显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
SELECT ename,hiredate from emp ORDER BY hiredate ASC
#显示全部雇员的姓名、工做和薪金，按工做的降序顺序排序，而工做相同时按薪金升序
SELECT ename,job,sal from emp ORDER BY job DESC,sal
#显示全部雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最先年份的项目排在最前面
SELECT ename,DATE_FORMAT(hiredate,’%Y’) as hireyear , DATE_FORMAT(hiredate,’%m’) as hiremonth FROM emp ORDER BY hireyear,hiremonth
#显示在一个月为30天的状况下全部雇员的日薪金
SELECT ename, sal , sal/30 as ‘日薪’ from emp
#找出在（任何年份的）2月受聘的全部雇员
SELECT ename ,hiredate from emp where DATE_FORMAT(hiredate,’%m’) = ‘02’
#对于每一个雇员，显示其加入公司的天数
SELECT ename ,hiredate,TO_DAYS(NOW()) - TO_DAYS(hiredate) as ‘加入天数’ from emp
SELECT ename, DATEDIFF(now(),hiredate) as ‘加入天数’ from emp
#显示姓名字段的任何位置，包含 “A” 的全部雇员的姓名
SELECT ename from emp where ename like ‘%A%’
#以年、月和日显示全部雇员的服务年限
SELECT ename,hiredate, TIMESTAMPDIFF(year,hiredate,NOW()) as ‘加入年限’ ,TIMESTAMPDIFF(month,hiredate,NOW()) as ‘加入月’,TIMESTAMPDIFF(day,hiredate,NOW()) as ‘加入天数’ from emp
#列出至少有一个雇员的全部部门
SELECT DISTINCT dname from dept INNER JOIN emp on dept.deptno = emp.deptno
#列出薪金比"SMITH"多的全部雇员
SELECT ename,sal from emp where emp.sal >(SELECT sal from emp where ename=‘smith’)
#列出全部雇员的姓名及其直接上级的姓名
SELECT b.empno,b.ename,b.mgr,(SELECT a.ename from emp a where b.mgr = a.empno ) as mgrname from emp b
#列出入职日期早于其直接上级的全部雇员
SELECT a.ename FROM emp a where a.hiredate > (SELECT b.hiredate from emp b where a.mgr = b.empno)
#列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
SELECT a.dname,b.ename from dept a LEFT JOIN emp b on a.deptno = b.deptno
#列出全部“CLERK”（办事员）的姓名及其部门名称
SELECT a.ename , b.dname FROM emp a LEFT JOIN dept b on a.deptno = b.deptno WHERE a.job = ‘CLERK’
#列出各类工做类别的最低薪金，显示最低薪金大于1500的记录
SELECT job, MIN(sal) as ‘最低薪金’ from emp GROUP BY job HAVING MIN(sal) >1500
#列出从事“SALES”（销售）工做的雇员的姓名，假定不知道销售部的部门编号
SELECT a.ename from emp a where a.deptno = (SELECT b.deptno from dept b WHERE b.dname = ‘SALES’ )
#列出薪金高于公司平均水平的全部雇员
SELECT ename from emp where sal > (SELECT AVG(sal) from emp )
#列出与“SCOTT”从事相同工做的全部雇员
SELECT ename from emp WHERE job = (SELECT job from emp where ename = ‘scott’)
#列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金
SELECT ename,sal from emp where sal in (SELECT sal from emp where deptno = ‘30’) and deptno != ‘30’
#列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中全部雇员的薪金
SELECT ename,sal from emp where sal > (SELECT max(sal) from emp where deptno = ‘30’ )
#列出每一个部门的信息以及该部门中雇员的数量
SELECT a.* , b.cnumb from dept a LEFT JOIN (SELECT deptno,COUNT(deptno) as cnumb from emp GROUP BY deptno) b on a.deptno = b.deptno
#列出全部雇员的雇员名称、部门名称和薪金
SELECT a.ename,a.sal,c.dptname from emp a LEFT JOIN (SELECT deptno as dep,dname as dptname from dept) c on a.deptno = c.dep
#列出从事同一种工做但属于不一样部门的雇员的不一样组合
select t1.ename as a,t2.ename as b from emp t1, emp t2 WHERE t1.job = t2.job and t1.deptno != t2.deptno
#列出分配有雇员数量的全部部门的详细信息，即便是分配有0个雇员
SELECT a.* , b.cnumb from dept a LEFT JOIN (SELECT deptno,COUNT(deptno) as cnumb from emp GROUP BY deptno) b on a.deptno = b.deptno
#列出各类类别工做的最低工资
SELECT job,min(sal) from emp GROUP BY job
#列出各个部门的MANAGER（经理）的最低薪金
select t1.* FROM emp t1, (select deptno, MIN(sal) as sal from emp where job=‘manager’ GROUP BY deptno) t2 where t1.deptno=t2.deptno AND t1.sal = t2.sal and job=‘manager’;
#列出按年薪排序的全部雇员的年薪
SELECT ename, sal12 as ‘年薪’ from emp ORDER BY sal12
#列出薪金水平处于第四位的雇员 SELECT ename,sal from emp ORDER BY sal DESC LIMIT 3,1