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支持在一个操作内又也多个变量赋值,但是呢变量赋值和数据检索不能够同时开展,参考
两岸的界别;
  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
 
注意别下面2单insert语句的区分,第一种Product格式,values中须吃起相应的价值,其中日期系统默认1900-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:所有属性数据必须依赖主键;
  · 第三范式 3NF:数据库表中不克包含已于旁表中包含的非主键信息;
 关系项目数据库三良完整性:
  · 实体完整性:主键约束
primary key,唯一且非空;
  · 参照完整性:引用完整性,外键约束
foreign key 等干约束;
  · 用户从定义完整性:域完整性,字段类型等;
  c. 分区表
 按照数据水平方式分区,将数据分布于数据库的差不多单不等的文书组中:
  - 改善大型表以及有各种访问模式之表的可伸缩性和可管理性;
  - 对于多CPU系统,支持彼此方式对表操作;
 分区函数~分区方案:

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

 一个分区方案不得不用一个分区函数,一个分区函数可以为多单分区方案共用。
  d. 文件组 
 在数据库中对文本进行分组的同样栽管理机制,一个文本不能够是大半只公文组的成员。文件组只能分包数据文件,事务日志文件不能够是文本组的如出一辙部分。使用文件组可以凝集用户指向文本的依靠,通过文件组间接管理文件,可以教一样文件组内的文件分布于不同之硬盘中,能提高IO性能。
 具体地只是参考
文件和文件组。
  e. 标识符
 每一行数必须还出一个唯一的可分别的属性作为标识符。
  · identity:本地(表内)唯一,使用方式
identity(初始种子值,增量);
     select @@identity:查看新插入行数据的标识符(的序号)
 
     select $identity from 表名:引用(显示)表的唯一标识符列
 
  ·
uniqueidentifier:全局唯一,应用rowguidcol属性作为标识符指示新列为guid列,默认定义使用newid或newsequentialid()函数生成全局唯一值;同理,使用$rowguid引用唯一标识符列。
     ID uniqueidentifier default newsequentialid() rowguidcol   

 f. 主键 PK ~ 外键 FK 

 主键:保证全局唯一性;
 外键:建立及提高两独说明数据里链接的一律列或多列,强制引用完整性,可以有效防范误删;
  主键约束 ~ 外键约束 ~
唯一约束
  – 主键约束
  主键约束用于落实实体完整性,每个表底主键有且不得不发出一个,主键列非可知包含null值。声明并主键采用第2、3种植办法。创建PK约束,具体参见大话数据库或
其三种植方式创建主键约束;
  系统默认生成的主键约束名吧:PK_表名_列串号
  – 外键约束
  外键约束用于落实参照完整性,一个表A:foreign key指为另外一个表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约束下第2、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
  并集,用于整合2单以上的结果集,默认去还,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,连接表中享有数据的笛卡尔积,结果集的数量行数 =
第一个表中入查询条件的数目行数 *
第二单说明中入查询条件的多寡行数。cross
join后加条件只能用where,不克因此on。  
  · 自连接
  连接要字的少限还是同一个表明,将自身表的一个镜像当作另一个表来对待。自连接可以以需要简单坏询问的言语综合化一漫漫告句一次实施成功。参考示例:于连接查询,也不过参见大话数据库中有关从连接的例证。
  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%。
 · 非聚集索引
 
索引的顺序和数据表的物理顺序不同,单行检索快。一个表明最多249只无聚集索引。非聚集索引的叶级是找引页。索引页中的行标识符(或聚集键)指向数据页中的记录(或说明底聚集索引,再通过聚集索引检索数据),行标识符由文件ID、页号和行ID组成,并且是唯一的。数据堆通过采用索引分配图(IAM)页来保安。
特征:
 · 唯一性索引
 保证索引列中的成套数目是绝无仅有的。只能当可包实体完整性的列上创建唯一性索引。
 · 复合索引
 
一个目创建于2单或多只列上。不可知跨表建立复合列。注意列的排顺序。复合索引好加强查询性能,减少在一个表中所创的目数量。复合索引键中最多好组成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.
游标

游标是同样栽就同平等组数据被有一个笔录进行交互的法,是针对(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语句和可选控制流语句的
预编译
集合,经过第一糟编译后再也调用不必再编译。存储过程要用来返回数据。
.vs 函数

  • 函数不能够修改数据库表数据,存储过程得;
  • 储存过程得 execute
    执行,函数调用更灵活;

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

  • 容标准组件式编程,可移植性、可复用;
  • 大概容易用,预编译、执行进度快、效率高;
  • 精益求精安全体制、保证数据的安全;
  • 节约网络流量、降低网络负载;

分类

  • 系存储过程:存储在 master 数据库被,以
    “sp_”为前缀,用于自系统表中获取信息。
  • 用户从定义存储过程:T-SQL存储过程、CLR存储过程、临时存储过程。不克以CLR存储过程创建为即存储过程。

创建

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
触发器:响应数据操作语言事件,将触发器和点它的说话作为可在触发器内回滚的单个事务;常用、性能开销小,可以兑现相互之间关表数据的级联更改、评估数据修改前后表的状态。
ζ  AFTER 触发器:在 IUD
操作、INSTEAD OF 触发器和约束处理下被鼓舞;推荐还只能当表上指定; 
ζ  INSTEAD OF
触发器:在约处理之前被激起(执行预处理上约束操作),指定执行DML触发器以代表通常的接触动作,优先级高于触发语句之操作;
流淌:每个表或试图对每个 DML
触发操作 IUD,有还只能发出一个应和的 INSTEAD OF 触发器,可以出多独照应的
AFTER 触发器。
ζ  CLR
触发器:执行于托管代码中之措施;
·  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 全文索引基于的表名[索引包含的列列表]

原理:两步走
针对文件进行分词,并也各级一个面世的只有词记录一个目录项为保存出现过该单词的有着记录的音信。全文索引引擎对加入到全文索引的排列被的内容以字/词建立目录条目,即先定义一个词库,然后以篇章中寻找每个词条(term)出现的效率与位置,把这些频率位置信息以词库顺序归纳,完成对文件建立一个为词库为目录的目录。
· 创建基于关键字查询的目录
     如何对文件进行分词:二首位分词法、最要命匹配法和统计办法
     建立目录的数据结构:采用倒排索引的布局
· 在目录中搜索一定
   全文谓词:在
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数据库的同组数据令和一个数据库连接。无法持续此类。

图片 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

 

相关文章