SQL Server – 数据库初识

在网络笔试中,常遭遇数据库的标题,遂来大致计算,注意,以 Sql Server 数据库为例。

数据库

数据库系统,Database
System,由数据库和数据库管理种类整合。
数据库,DataBase
,是电脑应用体系中的一种越发管理数据财富的种类,根据数据结构来集团、存储和管理数据的堆栈。数据表是最中央的数据库对象,是储存数据的逻辑单元。

数据库管理连串,DataBase Management
System,DBMS,管理数据库,负责数据的存储、安全、一致性、并发、复苏和访问。

数据模型,平常由数据结构、数据操作和完整性约束三局地组成。

SQL语言

结构化查询语言,Structured Query
Language,SQL是一种数据库查询和次序设计语言,用于存储数据以及询问、更新、管理关周全据库系统,高级的非进程化编程语言。Transact-SQL是微软对SQL的恢弘,具有SQL的紧要性特点,同时增添了变量、运算符、函数、流程控制和注释等语言因素。
SQL语言分四类:数据定义语言[DDL]、数据查询语言[DQL]、数据操纵语言[DML]、数据控制语言[DCL]。

– [1].DDL(Data Defination
Language)
      style=”color: blue;”>成立和治本数据库中的对象,定义SQL格局以及数据库、表、视图和目录的成立和撤回。不需COMMIT。
     创建CREAT,  修改ALTER,  删除DROP,  删除TRUNCATE
     TRUNCATE,  RENAME

– [2].DQL(Data Query
Language)
     基本协会: SELECT子句、FROM 子句、WHERE子句组成查询块。
     SELECT<字段名表>,  FROM<表或视图名>,  
WHERE<查询条件>

– [3].DML(Data Manipulation
Language)
      style=”color: blue;”>直接操作数据表中的数据,依照要求寻找、插入、删除数据以及立异数据库.
     操作的单位是记录。DML要求COMMIT显式提交。
     插入INSERT,   删除DELETE,  更新UPDATE

– [4].DCL(Data Control
Language)
     用于授予或打消对用户对数据库对象的拜会权限,保证数据安全性。
     授权GRANT,  裁撤授权REVOKE,  显式限制权力集合DENY

事务控制语言TCL (Transaction Control Language)
交由COMMIT,回滚ROLLBACK,设置保存点SAVEPOINT
SQL>COMMIT:显式提交
SQL>ROLLBACK:回滚命令使数据库状态回到上次最终交给的景色
SQL>SET AUTOCOMMIT ON:自动提交
使用SQL命令直接完毕:隐式提交。

1.
数据类型

 1. 字符数据类型
  a. 字符串:char、varchar、text;
  b.
Unicode字符串:nchar、nvarchar、ntext,用N标识,unicode是统一字符编码标准,
双字节对字符(英文,汉字)编码;
   使用Unicode数据类型,可以最大限度地化解字符转换的题材。
 2. 数字数据类型
  a. 整数型:tinyint(1)、smallint(2)、int(4)、bigint(8);
  b.
Decimal和numeric:固定精度和小数位数,decimal(p,s)或numeric(p,s),0≤s≤p;
  c. 货币类型:smallmoney(4)、money(8);
  d. 近似数字:float、real(4);
  e. bit类型:0/1序列;
 3. 日期和岁月数据类型
  time、date、smalldatetime、datetime、datetime2、datetimeoffset;
 4. 二进制数据类型
  binary、varbinary;
 5. 其他数据类型
  uniqueidentifier:16字节的十六进制数字组合,全局唯一,
  sql_variant:协助各样数据类型;
  还有xml、table等,别的还足以自定义数据类型。

2.1
函数

置于函数详细介绍参考:行集函数、聚合函数、排行函数、标量函数
大概数据库书籍。
函数新闻查询
   a. 工具栏“扶助”- -> “动态资助”;
   b. 起首“文档教程”- -> “SQL server 教程”
系统函数 ~ 自定义函数
a. 系统函数
   允许用户在不直接访问系统表的情状下得到SQL系统表的消息。
b. 自定义函数:User Defined Function
优点

  • 模块化设计;
  • 实施进程快,缓存布署下跌编译费用、无需再一次解析和优化;
  • 减去互联网流量;

