本文主要向大家介绍了SQLServer数据库技术知识，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。

常见的数据库模型：【数据库管理系统中数据的存储结构】
层次模型
网状模型
关系模型【SSMS 使用】



1.sqlserver的分离与附加【移动数据库的时候使用】
分离：删除链接，可以移动数据库的mdf文件
附加：选中mdf文件添加，还原数据库

sqlserver数据库的备份与还原


2.数据库创建的时候需要创建两种数据文件：
数据文件：
主数据文件：*.mdf 有且只有一个
次数据文件：*.ndf 有0~N个【保证数据库不受硬盘容量大小的限制，比如：主数据容量100G,数据庞大，添加次数据容量，保证数据正常存储】
日志文件:
*.ldf 必须有一个或者多个

3.数据类型
小数类型numeric(15,2)：表示整数13位，小数部分2位
bit : 0~1

*  varchar(10)：变长字符串：最多存储10位，多于10个截取存储10位，存储5个汉字，10个字母，牺牲性能，节省空间   最长8000
char(10):固定长度字符串，多余的部分空格补充：浪费空间，提高性能    最长8000
*  nvarchar(10) ：双字节结构存储每个字符，存储10个汉字，20个字母       
大文本 text类型


4.sql语句

SELECT TOP 3 * FROM STUDENT WHERE ID='1' ORDER BY NAME6664

插入多行数据：
方法一：将现有表中的数据添加到已有存在的表
INSERT  INTO 表名  (列名)  SELECT(源表名对应的类名) FROM 源表名   【不选择列名，全部添加】
方法二：
SELECT(列名) INTO 表名 FROM 源表名
方法三:
通过UNION 关键字，合并数据进行插入【插入多行数据】
INSERT INTO 表名 （列名） 
SELECT (对应列数据) UNION
SELECT (对应列数据) UNION
SELECT (对应列数据) UNION
SELECT (对应列数据) 




4.sqlserver数据的导入和导出
右键数据库，任务，选择执行数据的导入【注意修改表】

导出导入数据出现错误:未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序

错误的原因：
这是由于该计算机上没有安装Microsoft Access Database Engine组件，该组件帮助在现有的Microsoft Office文件与其他数据源之间传输数据
解决办法:
下载安装：//www.microsoft.com/downloads/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displayLang=zh-cn 

5.sqlserver函数
1.字符串函数
CHARINDEX 寻找一个指定字符串在另一个字符串中的起始位置
SELECT CHARINDEX('JBN','MY JBN COURSE ') 返回4 

LEN 返回字符串的长度 
SELECT LEN('AAAA') 返回4

UPPER 传递的字符串变成大写
SELECT UPPER('aaa课程')  返回AAA课程

LOWER 转换变为小写
SELECT LOWER('AAAA') 返回aaaa

LTRIM 和 RTRIM 字符串左右去空格【左右有空格，嵌套使用】
SELECT RTRIM('   aaa课程')  返回aaa课程

RIGHT 从字符串的右边开始返回制定数目的字符
SELECT RIGHT('ABCDEF',3) 返回DEF

REPLACE 替换一个字符串中的字符
SELECT REPLACE('ABCDE','C','1')  返回 AB1DE

STUFF 在一个字符串中删除制定长度的字符串，并在该位置插入一个新的字符串
SELECT STUFF('ABCDEF',2,3,'123')【从第2个开始，删除3个，插入123】 返回 A123EF

CAST(变量  AS  NVARCHAR(30))
SELECT CONVERT(NVARCHAR(20),GETDATE(),102)   AS DATETIME --当前的系统时间按照一定的格式转变
SELECT CONVERT(DATETIME,'2017-08-28 09:09:09') AS DATETIME  --字符串类型转换成日期类型

SUBSTRING
SELECT SUBSTRING('123456',1,4) AS TITLE  返回1234
SELECT SUBSTRING('123456',0,4) AS TITLE  返回123


2.日期函数
GETDATE 得到当前系统的日期
SELECT GETDATE() 返回 今天的日期
select YEAR(GETDATE()) as  mytime
select month(GETDATE()) as  mytime
select day(GETDATE()) as  mytime

