【SQL server速成之路】索引与数据完整性

2301_79099373

已于 2024-04-12 07:20:21 修改

阅读量658

点赞数 19

分类专栏： 2024年程序员学习文章标签：前端 javascript 前端框架

于 2024-04-12 07:20:20 首次发布

本文链接：https://blog.csdn.net/2301_79099373/article/details/137663682

版权

2024年程序员学习专栏收录该内容

125 篇文章 2 订阅

订阅专栏

索引与数据完整性

索引
- 一.索引的分类
二. 系统表sysindexes
三、索引的创建
四、索引的删除
数据完整性
- 一、数据完整性的分类
二、域完整性的实现
三、实体完整性的实现
四、参照完整性的实现

在这里插入图片描述

索引

=================================================================

在数据库系统中建立索引主要有以下作用：

快速存取数据；
保证数据记录的唯一性；
实现表与表之间的参照完整性；
在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。

注：

Sql server支持在表中任何列（包括计算列）上定义索引；
唯一索引：不会有两行记录的索引键值相同；
不唯一索引：多个行共享同一索引键值；
复合索引：多列组合创建的索引。

一.索引的分类

聚集索引

聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。SQL Server 2012是按B树（BTREE）方式组织聚集索引的，B树方式构建为包含了多个节点的一棵树。顶部的节点构成了索引的开始点，叫做根。每个节点中含有索引列的几个值，一个节点中的每个值又都指向另一个节点或者指向表中的一行，一个节点中的值必须是有序排列的。指向一行的一个节点叫做叶子页。叶子页本身也是相互连接的，一个叶子页有一个指针指向下一组。

在这里插入图片描述

注：表中的顺序和索引顺序一致。

非聚集索引

非聚集索引完全独立于数据行的结构。SQL Server 2012也是按B树组织非聚集索引的，与聚集索引不同之处在于：非聚集索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。

对于非聚集索引，表中的数据行不按非聚集键的次序存储。

在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键，只有在表上创建聚集索引时，表内的行才按特定顺序存储。这些行按聚集索引键顺序存储。如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储。

在这里插入图片描述

注：

一个表中最多只能有一个聚集索引，可以有多个非聚集索引；
创建索引时先创建聚集索引，然后再创建非聚集索引。

二. 系统表sysindexes

系统表sysindexes的主要字段如下表所示。

在这里插入图片描述

用户创建数据库时，系统自动创建系统表，用户创建的每个索引均将在系统表中登记。

三、索引的创建

在xsbook数据库中，经常要对xs、book和jy这3个表查询和更新，为了提高查询和更新速度，可以考虑对3个表建立如下索引：

（1）对于xs表，按借书证号建立主键索引（PRIMARY KEY约束），索引组织方式为聚集索引；按姓名建立非唯一索引，索引组织方式为非聚集索引；

（2）对于book表，按ISBN建立主键索引或者唯一索引，索引组织方式为聚集索引；

（3）对于jy表，按借书证号+ISBN建立唯一索引，索引组织方式为聚集索引。

1．界面方式创建索引

在“对象资源管理器”中展开“数据库xsbook”→展开“表”中的“dbo.xs”→右击其中的“索引”项，在弹出的快捷菜单上选择“新建索引”菜单项的“非聚集索引”。这时，用户可以在弹出的“新建索引”窗口中输入索引名称（索引名在表中必须唯一），如ck_xs，（如果是唯一索引，需要勾选“唯一”复选框）。单击新建索引窗口的“添加”按钮→在弹出选择列窗口（如图所示）中选择要添加的列→添加完毕后，单击“确定”按钮。

在这里插入图片描述

除了使用上面的方法创建索引之外，还可以直接在表设计器窗口创建索引。在表设计器窗口创建索引的方法如下：

（1）右击表名，在弹出的快捷菜单中选择“设计”菜单项。

（2）在“表设计器”窗口中，选择需要创建索引的属性列，右击鼠标，在弹出的快捷菜单中选择“索引/键”菜单项。

2．使用SQL命令创建索引

