久久天堂一区二区三区_国内精品久久久久影院网站_亚洲国产一区二区三区最新_亚洲国产一区二区三区四区五区

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



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 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 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.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 WHERE I.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 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.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 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行轉列
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

北大青鳥網上報名
北大青鳥招生簡章
久久天堂一区二区三区_国内精品久久久久影院网站_亚洲国产一区二区三区最新_亚洲国产一区二区三区四区五区

      国产精品福利网| 亚洲女爱视频在线| 久久久久在线观看| 午夜影院日韩| 日韩视频在线一区二区| 国产毛片一区| 欧美日韩亚洲激情| 久久亚洲综合色| 亚洲欧美日韩国产成人| 99国产一区二区三精品乱码| 国产一区二区视频在线观看| 欧美日韩一区三区| 欧美精品播放| 蜜乳av另类精品一区二区| 久久国内精品自在自线400部| 亚洲视频1区2区| 亚洲精品午夜精品| 在线观看视频一区二区| 国产亚洲激情视频在线| 国产精品久久久久aaaa九色| 欧美剧在线免费观看网站| 久久伊人亚洲| 久久久久.com| 久久激情五月激情| 性欧美精品高清| 欧美一区二区三区婷婷月色 | 国产精品区二区三区日本| 欧美激情一区二区三区在线| 麻豆成人在线播放| 免费观看欧美在线视频的网站| 午夜视频在线观看一区| 亚洲欧美日韩视频一区| 亚洲视频碰碰| 亚洲欧美日韩精品综合在线观看| 一本色道久久综合亚洲精品婷婷| 在线观看国产精品淫| 亚洲动漫精品| 亚洲精品国精品久久99热一| 99精品99| 亚洲男人av电影| 欧美中文在线视频| 久久亚洲综合网| 欧美大尺度在线| 欧美日韩精选| 国产欧美 在线欧美| 国内精品伊人久久久久av一坑| 国内外成人在线| 亚洲国产美女| 亚洲一区二区三区免费视频| 亚洲欧美激情一区二区| 久久久久久自在自线| 免费精品视频| 国产精品久久久久aaaa九色| 国产一区在线播放| 亚洲欧洲精品一区二区三区| 一本久久a久久精品亚洲| 亚洲综合视频在线| 狼狼综合久久久久综合网| 欧美精品日韩精品| 国产麻豆成人精品| 亚洲日本va午夜在线影院| 亚洲少妇自拍| 久久精品夜色噜噜亚洲aⅴ| 欧美—级高清免费播放| 国产欧美日本| 日韩一区二区精品视频| 欧美与欧洲交xxxx免费观看| 欧美极品色图| 一区二区三区在线不卡| 国产精品99久久久久久www| 久久深夜福利免费观看| 欧美系列一区| 亚洲第一在线| 久久激情视频免费观看| 欧美日韩伦理在线免费| 狠狠操狠狠色综合网| 亚洲伊人久久综合| 美女主播精品视频一二三四| 国产精品久久久久久久第一福利| 在线观看日韩精品| 亚久久调教视频| 国产精品免费视频观看| 亚洲精品中文在线| 免费不卡在线观看| 黑人一区二区三区四区五区| 先锋影音久久| 国产精品美女在线观看| 夜夜爽夜夜爽精品视频| 欧美激情综合五月色丁香| 在线观看欧美日本| 久久综合色88| 悠悠资源网亚洲青| 一本色道久久综合亚洲精品小说| 欧美日韩亚洲视频一区| 国产精品久久久一本精品| 99国内精品久久| 欧美精品一区二区三区在线看午夜| 国产专区欧美专区| 欧美一区2区三区4区公司二百| 欧美日韩综合| 亚洲午夜激情网站| 国产精品护士白丝一区av| 一区二区电影免费观看| 欧美日本一区二区三区 | 小嫩嫩精品导航| 国产精品日韩在线播放| 亚洲免费视频成人| 国产精品日韩在线一区| 欧美一区二区三区在线观看视频| 国产欧美精品在线| 香蕉视频成人在线观看| 国内成人在线| 美女视频黄a大片欧美| 亚洲黄色性网站| 欧美日韩三级视频| 亚洲综合视频网| 一区二区三区在线不卡| 欧美粗暴jizz性欧美20| 一本色道久久88综合亚洲精品ⅰ | 欧美三级精品| 欧美在线播放视频| 黄色成人在线网址| 欧美激情欧美狂野欧美精品 | 国产精品国产三级国产专播精品人 | 欧美极品一区| 欧美一乱一性一交一视频| 在线观看免费视频综合| 欧美精品在线一区二区三区| 亚洲免费网站| 亚洲第一黄网| 国产精品裸体一区二区三区| 久久久久久夜精品精品免费| 日韩视频免费观看高清在线视频| 国产精品久久影院| 老牛影视一区二区三区| 亚洲一二三区精品| 亚洲国产人成综合网站| 国产精品日韩精品| 欧美成人中文| 亚洲欧美日韩一区二区三区在线| 亚洲电影免费观看高清完整版在线| 欧美日韩在线精品一区二区三区| 欧美综合国产精品久久丁香| 一本色道久久综合狠狠躁篇的优点 | 欧美日韩国产成人在线| 久久男人资源视频| 亚洲一区综合| 亚洲免费观看高清完整版在线观看| 国产女主播一区二区三区| 欧美精品国产一区| 久久深夜福利| 久久精品国产亚洲一区二区三区 | 一区二区三区欧美日韩| 在线免费观看欧美| 国产精品揄拍一区二区| 欧美午夜剧场| 欧美精品一卡| 欧美+亚洲+精品+三区| 久久久久九九九| 午夜精品久久久久久久99黑人| 99视频精品在线| 亚洲精品女av网站| 亚洲精品影院| 亚洲三级免费观看| 亚洲精品自在在线观看| 亚洲精品视频免费观看| 亚洲国产精品va在线看黑人动漫 | 欧美高清视频www夜色资源网| 久久精品国产精品亚洲| 亚洲欧美久久久久一区二区三区| 夜夜夜久久久| 日韩视频一区二区在线观看| 日韩午夜电影在线观看| 亚洲精品视频在线| 亚洲老司机av| 亚洲最新视频在线播放| av成人手机在线| 亚洲一品av免费观看| 亚洲影视在线播放| 久久成人久久爱| 久久在线视频在线| 欧美高清在线一区二区| 欧美日韩精品在线播放| 欧美日韩在线看| 国产女主播一区二区三区| 韩国女主播一区| 伊甸园精品99久久久久久| 最新成人av在线| 亚洲一级特黄| 久久久精品2019中文字幕神马| 久久这里有精品视频| 欧美精品在线免费| 国产精品久久久久久久久久免费 | 亚洲欧美日韩成人| 久色成人在线| 欧美午夜欧美| 尤妮丝一区二区裸体视频| 亚洲免费av观看| 午夜精品久久久| 欧美国产日韩在线观看|