Oracle基础

 
 1 常见关键字
1.1查询类

select, where,  join on, group by, order by,having
 执行顺序 ：

from > where > join on > group by > having > select > order by
 -- where 行过滤，select部分的字段别名不可以用在where
 --having 分组后过滤
 --order by 对查询结果进行排序，其后可以用select后面出现的别名。
   order by col [asc | desc] [NULLS FIRST/LAST]   --可以指定空值的排序

   --distinct
   如果使用dsitinct,排序列必须是选择列
select distinct depton, job
from emp
order by job;

1.2 事务类
 commit, rollback , set transaction
 commit
 语法：

 COMMIT [ WORK ] [ COMMENT clause ] [ WRITE clause ] [ FORCE clause ];
 WORK：可选的。它被Oracle添加为符合SQL标准
 COMMENT clause：可选的。 它用于指定与当前事务关联的注释
 WRITE clause：可选的。 它用于指定将已提交事务的重做信息写入重做日志的优先级。
 FORCE clause：可选的。 它用于强制提交可能已损坏或有疑问的事务。

 rollback
 语法:

ROLLBACK [ WORK ] [ TO [SAVEPOINT] savepoint_name  | FORCE ‘string‘ ];
 TO SAVEPOINT savepoint_name：可选，ROLLBACK语句撤消当前会话的所有更改，直到由savepoint_name指定的保存点。 如果省略该子句，则所有更改都将被撤消。
 FORCE ‘string’：可选，它用于强制回滚可能已损坏或有问题的事务。 使用此子句，可以将单引号中的事务ID指定为字符串。

 SET TRANSACTION
 在Oracle中，SET TRANSACTION语句可以用来设置事务的各种状态，比如只读、读/写、隔离级别，为事务分配名称或将事务分   配回滚段等等。
 语法：SET TRANSACTION [ READ ONLY | READ WRITE ]
                [ ISOLATION LEVEL [ SERIALIZE | READ COMMITED ]
                [ USE ROLLBACK SEGMENT ‘segment_name‘ ]
                [ NAME ‘transaction_name‘ ];

--for example
SET TRANSACTION READ WRITE NAME ‘RW_example‘;

 锁表 LOCK TABLE
在Oracle中，LOCK TABLE语句可以用来锁定表、表分区或表子分区。
LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];

SQL （Structured Query Language，结构化查询语言）支持如下类别命令：
数据定义语言（DDL）：CREATE（创建）、ALTER（更改）、TRUNCATE（截断）、DROP（删除）命令。
数据操纵语言(DML)：INSERT（插入）、SELECT（选择）、DELETE（删除）、UPDATE（修改）命令。
事务控制语言(TCL)：COMMIT（提交）、SAVEPOINT（保存点）、ROLLBACK（回滚）命令。
数据控制语言(DCL)：GRANT（授予）、REVOKE（回收）命令。

UPDATE xxx;
SAVEPOINT mark1;
DELETE FROM xxx;
SAVEPOINT mark2;
ROLLBACK TO SAVEPOINT mark1;
COMMIT;

授予对象权限
GRANT SELECT,UPDATE ON EMP
TO ORCL;

取消对象权限
REVOKE SELECT,UPDATE ON EMP
FROM ORCL;

常用的系统权限集合有以下三个:
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)
常用的数据对象权限:
ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,
DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名

GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;

2 表操作
2.1 建表与删表

创建表
 语法：

create table table_name (
             column1 字段类型 [约束],
             column2 字段类型 [约束],
             .....
          );

 删除表：DROP TABLE TABLE_NAME;

实例

 -- 创建 person 表

 CREATE TABLE PERSON(
     P_NO      NUMBER  NOT NULL,
     P_NAME    VARCHAR2(10),
     P_AGE     NUMBER(3),
     BIRTHDAY  DATE
 ) ; 

 

2.2 插入数据
 方法1：insert into 表名（ 列1，列2，...列n） values (值1，值2，...，值n);
 方法2：insert into 表1（ 列1，列2，...列n） select 列1，列2，...列n from 表2 where...;
 

