ORACLE-DB初級(jí)培訓(xùn)大綱

ORACLEDB初級(jí)培訓(xùn)大綱17/27ORACLEDB初級(jí)培訓(xùn)大綱培訓(xùn)講師：吳新助教：翁煥奇第一課如何連接ORACLEDB第一步：安裝ORACLENET(或者ORACLECLIENT)第二步：配制tnsnames.ora第三步：安裝一些client軟件SLQPLUS(oracleclient自帶)PLSQLDevelopTOAD

ORACLE表空間（TABLESPACE）的創(chuàng)建概念：表空間是數(shù)據(jù)庫(kù)邏輯存儲(chǔ)的概念一個(gè)數(shù)據(jù)庫(kù)由一個(gè)或多個(gè)表空間構(gòu)成，表空間用于存儲(chǔ)數(shù)據(jù)庫(kù)的數(shù)據(jù)每個(gè)表空間由一個(gè)或多個(gè)數(shù)據(jù)文件組成數(shù)據(jù)庫(kù)的數(shù)據(jù)實(shí)際存儲(chǔ)在數(shù)據(jù)文件中ORACLE缺省TABLESPACE查看：Select*fromall_tablespaces;TABLESPACE的建立：語(yǔ)法：CreatetablespaceTABLESPACE_NAMELoggingDatafile‘絕對(duì)路徑+文件名’SIZE10mExtentmanagementlocal;示例：創(chuàng)建DS_EP01,DS_MM01,DS_QM01,DS_YM01,DS_IDX01CreatetablespaceDS_EP01LoggingDatafile‘絕對(duì)路徑+文件名’SIZE100mExtentmanagementlocal;MES系統(tǒng)DB中的TABLESPACE命名規(guī)則格式：DS_XX01解釋：DS表示TABLESPACEXX模塊名99序列號(hào)

ORACLEDB用戶的創(chuàng)建需要?jiǎng)?chuàng)建以下的用戶：用戶口令缺省表空間權(quán)限EPEPDS_EP01role:connectsysprivs:unlimitedonDS_EP01selectanytableselectanydictionaryMMS010MMS010DS_MM01role:connectsysprivs:unlimitedonDS_MM01unlimitedonDS_QM01unlimitedonDS_YM01unlimitedonDS_IDEX01selectanytableselectanydictionary創(chuàng)建過(guò)程：Sqlplus>connectsys/assysdbaSqlplus>CreateuserEPidentifiedbyEPdefaulttablespaceDS_EP01;Sqlplus>grantconnecttoEP;Sqlplus>grantselectanytable,selectanydictionarytoEP;Sqlplus>alteruserEPquotaunlimitedonDS_EP01;Sqlplus>CreateuserMMS010identifiedbyMMS010defaulttablespaceDS_MM01;Sqlplus>grantconnecttoMMS010;Sqlplus>grantselectanytable,selectanydictionarytoMMS010;Sqlplus>alteruserMMS010quotaunlimitedonDS_MM01;

表（TABLE）的創(chuàng)建MES應(yīng)用表的命名規(guī)則表名：TXXYY99T代表TableXX模塊名YY產(chǎn)線名99序列號(hào)建表的示例：createtableTSMSFA1(REC_CREATORVARCHAR2(8)default''notnull,REC_CREATE_TIMEVARCHAR2(14)default''notnull,REC_REVISORVARCHAR2(8)default''notnull,REC_REVISE_TIMEVARCHAR2(14)default''notnull,ARCHIVE_FLAGVARCHAR2(1)default''notnull,MAT_NOVARCHAR2(20)default''notnull,CUST_MAT_NOVARCHAR2(20)default''notnull,MAT_NET_WTNUMBER(8,3)default0notnull)tablespaceDS_SM01;

表的字段（column）命名規(guī)則：一般是英文名稱縮寫注意：“REC_CREATORREC_CREATE_TIMEREC_REVISORREC_REVISE_TIMEARCHIVE_FLAG”為MES系統(tǒng)任何一張表的缺省字段，必須包含且必須為表的第一到第五個(gè)字段。