DATEADD  将指定的数值添加到指定的日期部分后的日期
SELECT DATEADD(mm,4,'01/01/2009')【月份加4】 返回2009-05-01 00:00:00:000

DATEDIFF 返回日期之间指定日期部分的间隔
SELECT DATEDIFF(mm,'01/01/2009','05/01/2009')  返回4

DATENAME 日期中指定日期部分的字符串形式
SELECT DATENAME(dw,'01/01/2009')【dw表示weekday】 返回 星期四
SELECT DATENAME(ww,'01/01/2009')【ww表示week】[本周是一年中的第几周 ]返回 1
SELECT DATENAME(yy,'01/01/2009')【返回指定时间所在的年份】 返回 2009
SELECT DATENAME(m,'01/01/2009') 返回 1
SELECT DATENAME(d,'01/01/2009') 返回 1
SELECT DATENAME(dy,'01/01/2009') 返回一年中的第几天 1

DATEPART 日期中指定日期部分的整数形式 
SELECT DATEPART(day,'01/15/2009')【day:天 month:月 year:年 】 返回15



3.数学函数：对数值进行代数运算
RAND 返回从0到1之间的随机数
SELECT RAND() 返回0.331898004481053

ABS 取表达式的绝对值
SELECT ABS(-43) 返回43

CEILING 取大于或等于指定值、表达式的最小整数
SELECT CEILING(44.3)  返回44

FLOOR  取小于或等于指定值、表达式的最大整数
SELECT FLOOR(44.3)  返回43

POWER 取数值表达式的幂值
SELECT POWER(5,2) 返回25

ROUND 将数值表达式四舍五入为指定精度
SELECT ROUND(43.543,1) 【1表示小数部分保留1位】返回43.500

SIGN 对于正数返回1，负数返回-1，0返回0 
SELECT SIGN(-21) 返回-1

SQRT 取浮点表达式的平方根
SELECT SQRT(9) 返回3


4.系统函数：获取sqlserver 中对象和设置的系统信息【DBO是每个数据库的默认用户】
CONVERT 转换数据类型
SELECT CONVERT(VARCHAR(50),12345) 返回'123456'

CURRENT_USER 返回当前用户的名字
SELECT CURRENT_USER 返回登陆的用户名【dbo】
 
DATALENGTH 返回用于指定字符串的字节数
SELECT DATALENGTH('中国') 返回4

HOST_NAME 当前用户登陆的计算机名字
SELECT HOST_NAME() 返回 DESKTOP-T6R4T1T

SYSTEM_USER 当前登陆SSMS的用户名称
SELECT SYSTEM_USER 返回sa

USER_NAME 从给定的用户ID返回用户名
SELECT USER_NAME(1) 返回 从任意数据库中返回’dbo‘
 
 
5.聚合函数【使用聚合函数不能和查询列一起使用，使用的话就要使用group by】【返回一个值】
SUM 求和
SELECT SUM(SCORE) FROM COURSE WHERE ID='9'

注意：
使用sum函数的时候，sql server 将忽略null，计算的时候不计算空值
使用sum函数，sql server 将集合中的smallint等当作int处理

AVG 求取平均分
SELECT AVG(GRADE) FROM STUDENT

MAX MIN 求最大值，最小值
SELECT MAX(SCORE) AS MAXS,MIN(SCORE) AS MINS FROM SCORE WHERE GRADE>=60

COUNT(),求列数
SELECT COUNT(GRADE) FROM STUDENT
SELECT COUNT( DISTINCT GRADE) FROM STUDESNT


6.模糊查询
LIKE BETWEEN IN 
SELECT * FROM STUDENT WHERE NAME LIKE  '%明%'
SELECT * FROM STUDENT WHERE NO BETWEEN 60 AND 100
SELECT * FROM STUDENT WHERE ADDRESS IN ('北京','上海','天津') 



7.分组查询 group by【以指定的列为组】【注意：使用聚合函数和其他列组合的话，就需要使用分组】
select count(address) as c, address as addre from Table1
group by address

