Oracle常用语句(手记)

hti

　　tnsping 192.168.194.156  --windows下连接linux下oracle服务器是否OK
　　lsnrctl status --查看监听器状态
　　lsnrctl start  --启动监听器
　　lsnrctl stop   --关闭监听器
　　startup --打开数据库,等于以下三个命令
　　startup nomount  --非安装启动，这种方式启动下可执行：重建控制文件、重建数据库,启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件
　　alter database mount --安装启动，这种方式启动下可执行：数据库日志归档、数据库恢复、重新命名一些数据库文件。如：系统表空间或日志文件
　　alter database open
　　--关闭数据库
　　shutdown normal;--正常等待用户提交断开
　　shutdown immediate;--不等待用户提交直接断开，没提交的事务回滚
　　shutdown abort;--异常关闭，此种方式下系统并不做任何检查和断开用户操作以及回退操作，而是直接将数据库现场撤销
　　--设置归档方式
　　shutdown immediate;
　　Startup mount;
　　Alter database archivelog;
　　Alter database open;
　　Alter system archive log start
　　archive log list;--查看
　　SELECT instance_name from v$instance;--查看实例名
　　select name from v$database
　　echo $ORACLE_SID--linux下putty
　　SELECT * FROM V$LOGFILE; --显示重做日志文件

　　create user tony>　　grant unlimited tablespace,dba to tony;--授权 (grant sysdba to tony)
　　grant connect,resource,All,dba on scott.dept to zhujh（with Grant option则为级联，Create时为with Admin option）
　　revoke dba from tony;--撤销权限
　　Revoke select on scott.dept from zhujh
　　alter user system account lock;--锁定
　　alter user system account unlock;

　　alter user tony>　　alter user tony default tablespace tbs01;--修改默认表空间
　　create role myrole; grant select on scott.dept to myrole（授权给角色）; grant myrole to tony（用户加入角色）--角色
　　drop user tony;--删除用户
　　--表空间

　　create tablespace tbs02 datafile '/u01/database/tbs02.dbf'>　　autoextend on next 5M maxsize 50M extent management local;
　　--索引表空间

　　SQL> create tablespace tbs_index datafile '/u01/database/tbs_index.dbf'>　　extent management local segment space management auto;
　　--临时表空间

　　create temporary tablespace temp01 tempfile '/u01/database/temp01.dbf'>　　autoextend on next 5M maxsize maxsize unlimited extent management local;
　　drop tablespace tbs02 including contents and datafiles;--删除表空间，包括文件
　　drop tablespace temp01 including contents and datafiles;--删除临时表空间，包括文件
　　alter database datafile '/u01/database/tbs02.dbf' resize 20M;--手动更改数据文件大小

