最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

天天实时：T-SQL基础教程Day2

来源：博客园

单表查询2.1 SELECT语句的元素SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numordersFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate)HAVING COUNT(*) > 1ORDER BY empid, orderyear;1 FROM 从Sales.Orders表查询行2 WHERE 仅筛选客户ID等于71的订单3 GROUP BY按雇员ID和订单年度对订单分组4 HAVING 仅筛选出大于1个订单的组5 SELECT 返回每组的雇员ID、订单年度和订单数量6 ORDER BY 按雇员ID和订单年度排序输出行

2.1.1 FROM子句FROM子句是逻辑化处理的第一个查询子句，此子句指定要查询的表名称和进行多表运算的表运算符。FROM Sales.Orders建议：要在代码中始终使用架构限定式的对象名称。如果不显示制定架构名称，SQL Server必须基于其隐式名称解析规则来确定所归属的架构，这造成了一些不必要的额外支出，并且会导致SQL Server选择不同的对象，而不是所期望的对象。


(资料图片)

2.1.2 WHERE子句可以指定一个谓词或逻辑表达式来筛选由From阶段返回的行。在谈到查询性能时，WHERE子句具有重要意义。基于筛选表达式，SQL Server将评估访问请求数据要使用的索引。通过使用索引，相比全表扫描，SQL Server有时可以用更少的工作获得所需数据。T-SQL使用三值谓词逻辑“返回TRUE”并不等同于“不返回FALSE”，还有UNKNOWN部分。

2.1.3 GROUP BY子句GROUP BY阶段允许把前面逻辑查询阶段返回的行排列到组中，组是根据GROUP BY子句中指定的元素而确定的。如果查询涉及分组，那么GROUP BY阶段的所有后续阶段，包括HAVING、SELECT、ORDER BY都是对组的操作，而不是对单个行进行操作。不参与到GROUP BY列表中的元素仅允许作为一个聚合函数的输入，如COUNT、SUM、AVG、MIN或MAX。注意，除了COUNT（*）之外，所有聚合函数忽略NULL标记。

2.1.4 HAVING子句可以指定一个谓词来筛选组，而不是筛选单个行。只有HAVING子句中逻辑表达式计算结果为TRUE的组，由HAVING阶段返回到下一个逻辑查询处理阶段。逻辑表达式计算结果为FALSE或UNKNOWN的组会被筛选掉。由于HAVING子句是在行分组后被处理，所以可以在逻辑表达式中引用集合函数。

2.1.5 SELECT子句SELECT子句是用户指定要返回到查询结果表中的属性（列）的地方。用户可基于所查询表的属性，在SELECT列表中建立表达式。SELECT子句是在FROM、WHERE、GROUP BY和HAVING子句之后处理的，这意味着SELECT子句中分配给表达式的别名，不会存在于之前的SELECT相关子句中。例如以下语句是错误的SELECT orderid, YEAR(orderdate) AS orderyearFROM Sales.OrdersWHERE orderyear > 2006;SELECT语句保持唯一性的方法，是加上DISTINCT子句，删除重复行。

2.1.6 ORDER BY子句出于展示效果的考虑，ORDER BY子句允许你对输出行进行排序。理解SQL的最重要一点是表中没有确定的顺序，因为表是被假定为表示一个集合（或是多重集合，如果有重复数据的话），并且集合是没有顺序的。这意味着在查询表时没有制定ORDER BY子句，查询将返回一个表结果，并且SQL Server可以按任意顺序自由返回输出行。标准SQL中把具有ORDER BY子句的结果称为游标——一个具有确定行顺序的非关系型结果。返回表结果或是游标的差异：在一些语言元素或运算符需要操作表结果，而不是游标。例如：表表达式，集合运算符等。

2.1.7 TOP和OFFSET-FETCH筛选1 TOP筛选TOP选项是一个专有的T-SQL功能，用于限制查询返回的行数或行的百分比。SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;2 OFFSET-FETCH筛选TOP选项不是标准SQL，且不支持跳过功能，OFFSET-FETCH是标准SQL，SQL Server2012时引入。SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate, orderidOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

2.1.8 开窗函数速览开窗函数的功能是：对于基本查询中的每一行，按行的窗口（组）进行运算，并计算一个标量结果值。行的窗口使用OVER子句定义。例子：SELECT orderid, custid, val, ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val) AS rownumFROM Sales.OrderValuesORDER BY custid, val;

