15个初学者必看的基础SQL查询语句

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)lt;= @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 lt;=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.Pricegt;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 gt;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

下面是一个简单的函数示例：nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!