一、基础信息

端口：1521

基础信息查看

--查看orale实例名
select name from v$database;
--查看oracle的ip地址
select sys_context('userenv','ip_address')from dual
--收回权限
revoke all on 表名 from 用户名
--将有序分区中的行分发到指定数目的组中，各个组有编号，编号从1开始，就像我们说的’分区’一样 ，分为几个区，一个区会有多少个
ntile(分区数量)over(patition by 字段 order by 字段)
查询当前时间
select sysdate from dual;

二、基础操作

1. 表操作

--重命名
rename table 新表名称 to 旧表名称;
--添加注释
comment on table STUINFO is '学生信息表';

---复制结构，不要数据 #变相的创建新空表
create table新表名称 as select 字段1,字段2 from旧表名称#变相的创建新表+插入数据
create table新表名称 as select * from旧表名称 where 1=2; 

查询当前用户所有的表名
select table_name from user_tables

查看表的结构
desc 表名

1.1分区表

• 用途：用于单表数据量超5000万
• 优点：提高性能，利于维护、查数
• 注意：

1. 如果是range分区，索引也可以建立分区索引，相对可以降低开销；如果list分区可以建立local索引，流程为先建分区，再跑数，再创建local索引。
2. 数据重跑时，如果表带有主键以及索引，总数据量过亿，在删分区时可能会导致索引碎片过大无法删除，这种表不建议重跑，必须重跑时建议先备份原表，新建一个表将数据插入，再建立主键和索引会比truncate 分区再重构索引要快很多。 ```sql —创建局部前缀索引；分区键（id）作为索引定义的第一列 create index local_prefixed_index on local_index_example (id, name) local;

—创建局部非前缀索引；分区键未作为索引定义的第一列 create index local_nonprefixed_index on local_index_example (name, id) local;

—查看有几个分区 select * from user_part_tables where table_name=’’;

—删除list分区 alter table 表面 drop partition 分区名;

<a name="KT5Ai"></a>
## 2. 列操作
```sql
alter table 表名称 add(namevarchar2(100),code varchar(20));
alter table 表名称 drop (name,code) ；
--修改字符长度
alter table 表名 modify(字段 类型);

--列注释
comment on column STUINFO.stuid is '学号';
comment on column STUINFO.stuname is '学生姓名';

--列重命名
alter table 表名 rename column 原列名 to 新列名;

2.1虚拟列

• 用途：用于上游推送的数据，某个字段需要稍作逻辑加工
• 优点：提高执行效率
• 注意：不能写在insert into的列中，无法update ```sql —创建虚拟列 create table 表名( 列名 类型, 列名 类型, 虚拟列名 类型 generated always as(表达式) virtual) partition by range/list (虚拟列名)( partition 分区名 value less than (值) / partition 分区名 values (值))

—添加虚拟列 alter table 表名 add 虚拟列名 类型 generated always as(表达式) virtual ;

—修改虚拟列 alter table 表名 modify 虚拟列名 as(表达式);

<a name="PSj4M"></a>
### 2.2行列转换

- 方法1：Oracle 10g以前用lead/lag 或者 decode 
- 方法2：Oracle 10g 以后用pivot 或者unpivot 
```sql
--行列转换
select * from sc t
语法：
select * from 表名 pivot (聚合函数(要求的值)for 想转的列 in(列的个数));

select * from sc pivot (max(score) for cno in ('c001' q1,'c002' as "2",'c003' as "3"));

select * from (select * from sc pivot (max(score) for cno in ('c001' q1,'c002' as "t2",'c003' as "s3")))
unpivot (score for cno in("q1","t2","s3"))；

--方法1
--得到按年汇总，季度为列的一个金额
select * from s

--列转行
--（1）用分析函数lead/lag
select s.y, s.amt q1, s.ld1 q2, s.ld2 q3, s.ld3 q4
  from (select s.*,
              -- lead(s.amt, 0) over(partition by s.y order by s.q) ld0, --得到想要的排序的列
               lead(s.amt, 1) over(partition by s.y order by s.q) ld1, 
               lead(s.amt, 2) over(partition by s.y order by s.q) ld2,
               lead(s.amt, 3) over(partition by s.y order by s.q) ld3
          from s) s
 where s.q = 1 --已order by排序的第一个作为筛选条件

