OracleSQL_071笔记_1（函数小结）

1、SQL基础

----查看数据库对象定义
SELECT DBMS_METADATA.GET_DDL(‘SEQUENCE’,‘EMP_SEQ’) FROM DUAL

SQL语句实例，查询各部门中工资大于5k的员工平均工资在6k以上的部门信息;

SELECT
DEPARTMENT_ID
,SUM(SALAR)
,AVG(SALAR)
FROM EMPLOYEES
WHERE SALARY>5000
AND DEPARTMENT=‘90’
GROUP BY EMPLOYEE_ID
HAVING AVG(SALAR) >6000
;

1.1、比较运算符

符号	注释
=	equal to
>	greater than
<	less than
>=	greate than or equal to
<=	less than or equal to
<>	not equal to
!=	not equal to
in (list)	match any of a list of values 匹配任意一个在列表中的字符
like	match a character pattern 匹配字符模
is null	is a null value
between … and …	是一个闭区间，等价于 <=… and >= …

1.2、行限制语句

用于在查询结果集中筛选出制定的数据，如排序后保留前或跳过前几行数据。
这类语句是在order by 之后对排序后的结果集进行操作的语句

SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; --排序后跳过前5个，从第6行开始，输出5个
FETCH FIRST 5 ROWS ONLY
; --输出结果集前五个，

1.3、定义变量

如果同一段代码需要替换不同的参数执行多次，可以使用定义变量的方式来简化；
单&符号表示的变量需要每次执行的时候重新赋值，如：&var1
双&符号表示的变量需要每次执行的时候重新赋值，如：&&var1
可以使用 define定义变量，定义的变量用，需要在变量名前用"&"来引用，如： define var3; select &var3 from dual;
可以使用 undefine 来删除变量，如：undefine var3;

定义变量并赋值

DEFINE DEP_ID=101;
UNDEFINE DEP_ID;
SELECT ‘&DEP_ID’ FROM DUAL; --使用时变量名前加上&

在sql中定义变量，每次执行时都要重新赋值

SELECT SALARY FROM EMPLOYEES WHERE SALARY<=&SAL;

定义一个持久化的变量，下次使用变量时不用赋值

SELECT SALARY FROM EMPLOYEES WHERE SALARY<=&&SAL;

2.单行函数

2.1、字符\数字函数

SELECT UPPER(‘abc’) from dual; -->ABC
SELECT lower(‘EFG’) from dual; -->efg
SELECT TRUNC(123.456) FROM DUAL ; -->123 直接截取
SELECT TRUNC(123.456,2) FROM DUAL ; -->123.45
SELECT ROUND(456.789) FROM DUAL; -->457 四舍五入
SELECT ROUND(-456.789) FROM DUAL; -->-457
SELECT FLOOR(12.3) FROM DUAL; -->12 寻找比自己小的整数
SELECT FLOOR(-12.3) FROM DUAL; --> -13
SELECT CEIL(45.937) FROM DUAL; --> 46 比自己大的整数
SELECT CEIL(-45.937) FROM DUAL; --> -45
SELECT MOD(94,9) FROM DUAL ; -->4

日期函数

SELECT SYSDATE FROM DUAL ; --中国 yyyy-mm-dd 美国： dd mm yy
–变更当前会话的日期格式
alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
alter session set nls_date_format=‘yyyy-mm-dd’;

空值操作

聚合函数时null会被忽略
与null计算的数字结果为null
字符串与null拼接返回字符本身
NULL的相关函数包括：nvl,nvl2,nullif,coalesce,decode

SELECT 1+NULL FROM DUAL; --> NULL
SELECT ‘A’||NULL FROM DUAL; --> A
SELECT NVL(1,NULL) FROM DUAL ; -->1 :nvl如果第一个参数为空，则返回第二个参数，否则返回第一个参数
SELECT NVL(NULL,2) FROM DUAL ; -->2
SELECT NVL2(‘null’,‘p2’,‘p3’) FROM DUAL; -->p2 nvl2函数用法：判断第一个参数是否为空，否则返回参数2，是则返回参数3
SELECT NVL2(NULL,‘p2’,‘p3’) FROM DUAL ; -->p3

SELECT NULLIF(‘P1’,‘P1’) FROM DUAL; --结果：NULL ； nullif函数： 当两个参数相同则返回null，两个参数不同则返回第一个参数；
SELECT NULLIF(‘Y’,‘N’) FROM DUAL; --Y
SELECT COALESCE(‘A’,‘B’,‘C’) FROM DUAL ; —> A 返回第一个非空
SELECT COALESCE(NULL,‘B’,‘C’) FROM DUAL ; —> B

decode函数 case表达式

SELECT LAST_NAME,SALARY,
DECODE(TRUNC(SALARY/2000,0),0,0.00,1,0.09,0.45) TAX --返回第一匹配的值，否则返回最后一个
FROM EMPLOYEES WHERE DEPARTMENT_ID=‘80’;

case表达式
case expr1 when val1 then ‘1’ when val2 then ‘2’ else ‘3’ end ;
case when expr1=val1 then ‘1’ when expr1=val2 then ‘2’ else ‘3’ end ;

