常用sql語法及實例講解

1、常用SQL語法及實例講解,主講人：楊桂紅日期：2011-06-02,培訓內(nèi)容說明,本次培訓主要針對DML（即數(shù)據(jù)操縱語言），不涉及或者極少涉及DDL（數(shù)據(jù)定義語言）、DCL（數(shù)據(jù)控制語言）；本培訓PPT的例子大多是ANSI SQL，少部分是sql server的特有的語法（非ANSI SQL的有做說明，但可能說明不全）。本培訓PPT的sql語句的測試環(huán)境是sql server 2008；,培訓內(nèi)容概述,數(shù)據(jù)操縱語言--插入(i

2、nsert),INSERT語句用于向數(shù)據(jù)庫表加入一行或多行數(shù)據(jù)。通常有兩種形式：1.插入一條記錄；2.插入子查詢結(jié)果。插入一條記錄的INSERT語句的語法形式如下： INSERT INTO table_name [column_name1， column_name2，…] VALUES(column_value1, column_value2,…); INSERT INTO 插入語句關(guān)鍵字。Table_name 要

3、插入數(shù)據(jù)的表名。column_name 插入數(shù)據(jù)對應的列名，可省略。column_value 要插入的新元組的的值，與column_name對應,也就是說每一個字段必須對應一個字段值。例子演示:insert into product(productName,ProductCode) values('sql培訓測試產(chǎn)品',1234);,數(shù)據(jù)操縱語言--插入(insert),插入子查詢結(jié)果的INSERT語句的語法形

4、式如下：INSERT INTO table [column_name1， column_name2，…] 子查詢column_name 插入數(shù)據(jù)對應的列名，可省略。子查詢 為SELECT語句，可批量插入數(shù)據(jù)。例子演示:insert into customer2(customername) select customername from customer注意事項：輸入項的順序和數(shù)據(jù)類型必須與表中列的順序和數(shù)據(jù)類型相對

5、應。要保證表定義時的非空列（ NOT NULL）必須有值。字符型和日期型值插入時要加入單引號。標識字段在以下情況不能插入顯式值:Cannot insert explicit value for identity column in table 'Customer2' when IDENTITY_INSERT is set to OFF.,數(shù)據(jù)操縱語言--修改(update),UPDATE語句用于修改數(shù)據(jù)庫表中特定記

6、錄的特定字段的數(shù)據(jù)。update語法形式如下： UPDATE table_name SET column_name=new_value[, column_name2=new_value2,…] [WHERE 子句] ;column_name=new_value 表示滿足條件的記錄的column_name列的相應的值修改成new_value.WHERE條件子句可省略。如果省略,則修改表中所有的記錄，否則修改滿足WHERE條件的

7、記錄。例子演示:update [Order] set OrderType=0;update [Order] set OrderType=1,orderdate=getdate() where RowID=102;update [Order] set OrderType=1 where RowID%2=1;update [Order] set OrderType=10 where upper(ordercode)='O

8、RD_3';,數(shù)據(jù)操縱語言--刪除(delete),DELETE語句用于從表中刪除一行或多行數(shù)據(jù)。delete語法形式如下： DELETE FROM table_name [WHERE 子句 ] table_name 要刪除數(shù)據(jù)記錄的表的名稱。WHERE 子句 跟要刪除記錄的條件；如果省略，則刪除表中所有的記錄。只能能刪除記錄，不能刪除表結(jié)構(gòu)；drop 刪除所有表記錄與整個表結(jié)構(gòu)。例子演示:Delete

9、 from [Order] where OrderType=0;Delete from [Order] where OrderType=1 and RowID=102;Delete from [Order] where OrderType=1 and RowID%2=1;Delete from [Order] where OrderType=10 and upper(ordercode)='ORD_3';,數(shù)據(jù)操

10、縱語言--查詢(select),使用select可以實現(xiàn)對數(shù)據(jù)庫數(shù)據(jù)的查詢操作,并可以對查詢進行分析、統(tǒng)計、排序等處理操作。 查詢語句的語法如下：SELECT [DISTINCT|ALL] [目標列表達式]FROM table_name1 [,table_name2,…][WHERE 條件表達式][GROUP BY column_name1 [HAVING 條件表達式] ][ORDER BY column_name

11、s[ASC|DESC]][LIMIT NUMBER1,NUMBER2];,數(shù)據(jù)操縱語言--查詢(select),SELECT子句用于指定要查詢的特定表中的列，它可以是星號 *、表達式、列表、變量等，各列名用逗號分隔。FROM子句用于指定要查詢的表或者視圖，當有多個表時，相互間用逗號相互隔開。 WHERE子句用來限定查詢的范圍和條件，緊跟在FROM子句之后，如果沒有WHERE 子句，將把表中所有的記錄作為查詢對象。GROUP

