Mysql函数题库练习

才一个假期没碰，朋友问我一些数据库就已经生疏了，还好当时电脑里备份了题库和答案，今天分享出来，这些都是题库中的题，网上应该也有很多，顺便也给自己过一遍

1 以首字母大写,其他字母小写的方式显示所有员工的姓名
 select CONCAT(upper(SUBSTR(ENAME,1,1)),LOWER(SUBSTR(ENAME,2,LENGTH(ENAME))))
from emp
2 将员工的工种用小写字母显示
select LOWER(job)
from emp
3 显示员工姓名超过5个字符的员工名
SELECT ENAME
from emp
where LENGTH(ENAME)>5
4 显示员工工种的第一个字符
SELECT left(job,1)
from emp
5 用！来填充员工工种job的结尾处，按10个字符长度输出。
SELECT RPAD(JOB,10,'!')
FROM emp
6 找出字符串"attached textfile"中第三个t出现的位置
SELECT LENGTH(SUBSTRING_INDEX('attached textfile','t',3))+1
7 去除字符串'  hello world  '两边的空格,并将单词间的空格改为','逗号。
SELECT REPLACE(TRIM('  hello world  '),' ',',')
8 以指定格式显示员工的佣金
（格式：ALLEN 's comm is 300)
注：如果佣金为NULL显示为null，如Smith 's comm is null。
SELECT CONCAT(ENAME,'''s comm is',IFNULL(COMM,'NULL')) as 佣金结果按格式显示！！！
from emp
9 查询1981-04月前两个月入职的员工
SELECT ENAME
from emp
WHERE STRCMP(year(HIREDATE),1981)=0 and(STRCMP(MONTH(HIREDATE),2)=0 or STRCMP(MONTH(HIREDATE),3)=0)

10 显示在一个月为30天的情况所有员工的日薪,忽略余数
 SELECT CEILING(sal/30)
from emp

11 显示员工在此公司工作了几个月（要求结果是整数）
SELECT ENAME,timestampdiff(MONTH,HIREDATE,now())
from emp

12 显示所有12月份入职的员工
SELECT ename
from emp
WHERE MONTH(HIREDATE)=12
13 显示员工的年薪（12个月的工资+补贴）
SELECT ename,CONCAT((sal*12),IFNULL(COMM,0))
from emp
14 显示所有员工的姓名、加入公司的年份和月份，并且按照年份排序
SELECT ENAME,year(HIREDATE),MONTH(HIREDATE)
from emp
ORDER BY year(HIREDATE)
15 求部门名称中带“S”字符的部门员工的工资合计、部门人数
SELECT SUM(IFNULL(sal,0)+IFNULL(COMM,0)),COUNT(EMPNO),t.DEPTNO
from emp
RIGHT JOIN
(SELECT DEPTNO
from dept
where dept.DNAME LIKE '%s%') t
ON emp.DEPTNO=t.DEPTNO
GROUP BY DEPTNO;

16 给任职日期超过30年的员工加薪10%
SELECT ENAME,(sal+sal/10) as sal1
from emp a
where a.EMPNO in
(SELECT e.EMPNO
from emp e
WHERE timestampdiff(year,HIREDATE,now())>30)

1.取得每个部门最高薪水的人员名称
    1.1 求每个部门的最高薪水

    1.2 将上面的表作为临时表和emp表连接

(1) select e.deptno,
max(e.sal) from emp e group by e.deptno
     (2)
        select e.deptno,e.ename,e.sal from emp e join
        (
         select e.deptno,max(e.sal) maxsal from emp e group by e.deptno
        ) t on t.deptno = e.deptno where e.sal = t.maxsal order by e.deptno;
2. 哪些人的薪水在部门平均薪水之上
    2.1 求出每个部门的平均薪水

    2.2 将上面的表作为临时表连接emp
SELECT t.deptno,ename,emp.sal,sal1
FROM
(select deptno,avg(sal) as sal1
from emp
GROUP BY deptno) t join emp
on t.deptno=emp.DEPTNO
WHERE t.sal1<emp.SAL

3.取得部门中（所有人的）平均薪水等级
            +-------+-------+-------+
            | GRADE | LOSAL | HISAL |
            +-------+-------+-------+
            |     1 |   700 |  1200 |
            |     2 |  1201 |  1400 |
            |     3 |  1401 |  2000 |
            |     4 |  2001 |  3000 |
            |     5 |  3001 |  9999 |
            +-------+-------+-------+
    3.1 取得部门中平均薪水的等级                    
    3.2 取得部门中（所有人的）平均的薪水等级
select *
FROM
(select deptno,avg(sal) as sal1
from emp
group by deptno) t JOIN salgrade
where sal1>losal and sal1<hisal

4. 不准用组函数（MAX），取得最高薪水（给出两种解决方案）
    select max(sal) from emp;

    1. 通过排序（降序），取出第一条
        select DISTINCT ENAME,DEPTNO,sal
from emp

ORDER BY SAL DESC
    2. limit 限制查询，限制查询得到的结果条数，一般用来做分页查询
    
    语法
    select * from xxx limit start,count;,如果start为0时，可以省略
select DISTINCT ENAME,DEPTNO,sal
from emp
ORDER BY SAL DESC
limit 1;

    2. 自连接的方案   select * from a join a on xxxx

5. 取得平均薪水最高的部门的部门编号
    1.  每个部门的平均薪水
        
    2. 最高的是多少

select *
FROM
(select deptno,avg(sal) as sal1
from emp
group by deptno) t
ORDER BY sal1 DESC
limit 1
6.取得平均薪水最高的部门的部门名称
select t.DEPTNO,sal1,ENAME
FROM
(select deptno,avg(sal) as sal1
from emp
group by deptno) t join emp
ON emp.DEPTNO=t.deptno
ORDER BY sal1 DESC
limit 1

7.求平均薪水的等级最低的部门的部门名称
    7.1求平均薪水等级（见3）

    7.2输出最低等级的部门名称
        （见6）
select *
FROM
(select deptno,ENAME,avg(sal) as sal1
from emp
group by deptno) t join salgrade
where sal1 BETWEEN losal and hisal
8.取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名
    8.1查询普通员工及其薪水
        注：
        not in不会自动忽略空值
        in会自动忽略空值
    8.2查询普通员工的最高薪资

    8.3 查询高于普通员工的最高薪资的mgr
SELECT *
from emp
where
sal>(SELECT max(sal)
from emp
where empno not in (select distinct MGR from emp where MGR is not null))

9.取得薪水最高的前五名员工
select DISTINCT ENAME,DEPTNO,sal
from emp
ORDER BY SAL DESC
limit 5
10.取得薪水最高的第六到第十名员工
select DISTINCT ENAME,DEPTNO,sal
from emp
ORDER BY SAL DESC
limit 6,10

11.取得最后入职的5名员工
select DISTINCT ENAME,DEPTNO,sal,HIREDATE
from emp
ORDER BY HIREDATE DESC
limit 5

 

12.取得每个薪水等级有多少员工
    SELECT count(empno)
from
(select empno,sal
from emp)t join salgrade
where sal BETWEEN losal and hisal
GROUP BY grade

14.列出所有员工及领导的名字
    
SELECT a.empno,a.ENAME,b.EMPNO as leaderno,b.ENAME as leadername
from emp as a,emp as b
WHERE b.EMPNO=a.MGR

15.列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
SELECT a.empno,a.ENAME,a.DEPTNO
from emp as a,emp as b
WHERE b.EMPNO=a.MGR and a.HIREDATE<b.HIREDATE

16.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT DNAME,EMPNO,ENAME,job,HIREDATE
FROM emp RIGHT outer JOIN dept on emp.DEPTNO=dept.DEPTNO;

17.列出至少有5个员工的所有部门
SELECT DNAME,count(EMPNO) as jishu
FROM emp,dept
where emp.DEPTNO=dept.DEPTNO
GROUP BY DNAME
HAVING jishu>=5

18.列出薪水比“SMITH”多的所有员工信息

select *
from emp
where sal>(select sal
from emp
where ename='SMITH')
19.列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数
19.1查询clerk员工信息
(select *
from emp
where job='clerk')

19.2求出每个部门的员工数量

19.3 以上两个查询结果连接输出题目信息
SELECT DNAME,count(EMPNO) as jishu,ENAME
FROM emp,dept
where emp.DEPTNO=dept.DEPTNO and emp.JOB='clerk'
GROUP BY DNAME

20.列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
SELECT count(empno) as number,JOB,min(sal) as zuidi
from emp
GROUP BY JOB
HAVING zuidi>1500

21.列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部门的部门编号】
select DISTINCT ename
from emp,dept
where emp.DEPTNO=dept.DEPTNO and DNAME='sales'
22.列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级
SELECT  e.ename as empname, d.dname, s.grade, b.ename as leadername  
FROM  emp e  
JOIN  salgrade s  
ON  e.sal BETWEEN s.losal AND s.hisal  
join  dept d  
on  d.DEPTNO = e.DEPTNO  
left join  emp b  
on  e.mgr = b.empno  
WHERE  
 e.sal > (SELECT avg(sal) FROM emp);  
23.列出与“SCOTT”从事相同工作的所有员工及部门名称
SELECT a.ENAME,a.DEPTNO,d.DNAME
from emp as a,emp as b
join dept d
ON d.DEPTNO=b.DEPTNO
where a.JOB=b.JOB and b.ENAME='SCOTT'
24.列出薪金等于部门30中员工的薪金的其它员工的姓名和薪金
SELECT ENAME,SAL
from emp
WHERE SAL in
(
SELECT  DISTINCT SAL
from emp  
where DEPTNO=30
)
and DEPTNO!=30
25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
SELECT ENAME,SAL,d.DNAME
from emp e
JOIN dept d
on d.DEPTNO=e.deptno
WHERE SAL >
(
SELECT  max(SAL)
from emp  
where DEPTNO=30
)
AND  e.deptno != 30;  

26.列出在每个部门工作的员工数量、平均工资和平均服务期限
    to_days(日期类型) -> 天数
    获取数据库的系统当前时间的函数是:now()
 SELECT   d.deptno,count(e.ename) as totalemp,ifnull(avg(e.sal), 0) as avgsal,ifnull(avg((to_days(now())-to_days(hiredate))/365), 0) as avgtime  
FROM  dept d  
LEFT outer JOIN  
 emp e  
on  
d.deptno = e.deptno  
group by  
 d.deptno;  
27.列出所有员工的姓名、部门名称和工资
SELECT DISTINCT e.ENAME,e.SAL,d.DNAME
from emp e
left JOIN
dept d
on d.DEPTNO=e.DEPTNO
28.列出所有部门的详细信息和人数
SELECT count(EMPNO),dept.DEPTNO,dept.DNAME,dept.LOC
from emp
join dept
on dept.DEPTNO=emp.DEPTNO
GROUP BY DEPTNO
29.列出各种工作的最低工资及从事此工作的雇员姓名
SELECT min(sal),JOB,ENAME
from emp
GROUP BY job
30.列出各个部门MANAGER的最低薪金
SELECT min(sal),DEPTNO
from emp
where JOB='MANAGER'
GROUP BY DEPTNO
31.列出所有员工的年工资，按年薪从低到高排序
select a1.sal*12 ,a2.dname
from emp a1,dept a2
where a1.deptno = a2.deptno order by a1.sal*12;
32.求出员工领导的薪水超过3000的员工名称和领导名称
SELECT a.ENAME ,b.ENAME as leader
from emp as a,emp as b
WHERE b.EMPNO=a.MGR and b.sal>3000
33.求部门名称中带“S”字符的部门员工的工资合计、部门人数
SELECT sum(sal),count(EMPNO),dept.DNAME
from emp,dept
where dept.DNAME LIKE '%s%'
GROUP BY dept.DNAME
 
34.给任职日期超过30年的员工加薪10%    
SELECT ENAME,(sal+sal/10) as sal1
from emp a
where a.EMPNO in
(SELECT e.EMPNO
from emp e
WHERE timestampdiff(year,HIREDATE,now())>30)

21.列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部门的部门编号】
select DISTINCT ename
from emp,dept
where emp.DEPTNO=dept.DEPTNO and DNAME='sales'
22.列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级
SELECT  e.ename as empname, d.dname, s.grade, b.ename as leadername  
FROM  emp e  
JOIN  salgrade s  
ON  e.sal BETWEEN s.losal AND s.hisal  
join  dept d  
on  d.DEPTNO = e.DEPTNO  
left join  emp b  
on  e.mgr = b.empno  
WHERE  
 e.sal > (SELECT avg(sal) FROM emp);  
23.列出与“SCOTT”从事相同工作的所有员工及部门名称
SELECT a.ENAME,a.DEPTNO,d.DNAME
from emp as a,emp as b
join dept d
ON d.DEPTNO=b.DEPTNO
where a.JOB=b.JOB and b.ENAME='SCOTT'
24.列出薪金等于部门30中员工的薪金的其它员工的姓名和薪金
SELECT ENAME,SAL
from emp
WHERE SAL in
(
SELECT  DISTINCT SAL
from emp  
where DEPTNO=30
)
and DEPTNO!=30
25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
SELECT ENAME,SAL,d.DNAME
from emp e
JOIN dept d
on d.DEPTNO=e.deptno
WHERE SAL >
(
SELECT  max(SAL)
from emp  
where DEPTNO=30
)
AND  e.deptno != 30;  

26.列出在每个部门工作的员工数量、平均工资和平均服务期限
    to_days(日期类型) -> 天数
    获取数据库的系统当前时间的函数是:now()
 SELECT   d.deptno,count(e.ename) as totalemp,ifnull(avg(e.sal), 0) as avgsal,ifnull(avg((to_days(now())-to_days(hiredate))/365), 0) as avgtime  
FROM  dept d  
LEFT outer JOIN  
 emp e  
on  
d.deptno = e.deptno  
group by  
 d.deptno;  
27.列出所有员工的姓名、部门名称和工资
SELECT DISTINCT e.ENAME,e.SAL,d.DNAME
from emp e
left JOIN
dept d
on d.DEPTNO=e.DEPTNO
28.列出所有部门的详细信息和人数
SELECT count(EMPNO),dept.DEPTNO,dept.DNAME,dept.LOC
from emp
join dept
on dept.DEPTNO=emp.DEPTNO
GROUP BY DEPTNO
29.列出各种工作的最低工资及从事此工作的雇员姓名
SELECT min(sal),JOB,ENAME
from emp
GROUP BY job
30.列出各个部门MANAGER的最低薪金
SELECT min(sal),DEPTNO
from emp
where JOB='MANAGER'
GROUP BY DEPTNO
31.列出所有员工的年工资，按年薪从低到高排序
select a1.sal*12 ,a2.dname
from emp a1,dept a2
where a1.deptno = a2.deptno order by a1.sal*12;
32.求出员工领导的薪水超过3000的员工名称和领导名称
SELECT a.ENAME ,b.ENAME as leader
from emp as a,emp as b
WHERE b.EMPNO=a.MGR and b.sal>3000
33.求部门名称中带“S”字符的部门员工的工资合计、部门人数
SELECT sum(sal),count(EMPNO),dept.DNAME
from emp,dept
where dept.DNAME LIKE '%s%'
GROUP BY dept.DNAME

ps:百分之90多应该都是稳的，不排除有一到两道题有问题。。。