15個初學者必看的基礎SQL查詢語句

　1、建立表格和資料插入SQL

　　我們在開始建立資料表和向表中插入示範資料之前，我想先跟大家解釋一下即時資料表的設計概念，這樣或許能幫助大家能更好的理解SQL查詢。

　　在資料庫設計中，有一條非常重要的規則就是要正確建立主鍵和外鍵的關係。

　　現在我們來建立幾個餐廳訂單管理的資料表，一共用到3張資料表，Item Master表、Order Master表和Order Detail表。

　[　建立Order Detail表：

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]

　　向Order Detail表插入資料：

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')

　2、簡單的Select查詢語句

　　Select查詢語句是SQL中最基本也是最重要的DML語句之一。那什麼是DML？ DML全名為Data Manipulation Language（資料操縱語言指令），它可以使用戶能夠查詢資料庫以及操作已有資料庫中的資料。

　　下面我們在SQL Server中用select語句來查詢我的姓名(Name)：

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]

　　在資料表中使用select查詢：

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')

　3、合計和標量函數使用select詢問量Server的內建函數，我們可以在select查詢語句中使用它們，像是Count(), Max(), Sum(), Upper(), lower(), Round()等等。下面我們用SQL程式碼來解釋這些函數的用法：

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')

　4、日期函數

　　在我們的專案資料表中基本上都會使用到日期列，因此日期函數在專案中扮演著非常重要的角色。有時候我們對日期函數要非常的小心，它隨時可以帶給你巨大的麻煩。在專案中，我們要選擇適當的日期函數和日期格式，以下是一些SQL日期函數的範例：

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')

　　DatePart –>  此函數可以取得年、月、日的資訊。

　　DateADD –>  此函數可以將目前的日期加減。

　　DateDiff  –>  此函數可以比較2個日期。

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'

　5、其他Select函數

　　Top —— 結合select語句，Top函數可以查詢頭幾條和末幾條的資料記錄。

　　Order By —— 結合select語句，Order By可以讓查詢結果以某個欄位正序和逆序輸出資料記錄。

-

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

　　Distinct － distinct關鍵字可以篩選重複的資料記錄。

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

　6、Where子句

　　Where子句在SQL Select查詢語句中非常重要，為什麼要使用where子句？什麼時候使用where子句？ where子句是利用一些條件來篩選資料結果集。

　　下面我們從10000筆資料記錄中查詢Order_No為某個值或某個區間的資料記錄，另外還有其他的條件。

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

　　Where – In 子句

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

　　Where – Between子句

-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

　　查詢某個條件區間的數據，我們常常使用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
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.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
Union ALL
 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_NOINNER JOIN ItemMasters as I
ON   D.Item_Code=I.Item_CodeWHEREI.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)<=  @eDate 
    )
select * from cte
option (maxrecursion 0)

　12、视图

　　很多人对视图View感到很沮丧，因为它看起来跟select语句没什么区别。在视图中我们同样可以使用select查询语句，但是视图对我们来说依然非常重要。

　　假设我们要联合查询4张表中的20几个字段，那么这个select查询语句会非常复杂。但是这样的语句我们在很多地方都需要用到，如果将它编写成视图，那么使用起来会方便很多。利用视图查询有以下几个优点：

一定程度上提高查询速度

可以对一些字段根据不同的权限进行屏蔽，因此提高了安全性

对多表的连接查询会非常方便

　　下面是一个视图的代码例子：

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
Union ALL
 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_CodeWHEREI.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