常用sql语句(oracle)

1、1. CREATE TABLE test212121 (id varchar2(15), Age number(2) CHECK(age >=18 and age<=65 ), sex char(2) check ( sex=男 or sex= 女), CONSTRAINT test_uk UNIQUE(name);2. ALTER TABLE test ADD(deptno varchar2(8); 增加3. ALTER TABLE test MODIFY (id varchar2(18);修改4. ALTER TABLE test ADD CONSTRAINT test_nn

2、UNIQUE(name);添加限制條件5. ALTER TABLE test DROP CONSTRAINT test_nn; 6. DROP TABLE test; 刪除列: ALTER TABLE TEST DROP (列名)7. CREATE VIEW test_v AS SELECT id,name from test;8. 快速做一个和原表一样的备份表?create table new_table as (select * from old_table); 9. CREATE INDEX test_n1 ON test(name);10. CREATE UNIQUE(所建立的索引所在

3、欄位值必需唯一) INDEX test_u1 ON (id,name);11. DROP INDEX test_n1; DROP VIEW test_v;12. GRANT ALTER ON salary.test TO micpo WITH GRANT OPTION;13.REVOKE INSERT ON salary.test FROM micpo;14.CREATE PUBLIC SYNONYM test FOR salary.test;15.INSERT INTO test(id,name,deptno) VALUES(02705,匡燕輝,26AB0000); UPDATE test

4、SET deptno=26AC0000 WHERE id=16411; DELETE test WHERE id=16163;16.DICT->數據庫字典select * from DICT where table_name LIKE 'ALL%' select * from all_tables -(user_tables,all_tab_columns,all_indexes,all_objuects) 17.ROWNUM->偽列指出從表中檢索數據的次序,它不受ORDER BY子句之影響 select * from ps_employee where name=

5、'彭光明' and ROWNUM=<1;18. ROWID 表示表中記錄的二進制地址,返回ROWID類型之數值.,把ROWID 緊接在select后邊,任意查地址select rowid from ps_employee;19. CURRVAL&NEXTVAL CREATE SEQUENCE order_id START WITH x INCREMENT BY y Maxvalue z ; select order_id.nextval from dual; select order_id.currval from dual;如何配置Sequence?建seque

6、nce seq_custid create sequence seq_custid start with 1 incrememt by 1; 建表时: create table cust cust_id smallint not null, . insert 时: insert into table cust values( seq_cust.nextval, .)20.DISTINCT->select DISTINCT * from ps_employee where name='彭光明'21.COUNT->返回一個集合中當前包含的元素的數量 select COU

7、NT(*) from ps_employee where name='彭光明22.INSTR(string1,string2)->以整數形式返回string1在string2中第一次出現的位置 select * from ps_employee where INSTR(name,'明')<>0;23.EXISTS->用于判斷集合中指定的元素是否存在.(用在數量比較大時) in最多只能包含255個元素 select * from ps_employee whereEXISTS (select * from ps_department where d

8、eptno='2ABB0200' and ps_department.deptno=ps_employee.deptno);24.DECODE->類似于一系列的IF-THEN-ELSE語句select DECODE(to_char(sysdate,'d'),1,'一',2,'二',3,'三', 4,'四',5,'五',6,'六',null) from dual25.BETWEEN.AND. ORDER BY ->select deptno,empno,na

9、me,ard_date from ps_employee where ard_date BETWEEN '01-Jul-02' AND '03-Jul-02'ORDER BY 1,2 desc; (01-Jul-02 和03-Jul-02不能交換)26.FROM-select a.deptno,a.short_name,b.num from ps_department a, (select deptno,count(*) num from ps_employee where status='1' group by deptno) b where

10、b.deptno=a.deptno and b.num > 10027.GROUP BY,HAVING->分組函數(AVG,COUNT,MAX,MIN,SUM需與Group by一起使用)select site, MIN(ard_date), MAX(ard_date) from ps_employee where status = '1' GROUP BY site HAVING MIN(ard_date)<'01-Jan-02'28.UNION(并)INTERSECT(交)MINUS（差）連結起來要是有重複紀錄,只顯示一條select em

11、pno,name,ard_date from ps_employee where ard_date='01-Jul-02' UNIONselect empno,name,ard_date from ps_employee where ard_date='10-Jul-02'29.IN ( in的優先級比union高) select empno,name,ard_date from ps_employee where ard_date in ('01-Jul-02','10- Jul-02')30.FOR UPDATE(NOWAIT

12、,WHERE CURRENT OF cursor) ->鎖定 select a.empno,b.deptno from ps_employee a,ps_department b where b.deptno=a.deptno and b.deptno='2ABB0000' FOR UPDATE (NOWAIT);31.FOR UPDATE OF->后邊跟的是具體的栏位名 select a.empno,b.deptno from ps_employee a,ps_department b where b.deptno=a.deptno and b.deptno=&#

13、39;2ABB0000' FOR UPDATE OF name32.(+)->select a.empno,,a.duty_kind from ps_employee a,ps_adjust b where a.empno=02705 and b.empno(+)=a.empno表示b接在a后邊的部份有可能空 33.RPAD(LPAD)->空的補上select RPAD(to_char(sysdate,'yyyymmdd'),12,'aa') from dual34.REPLACE(替代前後字母位數可以不等) (TRAN

14、SLATE)(替代字母位數必須相等) select REPLACE('JACK and JUE','J','BL') "Changes" form dual select TRANSLATE('ABCDEF','ABC','123') from dualLTRIM(S1,S2)刪除從左邊起出現在S1中的S2,遇到不包含S2的第一個字母,S2不寫系統默認為單個空格,結果就被返回select LTRIM('AASSSQAS','AS') from du

15、al/QAS 35.UPPER(LOWER)à將字符串轉換為大寫(小寫)select UPPER(jane.kuang) from dual; select LOWER(JANE.KUANG) from dual;INITCAP(S)返回字符串S中每個單詞第一個字母大寫,其餘小寫SUBSTR(xx,a,b)à返回字符串的一部分,假如省略b,系統默認返回全部,a不能省略 select SUBSTR(jane.kuang,1,4) from dual;INITCAPà第一個字母轉為大寫 select INITCAP(jane.kuang) from dual

16、;CONCATà連接兩個指定字符select CONCAT(MY ,NAME) name from dual;36.數字函數ABS(x)->取絕對值 select ABS(-7),ABS(7) from dual; CEIL(x)->取大於或等於x最小整數值 select CEIL(18.1),CEIL(-18.1) from dual;19,-18 FLOOR(x)->取小於或等於x大整數值select FLOOR(-23.5),FLOOR(23.5) from dual MOD(x,y)->x/y的余數,如果y是0,則返回x|

17、select MOD(23,5),MOD(4,1.3) from dual ROUND(x,y)->取舍入到小數點右邊y位的x值, Y可不寫默認為0 select ROUND(1.56),ROUND(1.56,1),ROUND(12.34,-2) from dualround(date)：把日期四舍五入round(25-MAY-95,MONTH)=01-JUN-95round(25-MAY-95,YEAR)=01-JAN-95,LOG(M,N)得到以M為底N的對數.SELECT LOG(2,8) FROM DUAL;-結果是3TRUNC(x,y)à返回剪裁到指定位置Y

18、后X的值(Y可不寫默認為0,將X裁剪為整數) select trunc(123.456),trunc(123.456,1),trunc(123.456,-1) from dual;trunc(date)：把日期截断trunc (25-MAY-95,MONTH)=01-MAY-95trunc (25-MAY-95,YEAR)=01-JAN-95POWER(M,N)得到M的N冪.SELECT POWER(2,3) FROM DUAL;8SIGN(N)當N<0時,得到-1;當N>0時,得到1;當N=0時,得到0;37.日期函數ADD_MONTHS->給指定的日期加上n個月 sele

19、ct ADD_MONTHS('01-JUL-02',1) from dual  LAST_DAY(SYSDATE)->返回給定月份的最后一天 select LAST_DAY(SYSDATE) from dual  MONTHS_BETWEEN(x,y)->返回兩個日期之間的月份差,即x-y的值 select MONTHS_BETWEEN('01-JUL-02','01-AUG-02') from dual NEXT_DAY(sysdate,x)à返回指定日期后的下一星期几的日期,1=<x=<7

20、 select NEXT_DAY(sysdate,1) from dual;38.轉換函數(TO_CHAR,TO_DATE,TO_NUMBER)TO_CHAR->日期轉字符 select TO_CHAR(sysdate,'yyyymmdd hh24:mi:ss') from dual select TO_CHAR(sysdate,'d') from dual -一周中的星期几 select TO_CHAR(sysdate,dd) from dual -一月中的几天 select TO_CHAR(sysdate,'ddd') from dua

21、l -一年中的几天 select TO_CHAR(sysdate,'DY') from dual -天的簡寫名 select TO_CHAR(sysdate,'IW') from dual -一年中的几周 select TO_CHAR(sysdate,'W') from dual -該月的第几周 數字轉字符 select TO_CHAR(12,'9999.99') from dual -每個9表示了結果中的一個有效位,所有前導的零都是空格select TO_CHAR(12,'0009.99') from dual

22、-前導的零會被顯示0012.00select TO_CHAR(12,'999d99') from dual -D為小數點12.00select TO_CHAR(12,'s9999.99') from dual -S為正數加+,負數加減號+12.0039.LEAST/GREATEST返回參數中最小值 SELECT LEAST/GREATEST(ABCD,abcd,a) from dualABCD/abcdVSIZE()返回VALUE的內部表示字節數SELECT VSIZE(ABCD) from dual/440.其它函數(GREATEST,LEAST,NVL,US

23、ER,UID,USERENV,VSIZE)GREATEST->返回最大值select GREATEST(10,-7,-8,11) from dualLEAST->返回最小值select LEAST(10,-7,-8,11) from dualNVL(EXPR1,EXPR2)若EXPR1是NULL,則返回EXPR2,否則返回EXPR141. COMMIT與ROLLBACK語句SAVEPOINT 將一個大的事務分成很多小塊,每一個小塊作為一個保存點42,外鍵 ALTER TABLE emp91506 ADD CONSTRAINT pk_emp foreign key(deptno) r

24、eferences DEPT91506(deptno)43.如何修改一张表的主键?alter table aaa drop constraint aaa_key ;alter table aaa add constraint aaa_key primary key(a1,b1) ;44子查詢(1)select lpad(' ',(level-1)*2,' ')|ename ename,prior ename manager(顯示父節點),sys_connect_by_path(ename,-) path(顯示路徑(9i以後版本) from emp91506 st

25、art with ename='BLAKE' OR ENAME='JONES' connect by prior empno=MGR and level<=3(可無) order sibling by ename(按照字母順序排序(9i以後版本)9i以後版本可以直接連接select lpad(' ',(level-1)*8,' ')|a.ename ename,dname from emp91506 a, dept91506 b where a.deptno=b.deptno start with mgr is null connect by mgr=prior empno;9i以前版本select e.ename,dname from dept91506 a, (select h.* from(select lpad(' ',(level-1)*8,' ')|b.ename ename,deptno from emp91506 b start with mgr is null connect by mgr=prior empno) h)e where e.deptno=a.deptno45. 如果修改表名?alter table old_table_name re