SQL语句，查询操作

#查询所有员工的姓名
SELECT ename FROM emp;
#查询员工的薪水和姓名
SELECT ename , sal FROM emp;
#查询员工表所有数据
SELECT * FROM emp;
#查询每一个员工的年薪
SELECT ename , sal*12 AS yearSal FROM emp;
#查询薪资为5000的员工的信息
SELECT * FROM emp
WHERE sal = 5000;
#查询职位为MANAGER的员工的信息
SELECT * FROM emp
WHERE job = "MANAGER";
#查询薪资不是5000的员工的信息
SELECT * FROM emp
WHERE sal != 5000;
#查询职位不是MANAGER的员工的信息
SELECT * FROM emp
WHERE job != "MANAGER";
#查询薪资大于2000的员工的信息
SELECT * FROM emp
WHERE sal > 2000;
#查询薪资在1000到2000之间的员工的信息
SELECT * FROM emp
WHERE sal >= 1000 && sal <=2000;
#查询部门编号为20，30的部门的信息
SELECT * FROM dept
WHERE deptno = 20 or deptno = 30;
#查询部门编号为20，30的部门的信息
SELECT * FROM dept
WHERE deptno = 20 or deptno = 30;
#查询部门编号不是20，30的部门的信息
SELECT * FROM dept
WHERE deptno != 20 AND deptno != 30;
#查询没有补助的员工的信息
SELECT * FROM emp
WHERE comn IS NULL;
#查有补助的员工的信息
SELECT * FROM emp
WHERE comn != "";
#查询姓名中第一个字母带有s的员工的信息
SELECT * FROM emp
WHERE ename LIKE "S%";
#查询姓名中第二个字母带有m的员工的信息
SELECT * FROM emp
WHERE ename LIKE "_M%";
#查询姓名中带有字母m的员工的信息
SELECT * FROM emp
WHERE ename LIKE "%M%";
#查询员工信息并根据员工的薪水（升序）
SELECT * FROM emp
ORDER BY sal ASC;
#查询员工信息并根据员工的薪水（降序）
SELECT * FROM emp
ORDER BY sal DESC;
#查询员工信息并根据员工入职日期降序排列
SELECT * FROM emp
ORDER BY hiredate DESC;
#查询职位为MANAGER 的员工信息,并且按照薪资从高到低排序
SELECT * FROM emp
WHERE job = "MANAGER"
ORDER BY sal DESC;
#查询员工的最高工资
SELECT MAX(sal) AS "员工的最高工资" FROM emp;
#查询员工的最低工资
SELECT MIN(sal) AS "员工的最低工资" FROM emp;
#查询当前公司总员工数
SELECT COUNT(emno) AS "公司总员工数" FROM emp;
#查询所有员工的年支出
SELECT SUM(sal) AS "所有员工的年支出" FROM emp;
#查询员工平均工资-->有问题
SELECT AVG(sal) AS "员工平均工资" FROM emp;
#通过员工表查询公司有哪几个部门-->有问题
SELECT DISTINCT deptno AS "部门编号" FROM emp;
#查询每种工作的最高薪资
SELECT job,sal AS "maxSal" FROM emp
GROUP BY job
HAVING MAX(sal);
#查询每种工作的最高薪资并升序显示
SELECT job,sal AS "maxSal" FROM emp
GROUP BY job
HAVING MAX(sal)
ORDER BY sal ASC;
#查询每个部门的平均薪资
SELECT deptno,sal AS "平均工资" FROM emp
GROUP BY deptno
HAVING AVG(sal);
#查询平均工资大于2000的岗位的平均工资 -->有问题
SELECT job,sal AS "平均工资" FROM emp
GROUP BY deptno
HAVING AVG(sal)>=2000;
#查询当前员工中工资排在前三位的人的信息
SELECT * FROM emp
ORDER BY sal DESC
LIMIT 3;
#查询员工表数据并进行分页，每页显示5条数据，查看第一页的数据

#找出薪水比公司平均薪水高的员工,要求显示员工的名字和薪水 --->有问题
SELECT ename,sal FROM emp
GROUP BY ename
HAVING  sal>AVG(sal);