神州数码ORACLE SQL讲义

SQL课程大纲•如何进入Sqlplus•建立/修改Table•INSERT指令介紹•UPDATE指令介紹•DELETE指令介紹•SELECT指令介紹•WHEREFunction介绍•GroupFunction介绍•OracleFunction介绍•LOAD,UNLOAD指令介紹•Sqlplus内的一些命令介绍进入ISQL•sqlplus1.常规登陆sqlplusSQL*Plus:Release9.2.0.1.0-Productionon星期三9月1709:57:022003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.2.快捷登陆sqlplususername/passwdusername登陆用户名passwd登陆用户密码SQL*Plus:Release9.2.0.1.0-Productionon星期三9月1709:59:182003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.1.0-ProductionWiththePartitioning,OLAPandOracleDataMiningoptionsJServerRelease9.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进制字符串，代表在表中的一个行的唯一地址建立/更改Table1.写好createtable的sql再执行vidpe_file.sch/*=========================================================档案代号:dpe_file档案名称:体检项目代号资料档==========.===================.===========================*/createtabledpe_file(dpe01varchar2(6),/*体检项目代号*/dpe02varchar2(30),/*说明*/dpe03varchar2(01),/*Nouse*/dpeactivarchar2(01),/*资料有效码*/dpeuservarchar2(10),/*资料所有者*/dpegrupvarchar2(06),/*资料所有部门*/dpemoduvarchar2(10),/*资料修改者*/dpedatedate/*最近修改日*/);createuniqueindexdpe_01ondpe_file(dpe01);建立/更改Table2.在unix环境下sqlplusds/dsdpe_file.sch即可createtable了/u1/topo/apy/sqlsqlplusds/dsdpe_file.schSQL*Plus:Release9.2.0.1.0-Productionon星期三9月1710:37:232003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.1.0-ProductionWiththePartitioning,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.1.0-ProductionDOC============================================================DOC档案代号:dpe_fileDOC档案名称:体检项目代号资料档DOC==========.===================.==============================DOC*/Tablecreated.Indexcreated.SQL建立/更改Table3.在sqlplus环境下建立tableSQLcreatetabledpe_file2(3dpe01varchar2(6),/*体检项目代号*/4dpe02varchar2(30),/*说明*/5dpe03varchar2(01),/*Nouse*/6dpeactivarchar2(01),/*资料有效码*/7dpeuservarchar2(10),/*资料所有者*/8dpegrupvarchar2(06),/*资料所有部门*/9dpemoduvarchar2(10),/*资料修改者*/10dpedatedate/*最近修改日*/11);Tablecreated.SQLcreateuniqueindexdpe_01ondpe_file(dpe01);Indexcreated.SQL建立/更改TableSQLdescdpe_file;NameNull?Type-----------------------------------------------------------------------------DPE01VARCHAR2(6)DPE02VARCHAR2(30)DPE03VARCHAR2(1)DPEACTIVARCHAR2(1)DPEUSERVARCHAR2(10)DPEGRUPVARCHAR2(6)DPEMODUVARCHAR2(10)DPEDATEDATESQL建立/修改Table•修改table1.写成sql更改SQLedalter_dpe.schaltertabledpe_filemodify(dpe01varchar2(10));/*修改dpe01*/altertabledpe_fileadd(dpe00varchar2(10));/*增加dep00*/altertabledpe_filedrop(dpe03);/*删除dpe03*/dropindexdpe_01;/*删除索引*/SQL@alter_dpe.schTablealtered.Tablealtered.Tablealtered.Indexdropped.建立/修改Table2.进入sqlplus更改SQLaltertabledpe_filemodify(dpe01varchar2(10));Tablealtered.SQLaltertabledpe_fileadd(dpe00varchar2(10));Tablealtered.SQLaltertabledpe_filedrop(dpe03);Tablealtered.SQLdropindexdpe_01;Indexdropped.SQL建立/修改TableSQLdescdpe_file;NameNull?Type-----------------------------------------------------------------------------DPE01VARCHAR2(10)DPE02VARCHAR2(30)DPEACTIVARCHAR2(1)DPEUSERVARCHAR2(10)DPEGRUPVARCHAR2(6)DPEMODUVARCHAR2(10)DPEDATEDATEDPE00VARCHAR2(10)INSERT指令介绍•INSERTINTOtable_name[(column-list)]VALUES(value-list)范例:1.INSERTINTOdpe_fileVALUES('A00003','test','','Y','carrier','1400','','03/09/17‘)2.INSERTINTOcus_file(cus01,cus02)VALUES(‘C00002’,‘test01’);UPDATE指令介绍•SyntaxUPDATEtable_nameSETcol=expr,col=expr[WHEREclause]范列說明:1.UPDATEdpe_fileSETdpe02=‘test02’WHEREdpe01LIKE‘A%1’2.UPDATEdpe_fileSETdpe02='test02',dpeacti='N‘WHEREdpe01like'A%1'3.UPDATEdpe_fileSETdpeuser='michael‘,dpegrup='2100‘WHERE(dpe01='A00002'ordpe01like'C_000_');DELETE指令介绍•Syntax:DELETEFROMtable_name[WHEREclause]范例说明:1.DELETEFROMdpe_file[注意]:没有where条件会将所有资料删除且无法将资料还原2.DELETEFROMdpe_fileWHEREdpe01LIKE‘C%’SELECT指令介绍•Syntax:•SELECTcolumn,group_function(column)FROMtable_name[WHEREcondition][GROUPBYgroup_by_expression][HAVINGgroup_condition][ORDERBYcolumn]范例说明:1.SELECT*FROMcus_fileorderbycus01ASC2.SELECTcus01,cus02FROMcus_fileorderbycus01DESC,cus023.SELECTlast_name,salary,12*salary+100FROMemployee4.SELECTlast_nameAsname,commissioncommFROMemployee5.SELECTlast_name||job_idas“EmployeeInfo”FROMemployee6.SELECTlast_name||‘isa’||job_idas“EmployeeDetail”FROMemployee7.SELECTdistinctdpe02FROMdpe_file8.SELECTlast_name,ageFROMemployeeWHEREage=45SELECT指令介绍•范例说明:9.SELECT*FROMemployeeWHEREagebetween20and65SELECT*FROMdpe_fileWHEREdpe02isnulland(dpeacti=‘Y’ordpeuserlike‘carr%’)10.SELECTemployee_id,UPPER(last_name)FROMemployeeWHEREINITCAP(last_name)=‘Higgins’SELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last-name,’a’)“Contain‘a’?”FROMemployeeWHERESUBSTR(job_id,4)=“REP”SELECTlast_name,salary,MOD(salary,5000)FROMemployeeWHEREjob_id=‘SA_REP’Jointable範例:11.SELECToea01,oeb02,oeb04,oeb12FROMoea_file,oeb_fileWHEREoea01=oeb0112.SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.