以 Sql Server 数据库为例,本篇简单介绍如何使用和查询元数据

本篇我将会介绍元数据中的索引,这些不是元数据中的对象,让我们查询出数据库中使用触发器的信息,Server版本中有什么触发器,本篇简单介绍如何使用和查询元数据,比如SQL Server,以 Sql Server 数据库为例,注意,以 Sql Server 数据库为例,注意

图片 3

背景

在第一篇中我介绍了如何访问元数据,元数据为什么在数据库里面,以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇,我选择了触发器的主题,因为它是一个能提供很好例子的数据库对象,并且在这个对象中能够提出问题和解决问题。

本篇我将会介绍元数据中的索引,不仅仅是因为它们本身很重要,更重要的是它们是很好的元数据类型,比如列或者分布统计,这些不是元数据中的对象。

索引对于任何关系数据库表都是必不可少的。然而,就像吐司上的黄油一样,过度使用它们可能会在数据库中产生问题。有时,可以对表进行过度索引或缺失索引,或者构建重复索引。有时问题是选择一个坏的填充因子,错误地设置ignore_dup_key选项,创建一个永远不会被使用(但必须被维护)的索引,丢失外键上的索引,或者将GUID作为主键的一部分。简而言之,任何频繁使用的数据库系统中的索引都需要定期维护和验证,而目录视图是完成这些工作的最直接的方式之一。

背景

  上一篇中,我介绍了SQL Server
允许访问数据库的元数据,为什么有元数据,如何使用元数据。这一篇中我会介绍如何进一步找到各种有价值的信息。以触发器为例,因为它们往往一起很多问题。

 

简介

  在数据库中,我们除了存储数据外,还存储了大量的元数据。它们主要的作用就是描述数据库怎么建立、配置、以及各种对象的属性等。本篇简单介绍如何使用和查询元数据,如何更有效的管理SQLServer
数据库。

  对一些有经验的数据库开发和管理人员而言,元数据是非常有价值的。下面我会介绍一下简单的原理,然后尽量用代码的方式直接说明,毕竟“talk
is cheap show me the code ”。

在互联网笔试中,常遇到数据库的问题,遂来简单总结,注意,以 Sql Server 数据库为例。

在互联网笔试中,常遇到数据库的问题,遂来简单总结,注意,以 Sql Server 数据库为例。

都有哪些索引可以查到?

让我们通过下面的简单语句来看一下都有哪些索引在你的数据库上,代码如下:

