ORACLE常用语句相关知识记录

P.L.C.

已于 2022-04-12 17:29:46 修改

阅读量841

点赞数

分类专栏： oracle 分页查询文章标签： oracle命令

于 2019-11-08 16:43:48 首次发布

本文链接：https://blog.csdn.net/woaiclh13/article/details/102975046

版权

oracle 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

分页查询

1 篇文章 0 订阅

订阅专栏

1.查看数据库名：

select name from v$database;

2.查看数据库实例名：

select instance_name from v$instance;

3.修改表字段类型

alter table tablename
modify (columnName varchar2(10) );

4.修改表名

alter table tb rename to tb2;

5.函数：NVL(expr1,expr2);
如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

nvl( column1, 0 );  如果column1为null,则转化为0.

6.将字符型转化为整形函数：to_number(exper1)

7.将nvl和to_number结合，则可以将字符转化为整形，为NULL时设置为0

8.增删改查语句

insert into tablename(col1,col2,col3) values( val1,val2,val3); 
# ---注意点：values

update tablename set col1='val2' where col2=val2;
-注意点：没有from,sql语句的字符只能使用单引号不是双引号
update可以多列一起更新：update table1 set (col1,col2)=(select col1, col2 from table2 where col3='xxx') 
#使用table2的查询出的结果，来更新table1中的2列，必须是table2查询出的仅1行

select * from tablename where col1='val2'; 
select * from table1 t1 where t1.col1 in (select col2 from table2 t2 where t2.col1='xxx')
#查询表1中col1的值等于table2中col1='xxx'的记录的col2的值的所有列

delete from tablename where col1=val2;           
---注意点：有from 。 val2为number型就不需要引号

总结：只有delete和select才有from;update没有from,insert时，是values不是value.所有字符必须使用单引号，不是双引号。

9.oracle的通配符
% 零或者多个字符
_ 单一任何字符（下划线）
\ 特殊字符
select * from table where col_date like '201910%' 使用关键字like且col_date的值为201910开头的记录，%为通配符
查询一个表中的某列值为另一个列的中的一部分，使用通配符：

select * from table1 where table1.col1 like '%'||table2.col2||'%' 
#查询出所在行的列2的值包含在列1中

10.group by 用于分组
当需要查询列和响应的数据库函数时（比如count(),sum()等），可以使用group by

select txn_date, count(*) from table1;(不使用group by 会报错，不能执行)
select txn_date, count(*) from table1 group by txn_date;
#(执行成功，根据txn_date进行分组统计，第一列显示txn_date,第二列则是对应的count（*）分组后的统计数)

使用group by 时，select 后的所有列名，都必须在group by后出现。
如select col1,col2, count(*) from table1 group by col1, col2;(select后出现了单独的col1和col2，所以group by 后必须跟上col1,col2,2个都需要加上)该处表示会根据col1和col2会进行分组。如果后面是一个字段，就把一个字段当成一组，如果是两个字段就把两个字段当成一组

11.HAVING
HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
SQL语言中设定集合函数的查询条件时使用HAVING从句而不是WHERE从句。通常情况下，HAVING从句被放置在SQL命令的结尾处。
当查询列作为查询判断条件时，存在两种写法：

select col1, count(*) from table1 group by col1 having col1 = 'xxxx'

上述按理还有另一种写法：

select col1,count(*) from table1 where col1='xxxx' group by col1;

当聚合函数作为查询判断条件时，只能使用having（不能像上面那样使用where语句）

select col1, count(*) from table1 group by col1 having count(*)> 2;

12.Oracle数据库分页的三种方法
– 不能对ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果
– 所以直接用只能从1开始
– rownum >10 没有记录，因为第一条不满足去掉的话，第二条的rownum又成了1，所以永远没有满足条件的记录。

select * from student where rownum>=1;

–如果想要用rownum不从1开始，需按下面方法使用

select a1.* from (select student.*,rownum rn from student) a1 where rn >5;（错误SQL）

–分页查询一(常规方式)

select * from (select a1.*,rownum rn from (select * from student) a1 where rownum <=5) where rn>=2;
注意：最里层的查询SQL，需要加上唯一值的字段，进行order by 排序，否则，分页查询出的数据可能有重复情况。且最里层的查询SQL不加rownum，是先将最里层的查询结果进行order by 排序，然后将排序后的结果进行rownum编号，然后取对应的rownum序号的值,这次是分页查询的正确流程

–分页查询二 (效率最高)

select a1.* from (select student.*,rownum rn from student where rownum <=5) a1 where rn >=3;

–分页查询三

select a1.* from (select student.*,rownum rn from student) a1 where rn between 3 and 5;

