oracle常用命令积累

一、创建
1、创建表
create table new_protectiontcm(
<列名> <类型> NOT NULL，
<列名> <类型> NOT NULL,
constraint pk_名称 primary key(<列名>)
)
2、创建外键
alter table <要创建外键的表名>
add constraint fk_名称  foreign key(要创建外键的表的字段) references  <要关联的表名> (要关联的表名的字段) on delete cascade;
on delete cascade：当被参照的数据被删除是，参照该数据的那些数据的对应值将会删掉
on delete set null：当被参照的数据被删除是，参照该数据的那些数据的对应值将会变为空值，不能有not null约束，否则将会发生：ORA-01407: 无法更新

3、创建表空间
create tablespace 表空间名称 datafile '/ora/oracle/app/oracle/oradata/orcl/dev01.dbf' size 10240M;

create tablespace 表空间名称 datafile '/ora/oracle/oradata/orcl/dw01.dbf' size 3072M;--增加表空间 
4、创建用户
create user dw identified by dw2013_ora106 default tablespace dw;
grant resource,connect,dba,EXECUTE ANY PROCEDURE,sysdba to dw;--赋予权限
alter user dev identified by devPassword;--修改用户密码


二、修改（增删改）
1、修改表名
alter table <表名1> rename to <表名2>;--修改表名;
2、修改表字段
alter table <表名> rename colum <old列名> to <new列名>;--修改表中列名
alter table <表名> modify <列名> <类型>;--修改字段类型
alter table <表名> add <列名> <类型>;--增加字段
alter table <表名> drop COLUMN <列名>;--删除字段
ALTER TABLE OLD_TABLE_NAMERENAME TO NEW_TABLE_NAME;(大写为系统命令)
alter table cmh_brands move tablespace SINOHEALTH_DATA; --修改表空间
alter index PK_II_ID rebuild tablespace SINOHEALTH_DATA; --修改对应的索引

alter table user__ modify join_meeting_flag default 0; --修改默认值
3、修改数据
update area set areaid = 48 where areaid = 49

4、删除表
DROP TABLE  <表名>;
drop table  <表名> cascade constraints;--当表中有关联外键，删掉表也不会影响关联表的数据

oracle删除用户  最快的办法是：
conn /as sysdba
delete from user$ where name='dzysc';
commit;
alter system flush shared_pool;

oracle 没有方法批量删除表
select 'drop table '||tname|| ';' from tab
where lower(tname) like 'mdm%' --查询以MDM开头的表
select 'drop table '||table_name|| ';' from user_tables; -- 当前用户下面全部表

三、插入

update <表名> set <列名> = <值/字符>;--把字段的所有记录都改为同一个值或者字符
INSERT INTO BOOK(bookid,name,price)   VALUES('100123','oracle sql',54.70);
INSERT INTO BOOK                         VALUES('100123','oracle sql',54.70);
INSERT INTO BOOK(bookid)           VALUES('100123');

update 表名 set 列名=replace(列名 , '?' ,'')把问号替换成空就可以了

四、查询
select sid,serial#,username,program,machine,status,logon_time from
v$session
where username = 'DW'--产科当前用户被锁事件

select spid, osuser, s.program,s.sql_address,q.sql_text
from v$process p, v$session s ,v$sql q
where p.addr=s.paddr
and s.sql_address=q.address
and s.sid=&SID

alter system kill session 'sid,serial#';

dbms_output.put_line () 相当于 java 的System.out.println()

select table_name from user_tables;--查看当前用户所有表

select * from dba_role_privs where granted_role='DBA'--查看dba角色的权限

--当用户被锁
alter user dev account unlock;
用system用户进去或者sqlplus "/as sysdba"进去也行


五、序列
select preproject_seq.next.nextval from dual；--当前表最后一个序列号

DROP SEQUENCE "PREPROJECT_SEQ";

CREATE SEQUENCE "PREPROJECT_SEQ"
INCREMENT BY 1 --递增为1
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1593 --从第几个开始
CACHE 10;