COMMENT備注命令“comment”是用以對(duì)表、表里字段及其他實(shí)體的注釋說(shuō)明。我們一般會(huì)用到對(duì)表及表字段的備注，如下：對(duì)表的comment 語(yǔ)法：Commentontabletable_nameis‘xxxxxxx’;對(duì)字段的comment語(yǔ)法：commentoncolumntable_name.column_nameis‘xxxxxxxx’;示例：Commentontabletsmsfa1is‘產(chǎn)成品材料表’;Commentoncolumntsmsfa1.rec_creatoris‘記錄創(chuàng)建者’;Commentoncolumntsmsfa1.rec_create_timeis‘記錄創(chuàng)建時(shí)間’;Commentoncolumntsmsfa1.rec_revisoris‘記錄修改者’;Commentoncolumntsmsfa1.rec_revise_timeis‘記錄修改時(shí)間’;Commentoncolumntsmsfa1.mat_nois‘材料號(hào)’;

INDEX（索引）一般分主鍵索引及非主鍵索引命名規(guī)則主鍵索引：table_name_pk非主鍵索引：table_name_idx99索引的創(chuàng)建主鍵索引主鍵必須包含唯一的值主鍵列不能包含NULL值每個(gè)實(shí)體表應(yīng)該都一個(gè)主鍵，并且每個(gè)表只能有一個(gè)主鍵語(yǔ)法：altertabletable_nameaddconstrainttable_name_pkprimarykey(column_name)usingindextablespaceds_idx01;主鍵創(chuàng)建示例：Altertabletsmsfa1addconstrainttsmsfa1_pkPrimarykey(mat_no)usingindextablespaceDS_SM01;注意：主鍵索引的表空間一般跟表一致。

非主鍵索引語(yǔ)法：createindexindex_nameontable_name(column_name)tablespacetablespace_name;創(chuàng)建示例：Createindextsmsfa1_idx_01ontsmsfa1(order_noasc)TablespaceDS_IDX01;

table(表)的字段的增加前提：新增字段只能在表的末尾如果某表存在大量數(shù)據(jù)，不允許隨便新增方法：方法1單獨(dú)新增字段語(yǔ)法示例：Altertabletsmsfa1add(order_novarchar2(10)notnulldefault‘‘);方法2批量新增字段語(yǔ)法示例：Altertabletsmsfa1add(order_novarchar2(10)notnulldefault‘‘，Consign_user_namevarchar2(50)notnulldefault‘‘,Mat_thicknumber(6,3)notnulldefault0);

table(表)的drop方法１：droptabletable_name;可以恢復(fù)方法2：droptabletable_namepurge;不可以恢復(fù)推薦：方法1表的恢復(fù)：flashbacktabletable_nametobeforedrop;Index(索引)的dropdropindexindex_name;Index(索引)的重建表的數(shù)據(jù)被大批量的增加或刪除以及在長(zhǎng)時(shí)間的新增及刪除后，建議重建INDEX。alterindexindex_namerebuildonline;

SQLCOMMAND的簡(jiǎn)單的介紹spoolspooloff制作建表腳本(SCRIPT)：文本編輯器存儲(chǔ)文件名（格式：文件名.sql）執(zhí)行方式Slqpluscommand下Sql>@絕對(duì)目錄下文件

本課的關(guān)鍵：規(guī)范化建表（表的命名、字段的命名、表的存儲(chǔ)地）規(guī)范化建索引（索引的命名、索引的存儲(chǔ)地）練習(xí)：以MES系統(tǒng)的基本設(shè)計(jì)的表結(jié)構(gòu)為例子，每人創(chuàng)建1、2張表，每張表有主鍵、單字段索引、復(fù)合字段索引。練習(xí)用腳本方式完成