分页查询补充内容：分页查询一定要使用order by,且排序条件必须是唯一的,这样分页查询出的内容才不重复
注意：oracle 的select 不加查询条件，每次查询出的结果，看似都是一样，其实有时候也是不一样的，也不清楚是否是根据物理存储地址rowid来的，（网上有说是根据入库的时候数据的物理地址rowid来的，然后update数据后，如果该区域存储不下，则rowid会改变，然后delete数据，新的数据又会存放到原地址，所以rowid会存在变化的情况），
在清除数据库缓存（sysdba进入数据库：alter system flush buffer_cache;），重启数据库后，查询结果可能就不一样，所以如果使用rownum进行排序时，可能出现重复的情况，所以需要使用唯一字段进行排序，必须唯一
或者方法2：可以在入库前，先进行编号处理，然后分页时直接去对应序号的记录，或者方法3：程序执行同一语句（此处还是应该唯一字段进行排序），空跑其他数据，只取需要的数据
（如第一次取1-2条，第2次取3-4条，可以每次执行同一进行唯一排序的sql，第一次取前两条记录，第二次取前两条记录不做处理，空跑，只取3-4条数据）
正确的分页查询SQL:select * from (select rownum as rn, t2.* from (select * from t_jrnl t1 where t1.col='xxx' order by seqno ) t2 where rownum <=10 ) t3 where rn >2 #第一个层只写查询语句，且排序，不能将排序写到和rownum<=10一层

add 20210813

12.union(和union all)
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法:

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SQL UNION ALL 语法:(UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。)

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

13.增加索引

create index indexname on tablename(col1,col2);

14.判断t2表和t1表是否满足ON中条件，如果满足则用t2表去更新t1表，如果不满足，则将t2表数据插入t1表,
但是有很多可选项1.正常模式,2.只update或者只insert,3.带条件的update或带条件的insert,4.全插入insert实现,5.带delete的update(觉得可以用3来实现)

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
 
WHEN MATCHED THEN
 
[UPDATE sql]
 
WHEN NOT MATCHED THEN
 
[INSERT sql]

merge into t1 using t2
on t1.col1 = t2.col1
when matched then
update set t1.col1=t2.col2
when not matched then
insert into t1(col1,col2) valuse(t2.col1,t2.col2)

15.to_number函数
to_number函数中也有很多预定义的固定格式：
格式值含义
9 代表一个数字
0 强迫0显示
$ 显示美元符号
L 强制显示一个当地的货币符号
. 显示一个小数点
, 显示一个千位分隔符号
FM 去掉空格影响

SQL> select '|'||to_char(5,'999')||'|' from dual;

结果为：| 5|

SQL> select '|'||to_char(5,'000')||'|' from dual;

结果为：| 005|

SQL> select '|'||to_char(-5,'000')||'|' from dual;

结果为：|-005|

可见：前面的空格是为＋号留的位置，只是为＋号时省略了。
如何去除多余的空格？

 SQL> select '|'||to_char(5,'fm000')||'|' from dual;

结果为：|005|

空格没有了，fm主要作用就是去除空格的影响。

16.查询一个表数据，插入到另一个表中

insert into XTHAME.tab1(pk_bdversion,vbdcode)  select pk_bdversion,vbdcode from  DSKNOW.COMBDVERSION;

17.连接：左连接、右连接、全连接
–左连接包含左边表所有记录，右边所有的匹配的记录，如果没有则用空补齐（简写的时候+在右边）

select * from t_wdx_a a left join t_wdx_b b on a.id = b.id;
select * from t_wdx_a a left outer join t_wdx_b b on a.id = b.id;
select * from t_wdx_a a,t_wdx_b b where a.id = b.id(+);

–right join 右连接包括右边表所有记录，匹配左边表的记录，如果没有则以空补齐(简写的时候+在左边)

select * from t_wdx_a a right join t_wdx_b b on a.id = b.id;
select * from t_wdx_a a, t_wdx_b b where a.id(+) = b.id;

–full join 全连接意思是左右表所有的记录全部显示出来

select * from t_wdx_a a full join t_wdx_b b on a.id = b.id;

18.模糊匹配
一个字段模糊匹配另一个字段

select * from table a where 字段1 like '%'||字段2||'%'

或者

select * from table where INSTR(col1,col2)>0  --： 字段col2包含在col1

19.对账处理时，可以使用to_number(t1.col1)=to_number(t2.col2) 金额字段以字符存储时，小数点后的零的个数不影响。

20.INSTR(字符串,子串,起始位置) 起始位置可以省略，默认为1 和like相识的模糊匹配
INSTR(col1,‘CDFS’)返回配置到的子串的其实位置，从1开始（第一位为1）；所以匹配到，则INSTR返回大于0；不匹配返回为0；

 select * from t_cdfs_notice where INSTR(infopro,'CDFS') > 0   #字段infopro值存在CDFS的列，显示出来。

