Oracle日常使用参考

一、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;

 

 

 

不断更新中。。。