EMP+DEPT+SALGRADE 表的基本操作2

distinct 去重

关于查询结果集的去重

distinct

mysql> SELECT distinct job from EMP;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 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,e.deptno from EMP e ,DEPT d where e.deptno = d.deptno;
+--------+--------+
| ename  | deptno |
+--------+--------+
| CLARK  |     10 |
| KING   |     10 |
| MILLER |     10 |
| SMITH  |     20 |
| JONES  |     20 |
| SCOTT  |     20 |
| ADAMS  |     20 |
| FORD   |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| MARTIN |     30 |
| BLAKE  |     30 |
| TURNER |     30 |
| JAMES  |     30 |
+--------+--------+
14 rows in set (0.00 sec)

内连接之等值连接

最大特点是:条件是等量关系

查询每个员工的部门名称,要求显示员工名和部门名

1、找出每一个员工的部门名称,要求显示员工名和部门名

表A join 表B ... on(连接条件)
好处:连接条件后面可以加where判断

mysql> select e.ename,d.deptno from EMP e join DEPT d on e.deptno = d.deptno;
+--------+--------+
| ename  | deptno |
+--------+--------+
| CLARK  |     10 |
| KING   |     10 |
| MILLER |     10 |
| SMITH  |     20 |
| JONES  |     20 |
| SCOTT  |     20 |
| ADAMS  |     20 |
| FORD   |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| MARTIN |     30 |
| BLAKE  |     30 |
| TURNER |     30 |
| JAMES  |     30 |
+--------+--------+
14 rows in set (0.00 sec)
---------------------------------加上where---------------------------
mysql>  select e.ename,d.deptno from EMP e join DEPT d  on e.deptno = d.deptno where d.deptno=10;
+--------+--------+
| ename  | deptno |
+--------+--------+
| CLARK  |     10 |
| KING   |     10 |
| MILLER |     10 |
+--------+--------+
3 rows in set (0.00 sec)

2、找出每个员工的工资等级,要求显示员工名,工资,工资等级

mysql> select e.ename,e.sal,s.grade from EMP e join  SALGRADE s on e.sal between s.losal and s.hisal;
+--------+------+-------+
| ename  | sal  | grade |
+--------+------+-------+
| SMITH  |  800 |     1 |
| ALLEN  | 1600 |     3 |
| WARD   | 1250 |     2 |
| JONES  | 2975 |     4 |
| MARTIN | 1250 |     2 |
| BLAKE  | 2850 |     4 |
| CLARK  | 2450 |     4 |
| SCOTT  | 3000 |     4 |
| KING   | 5000 |     5 |
| TURNER | 1500 |     3 |
| ADAMS  | 1100 |     1 |
| JAMES  |  950 |     1 |
| FORD   | 3000 |     4 |
| MILLER | 1300 |     2 |
+--------+------+-------+
14 rows in set (0.00 sec)

自连接

特点:一张表显示两张表,自己连接自己

1、找出每个员工的上级领导，要求显示员工名和对应的领导名

mysql> select a.ename "员工名",b.ename "领导名" from EMP a inner join EMP b on a.empno = b.MGR;
+-----------+-----------+
| 员工名    | 领导名    |
+-----------+-----------+
| FORD      | SMITH     |
| BLAKE     | ALLEN     |
| BLAKE     | WARD      |
| KING      | JONES     |
| BLAKE     | MARTIN    |
| KING      | BLAKE     |
| KING      | CLARK     |
| JONES     | SCOTT     |
| BLAKE     | TURNER    |
| SCOTT     | ADAMS     |
| BLAKE     | JAMES     |
| JONES     | FORD      |
| CLARK     | MILLER    |
+-----------+-----------+
13 rows in set (0.00 sec)

外连接(左连接/右连接)

什么是外连接,和内连接有什么区别?

内连接:

  假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接
  AB两张表没有主副之分,两张表示平等的

外连接:

  假设A和B表进行连接,使用外连接的话,AB两张表中有一张是主表,一张是副表,主要查询主表中的数据
  ,顺便查询副表中的数据没有喝主表的数据匹配上,副表自动模拟出null与之匹配

1、找出每个员工的上级领导，要求显示员工名和对应的领导名

