Oracle学习笔记整理手册

举报
yd_273762914 发表于 2020/12/02 22:58:16 2020/12/02
【摘要】 文章目录 第一章、Oracle SQL技巧(1)Oracle正则匹配使用(2)sql筛选出记录数大于2的记录(3)oracle同义词(4)oracle内外连接(5)oracle去重关键字(6)Oracle不等于符号(7)1=1和1=0(8)Oracle in关键字和等于符号(9)union(10)exist和in 第二章、Oracle数据库操作(1)创建表空间(...


第一章、Oracle SQL技巧

(1)Oracle正则匹配使用

PS:这条SQL可以通过正则匹对查询一下,表A的字段a是否有非数字的数据,有时候数据表的一些字段是varchar类型的,如果sql里用to_number的话,就肯定会报无效数字的错误

select * from 表A where not regexp_like(字段a,'^[[:digit:]]+$');

  
 
  • 1

(2)sql筛选出记录数大于2的记录

select id,count(*) from A a
where a.test like '%'
group by id
having count(*)>=2

  
 
  • 1
  • 2
  • 3
  • 4

(3)oracle同义词

创建同义词:
create public synonym tablename for user.tablename;
synonyms

  
 
  • 1
  • 2
  • 3

(4)oracle内外连接

Oracle左右连接可以使用(+),+号在左表示右外连接,在右表示左外连接

(5)oracle去重关键字

Oracle关键字:
distinct:过滤掉多余的重复记录只保留一条

(6)Oracle不等于符号

oracle不等于有下面三种表示:
!=、^=、<>
<>可移植性好

(7)1=1和1=0

select * from t where 1=1
1=1条件始终为true,是为了避免负责的条件查询出现错误
1=0条件始终未false,结果不会返回任何数据,所以可以用于建表,create table newtable as select * from oldtable where 1=0;

(8)Oracle in关键字和等于符号

oracle in 和 =的区别
in可以有多条记录,=只有一条记录
=性能更好,因为使用=符合能更好地使用索引

(9)union

union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;

union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;

(10)exist和in

select a.id from A a where a.id in(select b.id from B b)

select a.id from A a where exists(select b.id from B b where a.id = b.id)

  
 
  • 1
  • 2
  • 3

第二章、Oracle数据库操作

(1)创建表空间

–创建表空间
CREATE SMALLFILE
TABLESPACE “WORKFLOW01”
LOGGING
DATAFILE
‘D:\app\Administrator\oradata\oanet\WORKFLOW01.ora’ SIZE
245M EXTENT MANAGEMENT LOCAL SEGMENT SPACE Management AUTO

(2)oracle数据库还原

1、将TESTDB.bmp放置在dpdump目录,如下路径:
E:\app\Administrator\admin\orcl\dpdump

2、创建用户
create user testdb65 identified by “testdb65”
default tablespace users temporary tablespace temp;
用户授权
grant connect,dba to testdb65;

3、创建表空间
CREATE TABLESPACE HR_DATA01 DATAFILE ‘D:\nhsj\hr_data01.dbf’ SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; 【创建表空间】

4、导入 bgyhr2是来源库
impdp testdb65/testdb65@orcl schema=testdb65 dumpfile=TESTDB65.dmp

导出 scott 是要导出的库
expdp lfwtrain/a@orcl schema=当前用户 dumpfile=expdptest.dmp

(3)重导dmp文件

Oracle保存数据时一般以DMP文件的形式进行保存
本博客记录,DMP文件的导入 先打开CMD
sqlplus /nolog
以sysdba连接
conn /as sysdba
这里创建表空间,因为表空间进行存储的话,可以提供系统性能
CREATE TABLESPACE DATA01 DATAFILE 'D:\tablespace\data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
创建用户
CREATE USER job IDENTIFIED BY job DEFAULT TABLESPACE DATA01 TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON DATA01
授权
grant connect,dba to job;
复制dmp文件到oracle安装目录里的app\admin\orce\dpdump里,然后用以下代码导入dmp文件,要顺便导入logfile的话,加上logfile=文件名
impdp job/job@orcl schemas=job dumpfile=EXPDP160810GZ.dmp

(4)批量写入大量数据

SQL> begin
  2  for i in 1..5000 loop
  3  insert into employee values('EMPNO',i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL 过程已成功完成。

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

(5)col [字段名] format a[字符数]

sqlplus查询时有时候有些数据返回太长了,这里可以用col [字段名] format a[字符数]的格式定义数据返回格式
eg:sys_user表的name字段查询时返回20个字符

SQL> col name format a20;
SQL>select name from sys_user where username='admin';

  
 
  • 1
  • 2

(6)查询某张表约束信息

select * from user_constraints t where t.table_name = '表名'

  
 
  • 1

(7)强制删除表

即使有表关联也强删,不太建议,不过有些情况可以这样做
DROP TABLE 表名 CASCADE CONSTRAINTS;

(8)建表并加主键

例子

create table T_TEST
( UUID VARCHAR2(255) not null, CODE VARCHAR2(255) not null, constraint PK_T_TEST primary key (UUID)
);

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

(9)Oracle表数据回滚语句

【表数据恢复回N分钟之前】

alter table 表名 enable row movement; 
flashback table 表名 to timestamp systimestamp - interval 'N' minute; 

  
 
  • 1
  • 2
  • 3
  • 4

(10)Oracle修改有数据的数据字段类型

有些时候,我们是要修改一些数据字段的类型的,mysql是可以支持的,不过Oracle对一些有数据的数据字段是不予许修改的,这时候,可以通过下面sql进行修改,大概思路就是创建一个临时的,负责转运数据,然后再切换

alter table 表A add new_temp varchar2(50);
update 表A set new_temp = a;
commit;
update 表A set a = '';
commit;
alter table 表A modify a varchar2(50);
update 表A set a = new_temp;
commit;
alter table 表A drop column new_temp;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

第三章、Oracle函数用法

(1)NVL函数和NVL2函数

NVL函数的格式如下:NVL(expr1,expr2)
含义是:如果oracle第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第一个参数本来的值。

NVL2函数的格式如下:NVL2(expr1,expr2, expr3)
含义是:如果该函数的第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第三个参数的值。SQL> select ename,NVL2(comm,-1,1) from emp;

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL),否则返回第一个值。

Coalese函数的作用是的NVL的函数有点相似,其优势是有更多的选项。
格式如下:
Coalesce(expr1, expr2, expr3…… exprn)
表示可以指定多个表达式的占位符。所有表达式必须是相同类型,或者可以隐性转换为相同的类型。
例子,对于字段b查询出来为空的情况,就返回’’,否就返回vm_concat(a||’(’||b||’)’),具体可以参考我的博客: https://blog.csdn.net/u014427391/article/details/84981114

select to_char(nvl2(b,vm_concat(a||'('||b||')'), '') from A group by id

  
 
  • 1

(2)递归查询

start with 子句:遍历起始条件,有个小技巧,如果要查父结点,这里可以用子结点的列,反之亦然。

connect by 子句:连接条件。关键词prior,prior跟父节点列parentid放在一起,就是往父结点方向遍历;prior跟子结点列subid放在一起,则往叶子结点方向遍历

https://www.cnblogs.com/benbenduo/p/4588612.html
ps:例子,递归查询一般用于递归查单位,地区信息等等什么的。start with后可以加一些开始条件,没有就写1=1或者不写也可以的。id和parent_id根据业务需要替换。

select * from 表格1 start with 1=1 connect by id = prior parent_id 

  
 
  • 1

(3)条件sql

case when then else end 可以理解为流程控制语句或条件控制语句。可以实现资料获取的时候,可以更多的条件和自定义逻辑
列子:

case when (select count(1) from t_unit_info where para_unit_code = unitInfo.unit_code) > 0 then 'closed' else 'open' end as state

  
 
  • 1
  • 2
  • 3

select 1 from mytable;与select anycol(目的表集合中的任意一行) from mytable;与select * from mytable 作用上来说是没有差别的,都是查看是否有记录,一般是作条件用的。select 1 from 中的1是一常量,查到的所有行的值都是它,但从效率上来说,1>anycol>*,因为不用查字典表。

(4)vm_concat函数

函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行
ps:这个列转行函数vm_comcat()在oracle10比较稳定,到了oracle11就会出现一些不兼容的情况,oracle12版本就没这个函数了

具体可以参考我的博客:https://blog.csdn.net/u014427391/article/details/84981114

(5)replace函数

REPLACE(char, search_string [, replacement_string ] )

char : 等待替换的字符串

search_string : 搜索需要替换的字符串

replacement_string : 替换字符串

如果replacement_string缺省或者为null,那么所有char中出现的search_string 都将被移除

如果search_string为null,那么结果就是char
例子:过滤某个字段的换行符

select replace(a, chr(10)) from tableA

  
 
  • 1

(6)instr函数

格式一:instr( string1, string2 ) / instr(源字符串, 目标字符串)

格式二:instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析:string2 的值要在string1中查找,是从start_position给出的数值(即:位置)开始在string1检索,检索第nth_appearance(几)次出现string2。

例子:查询某个数据字段是否包含换行符

select * from tableA where instr(a,chr(10))>0

  
 
  • 1

(7)decode函数

含义解释:
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)

ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF

例子:下面的sql就可以加在where后面,sql意思就是t.project_Name为空的情况#{projectName}=#{projectName},也即1=1,就是不做任何操作;不为空的情况,t.project_Name=#{projectName}

decode(t.project_Name,null,#{projectName},t.project_Name) = #{projectName}

  
 
  • 1

(8)upper和lower函数

upper函数是将字段转成大写,lower相反,是将字段转成小写
例子:
upper将 user表里的user_name字段信息中含有字母的全部转成大写的方法:

update user set  user_name=Upper(user_name)

将 user表里的user_name字段信息中含有字母的全部转成小写的方法:

update user set  user_name=lower(user_name)

(9)oracle开窗函数

分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。

开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化

上面是开窗函数over(…)的简单介绍。

oracle开窗函数使用的话一般是和order、partition by、row_number()、rank()、dense_rank()几个函数一起使用

例子:成绩表的例子,

学号 姓名 班级 成绩
111 小王 1 92
123 小李 2 90
134 小钱 3 92

数据表为t_score,字段分别为stuId,stuName,classId ,score

over函数和row_number一起使用:
开窗函数和row_number函数一起使用的话,就是返回一行,不过这里其实不适合用来统计,因为统计成绩的话,可以存在分数一样的两条数据,而row_number只返回一条数据。


select * from ( select stuName,row_number()over(partition by classId order by score desc) rnfrom t_score ) where rn=1;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

over函数和rank一起使用:
rank函数功能也是排序,这里的话,假如有分数一样的两条数据的情况,是会返回两条的,但是假如第二名,就是从3开始计算了

 select stuName,rank()over(partition by classId order by score desc) mm from t_score


  
 
  • 1
  • 2
  • 3

over函数和dense_rank一起使用:
dense_rank函数是rank函数的补充,假如有分数一样的两条数据,是可以按照正常排序,第二名的情况,还是显示2

 select stuName,dense_rank()over(partition by classId order by score desc) mm from t_score


  
 
  • 1
  • 2

(10)oracle rpad函数

Oracle提供了rpad函数,rpad函数从右边对字符串使用指定的字符进行填充   
rpad(string,padded_length,[pad_string])   
string :表示被填充的字符串   
padded_length :表示字符的长度   
pad_string 是个可选参数,字符串是要粘贴到string的右边,如果这个参数没有写,lpad函数将会在string的右边粘贴空格。

(11)ceil函数和floor函数

ceil(n) 取大于等于数值n的最小整数;
floor(n)取小于等于数值n的最大整数

第四章、Oracle调优指南

(1)SQL资源使用情况分析

SELECT SQL_TEXT, SQL_FULLTEXT, ELAPSED_TIME, DISK_READS, BUFFER_GETS, EXECUTIONS, Round(ELAPSED_TIME / EXECUTIONS ,2), ROUND(DISK_READS / EXECUTIONS, 2), ROUND(BUFFER_GETS / EXECUTIONS , 2), ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) FROM V$SQLAREA 
 WHERE EXECUTIONS > 0 AND BUFFER_GETS > 0 AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8 ORDER BY Round(ELAPSED_TIME / EXECUTIONS ,2) desc;

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

(2)with…as用法

oracle提供了with…as用法,with as有点像视图,with as短语相当于子查询部分,其语法大概为:

with 临时表名tmp as (子查询SQL) select * from 表格 where id in (select * from
tmp)

给个例子,可以scott登录一下,然后给个子查询相关的sql

查出部门名称为SALES的职员信息:

select * from emp where deptno in (select deptno from dept where dname='SALES');


  
 
  • 1
  • 2

用with as进行改写,看起来貌似with as更麻烦一点,然后为什么一些DBA调优时候喜欢用with as?因为with as是有带缓存的,当该片断被调用2次以上,优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中,所以以后查询都是查全局temp表,子查询比较耗性能时候,可以用with as进行改造调优

with tmp as (select deptno from dept where dname='SALES')
select * from emp where deptno in (select * from tmp);

  
 
  • 1
  • 2

(34)查看数据库锁情况

WITH blockers_and_blockees AS
 (SELECT ROWNUM rn, a.* FROM gv$session a WHERE blocking_session_status = 'VALID' OR (inst_id, sid) IN (SELECT blocking_instance, blocking_session FROM gv$session WHERE blocking_session_status = 'VALID'))
SELECT LPAD(' ', 3 * (LEVEL - 1)) || sid || DECODE(LEVEL, 1, ' root blocker') blocked_session, inst_id, event, TO_CHAR(FLOOR(seconds_in_wait / 3600), 'fm9900') || ':' || TO_CHAR(FLOOR(MOD(seconds_in_wait, 3600) / 60), 'fm00') || ':' || TO_CHAR(MOD(seconds_in_wait, 60), 'fm00') time_in_wait, username, osuser, machine, (SELECT owner || '.' || object_name FROM dba_objects WHERE object_id = b.row_wait_obj#) waiting_on_object, CASE WHEN row_wait_obj# > 0 THEN DBMS_ROWID.rowid_create(1, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#) END waiting_on_rowid, (SELECT sql_text FROM gv$sql s WHERE s.sql_id = b.sql_id AND s.inst_id = b.inst_id AND s.child_number = b.sql_child_number) current_sql, status, serial#, (SELECT spid FROM gv$process p WHERE p.addr = b.paddr AND p.inst_id = b.inst_id) os_process_id
  FROM blockers_and_blockees b
CONNECT BY PRIOR sid = blocking_session AND PRIOR inst_id = blocking_instance
 START WITH blocking_session IS NULL;


  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45

(35)查询表索引情况

  • 查询表索引情况
 select table_name, index_name, last_analyzed, blevel, num_rows, leaf_blocks, distinct_keys, status from user_indexes
  where table_name = 'LIST_PART_TAB';

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 查询索引在哪些列上
select index_name, column_name, column_position
  from user_ind_columns
 where table_name = 'LIST_PART_TAB';


  
 
  • 1
  • 2
  • 3
  • 4
  • 查询普通表失效的索引
select ind.index_name, ind.table_name, ind.blevel, ind.num_rows, ind.leaf_blocks, ind.distinct_keys
  from user_indexes ind
 where status = 'INVALID';

  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 查询分区表失效的索引

select a.blevel, a.leaf_blocks, a.index_name, b.table_name, a.partition_name, a.status
  from user_ind_partitions a, user_indexes b
 where a.index_name = b.index_name and a.status = 'UNUSABLE';


  
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

文章来源: smilenicky.blog.csdn.net,作者:smileNicky,版权归原作者所有,如需转载,请联系作者。

原文链接:smilenicky.blog.csdn.net/article/details/82317376

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。