INSERT INTO PERSON(P_NO,P_NAME,P_AGE,BIRTHDAY) values (8856,‘张小明‘,18,date‘2000-5-6‘);
INSERT INTO PERSON(P_NO,P_NAME,P_AGE,BIRTHDAY) values (8857,‘张小红‘,18,date‘2000-7-6‘);
INSERT INTO PERSON(P_NO,P_NAME,P_AGE,BIRTHDAY) values (8858,‘李明‘,18,date‘2000-8-6‘);

 
 2.3 表添加列
 语法： ALTER TABLE 表名 ADD (字段 数据类型) [约束] ;

 ALTER TABLE PERSON ADD (CITY VARCHAR2(10) DEFAULT ‘深圳‘);
 ALTER TABLE PERSON ADD (COUNTRY VARCHAR2(10) );

2.4 修改列类型、长度
 语法：

 ALTER TABLE TABLE_NAME  MODIFY ( COL DATATYPE [约束] );
 ALTER TABLE PERSON MODIFY ( COUNTRY CHAR(15) );
 
2.5 修改列名
  ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;

1  ALTER TABLE PERSON RENAME COLUMN COUNTRY TO GUOJIA;

 
2.6 删除列
语法：ALTER TABLE  表名  DROP COLUMN 列名 ;

1  ALTER TABLE PERSON DROP GUOJIA;

 
2.7 添加主键与外键
添加主键-定义表时添加
 CREATE TABLE TABLE_NAME(
 column1 type  PRIMARY KEY,
 colume2 type,
 .....
 );

表存在后，再加主键
 ALTER TABLE 表1 ADD CONSTRAINT 主键名 PRIMARY KEY(列); 

 -- 添加外键
 CREATE TABLE TABLE_NAME(
 column1  type  PRIMARY KEY,
 colume2  type,
 colume3  type  REFERENCES 表2(对应列),
 .....
 );

--表存在后，再加外键
 ALTER TABLE 表1 ADD CONSTRAINT 外键名 FOREIGN KEY (表1的列) REFERENCES 表2(表2的列);

1  CREATE TABLE supplier
2  ( supplier_id numeric(10) not null,
3   supplier_name varchar2(50) not null,
4   contact_name varchar2(50),
5   CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
6  );

1 CREATE TABLE products
2 ( product_id numeric(10) not null,
3   supplier_id numeric(10) not null,
4   CONSTRAINT fk_supplier
5     FOREIGN KEY (supplier_id)
6     REFERENCES supplier(supplier_id)
7  );

 删除外键:
 ALTER TABLE table_name DROP CONSTRAINT constraint_name;
 
O级联删除外键:
级联删除是指当主表中的一条记录被删除，那么子表中所关联的记录也相应的自动删除。
CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE CASCADE
);

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n)
   ON DELETE CASCADE;

--禁用外键
 ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

2.8 复制表
 -- 只复制表结构
 CREATE TABLE newTable as select * from oldTable where 1=2;
 -- 复制表结构和数据
 CREATE TABLE newTable as select * from oldTable;
 
 
2.9 约束
 -- 列级约束定义是在定义列的同时定义约束
 -- NOT NULL  非空约束
 -- UNIQUE    唯一约束
 -- PRIMARY KEY    主键约束
 -- FOREIGN KEY    外键约束
 -- CHECK     检查约束
 -- DEFUALT    默认值

