神州数码ORACLE SQL讲义

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,*,SQL,课程大纲,如何,进入,Sqlplus,建立/修改,Tabl,e,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 Corporation.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,With the Partitioning,OLAP and Oracle Data Mining options,JServer Release 9.2.0.1.0-Production,建立/修改Table,数据类型:,VARCHAR,2(size),字符类型,(,变长,)例 VARCHAR2(10),CHAR,(size),字符类型,(,定长,)例 CHAR(10),NUMBER,(p,s),数值类型,例,:NUMBER(5),表示5位整数,例:NUMBER(15,3)表11位整数,3位小数,DATE,日期,时间类型,LONG,变长字符类型,最大长度,2G,CLOB,字符类型，最大长度,4G,BLOB,二进制类型，最大长度,4G,ROWID,16,进制字符串，代表在表中的一个行的唯一地址,建立/更改Table,1.,写好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 varchar2(06),/*资料所有部门 */,dpemodu varchar2(10),/*资料修改者,*/,dpedate date /*最近修改日 */,);,create unique index dpe_01 on dpe_file(dpe01);,建立/更改Table,2.在 unix 环境下 sqlplus ds/ds dpe_file.sch 即可create table 了,sqlplus ds/ds,=,DOC档案代号:dpe_file,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),/*资料所有者 */,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),DPEACTI 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);,/*删除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_file 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 INTO 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(C000,02,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,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(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,cus02,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,age FROM employee WHERE age=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 and 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 null,SELECT指令介紹-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 Function,AVG,COUNT,MAX,MIN,STDDEV,SUM,Oracle Function-1,Case-manipulation functions,lower,upper,initcap,Character-manipulation functions concat,substr,length,instr,lpad,rpad,trim,Function,Result,LOWER(SQL Course),