使用CREATE INDEX语句可以为表创建索引。语法格式：

CREATE [ UNIQUE ] /指定索引是否唯一/

[ CLUSTERED | NONCLUSTERED ] /索引的组织方式/

INDEX <索引名> /索引名称/

ON {[ <数据库名>. [ <架构名> ] . | <架构名>. ] <表名或视图名>}

( <列名> [ ASC | DESC ] [ ,…n ] ) /索引定义的依据/

[ WITH ( <relational_index_option> [ ,…n ] ) ] /索引选项/

[ ON { <分区方案名> (<列名>) /指定分区方案/

| <文件组名> /指定索引文件所在的文件组/

}

]

[ ; ]

其中：

<relational_index_option> ::=

{

PAD_INDEX = { ON | OFF }

| FILLFACTOR = fillfactor

| SORT_IN_TEMPDB = { ON | OFF }

| IGNORE_DUP_KEY = { ON | OFF }

| STATISTICS_NORECOMPUTE = { ON | OFF }

| DROP_EXISTING = { ON | OFF }

| ONLINE = { ON | OFF }

| ALLOW_ROW_LOCKS = { ON | OFF }

| ALLOW_PAGE_LOCKS = { ON | OFF }

| MAXDOP = max_degree_of_parallelism

}

说明：

（1）UNIQUE：表示为表或视图创建唯一索引（即不允许存在索引值相同的两行）。

（2）CLUSTERED | NONCLUSTERED：用于指定创建聚集索引还是非聚集索引，前者表示创建聚集索引，后者表示创建非聚集索引。

（3）<列名>：用于指定建立索引的字段，参数n表示可以为索引指定多个字段。

（4）WITH子句：用于指定定义的索引选项。

【例1】对于jy表，按借书证号+ISBN 创建索引（组合索引）。

/创建简单索引/

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'jy_num_ind ')

DROP INDEX jy.jy_num_ind

CREATE INDEX jy_num_ind

ON jy (借书证号,ISBN)

【例2】根据book表的ISBN列创建唯一聚集索引，因为指定了 CLUSTERED 子句，所以该索引将对磁盘上的数据进行物理排序。

/创建唯一聚集索引/

CREATE UNIQUE CLUSTERED INDEX book_id_ind

ON book(ISBN)

注：在创建索引之前，如果已经创建主键，系统会自动将主键列为聚集索引，如果未删除主键，则无法再创建新的聚集索引。

【例3】根据xs表中借书证号字段创建唯一聚集索引。如果输入了重复键值，将忽略该INSERT或UPDATE语句。

CREATE UNIQUE CLUSTERED INDEX xs_ind

ON xs(借书证号)

WITH IGNORE_DUP_KEY

【例4】创建一个视图，并为该视图创建索引。

/定义视图，如下例子中，由于使用了WITH SCHEMABINDING子句，因此，定义视图时，SELECT子句中表名必须为：架构名名.视图名的形式。/

CREATE VIEW VIEW1 WITH SCHEMABINDING

SELECT 索书号,书名,姓名

FROM dbo.jy, dbo.book, dbo.xs

WHERE jy.ISBN = book.ISBN AND xs.借书证号 = jy.借书证号

/在视图VIEW1上定义索引/

CREATE UNIQUE CLUSTERED INDEX Ind1

ON dbo.VIEW1(索书号 ASC)

注：

只有在视图上定义了WITH SCHEMABINDING ，才可以创建索引；

为视图创建的聚集索引必须是unique索引。

四、索引的删除

1．通过界面方式删除索引

通过界面方式删除索引的主要步骤如下：启动“SQL Server Management Studio”→在“对象资源管理器”中展开数据库“xsbook”→“表”→“dbo.xs”→“索引”，选择其中要删除的索引，单击鼠标右键，在弹出的快捷菜单上选择“删除”菜单项。在打开的“删除对象”窗口单击“确定”按钮即可。

2．通过SQL命令删除索引

语法格式：

DROP INDEX

{ <索引名> ON <表名或视图名> [ ,…n ]

| table_or_view_name.index_name [ ,…n ]

}

