oracle数据库总结回顾

--第一章 简单查询语句
--分组排序
--分组
SELECT DEPTNO,AVG(SAL),MAX(SAL),MIN(SAL),SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>1500;
--排序
SELECT * FROM EMP ORDER BY SAL DESC;
--书写顺序
SELECT /FROM /WHERE /GROUP /BY HAVING /ORDER BY
--执行顺序
FROM /WHERE /ORDER BY /HAVING /SELECT /ORDER BY
--第二章 函数
--一、转换函数
--1.TO_CHAR
--数值转
SELECT TO_CHAR(1258.354,'$99999.999') FROM DUAL;
SELECT TO_CHAR(125.21,'L99999.999') FROM DUAL;
--日期转
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS:FF') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
--2.TO_NUMBER
SELECT TO_NUMBER('$15214','$999999.99') FROM DUAL;
SELECT TO_NUMBER('￥125','L9999') FROM DUAL;
--3.TO_DATE
SELECT TO_DATE('19980519','YYYY-MM-DD') FROM DUAL;
--二、字符型函数
--返回字符
--1.CONCAT(STR1,STR2) 连接
SELECT CONCAT('HELLO',' WORLD') FROM DUAL;
--2.LOWER() UPPER() INITCAP()    小写、大写、首字母大写
SELECT LOWER('LSKAKJ') FROM DUAL;
SELECT UPPER('jjj') FROM DUAL;
SELECT INITCAP('kkkkk') FROM DUAL;
--3.REPLACE(STR,S1,S2)   替换
SELECT REPLACE('LISI','I','Q') FROM DUAL;
--4.TRIM()/LTRIM()/RTRIM()    去除
SELECT TRIM(' KKKKK ') FROM DUAL;
SELECT TRIM(LEADING 'S' FROM 'SSGT') FROM DUAL;
SELECT LTRIM('SGGGS','S') FROM DUAL;
SELECT RTRIM('HHHS','S') FROM DUAL;
--5.SUBSTR(STR,IND,LEN)     截取
SELECT SUBSTR('ABCDEFBAD',1,5) FROM DUAL;
--6.LPAD(STR,N,S)/RPAD(STR,N,S)     填充
SELECT LPAD('AS',10,'S') FROM DUAL;
SELECT RPAD('AS',10,'S') FROM DUAL;
--返回数值
--7.LENGTH(STR)    获取字符串长度
SELECT LENGTH('AHGUASA') FROM DUAL;
--8.INSTR(STR,S,IND,N)   获取字符定位
SELECT INSTR('ABSFGAFAGS','A',1,2) FROM DUAL;
SELECT INSTR('ABVSGFAVSA','A',-2,2) FROM DUAL;
--三、数值型函数
--1.ABS()   绝对值
SELECT ABS(-54) FROM DUAL;
--2.CEIL()/FLOOR()     向上取整、向下取整
SELECT CEIL(12.2) FROM DUAL;
SELECT CEIL(-12.2) FROM DUAL;
--无论NUM是正数还是负数，所谓上，即数轴指向的方向，所谓下，即数轴背向的方向
SELECT FLOOR(12.5) FROM DUAL;
SELECT FLOOR(-12.5) FROM DUAL;
--3.MOD()   取余
SELECT MOD(12,5) FROM DUAL;
--4.ROUND(NUM,P)          四舍五入
SELECT ROUND(12.5647,2) FROM DUAL;
SELECT ROUND(12.54,-1) FROM DUAL;
--5.TRUNC(NUM,P)         截断
SELECT TRUNC(SYSDATE,'MM') FROM DUAL;
SELECT TRUNC(14.25,1) FROM DUAL;
SELECT TRUNC(15.25,-1) FROM DUAL;
--四、日期型函数
--1.日期加减
SELECT SYSDATE+1 FROM DUAL;
--2.MONTHS_BETWEEN()   获取两个日期的月份差值
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('20230114','YYYY-MM-DD')) FROM DUAL;
--3.LAST_DAY()             获取月末日期
SELECT LAST_DAY(SYSDATE) FROM DUAL;
--4.ADD_MONTHS()          月份加减
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,-2) FROM DUAL;
--5.NEXT_DAY(D,W)        获取下个周几
SELECT NEXT_DAY(TRUNC(SYSDATE,'IW')+6,'星期五') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'星期二') FROM DUAL;
--五、其他函数
--1.DECODE()     条件取值   SELECT * FROM EMP;
SELECT JOB,SAL,DECODE(JOB,'CLERK',1.2*SAL,'SALESMAN',1.5*SAL,2*SAL) FROM EMP;
--2.COALESCE()       返回集合中第一个不为空的值
CREATE TABLE BOY(姓名 CHAR(50),
                 小名 CHAR(20),
                 绰号 CHAR(50));  --建表