分类

  • 标量型函数:Scalar Function,只可以回到标量值;
  • 内联表值型函数:Inline table-valued Function,参数化的视图,只可以回去
    TABLE 类型;
  • 多注脚表值型函数:Multi-Statement Table-Valued
    Function,标量型函数和内联表值型函数的重组;

创建

  create function 函数名(@参数名 参数类型, [..])
      returns 返回值类型
   as
   begin
      SQL语句;
      return 返回的对象;
   end

注:begin…end 块中的语句不能有其它副功用。
查询
函数的概念、架构等。
修改/删除
alter/drop function 函数名

2.2
关键字

  a. set ~ select
 
select帮衬在1个操作内同时为多个变量赋值,不过为变量赋值和数据检索不或许而且进行,参考
两边的分别
  b. cast() ~ convert()
类型转换函数
  · cast(源值 as 目标项目);
  · convert(目的数据类型,源数据[,
格式化代号]),可以格式化日期和数值;
  c. delete ~ drop ~
truncate

  ·
delete:DML,删除数据表中的行(一行或全部行)/记录,自动隐式commit,不大概回滚;
        delete from 表名 where 条件
  ·
drop:DDL,显式手动commit,可以回滚,删除数据库、数据表或删除数据表的字段;
        drop table 表名
  ·
Truncate:神速、无日志记录,删除数据表中的数据、不删除表,不可復苏;
        truncate table 表名
  从删除速度来说,drop> truncate > delete,其余不一样详细参考
delete ~ drop ~
Truncate

  d. insert
 
注意区分上边3个insert语句的分别,第叁种Product格式,values中必须交给相应的值,其中国和东瀛期系统暗中认可1903-01-01;第叁种格式,values中使用default约束。

   insert into Product(productName,productPrice,productStorage,productDate,productClass) 
                 values('电冰箱', null, 0, '', 1)
   insert into Product(productName,productClass) values('电冰箱',1)

  批量插入数据
  [1]. insert into
目的表表名或列视图 select 检索语句
from 源表名
  [2]. select 列列表 into 目的表表名 from 源表表名     
  e. waitfor
   定时、延时或堵住实施批处理、存储进程或业务。  

3.
数量库表设计难点

  常用表操作格式 
  [a]. 创建数据库与表 
   create database/table 数据库名/表名 
  [b]. 查看表信息 
   exec sp_help 表名   
  [c]. 添加新列、修改列名与类型 
   alter table 表名 
    add 列名 列类型 
   exec sp_rename ‘表名.列名’, ‘新列名’ (注意必须加引号) 
   alter table 表名 
    alter column 列名 新的列数据类型     

  a. E-R模型图
 实体-联系(Entities-Relationships)模型,描述概念数据模型的艺术之一,软件生命周期的设计阶段,提供实体、属性、联系的面向用户的表明方法,实体之间存在一定、一对多、多对多的关联。
  b. 事关规范化
数据库完整性
  三大范式:
  · 第贰范式 1NF:全体属性(值)是不可分割的原子值;
  · 第①范式 2NF:全体属性数据必须借助主键;
  · 第1范式 3NF:数据库表中不或然包括已在此外表中包涵的非主键音信;
 关系型数据库三大完整性:
  · 实体完整性:主键约束
primary key,唯一且非空;
  · 参照完整性:引用完整性,外键约束
foreign key 等关系约束;
  · 用户自定义完整性:域完整性,字段类型等;
  c. 分区表
 依据数据水平情势分区,将数据分布于数据库的两个例外的文本组中:
  - 改良大型表以及独具各样访问方式的表的可伸缩性和可管理性;
  - 对于多CPU系统,帮助相互格局对表操作;
 分区函数~分区方案:

  create partition function 函数名(分区条件) 
   as range left/right for values() 
  create partition scheme 方案名 
   as partition 函数名

 3个分区方案不得不用三个分区函数,三个分区函数可以被四个分区方案共用。
  d. 文件组 
 在数据库中对文件进行分组的一种管理机制,一个文件不可以是五个文本组的分子。文件组只好分包数据文件,事务日志文件无法是文本组的一有的。使用文件组可以凝集用户对文本的借助,通过文件组直接管理文件,可以使得同一文件组内的公文分布在差别的硬盘中,能拉长IO品质。
 具体地可参考
