有用的sql语句.docxVIP

select id, name, class, scro ?? from (select row_number() over(partition by class order by scro desc)? cnt, ??????????????? id, ??????????????? name, ??????????????? class, ??????????????? scro ?????????? from student) a ? where t = 3; 查找排序后的前三行? select * ? from (select rw.*, rownum ????????? from (select * ????????????????? from student d ???????????????? where d.class = b ???????????????? order by d.scro desc) rw ???????? where rw.id = 1 ???????? order by rw.class desc) n where rownum = 3 表复制insert into table_a (id,name,age) select b.id,,b.age from table_b; --删除表数据的触发器CREATE OR REPLACE PROCEDURE delete_data IS BEGIN ?? delete from test ; ?? COMMIT; EXCEPTION ?? WHEN NO_DATA_FOUND ?? THEN ????? NULL; ?? WHEN OTHERS ?? THEN ????? RAISE; END delete_data; --定时删除? 每隔5分钟执行一次的计划DECLARE ? X NUMBER; BEGIN ? SYS.DBMS_JOB.SUBMIT ? ( job?????? = X ?? ,what????? = delete_data; ?? ,next_date = to_date(25/08/2008 00:00:00,dd/mm/yyyy hh24:mi:ss) ?? ,interval? = sysdate+1/24/12 ?? ,no_parse? = FALSE ? ); ? SYS.DBMS_OUTPUT.PUT_LINE(Job Number is: || to_char(x)); COMMIT; END; --查看当前oracle计划select job,next_date,next_sec,failures,broken from user_jobs; --删除一个job begin dbms_job.remove(46);--46为job号end; --给新表插入旧表对应字段的所有数据insert into aaa(id,name) select b.id, from bbb; 文章有哪些信誉好的足球投注网站：???? 【点击打包该文章】【到本站论坛,与同行交流】select * from all_users;??????????????? ##查看所有用户select name from v$database;???????????? ##查看当前数据库database test;??????????????????? ##进入test数据库select * from v$instance;??????????????? ##查看所有的数据库实例shutdown immediate??????????????? ##关闭数据库alter user sys identified by new_password;??????? ##更改用户密码select username,password from dba_users; ##查看当实例中的用户和密码show parameter control_files;?????????? ## 查看控制文件; select member from v$logfile;??????? ##查看日志文件show parameter ;???????????????? ## 查看数据库参数select * from user_role_privs;??????? ##查看当前用户的角色select username,default_tablespace from user_users; ##查看当前用户的缺省表空间alter user system identified by [password]??????? ##修改用户的密码ALTER USER SCOTT ACCOUNT UNLOCK?????