数据库应用技术SQLServer5.ppt

《数据库应用技术SQLServer5.ppt》由会员分享，可在线阅读，更多相关《数据库应用技术SQLServer5.ppt（66页珍藏版）》请在三一办公上搜索。

1、数据库应用技术SQL Server 2005,SQL Server 2005,第4章 SQL Server2005表的创建与管理,能力目标:能根据项目逻辑设计应用SSMS和T-SQL创建表。能根据项目逻辑设计中完整性规则应用SSMS和T-SQL设置表的主码、约束和外码。能根据逻辑设计设置合适的索引。理解实体和实体之间的联系在数据库中的实现。能根据项目逻辑设计创建并管理关系图。通过数据操作规范，培养严谨的科学态度。任务设计:根据项目逻辑设计创建表。根据项目逻辑设计中完整性规则设置表的主码、约束和外码。,SQL Server 2005,第4章 SQL Server2005表的创建与管理,创建合适的

2、索引。创建关系图。插入一定数量的记录供后续任务使用。知识要求:SQL Server 2005表的基本知识。表的创建、修改和删除操作方法。记录的插入、删除和修改操作方法。索引的基本知识。索引的创建和删除操作方法。SQL Server 2005关系图。,SQL Server 2005,第4章 SQL Server2005表的创建与管理,掌握表的创建、修改和删除操作,了解SQL Server 2005表的基本知识,熟练掌握记录的插入、删除和修改操作,了解索引的基本知识,掌握索引的创建和删除操作,目标,SQL Server 2005,4.1 SQL Server 2005表的基本知识,表的基本概念,1

3、,表的类型,2,表的数据类型,3,表的完整性体现,4,1,表的设计,5,SQL Server 2005,4.1.1表的基本概念,表是数据库对象，用于存储实体集和实体间联系的数据。SQL Server 2005表主要由列和行构成。关系模型的组成要素列：每一列用来保存对象的某一类属性。行：每一行用来保存一条记录，是数据对象的一个实例。,SQL Server 2005,4.1.1表的基本概念,例如：教务管理数据库EDUC中的学生Student表。,SQL Server 2005,4.1.2 表的类型,SQL Server 2005除了提供了用户定义的标准表外，还提供了一些特殊用途的表：分区表、临时表

4、和系统表。分区表当表很大时，可以水平地把数据分割成一些单元，放在同一个数据库的多个文件组中。用户可以通过分区快速地访问和管理数据的某部分子集而不是整个数据表，从而便于管理大表和索引。,SQL Server 2005,4.1.2 表的类型,临时表局部临时表：只是对一个数据库实例的一次连接中的创建者是可见的。在用户断开数据库的连接时，局部临时表就被删除。全局临时表：对所有的用户和连接都是可见的，并且只有所有的用户都断开临时表相关的表时，全局临时表才会被删除。系统表用来保存一些服务器配置信息数据，用户不能直接查看和修改系统表，只有通过专门的管理员连接才能查看和修改。不同版本的数据库系统的系统表一般不

5、同，在升级数据库系统时，一些应用系统表的应用可能需要重新改写。,SQL Server 2005,5.1.3表的数据类型,下表列出了SQL Server 2005中常用的数据类型：,SQL Server 2005,4.1.4 表的完整性体现,主键约束体现实体完整性，即主键各列不能为空且主键作为行的惟一标识系统表外键约束体现参照完整性默认值和规则等体现用户定义的完整性,SQL Server 2005,4.1.5 表的设计,设计表时需要确定如下内容：表中需要的列以及每一列的类型（必要时还要有长度）列是否可以为空 是否需要在列上使用约束、默认值和规则 需要使用什么样的索引 哪些列作为主键,SQL Se

