[大数据]MySQL学习笔记（3）

连接查询分类

1. 内连接
   （1）等值连接：等号连接
   （2）非等值连接：除等号之外的操作符连接
   （3）自连接：一张表当作多张表来连接
2. 外连接
   （1）左外连接：左表为主表，右表可能为null
   （2）右外连接：右表为主表，左表可能为null
3. 全连接

内连接和外连接的区别是：

1. 内连接：只显示两个表都匹配的数据（使用内连接会导致部分数据丢失）
2. 左外连接：显示左表的全部数据（不管两个表是否匹配），右表中没有匹配的数据用null
3. 右外连接：显示右表的全部数据（不管两个表是否匹配），左表中没有匹配的数据用null

1. 等值连接

// 查询每个员工的部门名
// inner可以省略
mysql> select e.ename, e.deptno, d.dname
    -> from emp e
    -> inner join dept d
    -> on e.deptno = d.deptno;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+
14 rows in set (0.00 sec)

2. 非等值连接

// 找出员工的工资等级
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.00 sec)

3. 自连接

// 找出每个员工的上级
// 有13条记录，因为king没有上级领导
mysql> select e1.ename, e2.ename as mgrname 
    -> from emp e1 
    -> inner join emp e2 
    -> on e1.mgr = e2.empno;
+--------+---------+
| ename  | mgrname |
+--------+---------+
| SMITH  | FORD    |
| ALLEN  | BLAKE   |
| WARD   | BLAKE   |
| JONES  | KING    |
| MARTIN | BLAKE   |
| BLAKE  | KING    |
| CLARK  | KING    |
| SCOTT  | JONES   |
| TURNER | BLAKE   |
| ADAMS  | SCOTT   |
| JAMES  | BLAKE   |
| FORD   | JONES   |
| MILLER | CLARK   |
+--------+---------+
13 rows in set (0.00 sec)

4. 外连接

// 1. 找出所有员工的上级领导
mysql> select e1.ename, e2.ename as mgrname 
    -> from emp e1 
    -> left join emp e2 
    -> on e1.mgr = e2.empno;
+--------+---------+
| ename  | mgrname |
+--------+---------+
| SMITH  | FORD    |
| ALLEN  | BLAKE   |
| WARD   | BLAKE   |
| JONES  | KING    |
| MARTIN | BLAKE   |
| BLAKE  | KING    |
| CLARK  | KING    |
| SCOTT  | JONES   |
| KING   | NULL    |
| TURNER | BLAKE   |
| ADAMS  | SCOTT   |
| JAMES  | BLAKE   |
| FORD   | JONES   |
| MILLER | CLARK   |
+--------+---------+
14 rows in set (0.00 sec)

// 2. 找出哪个部门没有员工
mysql> select d.* from dept d 
    -> left join emp e 
    -> on d.deptno = e.deptno 
    -> where e.ename is null;
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)

5. 三表查询

// 1. 找出每个员工的部门名称、工资等级和上级领导
mysql> select e.ename, d.dname, s.grade,ee.ename as mgrname 
    -> from emp e 
    -> join dept d 
    -> on e.deptno = d.deptno 
    -> join salgrade s 
    -> on e.sal between s.losal and s.hisal
    -> left join emp ee 
    -> on e.mgr = ee.empno;
+--------+------------+-------+---------+
| ename  | dname      | grade | mgrname |
+--------+------------+-------+---------+
| 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)

6. 子查询

// 1. 查找高于平均工资的员工信息
// where后面嵌套子查询
mysql> select * 
    -> from emp 
    -> where sal > (select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)

// 2. 找出每个部门的平均工资的工资等级
// from后面嵌套子查询
mysql> select a.*, s.grade 
    -> from (select deptno, avg(sal) as avgsal from emp group by deptno) a 
    -> join salgrade s 
    -> on a.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

// 3. 找出每个部门的平均工资等级
// 下面是错误示范
mysql> select t.deptno, avg(t.grade) 
    -> from (select e.ename,e.deptno, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t 
    -> group by t.deptno;
    
// 正确的写法如下：求出员工的工资等级之后直接分组即可求得等级平均值，不需要作再为一张表
mysql> select e.deptno, avg(s.grade) 
    -> from emp e 
    -> join salgrade s 
    -> on e.sal between s.losal and s.hisal 
    -> group by e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)

7. union操作

// 查找岗位是salseman和manager的员工
// 第一种
mysql> select ename, job 
    -> from emp 
    -> where job = 'salesman' or job = 'manager';
// 第二种
mysql> select ename, job 
    -> from emp 
    -> where job in('salesman', 'manager');
// 第三种
mysql> select ename, job from emp where job = 'salesman'
    -> union
    -> select ename, job from emp where job = 'manager';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
+--------+----------+
7 rows in set (0.00 sec)

8. limit操作

// 1. 取出工资前5名的员工
mysql> select ename, sal 
    -> from emp 
    -> order by sal desc 
    -> limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

// 2. 取出工资第4到第9的员工
mysql> select ename, sal 
    -> from emp 
    -> order by sal 
    -> limit 3, 6;
+--------+---------+
| ename  | sal     |
+--------+---------+
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
+--------+---------+
6 rows in set (0.00 sec)