分组筛选:[对分组查询出来的结果再次进行筛选] 
select count(address) as c, address as addre from Table1
group by address
having count(*)>2

8.链接查询
1.内链接查询 INNER JOIN:做笛卡儿积，筛选符合条件的
SELECT S.SNAME FROM STUDENT S
INNER JOIN COURSE C
ON S.NO=C.SNO
2.外链接查【在内链接的基础上进一步完善】做内链接的时候可能on条件的时候没有匹配的，就会某一条的数据不显示，外链接会显示出来如：
name   grade  score
曲灵风 NULLNULL

主表（左表）【主表的数据一定显示】
左外连接查询 LEFT JOIN


主表（右表）【主表的数据一定显示】
右外连接查询 RIGHT JOIN


事务：
保证逻辑数据的一致性和可恢复性必不可少的利器
锁：
多用户访问同一数据资源时，对访问的先后次序权限管理的一种机制，没有锁事务将一塌糊涂，不能保证数据的安全正确读写
死锁:
不同事务之间抢占数据资源造成的

sql server常见的三种事务
1.自动提交事务：是SQL Server默认的一种事务模式，每条Sql语句都被看成一个事务进行处理
2.显式事务：T-sql标明，由Begin Transaction开启事务开始，由Commit Transaction 提交事务、Rollback Transaction 回滚事务结束
显式事务的应用：
常用语句就四个。

Begin Transaction：标记事务开始。
Commit Transaction：事务已经成功执行，数据已经处理妥当。
Rollback Transaction：数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。
Save Transaction：事务内部设置的保存点，就是事务可以不全部回滚，只回滚到这里，保证事务内部不出错的前提下【出异常的时候会还原到保存点】。


begin tran
--捕捉错误机制 try catch 
begin try
--语句正确
insert into Student(id,name,address)  values(11,'11','11')
--加入保存点
save tran savePoint_name
--id是int 出错
insert into Student(id,name,address)  values('22','22','22')
--语句正确
insert into Student(id,name,address)  values(33,'33','33')
end try
begin catch
select ERROR_NUMBER() as errorNumber, --错误代码
  ERROR_SEVERITY() as errorSeverity, --错误严重级别 级别小于10 try catch 捕捉不到
  Error_state() as ErrorState ,  --错误状态码
  Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
          Error_line() as ErrorLine,  --发生错误的行号
          Error_message() as ErrorMessage  --错误的具体信息
if(@@TRANCOUNT>0)  --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
rollback tran  savePoint_name---由于出错，这里回滚到开始，第一条语句也没有插入成功。
end catch
if(@@TRANCOUNT>0)
commit tran --如果成功Student表中，将会有3条数据。



----------------------------游标无事务循环

declare @name nvarchar(20),@address nvarchar(20)
declare cur_stu cursor for ---把查询出来的结果集放在游标中
select name,address from Student
--for read only  只读游标
--for update of name   --定义只更新name的游标
declare @stu cursor  --把上面定义的游标存放在游标变量里
set @stu=cur_stu

--取出表里的第一行
open @stu
fetch next from @stu into @name,@address --取出游标里的数据放入变量
print  @name
print @address
while @@FETCH_STATUS=0  --返回被fetch语句执行的最后游标状态
begin
--需要进行的更改数据库的操作语句
fetch next from @stu into @name,@address
print  @name
print @address
end
close @stu
deallocate cur_stu



-----------------------------------游标有事务循环

declare @name nvarchar(20),@address nvarchar(20),@error int
set @error=0
begin tran ----申明事务
declare cur_stu cursor for ---把查询出来的结果集放在游标中
select name,address from Student
--for read only  只读游标
declare @stu cursor  --把上面定义的游标存放在游标变量里
set @stu=cur_stu

