一、Oracle中常用的函数
1. 单行函数-字符函数
lower:转换为小写。如lower('John Smith'),返回john smith
upper:转换为大写。如upper('John Smith'),返回JOHN SIMTH
initcap:单词首字母大写。如initcap('JOHN smith'),返回John Smith
concat:字符串连接。如concat('Hello ','World'),返回Hello World
substr:截取子串。如substr('HelloWorld',4,3),返回loW
instr:定位子串。如instr('Hello World','or'),返回8
lpad:左侧填充。如lpad('Smith',10,'*'),返回*****Smith
rpad:右侧填充。如rpad('Smith',10,'*'),返回Smith*****
trim:过滤首尾空格。如trim(' Mr Smith '),返回Mr Smith
replace:替换。如replace('Smith','mi','na'),返回Snath
translate:替换。如translate('acdd','cd','ef'),返回aeff
length:字符串的长度。如length('Smith'),返回5
ascii:求ASC码。如ascii('A'),返回97
chr:asc码变字符。如chr(97),返回a
soundex:将alpha字符串转换成由四个字符组成的代码,以查找相似的词或名称。
to_multi_byte:将字符串中的单字节字符转化为多字节字符。
bfilename(dir,file):指定一个外部二进制文件。
convert:转换函数。
dump:以fmt指定的内部数字格式返回一个VARCHAR2类型的值。
empty_blob和empty_clob:对大数据类型字段进行初始化操作。
2. 单行函数-数值函数
abs:取绝对值。如abs(-3.14),返回3.14
round:四舍五入。如round(314.15,-2),返回300
trunc:截断。如trunc(3.1415,3),返回3.141
ceil:向上取整。如ceil(3.14),返回4
floor:向下取整。如floor(3.14),返回3
sign:判断数值正负。如sign(-3.14),返回-1
sin:三角函数正弦。如sin(3.14),返回0.00159265
cos:余弦。如cos(-5),返回0.28366218
tan:正切。如tan(10),返回0.64836082
asin:反正弦。如asin(0.5),返回0.52359877
acos:反余弦。如 acos(-1),返回3.14159265
atan:反正切。如atan(1),返回0.78539816
sinh:双曲正弦的值。如sinh(10),返回11013.2328
tanh:双曲正切值。如tanh(20),返回1
power:幂运算。如power(4.5,2),返回20.25
sqrt:开平方根。如sqrt(9),返回3
mod:取模(取模)。如mod(10,3),返回1
exp:基数为e的幂运算。如exp(1),返回2.71828183
log:对数运算。如log(4,16.0),返回2
ln:自然对数运算。如ln(7),返回1.94591015
stddev(distinct|all):求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差。
variance(distinct|all):求协方差
3. 单行函数-日期函数
add_months(x,y):计算在日期x基础上增加y个月后的日期。如add_month(sysdate,2)
last_day(x):返回日期x当月最后一天的日期。如last_day(sysdate)
months_between(x,y):返回日期x和y之间相差的月数。如months_between(sysdate,hiredate)
round(x,y):将日期x四舍五入到y所指定的日期单位(月或年)的第一天。如round(sysdate,'month')或round(sysdate,'year')
trunc(x,y):将日期x截断到y所指定的日期单位(月或年)的第一天。如trunc(sysdate,'month')或trunc(sysdate,'year')
next_day(x,y):计算指定日期x后的第一个星期几(有参数y指定)对应的日期。如next_day(sysdate,'SUNDAY')
new_time(date, timezone1, timezone2) :把timezone1时区的日期时间date转换为timezone2时区的日期时间。
sysdate:得到系统的当前日期。
4. 单行函数-转换函数
to_number:字符类型转换为数值类型。
to_date:字符类型转换为日期类型。
to_char:数值类型或日期类型转换为字符类型。
5. 单行函数-通用函数
greatest:求最大值。如greatest(100,90,80,101,01,19)
least:2.求最小值。如least(100,0,-9,10)
nvl(exp1,exp2):如果表达式exp1的值为null,则返回exp2的值,否则返回exp1的值。
nvl2(exp1,exp2,exp3):如果表达式exp1的值不为null,则返回exp2的值,否则返回exp3的值。
nullif(exp1,exp2):如果表达式exp1与exp2的值相等则返回null,否则返回exp1的值。
coalesce(exp1,exp2,...):依次考察各参数表达式,遇到非null值即停止并返回该值。
CASE函数:case表达式用于实现多路分支结构。
语法格式:
case exp when comparison_exp1 then return_exp1
when comparison_exp2 then return_exp2
when comparison_exp3 then return_expn
else else_exp
end
DECODE()函数:用于实现多路分支结构
语法格式:
decode(col | expression, search1, result1
[,search2, result2,...,]
[,default])
6. 分组函数
avg:计算平均值,适用数值型。
count:返回查询所得到的记录行数,适用任何类型数据。
max:计算最大值,适用任何类型数据。
min:计算最小值,适用任何类型数据。
sum:求和,适用数值型。
GROUP BY子句:将表中的数据分成若干小组。如:select deptno, avg(sal) from emp group by depno;
HAVING子句:用于过滤分组。
二、Oracle中的常用操作
查看系统日期:select sysdate from dual
查看当前用户下的表:select * from tab
查看包含的所有的表:select * from tab where tabtype='TABLE'
查看表结构:desc 表名
清除屏幕:clear screen
退出:quit/exit
检查语句是否有错: show error
查看参数:show parameter;
查看数据库参数:show parameter db;
查看当前用户的角色:select * from user_role_privs;
查看当前用户的系统权限:select * from user_sys_privs;
查看当前用户的表权限:select * from user_tab_privs;
查看当前用户的缺省表空间:select username, default_tablespace from user_users;
查看用户:show user;
创建用户:create user 用户名 identified by 密码;
修改密码:alter user 用户名 identified by 密码;
锁定用户:alter user 用户名 account lock
解锁用户:alter user 用户名 account unlock
用户口令立即失效:alter user 用户名 password expire
删除用户:drop user 用户名
删除用户及数据:drop user 用户名 cascade
赋予用户创建表的权限:grant create table to 用户名
赋予用户删除表的权限:grant drop table to 用户名
赋予用户插入表的权限:grant insert table to 用户名
赋予用户修改表的权限:grant update table to 用户名
给用户赋予创建会话的权限:grant create session to 用户名
给用户赋予创建表、视图、触发器、序列、过程权限:grant create table,create view,create trigger, create sequence,create procedure to 用户名
给用户赋予管理员权限:grant dba to 用户名
给用户赋予登录权限:grant connect to 用户名
给用户赋予无限表空间权限:grant unlimited tablespace to 用户名
授予所有权限(all)给所有用户(public):grant all to public;
收回权限:revoke dba from 用户名
查看用户下所有的表:select * from user_tables;
查看名称包含log字符的表:select * from user_objects where instr(object_name, 'LOG')>0;
开启自动提交:set autocommit on
关闭自动提交:set sutocommit off
创建角色:create role 角色名
删除角色:drop role 角色名
把角色赋予用户:grant 角色名 to 用户名
查看版本:select * from v$version; 或 select * from sys.v_$version;
查数据库的SID:select * from v$database;
查某个用户是从哪台机器登陆ORACLE的:SELECT machine , terminal,username FROM V$SESSION;
查看当前用户权限:select * from session_privs;
查看授予用户的角色:select * from dba_role_privs;
设置一行字符个数,缺省为80:set linesize 80;
显示用户名和用户状态:select username, account_status from dba_users;
加载并执行SQL脚本文件:@E:\1.sql;或者start E:\1.sql;
查询表空间:select * from v$tablespace;
进入记事本编辑SQL语句页面:ed
开始录制:spool E:\2.txt;
结束录制:spool off;
查看当前库的所有数据表:select count(1) from all_tables;
查看系统拥有哪些用户:select * from all_users;
查看角色拥有那些权限:select role, privilege from role_sys_privs where role='RESOURCE';
或 select grantee,privilege from dba_sys_privs where grantee='RESOURCE';
连接到新用户:conn 用户名/密码
连接到SYS用户:conn sys/密码 as sysdba
创建自增长的序列:create sequence 序列名 increment by 1 start with 1 nomaxvalue nocycle cache 10;
查询序列的值:select 序列名.nextval from dual;
删除序列:drop sequence 序列名
重启Oracle:su - oracle;sqlplus / as sysdba;shutdown immediate;startup;lsnrctl stop;lsnrctl start;
密码过期时间限制:select * from dba_profiles s where s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
解除密码过期限制:ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
创建远程数据库链接:create [public] database link 数据库链接名 connect to 用户名 identified by 密码 using '服务器上tnsnames.ora配置的服务名';
从远程数据库中查询数据:select * from 表名@数据库链接名
删除数据库链接:drop [public] database link 数据库链接名
创建目录:create or replace directory 目录名 as '路径名';
给用户赋予读写该目录的权限:grant read, write on directory 目录名 to 用户名;
给所有人赋予读写该目录的权限:grant read, write on directory 目录名 to public;
给用户赋予创建目录的权限:grant create any directory to 用户名;
查看用户的目录权限:select owner,table_name,grantor,privilege from dba_tab_privs where grantee='用户名';
查看已有的目录:select * from dba_directories;
删除目录:drop directory 目录名;
从root用户切换到oracle用户:su - oracle
不登录进入sqlplus环境:sqlplus /nolog
以管理员模式登录sqlplus环境:sqlplus / as sysdba
启动数据库:startup
停止数据库:shutdown immediate
启动监听服务:lsnrctl start
停止监听服务:lsnrctl stop
查看监听状态:lsnrctl status
重命名表名:alter table 表名 rename to 新表名;
重命名表中的列名:alter table 表名 rename column 列名 to 新列名;
添加字段:alter table 表名 add 列名 varchar(30);
添加字段并设默认值:alter table 表名 add 列名 varchar(30) default '默认值';
修改字段类型:alter table 表名 modify 列名 varchar(50);
删除字段:alter table ts_user drop column 列名;
给表加注释:comment on table 表名 is '注释信息';
给字段加注释:comment on column 表名.列名 is '注释信息';
增加一行数据:insert into ts_user (id, user_name) values ('1', 'tuozixuan');
删除数据:delete from ts_user where id ='1';
更新数据:update ts_user set user_name where id='1'
查询数据:select * from ts_user where id ='1';
创建新表,新表表结构与原表相同:create table 新表 as select * from 原表 where 1<>1;
创建新表,新表表结构和数据与原表相同:create table 新表 as select * from 原表;
把查询的数据之间插入另一张表:insert into ts_user_temp (id, user_name) select id, user_name from ts_user;
查看有哪些表空间:select * from dba_tablespaces;
当前用户的表空间:select * from user_tablespaces;
创建表空间testdb:create tablespace testdb datafile 'F:\oracle\oradata\orcl\testdb.dbf' size 100m autoextend on next 50m extent management local;
删除表空间testdb:drop tablespace testdb including contents and datafiles
查看表空间与文件路径:select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
产生任意大小的随机数:select dbms_random.random from dual;
产生0-1之间的随机数:select dbms_random.value from dual;
产生10-20之间的随机数:select dbms_random.value(10,20) from dual;
产生服从正态分布的随机数:select dbms_random.normal from dual;
产生随机字符串:select dbms_random.string('P',15) from dual;
(u,U:大写字母;l,L:小写字母;a,A:大小写字母;x,X:数字大写字母;p,P:可打印字符)
产生0-100的随机数:select dbms_random.value * 100 from dual;或select dbms_random.value(0,100) from dual;
产生0-100的整数:select trunc(dbms_random.value(0,100)) from dual;或select abs(mod(dbms_random.random,100)) from dual;
产生长度为20的随机数字串:select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual;
产生随机日期:select to_date(to_char(sysdate,'J') + trunc(dbms_random.value(0,365)),'J') from dual;
从表中随机取数据:select * from (select * from emp order by dbms_random.random) where rownum < 10;
生成GUID:select sys_guid() from dual;
不断更新中。。。
相关推荐
NULL 博文链接:https://askerain.iteye.com/blog/2294044
本书共有28章,每章都给出详细的样例与使用说明,全书基本覆盖了Oracle 12c的SQL、PL/SQL及DBA日常管理的基本部分,每章节按照主题进行内容的描述,每个主题构成完整的内容单元。使用者可直接从这些主题中查阅到所...
DBA日常维护工作手册参考 降低现场实施人员和用户oracle数据库管理难度
Oracle数据库日常使用中的维护技巧探究.pdf
ORACLE数据库日常使用中的维护和技巧.pdf
Oracle 数据库日常维护手册,内容不太新(适合9i、10g的数据库),对服务器和unix上的oracle基本没涉及,入门、初级的选手可用来学习和参考(不是我原创的)
讲述了IBM小型机日常巡检内容,同时对oracle10g中进程占用的PS空间较大做了参数修改,确保能释放资源。另外,oracle的巡检只是最简单的巡检,仅供参考
Oracle数据库日常维护指导手册_挺好的教程,需要的可以学习参考下~
Oracle日常维护,用户系统的日常维护,较完整,可供参考。
本教材是作者在多年的使用Oracle数据库系统的工作中和问题解决中的总结,内容主要解决问题和参考为主。对概念和理论只作简单的描述。要想详细了解Oracle的技术及理论,还得参考另外的资料。本教材主要包括三个部分,...
文档按问题处于的阶段分两部分--事前阶段、事中阶段,事前阶段描述了每天、每周末、每月末针对数据库所需要进行的管理工作,如:日常监控,包括环境监控、数据库运行状况监控、性能监控;日常数据库管理,包括:系统...
一个非常精悍但很全面的9i DBA的参考手册,包括日常命令,管理视图等非常有用的信息。
oracle数据库日常运维探讨.pdf
2.2. DBA 日常工作——运行维护 19 2.2.1. ORACLE数据库管理员应按如下方式对ORACLE数据库系统做定期监控: 19 2.2.2. 每天的工作 20 2.2.3. 每周的工作 21 2.2.4. 每月的工作 22 DBA例行工作 22 2.3. 开发 DBA 工作...
Oracle数据库日常维护与优化.pdf
ORACLE集群日常维护和保养.pdf
OracleDBA日常维护手册(最全最实用)非常适合DBA参考使用,包含日常维护常用命令。
探讨Oracle数据库日常维护与优化.pdf
论oracle数据库日常维护与优化.pdf