文件和文件组
  e. 标识符
 每一行数据必须都有壹个唯一的可分其他质量作为标识符。
  · identity:本地(表内)唯一,使用方法
identity(开头种子值,增量);
     select @@identity:查看新插入行数据的标识符(的序号)
 
     select $identity from 表名:引用(突显)表的绝无仅有标识符列
 
  ·
uniqueidentifier:全局唯一,应用rowguidcol属性作为标识符提示新列为guid列,暗许定义使用newid或newsequentialid()函数生成全局唯一值;同理,使用$rowguid引用唯一标识符列。
     ID uniqueidentifier default newsequentialid() rowguidcol   

 f. 主键 PK ~ 外键 FK 

 主键:保障全局唯一性;
 外键:建立和增强四个表数据里面链接的一列或多列,强制引用完整性,可以有效预防误删;
  主键约束 ~ 外键约束 ~
唯一约束

  – 主键约束
  主键约束用于落实实体完整性,逐个表的主键有且只好有3个,主键列不可以包含null值。评释联合主键拔取第① 、3种方法。创立PK约束,具体参见大话数据库或
三种形式创设主键约束
  系统暗中同意生成的主键约束名为:PK_表名_队列串号
  – 外键约束
  外键约束用于落到实处参照完整性,一个表A:foreign key指向另3个表B:primary
key,表B是主表,表A是从表。外键约束创设三种办法,参见大话数据库或者
两种方法创设外键约束
  系统暗许生成的外键约束名为:FK_表名_字段名_队列串号
 示例主/外键的二种成立方法:
  1.
创制table时,直接在字段前边申明为 primary key 或者 foreign key

1  create table orders(
2         orderID varchar(10) not null primary key,
3         orderProduct varchar(30) not null,
4         personID varchar(20) foreign key references persons(personID)
5   );

  2.
创建table时,全体字段评释之后,添加主键和外键的封锁语句

1  create table orders(
2         orderID varchar(10) not null,
3         orderProduct varchar(30) not null,
4         personID varchar(20) not null,
5         constraint PK_orders primary key(orderID),
6         constraint FK_orders_personID foreign key(personID) references persons(personID)
7  );

  3.
在table已成立后,为表添加主外键约束

1  alter table orders
2         add constraint PK_orders primary key(orderID),
3              constraint FK_orders_personID foreign key(personID) references persons(personID) 

  – not null 约束
  强制列不收受null值,具体运用参考上述代码。
  – default 约束
  用于向列中插入默许值,default只好用来insert语句且不能与identity同时用,具体运用参考如下示例代码:
  1.
创设table时,直接在字段前边注明为 default

1  create table Certifications(
2      certID int not null primary key identity(1001,1),
3      certName varchar(20) not null,
4      certPassword varchar(20) default('12345678'),
5      certTime varchar(30) default(getdate())
6  );

  2.
注意,default约束不设有此种方法;
  3.
在table已成立后,为表添加默许约束

1  alter table Certifications
2      add constraint DF_Certifications_certPassword default '123456' for certPassword,
3           constraint DF_Certifications_certTime default getdate() for certTime

  – check 约束
 
用于限制列中的数据的界定,为几个列定义check约束采取第三 、3种艺术,具体方法如下:
  1. 创制table时,直接在字段前面添加:check(条件表明式)
  2. 创造table时,全体字段注解之后添加:constraint CHK_表名_字段名 check(条件表达式)
  3. 在table已开立后,为表添加check约束

1   alter table 表名
2       add constraint CHK_表名_字段名 check(条件表达式)

  – unique 唯一约束
 用于唯一标识表中的每条记下,通过唯一性性索引强制实体完整性,unique算是对primary
key的填补,不过各样表可有五个unique约束且允许null值,创设unique约束的3种办法可参看上述措施:

1  [1].unique
2  [2].constraint UNQ_表名_字段名 unique(字段名)
3  [3].alter table 表名
4        add constraint UNQ_表名_字段名 unique(字段名)

  总结
  · 获取table的束缚音信:exec
sp_helpconstraint 表名
  · 裁撤上述各个束缚:alter
