ORACLE-DB初級培訓大綱

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

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

ORACLEDB用戶的創(chuàng)建需要創(chuàng)建以下的用戶：用戶口令缺省表空間權限EPEPDS_EP01role:connectsysprivs:unlimitedonDS_EP01selectanytableselectanydictionaryMMS010MMS010DS_MM01role:connectsysprivs:unlimitedonDS_MM01unlimitedonDS_QM01unlimitedonDS_YM01unlimitedonDS_IDEX01selectanytableselectanydictionary創(chuàng)建過程：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應用表的命名規(guī)則表名：TXXYY99T代表TableXX模塊名YY產(chǎn)線名99序列號建表的示例：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)任何一張表的缺省字段，必須包含且必須為表的第一到第五個字段。

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

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

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

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

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

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

本課的關鍵：規(guī)范化建表（表的命名、字段的命名、表的存儲地）規(guī)范化建索引（索引的命名、索引的存儲地）練習：以MES系統(tǒng)的基本設計的表結構為例子，每人創(chuàng)建1、2張表，每張表有主鍵、單字段索引、復合字段索引。練習用腳本方式完成

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

表dual的介紹Dual是Oracle中的一個實際存在的表，任何用戶均可讀取，常用在沒有目標表的Select語句塊中Selectto_char(sysdate,’yyyymmddhh24miss’)fromdual;Selectsubstrb(‘12345’,2,3)fromdual;幾個常用函數(shù)NVL()對null的操作LOWER()小寫UPPER()大寫TRIM()去除空格LTRIM()去除左空格RTRIM()去除右空格LPAD(‘999’,4,’5’)999為主數(shù)據(jù)，4為補齊位數(shù)，5為填補數(shù)據(jù)[左]RPAD()[右]LENGTH()LENGTHB()獲取字符型數(shù)據(jù)長度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;)

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

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

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