--（2）用decode
 select s.y,
       sum(decode(s.q, 1, amt, null)) q1,
       sum(decode(s.q, 2, amt, null)) q2,
       sum(decode(s.q, 3, amt, null)) q3,
       sum(decode(s.q, 4, amt, null)) q4
  from s
 group by s.y

 --（3）用case when
 select s.y,
       sum(case when s.q = '1' then amt else null end) q1,
       sum(case when s.q = '2' then amt else null end) q2,
       sum(case when s.q = '3' then amt else null end) q3,
       sum(case when s.q = '4' then amt else null end) q4
  from s
 group by s.y

 --（3）部分关联
 select s.y, s.amt q1, a.amt q2, b.amt q3, c.amt q4
   from s,
        (select * from s where s.q = 2) a,
        (select * from s where s.q = 3) b,
        (select * from s where s.q = 4) c
  where s.y = a.y
    and s.y = b.y
    and s.y = c.y
    and s.q=1


--行转列
with a as
 (select s.y,
         sum(decode(s.q, 1, amt, null)) q1,
         sum(decode(s.q, 2, amt, null)) q2,
         sum(decode(s.q, 3, amt, null)) q3,
         sum(decode(s.q, 4, amt, null)) q4
    from s
   group by s.y)    --将结果集命名为a，把a行转列
select a.y, 1, a.q1
  from a
union all
select a.y, 2, a.q2
  from a
union all
select a.y, 3, a.q3
  from a
union all
select a.y, 4, a.q4 from a

------------------------方法2:行列转换-----------------------------
--行转列语法：
SELECT * FROM 表名 PIVOT (聚合函数(第三列名)FOR 第二列名 IN());

SELECT * FROM SC PIVOT (MAX(SCORE) FOR CNO IN ('c001' Q1,'c002' AS "2",'c003' AS "3"));

--列转行语法
SELECT * FROM 
(
SELECT * FROM SC PIVOT (MAX(SCORE) FOR CNO IN ('c001' Q1,'c002' AS "T2",'c003' AS "S3"))
)
UNPIVOT (SCORE FOR CNO IN("Q1","T2","S3"))

3. 添加约束（add constraint）

--添加主键约束（将stuNo作为主键）
alter table stuInfo add constraintPK_stuNoprimarykey (stuNo)

--添加外键约束 (主表stuInfo和从表stuMarks建立关系，关联字段stuNo)
alter table stuInfo add constraintFK_stuNoforeignkey(stuNo) references stuMarks(stuNo)

--添加唯一约束（身份证号唯一）
alter table stuInfo add constraintUQ_stuIDunique(stuID)

--添加默认约束（如果地址不填 默认为“地址不详”）
alter table stuInfo add constraintDF_stuAddressdefault (‘地址不详’) for stuAddress

--添加检查约束 （对年龄加以限定 15-40岁之间）
alter table stuInfo add constraintCK_stuAgecheck(stuAge between 15 and 40)

4. 批量插入

采用insert into select from 语句来一次性插入一个集合,这种主要依据于要插入的数据源已经存储于数据库对象中,或者利用dual虚表来构造数据，经过加工后写入一个集合。insert into test (xh,mx) select ‘123’,’测试’ from dual;

--两个表存在字段一样，复制数据
insert into  新表 select * from  旧表;     <=>    select * into 新表 from 旧表;

--两个表存在字段一样，只复制表结构，不复制数据  
insert into 新表 select * from 旧表 where 1=2;<=>select * into 新表 from 旧表 where 1=2;

--新表只有旧表的部分字段，复制部分字段数据 
insert into  新表 (field1,field2,.....) select field1,field2,field3 from  旧表

5.查询

5.1 求并集-union

:::info

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• Union all：对两个结果集进行并集操作，包括重复行，不进行排序；
• Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

• Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。 :::

  6. 游标

• 概念：指向结果集的一个指针，当成一个结果集来使用

