學習SQL應知道的動态SQL語句基本文法

1 、普通SQL語句可以用Exec執行 eg: Select * from tableName Exec('select * from tableName') Exec sp_executesql N'select * from tableName' -- 請注意字元串前一定要加N 2、字段名，表名，資料庫名之類作為變量時，必須用動态SQL eg: declare @fname varchar(20) set @fname = 'FiledName' Select @fname from tableName -- 錯誤,不會提示錯誤，但結果為固定值FiledName,并非所要。 Exec('select ' + @fname + ' from tableName') -- 請注意 加号前後的 單引号的邊上加空格 當然将字元串改成變量的形式也可 declare @fname varchar(20) set @fname = 'FiledName' --設定字段名 declare @s varchar(1000) set @s = 'select ' + @fname + ' from tableName' Exec(@s) -- 成功 exec sp_executesql @s -- 此句會報錯 declare @s Nvarchar(1000) -- 注意此處改為nvarchar(1000) set @s = 'select ' + @fname + ' from tableName' Exec(@s) -- 成功 exec sp_executesql @s -- 此句正确 3、輸出參數 declare @num int, @sqls nvarchar(4000) set @sqls='select count(*) from tableName' exec(@sqls) --如何将exec執行結果放入變量中？ declare @num int, @sqls nvarchar(4000) set @sqls='select @a=count(*) from tableName ' exec sp_executesql @sqls,N'@a int output',@num output select @num 此外，如果想要在SQL語句 字元串中使用 單引号 '' 可以 使用 '''' SQL Server資料庫中經常用到的identity列 釋出時間：2008.03.24 04:59 來源：賽迪網 作者：Alice 【賽迪網－IT技術報道】SQL Server中，經常會用到Identity辨別列，這種自增長的字段操作起來的确是比較友善。但它有時還會帶來一些麻煩。 示例一 ：當表中被删除了某些資料的時候，自增長列的編号就不再是一個連線的數列。這種時候我們可以用以下方案來解決。 SET IDENTITY_INSERT [TABLE] [ON|OFF] 允許将顯式值插入表的辨別列中，當設定為ON時，這時可能在INSERT操作時手工指定插入到辨別列中的編号，同時必須在操作完成後，将IDENTITY_INSERT還原成OFF，否則下次插入的時候必須指定編号，那不然就無法完成INSERT操作。 示例二：當表中的記錄被全部删除，但此時辨別列的值越來越大的時候，如果不加以重置，它還會無休止的增長。這個時候我們就要用到： DBCC CHECKIDENT（TABLE, [RESEED|NORESEED], [1]) 将把指定表的種子值強制重設為1。然而，你可能不想将種子重設為1，在這種情況下，你可以用你想用的種子值替代第三個參數。有時候你可能想知道目前的種子，而不是想重設種子,這時你就要用到NORESEED，而不用再去顧忌第三個參數。 “一網打盡”通用SQL資料庫的查詢語句 （1） 釋出時間：2008.01.04 04:40 來源：賽迪網 作者：20933 通用SQL資料庫的查詢語句： （注釋：本文中Transact-SQL查詢隻包括選擇清單、FROM子句和WHERE子句。） 一、 簡單查詢 簡單的Transact-SQL查詢隻包括選擇清單、FROM子句和WHERE子句。它們分别說明所查詢列、查詢的表或視圖、以及搜尋條件等。 例如，下面的語句查詢testtable表中姓名為“張三”的nickname字段和email字段。 SELECT nickname,email FROM testtable WHERE name= '張三 ' (一) 選擇清單 選擇清單(select_list)指出所查詢列，它可以是一組列名清單、星号、表達式、變量(包括局部變量和全局變量)等構成。 1、選擇所有列 例如，下面語句顯示testtable表中所有列的資料: SELECT * FROM testtable 2、選擇部分列并指定它們的顯示次序 查詢結果集合中資料的排列順序與選擇清單中所指定的列名排列順序相同。 例如: SELECT nickname,email FROM testtable 3、更改列标題 在選擇清單中，可重新指定列标題。定義格式為: 列标題=列名 列名 列标題 如果指定的列标題不是标準的辨別符格式時，應使用引号定界符，例如，下列語句使用漢字顯示列标題: SELECT 昵稱=nickname,電子郵件=email FROM testtable 4、删除重複行 SELECT語句中使用ALL或DISTINCT選項來顯示表中符合條件的所有行或删除其中重複的資料行，預設為ALL。使用DISTINCT選項時，對于所有重複的資料行在SELECT傳回的結果集合中隻保留一行。 5、限制傳回的行數 使用TOP n [PERCENT]選項限制傳回的資料行數，TOP n說明傳回n行，而TOP n PERCENT時，說明n是表示一百分數，指定傳回的行數等于總行數的百分之幾。 例如: SELECT TOP 2 *FROM testtable SELECT TOP 20 PERCENT * FROM testtable (二)FROM子句 FROM子句指定SELECT語句查詢及與查詢相關的表或視圖。在FROM子句中最多可指定256個表或視圖，它們之間用逗号分隔。 在FROM子句同時指定多個表或視圖時，如果選擇清單中存在同名列，這時應使用對象名限定這些列所屬的表或視圖。例如在usertable和citytable表中同時存在cityid列，在查詢兩個表中的cityid時應使用下面語句格式加以限定: SELECT username,citytable.cityid FROM usertable,citytable WHERE usertable.cityid=citytable.cityid 在FROM子句中可用以下兩種格式為表或視圖指定别名: 表名 as 别名 表名 别名 例如上面語句可用表的别名格式表示為: SELECT username,b.cityid FROM usertable a,citytable b WHERE a.cityid=b.cityid SELECT不僅能從表或視圖中檢索資料，它還能夠從其它查詢語句所傳回的結果集合中查詢資料。 例如: SELECT a.au_fname+a.au_lname FROM authors a,titleauthor ta (SELECT title_id,title FROM titles WHERE ytd_sales> 10000 ) AS t WHERE a.au_id=ta.au_id AND ta.title_id=t.title_id 此例中，将SELECT傳回的結果集合給予一别名t，然後再從中檢索資料。 (三) 使用WHERE子句設定查詢條件 WHERE子句設定查詢條件，過濾掉不需要的資料行。例如下面語句查詢年齡大于20的資料: SELECT * FROM usertable WHERE age> 20 WHERE子句可包括各種條件運算符: 比較運算符(大小比較):> 、> =、=、 <、 <=、 <> 、!> 、! < 範圍運算符(表達式值是否在指定的範圍):BETWEEN…AND… NOT BETWEEN…AND… 清單運算符(判斷表達式是否為清單中的指定項):IN (項1,項2……) NOT IN (項1,項2……) 模式比對符(判斷值是否與指定的字元通配格式相符):LIKE、NOT LIKE 空值判斷符(判斷表達式是否為空):IS NULL、NOT IS NULL 邏輯運算符(用于多條件的邏輯連接配接):NOT、AND、OR 1、範圍運算符例:age BETWEEN 10 AND 30相當于age> =10 AND age <=30 2、清單運算符例:country IN ( 'Germany ', 'China ') 3、模式比對符例:常用于模糊查找，它判斷列值是否與指定的字元串格式相比對。可用于char、varchar、text、ntext、datetime和smalldatetime等類型查詢。 可使用以下通配字元: 百分号%:可比對任意類型和長度的字元，如果是中文，請使用兩個百分号即%%。 下劃線_:比對單個任意字元，它常用來限制表達式的字元長度。 方括号[]:指定一個字元、字元串或範圍，要求所比對對象為它們中的任一個。[^]:其取值也[] 相同，但它要求所比對對象為指定字元以外的任一個字元。 例如: 限制以Publishing結尾，使用LIKE '%Publishing ' 限制以A開頭:LIKE '[A]% ' 限制以A開頭外:LIKE '[^A]% ' 4、空值判斷符例WHERE age IS NULL 5、邏輯運算符:優先級為NOT、AND、OR (四)查詢結果排序 使用ORDER BY子句對查詢傳回的結果按一列或多列排序。ORDER BY子句的文法格式為: ORDER BY {column_name [ASC ¦DESC]} [,…n] 其中ASC表示升序，為預設值，DESC為降序。ORDER BY不能按ntext、text和image資料類型進行排序。 例如: SELECT * FROM usertable ORDER BY age desc,userid ASC 另外，可以根據表達式進行排序。 二、 聯合查詢 UNION運算符可以将兩個或兩個以上上SELECT語句的查詢結果集合合并成一個結果集合顯示，即執行聯合查詢。UNION的文法格式為: select_statement UNION [ALL] selectstatement [UNION [ALL] selectstatement][…n] 其中selectstatement為待聯合的SELECT查詢語句。 ALL選項表示将所有行合并到結果集合中。不指定該項時，被聯合查詢結果集合中的重複行将隻保留一行。 聯合查詢時，查詢結果的列标題為第一個查詢語句的列标題。是以，要定義列标題必須在第一個查詢語句中定義。要對聯合查詢結果排序時，也必須使用第一查詢語句中的列名、列标題或者列序号。 在使用UNION 運算符時，應保證每個聯合查詢語句的選擇清單中有相同數量的表達式，并且每個查詢選擇表達式應具有相同的資料類型，或是可以自動将它們轉換為相同的資料類型。在自動轉換時，對于數值類型，系統将低精度的資料類型轉換為高精度的資料類型。 在包括多個查詢的UNION語句中，其執行順序是自左至右，使用括号可以改變這一執行順序。例如: 查詢1 UNION (查詢2 UNION 查詢3) 三、連接配接查詢 通過連接配接運算符可以實作多個表查詢。連接配接是關系資料庫模型的主要特點，也是它差別于其它類型資料庫管理系統的一個标志。 在關系資料庫管理系統中，表建立時各資料之間的關系不必确定，常把一個實體的所有資訊存放在一個表中。當檢索資料時，通過連接配接操作查詢出存放在多個表中的不同實體的資訊。連接配接操作給使用者帶來很大的靈活性，他們可以在任何時候增加新的資料類型。為不同實體建立新的表，爾後通過連接配接進行查詢。 連接配接可以在SELECT 語句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出連接配接時有助于将連接配接操作與WHERE子句中的搜尋條件區分開來。是以，在Transact-SQL中推薦使用這種方法。 SQL-92标準所定義的FROM子句的連接配接文法格式為: FROM join_table join_type join_table [ON (join_condition)] 其中join_table指出參與連接配接操作的表名，連接配接可以對同一個表操作，也可以對多表操作，對同一個表操作的連接配接又稱做自連接配接。 join_type 指出連接配接類型，可分為三種:内連接配接、外連接配接和交叉連接配接。内連接配接(INNER JOIN)使用比較運算符進行表間某(些)列資料的比較操作，并列出這些表中與連接配接條件相比對的資料行。根據所使用的比較方式不同，内連接配接又分為等值連接配接、自然連接配接和不等連接配接三種。外連接配接分為左外連接配接(LEFT OUTER JOIN或LEFT JOIN)、右外連接配接(RIGHT OUTER JOIN或RIGHT JOIN)和全外連接配接(FULL OUTER JOIN或FULL JOIN)三種。與内連接配接不同的是，外連接配接不隻列出與連接配接條件相比對的行，而是列出左表(左外連接配接時)、右表(右外連接配接時)或兩個表(全外連接配接時)中所有符合搜尋條件的資料行。 交叉連接配接(CROSS JOIN)沒有WHERE 子句，它傳回連接配接表中所有資料行的笛卡爾積，其結果集合中的資料行數等于第一個表中符合查詢條件的資料行數乘以第二個表中符合查詢條件的資料行數。 連接配接操作中的ON (join_condition) 子句指出連接配接條件，它由被連接配接表中的列和比較運算符、邏輯運算符等構成。 無論哪種連接配接都不能對text、ntext和image資料類型列進行直接連接配接，但可以對這三種列進行間接連接配接。例如: SELECT p1.pub_id,p2.pub_id,p1.pr_info FROM pub_info AS p1 INNER JOIN pub_info AS p2 ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info) (一)内連接配接 内連接配接查詢操作列出與連接配接條件比對的資料行，它使用比較運算符比較被連接配接列的列值。内連接配接分三種: 1、等值連接配接:在連接配接條件中使用等于号(=)運算符比較被連接配接列的列值，其查詢結果中列出被連接配接表中的所有列，包括其中的重複列。 2、不等連接配接: 在連接配接條件使用除等于運算符以外的其它比較運算符比較被連接配接的列的列值。這些運算符包括> 、> =、 <=、 <、!> 、!

<和>

<> 。 3、自然連接配接:在連接配接條件中使用等于(=)運算符比較被連接配接列的列值，但它使用選擇清單指出查詢結果集合中所包括的列，并删除連接配接表中的重複列。 例，下面使用等值連接配接列出authors和publishers表中位于同一城市的作者和出版社: SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city 又如使用自然連接配接，在選擇清單中删除authors 和publishers 表中重複列(city和state): SELECT a.*,p.pub_id,p.pub_name,p.country FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city (二)外連接配接 内連接配接時，傳回查詢結果集合中的僅是符合查詢條件( WHERE 搜尋條件或 HAVING 條件)和連接配接條件的行。而采用外連接配接時，它傳回到查詢結果集合中的不僅包含符合連接配接條件的行，而且還包括左表(左外連接配接時)、右表(右外連接配接時)或兩個邊接表(全外連接配接)中的所有資料行。如下面使用左外連接配接将論壇内容和作者資訊連接配接起來: SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b ON a.username=b.username 下面使用全外連接配接将city表中的所有作者以及user表中的所有作者，以及他們所在的城市: SELECT a.*,b.* FROM city as a FULL OUTER JOIN user as b ON a.username=b.username (三)交叉連接配接 交叉連接配接不帶WHERE 子句，它傳回被連接配接的兩個表所有資料行的笛卡爾積，傳回到結果集合中的資料行數等于第一個表中符合查詢條件的資料行數乘以第二個表中符合查詢條件的資料行數。例，titles表中有6類圖書，而publishers表中有8家出版社，則下列交叉連接配接檢索到的記錄數将等。 于6*8=48行。 SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type [Post=0][/Post] 教你快速掌握一些異常精妙的"SQL"語句 釋出時間：2008.03.11 04:58 來源：賽迪網 作者：錢海歌 【賽迪網－IT技術報道】精妙的"SQL"語句: ◆複制表(隻複制結構,源表名：a 新表名：b) SQL: select * into b from a where 1<>1 　　 ◆拷貝表(拷貝資料,源表名：a 目标表名：b) SQL: insert into b(a, b, c) select d,e,f from b; 　 ◆顯示文章、送出人和最後回複時間 SQL: select a.title,a.username,b.adddate from table a, (select max(adddate) adddate from table where table.title=a.title) b 　 ◆說明：外連接配接查詢(表名1：a 表名2：b) SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c ◆日程安排提前五分鐘提醒 SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>5 　　 ◆兩張關聯表，删除主表中已經在副表中沒有的資訊 SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) ◆說明： SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1,(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE, 'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE (TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01',' YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, WHERE X.NUM = Y.NUM （+）AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM 　　 ◆說明： SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱='"&strdepartmentname&"' and 專業名稱 ='"&strprofessionname&"' order by 性别,生源地,聯考總成績 執行個體講解SQL Server中"Update"的用法 釋出時間：2008.02.28 05:07 來源：賽迪網 作者：Alizze SQL Server中"Update"的用法： 例子： 在表中有兩個字段：id_no (varchar) , in_date (datetime) ，把in_date相同的記錄的in_date依次累加1秒， 使in_date沒有相同的記錄。 以下為原始的資料: id_no in_date 5791 2003-9-1 14:42:02 5792 2003-9-1 14:42:02 5794 2003-9-1 14:42:02 5795 2003-9-1 14:42:03 5796 2003-9-1 14:42:03 5797 2003-9-1 14:42:03 5831 2003-9-1 14:42:04 5832 2003-9-1 14:42:14 5833 2003-9-1 14:42:14 結果為： id_no in_date 5791 2003-9-1 14:42:02 5792 2003-9-1 14:42:03 5794 2003-9-1 14:42:04 5795 2003-9-1 14:42:05 5796 2003-9-1 14:42:06 5797 2003-9-1 14:42:07 5831 2003-9-1 14:42:08 5832 2003-9-1 14:42:14 5833 2003-9-1 14:42:15 處理的方法： --建立測試環境 create table a(id_no varchar(8),in_date datetime) go insert into a select '5791','2003-9-1 14:42:02' union all select '5792','2003-9-1 14:42:02' union all select '5794','2003-9-1 14:42:02' union all select '5795','2003-9-1 14:42:03' union all select '5796','2003-9-1 14:42:03' union all select '5797','2003-9-1 14:42:03' union all select '5831','2003-9-1 14:42:04' union all select '5832','2003-9-1 14:42:04' union all select '5833','2003-9-1 14:42:04' union all select '5734','2003-9-1 14:42:02' union all select '6792','2003-9-1 14:42:22' union all select '6794','2003-9-1 14:42:22' union all select '6795','2003-9-1 14:42:23' union all select '6796','2003-9-1 14:42:23' union all select '6797','2003-9-1 14:42:23' union all select '6831','2003-9-1 14:42:34' union all select '6832','2003-9-1 14:42:34' union all select '6833','2003-9-1 14:42:54' union all select '6734','2003-9-1 14:42:22' go --生成臨時表，按照in_date排序 select * into # from a order by in_date --相同的時間，加一秒。加完了不帶重複的 declare @date1 datetime,@date2 datetime,@date datetime update # set @date=case when @date1=in_date or @date2>=in_date then dateadd(s,1,@date2) else in_date end, @date1=in_date, @date2=@date, in_date=@date --更新到基本表中去 update a set a.in_date=b.in_date from a a join # b on a.id_no=b.id_no select * from a drop table #,a 三種資料庫利用SQL語句進行高效果分頁 釋出時間：2008.01.21 04:50 來源：賽迪網 作者：10687 在程式的開發過程中，處理分頁是大家接觸比較頻繁的事件，因為現在軟體基本上都是與資料庫進行挂釣的。但效率又是我們所追求的，如果是像原來那樣把所有滿足條件的記錄全部都選擇出來，再去進行分頁處理，那麼就會多多的浪費掉許多的系統處理時間。為了能夠把效率提高，是以現在我們就隻選擇我們需要的資料，減少資料庫的處理時間，以下就是常用SQL分頁處理： 1、SQL Server、Access資料庫 這都微軟的資料庫，都是一家人，基本的操作都是差不多，常采用如下分頁語句： PAGESIZE：每頁顯示的記錄數 CURRENTPAGE：目前頁号 資料表的名字是：components 索引主鍵字是：id select top PAGESIZE * from components where id not in (select top (PAGESIZE*(CURRENTPAGE-1)) id from components order by id)order by id 如下列： select top 10 * from components where id not in (select top 10*10 id from components order by id) order by id 從101條記錄開始選擇，隻選擇前面的10條記錄 2、Oracle資料庫 因為Oracle資料庫沒有Top關鍵字，是以這裡就不能夠像微軟的資料據那樣操作，這裡有兩種方法： (1)、一種是利用相反的。 PAGESIZE：每頁顯示的記錄數 CURRENTPAGE：目前頁号 資料表的名字是：components 索引主鍵字是：id select * from components where id not in(select id from components where rownum<=(PAGESIZE*(CURRENTPAGE-1))) and rownum<=PAGESIZE order by id; 如下例： select * from components where id not in (select id from components where rownum<=100) and rownum<=10 order by id; 從101到記錄開始選擇，選擇前面10條。 (2)、使用minus，即中文的意思就是減去。 select * from components where rownum <=(PAGESIZE*(CURRENTPAGE-1)) minus select * from components where rownum <=(PAGESIZE*(CURRENTPAGE-2)); 如例：select * from components where rownum<=10 minus select * from components where rownum<=5;. (3)、一種是利用Oracle的rownum，這個是Oracle查詢自動傳回的序号，一般不顯示，但是可以通過select rownum from [表名]看到，注意，它是從1到目前的記錄總數。 select * from (select rownum tid,components. * from components where rownum<=100) where tid<=10; 深入講解SQL Server資料庫的嵌套子查詢 釋出時間：2008.02.02 05:05 來源：賽迪網 作者：Liulian 很多人對子查詢(subqueries)的使用都感到很迷惑，尤其對于嵌套子查詢(即子查詢中包含一個子查詢)。現在，就讓我們追本溯源地探究這個問題。 有兩種子查詢類型：标準和相關。标準子查詢執行一次，結果回報給父查詢。相關子查詢每行執行一次，由父查詢找回。在本文中，我将重點讨論嵌套子查詢(nested subqueries)(我将在以後介紹相關子查詢)。 試想這個問題:你想生成一個賣平墊圈的銷售人員清單。你需要的資料分散在四個表格中:人員.聯系方式(Person.Contact)，人力資源.員工(HumanResources.Employee)，銷售.銷售訂單标題(Sales.SalesOrderHeader)，銷售.銷售訂單詳情(Sales.SalesOrderDetail)。在SQL Server中，你從内壓式(outside-in)寫程式，但從外壓式(inside-out)開始考慮非常有幫助，即可以一次解決需要的一個語句。 如果從内到外寫起，可以檢查Sales.SalesOrderDetail表格，在LIKE語句中比對産品數(ProductNumber)值。你将這些行與Sales.SalesOrderHeader表格連接配接，從中可以獲得銷售人員IDs(SalesPersonIDs)。然後使用SalesPersonID連接配接SalesPersonID表格。最後，使用ContactID連接配接Person.Contact表格。 USE AdventureWorks ; GO SELECT DISTINCT c.LastName, c.FirstName FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE EmployeeID IN (SELECT SalesPersonID FROM Sales.SalesOrderHeader WHERE SalesOrderID IN (SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE ProductID IN (SELECT ProductID FROM Production.Product p WHERE ProductNumber LIKE'FW%'))); GO 這個例子揭示了有關SQL Server的幾個絕妙事情。你可以發現，可以用IN()參數替代SELECT 語句。在本例中，有兩次應用，是以建立了一個嵌套子查詢。 我是标準化(normalization)的發燒友，盡管我不接受其荒謬的長度。由于标準化具有各種查詢而增加了複雜性。在這些情況下子查詢就顯得非常有用，嵌套子查詢甚至更加有用。 當你需要的問題分散于很多表格中時，你必須再次将它們拼在一起，這時你可能發現嵌套子程式就很有用。 使用SQL視圖查出所有的資料庫字典 釋出時間：2008.01.07 09:15 來源：賽迪網 作者：20936 本文中的SQL代碼可以在企業管理器、查詢分析器中簡單執行，直接了當的查出SQL Server 2000及SQL Server 2005的所有資料字典。 （注釋：資料庫字典包括表結構(分SQL Server 2000和SQL Server 2005)、索引和主鍵.外鍵.限制.視圖.函數.存儲過程.觸發器。） SQL Server 2000資料庫字典—表結構.sql SELECT TOP 100 PERCENT --a.id, CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名, CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表說明, a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 辨別, CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主鍵, b.name AS 類型, a.length AS 長度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小數位數, CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允許空, ISNULL(e.text, '') AS 預設值, ISNULL(g.[value], '') AS 字段說明, d.crdate AS 建立時間, CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改時間 FROM dbo.syscolumns a LEFT OUTER JOIN dbo.systypes b ON a.xtype = b.xusertype INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOIN dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND g.name = 'MS_Description' LEFT OUTER JOIN dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND f.name = 'MS_Description' ORDER BY d.name, a.colorder SQL Server 2005資料庫字典--表結構.sql SELECT TOP 100 PERCENT --a.id, CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名, CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表說明, a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 辨別, CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主鍵, b.name AS 類型, a.length AS 長度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小數位數, CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允許空, ISNULL(e.text, '') AS 預設值, ISNULL(g.[value], '') AS 字段說明, d.crdate AS 建立時間, CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改時間 FROM dbo.syscolumns a LEFT OUTER JOIN dbo.systypes b ON a.xtype = b.xusertype INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOIN dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND g.name = 'MS_Description' LEFT OUTER JOIN dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND f.name = 'MS_Description' ORDER BY d.name, a.colorder SQL Server資料庫字典--索引.sql SELECT TOP 100 PERCENT --a.id, CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名, CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名稱, d.name AS 列名, b.keyno AS 索引順序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending') WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL THEN '' ELSE '√' END AS 主鍵, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id, a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一, CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一限制, a.OrigFillFactor AS 填充因子, c.crdate AS 建立時間, c.refdate AS 更改時間 FROM dbo.sysindexes a INNER JOIN dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN dbo.sysobjects p ON p.name = a.name AND p.xtype = 'PK' WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id, N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0) ORDER BY c.name, a.name, b.keyno SQL Server資料庫字典--主鍵.外鍵.限制.視圖.函數.存儲過程.觸發器.sql SELECT DISTINCT TOP 100 PERCENT o.xtype, CASE o.xtype WHEN 'X' THEN '擴充存儲過程' WHEN 'TR' THEN '觸發器' WHEN 'PK' THEN '主鍵' WHEN 'F' THEN '外鍵' WHEN 'C' THEN '限制' WHEN 'V' THEN '視圖' WHEN 'FN' THEN '函數-标量' WHEN 'IF' THEN '函數-内嵌' WHEN 'TF' THEN '函數-表值' ELSE '存儲過程' END AS 類型, o.name AS 對象名, o.crdate AS 建立時間, o.refdate AS 更改時間, c.text AS 聲明語句 FROM dbo.sysobjects o LEFT OUTER JOIN dbo.syscomments c ON o.id = c.id WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND (OBJECTPROPERTY(o.id, N'IsMSShipped') = 0) ORDER BY CASE o.xtype WHEN 'X' THEN '擴充存儲過程' WHEN 'TR' THEN '觸發器' WHEN 'PK' THEN '主鍵' WHEN 'F' THEN '外鍵' WHEN 'C' THEN '限制' WHEN 'V' THEN '視圖' WHEN 'FN' THEN '函數-标量' WHEN 'IF' THEN '函數-内嵌' WHEN 'TF' THEN '函數-表值' ELSE '存儲過程' END DESC 兩個表間不存在的insert與存在的update 釋出時間：2008.02.29 05:07 來源：賽迪網 作者：Alice 兩個表間,不存在的insert與存在的update示例： IF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULL BEGIN DROP PROCEDURE dbo.sp_showtable_insert IF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_showtable_insert >>>' ELSE PRINT '<<< DROPPED PROCEDURE dbo.sp_showtable_insert >>>' END go SET ANSI_NULLS ON go SET QUOTED_IDENTIFIER ON go create procedure [dbo].[sp_showtable_insert] @tablename1 varchar(100), @tablename2 varchar(100) as begin DECLARE @MAX_ID NUMERIC(18,0) DECLARE @MAX_ID2 NUMERIC(18,0) create table #ins_tab(fg int,col_name1 nvarchar(150),col_name_val nvarchar(150),col_name2 nvarchar(150),colid numeric(18,0)) insert into #ins_tab(fg,col_name1,col_name_val,colid) values(0,'INSERT INTO '+@tablename1,'',10) insert into #ins_tab(fg,col_name1,col_name_val) values(1,'(','') insert into #ins_tab(fg,col_name1,col_name_val,colid) select 2,b.name,'/*'+b.name+'_Value*/',b.colid from sysobjects a ,syscolumns b where a.name=@tablename1 and a.id=b.id and a.type='U' insert into #ins_tab(fg,col_name1,col_name_val) values(3,')','') insert into #ins_tab(fg,col_name1,col_name_val) values(4,'SELECT ','') insert into #ins_tab(fg,col_name1,col_name_val,colid) select 5,'--'+b.name,b.name,b.colid from sysobjects a ,syscolumns b where a.name=@tablename2 and a.id=b.id and a.type='U' update #ins_tab set col_name2=b.name from sysobjects a ,syscolumns b,#ins_tab c where a.name=@tablename2 and a.id=b.id and a.type='U' and c.col_name1=b.name and c.fg=2 update #ins_tab set col_name_val= CASE when isnull(col_name2,'1') ='1' THEN 'null'+col_name_val else col_name2+col_name_val end where fg=2 delete #ins_tab from #ins_tab a where a.fg=5 and exists(select 1 from #ins_tab b where b.col_name1=a.col_name_val and b.fg=2) insert into #ins_tab(fg,col_name1,col_name_val,colid) values(6,'FROM '+@tablename2,'',10) insert into #ins_tab(fg,col_name1,col_name_val,colid) values(7,'UPDATE '+@tablename1,'',10) insert into #ins_tab(fg,col_name1,col_name_val) values(8,' SET ','') insert into #ins_tab(fg,col_name1,col_name_val,colid) SELECT 9,' '+substring(@tablename1+'.'+col_name1+' ',1,60)+'='+@tablename2+'.'+col_name1,'',colid FROM #ins_tab where fg=2 AND isnull(col_name2,'1')<>'1' insert into #ins_tab(fg,col_name1,col_name_val) SELECT 10,' FROM '+@tablename1+','+@tablename2,'' insert into #ins_tab(fg,col_name1,col_name_val) SELECT 11,'WHERE '+@tablename1+'.='+@tablename2+'.','' SELECT @MAX_ID=MAX(colid) from #ins_tab where fg=2 select 0 AS FG,'-- INSERT '+@tablename1+' FROM '+@tablename2,0 AS colid union select fg,col_name1,colid from #ins_tab where fg=0 union select fg,col_name1,colid from #ins_tab where fg=1 union select fg,CASE WHEN colid=@MAX_ID THEN ' ' +col_name1 ELSE ' '+col_name1+',' END AS col_name1, colid from #ins_tab where fg=2 union select fg,col_name1,colid from #ins_tab where fg=3 union select fg,col_name1,colid from #ins_tab where fg=4 union select 5 as fg,CASE WHEN colid=@MAX_ID THEN ' ' +col_name_val ELSE ' '+col_name_val+',' END AS col_name1,colid from #ins_tab where fg=2 union select 6 as fg,col_name1,colid from #ins_tab where fg=6 union select 6 AS FG,'WHERE NOT EXISTS(SELECT 1 FROM '+@tablename1+' WHERE '+@tablename1+'.='+@tablename2+'.',21 AS colid union select 8 AS FG,'-- UPDATE '+@tablename1+' FROM '+@tablename2,0 AS colid UNION select 7 as fg,col_name1,colid from #ins_tab where fg=5 --UPDATE union select 8 as fg,col_name1,colid from #ins_tab where fg=7 union select 9 as fg,col_name1,colid from #ins_tab where fg=8 union select 10 as fg,CASE WHEN colid=@MAX_ID THEN col_name1 ELSE col_name1+',' END AS col_name1, colid from #ins_tab where fg=9 union select 11 as fg,col_name1,colid from #ins_tab where fg=10 union select 12 as fg,col_name1,colid from #ins_tab where fg=11 union select 13 as fg,col_name1,colid from #ins_tab where fg=12 order by fg,colid drop table #ins_tab end go SET ANSI_NULLS OFF go SET QUOTED_IDENTIFIER OFF go IF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULL PRINT '<<< CREATED PROCEDURE dbo.sp_showtable_insert >>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_showtable_insert >>>' go 實作跨多個表格的資料進行組合的SQL語句 （1） 釋出時間：2008.01.25 07:38 來源：賽迪網 作者：武西 在對跨多個表格的資料進行組合時，有時很難搞清楚要使用哪一個SQL句法。我将在這裡對将多個表格中的查詢合并至單一聲明中的常用方式進行闡述。 在這篇文章中的樣本查詢符合SQL92 ISO标準。不是所有的資料庫生産商都遵循這項标準，而且很多廠商采取的提升措施會帶來一些意料不到的後果。如果你不确定你的資料庫是不是支援這些标準，你可以參看生産廠商的有關資料。 SELECT 一個簡單的SELECT聲明就是查詢多個表格的最基本的方式。你可以在FROM子句中調用多個表格來組合來自多個表格的結果。這裡是一個它如何工作的執行個體： 以下為引用的内容： SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1; 這個執行個體中，我使用點号(table1.column1)來指定專欄來自哪一個表格。如果所涉及的專欄隻在一個參考的表格中出現，你就不需要加入完整的名稱，但是加入完整名稱會對可讀性起到幫助。 在FROM子句中表格之間由逗号來分隔，你可以加入所需的任意多的表格，盡管一些資料庫有一個在引入正式的JOIN聲明之前他們可以有效地處理的内容這方面的限制，這個将在下面談到。 這個句法是一個簡單的INNER JOIN。一些資料庫将它看成與一個外部的JOIN是等同的。WHERE子句告知資料庫哪一個區域要做關聯，而且它傳回結果時，就像列出的表格在給定的條件下組合成一個單獨的表格一樣。值得注意的是，你的比較條件并不需要與你作為結果組傳回的專欄相同。在上面的例子中，table1.column1和table2.column1用來組合表格，但是傳回的卻是table2.column2。 你可以在WHERE子句中使用AND關鍵字來将這個功能擴充至多于兩個的表格。你還可以使用這樣的表格組合來限制你的結果而不用實際地從每個表格傳回專欄。在下面的例子中，table3與table1比對，但是我沒有從table3傳回任何東西來顯示。我隻是確定來自table1的有關專欄存在于table3之中。注意此例中table3需要在FROM子句中被引用。 以下為引用的内容： SELECT table1.column1, table2.column2 FROM table1, table2, table3 WHERE table1.column1 = table2.column1 AND table1.column1 = table3.column1; 然而，要注意的是，這個查詢多個表格的方式是一個暗指的JOIN。你的資料庫可能對事物進行不同的處理，這取決于它所使用的優化引擎。而且，忽略對與WHERE子句的相關特性的定義将會給你帶來不願看到的結果，例如從餘下的查詢中傳回與每一個可能的結果相關的專欄的rogue域，就像在CROSS JOIN之中一樣。 如果你習慣于你的資料庫處理這種類型的聲明的方式，且你隻對兩個或是少數幾個表格進行組合，一個簡單的SELECT聲明就可以達到目的。 JOIN JOIN的工作方式與SELECT聲明是相同的，它從不同的表格中傳回一個帶有專欄的結果組。在暗含的JOIN之上使用外部JOIN的優勢是對你的結果組的更好的控制，而且還可能在涉及很多個表格的情況下提升性能表現。 JOIN的類型有幾種：LEFT，RIGHT，FULL OUTER，INNER和CROSS。你所使用的類型是由你想要看到的結果所決定的。例如，使用LEFT OUTER JOIN将會從列出的第一個表格中傳回所有有關的行，而同時如果沒有資訊與第一個表格相關的話将潛在地從所列出的第二個表格中加入行。 在這裡INNER JOIN和暗含的JOIN是不同的，INNER JOIN将隻傳回那些在兩個表格中都有資料的行。 對第一個SELECT查詢使用如下JOIN聲明： 以下為引用的内容： SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1; 子查詢 子查詢，或叫子選擇聲明，是在一個查詢中将結果組作為資源使用的一個途徑。他經常被用來對結果進行限制或定義，而不是運作多個查詢或操縱應用軟體之中的資料。有了子查詢，你可以參考表格來決定資料的内含，或是在一些情況下，傳回一個專欄，而這個專欄是一個子選擇的結果。 下面的例子中使用了兩個表格。一個表格中包含了我想要傳回的資料，而另一個表格則給出一個比較點來确定什麼資料是我确實感興趣的。 以下為引用的内容： SELECT column1 FROM table1 WHERE EXISTS ( SELECT column1 FROM table2 WHERE table1.column1 = table2.column1 ); 子查詢很重要的一個方面就是性能表現。便利性是有代價的，它取決于你所使用的表格和聲明的大小，數量和複雜性，還有你可能會允許你的應用軟體做處理工作。每一個查詢在被主查詢作為資源使用之前，都将被完整地單獨處理。如果可能的話，創造性地使用JOIN聲明可以以較少的滞後時間提供出相同的資訊。 （責任編輯：盧兆林） 深入講解SQL Union和Union All的使用方法 釋出時間：2008.03.19 04:41 來源：賽迪網 作者：李思 【賽迪網－IT技術報道】UNION指令的目的是将兩個SQL語句的結果合并起來。從這個角度來看， 我們會産生這樣的感覺，UNION跟JOIN似乎有些許類似，因為這兩個指令都可以由多個表格中撷取資料。 UNION的一個限制是兩個 SQL 語句所産生的欄位需要是同樣的資料種類。另外，當我們用 UNION這個指令時，我們隻會看到不同的資料值 (類似 SELECT DISTINCT)。 union隻是将兩個結果聯結起來一起顯示，并不是聯結兩個表…… UNION 的文法如下： [SQL 語句 1] UNION [SQL 語句 2] 假設我們有以下的兩個表格， Store_Information 表格 store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999 Internet Sales 表格 Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750 而我們要找出來所有有營業額 (sales) 的日子。要達到這個目的，我們用以下的 SQL 語句： SELECT Date FROM Store_Information UNION SELECT Date FROM Internet_Sales 結果: Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-10-1999 Jan-11-1999 Jan-12-1999 有一點值得注意的是，如果我們在任何一個 SQL 語句 (或是兩句都一起) 用 "SELECT DISTINCT Date" 的話，那我們會得到完全一樣的結果。 SQL Union All UNION ALL 這個指令的目的也是要将兩個 SQL 語句的結果合并在一起。 UNION ALL 和 UNION 不同之處在于 UNION ALL 會将每一筆符合條件的資料都列出來，無論資料值有無重複。 UNION ALL 的文法如下： [SQL 語句 1] UNION ALL [SQL 語句 2] 我們用和上一頁同樣的例子來顯示出 UNION ALL 和 UNION 的不同。同樣假設我們有以下兩個表格: Store_Information 表格 store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999 Internet Sales 表格 Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750 而我們要找出有店面營業額以及網絡營業額的日子。要達到這個目的，我們用以下的SQL語句： SELECT Date FROM Store_Information UNION ALL SELECT Date FROM Internet_Sales 結果: Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-08-1999 Jan-07-1999 Jan-10-1999 Jan-11-1999 Jan-12-1999 ============ 表1 A B a 1 b 0 c 3 d 0 e 2 表2 A B c 0 e 4 合并兩個表除去重複的資料（以表2的資料為主），我們将會得到以下的表: A B a 1 b 0 c 0 d 0 e 4 select A,B from 表1 where A not in(select A from 表2) union select A,B from 表2 巧用一條SQL 實作其它進制到十進制轉換 釋出時間：2007.09.25 04:57 來源：賽迪網 作者：han 問：怎樣實作ORACLE中用一條SQL實作其它進制到十進制的轉換？ 答：具體示例如下： -----二進制轉換十進制----------------- select sum(data1) from (select substr('1101', rownum, 1) * power (2, length('1101') - rownum) data1 from dual connect by rownum <= length('1101')) -----八進制轉換十進制----------------- select sum(data1) from (select substr('1101', rownum, 1) * power (8, length('1101') - rownum) data1 from dual connect by rownum <= length('1101')) -----十六進制轉換十進制----------------- select sum(data1) from (select (CASE upper(substr('2D', rownum, 1)) WHEN 'A' THEN '10' WHEN 'B' THEN '11' WHEN 'C' THEN '12' WHEN 'D' THEN '13' WHEN 'E' THEN '14' WHEN 'F' THEN '15' ELSE substr('2D', rownum, 1) END) * power(16, length('2D') - rownum) data1 from dual connect by rownum <= length('2D')) 注釋： 對其它進制可以根據例子将power的底數改成相應的進制就可以了。 本文隻是一個例子，大家可以把它封裝成一個通用函數進行實用。 大家在試的時候将裡面相應的其它進制的數值換成自己的資料就可以了（有多處）。 執行個體講解如何才能讓你的SQL運作得更快 （1） 釋出時間：2008.01.22 04:52 來源：賽迪網 作者：10633 很多人在使用SQL時往往會陷入一個誤區，即太關注于所得的結果是否正确，而忽略了不同的實作方法之間可能存在的性能差異，這種性能差異在大型的或是複雜的資料庫環境中（如聯機事務處理OLTP或決策支援系統DSS）中表現得尤為明顯。 筆者在工作實踐中發現，不良的SQL往往來自于不恰當的索引設計、不充份的連接配接條件和不可優化的where子句。 在對它們進行适當的優化後，其運作速度有了明顯地提高！ 下面我将從這三個方面分别進行總結： 為了更直覺地說明問題，所有執行個體中的SQL運作時間均經過測試，不超過１秒的均表示為（< 1秒）。---- 測試環境: 主機：HP LH II---- 主頻：330MHZ---- 記憶體：128兆---- 作業系統：Operserver5.0.4---- 資料庫：Sybase11.0.3 一、不合理的索引設計 例：表record有620000行，試看在不同的索引下，下面幾個 SQL的運作情況： 1.在date上建有一非個群集索引 select count(*) from record where date > '19991201' and date < '19991214'and amount >2000 (25秒) select date ,sum(amount) from record group by date(55秒) select count(*) from record where date > '19990901' and place in ('BJ','SH') (27秒) 分析:date上有大量的重複值，在非群集索引下，資料在實體上随機存放在資料頁上，在範圍查找時，必須執行一次表掃描才能找到這一範圍内的全部行。 2.在date上的一個群集索引 select count(*) from record where date > '19991201' and date < '19991214' and amount >2000 （14秒） select date,sum(amount) from record group by date（28秒） select count(*) from record where date > '19990901' and place in ('BJ','SH')（14秒） 分析：在群集索引下，資料在實體上按順序在資料頁上，重複值也排列在一起，因而在範圍查找時，可以先找到這個範圍的起末點，且隻在這個範圍内掃描資料頁，避免了大範圍掃描，提高了查詢速度。 3.在place，date，amount上的組合索引 select count(*) from record where date > '19991201' and date < '19991214' and amount >2000 （26秒） select date,sum(amount) from record group by date（27秒） select count(*) from record where date > '19990901' and place in ('BJ, 'SH')（< 1秒） 分析：這是一個不很合理的組合索引，因為它的前導列是place，第一和第二條SQL沒有引用place，是以也沒有利用上索引；第三個SQL使用了place，且引用的所有列都包含在組合索引中，形成了索引覆寫，是以它的速度是非常快的。 4.在date，place，amount上的組合索引 select count(*) from record where date > '19991201' and date < '19991214' and amount >2000(< 1秒) select date,sum(amount) from record group by date（11秒） select count(*) from record where date > '19990901' and place in ('BJ','SH')（< 1秒） 分析：這是一個合理的組合索引。它将date作為前導列，使每個SQL都可以利用索引，并且在第一和第三個SQL中形成了索引覆寫，因而性能達到了最優。 5.總結： 預設情況下建立的索引是非群集索引，但有時它并不是最佳的；合理的索引設計要建立在對各種查詢的分析和預測上。 一般來說： ①.有大量重複值、且經常有範圍查詢（between, >,< ，>=,< =）和order by、group by發生的列，可考慮建立群集索引； ②.經常同時存取多列，且每列都含有重複值可考慮建立組合索引； ③.組合索引要盡量使關鍵查詢形成索引覆寫，其前導列一定是使用最頻繁的列。 二、不充份的連接配接條件： 例：表card有7896行，在card_no上有一個非聚集索引，表account有191122行，在account_no上有一個非聚集索引，試看在不同的表連接配接條件下，兩個SQL的執行情況： select sum(a.amount) from account a, card b where a.card_no = b.card_no（20秒） select sum(a.amount) from account a, card b where a.card_no = b.card_no and a.account_no=b.account_no（< 1秒） 分析：>在第一個連接配接條件下，最佳查詢方案是将account作外層表，card作内層表，利用card上的索引，其I/O次數可由以下公式估算為： 外層表account上的22541頁+（外層表account的191122行*内層表card上對應外層表第一行所要查找的3頁）=595907次I/O 在第二個連接配接條件下，最佳查詢方案是将card作外層表，account作内層表，利用account上的索引，其I/O次數可由以下公式估算為：外層表card上的1944頁+（外層表card的7896行*内層表account上對應外層表每一行所要查找的4頁）= 33528次I/O 可見，隻有充份的連接配接條件，真正的最佳方案才會被執行。 總結： 1.多表操作在被實際執行前，查詢優化器會根據連接配接條件，列出幾組可能的連接配接方案并從中找出系統開銷最小的最佳方案。連接配接條件要充份考慮帶有索引的表、行數多的表；内外表的選擇可由公式：外層表中的比對行數*内層表中每一次查找的次數确定，乘積最小為最佳方案。 2.檢視執行方案的方法-- 用set showplanon，打開showplan選項，就可以看到連接配接順序、使用何種索引的資訊；想看更詳細的資訊，需用sa角色執行dbcc(3604,310,302)。 三、不可優化的where子句 1.例：下列SQL條件語句中的列都建有恰當的索引，但執行速度卻非常慢： select * from record wheresubstring(card_no,1,4)='5378'(13秒) select * from record whereamount/30< 1000（11秒） select * from record whereconvert(char(10),date,112)='19991201'（10秒） 分析： where子句中對列的任何操作結果都是在SQL運作時逐列計算得到的，是以它不得不進行表搜尋，而沒有使用該列上面的索引； 如果這些結果在查詢編譯時就能得到，那麼就可以被SQL優化器優化，使用索引，避免表搜尋，是以将SQL重寫成下面這樣： select * from record where card_no like'5378%'（< 1秒） select * from record where amount< 1000*30（< 1秒） select * from record where date= '1999/12/01'（< 1秒） 你會發現SQL明顯快起來！ 2.例：表stuff有200000行，id_no上有非群集索引，請看下面這個SQL： select count(*) from stuff where id_no in('0','1')（23秒） 分析：---- where條件中的'in'在邏輯上相當于'or'，是以文法分析器會将in ('0','1')轉化為id_no ='0' or id_no='1'來執行。 我們期望它會根據每個or子句分别查找，再将結果相加，這樣可以利用id_no上的索引； 但實際上（根據showplan）,它卻采用了"OR政策"，即先取出滿足每個or子句的行，存入臨時資料庫的工作表中，再建立唯一索引以去掉重複行，最後從這個臨時表中計算結果。是以，實際過程沒有利用id_no上索引，并且完成時間還要受tempdb資料庫性能的影響。 實踐證明，表的行數越多，工作表的性能就越差，當stuff有620000行時，執行時間竟達到220秒！還不如将or子句分開： select count(*) from stuff where id_no='0' select count(*) from stuff where id_no='1' 得到兩個結果，再作一次加法合算。因為每句都使用了索引，執行時間隻有3秒，在620000行下，時間也隻有4秒。 或者，用更好的方法，寫一個簡單的存儲過程： create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no='0'select @b=count(*) from stuff where id_no='1'endselect @c=@a+@bselect @d=convert(char(10),@c)print @d 直接算出結果，執行時間同上面一樣快！ 總結： 大家可以看到，優化即where子句利用了索引，不可優化即發生了表掃描或額外開銷。 1.任何對列的操作都将導緻表掃描，它包括資料庫函數、計算表達式等等，查詢時要盡可能将操作移至等号右邊。 2.in、or子句常會使用工作表，使索引失效；如果不産生大量重複值，可以考慮把子句拆開；拆開的子句中應該包含索引。 3.要善于使用存儲過程，它使SQL變得更加靈活和高效。 從以上這些例子可以看出，SQL優化的實質就是在結果正确的前提下，用優化器可以識别的語句，充份利用索引，減少表掃描的I/O次數，盡量避免表搜尋的發生。其實SQL的性能優化是一個複雜的過程，上述這些隻是在應用層次的一種展現，深入研究還會涉及資料庫層的資源配置、網絡層的流量控制以及作業系統層的總體設計。 利用"SQL"語句自動生成序号的兩種方式 釋出時間：2008.03.13 04:55 來源：賽迪網 作者：Alizze 【賽迪網－IT技術報道】SQL Server2005資料庫中利用SQL語句自動生成序号: 1.首先，我們來介紹第一種方式: ◆查詢的SQL語句如下: select row_number() over (order by name) as rowid, sysobjects.[name] from sysobjects ◆運作的結果: rowid name 1 all_columns 2 all_objects 3 all_parameters 4 all_sql_modules 5 all_views 2.最後，我們來介紹第二種方式: 在我們利用這種方式生成自動序号時，Test_Table必須在資料庫中不能存在,因為在執行這些SQL語句的時後自動會建立表。 select id=IDENTITY(int,1,1), sysobjects.[name] as name into dbo.Test_Table from sysobjects 詳細講解有關擷取當月天數的實用技巧 釋出時間：2008.01.24 04:39 來源：賽迪網 作者：陳莫 擷取當月天數的實用技巧： 以下是引用片段： select day(dateadd(mm,1,getdate())-day(getdate())) --獲得當月天數 分析如下: select getdate() --目前日期 select day(getdate()) --目前第幾天 select getdate()-day(getdate()) --上個月最後一天 select dateadd(mm,1,getdate())-day(getdate()) --加上一個月 select day(dateadd(mm,1,getdate())-day(getdate())) --獲得當月天數 　　 以下是引用片段： <script language="VBScript"> Dim dt1, dt2 dt1 = Date dt1 = CDate(Year(dt1) & "-" & Month(dt1) & "-1") ' 得到本月第一天 dt2 = DateAdd("m", 1, dt1) ' 得到上個月第一天 MsgBox DateDiff("d", dt1, dt2) ' 得到兩個月的差 </script> 以下是引用片段： <script language="jscript"> var dt = new Date(); //得到目前時間 dt = new Date(dt.getFullYear(), dt.getMonth() + 1, 0); //得到本月最後一天 alert(dt.getDate()); // 本月最後一天即為本月的天數 </script> 取一表前N條記錄 各個資料庫的不同SQL寫法 從别處看到的，本人在用的是DB2，竟然都不一樣……看來是不能說“會SQL，所有的資料庫用起來都一樣”了。 1. ORACLE SELECT * FROM TABLE1 WHERE ROWNUM<=N 2. INFORMIX SELECT FIRST N * FROM TABLE1 3. DB2 SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N DB2 SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY 4. SQL SERVER SELECT TOP N * FROM TABLE1 5. SYBASE SELECT TOP N * FROM TABLE1 6. mysql: select * from table_name limit N 為什麼SQL不許在視圖定義ORDER BY子句 釋出時間：2007.08.03 05:01 來源：賽迪網 作者：luoyingshu 問：為什麼SQL Server不允許在視圖定義使用ORDER BY子句？ 答： SQL Server之是以不允許在視圖定義中使用ORDER BY子句是為了遵守ANSI SQL-92标準。因為對該标準的原理分析需要對結構化查詢語言（SQL）的底層結構和它所基于的數學理論進行讨論，我們不能在這裡對它進行充分的解釋。但是，如果你需要在視圖中指定ORDER BY子句，可以考慮使用以下方法： USE pubs GO CREATE VIEW AuthorsByName AS SELECT TOP 100 PERCENT * FROM authors ORDER BY au_lname, au_fname GO Microsoft在SQL Server 7.0中引入的TOP結構在同ORDER BY子句結合使用時是非常有用的。隻有在同TOP關鍵詞結合使用時，SQL Server才支援在視圖中使用ORDER BY子句。 注意：TOP關鍵詞是SQL Server對ANSI SQL-92标準的擴充。 一條SQL語句變得巨慢的原因及其解決方法 釋出時間：2008.01.30 04:58 來源：賽迪網 作者：趙震 現象：一條SQL突然運作的特别慢。 select uidTable.column_value, first_name||' ' ||last_name, company, job_title, upper(member_level), upper(service_value) from (select * from table(select cast(multiset (select b from bbb)as Taaa) from dual)) uidTable,member where uidTable.column_value = member.login_id(+) and member.site='alibaba' and member.site='test'; 出錯原因：使用者增加了一個條件member.site=test,造成連接配接的順序變化了，原來的驅動表是uidTable(最多1024條記錄），現在變成了member表做驅動(600W條）。是以這條語句變的巨慢。 但是既然是外連接配接，為什麼連接配接的順序會改變呢？因為外連接配接的連接配接順序不是由COST決定的，而是由連接配接的條件決定的。發現執行計劃如下： ------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------- | 0 | SELECT STATEMENT | | 1018 | 72278 | 8155 | | 1 | NESTED LOOPS | | 1018 | 72278 | 8155 | | 2 | VIEW | | 4072 | 69224 | 11 | | 3 | COLLECTION ITERATOR SUBQUERY FETCH| | | | | | 4 | TABLE ACCESS FULL | DUAL | 4072 | | 11 | | 5 | TABLE ACCESS FULL | BBB | 41 | 287 | 2 | | 6 | TABLE ACCESS BY INDEX ROWID | MEMBER | 1 | 54 | 2 | |* 7 | INDEX UNIQUE SCAN | MEMBER_SITE_LID_PK | 4 | | 1 | ------------------------------------------------- 為什麼根本就沒有執行外連接配接呢？問題出在member.site='test'這個條件上，因為對外連接配接的表加了條件，造成外連接配接失效。改為member.site(+)='test'後，問題徹底解決。 --------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------- | 0 | SELECT STATEMENT | | 1018 | 72278 | 8155 | | 1 | NESTED LOOPS | | 1018 | 72278 | 8155 | | 2 | VIEW | | 4072 | 69224 | 11 | | 3 | COLLECTION ITERATOR SUBQUERY FETCH| | | | | | 4 | TABLE ACCESS FULL | DUAL | 4072 | | 11 | | 5 | TABLE ACCESS FULL | BBB | 41 | 287 | 2 | | 6 | TABLE ACCESS BY INDEX ROWID | MEMBER | 1 | 54 | 2 | |* 7 | INDEX UNIQUE SCAN | MEMBER_SITE_LID_PK | 4 | | 1 | ----------------------------------------------------------- 教你快速掌握SQL語句各種寫法的效率問題 釋出時間：2008.04.22 08:49 來源：賽迪網 作者：科林 【賽迪網－IT技術報道】問題1：一次插入多條資料時下面這兩種方法,哪種方法效率高? CREATE TABLE tb(ID int, 名稱 NVARCHAR(30), 備注 NVARCHAR(1000)) INSERT tb SELECT 1,'DDD',1 UNION ALL SELECT 1,'5100','D' UNION ALL SELECT 1,'5200','E' 也可以這樣寫: CREATE TABLE tb1(ID int, 名稱 NVARCHAR(30), 備注 NVARCHAR(1000)) INSERT TB1 (ID,名稱,備注)VALUES(1,'DDD',1) INSERT TB1 (ID,名稱,備注)VALUES(1,'5100','D') INSERT TB1 (ID,名稱,備注)VALUES(1,'5200','E') 解答： 第1種好一些, 但也得有個量的控制, 因為第1種的union all是作為一個語句整體, 查詢優化器會嘗試做優化, 同時, 也要先算出這個結果再插入的。 問題2： 指派時: SELECT @a=N'aa' SET @a=N'aa' 上面兩種方法,哪種方法效率高? 解答： 如果是單個指派, 沒有什麼好比較的話. 不過, 如果是為多個變量指派, 經測試， SELECT 一次性指派, 比用SET 逐個指派效率好.. 問題3：取前幾條資料時 set ROWCOUNT 2 select * from tb order by fd select Top 2 * from tb order by fd 上面兩種方法,哪種方法效率高? 答： SET ROWCOUNT和TOP 是一樣的, 包括執行的計劃等都是一樣的 問題4：條件判斷時: where 0<(select count(*) from tb where ……） where exists(select * from tb where ……） 上面兩種方法,哪種方法效率高? 答： 這個一般是exists快, 當然, 具體還要看你後面的子查詢的條件, 是否會引用外層查詢中的對象的列. exists檢查到有值就傳回, 而且不傳回結果集, count需要統計出所有滿足條件的, 再傳回一個結果集, 是以一般情況下exists快. 問題5： (5)NULLIF的使用----->同理它的反函數ISNULL的使用 update tb set fd=case when fd=1 then null else fd end update tb set fd=nullif(fd,1) 上面兩種方法,哪種方法效率高? 答： 應該是一樣的 問題6：從字元串中取子字元串時 substring('abcdefg',1,3) left('abcderg',3)_ 上面兩種方法,哪種方法效率高? 答： 基本上是一樣的 問題7：EXCEPT和Not in的差別? 答： except會去重複, not in 不會(除非你在select中顯式指定) except用于比較的列是所有列, 除非寫子查詢限制列, not in 沒有這種情況 問題8：INTERSECT和UNION的差別? 答：intersect是兩個查詢都有的非重複值(交集), union是兩個查詢結果的所有不重複值(并集) 通過兩個例子講解PIVOT/UNPIVOT的用法 釋出時間：2008.03.13 04:58 來源：賽迪網 作者：ChenJaYi 【賽迪網－IT技術報道】使用過SQL Server 2000的人都知道，要想實作行列轉換,必須綜合利用聚合函數和動态SQL，具體實作起來需要一定的技巧，而在SQL Server 2005中，使用新引進的關鍵字PIVOT/UNPIVOT，則可以很容易的實作行列轉換的需求。 在本文中我們将通過兩個簡單的例子詳細講解PIVOT/UNPIVOT的用法。 PIVOT的用法： 首先建立測試表，然後插入測試資料 create table test(id int,name varchar(20),quarter int,profile int) insert into test values(1,'a',1,1000) insert into test values(1,'a',2,2000) insert into test values(1,'a',3,4000) insert into test values(1,'a',4,5000) insert into test values(2,'b',1,3000) insert into test values(2,'b',2,3500) insert into test values(2,'b',3,4200) insert into test values(2,'b',4,5500) select * from test id name quarter profile ----------- -------------- ----------- ----------- 1 a 1 1000 1 a 2 2000 1 a 3 4000 1 a 4 5000 2 b 1 3000 2 b 2 3500 2 b 3 4200 2 b 4 5500 (8 row(s) affected) 使用PIVOT将四個季度的利潤轉換成橫向顯示: select id,name, [1] as "一季度", [2] as "二季度", [3] as "三季度", [4] as "四季度" from test pivot ( sum(profile) for quarter in ([1],[2],[3],[4]) ) as pvt id name 一季度 二季度 三季度 四季度 -------- --------- ----------- -------- ------- ------- 1 a 1000 2000 4000 5000 2 b 3000 3500 4200 5500 (2 row(s) affected) UNPIVOT的用法： 首先建立測試表，然後插入測試資料 drop table test create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int) insert into test values(1,'a',1000,2000,4000,5000) insert into test values(2,'b',3000,3500,4200,5500) select * from test id name Q1 Q2 Q3 Q4 -------- ------- --------- --------- -------- -------- 1 a 1000 2000 4000 5000 2 b 3000 3500 4200 5500 (2 row(s) affected) 使用UNPIVOT，将同一行中四個季度的列資料轉換成四行資料： select id,name,quarter,profile from test unpivot ( profile for quarter in ([Q1],[Q2],[Q3],[Q4]) ) as unpvt id name quarter profile ----------- ----------- ---------- ----------- 1 a Q1 1000 1 a Q2 2000 1 a Q3 4000 1 a Q4 5000 2 b Q1 3000 2 b Q2 3500 2 b Q3 4200 2 b Q4 5500 (8 row(s) affected) 用一個執行個體講解GROUP BY CEIL的使用方法 釋出時間：2008.01.31 05:07 來源：賽迪網 作者：孫詩涵 GROUP BY CEIL的使用方法： SQL> WITH A AS (SELECT 'A' CD FROM DUAL 2 UNION 3 SELECT 'B' CD FROM DUAL 4 UNION 5 SELECT 'C' CD FROM DUAL 6 UNION 7 SELECT 'D' CD FROM DUAL 8 UNION 9 SELECT 'E' CD FROM DUAL 10 UNION 11 SELECT 'F' CD FROM DUAL 12 UNION 13 SELECT 'G' CD FROM DUAL 14 UNION 15 SELECT 'H' CD FROM DUAL 16 UNION 17 SELECT 'I' CD FROM DUAL 18 ) 19 select max(decode(mod(rownum, 5), 1, CD, null)) ID1, 20 max(decode(mod(rownum, 5), 2, CD, null)) ID2, 21 max(decode(mod(rownum, 5), 3, CD, null)) ID3, 22 max(decode(mod(rownum, 5), 4, CD, null)) ID4, 23 max(decode(mod(rownum, 5), 0, CD, null)) ID5 24 from a 25 group by ceil(rownum / 5) 26 ; ID1 ID2 ID3 ID4 ID5 --- --- --- --- --- A B C D E F G H I 例二： with a as (select '01' ym from dual union select '02' ym from dual union select '03' ym from dual union select '04' ym from dual union select '05' ym from dual union select '06' ym from dual union select '07' ym from dual union select '08' ym from dual union select '09' ym from dual union select '10' ym from dual union select '11' ym from dual union select '12' ym from dual ) select max(decode(mod(rownum, 6), 1, ym, null)) ID1, max(decode(mod(rownum, 6), 2, ym, null)) ID2, max(decode(mod(rownum, 6), 3, ym, null)) ID3, max(decode(mod(rownum, 6), 4, ym, null)) ID4, max(decode(mod(rownum, 6), 5, ym, null)) ID5, max(decode(mod(rownum, 6), 0, ym, null)) ID6 from a group by ceil(rownum / 6) ID1 ID2 ID3 ID4 ID5 ID6 --- --- --- --- --- --- 01 02 03 04 05 06 07 08 09 10 11 12 解析SQL語句中INSERT語句的另一種寫法 釋出時間：2007.08.30 05:17 來源：賽迪網 作者：xiaoqiao 今天一個偶然的機會中知道 SQL 中的 INSERT 語句還可以這樣寫： INSERT INTO tbl_name (col1, col2) VALUES (value1_1, value1_2), (value2_1, value2_2),(value3_1, value3_2)... 這樣的寫法實際上類似于： INSERT INTO tbl_name (col1, col2) VALUES (value1_1, value1_2) INSERT INTO tbl_name (col1, col2) VALUES (value2_1, value2_2) INSERT INTO tbl_name (col1, col2) VALUES (value3_1, value3_2) ... 如果在執行過程中碰到一個值錯誤，則這個值以後的資料都不會被插入。 輕松解決“每個Y的最新X”的SQL問題 釋出時間：2007.12.05 05:02 來源：賽迪網 作者：36113 在實際的工作和學習中，“每個Y的最新X”是大家經常遇到的問題。請注意這不是“按Y分組求最新的X值”，而是要求最新X的那條記錄或主鍵ID。下面我們用一條SQL語句來解決此問題。 生成執行個體表和資料： --建立表 CREATE TABLE dbo.Tab ( ID int NOT NULL IDENTITY (1, 1), Y varchar(20) NOT NULL, X datetime NOT NULL ) GO --插入資料 INSERT INTO Tab(Y, X) values('BBB', '2007-10-23 11:11:11') INSERT INTO Tab(Y, X) values('BBB', '2007-10-23 11:11:11') INSERT INTO Tab(Y, X) values('BBB', '2007-10-23 10:10:10') INSERT INTO Tab(Y, X) values('AAA', '2007-10-23 12:12:12') INSERT INTO Tab(Y, X) values('AAA', '2007-10-23 10:10:10') INSERT INTO Tab(Y, X) values('AAA', '2007-10-23 11:11:11') GO 解決“每個Y的最新X”SQL問題： --一條SQL語句實作 SELECT ID, Y, X FROM Tab T WHERE (NOT EXISTS (SELECT 1 FROM Tab T2 WHERE (T2.Y = T.Y) AND (T2.X > T.X OR T2.X = T.X AND T2.ID > T.ID))) 注釋：在Y列建立索引，可以很大的優化查詢速度。（責任編輯：盧兆林） 教你快速掌握編寫高效SQL語句的方法 釋出時間：2008.02.01 04:58 來源：賽迪網 作者：璞玉 高效的SQL語句示例： create table student( id varchar(4) not null, username varchar(20), sex varchar(50), age varchar(4), class varchar(50), constraint sy_test_key primary key ( id)) 假設現在class條件如果傳入空的話，就查詢所有，如果不為空的話，就根據特定條件查找，一般的寫好将對其進行判斷，然後寫兩條SQL語句，例如：(假設傳入的班級變量為classStr) if("".equals(classStr))//注意此處這種寫好優于classStr.equals(""),這樣寫的話，假如classStr傳入的為Null，則會報錯。 sql="select * from student"; else sql = "select * from student where class='"+classStr+"'"; 若采用逆向思維的話，則寫一條語句就可以解決上面的問題。 sql = "select * from student where ''='" + classStr + "' or '"+classStr+"'=class" 由上面語句可看出，如果classStr為空的話，則查詢所有，若classStr不為空的話，則根據其值進行查詢。 個人經驗總結：有關SQL語句的優化技術 （1） 釋出時間：2008.01.22 04:51 來源：賽迪網 作者：10633 操作符優化 ◆IN 操作符 用IN寫出來的SQL的優點是比較容易寫及清晰易懂，這比較适合現代軟體開發的風格。 但是用IN的SQL性能總是比較低的，從ORACLE執行的步驟來分析用IN的SQL與不用IN的SQL有以下差別： ORACLE試圖将其轉換成多個表的連接配接，如果轉換不成功則先執行IN裡面的子查詢，再查詢外層的表記錄，如果轉換成功則直接采用多個表的連接配接方式查詢。由此可見用IN的SQL至少多了一個轉換的過程。一般的SQL都可以轉換成功，但對于含有分組統計等方面的SQL就不能轉換了。 推薦方案：在業務密集的SQL當中盡量不采用IN操作符。 ◆NOT IN操作符 此操作是強列推薦不使用的，因為它不能應用表的索引。 推薦方案：用NOT EXISTS 或（外連接配接+判斷為空）方案代替 ◆<> 操作符（不等于） 不等于操作符是永遠不會用到索引的，是以對它的處理隻會産生全表掃描。 推薦方案：用其它相同功能的操作運算代替，如 a<>0 改為 a>0 or a<0 a<>’’ 改為 a>’’ IS NULL 或IS NOT NULL操作（判斷字段是否為空） 判斷字段是否為空一般是不會應用索引的，因為B樹索引是不索引空值的。 推薦方案： 用其它相同功能的操作運算代替，如 a is not null 改為 a>0 或a>’’等。 不允許字段為空，而用一個預設值代替空值，如業擴申請中狀态字段不允許為空，預設為申請。 建立位圖索引（有分區的表不能建，位圖索引比較難控制，如字段值太多索引會使性能下降，多人更新操作會增加資料塊鎖的現象） ◆> 及 < 操作符（大于或小于操作符） 　　　大于或小于操作符一般情況下是不用調整的，因為它有索引就會采用索引查找，但有的情況下可以對它進行優化，如一個表有100萬記錄，一個數值型字段A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那麼執行A>2與A>=3的效果就有很大的差別了，因為A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。 ◆LIKE操作符 LIKE操作符可以應用通配符查詢，裡面的通配符組合可能達到幾乎是任意的查詢，但是如果用得不好則會産生性能上的問題，如LIKE ‘%5400%’ 這種查詢不會引用索引，而LIKE ‘X5400%’則會引用範圍索引。一個實際例子：用YW_YHJBQK表中營業編号後面的戶辨別号可來查詢營業編号 YY_BH LIKE ‘%5400%’ 這個條件會産生全表掃描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 則會利用YY_BH的索引進行兩個範圍的查詢，性能肯定大大提高。 ◆UNION操作符 UNION在進行表連結後會篩選掉重複的記錄，是以在表連結後會對所産生的結果集進行排序運算，删除重複的記錄再傳回結果。實際大部分應用中是不會産生重複的記錄，最常見的是過程表與曆史表UNION。如： select * from gc_dfys union select * from ls_jg_dfys 這個SQL在運作時先取出兩個表的結果，再用排序空間進行排序删除重複的記錄，最後傳回結果集，如果表資料量大的話可能會導緻用磁盤進行排序。 推薦方案：采用UNION ALL操作符替代UNION，因為UNION ALL操作隻是簡單的将兩個結果合并後就傳回。 select * from gc_dfys union all select * from ls_jg_dfys ◆SQL書寫的影響 同一功能同一性能不同寫法SQL的影響 如一個SQL在A程式員寫的為 Select * from zl_yhjbqk B程式員寫的為 Select * from dlyx.zl_yhjbqk（帶表所有者的字首） C程式員寫的為 Select * from DLYX.ZLYHJBQK（大寫表名） D程式員寫的為 Select *　from DLYX.ZLYHJBQK（中間多了空格） 以上四個SQL在ORACLE分析整理之後産生的結果及執行的時間是一樣的，但是從ORACLE共享記憶體SGA的原理，可以得出ORACLE對每個SQL 都會對其進行一次分析，并且占用共享記憶體，如果将SQL的字元串及格式寫得完全相同則ORACLE隻會分析一次，共享記憶體也隻會留下一次的分析結果，這不僅可以減少分析SQL的時間，而且可以減少共享記憶體重複的資訊，ORACLE也可以準确統計SQL的執行頻率。 ◆WHERE後面的條件順序影響 WHERE子句後面的條件順序對大資料量表的查詢會産生直接的影響，如 Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1 Select * from zl_yhjbqk where xh_bz=1　and dy_dj = '1KV以下' 以上兩個SQL中dy_dj（電壓等級）及xh_bz（銷戶标志）兩個字段都沒進行索引，是以執行的時候都是全表掃描，第一條SQL的dy_dj = '1KV以下'條件在記錄集内比率為99%，而xh_bz=1的比率隻為0.5%，在進行第一條SQL的時候99%條記錄都進行dy_dj及xh_bz的比較，而在進行第二條SQL的時候0.5%條記錄都進行dy_dj及xh_bz的比較，以此可以得出第二條SQL的CPU占用率明顯比第一條低。 ◆查詢表順序的影響 在FROM後面的表中的清單順序會對SQL執行性能影響，在沒有索引及ORACLE沒有對表進行統計分析的情況下ORACLE會按表出現的順序進行連結，由此因為表的順序不對會産生十分耗伺服器資源的資料交叉。（注：如果對表進行了統計分析，ORACLE會自動先進小表的連結，再進行大表的連結） ◆SQL語句索引的利用 對操作符的優化（見上節） 對條件字段的一些優化 ◆采用函數處理的字段不能利用索引，如： substr(hbs_bh,1,4)=’5400’，優化處理：hbs_bh like ‘5400%’ trunc(sk_rq)=trunc(sysdate)， 優化處理： sk_rq>=trunc(sysdate) and sk_rq

50，優化處理：ss_df>30 ‘X’||hbs_bh>’X5400021452’，優化處理：hbs_bh>’5400021542’ sk_rq+5=sysdate，優化處理：sk_rq=sysdate-5 hbs_bh=5401002554，優化處理：hbs_bh=’ 5401002554’， 注：此條件對hbs_bh 進行隐式的to_number轉換，因為hbs_bh字段是字元型。 條件内包括了多個本表的字段運算時不能進行索引，如： ys_df>cx_df，無法進行優化 qc_bh||kh_bh=’5400250000’， 優化處理：qc_bh=’5400’ and kh_bh=’250000’ 應用ORACLE的HINT（提示）處理 提示處理是在ORACLE産生的SQL分析執行路徑不滿意的情況下要用到的。它可以對SQL進行以下方面的提示 ◆目标方面的提示： COST（按成本優化） RULE（按規則優化） CHOOSE（預設）（ORACLE自動選擇成本或規則進行優化） ALL_ROWS（所有的行盡快傳回） FIRST_ROWS（第一行資料盡快傳回） ◆執行方法的提示： USE_NL（使用NESTED LOOPS方式聯合） USE_MERGE（使用MERGE JOIN方式聯合） USE_HASH（使用HASH JOIN方式聯合） ◆索引提示： INDEX（TABLE INDEX）（使用提示的表索引進行查詢） 其它進階提示（如并行處理等等） 用SQL語句删除重複記錄的四種好方法 釋出時間：2008.03.04 04:44 來源：賽迪網 作者：林夕 問題：如何把具有相同字段的紀錄删除，隻留下一條。 例如：表test裡有id,name字段,如果有name相同的記錄隻留下一條，其餘的删除。name的内容不定，相同的記錄數不定。 用SQL語句删除重複記錄的四種方法： 方法1: 1、将重複的記錄記入temp1表: select [标志字段id],count(*) into temp1 from [表名] group by [标志字段id] having count(*)>1 2、将不重複的記錄記入temp1表: insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=1 3、作一個包含所有不重複記錄的表： select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1) 4、删除重複表:delete [表名] 5、恢複表： insert [表名] select * from temp2 6、删除臨時表: drop table temp1 drop table temp2 方法2: declare @max integer,@id integer declare cur_rows cursor local for select id,count(*) from 表名 group by id having count(*) > 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where id = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 注:set rowcount @max - 1 表示目前緩沖區隻容納@max-1條記錄﹐如果有十條重複的﹐就刪除 10條，一定會留一條的。也可以寫成delete from 表名。 方法3： create table a_dist(id int,name varchar(20)) insert into a_dist values(1,'abc') insert into a_dist values(1,'abc') insert into a_dist values(1,'abc') insert into a_dist values(1,'abc') exec up_distinct 'a_dist','id' select * from a_dist create procedure up_distinct(@t_name varchar(30) ,@f_key varchar(30)) --f_key表示是分組字段﹐即主鍵字段 as begin declare @max integer,@id varchar(30) , @sql varchar(7999) ,@type integer select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1' exec(@sql) open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key if @type=56 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id if @type=167 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' exec(@sql) fetch cur_rows into @id,@max end close cur_rows deallocate cur_rows set rowcount 0 end select * from systypes select * from syscolumns where id = object_id('a_dist') 方法4： 可以用IGNORE_DUP_KEY： create table dup (id int identity not null, name varchar(50)not null) go insert into dup(name) values ('abc') insert into dup(name) values ('abc') insert into dup(name) values ('abc') insert into dup(name) values ('abc') insert into dup(name) values ('abc') insert into dup(name) values ('abc') insert into dup(name) values ('abc') insert into dup(name) values ('cdefg') insert into dup(name) values ('xyz') insert into dup(name) values ('xyz') go select * from dup go create table tempdb..wk(id int not null, name varchar(50)not null) go create unique index idx_remove_dup on tempdb..wk(name) with IGNORE_DUP_KEY go INSERT INTO tempdb..wk (id, name) select id, name from dup go select * from tempdb..wk go delete from dup go set identity_insert dup on INSERT INTO dup (id, name) select id, name from tempdb..wk go set identity_insert dup off go select * from dup go 注釋：此處delete原表，再加入不重複的值。大家也可以通過join隻delete原表中重複的值。 不要在SQL Server中盲目地追求一句處理 （1） 釋出時間：2007.07.31 05:10 來源：賽迪網 作者：shuijing 在日常的學習和工作中，我們可以經常發現在處理SQL Server的時，很多人都會有一句出結果的習慣，但值得注意的是，不恰當的合并處理語句，往往會産生負面的性能，本篇針對使用 UNION ALL 代替 IF 語句的合并處理做出一個簡單的事例，用來說明這種方法會所帶來的負面結果。 示例： 表A和表B，這兩個表結構一緻，為不同的業務服務，現在寫一個存儲過程，存儲過程接受一個參數，當參數為0時，查詢表A，參數為1時，查詢表B。 1:一般處理方法: IF @Flag = 0 SELECT * FROM dbo.A ELSE IF @Flag = 1 SELECT * FROM dbo.B 2、一句處理方法: SELECT * FROM dbo.A WHERE @Flag = 0 UNION ALL SELECT * FROM dbo.B WHERE @Flag = 1 細化分析： 從語句的簡捷性來看，方法b具有技巧性，它們兩者之間，究竟那一個更好呢？你可能會從性能上來評估，以決定到底用那一種。單純從語句上來看，似乎兩者的效率差不多，下面通過資料測試來反映結果似乎和想像的一樣。 建立測試環境：（注，此測試環境主要是為幾個主題服務，是以結構看起來稍有差異） USE tempdb GO SET NOCOUNT ON --====================================== --建立測試環境 --====================================== RAISERROR('建立測試環境', 10, 1) WITH NOWAIT -- Table A CREATE TABLE [dbo].A( [TranNumber] [int] IDENTITY(1, 1) NOT NULL, [INVNO] [char](8) NOT NULL, [ITEM] [char](15) NULL DEFAULT (''), PRIMARY KEY([TranNumber]) ) CREATE INDEX [indexONinvno] ON [dbo].A([INVNO]) CREATE INDEX [indexOnitem] ON [dbo].A ([ITEM]) CREATE INDEX [indexONiteminnvo] ON [dbo].A([INVNO], [ITEM]) GO -- Table B CREATE TABLE [dbo].B( [ItemNumber] [char](15) NOT NULL DEFAULT (''), [CompanyCode] [char] (4) NOT NULL, [OwnerCompanyCode] [char](4) NULL, PRIMARY KEY([ItemNumber], [CompanyCode]) ) CREATE INDEX [ItemNumber] ON [dbo].B([ItemNumber]) CREATE INDEX [CompanyCode] ON [dbo].B([CompanyCode]) CREATE INDEX [OwnerCompanyCode] ON [dbo].B([OwnerCompanyCode]) GO --====================================== --生成測試資料 --====================================== RAISERROR('生成測試資料', 10, 1) WITH NOWAIT INSERT [dbo].A([INVNO], [ITEM]) SELECT LEFT(NEWID(), 8), RIGHT(NEWID(), 15) FROM syscolumns A, syscolumns B INSERT [dbo].B([ItemNumber], [CompanyCode], [OwnerCompanyCode]) SELECT RIGHT(NEWID(), 15), LEFT(NEWID(), 4), LEFT(NEWID(), 4) FROM syscolumns A, syscolumns B GO 進行性能測試： DECLARE @a int SET @a = 1 DECLARE @t TABLE( id int IDENTITY, a int, b int) DECLARE @dt datetime, @loop int, @id int SET @loop = 0 WHILE @loop < 5 BEGIN SET @loop = @loop + 1 RAISERROR('test %d', 10, 1, @loop) WITH NOWAIT SET @dt = GETDATE() SELECT [ITEM] FROM A WHERE @a = 0 AND [ITEM] < 'A' UNION ALL SELECT [ItemNumber] FROM B WHERE @a = 1 AND [ItemNumber] < 'A' INSERT @t(a) VALUES(DATEDIFF(ms, @dt, GETDATE())) SELECT @id = SCOPE_IDENTITY(), @dt = GETDATE() IF @a = 0 SELECT [ITEM] FROM A WHERE [ITEM] < 'A' ELSE IF @a = 1 SELECT [ItemNumber] FROM B WHERE [ItemNumber] < 'A' UPDATE @t SET b = DATEDIFF(ms, @dt, GETDATE()) WHERE id = @id END SELECT * FROM @t UNION ALL SELECT NULL, SUM(a), SUM(b) FROM @t 性能測試結果： id a b --- ------- ------- 1 3410 2063 2 1703 1656 3 1763 1656 4 1800 1793 5 1643 1856 NULL 10319 9024 從結果看，兩者的性能差異很小，是以兩者從性能上比較，可以視為沒有差異。 問題所在： 雖然在性能上，兩者沒有什麼差異，但另一個問題也許你從來沒有考慮過，那就是對表的通路的問題，在方法A中，肯定隻會通路到一個表；而在方法B中，情況還是如此嗎？答案是否定的，方法B始終會掃描兩個表。而這樣的潛台詞是，即使在我的查詢中，隻會用到A表，但如果B表被下了鎖的話，整個查詢就會被阻塞，而方法A不會。 為了證明這個問題，我們再做下面的測試 BLOCK 的測試—為表A加鎖： (查詢視窗A) BEGIN TRAN UPDATE A SET [ITEM] = RIGHT(NEWID(), 4) WHERE [ITEM] BETWEEN '9' AND 'A' --ROLLBACK TRAN -- 不復原事務，讓鎖一直保持 BLOCK 的測試—測試查詢方法A：(查詢視窗B) -- run query windows 2 DECLARE @a int SET @a = 1 IF @a = 0 SELECT [TranNumber] FROM A WHERE [ITEM] < 'A' ELSE IF @a = 1 SELECT [ItemNumber] FROM B WHERE [ItemNumber] < 'A' BLOCK 的測試—測試查詢方法B(查詢視窗C) -- run query windows 3 DECLARE @a int SET @a = 1 SELECT [ITEM] FROM A WHERE @a = 0 AND [ITEM] < 'A' UNION ALL SELECT [ItemNumber] FROM B WHERE @a = 1 AND [ItemNumber] < 'A' 結果： 可以看到，查詢視窗B中的查詢會及時地完成，而查詢視窗C的查詢會一直等待，你可以通過執行存儲過程 sp_who2，檢視目前的BLOCK狀況來确定查詢視窗C的查詢是否被查詢視窗A的查詢BLOCK住。 最後結論： 不要使用查詢方法B，它看起來很不錯，實際的結果即則是會增加被BLOCK的機會。 用人工智能技術自動對SQL語句進行重寫 （1） 釋出時間：2008.04.11 04:56 來源：賽迪網 作者：Lynn 【賽迪網－IT技術報道】SQL語句的優化是将性能低下的SQL語句轉換成目的相同的性能優異的SQL語句。 人工智能自動SQL優化就是使用人工智能技術，自動對SQL語句進行重寫，進而找到性能最好的等效SQL語句。 資料庫性能的優化 一個資料庫系統的生命周期可以分成：設計、開發和成品三個階段。在設計階段進行資料庫性能優化的成本最低，收益最大。在成品階段進行資料庫性能優化的成本最高，收益最小。 資料庫的優化通常可以通過對網絡、硬體、作業系統、資料庫參數和應用程式的優化來進行。最常見的優化手段就是對硬體的更新。根據統計，對網絡、硬體、作業系統、資料庫參數進行優化所獲得的性能提升，全部加起來隻占資料庫系統性能提升的40%左右，其餘的60%系統性能提升來自對應用程式的優化。許多優化專家認為，對應用程式的優化可以得到80%的系統性能的提升。 應用程式的優化 應用程式的優化通常可分為兩個方面：源代碼和SQL語句。由于涉及到對程式邏輯的改變，源代碼的優化在時間成本和風險上代價很高，而對資料庫系統性能的提升收效有限。 為什麼要優化SQL語句 . SQL語句是對資料庫進行操作的惟一途徑，對資料庫系統的性能起着決定性的作用。 . SQL語句消耗了70%至90%的資料庫資源。 . SQL語句獨立于程式設計邏輯，對SQL語句進行優化不會影響程式邏輯。 . SQL語句有不同的寫法，在性能上的差異非常大。 . SQL語句易學，但難精通。 優化SQL語句的傳統方法是通過手工重寫來對SQL語句進行優化。DBA或資深程式員通過對SQL語句執行計劃的分析，依靠經驗，嘗試重寫SQL語句，然後對結果和性能進行比較，以試圖找到性能較佳的SQL語句。這種傳統上的作法無法找出SQL語句的所有可能寫法，且依賴于人的經驗，非常耗費時間。 SQL優化技術的發展曆程 第一代SQL優化工具是執行計劃分析工具。這類工具針對輸入的SQL語句，從資料庫提取執行計劃，并解釋執行計劃中關鍵字的含義。 第二代SQL優化工具隻能提供增加索引的建議，它通過對輸入的SQL語句的執行計劃的分析，來産生是否要增加索引的建議。 第三代SQL優化工具不僅分析輸入SQL語句的執行計劃，還對輸入的SQL語句本身進行文法分析，經過分析産生寫法上的改進建議。 人工智能自動SQL優化 人工智能自動SQL優化出現在90年代末。目前在商用資料庫領域，LECCO Technology Limited(靈高科研有限公司)擁有該技術，并提供使用該技術的自動優化産品LECCO SQL Expert，它支援Oracle、Sybase、MS SQL Server和IBM DB2資料庫平台。該産品針對資料庫應用的開發和維護階段提供的子產品有：SQL文法優化器、PL/SQL內建化開發調試環境(IDE)、掃描器、資料庫螢幕等。其核心子產品SQL 文法優化器的工作原理為：①輸入一條源SQL語句；②“人工智能回報式搜尋引擎”對輸入的SQL語句，結合檢測到的資料庫結構和索引進行重寫，産生N條等效的SQL語句輸出；③産生的N條等效SQL語句再送入“人工智能回報式搜尋引擎”進行重寫，直至無法産生新的輸出或搜尋限額滿；④對輸出的SQL語句進行過濾，選出具有不同執行計劃的SQL語句；⑤對得到的SQL語句進行批量測試，找出性能最好的SQL語句。 LECCO SQL Expert自動優化執行個體 假設我們從源代碼中抽取出這條SQL語句(也可以通過内帶的掃描器或螢幕獲得SQL語句)： SELECT COUNT(*) FROM EMPLOYEE swheresEXISTS (SELECT 'X' FROM DEPARTMENT swheresEMP_DEPT=DPT_ID AND DPT_NAME LIKE 'AC%') AND EMP_ID IN (SELECT SAL_EMP_ID FROM EMP_SAL_HIST B swheresSAL_SALARY ＞ 70000) 按下“優化”按鈕後，經過10幾秒，SQL Expert就完成了優化的過程，并在這10幾秒的時間裡重寫産生了2267 條等價的SQL語句，其中136條SQL語句有不同的執行計劃。 接下來，我們可以對自動重寫産生的136條SQL語句進行批運作測試，以選出性能最佳的等效SQL語句。按下“批運作” 按鈕，在“終止條件” 頁選擇“最佳運作時間SQL語句”，按“确定”。 經過幾分鐘的測試運作後，我們可以發現SQL124的運作時間和反應時間最短。運作速度約有22.75倍的提升(源SQL語句運作時間為2.73秒，SQL124運作時間為0.12秒)。現在我們就可以把SQL124放入源代碼中，結束一條SQL語句的優化工作了。 “邊做邊學式訓練”提升SQL開發水準 LECCO SQL Expert不僅能夠找到最佳的SQL語句，它所提供的“邊做邊學式訓練”還能夠教開發人員和資料庫管理者如何寫出性能最好的SQL語句。LECCO SQL Expert的“SQL比較器”可以标明源SQL和待選SQL間的不同之處。 以上面優化的結果為例，為了檢視源SQL語句和SQL124在寫法上有什麼不同，我們可以按下“比較器” 按鈕，對SQL124和源SQL語句進行比較。“SQL 比較器”将SQL124相對于源SQL語句的不同之處以藍顔色表示了出來。如果選擇“雙向比較”複選框，“SQL 比較器”可以将兩條SQL語句的不同之處以藍色表示。當然，我們也可以從源語句和重寫後的SQL 語句中任選兩條進行比較。 從比較的結果可以看到，重寫後的SQL124把第一個Exists改寫成了In；在字段DPT_ID上進行了合并空字元串的操作，以誘導資料庫先執行子查詢中的 (SELECT DPT_ID||'' FROM DEPARTMENT WHERE DPT_NAME LIKE 'AC%') 在子查詢完成後，再與EMPLOYEE表進行嵌套循環連接配接(Nested Loop Join)。 如果覺得對寫法的改變難以了解，還可以點中“執行計劃”複選框，通過比較兩條SQL語句的執行計劃的不同，來了解其中的差異。在檢視執行計劃過程中，如果有什麼不明白的地方，可以點中“SQL資訊按鈕”，再點選執行計劃看不明白的地方，LECCO SQL Expert的上下文敏感幫助系統将提供執行計劃該處的解釋。 在“SQL比較器”中，選中“統計資訊”複選框後，可得到詳細的兩條SQL語句運作時的統計資訊比較，這對于學習不同的SQL寫法對資料庫資源的消耗很有幫助。 LECCO SQL Expert優化子產品的特點 LECCO SQL Expert優化子產品的特點主要表現為：自動優化SQL語句；以獨家的人工智能知識庫“回報式搜尋引擎”來重寫性能優異的SQL語句；找出所有等效的SQL語句及可能的執行計劃；保證産生相同的結果；先進的SQL文法分析器能處理最複雜的SQL語句；可以重寫SELECT、SELECT INTO、UPDATE、INSERT和DELETE語句；通過測試運作，為應用程式和資料庫自動找到性能最好的SQL語句；提供微秒級的計時，能夠優化Web應用程式和有大量使用者的線上事務進行中運作時間很短的SQL語句；為開發人員提供“邊做邊學式訓練”，迅速提高開發人員的SQL程式設計技能；提供上下文敏感的執行計劃幫助系統和SQL運作狀态幫助；不是猜測或建議，而是獨一無二的SQL重寫解決方案。 寫出專家級的SQL語句 LECCO SQL Expert的出現，使SQL的優化變得極其簡單，隻要能夠寫出SQL語句，它就能幫使用者找到最好性能的寫法。LECCO SQL Expert不僅能在很短的時間内找到所有可能的優化方案，而且能夠通過實際測試，确定最有效的優化方案。同以往的資料庫優化手段相比較，LECCO SQL Expert将資料庫優化技術帶到了一個嶄新的技術高度，依賴人的經驗、耗費大量時間、受人的思維束縛的資料庫優化手段已經被高效、省時且準确的自動優化軟體所取代了。通過内建的“LECCO小助手”的幫助，即使是SQL的開發新手，也能快速且簡單地寫出專家級的SQL語句。