Mysql函数题库练习

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.ame,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.列出所有员工及领导的名字
    
pno,a.ENAME,b.EMPNO as leaderno,b.ENAME as leadername
from emp as a,emp as b
WHERE b.EMPNO=a.MGR

15.列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
pno,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,ame) 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,ame) 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多应该都是稳的，不排除有一到两道题有问题。。。