table 表名 drop constraint
主/外键约束名 
  · 关闭/开启约束检测:nocheck/check constraint 约束名/all
  · 若表中已存在数量,在抬高封锁以前先选拔with nocheck能够禁止对已有数据的检测。
  · 级联更新/删除:on
update/delete cascade

4.
或多或少高级搜索技术

where
… union … group by … having … order by … (limit) … 

  a. 分组技术
  SQL Server
之二种分组技术介绍

  · group by
  在select中作为分组条件的列名一定即使在group by子句中动用的列列表中。

       select 作为分组条件的列名
聚合总结函数(被计算字段列) from
表名 group by 用于分组的列列表(A,B,C) 

  优先级:C > B > A
  · having
  having 与 where 语句看似,where 是在分拣在此之前过滤,having
是在分拣之后过滤,且having条件中时时包蕴聚合函数。
   group by … having … order by …  
  · rollup ~ cube
  rollup突显所选列的值得某一层次结构的成团,cube彰显所选列的值得拥有结成的集合,且进一步细化;两者均须求和group
by一起用。
  具体不相同详解见:rollup ~
cube
rollup
~ cube –
2

  b. 同步查询
  · union
  并集,用于整合二个以上的结果集,暗中认同去重,union
all不去重。但是有列类型和列数量是还是不是相应一致的范围。 
  c. 三番五次查询
 
 连接是关系型数据库模型的第三特征,通过两次三番运算符来完毕多少个表的联表查询,灵活,语句格式:

   select 表名.列名[列列表...]
      from table_A 连接运算符 table_B [on 联表查询的匹配条件]

  注意,在连接表查询中学会使用别名。以下可参考
延续查询简例接连关系示意图
  · 内连接
  inner join,也即平常连接,包罗等值连接、自然连接、不等连接。再次来到的询问结果集合仅仅是select的列列表以及适合查询条件和连接条件的行。其中,自然连接会去掉重复的属性列。  
  · 外连接
  outer
join,蕴涵左外连接、右外连接和完全连接。重回的询问结果集合不仅带有select的列列表以及适合查询条件和两次三番条件的行,还包涵左表(左连接)、右表(右连接)或七个连接表(完全连接)中的全体数据行。

      A left join B == B right join A;   
  · 交叉连接
  cross join,连接表中装有数据的笛Carl积,结果集的数额行数 =
第1个表中符合查询条件的多少行数 *
第3个表中符合查询条件的数目行数。cross
join后加条件只可以用where,不或者用on。  
  · 自连接
  连接首要字的两边都以同贰个表,将自个儿表的三个镜像当作另2个表来对待。自连接可以将急需两遍询问的说话综合成一条语句三回实施成功。参考示例:自连接查询,也可参见大话数据库中有关自连接的例证。
  d. 子查询
 即内部查询(inner
query),子查询就是位于select、update或delete语句中内部的查询。子查询在主查询执行此前实施一次,主查询使用子查询的结果。参考示例:子查询各类查询总计. 

  select select_list from table1
      where expression operator(select select_list from table2);

  · 单行子查询
  重临零行或一行。单行比较运算符:= ,>, >= ,< , <=
,<>。
  · 多行子查询 
  重临一行或多行。多行相比运算符:IN/NOT IN,ANY/ALL,EXISTS。
  ANY:匹配子查询拿到的结果集中的肆意一条数据;
  ALL:匹配子查询拿到的结果集中的全部数额;
  EXISTS:再次回到bool值,只检查行的存在性,而IN检查实际值的存在性(一般意况EXISTS品质高于IN)。
  f. 索引
  此处将引得拿出来作为单身的一章进行总计学习,如下。

5.
索引

目录是对数据库表中一列或多列的值举行排序的一种结构,急迅有效查找与键值关联的行,加速对表中著录的查找过滤或排序。索引采取 B树 结构。
优点:
 (1)飞快搜索读取数据;
 (2)保险数据记录的唯一性;
 (3)达成表与表之间的参考完整性,加速表和表之间的连日;
 (4)在拔取order by、group
by子句举办数据检索时,利用索引能够减掉排序分组时间;
 (5)通过接纳索引,能够在查询的经过中,使用优化隐藏器,提升系统的属性;
  缺点:
 (1)增加了数据库的贮存空间;
 (2)创立索引和保险索引要消耗时间;
 (3)插入和改动数据时要开销较多时光更新索引;
 (4)修改质量和摸索品质是相互顶牛的;