TRUNC和round的区别

trunc日期的话是截取时间，返回月初或年初，不会发生进位
round有可能返回明年初或下月初
round 过了7月1号(含)开始都是返回下一年，过来16号（含）开始返回下一月的 （无论大小月，无论大小年）

SELECT
SYSDATE --2021-03-11 23:40:36
,ROUND(SYSDATE,‘MM’) --2021-03-01 00:00:00
,ROUND(SYSDATE,‘YY’) --2021-01-01 00:00:00
,ROUND(TO_DATE(‘2021-10-31’,‘YYYY-MM-DD’) ,‘YY’) --2022-01-01 00:00:00
,ROUND(TO_DATE(‘2021-10-31’,‘YYYY-MM-DD’) ,‘MM’) --2021-11-01 00:00:00
,TRUNC(TO_DATE(‘2021-10-31’,‘YYYY-MM-DD’) ,‘YY’) --2021-01-01 00:00:00
,TRUNC(TO_DATE(‘2021-10-31’,‘YYYY-MM-DD’) ,‘MM’) --2021-10-01 00:00:00
FROM DUAL
;

转义语句

用q’[需转义的语句]’ 来转义字符串，如想返回一个单引号可以用四个单引号返回’’’’ ,也可以用 q’[’]’ 来实现；
select last_name,last_name ||q’[’]’||salary from employees ;

转换函数

在Oracle中数据类型转换分为隐式和显式转换两种。
隐式转换由系统自动完成，显式转换需要手动调整；
转换函数： to_char,to_date,to_number

to_date函数： 讲字符转成日期

select to_date(‘20210313’,‘yyyy-mm-dd’) from dual; --> 13-3月 -21
select to_date(20210313 ,‘yyyy-mm-dd’) from dual; --> 13-3月 -21 （数字和字符返回的日期相同）

to_char函数:

select to_char(sysdate,‘yyyymmdd’) from dual; --> 20210313
select to_char(sysdate,‘yyyy-mm-dd’) from dual; --> 2021-03-13
select to_char(3456,’$9999.99’) from dual ; --> $3456.00 : 可以通过$或L来改变数字的展示方式
select to_char(3456,’$9,999.99’) from dual ; --> $3,456.00 可以使用千位分隔符
select to_char(60000,‘99,999.00’) number from dual; --》60,000.00 注意精度不够是无法正常显示的
select last_name ,to_char(hire_date,‘fmDD Month YYYY’) as mo from employees;
select last_name ,to_char(hire_date,‘DD Month YYYY’) as mo from employees;
select last_name ,to_char(hire_date,‘YYYY-MM-DD’) as mo from employees;–>2020-07-12
select last_name ,to_char(hire_date,‘YYYY-fmMM-DD’) as mo from employees; -->2020-7-12
select hire_date,to_char(hire_date,‘yyyy-mm-dd’) from employees;

–当前日期
select
sessiontimezone --所在时区 ：伤害
,current_date --当日精确到秒
,current_timestamp --当前时间戳
from dual ; --2021-03-11 23:12:47

–找到每个员工的入职周数
select employee_id,hire_date
,CEIL((sysdate-hire_date)/7) AS WEEK_NUM --两个日期相减后先上取整
from employees;

select
to_date(‘2021-01-31’,‘yyyy-mm-dd’) as dt
,add_months(to_date(‘2021-01-31’,‘yyyy-mm-dd’) ,1) --大小月进行了处理 ：2021-02-28 00:00:00
,add_months(to_date(‘2021-02-28’,‘yyyy-mm-dd’) ,1) --大小月进行了处理 ：2021-03-31 00:00:00 月底日期+1后返回的都是月末
,add_months(sysdate,1) --月份加1
,last_day(sysdate) --月末
,months_between(sysdate,current_date) --相差月份
,next_day(sysdate,1) --距离制定日期最近的（1 周日、2 周一 、7周六）; 当前是周三，参数选1 返回下周日，参数选7返回本周六 ； 排除当天
from dual;

SELECT
SYSDATE --2021-03-11 23:26:23
,TRUNC(SYSDATE) --当天开始：2021-03-11 00:00:00
,TRUNC(SYSDATE,‘MM’) --当月开始： 2021-03-01 00:00:00
,TRUNC(SYSDATE,‘YY’) --2021-01-01 00:00:00
,TRUNC(SYSDATE,‘YY’)-1
,ROUND(SYSDATE,‘MM’) --返回本月1号
FROM DUAL
;

CREATE GLOBAL TEMPORARY TABLE TEST2 (
id char(1)
,VAL1 INT
,VAL2 INT
)ON COMMIT PRESERVE ROWS;

INSERT INTO TEST2 VALUES(‘a’,1,1);
INSERT INTO TEST2 VALUES(‘b’,2,NULL);

select sum(VAL1) from test2; -->3
select sum(VAL2) from test2; -->1 聚合函数忽略null
select count(VAL2) from test2; -->1
select id,val1+val2 from test2;–>null 值得加减运算会返回null