　　alter tablespace tbs02 add datafile '/u01/database/tbs02_1.dbf'>　　--表相关
　　create table t_deptno(id int not null,name char(10));
　　alter table t_deptno move tablespace tbs01;--修改表所在的表空间
　　alter table t_deptno add oper_date date;--增加列
　　alter table t_deptno drop column oper_date;--删除列
　　alter table t_deptno modify (name char(15));--修改列
　　alter table t_deptno rename column name1 to name;--更改列名
　　alter table t_deptno1 rename to t_deptno;--更改表名
　　--序列，相当于SQLServer 的自增id
　　CREATE SEQUENCE seq_test;(默认值)
　　CREATE SEQUENCE SEQUENCE seq_test start with 1 increment by 1;(从1开始，自增1)
　　insert into order_status2 (id) values (seq_test.nextval); --从下一个序列值开始
　　--同义词
　　Create synonym tmp for temp
　　--约束
　　alter table t_deptno add constraint pk_deptno1 primary key (id);--主键
　　alter table t_deptno add constraint uk_deptno unique(id,name) using index tablespace tbs_index; --unique索引，指定索引空间
　　alter table t_deptno disable constraint pk_deptno;--约束失效(enable为有效)
　　alter table t_deptno add constraint c1 check(id>0);--check约束
　　alter table department add constraint FK_fk1 foreign key(id) references t_deptno(no);--外键，其中被引用的主键列必须为unique
　　--常用函数
　　select sysdate from dual;
　　select last_day(sysdate) from dual;--本月最后一天
　　select add_months(sysdate,2) from dual;--当前日期增加月
　　select months_between(sysdate,to_date('20131116','YYYYMMDD')) from dual;--与当前日期相差的月份
　　select next_day(sysdate,1) from dual;--日期，1表示下一个星期日
　　--month 用9个字符表示的月份名,ww 当年第几周,w 本月第几周,DDD 当年第几天,DD 当月第几天,
　　--MM 月份数,hh24 24小时制小时数,Mi 分钟数,ss 秒数
　　select to_char(sysdate,'MM') from dual;
　　select to_char(sysdate,'yyyymmdd') from dual;
　　select to_number(to_char(sysdate,'yyyymmdd')) from dual;
　　Select TO_DATE(’12-3月-04’) from dual;
　　select round(sysdate,'year') from dual;--四舍五入year,month,
　　SELECT round(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM dual;--返回24，23.6，20
　　select trunc(sysdate,'year') from dual;--截断
　　SELECT trunc(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM dual;--返回23，23.5，20
　　select lower('Abc') from dual;--转为小写
　　select upper('Abc') from dual;--转为大写
　　select trim(' abc ') from dual;--去掉空格
　　select ltrim(' abc ') from dual;--去掉左空格
　　select rtrim(' abc ') from dual;--去掉右空格
　　select concat('abc','def') from dual;--连接字符串，返回abcdef
　　select substr('abcdefg',2,3) from dual;--取字串，返回bcd
　　select replace('aacde','a','b') from dual;--替换字符，返回bbcde
　　select length('abcdefg') from dual;--长度，返回7
　　SELECT INSTR('abcdefg','e',1) from dual;--出现的位置，返回5
　　Select sum(decode(sex,’男’,1,0)) 男人数,sum(decode(sex,’女’,1,0)) 女人数 from emp(decode相当于if语句)
　　Select a1,nvl(a2,’未输入’) from aa; (相当于isnull函数)
　　select ceil(10.6) from dual; --大于或等于数值n的最小整数，11
　　select floor(10.6) from dual; --小于或等于数值n的最小整数，10
　　--数据字典
　　select * from dba_users; --查看数据库里面所有用户
　　select * from all_users;  --查看你能管理的所有用户
　　select * from user_users; --查看当前用户信息
　　select text from user_source where type='PROCEDURE' and name='USP_01';--查看存储过程的定义
　　select object_name from all_objects where object_type='TABLE'；
　　select * from all_source where type='TABLE'；
　　select * from user_indexes; --可以查询出所有的用户表索引
　　select table_name from all_tables;--所有数据表
　　select table_name from user_tables;--查看当前用户数据表
　　select name from sys.v_$tablespace;--查看表空间 Select tablespace_name From Dba_Tablespaces;
　　select * from dba_tables where table_name='A' and owner='SYS'; --有条件查询表名放在哪个表空间
　　oracle中的IS和AS
　　a、在存储过程（PROCEDURE）和函数(FUNCTION)中没有区别
　　b、在视图（VIEW）中只能用AS
　　c、在游标(CURSOR)中只能用IS
　　--视图
　　create or replace view uv_dept
　　as
　　select * from t_deptno
　　go
　　--存储过程
　　create Or Replace PROCEDURE test(a in number,b out varchar2) as
　　begin
　　if a>10 then
　　begin
　　b:=’A’
　　end;
　　end if;
　　end;
　　--函数
　　create or replace function fn_sal(empname in varchar2) return number as
　　Result number;
　　begin
　　select sal into Result from emp where ename=empname;
　　return(Result);
　　end;
　　--触发器(2个重要的表old和New)
　　Create or Replace Tregger del_deptid
　　After delete on deptment
　　For each row
　　Begin

　　Delete from emp where>　　End del_deptid;
　　create Or Replace Trigger insert_dept
　　after insert on deptment
　　for each row
　　begin
　　if :old.books_id=’0001’ then
　　RAISE_APPLICATION_ERROR(-2000,’不允许删除！’) （自定义错误 值为-2000~20999）
　　end if
　　insert into emp(eid,ename,id) values(‘121,’Tonyzhang’,new.id);

　　--Update emp set>　　End;
　　-- INSTEAD OF 触发器，以这个触发器的语句代替DML（视图）
　　CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp
　　INSTEAD OF INSERT
　　ON dept_emp
　　FOR EACH ROW
　　DELARE
　　v_temp INT;
　　BEGIN
　　SELECT count(*) INTO v_temp FROM dept
　　WHERE deptno=:new.deptno;
　　IF v_temp=0 THEN
　　INSERT INTO dept(deptno,dname) VALUES(:new.deptno,:new.dname);
　　END IF;
　　SELECT count(*) INTO v_temp FROM emp
　　WHERE empno=:new.empno;
　　IF v_temp=0 THEN
　　INSERT INTO emp(empno,ename,deptno)
　　VALUES(:new.empno,:new.ename,:new.deptno);
　　EEND IF;
　　END;
　　--游标（4个属性 %Found，%ISOpen，%NotFound，%Rowcount）
　　declare cursor mycur IS
　　select * from books;
　　myrecord books%RowType
　　begin
　　open mycur;
　　fetch mycur INTO myrecord;
　　while mycur%FOUND loop
　　DBMS_OUTPUT.PUT_LINE(myrecord.books_id||’,’||myrecord.books_name);
　　fetch mycur INTO myrecord;
　　end loop;
　　close mycur;
　　end;
　　--导入和导出数据
　　EXP System/maneger@test file=d:\Mybak.dmp full=y | owner=(system,sys) | tables=(T1,T2)--备份
　　IMP System/maneger@test file=d:\Mybak.dmp full=y ignore=y | tables=(T1,T2)--还原