20200810 ORACLE 参考手册

最新推荐文章于 2025-04-14 10:09:30 发布

激流勇进_38193118

最新推荐文章于 2025-04-14 10:09:30 发布

阅读量354

点赞数

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/weixin_38193118/article/details/107906906

版权

Oracle 专栏收录该内容

7 篇文章

订阅专栏

1-查询基础

2-单行函数

3-分组函数-4-多表查询

5-子查询

6-高级查询

7-数据类型

8-改变表结构

1-查询基础

入门语句 
SQL 语句  功能描述
select * from all_tables where TABLE_NAME='AA'  ； --查询所有表：
select * from dba_tables where TABLE_NAME='AA'  ； --查询所有表：
select * from user_tables where TABLE_NAME='AA' ； --查询所有表：
select * from all_tab_columns where table_name='AA' ；   --查询字段：
select * from sys.all_ind_columns where table_name='AA' ；   --查询索引列：
select * from user_indexes  where table_name='AA' ；   --查询现有的索引：
select * from user_ind_columns where table_name='AA' ；  --索引建立在哪些字段上：
select * from all_constraints where table_name='AA'  ；  --查询约束：
select * from dictionary where table_name like '%AA%'；  --描述数据字典视图：
  
语句分类  
  
DML语句（操作） Insert、Update、Delete、Merge 
DDL语句（定义） Create、Alter、Drop、Truncate 
DCL语句（控制） Grant、Revoke 
事务控制语句    Commit、Rollback、Savepoint

2-单行函数

字符函数 	
select upper('fymod')        from dual;	-- 大写
select lower('ibo')          from dual;	-- 小写
select initcap('ibo')        from dual;	-- 首字母大写
select concat('Dear ','ibo') from dual;	-- 连接
select substr('abcdefg',2,3)  from dual; 	参数是两个，则截取到末尾，三个，第三个参数为截取个数。 
select substr('abcdefg',-4,2) from dual;	第二个是正数，从左向右数，为负数，则从右向左数。
select length('fymod') from dual ;	获取长度
select replace('aabbcc','A','a') from dual ;  	替换
select instr('mynameisfymod','name') from dual;  	--查找字符位置
select lpad('op',5,'#') from dual ;  	--左补齐
select rpad('op',5,'#') from dual ;  	--右补齐
select trim('  fymod f y  ')||'op' from dual ;  	--去掉左右空格
select round(123.456,2)  from dual;	--为123.46  第二个参数为小数位数
select round(123.456,-2) from dual;	--为100 为负值，四舍五入整数部分 
select mod(-123.456,3)  from dual;	--0.456 正负有第一个参数决定
select trunc(123.456,2) from dual;	--结果为123.45 一律舍掉 
select trunc(153.456,2) from dual;	--结果为100
select months_between(sysdate,to_date('2018-04-23','yyyy-mm-dd')) from dual;	--两个日期间的月份差
select add_months(sysdate,2) from dual;	--添加月份
select next_day(sysdate,1) from dual;	--next_day(x,y)用于计算x时间后第一个星期y的时间
select last_day(sysdate) from dual;	--当月最后一天
先执行命令：alter session set nls_date_format='yyyy-mm-dd hh24:mi:hh';	
select sysdate,trunc(sysdate,'mi') from dual;	--截取到分钟 2009-03-24 21:33:32 2009-03-24 21:33:00
select sysdate,trunc(sysdate,'hh') from dual;	--截取到小时 2009-03-24 21:32:59 2009-03-24 21:00:00
select sysdate,trunc(sysdate,'dd') from dual; --trunc(sysdate)	--截取今天 2018-05-28 00:00:00
select sysdate,trunc(sysdate,'d')  from dual;	--截取本周第一天
select sysdate,trunc(sysdate,'mm') from dual;	--截取本月第一天
select sysdate,trunc(sysdate,'y')  from dual;	--截取本年第一天

