第二章 单表查询 T-SQL语言基础(3)

单表查询(3)

2.6 处理字符数据

字符数据的查询处理,内容包括:类型,排序规则,运算符和函数,以及模式匹配.

2.6.1 数据类型

SQL Server支持两种字符数据类型----普通字符和Unicode字符.普通字符数据类型包括CHAR和VARCHAR,Unicode字符数据类型包括NCHAR和NVARCHAR.

普通字符和Unicode字符的区别:普通字符使用一个字节来保存每个字符,而Unicode字符则需要两个字节.

注:当表示一个普通的字符常量时,只需要简单地使用单引号:'This is a regular charactor string literal';当表示一个Unicode类型的字符常量时,需要在前面指定一个字符N作为前缀,N'This is a regular charactor string literal'

  ****名称中不包含VAR元素的任何数据类型都是固定长度的(CHAR,NCHAR),对于这样的类型,SQL Server会按照为列定义的大小,在行中为该列预留出固定的空间,所以该列的长度并不是字符串中字符的实际个数----(这种类型的存储消耗不是最优的,在读取数据时可能要付出更多的代价.)

  ****名称中包含VAR元素的数据类型是可变长度的(VARCHAR,NVARCHAR),SQL Server在行中会按字符串的实际长度来保存数据,外加两个额外的字节以保存数据的偏移值(offset data).----(这种类型的,更新数据时可能需要对行进行扩展,导致数据移动,超出当前页的范围.与固定长度的数据类型相比,对具有可变长度数据类型的数据进行更新的效率会有所降低.)

  注:在定义可变长度的数据类型时,可以使用MAX说明符,而不必指定字符的最大数量.(当用MAX说明符来定义一个列时,可以把一个值直接保存到行的内部,其长度具有一定的上限,默认是8000字节.大小超过该上限的值将作为大型对象(LOB,large object),保存在行的外部).

2.6.2 排序规则(Collation)

 排序规则是字符数据的一个属性,封装了几个方面的特征,包括多语言支持(和Unicode类型有关,因为它支持所有语言),排序规则,区分大小写,区分重音,等等.

--系统中目前支持的所有排序规则及其描述,可以查询表函数fn_helpcollations

select name,description    from sys.fn_helpcollations();

SQL Server实例的排序规则是在按照时设置的.当创建用户数据库时,可以使用COLLATE 子句指定数据库的排序规则.如果不指定,则默认采用SQL Server实例的排序规则.

数据库的排序规则决定了数据库中对象元数据的排序规则,同时也是用户列表默认使用的排序规则.

在定义列时,可以用COLLATE 子句显示地指定它的排序规则.如果不指定,则默认使用数据库的排序规则.

--用COLLATE 子句也可以修改表达式的排序规则

--以下是不区分大小写的环境
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname = N'davis';

--以下,使用了区分大小写的比较
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_CS_AS = N'davis';

2.6.3 运算符和函数

字符串串联运算符(+)

T-SQL 提供了加号(+)运算符,可以将两个或多个字符串合并或串联成一个字符串.

- Concatenation
SELECT empid, firstname + N' ' + lastname AS fullname
FROM HR.Employees;

ANSI SQL规定对NULL值执行字符串串联运算也会产生NULL值的结果.

-- Listing 2-7: Query Demonstrating String Concatenation
SELECT custid, country, region, city,
  country + N',' + region + N',' + city AS location
FROM Sales.Customers;

--CONCAT_NULL_YIELDS 设置OFF,改变SQL SERVER处理串联的方式.这时,SQL Server将把NULL值作为空字符串来进行串联.
SET CONCAT_NULL_YIELDS_NULL OFF;

--强烈建议避免修改SQL Server的标准行为,恢复为设置 ON
SET CONCAT_NULL_YIELDS_NULL ON;

--要把NULL值作为空字符串来处理,可以使用COALESCE函数.
--以编程方式将NULL替换为空字符串

SELECT custid, country, region, city,
  country + N',' + COALESCE(region, N'') + N',' + city AS location
FROM Sales.Customers;

T-SQL 提供了一套字符串处理函数......

SUBSTRING函数

--substring
SELECT SUBSTRING('abcde', 1, 3); -- 'abc'
select substring('abcde',1,9);
--特殊处理方式:
--如果第二个参数(第二个参数小于字符串长度,如果大于则返回空!)和第三个参数的和超过了输入字符串的长度,则函数返回从起始位置开始,直到字符串结尾的整个值表达式.
--当想返回字符串中从某个位置开始,直到其结尾的所有内容时--只要指定一个非常大的值或是表示整个输入字串长度的值就可以.

 LEFT和RIGHT函数