SELECT * FROM BOY FOR UPDATE;
SELECT 姓名,小名,绰号,COALESCE(姓名,小名,绰号) FROM BOY;
SELECT COALESCE(姓名,小名,绰号) FROM BOY;
--3.NVL(STR,0) 为空赋0             NVL2(STR,1,2) 为空赋1，不为空赋2、
SELECT NVL(COMM,0) FROM EMP;
SELECT NVL2(COMM,COMM+500,300) FROM EMP;
--4.DISTINCT()      去重
SELECT DISTINCT DEPTNO FROM EMP;
--第三章 子查询
--11.查询工资小于1000的员工所在的部门的部门名称和工作地点
--不相关子查询
SELECT DNAME,LOC FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE SAL<1000);
--相关子查询
SELECT DNAME,LOC FROM DEPT T1 WHERE EXISTS(SELECT DEPTNO FROM EMP T2 WHERE SAL<1000 AND T1.DEPTNO=T2.DEPTNO);
--第四章 伪列和开窗函数
--伪列 ROWNUM
--4.查询员工工资排在第四名到倒数第四名的员工
SELECT * FROM (SELECT A.*,ROWNUM RW FROM (SELECT * FROM EMP ORDER BY SAL DESC) A) WHERE RW BETWEEN 4 AND (SELECT COUNT(1) FROM EMP)-3;
--开窗函数 SELECT * FROM BUSINESS;
--1.聚和类
SELECT A.*,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP A;
--2.排序类 (必须加ORDER BY，不能指定窗口（ROWS）)
--ROW_NUMBER()    不并列不跳跃
SELECT A.*,ROW_NUMBER() OVER(PARTITION BY WEEK ORDER BY AMT) FROM BUSINESS A;
--RANK()      并列跳跃
SELECT A.*,RANK() OVER(PARTITION BY WEEK ORDER BY AMT) FROM BUSINESS A;
--DENSE_RANK()  并列不跳跃
SELECT A.*,DENSE_RANK() OVER(PARTITION BY WEEK ORDER BY AMT) FROM BUSINESS A;
--去重  SELECT * FROM BIAO;
SELECT ENAME, BNO, BSEX,TT
  FROM (SELECT ENAME,
               BNO,
               BSEX,TT,
               ROW_NUMBER() OVER(PARTITION BY ENAME ORDER BY TT DESC) BR --将名字相同的分为一组，再在这些组里根据日期排序，取出每个组里排第一的的数据
          FROM BIAO)
 WHERE BR = 1;  --ROW_NUMBER的另一种用法,当多条数据属于某一个人，但数据都不尽相同时（在某些字段上去重，（取最新数据））
 SELECT * FROM (SELECT A.*,ROW_NUMBER() OVER(PARTITION BY ENAME ORDER BY TT DESC) BR FROM BIAO A) WHERE BR=1;
--3.偏移类
--LAG()
SELECT A.*,LAG(DAY,2,0)OVER(PARTITION BY WEEK ORDER BY DATE_DT) FROM BUSINESS A; 
--LEAD()
SELECT A.*,LEAD(DAY,2) OVER(PARTITION BY WEEK ORDER BY DATE_DT) FROM BUSINESS A;
----查询五月连续登录五天的用户  SELECT * FROM EXAM;
--LAG()
SELECT ID
  FROM (SELECT A.*,
               TO_DATE(TS, 'YYYY-MM-DD') - 4 T1,
               LAG(TS, 4) OVER(PARTITION BY ID ORDER BY TS) T2
          FROM EXAM A)
 WHERE T1 = TO_DATE(T2, 'YYYY-MM-DD');
 --LEAD()
 SELECT ID
   FROM (SELECT A.*,
                LEAD(TS, 4) OVER(PARTITION BY ID ORDER BY TS) T1,
                TO_DATE(TS, 'YYYY-MM-DD') + 4 T2
           FROM EXAM A)
  WHERE TO_DATE(T1, 'YYYY-MM-DD') = T2;