分拣:依照目录的逐条与数据表的大体顺序是或不是相同
 · 聚集索引
 
索引的一一与数据表的大体顺序相同,进步多行追寻速度。壹个表只能包括三个聚集索引。聚集索引的叶级是数据页,数据值的依次总是依据升序排列。在创制任何非聚集索引此前先创建聚集索引。聚集索引的平分大小约为数据表的5%。
 · 非聚集索引
 
索引的次第与数据表的物理顺序分裂,单行检索快。3个表最多244个非聚集索引。非聚集索引的叶级是索引页。索引页中的行标识符(或聚集键)指向数据页中的记录(或表的聚集索引,再经过聚集索引检索数据),行标识符由文件ID、页号和行ID组成,并且是绝无仅有的。数据堆通过选择索引分配图(IAM)页来爱护。
特征:
 · 唯一性索引
 保险索引列中的全体数码是唯一的。只幸而可以确保实体完整性的列上创立唯一性索引。
 · 复合索引
 
2个索引成立在3个或多少个列上。不可以跨表建立复合列。注意列的排列顺序。复合索引可以升高查询品质,缩短在贰个表中所成立的目录数量。复合索引键中最多可以整合16列。
创设索引:
 · 直接开立:索引创造向导或create index
 基本方法,灵活易伸张、优化索引。语法:

 create [unique][clustered|nonclustered] index 索引名  
   on {表|视图}(列 [asc|desc][,...n])

 · 直接创立:利用约束直接创造
 主键约束 – ->
唯一性聚集索引,唯一性约束 – ->唯一性非聚集索引。
 利用约束创设索引的预先级高于create
index语句创造的目录。
保安索引:
 · 查看索引
  [1]. exec sp_helpindex 表名
  [2]. select * from sysindexes [where name = “索引名”]
 · 修改索引
  [1]. 修改索引名:exec sp_rename ‘表名.索引名’, ‘新索引名’
  [2]. 重新生成索引:alter
index 索引名/all on 表名
           rebuild;
     重新生成索引会先删除再重建索引。可以不要rebuild,直接用set设置索引选项。
 · 删除索引
   drop index 索引名 on 表名
   最幸亏剔除之前,利用exists判断索引名的存在性;
 · 总结音讯
 计算音讯是储存在Sql Server中列数据的样书,Sql
Server维护某一索引关键值的分布统计音讯。
  [1]. exec sp_updatestats
  [2]. update statistics 表名
[索引名]
 ·dbcc
showcontig
:展现表的数据和目录的零散新闻。
 ·dbcc dbreindex(表名,
索引名):重建表的二个或两个目录。
 ·showplan_all 和 statistics
io
:分析索引,查询质量,更好的调整查询和目录。
   set showplan_all on/off
   set statistics io on/off 
参考:
[1].
数据库索引的兑现原理目录由表及里
[2].
表和目录数据结构系列布局SQL索引学习-索引结构

6.
视图

视图是一种逻辑对象,是由主旨表导出的虚拟表,不占用其他数据空间、不存储数据,仅封装预约义的询问语句,其情节由询问定义。视图是查看数据库表数据的一种方法,提供了储存预定义的询问语句作为数据库中的对象以备后用的法力,但视图无法引得。被询问的表称为基表,对视图的多寡操作(增、删、改),系统依据视图的概念去操作与视图相关联的基本表。
优点:
 (1)保障数据的逻辑独立性,数据保密;
 (2)隐藏复杂的SQL,SQL语句复用,数据简化操作逻辑,易于检索数据;
 (3)重新格式化检索出来的数据;
成立视图: 
  create
view 视图名 [with
schemabinding/encryption]
as 查询语句  
 (1)对于视图和基表必须紧凑结合的景色,利用with
schemabinding将视图定义为索引视图;
 (2)对创制视图的SQL语句加密,利用with encryption;
珍爱视图:
 · 查看视图
  [1]. exec sp_helptext 视图名
  [2]. select definition
from sys.sql_modules
      where object_id=object_id(‘视图名’)
 · 修改视图
    alter view 视图名 as 查询语句  
   重命名视图: exec sp_rename 旧视图名, 新视图名
 
 · 删除视图
    drop view 视图名1 [,
视图名2, …]   

