MySQL数据库之二
1、连接查询
1.1、简单描述
第一天说到了MySQL中的表，因为是关系型数据库，所以数据库中的表并非都是单一存在的，表和表之间还可能是存在关系的。而表的如此设计是有原因的，因为在实际的工作环境中，数据的存储要尽量简单，如果将所有的数据存储到一张表中去，那么将会造成数据冗余。
| stuno | stuname | classno | classname |
|---|---|---|---|
| 101 | 张三 | 1 | 广东省深圳市中心小学3年1班 |
| 102 | 李四 | 2 | 广东省深圳市中心小学3年2班 |
为了避免造成数据冗余，那么将进行拆表来进行存储
student表：
| classno | classname | |
|---|---|---|
| 1 | 广东省深圳市中心小学3年1班 | |
| 2 | 广东省深圳市中心小学3年2班 |
class表：
| stuno | stuname | classno |
|---|---|---|
| 101 | 张三 | 1 |
| 102 | 李四 | 2 |
可以看到张三的学号是101，班级编号是1，广东省深圳市中心小学3年1班；
所谓的连接查询，一般都是多张表联合查询取出得到最终的结果。
1.2、连接查询的分类
根据表的连接方式，包括：
1、内连接
等值连接(比如说=等)；
非等值连接（比如说between..and..）；
自连接（自己连接自己）
2、外连接
左外连接（左连接）；
右外连接（右连接）；
3、全连接（很少用）
2、 笛卡尔积
在表的连接查询中有一个现象叫做笛卡尔积。在两张表在进行连接查询的时候，如果没有任何条件的限制，最终查询出来的结果条数就会是两种表中记录的乘积。
笛卡尔发现集合A{a,b,c},B{d,e}在进行组合的时候，有多种结果{a,d},{a,e},{b,d},{b,e},{c,d},{c,e}，有2*3=6中结果，但是有时候根据需要，需要把自己需要的结果查询出来。比如说只需要查询出{a,e}，那么就需要根据条件来进行过滤掉其他的五种结果。
但是只要是连接查询，那么笛卡尔积现象就会一直存在。
比如说emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 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 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
dept表中
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
那么使用表连接查询的时候，将会产生4*14=56条记录
mysql> select ename,dname from emp,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
| WARD | ACCOUNTING |
| WARD | RESEARCH |
| WARD | SALES |
| WARD | OPERATIONS |
| JONES | ACCOUNTING |
| JONES | RESEARCH |
| JONES | SALES |
| JONES | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH |
| MARTIN | SALES |
| MARTIN | OPERATIONS |
| BLAKE | ACCOUNTING |
| BLAKE | RESEARCH |
| BLAKE | SALES |
| BLAKE | OPERATIONS |
| CLARK | ACCOUNTING |
| CLARK | RESEARCH |
| CLARK | SALES |
| CLARK | OPERATIONS |
| SCOTT | ACCOUNTING |
| SCOTT | RESEARCH |
| SCOTT | SALES |
| SCOTT | OPERATIONS |
| KING | ACCOUNTING |
| KING | RESEARCH |
| KING | SALES |
| KING | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH |
| TURNER | SALES |
| TURNER | OPERATIONS |
| ADAMS | ACCOUNTING |
| ADAMS | RESEARCH |
| ADAMS | SALES |
| ADAMS | OPERATIONS |
| JAMES | ACCOUNTING |
| JAMES | RESEARCH |
| JAMES | SALES |
| JAMES | OPERATIONS |
| FORD | ACCOUNTING |
| FORD | RESEARCH |
| FORD | SALES |
| FORD | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH |
| MILLER | SALES |
| MILLER | OPERATIONS |
+--------+------------+
56 rows in set (0.00 sec)
但是这56条记录里面，可以发现出现了错误的数据。比如说smith所在的部门居然出现了4个，明明Smith所处的部门就在RESEARCH，结果查询出来了错误的数据。所以此时的解决办法就是通过条件来进行筛选掉错误的或者是不需要的数据。
笛卡尔积是数据库表联合查询的底层实现原理。很多数据不是我们想要的，所以需要通过条件语句来进行过滤。
2.1、为什么要给表起别名
对比下下面两个SQL语句
select ename,dname from emp,dept;
select e.ename,d.dname from emp e,dept d
如果按照第一种方式来进行查询，在选择ename和dname的时候都会从两张表中来进行查询。如ename会在emp和dept表中进行查找，如果只找到了一个，那么就选择出来；如果找到了两个，那么就会报错。从第一个角度出发，直接指定表名中的字段，可以节省查询时间；从第二个角度来说明，避免了错误还可以从对应表中找出来对应的数据。
总结：
1、可读性好。可以明确知道是从哪张表中查询出来对应的数据；
2、效率快。避免去另外一张表中来进行查询。
2.2、表连接查询原理
还是拿上面的emp和dept表来进行举例说明
emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 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 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
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 e.ename,d.deptno,d.dname from emp e,dept d where e.deptno = d.deptno;
+--------+--------+------------+
| ename | deptno | dname |
+--------+--------+------------+
| CLARK | 10 | ACCOUNTING |
| KING | 10 | ACCOUNTING |
| MILLER | 10 | ACCOUNTING |
| SMITH | 20 | RESEARCH |
| JONES | 20 | RESEARCH |
| SCOTT | 20 | RESEARCH |
| ADAMS | 20 | RESEARCH |
| FORD | 20 | RESEARCH |
| ALLEN | 30 | SALES |
| WARD | 30 | SALES |
| MARTIN | 30 | SALES |
| BLAKE | 30 | SALES |
| TURNER | 30 | SALES |
| JAMES | 30 | SALES |
+--------+--------+------------+
14 rows in set (0.04 sec)
查询出数据之前还是56条数据，只不过加上了e.deptno = de.deptno之后，过滤掉了不相等的情况。比如说Smith减少了三条无效的记录条数，那么对于14条数据来说，减少的条数就是14*3=42条，那么剩下的就是14条的有效的数据。
Smith的deptno是20，那么在和dept表中的deptno中的值来进行比较的时候，发现只有20是符合的，10、30、40是不符合条件的。那么就减少了无效的数据记录。
总结：表的关联查询，A表中的每行记录都会和B表中的每行记录进行匹配，在此匹配的基础之上，加上我们的过滤条件来进行筛选，符合匹配的才是我们想要的数据，不符合的被过滤掉了，所以不去进行显示。也就是说，我们手动的去避免出现笛卡尔积现象，去掉无用的数据，得到有用的数据。
3、内连接
3.1、内连接的等值连接最大的特点就是连接条件是等量连接
还是拿上面的案例来进行说明
案例：查询出每个员工所属部门的名字和部门
select
e.ename,d.deptno,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;
+--------+--------+------------+
| ename | deptno | dname |
+--------+--------+------------+
| CLARK | 10 | ACCOUNTING |
| KING | 10 | ACCOUNTING |
| MILLER | 10 | ACCOUNTING |
| SMITH | 20 | RESEARCH |
| JONES | 20 | RESEARCH |
| SCOTT | 20 | RESEARCH |
| ADAMS | 20 | RESEARCH |
| FORD | 20 | RESEARCH |
| ALLEN | 30 | SALES |
| WARD | 30 | SALES |
| MARTIN | 30 | SALES |
| BLAKE | 30 | SALES |
| TURNER | 30 | SALES |
| JAMES | 30 | SALES |
+--------+--------+------------+
14 rows in set (0.00 sec)
查询出来的数据如上所示。使用e.deptno = d.deptno来有效的展示有效的数据。
但是官方说明，这种语法不够清晰的来描述一个SQL的意义的本质。推荐如下查询方式：
select
e.ename,d.deptno,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
+--------+--------+------------+
| ename | deptno | dname |
+--------+--------+------------+
| CLARK | 10 | ACCOUNTING |
| KING | 10 | ACCOUNTING |
| MILLER | 10 | ACCOUNTING |
| SMITH | 20 | RESEARCH |
| JONES | 20 | RESEARCH |
| SCOTT | 20 | RESEARCH |
| ADAMS | 20 | RESEARCH |
| FORD | 20 | RESEARCH |
| ALLEN | 30 | SALES |
| WARD | 30 | SALES |
| MARTIN | 30 | SALES |
| BLAKE | 30 | SALES |
| TURNER | 30 | SALES |
| JAMES | 30 | SALES |
+--------+--------+------------+
14 rows in set (0.00 sec)
为什么推荐这种方式？而不是第一种？
官方认为表连接避免笛卡尔积的现象的连接条件不应该和where后面的过滤条件放到一起。连接查询的条件应该和where的过滤条件区分开来
这个地方以前没有在意，现在越想越觉得有用。
select
e.ename,e.sal,d.deptno
from
emp e,dept d
where
e.deptno = d.deptno and e.sal > 3000;
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| KING | 5000.00 | 10 |
+-------+---------+--------+
1 row in set (0.00 sec)
虽然是可以查询出来数据，但是表连接查询的条件是哪一个？过滤条件是哪一个？这个是不清楚的。而使用
select
e.ename,d.deptno,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno
where
e.sal > 3000;
+-------+--------+------------+
| ename | deptno | dname |
+-------+--------+------------+
| KING | 10 | ACCOUNTING |
+-------+--------+------------+
1 row in set (0.00 sec)
这种方式可以很清晰地显示出表和表之间的连接条件以及过滤条件。所以推荐的是下面的这种方式来进行查询。
3.2、外连接的非等值连接最大的特点连接关系是非等量连接
案列1：找出每个员工的工资等级，要求显示每个员工的员工名、工资和工资等级
select e.ename,e.sal, s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
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)
对于表间的连接条件e.sal between s.losal and s.hisal来说，是非等值连接。
3.3、自连接
自连接的最大特点就是一张表看成是两张表，自己来连接自己。这个时候一张表看成两张表比较好理解
案例1：找出每个员工的上级领导，要求显示出来员工名字和领导名字
分析：
领导也是员工，所以首先根据员工的领导来进行筛选出来是领导的表。然后根据领导的员工号和员工的上级编号来进行匹配。
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
根据笛卡尔积现成，我们来进行分析
select e1.ename,e2.ename
from emp e1
inner join emp e2
on e1.mgr = e2.empno
查询出来的结果：
+--------+-------+
| ename | ename |
+--------+-------+
| 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)
可以看到上面的员工和员工的领导的名字；但是注意到king是没有上级领导的，在king的mrg进行等值连接的时候，发现null是无法和其他的进行匹配的。所以14行记录显示出来的就只有三行。
但是14个员工，查看出来了13条记录，因为KING是老板，所以没有领导。但是这里却没有显示对应的数据，尽管是KING是没有领导的，但是依然想要查询出来对应的数据，那么内连接是无法做到的，可以使用外连接使用。
注意：
内连接查询可能会造成数据丢失的情况，因为上面的就不满足我们需要的条件
4、外连接
什么外连接？外连接和内连接的区别是什么？
外连接是在进行表和表之间进行连接查询的时候，以其中的一个表为主表，一个表为副表。主要是为了查询主表中的数据，捎带着查询出来副表中的数据。侧面说明了，一定要查询出来主表中的数据，如果副表中要是有数据，那么就查出来；如果没有那么就按照默认的null来进行显示。
内连接中的表和表之间是地位是等同的，没有主表和副表的区别。符合条件的我就将你给查询出来，没有的话就不去查询。
案例1：找出每个员工的上级领导，要求显示出来员工名字和领导名字
select
e.ename as self,m.ename as mgr
from
emp e
left outer join
emp m
on
e.mgr = m.empno;
+--------+-------+
| self | mgr |
+--------+-------+
| 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)
从上面可以看出内连接和外连接的区别，一个是13条数据，一个是14条数据。显然14条的更加的全面和清晰。
上面是左外连接，可以 改造成右外连接
select
e.ename as self,m.ename as mgr
from
emp m
right outer join
emp e
on
e.mgr = m.empno;
+--------+-------+
| self | mgr |
+--------+-------+
| 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)
左外连接和右外连接查询出来的数据是一样的，只需要掌握住一种查询方式即可。
在写SQL语句的时候，尽量保持规范。比如说带上inner和outer，可见性比较好，别人一看就知道你写的SQL语句采用的是外连接还是内连接。
内连接和外连接的区别在于：内连接是将条件连接能够匹配上的查询出来，没有匹配上的就不要；
外连接是要将主表中的数据查询出来，附带这将从表中的数据查询出来。
使用内连接可能会造成数据丢失，而是用外连接有了主表之分之后，就不会造成数据丢失。
案列2：查询出哪个部门中没有员工
分析
首先分析出哪张表来作为主表？
既然主表中都需要查询出来，副表没有的作为辅助。那么可以确定是以部门表来作为主表，员工表作为副表
也就是说部门表是主表，有员工展示出来，没有员工用null来进行表示。
select
d.deptno,e.*
from
dept d
left outer join
emp e
on
d.deptno = e.deptno
where
e.ename is null;
+--------+-------+-------+------+------+----------+------+------+--------+
| deptno | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+--------+-------+-------+------+------+----------+------+------+--------+
| 40 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+-------+-------+------+------+----------+------+------+--------+
1 row in set (0.00 sec)
可以看到部门为40的是没有员工的。
4.2、扩展：三种表进行联合查询
案例：找出每个员工的部门名称以及工资等级
分析：遇到这种需求，首先需要进行拆分步骤，拆分出来之后然后再进行查询；
1、首先查询出来每个员工的部门和工资
mysql> select ename,sal,deptno from emp;
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| FORD | 3000.00 | 20 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
14 rows in set (0.00 sec)
2、根据部门的编号查询出来对应的部门名称
select
e.ename,e.sal,e.deptno
from
emp e
left outer join
dept d
on
e.deptno = d.deptno;
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| FORD | 3000.00 | 20 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
14 rows in set (0.00 sec)
3、根据薪资来确定每个员工的所在的等级。以上面的临时表（其实还是最前面的那种表的名字）为一个表作为一张表来进行查询
select
e.ename,e.sal,e.deptno
from
emp e
left outer join
dept d
on
e.deptno = d.deptno
left outer join
salgrade s
on
e.sal between s.losal and s.hisal;
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| FORD | 3000.00 | 20 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
14 rows in set (0.00 sec)
多表进行连接的时候，需要主要的是：
select
....
from
A
left outer join
B
on
xxx
left outer join
C
on
xxx
表示的是AB下先进行查询，查询出来的结果是一张临时表，这个临时表（A和B表联合查询的结果）的名字还是A，所以A表继续和C进行联合查询；
案例2：找出每一个员工的部门名称、工资等级以及上级领导
1、可以在emp表中查询到部门编号、工资和上级领导
mysql> select deptno,sal,mgr from emp;
+--------+---------+------+
| deptno | sal | mgr |
+--------+---------+------+
| 20 | 800.00 | 7902 |
| 30 | 1600.00 | 7698 |
| 30 | 1250.00 | 7698 |
| 20 | 2975.00 | 7839 |
| 30 | 1250.00 | 7698 |
| 30 | 2850.00 | 7839 |
| 10 | 2450.00 | 7839 |
| 20 | 3000.00 | 7566 |
| 10 | 5000.00 | NULL |
| 30 | 1500.00 | 7698 |
| 20 | 1100.00 | 7788 |
| 30 | 950.00 | 7698 |
| 20 | 3000.00 | 7566 |
| 10 | 1300.00 | 7782 |
+--------+---------+------+
14 rows in set (0.00 sec)
2、在查出来上级领导
select
e.deptno,e.sal,e.mgr
from
emp e
left outer join
emp m
on
e.mgr = m.empno;
+--------+---------+------+
| deptno | sal | mgr |
+--------+---------+------+
| 20 | 800.00 | 7902 |
| 30 | 1600.00 | 7698 |
| 30 | 1250.00 | 7698 |
| 20 | 2975.00 | 7839 |
| 30 | 1250.00 | 7698 |
| 30 | 2850.00 | 7839 |
| 10 | 2450.00 | 7839 |
| 20 | 3000.00 | 7566 |
| 10 | 5000.00 | NULL |
| 30 | 1500.00 | 7698 |
| 20 | 1100.00 | 7788 |
| 30 | 950.00 | 7698 |
| 20 | 3000.00 | 7566 |
| 10 | 1300.00 | 7782 |
+--------+---------+------+
14 rows in set (0.00 sec)
3、确定了每个员工的上级领导之后，再根据部门编号来找到部门名称
select
e.deptno,e.sal,e.mgr,d.dname
from
emp e
left outer join
emp m
on
e.mgr = m.empno
left outer join
dept d
on
e.deptno = d.deptno;
+--------+---------+------+------------+
| deptno | sal | mgr | dname |
+--------+---------+------+------------+
| 20 | 800.00 | 7902 | RESEARCH |
| 30 | 1600.00 | 7698 | SALES |
| 30 | 1250.00 | 7698 | SALES |
| 20 | 2975.00 | 7839 | RESEARCH |
| 30 | 1250.00 | 7698 | SALES |
| 30 | 2850.00 | 7839 | SALES |
| 10 | 2450.00 | 7839 | ACCOUNTING |
| 20 | 3000.00 | 7566 | RESEARCH |
| 10 | 5000.00 | NULL | ACCOUNTING |
| 30 | 1500.00 | 7698 | SALES |
| 20 | 1100.00 | 7788 | RESEARCH |
| 30 | 950.00 | 7698 | SALES |
| 20 | 3000.00 | 7566 | RESEARCH |
| 10 | 1300.00 | 7782 | ACCOUNTING |
+--------+---------+------+------------+
14 rows in set (0.00 sec)
4、上面已经查询出来了部门名称、工资、上级领导和部门名称，下面只需要根据薪资来确定等级即可；
select
e.ename,e.mgr,d.dname,s.grade
from
emp e
left outer join
emp m
on
e.mgr = m.empno
left outer join
dept d
on
e.deptno = d.deptno
left outer join
salgrade s
on
e.sal between s.losal and s.hisal;
+--------+---------+------+------------+-------+
| deptno | sal | mgr | dname | grade |
+--------+---------+------+------------+-------+
| 20 | 800.00 | 7902 | RESEARCH | 1 |
| 30 | 1600.00 | 7698 | SALES | 3 |
| 30 | 1250.00 | 7698 | SALES | 2 |
| 20 | 2975.00 | 7839 | RESEARCH | 4 |
| 30 | 1250.00 | 7698 | SALES | 2 |
| 30 | 2850.00 | 7839 | SALES | 4 |
| 10 | 2450.00 | 7839 | ACCOUNTING | 4 |
| 20 | 3000.00 | 7566 | RESEARCH | 4 |
| 10 | 5000.00 | NULL | ACCOUNTING | 5 |
| 30 | 1500.00 | 7698 | SALES | 3 |
| 20 | 1100.00 | 7788 | RESEARCH | 1 |
| 30 | 950.00 | 7698 | SALES | 1 |
| 20 | 3000.00 | 7566 | RESEARCH | 4 |
| 10 | 1300.00 | 7782 | ACCOUNTING | 2 |
+--------+---------+------+------------+-------+
14 rows in set (0.00 sec)
5、对查询出来的临时表中的字段进行更改，确定最终结果
select
e.,e.sal,e.mgr,d.dname,s.grade
from
emp e
left outer join
emp m
on
e.mgr = m.empno
left outer join
dept d
on
e.deptno = d.deptno
left outer join
salgrade s
on
e.sal between s.losal and s.hisal;
+--------+------+------------+-------+
| ename | mgr | dname | grade |
+--------+------+------------+-------+
| SMITH | 7902 | RESEARCH | 1 |
| ALLEN | 7698 | SALES | 3 |
| WARD | 7698 | SALES | 2 |
| JONES | 7839 | RESEARCH | 4 |
| MARTIN | 7698 | SALES | 2 |
| BLAKE | 7839 | SALES | 4 |
| CLARK | 7839 | ACCOUNTING | 4 |
| SCOTT | 7566 | RESEARCH | 4 |
| KING | NULL | ACCOUNTING | 5 |
| TURNER | 7698 | SALES | 3 |
| ADAMS | 7788 | RESEARCH | 1 |
| JAMES | 7698 | SALES | 1 |
| FORD | 7566 | RESEARCH | 4 |
| MILLER | 7782 | ACCOUNTING | 2 |
+--------+------+------------+-------+
14 rows in set (0.00 sec)
注意：表和表之间连接的主副表之分，left加在哪里比较合适。
4.2、总结内连接和外连接的区别：
1、内连接中表和表之间没有主副表之分；外连接是有主副表之分的，以主表中数据为主，副表中的数据为辅，如果没有对应的数据，将由null来进 行代替；
2、内连接可能会造成数据丢失；外连接查询出来的数据是比较全面的；
3、无论是内连接还是外连接，其中的inner和outer都是省略的。所以用来区分内连接还是外连接的，是看有没有left和right来区分内连接还是外连接的。
4、外连接使用的较多，内连接使用的较少；
5、子连接
5.1、概念
子连接就是select语句中嵌套select的SQL语句。
5.2、出现的位置
select
(select)
from
(select)
where
(select);
可以出现在上面三个位置。
案例一：找出薪资高于平均薪资的员工
where中嵌套子查询。
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后面嵌套子查询[重点]
1、首先进行分组
select avg(sal) from emp group by deptno;
+-------------+
| avg(sal) |
+-------------+
| 2916.666667 |
| 2175.000000 |
| 1566.666667 |
+-------------+
3 rows in set (0.00 sec)
可以查询出每个部门的平均薪资，然后根据薪资来进行划分工资等级
2、将上面的表直接作为一张表
select
t.deptno,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
inner join
salgrade s
on
t.avgsal between s.losal and s.hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
| 30 | 3 |
| 10 | 4 |
| 20 | 4 |
+--------+-------+
3 rows in set (0.08 sec)
案例三：查询出每个员工所在的部门名称，要求显示员工名和部门名称
最直观的写法:select d.dname,d.deptno,e.ename from emp e inner join dept d on e.deptno = d.deptno;
使用子查询： select e.ename,(select d.dname from dept d where d.deptno = e.deptno) as deptname from emp e;
讲一下这里的select后面使用子查询的方式：
可以看到首先执行的是from emp e，不然子查询中不可能出现别名e。
接着取出每行记录，select d.dname from dept d where d.deptno = e.第一行的deptno，也就是说这样使用使用子查询的时候，首先会从表中取出一条数据，找出来e.ename，然后找到e.deptno去从dept表中来进行匹配，将查询出来的字段匹配上。
但是由于子查询有时候会造成效率低下，所以不推荐使用。但是有时候又不得不去使用，这是一个非常纠结的事情。
所以有效的选择很重要。所以在最开始拿到需求的时候要注意使用方式。同时这里也是一个优化的地方。
6、limit分页查询[重点]
6.1、概述：
使用limit来进行分页查询，显示的数据是整个结果集中的部分记录，而不是全部的记录条数。
6.2、语法格式：
limit startIndex,length
startIndex:下标识从0开始的
length:表示的将总记录条数分为length条数据来进行显示
案例：显示出一张表中的前五条记录
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 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 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
-- 显然记录数中的前五条数据
select * from emp limit 0,5;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)
-- 查询总记录条数中的第3条到第6条之间的数据
mysql> select * from emp limit 2,4;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)
注意：
一般说来，查询显示的总记录条数是固定的。变化的只可能是startIndex这个元素来进行操作。
又因为其实下标默认是从0开始的，所以比我们默认的页面来说，是少了一个的。
归纳总结一下
查询第一条到第三条，作为第一页
limit 0,3; limit (1-1)*3,3
查询第四条到第六条，作为第二页
limit 3,3; limit (2-1)*3,3
查询第七条到第九条，作为第三页
limit 6,3 limit (3-1)*3,3
查询第十条到第十二条，作为第四页
limit 9,3; limit(4-1)*3,3
...
查询第n页的数据
limit (n-1)*3,3;
6.3、通用语法
第n页显示length条数据，
limit startIndex:(n-1)*length,length
7、小结
在MySQL中
一：对字段的操作
1、起别名；2、数学运算，还可以起别名；3、distinct关键字去重，放前面表示将后面的字段作为一个整体，放在后面错误；
4、group by ，处在前面的起着主导作用，而且是将这两个看成是一个字段来进行处理。在select中只能出现分组函数和参与分组的字段
5、where后面不能有分组函数的出现，因为分组函数是在where后面执行的。
6、数学运算：>,<,!=,=，between...and...
7、null、is null、is not null等情况
8、having和group by是亲兄弟，having的出现就是专门为了group by服务的。但是having尽量少量，尽量在源头处避免，提高效率；
二、单表语句的执行顺序：
select
xxx
from
table
where
yyy
group by
xxx
having
xxxx
order by
xxxx
从table中查询数据，过滤条件是yyy，对条件过滤后的进行分组，发现还有不满意的使用having来进行过滤，将查询出来的数据在进行即将展示的时候，进行排序，展示出来。
多表查询：
分类：
内连接：等值连接、非等值连接、自连接
外连接：左外连接、右外连接
全连接：不重要
内连接可能会导致数据缺失，但是外连接不会。
表的连接查询涉及到笛卡尔积，笛卡尔积是无法避免的。只是通过连接条件将不满足的数据进行过滤掉，得到的是有价值的数据。
内连接只会将条件匹配的查询出来；外连接可以将条件没有匹配的也查询出来。所以外连接查询出来的数据多余内连接，而且数据上没有大问题，所以工作中绝大多数使用的是外连接。
外连接有主表和附表，主要的是查询主表，附带着查询出来附表。以主表中的每条数据为主，去匹配附表中的每条记录，然后根据条件来进行过滤掉。
多表查询的时候一定要记住有笛卡尔积现象的发生。有了笛卡尔积就有了主表中的一行记录结合附表在查询出来后，就有了多条记录的的情况。
从理论中来，到实践中去，最终回归理论
