若sql語句_如何優化SQL語句

① 若sql語句中order by指定了多個欄位,則怎麼排序

舉個例子吧：
order
by
id
desc,time
desc
先是按
id
降序排列
（優先）
如果
id
欄位
有些是一樣的話
再按time
降序排列
(前提是滿足id降序排列)

② 程序在執行SQL語句時，若出現系統異常中斷或資料庫突發崩潰，如何保證

備份可用數據後，卸載重裝恢復資料庫。
程序在執行SQL語句時，若出現系統異常中斷或資料庫突發崩潰，先備份可用的數據，包含所有系統資料庫和用戶資料庫的數據文件和日誌文件。
卸載原來的安裝。
系統表查找和刪除所有的MSSQLServer項。
磁碟上刪除安裝SQLSErver產生的所有文件。
重新安裝SQLServer，所需安裝的補丁的版本要求保持與備份系統資料庫時安裝的補丁版本一致。
單用戶模式下恢復master資料庫。
恢復其他系統資料庫。
恢復用戶資料庫。

③ 誰能給簡單介紹一下SQL的基本語句

下列語句部分是Mssql語句，不可以在access中使用。

SQL分類：
DDL—數據定義語言(CREATE，ALTER，DROP，DECLARE)
DML—數據操縱語言(SELECT，DELETE，UPDATE，INSERT)
DCL—數據控制語言(GRANT，REVOKE，COMMIT，ROLLBACK)

首先,簡要介紹基礎語句：
1、說明：創建資料庫

CREATE DATABASE database-name
2、說明：刪除資料庫
drop database dbname

3、說明：備份sql server
--- 創建備份數據的 device
USE master
EXEC sp_admpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 開始備份
BACKUP DATABASE pubs TO testBack

4、說明：創建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根據已有的表創建新表：
--A：
create table tab_new like tab_old --(使用舊表創建新表)
--B：
create table tab_new as select col1,col2… from tab_old definition only
5、說明：刪除新表
drop table tabname

6、說明：增加一個列
Alter table tabname add column col type

註：列增加後將不能刪除。DB2中列加上後數據類型也不能改變，唯一能改變的是增加varchar類型的長度。
7、說明：添加主鍵：
Alter table tabname add primary key(col)

說明：刪除主鍵：
Alter table tabname drop primary key(col)

8、說明：創建索引：
create [unique] index idxname on tabname(col….)

刪除索引：
drop index idxname

註：索引是不可更改的，想更改必須刪除重新建。
9、說明：創建視圖：
create view viewname as select statement

刪除視圖：
drop view viewname

10、說明：幾個簡單的基本的sql語句
選擇：select * from table1 where 范圍
插入：insert into table1(field1,field2) values(value1,value2)
刪除：delete from table1 where 范圍
更新：update table1 set field1=value1 where 范圍
查找：select * from table1 where field1 like 』%value1%』 ---like的語法很精妙，查資料!
排序：select * from table1 order by field1,field2 [desc]
總數：select count * as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、說明：幾個高級查詢運算詞
A： UNION 運算符
UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）並消去表中任何重復行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。
B： EXCEPT 運算符
EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重復行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重復行。
C： INTERSECT 運算符
INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行並消除所有重復行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重復行。
註：使用運算詞的幾個查詢結果行必須是一致的。
12、說明：使用外連接
A、left outer join：
左外連接（左連接）：結果集幾包括連接表的匹配行，也包括左連接表的所有行。
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
B：right outer join:
右外連接(右連接)：結果集既包括連接表的匹配連接行，也包括右連接表的所有行。
C：full outer join：
全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。

