《数据库应用技术SQLServer5共67页课件》由会员分享，可在线阅读，更多相关《数据库应用技术SQLServer5共67页课件（67页珍藏版）》请在金锄头文库上搜索。
1、数据库应用技术数据库应用技术SQLServer2019SQLServer2019SQL Server 2019第第4章章 SQL Server2019表的创建与管理表的创建与管理能力目标能力目标: :能根据项目逻辑设计应用SSMS和T-SQL创建表。能根据项目逻辑设计中完整性规则应用SSMS和T-SQL设置表的主码、约束和外码。能根据逻辑设计设置合适的索引。理解实体和实体之间的联系在数据库中的实现。能根据项目逻辑设计创建并管理关系图。通过数据操作规范，培养严谨的科学态度。任务设计任务设计: :根据项目逻辑设计创建表。根据项目逻辑设计中完整性规则设置表的主码、约束和外码。SQL Server 2
2、019第第4章章 SQL Server2019表的创建与管理表的创建与管理创建合适的索引。创建关系图。插入一定数量的记录供后续任务使用。知识要求知识要求: :SQL Server 2019表的基本知识。表的创建、修改和删除操作方法。记录的插入、删除和修改操作方法。索引的基本知识。索引的创建和删除操作方法。SQL Server 2019关系图。SQL Server 2019第第4章章 SQL Server2019表的创建与管理表的创建与管理掌握表的创建、修改和删除操作了解SQL Server 2019表的基本知识熟练掌握记录的插入、删除和修改操作了解索引的基本知识掌握索引的创建和删除操作目标SQ
3、L Server 20194.1 4.1 SQL Server 2019表的基本知识表的基本知识 表的基本概念表的基本概念1 表的类型表的类型2 表的数据类型表的数据类型3 表的完整性体现表的完整性体现41 表的设计表的设计5SQL Server 20194.1.1表的基本概念表的基本概念表表是是数数据据库库对对象象，用用于于存存储储实实体体集集和和实实体体间间联联系系的的数数据据。SQL SQL Server Server 20192019表表主主要要由由列列和和行行构构成成。关关系系模模型型的的组组成成要要素素列：每一列用来保存对象的某一类属性。行：每一行用来保存一条记录，是数据对象的一个
4、实例。SQL Server 20194.1.1表的基本概念表的基本概念例如：教务管理数据库EDUC中的学生Student表。SQL Server 20194.1.2 表的类型SQL Server 2019除了提供了用户定义的标准表外，还提供了除了提供了用户定义的标准表外，还提供了一些特殊用途的表：分区表、临时表和系统表。一些特殊用途的表：分区表、临时表和系统表。分区表分区表当表很大时，可以水平地把数据分割成一些单元，放在同一个数据库的多个文件组中。用户可以通过分区快速地访问和管理数据的某部分子集而不是整个数据表，从而便于管理大表和索引。SQL Server 20194.1.2 表的类型临时表局
5、部临时表 ：只是对一个数据库实例的一次连接中的创建者是可见的。在用户断开数据库的连接时，局部临时表就被删除。 全局临时表 ：对所有的用户和连接都是可见的，并且只有所有的用户都断开临时表相关的表时，全局临时表才会被删除 。系统表用来保存一些服务器配置信息数据，用户不能直接查看和修改系统表，只有通过专门的管理员连接才能查看和修改。不同版本的数据库系统的系统表一般不同，在升级数据库系统时，一些应用系统表的应用可能需要重新改写。 SQL Server 20195.1.3表的数据类型表的数据类型 下表列出了下表列出了SQL Server 2019中常用的数据类型中常用的数据类型：SQL Server 2
6、0194.1.4 表的完整性体现表的完整性体现l主键约束体现实体完整性，即主键各列不能为空且主键作为行的惟一标识系统表l外键约束体现参照完整性l默认值和规则等体现用户定义的完整性SQL Server 20194.1.5 表的设计表的设计 设计表时需要确定如下内容：n 表中需要的列以及每一列的类型（必要时还要有长度）n 列是否可以为空n 是否需要在列上使用约束、默认值和规则n 需要使用什么样的索引n 哪些列作为主键SQL Server 20194.2创建表创建表 使用使用SSMSSSMS创建表创建表1 使用使用T-SQLT-SQL语句创建表语句创建表2SQL Server 20194.2.1 使
7、用使用SSMS创建表创建表创建主 键约束创建唯一性约束 创建外键约束创建检查约束 保存表的定义创建表SQL Server 20194.2.1使用使用SSMS创建表创建表例如：在教务管理中的选课数据库（EDUC）中创建学生表Student, 课程表Course, 选课表SC。其中教务管理中的选课数据模型为： Student(SID,Sname,Sex,Birthday,Specialty) PK:SID Course(CID, Cname,Credit）PK:CID SC(SID, CID,Grade) PK:SID, CID FK:SID和CID SQL Server 20194.2.1 使用
8、使用SSMS创建表创建表创建表：在【对象资源管理器】窗口中，展开创建表：在【对象资源管理器】窗口中，展开“数据库数据库”下的下的EDUC节点，节点，右击右击“表表”节点，选择节点，选择“新建表新建表”命令，进入表设计器，在表设计器的第一命令，进入表设计器，在表设计器的第一列中输入列名，第二列选择数据类型，第三列选择是否为空；列中输入列名，第二列选择数据类型，第三列选择是否为空；例如：表例如：表StudentSQL Server 20194.2.1 使用使用SSMS创建表创建表表Course：表SC：SQL Server 20194.2.1 使用使用SSMS创建表创建表创建主键约束：单击选择一列
9、名，创建主键约束：单击选择一列名，SHIFT+单击选择连续的列名，单击选择连续的列名，CTRL+单击选择不相邻的列名，右键快捷菜单或工具栏按钮单击选择不相邻的列名，右键快捷菜单或工具栏按钮“设置设置主键主键”；例：Student中的SID，Course中的CID，SC中的SID, CID做法：单击选择一列名，SHIFT+单击选择连续的列名，CTRL+单击选择不相邻的列名，右键快捷菜单或工具栏按钮“设置主键”；SQL Server 20194.2.1 使用使用SSMS创建表创建表Student，Course表主键约束采用同样的方法设置；Course表的主键建立示意图如下：表的主键建立示意图如下：
10、SQL Server 20194.2.1 使用使用SSMS创建表创建表创建唯一性约束：创建唯一性约束：例如：Student表中的Sname 做法： 右键快捷菜单或工具栏按钮“索引/键”，在弹出的【索引/键】对话框中，单击“添加”按钮添加新的主/惟一键或索引；在常规的“类型”右边选择“惟一键”，在“列”的右边单击省略号按钮，选择列名Sname和排序规律；SQL Server 20194.2.1 使用使用SSMS创建表创建表如图所示：如图所示：SQL Server 20194.2.1 使用使用SSMS创建表创建表创建外键约束创建外键约束例如：SC表中的SID和CID设置为外码。做法：（1）右键快捷
11、菜单或工具栏“关系”，在弹出的【关系】对话框中，单击“添加”按钮添加新的约束关系；SQL Server 20194.2.1 使用使用SSMS创建表创建表（2 2）单击“表和列规范”左边的“”号，再单击“表和列规范”内容框中右边的省略号按钮，从弹出的【表和列】对话框中进行外键约束的表 和列的选择， 单击“确定”；SQL Server 20194.2.1 使用使用SSMS创建表创建表（3）回到【外键关系】对话框，将“强制外键约束”选项选择为“是”，设置“更新规则”和“删除规则”的值； 采用同样的方法添加外键采用同样的方法添加外键CIDCID；SQL Server 20194.2.1使用使用SSMS
12、创建表创建表创建检查约束创建检查约束 例如：Student表中的Sex等于男或女。 做法：右键菜单或工具栏“CHECK约束”，在打开的【CHECK约束】对话框中单击“添加”按钮，在表达式文本框中输入检查表达式，在表设计器中进行选项的设置；SQL Server 20194.2.1 使用使用SSMS创建表创建表保存表的定义保存表的定义（1 1）单击关闭表设计器窗口，弹出下图保存对话框，单击“是”钮；（2）输入表名，单击输入表名，单击“确定确定”按钮。按钮。SQL Server 20194.2.2使用使用T-SQL语句创建表语句创建表格式：格式： CREATE TABLE表名表名 （列名1 数据类型
13、 列级完整性约束， 列名 2 数据类型 列级完整性约束， 列名n 类型 约束， 表级完整性约束， ）；SQL Server 20194.2.2 使用T-SQL语句创建表约束：实现表的完整性 NULL/NOT NULL：空值/非空值约束。DEFAULT 常量表达式：默认值约束。UNIQUE：单值约束。PRIMARY KEY：主键约束，等价非空、单值。REFERENCES 父表名 (主键)：外键约束。CHECK (逻辑表达式)：检查约束。 SQL Server 20194.2.2 使用T-SQL语句创建表例如：在图书管理系统中的数据库（Library）中，创建读者表（Reader）, 读者类型表（
14、ReaderType），图书表（Book）和借阅表（Borrow）。图书管理系统的数据模型：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 SQL Server 20194.2.
15、2 使用使用T-SQL语句创建表语句创建表例1：创建读者类型表ReaderTypeCREATE TABLE ReaderType(TypeID int NOT NULL primary key,-类型编号,主键Typename char(8) NULL,-类型名称LimitNum int NULL,-限借数量LimitDays int NULL -借阅期限)SQL Server 20194.2.2 使用使用T-SQL语句创建表语句创建表例2：创建读者表Reade USE LibraryGOCREATE TABLE Reader(RID char(10) NOT NULL PRIMARY KEY
16、,-读者编号，主键Rname char(8) NULL,-读者姓名TypeID int NULL,-读者类型Lendnum int NULL ,-已借数量FOREIGN KEY(TypeID) REFERENCES ReaderType(TypeID) ON DELETE NO ACTION, -外键，不级联删除) SQL Server 20194.2.2 使用使用T-SQL语句创建表语句创建表例3：创建图书表BookUSE LibraryGOCREATE TABLE Book(BID char(9) PRIMARY KEY,-图书编号，主键Bname varchar(42) NULL,-图书
17、书名Author varchar(20) NULL,-作者PubComp varchar(28) NULL,-出版社PubDate datetime NULL,-出版日期Price decimal(7,2) NULL CHECK (Price0)-定价，检查约束) SQL Server 20194.2.2 使用使用T-SQL语句创建表语句创建表例4：创建图书借阅表Borrow (RID, BID,LendDate, ReturnDate)USE LibraryGOCREATE TABLE Borrow(RID char(10) NOT NULL -读者编号外键FOREIGN KEY REFER
18、ENCES 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) ) -表级约束，主键SQL Server
19、 20194.3 修改表 使用使用SSMSSSMS修改表修改表1 使用使用T-SQLT-SQL语句修改表语句修改表2SQL Server 20194.3.1 使用使用SSMS修改表修改表在【对象资源管理器】窗口中，展开在【对象资源管理器】窗口中，展开“数据库数据库”节点节点展开所选择的具体数据库节点，展开展开所选择的具体数据库节点，展开“表表”节点节点右键要修改的表，选择右键要修改的表，选择“修改修改”命令命令进入表设计器即可进行表的定义的修改进入表设计器即可进行表的定义的修改SQL Server 20194.3.2 使用使用T-SQL语句修改表语句修改表格式： ALTER table 表名表
20、名 (ALTER COLUMN 列名列名 列定义列定义， ADD 列名列名1 类型类型 约束，约束， DROP 列名列名 ）*列定义包括列的数据类型和完整性约束列定义包括列的数据类型和完整性约束 SQL Server 20194.3.2使用使用T-SQL语句修改表语句修改表2 23 31 1修改属性添加或删除列添加或删除约束SQL Server 20194.3.2 使用使用T-SQL语句修改表语句修改表修改属性修改属性 例如：把表Book 中PubComp 的类型varchar(28)改为varchar(30) USE Library GO ALTER TABLE Book ALTER COL
21、UMN PubComp varchar(30) NOT NULL GO SQL Server 20194.3.2使用使用T-SQL语句修改表语句修改表添加或删除列例1：为表Reader添加邮件地址 USE Library GO 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 20194.3.2 使用使用T
22、-SQL语句修改表语句修改表添加或删除约束 例1：为表Borrow添加主键约束（假设还没有创建）。 USE Library GO ALTER TABLE Borrow ADD PRIMARY KEY(RID,BID,LendDate) GO 例2：为表Borrow删除主键约束。 USE Library GO ALTER TABLE Borrow DROP PRIMARY KEY (RID,BID,LendDate) GO SQL Server 20194.4 删除表删除表 使用SSMS删除表1 使用T-SQL语句删除表2SQL Server 20194.4.1使用使用SSMS删除表删除表在【对
23、象资源管理器】窗口中，展开在【对象资源管理器】窗口中，展开“数据库数据库”节点节点展开所选择的具体数据库节点，展开展开所选择的具体数据库节点，展开“表表”节点节点右键要删除的表，选择右键要删除的表，选择“删除删除”命令或命令或DELETE键键SQL Server 20194.4.2 使用使用T-SQL语句删除表语句删除表格式： DROP TABLE 表名例如：先随便在数据库Library中建一个表Test，然后删除。 USE Library GO DROP TABLE Test SQL Server 20194.5 插入记录插入记录 使用使用SSMSSSMS插入记录插入记录1 使用使用T-SQ
24、LT-SQL语句插入记录语句插入记录2SQL Server 20194.5.1 使用使用SSMS插入记录插入记录在【对象资源管理器】窗口中，展开“数据库”节点，展开所选择的具体数据库节点，展开“表”节点，右键要插入纪录的表，选择“打开表”命令，即可输入纪录值例如：表ReaderTypeSQL Server 20194.5.2 使用使用T-SQL语句插入记录语句插入记录格式： INSERT INTO（表名|视图名）列名表 VALUES（常量表）例1：插入一行所有列的值 USE Library GO INSERT into Reader VALUES(2019216001,赵成刚,3,2,zhch
25、gsina) GO 例1：插入一行部分列 USE Library GO INSERT Reader(RID,Rname,TypeID) VALUES(2019060003,李亚茜,3) GO SQL Server 20194.6 修改记录修改记录 使用使用SSMSSSMS修改记录修改记录1 使用使用T-SQLT-SQL语句修改记录语句修改记录2SQL Server 20194.6.1 使用使用SSMS修改记录修改记录在【对象资源管理器】窗口中，展开“数据库”节点展开所选择的具体数据库节点，展开“表”节点右键要修改纪录的表，选择“打开表”命令，即可修改纪录值。SQL Server 20194.6
26、.2 使用使用T-SQL语句修改记录语句修改记录格式：格式： UPDATE 表名表名 SET 列名列名1表达式表达式, , 列名列名n表达式表达式 where 逻辑表达式逻辑表达式例例1 1：把读者类型表：把读者类型表ReaderType中学生的限借数量中学生的限借数量5本增加本增加2本本 USE Library GO UPDATE ReaderType SET LimitNum=LimitNum +2 WHERE Typename=学生学生 GO SQL Server 20194.7 删除记录删除记录 使用使用SSMSSSMS删除记录删除记录1 使用使用T-SQLT-SQL语句删除记录语句删
27、除记录2SQL Server 20194.7.1 使用使用SSMS修改记录修改记录在【对象资源管理器】窗口中，展开在【对象资源管理器】窗口中，展开“数据库数据库”节点节点展开所选择的具体数据库节点，展开展开所选择的具体数据库节点，展开“表表”节点节点右右键键要要删删除除纪纪录录的的表表，选选择择“打打开开表表”命命令令，右右击击要要删删除除的的行行，选择选择“删除删除”命令即可删除纪录。命令即可删除纪录。SQL Server 20194.7.2 使用使用T-SQL语句修改记录语句修改记录格式： DELETE 表名 WHERE 逻辑表达式例1：删除Borrow表中RID为2019216001的读
28、者的借书记录USE LibraryGODELETE ReaderWHERE RID=2019216001GO5.3 E-R模型到关系模型的转换 例2：删除test表中的所有记录USE LibraryGODELETE testSQL Server 20194.8 索引的基本操作索引的基本操作 索引的基本知识索引的基本知识1 使用使用SSMSSSMS创建索引创建索引2 使用使用T-SQLT-SQL语句创建索引语句创建索引3 使用使用SSMSSSMS删除索引删除索引4 使用使用T-SQLT-SQL语句删除索引语句删除索引5SQL Server 20194.8.1索引的基本知识索引的基本知识 索引基础
29、知识索引基础知识 索引的类型索引的类型 创建原则及注意事项创建原则及注意事项SQL Server 20194.8.1 索引的基本知识索引的基本知识索引基础知识索引基础知识 索引的类型 用户可以通过合理地创建索引大大提高数据库的查找速度索引也可以保证列的惟一性，从而确保表中数据的完整性 索引中包含键值，这些键值存储在一种数据结构（B-树）中，通键 可以快速地找到与键值相关的数据记录 SQL Server 20194.8.1 索引的基本知识索引的基本知识索引的类型 聚集索引：根据索引的键值排序，表中的数据并保存。每个表只能有一个聚集索引 。非聚集索引：索引的键值包含指向表中记录存储位置的指针，不对
30、表中数据排序，只对键值排序。惟一索引：保证索引中不含有相同的键值，聚集索引和非聚集索引都可以是惟一索引。包含列的索引：一种非聚集索引，其中包含一些非键值的列，这些列对键只有辅助作用。全文（full-text）索引：上Microsoft全文引擎（full-text engine）创建并管理的一种基开符号的函数（token-based functional）索引，支持快速的字符串中单词的查找。SQL Server 20194.8.1 索引的基本知识索引的基本知识创建原则及注意事项创建原则及注意事项下列情况适合建立索引：下列情况适合建立索引：经常被查询搜索的列，如经常在where子句中出现的列。 在
31、ORDER BY子句使用的列。外键或主键列。 下列情况不适合建立索引：下列情况不适合建立索引：在查询中很少被引用的列。包含太多重复值的列。数据类型为bit、text、image 等的列不能建立索引。 SQL Server 20194.8.2 使用使用SSMS创建索引创建索引（1）单击相应表左边的“”号，右击“索引”节点，选择“新建索引”命令； （2）在弹出的【新建索引】对话框中设置要创建索引的名称、类型，添加索引键列。SQL Server 20194.8.2 使用使用SSMS创建索引创建索引例 ： 在 EDUC数 据 库 中 为 Student建 立 非 聚 集 的 惟 一 的 复 合 索 引
32、Studentindex（包含SID和Sname列）。 SQL Server 20194.8.3 使用使用T-SQL语句创建索引语句创建索引格式格式 CREATEUNIQUECLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名（列名ASC|DESC,n） WITH PAD_INDEX ,FILLACTOR=fillfactor ,DROP_EXISTING SQL Server 20194.8.3 使用使用T-SQL语句创建索引语句创建索引 说明 UNIQUE：指定创建的索引为惟一索引CLUSTERED|NONCLUSTERED：指定创建的是聚集索引还是非聚集索引ASC
33、|DESC：确定某个具体的索引列是升序还是降序，默认为升序ASCPAD_INDEX和fillfactor：填充因子，它指定创建索引的过程中，索引页的填满程度DROP_EXISTING：删除先前存在的、与创建索引同名的索引SQL Server 20194.8.3 使用使用T-SQL语句创建索引语句创建索引 例：在EDUC数据库中为Student表创建一个唯一性聚集索引Studentindex2，索引关键字为SID，升序排列，填充因子是50（执行该例时先将SID的主键移去） USE EDUC GO CREATE UNIQUE CLUSTERED INDEX Studentindex2 ON Stu
34、dent (SID ASC) WITH FILLFACTOR=50 SQL Server 20194.8.4使用使用SSMS删除索引删除索引 右键单击要删除的索引“删除”单击要删除的索引，“编辑”“删除”SQL Server 20194.8.5 使用使用T-SQL语句删除索引语句删除索引 格式： DROP INDEX 表名.索引名例：删除表Student的索引studentindex USE EDUC GO DROP INDEX Studen.studentindexSQL Server 20194.9 小结小结 本章阐述SQL Server 2019表的基本知识；讲述了表的创建、修改和删除的方法以及表中记录的添加、修改和删除的方法；介绍了索引的基本操作，其中重点介绍了如何创建索引和删除索引。谢谢！谢谢！67
