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

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