2.2谓词和运算符谓词是计算为TRUE、FALSE或UNKNOWN的逻辑表达式。可以使用逻辑运算符来连接谓词，如AND、NOT和OR。T-SQL支持的谓词，例如IN、BETWEEN、LIKE等。T-SQL支持的比较运算符包括=、>、<、>=、<=、<>、!=、!>、!<。最后三个运算符不是标准的。T-SQL支持的算术运算符：+、-、*、/、以及取模%以下列出了运算符的优先级（从最高到最低）1 （）2 *、/、%3 +（正号）-（符号）+（加号）+（串联）-（减号）4 =、>、<、>=、<=、<>、!=、！>、!<5 NOT6 AND7 BETWEEN、IN、LIKE、OR8 =（赋值）

2.3 CASE表达式CASE表达式是一个标量表达式，返回一个基于条件逻辑的值。需要注意的是，CASE是表达式而不是语句，它不允许你控制活动流或做一些基于条件逻辑的操作。不过，它的返回值缺失基于条件逻辑的。CASE表达式具有“简单”和“搜索”两种格式。简单格式允许在一个可能值列表中比较一个值或标量表达式，并返回第一个匹配值。如果无匹配值，则返回ELSE子句中的值或NULL。CASE搜索格式更加灵活，允许你再WHEN子句中指定谓词或逻辑表达式，二不是限制于进行相等比较。CASE表达式返回第一个WHEN逻辑表达式计算结果为TRUE的相关联THEN子句中的值。如果没有WHEN表达式计算结果为TRUE，则返回ELSE子句（如有）中的值或NULL。

2.4 NULL标记SQL不同的语言元素对于UNKNOWN有不同的处理方式。对于查询筛选而言，SQL的正确处理定义是“接受TRUE”，意味着FALSE和UNKNOWN都会被筛选掉。对于CHECK约束而言，SQL的正确处理定义是“拒绝FALSE”，意味着TRUE和UNKNOWN会被接受。UNKNOWN，当否定它时，仍然会得到UNKNOWN值。比较两个NULL标记的表达式（NULL=NULL）计算为UNKNOWN。SQL提供了谓词IS NULL和IS NOT NULL来解决比较问题。对于分组和排序目的，两个NULL标记被视为相等。即GROUP BY子句将所有NULL标记排列为一组。ORDER BY子句也将所有NULL标记排序在一起。

2.5 同时操作SQL支持一个称作同时操作（all at once operations）的概念，即出现在同一逻辑处理阶段的所有表达式在同一时间点进行逻辑计算。select 1 as a, 2 as b into #allatonceupdate #allatonce set a=b,b=aselect * from #allatonce

2.6.1 数据类型SQL Server支持两种字符数据类型——常规和Unicode。常规数据类型包括CHAR和VARCHAR，Unicode数据类型包括NCHAR和NVARCHAR。常规字符的每个字符使用一个字节，而Unicode数据的每个字符要求2个字节，并且需要一个代理项对时，要求4个字节。（代理项（Surrogate），是一种仅在 UTF-16 中用来表示补充字符的方法。在 UTF-16 中，为补充字符分配两个 16 位的 Unicode 代码单元：第一个代码单元，被称为高代理项代码单元或前导代码单元；第二个代码单元，被称为低代理项代码单元或尾随代码单元。这两个代码单元组合在一起，就被称为代理项对。）在表示常规字符文本时，只需使用单引号，表示Unicode字符文本时，需要指定字符N（即National）作为前缀。当使用MAX说明符来定义可变长度数据类型时，当大小在8000内时，可以内置存储在行内，超过时，作为大型对象（LOB）存储在行外部。

2.6.2 排序规则排序规则是一个字符数据属性，其封装了多项内容，包括语言支持、排序顺序、区分大小写、区分重音等。CI数据不区分大小写AS数据区分重音数据库的排序规则决定了数据库对象元数据的排序规则，包括对象和列名。如果区分大小写，则可以创建名为t1和T1的两个表。

2.6.3 运算符和函数1 字符串连接（加号运算符和CONCAT函数）2 SUBSTRING函数3 LEFT和RIGHT函数4 LEN和DATALENGTH函数5 CHARINDEX函数6 PATINDEX函数7 REPLACE函数8 REPLICATE函数9 STUFF函数10 UPPER和LOWER函数11 RTRIM和LTRIM函数12 FORMAT函数13 COMPRESS和DECOMPRESS函数14 STRING_SPLIT函数15 LIKE谓词

2.7.2 日期和时间常量SQL Server不同日期和时间的常量表示方法，相反，它允许用户指定可以被显式或隐式转换为日期和时间数据类型的不同类型常量。使用字符串表示日期和时间值是最好的做法。

2.7.3 独立使用日期和时间SQL Server 2008引入了独立的DATE和TIME数据类型。

