[MSSQL]-个人总结SELECT语句.docx

个人总结SQL的语句虽然 SELECT 语句的完整语法比较复杂，但是大多数 SELECT 语句都描述结果集的四个主要属性1、结果集中的列的数量和属性。2、从中检索结果集数据的表，以及这些表之间的所有逻辑关系。3、为了符合 SELECT 语句的要求，源表中的行所必须达到的条件。不符合条件的行会被忽略。4、结果集的行的排列顺序。它的主要子句可归纳如下：SELECT select_list -描述结果集的列INTO new_table_name -指定使用结果集来创建新表FROM table_list -包含从中检索到结果集数据的表的列表返回结果集的对象。 WHERE search_conditions -WHERE 子句是一个筛选，它定义了源表中的行要满足 SELECT 语句的要求所必须达到的条件 GROUP BY group_by_list -根据 group_by_list 列中的值将结果集分成组 HAVING search_conditions -结果集的附加筛选 ORDER BY order_list ASC | DESC -结果集的附加筛选一、使用选择列表1、使用 *号来选择所有列；使用“表名|别名。字段”选取特定的列。2、AS 子句可用来更改结果集列的名称或为派生列分配名称，也可以使用空格代替如: SELECT Name AS Name1,Name Name2 FROM Product ORDER BY Name ASC3、使用 DISTINCT 消除重复项如：select distinct Year from A4、使用 TOP 和 PERCENT 限制结果集数量TOP ( expression ) PERCENT WITH TIES -expression数量、PERCENT按百分比返回数据、WITH TIES返回排序与最后一行并列的行。如：获取成绩前三名的同学select top 3 * from Score order by Num desc -不考虑成绩并列select top 3 WITH TIES * from Score order by Num desc -可解决成绩并列的问题5、选择列表中的计算值选择的列不但可以包括数据表列，还可以包括计算值，这些结果集列被称为派生列。计算并且包括以下运算：对数值列或常量使用算术运算符或函数进行的计算和运算。如SUM()，COUNT(),AVG()等。数据类型转换.如CAST(ProductID AS VARCHAR(10) 。CASE 函数。 如select ID,name,Case Sex when m then 男 else 女 end from Student-根据SEX的值输出性别信息6、子查询。select ID,name,(Select(sum) from Score S where S.SID=A.ID ) AllScore from Student A-获取学生的基本信息和总成绩。7、使用INTO。 使用INTO 将会把选择的数据插入到指定的表中而不返回数据集。如： select ID,name,(Select(sum) from Score S where S.SID=A.ID ) INTO #T Student-将查询的结果装入临时表T中。二、使用 FROM 子句1、使用表别名SELECT 语句的可读性可通过为表指定别名来提高，别名也称为相关名称或范围变量。分配表别名时，可以使用 AS 关键字，也可以不使用： table_name AS table alias 或 table_name table_alias2、使用 PIVOT 和 UNPIVOT SQL2005有效可以使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。PIVOT示例：SELECT * FROM StuSources pivot(sum(chengji) for kecheng in(语文,数学,历史) as prv -将行转换为列SELECT VendorID, Employee, Orders FROM pvtUNPIVOT(Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)AS unpvt -将列转换为行er3、使用 APPLY 使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 有两种形式： CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。如：-Create Employees table and insert valuesCREATE TABLE Employees(empidintNOT NULL, -员工编号mgridintNULL,-经理编号empname varchar(25) NOT NULL,-姓名salary money NOT NULL,-薪水CONSTRAINT PK_Employees PRIMARY KEY(empid),)GOINSERT INTO Employees VALUES(1 , NULL, Nancy, $10000.00)INSERT INTO Employees VALUES(2 , 1, Andrew , $5000.00)INSERT INTO Employees VALUES(3 , 1, Janet, $5000.00)INSERT INTO Employees VALUES(4 , 1, Margaret, $5000.00) INSERT INTO Employees VALUES(5 , 2, Steven , $2500.00)INSERT INTO Employees VALUES(6 , 2, Michael , $2500.00)INSERT INTO Employees VALUES(7 , 3, Robert , $2500.00)INSERT INTO Employees VALUES(8 , 3, Laura, $2500.00)INSERT INTO Employees VALUES(9 , 3, Ann , $2500.00)INSERT INTO Employees VALUES(10, 4, Ina , $2500.00)INSERT INTO Employees VALUES(11, 7, David, $2000.00)INSERT INTO Employees VALUES(12, 7, Ron , $2000.00)INSERT INTO Employees VALUES(13, 7, Dan , $2000.00)INSERT INTO Employees VALUES(14, 11 , James, $1500.00)GO-Create Departments table and insert valuesCREATE TABLE Departments(deptid INT NOT NULL PRIMARY KEY,deptname VARCHAR(25) NOT NULL,deptmgrid INT NULL REFERENCES Employees)GOINSERT INTO Departments VALUES(1, HR, 2)INSERT INTO Departments VALUES(2, Marketing, 7)INSERT INTO Departments VALUES(3, Finance,8)INSERT INTO Departments VALUES(4, R&D,9)INSERT INTO Departments VALUES(5, Training, 4)INSERT INTO Departments VALUES(6, Gardening, NULL)- 若要返回每个部门经理的所有级别的全部下属，请使用下面的查询：GOCREATE FUNCTION dbo.fn_getsubtree(empid AS INT) RETURNS TREE TABLE(empid INT NOT NULL,empname VARCHAR(25) NOT NULL,mgrid INT NULL,lvl INT NOT NULL)ASBEGIN WITH Employees_Subtree(empid, empname, mgrid, lvl)AS( - Anchor Member (AM)SELECT empid, empname, mgrid, 0FROM employeesWHERE empid = empidUNION all - Recursive Member (RM)SELECT e.empid, e.empname, e.mgrid, es.lvl+1FROM employees AS eJOIN employees_subtree AS esON e.mgrid = es.empid)INSERT INTO TREESELECT * FROM Employees_SubtreeRETURNENDGOSELECT *FROM Departments AS DCROSS APPLY fn_getsubtree(D.deptmgrid) AS ST三、使用WHERE 和 HAVING 筛选行 WHERE 和 HAVING 子句可以控制用于生成结果集的源表中的行。WHERE 和 HAVING 是筛选器。这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用于生成结果集。HAVING 子句通常与 GROUP BY 子句一起使用以筛选聚合值结果。但是 HAVING 也可以在不使用 GROUP BY 的情况下单独指定。HAVING 子句指定在应用 WHERE 子句筛选器后要进一步应用的筛选器。这些筛选器可以应用于 SELECT 列表中所用的聚合函数。1、比较搜索条件。2、范围搜索条件。NOT BETWEEN 范围搜索返回介于两个指定值之间的所有值SELECT * FROM A WHERE Acount Between 10 AND 203、列表搜索条件。IN 关键字使您可以选择与列表中的任意值匹配的行。SELECT ProductID, Name FROM Product WHERE CategoryID IN (12, 14, 16)4、搜索条件中的模式匹配。LIKE 关键字搜索与指定模式匹配的字符串、日期或时间值。% 包含零个或多个字符的任意字符串。_ 任何单个字符。 指定范围（例如 a-f）或集合（例如 abcdef）内的任何单个字符。 不在指定范围（例如 a - f）或集合（例如 abcdef）内的任何单个字符。5、NULL比较搜索条件。NULL比较行为取决于 SET ANSI_NULLS设置，当 SET ANSI_NULLS 为 ON 时，如果比较中有一个或多个表达式为 NULL，则既不输出 TRUE 也不输出 FALSE，而是输出 UNKNOWN。此时需要使用 IS NOT NULL 子句测试 NULL 值。当 ANSI_NULLS 为 OFF 时，如果 ColumnA 包含空值，则比较操作 ColumnA = NULL 返回 TRUE；如果 ColumnA 除包含 NULL 外还包含某些值，则比较操作返回 FALSE。此外，两个都取空值的表达式的比较也输出 TRUE6、所有记录（=ALL、ALL、= ALL、ANY）。select * from A where Amount=Any(select amount from A where Year=2001) and Year20017、逻辑运算符。逻辑运算符包括 AND、OR 和 NOT。逻辑运算符的优先顺序为NOT、AND 和 OR。四、使用 GROUP BY 分组行GROUP BY 子句用来为结果集中的每一行产生聚合值。如果聚合函数没有使用 GROUP BY 子句，则只为 SELECT 语句报告一个聚合值。WHERE 搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。select Year,Sum(AMount) AMount from A Group By Year -按年份分组统计销量五、用 ORDER BY 对行进行排序 ORDER BY 子句按一列或多列（最多 8,060 个字节）对查询结果进行排序。排序可以是升序的 (ASC)，也可以是降序的 (DESC)。如果未指定是升序还是降序，就假定为 ASC。六、子查询子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。1、使用别名的子查询 当表进行自联接或需要引入外表表列与本表列名相同时需要使用表别名2、使用 NOT IN 的子查询 USE AdventureWorks;GOSELECT NameFROM Production.ProductWHERE ProductSubcategoryID IN(SELECT ProductSubcategoryIDFROM Production.ProductSubcategoryWHERE Name = Wheels) 3、UPDATE、DELETE 和 INSERT 语句中的子查询 如：联表更新GO UPDATE Production.ProductSET ListPrice = ListPrice * 2FROM Production.Product AS pINNER JOIN Purchasing.ProductVendor AS pvON p.ProductID = pv.ProductID AND pv.VendorID = 51;4、使用比较运算符的子查询 GOSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID =(SELECT TerritoryIDFROM Sales.SalesPersonWHERE SalesPersonID = 276) 5、使用 NOT EXISTS 的子查询 6、用于替代表达式的子查询 GOSELECT Name, ListPrice, (SELECT AVG(ListPrice) FROM Production.Product) AS Average, ListPrice - (SELECT AVG(ListPrice) FROM Production.Product) AS DifferenceFROM Production.ProductWHERE ProductSubcategoryID = 1七、表联接通过联接，可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。联接条件可通过以下方式定义两个表在查询中的关联方式： 指定每个表中要用于联接的列。典型的联接条件在一个表中指定一个外键，而在另一个表中指定与其关联的键。指定用于比较各列的值的逻辑运算符（例如 = 或 ）。1、内联接INNER JOIN.仅当两个表中都至少有一个行符合联接条件时，内部联接才返回行,内部联接消除了与另一个表中的行不匹配的行.2、外连接 LEFT |RIGHT|FULL OUTER JOIN 外部联接会返回 FROM 子句中提到的至少一个表或视图中的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左外部联接引用的左表中的所有行，以及通过右外部联接引用的右表中的所有行。在完全外部联接中，将返回两个表的所有行其分为：左外部联接，数据列表包括了满足查询条件的左边表的所有行。右外部联接，数据列表包括了满足查询条件的右边表的所有行。完全外部联接，数据包含了所有满足查询条件的列。3、交叉联接CROSS JOIN 返回满足查询条件记录的笛卡尔积运算的集合（NM）。4、自联接表可以通过自联接与自身联接。5、多表联接SELECT p.Name, v.NameFROM Production.Product pJOIN Purchasing.ProductVendor pvON p.ProductID = pv.ProductIDJOIN Purchasing.Vendor vON pv.VendorID = v.VendorIDWHERE ProductSubcategoryID = 15ORDER BY v.Name6、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 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为“定位点成员”。2、例程的递归调用。递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。3、终止检查。 终止检查是隐式的；当上一个调用中未返回行时，递归将停止。示例：go-创建表CREATE TABLE T (id INT identity(1,1), NAME VARCHAR(100), FathID int)-插入测试数据Insert t(Name,FathID) VALUES(A,0) -1Insert t(Name,FathID) VALUES(B,0)Insert t(Name,FathID) VALUES( C,0)Insert t(Name,FathID) VALUES(Aa,1) -4Insert t(Name,FathID) VALUES(Ab,1) -5Insert t(Name,FathID) VALUES(Ac,1)Insert t(Name,FathID) VALUES(Ba,2)Insert t(Name,FathI