1、SQL課程大綱如何進入Sqlplus建立/修正TableINSERT 指令介紹UPDATE 指令介紹 DELETE 指令介紹SELECT指令介紹WHERE Function 引見Group Function引見Oracle Function 引見LOAD,UNLOAD 指令介紹Sqlplus內的一些命令引見進入ISQL sqlplus 1.常規登陸 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 登陸用戶名 passwd 登陸用戶密碼 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數據類型: VARCHAR2(size)字符類型 (變長) 例 VARCHAR2(10)CHAR(size)字符類型 (定長) 例 CHAR (10)NUMBER(p,s)數值類型例: NUMBER(5) 表示5位整數例: NUMBER(15,3) 表11位整數, 3位小數 DATE日期時間類型LONG變長字符類型,最大長度2GCLOB字符類型，最大長度4GBLOB二進制類型，最大長度4GROWID16進制字

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

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

6、 建立/更改Table3.在 sqlplus環境下建立 table SQL create table dpe_file 2 ( 3 dpe01 varchar2(6), /*體檢工程代號 */ 4 dpe02 varchar2(30), /*闡明 */ 5 dpe03 varchar2(01), /*No use */ 6 dpeacti varchar2(01), /*資料有效碼 */ 7 dpeuser varchar2(10), /*資料一切者 */ 8 dpegrup varchar2(06), /*資料一切部門 */ 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.寫成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.進入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 指令引見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 指令引見Syntax UPDATE table_name SET col=expr,col=expr WHERE clause 范列說明: 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 指令引見Syntax: DELETE FROM table_name WHERE clause范例闡明: 1.DELETE FROM dpe_file 留意:沒有where 條件會將一切資料刪除 且無法將資料復原2.DELETE FROM dpe_file WHERE dpe01 LIKE C%SELECT指令引見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指令引見范例闡明: 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 範例: 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指令介紹范例說明: 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指令介紹范例說明: 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比較符號 = = = 其它比較符號 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邏輯符號 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指令引見由于oracle沒有提供類似INFORMIX的load,unload語句，所以在tiptop環境下有一些工具1.運用load shell,

24、load shell會去呼叫$TOP/ora/load.42m 程序運用方式Usage: load database tablename txtfileEx : load ds ze_file ze_file.txt2.運用loaddb shell,loaddb shell會去呼叫external tools SQL*Loader運用方式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.直接運用Oracle提供的SQL*Loader,但必需本人編輯control file,用法sqlldr ds/ds control=azb_file.ctl log=azb_file.logLOAD/UNLOAD指令引見control file格