--窗口范围 
PRECEDING FOLLOWING CURRENT UNBOUNDED
--前二到当前
SELECT A.*,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY AMT ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM BUSINESS A;
--前二后二
SELECT A.*,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) FROM EMP A;
--不受限到当前
SELECT A.*,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AB FROM EMP A;
--第五章 集合运算及行列转换
--集合运算
--并集 UNION /UNION ALL
SELECT DEPTNO FROM EMP
UNION --去重
SELECT DEPTNO FROM DEPT;
SELECT DEPTNO FROM EMP
UNION ALL   --不去重
SELECT DEPTNO FROM DEPT;
--交集 INTERSECT
SELECT DEPTNO FROM EMP
INTERSECT     --你有我也有的
SELECT DEPTNO FROM DEPT;
--差集 MINUS
SELECT DEPTNO FROM DEPT
MINUS       --保留独有的
SELECT DEPTNO FROM EMP;
--行列转换 SELECT * FROM SCORE_1;  SELECT * FROM SCORE_2;
--行转列
--CASE WHEN THEN
SELECT STUDENT,SUM(CASE WHEN COURSE='CHINESE' THEN SCORE ELSE 0 END) CHINESE,
               SUM(CASE WHEN COURSE='MATH' THEN SCORE ELSE 0 END) MATH,
               SUM(CASE WHEN COURSE='ENGLISH' THEN SCORE ELSE 0 END) ENGLISH
               FROM SCORE_1 GROUP BY STUDENT ORDER BY STUDENT;
--DECODE
SELECT STUDENT,SUM(DECODE(COURSE,'CHINESE',SCORE,0)) CHINESE,
               SUM(DECODE(COURSE,'MATH',SCORE,0)) MATH,
               SUM(DECODE(COURSE,'ENGLISH',SCORE,0)) ENGLISH
               FROM SCORE_1 GROUP BY STUDENT ORDER BY STUDENT;
--PIVOT
SELECT * FROM SCORE_1 PIVOT(SUM(SCORE) FOR COURSE IN('CHINESE' AS CHINESE,'MATH' AS MATH,'ENGLISH' AS ENGLISH));
--列转行
--UNION ALL(一个学生三个坑)
SELECT STUDENT,'CHINESE' AS COURSE,CHINESE SCORE FROM SCORE_2
UNION ALL
SELECT STUDENT,'MATH' AS COURSE,MATH SCORE FROM SCORE_2
UNION ALL
SELECT STUDENT,'ENGLISH' AS SCORE,ENGLISH SCORE FROM SCORE_2 ORDER BY STUDENT;
--UNPIVOT
SELECT * FROM SCORE_2 UNPIVOT(SCORE FOR COURSE IN(CHINESE,MATH,ENGLISH));
--专有函数解析
/* 都写在FROM后面，PIVOT用于行转列，需要加聚合函数SUM(),而UNPIVOT()，用于列转行，如上所示，COURSE字段用来盛放三个字段，而SCORE字段
用于存放这三个字段下的数据
*/
--第六章 表连接
/*内连接：按照关联条件，将两表中都符合条件的数据取出；
外连接：
左外连接：将左表独有的数据加上符合关联条件的数据取出来；
右外连接：将右表独有的数据加上符合关联的数据取出来；
全外连接：将两表独有的数据加上符合关联条件的数据取出来；
笛卡尔连接（交叉连接）：没有关联条件，左表的每一条数据都与右表中的每一条数据做一次关联，相当于 左表数据*右表数据
*/
42.查询每个学生的姓名、最高成绩的课程名称和最高成绩
SELECT * FROM STUDENTS; --学生表
SELECT * FROM SCORES  ; --成绩表
SELECT * FROM COURSES ; --课程表
SELECT A.SNO,MAX(SCORE) FROM STUDENTS A LEFT JOIN SCORES B ON A.SNO=B.SNO GROUP BY A.SNO;
SELECT A.SNAME,C.CNAME,MA
  FROM STUDENTS A
  LEFT JOIN SCORES B
    ON A.SNO = B.SNO
  LEFT JOIN COURSES C
    ON B.CNO = C.CNO
  LEFT JOIN (SELECT SNO, MAX(SCORE) MA FROM SCORES GROUP BY SNO) D
    ON B.SNO = D.SNO WHERE SCORE=MA;
