神州數(shù)碼ORACLE SQL講義ppt課件

1、SQL課程大綱如何進(jìn)入Sqlplus建立/修正TableINSERT 指令介紹UPDATE 指令介紹 DELETE 指令介紹SELECT指令介紹WHERE Function 引見(jiàn)Group Function引見(jiàn)Oracle Function 引見(jiàn)LOAD,UNLOAD 指令介紹Sqlplus內(nèi)的一些命令引見(jiàn)進(jìn)入ISQL sqlplus 1.常規(guī)登陸 sqlplus SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 9月 17 09:57:02 2003 Copyright (c) 1982, 2002, Oracle Corporation. Al

2、l rights reserved. 2.快捷登陸 sqlplus username/passwd username 登陸用戶(hù)名 passwd 登陸用戶(hù)密碼 SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 9月 17 09:59:18 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Part

3、itioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production建立/修正Table數(shù)據(jù)類(lèi)型: VARCHAR2(size)字符類(lèi)型 (變長(zhǎng)) 例 VARCHAR2(10)CHAR(size)字符類(lèi)型 (定長(zhǎng)) 例 CHAR (10)NUMBER(p,s)數(shù)值類(lèi)型例: NUMBER(5) 表示5位整數(shù)例: NUMBER(15,3) 表11位整數(shù), 3位小數(shù) DATE日期時(shí)間類(lèi)型LONG變長(zhǎng)字符類(lèi)型,最大長(zhǎng)度2GCLOB字符類(lèi)型，最大長(zhǎng)度4GBLOB二進(jìn)制類(lèi)型，最大長(zhǎng)度4GROWID16進(jìn)制字

4、符串，代表在表中的一個(gè)行的唯一地址建立/更改Table1.寫(xiě)好create table 的sql再執(zhí)行 vi dpe_file.sch /* = 檔案代號(hào):dpe_file 檔案稱(chēng)號(hào):體檢工程代號(hào)資料檔 =.=.= */ create table dpe_file ( dpe01 varchar2(6), /*體檢工程代號(hào) */ dpe02 varchar2(30), /*闡明 */ dpe03 varchar2(01), /*No use */ dpeacti varchar2(01), /*資料有效碼 */ dpeuser varchar2(10), /*資料一切者 */ dpegrup

5、varchar2(06), /*資料一切部門(mén) */ dpemodu varchar2(10), /*資料修正者 */ dpedate date /*最近修正日 */ ); create unique index dpe_01 on dpe_file (dpe01); 建立/更改Table2.在 unix 環(huán)境下 sqlplus ds/ds dpe_file.sch 即可create table 了 sqlplus ds/ds = DOC檔案代號(hào):dpe_file DOC檔案稱(chēng)號(hào):體檢工程代號(hào)資料檔 DOC=.=.= DOC*/ Table created. Index created. SQL

6、 建立/更改Table3.在 sqlplus環(huán)境下建立 table SQL create table dpe_file 2 ( 3 dpe01 varchar2(6), /*體檢工程代號(hào) */ 4 dpe02 varchar2(30), /*闡明 */ 5 dpe03 varchar2(01), /*No use */ 6 dpeacti varchar2(01), /*資料有效碼 */ 7 dpeuser varchar2(10), /*資料一切者 */ 8 dpegrup varchar2(06), /*資料一切部門(mén) */ 9 dpemodu varchar2(10), /*資料修正者 *

7、/ 10 dpedate date /*最近修正日 */ 11 ); Table created. SQL create unique index dpe_01 on dpe_file (dpe01); Index created. SQL 建立/更改TableSQL desc dpe_file; Name Null? Type - - - DPE01 VARCHAR2(6) DPE02 VARCHAR2(30) DPE03 VARCHAR2(1) DPEACTI VARCHAR2(1) DPEUSER VARCHAR2(10) DPEGRUP VARCHAR2(6) DPEMODU VARC