--取出表里的第一行
open @stu
fetch next from @stu into @name,@address --取出游标里的数据放入变量
while @@FETCH_STATUS=0  --返回被fetch语句执行的最后游标状态
begin
--需要进行的更改数据库的操作语句
set @error=@error+@@ERROR  --记录每次的sql是否正确 ，0正确
fetch next from @stu into @name,@address   --转到下一个游标
print  @name
print @address
end
if @error=0
begin
commit tran --提交事务
end
else
begin
rollback tran --回滚事务
end
close @stu
deallocate cur_stu



----------------------------------事务[设置保存点]
begin tran
--捕捉错误机制 try catch 
begin try
--语句正确
insert into Student(id,name,address)  values(11,'11','11')
--加入保存点
save tran savePoint_name
--id是int 出错  
insert into Student(id,name,address)  values('22','22','22')
print 1/0;
--语句正确
insert into Student(id,name,address)  values(33,'33','33')
end try
begin catch
select ERROR_NUMBER() as errorNumber, --错误代码
  ERROR_SEVERITY() as errorSeverity, --错误严重级别 级别小于10 try catch 捕捉不到
  Error_state() as ErrorState ,  --错误状态码
  Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
          Error_line() as ErrorLine,  --发生错误的行号
          Error_message() as ErrorMessage  --错误的具体信息
if(@@TRANCOUNT>0)  --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
rollback tran  savePoint_name---由于出错，这里回滚到开始，第一条语句也没有插入成功。
end catch
if(@@TRANCOUNT>0)
commit tran --如果成功Student表中，将会有3条数据



----------------------------------事务[有错误继续执行]
--设置xact_abort on/off 只当是否回滚当前事务,on:当前sql出错,回滚整个事务,为off时如果sql出错回滚当前sql语句，其它语句照常运行读写数据库。
--【需要注意的时：xact_abort只对运行时出现的错误有用，如果sql语句存在编译时错误，那么他就失灵啦。】
--下面例子，当off的时候有两条数据，on的时候没有数据 ，数据过大，溢出太多
set xact_abort on
begin tran
--捕捉错误机制 try catch 
--语句正确
insert into Student(id,name,address)  values(11,'11','11')
--id是int 出错  
insert into Student(id,name,address)  values(9000000000000000000000000000000000000,'22','22')
print 1/0;
--语句正确
insert into Student(id,name,address)  values(33,'33','33')

commit tran --如果成功Student表中，将会有3条数据。





防止死锁的产生：
尽可能使用分区表，分区视图，把数据放置在不同的磁盘和文件组中，分散访问保存在不同分区的数据，减少因为表中放置锁而造成的其它事务长时间等待。

创建存储过程的作用:
存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
简单来说 存储过程 可以同时执行 多条sql语句 而且存储过程要比通常的sql语句要快 可以向里面传进参数 和 传出参数
3.隐式事务：使用Set IMPLICIT_TRANSACTIONS ON 将将隐式事务模式打开，不用Begin Transaction开启事务，当一个事务结束，这个模式会自动启用下一个事务，只用Commit Transaction 提交事务、Rollback Transaction 回滚事务即可



触发器：
是一种特殊类型的存储过程，他不同于我们之前介绍的存储过程，触发器只要是通过事件进行触发自动调用执行的，而存储过程是通过存储过程的名称被调用
什么是触发器


    触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。
    触发器一般用在check约束更加复杂的约束上面。
触发器和普通的存储过程的区别是：
触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。


DML触发器分为：
    1、 after触发器（之后触发）
        a、 insert触发器
        b、 update触发器
        c、 delete触发器


    2、 instead of 触发器 （之前触发） 
  其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。


触发器有两个特殊的表：
插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。

创建DML触发器语法：
create trigger tgr_name
on table_name
with encrypion –加密触发器
   for update...
as
   Transact-SQL
  
例子：
--创建insert型触发器
if(OBJECT_ID('tgr_classes_insert','tr') is not null) --不能查询非架构范围内的对象（如 DDL 触发器)
drop trigger tgr_classes_insert
go
create trigger tgr_classes_insert
on Student 
for insert  --插入触发器
as 
--定义变量
declare @id int ,@name nvarchar(20),@address nvarchar(20)
select @id=id, @address=address,@name=name from Student1
set @id=@id+5
set @name=@name+'student1'
set @address=@address+'student1'
insert into Student values(@id,@name,@address)
print '添加学生成功'
go

