Oracle应用知识tips

1.登录命令

sqlplus user_name/password@host_string

 

2.编辑最后一条命令

edit

 

3.执行sql脚本，使用@

@directory_path/example.sql

 

4.退出

exit

 

5.断开与数据库链接(*注意此时依然运行sql*plus)

disconnect

   恢复链接

connect

 

6.创建用户

create user_name identified by password

 

7.授予权限

grant connect, resource to user_name;

 

8.常用类型

char(n),

varchar2(n),

date, 范围：公元前4712年1月1日到公元后4712年12月31日 

integer,

number(precision, scale),

binary_float/binary_double, 当开发一个涉及大量数字运算的系统时，应当考虑使用binary_float或者binary_double

 

9.行标识(伪列):rowid 18位数字，以64为基数。

数据库中每一行都有一个唯一的行标识符：rowid，数据库内部通常使用它来访问该行。该值包含了该行在Oracle数据库中的物理位置。

 

10.日期运算

to_date() + 2 日期加上数字表示在这个日期上加上2天。

to_date() - to_date() 结果是这两个日期之间相差的天数。

 

11.列别名

name as “another alias”

 

12.空值 null

检查是否空值：is null / is not null

转换空值：nvl(col_name, 'default_value')

 

13.禁止显示重复行

distinct

 

14.比较操作

where customer_id > any(2, 3, 4);

where customer_id > all(2, 3, 4)

 

15.sql操作符

like

in

between

is null

is NAN (10g新增 非数字)

is INFINITE  (10g新增 无穷 匹配binary_float/binary_double)

 

16.escape

where first_name like '%a/_product%' escape '/' 

使用escape后表示_前面的反斜线是一个转义字符，不会再将_作为通配符。

 

17.排序order by 默认升序 

ASC  升序

DESC 降序

 

18.字符函数

ascii(x)

chr(x)

concat(x, y)

initcap(x)

instr(x, find_string, start, occurrence)

length(x)

lower(x)

upper(x)

lpad(x, width, pad_string)

rpad(x, width, pad_string)

ltrim(x, trim_string)

rtrim(x, trim_string)

trim([trim_char], x)

nanvl(x, value)  10g新增函数。判断x是否匹配NaN这个值。

nvl(x, value)

nvl2(x, value, value2)

replace(x, search_string, replace_string)

substr(x, start, length)

 

19.数字函数

abs(x)

acos(x)

asin(x)

atan(x)

atan2(x, y)

bitand(x, y)

cos(x)

cosh(x)

ceil(x)

exp(x)

floor(x)

log(x, y)

ln(x)

mod(x, y)

power(x, y)

round(x, y) 取整操作

sign(x)

sin(x)

sinh(x)

sqrt(x)

tan(x)

tanh(x)

trunc(x, y)

 

20.转换函数

convert(x, source_char_set, dest_char_set)

decode(x, search, result, default)

to_char(x, format)

to_clob(x)

to_date(x, format)

to_timestamp(x)

to_number(x, format)

 

21.正则表达式函数 10g

regexp_like(x, pattern, match_option)

regexp_instr(x, pattern, start, occurrence, return_option, match_option)

regexp_replace(x, pattern, replace_string, start, occurrence, match_option)

regecp_substr(x, pattern, start, occurrence, match_option)

 

22.聚合函数

avg(x)

count(x)

max(x)

median(x) 返回x的中间值

min(x)

stddev(x)

sum(x)

variance(x)

 

23.分组 group by

     过滤分组 having

 

24.日期和时间的处理

to_char(x, format)

to_date(x, format)

YYYY-MM-DD HH24:MI:SS

 

25.修改默认的日期格式

alter session set nls_date_format = 'MONTH-DD-YYYY'

 

26.时间值函数

add_month(x, y)

last_day(x) 返回包含x的月的最后一天

months_between(x, y) 返回x和y之间有几个月

next_day(x, day)

round(x, unit)

sysdate()

trunc(x, unit)

 

current_date() 返回数据库会话设置的本地区中的当前日期。

dbtimezone() 返回数据库的时区

new_time(x, time_zone1, time_zone2)

sessiontimezone() 返回数据库会话的时区

tz_offset(time_zone) 返回time_zone的时差中的小时和分钟

 

27.获取时区名

select * from v$timezone_names;

 

28.时间戳 timestamp 9i

 

29.时间间隔

interval year to month

interval day to second

 

30.查看表结构

desc

 

31.编辑sql

append

change

clear

del

list

run

/

 

32.保存、检索并运行文件

save filename

get filename 打开文件到缓冲区

start filename 打开文件到缓冲区并运行

@ filename 同start filename

edit filename

spool filename 将输出结果复制到filename所指定的文件中

 

33.设置页面大小

set pagesize 100

 

34.设置行大小

set linesize 50

 

35.清除列格式

column column_name clear

 

clear columns 清除所有列的格式化

 

36.临时变量

使用&指定临时变量 (使用&&防止重复输入一个变量)

使用set verify on/off 控制原行和新行的输出。

 

37.修改变量定义字符

set define '#'

 

38.定义变量

define product_id_var = 7

 

39.accept定义和设置变量

accept variable_name type Format format Prompt prompt

 

40.使用undefine命令删除变量

undefine product_id_var

 

41.关联子查询时exists比in的效率要高。

 

42.集合操作

union all 返回各个查询检索出的所有行，包括重复行

union 返回各个查询检索出的所有行，不包括重复行

intersect 返回两个查询检索出的共有行

minus 返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的记录

 

43.translate(x, from_string, to_string) 挺有意思的一函数

 

44.decode()函数和case表达式

case search_expression

 when expression1 then result1

 when expression2 then result2

 ...

 else default_result

end

 

case

 when condition1 then result1

 when condition2 then result2

 ...

 else default_result

end

 

45.创建DB link

1）在服务器的tnsnames.ora中写入相应的配置文件
2）在服务器上创建用户
create user user_remote identified by pwd_remote;
3） 给服务器上的user_local用户授权操作create database link
grant create database link to user_local;
4）服务器上用户user_local创建database link
create database link db_remote connect user_remote identified by pwd_remote using ‘db_remote’;  

// using 'db_remote' 是tnsnames.ora文件中配置的网络服务名。

 

46.事务锁

要支持并发，Oracle通过锁（lock）来确保表中的数据一直有效。

默认加锁机制：读程序（reader）不会阻塞读程序；写程序（writer）不会阻塞写程序；只有在试图对相同的行进行修改时，写程序才会阻塞写程序。

 

47.事务隔离级别

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE