sql(三)：多表查询、左右连接、组函数与分组统计 - 小西红柿
http://blog.csdn.net/troy__/article/details/24254595
一、多表查询
之前查询都是在一张表上进行的查询，如果使用多张表进行查询，则称为多表查询。格式如下：
- select {DISTINCT}* | 具体列名 别名
- form 表名称1 别名1，表名称2 别名2
- { where 条件(s) }
- {order by 排序的字段1 ASC | DESC，排序的字段2 ASC | DESC…..}
1. 使用多表查询，同时查询emp和dept表
- SELECT * FROM emp,dept ;
查询出来的结果条数是emp条数 * dept的条数。
说明在使用多表查询的时候会产生笛卡尔积。如果表的数据越多，笛卡尔积产生的结果就越多，想要去掉笛卡尔积，则必须使用字段进行关联的操作。
例如，使用dept字段来关联：
- SELECT * FROM emp,dept
- WHERE emp.deptno=dept.deptno ;
2. 如果表名过长，可以为其取别名
- SELECT * FROM emp e,dept d
- WHERE e.deptno=d.deptno ;
3.自关联
例：要求查询出每个雇员的姓名、工作、雇员的直接上级领导的姓名
- SELECT e.ename,e.job,m.ename
- FROM emp e,emp m
- WHERE e.mgr=m.empno ;
二、左右连接
1. （+）在左边表示右连接
例如：
- SELECT e.empno,e.ename,d.deptno,d.dname,d.loc
- FROM emp e,dept d
- WHERE e.deptno(+)=d.deptno ;
表示d表的deptno字段一定会出现，即使e表的deptno没有存在相匹配的行…
2. （+）在右边表示左连接
例如：
- SELECT e.empno,e.ename,d.deptno,d.dname,d.loc
- FROM emp e,dept d
- WHERE e.deptno=d.deptno(+) ;
表示e表的deptno字段一定会出现，即使d表的deptno没有存在相匹配的行…
三、SQL：1999语法(了解)
格式：
- SELECT table1.column,table2.column
- FROM table1 [CROSS JOIN table2]|
- [NATURAL JOIN table2]|
- [JOIN table2 USING(column_name)]|
- [JOIN table2 ON(table1.column_name=table2.column_name)]|
- [LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)];
四、组函数与分组统计
分组：例如，把所有男生分为一组，女生分为一组。
如果想求出每一组的平均身高，平均年龄等，这就需要分组函数。
1. 组函数
常用的分组函数有以下几个：
- COUNT() :求出全部的记录数
- MAX() :求出该组的最大值
- MIN(): 求出该组的最小值
- AVG():求出平均值
- SUM(): 求和
例： 求出工资最低的员工的信息
- SELECT MIN(sal) FROM emp ;
求出部门编号为20的所有人的总工资
- SELECT SUM(sal) FROM emp WHERE deptno=20 ;
2.分组统计
使用GROUP BY进行分组
- select {DISTINCT}* | 具体列名 别名
- form 表名称1 别名1，表名称2 别名2
- { where 条件(s) }
- {GROUP BY 分组条件}
- {order by 排序的字段1 ASC | DESC，排序的字段2 ASC | DESC…..}
例：求出每个部门的雇员数量
- select deptno,COUNT(empno)
- from emp
- group by deptno;
注意点：观察以下代码
- SELECT deptno,COUNT(empno) FROM emp ;
执行时出现
以上代码不能正确执行，是因为：
(1).如果程序使用了分组函数，则只能在以下两种情况中使用：
- sql中使用了GROUP BY，并指定了分组条件，这样可以将分组条件一起查询出来
- 如果不使用分组，则只能单独的使用分组函数
(2).在使用分组的时候，不能出现分组函数和分组条件之外的字段
- SELECT deptno,empno,COUNT(empno)
- FROM emp
- GROUP BY deptno ;
例：按部门分组，并显示部门的名称，及每个部门的员工数
- SELECT d.dname,COUNT(e.empno)
- FROM dept d,emp e
- WHERE d.deptno=e.deptno
- GROUP BY d.dname ;
如果需要过滤分组，则必须使用HAVING 分组条件，格式：
- select {DISTINCT}* | 具体列名 别名
- form 表名称1 别名1，表名称2 别名2
- { where 条件(s) }
- {GROUP BY 分组条件 HAVING 分组条件}
- {order by 排序的字段1 ASC | DESC，排序的字段2 ASC | DESC…..}
范例：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的合计升序排列：
- SELECT job,SUM(sal) su
- FROM emp
- WHERE job<>\’SALESMAN\’
- GROUP BY job HAVING SUM(sal)>5000
- ORDER BY su ;
分组的简单原则：
- 只有一列上出现重复的内容才可能用到分组