초보자가 꼭 읽어야 할 기본 SQL 쿼리문 15가지
- 伊谢尔伦원래의
- 2016-11-24 10:56:581145검색
1. 테이블을 생성하고 SQL에 데이터를 삽입합니다
데이터 테이블을 생성하고 테이블에 데모 데이터를 삽입하기 전에 실시간 데이터의 설계 개념에 대해 설명하겠습니다. SQL 쿼리를 더 잘 이해하는 데 도움이 될 수 있는 데이터 테이블입니다.
데이터베이스 설계에서 매우 중요한 규칙은 기본 키와 외래 키 간의 관계를 올바르게 설정하는 것입니다.
이제 레스토랑 주문 관리를 위한 여러 데이터 테이블을 만들어 보겠습니다. Item Master 테이블, Order Master 테이블, Order Detail 테이블 등 총 3개의 데이터 테이블이 사용됩니다.
테이블 생성:
품목 마스터 테이블 생성:
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]
품목 마스터 테이블에 데이터 삽입:
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에 내장된 함수로 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 ItemMasters4. 날짜 함수
날짜 열은 기본적으로 프로젝트 데이터 테이블에서 사용되므로 날짜 함수를 사용합니다. 프로젝트에서 매우 중요한 역할을 합니다. 때때로 우리는 날짜 기능에 매우 주의해야 합니다. 날짜 기능은 언제든지 큰 문제를 일으킬 수 있습니다. 프로젝트에서 적절한 날짜 함수와 날짜 형식을 선택해야 합니다. 다음은 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 –> 두 날짜를 비교할 수 있는 기능입니다.
--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 - 고유 키워드를 사용하면 중복된 데이터 레코드를 필터링할 수 있습니다.
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 절
SQL Select 쿼리문에서 Where 절은 매우 중요합니다. Where 절을 사용하는 이유는 무엇입니까? 언제 where 절을 사용하나요? where 절은 일부 조건을 사용하여 데이터 결과 집합을 필터링합니다.
다음으로 10,000개의 데이터 레코드 중 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 ItemMasters9、连接查询
到目前为止我们接触了不少单表的查询语句，现在我们来使用连接查询获取多个表的数据。
简单的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_CodeInner 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 >4013、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 MyPivot14、存储过程
我经常看到有人提问如何在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
END15、函数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
성명：
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
이전 기사：MySQL 최적화를 위한 21가지 팁다음 기사：MySQL 최적화를 위한 21가지 팁