1 DROP TABLE ADD_CONSTRAINT;
2   CREATE TABLE ADD_CONSTRAINT(
3       SNO   NUMBER(6) PRIMARY KEY,
4       SNAME VARCHAR2(10) NOT NULL,
5       SEX   CHAR(2) CHECK(SEX IN (‘男‘,‘女‘)),
6       SID   NUMBER(8) UNIQUE,
7       CITY  VARCHAR2(8) DEFAULT(‘深圳‘)
8   );

 1 -- 建表后再添加约束
 2  -- ALTER TABLE table_name MODIFY 列 CONSTRAINT 约束名 约束类型;
 3  -- ALTER TABLE table_name ADD CONSTRAINT 约束名 约束类型(列);
 4  CREATE TABLE ADD_CONSTRAINT_2(
 5       SNO   NUMBER(6) ,
 6       SNAME VARCHAR2(10) ,
 7       SEX   CHAR(2) ,
 8       SID   NUMBER(8) ,
 9       CITY  VARCHAR2(8)
10   );
11  
12  ALTER TABLE ADD_CONSTRAINT_2 ADD CONSTRAINT PK_T1_SNO PRIMARY KEY(SNO);
13  ALTER TABLE ADD_CONSTRAINT_2 MODIFY SNAME CONSTRAINT NN_T1_ID NOT NULL;
14  ALTER TABLE ADD_CONSTRAINT_2 ADD CONSTRAINT CK_T3_SEX CHECK(SEX in (‘男‘,‘女‘));
15  ALTER TABLE ADD_CONSTRAINT_2 ADD CONSTRAINT UN_T1_SID UNIQUE(SID);
16  ALTER TABLE ADD_CONSTRAINT_2 MODIFY CITY  DEFAULT ‘深圳‘;       -- DEFAULT 不能加 CONSTRAINT

 --查看约束
 select constraint_name,table_name,owner from user_constraints where table_name = ‘ADD_CONSTRAINT_2‘;
 

3 函数
3.1 聚合函数

sum()、avg()、count()、max()、min()

1  SELECT  SUM(P_AGE) FROM PERSON; --求和
2  SELECT  AVG(P_AGE) FROM PERSON; --平均
3  SELECT  COUNT(P_AGE) FROM PERSON; --计数
4  SELECT  MAX(P_AGE) FROM PERSON; --最大值
5  SELECT  MIN(P_AGE) FROM PERSON; --最小值

 COUNT (*) 统计所有行个数，包括重复行和空值的行
 COUNT (columname) 统计指定列非空值的行数
 COUNT (DISTINCR columname) 统计指定列中 非重复，非空值得行个数
 聚合函数不能做为 where 里查询条件出现（因为聚合是对所有查询结果的运算？）
 1 聚合函数不能出现在 where 子句中
 2 选择列表中的列、表达式，必须出现在 group by 子句中
 3 聚合函数中可以指定 all 和 distinct
 
 3.2 日期时间函数
 -- TO_DATE(‘日期时间‘,‘显示格式‘) 将日期时间用指定的格式输出

 SELECT TO_DATE(‘2019-6-20 20:36:36‘,‘YYYY-MM-DD HH24:mi:ss‘) FROM DUAL; --年月日 时分秒（DUAL为虚拟表）,HH:mi:ss 为12小时进制
 select TO_CHAR(to_date(‘2019-08-20‘,‘yyyy-mm-dd‘),‘day‘) from DUAL;     -- 某天是星期几
 select TO_DATE(‘2002-08-26‘,‘yyyy-mm-dd‘) from DUAL;
 SELECT SYSDATE FROM DUAL; --系统当前的日期时间

 SELECT TRUNC(SYSDATE,‘month‘) FROM DUAL;
 SELECT TO_CHAR(SYSDATE,‘MONTH‘,‘NLS_DATE_LANGUAGE=AMERICAN‘) FROM DUAL;
 SELECT TO_CHAR(SYSDATE,‘MONTH‘) FROM DUAL;

 1 --ADD_MONTHS(日期, 数字) ，增加月份
 2  SELECT ADD_MONTHS(date‘2019-06-06‘, -1)  FROM DUAL; --减一个月：2019/5/6
 3  SELECT ADD_MONTHS(date‘2019-06-06‘, 3)  FROM DUAL; --2019/9/6
 4  SELECT ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -1) FROM DUAL; -- 当月第一天
 5  
 6  -- MONTHS_BETWEEN 返回两个日期之间相差多少个月
 7  SELECT MONTHS_BETWEEN(sysdate,date‘2019-1-25‘) from dual;  -- 5
 8  
 9  -- LAST_DAY(日期 )  获取当月最后一天