--第七章 DML语句
--FOR UPDATE
SELECT * FROM EMP WHERE DEPTNO=10 FOR UPDATE;
--不基于原表
--增   INSERT INTO TABLE VALUES
--多条数据的插入
CREATE TABLE EMP5 AS SELECT * FROM EMP WHERE DEPTNO=20; --建一个备份表拿来操作
SELECT * FROM EMP5;
INSERT INTO EMP5 SELECT * FROM EMP WHERE DEPTNO=10;
--删   DELETE FROM TABLE WHERE
SELECT * FROM EMP5;
DELETE FROM EMP5 WHERE DEPTNO=10;
COMMIT;
--改   UPDATE TABLE SET 
SELECT * FROM EMP5;
UPDATE EMP5 SET DEPTNO=30;
UPDATE EMP5 SET ENAME=JOB,JOB=ENAME;
--基于原表 MEGER INTO
--表
SELECT * FROM GOODS_CURRENT;
SELECT * FROM GOODS_CHANGE;
--更新价格，更新商品
MERGE INTO GOODS_CURRENT A
USING GOODS_CHANGE B
ON(A.GOODS_ID=B.GOODS_ID)
WHEN MATCHED THEN
UPDATE
SET A.PRICE=B.PRICE,A.COST=A.COST+B.COST
WHEN NOT MATCHED THEN
INSERT(A.GOODS_ID,A.GOODS_NAME,A.PRICE,A.COST,A.GOODS_TYPE)
VALUES(B.GOODS_ID,B.GOODS_NAME,B.PRICE,B.COST,B.GOODS_TYPE)
--第八章 DDL语句与常见的数据库对象
--DDL操作
--创建
CREATE TABLE EMP(A NUMBER);
--删除
DROP TABLE EMP;
--修改
--ALTER
--增   ADD
ALTER TABLE EMP ADD(A NUMBER);
--删   DROP
ALTER TABLE EMP DROP (A,B);
--改   MODIFY(改数据精度与数据类型)
ALTER TABLE EMP MODIFY(DEPTNO VARCHAR2);
--清空    TRUNCATE
TRUNCATE TABLE EMP;
--重命名 RENAME
ALTER TABLE EMP RENAME TO EMP1;
--索引
--1.索引的命名规范           IND_TNAME_COLUMN   (IND_EMP_DEPTNO)
--索引的的类型
--按存储形式分
--1.B+TREE索引      列基数比较大时使用
--语法：
CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);
CREATE INDEX IND_EMP5_SAL ON EMP(SAL);
--2.位图索引       列基数比较小时使用   BITMAP
--语法：
CREATE BITMAP INDEX IND_NAME ON TB_NAME(COL_NAME);
CREATE BITMAP INDEX IND_EMP_COMM ON EMP(COMM);
--3.返向键索引 REVERSE     原始数据分支不明显但反向数据分支明显的列
--语法：
CREATE INDEX IND_NAME ON TB_NAME(COL_NAME) REVERSE;
CREATE INDEX IND_EMP_JOB ON EMP(JOB) REVERSE;
--4.基于函数的索引    对某列进行筛选时经常需要配合函数使用
--语法：
CREATE INDEX IND_NAME ON TB_NAME(FUNCTION(COL_NAME));
CREATE INDEX IND_EMP_ENAME ON EMP(LENGTH(ENAME));
--按唯一性分
--1.唯一性索引 UNIQUE
--语法：
CREATE UNIQUE INDEX IND_NAME ON TB_NAME(COL_NAME);
CREATE UNIQUE INDEX IND_EMP_DEPTNO ON EMP(DEPTNO);
/*注意点：
1）B-TREE索引可以建立唯一索引，位图索引不能建立唯一索引（因为位图索引上有很多
重复值                                       ）
CREATE UNIBUE BITMAP INDEX IND_EMP_ENAME ON EMP(ENAEM); --ERROR
2）如果在某列上建立了唯一约束或主键约束，ORACLE会自动在该列上建立一个同名的唯一索引*/
--2.非唯一性索引
--语法：
CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);
CREATE INDEX IND_EMP_JOB ON EMP(JOB);
--按列的个数分
--1.单列索引
--语法：
CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);
CREATE INDEX IND_EMP_SAL ON EMP(SAL);
--2.复合索引(也称联合索引)
--语法：
CREATE INDEX IND_NAME ON TB_NAME(COL_NAME1,COL_NAME2...);
CREATE INDEX IND_EMP_SC ON EMP(SAL,COMM);
--索引的删除
--语法：
DROP INDEX IND_NAME;
DROP INDEX IND_EMP_SC;
--索引的禁用与重建
--禁用
语法：（unusable）
ALTER INDEX IND_NAME UNUSABLE;
ALTER INDEX IND_EMP_SC UNUSABLE;
--重建
语法：
ALTER INDEX IND_NAME REBUILD;
ALTER INDEX IND_EMP_SC REBUILD;
--索引的数据字典
--所有索引
SELECT * FROM USER_INDEXES;
--索引列
SELECT * FROM USER_IND_COLUMNS;
--索引函数
SELECT * FROM USER_IND_EXPRESSIONS;
--视图
--语法
CREATE [OR REPLACE] VIEW V_viewname [(别名1,别名2...)] 
AS <子查询>
[WITH CHECK OPTION]  --检查  --可读写视图
[WITH READ ONLY] ;  --只读（不可对视图进行增删改）
--创建一个视图，内容包含各部门名称、工作地点、小写姓名、薪资、奖金、薪资奖金合计值。
CREATE OR REPLACE VIEW V_EM1 AS SELECT DNAME,LOC,LOWER(ENAME) A,SAL,COMM,SAL+NVL(COMM,0) B FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO=E.DEPTNO;
SELECT * FROM V_EM1;
DROP VIEW V_EM1;
--4.4视图的作用
1）视图能简化用户操作
2）视图使用户能以多种角度看待同一问题
3）视图对重构数据集提供了一定程度的逻辑独立性
4）视图能够对机密数据提供安全保护
5）适当利用视图可以更清晰地表达查询
--视图的数据字典
SELECT *FROM USER_VIEWS;
--序列
--1）语法
CREATE SEQUENCE 序列名称 SEQ_NAME
START WITH N --初始序号 递增：默认MINVALUE  递减：默认MAXVALUE
INCREMENT BY N --增长幅度  N为正 递增  N为负  递减
MINVALUE N | NOMINVALUE --最小值 N | 无最小值
MAXVALUE N | NOMAXVALUE --最大值 N | 无最大值
CACHE N | NOCACHE --缓存 N个序号 | 无缓存    默认缓存20个序号  --尽量设置大一点的值
CYCLE | NOCYCLE ;--循环 达到极值时是否从新循环生成序号
--
SELECT SEQ_1.NEXTVAL FROM DUAL;
DROP SEQUENCE SEQ_1;
--创建序列
CREATE SEQUENCE SEQ_1
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 50
NOCACHE
CYCLE
--
SELECT SEQ_1.NEXTVAL FROM DUAL;
SELECT SEQ_1.CURRVAL FROM DUAL;
--
SELECT TO_CHAR(SYSDATE,'YYYYMMDD')||LPAD(SEQ_1.NEXTVAL,6,'0'),ENAME FROM EMP;
--修改和删除
ALTER SEQUENCE SEQ_NAME CYCLE; --修改循环
ALTER SEQUENCE SEQ_1 CYCLE;
ALTER SEQUENCE SEQ_ NAME INCREMENT BY 3; --修改涨幅    select * from emp;
QLTER SEQUENCE SEQ_1 INCREMENT BY 3;
--第九章 其他数据库对象
--一，约束
--约束：约束是强加在表中的规则或条件。确保数据库满足业务规则，保证数据库的完整性。
--作用：让表中数据更加符合实际需要，如果录入不满足的数据，拒绝录入。
--约束分类
--1.唯一约束  UNIQUX
--建完表后添加约束：(表级约束)
语法：ALTER TABLE TB_NAME ADD CONSTRAINT CONSTR_NAME UNIQUE(COL_NAME);
ALTER TABLE EMP ADD CONSTRAINT EMP_EMPNO UNIQUE(EMPNO);
--建表的同时添加约束：（行级约束）
--自动生成约束名称
CREATE TABLE LI(LA VARCHAR2(15) UNIQUE,FI VARCHAR2(14));
--自定义约束名称：
CREATE TABLE LI(LA VARCHAR2(15) CONSTRAINT NU_LL NOT NULL,FI NUMBER);
--2.非空约束     非空约束只有行级约束，没有表级约束
--建表的同时添加约束：（行级约束） NOT NULL
CREATE TABLE LI(LA VARCHAR2(15) NOT NULL,FI NUMBER);
--3.主键约束 PRIMARY KEY
--建完表后添加约束：(表级约束)
ALTER TABLE EMP ADD CONSTRAINT PK_ENAME PRIMARY KEY(ENAME);
--建表的同时添加约束：（行级约束）
CREATE TABLE LI(LA VARCHAR2(15) PRIMARY KEY,FI NUMBER);
--4.检查约束 CHECK
--建完表后添加约束：
ALTER TABLE EMP ADD CONSTRAINT CH_SAL CHECK(SAL BETWEEN 1500 AND 6000);
--建表的同时添加约束：
CREATE TABLE LI(LA VARCHAR2(15) NOT NULL,FI NUMBER CHECK(FI IN(1,2,3)));
--5.外键约束
--建完表后添加约束：
--语法：
ALTER TABLE TB_NAME ADD CONSTRAINT CONSTR_NAME FOREIGN KEY  (COL_NAME)  REFERENCES MAIN_TB_NAME(PK_COL_NAME OR UN_COL_NAME);
ALTER TABLE EMP ADD CONSTRAINT F_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);
--约束的删除
ALTER TABLE EMP DROP CONSTRAINT F_DEPTNO;
--约束重命名
ALTER TABLE EMP RENAME CONSTRAINT F_原 TO F_改;
--约束禁用激活
--禁用
--语法： ALTER TABLE TB_NAME DISABLE CONSTRAINT CONSTR_NAME;
ALTER TABLE EMP DISABLE CONSTRAINT PK_EMPNO;
--激活
--语法：  ALTER TABLE TB_NAME ENABLE CONSTRAINT CONSTR_NAME;
ALTER TABLE EMP ENABLE CONSTRAINT PK_EMPNO;
--二，注释    COMMENT ON
--1.注释表
--语法：COMMENT ON TABLE TB_NAME IS '注释内容’;
COMMENT ON TABLE EMP IS '员工信息表';
--1.2 修改注释：  COMMENT ON COLUMN TB_NAME IS '新的注释内容’;
COMMENT ON TABLE EMP IS '新员工信息表';
--1.3 删除注释：  COMMENT ON COLUMN TB_NAME IS '’;
COMMENT ON TABLE EMP IS '';
--2.注释列
--语法：  COMMENT ON COLUMN TB_NAME.COL_NAME IS '注释内容’;
COMMENT ON COLUMN EMP.DEPTNO IS '部门编号';
--2.2 修改注释：  COMMENT ON COLUMN TB_NAME.COL_NAME IS '新的注释内容’;
COMMENT ON COLUMN EMP.SAL IS '新员工薪资';
--2.3 删除注释：  COMMENT ON COLUMN TB_NAME.COL_NAME IS '’;
COMMENT ON COLUMN EMP.SAL IS '';
--3.注意点
1.注释内容是字符型的，注意加英文单引号
2.删除注释时用的‘’不能替换为NULL
3.添加注释时只能一个一个添加
4.使用CREATE TABLE TB_NAME AS SELECT … 创建表时注释会被忽略(备份)/备份表时注释会被忽略
--4.数据字典
--表
SELECT * FROM USER_TAB_COMMENTS;
--列
SELECT * FROM USER_COL_COMMENTS;
/*  注：不论是列的注释还是表的注释，其语法基本一样，就是列的注释的关键词是COLUMN，而表是TABLE
列：COMMENT ON COLUMN TABLE_NAME.COLUMN_NAME IS '';
表：COMMENT ON TABLE TABLE_NAME LS '';
其后的修改删除的语法都是一样的，修改直接在后面的单引号内写上要修改的内容
而删除则是单引号内什么都不写，表示将注释内容置为空    */
--三，默认值  DEFAULT
/*定义：
1）定义：在某个字段未插入数据的情况下，自动为其插入固定值，这个固定值即默认值。
2）注意：默认值会在插入数据时未指定该字段内容时生效，若指定该字段插入数据为空值，则默认值设定不会生效。*/
--1.建表的同时创建默认值
/*语法：
CREATE TABLE TB_NAME (
COL_NAME1 COL_TYPE DEFAULT DEFAULT_VALUE ,
COL_NAME2 COL_TYPE DEFAULT DEFAULT_VALUE ,
COL_NAME3 COL_TYPE DEFAULT DEFAULT_VALUE ,
...
COL_NAMEn COL_TYPE DEFAULT DEFAULT_VALUE );*/
--建表的同时添加默认值
CREATE TABLE NAMES(FIRST_NAME VARCHAR2(10) DEFAULT '李',
                   LAST_NAME VARCHAR2(20));
