《SQL Server 2012 T-SQL基础》读书笔记 - 2.单表查询

时间：2023-03-09 04:24:49
《SQL Server 2012 T-SQL基础》读书笔记 - 2.单表查询

Chapter 2 Single-Table Queries

GROUP BY之后的阶段的操作对象就是组（可以把一组想象成很多行组成的）了，HAVING负责过滤掉一些组。
分组后的COUNT（*）表示每组的行数。COUNT（*）会把包含NULL的行也数进来，比如有5行的一组数据是30, 10, NULL, 10, 10，列名叫qty，那么COUNT(qty)会返回4，而COUNT（*）返回5。
可以SELECT DISTINCT，也可以在聚合函数中指定DISTINCT，例如：COUNT（DISTINCK 列名），以及SUM，AVG等

在SELECT字句中，除了<expression> AS <alias>,还可以<alias> = <expression>或者<expression> <alias>，作者不建议使用后两种语法，因为不清晰。但是要注意的是如果你在SELECT中不小心忘记了逗号，那么语法上是可以通过的，比如SELECT orderid orderdate其实是把orderdate当成orderid的别名了，需要注意。

SELECT DISTINCT...会消除重复的行。

你也可以在ORDER BY中指定SELECT中没出现过的字段，但是如果SELECT DISTINCT的话，就不行了。

TOP是T-SQL独有的，他依赖于ORDER BY。

可以根据百分比：SELECT TOP（1） PERCENT

DESCEND分别跟在每一个列名后面：ORDER BY orderdate DESC，orderid DESC

SELECT  TOP（5）WITH TIES orderid，orderdate，custid，empid

FROM Sales.Orders

ORDER BY orderdate DESC

WITH TIES的作用是：与TOP（5）中的最下面一行的orderdate相同的行也会被算进来，也就是可能返回大于5行的结果。

自Sql Server2012开始，可以用Offset和Fetch了，语法如下：

SELECT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate, orderid

OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

注意，ROWS也可以写成ROW，NEXT也可以写成FIRST。另外，必须有ORDER BY。

一个window function是这么一个function，它能对结果集中的每一行都计算出一个scalar（single）的值，而这个值的计算是基于结果集中的a set of rows（也可以说成a window of rows）。其实看定义不太好理解，看一下例子很好理解。关于OVER的更具体的用法被移到第七章了。

各子句的逻辑上的执行顺序：

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
    • Expressions
    • DISTINCT
  • ORDER BY
    • TOP / OFFSET-FETCH

注意谓词的结果是三元的：TRUE, FALSE, or UNKNOWN。

IN，BETWEEN和LIKE的用法：

SELECT orderid, empid, orderdate

FROM Sales.Orders

WHERE orderid IN(10248, 10249, 10250); --至少与其中一个相等。
SELECT orderid, empid, orderdate

FROM Sales.Orders

WHERE orderid BETWEEN 10300 AND 10310; --一个范围，包括边界值。
SELECT empid, firstname, lastname

FROM HR.Employees

WHERE lastname LIKE N'D%';

注意上面的N表示National，表示是Unicode(NCHAR or NVARCHAR)。

如果两个运算对象有相同的数据类型，表达式的结果还是这个类型：例如两个INT相除，得到的还是一个整数：5/2得到2。如果你想从两个integer类型的字段上得到一个带小数点的数的话，必须CAST(col1 AS NUMERIC(12, 2))/CAST(col2 AS NUMERIC(12, 2))。NUMERIC(12, 2)这个数据类型精度为12，带有两位小数。但如果5/2.0,2.0是实数（NUMERIC）比INT优先级更高，所以先把5隐式转换为5.0，结果为2.5。

CASE expression是一种scalar expression，我的理解就是CASE表达式会evaluate成一个单值。

CASE的第一种用法：

SELECT productid，productname，categoryid，

  CASE categoryid

    WHEN 1 THEN ‘Beverage’

    WHEN 2 THEN ‘Condiments’

    WHEN 3 THEN ‘Meat’

    WHEN 4 THEN ‘Seafood’

    ELSE ‘Unknown Category’

  END AS categoryname

FROM Production.Products；

如果没有ELSE子句，则默认将其视为ELSE NULL。

CASE的第二种用法：

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

FROM Sales.OrderValues;

这几种函数相当于简化快速版的CASE表达式：ISNULL, COALESCE, IIF, and CHOOSE。

在WHERE中，当salary是NULL的时候，salary > 0 evaluates to UNKNOWN，NOT (salary > 0)也evaluates to UNKNOWN。

查询中的过滤条件是接受TRUE的（a query filter “accepts TRUE”），意味着要过滤掉FALSE和UNKNOWN（注意这时候UNKNOWN不要）。

CHECK约束是拒绝FALSE的，意味着接受TRUE和UNKNOWN（注意这时候UNKNOWN是要的）。

举个例子：如果是WHERE salary > 0，那么NULL的salary的行会被过滤掉；如果是CHECK（salary > 0），那么NULL的salary的行会被接受。

