drop tablespace tablespacename [including contents [and datafiles]]
2．删除用户
drop user user_name cascade
3．删除表的注意事项
在删除一个表中的全部数据时，须使用truncate table 表名;因为用drop table，delete * from 表名时，tablespace表空间该表的占用空间并未释放，反复几次drop，delete操作后，该tablespace上百兆的空间就被耗光了。
4．having子句的用法
having子句对group by子句所确定的行组进行控制，having子句条件中只允许涉及常量,聚组函数或group by 子句中的列。
5．外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢
用外联接提高表连接的查询速度
在作表连接(常用于视图)时，常使用以下方法来查询数据:
select pay_no, project_name from a where a.pay_no not in (select pay_ no from b where value >=120000);
select a.empno from emp a where a.empno not in (select empno from emp1 where job=’sale’);
倘若利用外部联接,改写命令如下:
select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job=’sale’;
可以发现,运行速度明显提高.
8．从已知表新建另一个表：
create table b as select * (可以是表a中的几列) from a where a.column = ...;
9．查找、删除重复记录:
法一: 用group by语句 此查找很快的
select count(num), max(name) from student --查找表中num列重复的，列出重复的记录数，并列出他的name属性 group by num having count(num) >1 --按num分组后找出表中num列重复，即出现次数大于一次 delete from student(上面select的)
这样的话就把所有重复的都删除了。-----慎重
法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法:
---- 执行下面sql语句后就可以显示所有drawing和dsno相同且重复的记录
select * from em5_pipe_prefab where rowid!=(select max(rowid) from em5_pipe_prefab d --d相当于first,second where em5_pipe_prefab.drawing=d.drawing and em5_pipe_prefab.dsno=d.dsno);
---- 执行下面sql语句后就可以刪除所有drawing和dsno相同且重复的记录
delete from em5_pipe_prefab where rowid!=(select max(rowid) from em5_pipe_prefab d where em5_pipe_prefab.drawing=d.drawing and em5_pipe_prefab.dsno=d.dsno);
10．返回表中[n，m]条记录：
取得某列中第n大的行
select column_name from (select table_name.*,dense_rank() over (order by column desc) rank from table_name) where rank = &n；
假如要返回前5条记录：
select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)
假如要返回第5-9条记录：
select * from tablename where … and rownum<10 minus select * from tablename where … and rownum<5 order by name
选出结果后用name排序显示结果。(先选再排序)
注意：只能用以上符号(<、<=、!=)。
select * from tablename where rownum != 10;返回的是前９条记录。
decalre file_handle utl_file.file_type; begin file_handle:=utl_file.fopen( ‘c:/’,’test.txt’,’a’); utl_file.put_line(file_handle,’ hello,it’s a test txt file’); utl_file.fclose(file_handle); end;
create or replace type strings_table is table of varchar2(20); / create or replace function merge (pv in strings_table) return varchar2 is ls varchar2(4000); begin for i in 1..pv.count loop ls := ls || pv(i); end loop; return ls; end; / create table t (id number,name varchar2(10)); insert into t values(1,'joan'); insert into t values(1,'jack'); insert into t values(1,'tom'); insert into t values(2,'rose'); insert into t values(2,'jenny'); column names format a80; select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names from (select distinct id from t) t0; drop type strings_table; drop function merge; drop table t;
this example uses a max of 6, and would need more cut n pasting to do more than that： sql> select deptno, dname, emps 2 from ( 3 select d.deptno, d.dname, rtrim(e.ename ||', '|| 4 lead(e.ename,1) over (partition by d.deptno 5 order by e.ename) ||', '|| 6 lead(e.ename,2) over (partition by d.deptno 7 order by e.ename) ||', '|| 8 lead(e.ename,3) over (partition by d.deptno 9 order by e.ename) ||', '|| 10 lead(e.ename,4) over (partition by d.deptno 11 order by e.ename) ||', '|| 12 lead(e.ename,5) over (partition by d.deptno 13 order by e.ename),', ') emps, 14 row_number () over (partition by d.deptno 15 order by e.ename) x 16 from emp e, dept d 17 where d.deptno = e.deptno 18 ) 19 where x = 1 20 /
create sequence checkup_no_seq nocycle maxvalue 9999999999 start with 2;
2、建立触发器：
create or replace trigger set_checkup_no before insert on checkup_history for each row declare next_checkup_no number; begin --get the next checkup number from the sequence select checkup_no_seq.nextval into next_checkup_no from dual;
--use the sequence number as the primary key --for the record being inserted :new.checkup_no := next_checkup_no; end;
17．查看对象的依赖关系(比如视图与表的引用)
查看视图：dba_dependencies 记录了相关的依赖关系
查东西不知道要查看哪个视图时，可以在dba_objects里看，
select object_name from dba_objects where object_name like '%role%'(假如查看role相关)
然后desc一下就大体上知道了。
18．要找到某月中所有周五的具体日期
select to_char(t.d,'yy-mm-dd') from ( select trunc(sysdate, 'mm')+rownum-1 as d from dba_objects where rownum < 32) t where to_char(t.d, 'mm') = to_char(sysdate, 'mm') --找出当前月份的周五的日期 and trim(to_char(t.d, 'day')) = '星期五'
