数据库——sql-server

T-SQL语句的分类

1,数据操作语句（DML）：主要包括对象数据库数据的查询，插入，删除，修改（select,insert,update,delete）
2，数据定义语句（DDL）：对数据库以及数据库中的各种对象进行创建，删除，修改（create,alter,drop）
3,数据控制语句(DCL):数据控制语句用于安全管理，确定哪些用户能查看或修改数据库中的数据（grant，revoke，deny）

数据库文件和文件组

SQL Server 将数据库文件分为三类：主数据文件，辅数据文件，日志文件。
1. 主数据文件（主文件）
   是数据库的关键文件，是所有数据文件的起点，包含指向其他数据库文件的指针，每个数据库都必须有且仅有一个主文件，默认扩展名为.mdf。
2. 辅数据文件（辅文件）
   辅助主文件存储数据，包含不在主文件内的其他数据，一个数据库可以有0~n个辅文件，扩展名为.ndf。
3. 日志文件
   记录存放恢复数据库时所需要的所有日志信息，一个数据库可有1~n个日志文件，扩展名为.ldf。
总结：一个数据库必须至少包含一个主数据文件和一个日志文件。

SQL Server 为了更好的管理数据文件，提供了文件组概念，分为两种：主文件组（PRIMARY）和用户定义文件组。
1. 主文件组（PRIMARY）
   存放主数据文件和任何没有明确指定文件组的其他文件。
2. 用户定义文件组
   是在创建或修改数据库时用FILEGROUP关键字定义的文件组，存放辅数据文件。
文件组特性：
（1）一个文件只能属于一个文件组。
（2）只有数据文件才能归属于某个文件组，日志文件不属于任何文件组。
（3）每个数据库中都有一个默认的文件组在运行，可以指定默认文件组，没有指定的话，则默认为主文件组。
（4）若没有用户定义文件组，则所有的数据文件都存放在主文件组中。

删除和创建数据库

use master
go
create database testNewbase--创建数据库在
on primary
(
  name='testNewbase',
  filename='D:\SQLsever\testNewbase.mdf',
  size=5MB,
  filegrowth=1MB
)
log on
(
  name='testNewbase_log',
  filename='D:\SQLsever\testNewbase.ldf',
  size=1MB,
  filegrowth=10%
)
go

drop database testNewbase--删除数据库
go

删除和创建表

--创建产品表
create table productInfos
(
  Id int identity(1001,1) primary key not null,  --标识种子（起始位置）,增量
  proNo varchar(50) not null,
  proName nvarchar(50) not null,
  TypeId int not null,
  price decimal(18,2) default 0.00 null,
  proCount int default 0 null --加个默认值0
)
go

drop table productInfos
go

--创建产品类型表
create table ProductType
(
  TypeId int identity(1,1) primary key not null,
  TypeName nvarchar(20) not null
)
go

修改表

--添加一列
alter table ProductInfos add ProRemark nvarchar(max) null
--删除一列
alter table ProductInfos drop column ProRemark 
--修改一列
alter table ProductInfos alter column Prono nvarchar(50) null--将数据类型和是否为空都改变了
--修改列名
exec sp_rename'ProductInfos.procount','count','column'--谨用

创建约束

--五种约束：主键约束，外键约束，unique约束，check约束，default约束

在建表的过程中创建约束：
--主键约束primary key
--外键约束foreign key references 另一个表（列名）
create table productInfos
(
  Id int identity(1001,1) primary key not null,  
  proNo varchar(50) not null,
  proName nvarchar(50) not null,
  TypeId int not null foreign key references ProductType(typeid),--在这里进行外键约束，与另一个表进行关联
  price decimal(18,2) default 0.00 null,
  proCount int default 0 null --加个默认值0
)
go
--唯一约束unique
--default约束default（）有必要的话加单引号
--check约束check（Price<1000）

