COUNT(字段名)中放入字段，是统计字段中的非空值有多少个
COUNT(*)/COUNT(1) 中放入* 或者1，是统计有多少行，不管查询的结果中有没有空值
COUNT(1)的执行效率更快
SUM只会统计非空值，如果SUM聚合的数据列都是空，SUM也会返回一个空
AVG,MAX,MIN: 如果字段中存在空值，会自动去掉空
AVG求平均值时分母不计算空值个数，这里的分母是非空值的个数;
AVG不能统计字符串和日期;
GROUP BY
如果已使用了分组条件或者是聚合函数，
则 SELECT 后不能出现非分组字段以及非聚合字段
-- 多重分组
-- 需求：查询每个部门每种岗位的人数和工资合计
SELECT DEPTNO,JOB,COUNT(*),SUM(SAL)
FROM EMP
GROUP BY DEPTNO,JOB;
注意：HAVING 必须依赖于 GROUP BY
--建表时如果表名或字段名使用了系统中的关键字需要带上双引号
--在查询使用了关键字作为表名的表时，也需要带上双引号
--DROP TABLE "SELECT";
CREATE TABLE "SELECT"(U_ID NUMBER(4),
UNAME VARCHAR2(10),
AGE NUMBER(3)
);
SELECT * FROM "SELECT";
-- 在工作中建表规范:尽可能不要使用关键字，也不要使用空格;
-- 错误示例：因为 SELECT 的执行顺序在 WHERE 之后
-- 所以 SELECT 后面给字段起的别名无法用于 WHERE 中
SELECT EMPNO,ENAME,SAL AS 工资
FROM EMP
WHERE 工资 > 2000
注意！！！在工作中不能轻易修改已经投产的字段名
因为在实际项目中，要求不能写 SELECT * ，要罗列具体的字段名
SELECT U_ID,UNAME,BDATE FROM USER221;
-- 注意！！！工作中不能随便删除数据，切记切记！！！
-- 大多数用不上的数据会做逻辑删除，不会物理删除
！！！表关联
内关联和外关联（左外关联、右外关联、全外关联）
--内关联：取两表的交集，关联不上的数据不输出
--内关联使用AND/WHERE过滤，结果是一致的
--外关联（左外、右外、全外） 最好都用LEFT JOIN
--①左外关联/左关联 保留主表所有数据，以及从表能够关联上的数据，从表关联不上补空
--②右关联跟左关联的区别？
结果都是保留主表数据，从表关联不上的补空值。
--右关联跟左关联一样，AND只过滤从表数据，WHERE是过滤主从表所有数据
--③全外关联 保留两表的所有数据，关联不上互相补空值
假设有两张表A表和B表，A表有个ID字段，值是 1~6，B表有个ID字段，值是3~9
问题：
A JOIN B ON A.ID=B.ID 有_4_条数据？3 4 5 6
A LEFT JOIN B ON A.ID=B.ID 有_6_条数据？1 2 3 4 5 6
A LEFT JOIN B ON A.ID=B.ID AND A.ID>3 有_6_条数据？1 2 3 4 5 6
A LEFT JOIN B ON A.ID=B.ID WHERE A.ID>3 有_3_条数据？4 5 6
A RIGHT JOIN B ON A.ID=B.ID 有_7_条数据？3 4 5 6 7 8 9
A RIGHT JOIN B ON A.ID=B.ID AND B.ID<6 有_7_条数据？3 4 5 6 7 8 9
A FULL JOIN B ON A.ID=B.ID 有_9_条数据？1 2 3 4 5 6 7 8 9
A FULL JOIN B ON A.ID=B.ID WHERE B.ID>5 有_4_条数据？6 7 8 9
SELECT ENAME,SAL,E.DEPTNO,D.DEPTNO,DNAME FROM EMP E,DEPT D; --56 --笛卡尔乘积
！！集合 & EXISTS 存在 & WITH AS
--UNION和UNION ALL的区别？
--UNION ALL是合并结果集，UNION 也是合并结果集，但会对结果进行排序去重操作
--所以UNION的执行效率一般比较低，所以一般会优先使用UNION ALL+GROUP BY 代替UNION
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
UNION(并集)，返回各个查询的所有记录，不包括重复记录。
INTERSECT(交集)，返回两个查询共有的记录。
MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
--集合运算注意事项：使用集合运算时，需要保证SELECT语句直接字段数量、对应的列数据类型一致
--EXISTS 存在
--一般用来替代IN,在数据量很大时效率比IN高
--EXISTS不关注子查询的输出值，只关注WHERE后面的条件
--EXISTS和IN的区别？
--IN是遍历循环匹配，而EXISTS是判断存在则退出匹配的过程，总体来说匹配次数比IN要少
--所以EXISTS性能比IN要高
--查询部门表没有员工的部门
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP) --40
SELECT * FROM EMP WHERE DEPTNO NOT IN (SELECT DEPTNO FROM DEPT) --错误
--使用EXISTS改写
SELECT DEPTNO
FROM DEPT D
WHERE NOT EXISTS (SELECT 1 FROM EMP E WHERE D.DEPTNO = E.DEPTNO) --40
--类似题目，目前为止学了4种方法：
1）表关联 + WHERE + IS NULL;
2) NOT IN
3) NOT EXISTS
4) 补集MINUS
---WITH AS 语句是通过定义临时结果集或视图
提高查询的效率、降低复杂度、提高可读性和灵活性，并且是临时性的，不会对数据库造成额外的存储负担。
这些特点使得WITH AS语句在处理复杂查询、递归查询以及需要多次引用相同子查询的场景中尤为有用。
---WITH AS 可以将复杂的逻辑先写到 WITH AS 的子查询里。
如果WITH AS短语所定义的表名被调用两次以上，
则优化器会自动将WITH AS短语所获取的数据
放入一个临时表（TEMP）表里，如果只是被调用一次，则不会。
！！常用函数 & 场景判断
CASE WHEN 条件1
THEN 输出值1
WHEN 条件2
...
ELSE 输出值N --ELSE不写，其他情况默认补空值
END 别名
--别名不能以数字开头，不能使用特殊符号*+-,以数字开头或者有特殊符号可以加双引号
--示例：查询每个工作的人数
--输出格式如下：
CLERK SALESMAN MANAGER ANALYST PRESIDENT
4 4 3 2 1
SELECT COUNT(CASE WHEN JOB = 'CLERK' THEN 1 END) CLERK
,COUNT(CASE WHEN JOB = 'SALESMAN' THEN 1 END) SALESMAN
,COUNT(CASE WHEN JOB = 'MANAGER' THEN 1 END) MANAGER
,COUNT(CASE WHEN JOB = 'ANALYST' THEN 1 END) ANALYST
,COUNT(CASE WHEN JOB = 'PRESIDENT' THEN 1 END) PRESIDENT
FROM EMP
--DECODE()
语法：
DECODE(字段,判断值1,输出值1,判断值2,输出值2,...,输出值N)
--输出值N表示其他条件下的输出值，不写默认输出空值
--注意：DECODE只能适用于等值判断
！！分析函数
--注意：开窗函数不能在WHERE之后引用，一般分析函数不会跟GROUP BY 一起使用
COUNT(*) COUNT(1) COUNT(字段)
--COUNT(*),COUNT(1)执行结果一样，区别在于执行效率，COUNT(1)快
--COUNT(字段)统计字段里面非空的行数
！！行列转换 & 连续登录
--使用CASE WHEN进行行转列
SELECT NAME
,SUM(CASE WHEN COURSE = '语文' THEN SCORE END) 语文
,SUM(CASE WHEN COURSE = '数学' THEN SCORE END) 数学
,SUM(CASE WHEN COURSE = '英语' THEN SCORE END) 英语
FROM T_SCORE
GROUP BY NAME
--连续登录的思路：将表中日期字段减去一个连续的数字，如果日期连续，减出来的值相等，然后再按照
--减出来的字段进行分组计数，此时就能够得到每个用户所有连续登录天数，最大连续登录天数最后按照用户
--分组求最大值即可，一般连续数字用ROW_NUMBER()来构造;
SELECT USER_NAME,MAX(CT)
FROM (
SELECT USER_NAME,COUNT(*) CT
FROM (SELECT USER_NAME
,LOGIN_DATE
,LOGIN_DATE - ROW_NUMBER()OVER(PARTITION BY USER_NAME ORDER BY LOGIN_DATE) R1
FROM T_LOG
)
GROUP BY USER_NAME,R1
)
GROUP BY USER_NAME;
SELECT USER_NAME
,COUNT(*) CT
,MIN(LOGIN_DATE) START_DATE
,MAX(LOGIN_DATE) END_DATE
FROM (SELECT USER_NAME
,LOGIN_DATE
,LOGIN_DATE - ROW_NUMBER()OVER(PARTITION BY USER_NAME ORDER BY LOGIN_DATE) R1
FROM T_LOG
)
GROUP BY USER_NAME,R1
！！伪列 & 对象 & 递归
--ORACLE伪列--
伪表：DUAL --虚拟表，只有一行一列，一般用于满足SELECT语法规则
伪列：ROWNUM、ROWID
--①ROWNUM:返回查询结果的行号，一般用于分页查询
--ROWNUM不能用>进行比较，只能用小于或者小于等于
SELECT E.*,ROWNUM
FROM EMP E
WHERE ROWNUM BETWEEN 5 AND 10 -- 错误
SELECT *
FROM (
SELECT E.*,ROWNUM RN
FROM EMP E
)
WHERE RN BETWEEN 5 AND 10 -- 正确
SELECT *
FROM(SELECT T.*,ROWNUM RN
FROM (SELECT E.*
FROM EMP E
ORDER BY SAL DESC
)T
)
WHERE RN BETWEEN 5 AND 10
--删除重复数据
DELETE FROM EMP_BAK6
WHERE ROWID NOT IN (SELECT MIN(ROWID) --MAX
FROM EMP_BAK6
GROUP BY EMPNO --主键
)
--数据库对象
登录到SYSTEM用户(数据管理员)
CREATE USER 用户名 IDENTIFIED BY 密码;
--创建会话权限
GRANT CREATE SESSION TO LISI;
--赋予建表权限
GRANT CREATE TABLE TO LISI;
GRANT CREATE PROCEDURE TO LISI;
--回收权限
REVOKE 角色|权限 FROM 用户
REVOKE DBA FROM ZHANGSAN;
--修改用户的密码
ALTER USER 用户名 IDENTIFIED BY 新密码;
--修改用户处于锁定（非锁定）状态
ALTER USER 用户名 ACCOUNT LOCK|UNLOCK;
--删除用户
DROP USER ZHANGSAN CASCADE;
-- 递归查询 /树状查询
递归：从顶层到下一层级,一层一层递归去找。
递归里面有一个很重要的关键字,
LEVEL -- 伪列（关键字），代表树形结构中的层级编号
！！视图 VIEW
视图：将一段SELECT语句封装到视图中去,方便后续的重复查询
--GRANT CREATE VIEW TO SCOTT;报错权限不足时，需要用管理员用户SYSTEM赋予SCOTT创建视图权限
视图跟表的区别：
除了视图没有真实存储数据,在SELECT里面视图跟表没区别。
视图是没有存储数据的,视图存储的是一段 SELECT 代码.
作用：将已定的SELECT逻辑封装到视图里面,方便后续的每一次调用。
Q：视图可以UPDATE吗？
A：有时候可以(视图封装单表的时候,表中原有字段可以UPDATE)有时候不可以(其它的)
更新视图,最终是更新底表。--基表
更新视图：
UPDATE EMP_CNA
SET ENAME = 'A'; --源表字段一致可以更新
SELECT * FROM EMP_CNA;
UPDATE EMP_CNA
SET JOB_CNA = 'A'; --虚拟列无法更新
-----工作中,更多的是 REPLACE 视图的 写法,而不是 通过视图去更新表
总结：
1.视图是一段封装SELECT语句的代码,不保存数据;
-- 跟表的最大区别
2.可以用视图更新底表,局限性比较高。(单表且表中原有字段);
3.语法：CREATE OR REPLACE VIEW XXX AS SELECT ...;
--物化视图 会保存数据，占空间，提前将查询数据保存在内存当中，后续查询效率更高
MATERIALIZED VIEWS
物化视图需要手动刷新，否则只保留上一次查询数据，可以设置定时刷新
！！PLSQL程序块
--什么是程序块？
--可以通过多段SQL加上一些IF判断以及循环控制，来实现复杂的功能
--程序块是从上往下按顺序执行，所以变量最后的值，应该是最后一次赋值的对应值
--打印函数一次只能打印一个值
！！IF判断+循环
CASE WHEN 语法：
CASE WHEN 条件1
THEN 输出值1
WHEN 条件2
THEN 输出值2
...
ELSE 输出值N
END;
IF判断语法：
IF 条件1
THEN 执行事项1;
ELSIF 条件2
THEN 执行事项2;
...
ELSE 执行事项N;
END IF;
--总结：
1.IF判断 --只能用于程序块中
语法：
IF 条件1
THEN 执行事项1;
ELSIF 条件2
THEN 执行事项2;
...
ELSE 执行事项N;
END IF;
2.循环
--FOR循环
FOR I IN 下限..上限 LOOP --循环变量I会从下限自增到上限，每次增加1
循环体;
END LOOP;
--WHILE循环
WHILE 进入循环的条件 LOOP
循环体;
变量自增;
END LOOP;
--LOOP循环
LOOP
循环体;
变量自增;
EXIT WHEN 退出循环的条件;
END LOOP;
--WHILE循环和LOOP循环必须要写变量自增表达式，否则会进入死循环;
！！自定义函数 & 存储过程 & 游标
自定义函数--
系统没有的函数，可以指定实现特定的功能
--参数数据类型不需要长度
自定义函数，是一个数据库对象，也可以删除
创建语法：
CREATE OR REPLACE FUNCTION 函数名(参数名1 数据类型,参数2 数据类型,...) -- OR REPLACE可以省略
RETURN 返回值类型
IS
--声明变量
BEGIN
RETURN 返回值;
END;
--函数调用：
直接使用SELECT进行查询;
--删除函数
DROP FUNCTION 函数名;
--存储过程--
SP(STORE PROCEDURE)
--过程一般以SP开头
背景：将一段或者多段SQL语句，存储在数据库中，方便后续的重复调用
里面的SQL语句，一般是用来做数据同步（INSERT INTO+SELECT 语句）
数据同步就是指将源表数据同步至目标表
存储过程是一个数据库对象（PROCEDURE），需要创建。可以删除
--注意：创建存储过程，只是将代码保存到数据库中，不会去执行里面的代码
--如果要执行里面的代码，还需要重新调用存储过程
创建语法：
CREATE OR REPLACE PROCEDURE 过程名[参数名 数据类型]
IS
[声明变量]
BEGIN
END;
--调用
BEGIN
过程名[参数值];
END;
--删除过程
DROP PROCEDURE 过程名;
--存储过程和自定义函数的区别？
1.自定义函数有返回值，存储过程一般没有;
2.调用方式不一样，自定义函数直接SELECT调用，存储过程需要使用BEGIN ... END调用;
3.存储过程一般用于数据同步，而自定义函数一般用于数据的转换;
--游标--
游标一般用于封装一个查询结果集，分别对每一行数据进行处理，或者叫批量处理数据;
--游标需要声明
DECLARE
CURSOR 游标名 --游标一般以C开头
IS
SELECT 语句;
--注意：数据量大的时候，使用游标处理效率较低；
！！数据同步（全量）
--数据同步--
数据同步分为：全量同步和增量同步
全量同步：先清空，再插入
增量同步：有则更新，无则插入
--全量删除数据时，一般优先考虑使用TRUNCATE，因为TRUNCATE执行效率比DELETE高
--TRUNCATE删除数据时，会重置表的【高水位】，DELETE不会
--高水位：一般指表插入数据时，系统给予的存储空间，使用DELETE删除数据之后，不会被释放
--使用TRUCNATE删除，才会被释放;
--总结：
1.全量同步
实现逻辑：先清空，再插入
代码实现：先 TRUNCATE，再 INSERT INTO
2.动态SQL
使用背景：
程序块中不能直接使用DDL语言(TRUNCATE/CREATE/DROP ...)，需要动态SQL执行
程序块中执行其他程序块时，需要使用动态SQL拼接参数来执行
！！增量同步 & 异常处理 & 包
数据同步：将一张表或多张表的数据通过一系列的转换、汇总操作，迁移到另一张表；
--总结：
增量同步：
实现逻辑：有则更新，无则插入
语法：
MERGE INTO 目标表 A
USING (SELECT ... FROM 源表) B
ON (A.匹配字段=B.匹配字段)
WHEN MATCHED THEN UPDATE
SET A.更新字段1=B.字段1,
A.更新字段2=B.字段2,
...
WHEN NOT MATCHED THEN
INSERT (A.字段1,A.字段2,...)
VALUES (B,字段1,B.字段2,...);
--注意：不能更新ON里面的匹配字段，ON里面一般用主键进行匹配，否则会报错【无法获取稳定的行】
--MERGE INTO也是DML语言，需要提交，可以回滚
--异常处理--
程序块分为三部分：
DECLARE
--声明部分
BEGIN
--执行部分
EXCEPTION WHEN OTHERS THEN ... --异常处理部分
END;
--程序块在报错时，会自动终止程序，并以一种很不友好的方式进行提示，弹框显示报错信息
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
--当程序出现异常时，还可以执行提交/回滚命令
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
ROLLBACK; --COMMIT;
--当程序出现异常时，让其抛出异常 RAISE --弹框
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
RAISE;
--当程序出现异常时，忽略异常
EXCEPTION WHEN OTHERS THEN
NULL;
--总结：
1.异常处理：一般指程序因为数据或者参数的原因导致报错，程序会自动进入异常处理模块执行相应的操作
2.自定义异常
语法：
EXCEPTION WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM); --SQLERRM表示具体的异常信息
ROLLBACK;
RAISE; --RAISE表示抛出异常，弹框
...
3.异常信息：
--未找到任何数据
--除数为0
--字符转换到数字错误
--实际返回行数，超出请求行数
4.异常一般写在存储过程后面，一个过程一般写一个异常即可
一般在工作中，我们很少写异常，基本都是些其他的INSERT INTO语句;
--包--
关键字：PACKAGE
作用:主要用来封装多个存储过程或者函数，便于后续的管理和调用
包分为包头和包体：
包头：相当于书的目录，存放过程名或者函数名;
包体：相当于书的内容，存放过程或者函数的具体内容;
--包头创建
CREATE OR REPLACE PACKAGE 包名
IS
PROCEDURE 过程名;
FUNCTION 函数名
RETURN 返回值类型;
END;
--包体创建
CREATE OR REPLACE PACKAGE BODY 包名
IS
PROCEDURE 过程名
IS
BEGIN
END;
FUNCTION 函数名
RETURN 返回值类型
IS
BEGIN
END;
END;
--调用包里面的过程和函数
BEGIN
包名.过程名;
END;
SELECT 包名.函数名(参数值) FROM 表;
--删除包
DROP PACKAGE 包名;
！！正则表达式 & 分区表 & 日志表
--正则表达式--
REGEXP_LIKE --正则模糊匹配
REGEXP_INSTR --正则查找位置
REGEXP_SUBSTR --正则截取
REGEXP_REPLACE --正则替换
^ --放中括号外面表示字符串的开始，中括号里面表示非
$ --表示匹配字符串的结尾
+ --表示1位或者多位前面的匹配项
[] --表示范围
{} --表示长度
https://cloud.tencent.com/developer/article/1456428
--小练习：编写一个PLSQL,接收一个电话号码，判断该号码的合法性
--（11位纯数字，第一位是1，第二位是35689，其余不限）
--如果合法，打印该号码，否则打印“你输入的号码不合法，请重新输入”;
DECLARE
V_TELE NUMBER(20) := &一个电话号码;
BEGIN
IF REGEXP_LIKE(V_TELE,'^[1][35689][0-9]{9}$')
THEN DBMS_OUTPUT.PUT_LINE(V_TELE);
ELSE DBMS_OUTPUT.PUT_LINE('你输入的号码不合法，请重新输入');
END IF;
END;
--总结：
正则表达式：用于处理特殊的数据，特殊字符分割、判断...
主要有：
REGEXP_LIKE --正则模糊匹配
--正则模糊匹配--示例：查询姓名带S的员工
SELECT * FROM EMP WHERE ENAME LIKE '%S%';
--示例：找出姓名带字母的员工信息
SELECT * FROM EMP
WHERE REGEXP_LIKE(ENAME,'[a-zA-Z]');
REGEXP_INSTR --查找位置
--查找字符串'1231a442b46cdecfg'中第二次出现字母的位置
SELECT REGEXP_INSTR('1231a442b46cdecfg','[a-zA-Z]',1,2) FROM DUAL;
REGEXP_SUBSTR --截取
---找出 表TT 中市的名称
CREATE TABLE TT(CON VARCHAR2(100));
INSERT INTO TT VALUES('广东省-深圳市-龙岗区');
INSERT INTO TT VALUES('广东省-广州市-天河区');
--取市
SELECT REGEXP_SUBSTR(CON,'[^-]+',1,2) CITY FROM TT;
REGEXP_REPLACE --替换
--替换非数字
SELECT LENGTH(REGEXP_REPLACE('13412AHJD你好Ha2341jkh341!@#','[^0-9]'))
FROM DUAL;
--分区表--
当表数据量特别大的时候（上亿条），查询效率很慢，使用分区可以将整张表的数据存储在不同的表空间（物理文件上）
后续就可以指定分区去查询，避免全表的扫描;
数据量大的行业：银行、电信、电商、医疗,...
表分区完之后，逻辑上还是一张完整的表;
分区表的类型：范围分区、列表分区、哈希(散列)分区、组合分区
--范围分区 按照某个字段的范围，将数据存放到不同的分区
创建语法：
CREATE TABLE 表名(字段名 数据类型)
PARTITION BY RANGE (分区键)
(
PARTITION 分区1 VALUES LESS THAN (值1),
PARTITION 分区2 VALUES LESS THAN (值2),
...
);
--列表分区 一般重复数据较多的字段，适合做列表分区
创建语法：
CREATE TABLE 表名(字段名 数据类型)
PARTITION BY LIST (分区键)
(
PARTITION 分区1 VALUES (值1),
PARTITION 分区2 VALUES (值2),
PARTITION 分区3 VALUES (值3),
...
);
--哈希（散列）分区 按照某一列，无规则的进行分区，最终能够尽可能将数据均匀分布到各个分区，适用于唯一的字段
创建语法：
CREATE TABLE 表名(字段名 数据类型)
PARTITION BY HASH (分区键)
(
PARTITION 分区1,
PARTITION 分区2,
PARTITION 分区3,
...
);
--组合分区 分区里面再分区
范围+列表
创建语法：
CREATE TABLE 表名(字段名 数据类型)
PARTITION BY RANGE(主分区键)
SUBPARTITION BY LIST (子分区键)
(
PARTITION 主分区1 VALUES LESS THAN (值1)
(
SUBPARTITION 子分区1 VALUES (值),
SUBPARTITION 子分区2 VALUES (值),
SUBPARTITION 子分区3 VALUES (值)
),
PARTITION 主分区2 VALUES LESS THAN (值2)
(
SUBPARTITION 子分区1 VALUES (值),
SUBPARTITION 子分区2 VALUES (值),
SUBPARTITION 子分区3 VALUES (值)
),
PARTITION 主分区3 VALUES LESS THAN (值3)
(
SUBPARTITION 子分区1 VALUES (值),
SUBPARTITION 子分区2 VALUES (值),
SUBPARTITION 子分区3 VALUES (值)
)
);
总结：
分区：将一张大表的数据，分开存储在不同的物理文件上，可以指定分区查询数据，避免全表扫描;
优点：增加查询效率，方便管理数据
分区类型：
范围分区 RANGE
列表分区 LIST
哈希分区 HASH
组合分区 两两组合
分区的其他操作：
增加分区：ALTER TABLE 表名 ADD PARTITION 分区名 VALUES LESS THAN (值);
删除分区：ALTER TABLE 表名 DROP PARTITION 分区名;
截断分区：ALTER TABLE 表名 TRUNCATE PARTITION 分区名;
--工作中常用的分区:范围分区，一般按照时间进行范围分区，每个月的数据存放在一个分区
--日志表--
主要用于记录数据同步的运行状态，程序是否报错、什么时间执行等等
日志表主要字段：源表名、目标表名、步骤名、同步状态、同步行数、开始时间、结束时间、备注信息
.log
--创建日志表
SELECT * FROM T_LOG;
CREATE TABLE T_LOG(SRC_TABLE_NAME VARCHAR2(100),
TAR_TABLE_NAME VARCHAR2(100),
STEP_NAME VARCHAR2(100),
STATUS VARCHAR2(10),
ROW_COUNT NUMBER,
START_DATE DATE,
END_DATE DATE,
MARK VARCHAR2(100)
);
SELECT * FROM T_LOG;
--开发记录日志的存储过程
CREATE OR REPLACE PROCEDURE SP_LOG( SRC_TABLE_NAME VARCHAR2,
TAR_TABLE_NAME VARCHAR2,
STEP_NAME VARCHAR2,
STATUS VARCHAR2,
ROW_COUNT NUMBER ,
START_DATE DATE ,
END_DATE DATE ,
MARK VARCHAR2)
IS
BEGIN
INSERT INTO T_LOG VALUES(SRC_TABLE_NAME,TAR_TABLE_NAME,STEP_NAME,STATUS,ROW_COUNT,START_DATE,END_DATE,MARK);
COMMIT;
END;
--调用写日志的过程
BEGIN
SP_LOG('EMP','EMP_BAK','全量同步EMP至EMP_BAK','SUCCESS',14,SYSDATE,SYSDATE,'数据同步完成');
END;
--查询日志表
SELECT * FROM T_LOG;
--总结：
1.日志表，主要用于记录同步数据过程的运行情况;
2.主要字段：源表、目标表、行数、开始时间、结束时间、...
3.记录日志的方式：在同步数据的存储过程里面，调用写日志的存储过程;
4.记录日志的好处：
--记录行数--
方便核对数据的准确性
--记录时间--
方便观察SQL的执行效率
--记录报错信息--
方便找到报错的原因
！！序列 & 拉链表 & 索引 & （！！！oracle优化）
总结：
1.插入目标表的时候,尽量使用指定字段插入;
2.指定的字段顺序要跟SELECT的字段顺序一致;(保证字段类型 字段值匹配的上)
3.目的也是为了,后期表结构变更了,不用再去维护之前开发的存储过程;
4.指定字段插入的时候,其它未指定的字段,默认为空。所以,指定字段
插入的时候,一定要带上主键。--因为主键默认非空
INSERT INTO EMP_BAK(ENAME,JOB)
SELECT ENAME,JOB FROM EMP; --报错
员工编号是主键的话,则插入不成功。
--2.序列 -- SEQUENCE
序列是一个自增的值,可以作为一个列值。
序列的创建：
CREATE SEQUENCE SEQ_001 -- 创建序列 SEQ_001
START WITH 10 -- 定义序列从10 开始
INCREMENT BY 2 -- 步长为 2
MAXVALUE 20 --定义序列的最大值 是 20
MINVALUE 1 -- 定义序列的最小值
CYCLE -- 定义序列循环 不循环是 NOCYCLE
CACHE 5; -- 序列的缓存
SELECT SEQ_001.NEXTVAL FROM DUAL;
-- 第一次 返回 NEXT VALUE 就是 START WITH 对应的值
SELECT SEQ_001.CURRVAL FROM DUAL;
-- 返回 序列当前的所在值
SELECT SEQ_001.NEXTVAL FROM EMP;
--删除序列
DROP SEQUENCE SEQ_001;
SELECT SEQ_001.NEXTVAL,E.*
FROM EMP E;
--序列第一次查询，只能用NEXTVAL
SELECT SEQ_01.NEXTVAL FROM DUAL;
SELECT SEQ_01.CURRVAL FROM DUAL;
SELECT SEQ_01.NEXTVAL,E.* FROM EMP E;
-- 序列会结合主键去用(非空且唯一)
总结：
序列有两个关键字：
序列.NEXTVAL → 返回序列的下一个值
序列.CURRVAL → 返回序列的当前值
--拉链表--
作用：记录维度缓慢变化，一般用于保留历史和最新的数据
维度：观察事物的角度（地区、时间、产品）
之前的数据同步最终只能保留最新的数据，不太适合用于分析历史的数据变化情况
拉链表实现原理：在源表的基础上，新增三个字段（开始时间、结束时间、是否历史数据）
--总结：
1.拉链表概念：主要用于记录一个维度的变化过程;
2.原理：对比源表，在源表基础上新增三个字段（开始时间、结束时间、FLAG）做更新和插入操作;
3.开链 UPDATE,闭链 INSERT
4.查询最新数据？ FLAG=0
5.查询某一天的数据？ WHERE 某一天时间 BETWEEN 开始时间 AND 结束时间
6.适合做拉链表：维度表（客户信息表、产品信息表...）
不适合：流水表，交易信息表,...
7.程序实现自动记录拉链数据逻辑：
先适应MINUS得到发生变化的数据，然后更新拉链表对应的结束时间，最后再插入源表最新数据;
--数据库索引-- INDEX
图书馆索引：快速找到想要的书籍
数据库索引：快速找到想要的数据
数据库创建索引时，会生成一个类似目录的一张表，索引表
索引表包含两个字段（加索引的列，ROWID），后续查询数据时，先在索引表中找到满足条件数据的ROWID
然后再返回源表快速定位到每个ROWID对应的行;
表中字段加了索引之后，会自动排序
索引加在哪一个字段，取决于WHERE之后使用的字段
索引的作用：加快数据的查询、检索效率
索引一般只有在数据量很大的时候，适合加
--执行计划主要看：
1.扫描方式:
全表扫描 TABLE ACCESS FULL
索引扫描 TABLE ACCESS BY INDEX
2.耗费：耗费越高，效率越低
--索引的种类--
分为四种：
唯一索引 CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
、位图索引 CREATE BITMAP INDEX 索引名 ON 表名(字段名);
、组合索引CREATE INDEX 索引名 ON 表名(字段名1,字段2);
--注意：组合索引，必须要引用到第一个字段，才会走该索引
、函数索引 CREATE INDEX 索引名 ON 表名(函数名(字段名));
--适用于WHERE之后的字段加函数的场景,因为字段加了函数之后，原来的索引会失效
--唯一索引 适用于数据唯一的字段，主键，唯一键
--总结：
索引：
作用：提升查询效率
原理：对加索引的列，按照特定的规律进行排序，生成索引表，后续的查询会先在索引表中找到满足
条件的数据对应的ROWID,然后通过ROWID去源表中找到对应的行;
索引种类：唯一索引、位图索引、组合索引、函数索引
索引缺点：索引是一个数据库对象，会生成索引表，会占用一定的空间
还会影响表的增删改效率;
索引其他优点：增加排序和分组的效率;
数据量小的时候，使用索引不一定比全表扫描快;
索引有时候会失效，失效场景如下：
1.索引列用到函数时，会失效;
2.索引列进行算数运算时，会失效;
SELECT * FROM EMP_BAK WHERE EMPNO-10=7778;
3.索引列进行不等值判断，也会失效
SELECT * FROM EMP_BAK WHERE EMPNO<>7788;
4.索引列进行模糊查询，%在前会失效;
SELECT * FROM EMP_BAK WHERE ENAME LIKE 'S%'
SELECT * FROM EMP_BAK WHERE ENAME LIKE '%S'
SELECT * FROM EMP_BAK WHERE ENAME LIKE '_S'
5.索引列的空值过滤,会失效;
SELECT * FROM EMP WHERE EMPNO IS NULL;
索引适用场景：表总数据量大，查询的数据小，占总表15%左右;
--ORACLE优化--
当某段SQL执行很慢的时候，就需要考虑对其进行优化，提升执行效率，减少执行时间
1.从数据库层面考虑：
给WHERE之后的常用的字段加索引
给大表进行分区
2.从SQL语句层面进行优化
①少用 SELECT *,尽量使用需要的字段;
②大表先过滤在关联，先过滤再分组;
③少用 ORDER BY ，排序非常消耗资源;
④去重尽量不用 DISTINCT ,一般用 GROUP BY 代替,DISTINCT 里面有排序的操作;
⑤使用 UNION ALL 代替 UNION ,UNION 是会排序去重;
⑥使用 EXISTS 代替 IN ，当子查询数据量大的时候，IN的匹配次数比EXISTS多
⑦复杂的语句使用 WITH AS 临时表，同一张表只需要扫描一次
⑧删除数据最好用 TRUNCATE，效率比 DELETE 高;
⑨避免索引失效;
⑩合理利用 HINTS ,强制走索引;
HINTS是ORACLE数据库的一种提示
一般一个SELECT语句可以通过查看执行计划来判断是否走索引，如果加了索引
系统没走，就可以使用HINTS来强制走索引
--示例：
SELECT * FROM EMP_BAK
WHERE EMPNO=7798
SELECT * FROM EMP_BAK
WHERE EMPNO+10=7798; --没走索引
SELECT /*+ INDEX (EMP_BAK UNI_INDEX_EMPNO) */* FROM EMP_BAK
WHERE EMPNO+10=7788;
SELECT * FROM EMP_BAK
WHERE EMPNO=7788;
--执行计划一般看哪些内容
1、表的扫描方式（全表扫描、索引列扫描）
2、耗费，耗费越高，效率越低
3、看关联机制：
哈希连接（HASH JOIN）：适合于等值连接
嵌套循环（NESTED LOOP）：适用于小表关联大表
排序合并（MERGE JOIN）：适用于不等值关联
--HASH JOIN
SELECT * FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
--NESTED LOOP
SELECT * FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO<>D.DEPTNO
--MERGE JOIN
SELECT * FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO>D.DEPTNO;
查看执行计划方法：
(1)在PLSQL里面选中SELECT语句，按F5;
(2)在SQL*PLUS或者PL/SQL DEVELOPER打开的COMMAND WINDOW（命令）中，执行如下命令：
EXPLAIN PLAN FOR SELECT * FROM DUAL; +回车
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); +回车
HINTS还可以指定关联机制，全表扫描等等...
1、/*+ FULL(表名) */ --指定全表扫描
2、/*+ USE_NL(表名1，表名2) */ --指定用NESTED LOOP连接
3、/*+ USE_HASH(表名1，表名2) */ --指定用HASH连接
4、/*+ USE_MERGE(表名1，表名2) */ --指定用SORT MERGE JOIN
