Sqlserver的基础

数据的生命周期

OLTP(连接事务处理)-->ETL(将数据移动到仓库)-->DW(数据仓库)-->OLAP(联机分析处理)-->DM(数据检索，对数据模型进行设计和优化)

sqlserver安装时默认的数据库

1. master：保存实例范围的元数据／服务器配置／实例中的数据库的信。
2. resource：保存系统的所有对象
3. model：新数据的模板
4. tempdb：保存临时数据的地方
5. msdb：服务保存的数据,如作业计划之类的 master数据库的屋里属性 主数据库（.mdf）,日志（.ldf）。

集合运算

1. 并集[union]：两集合的总和，默认union distinct两集合去除了重复的数据。可u用union all 去带重复的数据。
2. 交集[intersect]：取两集合的相同的数据，剔除了重复的数据。不可与all合用。
3. 差集[except]：A except B = A-B 。从A集合中剔除有B集合的数据。
4. 差集的优先级大于并集和交集，交集和并集优先级相同。

判断数据库是否存在

if db_id('数据库名称') is null
create database name( 
)

数据库的完整性（约束）

1. 主键约束 : constraint pk_name primary key (主键)
2. 外键约束 : constraint fk_name foreign key(外键) references 外表名称(外表主键)
3. 唯一约束 : constraint unq_name unique(字段)
4. 检查约束 : constraint chk_name check(xxx>0表达式)
5. 默认约束 : constraint dft_name default(默认值) from 字段

添加约束
--主键
alter table abo.tableName Add constraint pk_name primary key
--外键
alter table orders add constraint FK_Name foreign key (customerId) references customer(customerId)  
--外键联级动作
alter table orders   add constraint FK_Orders_CustomerId        --添加约束 名称
    foreign key (customerId)    references customer(customerId)    --外键约束，外键列名，被引用列名
    on update     no action    --默认　　修改时不级联更新子表
    on delete     cascade      --删除时级联删除依赖行  
--唯一约束
alter table Account add constraint AK_AccountName unique (Account_Name) 
--check约束
alter table Account  add constraint CN_AccountAge check  (Account_Age > 18);  
--默认约束
alter table person add constraint CN_DefaultName default '无名氏' for person_name
--删除约束
alter table AdItem drop constraint AdOrder_AdItem_FK1

--规则添加约束
--添加规则
CREATE RULE Age18Rule AS @Age > 18;
go;
--激活规则，指定表列
EXEC sp_bindrule 'Age18Rule','person.person_age';
--接触规则
EXEC sp_unbindrule 'person.person_age';
--删除规则
go;
DROP RULE Age18Rule;
--默认值创建
--创建默认值，绑定如规则绑定一致
CREATE DEFAULT AgeDefault AS 0;
DROP DEFAULT AgeDefault
--禁用约束
--生成启用or禁用指定表外键约束的sql
select 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='pk' and a.parent_obj = b.id and b.name='classify'; 
select 'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name='表名'; 
--查询约束信息
EXEC sys.sp_helpconstraint @objname = N'Users'

--注：当表中的数据有不符合新添加的约束，约束条件无法添加。

查询表字段信息

SELECT 
表名       = d.name, --case when a.colorder=1 then d.name else '' end,
表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号   = a.colorder,
字段名     = a.name,
标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型       = b.name,
占用字节数 = a.length,
长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空     = case when a.isnullable=1 then '√'else '' end,
默认值     = isnull(e.text,''),
默认值名称 = (select name from sys.default_constraints where parent_object_id=object_id(d.name) and parent_column_id = a.colorder),
字段说明   = isnull(g.[value],''),
'public '+
(
case  
when CHARINDEX('Enum',a.name) >0 then a.name
when b.name= 'int' then b.name 
when b.name = 'bit' then 'bool'
when b.name = 'money' then 'double'
when b.name = 'decimal' then b.name
when b.name = 'datetime' then 'DateTime'
when b.name = 'date' then 'DateTime'
when b.name = 'uniqueidentifier' then 'Guid'
else 'string' end
) + ' '+ 
(
case
when a.isnullable=1 and b.name <> 'nvarchar' then '?' 
else '' end
) +
a.name + ' {get;set;}' as '属性'
FROM  syscolumns a
left join systypes b on   a.xusertype = b.xusertype
inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties  g on  a.id=G.major_id and a.colid=g.minor_id  
left join sys.extended_properties f on  d.id=f.major_id and f.minor_id=0where 
d.name='Users'    
--如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
order by a.id,a.colorder;

