程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> 15個初學者必看的基本SQL查詢語句

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

編輯：MSSQL

15個初學者必看的基本SQL查詢語句。本站提示廣大學習愛好者：（15個初學者必看的基本SQL查詢語句）文章只能為提供參考，不一定能成為您想要的結果。以下是15個初學者必看的基本SQL查詢語句正文

本文將分享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

上面是一個簡略的函數示例：

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]()

上面的一個函數可以完成從給定的日期中獲得以後月的最初一天：

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

以上就是合適初學者進修的基本SQL查詢語句，願望對年夜家進修SQL查詢語句有所贊助。