sql server 常用函数等

sql声明周期：
OLTP:
DSA
DW
BISM
DM
ETL
MDX
MAX
DMX

SQL 数据完整性
1.主键约束
2.唯一约束
3.外键约束
4.check约束
5.默认约束

sql执行顺序
1.from
2.where
3.group by
4.having
5.select
  表达式
  distinct
6.order by
  top/offset-ftch

TOP和OFFSET-FETCH筛选
1.top返回多少shao
2.OFFSET-FETCH可以跳过多少行，然后返回多少行

开窗函数ROW_NUMBER
1.可以自动生成唯一键

where条件的三个值
1.true
2.false
3.unknown

in，between，like
1.in.一个或多少值至少包含
2.between范围
3.like模糊查询

sql的比较运算符
1.=
2.>
3.<
4.>=
5.<=
6.<>
7.!=,!>,!< （非标准）

sql的算数运算符
1.+
2.-
3.*
4./
5.%
6.整数和整数运算 5/2=2 不会带小数

sql优先级
1.（）
2.*，/，%
3.+，-
4.比较运算符
5.not
6.and
7.between，in，like，or
8.=

case标量表达式
1.可以在select，where，having，order by 以及check中使用
2.

一些判断的sql
1.coalesce 将空值替换成其他值
2.iif判断
3.choose按照第一个参数取后面的值

常规数据和unicode
1.常规数据：char，varchar 除英语外只能使用一个字符 直接单引号就行
2.unicode：Nchar和Nvarchar 可以支持多种语言 需要指定前缀N

char和Nchar  varchar和Nvarchar
1.是固定长度 
2.是可变长度，按实际取

Nvarchar（max）
1.最大支持8000个字节

排序规则
1.是一个字符数据属性，包括语言支持，排序顺序，区分大小写，区分重音等，对元数据的默认排序。
2.COLLATE子句可以设置排序规则

字符串的拼接
1.+：拼接空的话，直接返回空
2.concat：忽略null
3.coalesce：筛选，返回一个非空的

SUBSTRING，left，right函数
1.三个参数：字符串，从多少开始，截取多少
2：left，right是SUBSTRING的简化版本

len，datalength 获取字符串长度
1.len：返回输入字符数
2.datalength：返回输入字节数

charindex
1：返回子字符在字符串中第一次出现的位置

patindex
1.类似于like的模式。

replace
1.可以使用另一个字符串替换出现的所有字符串

replicate
1.可以指定一个字符串重复出现多少次

stuff 
1.允许从字符串中移除指定数量的字符
2.字符串 第几位开始 移除多少 追加的字符

upper和lower
1.upper：转大写
2.lower：转xiaoxie

rtrim和lerim
1.删除前面或者后面空格

format
1.自定义格式字符串输入，按照标准对字符串进行转换

like谓词
1.%通配符
2._表示单个字符
3.[xxxx]必须是指定中的一个
4.[A-E]表示范围
5.[^A-E]表示不在这个范围

sql日期时间数据类型
1.datetime：8字节  精准度31/3毫秒
2.smalldatetime 4字节 1分钟
3.date 3~5字节 1天
4.time 3~5字节 100纳秒
5.datetime2 6~8字节 100纳秒
6.datetimeoffset 8~10字节 100纳秒
7.set language xxxx 可能对时间做出不同解释

yaer，month
1.虽然可以获取到值但是会破坏索引，可以用>='20200101' and <'20200201'

获取当前日期和时间
1.getdate:当时间和日期
2.current_timestamp:等同于上一个，但是遵循ansisql
3.getutcdate:当前utc时间
4.sysdatetime:当时间和日期
5.sysutcdatetime:当前utc时间
6.sysdatetimeoffset:包含时区偏移量的当前时间

数据转换case，convert，parse
1.都提供try_xxxx，区别是转换失败会返回null
2.convert，parse可以指定转换类型

switchoffset
1.可以把时间的值调整为指定时区

todatetimeoffset
1.简单的把两个值合并成一个新值

dateadd
1.对时间进行加操作

datetimeiff
1.返回两个时间相差的时间

datepart
1.返回时间或日期的值部分的整数

datename
1.返回时间或日期的值部分的整数，是字符串格式

isdate
1.判断是否是时间，是1否0

fromparts
1.可以快速的把时间拼接起来，组成时间格式

eomonth
1.返回当月的月末

schema_name
1.可以将架构id转换成名字

sys.tables
1.存放着表和架构信息

sys.columns
1.存放着表中列信息

sys.sp_help
1.返回对象常用信息的多个结果集，以及列，索引，约束等信息

sys.sp_columns
1.返回对象列的信息

sys.sp_helpconstraint
1.返回对象中的约束信息

联接
1.join 标准
2.apply t-sql拓展
3.pivot t-sql拓展
4.unpivot t-sql拓展

交叉联接
1.两个表的笛卡尔积
2.cross join

自交叉链接
1.交叉链接，内部链接，外部链接

内部链接 
1.inner join 

sql-89和sql-92标准
1.推荐使用sql-92

复合联接
1.指定多个联接条件

相等联接，不相等联接
1.联接条件用到了除等号的任何运算符

多联接查询
1.单个查询中有多个联接

外部联接
1.left join
2.right join

on和where 条件
1.on并不负责数据是否被最终展示，只是判断是否和另外表中数据是否关联

关于子查询和联接查询
1.有的时候两种方式都能执行，谁的效率最高很难讲，需要结合实际情况

exists
1.可接受一个子查询输入。这是一种短路计算功能。性能好。