2.7.5 日期和时间函数1 当前日期和时间2 CAST、CONVERT和PARSE函数，及其TRY_对应函数3 SWITCHOFFSET函数4 TODATETIMEOFFSET函数5 DATEADD函数6 DATEDIFF函数7 DATEPART函数8 YEAR、MONTH和DAY函数9 DATENAME函数10 ISDATE函数11 FROMPARTS函数12 EOMONTH函数

2.8.1 目录视图目录视图为数据库中的对象提供了非常详细的信息。如果想列出数据库中的表和架构，可以查询sys.tables视图。SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_nameFROM sys.tables;要获取表中列的信息，可以查询sys.columns表。SELECT name AS column_name, TYPE_NAME(system_type_id) AS column_type, max_length, collation_name, is_nullableFROM sys.columnsWHERE object_id = OBJECT_ID(N"Sales.Orders");

2.8.2 信息结构视图信息架构视图是一个视图集合，位于名为INFORMATION_SCHEMA的架构中，并以标准方式提供元数据信息。SELECT TABLE_SCHEMA, TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = N"BASE TABLE";

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, IS_NULLABLEFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA = N"Sales" AND TABLE_NAME = N"Orders";

2.8.3 系统存储过程和函数系统存储过程和函数用来内部查询系统目录，有整理后的元数据信息。EXEC sys.sp_tables;

EXEC sys.sp_help @objname = N"Sales.Orders";

EXEC sys.sp_columns @table_name = N"Orders", @table_owner = N"Sales";

EXEC sys.sp_helpconstraint @objname = N"Sales.Orders";

SELECT SERVERPROPERTY("ProductLevel");

SELECT DATABASEPROPERTYEX(N"TSQL2012", "Collation");

SELECT OBJECTPROPERTY(OBJECT_ID(N"Sales.Orders"), "TableHasPrimaryKey");

SELECT COLUMNPROPERTY(OBJECT_ID(N"Sales.Orders"), N"shipcountry", "AllowsNull");

章节代码

