Oracle学习笔记

 

常用设置

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. 

 

转载于:https://www.cnblogs.com/goooogs/p/4051791.html