7.
游标

游标是一种只和一组数据中某1个记下举行互动的章程,是对(select)结果集的一种增添。将面向集合的数据库管理种类和面向行的程序设计结合,紧要用以交互式应用。
Transact-SQL 游标
储存进度、触发器和
T-SQL脚本,服务器端(后台)游标,仅支持单行数据提取,分为;

  • 静态游标:快照游标,在 tempdb 中创立游标;须求目前表保存结果集;
  • 动态游标:打开速度快、不需变更一时半刻内部工作表,但总是速度慢,不协理相对提取;
  • 只进游标:暗中承认值,顺序提取、不扶助滚动,最节省财富;
  • 键集驱动游标:键集唯一标识行,键集是开辟游标时在 tempdb
    中变化并内置在表 keyset 中;须求目前表保存键集;

注:客户端(前台)游标,仅协助静态游标,暗中认同在客户机上缓存整个结果集、需保证游标地方新闻。服务器(后台)游标品质更佳、更标准的一直更新,允许多少个基于游标的移动语句。
动用游标的典型进度,分为:

  • 声称游标:declare 游标名称 + SQL检索语句

    declare 游标名称 cursor

     [local|global] [forward_only|scroll] [static|dynamic] ..
    

    for SQL(select)检索语句

  • 开拓游标: open [golbal] 游标名称 | 游标变量
     ,游标打开的同时搜寻数据并蕴藏。

  • 领取数额

    fetch [next|prior|first|last | absolute|relative]

        from [global] 游标名称 | 游标变量
        into 结果变量[..]
    

    定位修改和删除数据:前提是用  for
update of 列列表; 设置可编制的列。

  update 表名 set 列名=新值[..] where current of 游标名
  delete from 表名 where current of 游标名
  • 闭馆游标: close [golbal] 游标名称 | 游标变量  
  • 除去游标: deallocate [golbal] 游标名称 | 游标变量  

注:游标变量指点用了游标的变量。其余操作:

  select @@CURSOR_ROWS;    // 游标中的数据行数
  select @@FETCH_STATUS;   // fetch执行状态(-2,-1,0)  

8.
仓储进度

仓储进度(Stored
Procedure),数据库架构成效域内的最首要目标,是储存在巨型数据库系统中一段为了达成一定功用的可复用的代码块,是SQL语句和可选控制流语句的
预编译
集合,经过第4回编译后再也调用不必再一次编译。存储进程主要用来再次回到数据。
.vs 函数

  • 函数不能改改数据库表数据,存储进程可以;
  • 积存进程必须 execute
    执行,函数调用更灵活;

优点:简单、安全、高性能

  • 允许标准组件式编程,可移植性、可复用;
  • 简不难单易用,预编译、执行进程快、效能高;
  • 改进安全部制、保障数据的平安;
  • 节省互联网流量、降低网络负载;

分类

  • 系统存储进程:存储在 master 数据库中,以
    “sp_”为前缀,用于从系统表中获取新闻。
  • 用户自定义存储进程:T-SQL存储进程、CLRubicon存储进度、一时存储进程。不或许将CL奥迪Q5存储进程成立为权且存储进程。

创建

1  create proc|procedure 存储过程名
2        (@parameter 参数数据类型 [,...])
3  as
4  begin
5    < SQL语句代码块 
6    return >
7  end

返回值

  • 应用 return 重返五个值;
  • 选择 output 定义再次来到参数来回到三个值; 

维护
· 查看:
  [1]. exec sp_helptext 存储进程名;
  [2]. sys.sql_modules目录视图;
  [3]. object_definition元数据函数; 
· 加密:with encryption
· 修改:直接将 create 替换为
alter;
· 删除:drop proc 存储进程名;
执行

  • 语法分析阶段
  • 剖析阶段
  • 编译阶段:分析存储进度、生成存储进程执行陈设。执行布署存储在进度火速缓存区(专门用于存储已经编译过的询问规划的缓冲区)。
    • 重复编译:[1].sp_recompile;[2]. 执行时在 exec 语句中采纳with recompile;
  • 举行阶段

9.
触发器

