Oracle基礎(chǔ)語(yǔ)言輔導(dǎo).doc

基本Oracle語(yǔ)言一、 create(創(chuàng)建)1創(chuàng)建表創(chuàng)建表CREATE TABLE ( column1 DATATYPE NOT NULL PRIMARY KEY, column2 DATATYPE NOT NULL,.constraint 約束類型 (要約束的字段). ）說(shuō)明：DATATYPE -是Oracle的數(shù)據(jù)類型,可以查看附錄。NUT NULL -可不可以允許資料有空的（尚未有資料填入）。PRIMARY KEY -是本表的主鍵。constraint -是對(duì)表里的字段添加約束.(約束類型有 Check,Unique,Primary key,not null,Foreign key)。示例:create table stu(s_id number(8) PRIMARY KEY,s_name varchar2(20) not null,s_sex varchar2(8),clsid number(8),constraint u_1 unique(s_name),constraint c_1 check (s_sex in (MALE,FEMALE);復(fù)制表CREATE TABLE as (需注意的是復(fù)制表不能復(fù)制表的約束);示例:create table test as select * from emp;如果只復(fù)制表的結(jié)構(gòu)不復(fù)制表的數(shù)據(jù)則:create table test as select * from emp where 1=2;2創(chuàng)建索引創(chuàng)建索引CREATE UNIQUE INDEX ON (字段 ASC|DESC);UNIQUE -確保所有的索引列中的值都是可以區(qū)分的。ASC|DESC -在列上按指定排序創(chuàng)建索引。(創(chuàng)建索引的準(zhǔn)則：1.如果表里有幾百行記錄則可以對(duì)其創(chuàng)建索引(表里的記錄行數(shù)越多索引的效果就越明顯)。2.不要試圖對(duì)表創(chuàng)建兩個(gè)或三個(gè)以上的索引。3.為頻繁使用的行創(chuàng)建索引。) 示例create index i_1 on emp(empno asc);3創(chuàng)建同義詞CREATE SYNONYM for 同義詞即是給表或視圖取一個(gè)別名。示例:create synonym mm for emp;二、 Alter(修改)1修改表修改表1.向表中添加新字段ALTER TABLE ADD (字段1 類型 NOT NULL,字段2 類型 NOT NULL. );2.修改表中字段ALTER TABLE modify(字段1 類型,字段2 類型. );3 .刪除表中字段ALTER TABLE drop(字段1,字段2. );4 .修改表的名稱RENAME to ;5 .對(duì)已經(jīng)存在的表添加約束ALTER TABLE ADD CONSTRAINT 約束類型 (針對(duì)的字段名);示例：Alter table emp add constraint S_F Foreign key (deptno) references dept(deptno); 6 .對(duì)表里的約束禁用;ALTER TABLE DISABLE CONSTRAINT ;7 .對(duì)表里的約束重新啟用;ALTER TABLE ENABLE CONSTRAINT ;8 .刪除表中約束ALTER TABLE DROP CONSTRAINT ;示例:ALTER TABLE emp drop CONSTRAINT ;三、 Drop（刪除）1刪除表刪除表DROP TABLE ;示例drop table emp;2刪除索引刪除索引DROP INDEX ;示例drop index i_1;3刪除同義詞刪除同義詞DROP SYNONYM ;示例drop synonym mm;四、 數(shù)據(jù)操縱語(yǔ)言1插入記錄INSERT INTO table_name (column1,column2,.)values ( value1,value2, .); 示例insert into emp (empno,ename) values(9500,AA);把 一個(gè)表中的數(shù)據(jù)插入另一個(gè)表中INSERT INTO 示例create table a as select * from emp where 1=2;insert into a select * from emp where sal2000;2查詢記錄查詢記錄一般查詢SELECT DISTINCT FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC DISTINCT -表示隱藏重復(fù)的行WHERE -按照一定的條件查找記錄GROUP BY -分組查找(需要匯總時(shí)使用)HAVING -分組的條件ORDER BY -對(duì)查詢結(jié)果排序要顯示全部的列可以用*表示示例：select * from emp;WHERE 語(yǔ)句的運(yùn)算符where AND -兩個(gè)條件都滿足示例：select * from emp where deptno=10 and sal1000;where OR -兩個(gè)條件中有一個(gè)滿足即可示例：select * from emp where deptno=10 OR sal2000; where NOT -不滿足條件的示例：select * from emp where not deptno=10;where IN(條件列表) -所有滿足在條件列表中的記錄示例：select * from emp where empno in(7788,7369,7499);where BETWEEN . AND. -按范圍查找示例：select * from emp where sal between 1000 and 3000;where 字段 LIKE -主要用與字符類型的字段 示例1：select * from emp where ename like _C%; -查詢姓名中第二個(gè)字母是C的人- 表示任意字符;% 表示多字符的序列；本文摘自：/pc where 字段 IS NOT NULL -查找該字段是不是空的記錄匯總數(shù)據(jù)是用的函數(shù)SUM -求和示例：select deptno,sum(sal) as sumsal from emp GROUP BY deptno;AVG -求平均值MAX -求最大值MIN -求最小值COUNT -求個(gè)數(shù)子查詢 SELECT from where 字段 運(yùn)算符();示例：select * from emp where sal=(select max(sal) from emp);運(yùn)算符Any 示例：select * from emp where salANY(select sal from emp where deptno=30) and deptno30;-找出比deptno=30的員工最低工資高的其他部門的員工ALL select * from emp where salALL(select sal from emp where deptno=30) and deptno30;-找出比deptno=30的員工最高工資高的其他部門的員工連接查詢 SELECT from WHERE table1.字段(+)=table2.字段(+)示例select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;查詢指定行數(shù)的數(shù)據(jù)SELECT from WHERE ROWNUM行數(shù);示例:select * from emp where rownum=10;-查詢前10行記錄注意ROWNUM只能為1 因此不能寫 select * from emp where rownum between 20 and 30;要查第幾行的數(shù)據(jù)可以使用以下方法:select * from emp where rownum=3 and empno not in (select empno from emp where rownum=3);結(jié)果可以返回整個(gè)數(shù)據(jù)的3-6行;不過(guò)這種方法的性能不高；如果有別的好方法請(qǐng)告訴我。3更新記錄更新數(shù)據(jù)UP