8、HAR2(10) DPEDATE DATE SQL 建立/修正Table修正table 1.寫(xiě)成sql 更改 SQL ed alter_dpe.sch alter table dpe_file modify (dpe01 varchar2(10); /*修正dpe01*/ alter table dpe_file add (dpe00 varchar2(10); /*添加dep00*/ alter table dpe_file drop (dpe03); /*刪除dpe03*/ drop index dpe_01; /*刪除索引*/ SQL alter_dpe.sch Table altere

9、d. Table altered. Table altered. Index dropped. 建立/修正Table2.進(jìn)入sqlplus 更改 SQL alter table dpe_file modify (dpe01 varchar2(10); Table altered. SQL alter table dpe_file add (dpe00 varchar2(10); Table altered. SQL alter table dpe_file drop (dpe03); Table altered. SQL drop index dpe_01; Index dropped. SQ

10、L 建立/修正TableSQL desc dpe_file; Name Null? Type - - - DPE01 VARCHAR2(10) DPE02 VARCHAR2(30) DPEACTI VARCHAR2(1) DPEUSER VARCHAR2(10) DPEGRUP VARCHAR2(6) DPEMODU VARCHAR2(10) DPEDATE DATE DPE00 VARCHAR2(10) INSERT 指令引見(jiàn)INSERT INTO table_name(column-list) VALUES(value-list)范例:1. INSERT INTO dpe_file VAL

11、UES(A00003,test,Y,carrier,1400,03/09/17)2. INSERT INTO cus_file(cus01,cus02) VALUES(C00002, test01);UPDATE 指令引見(jiàn)Syntax UPDATE table_name SET col=expr,col=expr WHERE clause 范列說(shuō)明: 1.UPDATE dpe_file SET dpe02=test02 WHERE dpe01 LIKE A%1 2. UPDATE dpe_file SET dpe02=test02, dpeacti=N WHERE dpe01 like A%1

12、 3. UPDATE dpe_file SET dpeuser=michael, dpegrup=2100 WHERE (dpe01 = A00002 or dpe01 like C_000_);DELETE 指令引見(jiàn)Syntax: DELETE FROM table_name WHERE clause范例闡明: 1.DELETE FROM dpe_file 留意:沒(méi)有where 條件會(huì)將一切資料刪除 且無(wú)法將資料復(fù)原2.DELETE FROM dpe_file WHERE dpe01 LIKE C%SELECT指令引見(jiàn)Syntax:SELECT column,group_function(c

13、olumn) FROM table_name WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column 范例闡明: 1.SELECT * FROM cus_file order by cus01 ASC 2.SELECT cus01,cus02 FROM cus_file order by cus01 DESC,cus02 3.SELECT last_name,salary,12*salary+100 FROM employee 4.SELECT last_name As name,c

14、ommission comm FROM employee 5.SELECT last_name|job_id as “Employee Info FROM employee 6.SELECT last_name | is a |job_id as “Employee Detail FROM employee 7.SELECT distinct dpe02 FROM dpe_file 8. SELECT last_name,age FROM employee WHERE age=45 SELECT指令引見(jiàn)范例闡明: 9.SELECT * FROM employee WHERE age betwe

15、en 20 and 65 SELECT * FROM dpe_file WHERE dpe02 is null and (dpeacti=Y or dpeuser like carr%) 10.SELECT employee_id,UPPER(last_name) FROM employee WHERE INITCAP(last_name) = Higgins SELECT employee_id,CONCAT(first_name,last_name) NAME , job_id,LENGTH(last_name), INSTR(last-name,a) “Contain a? FROM e

16、mployee WHERE SUBSTR(job_id,4)=“REP SELECT last_name,salary,MOD(salary,5000) FROM employee WHERE job_id=SA_REP Join table 範(fàn)例: 11.SELECT oea01,oeb02,oeb04,oeb12 FROM oea_file,oeb_file WHERE oea01=oeb01 12.SELECT e.employee_id,e.last_name,e.department_id,d.department_id, d.location_id FROM employee e

17、,department d WHERE e.department_id=d.department_id SELECT指令介紹范例說(shuō)明: 13. SELECT e.last_name,e.department_id,d.department_name FROM employee e ,department d WHERE e.department_id(+)=d.department_id SELECT e.last_name,e.department_id,d.department_name FROM employee e ,department d WHERE e.department_id

18、=d.department_id(+) 14.INSERT INTO cus1_file SELECT * FROM cus_file 15.INSERT INTO cua_file(cua01,cua02) SELECT cus01,cus02 FROM cus_file WHERE cus01 MATCHES C* 16.SELECT COUNT(*) FROM oea_file WHERE oea02 BETWEEN 01/01/01 AND 01/12/31 17.SELECT COUNT(DISTINCT oea03) FROM oea_file WHERE oea02 BETWEE

19、N 01/01/01 AND 01/12/31 18.SELECT MAX(oea02),MIN(oea02) FROM oea_file WHERE oea02 BETWEEN 01/01/01 AND 01/12/31 SELECT指令介紹范例說(shuō)明: 19. SELECT AVG(oeb12) FROM oea_file,oeb_file WHERE oea02 BETWEEN 010101 AND 011231 AND oea01=oeb01 AND oeb04=11-03-SDD16 and oeb120 20.SELECT * FROM oea_file WHERE oea02 =

20、(SELECT MAX(oea02) FROM oea_file WHERE oea02 = 01/01/01) 21.SELECT department_id,AVG(salary) FROM employee GROUP BY department_id 22.SELECT department_id,MAX(salary) FROM employee GROUP BY department_id HAVING MAX(salary)10000 SELECT指令介紹-where比較符號(hào) = = = 其它比較符號(hào) BETWEEN . AND . WHERE age between 20 an

21、d 30 IN (.) WHERE age in (20,21,22,23,24,25,26,27,28,29,30) LIKE WHERE dpe01 like A_0% IS NULL WHERE age is nullSELECT指令介紹-where邏輯符號(hào) AND OR NOT where dpe01=A00001 AND dpe02=test01 where dpe01=A00001 OR dpe01=C00001 where age not in (20,21,22,23,24,25,26,27,28,29,30)Group FunctionAVGCOUNTMAXMINSTDDEV

22、SUMOracle Function-1Case-manipulation functions lower,upper,initcapCharacter-manipulation functions concat,substr,length, instr,lpad,rpad,trimFunctionResultLOWER(SQL Course)sql courseUPPER(SQL Course)SQL COURSEINITCAP(SQL Course)Sql CourseCONCAT(Hello,World)HelloWorldSUBSTR(Helloworld,1,5)HelloLENGT

23、H(HelloWorld)10INSTR(HelloWorld,W)6LPAD(salary,10,*)*24000RPAD(salary,10,*)24000*TRIM(H FROM HelloWorld)elloWorldFUNCTION-2Number Function ROUND round(45.926,2) 45.93 TRUNC trunc(45.926,2) 45.92 MOD mod(1600,300) 100LOAD/UNLOAD指令引見(jiàn)由于oracle沒(méi)有提供類(lèi)似INFORMIX的load,unload語(yǔ)句，所以在tiptop環(huán)境下有一些工具1.運(yùn)用load shell,

24、load shell會(huì)去呼叫$TOP/ora/load.42m 程序運(yùn)用方式Usage: load database tablename txtfileEx : load ds ze_file ze_file.txt2.運(yùn)用loaddb shell,loaddb shell會(huì)去呼叫external tools SQL*Loader運(yùn)用方式Usage: /u1/topo/ora/bin/loaddb dbname tablename txtfileEx1 : /u1/topo/ora/bin/loaddb ds1Ex2 : /u1/topo/ora/bin/loaddb ds1 ima_file ima_file.txtEx3 : /u1/topo/ora/bin/loaddb ds1 i%_file3.直接運(yùn)用Oracle提供的SQL*Loader,但必需本人編輯control file,用法sqlldr ds/ds control=azb_file.ctl log=azb_file.logLOAD/UNLOAD指令引見(jiàn)control file格