oracle注意点

oracle学习注意点

概念

• orcal只有一个库，多个方案（schema）
• 一个用户对应一个方案，方案的名称和用户名称完全相同，且肯定都存在
• 访问数据库对象的时候，通常采取的是“方案名.对象名”的方式来访问(scott.emp)
• 创建用户后grant(授权)/revoke(移除权限)，分配限额

数据类型

char(size) 定长字符，≤2000个字节（查询性能优于变长verchar！！）
varchar2(size) 变长字符，≤4000个字节
date 日期数据，默认的格式是dd-mon-yy：如11-6月-06
timestamp 日期数据，要比date数据更确切，包含时分秒。
number[(s,p)] 数字型，可存放实型和整型 ,精度( p)和范围(s)，整数位=范围(s)-精度( p)
blob 存放图形、声音和影像，大二进制对象，≤4GB
clob 存放大文本文件，≤4GB

sql语句

• 语句大小写不敏感
• 单引号 ： 定义字符串类型的内容时，比如select之后，where之后等
• 双引号 ： 只有在定义”别名”时使用，针对有空格和下划线等特殊情况
• null值不参与运算，会返回空值
• 字符串连接符：CONCAT(char c1, char c2, …, char cn) 和 ‘c1’ || ‘c2’
• sqlplus developer查看的日期是被修改过的，可以通过命令查看原格式（dd-mon -yy 如：01-jan -99表示1999/01/01）

字符函数

• LOWER (strexp) 返回字符串,并将所有的字符小写
• UPPER (strexp) 返回字符串,并将所有的字符大写
• INITCAP(strexp) 将字符串的(每个单词的)第一个字母变为大写,后面的小写;
• CONCAT(strexp, strexp): 连接两个字符串
• Substr(str,start_index,length) :从指定的位置截取指定长度的字符串；substr(str, -3),截取最后三个字符
• LENGTH(strexp):返回字符串的长度
• LPAD( string1, padded_length, [ pad_string ] ) 在string1的左边粘贴padded_length-length(string1)个pad_string 字符，最后返回padded_length个长度的字符串
  1. padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，则相当于substr函数，对string1做截取
  2. pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。
• RPAD(粘贴字符) 在string1的右边粘贴padded_length-length(string1)个pad_string 字符，最后返回padded_length个长度的字符串
• Trim():截取字符串两端特殊字符
  1. Trim(’-’ from ‘–hello-world–’);可以指定从字符串两边要截取的特殊字符,此处为’-’

数字函数

ROUND( number, decimal_places ) 四舍五入，小数保留decimal_places（正数） 位，decimal_places为负数时，对整数四舍五入
TRUNC( number, decimal_places ) 截取decimal_places（正数） 位小数，decimal_places为负数时，对整数截取，补0
mod（number1，number2）两个数值相除并返回其余数

转换函数

TO_CHAR(number, ‘fmt’) 数字转字符串
TO_NUMBER(char [, ‘fmt’])) 字符串转数字，无特殊符号（如$）时可以不写fmt

**fmt 的格式如下**

TO_CHAR(date, ‘fmt’) 日期转字符串
TO_DATE(char [, ‘fmt’])) 字符串转日期，无特殊字符（如中文）时可以不写fmt

**fmt 的格式如下**

必须用单引号括起来,并且是大小写敏感,中文加双引号，
如：select sysdate,to_char(sysdate,‘YYYY"年"mm"月"DD"日"’) from dual;

**日期 的格式如下**

日期函数

add_months(date_value,number_of_months) 从一个日期值增加或减少一些月份
current_date 返回当前会话时区中的当前日期
last_day(date_value) 返回指定日期中的月份的最后一天的日期
months_between(f,s) 日期f和s间相差月数

其他函数

NVL (expr1, expr2) expr1为空，则输出expr2，expr1和expr2数据类型必须一样
NVL2 (expr1, expr2, expr3) expr0不为Null,返回expr1, 为Null,返回expr2，expr0可以为任何数据类型，但是expr1和expr2数据类型必须一样
COALESCE (expr1, expr2, …, exprn) 依次判断，返回第一个非空的值

case表达式和DECODE函数

CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

select 
	case NVL(age,0)/20
		when 0 then '青年'
		when 1 then '中年'
		else '老年'
	end "阶段"
from tableName;

DECODE函数类似于一系列CASE
DECODE(col/expression, search1, result1 [, search2, result2,…,] [, default])

select 
	decode(NVL(age,0)/20,
		0 ,'青年',
		1 ,'中年',
		'老年')"阶段"
from tableName;

分组函数

不会计算空值，计算空值方式：avg(nvl(field,0))
AVG ([DISTINCT|ALL]n)
COUNT ({ *|[DISTINCT|ALL]expr})
MAX ([DISTINCT|ALL]expr)
MIN ([DISTINCT|ALL]expr)
SUM ([DISTINCT|ALL]n)

分页

注意：

• rownum放在order by之后，否则会乱序
• where后使用rownum伪列，只能使用<或者<=或者=符合

按工资从高到底查询员工信息，每页10条
分析：
1、查询所有员工，按照工资进行降序排序
2、使用rownum(伪列)方式，为查询结果添加序号
3、将rownum转变为实列，通过大于小于选取数据

select rs.* from  --伪列转实列
  (select rownum sn,t.last_name,t.salary
    from
      (select last_name,salary 
      from employees
      order by salary desc) t
   ) rs
 where rs.sn>=11 and rs.sn<=20

拷贝表

拷贝表和数据
create table table_copy as (select * from table )
只拷贝表结构
create table table_copy as (select * from table  where 1=2)

Union语句

union 不允许重复值
union all 允许重复值，性能高

悲观锁

语法：select … for update
悲观锁会阻止另一个悲观锁或者 update语句
但是悲观锁不会阻止普通的查询

恢复删除表

drop 的表在oracle垃圾箱中仍然可以找回

SELECT original_name,object_name, droptime FROM user_recyclebin;

original_name为原名