12、 BY 子句是分組查詢子句。HAVING子句用于指定分組子句的條件。GROUP BY子句、HAVING子句和集合函數(shù)一起可以實現(xiàn)對每個組生成一行和一個匯總值。ORDER BY子句可以根據(jù)一個列或者多個列來對查詢的結(jié)果進行排序。LIMIT 限制顯示的記錄數(shù)(各種數(shù)據(jù)庫管理系統(tǒng)，語法有差異)， 從number1記錄開始，顯示number2條記錄。,數(shù)據(jù)操縱語言--單表查詢,單表查詢 單表查詢是指只涉及一個表的查詢。1.查詢指定列

13、: select o.rowid,o.ordertype from [order] o;2.查詢?nèi)康牧?select * from [order];3. 查詢非重復的記錄:select distinct o.rowid,o.ordertype from [order] o;4.用where字句篩選查詢記錄: select * from [order] where ordertype=1;5. 使用分組、集合函數(shù)：select

14、t.ordertype,COUNT(OrderType) from [Order] t group by OrderType having COUNT(OrderType) >1下表是一些常用的查詢條件 (注意:查詢條件當中,null前邊不能跟=,!=):,數(shù)據(jù)操縱語言--Where 字句,用where字句建立篩選標準，即是θ連接，是一種舊的連接。Where字句可以用在select、update、delete、insert

15、……select、create view等查詢或者子查詢當中。前面提到的一些常用的查詢條件，在where子句中都適用。例子演示：select * from [order] where ordertype=1select * from [order] where ordertype between 1 and 2select * from [order] where ordertype in (1,2,0);select *

16、from [order] where ordercode like '%ord%';select * from [order] where ordertype is not null;select * from [order] where ordertype=1 and ordercode like '%ord%';insert into customer2(customername) select

17、 customername from customer where rowid>1;……,數(shù)據(jù)操縱語言---連接查詢,若一個查詢，同時涉及兩個或者兩個以上表，稱之為連接查詢。連接查詢主要分為三類：1.笛卡爾積連接；2.內(nèi)連接；3.外連接；笛卡爾積連接 笛卡爾積連接不帶WHERE 子句，它返回被連接的兩個表所有數(shù)據(jù)行的笛卡爾積，返回到結(jié)果集合中的數(shù) 據(jù)行數(shù)等于第一個表中符合查詢條件的數(shù)據(jù)行數(shù)乘以第二個表中符合查

18、詢條件的數(shù)據(jù)行數(shù)。（這種連接不具實際意義，日常極少使用）內(nèi)連接 內(nèi)連接查詢操作列出與連接條件匹配的數(shù)據(jù)行。外連接 外連接，返回到查詢結(jié)果集合的不僅包含符合連接條件的行，而且還包括左表(left join)、右表(right join)，兩個表（full join）中的所有數(shù)據(jù)行。,數(shù)據(jù)操縱語言--連接查詢,笛卡爾積連接Select * from [order],orderdetail;內(nèi)連接Se

19、lect * from [order] o inner join orderdetail od on o.rowid=od.orderid;select re1.regioncode 上級區(qū)域編碼,re1.regionname 上級區(qū)域名稱,re2.regioncode 區(qū)域編碼,re2.regionname 區(qū)域名稱 from region re1 inner join region re2 on re1.rowid=re2.pa

20、rentregionid……,數(shù)據(jù)操縱語言--連接查詢,左連接：返回包括左表中的所有記錄和右表中連接字段相等的記錄,左邊表沒有匹 配時，右表為空。select cu.RowID,cu.CustomerName,cp.RowID,cp.CustomerID,cp.SKU_ID from Customer cu left join customerproduct cp on cu.rowid=cp.customerid右連接:返回包

21、括右表中的所有記錄和左表中連接字段相等的記錄，右邊表沒有匹配時，顯示為空。select cu.RowID,cu.CustomerName,cp.RowID,cp.CustomerID,cp.SKU_ID from customerproduct cp right join Customer cu on cu.rowid=cp.customerid全連接:返回包括左、右表中的所有記錄，左邊或者右邊表沒有匹配時，顯示為空.selec

22、t cu.RowID,cu.CustomerName,cp.RowID,cp.CustomerID,cp.SKU_ID from customerproduct cp full join Customer cu on cu.rowid=cp.customerid,常用函數(shù)--聚合函數(shù),Sum，求和：select sum(rowid) from customer where ……Count,求符合條件的記錄數(shù)：select count

23、(*) from customer where ……Avg,求平均數(shù)：select avg(rowid) from customer where……Min,求最小值：select min(rowid) from customer where……Max,求最大值：select max(rowid) from customer where……,常用函數(shù)--標量函數(shù)Case,Case 表達式的語法為：case when 條件表達式