第二課oracle函數(shù)簡(jiǎn)介幾種常用的數(shù)據(jù)類型CHARVARCHAR2NUMBER(n)NUMBER(N,M)DATEC語(yǔ)言對(duì)應(yīng)的數(shù)據(jù)類型ORACLEDBC語(yǔ)言描述CHAR(n)charcol[n+1]固定長(zhǎng)字符型VARCHAR2(n)charcol[n+1]變長(zhǎng)字符型NUMBER(n)intorlongint整型NUMBER(n,m)double浮點(diǎn)數(shù)字型特殊類型NULL的介紹NULL與字符‘‘的區(qū)別NULL與數(shù)字0的區(qū)別NULL的判定isnullorisnotnullNVL函數(shù)

表dual的介紹Dual是Oracle中的一個(gè)實(shí)際存在的表，任何用戶均可讀取，常用在沒(méi)有目標(biāo)表的Select語(yǔ)句塊中Selectto_char(sysdate,’yyyymmddhh24miss’)fromdual;Selectsubstrb(‘12345’,2,3)fromdual;幾個(gè)常用函數(shù)NVL()對(duì)null的操作LOWER()小寫UPPER()大寫TRIM()去除空格LTRIM()去除左空格RTRIM()去除右空格LPAD(‘999’,4,’5’)999為主數(shù)據(jù)，4為補(bǔ)齊位數(shù)，5為填補(bǔ)數(shù)據(jù)[左]RPAD()[右]LENGTH()LENGTHB()獲取字符型數(shù)據(jù)長(zhǎng)度SUBSTR(….,x,y)x開始截取字符位置，y截取目的字符位置SUBSTRB()bity||INSTR()查詢字符串中是否存在某一字母，若存在返回值>0，否則返回值為0DECODE()類似于switchTO_CHAR()TO_NUMBER()TO_DATE()ADD_MONTHS()Round()四舍五入Ceil()向上取整ASCII()ASCII碼轉(zhuǎn)換CHR()Replace(‘a(chǎn)bcd’,’bc’,’cb’)替換字符串Casewhen使用例子：(SELECTCOUNT(CASEWHENsalary<2000THEN1ELSEnullEND)count1,COUNT(CASEWHENsalaryBETWEEN2001AND4000THEN1ELSEnullEND)count2,COUNT(CASEWHENsalary>4000THEN1ELSEnullEND)count3FROMemployees;)

本課的關(guān)鍵：熟練運(yùn)用ORACLE提供的常用函數(shù)練習(xí)：在已經(jīng)創(chuàng)建的表里新增一些記錄練習(xí)上面提供所有函數(shù)將其中某條記錄用函數(shù)通過(guò)SELECT拼裝成一條文本