注：DROP INDEX语句可以一次删除一个或多个索引；

不适合删除通过定义primary key或unique约束创建的索引；删除通过定义primary key或unique约束创建的索引须通过删除约束实现；

系统表上的索引不能使用drop语句。

数据完整性

====================================================================

一、数据完整性的分类

1．域完整性

域完整性又称为列完整性，指列数据输入的有效性。实现域完整性的方法有：限制类型（通过数据类型）、格式（通过CHECK约束和规则）或可能的取值范围（通过CHECK约束、DEFALUT定义、NOT NULL定义）等。

【例5】对于数据库xsbook的xs表，如果允许读者当前的在借图书量最多为20本，为了对读者当前的在借图书量进行限制，可以在定义xs表时，规定：“0≤借书量≤20”的约束条件达到目的。定义表xs的同时定义借书量字段的约束条件：

USE xsbook

CREATE TABLE xs

(

借书证号 char(8) NOT NULL PRIMARY KEY,

姓名 char(8) NOT NULL,

性别 bit NOT NULL DEFAULT 1,

出生时间 date NOT NULL ,

/如下语句定义字段的同时定义约束条件/

专业 char(12) NOT NULL,

借书量 int CHECK (借书量 >=0 AND 借书量<=20) NOT NULL,

照片 varbinary(MAX) NULL

)

2．实体完整性

实体完整性又称为行的完整性，要求表中有一个主键，其值不能为空且能唯一地标识对应的记录。通过索引、UNIQUE 约束、PRIMARY KEY 约束或 IDENTITY 属性等可实现数据的实体完整性。

例如，对于xsbook数据库中xs表，借书证号作为主键，每一个读者的借书证号能唯一的标识该读者对应的行记录信息，那么在输入数据时，则不能有相同借书证号的行记录，通过对借书证号这一字段建立主键约束可实现表xs的实体完整性。

3．参照完整性

参照完整性又称引用完整性。保证主表数据与从表数据的一致性。SQL Server 2012中，参照完整性的实现是通过定义外键（外码）与主键（主码）之间或外键与唯一键之间的对应关系实现的。

码：即前面所说的关键字，又称为“键”，是能唯一标识表中记录的字段或字段组合。如果一个表有多个码，可选其中一个作为主码（主键），其余的称为后选码。

外键：如果一个表中的一个字段或若干个字段的组合是另一个表的键则称该字段或字段组合为该表的外键。xs和jy表的对应关系如图所示。

二、域完整性的实现

Sql server通过数据类型、check约束、default定义、not null定义实现域完整性。

CHECK约束实际上是字段输入内容的验证规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。对于timestamp和identity两种类型字段不能定义CHECK约束。

（1）通过界面方式创建与删除CHECK约束。

对于xsbook数据库的xs表，要求读者的借书证号必须由6个数字字符构成，并且不能为“000000”。

① 启动“SQL Server Management Studio”→在“对象资源管理器”中展开“数据库”→“xsbook”→“表”→选择“dbo.xs”，右击鼠标选择“设计”菜单项。

② 在打开的“表设计器”窗口中选择“借书证号”属性列，右击鼠标选择“CHECK约束”菜单项。

③ 在打开的 “CHECK约束”窗口（如图所示）中，单击“添加”按钮，添加一个“CHECK约束”。

（2）使用SQL语句在创建表时创建CHECK约束

利用T-SQL命令可以使用两种方式定义约束：作为列的约束或作为表的约束。

语法格式：

CREATE TABLE table_name /指定表名/

( <列名> <数据类型>

{

NOT NULL | NULL /指定为空性/

| [ DEFAULT <默认值表达式> ] /指定默认值/

| [ CONSTRAINT <约束名> ] CHECK ( logical_expression )] /CHECK约束表达式/

}[,…n]

[ CONSTRAINT constraint_name ] CHECK ( logical_expression )][,…n]

)

（3）使用SQL语句在修改表时创建CHECK约束

语法格式：

ALTER TABLE <表名>

