常用设置
set verify on/off ------ 使用替代变量时是否显示旧值和新值 set heading on/off ----- 列头 set feedback on/off ---- 结果集数量 spool a.txt -- 输出查询结果到文件 spool off ----- 终止输出到文件 define _EDITOR = vim ----- sqlplus使用的编辑器
GROUP 子句不能使用列别名:
SELECT --------------- 5 FROM ----------------- 1 WHERE ---------------- 2 GROUP BY ------------- 3 HAVING --------------- 4 ORDER BY ------------- 6
特殊字符
'''' ------------------- 单引号(最外侧的单引号之内,每两个单引号表示一个单引号) q'[.*]' ---------------- []可替换为任意单个字符,中间的内容保持原样 'S\_%' escape '\' ------- 使用通配符时,如果要匹配的字符串中包含通配符,使用转义
替代变量
&var ----- 数字等 '&var' ---- 字符串、日期等 &&var --- 替代变量,同时有隐式define where name = '&var' ---- 如果name包含&字符,则此处将失败 set define "&" ----------- 默认 set define off ------------ 禁用&的定义 set define on ------------ 启用&的定义 set define # ------------- 定义为其他字符(off、on之后恢复为&) where name = '&'||'var' ---- 通过拼接来实现把&当作普通字符
关于null
IN -------------- = ANY(100,null) => 结果正确 NOT IN ----- <> ALL(100,null) => 始终 no rows selected order by 1 asc ------------------------ null 显示在最后 order by 1 asc nulls first ---------- null 显示在最前 order by 1 desc ---------------------- null 显示在最前 order by 1 desc nulls last -------- null 显示在最后
字符串函数
lower ---- 小写 upper ---- 大写 initcap --- 单词首字母大写 concat --- 连接 substr ---- 取子串 length ---- 求长度 instr ------- 找位置 lpad ------- 左填充 rpad ------- 右填充 trim -------- 去空字符/指定字符 replace --- 替换
数字函数
round -- 四舍五入 trunk --- 截断 mod ---- 取余
日期函数
sysdate --- 系统当前日期 round ------ 四舍五入 trunk ------- 截断 months_between(date1, date2) --- date1 - date2转换为月 add_months next_day --- 指定日期之后的下一个星期 last_day ---- 指定日期的月份的最后一天
关于星期的说明:
星期的第一天默认是周日,要把周一当作星期的第一天,可以转换之前把日期减1
select to_char(sysdate, 'Day'); => select to_char(sysdate - 1, 'Day');
单行函数
nvl ----- 为空返回指定的值 nvl2 --- 为空返回指定的值,否则返回另一个指定的值 nullif -- 两值是否相等,相等返回null,否则返回第一个值 COALESCE -- 返回第一个非空值
条件表达式
case expr when ... then ... when ... then ... else ... end decode (col|expr, search1, result1, search2, result2, default) -- 行列转换 select name, decode(course, 'yuwen', grade) yuwen, decode(course, 'math', grade) math, decode(course, 'english', grade) english from test; NAME YUWEN MATH ENGLISH ---------- ---------- ---------- ---------- zs 10 zs 20 zs 30 ls 40 ls 50 ls 60 ww 70 ww 80 ww 90 select name,max(yuwen) yuwen,max(math) math,max(english) english from (select name, decode(course, 'yuwen', grade,'') yuwen, decode(course, 'math', grade,'') math, decode(course, 'english', grade,'') english from test) a group by name; NAME YUWEN MATH ENGLISH ---------- ---------- ---------- ---------- ls 40 50 60 zs 10 20 30 ww 70 80 90
组函数 (多条记录返回一个值,忽略空值)
count sum min max avg count(*), count(1), count(0) --总行数(记录中的所有信息为空也计算在内) count(null) -- 0行 count(col) -- 不为空的行数 -- 计算有提成的人数 select count(case when COMMISSION_PCT is not null then 1 else null end) from employees; select count(COMMISSION_PCT) from employees; -- 对有空值的列求平均值 select avg(nvl(COMMISSION_PCT, 0)) from employees;
说明: select目标列中只能出现group by中的列和组函数
rownum 分页
-- 不带排序的分页 rownum select * from ( select rownum rn, last_name, salary from employees where rownum <= &n * 10) a where rn > (&n - 1) * 10; -- 排序的分页 rownum select * from ( select rownum rn, last_name, salary from ( select last_name, salary from employees order by last_name) a where rownum <= &n * 10) b where rn > (&n - 1) * 10; -- 排序的分页 分析函数: row_number() select * from ( select row_number() over(order by last_name) rn, last_name, salary from employees) where rn between (&n - 1) * 10 and &n * 10; A 1 A 2 B 3
分析函数: row_number()
-- 不分组编号(相同的值编号递增) select row_number() over(order by salary) rn, salary from employees -- 在分组内编号(同一分组内相同的值编号递增) select department_id, row_number() over(partition by department_id order by salary) rn, salary from employees; A 1 A 2 B 1
分析函数: rank()
-- 不分组编号(相同的值编号不递增,计数) select department_id, rank() over(order by salary) rn, salary from employees; -- 在分组内编号(同一分组内相同的值编号不递增,计数) select department_id, rank() over(partition by department_id order by salary) rn, salary from employees; A 1 A 1 B 3
分析函数: dense_rank()
select department_id, dense_rank() over(order by salary) rn, salary from employees; -- 在分组内编号(同一分组内相同的值编号不递增,不计数) select department_id, dense_rank() over(partition by department_id order by salary) rn, salary from employees; A 1 A 1 B 2
注意: 分析函数不能出现在WHERE子句中
多表连接
nature join -- 两表中列名相同的进行自然连接 using (column_name) -- 指定两表中相同列名的公共列进行自然连接 on () LEFT [OUTER] JOIN: 保留左侧表中的记录 -- oracle中把(+)放在右边 RIGHT [OUTER] JOIN: 保留右侧表中的记录 -- oracle中把(+)放在左边 FULL [OUTER] JOIN: 保留两侧表中的记录 CROSS JOIN: 笛卡尔积 (不含关联条件或关联条件无效的连接) -- SQL 1999 t1 join t2 on () join t3 on () ... -- Oracle t1, t2, t3, ... where ...
集合操作
union all -- 并集(不排序不去重) union -- 并集(排序去重) intersect -- 交集(排序去重) minus -- 差集(排序去重) -- 自定义的排序操作要放在集合操作之后
group增强
rollup方式增强
--group by rollup(a,b,c) 会产生n+1种分组计算的结果 group by rollup(a, b, c) group by a group by a, b group by a, b, c -- 相当于 group by a union group by a,b union group by a,b,c union total
cube方式增强
-- group by cube(a,b,c),产生2的n次方种分组计算结果 group by cube (a, b, c) group by a group by b group by c group by a, b group by a, c group by b, c group by a, b, c -- 相当于 group by a union group by b union group by c union group by a,b union group by b,c union group by a,c union group by a,b,c union total
复合列的group by
select deptno,job,mgr,sum(sal) from emp group by rollup(deptno,(job,mgr)); -- 括号中的两列当作一列进行group by: deptno --> A (job,mgr) --> B group by A,B
grouping sets
select deptno,job,sum(sal) from emp group by grouping sets (deptno,job),(job); -- 返回的结果相当于 group by deptno,job union all group by job
grouping
grouping(col1) -- 把按照col1分组之后的小计标记出来(显示为1)
1: 不参与小计/总计运算,可用于显示小计/总计
0: 参与小计/总计运算 A B ---------- ---------- 100 1000 200 2000 300 3000 300 4000 SELECT A, B, GROUPING(A), GROUPING(B) FROM T1 GROUP BY ROLLUP(A,B); A B COUNT(*) GROUPING(A) GROUPING(B) ---------- ---------- ---------- ----------- ----------- 100 1000 1 0 0 100 1 0 1 200 2000 1 0 0 200 1 0 1 300 3000 1 0 0 300 4000 1 0 0 300 2 0 1 4 1 1
-- A,B2个字段分组之后的小计是按照A单独分组的结果 -- 最后总计就是不分组统计的结果或者是按照A分组之后的结果的和
grouping_id
-- 返回所有grouping列组合位向量(二进制值)的十进制数: col f for a4 select deptno,job,sum(sal),grouping(deptno)||grouping(job) f,grouping_id(deptno,job) b from emp group by rollup(deptno,job) order by deptno; DEPTNO JOB SUM(SAL) F B ------ --------- ---------- ---- ---------- 10 CLERK 1300 00 0 10 MANAGER 2450 00 0 10 PRESIDENT 5000 00 0 10 8750 01 1 20 ANALYST 6000 00 0 20 CLERK 1900 00 0 20 MANAGER 2975 00 0 20 1087 01 1 30 CLERK 950 00 0 30 MANAGER 2850 00 0 30 SALESMAN 5600 00 0 30 9400 01 1 29025 11(二进制) 3 (十进制) --打印如下结果集 DEPTNO JOB SUM(SAL) ------ --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 subtotal 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 subtotal 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 subtotal 9400 total 29025
Merge
MERGE <hint> INTO <table_name> USING <table_view_or_query> ON (<condition>) WHEN MATCHED THEN <update_clause> WHEN NOT MATCHED THEN <insert_clause>;
multiTable Inserts
Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This
statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's
main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format:
Unconditional insert into ALL tables
INSERT ALL INTO sal_history VALUES(empid,hiredate,sal) INTO mgr_history VALUES(empid,mgr,sysdate) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id > 200;
Conditionally insert into ALL tables
INSERT ALL WHEN SAL>10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR>200 THEN INTO mgr_history VALUES(EMPID,MGR,SYSDATE) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id > 200;
Insert into the FIRST table with a matching condition
INSERT FIRST WHEN SAL > 25000THEN INTO special_sal VALUES(DEPTID,SAL) WHEN HIREDATE like ('%00%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE) WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID,HIREDATE) ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE) SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE FROM employees GROUP BY department_id;
Pivoting insert to split non-relational data
INSERT ALL INTO Sales_info VALUES (employee_id,week_id,sales_MON) INTO Sales_info VALUES (employee_id,week_id,sales_TUE) INTO Sales_info VALUES (employee_id,week_id,sales_WED) INTO Sales_info VALUES (employee_id,week_id,sales_THUR) INTO Sales_info VALUES (employee_id,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM Sales_source_data;
-- The restrictions on multitable INSERTs are: -- -- Multitable inserts can only be performed on tables, not on views or materialized views. -- You cannot perform a multitable insert via a DB link. -- You cannot perform multitable inserts into nested tables. -- The sum of all the INTO columns cannot exceed 999. -- Sequences cannot be used in the subquery of the multitable insert statement.