SQL应用教程
SQL 應用教程
第一部分 SQL 語法及注意事項
1. 在有group by的語句中，select中搜索的字段應該是在group by中出現(xiàn)的，或者是字段的聚合函數(shù)計算值，例如： ?
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;或者有聚合函數(shù)的?
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在執(zhí)行IN子查詢時，首先執(zhí)行子查詢，將查詢結果放入臨時表再執(zhí)行主查詢。而EXIST則是首先檢查主查詢，然后運行子查詢直到找到第一個匹配項。NOT EXISTS比NOT IN效率稍高。但具體在選擇IN或EXIST操作時，要根據(jù)主子表數(shù)據(jù)量大小來具體考慮。
?不用"<>"或者"!="操作符。對不等于操作符的處理會造成全表掃描，可以用"<" or ">"代替。
?Where子句中出現(xiàn)IS NULL或者IS NOT NULL時，Oracle會停止使用索引而執(zhí)行全表掃描。可以考慮在設計表時，對索引列設置為NOT NULL。這樣就可以用其他操作來取代判斷NULL的操作。
?當通配符"%"或者"_"作為查詢字符串的第一個字符時，索引不會被使用。
?對于有連接的列"||"，最后一個連接列索引會無效。盡量避免連接，可以分開連接或者使用不作用在列上的函數(shù)替代。
?如果索引不是基于函數(shù)的，那么當在Where子句中對索引列使用函數(shù)時，索引不再起作用。
?Where子句中避免在索引列上使用計算，否則將導致索引失效而進行全表掃描。
?對數(shù)據(jù)類型不同的列進行比較時，會使索引失效。
?用">="替代">"。
?UNION操作符會對結果進行篩選，消除重復，數(shù)據(jù)量大的情況下可能會引起磁盤排序。如果不需要刪除重復記錄，應該使用UNION ALL。
?Oracle從下到上處理Where子句中多個查詢條件，所以表連接語句應寫在其他Where條件前，可以過濾掉最大數(shù)量記錄的條件必須寫在Where子句的末尾。
?Oracle從右到左處理From子句中的表名，所以在From子句中包含多個表的情況下，將記錄最少的表放在最后。（只在采用RBO優(yōu)化時有效，下文詳述）
?Order By語句中的非索引列會降低性能，可以通過添加索引的方式處理。嚴格控制在Order By語句中使用表達式。
?不同區(qū)域出現(xiàn)的相同的Sql語句，要保證查詢字符完全相同，以利用SGA共享池，防止相同的Sql語句被多次分析。
?多利用內(nèi)部函數(shù)提高Sql效率。
?當在Sql語句中連接多個表時，使用表的別名，并將之作為每列的前綴。這樣可以減少解析時間。
需要注意的是，隨著Oracle的升級，查詢優(yōu)化器會自動對Sql語句進行優(yōu)化，某些限制可能在新版本的Oracle下不再是問題。尤其是采用CBO（Cost-Based Optimization，基于代價的優(yōu)化方式）時。
我們可以總結一下可能引起全表掃描的操作：
在索引列上使用NOT或者"<>";
?對索引列使用函數(shù)或者計算；
?NOT IN操作；
?通配符位于查詢字符串的第一個字符；
?IS NULL或者IS NOT NULL；
?多列索引，但它的第一個列并沒有被Where子句引用；
Oracle優(yōu)化器
Oracle優(yōu)化器（Optimizer）是Oracle在執(zhí)行SQL之前分析語句的工具。Oracle的優(yōu)化器有兩種優(yōu)化方式：基于規(guī)則的（RBO）和基于代價的（CBO）。
RBO：優(yōu)化器遵循Oracle內(nèi)部預定的規(guī)則。
?CBO：依據(jù)語句執(zhí)行的代價，主要指對CPU和內(nèi)存的占用。優(yōu)化器在判斷是否使用CBO時，要參照表和索引的統(tǒng)計信息。統(tǒng)計信息要在對表做analyze后才會有。Oracle8及以后版本，推薦用CBO方式。
?Oracle優(yōu)化器的優(yōu)化模式主要有四種：
?Rule：基于規(guī)則；
?Choose：默認模式。根據(jù)表或索引的統(tǒng)計信息，如果有統(tǒng)計信息，則使用CBO方式；如果沒有統(tǒng)計信息，相應列有索引，則使用RBO方式。
?First rows：與Choose類似。不同的是如果表有統(tǒng)計信息，它將以最快的方式返回查詢的前幾行，以獲得最佳響應時間。
?All rows：即完全基于Cost的模式。當一個表有統(tǒng)計信息時，以最快方式返回表所有行，以獲得最大吞吐量。沒有統(tǒng)計信息則使用RBO方式。
?設定優(yōu)化模式的方式
?Instance級別：在init<SID>.ora文件中設定OPTIMIZER_MODE；
?Session級別：通過SQL> ALTER SESSION SET OPTIMIZER_MODE=;來設定。
?語句級別：通過SQL> SELECT /ALL _ROWS/ ……;來設定。可用的HINT包括/+ALL_ROWS/、/+FIRST_ROWS/、/+CHOOSE/、/+RULE/ 等。
要注意的是，如果表有統(tǒng)計信息，則可能造成語句不走索引的結果。可以用SQL>ANALYZE TABLE table_name DELETE STATISTICS; 刪除索引。對列和索引更新統(tǒng)計信息的SQL：SQL> ANALYZE TABLE table_name COMPUTE STATISTICS;SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;?
?
第二部分 常用SQL語句
?
第一章?
?
(1) 數(shù)據(jù)記錄篩選：?
sql="select * from 數(shù)據(jù)表 where 字段名=字段值 order by 字段名 "?
sql="select * from 數(shù)據(jù)表 where 字段名 like ‘%字段值%‘ order by 字段名 "?
sql="select top 10 * from 數(shù)據(jù)表 where 字段名 order by 字段名 "?
sql="select * from 數(shù)據(jù)表 where 字段名 in (‘值1‘,‘值2‘,‘值3‘)"?
sql="select * from 數(shù)據(jù)表 where 字段名 between 值1 and 值2"?
(2) 更新數(shù)據(jù)記錄：?
sql="update 數(shù)據(jù)表 set 字段名=字段值 where 條件表達式"?
sql="update 數(shù)據(jù)表 set 字段1=值1,字段2=值2 …… 字段n=值n where 條件表達式"?
(3) 刪除數(shù)據(jù)記錄：?
sql="delete from 數(shù)據(jù)表 where 條件表達式"?
sql="delete from 數(shù)據(jù)表" (將數(shù)據(jù)表所有記錄刪除)?
(4) 添加數(shù)據(jù)記錄：?
sql="insert into 數(shù)據(jù)表 (字段1,字段2,字段3 …) valuess (值1,值2,值3 …)"?
sql="insert into 目標數(shù)據(jù)表 select * from 源數(shù)據(jù)表" (把源數(shù)據(jù)表的記錄添加到目標數(shù)據(jù)表)?
(5) 數(shù)據(jù)記錄統(tǒng)計函數(shù)：?
AVG(字段名) 得出一個表格欄平均值?
COUNT(*|字段名) 對數(shù)據(jù)行數(shù)的統(tǒng)計或?qū)δ骋粰谟兄档臄?shù)據(jù)行數(shù)統(tǒng)計?
MAX(字段名) 取得一個表格欄最大的值?
MIN(字段名) 取得一個表格欄最小的值?
SUM(字段名) 把數(shù)據(jù)欄的值相加?
引用以上函數(shù)的方法：?
sql="select sum(字段名) as 別名 from 數(shù)據(jù)表 where 條件表達式"?
set rs=conn.excute(sql)?
用 rs("別名") 獲取統(tǒng)的計值，其它函數(shù)運用同上。?
(5) 數(shù)據(jù)表的建立和刪除：?
CREATE TABLE 數(shù)據(jù)表名稱(字段1 類型1(長度),字段2 類型2(長度) …… )?
例：CREATE TABLE tab01(name varchar(50),datetime default now())?
DROP TABLE 數(shù)據(jù)表名稱 (永久性刪除一個數(shù)據(jù)表)?
(6) 記錄集對象的方法：?
rs.movenext 將記錄指針從當前的位置向下移一行?
rs.moveprevious 將記錄指針從當前的位置向上移一行?
rs.movefirst 將記錄指針移到數(shù)據(jù)表第一行?
rs.movelast 將記錄指針移到數(shù)據(jù)表最后一行?
rs.absoluteposition=N 將記錄指針移到數(shù)據(jù)表第N行?
rs.absolutepage=N 將記錄指針移到第N頁的第一行?
rs.pagesize=N 設置每頁為N條記錄?
rs.pagecount 根據(jù) pagesize 的設置返回總頁數(shù)?
rs.recordcount 返回記錄總數(shù)?
rs.bof 返回記錄指針是否超出數(shù)據(jù)表首端，true表示是，false為否?
rs.eof 返回記錄指針是否超出數(shù)據(jù)表末端，true表示是，false為否?
rs.delete 刪除當前記錄，但記錄指針不會向下移動?
rs.addnew 添加記錄到數(shù)據(jù)表末端?
rs.update 更新數(shù)據(jù)表記錄?
判斷所填數(shù)據(jù)是數(shù)字型?
if not isNumeric(request("字段名稱")) then?
response.write "不是數(shù)字"?
else?
response.write "數(shù)字"?
end if
第二章
兩張關聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息?
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
四表聯(lián)查問題：
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 語句搞定數(shù)據(jù)庫分頁
select top 10 b.* from (select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段
具體實現(xiàn)：
關于數(shù)據(jù)庫分頁：
? 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的字段如果是邏輯索引的，查詢的結果后實際表中的不一致（邏輯索引中的數(shù)據(jù)有可能和數(shù)據(jù)表中的不一致，而查詢時如果處在索引則首先查詢索引）
前10條記錄
select top 10 * form table1 where 范圍
選擇在每一組b值相同的數(shù)據(jù)中對應的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績排名,等等.)
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條數(shù)據(jù)
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
?這種操作牽連大量的數(shù)據(jù)的移動，這種做法不適合大容量但數(shù)據(jù)操作
3),例如：在一個外部表中導入數(shù)據(jù)，由于某些原因第一次只導入了一部分，但很難判斷具體位置，這樣只有在下一次全部導入，這樣也就產(chǎn)生好多重復的字段，怎樣刪除重復字段
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可能不是連續(xù)的。
解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
- 下一篇： 编程入门教程