再举个例子：WHERE region <> N'WA'，你可以想象成：对每一行都计算一下这一行的region <> N'WA'是不是TURE，如果是TURE就接受，如果是FALSE或者UNKNOWN就拒绝。

两个实用谓词：IS NULL和IS NOT NULL

但是在GROUP BY或者ORDER BY中，NULL的行就被看作是一样的，所以所有NULL的会被分在一组里，ORDER BY的话会先返回NULL的行。至于有UNIQUE约束的列，如果放两个NULL进去会怎么样？自己试试便知。

记得在同一逻辑查询阶段中出现的所有表达式都是同时进行计算(evaluated)的，比如：

SELECT

  orderid,

  YEAR(orderdate) AS orderyear,

  orderyear + 1 AS nextyear

FROM Sales.Orders;

以上会报错，看起来似乎SELECT中的第三个表达式是在第二个表达式赋给别名之后发生的，实则不然。其实在SELECT list里面的表达式在逻辑上都是同时进行的。

再比如：WHERE col1 <> 0 AND col2/col1 > 2;

由于“在逻辑上，上面两个表达式是同时运算的”这个概念的存在，所以SQL Server有权任意调整他们的顺序，一般是先运行简单的（支持short circuits），所以以上的WHERE还是可能会除以0。（但CASE表达式里面的跟在WHEN表达式是会按顺序的，以及FROM中对多个表的联接是从左到右进行的。）

CHAR,和NCHAR是定长（不足自动补空格），VARCHAR和NVARCHAR是变长。

Collation用于指定语言，排序规则，大小写区分，区分重音等信息。你可以在四个层面指定：SQL Server实例，数据库，列和表达式。用法举例：

WHERE lastname COLLATE Latin1_General_CS_AS = N'davis';

CS表示case insensitive；AS表示accent sensitive。

如果不像这样显示指定COLLATE的话，就用比它更高的那个层面的指定过的Collation。

+可以用作字符串连接：country + N',' + region + N',' + city AS location

默认下，如果region是NULL，那么整个表达式都是NULL。如果想要让NULL的region显示为空，那么可以用COALESCE ()，COALESCE ()函数会返回第一个不为NULL的值。

SQL Server 2012提供了一个函数：CONCAT(country, N',' + region, N',' + city)，可以把NULL自动转为空字符串。

几种操作字符串常用的函数：

  • SELECT SUBSTRING('abcde', 1, 3);的结果是‘abc’。就算第三个参数超出了整个字符串长度，也不要紧，它会返回到字符串结尾。
  • LEFT(string, n), RIGHT(string, n)这两个函数是上面的SUBSTRING的方便方法，举例：

    SELECT RIGHT('abcde', 3);会返回‘cde’。
  • SELECT LEN(N'abcde');返回5（字符串长度，并会忽略结尾的blanks）。SELECT DATALENGTH(N'abcde');返回10（byte数）。
  • CHARINDEX(substring, string[, start_pos])会返回字符串中子字符串第一次出现的位置，比如：SELECT CHARINDEX(' ','Itzik Ben-Gan');返回6。
  • PATINDEX(pattern, string)和上面的类似，只不过pattern参数是正则表达式。
  • REPLACE(string, substring1, substring2)会replace 所有 substring1 with substring2，举例：REPLACE('1-a 2-b', '-', ':')返回‘1:a 2:b’。有个小技巧是可以通过这个函数来算出一个字符串里出现了多少次某个字符，比如以下统计lastname中’e‘出现的次数：

    LEN(lastname) - LEN(REPLACE(lastname, 'e', ''))。
  • REPLICATE(string, n)，举例：SELECT REPLICATE('abc', 3);返回‘abcabcabc’。STUFF(string, pos, delete_length, insertstring)会把一段子字符串替换成一段新的，举例：SELECT STUFF('xyz', 2, 1, 'abc');返回‘xabcz’。
  • UPPER(string), LOWER(string)太明显了，不介绍了。
  • RTRIM(string), LTRIM(string)会把首尾的spaces删掉。
  • FORMAT(input , format_string, culture)貌似是2012新增的，用法参考MSDN，书上没写

通配符：

%代表任意长度的字符串，包括空字符串。

_代表一个字符。

[<List of Characters>]跟正则表达式里面意思一样，比如[abc]。

[<Character>-<Character>]也跟正则表达式里面的意思一样，比如[A-E]。

[^<Character List or Range>]。比如[^A-E]表示一个不是A到E的字符。

特殊字符的转义没看懂。

《SQL Server 2012 T-SQL基础》读书笔记 - 2.单表查询

以上各种类型分别在SQL Server 2005,2008,2012引进，区别在于存储空间，精度。存储空间是根据你定义的时候指定的参数决定的，比如TIME(0)表示精确到一秒，TIME(7)表示精确到nanosecond，不指定的话默认是7。

当把一个字符串显示或隐式地转换为日期格式时，你可以通过设置LANGUAGE或DATEFORMAT（比如 SET LANGUAGE British;），但这被认为是很不好的做法。正确做法是用语言中立的格式，如下图所示：

