Microsoft SQL Server 2008技术内幕：T-SQL语言基础（单表查询）

一、select语句的元素

• 各子句的逻辑处理顺序
  • from
  • where
  • group by
  • having
  • select
    • over
    • distinct
    • top
  • order by

1.1 分隔标识符名称

• 当标识符是非常规的（名称中嵌入空格或其他特殊字符、数字开头或是SQL Server关键字），需要分割标识符
  • ANSI SQL标准格式
    • 双引号
      • 例如，Sales.“Order Details”
  • SQL Server特有的
    • 方括号
      • 例如，Sales.[Order Details]

1.2 where子句

• where阶段只返回让逻辑表达式为true的那些行

1.3 group by子句

• group by阶段之后的所有阶段（包括having、select以及order by）的操作对象将是组
  • 之后处理的子句中指定的所有表达式务必保证为每个组只返回一个单值
• 所有的聚合函数都会忽略NULL值，除了count(*)

1.4 having子句

• 用于指定对组进行过滤的谓词或逻辑表达式
• 只返回让逻辑表达式为true的组。

1.5 select子句

1.5.1 别名

• AS
  • select YEAR(orderdate) AS orderyear
• 等号
  • select orderyear = YEAR(orderdate)
• 空格
  • select YEAR(orderdate) orderyear

1.5.2 重复表达式

• SQL Server能够标识在查询中重复使用的同一表达式
  • 只需对表达式进行一次计算

1.5.3 distinct子句

• 删除重复行
  • 结果中某一行的所有列与另一行完全相同

1.5.4 星号的使用

• 缺点
  • 如果客户端通过列的原始位置引用列，当表结构发生变化时会出现意想不到的结果
  • 解析列名需要付出额外代价

1.5.5 order by子句

• T-SQL支持在order by子句中指定没有在select子句中出现的元素
  • 但指定distinct后，order by子句只能选取select列表中出现的元素
    • distinct的结果可能实际对应于多个order by子句中选取的元素

1.5.6 top选项

• T-SQL特有
  • 限制查询返回的行数或百分比
    • 行数
      • select top (5) orderid from … order by orderdate desc
    • 百分比
      • select top (1) percent orderid from … order by orderdate desc
• with ties
  • 返回与TOP n行中最后一行的排序值相同的其他所有行
  • select top (5) with ties orderid from … order by orderdate desc

1.5.7 over子句

• 为行定义一个窗口，以便进行特定运算
• 作用
  • 可以在同一行中同时返回基础行的列和聚合列
• 使用条件
  • 必须与聚合函数、排名函数搭配使用
• over( )

  • partition by …

  • order by …

1.5.7.1 over（partition by）

• 必须与聚合函数搭配
• 指定聚合函数的分组依据

select orderid,custid,val,
	sum(val) over() as totalvalue,
	sum(val) over(partition by custid) as custtotalvalue
from Sales.OrderValues;

1.5.7.2 over（order by）

• 凡是开窗函数含有order by的，就必须与排名函数搭配
• 指定排名函数所依据的顺序
• 排名函数
  • row_number()
    • 行号
  • rank()
    • 排名
    • 表示之前有多少行具有更低的排序值
  • dense_rand()
    • 排名
    • 表示之前有多少个更低的排序值
  • ntile(n)
    • 分成n个组
    • 表示每个组的组号

select orderid,custid,val,
	row_number() over(order by val) as rownum,
	rank() over(order by val) as rank,
	dense_rank() over(order by val) as dense_rank,
	ntile(10) over(order by val) as ntile
from Sales.OrderValues order by val;

1.5.7.3 over（partition by … order by …）

• 产生分组排序结果

select orderid,custid,val,
	row_number() over(partition by custid order by val) as rownum
from Sales.OrderValues order by custid,val

二、between、in、like

• between … and …
  • 包含上、下边界值
• like
  • 通配符
    • %
      • 表示任意多个字符
    • _
      • 表示一个字符

三、case表达式

• case表达式是标量表达式，基于条件逻辑返回一个值

3.1 简单表达式