--left,right
--分别返回输入字符串从左边或右边开始指定个数的字符.
--第一个参数是函数要处理的字符串.第二个参数是要从字符串的左边或右边提取的字符个数.
select left('abcde',3) --'abc'
SELECT RIGHT('abcde', 3); -- 'cde'

 LEN和DATALENGTH函数

--返回字符串中的字符数
SELECT LEN(N'abcde'); -- 5

--返回字符串的字节数
SELECT DATALENGTH(N'abcde'); -- 10

--对于普通字符,这两个函数返回的数字是相同的,因为每个字符只占一个字节的存储空间.而Unicode字符,每个字符需要两个字节的存储空间,字符串的字符数是字节数的一半.
--len和datalength区别:前者不包含尾随空格,而后者会包含尾随的空格.

CHARINDEX函数

--charindex函数返回字符串中某个子串第一次出现的起始位置
SELECT CHARINDEX(' ','Itzik Ben-Gan'); -- 6

PATINDEX函数

--patindex函数返回字符串中某个模式第一次出现的起始位置.
--patindex(pattern,string)
--参数pattern使用的模式与T-SQL中LIKE谓词使用的模式类似
SELECT PATINDEX('%[0-9]%', 'abcd123efgh'); -- 5,返回数字第一次出现的位置

REPLACE函数

--replace函数将字符串中出现的所有某个子字符串替换为另一个字符串.
SELECT REPLACE('1-a 2-b', '-', ':'); -- '1:a 2:b'
--可以使用REPLACE函数来计算字符串中某个字符出现的次数.
--以下,先将字符串中所有的那个字符替换为空字符串,再计算字符串的原始长度和新长度的差值,差值就是出现的次数.
SELECT empid, lastname,
  LEN(lastname) - LEN(REPLACE(lastname, 'e', '')) AS numoccur
FROM HR.Employees;

REPLICATE函数

--指定次数复制字符串
select replicate('abc',3); --abcabcabc

--以下,先生存9个0,然后将supplierid由int类型转换成VARCHAR类型,两者字符串联,然后从右边取10位
SELECT supplierid,
  RIGHT(REPLICATE('0', 9) + CAST(supplierid AS VARCHAR(10)),
        10) AS strsupplierid
FROM Production.Suppliers;

STUFF函数

--stuff函数可以先删除字符串中的一个子串,再插入一个新的子字符串作为替换.
--stuff(string,pos,delete_length,insertstring)
--函数对输入参数string进行处理,从输入参数pos指定的位置开始删除delete_length参数指定长度的字符;然后将insertstring参数指定的字符串插入到pos指定的位置.
SELECT STUFF('xyz', 2, 1, 'abc'); -- 'xabcz'

UPPER和LOWER函数

--大小写函数
SELECT UPPER('Itzik Ben-Gan'); -- 'ITZIK BEN-GAN'

SELECT LOWER('Itzik Ben-Gan'); -- 'itzik ben-gan'

RTRIM和LTRIM函数

--rtrim和ltrim 删除右边的空格(尾随)和左边的空格(前导)
SELECT RTRIM(LTRIM('   abc   ')); -- 'abc'

2.6.4 LIKE谓词

T-SQL 提供了一个LIKE谓词,用于检查字符串是否能够匹配指定的模式.

--%(百分号)通配符 代表任意长度的字符串,包括空字符串.
-- Last name starts with D
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';

-- _(下划线)通配符 代表任意单个字符
-- Second character in last name is e
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'_e%';

--[<字符列>]通配符 表示必须匹配列指定字符中的一个字符
-- First character in last name is A, B or C
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[ABC]%';

--[<字符>-<字符>]通配符 表示必须匹配指定范围内的一个字符
-- First character in last name is A through E
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[A-E]%';

--[^<字符列或范围>] 表示不属于指定字符列或范围内的任意单个字符
-- First character in last name is not A through E
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[^A-E]%';


--ESCAPE(转义)字符
--搜索包含特殊通配符的字符串('%','_','[',']'),则必须使用转义字符

2.7 处理日期和时间数据

 2.7.1 日期和时间类型

2008之前的时间数据类型:DATETIME,SMALLDATETIME

2008新增的时间数据类型:DATE,TIME,DATETIME2和DATETIMEOFFSET

注:TIME,DATETIME2,DATETIMEOFFSET的存储空间大小要依赖于所选择的精度(precision).可以通过0~7之间的整数来指定其精度,分别代表不同小数位数的秒值的精度.例如:TIME(0)只能准确到1秒;TIME(3)表示准确到1毫秒;TIME(7)表示准确到100纳秒.如没指定,SQL Server默认将上述三种类型的精度设置为7.