ADD [<列的定义>]

[CONSTRAINT <约束名>] CHECK (logical_expression)

（4）使用SQL语句删除CHECK约束

CHECK约束的删除可在对象资源管理器中通过界面删除，读者可以自己试一试，在此介绍如何利用SQL命令删除。

使用ALTER TABLE语句的DROP子句可以删除CHECK约束。语法格式：

ALTER TABLE <表名>

DROP CONSTRAINT <约束名>

三、实体完整性的实现

表中应有一个列或列的组合，其值能唯一标识表中每一行，选择这样的列或列的组合可以作为主键实现实体完整性。通过定义PRIMARY KEY约束创建主键。

PRIMARY KEY约束与UNIQUE约束的主要区别如下：

一个数据表只能创建一个PRIMARY KEY约束，但一个表中可根据需要对不同的列创建若干个UNIQUE约束；
PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL；
一般创建PRIMARY KEY约束时，系统会自动产生索引，索引的默认类型为簇索引。创建UNIQUE约束时，系统会自动产生一个UNIQUE索引，索引的默认类型为非簇索引。

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数前端工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Web前端开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上前端开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024c （备注前端）

TCP协议

TCP 和 UDP 的区别？
TCP 三次握手的过程？
为什么是三次而不是两次、四次？
三次握手过程中可以携带数据么？
说说 TCP 四次挥手的过程
为什么是四次挥手而不是三次？
半连接队列和 SYN Flood 攻击的关系
如何应对 SYN Flood 攻击？
介绍一下 TCP 报文头部的字段
TCP 快速打开的原理(TFO)
说说TCP报文中时间戳的作用？
TCP 的超时重传时间是如何计算的？
TCP 的流量控制
TCP 的拥塞控制
说说 Nagle 算法和延迟确认？
如何理解 TCP 的 keep-alive？

浏览器篇

浏览器缓存?
说一说浏览器的本地存储？各自优劣如何？
说一说从输入URL到页面呈现发生了什么？
谈谈你对重绘和回流的理解
XSS攻击
CSRF攻击
HTTPS为什么让数据传输更安全？
实现事件的防抖和节流？
实现图片懒加载？

一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

什么是三次而不是两次、四次？

三次握手过程中可以携带数据么？
说说 TCP 四次挥手的过程
为什么是四次挥手而不是三次？
半连接队列和 SYN Flood 攻击的关系
如何应对 SYN Flood 攻击？
介绍一下 TCP 报文头部的字段
TCP 快速打开的原理(TFO)
说说TCP报文中时间戳的作用？
TCP 的超时重传时间是如何计算的？
TCP 的流量控制
TCP 的拥塞控制
说说 Nagle 算法和延迟确认？
如何理解 TCP 的 keep-alive？

[外链图片转存中…(img-rHXaEWoS-1712877604101)]

浏览器篇

浏览器缓存?
说一说浏览器的本地存储？各自优劣如何？
说一说从输入URL到页面呈现发生了什么？
谈谈你对重绘和回流的理解
XSS攻击
CSRF攻击
HTTPS为什么让数据传输更安全？
实现事件的防抖和节流？
实现图片懒加载？

[外链图片转存中…(img-LzkNVItw-1712877604102)]

一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
[外链图片转存中…(img-I9QpG5o7-1712877604102)]

2301_79099373

关注

19
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
【SQL server速成之路】索引与数据完整性

在“对象资源管理器”中展开“数据库xsbook”→展开“表”中的“dbo.xs”→右击其中的“索引”项，在弹出的快捷菜单上选择“新建索引”菜单项的“非聚集索引”。通过界面方式删除索引的主要步骤如下：启动“SQL Server Management Studio”→在“对象资源管理器”中展开数据库“xsbook”→“表”→“dbo.xs”→“索引”，选择其中要删除的索引，单击鼠标右键，在弹出的快捷菜单上选择“删除”菜单项。创建UNIQUE约束时，系统会自动产生一个UNIQUE索引，索引的默认类型为非簇索引。
复制链接

扫一扫