24、then 操作 else 操作endupdate例子演示： 以下第1的sql語句跟第2的3個sql語句，執(zhí)行效果是一樣的： 1:update [Order] set Remark= case when OrderType=1 then '冷凍產(chǎn)品' when OrderType=0 then '常溫產(chǎn)品訂單'else '未定義產(chǎn)品訂單' end, orderdate=getdat

25、e();2: update [Order] set remark='冷凍產(chǎn)品訂單',orderdate=getdate() where OrderType=1; update [Order] set remark='常溫產(chǎn)品訂單',orderdate=getdate() where OrderType=0; update [Order] set remark='未

26、定義產(chǎn)品訂單',orderdate=getdate() where OrderType not in(1,0) or OrderType is null;,常用函數(shù)--標量函數(shù)Case,select例子演示： 以下第1的sql語句跟第2的3個sql語句，執(zhí)行效果是一樣的：1.Select cu.customername+case when o.ordertype=1 then '----冷凍產(chǎn)品訂單----數(shù)量為

27、' when o.ordertype=0 then '----常溫產(chǎn)品訂單----數(shù)量為'else '----未定義產(chǎn)品訂單----數(shù)量為' end +o.ordercount from [order] o,customer cu where o.customerid=cu.rowid 2.Select cu.customername+‘----冷凍產(chǎn)品訂單----數(shù)量為’+o.order

28、count from [order] o,customer cu where o.customerid=cu.rowid and o.ordertype=1；Select cu.customername+‘----冷凍產(chǎn)品訂單----數(shù)量為’+o.ordercount from [order] o,customer cu where o.customerid=cu.rowid and o.ordertype=0；Sel

29、ect cu.customername+'----冷凍產(chǎn)品訂單----數(shù)量為'+o.ordercount from [order] o,customer cu where o.customerid=cu.rowid and (o.ordertype not in(0,1) or o.OrderType is not null);,常用函數(shù)--標量函數(shù)Case,目前屈臣氏項目有個表單是需要顯示當天的拜訪門店及門店當前

30、的拜訪狀態(tài)的，顯示效果如下（列表控件當中顯示“XXX終端名----處理狀態(tài)”，處理狀態(tài)包括未處理，已處理，處理中）：數(shù)據(jù)庫中有一個專門的字段是表示處理狀態(tài)的，但是字段值不是“未處理，已處理，處理中”，而是分別用1,2,3表示；那么這個需求就可以用case函數(shù)來處理。,常用函數(shù)--標量函數(shù)Cast,Cast顯式的把表達式由一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型。例子演示：Select cast(o.remark as

31、 char(5)) from [order] o;-?把remark轉(zhuǎn)換成為char(5)；Select sum(CAST(t.Col1 as int)) from [Product] t；?把col1轉(zhuǎn)換為int再求和；Sql server還有一個convert函數(shù)也是類似用途。,常用函數(shù)--標量函數(shù)extract,從日期抽取部分的數(shù)據(jù)ANSI SQL函數(shù)是extract。標準語法為： extract（date_part

32、from expression）：例子如下：Select extract(year from '2007-10-10');---返回2007注意事項：sql server不支持Extract，如果使用Extract會提示其不是內(nèi)置函數(shù)，但是在sql server中，datepart（date_part，expression）函數(shù)即是相當于extract：例子如下：Select datepart(month,

33、'2007-01-01');,常用函數(shù)--標量函數(shù)lower，upper,Lower，將字符轉(zhuǎn)換為全部小寫：Select lower(ordercode) from [order];Upper，將字符轉(zhuǎn)換為全部大寫：Select upper(ordercode) from [order];有時候要輸出純大寫或者純小寫的字符串，那么這兩個函數(shù)就可以被用到；,常用函數(shù)--數(shù)字標量函數(shù),Abs：取絕對值；S

34、elect abs(rowid) from customer;取一個數(shù)字除以另一個數(shù)字的余數(shù)， ANSI SQL標準函數(shù)是MOD；如：Select mod(ordertype,2) from order;跟extract一樣，sql server也是不支持這個函數(shù)，sql sever同樣效果的用%。如： Select rowid%4from [product];Floor：無條件舍去小數(shù)部分；Select floor(

35、1.2);,常用函數(shù)--Top函數(shù),Top n，用于查詢最前的n條記錄，這個“最前”，是相對于查詢記錄來說的。比如：select top 10 * from customer,返回的是select * from customer的最前面10行；如果rowid越大表示越新，那么要得到最新的n條記錄，可以先對查詢結(jié)果根據(jù)rowid進行排序，然后在再top n，如下：Select top 10 *from customer ord

