一、多表查询
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
1.引出
假如我们现在要查询员工的姓名还有部门名称
这两个字段在不同表中，如果没有关联条件的话，查询出来的结果会怎么样呢，让我们来看看。
SELECT last_name, department_name
FROM employees, departments;+-----------+----------------------+| last_name | department_name |+-----------+----------------------+| King | Administration || King | Marketing || King | Purchasing || King | Human Resources || King | Shipping || King | IT || King |Public Relations || King | Sales || King | Executive || King | Finance || King | Accounting || King | Treasury |...| Gietz | IT Support || Gietz | NOC || Gietz | IT Helpdesk || Gietz | Government Sales || Gietz | Retail Sales || Gietz | Recruiting || Gietz | Payroll |+-----------+----------------------+2889rowsinset(0.01 sec)
SELECTCOUNT(employee_id)FROM employees;#输出107行SELECTCOUNT(department_id)FROM departments;#输出27行SELECT107*27FROM dual;107*27=2889
很明显上面的操作是错误的
上面的操作，会导致员工表的一条记录会和部门表的每一条记录相匹配，就好像一个员工在所有部门都工作过一样，从现实角度来说，很明显，是不会出现这种情况的，
这种现象就是笛卡尔积。
2.笛卡尔积
笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。比如：有两个表，左表有m条数据记录，x个字段，右表有n条数据记录，y个字段，则执行交叉连接后将返回m*n条数据记录，x+y个字段。笛卡儿积示意图如图所示。
SQL92中，笛卡尔积也称为
交叉连接，英文是
CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：
查询员工姓名和所在部门名称SELECT last_name,department_name FROM employees,departments;SELECT last_name,department_name FROM employees CROSSJOIN departments;SELECT last_name,department_name FROM employees INNERJOIN departments;SELECT last_name,department_name FROM employees JOIN departments;
3. 笛卡尔积的解决方法
- 笛卡尔积的错误会在下面条件下产生：- 省略多个表的连接条件（或关联条件）- 连接条件（或关联条件）无效- 所有表中的所有行互相连接
- 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。
SELECT table1.column, table2.columnFROM table1, table2
WHERE table1.column1 = table2.column2;#连接条件
#案例：查询员工的姓名及其部门名称SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
注意:如果不同的表中有相同的字段，我们要声明我们查的是哪一张表的字段,表名.字段名这个和Java中，类名.属性是类似的，挺好理解的。
SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
二、多表查询分类
1.等值连接和非等值连接
等值连接其实很好理解，就是谁等于谁的意思，使用=。
非等值连接的话，比如查询某个字段>某个值的记录等等
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;拓展:
- 使用别名可以简化查询。— 有的字段名太长了
- 列名前使用表名前缀可以提高查询效率。
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id;需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。
2.自连接和非自连接
自连接，它的字面意思就是自己和自己连接
比如说现在有一张表，我们想要查找员工信息和对应的上级信息
我们知道，只有一张表是没办法把它们关联起来的，要想把它们他们关联起来，肯定是要有关联条件的，那么就应该要有两张表，这个时候，我们就可以抽取出一张表，和本来的表本质上是一样的，然后我们对表起别名，table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。
比如说：现在我们想要查找员工和对应老板的名字，我们就可以使用自连接
SELECT CONCAT(worker.last_name ,' works for ', manager.last_name)FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ;练习：查询出last_name为 ‘Chen’ 的员工的 manager 的信息。
3.内连接和外连接
- 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
- 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行****，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
- 如果是左外连接，则连接条件中左边的表也称为
主表，右边的表称为从表。如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。 外连接查询的数据比较多
SQL92：使用(+)创建连接
- 在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
- Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
#左外连接SELECT last_name,department_name FROM employees ,departments WHERE employees.department_id = departments.department_id(+);#右外连接 SELECT last_name,department_name FROM employees ,departments WHERE employees.department_id(+)= departments.department_id;```
SQL99语法实现多表查询
1.基本语法
使用JOIN…ON子句创建连接的语法结构：SELECT table1.column, table2.column,table3.columnFROM table1 JOIN table2 ON table1 和 table2 的连接条件 JOIN table3 ON table2 和 table3 的连接条件语法说明：
可以使用 ON 子句指定额外的连接条件 。
- 这个连接条件是与其它条件分开的。
- ON****子句使语句具有更高的易读性。
- 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
2.内连接(INNER JOIN)
语法
select 字段
from 表1
join 表2 on 两个表的连接条件
where 其他子句
比如我们现在想要查询各个部门的员工的信息，他们的连接条件就是员工表中部门id和部门表中的部门id一样
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON(e.department_id = d.department_id);
这里截取部分结果
+-------------+-------------+---------------+---------------+-------------+| employee_id | last_name | department_id | department_id | location_id |+-------------+-------------+---------------+---------------+-------------+|103| Hunold |60|60|1400||104| Ernst |60|60|1400||105| Austin |60|60|1400||106| Pataballa |60|60|1400||107| Lorentz |60|60|1400||120| Weiss |50|50|1500||121| Fripp |50|50|1500||122| Kaufling |50|50|1500||123| Vollman |50|50|1500||124| Mourgos |50|50|1500||125| Nayer |50|50|1500||126| Mikkilineni |50|50|1500||127| Landry |50|50|1500||128| Markle |50|50|1500||129| Bissot |50|50|1500|
使用内连接的一个问题就是他们把所有的信息都显示出来，它只能够显示匹配的数据，而外连接可以把不匹配的数据也显示出来
先来看看表的数据，方便后续操作
mysql>select*from 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|+-------+--------+-----------+------+------------+---------+---------+--------+14rowsinset(0.00 sec)
mysql>select*from dept;+--------+------------+----------+| DEPTNO | DNAME | LOC |+--------+------------+----------+|10| ACCOUNTING | NEW YORK ||20| RESEARCH | DALLAS ||30| SALES | CHICAGO ||40| OPERATIONS | BOSTON |+--------+------------+----------+4rowsinset(0.00 sec)
mysql>select*from emp e
->join dept d
->on e.deptno=e.deptno;+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+|7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|10| ACCOUNTING | NEW YORK ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|10| ACCOUNTING | NEW YORK ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|10| ACCOUNTING | NEW YORK ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|10| ACCOUNTING | NEW YORK ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|10| ACCOUNTING | NEW YORK ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|10| ACCOUNTING | NEW YORK ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|10| ACCOUNTING | NEW YORK ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|10| ACCOUNTING | NEW YORK ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|10| ACCOUNTING | NEW YORK ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|10| ACCOUNTING | NEW YORK ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|10| ACCOUNTING | NEW YORK ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|10| ACCOUNTING | NEW YORK ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|10| ACCOUNTING | NEW YORK ||7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|10| ACCOUNTING | NEW YORK ||7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|20| RESEARCH | DALLAS ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|20| RESEARCH | DALLAS ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|20| RESEARCH | DALLAS ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|20| RESEARCH | DALLAS ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|20| RESEARCH | DALLAS ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|20| RESEARCH | DALLAS ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|20| RESEARCH | DALLAS ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|20| RESEARCH | DALLAS ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|20| RESEARCH | DALLAS ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|20| RESEARCH | DALLAS ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|20| RESEARCH | DALLAS ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|20| RESEARCH | DALLAS ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|20| RESEARCH | DALLAS ||7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|20| RESEARCH | DALLAS ||7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|30| SALES | CHICAGO ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|30| SALES | CHICAGO ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|30| SALES | CHICAGO ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|30| SALES | CHICAGO ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|30| SALES | CHICAGO ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|30| SALES | CHICAGO ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|30| SALES | CHICAGO ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|30| SALES | CHICAGO ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|30| SALES | CHICAGO ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|30| SALES | CHICAGO ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|30| SALES | CHICAGO ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|30| SALES | CHICAGO ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|30| SALES | CHICAGO ||7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|30| SALES | CHICAGO ||7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|40| OPERATIONS | BOSTON ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|40| OPERATIONS | BOSTON ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|40| OPERATIONS | BOSTON ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|40| OPERATIONS | BOSTON ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|40| OPERATIONS | BOSTON ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|40| OPERATIONS | BOSTON ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|40| OPERATIONS | BOSTON ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|40| OPERATIONS | BOSTON ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|40| OPERATIONS | BOSTON ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|40| OPERATIONS | BOSTON ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|40| OPERATIONS | BOSTON ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|40| OPERATIONS | BOSTON ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|40| OPERATIONS | BOSTON ||7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|40| OPERATIONS | BOSTON |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+56rowsinset(0.01 sec)
– 问题：
– 1.40号部分没有员工，没有显示在查询结果中
– 2.员工scott没有部门，没有显示在查询结果中
所以想显示所有数据，要使用外连接
外连接(OUTER JOIN)
1.左外连接左外连接： left outer join – 左面的那个表的信息，即使不匹配也可以查看出效果
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;2.右外连接
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
mysql>select*->from emp e
->rightouterjoin dept d
->on e.deptno = d.deptno;+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+|7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|10| ACCOUNTING | NEW YORK ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|10| ACCOUNTING | NEW YORK ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|10| ACCOUNTING | NEW YORK ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|20| RESEARCH | DALLAS ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|20| RESEARCH | DALLAS ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|20| RESEARCH | DALLAS ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|20| RESEARCH | DALLAS ||7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|20| RESEARCH | DALLAS ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|30| SALES | CHICAGO ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|30| SALES | CHICAGO ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|30| SALES | CHICAGO ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|30| SALES | CHICAGO ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|30| SALES | CHICAGO ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|30| SALES | CHICAGO ||NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|40| OPERATIONS | BOSTON |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+15rowsinset(0.00 sec)
3.满外连接(FULL OUTER JOIN)
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
在讲满外连接之前，我们先来介绍一下union关键字的使用，相信看了以后大家就清楚了
4.UNION
合并查询结果
利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
语法格式：
SELECT column,… FROM table1
UNION [ALL]
SELECT column,… FROM table2
UNION操作符
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
`UNION ALL操作符
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
为什么union all的效率比较高呢？首先我们如果使用union的话，它会先把数据查询出来，紧接着还要进去去重操作，它多了一步去重操作，当然花费的时间就比较多了，影响效率。
mysql>select*->from emp e
->leftouterjoin dept d
->on e.deptno = d.deptno
->union-- 并集 去重 效率低->select*->from emp e
->rightouterjoin dept d
->on e.deptno = d.deptno;+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+|7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|20| RESEARCH | DALLAS ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|30| SALES | CHICAGO ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|30| SALES | CHICAGO ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|20| RESEARCH | DALLAS ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|30| SALES | CHICAGO ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|30| SALES | CHICAGO ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|10| ACCOUNTING | NEW YORK ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|20| RESEARCH | DALLAS ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|10| ACCOUNTING | NEW YORK ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|30| SALES | CHICAGO ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|20| RESEARCH | DALLAS ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|30| SALES | CHICAGO ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|20| RESEARCH | DALLAS ||7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|10| ACCOUNTING | NEW YORK ||NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|40| OPERATIONS | BOSTON |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+15rowsinset(0.01 sec)
mysql>^C
mysql> https://blog.csdn.net/weixin_42250835/article/details/123535439^Z^Z^C
mysql>select*->from emp e
->leftouterjoin dept d
->on e.deptno = d.deptno
->union-- 并集 去重 效率低->select*->from emp e
->rightouterjoin dept d
->on e.deptno = d.deptno;+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+|7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|20| RESEARCH | DALLAS ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|30| SALES | CHICAGO ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|30| SALES | CHICAGO ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|20| RESEARCH | DALLAS ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|30| SALES | CHICAGO ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|30| SALES | CHICAGO ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|10| ACCOUNTING | NEW YORK ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|20| RESEARCH | DALLAS ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|10| ACCOUNTING | NEW YORK ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|30| SALES | CHICAGO ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|20| RESEARCH | DALLAS ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|30| SALES | CHICAGO ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|20| RESEARCH | DALLAS ||7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|10| ACCOUNTING | NEW YORK ||NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|40| OPERATIONS | BOSTON |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+15rowsinset(0.00 sec)
mysql>select*->from emp e
->leftouterjoin dept d
->on e.deptno = d.deptno
->unionall-- 并集 不去重 效率高->select*->from emp e
->rightouterjoin dept d
->on e.deptno = d.deptno;+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+|7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|20| RESEARCH | DALLAS ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|30| SALES | CHICAGO ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|30| SALES | CHICAGO ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|20| RESEARCH | DALLAS ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|30| SALES | CHICAGO ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|30| SALES | CHICAGO ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|10| ACCOUNTING | NEW YORK ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|20| RESEARCH | DALLAS ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|10| ACCOUNTING | NEW YORK ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|30| SALES | CHICAGO ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|20| RESEARCH | DALLAS ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|30| SALES | CHICAGO ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|20| RESEARCH | DALLAS ||7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|10| ACCOUNTING | NEW YORK ||7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|10| ACCOUNTING | NEW YORK ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|10| ACCOUNTING | NEW YORK ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|10| ACCOUNTING | NEW YORK ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|20| RESEARCH | DALLAS ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|20| RESEARCH | DALLAS ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|20| RESEARCH | DALLAS ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|20| RESEARCH | DALLAS ||7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|20| RESEARCH | DALLAS ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|30| SALES | CHICAGO ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|30| SALES | CHICAGO ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|30| SALES | CHICAGO ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|30| SALES | CHICAGO ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|30| SALES | CHICAGO ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|30| SALES | CHICAGO ||NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|40| OPERATIONS | BOSTON |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+29rowsinset(0.00 sec)
为了让大家更清楚知道他们的区别，我们分别看一下有多少记录
->on e.deptno = d.deptno' at line 2
mysql>select*->from emp e
->leftouterjoin dept d
->on e.deptno = d.deptno;+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+|7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|20| RESEARCH | DALLAS ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|30| SALES | CHICAGO ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|30| SALES | CHICAGO ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|20| RESEARCH | DALLAS ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|30| SALES | CHICAGO ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|30| SALES | CHICAGO ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|10| ACCOUNTING | NEW YORK ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|20| RESEARCH | DALLAS ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|10| ACCOUNTING | NEW YORK ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|30| SALES | CHICAGO ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|20| RESEARCH | DALLAS ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|30| SALES | CHICAGO ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|20| RESEARCH | DALLAS ||7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|10| ACCOUNTING | NEW YORK |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+14rowsinset(0.00 sec)
mysql>select*->from emp e
->rightouterjoin dept d
->on e.deptno = d.deptno;+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+|7934| MILLER | CLERK |7782|1982-01-23|1300.00|NULL|10|10| ACCOUNTING | NEW YORK ||7839| KING | PRESIDENT |NULL|1981-11-17|5000.00|NULL|10|10| ACCOUNTING | NEW YORK ||7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|10| ACCOUNTING | NEW YORK ||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|20| RESEARCH | DALLAS ||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20|20| RESEARCH | DALLAS ||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20|20| RESEARCH | DALLAS ||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20|20| RESEARCH | DALLAS ||7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20|20| RESEARCH | DALLAS ||7900| JAMES | CLERK |7698|1981-12-03|950.00|NULL|30|30| SALES | CHICAGO ||7844| TURNER | SALESMAN |7698|1981-09-08|1500.00|0.00|30|30| SALES | CHICAGO ||7698| BLAKE | MANAGER |7839|1981-05-01|2850.00|NULL|30|30| SALES | CHICAGO ||7654| MARTIN | SALESMAN |7698|1981-09-28|1250.00|1400.00|30|30| SALES | CHICAGO ||7521| WARD | SALESMAN |7698|1981-02-22|1250.00|500.00|30|30| SALES | CHICAGO ||7499| ALLEN | SALESMAN |7698|1981-02-20|1600.00|300.00|30|30| SALES | CHICAGO ||NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|40| OPERATIONS | BOSTON |+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+15rowsinset(0.00 sec)
14+15=29所=所以可以看出union all确实是不去重
总结
中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id`= d.`department_id`;
左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFTJOIN departments d
ON e.`department_id`= d.`department_id`;
右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHTJOIN departments d
ON e.`department_id`= d.`department_id`;
左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFTJOIN departments d
ON e.`department_id`= d.`department_id`WHERE d.`department_id`ISNULL
右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHTJOIN departments d
ON e.`department_id`= d.`department_id`WHERE e.`department_id`ISNULL
左下图：满外连接
左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFTJOIN departments d
ON e.`department_id`= d.`department_id`WHERE d.`department_id`ISNULLUNIONALL#没有去重操作，效率高SELECT employee_id,last_name,department_name
FROM employees e RIGHTJOIN departments d
ON e.`department_id`= d.`department_id`;
右下图
左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFTJOIN departments d
ON e.`department_id`= d.`department_id`WHERE d.`department_id`ISNULLUNIONALLSELECT employee_id,last_name,department_name
FROM employees e RIGHTJOIN departments d
ON e.`department_id`= d.`department_id`WHERE e.`department_id`ISNULL
4.自然连接
SQL99 在 SQL92 的基础上提供了一些特殊语法，比如
NATURAL JOIN
用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中
所有相同的字段
，然后进行
等值连接
。
SELECT employee_id,last_name,department_name
FROM employees e NATURALJOIN departments d;
上面的写法的效果和下面是一样的
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id`= d.`department_id`AND e.`manager_id`= d.`manager_id`;
5.using连接
当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的
同名字段
进行等值连接。但是只能配合JOIN一起使用。比如：
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING(department_id);
你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用
JOIN...USING
可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
注意:using只能和join配合使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等
三、子查询
1.不相关子查询
子查询就是查询语句的嵌套，有多个select语句
子查询的引入:
– 查询所有比“CLARK”工资高的员工的信息
– 步骤1：“CLARK”工资
mysql>select*from emp where ename='clark'; 工资2450+-------+-------+---------+------+------------+---------+------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+-------+---------+------+------------+---------+------+--------+|7782| CLARK | MANAGER |7839|1981-06-09|2450.00|NULL|10|+-------+-------+---------+------+------------+---------+------+--------+1rowinset(0.00 sec)
– 步骤2：查询所有工资比2450高的员工的信息
mysql>select*from emp where sal >2450;+-------+-------+-----------+------+------------+---------+------+--------+| 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||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|+-------+-------+-----------+------+------------+---------+------+--------+5rowsinset(0.01 sec)
两次命令解决问题的话，效率低 ，第二个命令依托于第一个命令，第一个命令的结果给第二个命令使用，但是
因为第一个命令的结果可能不确定要改，所以第二个命令也会导致修改
将步骤1和步骤2合并 --》子查询：-- 一个命令解决问题 --》效率高
mysql>select*from emp where sal>(select sal from emp where ename='clark');+-------+-------+-----------+------+------------+---------+------+--------+| 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||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|+-------+-------+-----------+------+------------+---------+------+--------+5rowsinset(0.00 sec)
【2】执行顺序：
先执行子查询，再执行外查询；
【3】不相关子查询：
子查询可以独立运行，称为不相关子查询。
【4】不相关子查询分类：
根据子查询的结果行数，可以分为单行子查询和多行子查询。
练习
单行子查询
mysql>-- 单行子查询
mysql>-- 查询工资高与拼接工资的员工名字和工资
mysql>select ename,sal from emp
->where sal>(selectavg(sal)from emp);+-------+---------+| ename | sal |+-------+---------+| JONES |2975.00|| BLAKE |2850.00|| CLARK |2450.00|| SCOTT |3000.00|| KING |5000.00|| FORD |3000.00|+-------+---------+6rowsinset(0.00 sec)
-- 查询和CLARK同一部门且比他工资低的雇员名字和工资。select ename,sal
from emp
where deptno =(select deptno from emp where ename ='CLARK')and
sal <(select sal from emp where ename ='CLARK')+--------+---------+| ename | sal |+--------+---------+| MILLER |1300.00|+--------+---------+1rowinset(0.00 sec)
多行子查询：
【1】查询【部门20中职务同部门10的雇员一样的】雇员信息。
-- 查询雇员信息select*from 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|+-------+--------+-----------+------+------------+---------+---------+--------+14rowsinset(0.00 sec)-- 查询部门20中的雇员信息select*from emp where deptno =20;+-------+-------+---------+------+------------+---------+------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+-------+---------+------+------------+---------+------+--------+|7369| SMITH | CLERK |7902|1980-12-17|800.00|NULL|20||7566| JONES | MANAGER |7839|1981-04-02|2975.00|NULL|20||7788| SCOTT | ANALYST |7566|1987-04-19|3000.00|NULL|20||7876| ADAMS | CLERK |7788|1987-05-23|1100.00|NULL|20||7902| FORD | ANALYST |7566|1981-12-03|3000.00|NULL|20|+-------+-------+---------+------+------------+---------+------+--------+5rowsinset(0.00 sec)-- 部门10的雇员的职务：select job from emp where deptno =10;-- MANAGER,PRESIDENT,CLERK+-----------+| job |+-----------+| MANAGER || PRESIDENT || CLERK |+-----------+3rowsinset(0.00 sec)-- 查询部门20中职务同部门10的雇员一样的雇员信息。select*from emp
where deptno =20and job in(select job from emp where deptno =10)-- > Subquery returns more than 1 rowselect*from emp
where deptno =20and job =any(select job from emp where deptno =10)
【2】查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
-- 查询雇员的编号、名字和工资select empno,ename,sal from emp
+-------+--------+---------+| empno | ename | sal |+-------+--------+---------+|7369| SMITH |800.00||7499| ALLEN |1600.00||7521| WARD |1250.00||7566| JONES |2975.00||7654| MARTIN |1250.00||7698| BLAKE |2850.00||7782| CLARK |2450.00||7788| SCOTT |3000.00||7839| KING |5000.00||7844| TURNER |1500.00||7876| ADAMS |1100.00||7900| JAMES |950.00||7902| FORD |3000.00||7934| MILLER |1300.00|+-------+--------+---------+14rowsinset(0.00 sec)-- “SALESMAN”的工资：select sal from emp where job ='SALESMAN';+---------+| sal |+---------+|1600.00||1250.00||1250.00||1500.00|+---------+4rowsinset(0.00 sec)-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。-- 多行子查询：select empno,ename,sal
from emp
where sal >all(select sal from emp where job ='SALESMAN');+-------+-------+---------+| empno | ename | sal |+-------+-------+---------+|7566| JONES |2975.00||7698| BLAKE |2850.00||7782| CLARK |2450.00||7788| SCOTT |3000.00||7839| KING |5000.00||7902| FORD |3000.00|+-------+-------+---------+6rowsinset(0.00 sec)
2.相关子查询
【1】不相关的子查询引入：
不相关的子查询：子查询可以独立运行，先运行子查询，再运行外查询。
相关子查询：子查询不可以独立运行，并且先运行外查询，再运行子查询
【2】不相关的子查询优缺点：
好处：简单 功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）
缺点：稍难理解
【3】sql展示：
-- 【1】查询最高工资的员工 （不相关子查询）select*from emp where sal =(selectmax(sal)from emp)-- 【2】查询本部门最高工资的员工 （相关子查询）-- 方法1：通过不相关子查询实现：select*from emp where deptno =10and sal =(selectmax(sal)from emp where deptno =10)unionselect*from emp where deptno =20and sal =(selectmax(sal)from emp where deptno =20)unionselect*from emp where deptno =30and sal =(selectmax(sal)from emp where deptno =30)-- 缺点：语句比较多，具体到底有多少个部分未知-- 方法2： 相关子查询select*from emp e where sal =(selectmax(sal)from emp where deptno = e.deptno)orderby deptno
-- 【3】查询工资高于其所在岗位的平均工资的那些员工 （相关子查询）-- 不相关子查询：select*from emp where job ='CLERK'and sal >=(selectavg(sal)from emp where job ='CLERK')union......-- 相关子查询：select*from emp e where sal >=(selectavg(sal)from emp e2 where e2.job = e.job)
四、聚合函数
1.聚合函数介绍
聚合函数作用于一组数据，并对一组数据返回一个值。
聚合函数类型
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
- 语法
注意:聚合函数不允许嵌套使用
1.1 AVG和SUM函数
可以对数值型数据使用AVG 和 SUM 函数。
他们在计算有空值的时候，会把非空计算进去，然后自动忽略空值
AVG=SUM/COUNT
mysql>select*from 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|+-------+--------+-----------+------+------------+---------+---------+--------+14rowsinset(0.00 sec)
1.2 MIN和MAX函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
1.3 COUNT函数
- COUNT(*)返回表中记录总数，适用于任意数据类型。
mysql>selectcount(*)from emp;+----------+|count(*)|+----------+|14|+----------+1rowinset(0.01 sec)
- 计算指定字段再查询结果中出现的个数
mysql>selectcount(comm)from emp;+-------------+|count(comm)|+-------------+|4|+-------------+1rowinset(0.00 sec)
- COUNT(expr) 返回expr不为空的记录总数。
- -*问题：用count()，count(1)，count(列名)谁好呢?*其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。Innodb引擎的表用count(),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。
- *问题：能不能使用count(列名)替换count()?*不要使用 count(列名)来替代
count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。说明： count()会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。 这样子讲的话，大家可能还比较懵，接下来，我来演示一下
2.group by
使用group by可以进行分组，我们以前使用avg可以求出所有员工的平均工资，但是如果我们想要求各个部门的员工的平均工资的话，就得对部门进行分组，以部门为单位来划分，然后求出他们各自的平均工资
注意:字段不可以和多行函数一起使用，因为记录个数不匹配，这样就会导致查询的数据没有全部展示，但是，如果这个字段属于分组是可以的
mysql>select deptno,avg(sal)from emp groupby deptno;+--------+-------------+| deptno |avg(sal)|+--------+-------------+|20|2175.000000||30|1566.666667||10|2916.666667|+--------+-------------+3rowsinset(0.00 sec)
统计各个岗位的平均工资
mysql>select job,avg(sal)from emp groupby job;+-----------+-------------+| job |avg(sal)|+-----------+-------------+| CLERK |1037.500000|| SALESMAN |1400.000000|| MANAGER |2758.333333|| ANALYST |3000.000000|| PRESIDENT |5000.000000|+-----------+-------------+5rowsinset(0.00 sec)
3.使用having进行分组后的筛选
使用having的条件:
1 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
统计各个部门的平均工资 ,只显示平均工资2000以上的 - 分组以后进行二次筛选 having
mysql>select deptno,avg(sal)from emp
->groupby deptno
->havingavg(sal)>2000;+--------+-------------+| deptno |avg(sal)|+--------+-------------+|20|2175.000000||10|2916.666667|+--------+-------------+2rowsinset(0.01 sec)
五、where和having的对比
区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。
小结如下：
开发中的选择：
WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。
六、select的执行过程
1.关键字顺序
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…
2.SELECT 语句的执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：
SELECTDISTINCT player_id, player_name,count(*)as num 顺序 5FROM player JOIN team ON player.team_id = team.team_id 顺序 1WHERE height >1.80 顺序 2GROUPBY player.team_id 顺序 3HAVING num >2 顺序 4ORDERBY num DESC 顺序 6LIMIT2 顺序 7
3.SQL的执行原理(先了解)
SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
- 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
- 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
- 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
当我们拿到了查询数据表的原始数据，也就是最终的虚拟表
vt1
，就可以在此基础上再进行
WHERE 阶段
。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表
vt2
。
然后进入第三步和第四步，也就是
GROUP 和 HAVING 阶段
。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表
vt3
和
vt4
。
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到
SELECT 和 DISTINCT 阶段
。
首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表
vt5-1
和
vt5-2
。
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是
ORDER BY 阶段
，得到虚拟表
vt6
。
最后在 vt6 的基础上，取出指定行的记录，也就是
LIMIT 阶段
，得到最终的结果，对应的是虚拟表
vt7
。
当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。
本文转载自: https://blog.csdn.net/qq_52797170/article/details/123541334
版权归原作者 Java的学习之路 所有， 如有侵权，请联系我们删除。
版权归原作者 Java的学习之路 所有， 如有侵权，请联系我们删除。