• 优点：效率高
• 缺点：内存消耗大 ```sql declare —类型定义 cursor c_job is select empno, ename, job, sal from emp where job = ‘MANAGER’; —定义一个游标变量 c_row c_row c_job%rowtype; begin open c_job; loop —提取一行数据到c_row fetch c_job

  into c_row;
  

  —判读是否提取到值，没取到值就退出 —取到值c_job%notfound 是false —取不到值c_job%notfound 是true exit when c_job%notfound; dbms_output.put_line(c_row.empno || ‘-‘ || c_row.ename || ‘-‘ || c_row.job || ‘-‘ || c_row.sal); end loop; —关闭游标 close c_job; end; ————————————————游标————————————
  —定义： DECLARE

    CURSOR C_游标名称
       IS
       SELECT 结果集 FROM 表;
  

—类似用法：

 WITH X AS (SELECT * FROM EMP)  --X就是指向EMP表的一个结果集

————循环会结合游标对批量数据进行操作—————- —语法：

DECLARE —声明 CURSOR 游标名[(参数1 数据类型[,参数2 数据类型…])] IS SELECT 语句;

—例1：

游标的数据 我们通过 循环 取值 ,从而实现 批量操作 。 DECLARE
CURSOR C_EMP — 定义游标 C_EMP IS
SELECT * FROM EMP ; — 指向 EMP 表的结果集 BEGIN
FOR X IN C_EMP LOOP — 通过 FOR 循环 让 变量 X 每次从 游标的结果集中 取 1行数据 DBMS_OUTPUT.PUT_LINE(X.DEPTNO||’ ‘||X.EMPNO || ‘ ‘|| X.ENAME ||’ ‘||X.SAL); — 这里取值的方法 类似之前V_EMP.EMPNO END LOOP ; END ;

—例2：

— 接受一个部门的编号 ，打印出该部门的员工姓名 定义不带参数的游标 ，使用变量 做条件 DECLARE V_DEPTNO NUMBER(2) :=&来个部门编号 ;

CURSOR C_EMP_X IS SELECT ENAME FROM EMP WHERE DEPTNO = V_DEPTNO ; BEGIN
DBMS_OUTPUT.PUT_LINE(V_DEPTNO||’的员工如下：’) ;

FOR X IN C_EMP_X LOOP — 游标结果集有 N 行，那么FOR 循环就会循环 N 次 DBMS_OUTPUT.PUT_LINE(‘ ‘||X.ENAME); END LOOP ;

END ;

——定义一个 带参数游标 使用 参数做条件 DECLARE CURSOR C_EMP_X(P_DEPTNO NUMBER ) — 参数 不允许加长度 ，而变量需要加长度 IS SELECT ENAME FROM EMP WHERE DEPTNO = P_DEPTNO ; — 将参数值 做条件 BEGIN FOR X IN C_EMP_X(&来个部门编号) LOOP DBMS_OUTPUT.PUT_LINE(‘ ‘||X.ENAME); END LOOP; END ;

—例3： — 接受一个部门编号 ，打印出该员工所在部门 最高工资和最低工资的员工信息 ， 显示 部门编号 ，姓名 ，工资 —- SQL
SELECT A.DEPTNO ,A.ENAME ,A.SAL FROM ( SELECT E. , MAX(E.SAL)OVER() MAX_SAL , MIN(E.SAL)OVER() MIN_SAL FROM EMP E WHERE E.DEPTNO = /20 */V_DEPTNO) A WHERE A.SAL = A.MAX_SAL OR A.SAL = A.MIN_SAL ;

—————— PLSQL 实现 — 方法一 ，使用 不带参数的游标实现 DECLARE V_DEPTNO NUMBER(2) :=&部门编号;

CURSOR C_EMP — 定义游标 C_EMP IS SELECT A.DEPTNO, A.ENAME, A.SAL FROM (SELECT E., MAX(E.SAL) OVER() MAX_SAL, MIN(E.SAL) OVER() MIN_SAL FROM EMP E WHERE E.DEPTNO = V_DEPTNO /20 */ ) A WHERE A.SAL = A.MAX_SAL OR A.SAL = A.MIN_SAL; — 指向游标对应的结果集 BEGIN FOR X IN C_EMP LOOP — 通过 FOR 循环 让 变量 X 每次从 游标的结果集中 取 1行数据 DBMS_OUTPUT.PUT_LINE(X.DEPTNO || ‘ ‘ || X.ENAME || ‘ ‘ || X.SAL); — 这里取值的方法 类似之前V_EMP.EMPNO END LOOP; END;

— 方法二 使用带参数的游标实现 DECLARE CURSOR C_EMP(P_DEPTNO NUMBER) — 定义游标 C_EMP IS — 指向游标对应的结果集

— 例 SELECT A.DEPTNO, A.ENAME, A.SAL FROM (SELECT E., MAX(E.SAL) OVER() MAX_SAL, MIN(E.SAL) OVER() MIN_SAL FROM EMP E WHERE E.DEPTNO = P_DEPTNO /20 */ ) A WHERE A.SAL = A.MAX_SAL OR A.SAL = A.MIN_SAL;

BEGIN FOR X IN C_EMP(&部门) LOOP — 通过 FOR 循环 让 变量 X 每次从 游标的结果集中 取 1行数据 DBMS_OUTPUT.PUT_LINE(X.DEPTNO || ‘ ‘ || X.ENAME || ‘ ‘ || X.SAL); — 这里取值的方法 类似之前V_EMP.EMPNO END LOOP; END;

<a name="tWqLP"></a>
## 7.其他操作
```sql
--细节
1.多表关联时，如果过滤条件为 != 时，会将null值给过滤掉，需要or 存在空值字段

