特殊SQL語句及優化原則

 　　1.按姓氏筆畫排序:

　　Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

　　2.數據庫加密:

　　select encrypt('原始密碼')

　　select pwdencrypt('原始密碼')

　　select pwdcompare('原始密碼','加密後密碼') = 1--相同;否則不相同 encrypt('原始密碼')

　　select pwdencrypt('原始密碼')

　　select pwdcompare('原始密碼','加密後密碼') = 1--相同;否則不相同

　　3.取回表中字段:

　　declare @list varchar(1000),@sql nvarchar(1000)

　　select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'

　　set @sql='select '+right(@list,len(@list)-1)+' from 表A'

　　exec (@sql)

　　4.查看硬盤分區:

　　EXEC master..xp_fixeddrives

　　5.比較A,B表是否相等:

　　if (select checksum_agg(binary_checksum(*)) from A)

　　=

　　(select checksum_agg(binary_checksum(*)) from B)

　　print '相等'

　　else

　　print '不相等'

　　6.殺掉所有的事件探察器進程:

　　DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROMmaster.dbo.sysprocesses

　　WHERE program_name IN('SQL profiler',N'SQL 事件探查器')

　　EXEC sp_msforeach_worker '?'

　　7.記錄搜索:

　　開頭到N條記錄

　　Select Top N * From 表

　　-------------------------------

　　N到M條記錄(要有主索引ID)

　　Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc

　　----------------------------------

　　N到結尾記錄

　　Select Top N * From 表 Order by ID Desc

　　8.如何修改數據庫的名稱:

　　sp_renamedb 'old_name', 'new_name'

　　9：獲取當前數據庫中的所有用戶表

　　select Name from sysobjects where xtype='u' and status>=0

　　10：獲取某一個表的所有字段

　　select name from syscolumns where id=object_id('表名')

　　11：查看與某一個表相關的視圖、存儲過程、函數

　　select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

　　12：查看當前數據庫中所有存儲過程

　　select name as 存儲過程名稱 from sysobjects where xtype='P'

　　13：查詢用戶創建的所有數據庫

　　select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

　　或者

　　select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

　　14：查詢某一個表的字段和數據類型

　　select column_name,data_type from information_schema.columns

　　where table_name = '表名'

　　[n].[標題]:

　　Select * From TableName Order By CustomerName

　　[n].[標題]:

　　Select * From TableName Order By CustomerName

　　--------------------------------------------------------------------------------------

　　Sql優化是一項復雜的工作，以下的一些基本原則是本人看書時所記錄下來的，很明確且沒什麼廢話：

　　1. 索引的使用：

　　(1).當插入的數據為數據表中的記錄數量的10%以上，首先需要刪除該表的索引來提高數據的插入效率，當數據插入後，再建立索引。

　　(2).避免在索引列上使用函數或計算，在where子句中，如果索引是函數的一部分，優化器將不再使用索引而使用全表掃描。如：

　　低效：select * from dept where sal*12 >2500;

　　高效：select * from dept where sal>2500/12;

　　(3).避免在索引列上使用not和 “!=”，索引只能告訴什麼存在於表中，而不能告訴什麼不存在於表中，當數據庫遇到not 和 “!=”時，就會停止使用索引而去執行全表掃描。

　　(4).索引列上>=代替>

　　低效：select * from emp where deptno > 3

　　高效：select * from emp where deptno >=4

　　兩者的區別在於，前者dbms將直接跳到第一個deptno等於4的記錄，而後者將首先定位到deptno等於3的記錄並且向前掃描到第一個deptno大於3的。

　　(5).非要對一個使用函數的列啟用索引，基於函數的索引是一個較好的方案。

　　2. 游標的使用：

　　當在海量的數據表中進行數據的刪除、更新、插入操作時，用游標處理的效率是最慢的，但是游標又是必不可少的，所以正確使用游標十分重要：

　　(1). 在數據抽取的源表中使用時間戳，這樣每天的維表數據維護只針對更新日期為最新時間的數據來進行，大大減少需要維護的數據記錄數。

　　(2). 在insert和update維表時都加上一個條件來過濾維表中已經存在的記錄，例如：

　　insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)

　　ods_customer為數據源表。dim_customer為維表。

　　(3). 使用顯式的游標，因為隱式的游標將會執行兩次操作，第一次檢索記錄，第二次檢查too many rows這個exception,而顯式游標不執行第二次操作。

　　3. 據抽取和上載時的sql優化：

　　(1). Where 子句中的連接順序：

　　oracle采用自下而上的順序解析where子句，根據這個原理，表之間的連接必須寫在其他where條件之前，那些可以過濾掉大量記錄的條件必須寫在where子句的末尾。如：

　　低效：select * from emp e where sal>5000 and job = ‘manager’ and 25<(select count (*) from emp where mgr=e.empno);

　　高效：select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;

　　(2). 刪除全表時，用truncate 替代 delete,同時注意truncate只能在刪除全表時適用，因為truncate是ddl而不是dml。

　　(3). 盡量多使用commit

　　只要有可能就在程序中對每個delete,insert,update操作盡量多使用commit,這樣系統性能會因為commit所釋放的資源而大大提高。

　　(4). 用exists替代in ，可以提高查詢的效率。

　　(5). 用not exists 替代 not in

　　(6). 優化group by

　　提高group by語句的效率，可以將不需要的記錄在group by之前過濾掉。如：

　　低效：select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;

　　高效： select job, avg(sal) from emp having job=’president’ or job=’manager’ group by job;

　　(7). 有條件的使用union-all 替代 union：這樣做排序就不必要了，效率會提高3到5倍。

　　(8). 分離表和索引

　　總是將你的表和索引建立在不同的表空間內，決不要將不屬於oracle內部系統的對象存放到system表空間內。同時確保數據表空間和索引表空間置於不同的硬盤控制卡控制的硬盤上。