mysql表查询相关语句例子

一、单表查询
1. 查询出部门编号为30的所有员工  
SELECT * FROM emp WHERE deptno=30;
2. 所有销售员的姓名、编号和部门编号。
SELECT ename,empno,deptno FROM emp;
3. 找出奖金高于工资的员工。
SELECT * FROM emp WHERE comm>sal ;
4. 找出奖金高于工资60%的员工。
SELECT * FROM emp WHERE comm>sal*0.6 ;
5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。
SELECT * FROM emp  WHERE (deptno=20 AND job='销售人员') OR (deptno=10 AND job='经理');
7. 有奖金的工种。
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;
6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
SELECT * FROM emp  WHERE (deptno=20 AND job='销售人员') OR (deptno=10 AND job='经理') OR(job NOT IN('经理','销售人员')AND sal>=20000);
8. 无奖金或奖金低于1000的员工。
SELECT * FROM emp WHERE comm IS NULL OR comm<1000;
9. 查询名字由三个字组成的员工。
SELECT * FROM emp WHERE ename LIKE '___';
10.查询2000年入职的员工。
SELECT * FROM emp WHERE hiredate LIKE '200%';
11. 查询所有员工详细信息，用编号升序排序
SELECT * FROM emp ORDER BY empno ASC;
12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
SELECT * FROM emp ORDER BY sal DESC,hiredate ASC;
13. 查询每个部门的平均工资
SELECT deptno,AVG(sal) '平均工资' FROM emp GROUP BY deptno;
14. 查询每个部门的雇员数量。 
SELECT deptno,COUNT(*) '人数' FROM emp GROUP BY deptno;
15. 查询每种工作的最高工资、最低工资、人数
SELECT job,MAX(sal) '最高工资',MIN(sal) '最低工资',COUNT(*) '人数' FROM emp GROUP BY job;
16. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列
SELECT job,SUM(sal) '月工资总和'  FROM emp WHERE job<>'销售员' GROUP BY job HAVING SUM(sal)>50000 ORDER BY SUM(sal) ASC;
 

二多表查询与子查询

/*
1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/

SELECT d.deptno,d.dname,d.loc,e.num FROM dept d INNER JOIN (SELECT deptno,COUNT(empno) num FROM emp e GROUP BY deptno) e ON e.deptno=d.deptno;

/*
2. 列出薪金比关羽高的所有员工。*/
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='关羽');
/*
3. 列出所有员工的姓名及其直接上级的姓名。*/
SELECT e.ename,IFNULL(l.ename,'boss') leader FROM emp e LEFT JOIN emp l ON l.empno=e.mgr;
/*
4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
SELECT s.*,d.dname FROM (SELECT e.empno,e.ename,e.deptno FROM emp e INNER JOIN emp l WHERE e.mgr=l.empno AND e.hiredate<l.hiredate) s
INNER JOIN dept d ON d.deptno=s.deptno;
;
/*
5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/
SELECT d.dname,e.* FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno;
/*
6. 列出所有文员的姓名及其部门名称，部门的人数。
SELECT b.ename,IFNULL(b.dname,'boss') aname,IFNULL(a.num,0) number FROM (SELECT COUNT(s.deptno) num,s.deptno FROM emp s GROUP BY deptno) a
RIGHT JOIN 
(SELECT e.ename,d.dname,e.deptno FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno WHERE job='文员') b
ON a.deptno=b.deptno;*/

SELECT e.ename, d.dname, z.cnt
FROM emp e, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z, dept d
WHERE e.deptno=d.deptno AND z.deptno=d.deptno;

/*
7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
SELECT DISTINCT job FROM emp WHERE sal>15000*/
SELECT a.jobNum,b.* FROM (SELECT COUNT(job) jobNum,job FROM emp e GROUP BY job) a INNER JOIN (
SELECT job FROM emp GROUP BY job HAVING MIN(sal)>15000) b
ON a.job=b.job;

/*
8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/
SELECT e.ename FROM emp e INNER JOIN dept d ON d.deptno=e.deptno WHERE d.dname='销售部';
/*
9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

SELECT a.*,b.ename FROM (SELECT e.*,d.dname,s.grade FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal WHERE e.sal>(SELECT AVG(sal) FROM emp)) a
LEFT JOIN emp b ON a.mgr=b.empno;

/*
10.列出与庞统从事相同工作的所有员工及部门名称。*/
SELECT e.*,dname FROM emp e LEFT JOIN dept d ON d.deptno=e.deptno WHERE job=(SELECT job FROM emp WHERE ename='庞统') AND ename <>'庞统';

/*
11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/

SELECT e.ename,e.sal,d.dname FROM emp e LEFT JOIN dept d ON d.deptno=e.deptno WHERE sal>(SELECT MAX(sal) FROM emp GROUP BY deptno HAVING deptno=30);
/*
12.列出每个部门的员工数量、平均工资。*/
SELECT COUNT(*),AVG(sal) FROM emp GROUP BY deptno;