一、多表查询
所有的连接分析，参考之前随笔：http://www.cnblogs.com/jiangbei/p/7420136.html
1.笛卡尔积
select last_name, department_name
from employees, departments
产生无效的笛卡尔积可以通过where进行过滤
连接查询：
2.等值连接
select e.last_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id
3.外连接
SQL99的连接语法如下：
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)];
，这里介绍Oracle的左外连接写法：
--左外连接（e left join d）
select e.last_name,e.department_id, d.department_name
from employees e,departments d
where e.department_id = d.department_id(+)
自然连接可以省略连接条件，自动匹配所有相同的行进行笛卡尔积去重！
--自然连接（此种形式下无法使用表别名）
select employees.last_name,employees.department_id, departments.department_name
from employees NATURAL JOIN departments
CROSS JOIN叉集——与笛卡尔积相同
--CROSS JOIN叉集
SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;
自连接
--自连接
SELECT
e1.last_name,e2.last_name,e2.salary,e2.email
FROM
employees e1,employees e2
WHERE
e1.manager_id = e2.employee_id
AND
LOWER(e1.last_name) = 'chen'
二、分组函数
1.组类型函数
AVG 返回平均值
COUNT 返回记录的数量（非空数目）
COUNT(DISTINCT expr)返回expr非空且不重复的记录总数
MAX 返回最大值
MIN 返回最小值
SUM 返回总值
STDDEV 返回标准差
VARIANCE 返回统计方差
方差与标准差的拓展阅读：点击查看
示例：
SELECT AVG(salary),SUM(salary),MAX(salary),MIN(salary) FROM employees
AVG(SALARY) SUM(SALARY) MAX(SALARY) MIN(SALARY) ----------- ----------- ----------- ----------- 6461.682242 691400 24000 2100
2.分组——GROUP BY
查询列仅能是分组列或组函数（聚合函数）
SELECT department_id,ROUND(AVG(salary),2) sal_avg FROM employees GROUP BY department_id
DEPARTMENT_ID SAL_AVG ------------- ---------- 100 8600 30 4150 7000 90 19333.33 20 9500 70 10000 110 10150 50 3475.56 80 8955.88 40 6500 60 5760 10 4400
与WHERE的搭配，注意SQL的顺序：（这里是先选出符合条件的，再进行分组，分组过滤则参考HAVING）
SQL顺序（手写顺序与机读顺序参考之前随笔：http://www.cnblogs.com/jiangbei/p/7420136.html）
SELECT department_id,ROUND(AVG(salary),2) sal_avg
FROM employees
WHERE department_id IN (10,20,30)
GROUP BY department_id
3.分组过滤HAVING
WHERE中不能使用组函数过滤，于是HAVING登场..
示例：
SELECT department_id,ROUND(AVG(salary),2) sal_avg
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 7500
4.组函数支持嵌套
SELECT MAX(AVG(salary)) sal_avg
FROM employees
GROUP BY department_id