Trigger,触发器是出格的仓储进度,由 事件
自动触发,无法显式调用,主要用于维护和增强数据的(一致/引用)完整性约束和作业规则([1].
约束;[2]. 触发器)。触发器可以级联嵌套。常用的 inserted 和 deleted
表是对准当下触发器的一对表,在高速缓存中蕴藏新插入或删除的行数据的副本。可以了解为委托事件。经常触发器只与单个表关联。 
自律 vs 触发器 vs 存储进程
自律首要被用于强制数据的完整性,能提供比触发器更好的属性;触发器常用来评释工作规则或是复杂的多少印证。触发器可以兑现约束的满贯功用,但先行通过自律完毕。

  • 错误消息管理:约束只可以拔取规则的连串错误新闻,触发器可以自定义错误音讯;
  • 天性差别;
  • 治本维护的工作量; 

参考:自律与数据库对象规则、暗许值+数据库设计中约束、触发器和仓储进程
事件 –
-> 触发器 – -> 存储进度
·  DML
触发器:响应数据操作语言事件,将触发器和接触它的话语作为可在触发器内回滚的单个事务;常用、质量开销小,可以兑现相关表数据的级联更改、评估数据修改前后表的情状。
ζ  AFTE奥德赛 触发器:在 IUD
操作、INSTEAD OF 触发器和封锁处理未来被激发;推荐且不得不在表上指定; 
ζ  INSTEAD OF
触发器:在封锁处理从前被鼓舞(执行预处理补充约束操作),指定执行DML触发器以代表常常的触及动作,优先级高于触发语句的操作;
注:每一个表或打算针对各样 DML
触发操作 IUD,有且不得不有四个一点青眼的 INSTEAD OF 触发器,可以有两个照应的
AFTE瑞鹰 触发器。
ζ  CLHaval触发器:执行在托管代码中的方法;
·  DDL 触发器:响应数据定义语言事件,用于在数据库中施行管理职责;
·  登录触发器:响应 logon 事件,用于审核和决定服务器会话;
优点

  • 预编译、已优化,执行成效高;
  • 已封装,安全、易维护;
  • 可重复使用;

缺点

  • 侵夺服务器财富多;
  • 后置触发(事后诸葛武侯);

创制与保安
·  DDL

  create/alter trigger 触发器名称
        on 作用域(DDL:数据库名database/服务器名all server)
        FOR create|alter|drop|grant 等DDL触发器
   as SQL处理语句

  删除: drop trigger 触发器名;  修改: create – -> alter  
·  DML

  create trigger 触发器名称
       on 作用域(DML:表名/视图名)
       [FOR|AFTER|INSTEAD OF] {[insert [,] update [,] delete]}
   as SQL处理语句

  嵌套:级联触发,递归触发
   ·  直接递归:更新T,触发Trig,Trig更新T,再一次触发Trig;
   ·
直接递归:更新T1,触发Trig1,Trig1更新T2,T2触发Trig2,Trig2更新T1;
  参考:何以控制触发器递归

10.
事务 – 锁

 具体参考 业务和锁 –
sqh

11.
全文索引

全文索引是一种新鲜序列的基于标记的功能性索引,用于提升在大数据文本中寻找指定关键字的快慢,由 全文索引引擎服务 (SQL Server
FullText Search)创造和维护。全文索引成立和尊崇的经过称为填充:完全填充、基于时间戳的增量式填充、基于更改追踪的填写。全文索引只可以在数据表上创办。
全文索引 .vs. 普通索引

  • 平时索引接纳B-Tree结构,全文索引基于标记生成倒排、堆积且压缩的目录;
  • 一般性索引适于字符/字段/短文本查询,全文索引是依照关键字查询的目录,针对语言词语/长文本搜索;
  • 种种表允许有几七个常见索引,全文索引只可以有壹个;
  • 平日索引自动更新、实时性强,全文索引要求定期维护;

全文目录 全文索引
储存全文索引,是创办全文索引的前提。全文目录是虚构对象,是意味着全文索引的逻辑概念。全文目录和全文索引都以为全文检索查询服务。

  • rebuild:重新生成全文目录;
  • reorganize:优化全文目录;

    create fulltext catalog 全文目录名
    create fulltext index on 全文索引基于的表名[索引包括的列列表]