系统数据表

-- 移动每个文件
alter database database_name modify file (name=logical_name,filename='new_path');
--查询数据信息
select * from sys.databases;
--查询数据物理文件
select * from sys.master_files;
--查询在数据库中创建的每个对象（例如约束、默认值、日志、规则以及存储过程）
--F 外键; V 视图; P 存储过程; C check约束; D 默认约束; U 表; S 系统表; PK 主键
select * from sysobjects a 
where a.xtype = 'PK';
--外键约束表
select * from sys.foreign_keys;

sql的执行顺序

from
where
group by
having
select
over
distinct
top
order by

over的使用

select sum(1) over(),
sum(1) over(partition by name ,idcard) from carddetail;
over的四种用法
row_number() 此用于递增数据
rank() 有并列的情况下，之后递增的数据根据并列+1
dense_rank() 在并列情况下，之后递增的数据根据总数据递增，会看出有跳数
ntile(10) 将数据分组，可这设置多少条数据为一组

运算符

between xx and xxx
like ‘[abc]_%’
in or not in 在查询的字段中值很少有重复的尽量少用in关键字，使用exists
case 字段 when 值或表达式 then 赋值 else 值 end
null unknown，当不同类型比较或运算无法计算时得出的结果为unknown，逻辑运算为false。

排序规则

select * from sys.fn_helpcollations();--Arabic_CI_AS
ci 不区分大小写
字典排序法
set concat_null_yields_null off; --不支持null字符拼接

递归查询

--向下递归查询
with temp (FolderName,id,ParentID)
as(
select a.FolderName,id,ParentID from NC_FolderInFo a where ParentID=105
union all
select m.FolderName,m.id,m.ParentID from NC_FolderInFo  as m
inner join temp as child  on m.ParentID = child.ID
)
select count(1) from temp t
inner join NC_FileInfo f on (t.id = f.FolderID_FK)
where IsEnable is null;

获取分组的前几位

--使用ROW_NUMBER()或rant()[如同分数同名次] 函数
select t.Subject,t.Amount,t.Name,t.Age ,t.*
from (
select 
ROW_NUMBER() over(partition by i.Subject order by i.Amount desc) inum,
u.Name,u.Age,i.Subject,i.Amount 
from Income i
join [User] u on i.UserId = u.ID
) t 
where t.inum<3
order by t.Subject, t.Amount desc;

--条件排除
select distinct t1.Subject,t1.Amount,u1.Name,u1.Age
from Income as t1
join [User] u1 on t1.UserId = u1.ID
where t1.Id in
(
select top 2 t2.Id  from Income as t2
join [User] u2 on t2.UserId = u2.ID
where t1.Subject=t2.Subject
    order by t2.Amount desc
)
order by Subject, Amount desc;

-- 此方法如有重复的值不适合取前几位，可用于去比较值
select a.*
from Income a where (select count(1) from Income where Subject=a.Subject and Amount>a.Amount)<=1

函数

--字符串
substring(string,start,length);--下标从1开始
left(string,n);
right(string,n);
len(string);--计算字符的长度（个数）
datalength(string);按照ascii计算，unicode一个字符算两个长度
charindex(substring,string[,start_pos]) --查找字符在某字符串中在哪个位置
patindex('%[0-9]%','xxx'),--查找字符第一次出现的位置
replace(string,substring,substring),替换字符
replicate(string,n)--将字符重复几次
stuff('xyz',2,1,'abc')--xabcz
--upper和lower 大小姐转换
rtrim、ltrim
--日期
datetime [19530101-99991231]
smalldatetime [19000101-20790606]
--year,quarter,month,dayofyear,day,week,hour,second,minute
dateadd(part,n,val)
datediff(part,val,val2)--两日期相差
datepart(part,val)--指定日期的数值 datename(part,val)
year、month、day
isdate(val)--是否是日期格式 返回1为日期格式
--其他函数
cast('' AS datatime)
convert(类型,'',101);

