cs5SQLServer2000数据库基本操作.ppt

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_nameONPRIMARY,n，FILEGROUPfilegroup_name，.nLOGON，.nFORLOAD|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数据库属性对话框,使用TransactSQL命令修改数据库ALTERDATABASEdatabaseADDFILE，n)TOFILEGROUPfilegroup_nameADDLOGFILE，nREMOVEFILElogical_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:mssql2000exampledbl_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:mssql2000exampledbl_log21df),修改数据库名系统存储过程sp_renamedb(在master系统数据库中)能够修改数据库的名称。在使用sp_renamedb修改数据库名之前，必须将数据库设置成单用户模式。设置数据库为单用户模式，用户可以在图5-7所示的数据库属性对话框中选择【选项】，在弹出的对话框中选择【限制访问】，再选中该数据库的【单用户】选项。用户也可以使用系统存储过程sp_dboption(在master系统数据库中)设置【单用户】选项。下述命令行是将exampledbl数据库更名为exampledb的完整过程,用sp_helpdb来查看。见图5-8。,图5-8数据库更名过程,5.1.4删除数据库使用企业管理器删除数据库使用企业管理器删除数据库非常简单，但每次只能删除一个数据库。方法是启动企业管理器后，展开【数据库】文件夹，单击要删除的数据库后如exampledb2，用鼠标右键单击要删除的数据库名，从弹出的快捷菜单中单击【删除】命令，系统弹出警告对话框见图5-9，要求用户确认是否删除该数据库，单击【是】按钮，就删除了该数据库。使用TransactSOL命令删除数据库DROPDATABASEdatabase_name，ndatabase_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命令创建表。语法格式如下：CREATETABLEdatabase_name.owner.|owner.Table_name(-列定义|column_nameAScomputed_column_expression-计算列定义|,n)ONfilegroup|DEFAULTTEXTIMAGE_ONfilegroup|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_nameALTERCOLUMNcolumn_namenew_data_type(precision,scale)NULL|NOTNULL|ADD|DROPROWGUIDCOL|ADD|column_nameAScomputed_column_expression,.n|WITHCHECK|WITHNOCHECK|ADD,.n|DROPCONSTRAINTconstraint_name|COLUMNcolumn,.n|CHECK|NOCHECKCONSTRAINTALL|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命令添加数据，它主要有以下几种形式：INSERTVALUES:使用VALUES子句为所添加的数据行提供列值。INSERTSELECT：将SELECT语句的查询结果添加到到表中。INSERTDEFAULTVALUES:将每列的默认值赋予新添加的数据行。INSERT命令的主要语句格式如下：INSERTINTOtable_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提供的数据一一对应即可,使用INSERTSELECT语句添加数据使用INSERT子句可以将从一个或多个表或视图查询出的数据添加到表中，它可以添加多行数据，而VALUES子句只能添加一行数据，但它们都可以给数据行的全部列或部分列添加数据。需要注意的是被插入数据的表与SELECT子句的结果集必须兼容，即列数、列序、数据类型都应兼容。使用INSERTDEFAULTVALUES语句添加数据使用INSERTDEFAULTVALUES子句能够向表中添加一行数据，语句为：INSERTINTOtable_nameDEFAULTVALUES添加的数据行各列均为默认值，未设默认值的列，列值为NULL。,5.2.5修改和删除表中的数据修改表中的数据修改表中的数据，最方便的方法是使用企业管理器。类似于添加数据，打开表的数据窗口，如图5-13所示，就可以修改表中的所有数据了。但一些少量、分散的数据修改用企业管理器较合适，成批数据的修改用UPDATE语句更好。语句格式如下：UPDATEtable_name|view_nameSETcolumn_name=expression|DEFAULT|NULL,.nWHERE,修改所有数据行的值【例5-12】利用UPDATE修改借阅情况表borrowinf的应还日期为自借期开始后的30天之内，语句为：UPDATEboorowinfSET应还日期=借期+30修改部分数据行的值【例5-13】将读者类型表type中的所有学生读者的限借阅数量增加5、借阅期限增加30天，所有教师读者的限借阅数量增加10、借阅期限增加60天，可用以下两个语句实现：UPDATEtypeSET限借阅数量=限借阅数量+5,借阅期限=借阅期限+30WHERE类型名称=学生UPDATEtypeSET限借阅数量=限借阅数量+10,借阅期限=借阅期限+60WHERE类型名称=教师,删除表中的数据删除表中的数据，是指删除表中某些数据行。用Transact-SQL中的DELETE命令来实现删除操作，命令的主要语句格式如下：DELETEFROMtable_name|view_nameWHERE【例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)WITHENCRYPTIONASSelect_statementWITHCHECKOPTION命令中的参数含义如下: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)WITHENCRYPTIONASselect-statementWITHCHECKOPTIONALTERVIEW命令通常用于修改视图查询的定义，语法几乎等同于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命令删除视图语法格式为DROPVIEWview，nDROPVIEW命令可以删除多个视图，各视图名之间用逗号分隔。视图删除后，在系统表sysobjects、syscolumns、syscomments、sysdepnds和sysprotects中存储的关于视图的信息也会被随之删除，对视图的权限同样被删除。【例5-21】删除视图borrowinf_VIEW。DROPVIEWborrowinf_VIEW,5.2.8索引SQLServer的索引是一种物理结构，它能够提供一种以一列或多列的值为基础迅速查找表中行的能力。本小节介绍索引的分类、索引的创建、修改和删除。索引的类型SQLServer2000中提供了以下几种索引：聚簇索引在聚簇索引中，行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序。因为行是经过排序的，所以每个表只能有一个聚簇索引。由于聚簇索引的顺序与数据行存放的物理顺序相同，所以聚簇索引最适合范围搜索。因为找到一个范围内开始的行后可以很快地取出后面的行。如果表中没有创建其他的聚簇索引，则在表的主键列上自动创建聚簇索引。,非聚簇索引非聚簇索引并不在物理上排列数据，即索引中的逻辑顺序并不等同于表中行的物理顺序。索引仅仅记录指向表中行的位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速地定位数据。非聚簇索引作为与表分离的对象存在，所以，可以为表中每一个常用于查询的列定义非聚簇索引。非聚簇索引的特点使它很适合于那种直接匹配单个条件的查询，而不太适合于返回大量结果的查询。比如readers表的姓名列上就很适合建立