MSSQL个人总结SELECT语句Word下载.docx
《MSSQL个人总结SELECT语句Word下载.docx》由会员分享，可在线阅读，更多相关《MSSQL个人总结SELECT语句Word下载.docx（8页珍藏版）》请在冰豆网上搜索。
selecttop3WITHTIES*fromScoreorderbyNumdesc--可解决成绩并列的问题
5、选择列表中的计算值
选择的列不但可以包括数据表列，还可以包括计算值，这些结果集列被称为派生列。
计算并且包括以下运算：
对数值列或常量使用算术运算符或函数进行的计算和运算。
如SUM（），COUNT（）,AVG（）等。
数据类型转换.如CAST（ProductIDASVARCHAR（10））。
CASE函数。
如
selectID,[name],CaseSexwhen'
m'
then'
男'
else'
女'
endfromStudent
--根据SEX的值输出性别信息
6、子查询。
selectID,[name],（Select（sum）fromScoreSwhereS.SID=A.ID）AllScorefromStudentA
--获取学生的基本信息和总成绩。
7、使用INTO。
使用INTO将会把选择的数据插入到指定的表中而不返回数据集。
selectID,[name],（Select（sum）fromScoreSwhereS.SID=A.ID）INTO#T
Student
--将查询的结果装入临时表T中。
二、使用FROM子句
1、使用表别名
SELECT语句的可读性可通过为表指定别名来提高，别名也称为相关名称或范围变量。
分配表别名时，可以使用AS关键字，也可以不使用：
table_nameAStablealias或table_nametable_alias
2、使用PIVOT和UNPIVOT[SQL2005有效]
可以使用PIVOT和UNPIVOT关系运算符对表值表达式进行操作以获得另一个表。
PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。
UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。
PIVOT示例：
SELECT*FROM[StuSources]pivot（sum（chengji）forkecheng
in（[语文],[数学],[历史]））asprv
--将行转换为列
SELECTVendorID,Employee,OrdersFROM
pvtUNPIVOT （OrdersFOREmployeeIN（Emp1,Emp2,Emp3,Emp4,Emp5））ASunpvt--将列转换为行er
3、使用APPLY
使用APPLY运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。
表值函数作为右输入，外部表表达式作为左输入。
通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。
APPLY有两种形式：
CROSSAPPLY和OUTERAPPLY。
CROSSAPPLY仅返回外部表中通过表值函数生成结果集的行。
OUTERAPPLY既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为NULL。
--CreateEmployeestableandinsertvalues
CREATETABLEEmployees
（
empid int NOTNULL,--员工编号
mgrid int NULL,--经理编号
empnamevarchar（25）NOTNULL,--姓名
salary
money NOTNULL,--薪水
CONSTRAINTPK_EmployeesPRIMARYKEY（empid）,
）
GO
INSERTINTOEmployeesVALUES（1,NULL,'
Nancy'
,$10000.00）
INSERTINTOEmployeesVALUES（2,1 ,'
Andrew'
$5000.00）
INSERTINTOEmployeesVALUES（3,1 ,'
Janet'
,$5000.00）
INSERTINTOEmployeesVALUES（4,1 ,'
Margaret'
$5000.00）
INSERTINTOEmployeesVALUES（5,2 ,'
Steven'
$2500.00）
INSERTINTOEmployeesVALUES（6,2 ,'
Michael'
INSERTINTOEmployeesVALUES（7,3 ,'
Robert'
INSERTINTOEmployeesVALUES（8,3 ,'
Laura'
,$2500.00）
INSERTINTOEmployeesVALUES（9,3 ,'
Ann'
INSERTINTOEmployeesVALUES（10,4 ,'
Ina'
INSERTINTOEmployeesVALUES（11,7 ,'
David'
,$2000.00）
INSERTINTOEmployeesVALUES（12,7 ,'
Ron'
$2000.00）
INSERTINTOEmployeesVALUES（13,7 ,'
Dan'
INSERTINTOEmployeesVALUES（14,11
'
James'
,$1500.00）
--CreateDepartmentstableandinsertvalues
CREATETABLEDepartments
deptid INTNOTNULLPRIMARYKEY,
deptname
VARCHAR（25）NOTNULL,
deptmgridINTNULLREFERENCESEmployees
INSERTINTODepartmentsVALUES（1,'
HR'
2）
INSERTINTODepartmentsVALUES（2,'
Marketing'
7）
INSERTINTODepartmentsVALUES（3,'
Finance'
8）
INSERTINTODepartmentsVALUES（4,'
R&
D'
9）
INSERTINTODepartmentsVALUES（5,'
Training'
4）
INSERTINTODepartmentsVALUES（6,'
Gardening'
NULL）
--若要返回每个部门经理的所有级别的全部下属，请使用下面的查询：
CREATEFUNCTIONdbo.fn_getsubtree（@empidASINT）RETURNS@TREETABLE
empidINTNOTNULL,
empnameVARCHAR（25）NOTNULL,
mgridINTNULL,
lvlINTNOTNULL
AS
BEGIN
WITHEmployees_Subtree（empid,empname,mgrid,lvl）
（
--AnchorMember（AM）
SELECTempid,empname,mgrid,0
FROMemployees
WHEREempid=@empid
UNIONall
--RecursiveMember（RM）
SELECTe.empid,e.empname,e.mgrid,es.lvl+1
FROMemployeesASe
JOINemployees_subtreeASes
ONe.mgrid=es.empid
INSERTINTO@TREE
SELECT*FROMEmployees_Subtree
RETURN
END
SELECT*
FROMDepartmentsASD
CROSSAPPLYfn_getsubtree（D.deptmgrid）ASST
三、使用WHERE和HAVING筛选行
WHERE和HAVING子句可以控制用于生成结果集的源表中的行。
WHERE和HAVING是筛选器。
这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用于生成结果集。
HAVING子句通常与GROUPBY子句一起使用以筛选聚合值结果。
但是HAVING也可以在不使用GROUPBY的情况下单独指定。
HAVING子句指定在应用WHERE子句筛选器后要进一步应用的筛选器。
这些筛选器可以应用于SELECT列表中所用的聚合函数。
1、比较搜索条件。
2、范围搜索条件。
[NOT]BETWEEN范围搜索返回介于两个指定值之间的所有值
SELECT*FROMAWHEREAcountBetween10AND20
3、列表搜索条件。
IN关键字使您可以选择与列表中的任意值匹配的行。
SELECTProductID,[Name]FROM
ProductWHERE
CategoryIDIN（12,14,16）
4、搜索条件中的模式匹配。
LIKE关键字搜索与指定模式匹配的字符串、日期或时间值。
%包含零个或多个字符的任意字符串。
_任何单个字符。
[]指定范围（例如[a-f]）或集合（例如[abcdef]）内的任何单个字符。
[^]不在指定范围（例如[^a-f]）或集合（例如[^abcdef]）内的任何单个字符。
5、NULL比较搜索条件。
NULL比较行为取决于SETANSI_NULLS设置，当SETANSI_NULLS为ON时，如果比较中有一个或多个表达式为NULL，则既不输出TRUE也不输出FALSE，而是输出UNKNOWN。
此时需要使用IS[NOT]NULL子句测试NULL值。
当ANSI_NULLS为OFF时，如果ColumnA包含空值，则比较操作ColumnA=NULL返回TRUE；
如果ColumnA除包含NULL外还包含某些值，则比较操作返回FALSE。
此外，两个都取空值的表达式的比较也输出TRUE
6、所有记录（=ALL、>
ALL、<
=ALL、ANY）。
select*fromAwhereAmount=Any（selectamountfromAwhere[Year]=2001）and[Year]<
>
2001
7、逻辑运算符。
逻辑运算符包括AND、OR和NOT。
逻辑运算符的优先顺序为NOT、AND和OR。
四、使用GROUPBY分组行
GROUPBY子句用来为结果集中的每一行产生聚合值。
如果聚合函数没有使用GROUPBY子句，则只为SELECT语句报告一个聚合值。
WHERE搜索条件在进行分组操作之前应用；
而HAVING搜索条件在进行分组操作之后应用。
HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数。
HAVING子句可以引用选择列表中显示的任意项。
select[Year],Sum（AMount）AMountfromAGroupBy[Year]--按年份分组统计销量
五、用ORDERBY对行进行排序
ORDERBY子句按一列或多列（最多8,060个字节）对查询结果进行排序。
排序可以是升序的（ASC），也可以是降序的（DESC）。
如果未指定是升序还是降序，就假定为ASC。
六、子查询
子查询是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询。
任何允许使用表达式的地方都可以使用子查询。
子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。
1、使用别名的子查询
当表进行自联接或需要引入外表表列与本表列名相同时需要使用表别名
2、使用[NOT]IN的子查询
USEAdventureWorks;
SELECTName
FROMProduction.Product
WHEREProductSubcategoryIDIN
（SELECTProductSubcategoryID
FROMProduction.ProductSubcategory
WHEREName='
Wheels'
）
3、UPDATE、DELETE和INSERT语句中的子查询
联表更新
GO
UPDATEProduction.Product
SETListPrice=ListPrice*2
FROMProduction.ProductASp
INNERJOINPurchasing.ProductVendorASpv
ONp.ProductID=pv.ProductIDANDpv.VendorID=51;
4、使用比较运算符的子查询
SELECTCustomerID
FROMSales.Customer
WHERETerritoryID=
（SELECTTerritoryID
FROMSales.SalesPerson
WHERESalesPersonID=276）
5、使用[NOT]EXISTS的子查询
6、用于替代表达式的子查询
SELECTName,ListPrice,
（SELECTAVG（ListPrice）FROMProduction.Product）ASAverage,
ListPrice-（SELECTAVG（ListPrice）FROMProduction.Product）ASDifference
WHEREProductSubcategoryID=1
七、表联接
通过联接，可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。
联接条件可通过以下方式定义两个表在查询中的关联方式：
指定每个表中要用于联接的列。
典型的联接条件在一个表中指定一个外键，而在另一个表中指定与其关联的键。
指定用于比较各列的值的逻辑运算符（例如=或<
）。
1、内联接[INNER]JOIN.
仅当两个表中都至少有一个行符合联接条件时，内部联接才返回行,内部联接消除了与另一个表中的行不匹配的行.
2、外连接{LEFT|RIGHT|FULL}OUTERJOIN
外部联接会返回FROM子句中提到的至少一个表或视图中的所有行，只要这些行符合任何WHERE或HAVING搜索条件。
将检索通过左外部联接引用的左表中的所有行，以及通过右外部联接引用的右表中的所有行。
在完全外部联接中，将返回两个表的所有行
其分为：
左外部联接，数据列表包括了满足查询条件的左边表的所有行。
右外部联接，数据列表包括了满足查询条件的右边表的所有行。
完全外部联接，数据包含了所有满足查询条件的列。
3、交叉联接CROSSJOIN
返回满足查询条件记录的笛卡尔积运算的集合（N×
M）。
4、自联接
表可以通过自联接与自身联接。
5、多表联接
SELECTp.Name,v.Name
FROMProduction.Productp
JOINPurchasing.ProductVendorpv
ONp.ProductID=pv.ProductID
JOINPurchasing.Vendorv
ONpv.VendorID=v.VendorID
WHEREProductSubcategoryID=15
ORDERBYv.Name
6、NULL和联接
联接表的列中的空值（如果有）互相不匹配。
如果其中一个联接表的列中出现空值，只能通过外部联接返回这些空值（除非WHERE子句不包括空值）。
八、结果集的操作
1、[ALL]UNION运算符组合结果集。
UNION运算符使您得以将两个或多个SELECT语句的结果组合成一个结果集。
使用UNION运算符组合的结果集都必须具有相同的结构。
而且它们的列数必须相同，并且相应的结果集列的数据类型必须兼容。
默认情况下，UNION运算符将从结果集中删除重复的行。
如果使用ALL关键字，那么结果中将包含所有行而不删除重复的行。
2、EXCEPT和INTERSECT执行半联接操作
EXCEPT和INTERSECT运算符使您可以比较两个或多个SELECT语句的结果并返回非重复值。
EXCEPT运算符返回由EXCEPT运算符左侧的查询返回、而又不包含在右侧查询所返回的值中的所有非重复值。
INTERSECT返回由INTERSECT运算符左侧和右侧的查询都返回的所有非重复值。
使用EXCEPT或INTERSECT比较的结果集必须具有相同的结构。
3、公用表表达式（CTE）递归查询
公用表表达式（CTE）具有一个重要的优点，那就是能够引用其自身，从而创建递归CTE。
递归CTE是一个重复执行初始CTE以返回数据子集直到获取完整结果集的公用表表达式。
递归CTE由下列三个元素组成：
1、例程的调用：
递归CTE的第一个调用包括一个或多个由UNIONALL、UNION、EXCEPT或INTERSECT运算符联接的CTE_query_definitions。
由于这些查询定义形成了CTE结构的基准结果集，所以它们被称为“定位点成员”。
2、例程的递归调用。
递归调用包括一个或多个由引用CTE本身的UNIONALL运算符联接的CTE_query_definitions。
这些查询定义被称为“递归成员”。
3、终止检查。
终止检查是隐式的；
当上一个调用中未返回行时，递归将停止。
示例：
go
--创建表
CREATE
TABLET（idINT
identity（1,1）,[NAME]VARCHAR（100）,FathIDint）
--插入测试数据
Insertt（[Name],FathID）VALUES（'
A'
0）--1
B'
0）
Insertt（[Name],FathID）VALUES（'
C'
Aa'
1）--4
Ab'
1）--5
升级会员 