企业局域网的安全与运维Ⅱ-数据库和表的创建与管理.ppt

企业局域网的安全与运 维2 创建管理数据库和表 课程引入 l在ssms中如何创建数据库？ l数据库的大小可以随着数据的增加变化吗？ l数据库在硬盘上的表现形式是什么后缀名称的 文件呢？ 课程引入 l数据库结构设计完成后，需要从逻辑设计阶段 ，过渡到物理设计阶段，即在数据库中创建数 据表了。 本节学习目标 l时间：1次课，2学时； l本节学习目标： l知识点： 系统数据库、文件存储位置； 数据库文件的类型、默认扩展名； 数据文件、事务日志文件、文件组； lsql server 2005数据表的类型 l属性的数据类型； 本节学习目标 l技能要求： 能够应用ssms创建、打开、删除数据库； 能够分离和附加数据库 ； 掌握sql server 2005创建数据表的方法； 掌握数据表结构的查看、修改及删除的操作。 文件和文件组 l1. 数据库文件 lsql server 2005 数据库具有如下3种类型的文件。 l(1) 主要数据文件 l主要数据文件包含数据库的启动信息，并指向数据库中的 其他文件。 l(2) 次要数据库文件 l除主要数据文件以外的所有其他数据文件都是次要数据文 件。 l(3) 事务日志文件 l事务日志文件保存用于恢复数据库的日志信息。 数据库的文件和文件组 l数据库的文件有三类： 主数据文件（primary）：每个数据库有且只有 一个主数据文件，它是数据库和其他数据库文件的 起点，主数据库文件的扩展名一般为mdf。 辅数据文件(secondary)：用于存储主数据文件中未存 储的剩余数据和数据库对象， 一个数据库可有零个或 多个辅数据文件，辅数据文件的多少主要根据数据库的 大小、磁盘存储情况和存储性能要求而设置。辅数据文 件的扩展名常为.ndf。 事务日志文件：存储数据库的事务日志信息，当数据库 损坏时，管理员使用事务日志文件恢复数据库。日志文 件的扩展名一般为.ldf 。每个数据库必须有一个或多个 日志文件。 思考问题：最简单的数据库有哪些文件组成？ 每个数据库至少有哪几个数据文件？ 文件和文件组 l2. 数据库逻辑和物理文件名称 lsql server 2005 文件有以下两个名称： l(1) 逻辑文件名(logical_file_name) llogical_file_name 是在所有 transact-sql 语句中引用 物理文件时所使用的名称。 l(2) 物理文件名(os_file_name) los_file_name 是包括目录路径的物理文件名。它必须符 合操作系统文件命名规则。 l例：在默认 sql server 2005 实例上创建的数据库的逻 辑文件名和物理文件名示例如图6所示，该数据库有一个 主数据库文件，两个辅助数据库文件和两个日志文件。 sql serversql server的数据库文件和文件的数据库文件和文件 组必须遵守的规则组必须遵守的规则 l一个文件或文件组必须被一个数据库使用。 l一个文件只能属于一个文件组。 l数据和事务日志不能共存于一个文件或文件组 上。 l日志文件不能属于文件组。 数据库对象及构成数据库对象及构成 l数据库是sql server存放表、视图、索引、 存储过程、触发器、事务日志等数据库对象的 逻辑实体。（逻辑结构） l一个数据库可以存储在一个或多个文件中。（ 物理结构） 文件和文件组 l图6 数据库的逻辑文件名与物理文件名示例 事务日志 l1. 事务日志支持的操作 l事务日志支持以下操作。 (1) 恢复个别的事务 l(2) sql server 启动时恢复所有未完成的事务 l(3) 将还原的数据库、文件、文件组或页前滚到故障点 l(4) 支持事务复制 l(5) 支持备用服务器解决方案 系统数据库 l1. master数据库 lmaster 数据库记录 sql server 系统的所有系统级信息。 这包括实例范围的元数据、端点、链接服务器和系统配置设 置。master 数据库还记录所有其他数据库是否存在，以及 这些数据库文件的位置。另外，master 还记录 sql server 的初始化信息。因此，如果 master 数据库不可用， 则 sql server 无法启动。在 sql server 2005 中，系统 对象不再存储在 master 数据库中，而是存储在 resource 数据库中。 系统数据库 系统数据库 l使用 master 数据库时，考虑下列建议： l始终有一个 master 数据库的当前备份可用。 l执行下列操作后，尽快备份 master 数据库： l 创建、修改或删除数据库。 l 更改服务器或数据库的配置值。 l 修改或添加登录账户 l不要在 master 中创建用户对象。否则，必须更频繁地备 份 master。 系统数据库 l2. model数据库 lmodel 数据库用作在 sql server 实例上创建所有数据库 的模板。因为每次启动 sql server 时都会创建 tempdb ，所以 model 数据库必须始终存在于 sql server 系统 中。 l当发出 create database 语句时，将通过复制 model 数据库中的内容来创建数据库的第一部分，然后用 空页填充新数据库的剩余部分。 l如果修改 model 数据库，之后创建的所有数据库都将继 承这些修改。例如，可以设置权限或数据库选项或者添加 对象，例如，表、函数或存储过程。 系统数据库 l3. msdb数据库 lmsdb 数据库由 sql server 代理用来计划警报和作业。 l对于 sql server 2005 的不同版本，这些文件的大小可能 略有不同。 l4. tempdb数据库 ltempdb 数据库是连接到 sql server 实例的所有用户都可 用的全局资源，它保存所有临时表和临时存储过程。另外， 它还用来满足所有其他临时存储要求，例如存储 sql server 生成的工作表。 系统数据库 系统数据库 l5. resource数据库 lresource 数据库是只读数据库，它包含了 sql server 2005 中的所有系统对象。sql server 系统对象(例如 sys.objects)在物理上持续存在于 resource 数据库中，但 在逻辑上，它们出现在每个数据库的 sys 架构中。 resource 数据库不包含用户数据或用户元数据。 lresource 数据库具有显著的优点。例如，快速的版本升级 和易于回滚 service pack 的能力。 lresource 数据库的物理文件名为 mssqlsystemresource.mdf。默认情况下，此文件位于 c:program filesmicrosoft sql servermssql.1mssqldatamssqlsystemresource.m df。 lsql server 2005中示例数据库有两个： ladventureworks ladventureworksdw 创建数据库 l若要创建数据库，必须确定数据库的名称、所有者、大小以 及存储该数据库的文件和文件组。其中，所有者是创建数据 库的用户。 l在创建数据库之前，应注意下列事项： l若要创建数据库，必须至少拥有 create database、 create any database 或 alter any database 权限。 l在 sql server 2005 中，对各个数据库的数据和日志文件 设置了某些权限。如果这些文件位于具有打开权限的目录中 ，那么以上权限可以防止文件被意外篡改。 l创建数据库的用户将成为该数据库的所有者。 创建数据库 l对于一个 sql server 实例，最多可以创建 32,767 个数据 库。 l数据库名称必须遵循为标识符指定的规则。 lmodel 数据库中的所有用户定义对象都将复制到所有新创建 的数据库中。可以向 model 数据库中添加任何对象(例如表 、视图、存储过程和数据类型)，以将这些对象包含到所有 新创建的数据库中。 l在sql server中，可以通过sql server management studio的图形界面或transact-sql语句实现，下面利用 ssms来创建数据库。 在图形界面下创建数据库 l这里创建一个教学数据库，名称为s_c_t，该数据库将贯穿本书 的全部过程。 l在sql server management studio下创建教学数据库过程如下 。 l(1)启动“sql server management studio”。 l(2)在【对象资源管理器】的树状结构中右击【数据库】，在出 现的快捷菜单中选择【新建数据库】菜单项，如图8所示。 l(3)出现【新建数据库】窗口，如图9所示。 在图形界面下创建数据库 l图8 创建数据库界面 在图形界面下创建数据库 l图9 【新建数据库】窗口 在图形界面下创建数据库 l在图9【常规】选项页的【数据库名称】处输入“s_c_t”，在【逻 辑名称】下输入主数据库文件的逻辑名称为“s_c_t_data”，在【 初始大小】下可以设置主数据库文件的大小，单击【自动增长】 下的【浏览】按钮，出现如图10所示的【更改自动增长设置】对 话框。 l在图9中的【路径】下单击【浏览】按钮，出现如图11所示的【定 位文件夹】对话框。 在图形界面下创建数据库 l图10 更改自动增长设置对话框 在图形界面下创建数据库 l图11 【定位文件夹】对话框 在图形界面下创建数据库 l在该对话框中可以改变文件存放路径，本例选择默认路径，设置好后单击【 确定】按钮，返回图9中。 l在数据库文件框中的第二行可以同样设置日志文件。 l可以单击【添加】按钮增加数据库的数据文件及日志文件，如图12所示建立 了一个主数据库文件s_c_t_data、一个辅助数据库文件s_c_t_data1、两 个日志文件s_c_t_log和s_c_t_log1。 l在图12中也可以单击【删除】按钮删除设置错误的数据库文件。 l在图12中左侧选择【选项】页，出现如图13所示的【选项】界面。 l在图13中显示数据库的各选项及其值。图14显示了【文件组】界面。 l设置好各项后单击【确定】按钮，返回sql server management studio 界面，数据库创建完成，如图15所示。 在图形界面下创建数据库 l图12 添加数据库文件界面 在图形界面下创建数据库 l图13 【选项】界面 在图形界面下创建数据库 l图14 【文件组】界面 l练习1： l该数据库名称为employees，有1个10mb和1个20mb的 数据文件和2个10mb的事务日志文件。数据文件逻辑名 称为employee1和employee2，物理文件名为 employee1.mdf和employee2.ndf。主文件是 employee1，由primary指定，两个数据文件的最大尺 寸分别为无限大和100mb，增长速度分别为10%和1mb 。事务日志文件的逻辑名为employeelog1和 employeelog2，物理文件名为employeelog1.ldf和 employeelog2.ldf，最大尺寸均为50mb，文件增长速 度为1mb。 练习 创建sales库，要求： l主数据文件的初始大小为5mb，最大空间为 20mb，文件的增量为1mb l次数据文件属于s_group文件组，初始大小 为5mb，最大空间不受限制（unlimited）， 文件的增量为2mb l日志文件的初始大小为2mb，最大空间不受限 制，文件的增量为1 查看数据库信息 l1. 使用图形工具查看 l在【对象资源管理器】窗口中右击要查看的数据库 名称，在级联菜单中选择属性，会出现如图16所示 的数据库属性对话框。 l通过选择左侧的不同选项，可以查看数据库的相应 信息和修改相应参数。 查看数据库信息 l图16 数据库属性对话框 缩小数据库 1、ssms l 自动收缩：数据库属性选项自动收缩 l 选定要收缩的数据库，操作所有任务收缩数据 库 删除数据库 l在sql server中，可以通过sql server management studio的图形界面或transact-sql语句删除数据库。 l1. 使用图形工具删除 l在【对象资源管理器】窗口中右击要查看的数据库名称， 在级联菜单中选择【删除】，会出现如图5.19所示的【删 除对象】对话框。 l在图19中单击【确定】按钮，即可删除数据库。 当数据库处于以下三种情况之一时,不能被删除: l 当有用户使用此数据库时 l 当数据库正在被恢复(restore)时 l 当数据库正在参与复制时 删除数据库 w注意：千万不要删除系统数据库 思考和记录 l在删除数据库时可能会出现的错误? 1 数据表的建立 l在设计数据库时，必须先确定数据库所需的表、每个表中 数据的类型以及可以访问每个表的用户。在创建表及其对 象之前，要先确定表的下列特征： l表要包含的数据的类型。 l表中的列数，每一列中数据的类型和长度(如果必要)。 l哪些列允许空值。 l是否要使用以及何处使用约束、默认设置和规则。 l所需索引的类型，哪里需要索引，哪些列是主键，哪些是 外键。 l创建表的最有效的方法是同时定义表中所需的所有内容。 这些内容包括表的数据限制和其他组件。在创建和操作表 后，将对表进行更为细致的设计。 表数据库中最重要的对象 l表的类型： l标准表：存储用户数据 l系统表：存储系统数据（配置） l临时表：常用来存储查询过程中产生的临时数据 l分区表：将一个数据量庞大的表水平的划分成多个 单元的表，这些单元分散到数据库的多个文件组中 ，来实现对多个单元的并行访问 1.1 数据类型 l设计表时首先要执行的操作之一是为每个列指定数据类型。 数据类型定义了各列允许使用的数据值。通过下列方法之一 可以为列指定数据类型： l使用 sql server 2005 系统数据类型。 l创建基于系统数据类型的别名数据类型。 l1. 系统数据类型 l在 sql server 2005 中，每个列、局部变量、表达式和参 数都具有一个相关的数据类型。数据类型是一种属性，用于 指定对象可保存的数据的类型：整数数据、字符数据、货币 数据、日期和时间数据、二进制字符串等。 1.1 数据类型 lsql server 提供系统数据类型集，该类型集定义了可与 sql server 一起 使用的所有数据类型。 (1)精确数字类型 lbigint、int、smallint、tinyint 取值范围及存储字节数见表。 lbit 可以取值为 1、0 或 null 的整数数据类型。 ldecimal与numeric 带固定精度和小数位数的数值数据类型。 lmoney和smallmoney 代表货币或货币值的数据类型。 l(2)近似数字类型 lfloat和real 用于表示浮点数值数据的大致数值数据类型。 l(3)日期和时间类型 ldatetime和smalldatetime 用于表示某天的日期和时间的数据类型。 1.1 数据类型 l(4)字符串类型 lchar 和 varchar 固定长度或可变长度的字符数据类型。 ltext 用于存储大型非 unicode 字符、unicode 字符及二进制数据的固定 长度数据类型和可变长度数据类型。 l(5)unicode字符串类型 lnchar 和 nvarchar 字符数据类型(nchar 长度固定，nvarchar 长度可变) 和 unicode 数据使用 unicode ucs-2 字符集。 lntext 用于存储大型非 unicode 字符、unicode 字符及二进制数据的固 定长度数据类型和可变长度数据类型。 l(6)二进制字符串类型 lbinary 和 varbinary 固定长度或可变长度的binary 数据类型。 limage 用于存储大型非 unicode 字符、unicode 字符及二进制数据的固 定长度数据类型和可变长度数据类型。 1.1 数据类型 l(7)其他数据类型 lcursor 这是变量或存储过程 output 参数的一种数据类型，这些 参数包含对游标的引用。 ltable 一种特殊的数据类型，用于存储结果集以进行后续处理。 ltimestamp 公开数据库中自动生成的惟一二进制数字的数据类型 。 luniqueidentifier 公开数据库中自动生成的惟一二进制数字的数据 类型。 lxml 存储 xml 数据的数据类型。 数据字典 l在数据字典中要体现如 下内容，根据数据字典 ，可以应用不同的数据 库软件创建表。 序 号 字段名描述数据类型索 引 必填字 段 1number学号 int是 2student- id 学生 i d varchar(1 0) 主 键 是 3sex性别char(2)是 4age年龄int否 5brith生日 char(30)是 6class班级int是 7departmen t 系别 varchar(2 0) 是 8time入学 时 间 char(30)是 9score成绩 float(10)是 数据表的创建 l在sql server中，可以通过sql server management studio的 图形界面或transact-sql语句实现数据表的创建，下面将介绍利 用ssms来创建表。 l例如，在教学数据库中创建如下3张数据表，学生表(s)、课程表 (c)、选课表(s_c)，其表结构如表所示。 数据表的创建 数据表的创建 l1. 在图形界面下创建数据表 l(1) 在sql server management studio中的对象资源管理 器中，右击 “teaching”数据库下的【表】项，在弹出的快 捷菜单中选择【新建表】命令，出现如图所示的表设计器界 面。 l(2) 在图中的列名下依次输入列名，在数据类型下选择数据 类型，并选择各个列是否允许空值，也可在下面的列属性对 话框中修改某列的属性，出现如图所示的学生数据表结构。 l(3) 选中sno所在的列，单击工具栏上的【设置主键】按钮 设置主键。 利用ssms创建表 l默认值：指定列的默认值。 l只有常量值（如字符串）、sql se