oracle总结（四）

1、嵌套函数：
单行函数可以嵌套任意层；嵌套函数从最深层开始执行。


2、条件表达式：
CASE 表达式（简单 CASE） 
语法：
CASE 表达式 
WHEN 条件 1 THEN 返回值 1 
WHEN 条件 2 THEN 返回值 2 
. 
. 
. 
WHEN 条件 n THEN 返回值 n 
ELSE  返回值 
END


DECODE 函数 
语法：
DECODE（ 
 条件,
 比较值 1，返回值 1 
 比较值 2，返回值 2  
. 
. 
. 
 比较值 n，返回值 n
 返回值（不满足条件时）   
）


3、从多表中显示数据
等值查询：自然连接（内连接）使用 AND 操作符增加查询条件
使用表的别名来简化查询，提高查询功能
多表等值连接查询，为了连接 N 个表，至少需要 N-1 个连接条件


非等值查询 
使用 BETWEEN AND 查询近似值作为连接条件的多表结果


外连接查询
SELECT T1.COL,T2.COL FROM WHERE T1.COL(+)=T2.COL;左外连接所有 T2 的 T1 信息
SELECT T1.COL,T2.COL FROM WHERE T1.COL=T2.COL(+);右外连接所有 T1 的 T2 信息
为了看到与连接条件不匹配的数据，就必须得用外连接
左外连接(左边的表不加限制) 
右外连接(右边的表不加限制) 
全外连接(左右两表都不加限制) 


自连接 
通过表的别名来创建虚拟逻辑表,进行自连接查询。


交叉连接
交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积，返回结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数
Cross join
所谓笛卡尔积,通俗点说就是指包含两个集合中任意取出两个元素构成的组合的集合。假设R中有元组M个，S中有元组N个，则R和S的笛卡尔积中包含的元组数量就是M*N。这个规则可以向多个关系扩展。


用字函数产生的总计
对多行的计算产生单行的结果。组函数用语对每个组的行集进行运算，每个组产生一个结果。
AVG([DISTINCT/ALL]col)
只能用与数字。只能对多行的数据进行运算，不能在这个函数中做单
行的数学运算。
CORR(x1,x2)
返回表达式 X1 和 X2 组成的集合的相关系数。在保证所有行中的 X1 和 X2 都不为 NULL 之后结果
通过
COUNT([DISTINCT/ALL]col)
所有非空字段的行数。
MAX([DISTINCT/ALL]col)
可以用于任何类型，当用于日期类型时代表最晚。忽略空值。字符类型时候，比较字符串首字母的 ASCLL 值。 
MIN([DISTINCT/ALL]col)
可以用于任何类型，当用于日期类型时代表最早。忽略空值。字符类型时候，比较字符串首字母的 ASCLL 值。
SUM([DISTINCT/ALL]col)
返回选择列表项目的总和，只能用于数字。 
STDDEV([DISTINCT/ALL]col)
标准方差


select col,group function(col) from table where 条件 group by col; 
GROUP BY 
必须：出现在查询列表中的一个字段，但没有出现在函数中，那么这个字段必须要出现在 GROUP 
BY 中。 
可以：出现在 GROUP BY 子句中的字段可以不出现在查询列表中。 
执行顺序：先排列，再运算。 
WHERE 子句中不能使用 group function。 

限制组必须使用 HAVING 子句。

eg：HAVING SUM(OrderPrice)<2000

select col,group function from table 
where 条件//可以没有条件限制
group by col having group_condition //组过滤，在过滤以后，再进行分组计算。
order by col;
组函数嵌套最多只能有两层。

select max(avg(salary)) from employees group by employee_id;

子查询
select col from table 
where expr operator (select col from table); 
子查询在主查询执行前执行一次。子查询的结果被用于主查询。
使用规则： 
在 WHERE 和 HAVING 子句中都可以使用子查询。 
子查询必须用括号扩起。 
子查询应该在比较条件的右边。 
在子查询中的 ORDER BY 子句不需要，除非执行 TOP-N 分析。 
TOP-N 分析： （在一些表里求出最怎么怎么怎么样（最好、做多...）的几个人）。 
对单行子查询使用单行比较操作符，多行子查询使用多行比较操作符。 
可以在子查询中使用组函数。


子查询的分类： 
单行单列子查询：一定返回一行 
单行操作符：>,<,=,>=,<=,<> 
多行单列子查询 
单行多列子查询：返回零行或多行 
多行操作符： 
 in 等于列表中的任何值。（不能用 NOT IN） 
 ANY 与子查询返回的每个值进行比较。(小于是小于最大的，大于是大于最小的)  
 select employee_id,last_name,job_id,salary from smployees 
 where 
 salary < any(select salary from employees where job_id='IT_PROG') 
 and job_id <> 'IT_PROG'; 
 ALL（小于是小于最小的，大于是大于最大的）


ps：
in：in是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
not in：内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快
只要not in的子查询中包含空值，那么最终的结果就为空。
对于not exists查询，内表存在空值对查询结果没有影响；对于not in查询，内表存在空值将导致最终的查询结果为空。
对于not exists查询，外表存在空值，存在空值的那条记录最终会输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。
not in会转化为 a <> ? and b <> ? and c <>?