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

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

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]

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

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

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

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'

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

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

-- GETDATE() -> to Display the Current Date and Time-- Format() -> used to display our date in our requested formatSelect 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 ItemmastersSelect 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 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

--Top to Select Top first and last records using Select Statement.Select * FROM ItemMasters--> First Display top 2 RecordsSelect TOP 2 Item_Code ,Item_name as Item ,Price ,Description ,In_DATEFROM 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 columnsSelect TOP 2 Item_Code ,Item_name as Item ,Price ,Description ,In_DATEFROM ItemMastersORDER BY Item_Code DESC

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

Select * from ItemMastersSelect * 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 3Select * FROM OrderDetails WHERE qty>3

-- In clause -> used to display the data which is in the conditionselect * 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

-- between -> Now if we want to display the data between to date range then we use betweeen keywordselect * FROM ItemMastersselect * 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'

--Group By -> To display the data with group result.Here we can see we display all the AQggregate result by Item NameSelect 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_NOSelect 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_CodeSelect 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 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

--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 resultSELECT * FROM ItemMasters   WHERE Item_Code IN  (SELECT Item_Code FROM ItemMasters WHERE price > 40)-- Sub Query with Insert StatementINSERT 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

--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 conditionSELECT * 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 fieldSELECT *  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 --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%'

Select column1,Colum2 from Table1UnionSelect Column1,Column2 from Table2Select column1,Colum2 from Table1Union AllSelect Column1,Column2 from Table2

--Select with different where condition which display the result as 2 Table resultselect Item_Code,Item_Name,Price,Description FROM ItemMasters where price <=44select 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 <=44UNIONselect 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

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 cteoption (maxrecursion 0)

CREATE VIEW viewnameASSelect ColumNames from yourTableExample : -- Here we create view for our Union ALL exampleCreate VIEW myUnionVIEWAS 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 querySelect * 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

-- Simple Pivot Example SELECT * FROM ItemMasters PIVOT(SUM(Price)     FOR ITEM_NAME IN ([Chiken Burger], Coffee,Coke)) AS PVTTable-- Pivot with detail exampleSELECT *FROM (  SELECT     ITEM_NAME,     price as TotAmount   FROM ItemMasters) as sPIVOT(  SUM(TotAmount)  FOR [ITEM_NAME] IN ([Chiken Burger], [Coffee],[Coke]))AS MyPivot

CREATE PROCEDURE [ProcedureName]                       AS                                BEGIN-- Select or Update or Insert query.ENDTo execute SP we useexec 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 queryselect @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 displayset @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

Create Function functionNameAsBeginEND

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 Nameselect [dbo].[ufnSelectitemMaster]()

ALTER FUNCTION [dbo].[ufn_LastDayOfMonth](  @DATE NVARCHAR(10) )RETURNS NVARCHAR(10)ASBEGIN  RETURN CONVERT(NVARCHAR(10), DATEADD(D, -1, DATEADD(M, 1, CAST(SUBSTRING(@DATE,1,7) + '-01' AS DATETIME))), 120)ENDSELECT dbo.ufn_LastDayOfMonth('2014-09-01')AS LastDay