资源描述
数据库系统概论An Introduction to Database System第5章 SQL Server 2005表的创建与操作 第1页/共89页教学目标： 了解SQL Server 2005表的基本知识； 掌握表的创建、修改和删除操作； 掌握数据的完整性的实现方法； 熟练掌握记录的插入、删除和修改操作； 了解索引的基本知识，掌握索引的创建和删除操作 第2页/共89页第5章SQL Server 2005数据表的创建和管理 5.1 SQL Server 2005表的基本知识 5.2创建表 5.3修改表 5.4删除表 5.5插入记录 5.6修改记录 5.7删除记录 5.8索引的基本操作 5.9完整性实现第3页/共89页5.1 SQL Server 2005表的基本知识 表的基本概念 表的类型 表的数据类型 表的设计第4页/共89页表的基本概念 表:数据库对象，用于存储实体集和实体间联系的数据 第5页/共89页表的基本概念 例如：教务管理数据库EDUC中的学生Student表， 第6页/共89页表的类型 SQL Server 2005除了提供了用户定义的标准表外，还提供了一些特殊用途的表 分区表:当表很大时，可以水平地把数据分割成一些单元，放在同一个数据库的多个文件组中。用户可以通过分区快速地访问和管理数据的某部分子集而不是整个数据表，从而便于管理大表和索引。文件组文件组文件组分区表第7页/共89页表的类型 临时表：局部临时表和全局临时表。 局部临时表只是对一个数据库实例的一次连接中的创建者是可见的。在用户断开数据库的连接时，局部临时表就被删除。 全局临时表创建后对所有的用户和连接都是可见的，并且只有所有的用户都断开临时表相关的表时，全局临时表才会被删除。第8页/共89页表的类型 系统表：系统表用来保存一些服务器配置信息数据，用户不能直接查看和修改系统表，只有通过专门的管理员连接才能查看和修改。不同版本的数据库系统的系统表一般不同，在升级数据库系统时，一些应用系统表的应用可能需要重新改写。第9页/共89页表的数据类型数据类型数据类型系统数据类型系统数据类型应用说明应用说明二进制二进制image图像、视频、音乐图像、视频、音乐Binary(n)标记或标记组合数据标记或标记组合数据varbinary(n)同上（变长）同上（变长）精确精确数字数字精确整数精确整数bigint长整数长整数-263263-1int整数整数-231231-1smallint短整数短整数-215215-1tinyint更小的整数更小的整数0255精确小数精确小数Decimal(p,s) 小数，小数，p: 最大数字位数最大数字位数s: 最大小数位最大小数位数数numeric(p,s) 同上同上近似数字近似数字float(n)1.79E3081.79E308real3.40E383.40E38第10页/共89页表的数据类型字符字符char(n)定长字符型定长字符型varchar(n)变长字符型变长字符型text变长文本型，存储字符长度大于变长文本型，存储字符长度大于8000的的变长字符变长字符Unicodenchar(n)unicode字符（双倍空间）字符（双倍空间）nvarchar(n)unicode字符（双倍空间）字符（双倍空间）ntextunicode字符（双倍空间）字符（双倍空间）日期和时间日期和时间Datetime1753-1-19999-12-31（12:00:00）smalldatetime1900-1-12079-6-6货币货币Money-263263-1（保留小数点后四位）（保留小数点后四位）smallmoney-231231-1（保留小数点后四位）（保留小数点后四位）第11页/共89页表的数据类型特殊特殊bit0/1，判定真或假，判定真或假Timestamp自动生成的惟一的二进制数，修改该行自动生成的惟一的二进制数，修改该行时随之修改，反应修改记录的时间时随之修改，反应修改记录的时间uniqueidentifier全局惟一标识（全局惟一标识（GUID），十六进制数），十六进制数字，由网卡字，由网卡/处理器处理器ID以及时间信息以及时间信息产生，用法同上产生，用法同上用户自定义用户自定义用户自行命名用户自行命名用户可创建自定义的数据类型用户可创建自定义的数据类型第12页/共89页表的设计一定要先设计好，再动手创建设计表时需要确定如下内容： 表中需要的列以及每一列的类型（必要时还要有长度） 列是否可以为空 是否需要在列上使用约束、默认值和规则 需要使用什么样的索引 哪些列作为主键第13页/共89页5.2创建表 使用SSMS创建表 使用T-SQL语句创建表第14页/共89页使用SSMS创建表创建表有两种方法：使用SSMS；使用T-SQL语言。 例1：在教务管理中的选课数据库（EDUC）中创建学生表Student, 课程表Course, 选课表SC。请看演示使用SSMS创建表第15页/共89页使用T-SQL语句创建表例：在教务管理中的选课数据库（EDUC）中创建学生表Student, 课程表Course, 选课表SC。Student(SID,Sname,Sex,Birthday,Specialty) PK:SIDCourse(CID, Cname,Credit）PK:CIDSC(SID, CID,Grade) PK:SID, CID FK:SID和CID第16页/共89页使用T-SQL语句创建表CREATE TABLE表名（列名1 数据类型 列级完整性约束， 列名 2 数据类型 列级完整性约束， 列名n 类型 约束，表级完整性约束，）；第17页/共89页使用T-SQL语句创建表约束：实现表的完整性DEFAULT 常量表达式：默认值约束。NULL/NOT NULL：空值/非空值约束。UNIQUE：单值约束。PRIMARY KEY：主键约束，等价非空、单值。REFERENCES 父表名 (主键)：外键约束。CHECK (逻辑表达式)：检查约束。第18页/共89页使用T-SQL语句创建表 例：在图书管理系统中的数据库（Library）中，创建读者表（Reader）, 读者类型表（ReaderType），图书表（Book）和借阅表（Borrow）。第19页/共89页使用T-SQL语句创建表ReaderType(TypeID, Typename,LimitNum, LimitDays)PK: TypeIDReader (RID,Rname,TypeID,Lendnum)PK：RID FK：TypeIDBook (BID,Bname,Author, PubComp, PubDate,Price)PK：BIDBorrow (RID, BID,LendDate, ReturnDate) PK：RID, BID, LendDate FK：RID和BID第20页/共89页使用T-SQL语句创建表创建读者类型表ReaderTypeCREATE TABLE ReaderType(TypeID int NOT NULL primary key,-类型编号,主键Typename char(8) NULL,-类型名称LimitNum int NULL,-限借数量LimitDays int NULL -借阅期限)第21页/共89页使用T-SQL语句创建表创建读者表ReaderUSE LibraryGOCREATE TABLE Reader(RID char(10) NOT NULL PRIMARY KEY,-读者编号，主键Rname char(8) NULL,-读者姓名TypeID int NULL,-读者类型Lendnum int NULL ,-已借数量FOREIGN KEY(TypeID) REFERENCES ReaderType(TypeID) ON DELETE NO ACTION, -外键，不级联删除)第22页/共89页使用T-SQL语句创建表创建图书表BookUSE LibraryGOCREATE TABLE Book(BID char(9) PRIMARY KEY,-图书编号，主键Bname varchar(42) NULL,-图书书名Author varchar(20) NULL,-作者PubComp varchar(28) NULL,-出版社PubDate datetime NULL,-出版日期Price decimal(7,2) NULL CHECK (Price0)-定价，检查约束)第23页/共89页使用T-SQL语句创建表创建图书借阅表Borrow (RID, BID,LendDate, ReturnDate)USE LibraryGOCREATE TABLE Borrow(RID char(10) NOT NULL -读者编号外键FOREIGN KEY REFERENCES Reader(RID) ON DELETE CASCADE, /*删除主表记录时不级联删除子表相应记录*/BID char(15) NOT NULL -图书编号外键FOREIGN KEY REFERENCES Book(BID) ON DELETE NO ACTION, /*删除主表记录时级联删除子表相应记录*/LendDate datetime NOT NULL DEFAULT(getdate(),/*借期，默认值为当前日期*/ReturnDate datetime NULL,-还期primary key(RID,BID,LendDate) -表级约束，主键第24页/共89页5.3修改表 使用SSMS修改表 使用T-SQL语句修改表第25页/共89页使用SSMS修改表请看演示SSMS修改表第26页/共89页使用T-SQL语句修改表格式：ALTER table 表名(ALTER COLUMN 列名 列定义，ADD 列名1 类型 约束，DROP 列名）*列定义包括列的数据类型和完整性约束第27页/共89页使用T-SQL语句修改表修改属性例：把表Book 中PubComp 的类型varchar(28)改为varchar(30)。USE LibraryGOALTER TABLE Book ALTER COLUMN PubComp varchar(30) NOT NULLGO第28页/共89页使用T-SQL语句修改表添加或删除列例：为表Book添加ISBN列。国际标准书号由ISBN冠头，后接以下四段10位数字，每两部分之间以水平线或斜线隔开。如：ISBN 7-115-08612-5USE LibraryGOALTER TABLE Book ADD ISBN varchar(13) NULLGO第29页/共89页使用T-SQL语句修改表例：为表Reader添加邮件地址。USE LibraryGOALTER TABLE Reader ADD E-mail varchar(50) NULL CHECK(E-mail like %)GO例6：为表Reader删除邮件地址。USE LibraryGOALTER TABLE Reader DROP COLUMN E-mail GO说明：必须先删除其上的约束。第30页/共89页使用T-SQL语句修改表例：为表Reader添加邮件地址。USE LibraryGOALTER TABLE Reader ADD E-mail varchar(50) NULL CHECK(E-mail like %)GO第31页/共89页使用T-SQL语句修改表添加或删除约束例：为表Borrow添加主键约束（假设还没有创建）。USE LibraryGOALTER TABLE Borrow ADD PRIMARY KEY(RID,BID,LendDate) GO第32页/共89页使用T-SQL语句修改表例：为表Reader删除邮件地址。USE LibraryGOALTER TABLE Reader DROP COLUMN E-mail GO说明：必须先删除其上的约束。第33页/共89页使用T-SQL语句修改表例：为表Borrow删除主键约束。USE LibraryGOALTER TABLE Borrow DROP PRIMARY KEY (RID,BID,LendDate)GO第34页/共89页5.4删除表 使用SSMS删除表 使用T-SQL语句删除表第35页/共89页使用SSMS使用ssms删除表请看演示第36页/共89页使用T-SQL语句删除表 例：先随便在数据库Library中建一个表Test，然后删除。USE LibraryGODROP TABLE Test第37页/共89页5.5插入记录 使用SSMS添加记录 使用T-SQL语句插入记录第38页/共89页使用SSMS添加记录使用SSMS添加记录请看演示第39页/共89页使用T-SQL语句插入记录INSERT INTO（表名|视图名）列名表 VALUES（常量表）第40页/共89页使用T-SQL语句插入记录 插入一行所有列的值例：USE LibraryGOINSERT into Reader VALUES(2005216001,赵成刚,3,2,)GO第41页/共89页使用T-SQL语句插入记录 插入一行的部分列例12：USE LibraryGOINSERT Reader(RID,Rname,TypeID)VALUES(2004060003,李亚茜,3)GO第42页/共89页5.6修改记录 使用SSMS修改记录 使用T-SQL语句修改记录第43页/共89页使用SSMS修改记录使用SSMS修改记录请看演示第44页/共89页使用T-SQL语句修改记录UPDATE 表名SET 列名1表达式, 列名n表达式where 逻辑表达式第45页/共89页使用T-SQL语句修改记录例：把读者类型表ReaderType中学生的限借数量5本增加2本。USE LibraryGOUPDATE ReaderTypeSET LimitNum=LimitNum +2WHERE Typename=学生GO第46页/共89页使用T-SQL语句修改记录例：计算读者中的已借数量。USE LibraryGOUPDATE ReaderSET Lendnum =( SELECT COUNT(*) 从借阅表中统计出每个读者借书的册数 FROM Borrow WHERE ReturnDate IS NULL AND Reader.RID=Borrow.RID)GO第47页/共89页5.7删除记录 使用SSMS删除记录 使用T-SQL语句删除记录第48页/共89页使用SSMS删除记录 使用SSMS删除记录 请看演示第49页/共89页使用T-SQL语句删除记录DELETE 表名WHERE 逻辑表达式第50页/共89页使用T-SQL语句删除记录例：删除Borrow表中RID为2005216001的读者的借书记录USE LibraryGODELETE ReaderWHERE RID=2005216001GO第51页/共89页使用T-SQL语句删除记录例：删除test表中的所有记录USE LibraryGODELETE test第52页/共89页5.8索引的基本操作 索引的基本知识 使用SSMS创建索引 使用T-SQL语句创建索引 使用SSMS删除索引 使用T-SQL语句删除索引 维护索引第53页/共89页索引的基本知识 作用 与书的索引类似，数据库中的索引可以使用户快速地找到表中或者视图中的信息。一方面用户可以通过合理地创建索引大大提高数据库的查找速度，另一方面索引也可以保证列的惟一性，从而确保表中数据的完整性。第54页/共89页索引的基本知识 索引可以创建在任意表和视图的列字段上，索引中包含键值，这些键值存储在一种数据结构（B-树）中，通过键值可以快速地找到与键值相关的数据记录。第55页/共89页索引的基本知识 索引的类型 聚集索引：根据索引的键值，排序表中的数据并保存。所以每个表只能有一个聚集索引 。 汉语字典的正文本身就是一个聚集索引 。第56页/共89页索引的基本知识 索引的类型 非聚集索引：索引的键值包含指向表中记录存储位置的指针，不对表中数据排序，只对键值排序。 由于非聚集索引的表没有按顺序进行排序，所以查找速度明显低于带聚集索引的表。第57页/共89页索引的基本知识 索引的类型 惟一索引：保证索引中不含有相同的键值，聚集索引和非聚集索引都可以是惟一索引。第58页/共89页索引的基本知识 索引的类型 包含列的索引：一种非聚集索引，其中包含一些非键值的列，这些列对键只有辅助作用。 全文（full-text）索引：上Microsoft全文引擎（full-text engine）创建并管理的一种基开符号的函数（token-based functional）索引，支持快速的字符串中单词的查找。 XML索引：XML数据列中的XML二进制大对象（BLOBs）。第59页/共89页索引的基本知识 创建原则及注意事项 ：权衡利弊 是否适合创建索引？创建什么类型索引？ 经常被查询搜索的列，如经常在where子句中出现的列。 在ORDER BY子句使用的列。 外键或主键列。 值惟一的列。 在查询中很少被引用的列。 包含太多重复值的列。 数据类型为bit、text、image 等的列不能建立索引。第60页/共89页索引的基本知识什么情况下创建聚集索引 常用的运算符是Between、=、和=，这些查询代表的是范围查询。因为使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行物理相邻。返回大型结果集的查询语句。使用JOIN子句的查询。使用Order by或Group by子句的查询。在Order by或Group by子句中指定的列的索引，可以使数据库引擎不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。第61页/共89页索引的基本知识那么如何选择聚集索引的列？ 惟一值或包含许多不重复值的列。 按顺序被访问的列。 值惟一的标识列。 查询结果中经常需要查询的列。第62页/共89页索引的基本知识什么情况下创建非聚集索引 数据量大、更新操作少。 决策支持系统应用程序和主要包含只读数据的表。在创建非聚集索引后， 查询优化器具有更多可供选择的索引用来确定最快的访问方法。数据的低更新特 征意味着索引维护不会降低性能。 联机事务处理应用程序和包含大量更新表的数据库应避免使用过多的非 聚集索引。 窄索引，即列越少越好。第63页/共89页索引的基本知识下述情况适合选择非聚集索引 覆盖查询的列 大量非重复值的列第64页/共89页使用SSMS创建索引使用SSMS创建索引。请看演示第65页/共89页使用T-SQL语句创建索引CREATEUNIQUECLUSTERED|NONCLUSTERED INDEX 索引名ON 表名（列名ASC|DESC,n）WITHPAD_INDEX,FILLACTOR=fillfactor,DROP_EXISTING第66页/共89页使用T-SQL语句创建索引 例：在EDUC数据库中为Student表创建一个唯一性聚集索引Studentindex2，索引关键字为SID，升序排列，填充因子是50（执行该例时先将SID的主键移去）USE EDUCGOCREATE UNIQUE CLUSTERED INDEX Studentindex2ON Student (SID ASC)WITHFILLFACTOR=50第67页/共89页使用SSMS删除索引使用SSMS删除索引。请看演示第68页/共89页使用T-SQL删除索引DROP INDEX 表名.索引名例：删除表Student的索引studentindexUSE EDUCGODROP INDEX Studen.studentindex第69页/共89页维护索引为什么维护索引? 在数据库增长、页拆分、然后最终删除数据时，会产生碎片 。索引碎片的对数据库有很大的危害。第一、碎片是浪费的空间。 第二、碎片很容易带来麻烦记录散布在各处会在检索数据时带来额外的开销。 第70页/共89页维护索引检测碎片可以查看碎片的情况 ：sys.dm_db_index_physical_stats(database_id|NULL,object_id|NULL,index_id|NULL|0,partition_number|NULL,mode|NULL|DEFAULT)第71页/共89页维护索引 例20：-定义两个临时变量DECLAREdb_id SMALLINT;DECLAREobject_id INT;-在这里更改临时变量的值，更改数据库和数据对象的名称SETdb_id=DB_ID(Ndb_student);第72页/共89页维护索引-检测数据库和数据对象是否存在IFdb_id IS NULLBEGIN;PRINT N数据库名称不对或指定的数据库不存在;END;ELSE IFobject_id IS NULLBEGIN;PRINT N数据对象名称不对或指定的数据对象不存在;END;ELSE-调用系统函数检测碎片信息BEGIN;SELECT*FROM sys.dm_db_index_physical_stats(db_id,object_id,NULL,NULL,LIMITED);END;GO第73页/共89页维护索引分析检测结果： 【avg_fragmentation_in_percent】单元格：逻辑碎片的百分比。 【fragment_count】单元格：索引中的碎片（物理上连续的叶页）数量。 【avg_fragment_size_in_pages】单元格：索引中一个碎片的平均页数。 第74页/共89页维护索引上述信息中，【avg_fragmentation_in_percent】单元格的信息可以用来决定下一步的处理方法。处理索引碎片的方法有： 【avg_fragmentation_in_percent】单元格显示的信息如果30%，推荐采用联机重建索引。第75页/共89页维护索引例：对EDUC数据库中的i_t_i_studentno索引重组。-打开数据库USEEDUCGO-重组索引ALTER INDEXi_t_i_studentnoON dbo.t_index REORGANIZE WITH(LOB_COMPACTION=ON)GO第76页/共89页维护索引例：对EDUC数据库中的i_t_i_studentno索引重建。-使用数据库USEdb_studentGO-使用Alter Index重建索引ALTER INDEX i_t_i_studentno ON dbo.t_index REBUILD WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,SORT_IN_TEMPDB=OFF,ONLINE=OFF)GO第77页/共89页5.9完整性实现 数据完整性分类 实体完整性 域完整性 引用完整性第78页/共89页数据完整性分类 约束中真正的内涵是什么 数据完整性分在数据库管理系统中，保证数据库中的数据完整性是非常重要的。所谓数据完整性，就是指存储在数据库中数据的一致性和正确性。约束定义关于列中允许值的规则，是强制完整性的标准机制。使用约束优先于使用触发器、规则和默认值。 第79页/共89页数据完整性分类 实体完整性简单的说，就是将表中的每一行看作一个实体。实体完整性要求表的标示符列或主键的完整性。可以通过建立唯一索引、PRIMARY KEY约束、UNIQUE约束，以及列的IDENTITY属性来实施实体完整性。 第80页/共89页实体完整性例：EDUC 数据库中student表的SID为主码Create Table student(SID char（10）primary key，。)或者使用表级主码约束Create Table student(SID char（10），。CONSTRAINT student_pk PRIMARY KEY (SID),。)第81页/共89页域完整性 域完整性是指给定列的输入有效性。要求表中指定列的数据具有正确的数据类型、格式和有效的数据范围。强制域有效性的方法有：限制类型（通过数据类型）、格式（通过 CHECK 约束和规则）或可能值的范围。域完整性通过 FOREIGN KEY 约束、CHECK 约束、DEFAULT 定义、NOT NULL 定义和规则来实现。 第82页/共89页域完整性例：EDUC 数据库中student表的sex设置DEFAULT约束Create Table student（。Sex char(2) default 男，。）第83页/共89页域完整性例：我们使用下面的语句在student表给SID增加约束只能前两位取值字母A-Z，后8位取值数字0-9。Create table student（。CONSTRAINT student_SID_CK check (SID likeA-ZA-Z0-9 0-9 0-9 0-9 0-9 0-9 0-9 0-9),。）第84页/共89页域完整性例：student表给sname增加unqie约束Create table student（。Sname char(10) unique,。）第85页/共89页引用完整性 引用完整性又称参照完整性。引用完整性维持被参照表和参照表之间的数据一致性，他通过主键（PRIMARY KEY）约束和外键（FOREIGN KEY）约束来实现。引用完整性确保键值在所有表中一致。 第86页/共89页引用完整性例：创建图书借阅表Borrow (RID, BID,LendDate, ReturnDate)USE LibraryGOCREATE TABLE Borrow(RID char(10) NOT NULL -读者编号外键FOREIGN KEY REFERENCES Reader(RID) ON DELETE CASCADE, /*删除主表记录时不级联删除子表相应记录*/BID char(15) NOT NULL -图书编号外键FOREIGN KEY REFERENCES Book(BID) ON DELETE NO ACTION, /*删除主表记录时级联删除子表相应记录*/第87页/共89页引用完整性 用户定义完整性使您得以定义不属于其它任何完整性分类的特定业务规则。所有的完整性类型都支持用户定义完整性。第88页/共89页感谢您的观看！第89页/共89页
展开阅读全文