转换函数  
  
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;  
select TO_CHAR(123,'$99,999.9') from dual;  
select to_char(4567,'xxxx') from dual;  
select to_char(123,'xxx') from dual;  
SELECT TO_NUMBER('0A', 'XX') FROM dual; 
select TO_NUMBER('$123,456.78','$999,999.99') from dual;  
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;  
select sysdate,sysdate - interval '7' MINUTE from dual;   --当前时间减去7分钟的时间
select sysdate - interval '7' hour from dual;   --当前时间减去7小时的时间
select sysdate - interval '7' day from dual;  --当前时间减去7天的时间
select sysdate,sysdate - interval '7' month from dual;  --当前时间减去7月的时间
select sysdate,sysdate - interval '7' year from dual;   --当前时间减去7年的时间
select sysdate,sysdate - 8 *interval '2' hour from dual;  --时间间隔乘以一个数字

select to_char(485,'9G99') from dual;  --   ' 4 8 5'
select to_char(1485,'9,999') from dual;  --   ' 1,485'
select to_char(1485,'9G999') from dual;  --   ' 1 485'
select to_char(148.5,'999.999') from dual;  --   ' 148.500'
select to_char(148.5,'999D999') from dual;  --   ' 148,500'
select to_char(-485,'999PR') from dual;  --   '<485>'
select to_char(3148.5,'9G999D999') from dual;  --   ' 3 148,500'
select to_char(-0.1,'99.99') from dual;  --   ' -.10'
select to_char(-0.1,'FM9.99') from dual;  --   '-.1'
select to_char(0.1,'0.9') from dual;  --   ' 0.1'
select to_char(12,'9990999.9') from dual;  --   ' 0012.0'
select to_char(12,'FM9990999.9') from dual;  --   '0012'
select to_char(485,'999') from dual;  --   ' 485'
select to_char(-485,'999') from dual;  --   '-485'
select to_char(485, '"Good number:"999') from dual;  --   'Good number: 485'
select to_char(485.8,'"Pre-decimal:"999" Post-decimal:" .999') from dual;  --   'Pre-decimal: 485 Post-decimal: .800'
select to_char(12,'99V999') from dual;  --   ' 12000'
select to_char(12.4,'99V999') from dual;  --   ' 12400'
select to_char(12.45, '99V9') from dual;  --   ' 125'

通用函数	
select nvl(null,0) from dual;	空值替换 
select nvl2(null,1,2) from dual;	拓展空值替换
select nullif('a','b') from dual;	两等则null；否参一
select  coalesce(null,null,0，2) from dual;	值取不为null为止
select case when 2<1 then 1 when 3=2 then 2 else 3 end from dual ;	
select decode(2,1,'true','false') from dual

3-分组函数-4-多表查询

分组函数	
select sid,count(1),avg(sid),sum(sid),max(sid),min(sid) from aa	
group by sid	
--having count(1)=1	
	
多表查询	
select * from emp cross join dept  <---> select * from emp , dept 	笛卡尔积
select e.deptno,e.ename,p.dname from emp e, dept p where e.deptno = p.deptno  s	等值连接
elect e.ename,e.deptno,s.grade  from emp e, salgrade s where e.sal between s.losal and s.hisal 	非等值连接
select t1.empno,t1.ename,t2.empno,t2.ename from emp t1, emp t2 where t1.mgr=t2.empno 	自连接
select t.empno,t.ename,p.deptno from emp e left join dept p on e.empno=p.deptno	左连接
select t.empno,t.ename,p.deptno from emp e right join dept p on e.empno=p.deptno	右连接
select t.empno,t.ename,p.deptno from emp e full join dept p on e.empno=p.deptno	满连接
	
集合操作	
	
select deptno from emp union select deptno from dept 	重复仅显示一次
select deptno from emp union all select deptno from dept 	重复数据也显示
select deptno from emp intersect select deptno from scott.dept 	只显示重复的

5-子查询

单行子查询	
select * from emp where sal > (select sal from emp where empno=7566)	
	
多行子查询	
select * from emp where job in (select job from emp where ename = 'MARTIN' or ename = 'SMITH');	
SELECT * FROM TABLE WHERE COL > ALL(SELECT COL FROM TABLEA) 相当于 SELECT * FROM TABLE WHERE COL > (SELECT MAX(COL) FROM TABLEA)；	
SELECT * FROM TABLE WHERE COL > ANY(SELECT COL FROM TABLEA) 相当于 SELECT * FROM TABLE WHERE COL > (SELECT MIN(COL) FROM TABLEA)；	
	