原理:两步走
对文件进行分词,并为每一个涌出的单词记录多少个索引项以保存出现过该单词的全数记录的新闻。全文索引引擎对投入到全文索引的列中的内容按字/词建立目录条目,即先定义2个词库,然后在篇章中检索每一个词条(term)出现的频率和岗位,把这几个频率地方信息按词库顺序总结,完成对文件建立3个以词库为目录的目录。
· 创立基于关键字查询的目录
     怎么样对文件进行分词:二元分词法、最大匹配法和计算办法
     建立目录的数据结构:选择倒排索引的布局
· 在目录中搜寻一定
   全文谓词:在
select 的 where/having 子句中指定
     contains:精确。简单词、派生词、加权词、前缀词、邻近词;
     freetext:模糊。文本拆分,分别搜索;
   行集函数:在 from
子句中指定
     containstable:
     freetexttable:

参考:全文索引原理介绍全文索引原理及范例

SQL-Server Helper

1. 上边给出 SQL-Server
数据库命令执行的两种办法样例

图片 1图片 2

 1 public static bool ExecuteSqlNoResult(string sql)
 2 {
 3     using(SqlConnection conn = new SqlConnection())
 4     {
 5         try
 6         {
 7             conn.ConnectionString = RVCConnectingString;
 8             conn.Open();
 9             SqlCommand command = new SqlCommand(sql, conn);
10             command.ExecuteNonQuery();
11             return true;
12         }
13         catch(Exception ex)
14         {
15             // 
16             return false;
17         }
18     }
19 }

[1]. 执行SQL,无重返值

里头,SqlCommand表示要对SQL
Server数据库执行的三个Transact-SQL语句或存储进程。无法持续此类。

图片 3图片 4

 1 public static bool ExecuteSqlWithResult(string sql, out DataTable dtResult)
 2 {
 3     using(SqlConnection conn = new SqlConnection())
 4     {    
 5         dtResult = new DataTable(); 
 6         try
 7         {
 8             conn.ConnectionString = DatabaseConnectingString;
 9             conn.Open();
10             SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
11             sda.Fill(dtResult);
12             return true;
13         }
14         catch(Exception ex)
15         {
16             // 
17             return false;
18         }
19     }
20 }

[2]. 执行SQL,重临结果

其间,SqlDataAdapter表示用于填充System.Data.DataSet和更新SQL
Server数据库的一组数据命令和3个数据库连接。无法继续此类。

图片 5图片 6

 1 public static bool ExecuteSqlTrans(List<string> sqlList)
 2 {
 3     using(SqlConnection conn = new SqlConnection())
 4     {
 5         SqlTransaction sqlTrans = null;
 6         try
 7         {
 8             conn.ConnectionString = DatabaseConnectingString;
 9             conn.Open();
10             
11             sqlTrans = conn.BeginTransaction();
12             SqlCommand command = new SqlCommand();    
13             command.Transaction = sqlTrans;
14             command.Connection = conn;
15             
16             string sql = null;
17             foreach(string sqlTmp in sqlList)
18             {
19                 sql = sqlTmp;
20                 command.CommandText = sql;
21                 command.ExecuteNonQuery();
22             }
23             
24             // 提交事务(前面执行无误的情况下)
25             sqlTrans.Commit();
26             return true;
27         }
28         catch(Exception ex)
29         {
30             if(sqlTrans != null)
31             {
32                 // 执行出错,事务回滚
33                 sqlTrans.RollBack();
34             }
35             retrun false;
36         }
37     }
38 }

[3].
批量举办SQL,以工作方式

其间,SqlTransaction表示要在 SQL Server 数据库中处理的 Transact-SQL
事务。不可以继续此类。

2.
判断表、存储进程等的存在性

// 判断普通表
IF NOT EXISTS( SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'TableName') AND OBJECTPROPERTY(ID, 'IsTable')=1 )
BEGIN
CREATE TABLE TableName(
    ... ...
)
END

// 判断存储过程
IF exists(select 1 from sysobjects where id=object_id('ProcName') and xtype='P')
    DROP PROC ProcName
GO

// 判断临时表
IF object_id('tempdb..#TAB_Tmp_Name') is not null 
BEGIN
    DROP table #TAB_Tmp_Name
END;
CREATE table #TAB_Tmp_Name(
  ... ...  
);

3

 

相关文章