2.7.2 字符串文字

SQL Server允许指定不同类型的字符串文字,再讲其转换(显示或隐示地)为相应的日期和时间数据类型.(使用字符串来表示日期和时间值是一种最佳实践)

--以下,SQL SERVER会把'20070212'看作是一个字符串常量,而不是日期和时间常量,但是涉及了两种不同类型的操作数,所以其中一种操作数的类型会转换
--SQL SERVER数据类型之间的优先关系,运算时将具有较低数据类型优先级的操作数隐式地转换为具有较高优先级的类型.这个例子,把字符串文字转换成了列具有的数据类型datetime
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = '20070212';

--隐式转换是在幕后进行的.显示地将字符串转换为DATETIME数据类型是下面的例子!
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = CAST('20070212' AS DATETIME);

语言设置对日期和时间数据类型的影响,不推荐使用

--SET LANGUAGE命令改写会话中的默认语言
--SET DATEFORMAT命令改写会话中的DATEFORMAT设置
--字符串'02/12/2007' 语言环境不一样,解释也不一样
--LANGUAGE/DATEFORMAT只会影响输入值的解释方式,对数据输出中使用的格式没有任何影响
SET LANGUAGE British;
SELECT CAST('02/12/2007' AS DATETIME);

SET LANGUAGE us_english;
SELECT CAST('02/12/2007' AS DATETIME);

--字符串文字中未指定时间,SQL SERVER默认将时间设置为午夜.不指定时区,SQL SERVER将采用00:00
--'YYYY-MM-DD'和'YYYY-MM-DD hh:mm:ss...'格式,当转换到DATETIME或SMALLDATETIME类型时,它们是语言相关的;当转换到DATE,DATETIME2,以及DATETIMEOFFSET时,它们是语言无关的.
--注:类似'YYYYMMDD'这种是一条最佳实践原则
SET LANGUAGE British;
SELECT CAST('20070212' AS DATETIME);

SET LANGUAGE us_english;
SELECT CAST('20070212' AS DATETIME);

--CONVERT函数的第3个参数中指定一个表示正在使用的样式的数字,显示地将字符串文字转换成想要的数据类型.
SELECT CONVERT(DATETIME, '02/12/2007', 101);--2007-02-12 00:00:00.000

SELECT CONVERT(DATETIME, '02/12/2007', 103);--2007-12-02 00:00:00.000

2.7.3 单独使用日期和时间

2008引入了可以单独使用日期和时间部分的数据类型.

2005关于日期和时间的数据类型是DATETIME或SMALLDATETIME类型,当使用日期数据时,保存数据的时间值将是午夜(时间部分全是0).当使用时间数据时,保存数据的日期值将是基础日期1900年1月1日

-- Working with Date and Time Separately
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = '20070212';

SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20070212'
  AND orderdate < '20070213';

SELECT CAST('12:30:15.123' AS DATETIME);--1900-01-01 12:30:15.123

2.7.4 过滤日期范围

--原则:为了潜在地有效利用索引,就需要对谓词进行调整,以便对过滤条件中的列不进行处理
--意思:不要对索引列进行处理
--以下,orderdate是索引列,不应该使用函数来过滤某个月生产的订单,使用一个范围过滤条件最佳实践
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007;

SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101';

SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;

SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20070201' AND orderdate < '20070301';

2.7.5 日期和时间函数

 返回当前日期和时间函数

-- Current Date and Time
SELECT
  GETDATE()           AS [GETDATE],
  CURRENT_TIMESTAMP   AS [CURRENT_TIMESTAMP],
  GETUTCDATE()        AS [GETUTCDATE],
  SYSDATETIME()       AS [SYSDATETIME],
  SYSUTCDATETIME()    AS [SYSUTCDATETIME],
  SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET];
--GETDATE(),CURRENT_TIMESTAMP,GETUTCDATE() 为DATETIME类型
--SYSDATETIME(),SYSUTCDATETIME() 为DATETIME2类型;SYSDATETIMEOFFSET()为DATETIMEOFFSET类型;这个三个为2008新增的

--除了ANSI函数CURRENT_TIMESTAMP以下,对于不需要参数的函数,在调用它们时必须多加一对空的圆括号.
--推荐选用 CURRENT_TIMESTAMP,按照标准来

