mysql 常用的命令2

找出每个部门，不同工作岗位的最高薪资。
mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
9 rows in set (0.01 sec)

每个部门最高薪资
mysql> select deptno, max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

找出每个部门最高薪资，并且查出其中大于2900的
mysql> select deptno,max(sal) from emp where sal > 2900 group by deptno;
+--------+----------+
| deptno | max(sal) | 这样写效率较高
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
+--------+----------+
2 rows in set (0.00 sec)

mysql> select deptno, max(sal) from emp group by deptno having max(sal) > 2900;
+--------+----------+
| deptno | max(sal) | 使用having效率较低
+--------+----------+ having是对于分组后的过滤，只能联合使用，不能单独拿出来使用。
| 10 | 5000.00 |
| 20 | 3000.00 |
+--------+----------+
2 rows in set (0.00 sec)

找出每个部门的平均薪资

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

先找出每个部门的平均薪资，要求显示其中大于2000的。
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
+--------+-------------+
2 rows in set (0.00 sec)

distinct可以去除重复字段，出现在字段最前面，去除所有字段重复。
mysql> select job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| SALESMAN |
| MANAGER |
| SALESMAN |
| MANAGER |
| MANAGER |
| ANALYST |
| PRESIDENT |
| SALESMAN |
| CLERK |
| CLERK |
| ANALYST |
| CLERK |
+-----------+
14 rows in set (0.00 sec)

mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

mysql> select deptno,job from emp;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | SALESMAN |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | SALESMAN |
| 20 | CLERK |
| 30 | CLERK |
| 20 | ANALYST |
| 10 | CLERK |
+--------+-----------+
14 rows in set (0.00 sec)

mysql> select distinct deptno,job from emp;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
+--------+-----------+
9 rows in set (0.00 sec)

统计岗位的数量
mysql> select count( distinct job) from emp;
+----------------------+
| count( distinct job) |
+----------------------+
| 5 |
+----------------------+
1 row in set (0.00 sec)

查询员工名称和部门名称，使用内连接查询。
mysql> select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
14 rows in set (0.01 sec)

查询员工的薪资等级，显示员工名，薪资，薪资等级
mysql> select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.01 sec)

查询每个员工的上级，要求显示员工，上级的名字，
这里使用自连接，一张表看成两张表，一个员工表，一个上级表。

查询每个员工的薪资等级和部门名称
mysql> select e.ename , d.dname , s.grade from emp e join dept d join salgrade s on e.deptno = d.deptno and e.sal between s.losal and s.hisal;
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+
14 rows in set (0.00 sec)

select
e.ename,d.dname,s.grade
from
emp e 这里是三张表查询。先让emp 和 dept结合
join 然后让emp 和 salgrade结合查询。
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and hisal;

+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+
14 rows in set (0.00 sec)

查询每个员工的部门名称，工资等级和上级领导

select
e1.ename as \'员工\',d.dname,s.grade,e2.ename as \'上级\'
from
emp e1
join
dept d
on
e1.deptno = d.deptno
join
salgrade s
on
e1.sal between s.losal and s.hisal
left join 这里注意的是left放在后面才能起作用。
emp e2
on
e1.mgr = e2.empno;

+--------+------------+-------+-------+