首页 > 代码库 > 15个初学者必看的基础SQL查询语句(转)

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

译文链接:http://www.codeceo.com/article/15-basic-sql-for-beginner.html
英文原文:Basic SQL Queries for Beginners

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

4、日期函数

在我们的项目数据表中基本都会使用到日期列,因此日期函数在项目中扮演着非常重要的角色。有时候我们对日期函数要非常的小心,它随时可以给你带来巨大的麻烦。在项目中,我们要选择合适的日期函数和日期格式,下面是一些SQL日期函数的例子:

-- 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 –>  该函数可以获取年、月、日的信息。

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

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

Where子句在SQL Select查询语句中非常重要,为什么要使用where子句?什么时候使用where子句?where子句是利用一些条件来过滤数据结果集。

下面我们从10000条数据记录中查询Order_No为某个值或者某个区间的数据记录,另外还有其他的条件。

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

Where – In 子句

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

Where – Between子句

-- 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.853select * 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 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 & 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 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

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

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

12、视图

很多人对视图View感到很沮丧,因为它看起来跟select语句没什么区别。在视图中我们同样可以使用select查询语句,但是视图对我们来说依然非常重要。

假设我们要联合查询4张表中的20几个字段,那么这个select查询语句会非常复杂。但是这样的语句我们在很多地方都需要用到,如果将它编写成视图,那么使用起来会方便很多。利用视图查询有以下几个优点:

  • 一定程度上提高查询速度
  • 可以对一些字段根据不同的权限进行屏蔽,因此提高了安全性
  • 对多表的连接查询会非常方便

下面是一个视图的代码例子:

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

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 exampleSELECT *FROM (    SELECT        ITEM_NAME,         price as TotAmount     FROM ItemMasters) as sPIVOT(    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.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 = NSELECT  + @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 functionNameAsBeginEND

下面是一个简单的函数示例:

-- =============================================                                                                -- 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 Nameselect [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)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

 

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