其次，大家來看一些不錯的sql語句
1、說明：復製表(只復制結構,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a

2、說明：拷貝表(拷貝數據,源表名：a 目標表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;

3、說明：跨資料庫之間表的拷貝(具體數據使用絕對路徑) (Access可用)
insert into b(a, b, c) select d,e,f from b in 『具體資料庫』 where 條件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

4、說明：子查詢(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

5、說明：顯示文章、提交人和最後回復時間
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

6、說明：外連接查詢(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、說明：在線視圖查詢(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;

8、說明：between的用法,between限制查詢數據范圍時包括了邊界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 數值1 and 數值2

9、說明：in 的使用方法
select * from table1 where a [not] in (『值1』,』值2』,』值4』,』值6』)

10、說明：兩張關聯表，刪除主表中已經在副表中沒有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

11、說明：四表聯查問題：
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 .....

12、說明：日程安排提前五分鍾提醒
SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>5

13、說明：一條sql 語句搞定資料庫分頁
select top 10 b.* from (select top 20 主鍵欄位,排序欄位 from 表名 order by 排序欄位 desc) a,表名 b where b.主鍵欄位 = a.主鍵欄位 order by a.排序欄位

14、說明：前10條記錄
select top 10 * form table1 where 范圍

15、說明：選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息(類似這樣的用法可以用於論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

16、說明：包括所有在 TableA 中但不在 TableB和TableC 中的行並消除所有重復行而派生出一個結果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

17、說明：隨機取出10條數據
select top 10 * from tablename order by newid()

18、說明：隨機選擇記錄
select newid()

19、說明：刪除重復記錄
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

20、說明：列出資料庫里所有的表名
select name from sysobjects where type='U'

21、說明：列出表裡的所有的
select name from syscolumns where id=object_id('TableName')

22、說明：列示type、vender、pcs欄位，以type欄位排列，case可以方便地實現多重選擇，類似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
顯示結果：
type vender pcs
電腦 A 1
電腦 A 1
光碟 B 2
光碟 A 2
手機 B 3
手機 C 3

23、說明：初始化表table1
TRUNCATE TABLE table1

24、說明：選擇從10到15的記錄
select top 5 * from (select top 15 * from table order by id asc) table_別名 order by id desc

隨機選擇資料庫記錄的方法（使用Randomize函數，通過SQL語句實現）
對存儲在資料庫中的數據來說，隨機數特性能給出上面的效果，但它們可能太慢了些。你不能要求ASP「找個隨機數」然後列印出來。實際上常見的解決方案是建立如下所示的循環：
Randomize
RNumber = Int(Rnd*499) +1

While Not objRec.EOF
If objRec("ID") = RNumber THEN
... 這里是執行腳本 ...
end if
objRec.MoveNext
Wend

這很容易理解。首先，你取出1到500范圍之內的一個隨機數（假設500就是資料庫內記錄的總數）。然後，你遍歷每一記錄來測試ID 的值、檢查其是否匹配RNumber。滿足條件的話就執行由THEN 關鍵字開始的那一塊代碼。假如你的RNumber 等於495，那麼要循環一遍資料庫花的時間可就長了。雖然500這個數字看起來大了些，但相比更為穩固的企業解決方案這還是個小型資料庫了，後者通常在一個資料庫內就包含了成千上萬條記錄。這時候不就死定了？
採用SQL，你就可以很快地找出准確的記錄並且打開一個只包含該記錄的recordset，如下所示：
Randomize
RNumber = Int(Rnd*499) + 1

SQL = "SELECT * FROM Customers WHERE ID = " & RNumber

set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")

不必寫出RNumber 和ID，你只需要檢查匹配情況即可。只要你對以上代碼的工作滿意，你自可按需操作「隨機」記錄。Recordset沒有包含其他內容，因此你很快就能找到你需要的記錄這樣就大大降低了處理時間。
再談隨機數
現在你下定決心要榨乾Random 函數的最後一滴油，那麼你可能會一次取出多條隨機記錄或者想採用一定隨機范圍內的記錄。把上面的標准Random 示例擴展一下就可以用SQL應對上面兩種情況了。
為了取出幾條隨機選擇的記錄並存放在同一recordset內，你可以存儲三個隨機數，然後查詢資料庫獲得匹配這些數字的記錄：
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3

假如你想選出10條記錄（也許是每次頁面裝載時的10條鏈接的列表），你可以用BETWEEN 或者數學等式選出第一條記錄和適當數量的遞增記錄。這一操作可以通過好幾種方式來完成，但是 SELECT 語句只顯示一種可能（這里的ID 是自動生成的號碼）：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"

注意：以上代碼的執行目的不是檢查資料庫內是否有9條並發記錄。

隨機讀取若干條記錄，測試過
Access語法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
mysqlelect * From 表名 Order By rand() Limit n
Access左連接語法(最近開發要用左連接,Access幫助什麼都沒有,網上沒有Access的SQL說明,只有自己測試, 現在記下以備後查)
語法elect table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...
使用SQL語句用...代替過長的字元串顯示
語法：
SQL資料庫：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
Access資料庫：SELECT iif(len(field)>2,left(field,2)+'...',field) FROM tablename;

Conn.Execute說明
Execute方法
該方法用於執行SQL語句。根據SQL語句執行後是否返回記錄集，該方法的使用格式分為以下兩種：
1．執行SQL查詢語句時，將返回查詢得到的記錄集。用法為：
Set 對象變數名=連接對象.Execute("SQL 查詢語言")
Execute方法調用後，會自動創建記錄集對象，並將查詢結果存儲在該記錄對象中，通過Set方法，將記錄集賦給指定的對象保存，以後對象變數就代表了該記錄集對象。

2．執行SQL的操作性語言時，沒有記錄集的返回。此時用法為：
連接對象.Execute "SQL 操作性語句" [, RecordAffected][, Option]
·RecordAffected 為可選項，此出可放置一個變數，SQL語句執行後，所生效的記錄數會自動保存到該變數中。通過訪問該變數，就可知道SQL語句隊多少條記錄進行了操作。
·Option 可選項，該參數的取值通常為adCMDText，它用於告訴ADO，應該將Execute方法之後的第一個字元解釋為命令文本。通過指定該參數，可使執行更高效。

·BeginTrans、RollbackTrans、CommitTrans方法
這三個方法是連接對象提供的用於事務處理的方法。BeginTrans用於開始一個事物；RollbackTrans用於回滾事務；CommitTrans用於提交所有的事務處理結果，即確認事務的處理。
事務處理可以將一組操作視為一個整體，只有全部語句都成功執行後，事務處理才算成功；若其中有一個語句執行失敗，則整個處理就算失敗，並恢復到處里前的狀態。
BeginTrans和CommitTrans用於標記事務的開始和結束，在這兩個之間的語句，就是作為事務處理的語句。判斷事務處理是否成功，可通過連接對象的Error集合來實現，若Error集合的成員個數不為0，則說明有錯誤發生，事務處理失敗。Error集合中的每一個Error對象，代表一個錯誤信息。
參考資料：http://dev.csdn.net/author/qq_x_yy/.html

④ 如何優化SQL語句

一、問題的提出
在應用系統開發初期，由於開發資料庫數據比較少，對於查詢SQL語句，復雜視圖的的編寫等體會不出SQL語句各種寫法的性能優劣，但是如果將應用系統提交實際應用後，隨著資料庫中數據的增加，系統的響應速度就成為目前系統需要解決的最主要的問題之一。系統優化中一個很重要的方面就是SQL語句的優化。對於海量數據，劣質SQL語句和優質SQL語句之間的速度差別可以達到上百倍，可見對於一個系統不是簡單地能實現其功能就可，而是要寫出高質量的SQL語句，提高系統的可用性。
在多數情況下，Oracle使用索引來更快地遍歷表，優化器主要根據定義的索引來提高性能。但是，如果在SQL語句的where子句中寫的SQL代碼不合理，就會造成優化器刪去索引而使用全表掃描，一般就這種SQL語句就是所謂的劣質SQL語句。在編寫SQL語句時我們應清楚優化器根據何種原則來刪除索引，這有助於寫出高性能的SQL語句。
二、SQL語句編寫注意問題
下面就某些SQL語句的where子句編寫中需要注意的問題作詳細介紹。在這些where子句中，即使某些列存在索引，但是由於編寫了劣質的SQL，系統在運行該SQL語句時也不能使用該索引，而同樣使用全表掃描，這就造成了響應速度的極大降低。
1.
IS
NULL
與
IS
NOT
NULL
不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。
任何在where子句中使用is
null或is
not
null的語句優化器是不允許使用索引的。
2.
聯接列
對於有聯接的列，即使最後的聯接值為一個靜態值，優化器是不會使用索引的。我們一起來看一個例子，假定有一個職工表(employee)，對於一個職工的姓和名分成兩列存放(FIRST_NAME和LAST_NAME)，現在要查詢一個叫比爾.柯林頓(Bill
Cliton)的職工。
下面是一個採用聯接查詢的SQL語句，
select
*
from
employss
where
first_name||''||last_name
='Beill
Cliton';
上面這條語句完全可以查詢出是否有Bill
Cliton這個員工，但是這里需要注意，系統優化器對基於last_name創建的索引沒有使用。
當採用下面這種SQL語句的編寫，Oracle系統就可以採用基於last_name創建的索引。
***
where
first_name
='Beill'
and
last_name
='Cliton';
.
帶通配符(%)的like語句
同樣以上面的例子來看這種情況。目前的需求是這樣的，要求在職工表中查詢名字中包含cliton的人。可以採用如下的查詢SQL語句:
select
*
from
employee
where
last_name
like
'%cliton%';
這里由於通配符(%)在搜尋詞首出現，所以Oracle系統不使用last_name的索引。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當通配符出現在字元串其他位置時，優化器就能利用索引。在下面的查詢中索引得到了使用:
select
*
from
employee
where
last_name
like
'c%';
4.
Order
by語句
ORDER
BY語句決定了Oracle如何將返回的查詢結果排序。Order
by語句對要排序的列沒有什麼特別的限制，也可以將函數加入列中(象聯接或者附加等)。任何在Order
by語句的非索引項或者有計算表達式都將降低查詢速度。
仔細檢查order
by語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫order
by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在order
by子句中使用表達式。
5.
NOT
我們在查詢時經常在where子句使用一些邏輯表達式，如大於、小於、等於以及不等於等等，也可以使用and(與)、or(或)以及not(非)。NOT可用來對任何邏輯運算符號取反。下面是一個NOT子句的例子:
...
where
not
(status
='VALID')
如果要使用NOT，則應在取反的短語前面加上括弧，並在短語前面加上NOT運算符。NOT運算符包含在另外一個邏輯運算符中，這就是不等於(<>)運算符。換句話說，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運算符中，見下例:
...
where
status
<>'INVALID';
對這個查詢，可以改寫為不使用NOT:
select
*
from
employee
where
salary<3000
or
salary>3000;
雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。
雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。

⑤ SQL語句執行過程詳解

SQL語句執行過程詳解
一條sql，plsql的執行到底是怎樣執行的呢？
一、SQL語句執行原理：
第一步:客戶端把語句發給伺服器端執行當我們在客戶端執行 select 語句時,客戶端會把這條 SQL 語句發送給伺服器端,讓伺服器端的
進程來處理這語句。也就是說,Oracle 客戶端是不會做任何的操作,他的主要任務就是把客戶端產生
的一些 SQL 語句發送給伺服器端。雖然在客戶端也有一個資料庫進程,但是,這個進程的作用跟伺服器
上的進程作用事不相同的。伺服器上的資料庫進程才會對SQL 語句進行相關的處理。不過,有個問題需
要說明,就是客戶端的進程跟伺服器的進程是一一對應的。也就是說,在客戶端連接上伺服器後,在客戶
端與伺服器端都會形成一個進程,客戶端上的我們叫做客戶端進程;而伺服器上的我們叫做伺服器進程。
第二步:語句解析
當客戶端把 SQL 語句傳送到伺服器後,伺服器進程會對該語句進行解析。同理,這個解析的工作,
也是在伺服器端所進行的。雖然這只是一個解析的動作,但是,其會做很多「小動作」。
1. 查詢高速緩存(library cache)。伺服器進程在接到客戶端傳送過來的 SQL 語句時,不
會直接去資料庫查詢。而是會先在資料庫的高速緩存中去查找,是否存在相同語句的執行計劃。如果在
數據高速緩存中,則伺服器進程就會直接執行這個 SQL 語句,省去後續的工作。所以,採用高速數據緩
存的話,可以提高 SQL 語句的查詢效率。一方面是從內存中讀取數據要比從硬碟中的數據文件中讀取
數據效率要高,另一方面,也是因為這個語句解析的原因。
不過這里要注意一點,這個數據緩存跟有些客戶端軟體的數據緩存是兩碼事。有些客戶端軟體為了
提高查詢效率,會在應用軟體的客戶端設置數據緩存。由於這些數據緩存的存在,可以提高客戶端應用軟
件的查詢效率。但是,若其他人在伺服器進行了相關的修改,由於應用軟體數據緩存的存在,導致修改的
數據不能及時反映到客戶端上。從這也可以看出,應用軟體的數據緩存跟資料庫伺服器的高速數據緩存
不是一碼事。
2. 語句合法性檢查(data dict cache)。當在高速緩存中找不到對應的 SQL 語句時,則服
務器進程就會開始檢查這條語句的合法性。這里主要是對 SQL 語句的語法進行檢查,看看其是否合乎
語法規則。如果伺服器進程認為這條 SQL 語句不符合語法規則的時候,就會把這個錯誤信息,反饋給客
戶端。在這個語法檢查的過程中,不會對 SQL 語句中所包含的表名、列名等等進行 SQL 他只是語法
上的檢查。
3. 語言含義檢查(data dict cache)。若 SQL 語句符合語法上的定義的話,則伺服器進程
接下去會對語句中的欄位、表等內容進行檢查。看看這些欄位、表是否在資料庫中。如果表名與列名不
准確的話,則資料庫會就會反饋錯誤信息給客戶端。所以,有時候我們寫 select 語句的時候,若語法
與表名或者列名同時寫錯的話,則系統是先提示說語法錯誤,等到語法完全正確後,再提示說列名或表名
錯誤。
4. 獲得對象解析鎖(control structer)。當語法、語義都正確後,系統就會對我們需要查詢
的對象加鎖。這主要是為了保障數據的一致性,防止我們在查詢的過程中,其他用戶對這個對象的結構發
生改變。
5. 數據訪問許可權的核對(data dict cache)。當語法、語義通過檢查之後,客戶端還不一定
能夠取得數據。伺服器進程還會檢查,你所連接的用戶是否有這個數據訪問的許可權。若你連接上伺服器
的用戶不具有數據訪問許可權的話,則客戶端就不能夠取得這些數據。有時候我們查詢數據的時候,辛辛苦
苦地把 SQL 語句寫好、編譯通過,但是,最後系統返回個「沒有許可權訪問數據」的錯誤信息,讓我們氣
半死。這在前端應用軟體開發調試的過程中,可能會碰到。所以,要注意這個問題,資料庫伺服器進程先
檢查語法與語義,然後才會檢查訪問許可權。
6. 確定最佳執行計劃 ?。當語句與語法都沒有問題,許可權也匹配的話,伺服器進程還是不會直接對
資料庫文件進行查詢。伺服器進程會根據一定的規則,對這條語句進行優化。不過要注意,這個優化是有
限的。一般在應用軟體開發的過程中,需要對資料庫的 sql 語言進行優化,這個優化的作用要大大地大
於伺服器進程的自我優化。所以,一般在應用軟體開發的時候,資料庫的優化是少不了的。當伺服器進程
的優化器確定這條查詢語句的最佳執行計劃後,就會將這條 SQL 語句與執行計劃保存到數據高速緩存
(library cache)。如此的話,等以後還有這個查詢時,就會省略以上的語法、語義與許可權檢查的步驟,
而直接執行 SQL 語句,提高 SQL 語句處理效率。
第三步:語句執行
語句解析只是對 SQL 語句的語法進行解析,以確保伺服器能夠知道這條語句到底表達的是什麼意
思。等到語句解析完成之後,資料庫伺服器進程才會真正的執行這條 SQL 語句。這個語句執行也分兩
種情況。
一是若被選擇行所在的數據塊已經被讀取到數據緩沖區的話,則伺服器進程會直接把這個數據傳遞
給客戶端,而不是從資料庫文件中去查詢數據。
若數據不在緩沖區中,則伺服器進程將從資料庫文件中查詢相關數據,並把這些數據放入到數據緩沖
區中(buffer cache)。
第四步:提取數據
當語句執行完成之後,查詢到的數據還是在伺服器進程中,還沒有被傳送到客戶端的用戶進程。所以,
在伺服器端的進程中,有一個專門負責數據提取的一段代碼。他的作用就是把查詢到的數據結果返回給
用戶端進程,從而完成整個查詢動作。從這整個查詢處理過程中,我們在資料庫開發或者應用軟體開發過
程中,需要注意以下幾點:
一是要了解資料庫緩存跟應用軟體緩存是兩碼事情。資料庫緩存只有在資料庫伺服器端才存在,在
客戶端是不存在的。只有如此,才能夠保證資料庫緩存中的內容跟資料庫文件的內容一致。才能夠根據
相關的規則,防止數據臟讀、錯讀的發生。而應用軟體所涉及的數據緩存,由於跟資料庫緩存不是一碼事
情,所以,應用軟體的數據緩存雖然可以提高數據的查詢效率,但是,卻打破了數據一致性的要求,有時候
會發生臟讀、錯讀等情況的發生。所以,有時候,在應用軟體上有專門一個功能,用來在必要的時候清除
數據緩存。不過,這個數據緩存的清除,也只是清除本機上的數據緩存,或者說,只是清除這個應用程序
的數據緩存,而不會清除資料庫的數據緩存。
二是絕大部分 SQL 語句都是按照這個處理過程處理的。我們 DBA 或者基於 Oracle 資料庫的
開發人員了解這些語句的處理過程,對於我們進行涉及到 SQL 語句的開發與調試,是非常有幫助的。有
時候,掌握這些處理原則,可以減少我們排錯的時間。特別要注意,資料庫是把數據查詢許可權的審查放在
語法語義的後面進行檢查的。所以,有時會若光用資料庫的許可權控制原則,可能還不能滿足應用軟體許可權
控制的需要。此時,就需要應用軟體的前台設置,實現許可權管理的要求。而且,有時應用資料庫的許可權管
理,也有點顯得繁瑣,會增加伺服器處理的工作量。因此,對於記錄、欄位等的查詢許可權控制,大部分程
序涉及人員喜歡在應用程序中實現,而不是在資料庫上實現。
DBCC DROPCLEANBUFFERS
從緩沖池中刪除所有清除緩沖區。
DBCC FREEPROCCACHE
從過程緩存中刪除所有元素。
DBCC FREESYSTEMCACHE
從所有緩存中釋放所有未使用的緩存條目
SQL語句中的函數、關鍵字、排序等執行順序:
1. FROM 子句返回初始結果集。
2. WHERE 子句排除不滿足搜索條件的行。
3. GROUP BY 子句將選定的行收集到 GROUP BY 子句中各個唯一值的組中。
4. 選擇列表中指定的聚合函數可以計算各組的匯總值。
5. 此外,HAVING 子句排除不滿足搜索條件的行。
6. 計算所有的表達式;
7. 使用 order by 對結果集進行排序。
8. 查找你要搜索的欄位。
二、SQL語句執行完整過程：
1.用戶進程提交一個 sql 語句:
update temp set a=a*2，給伺服器進程。
2.伺服器進程從用戶進程把信息接收到後,在 PGA 中就要此進程分配所需內存,存儲相關的信息,如在會
話內存存儲相關的登錄信息等。
3.伺服器進程把這個 sql 語句的字元轉化為 ASCII 等效數字碼,接著這個 ASCII 碼被傳遞給一個
HASH 函數,並返回一個 hash 值,然後伺服器進程將到shared pool 中的 library cache 中去查找是否存在相
同的 hash 值,如果存在,伺服器進程將使用這條語句已高速緩存在 SHARED POOL 的library cache 中的已
分析過的版本來執行。
4.如果不存在,伺服器進程將在 CGA 中,配合 UGA 內容對 sql,進行語法分析,首先檢查語法的正確性,接
著對語句中涉及的表,索引,視圖等對象進行解析,並對照數據字典檢查這些對象的名稱以及相關結構，並根據
ORACLE 選用的優化模式以及數據字典中是否存在相應對象的統計數據和是否使用了存儲大綱來生成一個
執行計劃或從存儲大綱中選用一個執行計劃,然後再用數據字典核對此用戶對相應對象的執行許可權,最後生成
一個編譯代碼。
5.ORACLE 將這條 sql 語句的本身實際文本、HASH 值、編譯代碼、與此語名相關聯的任何統計數據
和該語句的執行計劃緩存在 SHARED POOL 的 library cache中。伺服器進程通過 SHARED POOL 鎖存
器(shared pool latch)來申請可以向哪些共享 PL/SQL 區中緩存這此內容,也就是說被SHARED POOL 鎖存
器鎖定的 PL/SQL 區中的塊不可被覆蓋,因為這些塊可能被其它進程所使用。
6.在 SQL 分析階段將用到 LIBRARY
CACHE,從數據字典中核對表、視圖等結構的時候,需要將數據
字典從磁碟讀入 LIBRARY
CACHE,因此,在讀入之前也要使用LIBRARY
CACHE 鎖存器(library cache
pin,library cache lock)來申請用於緩存數據字典。到現在為止,這個 sql 語句已經被編譯成可執行的代碼了,
但還不知道要操作哪些數據,所以伺服器進程還要為這個 sql 准備預處理數據。
7.首先伺服器進程要判斷所需數據是否在 db buffer 存在,如果存在且可用,則直接獲取該數據,同時根據
LRU 演算法增加其訪問計數;如果 buffer 不存在所需數據,則要從數據文件上讀取首先伺服器進程將在表頭部
請求 TM 鎖(保證此事務執行過程其他用戶不能修改表的結構),如果成功加 TM 鎖,再請求一些行級鎖(TX
鎖),如果 TM、TX 鎖都成功加鎖,那麼才開始從數據文件讀數據,在讀數據之前,要先為讀取的文件准備好
buffer 空間。伺服器進程需要掃面 LRU list 尋找 free db buffer,掃描的過程中,伺服器進程會把發現的所有
已經被修改過的 db buffer 注冊到 dirty list 中, 這些 dirty buffer 會通過 dbwr 的觸發條件,隨後會被寫出到
數據文件,找到了足夠的空閑 buffer,就可以把請求的數據行所在的數據塊放入到 db buffer 的空閑區域或者
覆蓋已經被擠出 LRU list 的非臟數據塊緩沖區,並排列在 LRU list 的頭部,也就是在數據塊放入 DB
BUFFER 之前也是要先申請 db buffer 中的鎖存器,成功加鎖後,才能讀數據到 db buffer。
8.記日誌現在數據已經被讀入到 db buffer 了,現在伺服器進程將該語句所影響的並被讀
入 db buffer 中的這些行數據的 rowid 及要更新的原值和新值及 scn 等信息從 PGA 逐條的寫入 redo log
buffer 中。在寫入 redo log buffer 之前也要事先請求 redo log buffer 的鎖存器,成功加鎖後才開始寫入,當
寫入達到 redo log buffer 大小的三分之一或寫入量達到 1M 或超過三秒後或發生檢查點時或者 dbwr 之前
發生,都會觸發 lgwr 進程把 redo log buffer 的數據寫入磁碟上的 redo file 文件中(這個時候會產生log file
sync 等待事件)
已經被寫入 redofile 的 redo log buffer 所持有的鎖存器會被釋放,並可被後來的寫入信息覆蓋,
redo log buffer是循環使用的。Redo file 也是循環使用的,當一個 redo file 寫滿後,lgwr 進程會自動切換到
下一 redo file(這個時候可能出現 log fileswitch(checkpoint complete)等待事件)。如果是歸檔模式,歸檔進
程還要將前一個寫滿的 redo file 文件的內容寫到歸檔日誌文件中(這個時候可能出現 log file
switch(archiving needed)。
9.為事務建立回滾段在完成本事務所有相關的 redo log buffer 之後,伺服器進程開始改寫這個 db buffer
的塊頭部事務列表並寫入 scn,然後包含這個塊的頭部事務列表及 scn 信息的數據副本放入回滾段中,將
這時回滾段中的信息稱為數據塊的「前映像「,這個」前映像「用於以後的回滾、恢復和一致性讀。(回滾段可以
存儲在專門的回滾表空間中,這個表空間由一個或多個物理文件組成,並專用於回滾表空間,回滾段也可在其它
表空間中的數據文件中開辟。
10.本事務修改數據塊准備工作都已經做好了,現在可以改寫 db buffer 塊的數據內容了,並在塊的頭部寫
入回滾段的地址。
11.放入 dirty list 如果一個行數據多次 update 而未 commit,則在回滾段中將會有多個「前映像「,除了第
一個」前映像「含有 scn 信息外,其他每個「前映像「的頭部都有 scn 信息和「前前映像」回滾段地址。一個
update 只對應一個 scn,然後伺服器進程將在 dirty list 中建立一
條指向此 db buffer 塊的指針(方便 dbwr 進程可以找到 dirty list 的 db buffer 數據塊並寫入數據文件中)。
接著伺服器進程會從數據文件中繼續讀入第二個數據塊,重復前一數據塊的動作,數據塊的讀入、記日誌、建
立回滾段、修改數據塊、放入 dirty list。當 dirty queue 的長度達到閥值(一般是 25%),伺服器進程將通知
dbwr 把臟數據寫出,就是釋放 db buffer 上的鎖存器,騰出更多的 free db buffer。前面一直都是在說明
oracle 一次讀一個數據塊,其實 oracle 可以一次讀入多個數據塊(db_file_multiblock_read_count 來設置一
次讀入塊的個數)
說明:
在預處理的數據已經緩存在 db buffer 或剛剛被從數據文件讀入到 db buffer 中,就要根據 sql 語句
的類型來決定接下來如何操作。
1>如果是 select 語句,則要查看 db buffer 塊的頭部是否有事務,如果有事務,則從回滾段中讀取數據;如
果沒有事務,則比較 select 的 scn 和 db buffer 塊頭部的 scn,如果前者小於後者,仍然要從回滾段中讀取數據;
如果前者大於後者,說明這是一非臟緩存,可以直接讀取這個 db buffer 塊的中內容。
2>如果是 DML 操作,則即使在 db buffer 中找到一個沒有事務,而且 SCN 比自己小的非臟
緩存數據塊,伺服器進程仍然要到表的頭部對這條記錄申請加鎖,加鎖成功才能進行後續動作,如果不成功,則要
等待前面的進程解鎖後才能進行動作(這個時候阻塞是 tx 鎖阻塞)。
用戶 commit 或 rollback 到現在為止,數據已經在 db buffer 或數據文件中修改完
成,但是否要永久寫到數文件中,要由用戶來決定 commit(保存更改到數據文件) rollback 撤銷數據的更改)。
1.用戶執行 commit 命令
只有當 sql 語句所影響的所有行所在的最後一個塊被讀入 db buffer 並且重做信息被寫入 redo log
buffer(僅指日誌緩沖區,而不包括日誌文件)之後,用戶才可以發去 commit 命令,commit 觸發 lgwr 進程,但不
強制立即 dbwr來釋放所有相應 db buffer 塊的鎖(也就是no-force-at-commit,即提交不強制寫),也就是說有
可能雖然已經 commit 了,但在隨後的一段時間內 dbwr 還在寫這條 sql 語句所涉及的數據塊。表頭部的行鎖
並不在 commit 之後立即釋放,而是要等 dbwr 進程完成之後才釋放,這就可能會出現一個用戶請求另一用戶
已經 commit 的資源不成功的現象。
A .從 Commit 和 dbwr 進程結束之間的時間很短,如果恰巧在 commit 之後,dbwr 未結束之前斷電,因為
commit 之後的數據已經屬於數據文件的內容,但這部分文件沒有完全寫入到數據文件中。所以需要前滾。由
於 commit 已經觸發 lgwr,這些所有未來得及寫入數據文件的更改會在實例重啟後,由 smon 進程根據重做日
志文件來前滾,完成之前 commit 未完成的工作(即把更改寫入數據文件)。
B.如果未 commit 就斷電了,因為數據已經在 db buffer 更改了,沒有 commit,說明這部分數據不屬於數
據文件,由於 dbwr 之前觸發 lgwr 也就是只要數據更改,(肯定要先有 log) 所有 DBWR,在數據文件上的修改
都會被先一步記入重做日誌文件,實例重啟後,SMON 進程再根據重做日誌文件來回滾。
其實 smon 的前滾回滾是根據檢查點來完成的,當一個全部檢查點發生的時候,首先讓 LGWR 進程將
redo log buffer 中的所有緩沖(包含未提交的重做信息)寫入重做日誌文件,然後讓 dbwr 進程將 db buffer 已
提交的緩沖寫入數據文件(不強制寫未提交的)。然後更新控制文件和數據文件頭部的 SCN,表明當前資料庫
是一致的,在相鄰的兩個檢查點之間有很多事務,有提交和未提交的。
像前面的前滾回滾比較完整的說法是如下的說明:

A.發生檢查點之前斷電,並且當時有一個未提交的改變正在進行,實例重啟之後,SMON 進程將從上一個
檢查點開始核對這個檢查點之後記錄在重做日誌文件中已提交的和未提交改變,因為
dbwr 之前會觸發 lgwr,所以 dbwr 對數據文件的修改一定會被先記錄在重做日誌文件中。因此,斷電前被
DBWN 寫進數據文件的改變將通過重做日誌文件中的記錄進行還原,叫做回滾,
B. 如果斷電時有一個已提交,但 dbwr 動作還沒有完全完成的改變存在,因為已經提交,提交會觸發 lgwr
進程,所以不管 dbwr 動作是否已完成,該語句將要影響的行及其產生的結果一定已經記錄在重做日誌文件中
了,則實例重啟後,SMON 進程根據重做日誌文件進行前滾.
實例失敗後用於恢復的時間由兩個檢查點之間的間隔大小來決定,可以通個四個參數設置檢查點執行的頻
率:

Log_checkpoint_interval:
決定兩個檢查點之間寫入重做日誌文件的系統物理塊(redo blocks)
的大小,默認值是 0,無限制。
log_checkpoint_timeout:
兩個檢查點之間的時間長度（秒）默認值 1800s。
fast_start_io_target:
決定了用於恢復時需要處理的塊的多少,默認值是 0,無限制。
fast_start_mttr_target:
直接決定了用於恢復的時間的長短,默認值是 0,無限制(SMON 進程執行的前滾
和回滾與用戶的回滾是不同的,SMON 是根據重做日誌文件進行前滾或回滾,而用戶的回滾一定是根據回滾段
的內容進行回滾的。
在這里要說一下回滾段存儲的數據,假如是 delete 操作,則回滾段將會記錄整個行的數據,假如是 update,
則回滾段只記錄被修改了的欄位的變化前的數據(前映像),也就是沒有被修改的欄位是不會被記錄的,假如是
insert,則回滾段只記錄插入記錄的 rowid。這樣假如事務提交,那回滾段中簡單標記該事務已經提交;假如是
回退,則如果操作是 delete,回退的時候把回滾段中數據重新寫回數據塊,操作如果是 update,則把變化前數據
修改回去,操作如果是 insert,則根據記錄的 rowid 把該記錄刪除。
2.如果用戶 rollback。
則伺服器進程會根據數據文件塊和 DB BUFFER 中塊的頭部的事務列表和 SCN 以及回滾段地址找到
回滾段中相應的修改前的副本,並且用這些原值來還原當前數據文件中已修改但未提交的改變。如果有多個
「前映像」,伺服器進程會在一個「前映像」的頭部找到「前前映像」的回滾段地址,一直找到同一事務下的最早的
一個「前映像」為止。一旦發出了 COMMIT,用戶就不能rollback,這使得 COMMIT 後 DBWR 進程還沒有
全部完成的後續動作得到了保障。到現在為例一個事務已經結束了。
說明:
TM 鎖:
符合 lock 機制的,用於保護對象的定義不被修改。 TX 鎖:
這個鎖代表一個事務,是行
級鎖,用數據塊頭、數據記錄頭的一些欄位表示,也是符合 lock 機制,有 resource structure、lock
structure、enqueue 演算法。

⑥ 請問SQL語句中若欄位名本身包含有方括弧的SELECT語句該怎麼寫

試試使用後面加參數的方式，以ASP.NET c#為例

首先建立一個COMMAND對象
OleDbCommand mycmd = new OleDbCommand("select @canshu1,@canshu2 from 表名「, con);
其中@後面的就是參數，
mycmd.Parameters.AddWithValue("@canshu1", 」[sssd]「); 這樣就把[sssd]換到以前的語句里了，這種方式可以避免方括弧，單引號等敏感字元

⑦ SQL語句執行流程與順序原理解析

SQL語句執行流程與順序原理解析
Oracle語句執行流程
第一步：客戶端把語句發給伺服器端執行
當我們在客戶端執行SQL語句時，客戶端會把這條SQL語句發送給伺服器端，讓伺服器端的進程來處理這語句。也就是說，Oracle 客戶端是不會做任何的操作，他的主要任務就是把客戶端產生的一些SQL語句發送給伺服器端。伺服器進程從用戶進程把信息接收到後，在PGA 中就要此進程分配所需內存，存儲相關的信息，如：在會話內存存儲相關的登錄信息等。
雖然在客戶端也有一個資料庫進程，但是，這個進程的作用跟伺服器上的進程作用是不相同的，伺服器上的資料庫進程才會對SQL 語句進行相關的處理。不過，有個問題需要說明，就是客戶端的進程跟伺服器的進程是一一對應的。也就是說，在客戶端連接上伺服器後，在客戶端與伺服器端都會形成一個進程，客戶端上的我們叫做客戶端進程，而伺服器上的我們叫做伺服器進程。
第二步：語句解析
當客戶端把SQL語句傳送到伺服器後，伺服器進程會對該語句進行解析。這個解析的工作是在伺服器端所進行的，解析動作又可分為很多小動作。
1）查詢高速緩存（library cache）
伺服器進程在接到客戶端傳送過來的SQL語句時，不會直接去資料庫查詢。伺服器進程把這個SQL語句的字元轉化為ASCII等效數字碼，接著這個ASCII碼被傳遞給一個HASH函數，並返回一個hash值，然後伺服器進程將到shared pool中的library cache（高速緩存）中去查找是否存在相同的hash值。如果存在，伺服器進程將使用這條語句已高速緩存在SHARED POOL的library cache中的已分析過的版本來執行，省去後續的解析工作，這便是軟解析。若調整緩存中不存在，則需要進行後面的步驟，這便是硬解析。硬解析通常是昂貴的操作，大約占整個SQL執行的70%左右的時間，硬解析會生成執行樹，執行計劃，等等。
所以，採用高速數據緩存的話，可以提高SQL 語句的查詢效率。其原因有兩方面：一方面是從內存中讀取數據要比從硬碟中的數據文件中讀取數據效率要高，另一方面也是因為避免語句解析而節省了時間。
不過這里要注意一點，這個數據緩存跟有些客戶端軟體的數據緩存是兩碼事。有些客戶端軟體為了提高查詢效率，會在應用軟體的客戶端設置數據緩存。由於這些數據緩存的存在，可以提高客戶端應用軟體的查詢效率。但是，若其他人在伺服器進行了相關的修改，由於應用軟體數據緩存的存在，導致修改的數據不能及時反映到客戶端上。從這也可以看出，應用軟體的數據緩存跟資料庫伺服器的高速數據緩存不是一碼事。
2）語句合法性檢查（data dict cache）
當在高速緩存中找不到對應的SQL語句時，則伺服器進程就會開始檢查這條語句的合法性。這里主要是對SQL語句的語法進行檢查，看看其是否合乎語法規則。如果伺服器進程認為這條SQL語句不符合語法規則的時候，就會把這個錯誤信息反饋給客戶端。在這個語法檢查的過程中，不會對SQL語句中所包含的表名、列名等等進行檢查，只是檢查語法。
3）語言含義檢查（data dict cache）
若SQL 語句符合語法上的定義的話，則伺服器進程接下去會對語句中涉及的表、索引、視圖等對象進行解析，並對照數據字典檢查這些對象的名稱以及相關結構，看看這些欄位、表、視圖等是否在資料庫中。如果表名與列名不準確的話，則資料庫會就會反饋錯誤信息給客戶端。
所以，有時候我們寫select語句的時候，若語法與表名或者列名同時寫錯的話，則系統是先提示說語法錯誤，等到語法完全正確後再提示說列名或表名錯誤。
4）獲得對象解析鎖（control structer）
當語法、語義都正確後，系統就會對我們需要查詢的對象加鎖。這主要是為了保障數據的一致性，防止我們在查詢的過程中，其他用戶對這個對象的結構發生改變。
5）數據訪問許可權的核對（data dict cache）
當語法、語義通過檢查之後，客戶端還不一定能夠取得數據，伺服器進程還會檢查連接用戶是否有這個數據訪問的許可權。若用戶不具有數據訪問許可權的話，則客戶端就不能夠取得這些數據。要注意的是資料庫伺服器進程先檢查語法與語義，然後才會檢查訪問許可權。
6）確定最佳執行計劃
當語法與語義都沒有問題許可權也匹配，伺服器進程還是不會直接對資料庫文件進行查詢。伺服器進程會根據一定的規則，對這條語句進行優化。在執行計劃開發之前會有一步查詢轉換，如：視圖合並、子查詢解嵌套、謂語前推及物化視圖重寫查詢等。為了確定採用哪個執行計劃，Oracle還需要收集統計信息確定表的訪問聯結方法等，最終確定可能的最低成本的執行計劃。
不過要注意，這個優化是有限的。一般在應用軟體開發的過程中，需要對資料庫的sql語句進行優化，這個優化的作用要大大地大於伺服器進程的自我優化。
當伺服器進程的優化器確定這條查詢語句的最佳執行計劃後，就會將這條SQL語句與執行計劃保存到數據高速緩存（library cache）。如此，等以後還有這個查詢時，就會省略以上的語法、語義與許可權檢查的步驟，而直接執行SQL語句，提高SQL語句處理效率。
第三步：綁定變數賦值
如果SQL語句中使用了綁定變數，掃描綁定變數的聲明，給綁定變數賦值，將變數值帶入執行計劃。若在解析的第一個步驟，SQL在高速緩沖中存在，則直接跳到該步驟。
第四步：語句執行
語句解析只是對SQL語句的語法進行解析，以確保伺服器能夠知道這條語句到底表達的是什麼意思。等到語句解析完成之後，資料庫伺服器進程才會真正的執行這條SQL語句。
對於SELECT語句：
1）首先伺服器進程要判斷所需數據是否在db buffer存在，如果存在且可用，則直接獲取該數據而不是從資料庫文件中去查詢數據，同時根據LRU 演算法增加其訪問計數；
2）若數據不在緩沖區中，則伺服器進程將從資料庫文件中查詢相關數據，並把這些數據放入到數據緩沖區中（buffer cache）。
其中，若數據存在於db buffer，其可用性檢查方式為：查看db buffer塊的頭部是否有事務，如果有事務，則從回滾段中讀取數據；如果沒有事務，則比較select的scn和db buffer塊頭部的scn，如果前者小於後者，仍然要從回滾段中讀取數據；如果前者大於後者，說明這是一非臟緩存，可以直接讀取這個db buffer塊的中內容。
對於DML語句（insert、delete、update）：
1）檢查所需的資料庫是否已經被讀取到緩沖區緩存中。如果已經存在緩沖區緩存，則直接執行步驟3；
2）若所需的資料庫並不在緩沖區緩存中，則伺服器將數據塊從數據文件讀取到緩沖區緩存中；
3）對想要修改的表取得的數據行鎖定（Row Exclusive Lock），之後對所需要修改的數據行取得獨占鎖；
4）將數據的Redo記錄復制到redo log buffer；
5）產生數據修改的undo數據；
6）修改db buffer；
7）dbwr將修改寫入數據文件；
其中，第2步，伺服器將數據從數據文件讀取到db buffer經經歷以下步驟：
1）首先伺服器進程將在表頭部請求TM鎖（保證此事務執行過程其他用戶不能修改表的結構），如果成功加TM鎖，再請求一些行級鎖（TX鎖），如果TM、TX鎖都成功加鎖，那麼才開始從數據文件讀數據。
2）在讀數據之前，要先為讀取的文件准備好buffer空間。伺服器進程需要掃描LRU list尋找free db buffer，掃描的過程中，伺服器進程會把發現的所有已經被修改過的db buffer注冊到dirty list中。如果free db buffer及非臟數據塊緩沖區不足時，會觸發dbwr將dirty buffer中指向的緩沖塊寫入數據文件，並且清洗掉這些緩沖區來騰出空間緩沖新讀入的數據。
3）找到了足夠的空閑buffer，伺服器進程將從數據文件中讀入這些行所在的每一個數據塊（db block）（DB BLOCK是ORACLE的最小操作單元，即使你想要的數據只是DB BLOCK中很多行中的一行或幾行，ORACLE也會把這個DB BLOCK中的所有行都讀入Oracle DB BUFFER中）放入db buffer的空閑的區域或者覆蓋已被擠出LRU list的非臟數據塊緩沖區，並且排列在LRU列表的頭部，也就是在數據塊放入db buffer之前也是要先申請db buffer中的鎖存器，成功加鎖後，才能讀數據到db buffer。
若數據塊已經存在於db buffer cache（有時也稱db buffer或db cache），即使在db buffer中找到一個沒有事務，而且SCN比自己小的非臟緩存數據塊，伺服器進程仍然要到表的頭部對這條記錄申請加鎖，加鎖成功才能進行後續動作，如果不成功，則要等待前面的進程解鎖後才能進行動作（這個時候阻塞是tx鎖阻塞）。
在記redo日誌時，其具體步驟如下：
1）數據被讀入到db buffer後，伺服器進程將該語句所影響的並被讀入db buffer中的這些行數據的rowid及要更新的原值和新值及scn等信息從PGA逐條的寫入redo log buffer中。在寫入redo log buffer之前也要事先請求redo log buffer的鎖存器，成功加鎖後才開始寫入。
2）當寫入達到redo log buffer大小的三分之一或寫入量達到1M或超過三秒後或發生檢查點時或者dbwr之前發生，都會觸發lgwr進程把redo log buffer的數據寫入磁碟上的redo file文件中（這個時候會產生log file sync等待事件）。
3）已經被寫入redo file的redo log buffer所持有的鎖存器會被釋放，並可被後來的寫入信息覆蓋，redo log buffer是循環使用的。Redo file也是循環使用的，當一個redo file寫滿後，lgwr進程會自動切換到下一redo file（這個時候可能出現log file switch（check point complete）等待事件）。如果是歸檔模式，歸檔進程還要將前一個寫滿的redo file文件的內容寫到歸檔日誌文件中（這個時候可能出現log file switch（archiving needed）。
在為事務建立undo信息時，其具體步驟如下：
1）在完成本事務所有相關的redo log buffer之後，伺服器進程開始改寫這個db buffer的塊頭部事務列表並寫入scn（一開始scn是寫在redo log buffer中的，並未寫在db buffer）。
2）然後包含這個塊的頭部事務列表及scn信息的數據副本放入回滾段中，將這時回滾段中的信息稱為數據塊的「前映像」，這個「前映像」用於以後的回滾、恢復和一致性讀。（回滾段可以存儲在專門的回滾表空間中，這個表空間由一個或多個物理文件組成，並專用於回滾表空間，回滾段也可在其它表空間中的數據文件中開辟）。
在修改信息寫入數據文件時，其具體步驟如下：
1）改寫db buffer塊的數據內容，並在塊的頭部寫入回滾段的地址。
2）將db buffer指針放入dirty list。如果一個行數據多次update而未commit，則在回滾段中將會有多個「前映像」，除了第一個「前映像」含有scn信息外，其他每個"前映像"的頭部都有scn信息和"前前映像"回滾段地址。一個update只對應一個scn，然後伺服器進程將在dirty list中建立一條指向此db buffer塊的指針（方便dbwr進程可以找到dirty list的db buffer數據塊並寫入數據文件中）。接著伺服器進程會從數據文件中繼續讀入第二個數據塊，重復前一數據塊的動作，數據塊的讀入、記日誌、建立回滾段、修改數據塊、放入dirty list。
3）當dirty queue的長度達到閥值（一般是25%），伺服器進程將通知dbwr把臟數據寫出，就是釋放db buffer上的鎖存器，騰出更多的free db buffer。前面一直都是在說明oracle一次讀一個數據塊，其實oracle可以一次讀入多個數據塊（db_file_multiblock_read_count來設置一次讀入塊的個數）
當執行commit時，具體步驟如下：
1）commit觸發lgwr進程，但不強制dbwr立即釋放所有相應db buffer塊的鎖。也就是說有可能雖然已經commit了，但在隨後的一段時間內dbwr還在寫這條sql語句所涉及的數據塊。表頭部的行鎖並不在commit之後立即釋放，而是要等dbwr進程完成之後才釋放，這就可能會出現一個用戶請求另一用戶已經commit的資源不成功的現象。
2）從Commit和dbwr進程結束之間的時間很短，如果恰巧在commit之後，dbwr未結束之前斷電，因為commit之後的數據已經屬於數據文件的內容，但這部分文件沒有完全寫入到數據文件中。所以需要前滾。由於commit已經觸發lgwr，這些所有未來得及寫入數據文件的更改會在實例重啟後，由smon進程根據重做日誌文件來前滾，完成之前commit未完成的工作（即把更改寫入數據文件）。
3）如果未commit就斷電了，因為數據已經在db buffer更改了，沒有commit，說明這部分數據不屬於數據文件。由於dbwr之前觸發lgwr也就是只要數據更改，（肯定要先有log）所有dbwr在數據文件上的修改都會被先一步記入重做日誌文件，實例重啟後，SMON進程再根據重做日誌文件來回滾。
其實smon的前滾回滾是根據檢查點來完成的，當一個全部檢查點發生的時候，首先讓LGWR進程將redologbuffer中的所有緩沖（包含未提交的重做信息）寫入重做日誌文件，然後讓dbwr進程將dbbuffer已提交的緩沖寫入數據文件（不強制寫未提交的）。然後更新控制文件和數據文件頭部的SCN，表明當前資料庫是一致的，在相鄰的兩個檢查點之間有很多事務，有提交和未提交的。
當執行rollback時，具體步驟如下：
伺服器進程會根據數據文件塊和db buffer中塊的頭部的事務列表和SCN以及回滾段地址找到回滾段中相應的修改前的副本，並且用這些原值來還原當前數據文件中已修改但未提交的改變。如果有多個」前映像「，伺服器進程會在一個「前映像」的頭部找到「前前映像」的回滾段地址，一直找到同一事務下的最早的一個「前映像」為止。一旦發出了commit，用戶就不能rollback，這使得commit後dbwr進程還沒有全部完成的後續動作得到了保障。
第五步：提取數據
當語句執行完成之後，查詢到的數據還是在伺服器進程中，還沒有被傳送到客戶端的用戶進程。所以，在伺服器端的進程中，有一個專門負責數據提取的一段代碼。他的作用就是把查詢到的數據結果返回給用戶端進程，從而完成整個查詢動作。
從這整個查詢處理過程中，我們在資料庫開發或者應用軟體開發過程中，需要注意以下幾點：
一是要了解資料庫緩存跟應用軟體緩存是兩碼事情。資料庫緩存只有在資料庫伺服器端才存在，在客戶端是不存在的。只有如此，才能夠保證資料庫緩存中的內容跟資料庫文件的內容一致。才能夠根據相關的規則，防止數據臟讀、錯讀的發生。而應用軟體所涉及的數據緩存，由於跟資料庫緩存不是一碼事情，所以，應用軟體的數據緩存雖然可以提高數據的查詢效率，但是，卻打破了數據一致性的要求，有時候會發生臟讀、錯讀等情況的發生。所以，有時候，在應用軟體上有專門一個功能，用來在必要的時候清除數據緩存。不過，這個數據緩存的清除，也只是清除本機上的數據緩存，或者說，只是清除這個應用程序的數據緩存，而不會清除資料庫的數據緩存。
二是絕大部分SQL語句都是按照這個處理過程處理的。我們DBA或者基於Oracle資料庫的開發人員了解這些語句的處理過程，對於我們進行涉及到SQL語句的開發與調試，是非常有幫助的。有時候，掌握這些處理原則，可以減少我們排錯的時間。特別要注意，資料庫是把數據查詢許可權的審查放在語法語義的後面進行檢查的。所以，有時會若光用資料庫的許可權控制原則，可能還不能滿足應用軟體許可權控制的需要。此時，就需要應用軟體的前台設置，實現許可權管理的要求。而且，有時應用資料庫的許可權管理，也有點顯得繁瑣，會增加伺服器處理的工作量。因此，對於記錄、欄位等的查詢許可權控制，大部分程序涉及人員喜歡在應用程序中實現，而不是在資料庫上實現。
Oracle SQL語句執行順序
(8)SELECT (9) DISTINCT (11) <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
1）FROM：對FROM子句中的表執行笛卡爾積(交叉聯接)，生成虛擬表VT1。
2）ON：對VT1應用ON篩選器，只有那些使為真才被插入到TV2。
3）OUTER (JOIN):如果指定了OUTER JOIN(相對於CROSS JOIN或INNER JOIN)，保留表中未找到匹配的行將作為外部行添加到VT2，生成TV3。如果FROM子句包含兩個以上的表，則對上一個聯接生成的結果表和下一個表重復執行步驟1到步驟3，直到處理完所有的表位置。
4）WHERE：對TV3應用WHERE篩選器，只有使為true的行才插入TV4。
5）GROUP BY：按GROUP BY子句中的列列表對TV4中的行進行分組，生成TV5。
6）CUTE|ROLLUP：把超組插入VT5，生成VT6。
7）HAVING：對VT6應用HAVING篩選器，只有使為true的組插入到VT7。
8）SELECT：處理SELECT列表，產生VT8。
9）DISTINCT：將重復的行從VT8中刪除，產品VT9。
10）ORDER BY：將VT9中的行按ORDER BY子句中的列列表順序，生成一個游標(VC10)，生成表TV11，並返回給調用者。
以上每個步驟都會產生一個虛擬表，該虛擬表被用作下一個步驟的輸入。這些虛擬表對調用者(客戶端應用程序或者外部查詢)不可用。只有最後一步生成的表才會會給調用者。如果沒有在查詢中指定某一個子句，將跳過相應的步驟。

⑧ 資料庫常用sql語句有哪些

資料庫常用sql語句

Student(S#,Sname,Sage,Ssex) 學生表

Course(C#,Cname,T#) 課程表

SC(S#,C#,score) 成績表

Teacher(T#,Tname) 教師表

問題：

1、查詢「001」課程比「002」課程成績高的所有學生的學號；

select a.S# from (select s#,score from SC where C#='001') a,(select s#,score

from SC where C#='002') b

where a.score>b.score and a.s#=b.s#;

2、查詢平均成績大於60分的同學的學號和平均成績；

select S#,avg(score)

from sc

group by S# having avg(score) >60;

3、查詢所有同學的學號、姓名、選課數、總成績；

select Student.S#,Student.Sname,count(SC.C#),sum(score)

from Student left Outer join SC on Student.S#=SC.S#

group by Student.S#,Sname

4、查詢姓「李」的老師的個數；

select count(distinct(Tname))

from Teacher

where Tname like '李%';

5、查詢沒學過「葉平」老師課的同學的學號、姓名；

select Student.S#,Student.Sname

from Student

where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平');

6、查詢學過「001」並且也學過編號「002」課程的同學的學號、姓名；

select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');

7、查詢學過「葉平」老師所教的所有課的同學的學號、姓名；

select S#,Sname

from Student

where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='葉平'));

8、查詢課程編號「002」的成績比課程編號「001」課程低的所有同學的學號、姓名；

Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2

from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2<score;

9、查詢所有課程成績小於60分的同學的學號、姓名；

select S#,Sname

from Student

where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

10、查詢沒有學全所有課的同學的學號、姓名；

select Student.S#,Student.Sname

from Student,SC

where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

11、查詢至少有一門課與學號為「1001」的同學所學相同的同學的學號和姓名；

select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';

12、查詢至少學過學號為「001」同學所有一門課的其他同學學號和姓名；

select distinct SC.S#,Sname

from Student,SC

where Student.S#=SC.S# and C# in (select C# from SC where S#='001');

13、把「SC」表中「葉平」老師教的課的成績都更改為此課程的平均成績；

SC set score=(select avg(SC_2.score)

from SC SC_2

where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='葉平');

14、查詢和「1002」號的同學學習的課程完全相同的其他同學學號和姓名；

select S# from SC where C# in (select C# from SC where S#='1002')

group by S# having count(*)=(select count(*) from SC where S#='1002');

15、刪除學習「葉平」老師課的SC表記錄；

Delect SC

from course ,Teacher

where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平';

16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號「003」課程的同學學號、2、

號課的平均成績；

Insert SC select S#,'002',(Select avg(score)

from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');

17、按平均成績從高到低顯示所有學生的「資料庫」、「企業管理」、「英語」三門的課程成績，按如下形式顯示：學生ID,,資料庫,企業管理,英語,有效課程數,有效平均分

SELECT S# as 學生ID

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 資料庫

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企業管理

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英語

,COUNT(*) AS 有效課程數, AVG(t.score) AS 平均成績

FROM SC AS t

GROUP BY S#

ORDER BY avg(t.score)

18、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分

SELECT L.C# As 課程ID,L.score AS 最高分,R.score AS 最低分

FROM SC L ,SC AS R

WHERE L.C# = R.C# and

L.score = (SELECT MAX(IL.score)

FROM SC AS IL,Student AS IM

WHERE L.C# = IL.C# and IM.S#=IL.S#

GROUP BY IL.C#)

AND

R.Score = (SELECT MIN(IR.score)

FROM SC AS IR

WHERE R.C# = IR.C#

GROUP BY IR.C#

);

19、按各科平均成績從低到高和及格率的百分數從高到低順序

SELECT t.C# AS 課程號,max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績

,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分數

FROM SC T,Course

where t.C#=course.C#

GROUP BY t.C#

ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

20、查詢如下課程平均成績和及格率的百分數(用"1行"顯示): 企業管理（001），馬克思（002），OO&UML （003），資料庫（004）

SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企業管理平均分

,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企業管理及格百分數

,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 馬克思平均分

,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 馬克思及格百分數

,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分

,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分數

,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 資料庫平均分

,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 資料庫及格百分數

FROM SC

21、查詢不同老師所教不同課程平均分從高到低顯示

SELECT max(Z.T#) AS 教師ID,MAX(Z.Tname) AS 教師姓名,C.C# AS 課程ID,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績

FROM SC AS T,Course AS C ,Teacher AS Z

where T.C#=C.C# and C.T#=Z.T#

GROUP BY C.C#

ORDER BY AVG(Score) DESC

22、查詢如下課程成績第 3 名到第 6 名的學生成績單：企業管理（001），馬克思（002），UML （003），資料庫（004）

[學生ID],[學生姓名],企業管理,馬克思,UML,資料庫,平均成績

SELECT DISTINCT top 3

SC.S# As 學生學號,

Student.Sname AS 學生姓名 ,

T1.score AS 企業管理,

T2.score AS 馬克思,

T3.score AS UML,

T4.score AS 資料庫,

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 總分

FROM Student,SC LEFT JOIN SC AS T1

ON SC.S# = T1.S# AND T1.C# = '001'

LEFT JOIN SC AS T2

ON SC.S# = T2.S# AND T2.C# = '002'

LEFT JOIN SC AS T3

ON SC.S# = T3.S# AND T3.C# = '003'

LEFT JOIN SC AS T4

ON SC.S# = T4.S# AND T4.C# = '004'

WHERE student.S#=SC.S# and

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)

NOT IN

(SELECT

DISTINCT

TOP 15 WITH TIES

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)

FROM sc

LEFT JOIN sc AS T1

ON sc.S# = T1.S# AND T1.C# = 'k1'

LEFT JOIN sc AS T2

ON sc.S# = T2.S# AND T2.C# = 'k2'

LEFT JOIN sc AS T3

ON sc.S# = T3.S# AND T3.C# = 'k3'

LEFT JOIN sc AS T4

ON sc.S# = T4.S# AND T4.C# = 'k4'

ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);

23、統計列印各科成績,各分數段人數:課程ID,課程名稱,[100-85],[85-70],[70-60],[<60]

SELECT SC.C# as 課程ID, Cname as 課程名稱

,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]

,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]

,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]

,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]

FROM SC,Course

where SC.C#=Course.C#

GROUP BY SC.C#,Cname;

24、查詢學生平均成績及其名次

SELECT 1+(SELECT COUNT( distinct 平均成績)

FROM (SELECT S#,AVG(score) AS 平均成績

FROM SC

GROUP BY S#

) AS T1

WHERE 平均成績 > T2.平均成績) as 名次,

S# as 學生學號,平均成績

FROM (SELECT S#,AVG(score) 平均成績

FROM SC

GROUP BY S#

) AS T2

ORDER BY 平均成績 desc;

25、查詢各科成績前三名的記錄:(不考慮成績並列情況)

SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數

FROM SC t1

WHERE score IN (SELECT TOP 3 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC

)

ORDER BY t1.C#;

26、查詢每門課程被選修的學生數

select c#,count(S#) from sc group by C#;

27、查詢出只選修了一門課程的全部學生的學號和姓名

select SC.S#,Student.Sname,count(C#) AS 選課數

from SC ,Student

where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;

28、查詢男生、女生人數

Select count(Ssex) as 男生人數 from Student group by Ssex having Ssex='男';

Select count(Ssex) as 女生人數 from Student group by Ssex having Ssex='女'；

29、查詢姓「張」的學生名單

SELECT Sname FROM Student WHERE Sname like '張%';

30、查詢同名同性學生名單，並統計同名人數

select Sname,count(*) from Student group by Sname having count(*)>1;;

31、1981年出生的學生名單(註：Student表中Sage列的類型是datetime)

select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age

from student

where CONVERT(11),DATEPART(year,Sage))='1981';

32、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列

Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;

33、查詢平均成績大於85的所有學生的學號、姓名和平均成績

select Sname,SC.S# ,avg(score)

from Student,SC

where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;

34、查詢課程名稱為「資料庫」，且分數低於60的學生姓名和分數

Select Sname,isnull(score,0)

from Student,SC,Course

where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='資料庫'and score<60;

35、查詢所有學生的選課情況；

SELECT SC.S#,SC.C#,Sname,Cname

FROM SC,Student,Course

where SC.S#=Student.S# and SC.C#=Course.C# ;

36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數；

SELECT distinct student.S#,student.Sname,SC.C#,SC.score

FROM student,Sc

WHERE SC.score>=70 AND SC.S#=student.S#;

37、查詢不及格的課程，並按課程號從大到小排列

select c# from sc where scor e<60 order by C# ;

38、查詢課程編號為003且課程成績在80分以上的學生的學號和姓名；

select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';

39、求選了課程的學生人數

select count(*) from sc;

40、查詢選修「葉平」老師所授課程的學生中，成績最高的學生姓名及其成績

select Student.Sname,score

from Student,SC,Course C,Teacher

where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='葉平' and SC.score=(select max(score)from SC where C#=C.C# );

41、查詢各個課程及相應的選修人數

select count(*) from sc group by C#;

42、查詢不同課程成績相同的學生的學號、課程號、學生成績

select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;

43、查詢每門功成績最好的前兩名

SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數

FROM SC t1

WHERE score IN (SELECT TOP 2 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC

)

ORDER BY t1.C#;

44、統計每門課程的學生選修人數（超過10人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，查詢結果按人數降序排列，若人數相同，按課程號升序排列

select C# as 課程號,count(*) as 人數

from sc

group by C#

order by count(*) desc,c#

45、檢索至少選修兩門課程的學生學號

select S#

from sc

group by s#

having count(*) > = 2

46、查詢全部學生都選修的課程的'課程號和課程名

select C#,Cname

from Course

where C# in (select c# from sc group by c#)

47、查詢沒學過「葉平」老師講授的任一門課程的學生姓名

select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='葉平');

48、查詢兩門以上不及格課程的同學的學號及其平均成績

select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score<60 group="" by="" having="">2)group by S#;

49、檢索「004」課程分數小於60，按分數降序排列的同學學號

select S# from SC where C#='004'and score<60 order by score desc;

50、刪除「002」同學的「001」課程的成績

from Sc where S#='001'and C#='001';

一、SQL語言簡介

1、SQL語言簡介

SQL是結構化查詢語言(Structured Query Language)，是用於訪問和處理資料庫的標準的計算機語言。

SQL語言的功能如下：

A、SQL面向資料庫執行查詢

B、SQL可從資料庫取回數據

C、SQL可在資料庫中插入新的記錄

D、SQL可更新資料庫中的數據

E、SQL可從資料庫刪除記錄

F、SQL可創建新資料庫

G、SQL可在資料庫中創建新表

H、SQL可在資料庫中創建存儲過程

I、SQL可在資料庫中創建視圖

J、SQL可以設置表、存儲過程和視圖的許可權

SQL是一門ANSI的標准計算機語言，用來訪問和操作資料庫系統。SQL語句用於取回和更新資料庫中的數據。SQL可與資料庫程序協同工作，比如MS Access、DB2、Informix、MS SQL Server、Oracle、MySQL、Sybase以及其他資料庫系統。

每一種資料庫有自己版本的SQL語言，但是為了與ANSI標准相兼容，SQL必須以相似的方式共同地來支持一些主要的關鍵詞（比如 SELECT、UPDATE、DELETE、INSERT、WHERE等等）。

除了SQL標准之外，大部分SQL資料庫程序都擁有自己的私有擴展。

2、SQL語言分類

SQL語言分為數據定義語言、數據控制語言、數據操作語言、數據查詢語言，分別實現對資料庫數據操作。

二、SQL語言基礎

1、數據定義語言（DDL）

DDL：Data Definition Language

用於定義和管理數據對象，包括資料庫、數據表、函數、視圖、索引、觸發器等。例如：CREATE、DROP、ALTER等語句。

create table student

(

sid INT,

sname var20)

);

alter table student add age int default 20;

student;

drop database student;

2、數據控制語言（DCL）

DCL：Data Control Language

用來管理資料庫的語言，包含授權用戶訪問、拒絕用戶訪問、撤銷授予的許可權。例如：GRANT、DENY、REVOKE、COMMIT、ROLLBACK等語句。

創建用戶

create user 『wang』@『localhost』 identified by 『a1!』;

許可權設置

grant select on db.student to 『wang』@『localhost』;

撤銷許可權

revoke select on db.student from 『wang』@『localhost』;

3、數據操作語言（DML）

DML：Data Manipulation Language

用於操作資料庫對象中所包含的數據，增、刪、改。例如：INSERT、DELETE、UPDATE語句。

4、數據查詢語言（DQL）

DQL：Data Query Language

用於查詢資料庫對象中所包含的數據，能夠進行單表查詢、連接查詢、嵌套查詢，以及集合查詢等各種復雜程度不同的資料庫查詢，並將數據返回到客戶機中顯示。例如：SELECT語句。

三、常量和變數

1、常量

A、字元常量

字元串常量使用單引號或雙引號，數值常量不用加引號。

如果字元串常量中需要換行、有單引號、雙引號 % _

前面需要加轉義字元

換行

』一個單引號

」一個雙引號

一個如果沒有轉義字元就認為是一個轉義字元

% 一個% 如果沒有轉義字元就認為這是一個通配符

_ 一個_ 如果沒有轉移字元就認為是一個通配符

select 『hanli』gang001』

select 『han ligang001』

select 「han"ligang001」

select 「韓立剛001」

B、數值常量

數值常量不用添加引號，

select 100+100+200

C、布爾常量

布爾型常量取值 true 和 false

在SQL中使用1和0表示

select true,false

在表達式中

select 100>200

select 100<200

2、變數

用戶自定義變數使用@開始，使用set給變數賦值。

set @name=『孫悟空』;

select @name;

select * from student;

into student values (6,『孫悟空』,20)；

into student values (8,@name,20)；

set @sid=9,@nid=10

into student values (@sid,@name,20)；

select @sid+@nid；

set @sid=@sid+1；

select @sid；

set @sname3=(select sname from student where sid=9)；

select @sname3；

3、系統變數

系統變數分為全局系統變數和會話系統變數。

全局系統變數：針對所有默認設置

會話系統變數：針對當前用戶生效，用戶登錄MySQL會使用全局系統變數，如果會話中更改了變數值，使用更改後的值，不過只針對當前用戶生效。

show variables 顯示會話系統變數

show global variables 顯示全局系統變數

show session variables 顯示會話系統變數

show global variables like 『sql_select_limit』；使用通配符顯示匹配的變數設置

show session variables like 『sql_select_limit』；系統變數使用@@標識

select @@global.sql_select_limit 查看某個全局系統變數設置

select @@session.sql_select_limit 查看某個會話系統變數設置

set @@session.sql_select_limit=2 設置會話系統變數

全局系統變數需要在/etc/my.cnf配置文件中修改。

⑨ 資料庫常用sql語句有哪些

資料庫常用sql語句有哪些

SQL語句有哪些？SQL語句無論是種類還是數量都是繁多的，很多語句也是經常要用到的，下文我為大家分享的就是SQL的常用語句，僅供參考！

50個常用的sql語句

Student(S#,Sname,Sage,Ssex) 學生表

Course(C#,Cname,T#) 課程表

SC(S#,C#,score) 成績表

Teacher(T#,Tname) 教師表

問題：

1、查詢“001”課程比“002”課程成績高的所有學生的學號；

select a.S# from (select s#,score from SC where C#='001') a,(select s#,score

from SC where C#='002') b

where a.score>b.score and a.s#=b.s#;

2、查詢平均成績大於60分的同學的學號和平均成績；

select S#,avg(score)

from sc

group by S# having avg(score) >60;

3、查詢所有同學的學號、姓名、選課數、總成績；

select Student.S#,Student.Sname,count(SC.C#),sum(score)

from Student left Outer join SC on Student.S#=SC.S#

group by Student.S#,Sname

4、查詢姓“李”的老師的個數；

select count(distinct(Tname))

from Teacher

where Tname like '李%';

5、查詢沒學過“葉平”老師課的同學的學號、姓名；

select Student.S#,Student.Sname

from Student

where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平');

6、查詢學過“001”並且也學過編號“002”課程的同學的學號、姓名；

select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');

7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名；

select S#,Sname

from Student

8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名；

Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2

from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;

9、查詢所有課程成績小於60分的同學的學號、姓名；

select S#,Sname

from Student

where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

10、查詢沒有學全所有課的同學的學號、姓名；

select Student.S#,Student.Sname

from Student,SC

where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

11、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名；

select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';

12、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名；

select distinct SC.S#,Sname

from Student,SC

where Student.S#=SC.S# and C# in (select C# from SC where S#='001');

13、把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績；

update SC set score=(select avg(SC_2.score)

from SC SC_2

where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='葉平');

14、查詢和“1002”號的同學學習的課程完全相同的其他同學學號和姓名；

select S# from SC where C# in (select C# from SC where S#='1002')

group by S# having count(*)=(select count(*) from SC where S#='1002');

15、刪除學習“葉平”老師課的SC表記錄；

Delect SC

from course ,Teacher

where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平';

16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“003”課程的同學學號、2、

號課的平均成績；

Insert SC select S#,'002',(Select avg(score)

from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');

17、按平均成績從高到低顯示所有學生的“資料庫”、“企業管理”、“英語”三門的課程成績，按如下形式顯示：學生ID,,資料庫,企業管理,英語,有效課程數,有效平均分