開(kāi)發(fā)部數(shù)據(jù)庫(kù)及數(shù)據(jù)操作培訓(xùn)資料.ppt

數(shù)據(jù)庫(kù)及數(shù)據(jù)操作培訓(xùn)資料 演示者 施運(yùn)淵 培訓(xùn)大綱 第1課 關(guān)系型數(shù)據(jù)庫(kù)簡(jiǎn)單介紹關(guān)系型數(shù)據(jù)庫(kù)及常用關(guān)系型數(shù)據(jù)庫(kù)的介紹 第2課 表表和字段的命名規(guī)范 字段數(shù)據(jù)類型 建表原則等 第3課 視圖視圖命名規(guī)范 索引視圖 可更新視圖 第4課 索引索引創(chuàng)建原則 哪些情況可能無(wú)法使用到索引 第5課 觸發(fā)器觸發(fā)器簡(jiǎn)單說(shuō)明 使用情況 培訓(xùn)大綱 第6課 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程說(shuō)明 使用情況 第7課 常用函數(shù)一些常用的函數(shù) 第8課 性能優(yōu)化如何著手優(yōu)化性能 第8課 常用技巧開(kāi)發(fā)時(shí)使用到的一些技巧 第1課 關(guān)系型數(shù)據(jù)庫(kù) 關(guān)系型數(shù)據(jù)庫(kù) 關(guān)系數(shù)據(jù)庫(kù)是建立在集合代數(shù)基礎(chǔ)上 應(yīng)用數(shù)學(xué)方法來(lái)處理數(shù)據(jù)庫(kù)中的數(shù)據(jù) 現(xiàn)實(shí)世界中的各種實(shí)體以及實(shí)體之間的各種聯(lián)系均用關(guān)系模型來(lái)表示 關(guān)系模型由關(guān)系數(shù)據(jù)結(jié)構(gòu) 關(guān)系操作集合 關(guān)系完整性約束三部分組成 關(guān)系型數(shù)據(jù)庫(kù)以行和列的形式存儲(chǔ)數(shù)據(jù) 以便于用戶理解 儲(chǔ)存與管理數(shù)據(jù)的基本形式是二維表 使用結(jié)構(gòu)化查詢語(yǔ)言 SQL 作為查詢工具 第1課 關(guān)系型數(shù)據(jù)庫(kù) 常用關(guān)系型數(shù)據(jù)庫(kù) OracleSQLServerSybaseDB2MySQL 第2課 數(shù)據(jù)表 表的定義表 TABLE 是數(shù)據(jù)庫(kù)中用來(lái)存儲(chǔ)數(shù)據(jù)的對(duì)象 是有結(jié)構(gòu)的數(shù)據(jù)的集合 是整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的基礎(chǔ) 表命名規(guī)則表名用T 開(kāi)頭 表名長(zhǎng)度不能超過(guò)30個(gè)字符 表名中含有單詞全部采用單數(shù)形式 單詞要大寫(xiě) 多個(gè)單詞間用下劃線 進(jìn)行連接 若庫(kù)中有多個(gè)系統(tǒng) 表名采用系統(tǒng)名稱 單詞或多個(gè)單詞 系統(tǒng)名是開(kāi)發(fā)系統(tǒng)的縮寫(xiě) 如VNET 表中含有的單詞建議用完整的單詞 如果導(dǎo)致表名長(zhǎng)度超過(guò)30個(gè)字符 則從最后一個(gè)單詞開(kāi)始 依次向前采用該單詞的縮寫(xiě) 第2課 數(shù)據(jù)表 字段命名規(guī)則字段名長(zhǎng)度不能超過(guò)30個(gè)字符 字段名中含有單詞全部采用單數(shù)形式 單詞要大寫(xiě) 多個(gè)單詞間用下劃線 進(jìn)行連接 字段名中含有的單詞建議用完整的單詞 如果導(dǎo)致長(zhǎng)度超過(guò)30個(gè)字符 則從最后一個(gè)單詞開(kāi)始 依次向前采用該單詞的縮寫(xiě)字段類型設(shè)置原則按實(shí)際業(yè)務(wù)需要來(lái)選擇數(shù)據(jù)類型 例如需要參與計(jì)算的就用數(shù)字型 與時(shí)間有關(guān)的就用日期型 切勿所有字段全設(shè)置成一種類型 數(shù)據(jù)表的關(guān)聯(lián)字段必須是同種類型 字段長(zhǎng)度也需與業(yè)務(wù)聯(lián)系 禁止盲目設(shè)置為最大值 第2課 數(shù)據(jù)表 常用字段類型字符型 Varchar 用來(lái)存放可變長(zhǎng)度的字符串 一般最大長(zhǎng)度在2000 4000左右 絕大部分字符串均可使用此類型 推薦使用 Char 定長(zhǎng)的字符串 長(zhǎng)度同Varchar Text Clob 用來(lái)存放可變長(zhǎng)度的字符串 幾乎無(wú)上限 LONG Oracle專有 超長(zhǎng)字符串 但不支持Like 并且每張表只能有1個(gè)LONG型字段 準(zhǔn)備廢棄的字段類型 不推薦 總結(jié) 在大部分情況下優(yōu)先使用Varchar類型 因?yàn)槠溥m應(yīng)性最好 Char可使用在長(zhǎng)度范圍變化較小的情況下 因?yàn)槠湫瘦^高 但是由于是定長(zhǎng)的字段 所以不足部分會(huì)以空格填充 編程時(shí)要特別注意 并且占用空間也較大 Nvarchar和Nchar與環(huán)境變量NLS指定的語(yǔ)言集密切相關(guān) 所以不推薦 第2課 數(shù)據(jù)表 常用字段類型數(shù)字型 Integer 整數(shù)型 4字節(jié) 值為 2 31 2 31 1Decimal p s 數(shù)字?jǐn)?shù)據(jù) 固定精度為P 寬度為S 優(yōu)先使用FLOAT 浮點(diǎn)數(shù)類型 NUMBER 38 雙精度 運(yùn)算可能會(huì)產(chǎn)生問(wèn)題 REAL 實(shí)數(shù)類型 NUMBER 63 精度更高總結(jié) 在大部分情況下優(yōu)先使用Decimal p s 類型 因?yàn)槠溥m應(yīng)性最好 Integer用于確認(rèn)數(shù)據(jù)為整數(shù)情況下 第2課 數(shù)據(jù)表 常用字段類型日期型 Datetime 描述某天的日期和時(shí)刻 二進(jìn)制型 Image Blob 存儲(chǔ)二進(jìn)制數(shù)據(jù) LongRAW Oracle獨(dú)有 準(zhǔn)備廢棄的字段類型 不推薦 第2課 數(shù)據(jù)表 主鍵 外鍵 非空字段 默認(rèn)數(shù)據(jù)及備注主鍵 所有的表必須要有主鍵 如果沒(méi)有業(yè)務(wù)邏輯上的主鍵 也需要?jiǎng)?chuàng)建一個(gè)無(wú)意義列作為主鍵 建議使用一個(gè)字段作為主鍵 可提高索引的大小及效率 外鍵 沒(méi)有必要的話不推薦設(shè)置外鍵 校驗(yàn)回滾等操作由程序來(lái)控制 過(guò)多使用外鍵只會(huì)增加系統(tǒng)的復(fù)雜度 非空字段 非空字段可以在數(shù)據(jù)層面對(duì)業(yè)務(wù)數(shù)據(jù)進(jìn)行校驗(yàn) 校驗(yàn)工作主要還是在程序里完成 非空字段只是作為最后的檢驗(yàn)手段 默認(rèn)數(shù)據(jù) 數(shù)字型 日期型建議盡可能設(shè)置默認(rèn)值 可以減少因?yàn)閿?shù)據(jù)原因而引起的程序錯(cuò)誤 備注 所有字段都必須填寫(xiě)備注 建表原則哈哈 第2課 數(shù)據(jù)表 建表原則與實(shí)體相關(guān) 也就是說(shuō)一張表能對(duì)應(yīng)現(xiàn)實(shí)世界的一種事物在某一方面的屬性或一種事務(wù)在某一方面的屬性 簡(jiǎn)單來(lái)說(shuō)一個(gè)員工 事物 其基本信息就應(yīng)該是獨(dú)立的一張表 一次員工的薪資調(diào)整 事務(wù) 也是獨(dú)立的一張表 正確理解一對(duì)一 一對(duì)多 多對(duì)多的關(guān)系 一對(duì)一 用戶的通訊方式 包括手機(jī) 居住地址等等 與用戶的基本信息 姓名 性別 等就是一對(duì)一的關(guān)系 建議將一對(duì)一的數(shù)據(jù)存儲(chǔ)在一張表內(nèi) 增加表的長(zhǎng)度而減少表的數(shù)量 一對(duì)多 用戶基本信息與用戶履歷就是一對(duì)多關(guān)系 可使用2張表 用戶基本信息表 用戶履歷表 來(lái)存儲(chǔ) 多對(duì)多 人員與崗位之間就是多對(duì)多關(guān)系 一般使用3張表來(lái)存儲(chǔ)這種結(jié)構(gòu) 人員表 崗位表 人員崗位關(guān)系表 第2課 數(shù)據(jù)表 建表原則通俗地理解三個(gè)范式 通俗地理解是夠用的理解 并不是最科學(xué)最準(zhǔn)確的理解 第一范式 1NF是對(duì)屬性的原子性約束 要求屬性具有原子性 不可再分解 第二范式 2NF是對(duì)記錄的惟一性約束 要求記錄有惟一標(biāo)識(shí) 即實(shí)體的惟一性 第三范式 3NF是對(duì)字段冗余性的約束 即任何字段不能由其他字段派生出來(lái) 它要求字段沒(méi)有冗余 沒(méi)有冗余的數(shù)據(jù)庫(kù)設(shè)計(jì)可以做到 但是 沒(méi)有冗余的數(shù)據(jù)庫(kù)未必是最好的數(shù)據(jù)庫(kù) 有時(shí)為了提高運(yùn)行效率 就必須降低范式標(biāo)準(zhǔn) 適當(dāng)保留冗余數(shù)據(jù) 第2課 數(shù)據(jù)表 建表原則計(jì)算列 列有兩種類型 數(shù)據(jù)列和計(jì)算列 數(shù)據(jù)列指的是原始采樣而得的數(shù)據(jù) 計(jì)算列是指對(duì)數(shù)據(jù)列通過(guò)一定的公式計(jì)算加工而得出的數(shù)據(jù)列 例如 單價(jià) 數(shù)量屬于數(shù)據(jù)列 總價(jià) 單價(jià) 數(shù)量就是計(jì)算列 根據(jù)業(yè)務(wù)需求 對(duì)于常用的計(jì)算數(shù)據(jù)我們可以通過(guò)增加冗余的計(jì)算列來(lái)減少查詢的時(shí)間 切記計(jì)算列需要使用觸發(fā)器或者程序來(lái)保持其正確性 正確認(rèn)識(shí)數(shù)據(jù)冗余 增加數(shù)據(jù)冗余的目的是為了減少表關(guān)聯(lián)從而提高檢索速度 切記冗余列需要使用觸發(fā)器或者程序來(lái)保持其正確性 在以下情況下可以考慮使用數(shù)據(jù)冗余 當(dāng)查詢的主表或從表中有一張或多張表數(shù)據(jù)量巨大 參與關(guān)聯(lián)的表太多 主要業(yè)務(wù)數(shù)據(jù) 也就是用戶經(jīng)常查詢的數(shù)據(jù) 第2課 數(shù)據(jù)表 建表原則三少原則 一個(gè)數(shù)據(jù)庫(kù)中表的個(gè)數(shù)越少越好 只有表的個(gè)數(shù)少了 才能說(shuō)明系統(tǒng)的E R圖少而精 去掉了重復(fù)的多余的實(shí)體 形成了對(duì)客觀世界的高度抽象 進(jìn)行了系統(tǒng)的數(shù)據(jù)集成 防止了打補(bǔ)丁式的設(shè)計(jì) 一個(gè)表中組合主鍵的字段個(gè)數(shù)越少越好 因?yàn)橹麈I的作用 一是建主鍵索引 二是做為子表的外鍵 所以組合主鍵的字段個(gè)數(shù)少了 不僅節(jié)省了運(yùn)行時(shí)間 而且節(jié)省了索引存儲(chǔ)空間 一個(gè)表中的字段個(gè)數(shù)越少越好 只有字段的個(gè)數(shù)少了 才能說(shuō)明在系統(tǒng)中不存在數(shù)據(jù)重復(fù) 且很少有數(shù)據(jù)冗余 更重要的是督促讀者學(xué)會(huì) 列變行 這樣就防止了將子表中的字段拉入到主表中去 在主表中留下許多空余的字段 所謂 列變行 就是將主表中的一部分內(nèi)容拉出去 另外單獨(dú)建一個(gè)子表 第2課 數(shù)據(jù)表 建表原則三少原則 一個(gè)數(shù)據(jù)庫(kù)中表的個(gè)數(shù)越少越好 只有表的個(gè)數(shù)少了 才能說(shuō)明系統(tǒng)的E R圖少而精 去掉了重復(fù)的多余的實(shí)體 形成了對(duì)客觀世界的高度抽象 進(jìn)行了系統(tǒng)的數(shù)據(jù)集成 防止了打補(bǔ)丁式的設(shè)計(jì) 一個(gè)表中組合主鍵的字段個(gè)數(shù)越少越好 因?yàn)橹麈I的作用 一是建主鍵索引 二是做為子表的外鍵 所以組合主鍵的字段個(gè)數(shù)少了 不僅節(jié)省了運(yùn)行時(shí)間 而且節(jié)省了索引存儲(chǔ)空間 一個(gè)表中的字段個(gè)數(shù)越少越好 只有字段的個(gè)數(shù)少了 才能說(shuō)明在系統(tǒng)中不存在數(shù)據(jù)重復(fù) 且很少有數(shù)據(jù)冗余 更重要的是督促讀者學(xué)會(huì) 列變行 這樣就防止了將子表中的字段拉入到主表中去 在主表中留下許多空余的字段 所謂 列變行 就是將主表中的一部分內(nèi)容拉出去 另外單獨(dú)建一個(gè)子表 第3課 視圖 視圖的作用視圖能簡(jiǎn)化用戶的操作 主要功能 視圖機(jī)制可以使用戶以不同的方式查詢同一數(shù)據(jù) 視圖對(duì)數(shù)據(jù)庫(kù)重構(gòu)提供了一定程度的邏輯獨(dú)立性 視圖可以對(duì)機(jī)密的數(shù)據(jù)提供安全保護(hù) 命名規(guī)則名稱用VW 開(kāi)頭 長(zhǎng)度不能超過(guò)30個(gè)字符 名稱中含有單詞全部采用單數(shù)形式 單詞要大寫(xiě) 多個(gè)單詞間用下劃線 進(jìn)行連接 名稱中含有的單詞建議用完整的單詞 如果導(dǎo)致長(zhǎng)度超過(guò)30個(gè)字符 則從最后一個(gè)單詞開(kāi)始 依次向前采用該單詞的縮寫(xiě) 第3課 視圖 索引視圖對(duì)于標(biāo)準(zhǔn)視圖而言 為每個(gè)引用視圖的查詢動(dòng)態(tài)生成結(jié)果集的開(kāi)銷很大 特別是對(duì)于那些涉及對(duì)大量行進(jìn)行復(fù)雜處理 如聚合大量數(shù)據(jù)或聯(lián)接許多行 的視圖 如果在查詢中頻繁地引用這類視圖 可通過(guò)對(duì)視圖創(chuàng)建唯一聚集索引來(lái)提高性能 對(duì)視圖創(chuàng)建唯一聚集索引后 結(jié)果集將存儲(chǔ)在數(shù)據(jù)庫(kù)中 就像帶有聚集索引的表一樣 如果很少更新基礎(chǔ)數(shù)據(jù) 則索引視圖的效果最佳 維護(hù)索引視圖的成本可能高于維護(hù)表索引的成本 索引視圖可以提高下列查詢類型的性能 處理大量行的聯(lián)接和聚合 許多查詢經(jīng)常執(zhí)行的聯(lián)接和聚合操作 決策支持工作負(fù)荷 第3課 視圖 索引視圖索引視圖通常不會(huì)提高下列查詢類型的性能 具有大量寫(xiě)操作的OLTP系統(tǒng) 具有大量更新的數(shù)據(jù)庫(kù) 不涉及聚合或聯(lián)接的查詢 GROUPBY鍵具有高基數(shù)度的數(shù)據(jù)聚合 GROUPBYID 語(yǔ)法 CREATETABLEwide tbl aintPRIMARYKEY bint zint CREATEVIEWv abcWITHSCHEMABINDINGASSELECTa b cFROMdbo wide tblWHEREaBETWEEN0AND1000CREATEUNIQUECLUSTEREDINDEXi abcONv abc a 第3課 視圖 可更新視圖利用視圖進(jìn)行數(shù)據(jù)增 刪 改操作 會(huì)受到一定的限制 由兩個(gè)以上的基本表導(dǎo)出的視圖 視圖的字段來(lái)自字段表達(dá)式函數(shù) 視圖定義中有嵌套查詢 在一個(gè)不允許更新的視圖上定義的視圖 第4課 索引 定義說(shuō)明使用索引可快速訪問(wèn)數(shù)據(jù)庫(kù)表中的特定信息 索引是對(duì)數(shù)據(jù)庫(kù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu) 例如employee表的姓 lname 列 如果要按姓查找特定職員 與必須搜索表中的所有行相比 索引會(huì)幫助您更快地獲得該信息 索引是一個(gè)單獨(dú)的 物理的數(shù)據(jù)庫(kù)結(jié)構(gòu) 它是某個(gè)表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識(shí)這些值的數(shù)據(jù)頁(yè)的邏輯指針清單 索引提供指向存儲(chǔ)在表的指定列中的數(shù)據(jù)值的指針 然后根據(jù)您指定的排序順序?qū)@些指針排序 數(shù)據(jù)庫(kù)使用索引的方式與您使用書(shū)籍中的索引的方式很相似 它搜索索引以找到特定值 然后順指針找到包含該值的行 第4課 索引 索引的優(yōu)點(diǎn)大大加快數(shù)據(jù)的檢索速度 創(chuàng)建唯一性索引 保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性 加速表和表之間的連接 在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí) 可以顯著減少查詢中分組和排序的時(shí)間 索引的缺點(diǎn)索引需要占物理空間 當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加 刪除和修改的時(shí)候 索引也要?jiǎng)討B(tài)的維護(hù) 降低了數(shù)據(jù)的維護(hù)速度 錯(cuò)誤的索引可能會(huì)減慢查詢速度 第4課 索引 索引類型唯一索引 唯一索引是不允許其中任何兩行具有相同索引值的索引 主鍵索引 在數(shù)據(jù)庫(kù)關(guān)系圖中為表定義主鍵將自動(dòng)創(chuàng)建主鍵索引 主鍵索引是唯一索引的特定類型 該索引要求主鍵中的每個(gè)值都唯一 當(dāng)在查詢中使用主鍵索引時(shí) 它還允許對(duì)數(shù)據(jù)的快速訪問(wèn) 聚集索引 在聚集索引中 表中行的物理順序與鍵值的邏輯 索引 順序相同 一個(gè)表只能包含一個(gè)聚集索引 非簇集索引 普通索引 第4課 索引 建索引原則經(jīng)常做為條件查詢的字段加上索引 如果幾個(gè)字段同時(shí)做為查詢時(shí)就建復(fù)合索引 不要索引常用的小型表 數(shù)據(jù)量小的表不需要索引 500行 不要在大字段上建立索引如文本型之類的 對(duì)于查詢中很少涉及的列或者重復(fù)值比較多的列 不要建立索引 對(duì)于按范圍查詢的列 最好建立索引 如交易日期等 表中若有主鍵或者外鍵 一定要為其建立索引 在SQL語(yǔ)句中經(jīng)常進(jìn)行GROUPBY ORDERBY的字段上建立索引 不要過(guò)度索引 修改表時(shí)數(shù)據(jù)庫(kù)需要更新索引 過(guò)度的索引不但耗費(fèi)額外的空間 而且降低了修改更新操作的效率 第4課 索引 無(wú)法使用到索引的一些情況當(dāng)WHERE條件中的字段是類似column1isnull或者column1isnotnull是 即便column1上面本來(lái)有索引也不會(huì)用到 對(duì)列進(jìn)行數(shù)據(jù)加工后再做比較 例如 column1 column2 aaabbb Substr column1 1 aaa 等Like的字符串中第一個(gè)字符如果是 則用不到索引 例如 Column1like aaa 是可以的Column1like aaa 用不到也用不到索引有時(shí)可以采取適當(dāng)措施改寫(xiě)后可以用到索引 例 column1有3個(gè)值 A B C 三個(gè)值的分布為 A 10 B 80 C 10 則column1 B 可以改寫(xiě)為column1in A C 第4課 索引 無(wú)法使用到索引的一些情況如果能不用到排序 則盡量避免排序 用到排序的情況有UNION 可用UNIONALL操作符替代UNION 因?yàn)閁NIONALL操作只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回 OrderbyGroupbyDistinctIn有時(shí)候也會(huì)用到排序確實(shí)要排序的時(shí)候也盡量要排序小數(shù)據(jù)量 盡量讓排序在內(nèi)存中執(zhí)行 第5課 觸發(fā)器 定義說(shuō)明觸發(fā)器 trigger 是個(gè)特殊的存儲(chǔ)過(guò)程 它的執(zhí)行不是由程序調(diào)用 也不是手工啟動(dòng) 而是由事件來(lái)觸發(fā) 比如當(dāng)對(duì)一個(gè)表進(jìn)行操作 insert delete update 時(shí)就會(huì)激活它執(zhí)行 觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等 財(cái)務(wù) 人事等系統(tǒng)較多使用 優(yōu)點(diǎn)觸發(fā)器可通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改 預(yù)編譯 已優(yōu)化 效率較高 避免了SQL語(yǔ)句在網(wǎng)絡(luò)傳輸然后再解釋的低效率 安全 不會(huì)有SQL語(yǔ)句注入問(wèn)題存在 不需要修改程序重新編譯 第5課 觸發(fā)器 缺點(diǎn)可移植性差 觸發(fā)器排錯(cuò)困難 而且數(shù)據(jù)容易造成不一致 后期維護(hù)不方便 影響數(shù)據(jù)庫(kù)的結(jié)構(gòu) 同時(shí)增加了維護(hù)的復(fù)雜程序 語(yǔ)法create orreplace trigger觸發(fā)器名觸發(fā)時(shí)間觸發(fā)事件on表名 foreachrow 第5課 觸發(fā)器 觸發(fā)時(shí)間 指明觸發(fā)器何時(shí)執(zhí)行before 表示在數(shù)據(jù)庫(kù)動(dòng)作之前觸發(fā)器執(zhí)行 after 表示在數(shù)據(jù)庫(kù)動(dòng)作之后出發(fā)器執(zhí)行 觸發(fā)事件 指明哪些數(shù)據(jù)庫(kù)動(dòng)作會(huì)觸發(fā)此觸發(fā)器insert 數(shù)據(jù)庫(kù)插入會(huì)觸發(fā)此觸發(fā)器 update 數(shù)據(jù)庫(kù)修改會(huì)觸發(fā)此觸發(fā)器 delete 數(shù)據(jù)庫(kù)刪除會(huì)觸發(fā)此觸發(fā)器 表名 數(shù)據(jù)庫(kù)觸發(fā)器所在的表 foreachrow 對(duì)表的每一行觸發(fā)器執(zhí)行一次 如果沒(méi)有這一選項(xiàng) 則只對(duì)整個(gè)表執(zhí)行一次 第5課 觸發(fā)器 觸發(fā)器無(wú)法使用的語(yǔ)句CREATE語(yǔ)句 如 CREATETABLE CREATEINDEX等 ALTER語(yǔ)句 如 ALTERTABLE ALTERINDEX等 DROP語(yǔ)句 如 DROPTABLE DROPINDEX等 第6課 存儲(chǔ)過(guò)程 定義說(shuō)明存儲(chǔ)過(guò)程 StoredProcedure 是一組為了完成特定功能的SQL語(yǔ)句集 經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中 用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù) 如果該存儲(chǔ)過(guò)程帶有參數(shù) 來(lái)執(zhí)行它 存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象 任何一個(gè)設(shè)計(jì)良好的數(shù)據(jù)庫(kù)應(yīng)用程序都應(yīng)該用到存儲(chǔ)過(guò)程 優(yōu)點(diǎn)預(yù)編譯 已優(yōu)化 效率較高 避免了SQL語(yǔ)句在網(wǎng)絡(luò)傳輸然后再解釋的低效率 安全 不會(huì)有SQL語(yǔ)句注入問(wèn)題存在 不需要修改程序重新編譯 第6課 存儲(chǔ)過(guò)程 缺點(diǎn)可移植性差 調(diào)試?yán)щy 影響數(shù)據(jù)庫(kù)的結(jié)構(gòu) 同時(shí)增加了維護(hù)的復(fù)雜程序 語(yǔ)法CREATEPROCEDURE 擁有者 存儲(chǔ)過(guò)程名 程序編號(hào) 參數(shù) 1 參數(shù) 1024 WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION AS程序行 第7課 常用函數(shù) 數(shù)字函數(shù)沒(méi)有特別特殊的 大家可以參考手冊(cè) 字符串函數(shù)字符串連接符Oracle Sybase SQLServer MySQL 無(wú)字符串連接函數(shù) CONCAT str1 str2 str3 INSTR C1 C2 I J 在一個(gè)字符串中搜索指定的字符 返回發(fā)現(xiàn)指定的字符的位置 C1被搜索的字符串C2希望搜索的字符串 第7課 常用函數(shù) 字符串函數(shù)LENGTH 返回字符串的長(zhǎng)度LOWER 返回字符串 并將所有的字符小寫(xiě)UPPER 返回字符串 并將所有的字符大寫(xiě)LTRIM和RTRIM LTRIM刪除左邊出現(xiàn)的字符串 RTRIM刪除右邊出現(xiàn)的字符串SUBSTR string start count 取子字符串 從start開(kāi)始 取count個(gè)REPLACE string s1 s2 string希望被替換的字符或變量 s1被替換的字符串 s2要替換的字符串 第7課 常用函數(shù) 日期函數(shù)當(dāng)前日期時(shí)間 Oracle SysdateSQLServer Sybase GetDate MySql Now curedate 等等 不同的是這些都不能作為字段默認(rèn)值 默認(rèn)值必須使用current timestamp 但是current timestamp只用在timestamp的列 對(duì)datetime列無(wú)效 日期轉(zhuǎn)換函數(shù) Oracle TO CHAR 日期字段 YYYY MM DDHH24 MI SS TO DATE 日期格式字符串 該字符串的格式 SQLServer Sybase 字符串可以直接當(dāng)做日期來(lái)插入 格式化獲取日期用Convert Char 長(zhǎng)度 日期字段 參數(shù) Char代表獲取的字符長(zhǎng)度 參數(shù)有很多 常用20 108 120 111等 第7課 常用函數(shù) 日期函數(shù)兩個(gè)日期之差 Oracle 日期1 日期2 返回帶小數(shù)點(diǎn)的天數(shù)SQLServer Sybase DATEDIFF datepart startdate enddate datepart 日期差的單位 MySql DATEDIFF startdate enddate 返回帶小數(shù)點(diǎn)的天數(shù)日期添加時(shí)間間隔 Oracle 時(shí)間函數(shù) 天數(shù) 可以帶小數(shù) SQLServer Sybase DATEADD datepart Number 日期字段 datepart 日期單位 Number 增加的數(shù)字 可以為負(fù) MySQL DATE ADD date INTERVALexprtype 第8課 性能優(yōu)化 在給定的系統(tǒng)硬件和系統(tǒng)軟件條件下 提高數(shù)據(jù)庫(kù)系統(tǒng)的運(yùn)行效率的辦法是 1 在數(shù)據(jù)庫(kù)物理設(shè)計(jì)時(shí) 降低范式 增加冗余 少用觸發(fā)器 多用存儲(chǔ)過(guò)程 2 當(dāng)計(jì)算非常復(fù)雜 而且記錄條數(shù)非常巨大時(shí) 例如一千萬(wàn)條 復(fù)雜計(jì)算要先在數(shù)據(jù)庫(kù)外面 以文件系統(tǒng)方式用C 語(yǔ)言計(jì)算處理完成之后 最后才入庫(kù)追加到表中去 3 發(fā)現(xiàn)某個(gè)表的記錄太多 例如超過(guò)一千萬(wàn)條 則要對(duì)該表進(jìn)行水平分割 水平分割的做法是 以該表主鍵PK的某個(gè)值為界線 將該表的記錄水平分割為兩個(gè)表 若發(fā)現(xiàn)某個(gè)表的字段太多 例如超過(guò)八十個(gè) 則垂直分割該表 將原來(lái)的一個(gè)表分解為兩個(gè)表 第9課 常用技巧 SQL語(yǔ)句性能一個(gè)SQL語(yǔ)句不要關(guān)聯(lián)太多的表 如果其中有大數(shù)據(jù)量的表時(shí) 盡量減少該表與其他表的關(guān)聯(lián) Where條件禁用或少用函數(shù)及字段數(shù)據(jù)操作 因?yàn)檫@樣無(wú)法使用到索引 例如SELECT FROMAwhereprice num 1000 Where子句順序?qū)π阅軟](méi)有影響 可能一些老版本的DBMS會(huì)有影響 為了統(tǒng)一 一般來(lái)說(shuō)表之間的連接必須寫(xiě)在其他WHERE條件之前 那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫(xiě)在WHERE子句的末尾 SELECT子句中避免使用 ORACLE在解析的過(guò)程中 會(huì)將 依次轉(zhuǎn)換成所有的列名 這個(gè)工作是通過(guò)查詢數(shù)據(jù)字典完成的 這意味著將耗費(fèi)更多的時(shí)間 第9課 常用技巧 SQL語(yǔ)句性能SELECT子句禁用或少用Distinct Distinct會(huì)引起全表掃描一般可以考慮用EXIST替換 EXISTS使查詢更為迅速 因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后 馬上返回結(jié)果 例子 低效 SELECTDISTINCTDEPT NO DEPT NAMEFROMDEPTD EMPEWHERED DEPT NO E DEPT NO 高效 SELECTDEPT NO DEPT NAMEFROMDEPTDWHEREEXISTS SELECT X FROMEMPEWHEREE DEPT NO D DEPT NO sql語(yǔ)句用大寫(xiě)的 因?yàn)閛racle總是先解析sql語(yǔ)句 把小寫(xiě)的字母轉(zhuǎn)換成大寫(xiě)的再執(zhí)行 第9課 常用技巧 SQL語(yǔ)句性能用EXISTS替代IN 用NOTEXISTS替代NOTIN 在許多基于基礎(chǔ)表的查詢中 為了滿足一個(gè)條件 往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接 在這種情況下 使用EXISTS 或NOTEXISTS 通常將提高查詢的效率 在子查詢中 NOTIN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并 無(wú)論在哪種情況下 NOTIN都是最低效的 因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷 為了避免使用NOTIN 我們可以把它改寫(xiě)成外連接 OuterJoins 或NOTEXISTS 例子 高效 SELECT FROMEMP 基礎(chǔ)表 WHEREEMPNO 0ANDEXISTS SELECT X FROMDEPTWHEREDEPT DEPTNO EMP DEPTNOANDLOC MELB 低效 SELECT FROMEMP 基礎(chǔ)表 WHEREEMPNO 0ANDDEPTNOIN SELECTDEPTNOFROMDEPTWHERELOC MELB 第9課 常用技巧 SQL語(yǔ)句性能使用表的別名 Alias 當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí) 請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上 這樣一來(lái) 就可以減少解析的時(shí)間并減少那些由Column歧義引起的語(yǔ)法錯(cuò)誤 用 替代 高效 SELECT FROMEMPWHEREDEPTNO 4低效 SELECT FROMEMPWHEREDEPTNO 3兩者的區(qū)別在于 前者DBMS將直接跳到第一個(gè)DEPT等于4的記錄而后者將首先定位到DEPTNO 3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄 避免在索引列上使用ISNULL和ISNOTNULL 第9課 常用技巧 SQL語(yǔ)句性能用UNION替換OR 適用于索引列 通常情況下 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果 對(duì)索引列使用OR將造成全表掃描 注重 以上規(guī)則只針對(duì)多個(gè)索引列有效 假如有column沒(méi)有被索引 查詢效率可能會(huì)因?yàn)槟銢](méi)有選擇OR而降低 在下面的例子中 LOC ID和REGION上都建有索引 高效 SELECTLOC ID LOC DESC REGIONFROMLOCATIONWHERELOC ID 10UNIONSELECTLOC ID LOC DESC REGIONFROMLOCATIONWHEREREGION MELBOURNE 低效 SELECTLOC ID LOC DESC REGIONFROMLOCATIONWHERELOC ID 10ORREGION MELBOURNE 假如你堅(jiān)持要用OR 那就需要返回記錄最少的索引列寫(xiě)在最前面 第9課 常用技巧 SQL語(yǔ)句性能總是使用索引的第一個(gè)列 假如索引是建立在多個(gè)列上 只有在它的第一個(gè)列 leadingcolumn 被where子句引用時(shí) 優(yōu)化器才會(huì)選擇使用該索引 這也是一條簡(jiǎn)單而重要的規(guī)則 當(dāng)僅引用索引的第二個(gè)列時(shí) 優(yōu)化器使用了全表掃描而忽略了索引用UNION ALL替換UNION 假如有可能的話 UNION ALL不會(huì)去除重復(fù)的行 慎用 索引只能告訴你什么存在于表中 而不能告訴你什么不存在于表中 使用count 或count 主鍵 而不要使用count column name 避免使用count distinctcolumn name 第9課 常用技巧 SQL語(yǔ)句性能等號(hào)右邊盡量不要使用字段名 如 select fromtbwherefield1 field3盡量少用子查詢 特別是相關(guān)子查詢 因?yàn)檫@樣會(huì)導(dǎo)致效率下降 使用count 或count 主鍵 而不要使用count column name 避免使用count distinctcolumn name 第9課 常用技巧 SQL語(yǔ)句調(diào)優(yōu)