链接查询

1. cross join --n*m
2. inner join -- A并B
3. left join -- A(包括重复的)
4. left outer join -- 等价left join
5. full join -- 全表链接

表表达式

-- with关键字
with ets as(
select id,parentid,name
from tableName
where id = 1
union all
select ets as t
join on tableName as n on t.parentid = t.id
)
select * from ets;
-- 创建视图
if object_id('','V')
drop view viewName
go
create view viewName 
as
select * from aa;
go;
select * from viewName;
-- 视图参数选项
alter view viewName with encryption
as
select * from tableName
go
schemabinding --此关键字定义在视图中，视图中引用的表不能随意修改
--查询视图存储过程等定义的文本
select object_definition(object_id('sp_addarticle'));
exec sp_helptext 'sp_addarticle';
在视图中sql尾部添加with check option，通过视图添加或修改的数据只能满足查询的条件内容
-- 创建函数
go
create function funcName(@cid as int)
returns table
as 
return
select * from blogs
where id = @cid
go

集合运算

• 并集[union]：两集合的总和，如默认union distinct两集合去除了重复的数据。可u用union all 带重复的数据。
• 交集[intersect]：取两集合的相同的数据，剔除了重复的数据。不可与all合用，在内部构建已经不使用，如使用它所查询的是A重复x乘以B重复的y。建议应row_number()
• 差集[except]：A except B = A-B 。从A集合中剔除有B集合的数据。
• 差集的优先级大于并集和交集，交集和并集优先级相同,except all。

透视及分组集

select  empid, A, B, C, D
from(
  select empid ,custid,qty from ordrs
) as E
[un]pivot(sum(qty) for cusid in(A,B,C,D)) as P;
ag:
select  userid, 语文, 数学
from(
  select userid ,Subject,Amount from Income
) as E
pivot(sum(Amount) for Subject in(语文, 数学)) as P;

分组集

group by：结合合计，根据一列或多列进行分组。
--类似几个分组 union all 在一起
select * from [order]
group by 
grouping sets
(
(empid,custid),
(empid),
(custid)
)
grouping：指示是否聚合group by列表中的指定列表表达式。如grouping返回1则聚合；返回0则不聚合。
rollup/cube：生成简单的group by集合行及小计行或超聚行，还生成一个总计行。rollup选取的列从左到右排序进行聚行，cube则从右到左。grouping_id 获取当列的汇总的列数
select empid,custid,sum(qty) as sumqty from seven_sale_orders
group by 
grouping sets
(zz
(empid ,custid),(empid)--根据此进行分组计算汇总
)

插入数据

insert into tablename(col1, col2....)
values(col1val, col2val....)
,(col1val, col2val....)--支持2005以上sqlserver
;
--将其他查询的结果插入到另一个表中，
insert into tablename(col1, col2....)
select col1,col2.....
from oldtablename 
where.....;

insert into tablename(col1, col2....)
select col1val, col2val.... union all
select col1val, col2val.... union all
select col1val, col2val.... ;

--利用存储过程进行插入数据
insert into tablename(col1, col2....)
exec procname @var ='' --执行存储过程 传递参数，返回的结果为集合

--此语法从其他表中的数据插入数据时创建新表
select col1, col2......
into talename
from oldtablename

--文件形式插入
bulk insert tablename from ‘xxxxsql插入的文件.txt’
with(
datafileType = 'char', --文件选取的类型
fieldterminator = ',', --分割符
rowterminator = '\n' --分界
);

--显示每个插入的记录
insert into Income 
output inserted.Id,inserted.Amount
values(2,10,2014,6,'C'),
(2,20,2014,6,'B');

declare @newRows table(id int,Amount numeric(18, 3))
insert into Income 
output inserted.Id,inserted.Amount
into @newRows
values(2,10,2014,6,'C'),
(2,20,2014,6,'B');
select * from @newRows;

--带参数赋值
declare @row tables(xxx int,xxx nvarchar(40))
insert into tabname
output instered.xx,instered.xxx
select xxx from tab2
where 1=1