--测试插入数据
insert into Student values(4,'44','44') 

select * from Student
delete from Student where address='44'


--创建删除触发器【delete触发器会在删除数据的时候，将刚才删除的数据保存在deleted表中。】

if(OBJECT_ID('tgr_classes_insert','tr') is not null)
drop trigger tgr_classes_insert
go 
create trigger tgr_classes_insert
on Student
for delete --删除触发器
as
print '备份数据中'
if(OBJECT_ID('classesBackup', 'U') is not null)
--存在classesBackup，直接插入数据
insert into classesBackup  select id, name, address from deleted;
else 
--不存在classesBackup创建再插入
       select * into classesBackup from deleted;
print'备份成功'  
go 

-- 测试删除
--不显示影响行数
--set nocount on;
delete from Student where name='1'




--创建update触发器【 update触发器会在更新数据后，将更新前的数据保存在deleted表中，更新后的数据保存在inserted表中。】

if(OBJECT_ID('tgr_classes_insert','tr') is not null)
drop trigger tgr_classes_insert
go
create trigger tgr_classes_insert
on Student 
for update
as
declare @oldname varchar(20),@newName nvarchar(20)
--更新前的数据
select @oldname=name  from deleted
--可以把之前的数据存放在一张表中
if(exists (select * from Student where name like '%'+@oldname +'%'))
begin
--更新后的数据
select  @newName=name from inserted
update Student set name=replace(name,@oldname,@newName) where name like '%'+@oldName+'%'
print '更新数据成功'
end
else
print '没有该数据'
go

--更新测试
update Student set name='cuicui' where name='老虎student1'


--update更新列级触发器

if (object_id('tgr_classes_update_column', 'TR') is not null)
   drop trigger tgr_classes_update_column
go
create trigger tgr_classes_update_column
on Student
   for update
as
   --列级触发器：是否更新了班级创建时间
   if (update(address))
   begin
       raisError('系统提示：student表中的address不能更改！', 16, 11);
       rollback tran;
   end
go

--测试更新列触发器
update Student set address='ooo'
   
   
启用禁用触发器
disable trigger tgr_message on Student 
enable trigger tgr_message on Student 

查询创建的触发器信息
select * from sys.triggers
select * from sys.objects where type = 'Td';

查看触发器触发事件
select te.* from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';

查看创建触发器语句
exec sp_helptext 'tgr_message'

触发器实例
验证插入数据
if ((object_id('tgr_valid_data', 'TR') is not null))
   drop trigger tgr_valid_data
go
create trigger tgr_valid_data
on student
after insert
as
   declare @address nvarchar(20),@name varchar(20);
   select @name = s.name, @address = s.address from inserted s;
   if (@address = '北京')
   begin
       raisError('插入新数据的address有问题', 16, 1); --第二个参数是严重程度【级别小于10 try catch 捕捉不到】，第三个是状态
       rollback tran;
   end
go

测试插入数据
insert into Student values(7,'77','上海')


操作日志

if (object_id('log', 'U') is not null)
   drop table log
go
create table log(
   id int identity(1, 1) primary key, --创建自动增长的id
   action varchar(20),
   createDate datetime default getDate()
)
go
if (exists (select * from sys.objects where name = 'tgr_student_log'))
   drop trigger tgr_student_log
go
create trigger tgr_student_log
on student
after insert, update, delete
as
if(exists (select 1 from inserted)) and (exists (select 1 from deleted))--有时候为了提高效率，只是为了测试下某个表中是否存在记录，就用1来代替列
begin 
insert into log(action) values ('update')
end
   else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
   begin
       insert into log(action) values('inserted');
   end
   else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
   begin
       insert into log(action) values('deleted');
   end
go

测试日志列表
insert into student values('7','7','7');
update student set name = '66' where name = '55';
delete student where name = '77';
select * from log;
select * from student order by id;
本文由职坐标整理并发布，了解更多内容，请关注职坐标SQL Server数据库频道！