二. DML
1. INSERT
INSERT INTO TABLE_NAME VALUES( VALUE1, VAULE2, ...)
INSERT INTO TABLE_NAME(COL1, COL2, ...) VALUES( VALUE1, VAULE2, ...)
INSERT INTO TABLE_NAME SELECT * FROM TABLE_NAME2
2. UPDATE
2.1 直接更新
最原始的更新数据的方式
UPDATE TABLE_NAME
SET COLUMN_NAME1 = VALUE1
WHERE CONDITIONS COMMIT;
2.2 关联更新
与其他表关联进行数据的更新
UPDATE TABLE_NAME1
SET COLUMN_NAME1 = (SELECT ...) -- 子查询
WHERE EXISTS (SELECT ...)
- WHERE EXISTS 是为了防止两个表没有关联上的数据不会被更新为 NULL
- 子查询中的表有多少行数据，就要被扫描多少次，性能低
2.3 MERGE INTO
利用MERGE INTO 进行关联更新
条件匹配就更新，不匹配则进行插入
MERGE INTO TABLE_NAME1 USING TABLE_NAME2 ON (CONDITIONS)
WHEN MATCHED THEN
(UPDATE SET TBALE_NAME1.COLUMN_NAME1 = ...)
WHEN NOT MATCHED THEN
(INSERT ...)
COMMIT;
3. DELETE
删除重复数据，rowid 是oracle 数据库特有的
只保留两次重复的数据
delete from t where rowid in
(select A.rowid
from
(select id, row_number() over(partition by id order by rowid) rn, rowid from t) A
where A.rn >2 )
4. SELECT
4.1 ROWNUM | ROWID
ROWNUM 是伪列 回根据返回的行数，进行编号
ROWID 是伪列 oracle 特有的，每一行的物理地址
适用场合：
- 限制返回行数
- 给查询的数据 进行编号
- 分页 - 每页显示的行数是固定的
Oracle 通用的 正确的分页框架
SELECT *
FROM(SELECT *
FROM(SELECT T.*, ROWNUM AS RN
FROM (需要分页的SQL)T)
WHERE ROWNUM <= 10)
WHERE RN >= 1
第一页 (每三行分一页)
SELECT *
FROM(SELECT *
FROM(SELECT T.*, ROWNUM AS RN
FROM (SELECT * FROM TABLE ORDER BY COL1)T)
WHERE ROWNUM <= 3)
WHERE RN >= 1
第二页 (每三行分一页)
SELECT *
FROM(SELECT *
FROM(SELECT T.*, ROWNUM AS RN
FROM (SELECT * FROM TABLE ORDER BY COL1)T)
WHERE ROWNUM <= 6)
WHERE RN >= 4
4.2 BETWEEN
BETWEEN 1500 AND 2500， >=1500 AND <= 2500
5. 函数
5.1 DECODE
DECODE（条件，值1，返回值1，值2，返回值2，缺失值）
5.2 CASE WHEN
CASE
WHEN condition THEN result
WHEN condition THEN result
...
ELSE result
END
5.3 LISTAGG
把几行数据揉合在一起
LISTAGG (COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_NAME2 | NULL )
SELECT SNAME, LISTAGG(GRADE,',') WITHIN GROUP (ORDER BY SNAME)
FROM STUDENT_INFO
GROUP BY SNAME
5.4 LAG
当前记录的id，以及上n条记录的id
SELECT
ID,
LAG(ID,N, NULL) OVER (ORDER BY ID) NEXT_RECORD_ID,
NAME
FROM STUDENT
ORDER BY ID ASC
5.5 LEAD
当前记录的id，以及下n条记录的id
SELECT
ID,
LEAD(ID,N, NULL) OVER (ORDER BY ID) NEXT_RECORD_ID,
NAME
FROM STUDENT
ORDER BY ID ASC
5.6 FIRST_VALUE
默认统计范围是ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
FIRST_VALUE(COLUMN_NAME) [IGNORE NULLS ] OVER(PARTITION BY ORDER BY ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
5.7 LAST_VALUE
注意使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
LAST_VALUE(COLUMN_NAME) [IGNORE NULLS ] OVER(PARTITION BY ORDER BY ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
5.8 ROWS BETWEEN
rows between ... preceding and ... following 在..之前 和 在... 之后的所有记录
适用场合：
- 指定一批记录： 例如 从当前记录开始直至某个部分的最后一条记录结束
- 指定一个时间间隔: 例如 在交易日之前的前30天
- 指定一个范围值： 例如 所有占到当前交易量总额5%的记录
表中的所有记录
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
当前记录 以及当前记录以前的所有记录
可以适用于滚动统计
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
SELECT
MONTH,
SUM(SALES)OVER(ORDER BY MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL_SALES
FROM ORDERS
GROUP BY MONTH
