oracle pl/sql学习笔记（一） 入门

imjtofvqop

--第一范式：（1NF）无重复的列
--第二范式：（2NF）属性完全依赖于主键
--第三范式：（3NF）属性不依赖于其它非主属性

--修改列
alter table 表名 drop column 列名;
alter table 表名 rename column 列名 to 新列名;
alter table 表名 add (列名 类型);
alter table 表名 modify (列名 类型);

--转移表至其他表空间
alter table 表名 move tablespace 表空间名;


--建表语句里面的子查询
create table 表名 as select * from 表名 where XXX
如果where 1<>1只获取表结构不获取数据

--插入语句中的子查询
insert into 表名 select * from 表名 where XXX

--联合语句
union  union all 并集
intersect        交集
minus            差集
混合运算中，优先级相同

自然连接   natural join
外连接简写(ORACLE专有) where a.xx=b.xx(+) b表为附属表

--层次化查询
select xx from XX start with son_name = XX connect by prior parent_id = son_id;

start with 指定起始条件   connect by 指定获得吓一跳记录的条件

sys_connect_by_path(列名,分隔符)  对起始位置到当前记录之间的结果集进行聚合操作，配合层次化查询使用

例子：
select max(sys_connect_by_path(column_name, '/')) market_path                                                                                    
from tablename                                                
start with column_name = XX                              
connect by prior parent_id = son_id;   --max()获取查出的结果集中排在末位位置的字符串

--二进制转十进制
select bin_to_num(1,1,1,1) from dual;

select sum(data)
  from (select substr('1111', rownum, 1) * power(2, length('1111') - rownum) data
          from dual
        connect by rownum <= length('1111'))

--十进制转二进制
select replace(reverse(max(sys_connect_by_path(mod(floor(15/power(2, rownum-1)), 2), '/'))), '/', '') data
from dual
connect by floor(15/power(2, rownum-1))>=1
order by rownum desc

--批量插入
insert into 表名(列名a,列名b,...) select 列名a,列名b,... ,from 表名 where XXX

--字符型
varchar 可变长度字符  占用空间不固定
char 固定长度字符，占用空间固定，效率较高

lpad(string,padded_length,[pad_string])  向左补全字符串
rpad(string,padded_length,[pad_string])  向右补全字符串
lower()
upper()
initcap() 单词首字符大写
length()
substr(string,start_index,length)
instr(string,sub_string,[start_index],[times]) 字符串出现的位置
ltrim() rtrim() trim()
concat(string,string) 注：concat只能连接两个参数的字符串
translate(被翻译字符串a,翻译字符串b,对应密码字符串c) 找a中字符在b中的位置，取c相应位置的字符
reverse() 翻转单字节字符串内容

--数值型
abs() 绝对值
round()  四舍五入
ceil() 向上取整
floor() 向下取整
mod() 取余（取模）
sign() 整数返回1，负数返回-1，0返回0
sqrt() 平方根
power(底数,指数)
trunc(number,小数点后位数) 截取数字,其余舍去
chr() ASCII码转换为字符
to_char(原数值,格式) --格式符 0 不匹配时强制添加0  9 不匹配时不添加0（但小数位还是强制添加） FMXX.XX FM格式符可以屏蔽所有不必要的空格和0  $货币符号 L本地货币符号

字符串与数值的比较计算中，字符串首先被转换为数值型，然后在进行比较

--日期型
sysdata
add_month(时间,增加的月数)
last_day() 日期缩在月最后一天
month_between()返回两个日期所差的月数
next_day(date,范围0~7的数字) 特定日期之后一周内的日期
trunc(date,格式符) 截取时间
current_date current_timestamp 当前时区的当前时间