top x with ties
1.可以查询出多条记录，比如查询成绩有并列第一的情况

派生表（子查询表）
1.无法保证顺序
2.所有列都必须具有名称
3.所有列明必须是唯一的

派生表于CTE
1.从一个CTE引用另一个CTE变得简单不需要嵌套
2.wiht关键词

视图
1.视图行没有顺序可言，因为它违反了关系模型定义基本特征
2.只能在视图外部查询中order by

encryption
1.可以代码混肴文本

object_definition
1.可以查询视图等内部代码

schemabinding
1.禁止修改视图等架构

check option
1.如果通过视图添加，数据必须符合视图的条件

内嵌TVF
1.支持输入参数

apply
1.cross apply
2.outer apply

集合运算符

union all
1.返回全部，没有真正的进行比较，也没有消除重复行

union 
1.不组合两个查询结果并且消除重复行

intersect
1.返回非重复结果

except
1.只返回第一个里面出现，第二个里面没有出现的数据

优先级问题
1.intersect>union =except

排名开窗函数
1.ROW_NUMBER
2.RANK
3.DENSE_BANK
4.NTILE

偏移开窗函数
1.LAG:上一个
2.LEAD:下一个
3.FIRST_VALUE:第一行
4.LAST_VALUE:最后一行

聚合开窗函数
1.sum(value) over():会返回所有值的总和
2.sum(value) over(xxxx):符合相应条件的总和

透视数据
1.pivot：把行转成列进行聚合等操作

逆透视数据
1.unpivot

分组集
1.grouping sets：按照很多条件多次对数据分组，结果合成一张表
2.cube：提供一个幂集，效果和1一样
3.使用分组集，没有参与的列null，但是可能本身就是null。使用函数grouping()，结果返回1，0

插入数据
1.insert values
2.insert select
3.insert exec
4.select into
5.bulk insert

当设置了自增列，想自己设置id
1.set identity_insert xxxx on  set identity_insert xxxx off

删除数据
1.delete
2.truncate
3.drop

update
1.

合并数据 merge
1.提供insert，update，delete

output
1.可以把刚刚新增修改删除的数据输出出来



事务
1.begin tran 开始事务
2.commit tran 提交事务
3.rollback tran 回滚事务

排它锁和共享锁
1.一个事务持有了排他锁，你就不能获得资源上的任何锁模式。能不能读取，取决于当前语句的隔离级别


可编辑对象
1.declare @xxx set @xxx=‘’ 
2.sql server management studio，sqlcmd，osql 可以批量进行处理

关于GO
1.并不是t-sql语言，是客户端工具的命令

if...else流元素
1.false和unknown都能激活else

while流元素
1.

游标
1.所做的按记录的逐个操作需要耗费一定的开销
2.声明，打开，遍历，关闭，释放

临时表
1.#本地临时表
2.##全局临时表

动态sql
1.exec执行动态sql