在建表后创建约束(别忘了需要选则列来进行创建约束)
主键约束id：alter table productInfos add constraint PK_ProductInfos primary key(Id)
外键约束typeid：alter table productinfos add constraint fk_productinfos foreign key(typeid) references producttype(typeid)
unique约束：alter table productinfos add constraint uk_productinfos unique(prono)
default约束：alter table productinfos add constraint dk_productinfos_procount default(0) for procount
check约束：alter table productinfos add constraint ck_productinfos check(price<10000)

插入数据

这些插入的前提都是有标识列，所以可以直接加。如果有标识列，那么就可以忽略标识列，直接写插入的其他列的内容，如果没有标识列，那么就需要全写。
1,单数据插入
insert into producttype (typename) values('工具类')
insert  producttype (typename) values('鞋子类')
insert producttype (typename) select('袜子类')
2，多数据插入
insert producttype(typename) values('工具类1'),('工具类2'),('工具类3')
这个前提是没有标识列，而是三列（id，mname，age）
insert test1(id,mname,age) 
select 1,'admin1',15 union
select 2,'admin2',16 union
select 3,'admin3',17 union
select 4,'admin4',18 
--ps：union和union all相比是有去重的功能的（有重复的就自动删除了）， 而union all允许插入相同的数据，所以效率较高
3，克隆表数据(前提，test1表中有name列，producttype表中有typename列)
第一种（目标表和源表都存在都存在数据库中）：insert into test1(mname) select typename from producttype
第二种（目标表不存在的情况下）：select typename into test2 from producttype

修改和删除数据

数据修改：
update test1 set mname = 'sss',age = 20 where id = 5--如果不加where那么所有的列都会进行改变，注意主键是不能修改的，where后面的条件可以使用and或者or进行连接。

数据删除：
（1）只是删除数据，表还在
 delete from table 如果不加条件where，那么就会删除整张表中所有数据。如果有标识列，即使所有数据删除，再插入时标识列的值保持之前的值并增加。标识列的缺点
 delete from test1 where id = 10
 如果想删除数据的同时使标识列回复初始值
 truncate table test1
 
（2）数据和表一起删除

表查询

查询一个表中的所有数据：select * from table
查询一个表中的部分列的数据：select id,age from table

分组查询

select 客户号 ,count(1) 数量 from 订单表 --每个客户号对应的数量
group by 客户号

比较完整的结构：
select 部门编号,count(1) 用户数 from UserInfos
where Age>26--分组前的筛选
group by 部门编号
having 部门编号>1 --分组后的筛选
order by 部门编号 desc

连接查询

定义：根据两个或多个表之间的关系，从这些表中查询数据
分类：内连接，外连接，全连接，交叉连接
内连接：（显示）
select m.订单号,单价,订购日期 from 订单明细表 m
inner join 订单表 n on m.订单号=n.订单号 --通过订单号将两个表进行关联（两个表中的相同部分）
where 单价>500
内连接：（隐式）
select m.订单号,单价,订购日期 from 订单明细表 m ,订单表 n
where m.订单号=n.订单号 and 单价>500

外连接：
左外连接：左表显示所有行，右表的行数与坐标保持一致，否则补null
select * from 订单表 m --这个是左表
left join 订单明细表 n --这个是右表
on m.订单号 = n.订单号

右外连接：
select * from 订单表 m --这个是左表
r join 订单明细表 n --这个是右表
on m.订单号 = n.订单号

全连接：显示两个表的所有行，匹配不上的是null。
select * from 订单表 n
full join 订单明细表 m
on n.订单号 = m.订单号

交叉连接：如果不带where子句，返回的行数是两个表行数的乘积。如果带where子句，等价与inner join返回的数据。
select * from 订单表 n
cross join 订单明细表 m
where n.订单号 = m.订单号

函数