mysql> select a.ename "员工编号",b.ename "大佬" from EMP a left join EMP b on a.empno= b.mgr;
+--------------+--------+
| 员工编号     | 大佬   |
+--------------+--------+
| FORD         | SMITH  |
| BLAKE        | ALLEN  |
| BLAKE        | WARD   |
| KING         | JONES  |
| BLAKE        | MARTIN |
| KING         | BLAKE  |
| KING         | CLARK  |
| JONES        | SCOTT  |
| BLAKE        | TURNER |
| SCOTT        | ADAMS  |
| BLAKE        | JAMES  |
| JONES        | FORD   |
| CLARK        | MILLER |
| SMITH        | NULL   |
| ALLEN        | NULL   |
| WARD         | NULL   |
| MARTIN       | NULL   |
| TURNER       | NULL   |
| ADAMS        | NULL   |
| JAMES        | NULL   |
| MILLER       | NULL   |
+--------------+--------+
21 rows in set (0.00 sec)

mysql> select a.ename "员工编号",b.ename "大佬" from EMP a right join EMP b on a.empno= b.mgr;
+--------------+--------+
| 员工编号     | 大佬   |
+--------------+--------+
| FORD         | SMITH  |
| BLAKE        | ALLEN  |
| BLAKE        | WARD   |
| KING         | JONES  |
| BLAKE        | MARTIN |
| KING         | BLAKE  |
| KING         | CLARK  |
| JONES        | SCOTT  |
| NULL         | KING   |
| BLAKE        | TURNER |
| SCOTT        | ADAMS  |
| BLAKE        | JAMES  |
| JONES        | FORD   |
| CLARK        | MILLER |
+--------------+--------+
14 rows in set (0.00 sec)

2、找出哪个部门没有员工

mysql> select d.* from EMP e right join DEPT d on e.deptno=d.deptno where e.ename is null;
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)

3、找出每一个员工的部门名称以及工资等级

涉及以下三张表

mysql> select * from EMP;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)

mysql> select * from DEPT;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> select e.ename,d.dname,s.grade from EMP e join DEPT d  on e.deptno=d.deptno join SALGRADE s on e.sal between losal andd 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)

4、找出每一个员工的部门名称,工资等级,以及上司领导

mysql> select e.ename "员工",d.dname,s.grade,e1.ename "领导" from EMP e join DEPT d  on e.deptno=d.deptno join SALGRADE s on e.sal between losal and hisal left join EMP e1 on e1.empno = e.mgr;
+--------+------------+-------+--------+
| 员工   | dname      | grade | 领导   |
+--------+------------+-------+--------+
| SMITH  | RESEARCH   |     1 | FORD   |
| ALLEN  | SALES      |     3 | BLAKE  |
| WARD   | SALES      |     2 | BLAKE  |
| JONES  | RESEARCH   |     4 | KING   |
| MARTIN | SALES      |     2 | BLAKE  |
| BLAKE  | SALES      |     4 | KING   |
| CLARK  | ACCOUNTING |     4 | KING   |
| SCOTT  | RESEARCH   |     4 | JONES  |
| KING   | ACCOUNTING |     5 | NULL   |
| TURNER | SALES      |     3 | BLAKE  |
| ADAMS  | RESEARCH   |     1 | SCOTT  |
| JAMES  | SALES      |     1 | BLAKE  |
| FORD   | RESEARCH   |     4 | JONES  |
| MILLER | ACCOUNTING |     2 | CLARK  |
+--------+------------+-------+--------+
14 rows in set (0.00 sec)

5、找出高于平均薪资的员工信息（用where字句中使用子查询）

第一步 找出平均薪资

mysql> select avg(sal) from EMP;
+-------------------+
| avg(sal)          |
+-------------------+
| 2073.214285714286 |
+-------------------+
1 row in set (0.00 sec)

第二步 where过滤

mysql> select ename,sal from EMP where sal > 2073.214285714286;
+-------+------+
| ename | sal  |
+-------+------+
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| SCOTT | 3000 |
| KING  | 5000 |
| FORD  | 3000 |
+-------+------+
6 rows in set (0.00 sec)

合并

mysql> select ename,sal from EMP where sal > (select avg(sal) from EMP);
+-------+------+
| ename | sal  |
+-------+------+
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| SCOTT | 3000 |
| KING  | 5000 |
| FORD  | 3000 |
+-------+------+
6 rows in set (0.00 sec)

6、找出每个部门平均薪资的薪资等级（from后面嵌套子查询）

mysql> select t.*,s.grade 
    -> from (select deptno,avg(sal) avgsal from EMP group by deptno) t
    -> join SALGRADE s on t.avgsal between s.losal and s.hisal;
+--------+--------------------+-------+
| deptno | avgsal             | grade |
+--------+--------------------+-------+
|     10 | 2916.6666666666665 |     4 |
|     20 |               2175 |     4 |
|     30 | 1566.6666666666667 |     3 |
+--------+--------------------+-------+
3 rows in set (0.00 sec)