delete from orders
ouput deleted.xxxx
where 1=1

自增列

--自增列语法
--获取当前作用域插入语句的自增列数值,没有插入记录为null
declare @new_key as int
--插入语句
set @new_key = scope_identity();
select @new_key as new_key

--获取表的自增列值
select IDENT_CURRENT('Income') as [ident_current],--当前表中的最新自增id
@@identity ,--获取最后一次会话的自增id
scope_identity() as id;

--更改自增列值
dbcc checkident('dbo.Income',RESEED ,26)

--更改自增列值 前提更改的值需比目前自增值小
set identity_insert seven_sys_loginuser on;
insert into seven_sys_loginuser(Id) values(5);
set identity_insert seven_sys_loginuser off;

删除数据

/*
delete truncate 区别
1、delete后面可以写条件删除数据，truncate则是将表中的数据全部删除
2、delete删除每行的数据都会记录在数据日志中，而truncate的删除则只记录每页的数据,它不能激活触发器。
3、delete删除全部的数据会将之前占用的页数保留，再去添加是累计添加，删除的数据临时存在于电脑的回车站中可恢复[重启电脑不可恢复]，可根据自增列查看再添加数据自增列以之前的自增列值累加。
   truncate则是将表中页全部删除，再增加数据时重新计算。
*/
delete from seven_sys_loginuser where PersonId=0;
truncate table textPact;

--联表删除
delete from A
[output deleted.xx]
from tableA as A
join tableB as B on A.id = b.ID
where b.name='';
# 联接更新
update od set  xxx=xxx
[output inserted.xx,deleted.xx]
from name2 as od
join order as o on()
where 1=1
# 查询数据
--多字段指定值显示
select * from (
values
(1003,'20150502',4,'B'),
(1003,'20150502',4,'B'),
(1003,'20150502',4,'B')
)
as o(orderid,orderdate,empid,custid)
# merge关键字 合并数据
merge /* top(2) */into TargetTable As T  --需要更改的数据对象
using SourceTable as S --以某数据对象为目标进行查看
on T.id = S.id --以什么字段进行比较
when Matched and(
t.xxx <> s.xxx
or t.xxx <> s.xxx
) then --字段比较相等
update set T.[desc] = S.[desc]  --更新
when not matched then--字段比较不相等
insert values(s.id,s.[desc]) --插入
when not matched by source then delete --目标表存在，原表不存在，则删除目标表不同的数据
output $Action As [action],inserted.id as 插入id, --显示操作的视图，$action 显示操作动作
inserted.[desc] as 插入的desc,
deleted.id as 删除的id,
deleted.[desc] as 删除的desc;

事务

/*
原子性：对整个事务是一个整体，要么全部执行要不全部不执行。
一致性：事务在对数据修改时不会发生冲突，数据要保持一致性。
隔离性: 各事务的修改和查下隔离。
持久性：事务执行后数据是持久进行保持。

锁：
》按锁的级别分为：共享锁和排他锁、意向排他锁和意向共享锁。还有更新锁、意向锁、架构锁。
》mssql系统默认每条增删改是一个事务的处理【除自定意外】，在事务中只有当事务执行请求资源时，其他资源请求无法请求，只有结束事务才可请求。
》当单个语句获得至少5000个锁时，就会触发锁的升级。
*/

--设置锁超时时间 -1 无限等待
set lock_timeout 5000;
--杀死线程,可利用sys.dm_exec_requests，sys.dm_exec_requests，sys.dm_tran_locks
kill 52;
隔离级别：
Read Uncommitted --未提交读
Read Committed --已提交读（默认）
Repeatable Read --可重复读
Serializable --可序列化
Snapshot --快照
Read Committed Snapshot --已提交读隔离
set transaction isolation level <name>

如对数据库进行备份：
if day(current_timestamp)=1
begin --全部复制
print '';
backup database xxx
to disk = '地址' with init
print '';
end
else
begin--差异备份
backup database xxx
to disk = '地址' with differential
end

游标

