《[精选]神州数码ORACLE SQL讲义》由会员分享，可在线阅读，更多相关《[精选]神州数码ORACLE SQL讲义（29页珍藏版）》请在金锄头文库上搜索。
1、SQL课程大纲,如何进入Sqlplus 建立/修改Table INSERT 指令介紹 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 Corp
2、oration. All 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 Wi
3、th the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production,建立/修改Table,数据类型:,建立/更改Table,1.写好create table 的sql再执行 vi dpe_file.sch /* = 档案代号:dpe_file 档案名称:体检项目代号资料档 =.=.= */ create table dpe_file ( dpe01 varchar2(6), /*体检项目代号 */ dpe02 varchar2(30), /*说明 */ dpe03 var
4、char2(01), /*No use */ dpeacti varchar2(01), /*资料有效码 */ dpeuser varchar2(10), /*资料所有者 */ dpegrup varchar2(06), /*资料所有部门 */ dpemodu varchar2(10), /*资料修改者 */ dpedate date /*最近修改日 */ ); create unique index dpe_01 on dpe_file (dpe01);,建立/更改Table,2.在 unix 环境下 sqlplus ds/ds sqlplus ds/ds = DOC档案代号:dpe_fil
5、e DOC档案名称:体检项目代号资料档 DOC=.=.= DOC*/ Table created. Index created. SQL,建立/更改Table,3.在 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), /*资料所有者 */
6、8 dpegrup varchar2(06), /*资料所有部门 */ 9 dpemodu varchar2(10), /*资料修改者 */ 10 dpedate date /*最近修改日 */ 11 ); Table created. SQL create unique index dpe_01 on dpe_file (dpe01); Index created. SQL,建立/更改Table,SQL desc dpe_file; Name Null? Type - - - DPE01 VARCHAR2(6) DPE02 VARCHAR2(30) DPE03 VARCHAR2(1) DPE
7、ACTI VARCHAR2(1) DPEUSER VARCHAR2(10) DPEGRUP VARCHAR2(6) DPEMODU VARCHAR2(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);
8、 /*删除dpe03*/ drop index dpe_01; /*删除索引*/ SQL alter_dpe.sch Table altered. Table altered. Table altered. Index dropped.,建立/修改Table,2.进入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_fi
9、le drop (dpe03); Table altered. SQL drop index dpe_01; Index dropped. SQL,建立/修改Table,SQL 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 INT
10、O table_name(column-list) VALUES(value-list) 范例: 1. INSERT INTO dpe_file VALUES(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 dpe
11、01 LIKE A%1 2. UPDATE dpe_file SET dpe02=test02, dpeacti=N WHERE dpe01 like A%1 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.DELE
12、TE FROM dpe_file WHERE dpe01 LIKE C%,SELECT指令介绍,Syntax: SELECT column,group_function(column) 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,cu
13、s02 3.SELECT last_name,salary,12*salary+100 FROM employee 4.SELECT last_name As name,commission 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
14、,age FROM employee WHERE age=45,SELECT指令介绍,范例说明: 9.SELECT * FROM employee WHERE age between 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(fir
15、st_name,last_name) NAME , job_id,LENGTH(last_name), INSTR(last-name,a) “Contain a?” FROM employee 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.SELEC
16、T e.employee_id,e.last_name,e.department_id,d.department_id, d.location_id FROM employee e ,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=d.department_id(+) 14.INSERT INTO cus1_file SELECT * FROM cus_file 15.INSERT INTO cua_file(cua01,cua02) SEL
