子查询
8、分组函数/聚合函数/多行处理函数
| count |
取得记录数 |
| sum |
求和 |
| avg |
取平均 |
| max |
取最大的数 |
| min |
取最小的数 |
注意：分组函数自动忽略空值，不需要手动的加where 条件排除空值。select count(*) from emp where xxx; 符合条件的所有记录总数。select count(comm) from emp; comm 这个字段中不为空的元素总数。
注意：分组函数不能直接使用在where 关键字后面。mysql> select ename,sal from emp where sal > avg(sal); ERROR 1111 (HY000): Invalid use of group function
8.1 、count
l 取得所有的员工数
| select count(*) from emp; |
Count(*)表示取得所有记录，忽略 null，为 null 的值也会取
得
l 取得津贴不为 null 员工数
| select count(comm) from emp; |
采用 count(字段名称)，不会取得为null 的记录
l 取得工作岗位的个数
| select count(distinct job ) from emp; |
8.2 、sum
l Sum 可以取得某一个列的和，null 会被忽略
l 取得薪水的合计
| select sum(sal) from emp; |
l 取得津贴的合计
| select sum(comm) from emp; |
null 会被忽略
l 取得薪水的合计（sal+comm）
| select sum(sal+comm) from emp; |
从以上结果来看，不正确，原因在于 comm 字段有null 值，所以无法计算，sum 会忽略掉，正确的做法是将 comm 字段转换成 0
| select sum(sal+IFNULL(comm, 0)) from emp; |
8.3 、avg
取得某一列的平均值
l 取得平均薪水
| select avg(sal) from emp; |
8.4 、max
取得某个一列的最大值
l 取得最高薪水
| select max(sal) from emp; |
l 取得最晚入职得员工
| select max(str_to_date(hiredate, '%Y-%m-%d')) fromemp; |
8.5 、min
取得某个一列的最小值
l 取得最低薪水
| select min(sal) from emp; |
l 取得最早入职得员工（可以不使用str_to_date 转换）
| select min(str_to_date(hiredate, '%Y-%m-%d')) from emp; |
8.6 、组合聚合函数
可以将这些聚合函数都放到 select 中一起使用
| select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp; |
9、分组查询
分组查询主要涉及到两个子句，分别是：groupby 和 having
9.1 、group by
l 取得每个工作岗位的工资合计，要求显示岗位名称和工资合计
| select job, sum(sal) from emp group by job; |
如果使用了 order by，order by 必须放到group by 后面
l 按照工作岗位和部门编码分组，取得的工资合计
n 原始数据
n 分组语句
| select job,deptno,sum(sal) from empgroup by job,deptno; |
mysql> select empno,deptno,avg(sal) from emp group by deptno;
+-------+ +-- +
| empno | deptno | avg(sal) |
+-------+ +-- +
| 7782 | 10 | 2916.666667 |
| 7369 | 20 | 2175.000000 |
| 7499 | 30 | 1566.666667 |
+-------+ +-- +
以上 SQL 语句在Oracle 数据库中无法执行，执行报错。
以上 SQL 语句在Mysql 数据库中可以执行，但是执行结果矛盾。
在 SQL 语句中若有group by 语句，那么在 select 语句后面只能跟分组函数+参与分组的字段。
9.2 、having
如果想对分组数据再进行过滤需要使用 having 子句取得每个岗位的平均工资大于 2000
| select job, avg(sal) from emp group by job having avg(sal) >2000; |
分组函数的执行顺序： 根据条件查询数据分组
采用 having 过滤，取得正确的数据
9.3 、select 语句总结
一个完整的 select 语句格式如下
| select 字段from 表名where ……. group by …….. having....... (就是为了过滤分组后的数据而存在的—不可以单独的出现) order by …….. |
以上语句的执行顺序
1. 首先执行 where 语句过滤原始数据
2. 执行 group by 进行分组
3. 执行 having 对分组数据进行操作
4. 执行 select 选出数据
5. 执行 order by 排序
原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤的。
10、连接查询
10.1 、SQL92 语法
连接查询：也可以叫跨表查询，需要关联多个表进行查询
l 显示每个员工信息，并显示所属的部门名称
| select ename, dname from emp, dept; |
| SQL> select ename, dname from emp, dept;
ENAME DNAME ------ -- SMITH ACCOUNTING ALLEN ACCOUNTING WARD ACCOUNTING JONES ACCOUNTING MARTIN ACCOUNTING BLAKE ACCOUNTING CLARK ACCOUNTING SCOTT ACCOUNTING KING ACCOUNTING TURNER ACCOUNTING ADAMS ACCOUNTING JAMES ACCOUNTING FORD ACCOUNTING MILLER ACCOUNTING SMITH RESEARCH ALLEN RESEARCH WARD RESEARCH JONES RESEARCH MARTIN RESEARCH BLAKE RESEARCH CLARK RESEARCH SCOTT RESEARCH KING RESEARCH TURNER RESEARCH ADAMS RESEARCH JAMES RESEARCH |
| FORD RESEARCH MILLER RESEARCH SMITH SALES ALLEN SALES WARD SALES JONES SALES MARTIN SALES BLAKE SALES CLARK SALES SCOTT SALES KING SALES TURNER SALES ADAMS SALES JAMES SALES FORD SALES MILLER SALES SMITH OPERATIONS ALLEN OPERATIONS WARD OPERATIONS JONES OPERATIONS MARTIN OPERATIONS BLAKE OPERATIONS CLARK OPERATIONS SCOTT OPERATIONS KING OPERATIONS TURNER OPERATIONS ADAMS OPERATIONS JAMES OPERATIONS FORD OPERATIONS MILLER OPERATIONS
已选择 56 行。 |
以上输出，不正确，输出了 56 条数据，其实就是两个表记录的成绩，这种情况我们称为：“笛卡儿乘积”，出现错误的原因是：没有指定连接条件
指定连接条件
| select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno; 也可以使用别名 select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno; |
以上结果输出正确，因为加入了正确的连接条件
以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）
l 取得员工和所属的领导的姓名
| select e.ename, m.ename from emp e, emp m where e.mgr=m.empno; |
| SQL> select * from emp;（普通员工）
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-12 月-80 800 20 7499 ALLEN SALESMAN 7698 20-2 月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2 月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4 月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9 月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5 月 -81 2850 30 7782 CLARK MANAGER 7839 09-6 月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4 月 -87 3000 20 7839 KING PRESIDENT 17-11 月-81 5000 10 7844 TURNER SALESMAN 7698 08-9 月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5 月 -87 1100 20 7900 JAMES CLERK 7698 03-12 月-81 950 30 7902 FORD ANALYST 7566 03-12 月-81 3000 20 7934 MILLER CLERK 7782 23-1 月 -82 1300 10
已选择 14 行。
SQL> select * from emp;（管理者）
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-12 月-80 800 20 |
| 7499 ALLEN SALESMAN 7698 20-2 月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2 月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4 月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9 月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5 月 -81 2850 30 7782 CLARK MANAGER 7839 09-6 月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4 月 -87 3000 20 7839 KING PRESIDENT 17-11 月-81 5000 10 7844 TURNER SALESMAN 7698 08-9 月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5 月 -87 1100 20 7900 JAMES CLERK 7698 03-12 月-81 950 30 7902 FORD ANALYST 7566 03-12 月-81 3000 20 7934 MILLER CLERK 7782 23-1 月 -82 1300 10
已选择 14 行。
SQL> select e.ename, m.ename from emp e, emp m where e.mgr=m.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 行。 |
以上称为“自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，如以上示例：第一个表 emp e 代码了员工表，emp m 代表了领导表，相当于员工表和部门表一样
10.2 、SQL99 语法
l （内连接）显示薪水大于 2000 的员工信息，并显示所属的部门名称
| 采用 SQL92 语法： select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno and e.sal > 2000; 采用 SQL99 语法： select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000; |
| 或 select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno where e.sal>2000; 在实际中一般不加inner 关键字 |
Sql92 语法和 sql99 语法的区别：99 语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比 sql92
更清晰
l （外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来
| 右连接： select e.ename, e.sal, d.dname from emp e right join dept d on e.deptno=d.deptno; 左连接： select e.ename, e.sal, d.dname from dept d left joinemp e on e.deptno=d.deptno; 以上两个查询效果相同 |
连接分类： 内链接
* 表 1 inner join 表 2 on 关联条件
* 做连接查询的时候一定要写上关联条件
* inner 可以省略外连接
*左外连接
* 表 1 left outer join 表 2 on 关联条件
* 做连接查询的时候一定要写上关联条件
* outer 可以省略*右外连接
* 表 1 right outer join 表 2 on 关联条件
* 做连接查询的时候一定要写上关联条件
* outer 可以省略
*左外连接（左连接）和右外连接（右连接）的区别：
*左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示
*右连接恰恰相反，以上左连接和右连接也可以加入 outer 关键字，但一般不建议这种写法，如：
| select e.ename, e.sal, d.dname from emp e right outerjoin dept d on e.deptno=d.deptno; |
| select e.ename, e.sal, d.dname from dept d left outer join emp e on e.deptno=d.deptno; |
左连接能完成的功能右连接一定可以完成
- 点赞
- 收藏
- 关注作者
评论（0）