10  SELECT LAST_DAY(date‘2019-06-06‘)  FROM DUAL;  --2019/6/30
11  SELECT ADD_MONTHS(LAST_DAY(date‘2019-06-06‘)+1,-1)  FROM DUAL;  --当月第一天
12  
13  --ROUND 四舍五入日期 
14  select round(sysdate,‘year‘) , round(sysdate,‘month‘) from dual;--年的第一天，月的第一天或下月的第一天
15  TRUNC 截取日期 trunc(sysdate,‘year‘) 或者 trunc(sysdate,‘month‘)

 1 --TRUNC() 用于截取时间或者数值，返回指定的值
 2  SELECT TRUNC(TO_DATE(‘2018-02-01 1:00:00‘,‘YYYY-MM-DD HH:MI:SS‘),‘yyyy‘) FROM DUAL ; --返回当年第一天 2018/1/1
 3  SELECT TRUNC(TO_DATE(‘2018-06-21 1:00:00‘,‘YYYY-MM-DD HH:MI:SS‘),‘mm‘) FROM DUAL ;   --返回当月第一天  2018/6/1
 4  SELECT TRUNC(TO_DATE(‘2018-05-23 1:00:00‘,‘YYYY-MM-DD HH:MI:SS‘),‘DD‘) FROM DUAL ;   --返回当前年月 2018/5/23
 5  SELECT TRUNC(TO_DATE(‘2019-06-20 1:00:00‘,‘YYYY-MM-DD HH:MI:SS‘),‘d‘) FROM DUAL ;    --返回当前星期的第一天(星期日) 2019/6/16
 6  SELECT TRUNC(TO_DATE(‘2018-02-01 18:12:12‘,‘YYYY-MM-DD HH24:MI:SS‘),‘hh‘) FROM DUAL ;--返回当前日期截取到小时,分秒取0
 7  SELECT TRUNC(TO_DATE(‘2018-02-01 1:12:12‘,‘YYYY-MM-DD HH:MI:SS‘),‘mi‘) FROM DUAL ;   --返回当前日期截取到分,秒补0
 8  SELECT TRUNC(SYSDATE),TRUNC(SYSDATE,‘dd‘)FROM DUAL; --当前年月日
 9  SELECT TRUNC(SYSDATE,‘yyyy‘) FROM DUAL;   --当年第一天
10  SELECT TRUNC(SYSDATE,‘mm‘) FROM DUAL ;    --当月第一天
11  SELECT trunc(sysdate,‘d‘) FROM dual ;    --当周第一天
12  SELECT TRUNC(ADD_MONTHS(SYSDATE,-3),‘q‘), TRUNC(SYSDATE,‘q‘), TRUNC(ADD_MONTHS(SYSDATE,3),‘q‘) FROM DUAL; --上季、当季、下季 第一天
13  SELECT trunc(add_months(sysdate,12),‘yyyy‘)-trunc(sysdate,‘yyyy‘) FROM dual; --当年的天数
14  SELECT trunc(add_months(sysdate,12),‘yyyy‘)FROM dual; --明年第一天

1  -- 时间提取  EXTRACT(YEAR/MONTH/DAY FROM DATE)
2  SELECT EXTRACT(DAY FROM LAST_DAY(SYSDATE)) FROM DUAL; --当月天数
3  SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;  --提取年：2019
4  SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; -- 提取月
5  SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;  -- 提取日
6  select EXTRACT(HOUR FROM TO_TIMESTAMP(sysdate,‘DD-MON-YYYY HH24:MI:SS‘)) AS HOUR from dual;  --从时间戳获取小时。MINUTE、SECOND也可