《SQL Server 2012 T-SQL基础》读书笔记 - 2.单表查询

观察上表可知：用‘YYYYMMDD’是最好的，因为这种格式兼容了以上每种日期类型。

如果你是SQL Server2008之前的版本，那么如果你只想用日期，你可以这样：WHERE orderdate = '20070212'。然后这个字符串会自动转化为时间部分是午夜零点的日期。同理，如果你不指定日期部分：'12:30:15.123'，那么会被自动加上1900年一月一号的日期。

如果在filtered column上做计算或操作的话，SQL Server就不能有效地利用索引而导致性能降低，比如：WHERE YEAR(orderdate) = 2007。所以你应该这样：

WHERE orderdate >= '20070101' AND orderdate < '20080101';

《SQL Server 2012 T-SQL基础》读书笔记 - 2.单表查询

作者建议使用标准SQL的函数：CURRENT_TIMESTAMP，注意用这个的时候后面不用加空括号，其他的函数都要加一对空括号。

三种用于将一种类型转换成另一种类型的函数：

CAST(value AS datatype)

CONVERT (datatype, value [, style_number])

PARSE (value AS datatype [USING culture])

他们都有对应的TRY...的版本，比如：TRY_CAST(value AS datatype)，区别在于没有TRY的如果失败就失败，有TRY的如果失败返回NULL。

SWITCHOFFSET(datetimeoffset_value, time_zone)接受一个DATETIMEOFFSET类型的值，和一个你要设成的时区，还你一个这个对应时区的DATETIMEOFFSET类型的值。

TODATETIMEOFFSET(date_and_time_value, time_zone)跟上面的类似，只是他能接受一个非DATETIMEOFFSET类型的参数，然后返还给你一个带你指定时区的DATETIMEOFFSET类型的值。

DATEADD(part, n, dt_val)

part参数可以是year, quarter, month, dayofyear, day, week, weekday, hour,

minute, second, millisecond, microsecond, and nanosecond。也可以同缩写，比如yy代替yyyy。返回的类型和输入类型一样，如果你输入的是一个字符串，那么会被隐式转换为DATETIME类型。用法举例：

SELECT DATEADD(year, 1, '20090212'); --输出2010-02-12 00:00:00.000。

DATEDIFF(part, dt_val1, dt_val2)

用法举例：

SELECT DATEDIFF(day, '20080212', '20090212'); --返回天数差。

得到时间部分为零点的当前日期：

SELECT

  DATEADD(

    day,

    DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101');

其实就是随便找个日期作为基准点，然后算下当前日期跟这个日期差了多少天，再往基准日期上加多少天就行。同理，把上面的day改成month就是当月的一号。同理，获得当月的最后一天：

SELECT

  DATEADD(

    month,

    DATEDIFF(month, '19991231', CURRENT_TIMESTAMP), '19991231');

在SQL Server2012中，可以用EOMONTH(input [, months_to_add])来返回input对应的“end of month”日期。months_to_add参数是可选的。

DATEPART(part, dt_val)返回一个integer，比如：

DATEPART(month, '20090212')返回2。

YEAR(dt_val), MONTH(dt_val), DAY(dt_val)是DATEPART的便捷方法。

DATENAME(dt_val, part)和DATEPART差不多，唯一不同在于他返回字符串，比如上面那个例子返回2，用这个就是返回February（根据当前session的语言决定）。

ISDATE(string)如果发现参数可以被转换成一个日期那就返回1，否则返回0，比如：

ISDATE('20090212')返回1，而ISDATE('20090230')返回0。

FROMPARTS函数是2012版新增的，他可以通过你指定的参数来返回一个日期类型的值，有点像构造函数的意思：

DATEFROMPARTS (year, month, day)

DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)

DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)

DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)

SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)

TIMEFROMPARTS (hour, minute, seconds, fractions, precision)

用法举例：

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

想要列出某个database中所有的table名（包括schema前缀），可以：

USE 指定的数据库;

SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_name

FROM sys.tables;

想要查询某个表的列的信息：

SELECT

name AS column_name,

TYPE_NAME(system_type_id) AS column_type,

max_length,

collation_name,

is_nullable

FROM sys.columns

WHERE object_id = OBJECT_ID(N'Sales.Orders');

或者，你也可以在INFORMATION_SCHEMA这个schema里面查询各种View，这些View都是遵守SQL标准的，所以不会得到特定于SQL Server的东西，比如：

SELECT TABLE_SCHEMA, TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = N'BASE TABLE';  --查询table信息

或者通过INFORMATION_SCHEMA.COLUMNS查询列的信息。

还有一些系统存储过程和函数，他们内部也是查询上面那些View什么的，但更方便

EXEC sys.sp_tables;--返回当前数据库里可以查询的objects（表、视图什么的）

sp_help接受一个object name，返回关于它的信息，比如：

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

sp_columns返回列的信息：

EXEC sys.sp_columns

@table_name = N'Orders',

@table_owner = N'Sales';

返回约束信息：

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

还有一系列可以返回各种元信息的函数。

相关文章