神州数码ORACLE SQL讲义.ppt

SQL课程大纲 如何进入Sqlplus建立 修改TableINSERT指令介紹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 数据类型 建立 更改Table 1 写好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 建立 更改Table 2 在unix环境下sqlplusds dssqlplusds ds DOC 档案代号 dpe fileDOC 档案名称 体检项目代号资料档DOC DOC Tablecreated Indexcreated SQL 建立 更改Table 3 在sqlplus环境下建立tableSQL createtabledpe file2 3dpe01varchar2 6 体检项目代号 4dpe02varchar2 30 说明 5dpe03varchar2 01 Nouse 6dpeactivarchar2 01 资料有效码 7dpeuservarchar2 10 资料所有者 8dpegrupvarchar2 06 资料所有部门 9dpemoduvarchar2 10 资料修改者 10dpedatedate 最近修改日 11 Tablecreated SQL createuniqueindexdpe 01ondpe file dpe01 Indexcreated SQL 建立 更改Table SQL descdpe file NameNull Type DPE01VARCHAR2 6 DPE02VARCHAR2 30 DPE03VARCHAR2 1 DPEACTIVARCHAR2 1 DPEUSERVARCHAR2 10 DPEGRUPVARCHAR2 6 DPEMODUVARCHAR2 10 DPEDATEDATESQL 建立 修改Table 修改table1 写成sql更改SQL edalter dpe schaltertabledpe filemodify dpe01varchar2 10 修改dpe01 altertabledpe fileadd dpe00varchar2 10 增加dep00 altertabledpe filedrop dpe03 删除dpe03 dropindexdpe 01 删除索引 SQL alter dpe schTablealtered Tablealtered Tablealtered Indexdropped 建立 修改Table 2 进入sqlplus更改SQL altertabledpe filemodify dpe01varchar2 10 Tablealtered SQL altertabledpe fileadd dpe00varchar2 10 Tablealtered SQL altertabledpe filedrop dpe03 Tablealtered SQL dropindexdpe 01 Indexdropped SQL 建立 修改Table SQL descdpe 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 45 SELECT指令介绍 范例说明 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 location idFROMemployeee departmentdWHEREe department id d department id SELECT指令介紹 范例說明 13 SELECTe last name e department id d department nameFROMemployeee departmentdWHEREe department id d department idSELECTe last name e department id d department nameFROMemployeee departmentdWHEREe department id d department id 14 INSERTINTOcus1 fileSELECT FROMcus file15 INSERTINTOcua file cua01 cua02 SELECTcus01 cus02FROMcus fileWHEREcus01MATCHES C 16 SELECTCOUNT FROMoea fileWHEREoea02BETWEEN 01 01 01 AND 01 12 31 17 SELECTCOUNT DISTINCToea03 FROMoea fileWHEREoea02BETWEEN 01 01 01 AND 01 12 31 18 SELECTMAX oea02 MIN oea02 FROMoea fileWHEREoea02BETWEEN 01 01 01 AND 01 12 31 SELECT指令介紹 范例說明 19 SELECTAVG oeb12 FROMoea file oeb fileWHEREoea02BETWEEN 010101 AND 011231 ANDoea01 oeb01ANDoeb04 11 03 SDD16 andoeb12020 SELECT FROMoea fileWHEREoea02 SELECTMAX oea02 FROMoea fileWHEREoea02 01 01 01 21 SELECTdepartment id AVG salary FROMemployeeGROUPBYdepartment id22 SELECTdepartment id MAX salary FROMemployeeGROUPBYdepartment idHAVINGMAX salary 10000 SELECT指令介紹 where 比较符号 其它比较符号BETWEEN AND WHEREagebetween20and30IN WHEREagein 20 21 22 23 24 25 26 27 28 29 30 LIKEWHEREdpe01like A 0 ISNULLWHEREageisnull SELECT指令介紹 where 逻辑符号 AND OR NOT wheredpe01 A00001 ANDdpe02 test01 wheredpe01 A00001 ORdpe01 C00001 whereagenotin 20 21 22 23 24 25 26 27 28 29 30 GroupFunction AVGCOUNTMAXMINSTDDEVSUM OracleFunction 1 Case manipulationfunctionslower upper initcapCharacter manipulationfunctionsconcat substr length instr lpad rpad trim FUNCTION 2 NumberFunctionROUNDround 45 926 2 45 93TRUNCtrunc 45 926 2 45 92MODmod 1600 300 100 LOAD UNLOAD指令介绍 因为oracle没有提供类似INFORMIX的load unload语句 所以在tiptop环境下有一些工具1 使用loadshell loadshell会去呼叫 TOP ora load 42m程序使用方式Usage loaddatabasetablenametxtfileEx loaddsze fileze file txt2 使用loaddbshell loaddbshell会去呼叫externaltoolsSQL Loader使用方式Usage u1 topo ora bin loaddbdbname tablename txtfile Ex1 u1 topo ora bin loaddbds1Ex2 u1 topo ora bin loaddbds1ima file ima file txt Ex3 u1 topo ora bin loaddbds1i file3 直接使用Oracle提供的SQL Loader 但必须自己编辑controlfile 用法sqlldrds dscontrol azb file ctllog azb file log LOAD UNLOAD指令介绍 controlfile格式如下loaddatainfile u1 tiptopo azb file txt appendintotableazb filefieldsterminatedby A az