mysql查询语句实例

– SELECT e.ENAME,d.DNAME from emp as e,dept as d

– SELECT e.ENAME,d.DNAME,s.GRADE,e1.ENAME FROM emp as e JOIN dept as d on

e.DEPTNO=d.DEPTNO JOIN salgrade as s on e.SAL BETWEEN s.LOSAL and s.HISAL LEFT JOIN emp e1

on e.MGR=e1.EMPNO

– 取得每个部门最高薪水的人员名称

SELECT e.ename,t.*
from emp e JOIN (SELECT deptno,MAX(sal) as maxsal FROM emp GROUP BY deptno) t
on t.deptno = e.DEPTNO and t.maxsal=e.SAL

– 哪些人的薪水在部门的平均薪水之上
– SELECT deptno,AVG(sal) as avgsal FROM emp GROUP BY deptno

SELECT e.ENAME,e.SAL,t.* FROM
emp as e JOIN
(SELECT deptno,AVG(sal) as avgsal FROM emp GROUP BY deptno) t
ON e.DEPTNO = t.deptno and e.sal > t.avgsal

– 取得部门中（所有人的）平均的薪水等级

SELECT e.DEPTNO,AVG(s.GRADE) from emp as e JOIN
salgrade as s on e.SAL BETWEEN s.LOSAL AND s.HISAL GROUP BY e.DEPTNO

– 取得平均薪水最高的部门的部门编号

方案一；
– SELECT e.DEPTNO,AVG(e.SAL) as avgsal from emp as e GROUP BY e.DEPTNO ORDER BY avgsal

desc LIMIT 1

方案二:
SELECT MAX(t.avgsal) FROM (SELECT e.DEPTNO,AVG(e.SAL) as avgsal from emp as e GROUP BY

e.DEPTNO) t

– 取得平均薪水最高的部门的部门名称

SELECT d.DNAME,e.DEPTNO,AVG(e.SAL) as avgsal
from emp as e
JOIN dept d ON e.DEPTNO=d.DEPTNO
GROUP BY e.DEPTNO ORDER BY avgsal
desc LIMIT 1

– 求平均薪水的等级最低的部门的部门名称
SELECT
t.*,s.grade
FROM
(SELECT d.DNAME,AVG(sal) as avgsal from emp e JOIN dept d ON e.DEPTNO=d.DEPTNO GROUP BY e.DEPTNO) t
JOIN
salgrade s
ON t.avgsal BETWEEN s.LOSAL AND s.HISAL WHERE s.GRADE=(SELECT grade FROM salgrade WHERE ((select avg(sal) as avgsal from emp GROUP BY deptno ORDER BY avgsal asc limit 1) BETWEEN losal and hisal)
)

– 列出所有“CLERK”的姓名和部门名称，部门人数

SELECT t1.,t2.department
FROM (SELECT e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno=d.deptno where e.job=‘CLERK’) t1
JOIN (SELECT deptno,COUNT() as department FROM emp GROUP BY deptno) t2
ON t1.deptno=t2.deptno