分组函数

–group by语句不确保汇总结果的顺序（同一个group by语句返回结果集的顺序可能不同）
– 使用聚合函数时不一定非要由group by 语句： SELECT MAX(VAL) FROM T1;

SELECT AVG(SALARY) FROM EMPLOYEES; -->平均值
SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEES; -->计数
SELECT SUM(SALARY) FROM EMPLOYEES; -->汇总
SELECT MIN(SALARY) FROM EMPLOYEES; -->最小值
SELECT MAX(SALARY) FROM EMPLOYEES; -->最大值
SELECT DEPARTMENT_ID,count(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID;
SELECT DEPARTMENT_ID,FIRST_NAME FROM EMPLOYEES where DEPARTMENT_ID IN (‘60’,‘90’);

–SELECT ID2 AS ID3 ,SUM(ID) FROM TEST1 GROUP BY ID3 ; 不能使用列的别名进行分组
–you canot use a column alias in then group by clause

–SELECT DEPARTMENT_ID,AVG(SALARY) FROM EMPLOYEES GROUP BY 1 ; 这种写法在oracle中不允许

----列传行函数
SELECT
DEPARTMENT_ID
,LISTAGG(FIRST_NAME,’,’) WITHIN GROUP(ORDER BY SALARY) AS LSNAME
FROM EMPLOYEES
where DEPARTMENT_ID IN (‘60’,‘90’)
GROUP BY DEPARTMENT_ID

PIVOT–行转列函数

STDDEV --偏差
VARIANCE --方差

集合操作1

INTERSECT UNION UNION ALL EXCEPT MINUS

CREATE GLOBAL TEMPORARY TABLE T1 (
A CHAR(4)
) ON COMMIT PRESERVE ROWS;

INSERT INTO T1 VALUES(‘A’);
INSERT INTO T1 VALUES(‘A’);
INSERT INTO T1 VALUES(‘B’);
INSERT INTO T1 VALUES(‘B’);
INSERT INTO T1 VALUES(‘C’);

CREATE GLOBAL TEMPORARY TABLE T2 (
A CHAR(4)
) ON COMMIT PRESERVE ROWS;

INSERT INTO T2 VALUES(‘B’);
INSERT INTO T2 VALUES(‘C’);
INSERT INTO T2 VALUES(‘D’);
INSERT INTO T2 VALUES(‘E’);

除了unionall，其他的集合操作都会进行distinct处理

select * from t1 union select * from t2 ; --去重后合并
select * from t1 union all select * from t2 ; --全量合并
select * from t1 intersect select * from t2 ; --去重后相交
select * from t1 minus select * from t2 ; --去重后相减 （TD中是Except）

集合操作2

CREATE TABLE A (
A VARCHAR(20)
,B VARCHAR(20)
);
INSERT INTO A VALUES (‘A1’,‘B1’);
INSERT INTO A VALUES (‘A1’,‘B1’);
INSERT INTO A VALUES (‘A2’,‘B2’);
INSERT INTO A VALUES (‘A3’,‘B3’);
INSERT INTO A VALUES (‘A5’,‘B6’);
INSERT INTO A VALUES (‘A5’,‘B6’);

CREATE TABLE B (
A VARCHAR(20)
,B VARCHAR(20)
);
INSERT INTO B VALUES (‘A1’,‘B1’);
INSERT INTO B VALUES (‘A2’,‘B2’);
INSERT INTO B VALUES (‘A3’,‘B3’);
INSERT INTO B VALUES (‘A4’,‘B4’);

–并集，去重
SELECT * FROM A
UNION
SELECT * FROM B ;

–并集，不排重
SELECT * FROM A
UNION ALL
SELECT * FROM B ;

–交集，去重
SELECT * FROM A
INTERSECT
SELECT * FROM B ;

–排他,去重
SELECT * FROM A
MINUS
SELECT * FROM B ;

DML语句

CREATE TABLE SUN_EMP (
EMP_ID VARCHAR(2)
,EMP_NAME VARCHAR(20)
,DEP_ID VARCHAR(2)
,HIRE_DATE DATE
);

INSERT INTO SUN_EMP VALUES(‘01’,‘anni’,‘10’,sysdate);
INSERT INTO SUN_EMP(EMP_ID,DEP_ID) VALUES (‘02’,‘10’);
INSERT INTO SUN_EMP VALUES (‘03’,‘ruizi’,‘30’,to_date(‘2020-01-01’,‘yyyy-mm-dd’));
INSERT INTO SUN_EMP VALUES (‘04’,‘ruizi’,‘30’,to_date(‘2020-01-02’,‘yyyy-mm-dd’));

INSERT INTO SUN_EMP SELECT * FROM SUN_EMP;
UPDATE SUN_EMP SET EMP_NAME=‘gailun’ WHERE EMP_ID=‘02’;
DELETE FROM SUN_EMP WHERE ROWID NOT IN (SELECT MIN(rowid) from SUN_EMP group by emp_id);

–delete from DML语句，删除数据时逻辑删除，标记数据为不可用。可以rollback
–truncate table DDL语句，彻底删除表中的数据，释放存储空间。 不可以rollback；删除速度更快。
–delete 和truncate产生的结果是不一样的。

----事务控制
commit
–显式提交：需要commit命令
–隐式提交：当时用DDL语句时，自动提交了,如: create,drop ;
rollback
savepoint
–保存点，可以恢复到这个时间点的数据，commit之后保存点自动释放。
–可以用于恢复数据
–实例：savepoint sp_name; dml… ; rollback to sp_name;

FOR UPDATE
–普通的select一般不会对数据加锁，但可以通过for update语句加锁数据。
SELECT * FROM SUN_EMP WHERE EMP_ID=‘01’ FOR UPDATE;
–上述语句会对检索出来的数据进行加锁，知道会话commit位置，在此期间
–其他会话对加锁数据的任何操作，都会进入等待状态。

----数据对象
table :表
view :视图
sequence :序列
index :索引
synonym :同义词

----数据类型
VARCHAR2 :变长4000bytes
VARCHAR
CHAR :定长2000bytes
NUMBER(N,M) :数值 ，n总长度，m精度
long ：
raw and long raw :存储二进制数据
CLOB
BLOB ：图片，音频视频
BFILE : 主要要是存放在数据服务器上的，操作系统存储或者大型存储器上划拨的空间
ROWID : 行号
DATE
timestamp ：时间戳
INTERVAL YEAR TO MONTH --描述事件间隔
INTERVAL DAY TO SECOND --描述事件间隔

----时间测试：
ALTER SESSION SET NLS_DATE_FORMAT=‘YYYY-MM-DD HH24:MI:SS’;
DROP TABLE TEST1;
CREATE TABLE TEST1 (
TIME1 TIMESTAMP,
TIME2 TIMESTAMP WITH TIME ZONE,
TIME3 TIMESTAMP WITH LOCAL TIME ZONE,
TIME4 INTERVAL YEAR(3) TO MONTH
);

INSERT INTO TEST1 VALUES(
current_date
,SYSDATE
,SYSDATE
,interval ‘123’ year(3)
);

SELECT to_char(sysdate+time4,‘yyyy-mm-dd hh34:mi:ss’) from test1;

----时间间隔

–时间精度位要设置合理
SELECT INTERVAL ‘30’ DAY(3) FROM DUAL; --间隔三天
SELECT CURRENT_DATE+(INTERVAL ‘3’ HOUR) FROM DUAL; --三个小时后
SELECT CURRENT_DATE+(INTERVAL ‘0 5’ DAY TO HOUR) FROM DUAL; --0天5小时之后
SELECT CURRENT_DATE+(INTERVAL ‘1:30:30’ HOUR TO SECOND) FROM DUAL; --1小时30分30秒之后
SELECT CAST(CURRENT_DATE AS VARCHAR(10) FORMAT ‘YYYY-MM-DD’) FROM DUAL;
SELECT FROM DUAL ;
SELECT
CEIL((TO_DATE(‘2020-01-01 10:00:00’,‘YYYY-MM-DD HH24:MI:SS’)-TO_DATE(‘2020-01-01 10:01:00’,‘YYYY-MM-DD HH24:MI:SS’))2460*60) --间隔到秒
AS INTER
FROM DUAL;

SELECT EXCEPT(HOUR FROM SYSDATE) FROM DUAL; --HOUR 获取小时 YEAR 获取年 MINUS 获取分

----约束
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY ：指定一个或多个列组合为外键，建立与同一个表或不同表中的主键唯一关键字之间的关系。
CHECK

–列级约束
CREATE TABLE TEST1 (
DEPT_ID NUMBER PRIMARY KEY
,DEPT_NAME VARCHAR2(60) UNIQUE
);
INSERT INTO TEST1 VALUES(10,‘D1’);
INSERT INTO TEST1 VALUES(20,‘D2’);
INSERT INTO TEST1 VALUES(40,‘D4’);

–表级约束：定义一个或多个列，可以定义任何约束，not null除外。
CREATE TABLE TEST2(
ID NUMBER
,NAME VARCHAR(60)
,DEPT_ID NUMBER
,CONSTRAINT T1_PK PRIMARY KEY (ID)
,CONSTRAINT T1_UK UNIQUE (NAME)
,CONSTRAINT T1_DEPT FOREIGN KEY (DEPT_ID) REFERENCES TEST1(DEPT_ID) --外键约束
);

INSERT INTO TEST2 VALUES(1,‘A’,10);
INSERT INTO TEST2 VALUES(2,‘B’,30); --违反外键
INSERT INTO TEST2 VALUES(2,‘B’,20);
INSERT INTO TEST2 VALUES(2,‘B’,20);–违反了主键、unique约束
INSERT INTO TEST2 VALUES(3,‘C’,20);

DELETE FROM TEST2 WHERE ID=3;–成功删除
DELETE FROM TEST1 WHERE DEPT_ID=10; --被引用的外键，无法删除
DELETE FROM TEST1 WHERE DEPT_ID=40;–未被引用，删除成功

DROP TABLE TEST1 PURGE;
DROP TABLE TEST2 PURGE;

–check 约束
– 用于定义各行都需要满足的条件
– 不许引用currval nextbal leve 和 rownum 伪列
– 不允许调用sysdate uid user 和 pserenv函数
– 不许涉及其他行的查询

CREATE TABLE TEST1 (
ID NUMBER
,NAME VARCHAR(20)
,SALARY NUMBER(18,2)
,CONSTRAINT T_C_ID CHECK (ID>10)
);
INSERT INTO TEST1 VALUES ( 9,‘ZS’,100); --插入失败
INSERT INTO TEST1 VALUES (12,‘ZS’,100); --插入成功

–约束操作
– 已存在数据，不满足新增约束的，该约束无法添加，新加入数据，不满足已有约束的，数据不能插入。
– 存在外键关联的主键无法被简单删除，可以通过 CASCADE 语句来删除

ALTER TABLE TEST1 ADD CONSTRAINT T1_PK PRIMARY KEY (ID,NAME);–添加约束，删除约束，修改约束
ALTER TABLE TEST1 DROP CONSTRAINT T1_PK; --删除约束
ALTER TABLE TEST1 DROP CONSTRAINT T1_PK CASCADE; --清空联系啊后删除
ALTER TABLE TEST1 DISABLE CONSTRAINT T1_PK CASCADE; --禁用,有主外键关系时需要使用cascade
ALTER TABLE TEST1 ENABLE CONSTRAINT T1_PK; --启用
–【删除】相关级联约束
–如果没有相关级联约束，可以通过alte drop语句删除表的列
ALTER TABLE TEST1 DROP (ID) CASCADE CONSTRAINTS ; --删除列和列上的主键外键约束，之后再对表进行DML操作就可以顺利进行。

–查看相关约束
SELECT TABLE_NAME,CONSTRAINT_NAME ,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME =‘TEST1’;

----创建用户
create user c##hr identified by ‘密码’
default tablespace user temporary tablespace temp --默认的表空间和临时表空间
quota 100m on user; --表空间上的配额

alter user c##hr default tablespace users temporary tablespace temp;
alter user c##hr quota unlimited on users ;

----DDL 语句

–建表 : CREATE TABLE 权限，一个可以使用的表空间。
CREATE TABLE SUN_EMPLOYEES (
EMPLOYEE_ID NUMBER(6)
,LAST_NAME VARCHAR2(25) NOT NULL
,EMAIL VARCHAR2(25) UNIQUE
,SALARY NUMBER(8,2)
,COMMISSION_PCT NUMBER(2,2)
,HIRE_DATE DATE DEFAULT SYSDATE
,CONSTRAINT EMP_PK PRIMARY KEY (EMPLOYEE_ID)
);
INSERT INTO SUN_EMPLOYEES VALUES ( ‘01’ ,‘LIMING’ ,‘email…’ ,1234 ,0.1 ,sysdate);
INSERT INTO SUN_EMPLOYEES VALUES ( ‘02’ ,‘LIMING’ ,‘email22’ ,1234 ,0.2 );

–表备注
COMMENT ON TABLE SUN_EMPLOYEES IS ‘员工表’;
COMMENT ON COLUMN SUN_EMPLOYEES.EMPLOYEE_ID IS ‘员工编号’;
–查看
SELECT TABLE_NAME,COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME=‘SUN_EMPLOYEES’;–查看表注释
SELECT TABLE_NAME,COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME=‘SUN_EMPLOYEES’ AND COLUMN_NAME=‘EMPLOYEE_ID’; --查看表注释
SELECT DBMS_METADATA.GET_DDL(‘TABLE’,‘SUN_EMPLOYEES’) FROM DUAL;–查看表的DDL语句
SELECT DBMS_METADATA.GET_DDL(‘VIEW’,‘SUN_EMPLOYEES’) FROM DUAL;–查看视图

----ALTER语句 ： ALTER TABLE T_NAME ADD|MODIFY|DROP [COLUMN] …

--对列的默认值修改，直接影响到后续的数据insert操作。 
--删除列时，尽量只每次只删除一个列；一旦被删除就不能恢复； 
--可以使用set unused设置列不可用 

ALTER TABLE SUN_EMPLOYEES ADD DEPT_ID VARCHAR2(2); --增
ALTER TABLE SUN_EMPLOYEES DROP COLUMN COMMISSION_PCT;--删
ALTER TABLE SUN_EMPLOYEES MODIFY DEPT_ID VARCHAR(4);--改:只有为空时才能更改类型 
--ALTER TABLE SUN_EMPLOYEES SET UNUSED (HIRE_DATE) ; --标记为不可用 
--ALTER TABLE SUN_EMPLOYEES DROP UNUSED COLUMNS HIRE_DATE; 
ALTER TABLE SUN_EMPLOYEES RENAME TO SUN_EMPLOYEES2 ; 

ALTER TABLE SUN_EMPLOYEES READ ONLY; 
ALTER TABLE SUN_EMPLOYEES READ WRITE; 
DESCRIBE SUN_EMPLOYEES; --查
TRUNCATE TABLE --清除表数据，不记录日志，无法rollback，降低高水位线。

–CTAS创建表

CREATE TABLE TEST2 AS SELECT * FROM TEST1 WHERE 1=0;

----删除表

--直接drop表的话，不是把表删除了，只是改个名放入回收站里。（用于闪回），
--即使数据库重启数据库后这些数据依旧存在； 使用 purge recyclebin 清空回收站数据，才能彻底删除。 

DROP TABLE SUN_EMPLOYEES;
DROP TABLE SUN_employees PURGE; --彻底删除 select tname from tab; 删除前后查看；
PURGE TABLE SUN_EMPLOYEES; --从回收站清空表
PURGE RECYCLEBIN;--彻底清空回收站

----默认值测试

--当存在默认是时，未指定该列数据，自动按照默认赋值； 
--客户插入null给存在默认值得列  
CREATE TABLE TEST1 (
 ID NUMBER
,NAME VARCHAR(20)
,HDATE DATE DEFAULT  SYSDATE 
);

insert into test1 values (1,'user1',TO_DATE('2020-08-23','YYYY-MM-DD'));--成功
insert into test1 values (1,'user1',null); --成功
insert into test1 values (1,'user1',null); --成功
insert into test1 (id,name) values (1,'user2'); --成功
commit; 

—数据库中表的分类：

用户表：用户创建和维护的表，记录了用户的信息。
数据字典表：由oracle server创建和维护的表和集合。

–尝用的数据字典表

USER_XXX	用户拥有对象的信息 
ALL_XXX 	用户客户访问对象的信息 
DBA_XXX		这些表或者视图都是受限的，只能由DBA可以访问
V$_XXX 		动态性能视图，反应了数据库内存，性能和锁的相关信息 

SHOW USER 查看当前用户 

SELECT TABLE_NAME FROM USER_TABLES;
SELECT TNAME FROM TAB; 
SELECT STAT

----同义词 synonym

–我们可以把synonym和视图理解为同种功能

----视图

–可以对视图做DML语句操作，但对应的表中数据也会被删除
–视图被定义语句，会被存放在user_views
–视图的底层表可以被删除，底层表删除之后视图仍然保留。底层被被重建之后，视图才能继续正常使用。
–视图就是一个查数规则

CREATE VIEW VIEW_TEST1 AS SELECT * FROM TEST1; --创建视图
DROP VIEW VIEW_TEST1; --删除视图
DESC VIEW_TEST1; --查看视图格式

–视图中增加约束
–增加约束语句之后，对where之后的字段修改会被禁止（仅限于等值：如果是>或其他仍可被修改）
CREATE OR REPLACE VIEW VTEST1 (EMPLOYEE_ID,SALARY) AS
SELECT EMPLOYEE_ID,SALARY FROM TEST1
WHERE SALARY =3000
WITH CHECK OPTION CONSTRAINT T_CK ;

–只读语句
WITH READ ONLY;

–内联视图
–可以在SQL中使用的带别名的子查询
–内联视图不是数据对象

SELECT A.LAST_NAME ,A.SALARY,A.DEPARTMENT_ID,B.MAXSAL
FROM EMPLOYESS A ,
(SELECT DEPARTMENT_ID ,MAX(SQLARY) MAXSAL FROM EMPLOYEES) B
AND A.SALARY < B.MAXSAL
;

序列 SEQUENCE

--生成唯一整数值得结构； 只有一个会话能读取下一个值，强制递增。 
--通常用于创建主键
--使用缓存到内存中的方式，虽然提高了效率，但存在造成序列断链的风险；
--系统视图： SELECT * FROM USER_SEQUENCES; 
--不可以在SQL文中使用nextval和currval；
--	带有distinct、group、having、order by、delete、update、create tale、alter table 的语句； 
--序列使用 
--对于持久递增序列来说，commit并不会对其有影响。事务是否提交与序列递增无关，序列是一个全局可见变量。 


--每一次SEQ.NEXTVAL的访问都会使序列，增长。 （包括select） 

CREATE SEQUENCE DEPT_ID_SEQ 		
INCREMENT BY NUMBER                 --步长，默认是1 
START WITH NUMBER                   --起始点 
[MAXVALUE NUMBER | NOMAXVALUE]      --最大值
[MINVALUE NUMBER | NOMINVALUE] 	    --最小值
[CYCLE | NOCYCLE ]                  --是否循环：达到最大或最小值限制之后，默认是抛出一个错误。
[CACHE NUMBER | NOCACHE]            --为了提高性能，Oracle预先生成序列值（默认20个），给使用者分发； 可以提供性能，但依旧有资源竞争。  
									--系统关闭后，会丢失20个序列值。
[ORDER | NOORDER]                   --只与群集数据库有关，Oracle强制集群中的实例协同递增序列 
;

ORACLE SEQUENCE 序列自增测试

--删除序列 
drop sequence SQ1_S001 ; 

--创建序列 
CREATE SEQUENCE SQ1_S001 INCREMENT BY 1 START WITH 1 MAXVALUE 999;

--调用【nextval】序列会自增，此时序列的值为1
SELECT SQ1_S001.nextval FROM DUAL ;  

--如果创建的序列未在此会话中调用过（触发），则无法查询当前值； 不会触发序列递增；
SELECT SQ1_S001.currval FROM DUAL ;  

--创建一张表，主键引用序列
create table t2 (id number default SQ1_S001.nextval ,name varchar(10)); 

--插入数据： 插入数据也会，会触发序列自增 
insert into t2 (name) values('zhangsan');  --2	zhangsan
insert into t2 (name) values('李四');  --3	李四 

--此时序列的当前值是 3  
SELECT SQ1_S001.currval FROM DUAL ;  

--插入一条数据，并不触发表的默认值，看下序列是是否会自增 
insert into t2 (id,name) values(99,'aiya');  --99	aiya 

--此时序列的当前值是 3,说明在默认值不被触发的情况下，序列不会自增。 
SELECT SQ1_S001.currval FROM DUAL ;  

--回滚当前事务 
rollback ; 
--T2表中的数据被清空
select * from t2;

--此时序列的值依旧是3，说明序列一旦发生就不会回退。 
SELECT SQ1_S001.currval FROM DUAL ;  

序列在未提交情况下也会递增，递增不可逆

--会话A（不提交）
DROP TABLE TEST1 PURGE ; 
CREATE TABLE TEST1 AS (ID NVUMBER , val NUMBER ,NAME VARCHAR2(10));
INSERT INTO TEST1 VALUES (SEQ_TEST1.NEXTVAL,10,'zhangsan'); --序列值为10 
ROLLBACK;
INSERT INTO TEST1 VALUES (SEQ_TEST1.NEXTVAL,11,'zhangsan'); --序列值为：11 
INSERT INTO TEST1 VALUES (SEQ_TEST1.CURRVAL,11,'zhangsan'); --当前值为：11 

--会话B
SELECT SEQ_TEST1.CURRVAL FROM DUAL;--查看序列当前值 
INSERT INTO TEST1 VALUES (SEQ_TEST1.NEXTVAL,12,'zhangsan'); --序列值为:12 

--会话C
CREATE TABLE TEST2 AS SELECT SEQ_TEST1.NEXTVAL , ID FROM TEST1; --序列会正常递增 
SELECT SEQ_TEST1.NEXTVAL FROM DUAL;  --也会造成序列增长 

序列增长

--每次访问nextval时都会使得序列递增。
--当前会话中调用 nextval 则当前会话的 currval会更新；其他会话中的currval会保留当前会后上一次调用nextval之后的值； 
--虽然currval值未发生变化，但是序列已经增长。 

  SELECT SEQ_TEST1.NEXTVAL FROM DUAL; --会导致序列增长 
  SELECT SEQ_TEST1.NEXTVAL FROM TEST1; --如果表中友3行数据，曾序列增加三个步长。 

--会话a
  
  SELECT SEQ_TEST1.CURRVAL FROM DUAL;   --10 
  SELECT SEQ_TEST1.NEXTVAL FROM DUAL;   --11 
  SELECT SEQ_TEST1.CURRVAL FROM DUAL;   --11 

--会话b
  
  SELECT SEQ_TEST1.CURRVAL FROM DUAL;   --10
  SELECT SEQ_TEST1.NEXTVAL FROM DUAL;   --12
  SELECT SEQ_TEST1.CURRVAL FROM DUAL;   --12

–序列修改

–必须是序列的拥有者才能修改序列，实行alter权限
–修改只会影响已后的序列号
–ALTER命令不能设置start with ,如果要从不同的序号出重新开始，则必须删除序列后重建。

ALTER SEQUENCE SEQ_TEST1
[INCREMENT BY NUMBER]
[START WITH NUMBER]
[MAXVALE/MINVALE NUMEBR | NOMAXVALUE/MINVALUE]
[CYCLE|NOCYCLE]
[CACLE|NOCACHE]
[ORDER|NOORDER]
;

–测试

SELECT SEQ_TEST1.CURRVAL FROM DUAL; --当前值
SELECT SEQ_TEST1.NEXTVAL FROM DUAL; --下一个值
ALTER SEQUENCE SEQ_TEST1 INCREMENT BY 10 ; --修改步长为10
SELECT SEQ_TEST1.CURRVAL FROM DUAL; --当前值
SELECT SEQ_TEST1.NEXTVAL FROM DUAL; --下一个值

DML：merge into 语句

CREATE TABLE T1 (ID VARCHAR2(10),SALARY NUMBER(10,4));
INSERT INTO T1 VALUES ('101',1000);
INSERT INTO T1 VALUES ('102',3000);
INSERT INTO T1 VALUES ('103',3400);
INSERT INTO T1 VALUES ('105',7000);
 
CREATE TABLE T2 ( ID VARCHAR2(10) ,BONUS NUMBER(10,4) ); 
INSERT INTO T2 VALUES ('105',700);
INSERT INTO T2 VALUES ('104',100);
INSERT INTO T2 VALUES ('103',0); 

--要修改的语句放在merge into语句之后 
--using 关键字后接匹配的数据表 
--on是匹配的关键字，且用于连接的关键字不能被操作 
--MERGE INTO 数据DML 语句需要commit后才能被其他会话看到修改结果； 


MERGE INTO T2 A  --这里的别名不需要as 关键字  
USING (SELECT ID ,SALARY FROM T1 ) B
ON (A.ID=B.ID)
WHEN MATCHED THEN 
  UPDATE SET A.BONUS =B.SALARY *0.2 --更新和删除可以同时存在 
  DELETE WHERE B.SALARY >=6000 
WHEN NOT MATCHED THEN 
  INSERT VALUES(B.ID,B.SALARY*0.2) --如果没有匹配就插入数据； insert语句不在需要 into 关键字 
;

临时表

--临时表分两种 会话级（SESSION） 事务（TRANSACTION）级别，会话级别会在会话结束后清空，事务级会在commit/rollback后清空；
--除此之外两个种表其他的性能一致 
--会话结束后的数据会被清空，但是表定义依旧会存在数据字典中； 
--DROP临时表时，数据表必须为空,必须要truncate表 
--临时表也会产生少量redo 
--临时表可以创建视图，索引 
--视图没有DML锁 

--事务级临时表后缀：ON COMMIT DELETE ROWS; 表中数据无法夸会话访问
--会话级临时表后缀：ON COMMIT PRESERVE ROWS; 表中数据无法夸事务访问 

CREATE GLOBAL TEMPORARY TABLE TMP_A (ID NUMBER ,NAEME VARCHAR2(10)) ON COMMIT PRESERVE ROWS;    

INSERT INTO TMP_A VALUES (1,'A') ;
INSERT INTO TMP_A VALUES (2,'B') ;
COMMIT; --此时创建的tmp_a是一个会话级的临时表，commit之后会保留数据，但是其他会话无法访问该数据


--直接删除表的话会报错，报错信息如下 
--	All the sessions using the session-specific temporary table have
--	to truncate table and all the transactions using transaction
--	specific temporary table have to end their transactions.
drop table tmp_a; 

--删除会话级临时表要truncate 表，必须是truncate不能是delete，这样是无法删除表的； 
--删除事务及临时表无需清空表，可直接删除。

truncate table tmp_a; 
drop table tmp_a;

--删除表之后，查看数据字典，并未发现表的回收站文件，说明临时表被删除之后无法闪回； 
select tname from tab;


--临时表上可以创建索引，但也必须是truncate的表，或新创建的表 
--创建emp_a表并插入数据，
truncate table TMP_A; --非空的表是无法创建索引的额
create index idx_emp_A ON TMP_A (ID);
SELECT * FROM USER_INDEXES; --查看创建的索引 

--临时表上创建视图 
--无论表中是否有数据，视图均和创建 
CREATE VIEW V_EMP_A AS SELECT * FROM TMP_A; 
SELECT * FROM USER_VIEWS;  --查看创建的视图 


--被删除的会话级临时表必须是空表
--事务及临时表无论有无数据，都可直接删除
--删除表会到导致临时表的索引会消失；视图会依旧保留，只是无法使用。 

TRUNC TABLE TMP_A; 
DROP TABLE TMP_A;
SELECT * FROM USER_INDEXES; --返回空  
SELECT * FROM USER_VIEWS;  --返回v_emp_as视图信息 

索引

–注意：具体应不应该创建索引，在哪里创建，要根据业务情景和具体的执行计划来决定。
–并不一定创建完索引查询就会变快，有时候会适得其反，加重系统运行负担。
–（如：频繁更新的列，添加索引后，数据库需要额外去频繁维护索引）。

–索引使用

1、索引的作用就是是为了加快数据检索，他可以隐式创建或显示创建，
2、索引是一个独立的对象，由Oracle自动使用和维护。

–可以使用索引

1、列包含较大范围的值（大量重复值，不能算大范围）
2、列包含大量的值
3、在where语句中或关联条件中常用的列或多个列，适合创建索引
4、表很大，语句检索的数据范围在表数据2~4%。 （大范围的全表取值，更适合全表扫描）

–不建议使用索引

1、表较小
2、查询中不常用的列
3、语句检索的数据量超过表的2~4%。
4、表中数据更新频繁，导致索引列也会被更新，系统负担较大，不建议使用。
5、被索引的列，被用于表达式的一部分被引用。（如：计算，或者函数操作）

–索引操作

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME); --创建
CREATE INDEX INDEX_UPER_NAME ON TABLE_NAME(UPPER(COLUMN_NAME)); --基于函数的索引
DROP INDEX INDEX_NAME;
DESC USER_INDEXES;
DESC USER_IND_COLUMNS;

–查询索引

SELECT
IC.INDEX_NAME
,IC.COLUMN_NAME
,IC.COLUMN_POSITION COL_POS
,IX.UNIQUENESS
FROM USER_INDEXES IX ,USER_IND_COLUMNS IC
WHERE IC.INDEX_NAME=IX.INDEX_NAME
AND IC.TABLE_NAME=IX.TABLE_NAME
;