----------------------------------------------------------------------- Microsoft SQL Server 2012 T-SQL Fundamentals-- Chapter 02 - Single-Table Queries-- ?Itzik Ben-Gan -------------------------------------------------------------------------------------------------------------------------------------------- Elements of the SELECT Statement----------------------------------------------------------------------- Listing 2-1: Sample QueryUSE TSQL2012;SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numordersFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate)HAVING COUNT(*) > 1ORDER BY empid, orderyear;----------------------------------------------------------------------- The FROM Clause---------------------------------------------------------------------SELECT orderid, custid, empid, orderdate, freightFROM Sales.Orders;----------------------------------------------------------------------- The WHERE Clause---------------------------------------------------------------------SELECT orderid, empid, orderdate, freightFROM Sales.OrdersWHERE custid = 71;----------------------------------------------------------------------- The GROUP BY Clause---------------------------------------------------------------------SELECT empid, YEAR(orderdate) AS orderyearFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate);SELECT  empid,  YEAR(orderdate) AS orderyear,  SUM(freight) AS totalfreight,  COUNT(*) AS numordersFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate);/*SELECT empid, YEAR(orderdate) AS orderyear, freightFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate);*/SELECT   empid,   YEAR(orderdate) AS orderyear,   COUNT(DISTINCT custid) AS numcustsFROM Sales.OrdersGROUP BY empid, YEAR(orderdate);----------------------------------------------------------------------- The HAVING Clause---------------------------------------------------------------------SELECT empid, YEAR(orderdate) AS orderyearFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate)HAVING COUNT(*) > 1;----------------------------------------------------------------------- The SELECT Clause---------------------------------------------------------------------SELECT orderid orderdateFROM Sales.Orders;SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numordersFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate)HAVING COUNT(*) > 1;/*SELECT orderid, YEAR(orderdate) AS orderyearFROM Sales.OrdersWHERE orderyear > 2006;*/SELECT orderid, YEAR(orderdate) AS orderyearFROM Sales.OrdersWHERE YEAR(orderdate) > 2006;/*SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numordersFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate)HAVING numorders > 1;*/SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numordersFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate)HAVING COUNT(*) > 1;-- Listing 2-2: Query Returning Duplicate RowsSELECT empid, YEAR(orderdate) AS orderyearFROM Sales.OrdersWHERE custid = 71;-- Listing 2-3: Query With a DISTINCT ClauseSELECT DISTINCT empid, YEAR(orderdate) AS orderyearFROM Sales.OrdersWHERE custid = 71;SELECT *FROM Sales.Shippers;/*SELECT orderid,  YEAR(orderdate) AS orderyear,  orderyear + 1 AS nextyearFROM Sales.Orders;*/SELECT orderid,  YEAR(orderdate) AS orderyear,  YEAR(orderdate) + 1 AS nextyearFROM Sales.Orders;----------------------------------------------------------------------- The ORDER BY Clause----------------------------------------------------------------------- Listing 2-4: Query Demonstrating the ORDER BY ClauseSELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numordersFROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate)HAVING COUNT(*) > 1ORDER BY empid, orderyear;SELECT empid, firstname, lastname, countryFROM HR.EmployeesORDER BY hiredate;/*SELECT DISTINCT countryFROM HR.EmployeesORDER BY empid;*/----------------------------------------------------------------------- The TOP and OFFSET-FETCH Filters-------------------------------------------------------------------------------------------------------------------------------------------- The TOP Filter----------------------------------------------------------------------- Listing 2-5: Query Demonstrating the TOP OptionSELECT TOP (5) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;-- Listing 2-6: Query Demonstrating TOP with Unique ORDER BY ListSELECT TOP (5) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;SELECT TOP (5) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;----------------------------------------------------------------------- The OFFSET-FETCH Filter----------------------------------------------------------------------- OFFSET-FETCHSELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate, orderidOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;----------------------------------------------------------------------- A Quick Look at Window Functions---------------------------------------------------------------------SELECT orderid, custid, val,  ROW_NUMBER() OVER(PARTITION BY custid                    ORDER BY val) AS rownumFROM Sales.OrderValuesORDER BY custid, val;----------------------------------------------------------------------- Predicates and Operators----------------------------------------------------------------------- Predicates: IN, BETWEEN, LIKESELECT orderid, empid, orderdateFROM Sales.OrdersWHERE orderid IN(10248, 10249, 10250);SELECT orderid, empid, orderdateFROM Sales.OrdersWHERE orderid BETWEEN 10300 AND 10310;SELECT empid, firstname, lastnameFROM HR.EmployeesWHERE lastname LIKE N"D%";-- Comparison operators: =, >, <, >=, <=, <>, !=, !>, !< SELECT orderid, empid, orderdateFROM Sales.OrdersWHERE orderdate >= "20080101";-- Logical operators: AND, OR, NOTSELECT orderid, empid, orderdateFROM Sales.OrdersWHERE orderdate >= "20080101"  AND empid IN(1, 3, 5);-- Arithmetic operators: +, -, *, /, %SELECT orderid, productid, qty, unitprice, discount,  qty * unitprice * (1 - discount) AS valFROM Sales.OrderDetails;-- Operators Precedence-- AND precedes ORSELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE        custid = 1    AND empid IN(1, 3, 5)    OR  custid = 85    AND empid IN(2, 4, 6);-- Equivalent toSELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE      ( custid = 1        AND empid IN(1, 3, 5) )    OR      ( custid = 85        AND empid IN(2, 4, 6) );-- *, / precedes +, -SELECT 10 + 2 * 3   -- 16SELECT (10 + 2) * 3 -- 36----------------------------------------------------------------------- CASE Expression----------------------------------------------------------------------- SimpleSELECT productid, productname, categoryid,  CASE categoryid    WHEN 1 THEN "Beverages"    WHEN 2 THEN "Condiments"    WHEN 3 THEN "Confections"    WHEN 4 THEN "Dairy Products"    WHEN 5 THEN "Grains/Cereals"    WHEN 6 THEN "Meat/Poultry"    WHEN 7 THEN "Produce"    WHEN 8 THEN "Seafood"    ELSE "Unknown Category"  END AS categorynameFROM Production.Products;-- SearchedSELECT orderid, custid, val,  CASE     WHEN val < 1000.00                   THEN "Less than 1000"    WHEN val BETWEEN 1000.00 AND 3000.00 THEN "Between 1000 and 3000"    WHEN val > 3000.00                   THEN "More than 3000"    ELSE "Unknown"  END AS valuecategoryFROM Sales.OrderValues;----------------------------------------------------------------------- NULLs---------------------------------------------------------------------SELECT custid, country, region, cityFROM Sales.CustomersWHERE region = N"WA";SELECT custid, country, region, cityFROM Sales.CustomersWHERE region <> N"WA";SELECT custid, country, region, cityFROM Sales.CustomersWHERE region = NULL;SELECT custid, country, region, cityFROM Sales.CustomersWHERE region IS NULL;SELECT custid, country, region, cityFROM Sales.CustomersWHERE region <> N"WA"   OR region IS NULL;----------------------------------------------------------------------- All-At-Once Operations---------------------------------------------------------------------/*SELECT   orderid,   YEAR(orderdate) AS orderyear,   orderyear + 1 AS nextyearFROM Sales.Orders;*//*SELECT col1, col2FROM dbo.T1WHERE col1 <> 0 AND col2/col1 > 2;*//*SELECT col1, col2FROM dbo.T1WHERE  CASE    WHEN col1 = 0 THEN "no" -- or "yes" if row should be returned    WHEN col2/col1 > 2 THEN "yes"    ELSE "no"  END = "yes";*//*SELECT col1, col2FROM dbo.T1WHERE (col1 > 0 AND col2 > 2*col1) OR (col1 < 0 AND col2 < 2*col1); */select 1 as a, 2 as b into #allatonceupdate #allatonce set a=b,b=aselect * from #allatonce----------------------------------------------------------------------- Working with Character Data-------------------------------------------------------------------------------------------------------------------------------------------- Collation---------------------------------------------------------------------SELECT name, descriptionFROM sys.fn_helpcollations();SELECT empid, firstname, lastnameFROM HR.EmployeesWHERE lastname = N"davis";SELECT empid, firstname, lastnameFROM HR.EmployeesWHERE lastname COLLATE Latin1_General_CS_AS = N"davis";----------------------------------------------------------------------- Operators and Functions----------------------------------------------------------------------- ConcatenationSELECT empid, firstname + N" " + lastname AS fullnameFROM HR.Employees;-- Listing 2-7: Query Demonstrating String ConcatenationSELECT custid, country, region, city,  country + N"," + region + N"," + city AS locationFROM Sales.Customers;-- convert NULL to empty stringSELECT custid, country, region, city,  country + COALESCE( N"," + region, N"") + N"," + city AS locationFROM Sales.Customers;-- using CONCAT (2012-only)SELECT custid, country, region, city,  CONCAT(country, N"," + region, N"," + city) AS locationFROM Sales.Customers;-- FunctionsSELECT SUBSTRING("abcde", 1, 3); -- "abc"SELECT RIGHT("abcde", 3); -- "cde"SELECT LEN(N"abcde"); -- 5SELECT DATALENGTH(N"abcde"); -- 10SELECT CHARINDEX(" ","Itzik Ben-Gan"); -- 6SELECT PATINDEX("%[0-9]%", "abcd123efgh"); -- 5SELECT REPLACE("1-a 2-b", "-", ":"); -- "1:a 2:b"SELECT empid, lastname,  LEN(lastname) - LEN(REPLACE(lastname, "e", "")) AS numoccurFROM HR.Employees;SELECT REPLICATE("abc", 3); -- "abcabcabc"SELECT supplierid,  RIGHT(REPLICATE("0", 9) + CAST(supplierid AS VARCHAR(10)),        10) AS strsupplieridFROM Production.Suppliers;SELECT STUFF("xyz", 2, 1, "abc"); -- "xabcz"SELECT UPPER("Itzik Ben-Gan"); -- "ITZIK BEN-GAN"SELECT LOWER("Itzik Ben-Gan"); -- "itzik ben-gan"SELECT RTRIM(LTRIM("   abc   ")); -- "abc"SELECT FORMAT(1759, "0000000000"); -- "0000001759"SELECT COMPRESS("abcd") as CompSELECTCAST(DECOMPRESS(COMPRESS(N"abcd"))AS NVARCHAR(MAX));SELECTCAST(COMPRESS(N"abcd")AS NVARCHAR(MAX));SELECT CAST(value AS INT) AS myvalueFROM STRING_SPLIT("10248,10249,10250", ",") AS S;----------------------------------------------------------------------- LIKE Predicate----------------------------------------------------------------------- Last name starts with DSELECT empid, lastnameFROM HR.EmployeesWHERE lastname LIKE N"D%";-- Second character in last name is eSELECT empid, lastnameFROM HR.EmployeesWHERE lastname LIKE N"_e%";-- First character in last name is A, B or CSELECT empid, lastnameFROM HR.EmployeesWHERE lastname LIKE N"[ABC]%";-- First character in last name is A through ESELECT empid, lastnameFROM HR.EmployeesWHERE lastname LIKE N"[A-E]%";-- First character in last name is not A through ESELECT empid, lastnameFROM HR.EmployeesWHERE lastname LIKE N"[^A-E]%";----------------------------------------------------------------------- Working with Date and Time Data----------------------------------------------------------------------- LiteralsSELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE orderdate = "20070212";SELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE orderdate = CAST("20070212" AS DATETIME);SET LANGUAGE British;SELECT CAST("02/12/2007" AS DATETIME);SET LANGUAGE us_english;SELECT CAST("02/12/2007" AS DATETIME);SET LANGUAGE British;SELECT CAST("20070212" AS DATETIME);SET LANGUAGE us_english;SELECT CAST("20070212" AS DATETIME);SELECT CONVERT(DATETIME, "02/12/2007", 101);SELECT CONVERT(DATETIME, "02/12/2007", 103);SELECT PARSE("02/12/2007" AS DATETIME USING "en-US");SELECT PARSE("02/12/2007" AS DATETIME USING "en-GB");-- Working with Date and Time SeparatelySELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE orderdate = "20070212";SELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE orderdate >= "20070212"  AND orderdate < "20070213";SELECT CAST("12:30:15.123" AS DATETIME);SELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE YEAR(orderdate) = 2007;SELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE orderdate >= "20070101" AND orderdate < "20080101";SELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;SELECT orderid, custid, empid, orderdateFROM Sales.OrdersWHERE orderdate >= "20070201" AND orderdate < "20070301";-- Functions-- Current Date and TimeSELECT  GETDATE()           AS [GETDATE],  CURRENT_TIMESTAMP   AS [CURRENT_TIMESTAMP],  GETUTCDATE()        AS [GETUTCDATE],  SYSDATETIME()       AS [SYSDATETIME],  SYSUTCDATETIME()    AS [SYSUTCDATETIME],  SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET];SELECT  CAST(SYSDATETIME() AS DATE) AS [current_date],  CAST(SYSDATETIME() AS TIME) AS [current_time];-- The CAST, CONVERT and PARSE Functions and their TRY_ CounterpartsSELECT CAST("20090212" AS DATE);SELECT CAST(SYSDATETIME() AS DATE);SELECT CAST(SYSDATETIME() AS TIME);SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112);SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS DATETIME);SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114);SELECT CAST(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) AS DATETIME);SELECT PARSE("02/12/2007" AS DATETIME USING "en-US");SELECT PARSE("02/12/2007" AS DATETIME USING "en-GB");-- SWITCHOFFSETSELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), "-05:00");SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), "+00:00");-- TODATETIMEOFFSET/*UPDATE dbo.T1  SET dto = TODATETIMEOFFSET(dt, theoffset);*/-- DATEADDSELECT DATEADD(year, 1, "20090212");-- DATEDIFFSELECT DATEDIFF(day, "20080212", "20090212");SELECT  DATEADD(    day,     DATEDIFF(day, "20010101", CURRENT_TIMESTAMP), "20010101");SELECT  DATEADD(    month,     DATEDIFF(month, "20010101", CURRENT_TIMESTAMP), "20010101");SELECT  DATEADD(    month,     DATEDIFF(month, "20091231", CURRENT_TIMESTAMP), "20091231");-- DATEPARTSELECT DATEPART(month, "20090212");-- DAY, MONTH, YEARSELECT  DAY("20090212") AS theday,  MONTH("20090212") AS themonth,  YEAR("20090212") AS theyear;-- DATENAMESELECT DATENAME(month, "20090212");SELECT DATENAME(year, "20090212");-- ISDATESELECT ISDATE("20090212");SELECT ISDATE("20090230");-- frompartsSELECT  DATEFROMPARTS(2012, 02, 12),  DATETIME2FROMPARTS(2012, 02, 12, 13, 30, 5, 1, 7),  DATETIMEFROMPARTS(2012, 02, 12, 13, 30, 5, 997),  DATETIMEOFFSETFROMPARTS(2012, 02, 12, 13, 30, 5, 1, -8, 0, 7),  SMALLDATETIMEFROMPARTS(2012, 02, 12, 13, 30),  TIMEFROMPARTS(13, 30, 5, 1, 7);-- EOMONTHSELECT EOMONTH(SYSDATETIME());-- orders placed on last day of monthSELECT orderid, orderdate, custid, empidFROM Sales.OrdersWHERE orderdate = EOMONTH(orderdate);----------------------------------------------------------------------- Querying Metadata----------------------------------------------------------------------- Catalog ViewsUSE TSQL2012;SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_nameFROM sys.tables;SELECT   name AS column_name,  TYPE_NAME(system_type_id) AS column_type,  max_length,  collation_name,  is_nullableFROM sys.columnsWHERE object_id = OBJECT_ID(N"Sales.Orders");-- Information Schema ViewsSELECT TABLE_SCHEMA, TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = N"BASE TABLE";SELECT   COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,   COLLATION_NAME, IS_NULLABLEFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA = N"Sales"  AND TABLE_NAME = N"Orders";-- System Stored Procedures and FunctionsEXEC sys.sp_tables;EXEC sys.sp_help  @objname = N"Sales.Orders";EXEC sys.sp_columns  @table_name = N"Orders",  @table_owner = N"Sales";EXEC sys.sp_helpconstraint  @objname = N"Sales.Orders";SELECT   SERVERPROPERTY("ProductLevel");SELECT  DATABASEPROPERTYEX(N"TSQL2012", "Collation");SELECT   OBJECTPROPERTY(OBJECT_ID(N"Sales.Orders"), "TableHasPrimaryKey");SELECT  COLUMNPROPERTY(OBJECT_ID(N"Sales.Orders"), N"shipcountry", "AllowsNull");

