oracle C级讲义.ppt

ORACLEC級講義 寶成國際集團 中山總公司電腦部 0 C級要求 1 能創建table 了解基本的限制及主鍵外鍵 2 掌握基本sql語句的應用 能利用sql做基本的資料新增 修改 刪除 會用sql函數 了解事務的含義 3 用SELECT語法產生簡單的View 4 能用sql plus產生報表 目錄 一 Oracle公司簡介二 Oracle版本介紹三 表格 table 的建立createtablealtertable四 簡單sqlselectinsertupdatedeletecommitsavepointrollbackdbtransactionssql分類createviewsql plus 一 Oracle公司簡介 Oracle公司簡介 二 Oracle版本介紹 Oracle8i 基本版 包含各種Oracle常用的功能Oracle8iEnterpriseEdition 完整版 包含各種可供使用者存取的功能 高階資料庫處理 網際網路資料庫及資料庫倉儲等Oracle8iPersonalEdition 個人版 專供個人開發資料庫軟體Oracle8iLite 專業版 專供設計行動運算環境 MobileComputionEnvironment 用的資料庫系統 輕量級可攜式的java資料庫 用於可攜式電腦 如PDA Palm 註 Oracle不管在哪一種平台上作業 其操作模式都一樣Oracle的安裝程式是用java開發的 Oracle版本号的格式 例如 oracle9 0 1 1 29 版本号0 新特性版本号1 第一个 维护版本号1 第二个 普通的补丁设置号码2 特殊的平台补丁设置号码 三 創建表格 表格 table 存放資料名稱 表名 不分大小寫欄位 column 也稱字段 屬性等 包括資料型態 欄位長度 缺省值資料列 屬性值限制 constraint 資料新增 修改 刪除的限制 有以下種 欄位限制 columnconstraint Null NotNull Unique Check表限制 tableconstraint 主鍵PrimaryKey 外部鍵ForeignKey限制越多 開發時少維護資料 但更新資料時間越長物理屬性 存放的表空間等 CREATETABLE 創建table語法 CREATE TEMPORARY TABLE schema table columndatatype DEFAULTexpr columnconstraint columndatatype tableconstraint CONSTRAINTchecknameCHECK checkexpr CONSTRAINTchecknameUNIQUE column 一個或多個CONSTRAINTpknamePRIMARYKEY column 資料不重複 一個CONSTRAINTfknameFOREIGNKEY column REFERENCEStablename column 指定鍵值與關聯表的主鍵值一致欄位check條件不能參考其他資料列的值 不能用虛擬位sysdate uid user userenv currval nextval level rownumCOMMENTONTABLE COLUMNtablename tablename columnnameIS text ALTERTABLE 修改table語法 ALTERTABLE schema table ADD columndatetype DEFAULTexpr columncontranit tableconstraint MODIFY columndatetype DEFAULTexpr columncontranit DROP COLUMNcolumn CASCADECONTRAINTS column column disable enable TABLELOCK TRIGGER contranitcontranit name RENAMETOnewtable RENAMEtablenameTOnewtable 改表名DROPTABLEtable CASCADECONTRAINTS 刪除表 更改表欄位的規則 加欄位 如不指定NOTNULL 可在隨后增加新的欄位 當表有資料時 新增的欄位不能定義為NOTNULL 解決方法 移資料 先加欄位 填資料 改NOTNULL 改欄位 可隨時增加字元欄位的寬度 可隨時增加NUMBER的位數 如表中有欄位每列是NULL時 可更改欄位的資料型態 減少字元欄位的寬度 減少NUMBER的位數或小數位數 刪欄位 刪多個欄位時 不用COLUMN關鍵字 如被刪欄位是主鍵或唯一限制條件的一部分時 須用CASCADECONSTRAINS 四 簡單sql SQL簡介 SQL全称是 结构化查询语言 StructuredQueryLanguage 最早的是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEMR开发的一种查询语言 它的前身是SQUARE语言 SQL歷史 SQL指令 DESCRIBE指令 查詢資料表Table的結構 DESCRIBETableName SELECT指令 主要用來查詢資料表Table 資料集View及資料物件DataObject內的資料 語法SELECT columnName columnNameAS alias FROMTableName 簡稱 TableName 簡稱 WHEREcondition GROUPBYcolumnName columnName ORDERBYcolumnName ASC DESC columnName ASC DESC 註意事項 SELECT子句 主要用來訂定在結果集內所要顯示的欄位 可以加上運算字符及內建函數 在columnname前須指定tablename 字串連結運算元 二元運算元 欄位經由連結後 會變成字串的表示式算數運算元 二元運算元 乘號與除號是優先於加號與減號 相同的運算是由左至右 括號優先運算註 運算元所執行的對象稱為運算子Operands 所謂二元運算元就是一次針對二個運算子作處理 所謂一元運算元就是一次針對一個運算子作處理 SQL函數 內建函數Built inFunction 有兩種 單列函數Single rowFunction及群組函數GroupFunction 單列函數 或稱純量函數 是將每一資料列都當作參數傳到函數 並將資料列轉換後當作結果傳回 如字元函數 數值函數 其他函數 日期函數及轉換函數等皆是如UPPER LENGTH ABS NVL LAST DAY及TO CHAR等 群組函數 或稱總合函數 是將全部資料列當參數 但只傳回其中一值如AVG COUNT GLB LUB MAX MIN STDDEV SUM及VARIANCE等 註意事項 DISTINCT 可以讓重複的資料以一筆傳回 FROM子句 提供SELECT要顯示的各資料欄位所對應的資料表TableWHERE子句 提供設定查詢資料過濾條件子句 若省略表示要查FROM的所有資料記錄 常用的條件子句運算元有三種 關係運算元Relational 如 ISNULL ISNOTNULL IN NOTIN EXISTS NOTEXISTS BETWEENStartValueANDEndValue等 Between100and200等同100 X 200IN 1 2 3 等同X 1 ORX 2 ORX 3邏輯運算元Logical 如AND OR NOT等 子查詢子句Subqueries 如SELECT可搭配IN及EXISTS使用 GROUPBY子句 分組 除了分組依據的欄位可不用內建群組函數 其他欄位皆須使用 若有對欄位做計算 如 sum avg min max 等 須以groupby的方式ListHAVING子句 必須配合GROUPBY 可將GROUPBY所查詢到的資料 再做篩選 其作用與WHERE相同 ORDERBY子句 排序 ASC 由小到大 default DESC 由大到小 相關資料表Table的Join查詢 要將兩個資料表Table的資料一起顯示 只要在FROM用逗點 隔開即可 而在WHERE條件子句至少會有Table數減1個條件式 對table間的欄位做比較 若table與table間有相同的欄位名稱 在columnname前須指定tablename Oracle有絕對關連InnerJoin和相對關連OutterJoin兩種 絕對關連InnerJoin 依據兩個資料表上的共同欄位使用InnerJoin時 兩個Table都必須找得到資料 即兩資料表皆必須存有資料或皆符合WHERE的條件式 相對關連OutterJoin 依據兩個資料表上的共同欄位使用OutterJoin時 允許有一Table的共同欄位資料不同 只要在WHERE的條件式上允許不同資料的Table後加上 符號 一 字符函數 返回字符值 可用於WHERE和ORDERBY子句 1 CHR x 返回ASCII碼表示的字符 与ASCII相反2 CONCAT string1 string2 返回串接上string2后的string1 与 運算符相同3 INITCAP string 返回string中每個單詞第一個字母大寫 而其它字母小寫的string 不是字母的字符不受影響 4 LOWER string 返回小寫形式的string 不是字母的不受影響 5 UPPER string 返回大寫形式的string6 LPAD string1 x string2 在左邊填充string2 使string1的長度為x 如果未指定string2 則以空格填充7 RPAD string1 x string2 与LPAD相同 唯填充在右邊8 LTRIM string1 string2 刪除string1中最左邊与string2相同的字串 string2缺省為單個空格9 RTRIM string1 string2 与LTRIM類似 只是刪除最右邊 10 REPLACE string search str replace str 用replace str替換string中所有為search str的字串并將結果返回 如果未指定replace str 則刪除string中所有為search str的字串 常用的SQLFUNCTION 11 SUBSTR string a b 返回string中從a位置開始有b個字符長的部分字串 如果a 0 就被認為是1 如果a 0 則從左往右起定位a 如果a0從左邊開始掃描 a 0從右邊開始掃描 返回第b次出現的位置 a和b缺省值為1 3 INSTRB string1 string2 a b 類似于INSTR 除了a以及返回值是以字節為單位進行表達的 如果是單字節字符集則完全相同4 LENGTH string 以字符為單位返回string的長度 注意char問題5 LENGTHB string 類似于LENGTH 除了返回值是以字節為單位進行表達的 三 數字函數 1 ABS x 返回x的絕對值2 CEIL x 返回大于或等于x的最小整數值3 FLOOR x 返回小于或等于x的最大整數值4 MOD x y 返回x除以y的余數 如果y為0 則返回x5 ROUND x y 返回舍入到小數點右邊y位的x值 y缺省值為0 將會舍入為最接近的整數 如果y是負數 則舍入到小數點左邊相應的位上 y必須為整數6 SIGN x 如果x0則返回17 TRUNC x y 返回截尾到y位小數的x值 不做舍入處理 y缺省值為0 將x截尾為一個整數值 如果y是負數 則結尾到小數點左邊相應的位上 四 日期函數1 ADD MONTHS d x 返回日期d加上x個月以后的結果2 LAST DAY d 返回日期d的月份的最后一天的日期3 MONTHS BETWEEN date1 date2 返回在date1和date2之間月的數目 如果 日 相同或date1和date2都是所在月最后一天 則返回整數 否則 返回的結果將包含一個分數部分4 SYSDATE 返回當前的日期和時間5 TRUNC d format 返回結尾到由format指定的單位的日期d 五 日期算術 1 d1 d2 返回d1和d2之間相差的天數2 d1 n 在d1上加上n天并作為date類型返回結果3 d1 n 從d1上減去n天并作為date類型返回結果注 兩個日期不能相加 SYSDATE 作業系統日期 時間selectto char sysdate yyyy mm ddhh24 mi ssAM fromdual 1999 02 0318 06 59PM 日期格式 年 yyyy year時 hh hh12 hh24月 month mon mm分 mi日 dd秒 ss星期 day dAM PM AM PM 0 6 0 Sunday 六 轉換涵數 1 TO CHAR d format nlsparams 將日期d轉換為一個VARCHAR2類型的字符串2 TO CHAR num format nlsparams 3 TO DATE string format nlsparams 4 TO NUMBER string format nlsparams VARCHAR2orCHAR From To VARCHAR2orCHAR NUMBER DATE NUMBER DATE VARCHAR2 VARCHAR2 NUMBER CHARACTER TO CHAR 七 分組函數 DISTINCT 重複的資料不參加計算ALL 缺省值1 AVG DISTINCT ALL col 返回平均值2 COUNT DISTINCT ALL col 返回查詢中行的數3 MAX DISTINCT ALL col 返回選擇列表項目的最大值 DISTINCT和ALL不起作用4 MIN DISTINCT ALL col 返回最小值5 SUM DISTINCT ALL col 返回選擇列表項目的數值的總和6 STDDEV col 7 VARIANCE 八 其它函數1 DECODE column base expr compara1 value1 compara2 value2 default 解碼函數 可以將欄位中的符號或代碼轉換成有意義的文字2 NVL expr1 expr2 處理NULL空值 如果expr1是NULL 則返回expr2 否則返回expr13 UID 返回唯一標識當前數据庫用戶的整數4 USER 返回當前ORACLE用戶的名字 INSERT指令 主要用來新增表資料 語法 INSERTINTO schema table column column VALUES value value subquery 注 可省略欄位的名稱 但新增值與欄位要匹配 日期及字串型態的欄位 新增值可用NULL關鍵字 當使用子查詢時 不能使用VALUES的語法 新增的欄位與子查詢的欄位數要相符合 當新增有欄位是外鍵時須能參考到ParentTable的資料 否則會報ora 02291 integrityconstraint xx fk name violated parentkeynotfound 需要提交COMMIT或回滾ROLLBACK INSERT 字元寬度和NUMBER精確度CHAR 固定長度 最長2000VARCHAR2 動態長度 最長4000NUMBER 不設 用最長NUMBER P S P 精度 即數值中所有數字位的個數S 刻度范圍 即小數位數在INSERT時 輸入資料比字元欄位寬度大時 會報錯ora 01401 insertedvaluetoolargeforcolumn 輸入資料的小數位數大於數值欄位的刻度范圍時 會被四捨五入到所設刻度範圍內 但大於精度時 會報錯ora 01438 valuelagerthanspecifiedprecisionallowedforcloumn UPDATE指令 主要用來修改表資料 語法 UPDATE schema tableSET column expr subquery column column subquery WHEREcondition 注 當更新的欄位是外鍵時須能參考到ParentTable的資料 否則會報ora 02291 integrityconstraint xx fk name violated parentkeynotfound需要提交COMMIT或回滾ROLLBACK 會鎖住更新的資料列RowLevelLock UPDATE DELETE指令 主要用來刪除表資料 語法 DELETE schema table WHEREcondition 注 當刪除時主鍵沒有被其他Table參考 否則會報ora 02292 integrityconstraint xx fk name violated childrecordfound 需要提交COMMIT或回滾ROLLBACK 會鎖住更新的資料列RowLevelLock DELETE COMMIT指令 確認新增 修改 刪除存檔 SAVEPOINT指令 建立回復註記 ROLLBACK指令 放棄新增 修改 刪除 語法 COMMIT SAVEPOINTsavepoint ROLLBACKTO SAVEPOINT savepoint 注 確保資料的一致性 可暫時的改變資料 下COMMIT前可以下ROLLBACK 新增 修改 刪除後一定要下COMMIT或ROLLBACK 將邏輯上相關的操作群組化 TOSAVEPOINT 回復指定的註記時點 COMMITSAVEPOINTROLLBACK 在下COMMIT或ROLLBACK前 修改前的資料狀態可以被復原 自己本身可以select己經修改的資料 其他使用者不會看見被修改的資料 資料會被locked住 其他使用者不能改變資料 在下COMMIT后 資料在資料庫當中永久改變 異動前的資料永久消失 所有使用者都可以看見結果 對於資料的lock會被解除 其他使用者可以異動此筆資料 所有的savepoints會被消除 当事务回滚到一保留点 具有SAVEPOINT 时 有下列情况 仅在该保留点之后执行的语句被撤消 该指定的保留点仍然被保留 该保留点之后所建立的保留点被删除 自该保留点之后所获取的全部表封锁和行封锁被释放 但指定的保留点以前所获取的全部数据封锁继续保持 该事务仍可继续 假如只有一個DML的語法失敗 只有這一個語法會被ROLLBACKOracleServer會建立一個內隱式的savepoint 所有其他的改變仍然被保留 使用者應該明確的執行COMMITorROLLBACK語法終止交易 ROLLBACKandSAVEPOINT Transaction SavepointA SavepointB COMMIT UPDATE INSERT COMMIT 一個資料庫交易包含一個下列的語法 改變資料的DML DATAMANIPULATIONLANGUAGE 語法INSERT UPDATE DELETE一個DDL DATADEFINITIONLANGUAGE 語法CREATE ALTER DROP一個DCL DATACONRTROLLANGUAGE 語法GRANT REVOKE一個資料庫交易的過程 交易事務開始 當第一個SQL語法被執行交易事務結束 下列任一事件發生時 COMMITorROLLBACK被執行DDLorDCL被執行 automaticcommit UserexitsSystemcrashes DatabaseTransactions 下列各類的情況會自動的commit DDLstatementisissuedDCLstatementisissuedNormalexitfromSQL Plus withoutexplicitlyissuingCOMMITorROLLBACK下列各類的情況會自動的rollback abnormalterminationofSQL Plusasystemfailure Sql分類 SQL指令共分四種類型 DML資料操作語言DataManipulationLanguage又稱資料修改語言DataModifyLanguage 這類SQL指令可對資料列進行擷取 新增 修改及刪除等工作 如SELECT INSERT UPDATE及DELETE等 TCL交易控制語言TransactionControlLanguage 任何系統跟資料庫溝通一定會有交易的問題 在資料庫系統內所謂交易是指由一個或多個相關的SQL命令所共同完成的工作 資料庫系統會利用確認COMMIT和回復ROLLBACK等指令來作交易控管 DDL資料定義語言DataDefinitionLanguage 可以用來建立CREATE 變更ALTER和棄置DROP資料庫物件DatabaseObject等指令 DCL資料控制語言DataControlLanguage 可以用來控制使用者對資料庫的存取權限控制 如GRANT授權 REVOKE撤銷和SETROLE角色設定等 view View的作用 可以限制資料的讀取 使複雜的查詢更容易充許資料的獨立 相同的資料可以建立不同的view 語法 CREATE ORREPLACE FORCE NOFORCE VIEWview alias alias ASsubquery WITHCHECKOPTION CONSTRAINTconstraint WITHREADO