一个月之前就注册了这个博客，今天才开始动笔写，真是有点。。。。
最近开始看面试题发现一个月之前看的sql那点东西都忘光了，所以决定通过上传当时学习时记录的笔记回忆当时的内容，希望能有所帮助。
==============================================分割线============================
本博客内容没有任何技术含量，仅供个人复习使用，慎重。
=============================================分割线==============================
use TSQLFundamentals2008
-- 基本执行顺序 from->where->group by->having->select->order by
select
empid
, YEAR(orderdate) as orderyear
, COUNT(*) as numorders
from
sales.orders
where
custid = 71
group by
empid
, year(orderdate)
having
count(*) > 1
order by
empid
, orderyear;
-- distinct 排除重复
select
empid
, year(orderdate)
, count(distinct custid) as numcusts
from
Sales.Orders
group by
empid
, year(orderdate);
-- order by 对结果进行排序，默认从小到大，desc倒序
— top (5)取出排序后的前五个结果
select top (5) orderid, orderdate, custid, empid
from Sales.Orders
order by orderdate desc;
-- top (5) percent 取出排序后前百分之五的结果
select top (5) percent orderid, orderdate, custid, empid
from Sales.Orders
order by orderdate desc;
use TSQLFundamentals2008
-- with ties 能够请求返回与top n行中最后一行的排序值相同的其他所有行
select top (5) with ties orderid, orderdate, custid, empid
from Sales.Orders
order by orderdate desc;
-- 关键字partition by可以对指定项进行聚合运算
select
orderid
, custid
, val
, SUM(val) over() as totalvalue
, SUM(val) over(partition by custid) as custtotalvalue
from Sales.OrderValues;
select
orderid
, custid
, val
, 100. * val / SUM(val) over() pctall
, 100. * val / sum(val) over(partition by custid) as pctcust
from
Sales.OrderValues;
-- over子句也支持四种排名函数：row_number（行号）、rank（排名）、
-- dense_rank（密集排名）、以及ntile。
-- row_number函数用于为查询的结果集中的各行递增的序列号，
-- 其逻辑顺序通过over子句中的order by语句进行指定。在我们的查询例子中，
-- 逻辑顺序基于的是val列：因此，从输出中可以看到，随着订单价格的增加，行号也随之增加。
-- 不过，即使订单价格没有增加，行号也会依然增加。
-- 所以，如果row_number函数的order by不能唯一确定行的顺序，
-- 查询结果就是不确定的。也就是说， 查询可能返回多个正确的结果。
-- 如果想却取得确定结果，需要在order by子句中添加元素。
-- rank与dense_rank的作用和row_number作用类似。区别是rank表示之前有多少行具有更低的排序值，
-- 而dense_rank则表示之前有多少个更低的排序值。
-- ntile函数可以把结果中的行关联到组（tile，相当于由行组成的指定数目的组），并为每一行分配
-- 一个所属的组的编号。ntile接受一个表示组的数量的输入参数，并要在over子句中指定逻辑顺序。
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;
-- 排名函数也支持在over子句中使用partition by语句
select
orderid
, custid
, val
, ROW_NUMBER() over(partition by custid order by val) as rownum
from sales.ordervalues
order by
custid
, val;
-- 注意，over子句中指定的order by逻辑与数据展示没什么关系，并不会改变查询结果
-- 表中的任何内容。如果在查询中不指定order by，和前面介绍的一样，就不能保证输出中行的任何顺序。
-- 如果需要确保查询结果的排名顺序，就不需再order by子句增加相应的排序条件，
-- 就像前面排名函数的最后两个查询例子演示的那样。
-- 如果在select处理接单制定了开窗函数，开窗计算会在distinct子句之前进行处理
--总结逻辑处理顺序如下
-- from
-- where
-- group by
-- having
-- select
-- over
-- distinct
-- top
-- order by
-- 由于在row_number会为不同行分配顺序号，造成所有行都不会相同，使distinct失去作用，
-- 所以在同一select中不同是指定distinct和row_number是一条最佳实践原则。
-- in谓词
select
orderid
, empid
, orderdate
from
Sales.Orders
where
orderid in(10248, 12049, 10250);
-- where 谓词
select
orderid
, empid
, orderdate
from
Sales.Orders
where
orderid between 10300 and 10310;
-- like谓词
select
empid
, firstname
, lastname
from
hr.Employees
where
lastname like N'D%';
-- 运算符：=, >, <, >=, <>, <=
-- 非标准运算符：!=, !>, !<
-- 逻辑运算符：or， and
-- 算数运算符：+，-，*，/，%
-- case表达式：简单表达式和搜索表达式
-- case简单格式将一个值（或一个标量表达式）与一组可能的取值进行比较，并返回第一个匹配的结果。
-- 如果列表中没有值等于测试值，case表达式就返回其else子句中列出的值。
-- 如果case表达式中没有else子句，则默认将其视为else null。
select
productid
, productname
, categoryid
, case categoryid
when 1 then 'Beverages'
when 2 then 'condiments'
when 3 then 'confections'
when 4 then 'dairy products'
else 'unkown category'
end as categoryname
from
production.products;
select
orderid
, custid
, val
, case ntile(3) over(order by val)
when 1 then 'low'
when 2 then 'medium'
when 3 then 'high'
else 'unkown'
end as titledesc
from
Sales.OrderValues
order by val;
-- case搜索表达式
select
orderid
, custid
, val
, case
when val < 1000.00 then 'less then 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;
-- 关于null
select
custid
, country
, region
, city
from
Sales.Customers
where
region = N'WA';
select
custid
, country
, region
, city
from
Sales.Customers
where
region is null;
-- 在用于比较和排序目的的不同语言元素中，sql处理null的方式也有所不同。一些元素
-- 认为两个null值彼此相等，而另一些则认为他们不相等。
-- 例如，当进行分组和排序时，认为两个null值是相等的。也就是说，group by子句会在每个组中重新组织所有的null值，
-- 就像有具体值得列一样；order by子句也会对所有null值进行排序。至于null值应该排在有效值之前还是之后，
-- ansi sql把它留给了具体的产品实现。t-sql是把null值排在了有效值之前。
-- ansi sql有两种unique约束：一种将多个null值视为相等的（只允许有一个null值），另一种则将多个null值视为不同的
-- （允许有多个null值）。sql server只实现了前者。
-- 记住这些sql在处理unknown和null值方面不一致的敌法个，以及发生逻辑错误的潜在可能，在编写每一条查询语句时应该
-- 明确地意识到正在使用的是三值谓词逻辑。如果默认的处理并不是你想要的效果，就必须显示地进行干预；否则，只要
-- 确保sql的默认行为是你实际上需要的就可以了。
-- 同时操作的概念和意义
-- 错误的例子，没有短路求值，可能会出现除零错误
-- select col1, col2
-- from dob.t1
-- where col1 <> 0 and col2 / col1 > 2;
-- 可以用case判断解决，但是代码麻烦
-- 可以用以下方法解决
-- select col1, col2
-- from dbo.t1
-- where col1 <> 0 and col2 > 2*col1;
-- 字符串相关
-- 系统中目前支持的所有排序规则及其描述
select
name
, description
from
sys.fn_helpcollations();
-- 让过滤条件区分大小写
select
empid
, firstname
, lastname
from
hr.Employees
where
lastname collate latin1_general_cs_as = N'davis';
-- 双引号"用于分隔不规则的标志符。在sql server中，有一个名为QUOTED_IDENTIFIER的设置选项，用于控制双引号
-- 的含义。可以在数据库级应用这个设置选项（用alter databse命令），也可以在会话级应用这个设置选项(用set命令）。
-- 当打开这个设置时，其行为符合标准sql的规定，双引号仅用于分割标志符。当关闭这个设置时，其行为就不是标准的了，
-- 双引号这时也可以用于分隔文字字符串（单引号的作用）。
-- 字符串连接符“+”。通过将一个名为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;
-- substring(string, start, length), 从1开始!!!!!
select substring('abcde', 1, 3);
-- left 和 right函数 left(string, n) right(string, n)
-- len和datalength函数
-- len返回字符串中的字符数，不包含尾随的空格
-- datalength返回字节数，包含尾随的空格
-- charindex函数返回字符串中某个子串第一次出现的起始位置
-- charindex(substring, sring[, start_pos])
-- patindex函数返回字符串中某个模式第一次出现的起始位置
-- patindex(pattern, string)
-- 参数pattern使用的模式与t-sql中like谓词的使用模式类似。
-- replace函数将字符串中出现的所有某个子串替换为另一个字符串
-- replace(string, substring1, substring2);
-- 可以使用replace函数来计算字符串中某个字符出现的次数。为此，先将字符串中所有的那个字符替换为空字符串，
-- 再计算字符串的原始长度和新长度的差值。
select
empid
, lastname
, LEN(lastname) - LEN(replace(lastname, 'e', '')) as numoccur
from
hr.Employees;
-- replicate函数以指定的次数复制字符串
-- replicate(string, n);
-- 对production.suppliers的查询为每个供应商的整数id生成一个10位数的字符串表示
select
supplierid
, right(replicate('0', 9) + cast(supplierid as varchar(10)), 10) as strsupplierid
from
Production.Suppliers;
-- stuff函数可以先删除字符串中的一个子串，再插入一个新的子字符串作为替换
-- stuff(string, pos, delete_length, insertstring);
select stuff('xyz', 2, 1, 'abc');
-- upper和lower函数
-- rtrim和ltrim函数
-- like谓词
-- %通配符代表任意长度的字符串，包括空字符串
-- _通配符代表单个字符
-- [<字符列>]通配符表示必须匹配匹配列指定字符中的一个字符（类似正则表达式）
-- escape（转义）字符：指定一个确保不会在数据中出现的字符作为转义字符，把它放在待查找的字符串前面，并紧接着
-- 模式字符串，在escape关键字后面制定该转义字符。例如，要检查一个名为col1的列中是否包含下划线，可以使用
-- col1 like '%!_%' escape'!'
-- 也可以使用 col1 like '%[_]%'
-- 日期时间类型
-- datetime 'YYYYMMDD hh:mm:ss.nnn'
-- smalldatetime 'YYYYMMDD hh:mm'
-- date 'YYYY-MM-DD'
-- time(0-7) 'hh:mm:ss.nnnnnnn'
-- datetime2(0-7) 'YYYY-MM-DD hh:mm:ss.nnnnnnn'
-- datetimeoffset(0-7) 'YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-]hh:mm'
-- 最后三种可选精度，默认为7
-- 为了有效的利用潜在索引，推荐使用(需要索引和性能的背景知识)
select
orderid
, custid
, empid
, orderdate
from
Sales.Orders
where
orderdate >= '20070201' and orderdate < '20080301';
-- 而不是
select
orderid
, custid
, empid
, orderdate
from
Sales.Orders
where
YEAR(orderdate) = 2007 and MONTH(orderdate) = 2;
-- getdate, current_timestamp, getutcdate,
-- sysdatetime(2008+), sysutcdatetime(2008+), sysdatetimeoffset(2008+)
-- 返回系统日期和时间。 除了current_timestamp，都需要多加一对圆括号。
select
GETDATE() as [getdate]
, current_timestamp as [current_timestamp]
, GETUTCDATE() as [getutcdate]
, SYSDATETIME() as [sysdatetime]
, SYSUTCDATETIME() as [sysutcdatetime]
, SYSDATETIMEOFFSET() as [sysdatetimewoffset];
-- cast和convert函数
-- cast(value as datatype)
-- convert(datatype, value, [, style_number])
-- cast是ansi标准的sql，而convert不是，推荐优先使用cast
select 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);
-- switchoffset函数可以按指定的时区对输入的datetimeoffset值进行调整
-- switchoffset(datetimeoffset_value, time_zone)
select SWITCHOFFSET(sysdatetimeoffset(), '-05:00');
select SWITCHOFFSET(sysdatetimeoffset(), '+00:00');
-- todatetimeoffset可以为输入的日期和时间值设置时区偏移量
-- todatetimeoffset(date_and_time_value, timezone)
-- 这个函数与swichoffset函数的区别有两点。首先，它可以接受的输入不限于datetimeoffset值，
-- 而是支持任何日期和时间数据类型。其次，它不是根据输入的原始值和指定的时区之间的差值来调整时间，
-- 而只是简单的利用指定的时区和时间值作为datetimeoffset值返回。
select todatetimeoffset(SYSDATETIMEOFFSET(), '-05:00');
select TODATETIMEOFFSET(sysdatetime(), '-05:00');
-- dateadd函数可以将指定日期的部分作为单位，为输入的日期和时间值增加指定的数量
-- dateadd(part, n, dt_val);
-- 日期部分的有效值包括year, quarter, month, dayofyear, day, week, weekday, hour, minute, sencond,
-- millisecond, nanosecond， 最后两个是2008新加的。
select dateadd(year, 1, '20090212');
-- datediff函数返回两个日期和时间值之间相差的指定部分的计数
-- datediff(part, dt_val, dt_val2)
select 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, '20010131', current_timestamp), '20010131');
-- DATEPART函数返回一个给定的日期和时间值的指定的整数
-- datepart(part, dt_val)
-- part部分包括year, quarter, month, dayofyear, day, week, weekday, hour, minute, sencond,
-- millisecond, microsecond, nanosecond, TZoffset, ISO_WEEK。最后四个是2008种增加的。
select DATEPART(month, '20090212');
-- year， month， day函数
-- datename函数返回一个给定日期和时间值部分的字符串
-- datename(part, dt_val)
select DATENAME(month, '20090212');
-- isdate函数接受一个字符串作为输入，如果能把这个字符串转换为日期和时间数据类型的值，则返回1，否则返回0。
-- isdate(string)
select ISDATE('20090212');
select ISDATE('20090230');
-- 查询元数据
-- 目录视图提供了关于数据库中各对象的非常详细的信息，包括sql server特定的信息。
-- 如果想列出数据库中的各个表，以及它们的架构名称，只要按一下所示的方法去查询sys.tables视图
use tsqlfundamentals2008;
select
SCHEMA_NAME(schema_id) as table_schema_name
, name as table_name
from
sys.tables;
-- 要得到有关某个表的列信息，可以查询sys.columns表。
-- 以下代码返回sales.orders表中的列信息，包括列名、数据类型（用type_name函数把系统类型id转换成类型名称）、
-- 最大长度、排序规则名称， 以及是否允许为null。
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架构内的一组视图，它们以一种标准化的方式来提供元数据信息。
-- 也就是说，这些视图是基于ansi sql标准而定义的，因此它们自然不会包含sql server特有的元数据。
-- 以下对information_schema.tables视图的查询可以列出当前数据库中的用户表，以及它们的架构名称
select table_schema, table_name
from information_schema.TABLES
where table_type = N'base table';
-- 以下对information_schema.columns视图的查询提供了有关sales.orders表中各个列的绝大多数的可用信息
select
column_name
, data_type
, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS
where
table_schema = N'sales'
and table_name = N'orders';
-- sp_tables存储过程返回可以在当前数据库中查询的对象列表：
exec sys.sp_tables;
-- sp_help存储过程接受一个对象名称作为输入，返回与之相关的多个结果集，包含了有关对象的一般信息，以及关于列、
-- 索引、约束等对象的信息。以下代码返回关于orders表的详细信息：
exec sys.sp_help
@objname = N'sales.orders';
-- sp_columns存储过程返回对象中有关列的信息。以下代码返回orders表中关于列的详细信息：
exec sys.sp_columns
@table_name = N'orders',
@table_owner = N'Sales';
-- sp_helpconstraint存储过程返回对象中关于约束的信息。例如，以下代码返回orders表中关于约束的信息
exec sys.sp_helpconstraint
@objname = N'sales.orders';
-- 还有一组函数可以返回关于数据库实体的各属性信息。
-- serverproperty函数返回当前数据库实例的指定属性信息。
-- 以下代码返回当前数据库实例的版本级别
select serverproperty('productlevel');
-- databasepropertyex函数返回最后定数据库的特定属性的信息。
-- 下列代码返回tsqlfundamentals2008的排序规则的当前设置
select DATABASEPROPERTYEX(N'tsqlfundamentals2008', 'collation');
-- objectproperty函数返回指定对象的特定属性的信息。
-- 以下代码的输出可以表明orders表是否具有主键
select objectproperty(object_id(N'sales_orders'), 'TableHasPrimaryKey');
-- columnproperty函数返回指定列上的特定属性的信息。
-- 以下代码的输出可以表明orders表中的shipcountry列是否可以为null
select
COLUMNPROPERTY(object_id(N'sales.orders'), N'shipcountry', 'allownull');
-- 练习
-- 第一题 返回2007年6月的订单
select
orderid
, orderdate
, custid
, empid
from
sales.Orders
where
orderdate >= '20070601' and orderdate < '20070701';
-- 第二题 返回每个月最后一天的订单
select
orderid
, orderdate
, custid
, empid
from
sales.Orders
where
month(dateadd(day, 1, orderdate)) = month(DATEADD(month, 1, orderdate));
-- 答案
select orderid, orderdate, custid, empid
from Sales.Orders
where orderdate = DATEADD(month, datediff(month, '19991231', orderdate), '19991231');
-- 第三题 返回姓氏（last name）中包含字母‘a'两次或更多次的雇员
select
empid
, firstname
, lastname
from
hr.Employees
where
lastname like '%a%a%';
-- 第四题 返回总价格（数量 * 单价）大于10000的所有订单，并按总价格排序
-- 错误，总价，先分组
--select
-- orderid
-- , (unitprice * qty) as totalvalue
--from
-- sales.OrderDetails
--where
-- (unitprice * qty) > 10000
--order by
-- totalvalue desc;
select
orderid
, sum((unitprice * qty)) as totalvalue
from
Sales.OrderDetails
group by
orderid
having
sum(unitprice * qty) > 10000
order by
totalvalue desc;
-- 第五题 返回2007年平均运费最高的三个发货国家(结果与答案不同)（没写年份！！！！）
select top (3)
shipcountry
, avg(freight) as avgfreight
from
sales.Orders
where
year(orderdate) = 2007
group by
shipcountry
order by
avgfreight desc;
-- 第六题 为每个顾客单独根据订单日期的顺序（用orderid作为附加属性）里计算其订单的行号。
select
custid
, orderdate
, orderid
, ROW_NUMBER() over(order by custid, orderid) as rownumber
from
Sales.orders
order by
custid, orderid;
-- 第七题 构造一个select语句，让它根据每个雇员的友好称谓，而返回性别。对于'Ms.'和
-- 'Mrs.'，返回'Female';对于'Mr.'则返回'Male'; 对于其他情况（例如，'Dr.')，则返回'unknown'。
select
empid
, firstname
, lastname
, titleofcourtesy
, case titleofcourtesy
when 'Mr.' then 'Male'
when 'Mrs.' then 'Female'
when 'Ms' then 'Female'
else 'Unknown'
end as gender
from
hr.Employees;
-- 或
select empid, firstname, lastname, titleofcourtesy,
case
when titleofcourtesy in ('Ms.', 'mrs.') then 'Female'
when titleofcourtesy = 'mr.' then 'Male'
else 'Unknown'
end as gender
from hr.Employees;
-- 第八题 返回每个客户的客户id和所在区域。对输出中的行按区域排序，null值排在最后面。
-- （注意，tsql中默认是把null排在前面的）
-- 为了把null值排在最后，可以用一个case表达式，当region列为null时就返回1，当region列不为null是就返回0.
-- 非null值得表达式返回值为0，因此，他们会排在null值的前面。把case表达式作为第一个排序列，并把region列
-- 指定为第二个排序列。这样，非null值也可以正确的参与排序。
select
custid
, region
from
sales.Customers
order by
case when region is null then 1 else 0 end
, region;