5s 6s管理_cs5sqlserver2000数据库基本操作

5 1SQLServer2000数据库基本操作5 2表和视图的基本操作5 3案例3 创建活期储蓄管理系统数据库 数据库的物理设计与实现 SQLServer2000中一台服务器上可创建多个数据库 SQLServer2000中的数据库是由数据表的集合组成的 每个数据表中包含数据以及其他数据库对象 这些对象包括视图 索引 存储过程和触发器等 数据库系统使用一组操作系统文件来映射数据库管理系统中保存的数据库 数据库中的所有数据和对象都存储在其映射的操作系统文件中 这些操作系统文件可以是数据文件或日志文件 5 1SQLServer2000数据库基本操作 5 1 1SQLServer数据库概述数据库文件和文件组SQLServer的数据库由数据文件和日志文件组成 数据文件是用来存放数据库中的数据的 数据文件又包括主数据文件和次数据文件 每个数据库都包括一个主数据文件和一个或多个日志文件 还可以有次数据文件 主数据文件 mdf 存储数据信息和数据库的启动信息 一个数据库有且仅有一个主数据文件 次数据文件 ndf 存储主数据文件存储不下的数据信息 一个数据库可以没有次数据文件 也可有多个次数据文件 日志文件 ldf 存储数据库的所有事务日志信息 包含用于恢复数据库的日志信息 一个数据库至少有一个日志文件 也可以有多个日志文件 数据库文件组为了方便管理 提高系统性能 将多个数据库文件组织成一组 即称为数据库文件组 数据库文件组控制各个文件的存放位置 常常将每个文件建立在不同的硬盘驱动器上 这样可以减轻单个硬盘驱动器的存储负载 提高数据库的存储效率 从而实现提高系统性能的目的 在使用数据库文件和文件组时 应该注意以下几点 1 每个文件或文件组只能用于一个数据库 2 每个文件只能属于一个文件组 3 日志文件是独立的 数据库的数据和日志信息不能放在同一个文件或文件组中 数据文件和日志文件总是分开的 系统数据库和示例数据库SQLServer支持系统数据库 示例数据库和用户数据库 系统和示例数据库是在安装SQLServer后自动创建的 用户数据库是由系统管理员或授权的用户创建的数据库 系统数据库SQLServer的系统数据库包括以下几个数据库 master数据库是SQLServer的总控数据库 保存了SQLServer系统的全部系统信息 所有登录信息和系统配置 保存了所有建立的其他数据库及其有关信息 用户应随时备份该数据库 以保证系统的正常运行 master数据库中包含大量的系统表 视图和存储过程 用于保存Server级的系统信息 并实现系统管理 其中特有的 常用的系统表和存储过程见附录B和附录C tempdb数据库tempdb是一个临时数据库 是全局资源 它保存全部的临时表和临时存储过程 每次启动SQLServer时 tempdb数据库都被重建 因此 该数据库在系统启动时总是干净的 使用tempdb不需要特殊的权限 不管SQLServer中安装了多少数据库 tempdb只有一个 tempdb是系统中负担最重的数据库 几乎所有的查询都可能使用它 model数据库是一个模板数据库 每当创建一个新数据库时 SQLServer就复制model数据库的内容到新建数据库中 因此 所有新建数据库的内容都和这个数据库完全一样 如果用户想使每个新建的数据库一开始就具有某些对象 可以将这些对象放到model数据库中 这样所有新建的数据库都将继承这些内容 model数据库中有18个系统表 master数据库中也有这些系统表 视图以及存储过程 用于保存数据库级的系统信息 msdb数据库msdb数据库是一个和自动化有关的数据库 SQLServer代理 SQLServerAgent 使用msdb数据库来安排报警 作业 并记录操作员 如完成一些调度性的工作 备份和复制等 示例数据库SQLServer的示例数据库主要包括以下两个数据库 pubs数据库一个图书出版方面的示例数据库 虽然用户可以随时修改 甚至删除该数据库 但建议用户保留该数据库 以便更好地学习SQLServer Northwind数据库一个涉及虚构的Northwind贸易公司在世界范围内进出口食品的销售情况示例数据库 5 1 2创建数据库创建数据库 就是为数据库确定名称 大小 存放位置 文件名和所在文件组的过程 文件名称 数据文件和日志文件的名称 逻辑名 文件名必须符合命名规则 在同一台SQLServer服务器上 各数据库的名称是惟一的 数据库的创建信息存放在master数据库的sysdatabases系统表中 创建数据库后 系统自动把model数据库中的信息复制到新建的数据库中 创建数据库之前 首先要考虑数据库的拥有者 数据库的初始容量 最大容量 增长量以及数据库文件的存放路径等因素 文件大小一般以MB为单位 SQLServer2000中创建数据库的方法 使用企业管理器创建数据库 使用Transact SQL语句创建数据库 使用向导创建数据库 使用企业管理器创建数据库例 以默认形式创建mydb数据库 步骤如下 1 启动企业管理器 连接服务器 展开其树形目录 用鼠标右键单击 数据库 文件夹 在弹出的快捷菜单中 单击 新建数据库 打开图5 2所的对话框 2 在 数据库属性 对话框的 名称 文本框内输入数据库名 逻辑名 例如MyDB 这个对话框自动以该数据库名命名 系统默认用该数据库名与 data 串的连接命名数据文件 见图5 3 该数据库名与 log 串的连接命名日志文件 见图5 4 这两个不同选项卡界面内的设置 分别为数据主文件和日志文件的名称 存储位置 初始大小 所属文件组 默认为主文件组PRIMARY 文件是否自动增长 增长的方式和文件大小的限制等 3 单击 确定 按钮 数据库就创建好了 在默认位置 采用默认设置创建了一个名为MyDB的数据库 图5 2数据库属性对话框 图5 3数据文件选项卡界面 图5 4事务日志文件选项卡界面 使用Transact SQL命令创建数据库CREATEDATABASEdatabase name ON PRIMARY n FILEGROUPfilegroup name n LOGON n FORLOAD FORATTACH 其中 文件格式 语法格式如下 NAME logical file name FILENAME os file name SIZE size MAXSIZE max size UNLIMITED FILEGROWTH growth increment 图5 5创建数据库exampledbl 例5 1 省略CREATEDATABASE命令中各选项创建一个数据库exampledbl 命令和执行结果见图5 5 例5 2 使用ON和LOGON选项创建一个数据库exampledb2 命令和执行结果见图5 6 图5 6创建数据库exampledb2 用向导创建数据库使用向导创建数据库是一种非常简单的方法 启动企业管理器 在工具菜单中选择向导即可按向导进行相应的操作 5 1 3修改数据库创建数据库后 可能会由于某种原因需要对其进行修改 例如增加和删除数据库的文件和文件组 修改文件和文件组的属性 但只有sysadmin和dbcreator服务器角色成员和DBO才能修改数据库 使用企业管理器修改数据库有两种方法来修改 使用企业管理器修改数据库 使用T SQL命令修改数据库 1 1 使用企业管理器修改数据库步骤如下 1 启动企业管理器 连接服务器 展开其树形目录 展开 数据库 文件夹 用鼠标右键单击要修改的数据库名 例如exampledb1 在弹出的快捷菜单中 单击 属性 命令 则弹出如图5 7所示的对话框 2 在exampledb1属性对话框 数据文件 选项卡画面中 可以修改数据库的主文件组和用户定义文件组中各数据文件的信息 包括逻辑名 物理文件名 初始长度 所属文件组及自动增长的限制等 3 单击 事务日志 选项卡 在这个选项卡画面中 用户可以修改数据库的日志文件的信息 包括逻辑名 物理文件名 初始长度及自动增长的限制等 4 单击 文件组 选项 权限 等选项卡 可以修改数据库的文件组 数据库选项 数据库访问权限等内容 图5 7数据库属性对话框 使用Transact SQL命令修改数据库ALTERDATABASEdatabase ADDFILE n TOFILEGROUPfilegroup name ADDLOGFILE n REMOVEFILElogical file name ADDFILEGROUPfilegroup name REMOVEFILEGROUPfilegroup name MODIFYFILE MODIFYFILEGROUPfilegroup namefilegroup property 其中语法格式如下 NAME logical file name FILENAME os file name SIZE size MAXSIZE max sizeUNLIMITED FILEGROWTH growth increment 例5 4 在exampledbl数据库中添加一个文件组exampledbl group 命令行为 ALTERDATABASEexampledblADDFILEGROUPexampledbl group 例5 5 将一个新的数据文件exampledbl data2添加到exampledbl数据库的exampledbl group文件组中 命令行为ALTERDATABASEexampledb1ADDFILE NAME exampledbl data2 FILENAME c mssql2000 exampledbl data2 mdf TOFILEGROUPexampledbl group 例5 6 修改数据文件exampledb2的初始长度为2MB 前面默认设置为IMB 命令行为 ALTERDATABASEexampledbdb2MODIFYFILE NAME exampledb2 SIZE 2 例5 7 将一个新的日志文件exampledbl log2添加到exampledbl数据库中 命令行为 ALTERDATABASEexampledblADDLOGFILE NAME exampledbl log2 FILENAME c mssql2000 exampledbl log2 1df 修改数据库名系统存储过程sp renamedb 在master系统数据库中 能够修改数据库的名称 在使用sp renamedb修改数据库名之前 必须将数据库设置成单用户模式 设置数据库为单用户模式 用户可以在图5 7所示的数据库属性对话框中选择 选项 在弹出的对话框中选择 限制访问 再选中该数据库的 单用户 选项 用户也可以使用系统存储过程sp dboption 在master系统数据库中 设置 单用户 选项 下述命令行是将exampledbl数据库更名为exampledb的完整过程 用sp helpdb来查看 见图5 8 图5 8数据库更名过程 5 1 4删除数据库使用企业管理器删除数据库使用企业管理器删除数据库非常简单 但每次只能删除一个数据库 方法是启动企业管理器后 展开 数据库 文件夹 单击要删除的数据库后如exampledb2 用鼠标右键单击要删除的数据库名 从弹出的快捷菜单中单击 删除 命令 系统弹出警告对话框见图5 9 要求用户确认是否删除该数据库 单击 是 按钮 就删除了该数据库 使用Transact SOL命令删除数据库DROPDATABASEdatabase name n database name指定要删除的数据库 且一次可以删除多个数据库 但不要随便删除系统数据库 可能会造成SQLServer系统崩溃 例5 8 删除数据库exampledb exampledb2 DROPDATABASEexampledb exampledb2 图5 9删除数据库对话框 5 2表和视图的基本操作 5 2 1基本知识SQLServer的一个数据库中可以存储20亿个表 一个表最多允许定义1024个列 表的行数和总大小仅受可使用空间的限制 表的每一列必须具有相同的数据类型 命名表在一个数据库中 允许多个用户创建表 创建表的用户称为该表的所有者 因此 表的名称应该体现数据库 用户和表名三方面的信息 格式如下 database name owner table name数据类型确定表的每列的数据类型 是设计表的重要步骤 列的数据类型就是定义该列所能存放的数据的值 SQLServer2000的数据类型很丰富 这里仅给出SQLServer常用的数据类型 见表5 4 设计表在为一个数据库设计表之前 应该完成了需求分析 确定了概念模型 将概念模型转换为关系模型 关系模型中的每一个关系对应数据库中的一个表 由第1章和第2章对图书管理系统的分析与设计知 若该系统的数据库为已创建的MyDb数据库 则需要为该数据库创建读者信息表 Readers 图书信息表 Books 借阅信息表 Borrowinf 读者类型表 type 创建表的过程 就是将一种具体的关系DBMS 例如SQLServer2000 作为工具 实现关系模型 逻辑模型 到物理模型的转换 即关系模型的物理实现 因此 以下称关系为表 称元组为行 或记录 称属性为列 或字段 设计表时需要确定如下内容 1 表中需要的列以及每一列的类型 必要时还要有长度 2 列是否可以为空 3 是否需要在列上使用约束 默认值和规则 4 需要使用什么样的索引 5 哪些列作为主键 表的设计要体现完整性约束的实现 实体完整性约束的体现是主键约束 即主键的各列不能为空 且主键作为行的惟一标识 外键约束是参照完整性约束的体现 默认值和规则等是用户定义的完整性约束的体现 下面对SQLServer2000中实现用户定义完整性的方法予以介绍 检查 CHECK 检查约束使用逻辑表达式来限制列上可以接受的数据 比如 可以指定Books表中的定价必须大于零 这样当插入表中的图书记录的定价为0或负数时 插入操作不能成功执行 从而保证了表中数据的正确性 可以在一列上使用多个检查约束 在表上建立的一个检查约束也可以在多个列上使用 默认值 DEFAULT 数据库中每一行中的每一列都应该有一个值 当然这个值也可以是空值 但有时向一个表中添加数据 添加一行记录 时 某列的值不能确定 或该列的值大量重复的取同一个值 这时可以将该列定义为允许接受空值或给该列定义一个默认值 空值 NULL 空值 NULL 意味着数据的值不确定 比如 Books表中某一行的 出版社 列为空值 并不表示该书没有出版社 而是表示目前还不知道它的出版社 又如 表Books中的书名列设置为不允许取空值 则输入数据时 必须给该列指定非空值 否则输入失败 使用以上这些约束实施的完整性被称为声明型数据完整性 它们是作为表和列定义的一部分在语法中实现的 可以在CREATETABLE语句或ALTERTABLE语句中定义或修改 5 2 2创建表使用企业管理器创建表利用企业管理器提供的图形界面来创建表 步骤如下 1 在树形目录中展开 服务器组 服务器 数据库 MyDb 2 选择 表 单击鼠标右键 在弹出的快捷菜单中选择 新建表 命令 打开设计表对话框 3 如图5 10所示 设计表对话框的上半部分有一个表格 在这个表格中输入列的列名 数据类型 长度 有的数据类型不需要指定长度 如datetime类型的长度为固定值8 是否可以为空 在允许空域中单击鼠标左键 可以切换是否允许为空值的状态 打勾说明允许为空值 空白说明不允许为空值 默认状态是允许为空值的 4 图5 10所示的设计表对话框的下半部分是特定列的详细属性 包括是否是标识列 是否使用默认值等 5 用图5 10所示的方法逐个定义好表中的列 6 设置主键约束 选中要作为主键的列 单击工具条上的 设置主键 按钮 主键列的前上方将显示钥匙标记 如图5 11所示 7 鼠标右键单击表中的任意一行 即任意一个列的定义 在弹出的快捷菜单中选择 属性 命令 可以打开如图5 12所示的表属性对话框 在该对话框中选择表选项卡 可以指定表的属性 比如表名 所有者 表的标识列等 图5 12中将表的名称设置为Readers 所有者设置为dbo 8 在属性对话框中选择 关系 选项卡 可以设置列上的外键约束 选择 索引 键 选项卡 可以设置列上的索引 以及主键约束和惟一性约束 选择 check约束 选项卡 可以设置列上的检查约束 9 定义好所有列后 单击图5 11工具栏上的按钮 表就创建完成了 图5 10创建表对话框 图5 11将编号设为主键 图5 12指定表的属性 创建惟一性约束的步骤如下 1 在如图5 12所示的属性对话框的 索引 键 选项卡中 单击 新建 按钮 2 在列名列表中选择要定义惟一性约束 3 选中 创建UNIQUE 复选框 表示创建惟一性约束 4 在索引名框中输入约束名 或接受默认的名字 创建外键约束的步骤如下 1 在图5 12所示的属性对话框的 关系 选项卡中 单击 新建 按钮 2 在 外键表 下拉列表框中选择要定义外健约束的表 并在其下的列表中选择表中要定义外键约束的列 3 在 主键表 下拉列表框中选择外键引用的表 并在其下的列表中选择表中外键引用的列 4 在 关系名 框中输入约束的名称 或接受默认的名称 5 选择 级联更新相关的字段 复选框指定使用级联修改 6 选择 级联删除相关的记录 复选框指定使用级联删除 创建检查约束的步骤如下 1 在如图5 12所示的属性对话框的 check约束 选项卡中 单击 新建 按钮 2 在 约束表达式 框中输入检查表达式 3 在 约束名 框中输入约束的名称 或接受默认的名称 使用Transact SQL命令创建表在Transact SQL中 使用CREATETABLE命令创建表 语法格式如下 CREATETABLE database name owner owner Table name 列定义 column nameAScomputed column expression 计算列定义 n ON filegroup DEFAULT TEXTIMAGE ON filegroup DEFAULT 例5 9 创建图书信息表Books CREATETABLEbooks 编号char 15 PRIMARYKEYNOTNULLCONTRAINTPK BooksPRIMARYKEY 书名varchar 42 NULL 作者varchar 8 NULL 出版社varchar 28 NULL 定价realNULLCONTRAINTCK BooksCHECK 定价 0 例5 10 创建图书借阅信息表Borrowinf CREATETABLEborrowinf 读者编号char 8 NOTNULLREFERENCEReaders 编号 ONDELETECASCADE 图书编号char 15 NOTNULLFOREIGNKEY 图书编号 REFERENCESBooks 编号 ONDELETENOACTION 借期datetimeNULLDEFAULT getdate 还期datetimeNULL ONDELETE子句有两个选项 NOACTION 被其他表外键引用的行不能被删除 这是SQLServer7 0以前版本的做法 CASCADE 被其他表外键引用的行可以被删除 而且其他表中通过外键引用了该行的行都将被删除 例5 11 创建读者信息表readers CREATETABLEreaders 编号char 10 NOTNULLPRIMARYKEY 姓名char 8 NULL 读者类型intNULL 限借阅数量intNULL 借阅期限intNULL 5 2 3修改表可以使用ALTERTABLE语句或企业管理器进行表的修改 ALTERTABLE命令格式ALTERTABLEtable name ALTERCOLUMNcolumn name new data type precision scale NULL NOTNULL ADD DROP ROWGUIDCOL ADD column nameAScomputed column expression n WITHCHECK WITHNOCHECK ADD n DROP CONSTRAINT constraint name COLUMNcolumn n CHECK NOCHECK CONSTRAINT ALL constraint name n 修改列属性表中的每一列都有列名 数据类型 数据长度以及是否允许为空值等属性 这些属性可以在表创建后修改 修改列属性使用子句ALTERCOLUMN 比如 以下的语句将rooks表的出版社列改为最大长度为30的varchar型数据 且不允许空值 ALTERTABLEBooksALTERCOLUMN出版社varchar 30 NOTNULL默认状态下 列是被设置为允许空值的 将一个原来允许空值的列改为不允许空值 必须满足以下两个条件 表中记录在该列上均不为空值 该列上没有创建索引 添加和删除列向表中增加 列时 应使新增加的列有默认值或允许为空值 如果既没有提供默认值也不允许为空值 那么新增列的操作将出错 向表中添加列需要使用ALTERTABLE的ADD子句 例如 向表books中添加出版日期列 DataTime型 允许空值 可以使用如下语句 ALTERTABLEbooksADD出版日期datetimeNULL又如 向readers表中添加电子邮件地址列 且要求输入的电子邮件地址必须包含 符号 可以使用以下语句 ALTERTABLEreadersADD邮件地址varchar 50 NULLCONSTRAINTCK EACHECK 邮件地址like 删除 列需要使用ALTERTABLE的子句 比如 删除readers表的邮件地址列 可以使用以下语句 ALTERTABLEreadersDROPCOLUMN邮件地址修改约束如果创建表borrowinf时没有定义主键约束 该表的主键由读者编号 图书编号 借期3个属性列组成 则可以用以下语句定义主键 ALTERTABLEReadersADDCONSTRAINTPK BH表中已有的数据在这几列上需要满足以下两个条件 不能有重复的数据 不能有空值 删除主键约束PK BH 可以使用以下语句 ALTERTABLEReadersDROPCONSTRAINTPK BHPRIMARYKEY 读者编号 图书编号 借期 修改约束时几个子句的说明 1 WITHNOCHECK子句添加的约束只对在以后改变或插入的行发生作用 而不检查已存在的行 比如 要对books表的定价列添加约束 使定价大于O 可以使用如下代码 ALTERTABLEBooksWITHNOCHECKADDCONSTRAINTCK Books 定价 0 2 NOCHECKCONSTRAINT子句和CHECKCONSTRAINT子句可以在ALTERTABLE语句中使用NOCHECKCONSTRAINT子句 使表的指定列上的检查约束无效 此时就可以添加一些不满足原来约束要求的数据了 使用CHECKCONSTRAINT子句可以使检查约束重新有效 这两个子句后面都要用约束名作为参数 例 为readers表添加了邮件地址列 并定义了检查约束使邮件地址必须包含 符号 邮件地址列的定义如下 ALTERTABLEreadersADD邮件地址varchar 50 NULLCONSTRAINTCK EACHECK 邮件地址like 使用以下语句使这一约束无效 ALTERTABLEReadersNOCHECKCONSTRAINTCK EA为了使约束重新有效 可使用如下语句 ALTERTABLEReadersCHECKCONSTRAINTCK EA 5 2 4向表中添加数据创建表只是建立了表结构 之后 应该向表中添加数据 只有SystemAdministrator角色成员 数据库和数据库对象所有者及其授权用户才能向表中添加数据 在添加数据时 对于不同的列数据类型 插入数据的格式不一样 因此 应严格遵守它们各自的要求 添加数据按输入顺序保存 条数不限 只受存储空间的限制 使用企业管理器添加数据启动企业管理器后 展开 数据库 文件夹 再展开要添加数据的数据库 如MyDb 可以看到所有的数据库对象 单击 表 用鼠标右击右边列表中要操作的表 例如readers 运行弹出快捷菜单中的 打开表 命令 在弹出的子菜单中单击 返回所有行 命令 打开该表的数据窗口 如图5 13所示 在数据窗口中 用户可以添加多行新数据 同时还可以修改表中数据 使用该窗口的快捷菜单 可以实现表中数据各行记录间跳转 剪贴 复制和粘贴等 图5 13表Readers的数据窗口 使用Transact SQL命令添加数据在Transact SQL中 使用INSERT命令添加数据 它主要有以下几种形式 INSERT VALUES 使用VALUES子句为所添加的数据行提供列值 INSERT SELECT 将SELECT语句的查询结果添加到到表中 INSERT DEFAULTVALUES 将每列的默认值赋予新添加的数据行 INSERT命令的主要语句格式如下 INSERT INTO table name view name column list VALUES DEFAULT NULL expression n select statement DEFAULTVALUES 添加数据到一行中的所有列INSERTreadersVALUES 2004060001 王晓奇 3 2 为表readers添加一行数据 INSERT语句中无须罗列表中的列名 但VALUES提供的数据与Readers表的各列在数量 顺序及数据类型各方面要一一对应 添加数据到一行中的部分列INSERTReaders 编号 姓名 读者类型 VALUES 2004060003 李亚茜 3 在此情况下 INSERT语句中必须罗列表中的列名 并且在添加数据前应该确认没有被添加的列上应设有默认值或NULL 否则这条INSERT语句出错 语句中的列名的顺序只要与VALUES提供的数据一一对应即可 使用INSERT SELECT语句添加数据使用INSERT子句可以将从一个或多个表或视图查询出的数据添加到表中 它可以添加多行数据 而VALUES子句只能添加一行数据 但它们都可以给数据行的全部列或部分列添加数据 需要注意的是被插入数据的表与SELECT子句的结果集必须兼容 即列数 列序 数据类型都应兼容 使用INSERT DEFAULTVALUES语句添加数据使用INSERT DEFAULTVALUES子句能够向表中添加一行数据 语句为 INSERTINTOtable nameDEFAULTVALUES添加的数据行各列均为默认值 未设默认值的列 列值为NULL 5 2 5修改和删除表中的数据修改表中的数据修改表中的数据 最方便的方法是使用企业管理器 类似于添加数据 打开表的数据窗口 如图5 13所示 就可以修改表中的所有数据了 但一些少量 分散的数据修改用企业管理器较合适 成批数据的修改用UPDATE语句更好 语句格式如下 UPDATE table name view name SET column name expression DEFAULT NULL n WHERE 修改所有数据行的值 例5 12 利用UPDATE修改借阅情况表borrowinf的应还日期为自借期开始后的30天之内 语句为 UPDATEboorowinfSET应还日期 借期 30修改部分数据行的值 例5 13 将读者类型表type中的所有学生读者的限借阅数量增加5 借阅期限增加30天 所有教师读者的限借阅数量增加10 借阅期限增加60天 可用以下两个语句实现 UPDATEtypeSET限借阅数量 限借阅数量 5 借阅期限 借阅期限 30WHERE类型名称 学生 UPDATEtypeSET限借阅数量 限借阅数量 10 借阅期限 借阅期限 60WHERE类型名称 教师 删除表中的数据删除表中的数据 是指删除表中某些数据行 用Transact SQL中的DELETE命令来实现删除操作 命令的主要语句格式如下 DELETE FROM table name view name WHERE 例5 14 删除borrowinf表中的读者编号为 2002060328的读者的借书记录 DELETEborrowinfWHERE读者编号 2002060328 例5 15 删除readers表中的所有记录 DELETEreaders 5 2 6删除表删除一个表时 它的结构定义 数据 约束 索引都将被永久地删除 如果一个表被其它表通过FOREIGNKEY约束引用 那么必须先删除定义FOREIGNKEY约束的表 或删除其FOREIGNKEY约束 当没有其他表引用它时 这个表才能被删除 否则 删除操作就会失败 删除一个表可以使用企业管理器或Transact SQL语句 使用企业管理器使用企业管理器删除一个表非常简单 只需展开 服务器组 服务器 数据库 表 在右边的窗口中用鼠标右击要删除的表 在弹出的快捷菜单中选择 删除 选项 如果确定要删除该表 则在弹出的 除去对象 对话框中单击 全部除去 按钮 便完成对表的删除 使用Transact SQL语句使用DROPTABLE语句可以删除表 其语法为 DROPTABLEtable name需要注意的是DROPTABLE语句不能用来删除系统表 例5 17 删除读者类型表type DROPTABLEtype 5 2 7视图的创建 修改和删除基本知识视图常用于集中 简化和定制显示数据库中的信息 视图象是一个过滤器 对于一个或多个基表中的数据进行筛选 引用 每个数据库中的sysobjects系统表都保存了视图的有关信息 这个系统表的xtype列描述数据库对象的类型 如字符V表示视图 S表示系统表 U表示用户表 name和id列分别为对象名和对象标识符 这些标识符在整个数据库中是惟一的 另一个保存视图信息的系统表是syscomments 表中text列包含了视图的定义 另外 两个与视图有关的系统存储过程sp helptext能够显示某个视图的定义 sp rename能够在必要时更改视图的名 或任何其它的数据库对象名 一般对于视图的查询不受任何限制 但要通过视图来修改基表的数据则有一些限制 创建视图在SQLServer中创建视图主要有以下3种方法 使用向导 使用企业管理器 使用Transact SQL命令 使用向导创建视图 1 启动企业管理器 展开要操作的数据库服务器 在企业管理器的 工具 菜单中选择 向导 项 打开 选择向导 对话框 展开 数据库 文件夹 单击 创建视图向导 项 如图5 14所示 单击 确定 按钮 打开创建视图的欢迎对话框 这个对话框向用户介绍了利用创建向导创建视图的主要步骤 2 单击 下一步 按钮 打开数据库选择对话框 视图必须创建在一个数据库中 在数据库下拉列表中选择所需要的数据库 如MyDb 3 单击 下一步 按钮 打开数据表选择对话框 此对话框中可以选择一个或多个创建视图时被引用的数据表 单击数据表右侧的复选框 选中所需的数据表如Borrowinf 4 单击 下一步 按钮 打开选择列对话框显示上一步选择的所有表中的列名 列的数据类型和选择状态 选择列 单击选择状态栏的复选框中选择视图中需要显示的列 如图5 15所示 5 单击 下一步 按钮 打开限制条件输入对话框 在这个对话框的文本框中输入Transact SQL命令 即WHERE子句的内容 用于限制视图的输出结果 这里不输入内容 如图5 16所示 如果不输入任何内容 则视图将所有选中表中选中列的所有数据行全部显示 6 单击 下一步 按钮 打开视图名输入对话框 在输入文本框中输入创建的视图名 如borrowinf view 7 单击 下一步 按钮 打开对话框如图5 17所示 在这个对话框中系统根据上面几步用户定义视图时的所有选择 形成定义视图的Transact SQL语句 用户可以直接在这个对话框中修改视图定义 最后单击 完成 按钮 视图在所选的数据库中创建完成 图5 14选择创建视图图5 15选择列对话框 图5 16限制条件输入对话框图5 17完成定义视图对话框 使用企业管理器创建视图创建视图 1 启动企业管理器 展开要使用的数据库 如MyDB 2 鼠标右击该数据库中的 视图 图标 在弹出的菜单中选择 新建视图 命令 打开的窗口如图5 18所示 右键单击图标窗格打开 添加表 对话框 如图5 19所示 3 基表或视图选择完成之后 表books的结构出现在视图创建 修改窗口的数据表显示区 在表中选择需要在视图中显示的列 此时在窗口下边的视图定义列显示表格中和SQL语句区中也会相应地出现所选择的列和SQL语句 如需加入限制条件 函数或计算列 可以手动在SQL语句区输入 如图5 20所示 4 单击工具栏按钮运行所定义的视图 在视图执行结果显示区显示出这个视图的查询结果 如图5 20下部分所示 用户可以发现当定义了WHERE子句时 视图执行之后 在基表相应列的右边会出现一个图标 并且在视图定义列的表格中出现相应的列名与准则 即视图的查询条件 图5 18新打开的创建视图对话框 图5 19添加表对话框 图5 20创建视图对话框 使用Transact SQL命令创建视图在T SQL中 使用CREATEVIEW命令创建视图 格式为 CREATEVIEWview name column n WITHENCRYPTION ASSelect statement WITHCHECKOPTION 命令中的参数含义如下 WITHENCRYPTION 给系统表syscomments中视图定义的SELECT命令加密 AS 说明视图要完成的操作 Select statement 定义视图的SELECT命令 需要特别注意 CREATEVIEW必须是批中的惟一语句 而且视图中的SELECT命令不能包括INTO ORDERBY等子句 另外 临时表也不能在查询中引用 WITHCHECKOPTION 强制所有通过视图修改的数据满足select Statement语句中指定的选择条件 例5 15 利用表books定义视图v1 books 查询books表中的作者 书名的情况 CREATEVIEWv1 booksASSELECT作者 书名FROMBooks可利用sp helptext来查看视图的定义 例如命令 sp helptextv1 books的执行结果是视图v1 books的定义 见图5 21 图5 21查看视图 例5 16 带条件的视图定义 定义视图v overdue 查询所有借阅超期的读者的读者编号 姓名 书名和应还日期 该视图的定义涉及到了borrowinf表 books表和readers表 CREATEVIEWv overdueASSELECT读者编号 姓名 书名 应还日期FROMborrowinfb readersr booksWHEREb 读者编号 r 编号andb 图书编号 books 编号andb 应还日期 getdate 视图定义后 可以向基本表一样查询 例如 若要查询视图v overdue 可以使用如下命令 SELECT FROMv overdue 例5 17 定义视图v overdue1 查询借书超期的读者姓名等情况 要求加密视图定义 该视图定义涉及到了borrowinf表及readers表 CREATEVIEWv overdue1WITHENCRYPTIONASSELECT读者编号 r 姓名 图书编号 应还日期FROMborrowinfbinnerjoinreadersrOnb 读者编号 r 编号WHERE应还日期 getdate 在安装系统和创建数据库之后 只有系统管理员sa和数据库所有者DBO具有创建视图的特权 此后他们可以使用GRANTCREATEVIEW命令将这个特权授予其他用户 此外 视图创建者必须具有在视图查询中包括的每一列的访问权 使用企业管理器修改视图视图建立之后 由于某种原因 如基表中的列发生了改变或需要在视图中增 删若干列等 需要对视图进行修改 利用企业管理器修改视图的步骤如下 1 展开服务器 展开数据库 2 单击 视图 图标 在窗口右边显示出当前数据库中的所有视图 3 右键单击要修改的视图名 在弹出的快捷菜单中选择 设计视图 命令 便可进入视图设计窗口 用户可以在这个窗口中对视图进行修改 这种方法不能修改加密过的视图定义 即使是管理员或数据库的所有者也不可以 还有一种利用视图属性对话框修改视图的方法 用鼠标双击要修改的视图名 弹出视图属性对话框 在这个对话框中显示出定义视图的Transact SQL命令 用户可以直接在这个对话框中修改 但这种方法同样只适用于未被加密的视图 使用Transact SQL命令修改视图在Transact SQL中 使用ALTERVIEW命令修改视图语法格式为ALTERVIEWview name column n WITHENCRYPTION ASselect statement WITHCHECKOPTION ALTERVIEW命令通常用于修改视图查询的定义 语法几乎等同于CREATEVIEW命令的语法 可以使用ALTERVIEW避免为视图重新指定已有的特权 使用这个命令修改已有的视图不影响依赖于视图的存储过程 另外 如果使用DROPVIEW和CREATEVIEW命令先删除再重新创建视图 使用视图的存储过程 或其他应用 将不能很好地工作 如果在创建视图时使用了WITHENCRYPTION选项和WITHCHECKOPTION选项 则在使用ALTERVIEW命令时 也必须包括这些选项 例5 18 修改 例5 16 中的视图v overdue ALTERVIEWv overdueWITHENCRYPTIONASSELECT读者编号 图书编号 应还日期FROMborrowinfWHERE应还日期 getdate 使用视图对于视图的使用主要包括视图的检索 通过视图对基表进行插入 修改 删除行的操作 对于视图的检索几乎没有什么限制 但是对通过视图实现表的插入 修改 删除操作则有一定的限制条件 视图检索视图定义之后 用户便可以像查询基本表一样查询视图 视图的查询总是转换为对它所依赖的基表的等价查询 利用Transact SQL的SELECT命令和企业管理器都可以对视图查询 其使用方法与基本表的查询完全一样 这里不再赘述 利用视图操作基表的数据视图也可以使用INSERT命令插入行 当执行INSERT命令时 实际上是向视图所引用的基表中插入了行 视图中的INSERT命令与基本表中使用INSERT命令格式完全一样 例5 19 利用视图向表borrowinf中加入一条数据 borrowinf VIEW是前面创建的视图 脚本如下 CREATEVIEWborrowinf VIEWASSELECT读者编号 图书编号 还期FROMborrowinf执行下面脚本 INSERTINTOborrowinf VIEWVALUES 2004160426 F12 245 2003 12 20 查看结果 SELECT FROMborrowinfWHERE图书编号 F12 245 图5 23执行添加命令 如果视图中有下面所述属性 则插入 更新或删除基表将失败 视图定义中的FROM子句包含两个或多个表 且SELECT选择列表达式中的列表包含来自多个表的列 视图的列是从集合函数派生的 视图中的SElECT命令包含GROUPBY子句或DISTINCT选项 视图的列是从常量或表达式派生的 同样 利用视图可以更新行 改变所引用的基表中一个或多个行或列的值 例5 20 将 例5 19 中插入的数据删除 DELETEFROMborrowinf VIEWWHERE图书编号 F12 245 这个例子执行后将基表borrowinf中的所有图书编号为 F12 245 的行删除 删除视图视图创建后 随时都可以删除 删除操作很简单 通过企业管理器或DROPVIEW命令都可以完成 利用企业管理器删除视图操作步骤如下 1 在当前数据库中单击视图图标 2 在右边视图列表窗口内单击需删除的视图 3 单击工具栏上的删除图标 弹出删除视图对话框 如图5 24所示 4 单击 全部除去 按钮 将选中的视图删除 在删除视图对话框中 单击 显示相关性 按钮显示对象的相关性 如果某视图在另一视图定义中被引用 当删除这个视图后 如果调用另一视图 则会出现错误提示 图5 24删除视图对话框 利用DROPVIEW命令删除视图语法格式为DROPVIEW view n DROPVIEW命令可以删除多个视图 各视图名之间用逗号分隔 视图删除后 在系统表sysobjects syscolumns syscomments sysdepnds和sysprotects中存储的关于视图的信息也会被随之删除 对视图的权限同样被删除 例5 21 删除视图borrowinf VIEW DROPVIEWborrowinf VIEW 5 2 8索引SQLServer的索引是一种物理结构 它能够提供一种以一列或多列的值为基础迅速查找表中行的能力 本小节介绍索引的分类 索引的创建 修改和删除 索引的类型SQLServer2000中提供了以下几种索引 聚簇索引在聚簇索引中 行的物理存储顺序与索引顺序完全相同 即索引的顺序决定了表中行的存储顺序 因为行是经过排序的 所以每个表只能有一个聚簇索引 由于聚簇索引的顺序与数据行存放的物理顺序相同 所以聚簇索引最适合范围搜索 因为找到一个范围内开始的行后可以很快地取出后面的行 如果表中没有创建其他的聚簇索引 则在表的主键列上自动创建聚簇索引 非聚簇索引非聚簇索引并不在物理上排列数据 即索引中的逻辑顺序并不等同于表中行的物理顺序 索引仅仅记录指向表中行的位置的指针 这些指针本身是有序的 通过这些指针可以在表中快速地定位数据 非聚簇索引作为与表分离的对象存在 所以 可以为表中每一个常用于查询的列定义非聚簇索引 非聚簇索引的特点使它很适合于那种直接匹配单个条件的查询 而不太适合于返回大量结果的查询 比如readers表的姓名列上就很适合建立非聚簇索引 为一个表建立索引默认都是非聚簇索引 在一列上设置惟一性约束也自动在该列上创建非聚簇索引 惟一性索引 UniqueIndexes 聚簇索引和非聚簇索引是按照索引的结构划分的 按照索引实现的功能还可以划分为惟一性索引和非惟一性索引 惟一性索引能够保证在创建索引的列或多列的组合上不包括重复的数据 聚簇索引和非聚簇索引都可以是惟一性索引 在创建主键约束和惟一性约束的列上会自动创建惟一性索引 创建索引在SQLServer中 索引有如下几个限制 只有表的拥有者才能在表上创建索引 每个表上只能创建一个聚簇索引 每个表上最多能创建249个非聚簇索引 一个索引最大为900字节 在char等类型的列上创建索引应考虑这一限制 另外 在多列上创建索引时要注意字节数的总和不要超过900 一个索引中最多包含的列数为16 另外 在创建聚簇索引时还要考虑数据库剩余空间的问题 在建立聚簇索引时所需要的可用空间应是数据库表中数据量的120 这是因为在