建立数据表

create table Test_Increase(
           userid number(10) primary key,  /*主键*/
           username varchar2(20)
           );

2创建自动增长序列
CREATE SEQUENCE TestIncrease_Sequence
INCREMENT BY 1   -- 每次加几个 
     START WITH 1     -- 从1开始计数 
     NOMAXVALUE       -- 不设置最大值 
     NOCYCLE          -- 一直累加，不循环 
     CACHE 10;

3创建触发器
CREATE TRIGGER Test_Increase BEFORE
insert ON  Test_Increase FOR EACH ROW
begin
select TestIncrease_Sequence.nextval into:New.userid from dual;

end;

六、常用函数
substr( string, start_position, [ length ] )
       substr('This is a test', 6, 2)     would return 'is'
     substr('This is a test', 6)     would return 'is a test'
     substr('TechOnTheNet', -3, 3)     would return 'Net'
     substr('TechOnTheNet', -6, 3)     would return 'The'

length(字段名) 统计字符长度
lengthb(字段名)    求得是字节长度

replace 字符串级别的代替
replace(字段名,原来内容,替换内容)
replace(cm.medical_name,'(溶媒结晶)','')

translate 字符级别的代替
select TRANSLATE('kkaxksx', 'kx', '12') from dual
结果：11a21s2


instr(字段名,含有内容)<或>或= 长度
instr(字段名,字段名)
case when 条件 then 结果1 else 结果2 end
to_char(对象,格式) 例如 to_char(riqi,'yyyy-MM-dd')
regexp_substr(字段名,匹配正则)
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

nvl(字段名,0) 如果字段名为null返回0
nvl2(字段名,'a','b')
如果第一个参数为null，则返回第三个参数
如果第一个参数为非null，则返回第二个参数

sign（number） 函数返回一个数字的正负标志
If number < 0, then sign returns -1.
If number = 0, then sign returns 0.
If number > 0, then sign returns 1.

sign(-23) would return -1
sign(0) would return 0
sign(0.001) would return 1

trunc( number, [ decimal_places ] )
trunc function returns a number truncated to a certain number of decimal places.
number 要截取的数字.
decimal_places 要保留的小数位. 这个参数必须是个整数. 如果此参数缺省，默认保留0位小数
trunc(-125.815, 2) would return -125.81
trunc(125.815) would return 125
trunc(125.815, 0) would return 125

abs(number) 返回数值的绝对值 如abs(-5) 返回 5

ceil(number)   即   ceil(数值）
根据输入值返回一个数值，输入参数可以是非整数，但返回结果则是大于等于输入参数的最小整数。
例：   ceil(5.1)   返回  6
       abs(-5.2)   返回  -5

row_number() over(order by a.register_time asc)
排序

日期格式 用 to_date()
to_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss')

floor() 去整数非四舍五入
round(字段, 小数位) 保留小数
dbms_random.value(30,50)取得随机数



七、注意
Oracle中like查询下划线等特殊字符的处理
ESCAPE Clause Example

You can include the actual characters "%" or "_" in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character.

To search for employees with the pattern 'A_B' in their name:

SELECT last_name
    FROM employees
    WHERE last_name LIKE '%A\_B%' ESCAPE '\';

The ESCAPE clause identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.

制表符 chr(9)
换行符 chr(10)
回车符 chr(13)

在Oracle中特殊字符是可以通过转义实现的，在Oracle10g中可以这样
实现，
比较好用的方法是用这样的一个方法：q'{}'.比如：
select q'[@#%&&&]' from dual;
得到的结果就是中括号中的内容，原样输出，里面什么字符都可以包括
。我这里强调的是q'{}'也是可以的。意思是说这个两个单引号中的的符
号是很灵活的，只要成对就行，常用的是{}或者是[],他们中间的内容回
原样输出，感兴趣的同志可以试试！
select q'[!@#$%^&*()]' from dual;
得到的结果就是：
  !@#$%^&*()