第二章 单表查询(2)

--2.2 谓词和运算符
use TSQLFundamentals2008;
go

select orderid, custid, empid, orderdate
from Sales.Orders
where (custid=1 
and empid in(1,3,5))
or
(custid=85
and empid in (2,4,6))

--2.3 Case表达式
select productid, productname, categoryid,
case categoryid
when 1 then 'Beverages'
when 2 then 'Condiments'
else 'Unknown Category'
end as 'categaryname'
from Production.Products;

--Case&ntile函数
select orderid, custid, val,
case ntile(3) over(order by val)
when 1 then 'Low'
when 2 then 'Medium'
when 3 then 'High'
else 'Unknown'
end as titledesc
from Sales.OrderValues
order by val;

--Case&when
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

--2.4 NULL值
--SQL支持用三值谓词逻辑，这意味着谓词的计算结果可以是true, false或unknown
--对两个null进行比较的表达式(null=null),其计算结果为unknown
select custid, country,region,city
from Sales.Customers
where region is null

--ANSI SQL有两种UNIQUE约束:一种将多个null值视为相等的(只允许有一个null值)，
--另一种则将多个null值视为不同的(允许有多个null值)。SQL Server只实现的前者

--2.5 同时操作
select orderid, custid, val
from Sales.OrderValues
where 
case
when val < 2000.00 then 'no'
when val < 3000.00 then 'yes'
else 'no'
end = 'yes'

--2.6 处理字符数据
--普通字符数据类型包括CHAR和VARCHAR，Unicode字符数据包括NCHAR和NVARCHAR，
--他们两者的区别是：普通的字符使用一个字节(byte)来保存每个字符，而Unicode
--字符则需要两个字节。
--名称中不包含VAR元素的任何数据类型都是固定长度的(CHAR,NCHAR),对于这样的类
--型，SQL Server会按照为列定义的大小，在行中为该列预留出固定的空间，所以该
--列的长度并不是字符串中字符的实际个数。
--名称中含有VAR元素的数据类型是可变长度的(VARCHAR,NVARCHAR),SQL Server在行
--中会按照字符串的实际长度来保存数据，外加两个额外的字节以保存数据的偏移值。
--在定义可变长度的数据类型时，可以使用MAX说明符，而不必指定字符的最大数量。

select empid, firstname + N' ' + lastname as fullname
from HR.Employees
go

--通过将一个名为CONCAT_NULL_YIELDS_NULL的会话选项设置为OFF，就可以改变
--SQL Server处理串联的方式。这时，SQL Server将把NULL值作为空字符串来进行串联。
set concat_null_yields_null off
select custid, country, region, city, country + N',' + region + N',' + city as location
from Sales.Customers
set concat_null_yields_null on
go

--coalsce函数，替换字符串
select custid, country, region, city, 
country + N',' + coalesce(region,N'') + N',' + city as location
from Sales.Customers

--substring函数，提取子串，SQL Server起始Index为1
select SUBSTRING(N'abcde', 1, 3)

--left和right函数是substring函数的简略形式
select LEFT(N'abcde', 3)
select RIGHT(N'abcde', 3)

--len和datalength函数
--对于unicode字符，每个字符需要两个字节的存储空间；因此字符串的
--字符数是字节数的一半。如果要得到字节数，则应该使用datalength函数。
select LEN(N'abcde') --5
select DATALENGTH(N'abcde') --10

--CHARINDEX函数 返回字符串中某个子串第一次出现的起始位置
select CHARINDEX(' ', N'Anders Fan')

--patindex函数 返回字符串中某个模式第一次出现的起始位置
select PATINDEX(N'%Fan%', N'Anders Fan Is Me')

--replace函数 将字符串中出现的所有某个子串替换为另一个字符串
select REPLACE(N'Anders Fan', N'Anders', N'Xufei')

--可以使用replace函数来计算字符串中某个字符出现的次数
select Len(N'Anders Fan') - Len(REPLACE(N'Anders Fan', N'n', N'')) as Num

--replacate函数 以指定的次数复制字符串值
select REPLICATE(N'Anders ', 100) as [The King]

--为每个供应商的整数ID生成一个10位数字的字符串表示,不足10位时,前面补0
select supplierid, 
RIGHT(replicate(N'0', 9) + CAST(supplierid as varchar(10)), 10) 
as strsupplierid
from Production.Suppliers

--stuff函数
select STUFF(N'xyz', 2, 1, N'abc') --xabcz

--Upper Lower
select UPPER(N'Anders Fan')
select LOWER(N'Anders Fan')

--like谓词
--%通配符 代表任意长度的字符串
select empid, lastname 
from HR.Employees
where lastname like N'D%'

-- _通配符 代表任意单个字符
select empid, lastname
from HR.Employees
where lastname like N'_e%'

--[]通配符 方括号中包括一列字符,表示必须匹配列指定字符中的一个字符
select empid, lastname
from HR.Employees
where lastname like N'[ABC]%'

--[-]通配符 方括号中包含一个字符范围,表示必须匹配指定范围内的一个字符
select empid, lastname
from HR.Employees
where lastname like N'[A-E]%'

--[^]通配符 ^后面跟着一个字符列或范围,表示不属于指定字符列或范围内的任意单个字符
select empid, lastname
from HR.Employees
where lastname like N'[^A-M]%'

--escape 转义字符