• 简单表达式将一个值（或一个标量表达式）与一组可能的取值进行比较，并返回第一个匹配的结果
  • 简单表达式基于相等性比较
• 如果没有匹配的结果，就返回else子句中列出的值
• 如果没有else子句，则默认视为else null

select lastname,firstname,region,
	case region
		when 'WA' then '华盛顿特区'
		else '未知地区'
	end as 区域
from hr.Employees

3.2 搜索表达式

• 搜索表达式不限于相等性比较，可以在when子句中指定谓词或逻辑表达式

select lastname,firstname,region,
	case when region ='WA' then '华盛顿特区'
		 when region is null then '未知地区'
		 else 'unknown'
	end as 区域
from hr.Employees

四、null值

• SQL Server使用三值谓词逻辑，即true、false、null
• 聚合函数都不统计null，除了count(*)
• unique约束只允许有一个null值

五、同时操作

• 同一逻辑查询处理阶段中出现的所有表达式都是同时进行计算的
• 例如，where子句中的短路运算不一定是按照从左到右的顺序进行
  • select col1 , col2 from dbo.T1 where col1<>0 and col2/col1>2
  • 处于同一优先级
• 例如，select子句中别名不能引用
  • select year(orderdate) as orderyear , orderyear + 1 as nextyear from sales.orders;

六、处理字符数据

6.1 字符数据类型

• 普通字符
  • 特点
    • 只能保存英文
    • 使用一个字节保存每个字符
  • 分类
    • char
      • 长度不可变，写入效率更高
      • 读取会超过实际使用的长度
    • varchar
      • 长度可变，读取效率更高
      • 写入需要移动数据进行长度拓展
      • 可以使用max说明符指定字符最大数量
        • 上限默认为8000字节
        • 超过上限则作为大型对象，保存在行的外部
• unicode字符
  • 特点
    • 可以混合多种语言
    • 使用两个字节保存每个字符
  • 分类
    • nchar
    • nvarchar

6.2 排序规则（collation）

6.2.1 概念

• 包括多语言支持、排序规则、区分大小写、区分重音等

6.2.2 sys.fn_helpcollations()

• 系统中所有支持的排序规则

select name,description from sys.fn_helpcollations();

• Latin1_General_BIN_CI_AS
  • Latin1_General
    • 支持英语
  • BIN
    • binary sort
    • 根据字符的二进制排序，即’A’<‘B’<‘a’<‘b’
    • 如果不指定，则默认为字典排序，即’A’和’a’<'B’和‘b’
  • CI
    • case insensitive
  • AS
    • accent-sensitive

6.2.3 collate

• 可以在四种不同级别上定义排序规则
  • SQL Server实例
  • 数据库
  • 列
  • 表达式
    • 例如，修改表达式的排序规则，使其区分大小写

select empid, firstname, lastname
from HR.Employees
where lastname collate Latin1_General_CS_AS = N'davis';

6.2.4 引号分隔的标识符

• ANSI中规定字符串用单引号分隔
• 可以修改此规则
  • quoted_identifier选项
    • 可在数据库级别进行设置
    • 也可在会话级别进行设置

set quoted_identifier off
select empid, firstname, lastname
from HR.Employees
where lastname = "davis"

6.3 运算符和函数

6.3.1 字符串串联 +

• ANSI对NULL值执行字符串串联运算的结果也为NULL
• 将NULL作为空字符串
  • 设置
    • set concat_null_yields_null off
  • 编程方式
    • coalesce()
      • 接收一系列值，返回其中第一个不为NULL的值
      • select lastname,firstname,coalesce(region,’’) as 区域
        from hr.Employees

6.3.2 substring

• select substring(string, start, length);

6.3.3 left和right函数

• left(string, n)
• right(string, n)

6.3.4 len和datalength

• len(string)
  • 返回字符数
• datalength(string)
  • 返回字节数

6.3.5 charindex

• charindex(substring, string [,start_pos] )

6.3.6 patindex

• patindex( pattern, string )
  • 某个模式第一次出现的起始位置

6.3.7 replace

• replace( string, substring1, substring2 )

6.3.8 replicate

• replicate( string, n )
  • 以指定的次数复制字符串值

