1、創建表和數據插入SQL
我們在開始創建數據表和向表中插入演示數據之前，我想給大家解釋一下實時數據表的設計理念，這樣也許能幫助大家能更好的理解sql查詢。
在數據庫設計中，有一條非常重要的規則就是要正確建立主鍵和外鍵的關系。
現在我們來創建幾個餐廳訂單管理的數據表，一共用到3張數據表，Item Master表、Order Master表和Order Detail表。
創建表：
創建Item Master表：
CREATE?TABLE?[dbo].[ItemMasters]( [Item_Code]?[varchar](20)?NOT?NULL, [Item_Name]?[varchar](100)?NOT?NULL, [Price]??Int?NOT?NULL, [TAX1]??Int?NOT?NULL, [Discount]??Int?NOT?NULL, [Description]?[varchar](200)?NOT?NULL, [IN_DATE]?[datetime]?NOT?NULL, [IN_USR_ID]?[varchar](20)?NOT?NULL, [UP_DATE]?[datetime]?NOT?NULL, [UP_USR_ID]?[varchar](20)?NOT?NULL, ?CONSTRAINT?[PK_ItemMasters]?PRIMARY?KEY?CLUSTERED? ( [Item_Code]?ASC )WITH?(PAD_INDEX??=?OFF,?STATISTICS_NORECOMPUTE??=?OFF,?IGNORE_DUP_KEY?=?OFF,?ALLOW_ROW_LOCKS??=?ON,?ALLOW_PAGE_LOCKS??=?ON)?ON?[PRIMARY] )?ON?[PRIMARY]
向Item Master表插入數據：
INSERT?INTO?[ItemMasters]???([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]??????????? ?,[IN_USR_ID],[UP_DATE],[UP_USR_ID])?????? ?VALUES??????????? ??('Item001','Coke',55,1,0,'Coke?which?need?to?be?cold',GETDATE(),'SHANU'???????????? ??,GETDATE(),'SHANU')? ??INSERT?INTO?[ItemMasters]???([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]??????????? ???,[IN_USR_ID],[UP_DATE],[UP_USR_ID])?????? ???VALUES???????????? ???('Item002','Coffee',40,0,2,'Coffe?Might?be?Hot?or?Cold?user?choice',GETDATE(),'SHANU'??????????? ????,GETDATE(),'SHANU')? ????INSERT?INTO?[ItemMasters]???([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]?????????? ?????,[IN_USR_ID],[UP_DATE],[UP_USR_ID])?????? ?????VALUES??????????? ??????('Item003','Chiken?Burger',125,2,5,'Spicy',GETDATE(),'SHANU'???????????? ??????,GETDATE(),'SHANU')? ??????INSERT?INTO?[ItemMasters]???([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]??????????? ???????,[IN_USR_ID],[UP_DATE],[UP_USR_ID])?????? ???????VALUES???????????? ???????('Item004','Potato?Fry',15,0,0,'No?Comments',GETDATE(),'SHANU'??????????? ????????,GETDATE(),'SHANU')
創建Order Master表：
CREATE?TABLE?[dbo].[OrderMasters]( [Order_No]?[varchar](20)?NOT?NULL, [Table_ID]?[varchar](20)?NOT?NULL, [Description]?[varchar](200)?NOT?NULL, [IN_DATE]?[datetime]?NOT?NULL, [IN_USR_ID]?[varchar](20)?NOT?NULL, [UP_DATE]?[datetime]?NOT?NULL, [UP_USR_ID]?[varchar](20)?NOT?NULL, ?CONSTRAINT?[PK_OrderMasters]?PRIMARY?KEY?CLUSTERED? ( [Order_No]?ASC )WITH?(PAD_INDEX??=?OFF,?STATISTICS_NORECOMPUTE??=?OFF,?IGNORE_DUP_KEY?=?OFF,?ALLOW_ROW_LOCKS??=?ON,?ALLOW_PAGE_LOCKS??=?ON)?ON?[PRIMARY] )?ON?[PRIMARY]
向Order Master表插入數據：
INSERT?INTO?[OrderMasters] ???????????([Order_No],[Table_ID]?,[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID]) ?????VALUES ???????????('Ord_001','T1','',GETDATE(),'SHANU'?,GETDATE(),'SHANU') INSERT?INTO?[OrderMasters] ???????????([Order_No],[Table_ID]?,[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID]) ?????VALUES ???????????('Ord_002','T2','',GETDATE(),'Mak'?,GETDATE(),'MAK') INSERT?INTO?[OrderMasters] ???????????([Order_No],[Table_ID]?,[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID]) ?????VALUES ???????????('Ord_003','T3','',GETDATE(),'RAJ'?,GETDATE(),'RAJ')
創建Order Detail表：
CREATE?TABLE?[dbo].[OrderDetails]( [Order_Detail_No]?[varchar](20)?NOT?NULL, [Order_No]?[varchar](20)?CONSTRAINT??fk_OrderMasters?FOREIGN?KEY?REFERENCES?OrderMasters(Order_No), [Item_Code]?[varchar](20)?CONSTRAINT??fk_ItemMasters?FOREIGN?KEY?REFERENCES?ItemMasters(Item_Code), [Notes]?[varchar](200)?NOT?NULL, [QTY]??INT?NOT?NULL, [IN_DATE]?[datetime]?NOT?NULL, [IN_USR_ID]?[varchar](20)?NOT?NULL, [UP_DATE]?[datetime]?NOT?NULL, [UP_USR_ID]?[varchar](20)?NOT?NULL, ?CONSTRAINT?[PK_OrderDetails]?PRIMARY?KEY?CLUSTERED? ( [Order_Detail_No]?ASC )WITH?(PAD_INDEX??=?OFF,?STATISTICS_NORECOMPUTE??=?OFF,?IGNORE_DUP_KEY?=?OFF,?ALLOW_ROW_LOCKS??=?ON,?ALLOW_PAGE_LOCKS??=?ON)?ON?[PRIMARY] )?ON?[PRIMARY] --Now?let’s?insert?the?3?items?for?the?above?Order?No?'Ord_001'. INSERT?INTO?[OrderDetails] ???????????([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY] ???????????,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID]) ?????VALUES ???????????('OR_Dt_001','Ord_001','Item001','Need?very?Cold',3 ???????????,GETDATE(),'SHANU'?,GETDATE(),'SHANU') INSERT?INTO?[OrderDetails] ???????????([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY] ???????????,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID]) ?????VALUES ???????????('OR_Dt_002','Ord_001','Item004','very?Hot?',2 ???????????,GETDATE(),'SHANU'?,GETDATE(),'SHANU') INSERT?INTO?[OrderDetails] ???????????([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY] ???????????,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID]) ?????VALUES ???????????('OR_Dt_003','Ord_001','Item003','Very?Spicy',4 ???????????,GETDATE(),'SHANU'?,GETDATE(),'SHANU')
向Order Detail表插入數據：
INSERT?INTO?[OrderDetails] ???????????([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY] ???????????,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID]) ?????VALUES ???????????('OR_Dt_004','Ord_002','Item002','Need?very?Hot',2 ???????????,GETDATE(),'SHANU'?,GETDATE(),'SHANU') INSERT?INTO?[OrderDetails] ???????????([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY] ???????????,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID]) ?????VALUES ???????????('OR_Dt_005','Ord_002','Item003','very?Hot?',2 ???????????,GETDATE(),'SHANU'?,GETDATE(),'SHANU') INSERT?INTO?[OrderDetails] ???????????([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY] ???????????,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID]) ?????VALUES ???????????('OR_Dt_006','Ord_003','Item003','Very?Spicy',4 ???????????,GETDATE(),'SHANU'?,GETDATE(),'SHANU')
2、簡單的Select查詢語句
Select查詢語句是SQL中最基本也是最重要的DML語句之一。那么什么是DML？DML全稱Data Manipulation Language（數據操縱語言命令），它可以使用戶能夠查詢數據庫以及操作已有數據庫中的數據。
下面我們在SQL Server中用select語句來查詢我的姓名(Name)：
SELECT?'My?Name?Is?SYED?SHANU' --?With?Column?Name?using?'AS' SELECT?'My?Name?Is?SYED?SHANU'?as?'MY?NAME' --?With?more?then?the?one?Column? SELECT?'My?Name'?as?'Column1',?'Is'?as?'Column2',?'SYED?SHANU'?as?'Column3'
在數據表中使用select查詢：
--?To?Display?all?the?columns?from?the?table?we?use?*?operator?in?select?Statement. Select?*?from?ItemMasters --?If?we?need?to?select?only?few?fields?from?a?table?we?can?use?the?Column?Name?in?Select?Statement. Select????Item_Code ,Item_name?as?Item ,Price ,Description ,In_DATE FROM ItemMasters
3、合計和標量函數
合計函數和標量函數都是SQL Server的內置函數，我們可以在select查詢語句中使用它們，比如Count(), Max(), Sum(), Upper(), lower(), Round()等等。下面我們用SQL代碼來解釋這些函數的用法：
select?*?from?ItemMasters --?Aggregate --?COUNT()?->?returns?the?Total?no?of?records?from?table?,?AVG()?returns?the?Average?Value?from?Colum,MAX()?Returns?MaX?Value?from?Column --?,MIN()?returns?Min?Value?from?Column,SUM()??sum?of?total?from?Column Select?Count(*)??TotalRows,AVG(Price)?AVGPrice ,MAX(Price)?MAXPrice,MIN(Price)?MinPrice,Sum(price)?PriceTotal? FROM?ItemMasters --?Scalar? --?UCASE()?->?Convert?to??Upper?Case??,LCASE()?->?Convert?to?Lower?Case, --?SUBSTRING()?->Display?selected?char?from?column?->SUBSTRING(ColumnName,StartIndex,LenthofChartoDisplay) --,LEN()?->?lenth?of?column?date, --?ROUND()??->?Which?will?round?the?value SELECT??UPPER(Item_NAME)?Uppers,LOWER(Item_NAME)?Lowers, SUBSTRING(Item_NAME,2,3)?MidValue,LEN(Item_NAME)?Lenths? ???,SUBSTRING(Item_NAME,2,LEN(Item_NAME))?MidValuewithLenFunction,? ????ROUND(Price,0)?as?Rounded FROM?ItemMasters
4、日期函數
在我們的項目數據表中基本都會使用到日期列，因此日期函數在項目中扮演著非常重要的角色。有時候我們對日期函數要非常的小心，它隨時可以給你帶來巨大的麻煩。在項目中，我們要選擇合適的日期函數和日期格式，下面是一些SQL日期函數的例子：
--?GETDATE()?->?to?Display?the?Current?Date?and?Time --?Format()?->?used?to?display?our?date?in?our?requested?format Select?GETDATE()?CurrentDateTime,?FORMAT(GETDATE(),'yyyy-MM-dd')?AS?DateFormats, ???FORMAT(GETDATE(),'HH-mm-ss')TimeFormats, ???CONVERT(VARCHAR(10),GETDATE(),10)?Converts1, ???CONVERT(VARCHAR(24),GETDATE(),113), ???CONVERT(NVARCHAR,?getdate(),?106)?Converts2?,--?here?we?used?Convert?Function? ??REPLACE(convert(NVARCHAR,?getdate(),?106),?'?',?'/')?Formats--?Here?we?used?replace?and?--convert?functions. ??--first?we?convert?the?date?to?nvarchar?and?then?we?replace?the?''?with?'/'? select?*?from?Itemmasters Select??ITEM_NAME,IN_DATE?CurrentDateTime,?FORMAT(IN_DATE,'yyyy-MM-dd')?AS?DateFormats, FORMAT(IN_DATE,'HH-mm-ss')TimeFormats, CONVERT(VARCHAR(10),IN_DATE,10)?Converts1, CONVERT(VARCHAR(24),IN_DATE,113), convert(NVARCHAR,?IN_DATE,?106)?Converts2?,--?here?we?used?Convert?Function? REPLACE(convert(NVARCHAR,IN_DATE,?106),?'?',?'/')?Formats FROM?Itemmasters
DatePart?–> ?該函數可以獲取年、月、日的信息。
DateADD?–> ?該函數可以對當前的日期進行加減。
DateDiff??–> ?該函數可以比較2個日期。
--Datepart?DATEPART(dateparttype,yourDate) SELECT?DATEPART(yyyy,getdate())?AS?YEARs?, DATEPART(mm,getdate())?AS?MONTHS, DATEPART(dd,getdate())?AS?Days, DATEPART(week,getdate())?AS?weeks, DATEPART(hour,getdate())?AS?hours --Days?Add?to?add?or?subdtract?date?from?a?selected?date. SELECT?GetDate()CurrentDate,DATEADD(day,12,getdate())?AS?AddDays?, ?DATEADD(day,-4,getdate())?AS?FourDaysBeforeDate? ?--?DATEDIFF()?->?to?display?the?Days?between?2?dates ?select?DATEDIFF(year,'2003-08-05',getdate())??yearDifferance?,? ??DATEDIFF(day,DATEADD(day,-24,getdate()),getdate())?daysDifferent, ?DATEDIFF(month,getdate(),DATEADD(Month,6,getdate()))?MonthDifferance
5、其他Select函數
Top?—— 結合select語句，Top函數可以查詢頭幾條和末幾條的數據記錄。
Order By?—— 結合select語句，Order By可以讓查詢結果按某個字段正序和逆序輸出數據記錄。
–
-Top?to?Select?Top?first?and?last?records?using?Select?Statement. Select?*?FROM?ItemMasters -->?First?Display?top?2?Records Select?TOP?2?Item_Code ?,Item_name?as?Item ?,Price ?,Description ?,In_DATE FROM?ItemMasters -->?to?Display?the?Last?to?Records?we?need?to?use?the?Order?By?Clause --?order?By?to?display?Records?in?assending?or?desending?order?by?the?columns Select?TOP?2??Item_Code ?,Item_name?as?Item ?,Price ?,Description ?,In_DATE FROM?ItemMasters ORDER?BY?Item_Code?DESC
Distinct?——?distinct關鍵字可以過濾重復的數據記錄。
Select?*?FROM?ItemMasters --Distinct?->?To?avoid?the?Duplicate?records?we?use?the?distinct?in?select?statement --?for?example?in?this?table?we?can?see?here?we?have?the?duplicate?record?'Chiken?Burger' --?but?with?different?Item_Code?when?i?use?the?below?select?statement?see?what?happen Select???Item_name?as?Item ,Price ,Description ,IN_USR_ID FROM?ItemMasters --?here?we?can?see?the?Row?No?3?and?5?have?the?duplicate?record?to?avoid?this?we?use?the?distinct?Keyword?in?select?statement. select?Distinct?Item_name?as?Item ,Price ,Description ,IN_USR_ID ?FROM?ItemMasters
6、Where子句
Where子句在SQL Select查詢語句中非常重要，為什么要使用where子句？什么時候使用where子句？where子句是利用一些條件來過濾數據結果集。
下面我們從10000條數據記錄中查詢Order_No為某個值或者某個區間的數據記錄，另外還有其他的條件。
Select?*?from?ItemMasters Select?*?from?OrderDetails --Where?->?To?display?the?data?with?certain?conditions --?Now?below?example?which?will?display?all?the?records?which?has?Item_Name='Coke' select?*?FROM?ItemMasters?WHERE?ITEM_NAME='COKE' --?If?we?want?display?all?the?records?Iten_Name?which?Starts?with?'C'?then?we?use?Like?in?where?clause. SELECT?*?FROM?ItemMasters?WHERE?ITEM_NAME?Like?'C%' -->?here?we?display?the?ItemMasters?where?the?price?will?be?greater?then?or?equal?to?40. -->?to?use?more?then?one?condition?we?can?Use?And?or?Or?operator. --If?we?want?to?check?the?data?between?to?date?range?then?we?can?use?Between?Operator?in?Where?Clause. select?Item_name?as?Item ,Price ,Description ,IN_USR_ID ?FROM?ItemMasters ?WHERE ITEM_NAME?Like?'C%'? AND? price?>=40 -->?here?we?display?the?OrderDetails?where?the?Qty?will?be?greater?3 Select?*?FROM?OrderDetails?WHERE?qty>3
Where – In 子句
--?In?clause?->?used?to?display?the?data?which?is?in?the?condition select?* FROM?ItemMasters WHERE Item_name?IN?('Coffee','Chiken?Burger') --?In?clause?with?Order?By?-?Here?we?display?the?in?descending?order. select?* FROM?ItemMasters WHERE Item_name?IN?('Coffee','Chiken?Burger') ORDER?BY?Item_Code?Desc
Where – Between子句
--?between??->?Now?if?we?want?to?display?the?data?between?to?date?range?then?we?use?betweeen?keyword select?*?FROM?ItemMasters select?*?FROM?ItemMasters ?WHERE ?In_Date?BETWEEN?'2014-09-22?15:59:02.853'?AND?'2014-09-22?15:59:02.853' select?*?FROM?ItemMasters ?WHERE ?ITEM_NAME?Like?'C%'? ?AND ?In_Date?BETWEEN?'2014-09-22?15:59:02.853'?AND?'2014-09-22?15:59:02.853'
查詢某個條件區間的數據，我們常常使用between子句。
7、Group By 子句
Group By子句可以對查詢的結果集按指定字段分組：
--Group?By?->?To?display?the?data?with?group?result.Here?we?can?see?we?display?all?the?AQggregate?result?by?Item?Name Select?ITEM_NAME,Count(*)??TotalRows,AVG(Price)?AVGPrice ,MAX(Price)?MAXPrice,MIN(Price)?MinPrice,Sum(price)?PriceTotal? FROM ItemMasters GROUP?BY?ITEM_NAME --?Here?this?group?by?will?combine?all?the?same?Order_No?result?and?make?the?total?or?each?order_NO Select?Order_NO,Sum(QTy)?as?TotalQTY? FROM?OrderDetails where?qty>=2 GROUP?BY?Order_NO --?Here?the?Total?will?be?created?by?order_No?and?Item_Code Select?Order_NO,Item_Code,Sum(QTy)?as?TotalQTY? FROM?OrderDetails where?qty>=2 GROUP?BY?Order_NO,Item_Code Order?By?Order_NO?Desc,Item_Code
Group By & Having 子句
--Group?By?Clause?--?here?this?will?display?all?the?Order_no? Select?Order_NO,Sum(QTy)?as?TotalQTY? FROM?OrderDetails GROUP?BY?Order_NO --?Having?Clause--?This?will?avoid?the?the?sum(qty)?less?then?4 Select?Order_NO,Sum(QTy)?as?TotalQTY? FROM?OrderDetails GROUP?BY?Order_NO HAVING?Sum(QTy)?>4
8、子查詢
子查詢一般出現在where內連接查詢和嵌套查詢中，select、update和delete語句中均可以使用。
--Sub?Query?--?Here?we?used?the?Sub?query?in?where?clause?to?get?all?the?Item_Code?where?the?price>40?now?this?sub? --query?reslut?we?used?in?our?main?query?to?filter?all?the?records?which?Item_code?from?Subquery?result SELECT?*?FROM?ItemMasters? ?WHERE?Item_Code?IN? (SELECT?Item_Code?FROM?ItemMasters?WHERE?price?>?40)? --?Sub?Query?with?Insert?Statement INSERT?INTO?ItemMasters???????????([Item_Code]?,[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE] ???????????,[IN_USR_ID],[UP_DATE]?,[UP_USR_ID]) ????Select?'Item006' ???????????,Item_Name,Price+4,TAX1,Discount,Description ???????????,GetDate(),'SHANU',GetDate(),'SHANU' ???????????from?ItemMasters ???????????where?Item_code='Item002'??????? --After?insert?we?can?see?the?result?as? ????????Select?*?from?ItemMasters
9、連接查詢
到目前為止我們接觸了不少單表的查詢語句，現在我們來使用連接查詢獲取多個表的數據。
簡單的join語句：
--Now?we?have?used?the?simple?join?with?out?any?condition?this?will?display?all?the --?records?with?duplicate?data?to?avaoid?this?we?see?our?next?example?with?condition SELECT?*?FROM?Ordermasters,OrderDetails --?Simple?Join?with?Condition??now?here?we?can?see?the?duplicate?records?now?has?been?avoided?by?using?the?where?checing?with?both?table?primaryKey?field SELECT?*? FROM Ordermasters?as?M,?OrderDetails?as?D where?M.Order_NO=D.Order_NO and?M.Order_NO='Ord_001' --?Now?to?make?more?better?understanding?we?need?to?select?the?need?fields?from?both? --table?insted?of?displaying?all?column. SELECT?M.order_NO,M.Table_ID,D.Order_detail_no,Item_code,Notes,Qty FROM? Ordermasters?as?M,?OrderDetails?as?D? where?M.Order_NO=D.Order_NO?????????????????? ?--?Now?lets?Join?3?table ?SELECT?M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price, ????????????????I.Price*D.Qty?as?TotalPrice FROM? Ordermasters?as?M,?OrderDetails?as?D,ItemMasters?as?I? where? M.Order_NO=D.Order_NO?AND?D.Item_Code=I.Item_Code
Inner Join,Left Outer Join,Right Outer Join and Full outer Join
下面是各種類型的連接查詢代碼：
--INNER?JOIN? --This?will?display?the?records?which?in?both?table?Satisfy?here?i?have?used?Like?in?where?class?which?display?the? SELECT?M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty?as?TotalPrice FROM? Ordermasters?as?M?Inner?JOIN?OrderDetails?as?D? ON?M.Order_NO=D.Order_NO INNER?JOIN?ItemMasters?as?I? ON???D.Item_Code=I.Item_Code WHERE M.Table_ID?like?'T%' --LEFT?OUTER?JOIN? --This?will?display?the?records?which?Left?side?table?Satisfy ?SELECT?M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty?as?TotalPrice FROM? Ordermasters?as?M?LEFT?OUTER?JOIN?OrderDetails?as?D? ON?M.Order_NO=D.Order_NO LEFT?OUTER?JOIN?ItemMasters?as?I? ON???D.Item_Code=I.Item_Code WHERE M.Table_ID?like?'T%' --RIGHT?OUTER?JOIN? --This?will?display?the?records?which?Left?side?table?Satisfy ?SELECT?M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty?as?TotalPrice FROM? Ordermasters?as?M?RIGHT?OUTER?JOIN?OrderDetails?as?D? ON?M.Order_NO=D.Order_NO RIGHT?OUTER?JOIN?ItemMasters?as?I? ON???D.Item_Code=I.Item_Code WHERE M.Table_ID?like?'T%' --FULL?OUTER?JOIN? --This?will?display?the?records?which?Left?side?table?Satisfy ?SELECT?M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty?as?TotalPrice FROM? Ordermasters?as?M?FULL?OUTER?JOIN?OrderDetails?as?D? ON?M.Order_NO=D.Order_NO FULL?OUTER?JOIN?ItemMasters?as?I? ON???D.Item_Code=I.Item_Code WHERE M.Table_ID?like?'T%'
10、Union合并查詢
Union查詢可以把多張表的數據合并起來，Union只會把唯一的數據查詢出來，而Union ALL則會把重復的數據也查詢出來。
Select?column1,Colum2?from?Table1 Union Select?Column1,Column2?from?Table2 Select?column1,Colum2?from?Table1 Union?All Select?Column1,Column2?from?Table2
具體的例子如下：
--Select?with?different?where?condition?which?display?the?result?as?2?Table?result select?Item_Code,Item_Name,Price,Description?FROM?ItemMasters?where?price?44 --?Union?with?same?table?but?with?different?where?condition?now?which?result?as?one?table?which?combine?both?the?result. select?Item_Code,Item_Name,Price,Description?FROM?ItemMasters?where?price?44 --?Union?ALL?with?Join?sample ?SELECT?M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty?as?TotalPrice FROM? Ordermasters?as?M?(NOLOCK)???Inner?JOIN?OrderDetails?as?D?? ON?M.Order_NO=D.Order_NO?INNER?JOIN?ItemMasters?as?I? ON???D.Item_Code=I.Item_Code?WHEREI.Price?44
11、公用表表達式（CTE）——With語句
CTE可以看作是一個臨時的結果集，可以在接下來的一個SELECT,INSERT,UPDATE,DELETE,MERGE語句中被多次引用。使用公用表達式可以讓語句更加清晰簡練。
declare?@sDate?datetime, ????????@eDate?datetime; select??@sDate?=?getdate()-5, ????????@eDate?=?getdate()+16; --select?@sDate?StartDate,@eDate?EndDate ;with?cte?as ???( ??????select?@sDate?StartDate,'W'+convert(varchar(2), ????????????DATEPART(?wk,?@sDate))+'('+convert(varchar(2),@sDate,106)+')'?as?'SDT'????? ??union?all ???????select??dateadd(DAY,?1,?StartDate)?, ??????????????'W'+convert(varchar(2),DATEPART(?wk,?StartDate))+'('+convert(varchar(2), ???????????????dateadd(DAY,?1,?StartDate),106)+')'?as?'SDT'??? ??FROM??cte ??WHERE?dateadd(DAY,?1,?StartDate)<p style="margin: 20px 0px; padding: 5px 5px 5px 10px; font-weight: normal; font-family: Verdana, " microsoft yahei border-left: solid rgb font-size: color: line-height: white-space: normal widows: background:> 12、視圖</p><p> 很多人對視圖View感到很沮喪，因為它看起來跟select語句沒什么區別。在視圖中我們同樣可以使用select查詢語句，但是視圖對我們來說依然非常重要。</p><p> 假設我們要聯合查詢4張表中的20幾個字段，那么這個select查詢語句會非常復雜。但是這樣的語句我們在很多地方都需要用到，如果將它編寫成視圖，那么使用起來會方便很多。利用視圖查詢有以下幾個優點：</p><p>一定程度上提高查詢速度</p><p>可以對一些字段根據不同的權限進行屏蔽，因此提高了安全性</p><p>對多表的連接查詢會非常方便</p><p> 下面是一個視圖的代碼例子：</p><pre class="brush:php;toolbar:false">CREATE? VIEW?viewname AS Select?ColumNames?from?yourTable Example?:? --?Here?we?create?view?for?our?Union?ALL?example Create? VIEW?myUnionVIEW AS ?SELECT?M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price, ????????I.Price*D.Qty?as?TotalPrice ????FROM? ??Ordermasters?as?M??Inner?JOIN?OrderDetails?as?D? ??ON?M.Order_NO=D.Order_NO?INNER?JOIN?ItemMasters?as?I? ??ON???D.Item_Code=I.Item_Code?WHEREI.Price?44 --?View?Select?query Select?*?from?myUnionVIEW --?We?can?also?use?the?View?to?display?with?where?condition?and?with?selected?fields? Select?order_Detail_NO,Table_ID,Item_Name,Price?from?myUnionVIEW?where?price?>40
13、Pivot行轉列
Pivot可以幫助你實現數據行轉換成數據列，具體用法如下：
--?Simple?Pivot?Example? SELECT?*??FROM?ItemMasters? PIVOT(SUM(Price)??????? ?FOR?ITEM_NAME?IN?([Chiken?Burger],?Coffee,Coke))?AS?PVTTable --?Pivot?with?detail?example SELECT?* FROM?( ????SELECT? ???????ITEM_NAME,? ????????price?as?TotAmount? ????FROM?ItemMasters )?as?s PIVOT ( ????SUM(TotAmount) ????FOR?[ITEM_NAME]?IN?([Chiken?Burger],?[Coffee],[Coke]) )AS?MyPivot
14、存儲過程
我經常看到有人提問如何在SQL Server中編寫多條查詢的SQL語句，然后將它們使用到C#程序中去。存儲過程就可以完成這樣的功能，存儲過程可以將多個SQL查詢聚集在一起，創建存儲過程的基本結構是這樣的：
CREATE?PROCEDURE?[ProcedureName]?????????????????????????????????????????????? AS???????????????????????????????????????????????????????????????? BEGIN --?Select?or?Update?or?Insert?query. END To?execute?SP?we?use exec?ProcedureName
創建一個沒有參數的存儲過程：
--?=============================================???????????????????????????????????????????????????????????????? --?Author??????:?Shanu???????????????????????????????????????????????????????????????? --?Create?date?:?2014-09-15???????????????????????????????????????????????????????????????? --?Description?:?To?Display?Pivot?Data???????????????????????????????????????????????????????? --?Latest???????????????????????????????????????????????????????????????? --?Modifier????:?Shanu???????????????????????????????????????????????????????????????? --?Modify?date?:?2014-09-15????????????????????????????????????????????????????????????????? --?=============================================???????????????????????????????????????????????????????????????? --?exec?USP_SelectPivot????????????????????????????????? --?=============================================??????????????????????????????????????????????????????????? Create?PROCEDURE?[dbo].[USP_SelectPivot]?????? AS???????????????????????????????????????????????????????????????? BEGIN????????????????????????????????????????????????? ???DECLARE?@MyColumns?AS?NVARCHAR(MAX), ????@SQLquery??AS?NVARCHAR(MAX) --?here?first?we?get?all?the?ItemName?which?should?be?display?in?Columns?we?use?this?in?our?necxt?pivot?query select?@MyColumns?=?STUFF((SELECT?','?+?QUOTENAME(Item_NAME)? ????????????????????FROM?ItemMasters ????????????????????GROUP?BY?Item_NAME ????????????????????ORDER?BY?Item_NAME ????????????FOR?XML?PATH(''),?TYPE ????????????).value('.',?'NVARCHAR(MAX)')? ????????,1,1,'') --?here?we?use?the?above?all?Item?name?to?disoplay?its?price?as?column?and?row?display set?@SQLquery?=?N'SELECT?'?+?@MyColumns?+?N'?from? ?????????????( ?????????????????SELECT? ???????ITEM_NAME,? ????????price?as?TotAmount? ????FROM?ItemMasters ????????????)?x ????????????pivot? ????????????( ?????????????????SUM(TotAmount) ????????????????for?ITEM_NAME?in?('?+?@MyColumns?+?N') ????????????)?p?' exec?sp_executesql?@SQLquery;??????????? ???RETURN?????????????????????????????????????????????? ????END
15、函數Function
之前我們介紹了MAX(),SUM(), GetDate()等最基本的SQL函數，現在我們來看看如何創建自定義SQL函數。創建函數的格式如下：
Create?Function?functionName As Begin END
下面是一個簡單的函數示例：
--?=============================================???????????????????????????????????????????????????????????????? --?Author??????:?Shanu???????????????????????????????????????????????????????????????? --?Create?date?:?2014-09-15???????????????????????????????????????????????????????????????? --?Description?:?To?Display?Pivot?Data???????????????????????????????????????????????????????? --?Latest???????????????????????????????????????????????????????????????? --?Modifier????:?Shanu???????????????????????????????????????????????????????????????? --?Modify?date?:?2014-09-15????????????????????????????????????????????????????????????????? Alter?FUNCTION?[dbo].[ufnSelectitemMaster]() RETURNS?int? AS? --?Returns?total?Row?count?of?Item?Master. BEGIN ??DECLARE?@RowsCount?AS?int; Select?@RowsCount=?count(*)+1?from?ItemMasters ?RETURN?@RowsCount; END --?to?View?Function?we?use?select?and?fucntion?Name select?[dbo].[ufnSelectitemMaster]()
下面的一個函數可以實現從給定的日期中得到當前月的最后一天：
--?=============================================???????????????????????????????????????????????????????????????? --?Author??????:?Shanu???????????????????????????????????????????????????????????????? --?Create?date?:?2014-09-15???????????????????????????????????????????????????????????????? --?Description?:?To?Display?Pivot?Data???????????????????????????????????????????????????????? --?Latest???????????????????????????????????????????????????????????????? --?Modifier????:?Shanu???????????????????????????????????????????????????????????????? --?Modify?date?:?2014-09-15???? ALTER?FUNCTION?[dbo].[ufn_LastDayOfMonth] ( ???@DATE?NVARCHAR(10)? ) RETURNS?NVARCHAR(10) AS BEGIN ???RETURN?CONVERT(NVARCHAR(10),?DATEADD(D,?-1,?DATEADD(M,?1,?CAST(SUBSTRING(@DATE,1,7)?+?'-01'?AS?DATETIME))),?120) END SELECT?dbo.ufn_LastDayOfMonth('2014-09-01')AS?LastDay
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
