本文將分享15個(gè)初學(xué)者必看的基礎(chǔ)SQL查詢語(yǔ)句，都很基礎(chǔ)杨拐，但是你不一定都會(huì)哗脖，所以好好看看吧瀑踢。

1扳还、創(chuàng)建表和數(shù)據(jù)插入SQL

我們?cè)陂_始創(chuàng)建數(shù)據(jù)表和向表中插入演示數(shù)據(jù)之前，我想給大家解釋一下實(shí)時(shí)數(shù)據(jù)表的設(shè)計(jì)理念橱夭，這樣也許能幫助大家能更好的理解SQL查詢氨距。

在數(shù)據(jù)庫(kù)設(shè)計(jì)中，有一條非常重要的規(guī)則就是要正確建立主鍵和外鍵的關(guān)系徘钥。

現(xiàn)在我們來創(chuàng)建幾個(gè)餐廳訂單管理的數(shù)據(jù)表衔蹲，一共用到3張數(shù)據(jù)表，Item Master表呈础、Order Master表和Order Detail表舆驶。

創(chuàng)建表：

創(chuàng)建Item Master表：

CREATE TABLE [dbo].[ItemMasters](
[Item_Code] varchar NOT NULL,
[Item_Name] varchar NOT NULL,
[Price] Int NOT NULL,
[TAX1] Int NOT NULL,
[Discount] Int NOT NULL,
[Description] varchar NOT NULL,
[IN_DATE] [datetime] NOT NULL,
[IN_USR_ID] varchar NOT NULL,
[UP_DATE] [datetime] NOT NULL,
[UP_USR_ID] varchar 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表插入數(shù)據(jù)：

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')
創(chuàng)建Order Master表：

CREATE TABLE [dbo].[OrderMasters](
[Order_No] varchar NOT NULL,
[Table_ID] varchar NOT NULL,
[Description] varchar NOT NULL,
[IN_DATE] [datetime] NOT NULL,
[IN_USR_ID] varchar NOT NULL,
[UP_DATE] [datetime] NOT NULL,
[UP_USR_ID] varchar 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表插入數(shù)據(jù)：

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')
創(chuàng)建Order Detail表：

CREATE TABLE [dbo].[OrderDetails](
[Order_Detail_No] varchar NOT NULL,
[Order_No] varchar CONSTRAINT fk_OrderMasters FOREIGN KEY REFERENCES OrderMasters(Order_No),
[Item_Code] varchar CONSTRAINT fk_ItemMasters FOREIGN KEY REFERENCES ItemMasters(Item_Code),
[Notes] varchar NOT NULL,
[QTY] INT NOT NULL,
[IN_DATE] [datetime] NOT NULL,
[IN_USR_ID] varchar NOT NULL,
[UP_DATE] [datetime] NOT NULL,
[UP_USR_ID] varchar 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表插入數(shù)據(jù)：

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、簡(jiǎn)單的Select查詢語(yǔ)句

Select查詢語(yǔ)句是SQL中最基本也是最重要的DML語(yǔ)句之一而钞。那么什么是DML沙廉？DML全稱Data Manipulation Language（數(shù)據(jù)操縱語(yǔ)言命令），它可以使用戶能夠查詢數(shù)據(jù)庫(kù)以及操作已有數(shù)據(jù)庫(kù)中的數(shù)據(jù)臼节。

下面我們?cè)赟QL Server中用select語(yǔ)句來查詢我的姓名(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'
在數(shù)據(jù)表中使用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撬陵、合計(jì)和標(biāo)量函數(shù)

合計(jì)函數(shù)和標(biāo)量函數(shù)都是SQL Server的內(nèi)置函數(shù)，我們可以在select查詢語(yǔ)句中使用它們网缝，比如Count(), Max(), Sum(), Upper(), lower(), Round()等等巨税。下面我們用SQL代碼來解釋這些函數(shù)的用法：

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、日期函數(shù)

在我們的項(xiàng)目數(shù)據(jù)表中基本都會(huì)使用到日期列粉臊，因此日期函數(shù)在項(xiàng)目中扮演著非常重要的角色草添。有時(shí)候我們對(duì)日期函數(shù)要非常的小心，它隨時(shí)可以給你帶來巨大的麻煩扼仲。在項(xiàng)目中远寸，我們要選擇合適的日期函數(shù)和日期格式，下面是一些SQL日期函數(shù)的例子：

-- 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 –> 該函數(shù)可以獲取年屠凶、月驰后、日的信息。

DateADD –> 該函數(shù)可以對(duì)當(dāng)前的日期進(jìn)行加減矗愧。

DateDiff –> 該函數(shù)可以比較2個(gè)日期灶芝。

--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函數(shù)

Top —— 結(jié)合select語(yǔ)句贱枣，Top函數(shù)可以查詢頭幾條和末幾條的數(shù)據(jù)記錄监署。

Order By —— 結(jié)合select語(yǔ)句，Order By可以讓查詢結(jié)果按某個(gè)字段正序和逆序輸出數(shù)據(jù)記錄纽哥。

--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關(guān)鍵字可以過濾重復(fù)的數(shù)據(jù)記錄钠乏。

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查詢語(yǔ)句中非常重要春塌，為什么要使用where子句晓避？什么時(shí)候使用where子句簇捍？where子句是利用一些條件來過濾數(shù)據(jù)結(jié)果集。

下面我們從10000條數(shù)據(jù)記錄中查詢Order_No為某個(gè)值或者某個(gè)區(qū)間的數(shù)據(jù)記錄俏拱，另外還有其他的條件暑塑。

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子句
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'
查詢某個(gè)條件區(qū)間的數(shù)據(jù)，我們常常使用between子句锅必。

7事格、Group By 子句

Group By子句可以對(duì)查詢的結(jié)果集按指定字段分組：

--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、子查詢

子查詢一般出現(xiàn)在where內(nèi)連接查詢和嵌套查詢中搞隐，select驹愚、update和delete語(yǔ)句中均可以使用。

--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.PriceD.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.PriceD.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查詢可以把多張表的數(shù)據(jù)合并起來逢捺，Union只會(huì)把唯一的數(shù)據(jù)查詢出來，而Union ALL則會(huì)把重復(fù)的數(shù)據(jù)也查詢出來癞季。

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
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
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.PriceD.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 WHERE I.Price <=44
Union ALL
SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.PriceD.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 WHERE I.Price>44
11劫瞳、公用表表達(dá)式（CTE）——With語(yǔ)句

CTE可以看作是一個(gè)臨時(shí)的結(jié)果集，可以在接下來的一個(gè)SELECT,INSERT,UPDATE,DELETE,MERGE語(yǔ)句中被多次引用绷柒。使用公用表達(dá)式可以讓語(yǔ)句更加清晰簡(jiǎn)練志于。

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.PriceD.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 I.Price <=44
Union ALL
SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,
I.PriceD.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 I.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行轉(zhuǎn)列

Pivot可以幫助你實(shí)現(xiàn)數(shù)據(jù)行轉(zhuǎn)換成數(shù)據(jù)列废睦，具體用法如下：

-- 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恨憎、存儲(chǔ)過程

我經(jīng)常看到有人提問如何在SQL Server中編寫多條查詢的SQL語(yǔ)句郊楣，然后將它們使用到C#程序中去。存儲(chǔ)過程就可以完成這樣的功能瓤荔，存儲(chǔ)過程可以將多個(gè)SQL查詢聚集在一起净蚤，創(chuàng)建存儲(chǔ)過程的基本結(jié)構(gòu)是這樣的：

-- =============================================
-- 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、函數(shù)Function

之前我們介紹了MAX(),SUM(), GetDate()等最基本的SQL函數(shù)输硝，現(xiàn)在我們來看看如何創(chuàng)建自定義SQL函數(shù)今瀑。創(chuàng)建函數(shù)的格式如下：

Create Function functionName
As
Begin
END
下面是一個(gè)簡(jiǎn)單的函數(shù)示例：

-- =============================================
-- 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
下面的一個(gè)函數(shù)可以實(shí)現(xiàn)從給定的日期中得到當(dāng)前月的最后一天：

-- =============================================
-- 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