avg()：select avg(sal) 平均工资，avg(comm) 平均奖金 from emp;--生成两列：平均工资和平均奖金
sun():select sum(sal) 总工资,sum(comm) 总奖金 from emp; --生成两列：总工资和总奖金
max():select max(sal) 最高工资,max(comm) 最高奖金 from emp;--生成两列：最高工资和最高奖金
min():select min(sal) 最低工资,min(comm) 最低奖金 from emp;--生成两列：最低工资和最低奖金
count(): select count(distinct deptno) 部门总数,count(deptno) "部门总数？",count(empno) 员工总数 from emp;--distinct去重
 
数据类型的转换：
select 'ab'+convert(varchar,2)--将2转化成字符串进行拼接 
select cast(2 as varchar)

字符串操作函数：
返回字符串中指定的字串开始的位置：select charindex('bc','abcde')
                            select patindex('%bc%','abcde')
将字符串变大写：select upper('abE')
将字符串变小写：select lower('abE')
取长度：select len('ABC')
去掉左边的空格:select ltrim('  adb ')
去掉右边的空格:select rtrim('  adb ')
取子串：从左边开始取select left('abcde',3)，结果是abc。从右边开始取select right('abcde',3)，结果是cde。从规定位置开始取select substring('abcde',2,3)从第二个开始，取三个
重复：select replicate('abc',4),将规定字符串重复4次
颠倒：select reverse('abc')
替换指定的字符串：select replace('abcde','cd','aa')将cd子字符串替换成aa
替换指定位置，指定长度的字符串：select stuff('abcde',2,3,'tt'),结果是atte


自定义函数：
分类：标量函数，内嵌表值函数，多声明表值函数
标量函数：对单一值的操作，返回的是单一值。
内嵌表值函数：参数化的视图，返回的是一个表。没有begin end包裹。	
多声明表值函数：返回的也是一个表，有begin end包裹的函数体。返回的表数据是由函数体的语句插入的，多次查询，甚至可以多次筛选与合并，弥补了内嵌表值函数的不足。

构造标量函数：
create function [dbo].getdingdanhao(@客户号2  char(6))--这里可以直接为@客户号2指定默认值：(@客户号2 char(6) =  'A00112')
returns char(6)
as
begin
     declare @订单号2 char(6)
     select @订单号2 = 订单号 from 订单表 where 客户号 = @客户号2
     return @订单号2
end
go
--调用函数
select dbo.getdingdanhao('A00112') as 新的列名--可以为结果取个新的列名
如果已经指定了默认值，那么就可以直接调用：
select dbo.getdingdanhao(default) as 新的列名


构造内嵌表值函数：
create function [dbo].getinfo(@订单号2 char(6))
returns table
as
return(
  select 订单号,客户号,订购日期 from 订单表
  where 订单号 = @订单号2
)
go
--调用：
select	* from dbo.getinfo('OR-22A')



构造多声明表值函数（多语句表值函数）
create function [dbo].search(@订单号2 char(6))
returns @要求订单 table(
  客户号 char(6) not null primary key,
  订单号 char(6) not null ,
  订购日期 date null
)
as
begin
  insert into @要求订单(客户号,订单号,订购日期)
  select 客户号,订单号,订购日期 from 订单表
  where 订单号 like '%'+@订单号2+'%'
  return 
end
go
--调用函数：
select * from dbo.search('OR-22A')

索引

作用：数据的查询，处理速度，可以快速的查找到数据，不必要扫描整个表，减少系统的响应时间
缺点：并不是越多越好，占用存储空间。
聚集索引：Clustered 逻辑顺序和物理顺序是一致的（效率较高），一个表中只有一个或一个也没有，例如：主键
非聚集索引：NonClustered 逻辑顺序和物理顺序不是一致的，一个表中可以右多个，例如：唯一索引
如果需要多个列上建立组合索引，这些列建立组合索引。