SELECT  convert(CHAR(50),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The Table', i.name AS index_name
FROM sys.indexes AS i
  INNER JOIN sys.tables t
    ON t.object_id=i.object_id
  WHERE is_hypothetical = 0 AND i.index_id <> 0;

结果如下:

图片 1

为什么要去引用sys.tables?这是因为它是确保只获得用户表的最简单方法。我们选择index_id
的values大于0,因为如果不为表创建集群索引,在sys中仍然有一个条目。索引,但它指向的是堆,不代表索引。每个表在sys中都有一行。索引值为0或1的索引。如果该表有一个聚集索引,则有一行数据且index_id值为1;如果该表是一个堆(这只是表示该表没有聚集索引的另一种方式),则会有一行的index_id值为0。此外,无论该表是否有聚集索引,每个非聚集索引都有一行,其index_id值大于1。我们过滤了的索引,这些索引是由数据库引擎优化顾问(DTA)创建的,目的仅仅是测试一个可能的索引是否有效。以防它们积累起来,最好把它们去掉。

如果你过一个多个指定的表,下面的这个查询是更为合理的,需要在上面的例子中增加对象的指定:

AND t.object_id = OBJECT_ID('Production.BillOfMaterials');

 

那么如何找到触发器的数据?

*  以sys.system_views*is表开始。让我们查询出数据库中使用触发器的信息。可以告知你当前SQL
Server版本中有什么触发器。

SELECT schema_name(schema_ID)+'.'+ name

  FROM sys.system_views WHERE name LIKE '%trigger%'

 ----------------------------------------

sys.dm_exec_trigger_stats              

sys.server_trigger_events              

sys.server_triggers                    

sys.trigger_event_types                

sys.trigger_events                     

sys.triggers                           



(6 row(s) affected)

  其中sys.triggers看起来信息很多,它又包含什么列?下面这个查询很容易查到:

 SELECT Thecol.name+ ' '+ Type_name(TheCol.system_type_id)

  + CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.system_views AS TheView

  INNER JOIN sys.system_columns AS TheCol

    ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

结果如下:

 Column_Information

----------------------------------------

name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

因此我们多这个信息有了更好的理解,有了一个目录的目录。这个概念有点让人头晕,但是另一方面,它也是相当简单的。我们能够查出元数据,再找个查询中,需要做的就是改变这个单词‘triggers’来查找你想要的视图名称。.

在2012及其以后版本,可以使用一个新的表值函数极大地简化上述查询,并可以避免各种连接。在下面的查询中,我们将查找sys.triggers
视图

中的列。可以使用相同的查询通过更改字符串中的对象名称来获取任何视图的定义。

 SELECT name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f

  ORDER BY column_ordinal;

查询结果如下:

 Column_Information

----------------------------------------

name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

sys.dm_exec_describe_first_result_set函数的最大优势在于你能看到任何结果的列,不仅仅是表和视图、存储过程或者贬值函数。

为了查出任何列的信息,你可以使用稍微修改的版本,只需要改变代码中的字符串’sys.triggers’即可,如下:

 Declare @TheParamater nvarchar(255)

Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM ' + @TheParamater

SELECT

  name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( @TheParamater, NULL, 0) AS f

  ORDER BY column_ordinal;

什么是动态在线目录?

  每一个关系型数据库系统,比如SQL Server
一定要提供关于它的结构的信息,这些信息往往需要通过sql语法来查询。通常这些信息被保存在指定数据表的结构中。这意味着数据库中有两种不同的表:一是用户自定义的表和系统表或者视图(包含元数据)。从SQL
Server 2005开始,只有视图可以查询了,不能直接看到数据表了。

 图片 2

系统视图

这种系统表或者视图的结合通常参考关系型数据库理论的文献叫做作为系统目录或者数据字典。

在数据库内部,有一些系统表一直追踪数据库中发生的每一件事情。系统表存储像表、活动、列、索引等事情。这些完全符合Edgar
Codd
的关系型数据库试试的十三条准则直译。这个准则就是定义动态在线目录,它就是“关于数据的数据”,也叫作元数据。

 Edgar Codd  准则4, 描述如下:

‘The database description is represented at the logical level in the
same way as ordinary data, so that authorized users can apply the same
relational language to its interrogation as they apply to the regular
data.’

翻译:像普通数据一样,在逻辑层的数据表达了对数据库的描述,以便于授权用户能应用相同的SQL语言来查询元数据,就如同查询常规数据一样。

在SQL
Server中,可以通过系统视图或者架构视图直接访问动态在线目录,方便用户更为快捷的开发和管理数据库。

数据库

数据库系统,Database
System,由数据库和数据库管理系统组成。
数据库,DataBase
,是计算机应用系统中的一种专门管理数据资源的系统,按照数据结构来组织、存储和管理数据的仓库。数据表是最基本的数据库对象,是存储数据的逻辑单元。

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

数据模型,通常由数据结构、数据操作和完整性约束三部分组成。

数据库

数据库系统,Database
System,由数据库和数据库管理系统组成。
数据库,DataBase
,是计算机应用系统中的一种专门管理数据资源的系统,按照数据结构来组织、存储和管理数据的仓库。数据表是最基本的数据库对象,是存储数据的逻辑单元。

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

数据模型,通常由数据结构、数据操作和完整性约束三部分组成。

每个表中有多少个索引,并展示他们的名字

前面的表并不特别有用,因为无法一眼看到每个表有多少索引,以及它们是什么。下面这个语句可以实现:

SELECT  convert(CHAR(20),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The_Table',
sum(CASE WHEN i.object_ID IS NULL THEN 0 ELSE 1 END) AS The_Count,
coalesce(stuff(( 
     SELECT ', '+i2.name
       FROM sys.indexes i2
       WHERE t.object_ID = i2.object_ID
       ORDER BY i2.name
     FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,''),'') AS Index_List
  FROM sys.tables AS t
  LEFT OUTER JOIN sys.indexes i
    ON t.object_id=i.object_id
      AND is_hypothetical = 0 AND i.index_id > 0 
GROUP BY t.Object_ID;

 

我在老的测试数据库上执行这个测试,对象名称比较短。

The_Table            The_Count   Index_List
-------------------- ----------- --------------------------------------------------
dbo.publishers       1           UPKCL_pubind
dbo.titles           2           titleind, UPKCL_titleidind
dbo.titleauthor      3           auidind, titleidind, UPKCL_taind
dbo.stores           1           UPK_storeid
dbo.sales            2           titleidind, UPKCL_sales
dbo.roysched         1           titleidind
dbo.discounts        0           
dbo.jobs             1           PK__jobs__6E32B6A51A14E395
dbo.pub_info         1           UPKCL_pubinfo
dbo.employee         2           employee_ind, PK_emp_id
dbo.authors          2           aunmind, UPKCL_auidind

(11 row(s) affected)

但是当然一个触发器是首先是一个对象,因此一定在sys.objects?

  在我们使用sys.triggers的信息之前,需要来重复一遍,所有的数据库对象都存在于sys.objects中,在SQL
Server 中的对象包括以下:聚合的CLR函数,check
约束,SQL标量函数,CLR标量函数,CLR表值函数,SQL内联表值函数,内部表,SQL存储过程,CLR存储过程,计划指南,主键约束,老式规则,复制过滤程序,系统基础表,同义词,序列对象,服务队列,CLR
DML
触发器,SQL表值函数,表类型,用户自定义表,唯一约束,视图和扩展存储过程等。

  触发器是对象所以基础信息一定保存在sys.objects。不走运的是,有时我们需要额外的信息,这些信息可以通过目录视图查询。这些额外数据有是什么呢?

 

  修改我们使用过的查询,来查询sys.triggers的列,这次我们会看到额外信息。这些额外列是来自于sys.objects。

 SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

------------------------------ ----------------------

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

以上这些让我们知道在sys.triggers的额外信息,但是因为它始终是表的子对象,所以有些不相关信息是不会展示在这些指定的视图或者sys.triggers中的。现在就要带大家去继续找找这些信息。

如何获得以上信息?

因为我们不能直接访问,需要使用视图和函数来看这些信息。只能看到你权限内的数据。有更好的方法在用户数据库中使用数据定义语言(DDL),这些DDL语句包括CREATE,
DROP, ALTER, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种方法可以使用DDL来修改视图中的任何信息,即使并不总是显而易见的。

关系型数据库使用动态的系统视图中的数据描述数据库,但是目前还有没有标准化。但是有一个包含在每个数据库内的架构可以读取这些信息:就是Information
Schema

不走运的是,这个架构不足以提供足够信息,这意味着我们需要使用SQL Server
系统数据库的视图和函数来补充信息。接下来需要解释一些术语和技术,我会尽可能少的细节足以让大家轻松地理解这些示例

如图所示,如何访问元数据,及其接口

 图片 3

 

* *

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   

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   

查找没有聚集索引的表

关于索引,您可以找到很多有趣的东西。例如,这里有一种快速查找表的方法,无需使用聚集索引(堆)

-- 展示所有没有聚集索引的表名称
SELECT  object_schema_name(sys.tables.object_id)+'.'
    +object_name(sys.tables.object_id) AS 'Heaps'
      FROM sys.indexes /* see whether the table is a heap */
      INNER JOIN sys.tables ON sys.tables.object_ID=sys.indexes.object_ID
      WHERE sys.indexes.type = 0;

触发器的问题

  触发器是有用的,但是因为它们在SSMS对象资源管理器窗格中不是可见的,所以一般用来提醒错误。触发器有时候会有些微妙的地方让其出问题,比如,当导入过程中禁用了触发器,并且由于某些原因他们没有重启。

下面是一个关于触发器的简要提醒:

  触发器可以在视图,表或者服务器上,任何这些对象上都可以有超过1个触发器。普通的DML触发器能被定义来执行替代一些数据修改(Insert,Update或者Delete)或者在数据修改之后执行。每一个触发器与只与一个对象管理。DDL触发器与数据库关联或者被定义在服务器级别,这类触发器一般在Create,Alter或者Drop这类SQL语句执行后触发。

  像DML触发器一样,可以有多个DDL触发器被创建在同一个T-SQL语句上。一个DDL触发器和语句触发它的语句在同一个事务中运行,所以除了Alter
DATABASE之外都可以被回滚。DDL触发器运行在T-SQL语句执行完毕后,也就是不能作为Instead
OF触发器使用。

  两种触发器都与事件相关,在DML触发器中,包含INSERT, UPDATE,
和DELETE,然而很多事件都可以与DDL触发器关联,稍后我们将了解。

系统视图

 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:

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

 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:

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