--插入数据验证：
INSERT INTO NAMES(LAST_NAME) VALUES('道煌');
--查表看结果
SELECT * FROM NAMES;
--2.建表之后添加默认值
--语法：  ALTER TABLE TB_NAME MODIFY COL_NAME DEFAULT DEFAULT_VALUE;
--添加默认值
ALTER TABLE EMP MODIFY SAL DEFAULT 3500;
--修改默认值
ALTER TABLE EMP MODIFY SAL DEFAULT 5000;
--删除默认值
ALTER TABLE EMP MODIFY SAL DEFAULT NULL;
--3.时间戳
/*时间戳：为表添加一个时间戳字段，其默认值设置为TO_CHAR(SYSDATE,'YYYY-MM-DD')，
这样以后在插入数据时会自动生成一条数据，显示数据插入的时间*/
--以刚刚创建的NAMES表为例
--1.添加字段（一般是在建表的时候将字段添加）
ALTER TABLE NAMES ADD(TINES VARCHAR2(20));      --ALTER TABLE NAMES MODIFY(TINES VARCHAR2(40));
--2.查表看字段添加没有
SELECT * FROM NAMES;
--3.为TIMES字段添加时间戳
ALTER TABLE NAMES MODIFY TINES DEFAULT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS');
--4.插入数据验证
INSERT INTO NAMES(LAST_NAME) VALUES('道光');
INSERT INTO NAMES(LAST_NAME) VALUES('道辉');
COMMIT;
--5.查表检验
SELECT * FROM NAMES;
--第十章 其他SQL语言分类
--1.分类
/*DDL:数据定义语言      CREATE /DROP /ALTER /TRUNCATE /RENAME
DML：数据操纵语言       INSERT INTO VALUES /DELETE /UPDATE SET
DQL：数据查询语言       SELECT
DCL：数据控制语言       GRANT /REVOKE
TCL：事务控制语言       COMMIT /ROLLBACK /SAVEPOINT
*/
--DCL  事务控制语句 （需在管理员用户下操作）
--用户维护
--2.1 创建用户
--语法： CREATE USER USER_NAME IDENTIFIED BY PASSWORD;
CREATE USER DAHU IDENTIFIED BY 123456;
/*注意：
1）用户名不区分大小写，在创建、删除、登录用户时可以任意使用大小写；密码严格区分大小写，
无论在创建用户、修改密码还是登录时都要注意
2）此时仅仅是在数据库中注册了用户，但没有任何权限，甚至登录数据库的权限都没有
3）每个用户下有自己各自的内容，互相之间不能直接访问，管理员用户可以直接访问其他用户的内容
4）普通用户间可以通过赋予权限实现互相访问*/
--2.2 为用户添加权限
--语法： GRANT PRIVILEGE TO USER_NAME;
GRANT CREATE VIEW TO SCOTT;
--2.3 收回用户权限
--语法： REVOKE PRIVILEGE FROM USER_NAME;
REVOKE CREATE VIEW FROM SCOTT;
--2.4 修改用户密码
--语法： ALTER USER USER_NAME IDENTIFIED BY NEW_PASSWORD;
ALTER USER SCOTT IDENTIFIED BY 123456;
--2.5 锁定/解锁用户
--锁定
--语法： ALTER USER USER_NAME ACCOUNT LOCK;
ALTER USER SCOTT ACCOUNT LOCK;
--解锁
--语法： ALTER USER USER_NAME ACCOUNT UNLOCK;
ALTER USER SCOTT ACCOUNT UNLOCK;
--2.6 删除用户
--语法： DROP USER USER_NAME； 用户下无任何对象         DROP USER USER_NAME CASCADE; --连同用户下的对象一并删除
DROP USER SCOTT;      /      DROP USER SCOTT CASCADE;
--2.7 数据字典
SELECT * FROM DBA_USER;
--角色维护
--3.1 角色定义
带有一系列权限的集合。
作用：
用户被赋予某角色，相当于同时被赋予了该角色下的所有权限
分类：
系统角色：数据库自带角色
自定义角色：用户根据自身实际需求，自行创建的角色
--3.2 创建角色
--语法： CREATE ROLE ROLE_NAME;
CREATE ROLE ROLE_JS;
--3.3 删除角色
--语法： DROP ROLE ROLE_NAME;
DROP ROLE ROLE_JS;
--将权限赋给角色，将角色赋给用户
--（1）给角色权限
GRANT CREATE VIEW TO ROLE_JS;
--(2)将角色赋给用户
GRANT ROLE_JS TO SCOTT;
--3.4 查看角色具有的权限
--对象权限
SELECT * FROM DBA_TAB_PRIVS;
--系统权限
SELECT * FROM DBA_SYS_PRIVS;
--用户，角色，权限之间的关系
用户：数据库的使用者，比如SYS、SCOTT
权限：指能否在数据库中做某种操作，比如创建表、查询数据
角色：带有一系列权限的集合，拥有角色，即拥有该角色下所有的权限，比如CONNECT,RESOURCE,DBA