练习代码

----------------------------------------------------------------------- Microsoft SQL Server 2012 T-SQL Fundamentals-- Chapter 02 - Single-Table Queries-- Solutions-- ?Itzik Ben-Gan ----------------------------------------------------------------------- 1 -- Return orders placed on June 2007-- Tables involved: TSQL2012 database, Sales.Orders table-- Desired output:orderid     orderdate               custid      empid----------- ----------------------- ----------- -----------10555       2007-06-02 00:00:00.000 71          610556       2007-06-03 00:00:00.000 73          210557       2007-06-03 00:00:00.000 44          910558       2007-06-04 00:00:00.000 4           110559       2007-06-05 00:00:00.000 7           610560       2007-06-06 00:00:00.000 25          810561       2007-06-06 00:00:00.000 24          210562       2007-06-09 00:00:00.000 66          110563       2007-06-10 00:00:00.000 67          210564       2007-06-10 00:00:00.000 65          410565       2007-06-11 00:00:00.000 51          810566       2007-06-12 00:00:00.000 7           910567       2007-06-12 00:00:00.000 37          110568       2007-06-13 00:00:00.000 29          310569       2007-06-16 00:00:00.000 65          510570       2007-06-17 00:00:00.000 51          310571       2007-06-17 00:00:00.000 20          810572       2007-06-18 00:00:00.000 5           310573       2007-06-19 00:00:00.000 3           710574       2007-06-19 00:00:00.000 82          410575       2007-06-20 00:00:00.000 52          510576       2007-06-23 00:00:00.000 80          310577       2007-06-23 00:00:00.000 82          910578       2007-06-24 00:00:00.000 11          410579       2007-06-25 00:00:00.000 45          110580       2007-06-26 00:00:00.000 56          410581       2007-06-26 00:00:00.000 21          310582       2007-06-27 00:00:00.000 6           310583       2007-06-30 00:00:00.000 87          210584       2007-06-30 00:00:00.000 7           4(30 row(s) affected)-- SolutionUSE TSQL2012;SELECT orderid, orderdate, custid, empidFROM Sales.OrdersWHERE orderdate >= "20070601"   AND orderdate < "20070701";-- 2-- Return orders placed on the last day of the month-- Tables involved: Sales.Orders table-- Desired output:orderid     orderdate               custid      empid----------- ----------------------- ----------- -----------10269       2006-07-31 00:00:00.000 89          510317       2006-09-30 00:00:00.000 48          610343       2006-10-31 00:00:00.000 44          410399       2006-12-31 00:00:00.000 83          810432       2007-01-31 00:00:00.000 75          310460       2007-02-28 00:00:00.000 24          810461       2007-02-28 00:00:00.000 46          110490       2007-03-31 00:00:00.000 35          710491       2007-03-31 00:00:00.000 28          810522       2007-04-30 00:00:00.000 44          410583       2007-06-30 00:00:00.000 87          210584       2007-06-30 00:00:00.000 7           410616       2007-07-31 00:00:00.000 32          110617       2007-07-31 00:00:00.000 32          410686       2007-09-30 00:00:00.000 59          210687       2007-09-30 00:00:00.000 37          910725       2007-10-31 00:00:00.000 21          410806       2007-12-31 00:00:00.000 84          310807       2007-12-31 00:00:00.000 27          410987       2008-03-31 00:00:00.000 19          810988       2008-03-31 00:00:00.000 65          310989       2008-03-31 00:00:00.000 61          211060       2008-04-30 00:00:00.000 27          211061       2008-04-30 00:00:00.000 32          411062       2008-04-30 00:00:00.000 66          411063       2008-04-30 00:00:00.000 37          3(26 row(s) affected)-- Solution-- in SQL Server 2012SELECT orderid, orderdate, custid, empidFROM Sales.OrdersWHERE orderdate = EOMONTH(orderdate);-- pre-SQL Server 2012 (advanced)SELECT orderid, orderdate, custid, empidFROM Sales.OrdersWHERE orderdate = DATEADD(month, DATEDIFF(month, "19991231", orderdate), "19991231");-- 3 -- Return employees with last name containing the letter "a" twice or more-- Tables involved: HR.Employees table-- Desired output:empid       firstname  lastname----------- ---------- --------------------9           Zoya       Dolgopyatova(1 row(s) affected)-- SolutionSELECT empid, firstname, lastnameFROM HR.EmployeesWHERE lastname LIKE "%a%a%";-- 4 -- Return orders with total value(qty*unitprice) greater than 10000-- sorted by total value-- Tables involved: Sales.OrderDetails table-- Desired output:orderid     totalvalue----------- ---------------------10865       17250.0011030       16321.9010981       15810.0010372       12281.2010424       11493.2010817       11490.7010889       11380.0010417       11283.2010897       10835.2410353       10741.6010515       10588.5010479       10495.6010540       10191.7010691       10164.80(14 row(s) affected)-- SolutionSELECT orderid, SUM(qty*unitprice) AS totalvalueFROM Sales.OrderDetailsGROUP BY orderidHAVING SUM(qty*unitprice) > 10000ORDER BY totalvalue DESC;-- 5 -- Return the three ship countries with the highest average freight in 2007-- Tables involved: Sales.Orders table-- Desired output:shipcountry     avgfreight--------------- ---------------------Austria         178.3642Switzerland     117.1775Sweden          105.16(3 row(s) affected)-- SolutionSELECT TOP (3) shipcountry, AVG(freight) AS avgfreightFROM Sales.OrdersWHERE orderdate >= "20070101" AND orderdate < "20080101"GROUP BY shipcountryORDER BY avgfreight DESC;-- in SQL Server 2012SELECT shipcountry, AVG(freight) AS avgfreightFROM Sales.OrdersWHERE orderdate >= "20070101" AND orderdate < "20080101"GROUP BY shipcountryORDER BY avgfreight DESCOFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;-- 6 -- Calculate row numbers for orders-- based on order date ordering (using order id as tiebreaker)-- for each customer separately-- Tables involved: Sales.Orders table-- Desired output:custid      orderdate               orderid     rownum----------- ----------------------- ----------- --------------------1           2007-08-25 00:00:00.000 10643       11           2007-10-03 00:00:00.000 10692       21           2007-10-13 00:00:00.000 10702       31           2008-01-15 00:00:00.000 10835       41           2008-03-16 00:00:00.000 10952       51           2008-04-09 00:00:00.000 11011       62           2006-09-18 00:00:00.000 10308       12           2007-08-08 00:00:00.000 10625       22           2007-11-28 00:00:00.000 10759       32           2008-03-04 00:00:00.000 10926       4...(830 row(s) affected)-- SolutionSELECT custid, orderdate, orderid,  ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownumFROM Sales.OrdersORDER BY custid, rownum;-- 7-- Figure out and return for each employee the gender based on the title of courtesy-- Ms., Mrs. - Female, Mr. - Male, Dr. - Unknown-- Tables involved: HR.Employees table-- Desired output:empid       firstname  lastname             titleofcourtesy           gender----------- ---------- -------------------- ------------------------- -------1           Sara       Davis                Ms.                       Female 2           Don        Funk                 Dr.                       Unknown3           Judy       Lew                  Ms.                       Female 4           Yael       Peled                Mrs.                      Female 5           Sven       Buck                 Mr.                       Male   6           Paul       Suurs                Mr.                       Male   7           Russell    King                 Mr.                       Male   8           Maria      Cameron              Ms.                       Female 9           Zoya       Dolgopyatova         Ms.                       Female (9 row(s) affected)-- SolutionsSELECT empid, firstname, lastname, titleofcourtesy,  CASE titleofcourtesy    WHEN "Ms."  THEN "Female"    WHEN "Mrs." THEN "Female"    WHEN "Mr."  THEN "Male"    ELSE             "Unknown"  END AS genderFROM HR.Employees;SELECT empid, firstname, lastname, titleofcourtesy,  CASE     WHEN titleofcourtesy IN("Ms.", "Mrs.") THEN "Female"    WHEN titleofcourtesy = "Mr."           THEN "Male"    ELSE                                        "Unknown"  END AS genderFROM HR.Employees;-- 8 (advanced, optional)-- Return for each customer the customer ID and region-- sort the rows in the output by region-- having NULLs sort last (after non-NULL values)-- Note that the default in T-SQL is that NULL sort first-- Tables involved: Sales.Customers table-- Desired output:custid      region----------- ---------------55          AK10          BC42          BC45          CA37          Co. Cork33          DF71          ID38          Isle of Wight46          Lara78          MT...1           NULL2           NULL3           NULL4           NULL5           NULL6           NULL7           NULL8           NULL9           NULL11          NULL...(91 row(s) affected)-- SolutionSELECT custid, regionFROM Sales.CustomersORDER BY  CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;

关键词：