36、er by rowid desc;目前屈臣氏項目中，有個表單需求是顯示最新的4張訂單記錄，就是用到這個top函數(shù)。 需要注意的是：top不是ANSI sql，它是sql server的內(nèi)置函數(shù)。不同的數(shù)據(jù)庫廠商，取最前n條記錄的方法不相同。,常用運算

37、符-- Exists運算符,Exists運算符檢查子查詢是否存在一個或者多個記錄；Select * from [order] o where exists(select * from orderdetail od where o.rowid=od.orderid),常用運算符-- Union 集合運算符,Union 集合運算符結(jié)合兩個或者以上的查詢結(jié)果集，以單一結(jié)果集顯示各個查詢所返回的全部數(shù)據(jù)行。語法如下：Select

38、語句1 union select 語句2 union select語句3……例子演示Select cu.customername+'----冷凍產(chǎn)品訂單----數(shù)量為'+o.ordercount from [order] o,customer cu where o.customerid=cu.rowid and o.ordertype=1UnionSelect cu.customername+'

39、----冷凍產(chǎn)品訂單----數(shù)量為'+o.ordercount from [order] o,customer cu where o.customerid=cu.rowid and o.ordertype=0注意事項：所有查詢的字段順序、數(shù)目都必須相同，數(shù)據(jù)類型不一定要相同，但是必須兼容。,其它—null值用法,Null，在數(shù)據(jù)庫記錄中，是表示一個未知的值；比如一個商品的價格字段為null，意思不是價格為0，而是價格未定

40、或者未知。例子演示：Select * from [order] o where o.ordertype is null;Select count(ordertype) from [order];--ordertype為null的記錄不會被統(tǒng)計；Select * from [order] o where o.ordertype not in(0,1);--- ordertype為null的記錄不會被查詢到；Select * f

41、rom [order] o where o.ordertype not between 0 and 1;--- ordertype為null的記錄不會被查詢到；Select sum(BoxInCount) from [product];----ordertype如果包含null值，不會被統(tǒng)計（而不是+null）Product表的Price3字段是準備上調(diào)價（但有些商品未調(diào)價，保持為null），price1字段記錄的現(xiàn)在價格，如果現(xiàn)在

42、要批量更新商品的價格price1，那么這樣寫：update product set price1=price1+price3;會導致有些價格為null。,事務(wù)—概念,事務(wù)(Transaction)是用戶定義的一個數(shù)據(jù)庫操作序列，這些操作要么全做要么全部不做，是一個不可分割的工作單位。 事務(wù)的四個特性（ ACID ）如下：原子性 (Atomicity)：事務(wù)中包含的程序作為數(shù)據(jù)庫的邏輯工作單位，它所做的對數(shù)據(jù)修改操作要么全部執(zhí)行，要么

43、完全不執(zhí)行；一致性(Consistency)：事務(wù)執(zhí)行之前和執(zhí)行之后數(shù)據(jù)庫都必須處于一致性狀態(tài)，即之前和之后都符合所有完整性約束；隔離性(Isolation)：一個事務(wù)內(nèi)部的操作及正在操作的數(shù)據(jù)必須封鎖起來，不被其它企圖進行修改的事務(wù)看到持續(xù)性(Durability) ：一旦一個事務(wù)提交，DBMS保證它對數(shù)據(jù)庫中數(shù)據(jù)的改變應該是永久性的，耐得住任何系統(tǒng)故障,事務(wù)—語法,一個事務(wù)會包含以下的關(guān)鍵字：START TRANSAC

44、TION 跟蹤接下來的SQL語句，直到遇見commit或rollback.COMMIT 提交結(jié)果，將保存到數(shù)據(jù)庫中。ROLLBACK 遇到問題，回滾到起點，數(shù)據(jù)庫的數(shù)據(jù)沒有改動。從START TRANSACTION開始數(shù)據(jù)庫沒有任何改變，直到執(zhí)行COMMIT。,事務(wù)—舉例,例子演示：BEGIN TRANinsert into Customer(CustomerName) values('測試事務(wù)&

45、#39;) --該語句能正確執(zhí)行insert into customer(RowID,CustomerName) values(12,'23') --該語句執(zhí)行會報錯IF @@ERROR 0BEGINROLLBACK TRANENDELSEBEGINCOMMIT TRANEND說明： TRAN是TRANSACTION的縮寫；rollback和commit后邊的TRAN可省略；,參考資料

46、及練習環(huán)境,查詢前幾條記錄SQL在不同數(shù)據(jù)庫中的用法：http://zmx.iteye.com/blog/426829Case –when-then-end用法：http://www.docin.com/p-114911991.htmlSQL技術(shù)手冊(第3版)，電子工業(yè)出版社; 第3版W3cschool SQL教程：http://www.w3cschool.cn/index-33.html練習環(huán)境：192.168.0