```sql
------------------------------------in的用法-------------------------------------------------------

--in里面数据量比较少时，可以使用
select * from [dbo].questionrecord where [questionid] In ('E09ACF40-0974-4898-B360-32F81A2B8D8B')
--外部查询数据量大，内部查询数据量小，推荐使用
select * from [dbo].questionrecord 
 where [questionid] In (select [questionid] from [dbo].questionrecord )
--内部查询量大，外部查询相对来说比较小，推荐使用
select * from [dbo].questionrecord 
where exists (select 1 from [dbo].questionrecord where questionrecord.ID=questionrecord.ID )

--如果in数量比较大，推荐创建临时表，给临时表加索引的方式解决



------------------------------------between的用法-------------------------------------------------------
--时间，数字，文本等都可以排序
select * from [dbo].questionrecord  where [option] BETWEEN 1 and 4


------------------------------------like的用法-------------------------------------------------------

--*:表示多个字符。比如这个意思就是以A结尾
select * from [dbo].questionrecord  where ID like '*A'
--%:这种方法在很多程序中要用到，主要是查询包含子串的。
select * from [dbo].questionrecord  where ID like '%A%'
--?:同于DOS命令中的？通配符，代表单个字符
select * from [dbo].questionrecord  where ID like 'A?'
--#:大致同上，不同的是代只能代表单个数字。
select * from [dbo].questionrecord  where ID like 'A#'
--[a-z],[!0-9],[0-9]等 组合类型 字符[范围类型]字符 cc[!a-d]#代表ccF#等 可以和其它几种方式组合使用
select * from [dbo].questionrecord  where ID like 'A[0-9]'
--开头和结尾都是%,对不起，很难优化
--SQL> select * from test_like where object_name like '%%';
--以常量结束，直接写的时候是不能应用index的
--SQL> select * from test_like where object_name like '%S';
--以常量结束的，加个reverse 函数，又可以用上index了
--SQL> select * from test_like where reverse(object_name)like reverse('%AS');


------------------------------------top的用法-------------------------------------------------------
select top 1 * from [dbo].questionrecord  
--注意此查询语句。会把外部查询和内部查询合并成一条语句。所以返回的并不是你想要的结果。
select top 2 * from (select top 4 * from questionrecord) m order by m.id desc 


------------------------------------OFFSET FETCH的用法-------------------------------------------------------

--无论是从逻辑读取数还是响应时间、实际执行行数等关键参数看，SQL Server 2012提供的OFFSET/FETCH NEXT分页方式都比Row_Number()方式有了较大的提升。
select  * from [dbo].questionrecord  
ORDER BY createdatetima  
OFFSET 0 * 10 ROWS
FETCH NEXT 10 ROWS ONLY;


------------------------------------row_number的用法-------------------------------------------------------
--说明：返回结果集分区内行的序列号，每个分区的第一行从1开始。
--语法：ROW_NUMBER () OVER  ([ <partition_by_clause> ] <order_by_clause>) 。
--备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
--参数：<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
--      <order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。

--使用row_number（）函数进行编号，如
select ID,ROW_NUMBER() over(order by createdatetima) as rows  from [dbo].questionrecord  
--先按照partition by进行分区，对分区结果分别进行序号输出
select [questionid], ROW_NUMBER() over(partition by iscorrect  order by createdatetima) as rows from questionrecord

 
------------------------------------CASE WHEN的用法-------------------------------------------------------

--可以在select，where，having，order by 以及check中使用
select
case iscorrect when 0 then '不合格' else '合格' end
from [dbo].questionrecord

select
(case when iscorrect=0 then '不合格' 
when iscorrect=1 then '合格'
else '无' end ) x
from [dbo].questionrecord

------------------------------------coalesce的用法-------------------------------------------------------


--将空值替换成其他值否则返回第一个非空值
select coalesce(modificationdatetima,getdate()) a from [dbo].questionrecord


------------------------------------iif的用法-------------------------------------------------------
select iif(1=1,1,0)


------------------------------------CHOOSE的用法-------------------------------------------------------
SELECT CHOOSE(0, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Zero Index value';
SELECT CHOOSE(1, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'First Index value';
SELECT CHOOSE(2, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Second Index value';
SELECT CHOOSE(3, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Third Index value';


------------------------------------COLLATE的用法-------------------------------------------------------

--两个表排序规则不一样，修改排序规则
--是一个字符数据属性，包括语言支持，排序顺序，区分大小写，区分重音等，对元数据的默认排序。
select * from [dbo].questionrecord join [dbo].question on questionrecord.questionid=question.ID COLLATE Chinese_PRC_CI_AI_WS


------------------------------------对字符串的操作以及CONCAT的用法-------------------------------------------------------

--1.+：拼接空的话，直接返回空
select 'a'+null

--2.concat：忽略null
select CONCAT('a','b',null)


------------------------------------SUBSTRING，left，right的用法-------------------------------------------------------

--1.三个参数：字符串，从多少开始，截取多少
select SUBSTRING('1234579',1,2)

--2：left，right是SUBSTRING的简化版本
select left('1234579',1)
select right('1234579',1)


------------------------------------len，datalength的用法-------------------------------------------------------

--1.len：返回输入字符数
--2.datalength：返回输入字节数

select len('abc')--3
select len('abc我')--4
select datalength('abc')--3
select datalength('abc我')--5

select len(N'abc')--3
select len(N'abc我')--4
select datalength(N'abc')--6
select datalength(N'abc我')--8


------------------------------------charindex的用法-------------------------------------------------------

--函数返回字符或者字符串在另一个字符串中的起始位置
SELECT charindex('AA','ANCDEFG')
SELECT charindex('A','ANCDEFG')
SELECT charindex('G','ANCDEFG')


------------------------------------patindex的用法-------------------------------------------------------

--'%pattern%'的用法类似于 like '%pattern%'的用法，也就是模糊查找其pattern字符串是否是expression找到，找到并返回其第一次出现的位置。
select patindex('%b%','abcaabbeeabb')

------------------------------------replace的用法-------------------------------------------------------

--将String中所有出现的from_str替换为to_str。
select replace('abcaabbeeabb','a','A')


------------------------------------replace的用法-------------------------------------------------------
SELECT REPLICATE('0', 10)


------------------------------------stuff的用法-------------------------------------------------------

--删除指定长度的字符，并在指定的起点处插入另一组字符。
SELECT STUFF('abcdef', 2, 3, 'ijklmn')

--也可以用于追加拼接字符串
(stuff((select ',' + stock_type from [SH_EDW].dbo.tq_companyStockList where company_name=tq_projectbasic.company_name and  stock_type= stock_type for xml path('')),1,1,''))


------------------------------------upper和lower的用法-------------------------------------------------------

--1.转大写
select upper('aaaa')

--2.转小写
select lower('AAA')


------------------------------------rtrim和lerim的用法-------------------------------------------------------

--去除左边空格和去除右边空格
select rtrim(' a    ')
select ltrim(' a    ')


------------------------------------format的用法-------------------------------------------------------

--可以对时间数字字符串感觉固定格式进行转换，用法较多
SELECT FORMAT(getdate(),'yyyyMMdd') as PerDate


------------------------------------关于时间类型的各种用法-------------------------------------------------------

--1.datetime：8字节  精准度31/3毫秒
--2.smalldatetime 4字节 1分钟
--3.date 3~5字节 1天
--4.time 3~5字节 100纳秒
--5.datetime2 6~8字节 100纳秒
--6.datetimeoffset 8~10字节 100纳秒
--7.set language xxxx 可能对时间做出不同解释


------------------------------------year，month的各种用法-------------------------------------------------------

select year(GETDATE())
select month(GETDATE())


------------------------------------获取当前日期和时间-------------------------------------------------------

--1.getdate:当时间和日期
select getdate()
--2.current_timestamp:等同于上一个，但是遵循ansisql
select current_timestamp
--3.getutcdate:当前utc时间
select getutcdate()
--4.sysdatetime:当时间和日期
select sysdatetime()
--5.sysutcdatetime:当前utc时间
select sysutcdatetime()
--6.sysdatetimeoffset:包含时区偏移量的当前时间
select sysdatetimeoffset()


------------------------------------case，convert，parse的各种用法-------------------------------------------------------

--都提供try_xxxx，区别是转换失败会返回null
--convert，parse可以指定转换类型
select TRY_CONVERT(datetime,'dasd')

SELECT CONVERT(varchar(100), GETDATE(), 0)--: 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 1)--: 05/16/06
SELECT CONVERT(varchar(100), GETDATE(), 2)--: 06.05.16
SELECT CONVERT(varchar(100), GETDATE(), 3)--: 16/05/06
SELECT CONVERT(varchar(100), GETDATE(), 4)--: 16.05.06
SELECT CONVERT(varchar(100), GETDATE(), 5)--: 16-05-06
SELECT CONVERT(varchar(100), GETDATE(), 6)--: 16 05 06
SELECT CONVERT(varchar(100), GETDATE(), 7)--: 05 16, 06
SELECT CONVERT(varchar(100), GETDATE(), 8)--: 10:57:46
SELECT CONVERT(varchar(100), GETDATE(), 9)--: 05 16 2006 10:57:46:827AM
SELECT CONVERT(varchar(100), GETDATE(), 10)--: 05-16-06
SELECT CONVERT(varchar(100), GETDATE(), 11)--: 06/05/16
SELECT CONVERT(varchar(100), GETDATE(), 12)--: 060516
SELECT CONVERT(varchar(100), GETDATE(), 13)--: 16 05 2006 10:57:46:937
SELECT CONVERT(varchar(100), GETDATE(), 14)--: 10:57:46:967
SELECT CONVERT(varchar(100), GETDATE(), 20)--: 2006-05-16 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 21)--: 2006-05-16 10:57:47.157
SELECT CONVERT(varchar(100), GETDATE(), 22)--: 05/16/06 10:57:47 AM
SELECT CONVERT(varchar(100), GETDATE(), 23)--: 2006-05-16
SELECT CONVERT(varchar(100), GETDATE(), 24)--: 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 25)--: 2006-05-16 10:57:47.250
SELECT CONVERT(varchar(100), GETDATE(), 100)--: 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 101)--: 05/16/2006
SELECT CONVERT(varchar(100), GETDATE(), 102)--: 2006.05.16
SELECT CONVERT(varchar(100), GETDATE(), 103)--: 16/05/2006
SELECT CONVERT(varchar(100), GETDATE(), 104)--: 16.05.2006
SELECT CONVERT(varchar(100), GETDATE(), 105)--: 16-05-2006
SELECT CONVERT(varchar(100), GETDATE(), 106)--: 16 05 2006
SELECT CONVERT(varchar(100), GETDATE(), 107)--: 05 16, 2006
SELECT CONVERT(varchar(100), GETDATE(), 108)--: 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 109)--: 05 16 2006 10:57:49:437AM
SELECT CONVERT(varchar(100), GETDATE(), 110)--: 05-16-2006
SELECT CONVERT(varchar(100), GETDATE(), 111)--: 2006/05/16
SELECT CONVERT(varchar(100), GETDATE(), 112)--: 20060516
SELECT CONVERT(varchar(100), GETDATE(), 113)--: 16 05 2006 10:57:49:513
SELECT CONVERT(varchar(100), GETDATE(), 114)--: 10:57:49:547
SELECT CONVERT(varchar(100), GETDATE(), 120)--: 2006-05-16 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 121)--: 2006-05-16 10:57:49.700
SELECT CONVERT(varchar(100), GETDATE(), 126)--: 2006-05-16T10:57:49.827
SELECT CONVERT(varchar(100), GETDATE(), 130)--: 18 ???? ?????? 1427 10:57:49:907AM
SELECT CONVERT(varchar(100), GETDATE(), 131)--: 18/04/1427 10:57:49:920AM


------------------------------------switchoffset的各种用法-------------------------------------------------------

--返回一个datetimeoffset值，它是从存储的时区偏移到指定的新时区偏移的改变。
select switchoffset(getutcdate(),'+08:00')


------------------------------------todatetimeoffset的各种用法-------------------------------------------------------
--【暂时不知道具体用法】
select todatetimeoffset(getutcdate(),'+08:00')


------------------------------------dateadd的各种用法-------------------------------------------------------

--年	yy, yyyy
--季度	qq, q
--月	mm, m
--年中的日	dy, y
--日	dd, d
--周	wk, ww
--星期	dw, w
--小时	hh
--分钟	mi, n
--秒	ss, s
--毫秒	ms
--微妙	mcs
--纳秒	ns
select DATEADD(DAY,8,getdate())


------------------------------------DATEDIFF 的各种用法-------------------------------------------------------

--1.返回两个时间相差的时间
--date-part : year | quarter | month | week | day | hour | minute | second | millisecond
select DATEDIFF (DAY,'2021-05-06 05:05:05','2021-05-07 05:05:05')


------------------------------------datepart 的各种用法-------------------------------------------------------

--1.返回时间或日期的值部分的整数
--year	yy, yyyy
--quarter	qq, q
--month	mm, m
--dayofyear	dy, y
--day	dd, d
--week	wk, ww
--weekday	dw
--Hour	hh
--minute	mi, n
--second	ss, s
--millisecond	ms
select datepart(year,GETDATE())


------------------------------------datename 的各种用法-------------------------------------------------------

--1.返回时间或日期的值部分的整数，是字符串格式
--year	yy, yyyy	年
--quarter	qq, q	季度
--month	mm, m	月
--dayofyear	dy, y	一年中的第几天
--day	dd, d	日
--week	wk, ww	一年中的第几周
--weekday	dw日期部分返回对应于星期中的某天的数，例如：Sunday =1	星期几
--Hour	hh	小时
--minute	mi, n	分钟
--second	ss, s	秒
--millisecond	ms	毫秒

select datename(year,GETDATE())


------------------------------------isdate 的各种用法-------------------------------------------------------

--判断是否时间格式
select isdate(GETDATE())


------------------------------------fromparts 的各种用法-------------------------------------------------------

--1.可以快速的把时间拼接起来，组成时间格式
select DATEfromparts('2021','05','06')
select DATEtimefromparts('2021','05','06','12','59','30','55')


------------------------------------eomonth 的各种用法-------------------------------------------------------

--1.返回当月的月末
select eomonth(GETDATE())


------------------------------------sys.tables 的各种用法-------------------------------------------------------

--1.存放着表和架构信息

--object_id	对象标识号。在数据库中是唯一的。
--principal_id	单个所有者的ID（如果与架构所有者不同）。默认情况下，包含架构的对象由架构所有者拥有。但是，可以使用ALTER AUTHORIZATION语句指定备用所有者以更改所有权。
--schema_id	包含对象的库的ID。
--parent_object_id	此对象所属的父级的ID。
--type	对象类型：
--type_desc	对象类型的描述：
--create_date	创建对象的日期。
--modify_date	使用ALTER语句对上次修改对象的日期。如果对象是表或视图，则在创建或更改表或视图上的聚簇索引时，modify_date也会更改。
--is_ms_shipped	对象由内部SQL Server组件创建。
--is_published	对象已发布。
--is_schema_published	仅发布对象的架构。
--lob_data_space_id	非零值是保存此表的大对象二进制（LOB）数据的数据空间（文件组或分区方案）的ID。LOB数据类型的示例包括varbinary（max），varchar（max），geography或xml。
--filestream_data_space_id	是FILESTREAM文件组的数据空间ID还是由FILESTREAM文件组组成的分区方案。
--max_column_id_used	此表使用的最大列ID。
--lock_on_bulk_load	表在批量加载时被锁定。有关更多信息，请参见sp_tableoption（Transact-SQL）。
--uses_ansi_nulls	使用SET ANSI_NULLS数据库选项ON创建表。
--is_replicated	1 =使用快照复制或事务复制发布表。
--has_replication_filter	1 =表具有复制过滤器。
--is_merge_published	1 =使用合并复制发布表。
--is_sync_tran_subscribed	1 =使用立即更新订阅订阅表。
--has_unchecked_assembly_data	1 =表包含持久化数据，这些数据取决于在最后一个ALTER ASSEMBLY期间其定义发生更改的程序集。在下一次成功的DBCC CHECKDB或DBCC CHECKTABLE之后将重置为0。
--text_in_row_limit	行中文本允许的最大字节数。
--large_value_types_out_of_row	1 =大值类型存储在行外。有关更多信息，请参见sp_tableoption（Transact-SQL）。
--is_tracked_by_cdc	1 =启用表以进行更改数据捕获。有关更多信息，请参见sys.sp_cdc_enable_table（Transact-SQL）。
--lock_escalation	表的LOCK_ESCALATION选项的值：
--lock_escalation_desc	表的lock_escalation选项的文本描述。可能的值有：TABLE，AUTO和DISABLE。

select * from sys.tables


------------------------------------sys.columns 的各种用法-------------------------------------------------------

--name
--
--sysname
--
--列名或过程参数的名称。
--
--id
--
--int
--
--此列所属表的对象 ID，或者与此参数关联的存储过程的 ID。
--
--xtype
--
--tinyint
--
--sys.types 中的物理存储类型。
--
--typestat
--
--tinyint
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--xusertype
--
--smallint
--
--扩展的用户定义数据类型的 ID。如果数据类型的数字超过 32,767，则溢出或返回 NULL。有关详细信息，请参阅 查询 SQL Server 系统目录。
--
--length
--
--smallint
--
--sys.types 中的最大物理存储长度。
--
--xprec
--
--tinyint
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--xscale
--
--tinyint
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--colid
--
--smallint
--
--列 ID 或参数 ID。
--
--xoffset
--
--smallint
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--bitpos
--
--tinyint
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--reserved
--
--tinyint
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--colstat
--
--smallint
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--cdefault
--
--int
--
--此列的默认值的 ID。
--
--domain
--
--int
--
--此列的规则或 CHECK 约束的 ID。
--
--number
--
--smallint
--
--过程分组时的子过程号。
--
--0 = 非过程项
--
--colorder
--
--smallint
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--autoval
--
--varbinary(8000)
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--offset
--
--smallint
--
--此列所在行的偏移量。
--
--collationid
--
--int
--
--列的排序规则的 ID。对于非字符列，此值为 NULL。
--
--status
--
--tinyint
--
--用于说明列或参数的属性的位图：
--
--0x08 = 列允许空值。
--
--0x10 = 添加 varchar 或 varbinary 列时，执行 ANSI 填充。保留 varchar 列的尾随空格以及 varbinary 列的尾随零。
--
--0x40 = 参数为 OUTPUT 参数。
--
--0x80 = 列为标识列。
--
--type
--
--tinyint
--
--sys.types 中的物理存储类型。
--
--usertype
--
--smallint
--
--sys.types 中的用户定义数据类型的 ID。如果数据类型数超过 32767，则会发生溢出或返回 NULL。有关详细信息，请参阅 查询 SQL Server 系统目录。
--
--printfmt
--
--varchar(255)
--
--标识为仅供参考。不提供支持。不保证以后的兼容性。
--
--prec
--
--smallint
--
--此列的精度级别。
--
---1 = xml 或大值类型。
--
--scale
--
--int
--
--此列的小数位数。
--
--NULL = 数据类型不是数值。
--
--iscomputed
--
--int
--
--指示列是否为计算列的标志：
--
--0 = 非计算列。
--
--1 = 计算列。
--
--isoutparam
--
--int
--
--指示过程参数是否为输出参数：
--
--1 = True
--
--0 = False
--
--isnullable
--
--int
--
--指示列是否允许空值：
--
--1 = True
--
--0 = False
--
--collation
--
--sysname
--
--列的排序规则的名称。如果不是基于字符的列，则为 NULL

select * from sys.columns where object_id=object_id(N'question')


------------------------------------schema_name和object_id 的各种用法-------------------------------------------------------

--将id转换成架构名
select schema_name(schema_id) from sys.tables
--将id转换成表名
select * from sys.columns where object_id=object_id(N'question')


------------------------------------sys.sp_help 的各种用法-------------------------------------------------------

--1.返回对象常用信息的多个结果集，以及列，索引，约束等信息
exec sys.sp_help @objname=N'question'


------------------------------------apply 的各种用法-------------------------------------------------------

--Cross Apply和Inner Join 有很多相同处，都是为了联表查询数据，而且取两张表之间的交集，不同在于，Cross apply 可以在 右表中加入左表的字段作为条件。
--APPLY有两种形式，一个是OUTER APPLY，一个是CROSS APPLY，区别在于指定OUTER，意味着结果集中将包含使右表表达式为空的左表表达式中的行，而指定CROSS，则相反，结果集中不包含使右表表达式为空的左表表达式中的行。

select a.id,t.questionid from question as a
cross apply 
(select top 1 *  from questionrecord as b where b.questionid=a.id)
as t 


------------------------------------PIVOT 的各种用法-------------------------------------------------------

--PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现
--PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

if object_id('tb')is not null drop table tb
go
create table tb(姓名 varchar(10),课程 varchar(10),分数 int)
insert into tb values('张三','语文',74)
insert into tb values('张三','数学',83)
insert into tb values('张三','物理',93)
insert into tb values('李四','语文',74)
insert into tb values('李四','数学',84)
insert into tb values('李四','物理',94)
go

select * from tb

select 姓名,
 max(case 课程 when '语文' then 分数 else 0 end)语文,
 max(case 课程 when '数学'then 分数 else 0 end)数学,
 max(case 课程 when '物理'then 分数 else 0 end)物理
from tb
group by 姓名

select * from tb pivot(max(分数) for 课程 in (语文,数学,物理)) a


------------------------------------unpivot 的各种用法-------------------------------------------------------

--列转行
CREATE TABLE dbo.CustomerPhones
(
  CustomerID INT PRIMARY KEY, -- FK
  Phone1 VARCHAR(32),
  Phone2 VARCHAR(32),
  Phone3 VARCHAR(32)
);
INSERT dbo.CustomerPhones
  (CustomerID, Phone1, Phone2, Phone3)
VALUES
  (1,'705-491-1111', '705-491-1110', NULL),
  (2,'613-492-2222', NULL, NULL),
  (3,'416-493-3333', '416-493-3330', '416-493-3339');

  select * from dbo.CustomerPhones

  --##Phone1、Phone2、Phone3这些列的结果转为行上的结果，成为一列，
  --##并且有了新的列名为Phone
select * from dbo.CustomerPhones   -----数据源
    unpivot 
(   
     Phone FOR Phones IN (Phone1, Phone2, Phone3)

) up


------------------------------------链表 的各种用法-------------------------------------------------------

--1.交叉链接，内部链接，外部链接
--概念：根据两个表或多个表的列之间的关系，从这些表中查询数据。
--目的：实现多个表查询操作。

--关于子查询和联接查询
--1.有的时候两种方式都能执行，谁的效率最高很难讲，需要结合实际情况

--sql-89和sql-92标准
--1.推荐使用sql-92

--内连接（INNER JOIN）
--       1、等值连接：在连接条件中使用等于号（=）运算符，其查询结果中列出被连接表中的所有列，包括其中的重复列。
select a.id,t.questionid from question as a
 join 
(select  *  from questionrecord as b )
as t on a.ID=t.questionid

--       2、不等连接：在连接条件中使用除等于号之外运算符（>、<、<>、>=、<=、!>和!<）
select a.id,t.questionid from question as a
 join 
(select  *  from questionrecord as b )
as t on a.ID<>t.questionid

--外连接
--      外连接分为左连接（LEFT JOIN）或左外连接（LEFT OUTER JOIN）、右连接（RIGHT JOIN）或右外连接（RIGHT OUTER JOIN）、
--      全连接（FULL JOIN）或全外连接（FULL OUTER JOIN）。我们就简单的叫：左连接、右连接和全连接。
--       1、左连接：
select a.id,t.questionid from question as a
left join 
(select  *  from questionrecord as b )
as t on a.ID=t.questionid
--       2、右连接：
select a.id,t.questionid from question as a
right join 
(select  *  from questionrecord as b )
as t on a.ID=t.questionid
--       3、全连接：
select a.id,t.questionid from question as a
full join 
(select  *  from questionrecord as b )
as t on a.ID=t.questionid

--交叉连接（CROSS JOIN）：也称迪卡尔积
--    概念：不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积（例如：T_student和T_class，返回4*4=16条记录），
--    如果带where，返回或显示的是匹配的行数。
select a.id,t.questionid from question as a
cross join 
(select  *  from questionrecord as b )
as t 


------------------------------------exists 的各种用法---------------------------------------------------------

--EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
--EXISTS 指定一个子查询，检测 行 的存在。
select a.id from question as a
where EXISTS
(select * from questionrecord as b where a.ID=b.questionid )


------------------------------------top x with ties 的各种用法---------------------------------------------------------

--可以查询出并列的top。比如考试成绩，并列第一
select top 1 with ties a.id from question as a
order by a.id 


------------------------------------派生表的注意事项---------------------------------------------------------
--1.无法保证顺序
--2.所有列都必须具有名称
--3.所有列明必须是唯一的


------------------------------------派生表的注意事项---------------------------------------------------------

--1.视图行没有顺序可言，因为它违反了关系模型定义基本特征
--2.只能在视图外部查询中order by
--
--encryption
--1.可以代码混肴文本
--
--object_definition
--1.可以查询视图等内部代码
--
--schemabinding
--1.禁止修改视图等架构
--
--check option
--1.如果通过视图添加，数据必须符合视图的条件


------------------------------------union all，intersect，except的各种用法---------------------------------------------------------
--优先级问题
--1.intersect>union =except

--union  默认地，UNION 操作符选取不同的值，消除重复行
select * from question 
union
select * from question 

--union all 返回全部，没有真正的进行比较，也没有消除重复行
select * from question 
union all
select * from question 

--intersect 返回查询结果中相同的部分既他们的交集
select * from question 
intersect
select * from question 

--except 只返回第一个里面出现，第二个里面没有出现的数据
select * from question 
except
select * from question 


------------------------------------Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE ---------------------------------------------------------

CREATE TABLE [dbo].[Order](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [TotalPrice] [int] NOT NULL,
    [SubTime] [datetime] NOT NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Order] ON 

GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (1, 1, 100, CAST(0x0000A419011D32AF AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (2, 2, 500, CAST(0x0000A419011D40BA AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (3, 3, 300, CAST(0x0000A419011D4641 AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (4, 2, 1000, CAST(0x0000A419011D4B72 AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (5, 1, 520, CAST(0x0000A419011D50F3 AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (6, 2, 2000, CAST(0x0000A419011E50C9 AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Order] OFF
GO
ALTER TABLE [dbo].[Order] ADD  CONSTRAINT [DF_Order_SubTime]  DEFAULT (getdate()) FOR [SubTime]
GO

--ROW_NUMBER
--          row_number的用途的非常广泛，排序最好用他，一般可以用来实现web程序的分页，他会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。row_number用法实例:
--注意：在使用row_number实现分页时需要特别注意一点，over子句中的order by 要与Sql排序记录中的order by 保持一致，否则得到的序号可能不是连续的。下面我们写一个例子来证实这一点，将上面Sql语句中的排序字段由SubTime改为TotalPrice。另外提一下，对于带有子查询和CTE的查询，子查询和CTE查询有序并不代表整个查询有序，除非显示指定了order by。
select ROW_NUMBER() OVER(order by [SubTime] desc) as row_num,* from [Order] order by [TotalPrice] desc

--RANK
--     　　rank函数用于返回结果集的分区内每行的排名， 行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，
--         rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，
--         也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。可能我描述的比较苍白，理解起来也比较吃力，我们直接上代码，
--         rank函数的使用方法与row_number函数完全相同。
select RANK() OVER(order by [UserId]) as rank,* from [Order] 

--DENSE_RANK
--    dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。
--    dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第四名，
--    dense_rank()是连续排序，有两个第一名时仍然跟着第二名。将上面的Sql语句改由dense_rank函数来实现。
select DENSE_RANK() OVER(order by [UserId]) as den_rank,* from [Order]

--NTILE
--     ntile函数可以对序号进行分组处理，将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，ntile 将返回此行所属的组的编号。
--     这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。
--     也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数，用来指定桶数。下面的SQL语句使用ntile函数对Order表进行了装桶处理：
select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]


------------------------------------ LAG，LEAD，FIRST_VALUE，LAST_VALUE 偏移函数的各种用法 ---------------------------------------------------------

--1.LAG:上一个 第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）
 select LAG([TotalPrice],2,0) over ( order by [SubTime] ),*   from [Order]  order by [SubTime] 

--2.LEAD:下一个 第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）
 select LEAD([TotalPrice],1) over (partition by [UserId] order by [SubTime] ),*   from [Order]   order by [SubTime] 

--3.FIRST_VALUE:第一行
 select FIRST_VALUE([TotalPrice]) over ( order by [SubTime] ),*   from [Order]   order by [TotalPrice] 

--4.LAST_VALUE:最后一行  如果需要全表找最后一个需要添加上:RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 select LAST_VALUE([TotalPrice]) over ( order by [SubTime] RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),*   from [Order]   order by [TotalPrice] 


 ------------------------------------ 聚合开窗函数 的各种用法 ---------------------------------------------------------

--sum([TotalPrice]) 最终结果
select sum([TotalPrice]) over (partition by [UserId]  ),*   from [Order]   
--sum([TotalPrice]) 按照[SubTime] 累加
select sum([TotalPrice]) over (partition by [UserId] order by  [SubTime] ),*   from [Order]   


 ------------------------------------ 分组集 grouping sets，cube 的各种用法 ---------------------------------------------------------

 --使用分组集，没有参与的列null，但是可能本身就是null。使用函数grouping()，结果返回1，0
select [UserId],count(1)   from [Order] group by [UserId]
union all
select [TotalPrice],count(1)   from [Order] group by [TotalPrice]

--grouping sets 等同于上一个效果
select [UserId],[TotalPrice],count(1)   from [Order] group by grouping sets ([TotalPrice],[UserId])

--cube 会把所有可能都组合一遍
select [UserId],[TotalPrice],count(1)   from [Order] group by cube ([TotalPrice],[UserId])


 ------------------------------------ 插入数据 的各种用法 ---------------------------------------------------------
--1.insert values
INSERT [dbo].[Order] ([UserId], [TotalPrice], [SubTime]) 
VALUES ( 1, 100, CAST(0x0000A419011D32AF AS DateTime)) 
,( 1, 100, CAST(0x0000A419011D32AF AS DateTime))

--2.insert select
INSERT [dbo].[Order] 
select [UserId], [TotalPrice], [SubTime] from [dbo].[Order]

--3.insert exec

--4.select into
select [UserId], [TotalPrice], [SubTime] into [dbo].[OrderTemp] from [dbo].[Order]
--5.bulk insert


--当设置了自增列，想自己设置id
--1.set identity_insert xxxx on  set identity_insert xxxx off



 ------------------------------------ delete，truncate，drop 的各种用法 ---------------------------------------------------------

-- 删除数据
--1.delete 能删除表中或基于真实表创建的视图中的数据
--2.truncate 只能删除表中的数据，无法应用在视图上
--3.drop  语句用来删除一个或多个表操作，当然也可以删除临时表。



 ------------------------------------ merge 的各种用法 ---------------------------------------------------------



 ------------------------------------ 事务 的各种用法 ---------------------------------------------------------

 begin tran
 begin try

    select 1

 end try
 begin catch
    if @@trancount>0
        rollback tran
 end catch
 if @@trancount>0
    commit tran

 ------------------------------------  if...else 的各种用法 ---------------------------------------------------------

 if YEAR(GETDATE())='2022'
    print '2021'
 else
    print  '0000'


 ------------------------------------  while 的各种用法 ---------------------------------------------------------

DECLARE @count INT;
SET @count = 1;
    
WHILE @count<= 5
BEGIN
   select 1
   SET @count = @count + 1;
END;

select [sɪˈlekt]           查询
from [frəm]              表名
where [wer]             条件
exists [ɪɡˈzɪsts]          包含
between [bɪˈtwiːn]   范围
like [laɪk]                 模糊查询
reverse [rɪˈvɜːrs]      字符串反转
top [tɑːp]                前多少条
OFFSET FETCH [ˈɔːfset] [fetʃ]     分页
row_number [roʊ] [ˈnʌmbər] 生成序号
over [ˈoʊvər]          配合开窗函数
partition [pɑːrˈtɪʃn]  能够在保留全部数据的基础上,只对其中某些字段做分组排序
case [keɪs] 
when [wen]
then [ðen]
else [els] 
end [end]                 sql判断用法
coalesce [ˌkoʊəˈles]  将空值替换成其他值
choose [tʃuːz] 按照第一个参数取后面的值
COLLATE [kəˈleɪt] 修改排序规则
concat         拼接字符串
substring
left [left]
right [raɪt]              截取字符串
len [lɛn]                 字符数
datalength [leŋθ]    字节数
charindex [tʃɑːr][ˈɪndeks] 函数返回字符或者字符串在另一个字符串中的起始位置
patindex [pæt] 类似于like的模式。
replace [rɪˈpleɪs] 代替
replicate [ˈreplɪkeɪt] 复制自己
stuff [stʌf] 拼接字符串
upper [ˈʌpər] 转大写
lower [ˈloʊər , ˈlaʊər] 转小写
trim [trɪm]    去除空格需要加l或者f
format [ˈfɔːrmæt] 自定义格式字符串输入，按照标准对字符串进行转换
language [ˈlæŋɡwɪdʒ] 修改语言，时间显示不同
convert [kənˈvɜːrt , ˈkɑːnvɜːrt] 
parse [pɑːrs]
T[əˈplaɪ]RY [traɪ] 提供try_case，convert，parse
switchoffset 返回一个datetimeoffset值，它是从存储的时区偏移到指定的新时区偏移的改变。
dateadd 在指定时间上面添加
DATEDIFF  判断时间相差多少
datepart [pɑːrt]
DATEfromparts 
DATEtimefromparts 快速拼接一个时间
eomonth 快速获取时间的月度
schema_name [ˈskiːmə] 架构名
object_id  表名
columns [ˈkɑləmz] 行信息
OUTER [ˈaʊtər] 右表没有返回null
CROSS [krɔːs] 返回交集
apply [əˈplaɪ] 
PIVOT [ˈpɪvət] 行转列
unpivot 列转行
union [ˈjuːniən] 联合
intersect [ˌɪntərˈsekt] 返回查询结果中相同的部分既他们的交集
except [ɪkˈsept] 只返回第一个里面出现，第二个里面没有出现的数据
RANK [ræŋk] 排序，相同的序号一样
DENSE_RANK [dens] 序号有规律，rank没有规律
NTILE [taɪl] 
LAG [læɡ]  上某个
LEAD [liːd]  下某个
FIRST_VALUE [fɜːrst] 第一个
LAST_VALUE [læst]  最后一个
merge [mɜːrdʒ] 提供insert，update，delete
identity_insert [aɪˈdentəti] [ɪnˈsɜːt ] 自增列
merge [mɜːrdʒ]