ORACLE-sql语句总结

一、同义词

1.创建同义词

create （or replace）（public） synonym name (同义词名字) for target(目标名称);

示例：A用户data表，要给B用户使用，再赋予B用户权限后，B用户使用data表需要加前缀：A.data

需求：B用户不加前缀，直接使用data表，则设置同义词如下：

create synonym data for A.data;

注意：表、序列等都可以设置同义词；

一个目标名称，同义词可以有多个；

public是设置该同义词为公有，不然则为当前用户私有；

2.查询同义词

查询当前用户拥有的同义词：select * from user_synonyms；

3.删除同义词

drop （public） synonyms 同义词名字；

二、修改字段

1. 修改表字段长度

alter table 表名 modify 字段列名 数据类型(大小)

三、赋权

1. 赋权语句

grant 具体的权限(select) on 权限对象 to 赋权对象

收回权限

revoke 具体的权限(select) on 权限对象 to 收权对象

四、序列

介绍：序列是一个计数器，并不会与特定的表关联，通过创建oracle序列和触发器实现表的主键自增。序列一般用来填充主键和计数。

1. 创建序列语法格式：

CREATE SEQUENCE 序列名

INCREMENT BY NUM

START WITH NUM

MAXVALUE/MINVALUE NUM|NOMAXVALUE

CYCLE|NOCYCLE

CACHE NUM|NOCACHE

释义：

1.INCREMENT BY用于定义序列的步长；省略，则默认为1，负值，则代表Oracle序列的值是按照此步长递减的。

2.START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

3.MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

4.MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是10的26次方;对于递增序列，最小值是1。

5.CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;最小值为1。对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

五、dual表

简介：dual表是一个虚拟表，用来构建select的语法规则，Oracle保证dual永远只有一条数据；

示例：

查看当前用户

select user from dual

查看序列的下一个值：

select 序列名.nextval from dual;

六、函数

1、NULL值函数

oracle数据库中如果某字段没有值，Oracle是没有设置默认值的，会给该字段NULL；那么在用到数值相加时

注意：

EVER+NULL=NULL

所以在处理这里数据时，我们会使用对NULL进行处理的函数

NVL(expr1，expr2)

expr1判断是否为空的字段

为空的情况下，取expr2的值

NVL2（expr1，expr2，expr3）

expr1判断是否为空的字段

如果expr1参数不为空，返回expr2参数的值；否则，返回expr3参数的值，类似于三目运算符

2、add_months函数

作用：对日期类型数据进行月份加减操作

--data是需要进行月份更改的日期，int是加减的月份，负数为减
add_months(date,int);

3、to_date函数

作用：将日期格式的字符串转换成指定格式的日期类型数据

to_date('日期字符串','转换后的日期格式')
--示例
to_date('20230402','YYYY-MM-DD')
--结果：2023-04-02日期格式存储在数据库中

七、查询

1、递归查询

SELECT * FROM TABLE_NAME START WITH 起始条件 AND CONNECT BY PRIOR 递归条件

示例：向下递归

SELECT * FROM EVENT_LOG START WITH ID='68237' CONNECT BY PRIOR PARENT_ID=ID

释义：

1. START WITH 起始条件 是查询第一条数据的条件，有了第一条才能递归查询后续的数据，并且该条数据也在查询的结果中

2. 递归条件中，前面的参数指的是当前数据的值，后面的条件是指需要查询的数据满足的条件；即PARENT_ID是68237这条数据的parent_id值，接着查出id等于该条数据parent_id的数据，再以次递归下去

2、分页查询

Oracle 除了建表时设计的各个字段，其实还有两个字段，分别是 ROWID(行标示符)和 ROWNUM(行号)；它们只在数据库内部使用，所以也通常称它们为伪列。可以通过指定查询伪列的值来查看；

select rowid,rownum,tab.* from table tab;

Oracle 数据库内部使用ROWID来存储行的物理位置;ROWNUM表示的该行的行号;

错误使用：

select * from table where rownum>=3 rownum <= 4;//没有查出任何结果

原因：查询表数据时，第一行的行号为1，所以不符合rownum>=3；则会将第一条数据去除掉，则原来的第二条变为第一条，以此类推，最后没有值

处理办法：

将需要查询出来的数据添加上rownum字段，组成一张新的表，再对新的表进行分页查询

示例1

select * (select rownum num,tab* from table tab) where num>=3 num <= 4;

使用终止筛选子结果，提高查询效率

select * (select rownum num,tab* from table tab where rownum <= 4) where num>=3 ;

3、差集查询

1. NOT EXISTS 不在此范围

有两种user1、user2表，查询user1存在的名字而user2不存在名字的数据

select * from user1 a where not exists (select * from user2 b where a.name=b.name) 

注意：比较的条件name列不能有空值，不然查询出的就是null数据，且数据类型要一致

两张表的表组成可以不一致，比如，user1有dad字段，而user2没有，不影响

2. NOT IN 不在此范围中

我们使用NOT IN可以过滤掉比较值有null的情况，即这个时候比较值name有为null的情况，也可以正常查询

select * from user1 a where a.name not in (select b.name from user2 b where a.name=b.name)

4、左右连接查询

以一张表为前提，查询结果为第一张表的数据加上第二张表中满足条件的数据，没有满足条件的项，会在第二张表的字段下显示为null；

左连查询，则是以左侧的表为基础，左边全显示，右表显示满足条件的数据，结果包含两张表的字段

--语法：
left join/right join... on 联查条件
示例：
select * from A a left join B b on a.id = b.id;
结果：
        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 A2                  1 AA
         1 A1                  1 BB
         2 B1                  2 CC
         4 D1
         3 C1

八、约束系统表

user_constraints

描述的是当前用户所拥有的所有约束关系；CONSTRAINTS_TYPE表述约束的类型；P代表主键约束，C代表非空约束，U代表唯一约束

user_cons_columns

描述的是用户当前拥有的约束字段是那些；

all_constraints:当前用户权限下的所有能够查看的约束

dba_constrains:数据库所有约束

九、数据用户被锁

--查询数据用户名状态
select * from dba_users where username='用户名';
--LOCKED锁;OPEN开放;修改状态即可，立即生效