topN查询	
select * from (select * from emp order by deptno) where rownum<6 	
	
分页查询	
select * from (  select rownum r,e.* from (select * from emp order by deptno) e where rownum <=5 ) t  where r>=3	
	
exists 判断执行	
select * from emp t1 where exists(select * from dept t2 where t1.deptno=t2.deptno) 	外层小用exists
等价于	
select * from dept t1 where t1.deptno in (select t2.deptno from emp t2) 	外层大用in

6-高级查询

dbms_random 随机数 		
		
select dbms_random.value(),dbms_random.value(2,3),sys_guid(),dbms_random.random,dbms_random.normal,dbms_random.string('P',8 ) from dual		
		
随机排序		
select * from ( select ename,job from emp order by dbms_random.value()) where rownum <= 5  		
等价于 		
select * from ( select ename,job from emp order by  sys_guid()) where rownum <= 5 		
select dbms_random.random() from dual 	介于2的31次方和-2的31次方之间的整形数值	
select dbms_random.normal  from dual ;	正态分布标准偏差为1，期望值为0。返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。	
select dbms_random.string('p',10) from dual；	
parameter：
  a表示all，   不区分大小写
  u表示upper， 返回大写字母
  l表示little，返回小写字母
  p表示print， 返回所有可以打印的字符
length表示随机数的长度
select * from emp order by comm desc nulls last ;	如果是last则将空值放到最后|如果是first则将空值放到最前	 

over 显示序列		length表示随机数的长度
select name,course,row_number() over(partition by course order by score desc) rank from student;	顺序排序	
select name,course,rank() over(partition by course order by score desc) rank from student;	跳跃排序，如果有两个第一级别时，接下来是第三级别	
elect name,course,dense_rank() over(partition by course order by score desc) rank from student;	连续排序，如果有两个第一级别时，接下来是第二级别 	

/*用lag,lead分析以后的数据*/  		
select t.id, lag(t.name,1,0) over(order by id desc) max_v, t.name,    	  	
lead(t.name,1,0) over(order by id desc) min_v  from TEST1 t;  		
select ename,sal,max(sal) over(),min(sal) over() from scott.emp		
select deptno,sal,sum(sal) over(partition by deptno order by ename) from emp	-
/*语法*/  
lag(exp_str,offset,defval) over()  前N行的数据(Lag)
Lead(exp_str,offset,defval) over() 后N行的数据(Lag) 
--exp_str要取的列  
--offset取偏移后的第几行数据  
--defval:没有符合条件的默认值 

/*用lag,lead分析以后的数据*/  
select t.id, lag(t.name,1,0) over(order by id desc) max_v, t.name,    
lead(t.name,1,0) over(order by id desc) min_v  
from TEST1 t;  

select ename,sal,max(sal) over(),min(sal) over() from scott.emp
select deptno,sal,sum(sal) over(partition by deptno order by ename) from emp


select  FName, FSalary, FCity, FAge,  
row_number() over(order by FSalary) as rownum,  
rank() over(order by FSalary) as rank,  
dense_rank() over(order by FSalary) as dense_rank,  
ntile(6) over(order by FSalary)as ntile 
from  T_Person 
order by  FName  

with temp as(  	
  select 'China' nation ,'Guangzhou' city from dual union all  	
  select 'China' nation ,'Shanghai' city from dual union all  	
  select 'China' nation ,'Beijing' city from dual union all  	
  select 'USA' nation ,'New York' city from dual union all  	
  select 'USA' nation ,'Bostom' city from dual union all  	
  select 'Japan' nation ,'Tokyo' city from dual   	
)  	
select nation,listagg(city,',') within GROUP (order by city)  	
from temp  	
group by nation 	
	
with temp as(  	
  select 500 population, 'China' nation ,'Guangzhou' city from dual union all  	
  select 1500 population, 'China' nation ,'Shanghai' city from dual union all  	
  select 500 population, 'China' nation ,'Beijing' city from dual union all  	
  select 1000 population, 'USA' nation ,'New York' city from dual union all  	
  select 500 population, 'USA' nation ,'Bostom' city from dual union all  	
  select 500 population, 'Japan' nation ,'Tokyo' city from dual   	
)  	
select population,  	
       nation,  	
       city,  	
       listagg(city,',') within GROUP (order by city) over (partition by nation) rank  	