6、rver 2005,4.2创建表,使用SSMS创建表,1,使用T-SQL语句创建表,2,SQL Server 2005,4.2.1 使用SSMS创建表,创建主 键约束,创建唯一性约束,创建外键约束,创建检查约束,保存表的定义,创建表,SQL Server 2005,4.2.1使用SSMS创建表,例如：在教务管理中的选课数据库（EDUC）中创建学生表Student,课程表Course,选课表SC。其中教务管理中的选课数据模型为：Student(SID,Sname,Sex,Birthday,Specialty)PK:SID Course(CID,Cname,Credit）PK:CID SC(SID

7、,CID,Grade)PK:SID,CID FK:SID和CID,SQL Server 2005,4.2.1 使用SSMS创建表,创建表：在【对象资源管理器】窗口中，展开“数据库”下的EDUC节点，右击“表”节点，选择“新建表”命令，进入表设计器，在表设计器的第一列中输入列名，第二列选择数据类型，第三列选择是否为空；例如：表Student,SQL Server 2005,4.2.1 使用SSMS创建表,表Course：,表SC：,SQL Server 2005,4.2.1 使用SSMS创建表,创建主键约束：单击选择一列名，SHIFT+单击选择连续的列名，CTRL+单击选择不相邻的列名，右键快捷

8、菜单或工具栏按钮“设置主键”；例：Student中的SID，Course中的CID，SC中的SID,CID做法：单击选择一列名，SHIFT+单击选择连续的列名，CTRL+单击选择不相邻的列名，右键快捷菜单或工具栏按钮“设置主键”；,SQL Server 2005,4.2.1 使用SSMS创建表,Student，Course表主键约束采用同样的方法设置；,Course表的主键建立示意图如下：,SQL Server 2005,4.2.1 使用SSMS创建表,创建唯一性约束：例如：Student表中的Sname 做法：右键快捷菜单或工具栏按钮“索引/键”，在弹出的【索引/键】对话框中，单击“添加”按

9、钮添加新的主/惟一键或索引；在常规的“类型”右边选择“惟一键”，在“列”的右边单击省略号按钮，选择列名Sname和排序规律；,SQL Server 2005,4.2.1 使用SSMS创建表,如图所示：,SQL Server 2005,4.2.1 使用SSMS创建表,创建外键约束例如：SC表中的SID和CID设置为外码。做法：（1）右键快捷菜单或工具栏“关系”，在弹出的【关系】对话框中，单击“添加”按钮添加新的约束关系；,SQL Server 2005,4.2.1 使用SSMS创建表,（2）单击“表和列规范”左边的“”号，再单击“表和列规范”内容框中右边的省略号按钮，从弹出的【表和列】对话框中进

10、行外键约束的表 和列的选择，单击“确定”；,SQL Server 2005,4.2.1 使用SSMS创建表,（3）回到【外键关系】对话框，将“强制外键约束”选项选择为“是”，设置“更新规则”和“删除规则”的值；,采用同样的方法添加外键CID；,SQL Server 2005,4.2.1使用SSMS创建表,创建检查约束 例如：Student表中的Sex等于男或女。做法：右键菜单或工具栏“CHECK约束”，在打开的【CHECK约束】对话框中单击“添加”按钮，在表达式文本框中输入检查表达式，在表设计器中进行选项的设置；,SQL Server 2005,4.2.1 使用SSMS创建表,保存表的定义（1

11、）单击关闭表设计器窗口，弹出下图保存对话框，单击“是”钮；,（2）输入表名，单击“确定”按钮。,SQL Server 2005,4.2.2使用T-SQL语句创建表,格式：CREATE TABLE表名（列名1 数据类型 列级完整性约束，列名 2 数据类型 列级完整性约束，列名n 类型 约束，表级完整性约束，）；,SQL Server 2005,4.2.2 使用T-SQL语句创建表,约束：实现表的完整性 NULL/NOT NULL：空值/非空值约束。DEFAULT 常量表达式：默认值约束。UNIQUE：单值约束。PRIMARY KEY：主键约束，等价非空、单值。REFERENCES 父表名(主键)

12、：外键约束。CHECK(逻辑表达式)：检查约束。,SQL Server 2005,4.2.2 使用T-SQL语句创建表,例如：在图书管理系统中的数据库（Library）中，创建读者表（Reader）,读者类型表（ReaderType），图书表（Book）和借阅表（Borrow）。图书管理系统的数据模型：ReaderType(TypeID,Typename,LimitNum,LimitDays)PK:TypeIDReader(RID,Rname,TypeID,Lendnum)PK：RID FK：TypeIDBook(BID,Bname,Author,PubComp,PubDate,Price)P

13、K：BIDBorrow(RID,BID,LendDate,ReturnDate)PK：RID,BID,LendDate FK：RID和BID,SQL Server 2005,4.2.2 使用T-SQL语句创建表,例1：创建读者类型表ReaderType,CREATE TABLE ReaderType(TypeID int NOT NULL primary key,-类型编号,主键Typename char(8)NULL,-类型名称LimitNum int NULL,-限借数量LimitDays int NULL-借阅期限),SQL Server 2005,4.2.2 使用T-SQL语句创建表,

14、例2：创建读者表Reade,USE 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,-外键，不级联删除),SQL Server 2005,4.2.2 使用T-SQL语句创建表,例3：创建图书表Book,USE LibraryGOCREATE

