注意）Oracle中的字符串是严格区分大小写的日期和字符只能在单引号中出现
）把列与列列与字符连接在一起用 ||符号
）列的别名紧跟列名也可以在列名和别名之间加入关键字AS以便在别名中包含空格或特殊的字符并区分大小写使用双引号
例子
SELECT last_name || is a || job_id AS Employee Details
FROM employees
where first_name like %s_;
通用函数
空值是无效的未指定的未知的或不可预知的值空值不是空格或者 包含空值的数学表达式的值都为空值
这些函数适用于任何数据类型同时也适用于空值
NVL (expr expr)
NVL (expr expr expr)
NULLIF (expr expr)
COALESCE (expr expr exprn)
（）NVL (expr expr) >expr为NULL返回expr不为NULL返回expr注意两者的类型要一致
（）NVL (expr expr expr) >expr不为NULL返回expr为NULL返回exprexpr和expr类型不同的话expr会转换为expr的类型
（）NULLIF (expr expr) >相等返回NULL不等返回expr
（）COALESCE(expr expr exprn) >列表中第一个非空的表达式是函数的返回值如果所有的表达式都是空值最终将返回一个空值
条件表达式在SQL语句中使用IFTHENELSE 逻辑可以使用两种方法:–CASE表达式 CASE expr WHEN comparison_expr THEN return_expr [WHEN comparison_expr THEN return_expr WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
–DECODE函数 DECODE ( col | expression search result [ search result] [ default] )
分组函数
分组函数作用于一组数据并对一组数据返回一个值
组函数语法
SELECT [column] group_function(column)
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
注意在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
(所用包含于SELECT列表中而未包含于组函数中的列都必须包含于GROUPBY子句中)
但包含在GROUP BY 子句中的列不必包含在SELECT列表中
且可以在在GROUP BY子句中包含多个列
不能在WHERE子句中使用组函数但可以在HAVING子句中使用组函数
使用HAVING过滤分组
行已经被分组
使用了组函数
满足HAVING子句中条件的分组将被显示
SELECT column group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
子查询
语法
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
注意子查询(内查询) 在主查询之前一次执行完成
子查询的结果被主查询使用(外查询)
子查询要包含在括号内
将子查询放在比较条件的右侧
除非进行TopN 分析否则不要在子查询中使用ORDER BY子句
单行操作符对应单行子查询多行操作符对应多行子查询
单行操作符operator 多行操作符operator
= 等于 IN 等于列表中的任何一个
> 大于 ANY 和子查询返回的任意一个值比较
>= 大于等于 ALL 和子查询返回的所有值比较
< 小于 EXISTS 功能等价于IN
<= 小于等于
<> 不等于
DECODE和CASESELECT中除GROUP BY 子句以外的所有子句中都可以使用单行子查询
在子查询中使用HAVING子句
SELECT select_list
FROM table
[GROUP BY group_by_expression]
[HAVING group_condition] expr operator
(SELECT select_list
FROM table);
在FROM子句中使用子查询例子
SELECT alast_name asalary adepartment_id bsalavg
FROM employees a (SELECT department_id AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE adepartment_id = bdepartment_id
AND asalary > bsalavg;
有两个简单例子以说明 exists和in的效率问题
) select * from T where exists(select from T where Ta=Ta) ;
T数据量小而T数据量非常大时T<<T 时) 的查询效率高
) select * from T where Ta in (select Ta from T) ;
T数据量非常大而T数据量小时T>>T 时) 的查询效率高
exists 用法
请注意 ）句中的有颜色字体的部分 理解其含义
其中 select from T where Ta=Ta 相当于一个关联表查询相当于
select from TT where Ta=Ta
但是如果你当当执行 ） 句括号里的语句是会报语法错误的这也是使用exists需要注意的地方
exists（xxx）就表示括号里的语句能不能查出记录它要查的记录是否存在
因此select 这里的 其实是无关紧要的换成*也没问题它只在乎括号里的数据能不能查找出来是否存在这样的记录如果存在这 ） 句的where 条件成立
in 的用法
继续引用上面的例子
) select * from T where Ta in (select Ta from T)
这里的in后面括号里的语句搜索出来的字段的内容一定要相对应一般来说T和T这两个表的a字段表达的意义应该是一样的否则这样查没什么意义
打个比方TT表都有一个字段表示工单号但是T表示工单号的字段名叫ticketidT则为id但是其表达的意义是一样的而且数据格式也是一样的这时用 ）的写法就可以这样
select * from T where Tticketid in (select Tid from T)
EXISTS操作符
EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行
– 不在子查询中继续查找
– 条件返回TRUE
如果在子查询中不存在满足条件的行
– 条件返回FALSE
– 继续在子查询中查找
相关子查询
相关子查询按照一行接一行的顺序执行主查询的每一行都执行一次子查询子查询使用了主查询中的列
SELECT column column
FROM table outer
WHERE columnk operator (SELECT colum colum
FROM table
WHERE expr= outerexpr);
相关更新
使用相关子查询依据一个表中的数据更新另一个表的数据
UPDATE table alias
SET column = (SELECT expression
FROM table alias
WHERE lumn = lumn);
相关删除
使用相关子查询依据一个表中的数据删除另一个表的数据
DELETE FROM table alias
WHERE column operator (SELECT expression
FROM table alias
WHERE lumn = lumn);
WITH子句
使用WITH子句可以避免在SELECT语句中重复书写相同的语句块
WITH子句将该子句中的语句块执行一次并存储到用户的临时表空间中
使用WITH子句可以提高查询效率
我们可以使用WITH Clause来定义一个query block然后在SELECT statement的其它地方使用这个query block如果在一个很复杂的 Query 里我们必须重复地使用某个 subquery那么使用WITH Clause可以降低Query的复杂度以及提高 performance因为WITH Clause 所读出的资料会暂存在User的temporary tablespace中
WITH子句应用举例
WITH
dept_costs AS (SELECT ddepartment_name SUM(esalary) AS dept_total
FROM employees e departments d
WHERE edepartment_id = ddepartment_id
GROUP BY ddepartment_name)
avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
GROUP BY 扩展
带有ROLLUP和CUBE操作的GROUP BY子句
使用带有ROLLUP和CUBE操作的GROUP BY子句产生多种分组结果
ROLLUP产生n + 种分组结果其是对GROUP BY子句的扩展
CUBE产生的n次方种分组结果其是对GROUP BY子句的扩展
注其中的n指的是group_by_expression的数目
ROLLUP操作符 ROLLUP产生n + 种分组结果顺序是从右向左
SELECT [column] group_function(column)
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
CUBE操作符CUBE会产生类似于笛卡尔集的分组结果
SELECT [column] group_function(column)
FROM table
[WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
GROUPING函数能够实现更加直观的分组结果显示提示
SELECT [column] group_function(column) [GROUPING(group_by_expression)]
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
GROUPING函数可以和CUBE或ROLLUP结合使用
使用GROUPING函数可以找到哪些列在该行中参加了分组
使用GROUPING函数 可以区分空值产生的原因
GROUPING函数返回 或
GROUPING SETS
GROUPING SETS是对GROUP BY子句的进一步扩充
使用GROUPING SETS可以实现在同一个查询中定义多个分组集
Oracle 对GROUPING SETS子句指定的分组集进行分组后用UNION ALL操作将各分组结果结合起来
Grouping set 的优点
– 只进行一次分组即可
– 不必书写复杂的UNION语句
– GROUPING SETS中包含的分组项越多性能越好
以下例子实现了对department_id job_id分组对job_id manager_id分组最终形成两个分组
SELECT department_id job_id manager_id avg(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id job_id) (job_id manager_id));
复合列
复合列是被作为整体处理的一组列的集合
使用括号将若干列组成复合列在ROLLUP或CUBE中作为整体进行操作
在ROLLUP或CUBE中复合列可以避免产生不必要的分组结果
例如GROUP BY ROLLUP( department_id (job_id manager_id)); //小括号实现复合列
连接分组集
连接分组集可以产生有用的对分组项的结合
将各分组集ROLLUP 和CUBE用逗号连接Oracle自动在GROUP BY子句中将各分组集进行连接
连接的结果是对各分组生成笛卡尔集
例如GROUP BY GROUPING SETS(a b) GROUPING SETS(c d);
例如GROUP BY department_id ROLLUP(job_id) CUBE(manager_id);