from temp

7-数据类型

--timestamp——>date：						
 select to_date(to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual ;				2018-05-24 09:00:03		
--date ——>timestamp：						
 select to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual;				2018-05-24 09:00:03		

SELECT TO_CHAR(to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'),'MM/DD/YYYY HH24:MI:SS') "Date" FROM dual				
SELECT CAST(to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss') AS TIMESTAMP) "Date" FROM dual; 				
				
SELECT  trunc(7/6)*6,			
        1*24*60*60 day_second,			
        TO_CHAR(to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'), 'MMDDYYYY:HH24:MI:SS') t1,			
        TO_CHAR(to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss'), 'MMDDYYYY:HH24:MI:SS') t2,			
        trunc(86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))),			
        60 * (trunc((86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))) / 60)),			
        --秒			
        trunc(86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))) -			
        60 * (trunc((86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))) / 60)) seconds,			
		--分	
        trunc((86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))) / 60) -			
        60 * (trunc(((86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))) / 60) / 60)) minutes,			
		--时	
        trunc(((86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))) / 60) / 60) -			
        24 * (trunc((((86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))) / 60) / 60) / 24)) hours,			
		--天	
        trunc((((86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))) / 60) / 60) / 24) days,			
		--周	
        trunc(((((86400 * (to_date('2018-09-25 10:20:10','yyyy-mm-dd hh24:mi:ss') - to_date('2018-05-24 09:20:00','yyyy-mm-dd hh24:mi:ss'))) / 60) / 60) / 24) / 7) weeks			
   FROM dual			
			
 SELECT TO_CHAR(date1, 'MMDDYYYY:HH24:MI:SS') date1,
        TO_CHAR(date2, 'MMDDYYYY:HH24:MI:SS') date2,
        trunc(86400 * (date2 - date1)) - 60 * (trunc((86400 * (date2 - date1)) / 60)) seconds,
        trunc((86400 * (date2 - date1)) / 60) - 60 * (trunc(((86400 * (date2 - date1)) / 60) / 60)) minutes,
        trunc(((86400 * (date2 - date1)) / 60) / 60) - 24 * (trunc((((86400 * (date2 - date1)) / 60) / 60) / 24)) hours,
        trunc((((86400 * (date2 - date1)) / 60) / 60) / 24) days,
        trunc(((((86400 * (date2 - date1)) / 60) / 60) / 24) / 7) weeks
   FROM date_table

24*60*60=86400

Select to_char(sysdate,'ss') from dual; 					--取当前时间秒部分 
Select to_char(sysdate,'mi') from dual; 					--取当前时间分钟部分 
Select to_char(sysdate,'HH24') from dual; 					--取当前时间秒小时部分 
Select to_char(sysdate,'DD') from dual; 					--取当前时间日期部分 
Select to_char(sysdate,'MM') from dual; 					--取当前时间月部分 
Select to_char(sysdate,'YYYY') from dual; 					--取当前时间年部分 
Select to_char(sysdate,'w') from dual; 					--取当前时间是一个月中的第几周(从1日开始算) 
Select to_char(sysdate,'ww') from dual; 					--取当前时间是一年中的第几周（从1.1开始算） 
Select to_char(sysdate,'iw') from dual; 					--取当前时间是一年中的第几周（按实际日历的） 
Select to_char(sysdate,'d') from dual; 					--取当前时间是一周的第几天，从星期天开始，周六结束 
Select to_char(sysdate,'day') from dual; 					--　取当前日是星期几，和数据库设置的字符集有关，会输出'Tuesday' 
Select to_char(sysdate,'ddd') from dual; 					-- 当前日是一年中的第几天 

Long	存储可变长字符串，最大长度为2G	 https://www.cnblogs.com/SaraMoring/p/5625839.html
		