declare c cursor fast_forward/* read only forward noly*/ for --申明路由
select  xx,xxx,xx from tabname;
open c --打开路由
fetch next [ [Next|prior|Frist|Last|Absoute n|Relative n ] from c into @xxxx,@xxxx; --跳到下一个指针并且复制到变量中
while @@fetch_status = 0
begin
xxxxx
 Update bigorder Set UserId='123' Where Current of  orderNum_03_cursor--修改当前
fetch next from c into xxxxxxxx;
end
close c; --关闭游标
deallocate c; --释放游标

数据库内部表、视图、函数、存储过程

select * from sys.tables;
select * from sys.columns;
select * from INFORMATION_SCHEMA.TABLES;--视图
select * from INFORMATION_SCHEMA.COLUMNS;
exec sys.sp_tables;
exec sys.sp_help;
exec sys.sp_columns @table_name='';
exec sys.sp_helpconstraint @objname='';--可查询是否有约束
select SERVERPROPERTY('属性名称');--
select DATABASEPROPERTYEX('数据库名称','');
select OBJECTPROPERTY(object_id('表名'),'');
select COLUMNPROPERTY(object_id('表名'),'字段名','');
--查看视图事务等创建文本
select object_definition(object_id('sp_addarticle'));
exec sp_helptext 'sp_addarticle';
--事物锁
select DB_NAME(resource_database_id),l.*
from sys.dm_tran_locks l

--查询最近链接数据信息
select *
from sys.dm_exec_connections c
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) as ST;
--执行sql请求信息
select * from sys.dm_exec_request

递归拼接字符

Stuff用法： Stuff (@expression,@startIndex,@length,@replacrExpression)

注:

• @expression 表示 要处理的字符串
• @startIndex 表示 开始删除字符串的位置（sql server索引从1而不是0开始）
• @lenght 表示 删除字符串的长度
• @replacrExpression 表示 删除后该位置 要替换的字符串
• SELECT STUFF((SELECT ',' + CAST(bs_id AS VARCHAR(50)) FROM [dbo].BuildSite FOR XML PATH('')),1,1,'') AS BsStr

进程冲突了，如何关闭进程

use master  --选择数据库
go
select spid from master.sys.sysprocesses where dbid = db_id('model');

use master  --选择数据库
go
declare @sql varchar(100) 
while 1=1 
begin 
  select top 1 @sql = 'kill '+cast(spid as varchar(3)) 
  from master..sysprocesses where spid > 50 and spid <> @@spid and  dbid = db_id('model')
  if @@rowcount = 0 
    break ;
  print(@sql)   --打印杀掉进程语句
  exec(@sql)    --执行杀掉进程语句
end
go
-- 查看进程信息
SELECT
SPID = er.session_id 
,Status = ses.status 
,[Login] = ses.login_name 
,Host = ses.host_name 
,BlkBy = er.blocking_session_id 
,DBName = DB_Name(er.database_id) 
,CommandType = er.command 
,SQLStatement = st.text 
,ObjectName = OBJECT_NAME(st.objectid) 
,ElapsedMS = er.total_elapsed_time 
,CPUTime = er.cpu_time 
,IOReads = er.logical_reads + er.reads 
,IOWrites = er.writes 
,LastWaitType = er.last_wait_type 
,StartTime = er.start_time 
,Protocol = con.net_transport 
,ConnectionWrites = con.num_writes 
,ConnectionReads = con.num_reads 
,ClientAddress = con.client_net_address 
,Authentication = con.auth_scheme 
FROM sys.dm_exec_requests er 
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st 
LEFT JOIN sys.dm_exec_sessions ses 
ON ses.session_id = er.session_id 
LEFT JOIN sys.dm_exec_connections con 
ON con.session_id = ses.session_id 
WHERE er.session_id > 50 
ORDER BY
er.blocking_session_id DESC
,er.session_id 

执行较大文件时，用命令执行文件

sqlcmd -S [IP地址] -U [用户名] -P [密码] -d [文件绝对地址]

其他语法

Select id,
        Case ntile(col) over(order by val)
        When 1 then '1'
        When 2 then '2'
        Else ''
        End as col1
From tableName
Select id,
        Case 
        When col>100 then '1'
        When col>200 then '2'
        Else ''
        End as col1
From tableName