21.插入数据库，报错：ora-01653:表xxx无法通过128（在表空间xxx中）扩展
原因：表空间满了，建议增加一个表空间文件，而不是扩大原表空间文件
解决方案：增加一个表空间问价：
1.先查询原表空间文件位置：切换为oracle用户，使用sysdba登oracle;

sqlplus / as sysdba
执行：select name from v$datafile
获取到表空间文件绝对路径后，构建新文件名(一般会以表空间名.dbf为文件名)（例如原地址为/aa/bb/cc.dbf;新文件就为/aa/bb/cc02/dbf）
2.如果没有退出，则执行：(如果退出了，则再使用oracle用户，使用sysdba登录oracle)
alter tablespace 表空间名 datafile ‘新表空间文件全名’ size 100M autoextend on next 200M maxsize 1024M;
添加一个表空间数据文件，初始大小为100M,每次200M的自动增长，最大1G

22.启动/停止数据库（启动先起监听,后起数据库;停止先停数据库,再停监听）

1.切换为oracle用户，
2.启动监听 lsnrctl start (stop)
3.使用sysdba登oracle;   sqlplus sys as sysdba
4.startup	(shutdown immediate)

add 20220118

23.字符串查询时的不等于 <>

select * from table where col ='xxx'   col等于xxx的列
select * from table where col <> 'xxx'  col不等于xxx的列(为null的列也会被过滤掉)

!= 、 <>、^= 三个符号都表示“不等于”的意思，在逻辑上没有本质区别
隐含一个“不为空 is not null”的前提，所以使用时null会被过滤掉

24.substr函数格式 (俗称：字符截取函数)

格式1： substr(string string, int a, int b);
格式2：substr(string string, int a) ;
解析：
    格式1：
        1、string 需要截取的字符串
        2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
        3、b 要截取的字符串的长度
    格式2：
        1、string 需要截取的字符串
        2、a 可以理解为从第a个字符开始截取后面所有的字符串。

25.minus 去除（差集）

select * from t_jrnl minus(
	select * from t_jrnl where txn_date='20211101'
)
	查询出t_jrnl表中，除了txn_date=20211101的所有记录。
	这个方法最好是使用在两个表中，且两个表的字段相同。

26.length 和lengthb (计算某个字段的值长度)
length:计算字段的值有多少个字符；lengthb:计算字段有多少个字节
select * from table t1 where length(t1.col1) > 10
select length(‘你好’) from dual;
------2
select lengthb(‘你好’) from dual;
------4

27.not exists 不存在（可用于检查签约关系）

update table1 t1 set status = '1' where t1.txn_date='20220111' and not exists (
    select 1 from table2 t2 where t1.user_no = t2.user_no and t2.sign_flg = 'Y'
)
#更新table1中的txn_date=‘20220111’的列的status值为1，更新条件是：该列的user_no不存在于后续的查询结果中（user_no + sign_flg='A'）

注意：update的表table1使用别名后，在not exists里面可以直接使用该表的字段,而不用在not exists里的select 后面再from table1,只需要from table2

28.INTERSECT（交集）只取两个表的重复记录
UNION ALL （并集），不去重，不排序
UNION（并集），去重，按默认规则排序
MINUS（差集）只取两个表不重复记录
MINUS,INTERSECT关键字,可以通过IN,NOT IN等实现,但是如果是大批量数据的话，效率远不如使用MINUS INTERSECT关键字进行处理。

select t.*, t.rowid from emp t  intersect  select s.*, s.rowid from emp s

29.DISTINCT 用于返回唯一不同的值

SELECT DISTINCT 列名称 FROM 表名称

更多ORALCE知识点，请访问该链接

30.主键，唯一约束，唯一索引的区别

/*创建主键(主键也属于约束吧)*/
alter table tablename add constraint primary_name primary key(colname); 
/*创建唯一约束*/
alter table tablename add constraint constraint_name unique(colname);
/*创建唯一索引*/
create unique index index_name on tablename(colname);

共同点和区别：
主键约束要求列值非空，而唯一键约束和唯一索引不要求列值非空，都不能有重复数据。
主键约束和唯一键约束会隐式创建同名的唯一索引，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除。
相同字段序列不允许重复创建索引

====未完待续

P.L.C.

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
ORACLE常用语句相关知识记录

1.查看数据库名：select name from v$database;2.查看数据库实例名：select instance_name from v$instance;3.修改表字段类型alter table tablenamemodify (columnName varchar2(10) );4.修改表名alter table tb rename to tb2;5.函数：NV...
复制链接

扫一扫