Blob和Clob类型的区别	BLOB 二进制大型对象（Binary   Large   Object)。存储的最大为4G字节     	
	CLOB 字符  大型对象（Character   Large   Object)。CLOB用于存储数据库中的大型单字节字符数据块，不支持宽度不等的字符集。存储的最大为4G字节	
	通常像图片、文件、音乐等信息就用BLOB字段来存储，先将文件转为二进制再存储进去。	
	像文章或者是较长的文字，就用CLOB存储，查询更新存储等操作都提供很大的方便。

8-改变表结构

/*---表（名字，约束,视图，序列）---*/

--修改表名
rename table_name to table_newname;--重命名表名或者使用
alter table table_name rename to table_newname
或者
create table table_newname as select * from table_name;
drop table table_name; 

在Oracle中，约束包括：not null、unique，primary key，foreign key和check五种,增加约束
alter table table_name modify column_name not null;--非空
alter table table_name modify column_name null;--为空
create unique index username_k on stu_account(username);   --唯一索引不能插入相同的数据
create unique index stu_couse_unq on stu_couse(stu_id,couse_id); --唯一学生
alter table table_name add constraint constraint_def unique(column_name);
alter table table_name add constraint constraint_def primary key(column_name);
alter table table_name add constraint constraint_def foreign key(column_name) references r2(column_name);
alter table table_name add constraint stu_fk foreign key (stu_id) references students(id) ON DELETE CASCADE;--外键约束,级联删除
alter table table_name add constraint constraint_def check(条件);
alter table table_name drop constraint column_name constraint_def;--删除约束
alter table table_name drop constraint primary key cascade;
禁止约束:
约束 disable;--新建约束时
alter table table_name disable constraint constraint_def;
允许约束：
alter table table_name enable constraint constraint_def;
获得约束信息：
select * from user_constraints;
select constraint_name,constraint_type from all_constraints where table_name = upper('student') 

--创建试图 目的把表联合起来 然后看成一个表，在与其他的联合进行查询 
create view xsxk as select cl.classname, s.stu_name,c.couse_id, c.couse_name from stu_couse sc,students s,classes cl,couse c where sc.stu_id=s.id and sc.couse_id=c.couse_id and s.class_id=cl.id;
--创建用户
create user han identified by han default tablespace
users Temporary TABLESPACE Temp;
grant connect,resource,dba to han; //授予用户han开发人员的权利

create sequence stu_couse_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;
drop sequence org_seq;

update table_name set (a1,a2,a3)=(select (A1,A2,A3) from table_name where...) where...;
select * from stu_account t where t.count_id=2 for update; --行锁

alter session set nls_date_format='yyyy-mm-dd';--日期设置成中国人习惯的方式
alter session set time_zone;--改变会话时区

查询索引列：select * from sys.all_ind_columns where table_name='EMP' 
查询现有的索引：select * from user_indexes  
索引建立在哪些字段上：select * from user_ind_columns

/*---字段，字段类型---*/
添加字段
alter table userinfo    add  remarks varchar2(100);  
alter table table_name  add  column_name datatype;  --增加字段
alter table table_name  drop column column_name;--删除字段
alter table table_name  rename column column_name To new_column_name;  --修改表字段名称
alter table userinfo modify remarks varchar2(150);   --更改字段长度

修改字段类型
a.如果表中没有数据
alter table 表名 modify(字段名1 类型,字段名2 类型,字段名3 类型.....)  
b.如果表中有数据(分为四步)
--修改原字段名   
alter table 表名 rename column 字段名a to 字段名b;  
--添加一个和原字段同名的字段"字段名a",添加的字段    
alter table 表名 add 字段名 目标类型;  
--将"字段名b"的数据更新到新建字段"字段名a"中  
update 表名 set 字段名a = tirm(字段名b);  
--更新完成后,删除原备份字段"字段名b"  
alter table 表名 drop column 字段名b  


/*---注释---*/
comment on table  audit_prod_main is    '稽核主推品项';
comment on column audit_prod_main.sid is     '序列号';

/*---查---*/
select table_name from user_tables; //当前用户的表
select table_name from all_tables; //所有用户的表
select table_name from dba_tables; //包括系统表
select table_name from dba_tables where owner=’zfxfzb’