第三課SQLandWHERE子句簡(jiǎn)介WHERE子句寫法的介紹如需有條件地從表中選取數(shù)據(jù)，可將WHERE子句添加到SELECT語(yǔ)句。語(yǔ)法SELECT列名稱FROM表名稱WHERE列運(yùn)算符值下面的運(yùn)算符可在WHERE子句中使用：操作符描述=等于<>不等于>大于<小于>=大于等于<=小于等于BETWEEN在某個(gè)范圍內(nèi)LIKE搜索某種模式注釋：在某些版本的SQL中，操作符<>可以寫為!=。引號(hào)的使用請(qǐng)注意，我們?cè)诶又械臈l件值周圍使用的是單引號(hào)。SQL使用單引號(hào)來(lái)環(huán)繞文本值（大部分?jǐn)?shù)據(jù)庫(kù)系統(tǒng)也接受雙引號(hào)）。如果是數(shù)值，請(qǐng)不要使用引號(hào)。AND&OR運(yùn)算符AND和OR運(yùn)算符用于基于一個(gè)以上的條件對(duì)記錄進(jìn)行過(guò)濾AND和OR運(yùn)算符AND和OR可在WHERE子語(yǔ)句中把兩個(gè)或多個(gè)條件結(jié)合起來(lái)。如果第一個(gè)條件和第二個(gè)條件都成立，則AND運(yùn)算符顯示一條記錄。如果第一個(gè)條件和第二個(gè)條件中只要有一個(gè)成立，則OR運(yùn)算符顯示一條記錄。ORDERBY子句ORDERBY語(yǔ)句用于對(duì)結(jié)果集進(jìn)行排序。ORDERBY語(yǔ)句ORDERBY語(yǔ)句用于根據(jù)指定的列對(duì)結(jié)果集進(jìn)行排序。ORDERBY語(yǔ)句默認(rèn)按照升序?qū)τ涗涍M(jìn)行排序。ASC升序排列如果您希望按照降序?qū)τ涗涍M(jìn)行排序，可以使用DESC關(guān)鍵字。LIKE操作符LIKE操作符用于在WHERE子句中搜索列中的指定模式。LIKE操作符LIKE操作符用于在WHERE子句中搜索列中的指定模式。通配符描述%替代一個(gè)或多個(gè)字符_僅替代一個(gè)字符IN操作符IN操作符允許我們?cè)赪HERE子句中規(guī)定多個(gè)值。SQLIN語(yǔ)法SELECTcolumn_name(s)FROMtable_nameWHEREcolumn_nameIN(value1,value2,...)BETWEEN操作符BETWEEN操作符在WHERE子句中使用，作用是選取介于兩個(gè)值之間的數(shù)據(jù)范圍。BETWEEN操作符操作符BETWEEN...AND會(huì)選取介于兩個(gè)值之間的數(shù)據(jù)范圍。這些值可以是數(shù)值、文本或者日期。SQLBETWEEN語(yǔ)法SELECTcolumn_name(s)FROMtable_nameWHEREcolumn_nameBETWEENvalue1ANDvalue2Alias（別名）通過(guò)使用SQL，可以為列名稱和表名稱指定別名（Alias）。SQLAlias表的SQLAlias語(yǔ)法SELECTcolumn_name(s)FROMtable_nameASalias_name列的SQLAlias語(yǔ)法SELECTcolumn_nameASalias_nameFROMtable_nameJOINSQLjoin用于根據(jù)兩個(gè)或多個(gè)表中的列之間的關(guān)系，從這些表中查詢數(shù)據(jù)。Join和Key有時(shí)為了得到完整的結(jié)果，我們需要從兩個(gè)或更多的表中獲取結(jié)果。我們就需要執(zhí)行join。數(shù)據(jù)庫(kù)中的表可通過(guò)鍵將彼此聯(lián)系起來(lái)。主鍵（PrimaryKey）是一個(gè)列，在這個(gè)列中的每一行的值都是唯一的。在表中，每個(gè)主鍵的值都是唯一的。這樣做的目的是在不重復(fù)每個(gè)表中的所有數(shù)據(jù)的情況下，把表間的數(shù)據(jù)交叉捆綁在一起。UNION和UNIONALL操作符SQLUNION操作符UNION操作符用于合并兩個(gè)或多個(gè)SELECT語(yǔ)句的結(jié)果集。請(qǐng)注意，UNION內(nèi)部的SELECT語(yǔ)句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)，每條SELECT語(yǔ)句中的列的順序必須相同。SQLUNION語(yǔ)法SELECTcolumn_name(s)FROMtable_name1UNIONSELECTcolumn_name(s)FROMtable_name2注釋：默認(rèn)地，UNION操作符選取不同的值。如果允許重復(fù)的值，請(qǐng)使用UNIONALL。SQLUNIONALL語(yǔ)法SELECTcolumn_name(s)FROMtable_name1UNIONALLSELECTcolumn_name(s)FROMtable_name2避免如下寫法：IsnullIsnotnullCol1||col2=:col12ColBetweendecode(:col1,‘a(chǎn)’,col,low_value)anddecode(:col2,’y’,col,high_value)Collike‘%abc%’Substrb(col,1,2)=‘a(chǎn)’col1

=

NVL

(:b1,col1)NVL

(col1,-999)=TO_DATE(),

TO_NUMBER(),等等推薦如下寫法：Col1like‘A%’用exists代替in用notexists代替notin關(guān)鍵點(diǎn)：在where字句里，如果某個(gè)字段時(shí)索引字段，那么該字段不建議有函數(shù)對(duì)其進(jìn)行操作。

insert單記錄插入Insertintotable_1(col1,col2,col3,……)values(:col1,:col2,:col3,….);多記錄插入Insertintotable_1(col1,col2,col3,…..)