2.not in 子查询里面如果有空值，需要在子查询里面加上 where 字段 is not null

8.正则表达式

/*正则表达式
regexp_like(字段,正则)
如：regexp_like(‘aaa,bbb,ccc,ddd,eee’,’[^,])
返回结果: ‘ccc’，即“aaa,bbb,ccc,ddd,eee”被”,”截取后的第三个子字符串。
特殊
[0-9]
[a-z]
[A-Z]
[[:alpha:]]任意字母字符,不分大小写    
[[:alnum:]]任意数字和字符,不分大小写    
[[:blank:]]空格或制表符    
[[:digit:]]0~9的数字    
[[:lower:]]小写字母    
[[:upper:]]大写字母    
[[:print:]]任意可打印字符    
[[:punct:]]任意标点符号    
[[:space:]]任意空白字符;空格,制表符,nl,ff,vt和cr
**/
例：
select * from 表名 where regexp_like (字段,'正则');

9.处理过程

--oracle三种循环的写法
for i in 下限...上限(条件判断语句) --in 里面一般用游标指定一个结果集
loop
    循环体
end loop;

while 表达式 loop
    循环体
end loop

loop
    循环体
    exit when 表达式
end loop

--递归
--循环查数
select * from dept e
start with  deptno=40  --从哪开始查
connect by  e.deptno = prior e.deptno-10 --查完后返回自己减去10再继续查，直到查出数据

10.hints提示

--常用HINTS

/*+ PARALLEL(表名1,并行数)[(表名2,并行数)……] */ --指定开启多少个并行|并发（一般为2、4、8……32）
/*+ INDEX(表名,索引名) */ --指定索引
/*+ FULL(表名) */ --指定全表扫描
/*+ USE_NL(表名1，表名2) */ --指定用NESTED LOOP连接
/*+ USE_HASH(表名1，表名2) */ --指定用HASH连接
/*+ USE_MERGE(表名1，表名2) */ --指定用SORT MERGE JOIN
/*+ LEADING(表名1，表名2) */ --指定表1作为驱动表
/*+ APPEND NOLOGGING*/ --数据直接插入到高水位上面(与insert连用)直接往后面插，无视前面的空位置

11.动态SQL

1. 适用范围

   在plsql程序开发中，可以使用dml语句和事务控制语句，但是还有很多语句（比如ddl语句：创建
   修改、删除、清空）不能直接在plsql中执行，需要使用动态sql

2. 动态sql语法

   execute immediate 动态语句字符串[into 变量列表][using 参数列表]

3. 解析

   动态语句字符串 指ddl和不确定的dml(即带参数的dml);
   如果动态语句是select语句，可以把查询的结果保存到into后面的变量中;
   如果动态语句中存在参数，using 为语句中的参数传值;
   动态sql中的参数格式是[:参数名]，参数在运行时需要使用using传值。 ```sql —举例1 直接在 动态语句字符串 中使用清空表格： execute immediate ‘TRUNCATE TABLE 表名’;

select * from EMP —举例2 通过变量把动态sql里 select 到的值 into到变量中 再通过dbms打印出来 declare sqlstr varchar2(100); sename varchar2(20); sempno number; begin sempno := 7788;

—select ename into sename from emp where empno=sempno;

sqlstr := ‘SELECT ENAME FROM EMP WHERE EMPNO=:EMPNO’; —-动态sql内的变量（：加变量名字）

execute immediate sqlstr into sename —输出值 using sempno; —给[:后面的参数进行传值]

dbms_output.put_line(sename);

end; ```