T-SQL学习笔记1

一个月之前就注册了这个博客，今天才开始动笔写，真是有点。。。。

最近开始看面试题发现一个月之前看的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;