初学者必看的基础SQL查询语句分别有哪些
这期内容当中小编将会给大家带来有关初学者必看的基础SQL查询语句分别有哪些，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
将分享15个初学者必看的基础SQL查询语句，都很基础，但是你不一定都会，所以好好看看吧。
1、创建表和数据插入SQL
我们在开始创建数据表和向表中插入演示数据之前，我想给大家解释一下实时数据表的设计理念，这样也许能帮助大家能更好的理解SQL查询。
在数据库设计中，有一条非常重要的规则就是要正确建立主键和外键的关系。
现在我们来创建几个餐厅订单管理的数据表，一共用到3张数据表，Item Master表、Order Master表和Order Detail表。
创建表：
创建Item Master表：
CREATETABLE[dbo].[ItemMasters]([Item_Code][varchar](20)NOTNULL,[Item_Name][varchar](100)NOTNULL,[Price]IntNOTNULL,[TAX1]IntNOTNULL,[Discount]IntNOTNULL,[Description][varchar](200)NOTNULL,[IN_DATE][datetime]NOTNULL,[IN_USR_ID][varchar](20)NOTNULL,[UP_DATE][datetime]NOTNULL,[UP_USR_ID][varchar](20)NOTNULL,CONSTRAINT[PK_ItemMasters]PRIMARYKEYCLUSTERED([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表插入数据：
INSERTINTO[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,'Cokewhichneedtobecold',GETDATE(),'SHANU',GETDATE(),'SHANU')INSERTINTO[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,'CoffeMightbeHotorColduserchoice',GETDATE(),'SHANU',GETDATE(),'SHANU')INSERTINTO[ItemMasters]([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])VALUES('Item003','ChikenBurger',125,2,5,'Spicy',GETDATE(),'SHANU',GETDATE(),'SHANU')INSERTINTO[ItemMasters]([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])VALUES('Item004','PotatoFry',15,0,0,'NoComments',GETDATE(),'SHANU',GETDATE(),'SHANU')
创建Order Master表：
CREATETABLE[dbo].[OrderMasters]([Order_No][varchar](20)NOTNULL,[Table_ID][varchar](20)NOTNULL,[Description][varchar](200)NOTNULL,[IN_DATE][datetime]NOTNULL,[IN_USR_ID][varchar](20)NOTNULL,[UP_DATE][datetime]NOTNULL,[UP_USR_ID][varchar](20)NOTNULL,CONSTRAINT[PK_OrderMasters]PRIMARYKEYCLUSTERED([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表插入数据：
INSERTINTO[OrderMasters]([Order_No],[Table_ID],[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])VALUES('Ord_001','T1','',GETDATE(),'SHANU',GETDATE(),'SHANU')INSERTINTO[OrderMasters]([Order_No],[Table_ID],[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])VALUES('Ord_002','T2','',GETDATE(),'Mak',GETDATE(),'MAK')INSERTINTO[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表：
CREATETABLE[dbo].[OrderDetails]([Order_Detail_No][varchar](20)NOTNULL,[Order_No][varchar](20)CONSTRAINTfk_OrderMastersFOREIGNKEYREFERENCESOrderMasters(Order_No),[Item_Code][varchar](20)CONSTRAINTfk_ItemMastersFOREIGNKEYREFERENCESItemMasters(Item_Code),[Notes][varchar](200)NOTNULL,[QTY]INTNOTNULL,[IN_DATE][datetime]NOTNULL,[IN_USR_ID][varchar](20)NOTNULL,[UP_DATE][datetime]NOTNULL,[UP_USR_ID][varchar](20)NOTNULL,CONSTRAINT[PK_OrderDetails]PRIMARYKEYCLUSTERED([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]--Nowlet’sinsertthe3itemsfortheaboveOrderNo'Ord_001'.INSERTINTO[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','NeedveryCold',3,GETDATE(),'SHANU',GETDATE(),'SHANU')INSERTINTO[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','veryHot',2,GETDATE(),'SHANU',GETDATE(),'SHANU')INSERTINTO[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','VerySpicy',4,GETDATE(),'SHANU',GETDATE(),'SHANU')
向Order Detail表插入数据：
INSERTINTO[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','NeedveryHot',2,GETDATE(),'SHANU',GETDATE(),'SHANU')INSERTINTO[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','veryHot',2,GETDATE(),'SHANU',GETDATE(),'SHANU')INSERTINTO[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','VerySpicy',4,GETDATE(),'SHANU',GETDATE(),'SHANU')
2、简单的Select查询语句
Select查询语句是SQL中最基本也是最重要的DML语句之一。那么什么是DML？DML全称Data Manipulation Language（数据操纵语言命令），它可以使用户能够查询数据库以及操作已有数据库中的数据。
下面我们在SQL Server中用select语句来查询我的姓名(Name)：
SELECT'MyNameIsSYEDSHANU'--WithColumnNameusing'AS'SELECT'MyNameIsSYEDSHANU'as'MYNAME'--WithmorethentheoneColumnSELECT'MyName'as'Column1','Is'as'Column2','SYEDSHANU'as'Column3'
在数据表中使用select查询：
--ToDisplayallthecolumnsfromthetableweuse*operatorinselectStatement.Select*fromItemMasters--IfweneedtoselectonlyfewfieldsfromatablewecanusetheColumnNameinSelectStatement.SelectItem_Code,Item_nameasItem,Price,Description,In_DATEFROMItemMasters
3、合计和标量函数
合计函数和标量函数都是SQL Server的内置函数，我们可以在select查询语句中使用它们，比如Count(), Max(), Sum(), Upper(), lower(), Round()等等。下面我们用SQL代码来解释这些函数的用法：
select*fromItemMasters--Aggregate--COUNT()->returnstheTotalnoofrecordsfromtable,AVG()returnstheAverageValuefromColum,MAX()ReturnsMaXValuefromColumn--,MIN()returnsMinValuefromColumn,SUM()sumoftotalfromColumnSelectCount(*)TotalRows,AVG(Price)AVGPrice,MAX(Price)MAXPrice,MIN(Price)MinPrice,Sum(price)PriceTotalFROMItemMasters--Scalar--UCASE()->ConverttoUpperCase,LCASE()->ConverttoLowerCase,--SUBSTRING()->Displayselectedcharfromcolumn->SUBSTRING(ColumnName,StartIndex,LenthofChartoDisplay)--,LEN()->lenthofcolumndate,--ROUND()->WhichwillroundthevalueSELECTUPPER(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)asRoundedFROMItemMasters
4、日期函数
在我们的项目数据表中基本都会使用到日期列，因此日期函数在项目中扮演着非常重要的角色。有时候我们对日期函数要非常的小心，它随时可以给你带来巨大的麻烦。在项目中，我们要选择合适的日期函数和日期格式，下面是一些SQL日期函数的例子：
--GETDATE()->toDisplaytheCurrentDateandTime--Format()->usedtodisplayourdateinourrequestedformatSelectGETDATE()CurrentDateTime,FORMAT(GETDATE(),'yyyy-MM-dd')ASDateFormats,FORMAT(GETDATE(),'HH-mm-ss')TimeFormats,CONVERT(VARCHAR(10),GETDATE(),10)Converts1,CONVERT(VARCHAR(24),GETDATE(),113),CONVERT(NVARCHAR,getdate(),106)Converts2,--hereweusedConvertFunctionREPLACE(convert(NVARCHAR,getdate(),106),'','/')Formats--Hereweusedreplaceand--convertfunctions.--firstweconvertthedatetonvarcharandthenwereplacethe''with'/'select*fromItemmastersSelectITEM_NAME,IN_DATECurrentDateTime,FORMAT(IN_DATE,'yyyy-MM-dd')ASDateFormats,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,--hereweusedConvertFunctionREPLACE(convert(NVARCHAR,IN_DATE,106),'','/')FormatsFROMItemmasters
DatePart –> 该函数可以获取年、月、日的信息。
DateADD –> 该函数可以对当前的日期进行加减。
DateDiff –> 该函数可以比较2个日期。
--DatepartDATEPART(dateparttype,yourDate)SELECTDATEPART(yyyy,getdate())ASYEARs,DATEPART(mm,getdate())ASMONTHS,DATEPART(dd,getdate())ASDays,DATEPART(week,getdate())ASweeks,DATEPART(hour,getdate())AShours--DaysAddtoaddorsubdtractdatefromaselecteddate.SELECTGetDate()CurrentDate,DATEADD(day,12,getdate())ASAddDays,DATEADD(day,-4,getdate())ASFourDaysBeforeDate--DATEDIFF()->todisplaytheDaysbetween2datesselectDATEDIFF(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可以让查询结果按某个字段正序和逆序输出数据记录。
--ToptoSelectTopfirstandlastrecordsusingSelectStatement.Select*FROMItemMasters-->FirstDisplaytop2RecordsSelectTOP2Item_Code,Item_nameasItem,Price,Description,In_DATEFROMItemMasters-->toDisplaytheLasttoRecordsweneedtousetheOrderByClause--orderBytodisplayRecordsinassendingordesendingorderbythecolumnsSelectTOP2Item_Code,Item_nameasItem,Price,Description,In_DATEFROMItemMastersORDERBYItem_CodeDESC
Distinct —— distinct关键字可以过滤重复的数据记录。
Select*FROMItemMasters--Distinct->ToavoidtheDuplicaterecordsweusethedistinctinselectstatement--forexampleinthistablewecanseeherewehavetheduplicaterecord'ChikenBurger'--butwithdifferentItem_CodewheniusethebelowselectstatementseewhathappenSelectItem_nameasItem,Price,Description,IN_USR_IDFROMItemMasters--herewecanseetheRowNo3and5havetheduplicaterecordtoavoidthisweusethedistinctKeywordinselectstatement.selectDistinctItem_nameasItem,Price,Description,IN_USR_IDFROMItemMasters
6、Where子句
Where子句在SQL Select查询语句中非常重要，为什么要使用where子句？什么时候使用where子句？where子句是利用一些条件来过滤数据结果集。
下面我们从10000条数据记录中查询Order_No为某个值或者某个区间的数据记录，另外还有其他的条件。
Select*fromItemMastersSelect*fromOrderDetails--Where->Todisplaythedatawithcertainconditions--NowbelowexamplewhichwilldisplayalltherecordswhichhasItem_Name='Coke'select*FROMItemMastersWHEREITEM_NAME='COKE'--IfwewantdisplayalltherecordsIten_NamewhichStartswith'C'thenweuseLikeinwhereclause.SELECT*FROMItemMastersWHEREITEM_NAMELike'C%'-->herewedisplaytheItemMasterswherethepricewillbegreaterthenorequalto40.-->tousemorethenoneconditionwecanUseAndorOroperator.--IfwewanttocheckthedatabetweentodaterangethenwecanuseBetweenOperatorinWhereClause.selectItem_nameasItem,Price,Description,IN_USR_IDFROMItemMastersWHEREITEM_NAMELike'C%'ANDprice>=40-->herewedisplaytheOrderDetailswheretheQtywillbegreater3Select*FROMOrderDetailsWHEREqty>3
Where – In 子句
--Inclause->usedtodisplaythedatawhichisintheconditionselect*FROMItemMastersWHEREItem_nameIN('Coffee','ChikenBurger')--InclausewithOrderBy-Herewedisplaytheindescendingorder.select*FROMItemMastersWHEREItem_nameIN('Coffee','ChikenBurger')ORDERBYItem_CodeDesc
Where – Between子句
--between->Nowifwewanttodisplaythedatabetweentodaterangethenweusebetweeenkeywordselect*FROMItemMastersselect*FROMItemMastersWHEREIn_DateBETWEEN'2014-09-2215:59:02.853'AND'2014-09-2215:59:02.853'select*FROMItemMastersWHEREITEM_NAMELike'C%'ANDIn_DateBETWEEN'2014-09-2215:59:02.853'AND'2014-09-2215:59:02.853'
查询某个条件区间的数据，我们常常使用between子句。
7、Group By 子句
Group By子句可以对查询的结果集按指定字段分组：
--GroupBy->Todisplaythedatawithgroupresult.HerewecanseewedisplayalltheAQggregateresultbyItemNameSelectITEM_NAME,Count(*)TotalRows,AVG(Price)AVGPrice,MAX(Price)MAXPrice,MIN(Price)MinPrice,Sum(price)PriceTotalFROMItemMastersGROUPBYITEM_NAME--HerethisgroupbywillcombineallthesameOrder_Noresultandmakethetotaloreachorder_NOSelectOrder_NO,Sum(QTy)asTotalQTYFROMOrderDetailswhereqty>=2GROUPBYOrder_NO--HeretheTotalwillbecreatedbyorder_NoandItem_CodeSelectOrder_NO,Item_Code,Sum(QTy)asTotalQTYFROMOrderDetailswhereqty>=2GROUPBYOrder_NO,Item_CodeOrderByOrder_NODesc,Item_Code
Group By & Having 子句
--GroupByClause--herethiswilldisplayalltheOrder_noSelectOrder_NO,Sum(QTy)asTotalQTYFROMOrderDetailsGROUPBYOrder_NO--HavingClause--Thiswillavoidthethesum(qty)lessthen4SelectOrder_NO,Sum(QTy)asTotalQTYFROMOrderDetailsGROUPBYOrder_NOHAVINGSum(QTy)>4
8、子查询
子查询一般出现在where内连接查询和嵌套查询中，select、update和delete语句中均可以使用。
--SubQuery--HereweusedtheSubqueryinwhereclausetogetalltheItem_Codewheretheprice>40nowthissub--queryreslutweusedinourmainquerytofilteralltherecordswhichItem_codefromSubqueryresultSELECT*FROMItemMastersWHEREItem_CodeIN(SELECTItem_CodeFROMItemMastersWHEREprice>40)--SubQuerywithInsertStatementINSERTINTOItemMasters([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'fromItemMasterswhereItem_code='Item002'--AfterinsertwecanseetheresultasSelect*fromItemMasters
9、连接查询
到目前为止我们接触了不少单表的查询语句，现在我们来使用连接查询获取多个表的数据。
简单的join语句：
--Nowwehaveusedthesimplejoinwithoutanyconditionthiswilldisplayallthe--recordswithduplicatedatatoavaoidthisweseeournextexamplewithconditionSELECT*FROMOrdermasters,OrderDetails--SimpleJoinwithConditionnowherewecanseetheduplicaterecordsnowhasbeenavoidedbyusingthewherechecingwithbothtableprimaryKeyfieldSELECT*FROMOrdermastersasM,OrderDetailsasDwhereM.Order_NO=D.Order_NOandM.Order_NO='Ord_001'--Nowtomakemorebetterunderstandingweneedtoselecttheneedfieldsfromboth--tableinstedofdisplayingallcolumn.SELECTM.order_NO,M.Table_ID,D.Order_detail_no,Item_code,Notes,QtyFROMOrdermastersasM,OrderDetailsasDwhereM.Order_NO=D.Order_NO--NowletsJoin3tableSELECTM.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.QtyasTotalPriceFROMOrdermastersasM,OrderDetailsasD,ItemMastersasIwhereM.Order_NO=D.Order_NOANDD.Item_Code=I.Item_Code
Inner Join,Left Outer Join,Right Outer Join and Full outer Join
下面是各种类型的连接查询代码：
--INNERJOIN--ThiswilldisplaytherecordswhichinbothtableSatisfyhereihaveusedLikeinwhereclasswhichdisplaytheSELECTM.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.QtyasTotalPriceFROMOrdermastersasMInnerJOINOrderDetailsasDONM.Order_NO=D.Order_NOINNERJOINItemMastersasIOND.Item_Code=I.Item_CodeWHEREM.Table_IDlike'T%'--LEFTOUTERJOIN--ThiswilldisplaytherecordswhichLeftsidetableSatisfySELECTM.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.QtyasTotalPriceFROMOrdermastersasMLEFTOUTERJOINOrderDetailsasDONM.Order_NO=D.Order_NOLEFTOUTERJOINItemMastersasIOND.Item_Code=I.Item_CodeWHEREM.Table_IDlike'T%'--RIGHTOUTERJOIN--ThiswilldisplaytherecordswhichLeftsidetableSatisfySELECTM.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.QtyasTotalPriceFROMOrdermastersasMRIGHTOUTERJOINOrderDetailsasDONM.Order_NO=D.Order_NORIGHTOUTERJOINItemMastersasIOND.Item_Code=I.Item_CodeWHEREM.Table_IDlike'T%'--FULLOUTERJOIN--ThiswilldisplaytherecordswhichLeftsidetableSatisfySELECTM.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.QtyasTotalPriceFROMOrdermastersasMFULLOUTERJOINOrderDetailsasDONM.Order_NO=D.Order_NOFULLOUTERJOINItemMastersasIOND.Item_Code=I.Item_CodeWHEREM.Table_IDlike'T%'
10、Union合并查询
Union查询可以把多张表的数据合并起来，Union只会把唯一的数据查询出来，而Union ALL则会把重复的数据也查询出来。
Selectcolumn1,Colum2fromTable1UnionSelectColumn1,Column2fromTable2Selectcolumn1,Colum2fromTable1UnionAllSelectColumn1,Column2fromTable2
具体的例子如下：
--Selectwithdifferentwhereconditionwhichdisplaytheresultas2TableresultselectItem_Code,Item_Name,Price,DescriptionFROMItemMasterswhereprice<=44selectItem_Code,Item_Name,Price,DescriptionFROMItemMasterswhereprice>44--Unionwithsametablebutwithdifferentwhereconditionnowwhichresultasonetablewhichcombineboththeresult.selectItem_Code,Item_Name,Price,DescriptionFROMItemMasterswhereprice<=44UNIONselectItem_Code,Item_Name,Price,DescriptionFROMItemMasterswhereprice>44--UnionALLwithJoinsampleSELECTM.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.QtyasTotalPriceFROMOrdermastersasM(NOLOCK)InnerJOINOrderDetailsasDONM.Order_NO=D.Order_NOINNERJOINItemMastersasIOND.Item_Code=I.Item_CodeWHEREI.Price<=44UnionALLSELECTM.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.QtyasTotalPriceFROMOrdermastersasM(NOLOCK)InnerJOINOrderDetailsasDONM.Order_NO=D.Order_NOINNERJOINItemMastersasIOND.Item_Code=I.Item_CodeWHEREI.Price>44
11、公用表表达式（CTE）——With语句
CTE可以看作是一个临时的结果集，可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练。
declare@sDatedatetime,@eDatedatetime;select@sDate=getdate()-5,@eDate=getdate()+16;--select@sDateStartDate,@eDateEndDate;withcteas(select@sDateStartDate,'W'+convert(varchar(2),DATEPART(wk,@sDate))+'('+convert(varchar(2),@sDate,106)+')'as'SDT'unionallselectdateadd(DAY,1,StartDate),'W'+convert(varchar(2),DATEPART(wk,StartDate))+'('+convert(varchar(2),dateadd(DAY,1,StartDate),106)+')'as'SDT'FROMcteWHEREdateadd(DAY,1,StartDate)<=@eDate)select*fromcteoption(maxrecursion0)
12、视图
很多人对视图View感到很沮丧，因为它看起来跟select语句没什么区别。在视图中我们同样可以使用select查询语句，但是视图对我们来说依然非常重要。
假设我们要联合查询4张表中的20几个字段，那么这个select查询语句会非常复杂。但是这样的语句我们在很多地方都需要用到，如果将它编写成视图，那么使用起来会方便很多。利用视图查询有以下几个优点：
一定程度上提高查询速度 可以对一些字段根据不同的权限进行屏蔽，因此提高了安全性 对多表的连接查询会非常方便
下面是一个视图的代码例子：
CREATEVIEWviewnameASSelectColumNamesfromyourTableExample:--HerewecreateviewforourUnionALLexampleCreateVIEWmyUnionVIEWASSELECTM.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.QtyasTotalPriceFROMOrdermastersasMInnerJOINOrderDetailsasDONM.Order_NO=D.Order_NOINNERJOINItemMastersasIOND.Item_Code=I.Item_CodeWHEREI.Price<=44UnionALLSELECTM.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.QtyasTotalPriceFROMOrdermastersasMInnerJOINOrderDetailsasDONM.Order_NO=D.Order_NOINNERJOINItemMastersasIOND.Item_Code=I.Item_CodeWHEREI.Price>44--ViewSelectquerySelect*frommyUnionVIEW--WecanalsousetheViewtodisplaywithwhereconditionandwithselectedfieldsSelectorder_Detail_NO,Table_ID,Item_Name,PricefrommyUnionVIEWwhereprice>40
13、Pivot行转列
Pivot可以帮助你实现数据行转换成数据列，具体用法如下：
--SimplePivotExampleSELECT*FROMItemMastersPIVOT(SUM(Price)FORITEM_NAMEIN([ChikenBurger],Coffee,Coke))ASPVTTable--PivotwithdetailexampleSELECT*FROM(SELECTITEM_NAME,priceasTotAmountFROMItemMasters)assPIVOT(SUM(TotAmount)FOR[ITEM_NAME]IN([ChikenBurger],[Coffee],[Coke]))ASMyPivot
14、存储过程
我经常看到有人提问如何在SQL Server中编写多条查询的SQL语句，然后将它们使用到C#程序中去。存储过程就可以完成这样的功能，存储过程可以将多个SQL查询聚集在一起，创建存储过程的基本结构是这样的：
CREATEPROCEDURE[ProcedureName]ASBEGIN--SelectorUpdateorInsertquery.ENDToexecuteSPweuseexecProcedureName
创建一个没有参数的存储过程：
--=============================================--Author:Shanu--Createdate:2014-09-15--Description:ToDisplayPivotData--Latest--Modifier:Shanu--Modifydate:2014-09-15--=============================================--execUSP_SelectPivot--=============================================CreatePROCEDURE[dbo].[USP_SelectPivot]ASBEGINDECLARE@MyColumnsASNVARCHAR(MAX),@SQLqueryASNVARCHAR(MAX)--herefirstwegetalltheItemNamewhichshouldbedisplayinColumnsweusethisinournecxtpivotqueryselect@MyColumns=STUFF((SELECT','+QUOTENAME(Item_NAME)FROMItemMastersGROUPBYItem_NAMEORDERBYItem_NAMEFORXMLPATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')--hereweusetheaboveallItemnametodisoplayitspriceascolumnandrowdisplayset@SQLquery=N'SELECT'+@MyColumns+N'from(SELECTITEM_NAME,priceasTotAmountFROMItemMasters)xpivot(SUM(TotAmount)forITEM_NAMEin('+@MyColumns+N'))p'execsp_executesql@SQLquery;RETURNEND
15、函数Function
之前我们介绍了MAX(),SUM(), GetDate()等最基本的SQL函数，现在我们来看看如何创建自定义SQL函数。创建函数的格式如下：
CreateFunctionfunctionNameAsBeginEND
下面是一个简单的函数示例：
AlterFUNCTION[dbo].[ufnSelectitemMaster]()RETURNSintAS--ReturnstotalRowcountofItemMaster.BEGINDECLARE@RowsCountASint;Select@RowsCount=count(*)+1fromItemMastersRETURN@RowsCount;END--toViewFunctionweuseselectandfucntionNameselect[dbo].[ufnSelectitemMaster]()
下面的一个函数可以实现从给定的日期中得到当前月的最后一天：
ALTERFUNCTION[dbo].[ufn_LastDayOfMonth](@DATENVARCHAR(10))RETURNSNVARCHAR(10)ASBEGINRETURNCONVERT(NVARCHAR(10),DATEADD(D,-1,DATEADD(M,1,CAST(SUBSTRING(@DATE,1,7)+'-01'ASDATETIME))),120)ENDSELECTdbo.ufn_LastDayOfMonth('2014-09-01')ASLastDay
上述就是小编为大家分享的初学者必看的基础SQL查询语句分别有哪些了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