1  --trunc(number,decimals)  number:指需要截取的数字，decimals 截取位数
2  select  trunc(122.555) from  dual t; --默认取整 ：122
3  select  trunc(122.555,2) from  dual t;  -- 122.55
4  select  trunc(122.555,-2) from  dual t;--负数表示从小数点左边开始截取2位并直接变为0 ：100
5  select  trunc(456.555,-3) from  dual t; -- 三位整数直接变为0：0
6  --取当年最后一天、当月第一天和本季度第一天
7  select trunc(add_months(sysdate,12),‘yyyy‘)-1,trunc(sysdate,‘mon‘),trunc(sysdate,‘Q‘) from dual;

 
 --FLOOR()函数：计算两个日期间的天数

 select floor(to_date(‘2019-05-03 20:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)-to_date(‘2018-05-01 12:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) A from dual;

1  -- ROUND(d[,fmt])，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。
2  SELECT ROUND(SYSDATE) FROM DUAL; -- SELECT ROUND(SYSDATE, ‘DDD‘) FROM DUAL; 月中的某一天
3  SELECT ROUND(date‘2019-6-14‘, ‘DAY‘) FROM DUAL;  --以周三为分界，日期对应的周几小于等于分界，返回日期当前周的第一天（周日为第一天），大于返回日期当下周的第一天（周日为第一天）
4  SELECT ROUND(date‘2019-6-15‘, ‘MON‘) FROM DUAL; -- 以当月的 15 号为分界，日期小于等于分界线则为当月第一天，大于则为下月第一天
5  SELECT ROUND(SYSDATE, ‘Q‘) FROM DUAL;
6  SELECT ROUND(DATE‘2019-05-16‘, ‘Q‘) FROM DUAL; --返回相应季度的第一天：2019/7/1 星期一 -- 以 5/15 为分界：小于为2019/4/1 星期一
7  SELECT ROUND(DATE‘2019-12-12‘, ‘Q‘) FROM DUAL; -- 以 每个季度的中间值：2-15/5-15/8-15/11-15为分界，小于则为当季第一天，大于则为下一季第一天
8  SELECT ROUND(SYSDATE, ‘YEAR‘) FROM DUAL; -当年第一天 2019-1-1
9  

3.3 转换函数
 --TO_CHAR(date[,fmt,[,nls_param]]):将日期转按一定格式换成字符类型 ，fmt,nls_param为可选项，fmt指定了要转化的格式，nls_param指定了返回日期所使用的语言

 1 select to_char(sysdate, ‘yyyy‘) 年,  --2019
 2        to_char(sysdate, ‘mm‘) 月,     -- 06
 3        to_char(sysdate, ‘DD‘) 日,     -- 26
 4        to_char(sysdate, ‘HH24‘) 时,    --14
 5        to_char(sysdate, ‘MI‘) 分,
 6        to_char(sysdate, ‘SS‘) 秒,
 7        to_char(sysdate, ‘DAY‘) 天,
 8        to_char(sysdate, ‘Q‘) 第几季度,
 9        to_char(sysdate, ‘W‘) 当月第几周,
10        to_char(sysdate, ‘WW‘) 当年第几周,
11        to_char(sysdate, ‘D‘) 当周第几天,
12        to_char(sysdate, ‘DDD‘) 当年第几天    
13   from dual;

  --TO_DATE(char[fmt[,‘nls_param’]])：将字符类型按一定格式转化为日期类型
 

SELECT TO_DATE(‘2019-6-20 20:36:36‘,‘YYYY-MM-DD HH24:mi:ss‘) FROM DUAL;

  --TO_NUMBER()数字型字符转为数字

SELECT TO_NUMBER(‘123‘) FROM DUAL; --123

 
3.4 分析函数
 -- 分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
常见类型     order by、partition by 可接多个字段
 row_number() over(partition by ... order by ...) 此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的），相同值不并列
 rank() over(partition by ... order by ...)  每组内部排序,跳号排序，相同值并列
 dense_rank() over (partition by ... order by ...)  每组内部排序,连续排序，相同值并列 。
 count() over (partition by ... order by ...)
 max() over (partition by ... order by ...)
 min() over (partition by ... order by ...)
 sum() over (partition by ... order by ...)
 avg() over (partition by ... order by ...)
 first_value() over(partition by ... order by ...)  --取尾记录值
 last_value() over(partition by ... order by ...)    --取尾记录值
 lag(colum,num) over (partition by ... order by ...)   -- 分组排序后，某列向下移动 num 行  
 lead(colum,num) over (partition by ... order by ...)   -- 分组排序后，某列向上移动 num 行 
 
 

SELECT EMPNO,ENAME,DEPTNO,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL) ROW_NUMBER排名,
                           RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) RANK排名,
                           DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL) DENSE_RANK排名
    FROM EMP_DAHAI;
 SELECT DISTINCT DEPTNO, count(1) over (partition by DEPTNO ORDER by DEPTNO) FROM EMP_DAHAI;
 SELECT DEPTNO, max(SAL) over (partition by DEPTNO ORDER by SAL ) FROM EMP; --每组都返回多行
 SELECT DEPTNO, sal,avg(SAL) over (partition by DEPTNO ORDER by SAL) FROM EMP; --每组都返回多行,累积平均
 SELECT DEPTNO,ENAME,HIREDATE, SAL, first_value(hiredate) over(partition by DEPTNO order by hiredate) as 部门首个入职时间,
                last_value(hiredate) over(partition by DEPTNO order by hiredate) as 部门最后入职时间
     FROM EMP_DAHAI;
 SELECT E.*, LAG(E.HIREDATE, 1) OVER (ORDER BY E.HIREDATE) - E.HIREDATE AS  比上一个人早入职的时间,
             LEAD(E.HIREDATE, 1) OVER (ORDER BY E.HIREDATE) - E.HIREDATE AS 比下一个人早入职的时间
   FROM EMP_DAHAI E;
 select  deptno,empno,lag(empno) over (partition by deptno order by ename ) from emp;
 

