神州数码ORACLE SQL讲义ppt课件

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格