--通过把CURRENT_TIMESTAMP或SYSDATETIME转换为DATE或TIME
SELECT
  CAST(SYSDATETIME() AS DATE) AS [current_date],
  CAST(SYSDATETIME() AS TIME) AS [current_time];

CAST和CONVERT函数

 CAST和CONVERT函数用于转换值的数据类型.

-- CAST and CONVERT
--CONVERT提供的第三个参数来指定转换的样式,例如:样式值101表示'MM/DD/YYYY',103表示'DD/MM/YYYY'
--CAST是ANSI标准SQL,推荐使用CAST函数,保证代码尽可能与标准兼容.

SELECT CAST('20090212' AS DATE);
SELECT CAST(SYSDATETIME() AS DATE);
SELECT CAST(SYSDATETIME() AS TIME);

--2008之前的版本中只想用日期或时间,就必须把DATETIME或SMALLDATETIME值中不相关的部分设置为'0'.
--只使用日期,就得把时间部分设置为午夜
--只使用时间,就得把日期设置为基础日期1900年1月1日

--date,2005获得当前日期部分
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112);
SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS DATETIME);

--time,2005获得当前时间部分
SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114);
SELECT CAST(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) AS DATETIME);

SWITCHOFFSET函数

-- SWITCHOFFSET
-- SWITCHOFFSET函数可以按指定的时区对输入的SWITCHOFFSET值进行调整.
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00');

TODATETIMEOFFSET函数

-- TODATETIMEOFFSET
-- TODATETIMEOFFSET函数可以为输入的日期和时间值设置时区偏移量
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-05:00');
SELECT TODATETIMEOFFSET(SYSDATETIME(), '-05:00');

DATEADD函数

-- DATEADD
-- DATEADD函数可以将指定日期的部分作为单位,为输入的日期和时间值增加指定的数量.
SELECT DATEADD(year, 1, '20090212');--2010-02-12 00:00:00.000
select dateadd(month,2,'20090212');--2009-04-12 00:00:00.000
select dateadd(day,10,'20090212'); --2009-02-22 00:00:00.000

DATEDIFF函数

-- DATEDIFF
-- DATEDIFF返回两个日期和时间值之间相差的指定部分的计数.
SELECT DATEDIFF(day, '20080212', '20090212'); --366
select datediff(year,'20080412', '20090212'); -- 1
select datediff(month,'20080416', '20090212'); --10


--当前系统日期和时间值中的时间部分设置为午夜,例子
SELECT
  DATEADD(
    day, 
    DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101');

--当前月份的第一天,例子
SELECT
  DATEADD(
    month, 
    DATEDIFF(month, '20010101', CURRENT_TIMESTAMP), '20010101');

--当前月份的最后一天,例子
SELECT
  DATEADD(
    month, 
    DATEDIFF(month, '19991231', CURRENT_TIMESTAMP), '19991231');

DATEPART函数

DATEPART函数返回一个表示给定日期和时间值的指定部分的整数.

-- DATEPART
-- 返回是整数!!
SELECT DATEPART(year, '20090212'); --2009
SELECT DATEPART(month, '20090212'); --2
SELECT DATEPART(day, '20090212'); --12

 YEAR,MONTH和DAY函数

YEAR,MONTH,以及DAY函数是DATEPART函数的简略版本,它们分别返回一个代表输入日期和时间值中年,月,日部分的整数.

-- DAY, MONTH, YEAR
--返回整数
SELECT
  DAY('20090212') AS theday, --12
  MONTH('20090212') AS themonth, --2
  YEAR('20090212') AS theyear; --2009

DATENAME函数

DATENAME函数返回一个表示给定日期和时间值的指定部分的字符串.

-- DATENAME
-- 返回指定部分的字符串
SELECT DATENAME(month, '20090212');

SELECT DATENAME(year, '20090212');

select datename(day,'20090212');

ISDATE函数

-- ISDATE
-- ISDATE接受一个字符串作为输入,如果能把这个字符串转换为日期和时间数据类型的值,则返回1;如果不能,则返回0.
SELECT ISDATE('20090212'); --1,可以返回日期和时间类型的值
SELECT ISDATE('20090230'); --0,不能返回日期和时间类型的值

2.8 查询元数据

 2.8.1 目录视图

-- Catalog Views
USE TSQLFundamentals2008;

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

2.8.2 信息架构视图

-- Information Schema Views
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = N'BASE TABLE';

SELECT 
  COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, 
  COLLATION_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'Sales'
  AND TABLE_NAME = N'Orders';

2.8.3 系统存储过程和函数

-- System Stored Procedures and Functions
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'TSQLFundamentals2008', 'Collation')

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

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