使用脚本创建索引
创建聚集索引
create clustered index pk_infos --如果不指定，默认是非聚集索引
on 订单表(客户号)
with
(
  drop_existing=on--on表示如果存在的话，就删除重新创造，off表示不管存不存在，直接重新创造
)
创建唯一非聚集索引 
create unique nonclustered index uq_infos
on 订单表(订单号) 
创建复合索引
create nonclustered index index_infos --如果不指定，默认是非聚集索引
on 订单表(客户号,订单号)
with
(
  drop_existing=off--on表示如果存在的话，就删除重新创造，off表示不管存不存在，直接重新创造
)

视图

视图：是虚拟表，由一个表或多个表通过查询而定义的，将查询定义保存起来，实际不包含数据。
与表的区别：表存储数据的地方，视图存储的是查询语句。
作用：简化查询，增加了数据的保密性
缺点：只是简化查询，并不提高查询速度，增加了维护成本。
分类：标准视图：存储查询定义，没有存储数据
     索引视图：（被具体化了的视图，创建了索引，可以提高查询性能）不适合经常更新基本数据表(数据是实际存在的，删除视图中的数据，基础表中的数据也被删除)
     分区视图：一台或多台服务器水平连接一组成员表的分区数据
创建标准视图：
create view vdingke
as
select k.客户号,订单号
from 客户表 k
inner join 订单表 d
on k.客户号 = d.客户号
go
使用视图：select * from vdingke 后面还可以加where条件和order by 客户号 desc



创建索引视图：
create view vdingke_index with schemabinding--查询订单表中的这两个列的信息
as
select 客户号,订单号 from dbo.订单表--dbo统一的所有者
go
使用视图：select * from vdingke_index
索引视图创建唯一聚集索引：
create unique clustered index uq_vdingke_index
on vdingke_index(订单号) --这里是视图中的列
go


创建分区视图--这个查询合并表数据，必须要保证两个表中的列是相同的。	
create view vtestinfos
as
select * from test1
union all
select * from test2
go
使用：select * from vtestinfos

存储过程

为了完成特定功能的一个或一组sql语句集合，经编译后存储在服务器端的数据库中，可以利用存储过程加速sql语句执行。调用名称，传入参数，执行来完成特定功能。
分类：系统存储过程：master数据库中，在其他数据库中可以直接调用。并且使用时不必加上数据库名，这些系统存储过程已经在新的数据库中自动创建。
     自定义存储过程：用户自己创建，完成特定的功能，可以传入参数，也可以有返回值
优点：提高应用程序的通用性和可移植性。多次调用，不必重新编写，维护人员可以随时更改。更有效的管理数据库的权限。提高执行sql的速度（一次编译，多次执行。）减轻服务器的负担。
缺点：专门维护他，占用数据库空间。
创建存储过程：
无参数的存储过程：
create proc searchuserinfos
as
begin
select * from 订单表/查视图也可以（vdingke_index）
select 客户号 from 订单表
end
如果要修改存储过程，就直接将create改成alter就可以了。
alter proc searchuserinfos
as
begin
select * from vdingke_index
select 客户号 from 订单表
end
执行存储过程：
exec searchuserinfos 
删除存储过程：drop proc searchuserinfos

创建带参数的存储过程--新增一个信息，是订单表中的（客户号，订单号，订购日期）
create proc adddingdan
@客户号 char(6),
@订购日期 date
as
begin
  declare @订单号 char(6)--如果要自己定义订单号变量
  set @订单号 = 'abcdef'
  insert into 订单表(客户号,订单号,订购日期)
  values(@客户号,@订单号,@订购日期)
  select * from 订单表
end
go
执行存储过程：
exec adddingdan 'A11111','2002-8-24'

如果不自定义变量：
alter proc adddingdan
@客户号 char(6),
@订单号 char(6),
@订购日期 date
as
begin
  insert into 订单表(客户号,订单号,订购日期)
  values(@客户号,@订单号,@订购日期)
  select * from 订单表
end
go

exec adddingdan 'A11111','C22222','2002-8-24'



也可以在其中加入别的修改内容，比如删除某些行
alter proc adddingdan
@客户号 char(6),
@订单号 char(6),
@订购日期 date
as
begin
  insert into 订单表(客户号,订单号,订购日期)
  values(@客户号,@订单号,@订购日期)
  delete from 订单表 where 客户号 = 'A11111'
  select * from 订单表