6.3.9 stuff

• stuff( string, pos, delete_length, insertstring )
  • 在指定位置删除指定长度，再在该位置插入指定字符串

6.3.10 upper和lower

6.3.11 rtrim和ltrim

6.4 like谓词

6.4.1 %

6.4.2 _

6.4.3 [ … ]

• 例如，[ABC]

6.4.4 [ - ]

• 例如，[A-E]

6.4.5 [ ^… ]

• 例如，[ ^ABC]
  • 不在A、B、C中
• [ ^A-E]

6.4.6 escape

• like ‘%!_%’ escape ‘!’
• 对于%、_、[
  • 可以直接用[]包裹，而不使用转义
  • 例如，like ‘%[_]%’

七、处理日期和时间数据

7.1 日期和时间数据类型

7.2 时间日期字符串

• 当字符串和日期时间类型进行比较时，会将字符串隐式转换为日期时间类型
• 部分日期字符串转换为日期时间类型时，转换结果与使用的语言有关
  • 例如，‘02/12/2007’，英国为2007-12-02，美国为2007-02-12
• 修改语言与日期格式设置
  • set language
    • 也会对应修改dateformat
  • set dateformat
  • 只影响输入格式
• 与语言无关的日期格式
  
  

7.3 过滤日期范围

• 在过滤条件中使用函数，可能无法利用索引，应将函数改为范围
• 例如，year(orderdata)=‘2007’ 与 orderdata>=‘2007-01-01’ and orderdata<=‘2007-12-31’

7.4 日期和时间函数

7.4.1 获取当前时间

select getdate() as "getdate",
	current_timestamp as "current_timestamp",
	getutcdate() as "getutcdate",
	sysdatetime() as "sysdatetime",
	sysutcdatetime() as "sysutcdatetime",
	sysdatetimeoffset() as "sysdatetimeoffset";

7.4.2 cast和convert函数

• cast( value as datatype )
  • ANSI
• convert( datatype, value [,stylenumber] )
  • 可以使用样式值指定字符串的格式

7.4.3 switchoffset

• switchoffset( datetimeoffset_value, time_zone )
  • 可以按指定的时区对输入的datetimeoffset值进行调整
  • 例如，select switchoffset( sysdatetimeoffset(), ‘-05:00’ )

7.4.4 todatetimeoffset

• 为输入的任何日期和时间类型值设置时区偏移量

7.4.5 dateadd

• dateadd( part, n, dt_val )
• 例如，select dateadd( year, 1, ‘20090212’);

7.4.6 datediff

• datediff( part, dt_val1, dt_val2 )
• dt_val2 - dt_val1的值

7.4.6.1 通过锚点日期计算某月第一天

select DATEADD(MONTH,DATEDIFF(MONTH,'2001-01-01',current_timestamp),'2001-01-01')

7.4.7 datepart

• datepart( part, dt_val )

7.4.8 year、month、day

• datepart的简略版本

7.4.9 datename

• datename( part, dt_val )
• 例如，select datename ( month, ‘20090212’ ) 返回 ‘February’

7.4.10 isdate

• isdate( string )

八、查询元数据

8.1 目录视图

8.1.1 查询数据库中的所有表

select schema_name(schema_id) as table_schema_name,
	name as table_name
	from sys.tables;

8.1.2 查询表中的所有列

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('Sales.Orders');

8.2 信息架构视图

• 基于ANSI SQL而定义的

8.2.1 查询数据库中的所有表

select table_schema,table_name
	from information_schema.tables
	where table_type = 'BASE TYPE';

8.2.2 查询表中的所有列

select column_name,data_type,character_maximum_length,
	collation_name,is_nullable
	from information_schema.columns
	where table_schema = 'Sales'
	and table_name = 'Orders';

8.3 系统存储过程和函数

8.3.1 存储过程

• sys.sp_tables
• sys.sp_help
• sys.sp_columns
• sys.sp_helpconstraint

8.3.2 函数

• serverproperty( propname)
• databasepropertyex( database, propname )
• objectproperty( objectid, propname )
• columnproperty( objectid, columnname, propname )