在做权限管理时，可直接将某权限赋予给某个用户，若某些权限需要经常赋予给用户，可直接建立一个包含这些权限的角色，
直接赋予角色即可达到赋予众多权限的目的。
--TCL 事务控制语言
--4.1 事务的ACID特性
原子性：事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做要么都不做
一致性：事务执行的结果必须是使数据库从一个一致性状态变成另一个一致性状态
隔离性：持续性也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。
接下来的其他操作或故障不应该对其执行结果有任何影响一个事务的执行不能被其他事务干扰
持久性：
--4.2 事务关键词
COMMIT;  --事务提交
ROLLBACK;  --事务回滚
SAVEPOINT; --保存点 --在接下来的DML操作可能有风险的情况下在DML过程中使用
--(1) COMMIT
--(2) ROLLBACK
--(3) 保存点语法：
设立保存点：SAVEPOINT SP_NAME;
回滚到某保存点：ROLLBACK TO SAVEPOINT SP_NAME;
--设立了保存点，则就是把表的当前状态保存下来，在下次想还原数据时直接回滚到这个状态就行
SAVEPOINT EMP_715;  --设立保存点
SELECT * FROM EMP;
DELETE FROM EMP WHERE DEPTNO=10; --删除10号部门的成员以做试验
ROLLBACK TO SAVEPOINT EMP_715;--回滚到保存点
--数据库的关闭/开启
--5.1 数据库关闭
--关闭数据库(在命令行窗口以管理员权限进行操作)
SHUTDOWN NORMAL/TRANSACTION/IMMEDIATE/ABORT
NORMAL:正常关闭方式（默认），阻止新用户接入并等待已连接用户主动断开后关闭。
TRANSACTION:事务关闭方式，阻止新连接和新事务，等待所有事务提交完，用户断开连接后关闭。
IMMEDIATE:立即关闭方式(常用)，立即终止当前用户连接，强行停止并回退事务，关闭数据库。
ABORT:终止关闭方式，具有强烈的强制性和一定的破坏性，除上诉三种外，慎用这种。
SHUTDOWN IMMEDIATE --关闭
--5.2 数据库开启
--开启数据库
--使用管理员用户登录SQLPLUS
STARTUP  --开启