end
go

exec adddingdan 'A11112','C22222','2002-8-24'

触发器

是一种特殊类型的存储过程，不同的是存储过程是通过调用名称执行，而是通过一个事件来触发的。
对表进行操作，插入，更新，删除，自动执行触发器。
分类：DDL触发器（create，alter，drop）
     DML触发器（insert，update，delete）
   其中，DML触发器又分为两种：after触发器，在执行这个操作之后再执行触发器操作。（insert，update，delete）
                          instead of触发器，不执行操作，而执行触发器操作。（insert，update，delete）
                          

这两个临时表不要进行修改

after触发器：对表操作以后触发


after insert触发器：在插入操作以后触发。
create trigger trigger_ke_insert
on 客户表
for insert
as
  declare @客户号2 varchar(20),@客户名2 varchar(20)
  select @客户号2 = 客户号 ,@客户名2 = 客户名 from inserted--存放要插入的数据的表
  print convert(varchar ,@客户号2)+'，'+@客户名2+'添加成功！';
go

--触发：trigger_ke_insert
insert into 客户表(客户号,客户名,地址,电话)
values('bb123','xiaoming','020802','110119')
最后的结果就是bb123 ，xiaoming            添加成功！
修改触发器，用alter


after update触发器：在修改以后触发。如果想要的结果是即打印修改前的，又打印修改后的数据。
create trigger trigger_ke_update
on 客户表
for update
as
  declare @客户号2 varchar(20),@客户名2 varchar(20)
  select @客户号2 = 客户号 ,@客户名2 = 客户名 from deleted--存放更新前的表
  print '修改前：'+convert(varchar ,@客户号2)+'，'+@客户名2;--这里如果数据类型不是varchar而是int再用convert，这里没必要用。
  select @客户号2 = 客户号 ,@客户名2 = 客户名 from inserted--存放更新后的表
  print '修改后：'+convert(varchar ,@客户号2)+'，'+@客户名2;--这里如果数据类型不是varchar而是int再用convert，这里没必要用。
 --select * into dd from deleted--将修改前的数据进行备份到dd表中。
go
--触发：update 客户表 set 客户号='bb123',客户名='xiaogang' where 电话 = '110119'
结果就是：修改前：bb123 ，xiaoming            
        修改后：bb123 ，xiaogang  
注意，如果要打印的是三个数据，但是修改的是两个数据，那一个没有修改的数据也是需要将他用变量命名的（在declare，select，print中，只是触发的时候不用管他。）


after delete触发器：在删除以后的触发器，可以在deleted表中看见删除的数据，可以对其备份，打印等。
create trigger trigger_ke_delete
on 客户表
for delete
as
  declare @客户号2 varchar(20),@客户名2 varchar(20)
  select @客户号2 = 客户号 ,@客户名2 = 客户名 from deleted--存放更新前的表
  print '要删除：'+convert(varchar ,@客户号2)+'，'+@客户名2;--这里如果数据类型不是varchar而是int再用convert，这里没必要用。
  --可以对其进行备份
  --如果目标表不存在
  select * into dd from deleted 
  print '备份成功'
go

--触发：delete from 客户表 where 客户号 = 'bb123'





--instead of insert触发器
不执行insert，执行的是触发器中的操作，但是在执行之前，临时表还是会改变的
create trigger trigger_insteadof_insert
on 客户表
instead of insert 
as
 declare @客户号2 varchar(20),@客户名2 varchar(20)
  select @客户号2 = 客户号 ,@客户名2 = 客户名 from inserted--存放插入的表
  print '要添加：'+convert(varchar ,@客户号2)+'，'+@客户名2;--这里如果数据类型不是varchar而是int再用convert，这里没必要用。
  go
  
 --进行触发： insert into 客户表(客户号,客户名,地址,电话)
             values('bb123','xiaoming','020802','110119')
             
   结果就是：要添加：bb123 ，xiaoming  ，其中标识列是不会改变的，因为实际上并没有向表中增加新的数据，insert操作没有执行，但是inserted表中是有要插入的数据的，触发器中的操作就是打印一下inserted表中的数据。   