3.5 字符拼接，截取
 --ASCII(x)  返回字符x的ASCII码。
 SELECT ASCII(‘A‘) FROM DUAL;
 SELECT ASCII(‘大‘) FROM DUAL;

 --拼接
 -- CONCAT(x,y) 连接字符串x和y。
 -- || 拼接
 SELECT CONCAT(CONCAT(‘xiao‘, ‘ming‘), ‘hao‘) as 拼接 FROM DUAL;
 SELECT ‘xiao‘ || ‘ming|| ‘hao‘ as 拼接2 FROM DUAL;
 
 -- 分组后拼接 wm_concat(sre)
 select deptno , wm_concat(ename||‘的工资为‘||sal) from emp group by deptno; --CLARK的工资为2450,KING的工资为5000,MILLER的工资为1300
 select empno , wm_concat(ename||‘的工资为‘||sal) from emp group by empno; --

 --INSTR(x, str [,start] [,n]) 在x中查找str，可以指定从start开始(start为负时，从后面开始找)，第n次出现的位置。字符定位
 SELECT INSTR(‘afdaagdSS‘, ‘s‘) FROM DUAL;
 SELECT INSTR(‘afdaagdSS‘, ‘a‘) FROM DUAL;
 SELECT INSTR(‘afdaagdSS‘, ‘a‘, 2) FROM DUAL;
 SELECT INSTR(‘afdaagdSS‘, ‘a‘, 2, 2) FROM DUAL;

 --LENGTH(x) 返回x的长度。
 SELECT LENGTH(‘afdaagdSS‘) FROM DUAL;

 --LOWER(x)  x转换为小写。
 SELECT LOWER(‘afdaagdSS‘) FROM DUAL;
 -- UPPER(x)  x转换为大写。
 SELECT UPPER(‘afdaagdSS‘) FROM DUAL;
 --INITCAP：将每个单词的首字母大写，其他字母小写
 SELECT INITCAP(‘i love oracle‘) FROM DUAL;

 --LTRIM(x[,trim_str]) 把x的左边截去trim_str字符串，缺省截去空格。
 SELECT LTRIM(‘ afda agdSS ‘) FROM DUAL
 SELECT LTRIM(‘Cafda agdSS ‘, ‘a‘) FROM DUAL; --中间的a不能截去

 --RTRIM(x[,trim_str]) 把x的右边截去trim_str字符串，缺省截去空格。
 --TRIM([trim_str FROM] x) 把x的两边截去trim_str字符串，缺省截去空格。
 SELECT RTRIM(‘Cafda agdSSa‘, ‘a‘) FROM DUAL; --中间的a不能截去
 SELECT TRIM(‘a‘ FROM ‘abda agdSSa‘) FROM DUAL; -- ‘a‘必须为单个字符

 --REPLACE(x,old,new)  在x中查找old，并替换为new。
 SELECT REPLACE(‘abda agdSSa‘, ‘a‘, ‘123‘) FROM DUAL;

 --SUBSTR(x,start[,length])  返回x的子串，从staart处开始，截取length个字符，缺省length，默认到结尾。
 SELECT SUBSTR(‘ADde#elk123dfdd#12#000dsfo‘, 3, 2) FROM DUAL;
 --1.截取 ‘ADde#elk123dfdd#12#000dsfo‘ 第二个#和第三个#号中间的字符
SELECT SUBSTR(‘ADde#elk123dfdd#12#000dsfo‘,
              INSTR(‘ADde#elk123dfdd#12#000dsfo‘, ‘#‘, 1, 2) + 1,
              INSTR(‘ADde#elk123dfdd#12#000dsfo‘, ‘#‘, 1, 3) - INSTR(‘ADde#elk123dfdd#12#000dsfo‘, ‘#‘, 1, 2) - 1)
  FROM DUAL;
 
 
3.6 数值操作函数
 /*
 +-*/
 >、=、<、>=、 <>
 
 ABS(x)  x绝对值  ABS(-3)=3
 MOD(x,y)  x除以y的余数 MOD(8,3)=2
 POWER(x,y)  x的y次幂 POWER(2,3)=8
 ROUND(x[,y])  x在第y位四舍五入 ROUND(3.456,2)=3.46
 floor(x) 向下取整
 */
 
 SELECT ROUND(3.456) FROM DUAL;
 SELECT ROUND(37.456, -1) FROM DUAL;    -- 40 ：为 -1 时，从小数点左边的第1为四舍五入
 SELECT ROUND(314.456, -2) FROM DUAL;    -- 300 ：为 -2 时，从小数点左边的第2 为四舍五入，第2为后面的数全为0
 select floor(2.3) from dual;    -- 2
 select floor(2.7) from dual;    -- 2
 
 
 -- TRUNC(x[,y])  x在小数点第y位截断 TRUNC(3.456,2)=3.45, y为负数时，从小数点右边开始，直接截取为0,小数部分也不要
 SELECT TRUNC(37.456, 2) FROM DUAL;
 select trunc(345.67,-2) from dual;  -- 300
 
 -- 符号函数 sign(x) x<0 则返回-1，x>1 返回1，x=0返回0
 --工资高于4000，加薪15%，低于4000，加20%
 select sign(-2),sign(0),sign(3) from dual;
 select decode(sign(sal - 4000),1, sal*1.15,-1, sal*1.2, sal*1.2),sal from emp;
 
 
3.7 其他函数
 -- NVL（列，默认值）  如果列值为null，则使用默认值表示
 SELECT E.*,NVL(COMM, 0) FROM EMP E;

 --NVL2（列，返回值1，返回值2） 如果列值不为null，返回结果1；如果列值为null，返回结果2
 select * from emp;
 SELECT E.*,NVL2(COMM, 500, 1000) FROM EMP E;
 
 -- DECODE（列|值，判断值1，返回值1，判断值2，返回值2，...，默认值）多值判断
 SELECT E.ENAME, E.JOB, DECODE(E.JOB, ‘CLERK‘,‘业务员‘, ‘MANAGER‘, ‘经理‘, ‘ANALYST‘,
          ‘分析员‘, ‘PRESIDENT‘, ‘总裁‘, ‘其他‘)  职位 -- 没匹配上就为 默认值：其他
 FROM EMP E;
 
 -- LAG(列，数) 某列向下移动 N 行
 SELECT E.*,
        LAG(E.sal, 1) OVER (ORDER BY E.HIREDATE) AS a_sal
   FROM EMP_DAHAI E;
   
 -- LEAD(列，数) 某列向上移动 N 行
 -- 查询前一个人比下一个人早入职的时间
   SELECT E.*,
        LEAD(E.HIREDATE, 1) OVER (ORDER BY E.HIREDATE) - E.HIREDATE AS 比下一个人早入职的时间
   FROM EMP_DAHAI E;

--填充函数
   LPAD / RPAD 左 / 右 填充
   如，LPAD (‘function‘, 15 , ‘=‘) 返回 ‘=======function‘
 
 --CASE WHEN 条件1 THEN 返回值1 [WHEN 条件2 THEN 返回值2 ...] ELSE 默认值 END    
 --所有数据库都支持  用于实现多条件判断，如果都不满足条件，则返回默认值
 SELECT E.ENAME,
       E.JOB,
       CASE E.JOB
         WHEN ‘CLERK‘ THEN
          ‘业务员‘
         WHEN ‘SALESMAN‘ THEN
          ‘销售员‘
         WHEN ‘MANAGER‘ THEN
          ‘经理‘
         WHEN ‘ANALYST‘ THEN
          ‘分析员‘
         WHEN ‘PRESIDENT‘ THEN
          ‘总裁‘
       END  AS 职位
  FROM EMP E;
 
 SELECT E.ENAME,
       E.SAL,
       (CASE WHEN E.SAL > 2000 THEN ‘高薪‘
             WHEN E.SAL < 1000 THEN ‘低薪‘
             ELSE ‘中薪‘
       END) AS SAL_LEV
  FROM EMP E;
 
 -- EXISTS（子查询） 用于判断子查询是否有数据返回，如果有则成立，否则不成立。
 SELECT D.*
   FROM DEPT D
 WHERE NOT EXISTS (SELECT 1 FROM EMP E WHERE E.DEPTNO  = 10); --查询与子查询没有关联
 
 SELECT *
   FROM DEPT D
 WHERE NOT EXISTS (SELECT 1 FROM EMP E WHERE  D.DEPTNO  = 20);  --查询与子查询有关联
 

3.8 伪列 rowid 、rownum
 --伪列 rowid :一种数据类型，它使用基于64为编码的18个字符来唯一标识一条记录物理位置的一个ID
 --rowid:用于去重数据 注意：重复数据只是针对 group by 后的字段，不是整行地数据比较
 DELETE FROM emp WHERE ROWID NOT IN( SELECT MIN(ROWID) FROM emp GROUP BY deptno);
 
 --rownum ROWNUM伪列是Oracle首先进行查询获取到结果集之后在加上去的一个伪列，这个伪列对符合条件的结果添加一个从1开始的序列号
  SELECT ROWNUM,empno,ename,job FROM emp WHERE ROWNUM < 6;
 
 --子查询部分固定表里面数据的记录编号，外层查询过滤子查询里面固定的记录编号就可以实现Oracle 分页
 -- ROWNUM 用于分页查询，查询不是从1 开始时 需要用子查询，rownum加别名，再使用别名
 SELECT * FROM (SELECT ROWNUM nums,emp.* FROM emp) WHERE nums > 5 AND nums <=10;
 
 CREATE TABLE DELETC_SAME( SNAME VARCHAR2(5),JOB VARCHAR2(2) );
 INSERT INTO DELETC_SAME VALUES (‘小明‘,‘A‘);
 INSERT INTO DELETC_SAME VALUES(‘小王‘,‘B‘);
 INSERT INTO DELETC_SAME VALUES(‘小红‘,‘C‘);
 INSERT INTO DELETC_SAME VALUES(‘小明‘,‘A‘);
 INSERT INTO DELETC_SAME VALUES(‘小王‘,‘B‘);
 INSERT INTO DELETC_SAME VALUES(‘小红‘,‘B‘);
 SELECT * from DELETC_SAME;
 
 select * from DELETC_SAME where rowid not in (select min(rowid)from DELETC_SAME group by JOB ); -- 重复的记录
 select min(rowid)from DELETC_SAME group by JOB ;
 select job,min(rowid), max(rowid) from DELETC_SAME group by JOB ;
 
 
3.9 集合运算
 /*
 联合运算（union） [去重 不排序] 、完全联合运算（union all）[不去重 升序]
 相交运算（intersect） [不去重 升序]：相交运算返回多个查询中所有相同的行。
 相减运算（minus）[不去重 升序]：相减运算返回在第一个查询中而不在第二个查询中的行。
 (第一个 SELECT 语句减第二个 SELECT 语句)。
 1、在两个select列表中的表达式必须在数目和数据类型上相匹配。
 2、可以用圆括号改变执行的顺序。
 */

3.10
 rollup 用于生成横向统计信息
Select deptno, job, Avg(sal), Max(sal) From emp
Group By rollup(deptno,job);

cube 用于生产纵向统计信息
Select deptno, job, Avg(sal), Max(sal) From emp
Group By Cube(deptno,job)
 

Oracle基础

原文：https://www.cnblogs.com/dahaibingzi/p/12389273.html