15、 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)-定价，检查约束),SQL Server 2005,4.2.2 使用T-SQL语句创建表,例4：创建图书借阅表Borrow(RID,BID,LendDate,ReturnDate),USE LibraryGOCREATE TA

16、BLE 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,-还期

17、primary key(RID,BID,LendDate)-表级约束，主键,SQL Server 2005,4.3 修改表,使用SSMS修改表,1,使用T-SQL语句修改表,2,SQL Server 2005,4.3.1 使用SSMS修改表,在【对象资源管理器】窗口中，展开“数据库”节点展开所选择的具体数据库节点，展开“表”节点右键要修改的表，选择“修改”命令进入表设计器即可进行表的定义的修改,SQL Server 2005,4.3.2 使用T-SQL语句修改表,格式：ALTER table 表名(ALTER COLUMN 列名 列定义，ADD 列名1 类型 约束，DROP 列名）*列定义包括

18、列的数据类型和完整性约束,SQL Server 2005,4.3.2使用T-SQL语句修改表,修改属性,添加或删除列,添加或删除约束,SQL Server 2005,4.3.2 使用T-SQL语句修改表,修改属性 例如：把表Book 中PubComp 的类型varchar(28)改为varchar(30)USE Library GO ALTER TABLE Book ALTER COLUMN PubComp varchar(30)NOT NULL GO,SQL Server 2005,4.3.2使用T-SQL语句修改表,添加或删除列例1：为表Reader添加邮件地址 USE Library G

19、O ALTER TABLE Reader ADD E-mail varchar(50)NULL CHECK(E-mail like%)GO 例2：为表Reader删除邮件地址。USE Library GO ALTER TABLE Reader DROP COLUMN E-mail GO说明：必须先删除其上的约束。,SQL Server 2004,4.3.2 使用T-SQL语句修改表,添加或删除约束 例1：为表Borrow添加主键约束（假设还没有创建）。USE Library GO ALTER TABLE Borrow ADD PRIMARY KEY(RID,BID,LendDate)GO 例2

20、：为表Borrow删除主键约束。USE Library GO ALTER TABLE Borrow DROP PRIMARY KEY(RID,BID,LendDate)GO,SQL Server 2005,4.4 删除表,使用SSMS删除表,1,使用T-SQL语句删除表,2,SQL Server 2005,4.4.1使用SSMS删除表,在【对象资源管理器】窗口中，展开“数据库”节点展开所选择的具体数据库节点，展开“表”节点右键要删除的表，选择“删除”命令或DELETE键,SQL Server 2005,4.4.2 使用T-SQL语句删除表,格式：DROP TABLE 表名例如：先随便在数据库L

21、ibrary中建一个表Test，然后删除。USE Library GO DROP TABLE Test,SQL Server 2005,4.5 插入记录,使用SSMS插入记录,1,使用T-SQL语句插入记录,2,SQL Server 2005,4.5.1 使用SSMS插入记录,在【对象资源管理器】窗口中，展开“数据库”节点，展开所选择的具体数据库节点，展开“表”节点，右键要插入纪录的表，选择“打开表”命令，即可输入纪录值例如：表ReaderType,SQL Server 2005,4.5.2 使用T-SQL语句插入记录,格式：INSERT INTO（表名|视图名）列名表 VALUES（常量表）

22、例1：插入一行所有列的值 USE Library GO INSERT into Reader VALUES(2005216001,赵成刚,3,2,)GO 例1：插入一行部分列 USE Library GO INSERT Reader(RID,Rname,TypeID)VALUES(2004060003,李亚茜,3)GO,SQL Server 2005,4.6 修改记录,使用SSMS修改记录,1,使用T-SQL语句修改记录,2,SQL Server 2005,4.6.1 使用SSMS修改记录,在【对象资源管理器】窗口中，展开“数据库”节点展开所选择的具体数据库节点，展开“表”节点右键要修改纪录的

23、表，选择“打开表”命令，即可修改纪录值。,SQL Server 2005,4.6.2 使用T-SQL语句修改记录,格式：UPDATE 表名 SET 列名1表达式,列名n表达式 where 逻辑表达式例1：把读者类型表ReaderType中学生的限借数量5本增加2本 USE Library GO UPDATE ReaderType SET LimitNum=LimitNum+2 WHERE Typename=学生 GO,SQL Server 2005,4.7 删除记录,使用SSMS删除记录,1,使用T-SQL语句删除记录,2,SQL Server 2005,4.7.1 使用SSMS修改记录,在【

24、对象资源管理器】窗口中，展开“数据库”节点展开所选择的具体数据库节点，展开“表”节点右键要删除纪录的表，选择“打开表”命令，右击要删除的行，选择“删除”命令即可删除纪录。,SQL Server 2005,4.7.2 使用T-SQL语句修改记录,格式：DELETE 表名 WHERE 逻辑表达式例1：删除Borrow表中RID为2005216001的读者的借书记录USE LibraryGODELETE ReaderWHERE RID=2005216001GO5.3 E-R模型到关系模型的转换 例2：删除test表中的所有记录USE LibraryGODELETE test,SQL Server 2

25、005,4.8 索引的基本操作,索引的基本知识,1,使用SSMS创建索引,2,使用T-SQL语句创建索引,3,使用SSMS删除索引,4,使用T-SQL语句删除索引,5,SQL Server 2005,4.8.1索引的基本知识,索引基础知识 索引的类型 创建原则及注意事项,SQL Server 2005,4.8.1 索引的基本知识,索引基础知识 索引的类型 用户可以通过合理地创建索引大大提高数据库的查找速度索引也可以保证列的惟一性，从而确保表中数据的完整性 索引中包含键值，这些键值存储在一种数据结构（B-树）中，通键 可以快速地找到与键值相关的数据记录,SQL Server 2005,4.8.1

26、 索引的基本知识,索引的类型 聚集索引：根据索引的键值排序，表中的数据并保存。每个表只能有一个聚集索引。非聚集索引：索引的键值包含指向表中记录存储位置的指针，不对表中数据排序，只对键值排序。惟一索引：保证索引中不含有相同的键值，聚集索引和非聚集索引都可以是惟一索引。包含列的索引：一种非聚集索引，其中包含一些非键值的列，这些列对键只有辅助作用。全文（full-text）索引：上Microsoft全文引擎（full-text engine）创建并管理的一种基开符号的函数（token-based functional）索引，支持快速的字符串中单词的查找。,SQL Server 2005,4.8.1

27、索引的基本知识,创建原则及注意事项下列情况适合建立索引：经常被查询搜索的列，如经常在where子句中出现的列。在ORDER BY子句使用的列。外键或主键列。下列情况不适合建立索引：在查询中很少被引用的列。包含太多重复值的列。数据类型为bit、text、image 等的列不能建立索引。,SQL Server 2005,4.8.2 使用SSMS创建索引,（1）单击相应表左边的“”号，右击“索引”节点，选择“新建索引”命令；,（2）在弹出的【新建索引】对话框中设置要创建索引的名称、类型，添加索引键列。,SQL Server 2005,4.8.2 使用SSMS创建索引,例：在EDUC数据库中为Stud

28、ent建立非聚集的惟一的复合索引Studentindex（包含SID和Sname列）。,SQL Server 2005,4.8.3 使用T-SQL语句创建索引,格式 CREATEUNIQUECLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名（列名ASC|DESC,n）WITH PAD_INDEX,FILLACTOR=fillfactor,DROP_EXISTING,SQL Server 2005,4.8.3 使用T-SQL语句创建索引,说明 UNIQUE：指定创建的索引为惟一索引CLUSTERED|NONCLUSTERED：指定创建的是聚集索引还是非聚集索引ASC|D

29、ESC：确定某个具体的索引列是升序还是降序，默认为升序ASCPAD_INDEX和fillfactor：填充因子，它指定创建索引的过程中，索引页的填满程度DROP_EXISTING：删除先前存在的、与创建索引同名的索引,SQL Server 2005,4.8.3 使用T-SQL语句创建索引,例：在EDUC数据库中为Student表创建一个唯一性聚集索引Studentindex2，索引关键字为SID，升序排列，填充因子是50（执行该例时先将SID的主键移去）USE EDUC GO CREATE UNIQUE CLUSTERED INDEX Studentindex2 ON Student(SID

30、ASC)WITH FILLFACTOR=50,SQL Server 2005,4.8.4使用SSMS删除索引,SQL Server 2005,4.8.5 使用T-SQL语句删除索引,格式：DROP INDEX 表名.索引名例：删除表Student的索引studentindex USE EDUC GO DROP INDEX Studen.studentindex,SQL Server 2005,4.9 小结,本章阐述SQL Server 2005表的基本知识；讲述了表的创建、修改和删除的方法以及表中记录的添加、修改和删除的方法；介绍了索引的基本操作，其中重点介绍了如何创建索引和删除索引。,Thank You!,