游标

对数据查询的结果集的一种访问机制，用户对结果集进行逐条访问（单条数据）
对象：结果集
游标：定义在结果集上的一个指针（可以控制这指针来遍历这个结果集）
缺点：数据读取出来放到TempDB里，放到内存中 --内存空间有限，不可以太大，内存空间不足。

分类：静态，动态，只进

静态：结果集，对数据库如何操作，结果集都不会改变。如果想要结果集和数据库改变保持一致，必须要关闭这个游标，重新打开。在检测的时候是检测不到数据的变化，消耗的资源相对比较少。动态相反。--一般选择静态游标
动态：与静态相对的，前后滚动，结果集会获取所有的改变，提取时，对数据库的操作都可以通过游标可见。
只进：不支持前后滚动，只支持从头走到尾，对数据库进行的更改是可见的，但是不可以滚动。如果在修改前就读取到了，那么在修改后，前面读到的数据也不会进行修改。（在提取后所作的更改是不可见的。）--效率比静态还高

游标的生命周期：
声明-打开-读取数据-关闭游标-释放游标


--创建只进游标：
declare dingdan_cursor cursor local --local表示局部，global表示全局游标
for 
  select 客户号,客户名 from 客户表
  --这里可以加where条件
  --打开游标
  open dingdan_cursor 

  declare @vardingdan cursor,@客户号2 varchar(20),@客户名2 varchar(20) --定义单行数据游标和要用到的变量
  set @vardingdan = dingdan_cursor
  fetch next from @vardingdan into @客户号2 , @客户名2--向后提取数据（prior 向上，first 第一个，last 最后一个，absolute n绝对位置，relative n从当前开始第一个，这里只能是next，因为是只进）
  while @@FETCH_STATUS = 0--判断是否执行成功
  begin 
       print @客户号2+','+@客户名2
	   fetch next from @vardingdan into @客户号2 , @客户名2
  end

  --关闭游标：
  close dingdan_cursor
  --释放游标
  deallocate dingdan_cursor
  
  结果：
  A00112,四环科技发展公司    
B20001,萨特高科技集团      
B21001,爱心生物工程公司    
C10001,三益贸易公司        
C10005,比特电子工程公司    
C20111,一得信息技术公司    
在begin和end中可以对表进行修改
 if @客户号2 = 'B20001'
	      update 客户表 set 客户名 = '新公司' where 客户号= 'C10001' 
	     
         
         
	      
--静态游标：表的改变是不可见的，结果集是不变的。
创建静态游标：
  declare dingdan_cursor_static cursor static  local --local表示局部，global表示全局游标
for 
  select 客户号,客户名 from 客户表
  --这里可以加where条件
  open dingdan_cursor_static

  declare @vardingdan cursor,@客户号2 varchar(20),@客户名2 varchar(20) --定义单行数据游标和要用到的变量
  set @vardingdan = dingdan_cursor_static
  fetch next from @vardingdan into @客户号2 , @客户名2--向后提取数据（prior 向上，first 第一个，last 最后一个，absolute n绝对位置，relative n从当前开始第一个，这里只能是next，因为是只进）
  while @@FETCH_STATUS = 0--判断是否执行成功
  begin 
       print @客户号2+','+@客户名2
       if @客户号2 = 'B20001'
	      update 客户表 set 客户名 = '新公司' where 客户号= 'C10001' 	   fetch next from @vardingdan into @客户号2 , @客户名2
  end

  --关闭游标：
  close dingdan_cursor_static
  --释放游标
  deallocate dingdan_cursor_static




动态游标：对数据库的改变是可见的。
将static改成dynamic