SQL应用教程
SQL 應用教程
第一部分 SQL 語法及注意事項
1. 在有group by的語句中，select中搜索的字段應該是在group by中出現的，或者是字段的聚合函數計算值，例如： ?
SELECT co,c3,c5,c11 FROM ST12509_main WHERE c4='帕克' GROUP BY co,c3; 這種寫法是不合理的，,c5和c11的值存在不確定性，正確的應該為 ?
SELECT co,c3 FROM ST12509_main WHERE c4='帕克' GROUP BY co,c3;或者有聚合函數的?
SELECT co,c3,SUM(c5*c11),AVG(c5) FROM ST12509_main WHERE c4='帕克' GROUP BY co,c3;
?
基本的Sql編寫注意事項(Oracle)
盡量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。
?不用NOT IN操作符，可以用NOT EXISTS或者外連接+替代。
?Oracle在執行IN子查詢時，首先執行子查詢，將查詢結果放入臨時表再執行主查詢。而EXIST則是首先檢查主查詢，然后運行子查詢直到找到第一個匹配項。NOT EXISTS比NOT IN效率稍高。但具體在選擇IN或EXIST操作時，要根據主子表數據量大小來具體考慮。
?不用"<>"或者"!="操作符。對不等于操作符的處理會造成全表掃描，可以用"<" or ">"代替。
?Where子句中出現IS NULL或者IS NOT NULL時，Oracle會停止使用索引而執行全表掃描。可以考慮在設計表時，對索引列設置為NOT NULL。這樣就可以用其他操作來取代判斷NULL的操作。
?當通配符"%"或者"_"作為查詢字符串的第一個字符時，索引不會被使用。
?對于有連接的列"||"，最后一個連接列索引會無效。盡量避免連接，可以分開連接或者使用不作用在列上的函數替代。
?如果索引不是基于函數的，那么當在Where子句中對索引列使用函數時，索引不再起作用。
?Where子句中避免在索引列上使用計算，否則將導致索引失效而進行全表掃描。
?對數據類型不同的列進行比較時，會使索引失效。
?用">="替代">"。
?UNION操作符會對結果進行篩選，消除重復，數據量大的情況下可能會引起磁盤排序。如果不需要刪除重復記錄，應該使用UNION ALL。
?Oracle從下到上處理Where子句中多個查詢條件，所以表連接語句應寫在其他Where條件前，可以過濾掉最大數量記錄的條件必須寫在Where子句的末尾。
?Oracle從右到左處理From子句中的表名，所以在From子句中包含多個表的情況下，將記錄最少的表放在最后。（只在采用RBO優化時有效，下文詳述）
?Order By語句中的非索引列會降低性能，可以通過添加索引的方式處理。嚴格控制在Order By語句中使用表達式。
?不同區域出現的相同的Sql語句，要保證查詢字符完全相同，以利用SGA共享池，防止相同的Sql語句被多次分析。
?多利用內部函數提高Sql效率。
?當在Sql語句中連接多個表時，使用表的別名，并將之作為每列的前綴。這樣可以減少解析時間。
需要注意的是，隨著Oracle的升級，查詢優化器會自動對Sql語句進行優化，某些限制可能在新版本的Oracle下不再是問題。尤其是采用CBO（Cost-Based Optimization，基于代價的優化方式）時。
我們可以總結一下可能引起全表掃描的操作：
在索引列上使用NOT或者"<>";
?對索引列使用函數或者計算；
?NOT IN操作；
?通配符位于查詢字符串的第一個字符；
?IS NULL或者IS NOT NULL；
?多列索引，但它的第一個列并沒有被Where子句引用；
Oracle優化器
Oracle優化器（Optimizer）是Oracle在執行SQL之前分析語句的工具。Oracle的優化器有兩種優化方式：基于規則的（RBO）和基于代價的（CBO）。
RBO：優化器遵循Oracle內部預定的規則。
?CBO：依據語句執行的代價，主要指對CPU和內存的占用。優化器在判斷是否使用CBO時，要參照表和索引的統計信息。統計信息要在對表做analyze后才會有。Oracle8及以后版本，推薦用CBO方式。
?Oracle優化器的優化模式主要有四種：
?Rule：基于規則；
?Choose：默認模式。根據表或索引的統計信息，如果有統計信息，則使用CBO方式；如果沒有統計信息，相應列有索引，則使用RBO方式。
?First rows：與Choose類似。不同的是如果表有統計信息，它將以最快的方式返回查詢的前幾行，以獲得最佳響應時間。
?All rows：即完全基于Cost的模式。當一個表有統計信息時，以最快方式返回表所有行，以獲得最大吞吐量。沒有統計信息則使用RBO方式。
?設定優化模式的方式
?Instance級別：在init<SID>.ora文件中設定OPTIMIZER_MODE；
?Session級別：通過SQL> ALTER SESSION SET OPTIMIZER_MODE=;來設定。
?語句級別：通過SQL> SELECT /ALL _ROWS/ ……;來設定。可用的HINT包括/+ALL_ROWS/、/+FIRST_ROWS/、/+CHOOSE/、/+RULE/ 等。
要注意的是，如果表有統計信息，則可能造成語句不走索引的結果。可以用SQL>ANALYZE TABLE table_name DELETE STATISTICS; 刪除索引。對列和索引更新統計信息的SQL：SQL> ANALYZE TABLE table_name COMPUTE STATISTICS;SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;?
?
第二部分 常用SQL語句
?
第一章?
?
(1) 數據記錄篩選：?
sql="select * from 數據表 where 字段名=字段值 order by 字段名 "?
sql="select * from 數據表 where 字段名 like ‘%字段值%‘ order by 字段名 "?
sql="select top 10 * from 數據表 where 字段名 order by 字段名 "?
sql="select * from 數據表 where 字段名 in (‘值1‘,‘值2‘,‘值3‘)"?
sql="select * from 數據表 where 字段名 between 值1 and 值2"?
(2) 更新數據記錄：?
sql="update 數據表 set 字段名=字段值 where 條件表達式"?
sql="update 數據表 set 字段1=值1,字段2=值2 …… 字段n=值n where 條件表達式"?
(3) 刪除數據記錄：?
sql="delete from 數據表 where 條件表達式"?
sql="delete from 數據表" (將數據表所有記錄刪除)?
(4) 添加數據記錄：?
sql="insert into 數據表 (字段1,字段2,字段3 …) valuess (值1,值2,值3 …)"?
sql="insert into 目標數據表 select * from 源數據表" (把源數據表的記錄添加到目標數據表)?
(5) 數據記錄統計函數：?
AVG(字段名) 得出一個表格欄平均值?
COUNT(*|字段名) 對數據行數的統計或對某一欄有值的數據行數統計?
MAX(字段名) 取得一個表格欄最大的值?
MIN(字段名) 取得一個表格欄最小的值?
SUM(字段名) 把數據欄的值相加?
引用以上函數的方法：?
sql="select sum(字段名) as 別名 from 數據表 where 條件表達式"?
set rs=conn.excute(sql)?
用 rs("別名") 獲取統的計值，其它函數運用同上。?
(5) 數據表的建立和刪除：?
CREATE TABLE 數據表名稱(字段1 類型1(長度),字段2 類型2(長度) …… )?
例：CREATE TABLE tab01(name varchar(50),datetime default now())?
DROP TABLE 數據表名稱 (永久性刪除一個數據表)?
(6) 記錄集對象的方法：?
rs.movenext 將記錄指針從當前的位置向下移一行?
rs.moveprevious 將記錄指針從當前的位置向上移一行?
rs.movefirst 將記錄指針移到數據表第一行?
rs.movelast 將記錄指針移到數據表最后一行?
rs.absoluteposition=N 將記錄指針移到數據表第N行?
rs.absolutepage=N 將記錄指針移到第N頁的第一行?
rs.pagesize=N 設置每頁為N條記錄?
rs.pagecount 根據 pagesize 的設置返回總頁數?
rs.recordcount 返回記錄總數?
rs.bof 返回記錄指針是否超出數據表首端，true表示是，false為否?
rs.eof 返回記錄指針是否超出數據表末端，true表示是，false為否?
rs.delete 刪除當前記錄，但記錄指針不會向下移動?
rs.addnew 添加記錄到數據表末端?
rs.update 更新數據表記錄?
判斷所填數據是數字型?
if not isNumeric(request("字段名稱")) then?
response.write "不是數字"?
else?
response.write "數字"?
end if
第二章
兩張關聯表，刪除主表中已經在副表中沒有的信息?
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
四表聯查問題：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
日程安排提前五分鐘提醒?
SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>5
一條sql 語句搞定數據庫分頁
select top 10 b.* from (select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段
具體實現：
關于數據庫分頁：
? declare @start int,@end int
? @sql ?nvarchar(600)
? set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
? exec sp_executesql @sql
注意：在top后不能直接跟一個變量，所以在實際應用中只有這樣的進行特殊的處理。Rid為一個標識列，如果top后還有具體的字段，這樣做是非常有好處的。因為這樣可以避免 top的字段如果是邏輯索引的，查詢的結果后實際表中的不一致（邏輯索引中的數據有可能和數據表中的不一致，而查詢時如果處在索引則首先查詢索引）
前10條記錄
select top 10 * form table1 where 范圍
選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復行而派生出一個結果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
隨機取出10條數據
select top 10 * from tablename order by newid()
隨機選擇記錄
select newid()
刪除重復記錄
1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
2),select distinct * into temp from tablename
? delete from tablename
? insert into tablename select * from temp
?這種操作牽連大量的數據的移動，這種做法不適合大容量但數據操作
3),例如：在一個外部表中導入數據，由于某些原因第一次只導入了一部分，但很難判斷具體位置，這樣只有在下一次全部導入，這樣也就產生好多重復的字段，怎樣刪除重復字段
alter table tablename
--添加一個自增列
add ?column_b int identity(1,1)
?delete from tablename where column_b not in(
select max(column_b) ?from tablename group by column1,column2,...)
alter table tablename drop column column_b
寫一條SQL查詢語句將表中年紀為空的值更新為25
select isnull(age,25) from TableName
?
第三章 各種實用SQL語句
?
寫出一條Sql語句：取出表A中第31到第40記錄（SQLServer,以自動增長的ID作為主鍵,注意：ID可能不是連續的。
解1: select top 10 * from A where id not in (select top 30 id
from A)
解2: select top 10 * from A where id >(select max(id) from (select
top 30 id from A )as A)
?
總結
- 上一篇： 跨域 （3） window.name
- 下一篇： 编程入门教程