第7章 　SQL Server 2005和T-SQL 　　 　　 　　 　　本章讲解的是ASP和SQL Server2005的结合应用，前面五章详细讲解了ASP的基础知识，从下一章开始就要讲解具体的实例了。对于没有数据库基础的读者，后面章节的学习将会倍感吃力。因此，本章介绍了SQL Server2005的相关知识，并且简单明了地介绍了T-SQL语言的使用。通过本章的学习，相信在后面章节的学习过程中会感觉轻松。 7.1 SQL Server 2005和T-SQL 　　SQL Server是微软公司开发的大型关系型数据库管理系统，具有非常强大的创建、开发、设计及管理功能，它在各个行业中得到了广泛应用，成为众多数据库产品中的杰出代表。下面将简单介绍SQL Server 2005数据库的应用和T-SQL语言的组成。 7.1.1 SQL Server 2005的应用 　　在SQL Server2005中最为常用的操作为创建数据库、创建数据表，创建索引、创建视图、创建存储过程、安全性管理等操作。下面分别介绍这些操作在SQL Server 2005中的应用。 　　1．创建数据库 　　在SQL Server 2005中创建数据库的过程比较简单，可通过数据库创建向导来实现数据库的创建过程。下面具体介绍创建数据库的步骤。 　　(1) 打开SQL Server 2005资源管理器，在窗体左侧菜单中右击“数据库”，在弹出的快捷菜单中选择“新建数据库”命令，弹出“新建数据库”窗体，如图7-1所示。 图7-1 新建数据库窗体 　　(2) 若要在这里创建一个名称为userdb的数据库，则在“数据库名称”文本框中输入userdb，如图7-2所示。 图7-2 填写数据库名称 　　(3) 在数据库文件列表框中可设置数据库的初始大小、自动增长量等信息，将初始大小设置5，单击自动增长后面的按钮，打开“更改自动更新”对话框。 　　(4) 在此对话框中选中“限制文件增长”单选按钮，设置数值为10，即将数据库文件最大设置为10M，如图7-3所示。 图7-3 设置自动增长值 　　(5) 设置完成后，单击“确定”按钮，完成数据库的创建。 　　2．创建数据表 　　在完成数据库的创建后，接下来就需要进行数据表的创建过程。下面介绍在SQL Server 2005中数据表的创建。 　　(1) 打开新建的userdb数据库，右击表，在弹出的快捷菜单中选择“新建表”选项。 　　(2) 打开数据表创建窗体，在列名中输入数据列名称，例如输入id，在数据类型中选择该数据列的数据类型，例如int，可设置该字段为标识种子，在列属性框中选择标识规范为“是”，如图7-4所示。 图7-4 创建数据表 　　(3) 单击快捷菜单栏中的图标，可设置该列为主键，继续添加其他数据列，允许设置其他列为空，如图7-5所示。 图7-5 数据表对话框 　　(4) 单击保存按钮，弹出“输入表名称”对话框，在该对话框中输入表的名称，例如users，如图7-6所示。 图7-6 输入表名对话框 　　(5) 单击“确定”按钮，即可完成数据表的创建过程。 　　3．管理索引和键 　　在实现了数据表的创建后，接下来就应该设置数据表的索引和键了。下面介绍管理数据表的索引和键的应用步骤。 　　(1) 打开数据表编辑窗体，单击图标，打开“索引/键”对话框，如图7-7所示。 图7-7 “索引/键”对话框 　　(2) 单击“常规”选项下的“列”选项，打开“索引列”对话框，在该对话框中可设置索引的列和排序顺序。此时默认的索引列为id，排序顺序为升序。如图7-8所示。 图7-8 “索引列”对话框 　　(3) 在此对话框中还可以添加此索引的其他列，例如设置name字段值为此索引的第二个列，也设置此列为升序，如图7-9所示。 图7-9 设置索引的其他列 　　(4) 单击“确定”按钮，实现此索引的设置，此时列中将会显示id和name字段，如图7-10所示。此时数据表的name字段被设置成为主键值，如图7-11所示。 图7-10 设置完成的索引 图7-11 数据表 　　4．创建视图 　　在实现数据表的创建和设置后，用户可以选择创建视图，关于视图的应用将会在下面介绍。下面介绍在SQL Server 2005中创建视图的步骤。 　　(1) 打开数据库，在“视图”上单击鼠标右键，在弹出的快捷菜单中选择“新建视图”命令，打开“添加表”对话框，如图7-12所示。 图7-12 “添加表”对话框 　　(2) 选择要创建视图时所需要的数据表，这里只有一个数据表，选择该数据表，单击“添加”按钮，然后单击“关闭”按钮，打开“视图”编辑界面，如图7-13所示。 图7-13 视图编辑界面 　　(3) 如果建立的视图要检索该数据表中的所有列，可在数据表中选择“所有列”复选框，如果选择部分列，可选择各列前面的复选框，例如这里选择name、sex、address数据列，如图7-14所示。 图7-14 视图编辑 　　(4) 单击按钮，打开“选择名称”对话框，在此对话框中编辑视图名称，如图7-15所示。 图7-15 编辑视图名称 　　(5) 将视图名称定义为View_1，单击“确定”按钮，完成视图的创建过程。 　　5．创建存储过程 　　下面介绍SQL Server 2005中存储过程的创建步骤，关于存储过程的具体编程方法，请读者参考本章7-3节中的内容。 　　打开数据库选择“可编程性”|“存储过程”命令，单击鼠标右键，在弹出的快捷菜单中选择“新建存储过程”命令，打开存储过程编辑界面，如图7-16所示。在此界面中可编辑存储过程。 图7-16 存储过程编辑界面 　　6．安全性管理 　　数据库安全性的管理为数据库用户管理，在介绍数据库用户前首先介绍SQL Server 2005中各用户角色所具有的功能，如表7-1所示。 表7-1 用 户 角 色 角 色 名 称 角 色 功 能 db_accessadmin 为固定数据库角色的成员可以为Windows登录账户、Windows组和SQL Server登录账户添加或删除访问权限 db_backupoperator 为固定数据库角色的成员可以备份该数据库 db_datareader 为固定数据库角色的成员可以读取所有用户表中的所有数据 db_datawriter 为固定数据库角色的成员可以在所有用户表中添加、删除或更改数据 db_ddladmin 为固定数据库角色的成员可以在数据库中运行任何数据定义语言(DDL)命令 db_denydatareader 为固定服务器角色的成员不能读取数据库内用户表中的任何数据 db_denydatawriter 为固定服务器角色的成员不能添加、修改或删除数据库内用户表中的任何数据 db_owner 为固定数据库角色的成员可以删除数据库。此行为是从早期版本变化而来的 db_securityadmin 为固定数据库角色的成员可以修改角色成员身份和管理权限 public 所有用户都包括在public角色中。Public成员身份是永久性的，无法更改 　　 　　在了解数据库用户角色后，下面介绍创建数据库用户的步骤。 　　(1) 打开数据库选择“安全性”|“用户”命令，单击鼠标右键，在弹出的快捷菜单中选择“新建用户”命令，打开“数据库用户－新建”窗体，如图7-17所示。 图7-17 “数据库用户－新建”窗体 　　(2) 在用户名中输seluser，选择数据库角色成员身份为db_owner，如图7-18所示。 图7-18 设置用户 　　(3) 单击登录名后的按钮，打开“选择登录名”对话框，如图7-19所示。 图7-19 “选择登录名”对话框 　　(4) 单击“浏览”按钮，打开“查找对象”对话框，在此对话框中选择一个匹配的对象，如图7-20所示。 图7-20 “查找对象”对话框 　　(5) 单击“确定”按钮，实现对象名称的添加，如图7-21所示。 图7-21 对象名称添加 　　(6) 单击“确定”按钮，实现登录名添加，如图7-22所示。 图7-22 实现登录名添加 　　(7) 单击“确定”按钮，实现该用户的添加。 7.1.2 SQL Server 2005的其他应用 　　SQL Server 2005还有一些常用的基础操作，例如服务的启动和关闭、数据库的备份和恢复、数据的导入与导出。下面就介绍这些方法的应用。 　　1．服务的启动和关闭 　　数据库服务的启动与关闭可通过“服务”窗体进行管理，下面介绍启动和关闭数据库的应用方法。 　　(1) 单击“控制面板”|“管理工具”窗体中的“服务”图标，打开“服务”窗体，在窗体中选择名称为SQL Server的服务，如图7-23所示。 图7-23 选择SQL Server 　　(2) 在SQL Server服务上单击鼠标右键，在弹出的快捷菜单中选择“属性”命令，如图7-24所示。 　　(3) 打开“SQL Server的属性”对话框，在“启动类型”列表框中可选择该数据库服务的启动类型，包含自动、手动和已禁用三个选项，如图7-25所示。 　　　　图7-24 属性命令 图7-25 “SQL Server的属性”对话框 　　(4) 当服务的状态为未启动时，“启动”按钮为可选状态，单击“启动”按钮，即可启动SQL Server 2005的服务。启动状态，如图7-26所示。 图7-26 启动服务 　　(5) 启动成功后，“启动”按钮为不可选择状态，而“停止”按钮则为可选择状态，如图7-27。 图7-27 启动状态 　　(6) 单击“停止”按钮，即可停止SQL Server 2005的服务。 　　2．附加数据库 　　用户将SQL Server数据库文件从一台计算机移动到另外一台计算机时，可以使用附加数据库的方法，将数据库移植到其他计算机上。下面介绍附加数据库的方法。 　　(1) 打开SQL Server 2005资源管理器，鼠标右键单击“数据库”，在弹出的快捷菜单中选择“附加”命令，如图7-28所示。 图7-28 选择“附加”命令 　　(2) 打开“附加数据库”窗体，单击“添加”按钮，打开“定位数据库文件”窗体，在此窗体中选择要附加的数据文件，如图7-29所示。 图7-29 “定位数据库文件”窗体 　　(3) 单击“确定”按钮，将数据库添加到“附加数据库”窗体中，如图7-30所示。 图7-30 附加数据库 　　(4) 单击“确定”按钮，开始附加数据库的操作，成功后将会看到附加的数据库，如图7-31所示。 图7-31 附加的数据库 　　3．数据库的备份和恢复 　　数据库的备份和恢复是数据库管理员最常用的安全性保护工作。下面介绍数据库备份和恢复的应用。 　　(1) 在要备份的数据库上单击鼠标右键，在弹出的快捷菜单中选择“任务”|“备份”命令，如图7-32所示。 图7-32 备份 　　(2) 打开“备份数据库”窗体，如图7-33所示。 图7-33 “备份数据库”窗体 　　(3) 单击“确定”按钮，即可实现数据库的备份操作。操作成功后，将会显示成功对话框，如图7-34所示。 图7-34 备份成功 　　当数据库遭到破坏后，可通过数据库恢复操作，将数据库还原，操作步骤如下。 　　(1) 在数据库上单击鼠标右键，在弹出的快捷菜单中选择“还原数据库”命令，打开“还原数据库”窗体，如图7-35所示。 　　(2) 在“目标数据库”下拉列表框中可选择一个存在的数据库，或者输入一个新数据库名称。在此输入一个新数据库的名称，在“源数据库”下拉列表框中选择一个备份的数据库。如图7-36所示。 图7-35 “还原数据库”窗体 图7-36 设置还原 　　(3) 单击“确定”按钮，完成数据库的还原操作，弹出一个成功对话框，如图7-37所示。 图7-37 还原数据库成功 　　(4) 此时可浏览到还原成功的数据库，如图7-38所示。 图7-38 还原的数据库 　　4．数据的导入与导出 　　在SQL Server 2005中可将数据库中的数据导出为其他格式的文件，例如Access文件，也可将其他格式的文件导入到SQL Server 2005中。下面首先介绍数据的导入操作。 　　(1) 新建一个数据库，命名为blog，在该数据库上单击鼠标右键，在弹出的快捷菜单中选择“任务”|“导入数据”命令，打开“SQL Server导入和导出向导”窗体，如图7-39所示。 图7-39 “SQL Server导入和导出向导”窗体 　　(2) 单击“下一步”按钮，打开“选择数据源”窗体，在“数据源”下拉列表框中选择“Microsoft Access”数据库，单击“浏览”按钮，弹出“打开”对话框，选择Access数据库文件，如图7-40所示。 图7-40 选择数据源 　　(3) 单击“打开”按钮，将数据源添加到窗体中，如图7-41所示。 图7-41 选择数据源 　　(4) 单击“下一步”按钮，打开“选择目标”窗体，在“数据库”下拉列表框中选择blog数据库，如图7-42所示。 图7-42 “选择目标”窗体 　　(5) 单击“下一步”按钮，打开“指定表复制或查询”窗体，在此选择“复制一个或多个表或视图的数据”单选按钮，如图7-43所示。 图7-43 “指定表复制或查询”窗体 　　(6) 单击“下一步”按钮，打开“选择源表和源视图”窗体，单击“全选”按钮，选中所有数据表，如图7-44所示。 图7-44 “选择源表和源视图”窗体 　　(7) 单击“下一步”按钮，打开“保存并执行包”窗体，如图7-45所示。 图7-45 “保存并执行包”窗体 　　(8) 单击“下一步”按钮，打开“完成该向导”窗体，如图7-46所示。 图7-46 “完成该向导”窗体 　　(9) 单击“完成”按钮，开始执行数据导入的操作，如图7-47所示。 图7-47 执行导入 　　(10) 导入完成后，可浏览导入数据表和数据，如图7-48所示。 图7-48 数据导入 　　在介绍了数据导入的操作后，下面介绍数据导出的操作。 　　(1) 在Myjob数据库上单击鼠标右键，在弹出的快捷菜单中选择“任务”|“导出数据”命令，打开“SQL Server导入和导出向导”窗体，如图7-49所示。 图7-49 “SQL Server导入和导出向导”窗体 　　(2) 单击“下一步”按钮，打开“选择数据源”窗体，在“数据源”下拉列表框中选择“SQL Native Client”，在“数据库”下拉列表框中选择“Myjob”，如图7-50所示。 图7-50 选择数据源 　　(3) 单击“下一步”按钮，打开“选择目标”窗体，在“目标”下拉列表框中选择Microsoft Access，单击浏览选择Access数据库，如图7-51所示。 图7-51 “选择目标”窗体 　　(4) 单击“下一步”按钮，打开“指定表复制或查询”窗体，在此选择“复制一个或多个表或视图的数据”单选按钮，如图7-52所示。 图7-52 “指定表复制或查询”窗体 　　(5) 单击“下一步”按钮，打开“选择源表和源视图”窗体，单击“全选”按钮，选中所有数据表，如图7-53所示。 图7-53 “选择源表和源视图”窗体 　　(6) 单击“下一步”按钮，打开“保存并执行包”窗体，如图7-54所示。 图7-54 “保存并执行包”窗体 　　(7) 单击“下一步”按钮，打开“完成该向导”窗体，如图7-55所示。 图7-55 “完成该向导”窗体 　　(8) 单击“完成”按钮，开始执行数据导出的操作，如图7-56所示。 图7-56 执行导出 　　(9) 导出完成后，可浏览导出数据表和数据，如图7-57所示。 图7-57 数据导出结果 7.1.3 T-SQL语言的组成 　　T-SQL语言包括：数据定义语言、数据控制语言、数据操纵语言和系统存储过程。其相应内容如下： ● 数据定义语言(DDL)，是指用来定义和管理数据库以及数据库中的各种对象的语句，这些语句包括CREATE、ALTER和DROP等语句。 ● 数据控制语言(DCL)，是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。 ● 数据操纵语言(DML)，是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。在默认情况下，只有sysadmin、dbcreator、db_owner或db_datawriter等角色的成员才有权利执行数据操作语言。 ● 系统存储过程(System Stored Procedure)，是SQL Server系统创建的存储过程，它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程可以在任意一个数据库中执行。系统存储过程创建并存放于系统数据库master中，并且名称以sp_或者xp_开头。 7.2 表的操作 　　表的操作主要指数据表的查询、添加、修改和删除操作。下面分别介绍这些操作的应用方法。 7.2.1 表的简单查询 　　SELECT是SQL中的查询语句。其基本格式如下： SELECT [ALL | DISTINCT] select_list FROM table_list/view_list [WHERE conditions] [GROUP BY group_list] [HAVING conditions] [ORDER BY order_list] 　　上面语法中各参数的含义如表7-2所示。 表7-2 SELECT语句中参数含义 参 数 描 述 ALL 表示将显示所有检索到的数据 DISTINCT 表示不在结果中显示重复的数据 select_list 列出了所选择列的名称，在检索之后系统将显示符合条件的列的数据 FROM 指定要进行检索的表的名称table_list或视图的名称view_list WHERE 可以指定检索的条件，使SELECT语句按照指定的检索条件在表中检索数据 GROUP BY 指定如何对检索到的数据进行分组，使显示结果按照需要分组 HAVING 可以看成用于组的WHERE子句。HAVING子句用来限制组 ORDER BY 指定如何对检索到的数据进行排序，使显示结果按照需要排序 　　 　　下面的例子检索musiclist表中演唱者为“王菲”的歌曲信息(包括ID、歌曲名称、演唱者和歌词)。 select * from musiclist where singer='王菲' 　　查询结果如图7-58所示。 图7-58 简单查询 7.2.2 表的复杂查询 　　很多时候，上面的查询语句是难以查询出所需的数据的，这就要用到更复杂的查询语句，包括在Where子句中使用运算符、IN等关键字。下面分别进行介绍。 　　1．比较运算符 　　比较运算符在WHERE子句中用于数字之间的比较，还可以用于字符串和时间的比较。SQL支持的比较运算符如表7-3所示。 表7-3 比较运算符 运 算 符 说 明 　　　　　　　　　= 等于 　　　　　　　　　!= 不等于 　　　　　　　　　> 大于 　　　　　　　　　< 小于 　　　　　　　　　>= 大于或等于 　　　　　　　　　<= 小于或等于 　　2．逻辑运算符 　　有时候，在WHERE子句中仅仅使用一个条件并不能准确地从表中检索到需要的数据，为了处理多条件查询，必须使用逻辑运算符，以便把简单的条件组合起来。SQL提供了三个逻辑运算符：AND、OR、NOT。 　　逻辑运算符用在WHERE子句中连接多个查询条件，返回带有 TRUE 或 FALSE 值的布尔数据类型。NOT用于对一个布尔表达式的值取反 ，它通常与LIKE、BETWEEN、IN、NULL、EXISTS等关键词一起使用。AND运算符可以连接两个或两个以上的条件，只有当AND连接的条件都为TRUE(真)时，AND返回的结果才是TRUE(真)。如果其中有一个条件为FALSE(假)，AND返回的值就是FALSE(假)。 　　下面的例子使用比较运算符和逻辑运算符查询musiclist表中Id大于2但小于5的歌曲信息(包括ID、歌曲名称、演唱者和歌词)。 select * from musiclist where id>2 and id<5 　　查询结果如图7-59所示。 图7-59 使用比较运算符 　　3．IN子句 　　在SQL中提供了IN子句来取代多个OR运算符，通过IN子句可以将一个值与其他几个值进行比较。通过 IN(或 NOT IN)引入的子查询结果包含零个或多个值的列表。子查询返回结果之后，外部查询将利用这些结果。 　　IN子句除了可以提高代码的可读性，通常还可以提高查询的性能。如果搜索条件在指定的列上建立了索引，那么IN子句可以使用索引来搜索数据，而OR运算符不能利用列上的索引。 　　IN语句的基本语法如下。 SELECT select_list FROM table_list/view_list WHERE column [NOT] IN(value_list) 　　下面的例子是用IN关键字查询演唱者为“蔡琴”或“周杰伦”的歌曲信息(包括ID、歌曲名称、演唱者和歌词)。 select * from musiclist where singer in('蔡琴','周杰伦') 　　查询结果如图7-60所示。 图7-60 使用IN关键字 　　4．LIKE子句 　　LIKE运算符用于匹配字符串或字符串的一部分(称为子串)。有些系统中可能支持对时间日期使用LIKE子句。“=”或IN运算符的缺陷是，必须知道要匹配的确切值。为了弥补这个缺陷，SQL提供了LIKE运算符。该运算符使用通配符，扩展了对字符串的使用能力。LIKE子句需要和通配符一起使用，SQL中的通配符如表7-4所示。 表7-4 通 配 符 通 配 符 解 释 示 例 ‘_’ 一个字符 Select aa From dd Where cc=’C_’ % 任意长度的字符 Select aa From dd Where cc=’C_%’ [] 括号中所指定范围内的一个字符 Select aa From dd Where cc=’C[1-2]’ [^] 不再括号中指定范围内的任意一个字符 Select aa From dd Where cc=’C[^1-2]’ 　　 　　下面的例子检索musiclist表中姓氏为“周”的歌曲信息(包括ID、歌曲名称、演唱者和歌词)。 select * from musiclist where singer LIKE '周%' 　　查询结果如图7-61所示。 图7-61 使用LIKE运算符 　　5．BETWEEN子句 　　BETWEEN子句可以完成与大于(等于)号、小于(等于)号和AND运算符三者相同范围的限制。使用BETWEEN子句将一个值与某个范围内的值进行比较，包括范围两边的值。若要指定某个排除范围，只能使用大于(>)和小于(<)运算符，而不能使用BETWEEN。另外，BETWEEN子句的基本格式如下： SELECT select_list FROM table_list/view_list WHERE column [NOT] BETWEEN lower_value AND upper_value 　　下面的例子检索musiclist表中ID在2到6之间(包括2和6)的歌曲信息(包括ID、歌曲名称、演唱者和歌词)。 select * from musiclist where id between 2 and 6 　　查询结果如图7-62所示。 图7-62 使用BETWEEN子句 7.2.3 向表中插入数据 　　在SQL中，INSERT语句用于向表中插入数据。INSERT语句可以分为两种方式：分别是使用INSERT...VALUES语句和INSERT...SELECT语句。INSERT...VALUES一次可以向表中添加一个记录。在VALUES关键字中指定新行中的部分列或所有列的值。这个语句的基本格式如下： INSERT INTO Tablename[(column1name,[column2name, ...])] VALUES (value1, [value2, ...]) 　　INSERT...SELECT可以一次将一个或多个表中的数添加到新表中。INSERT...SELECT语句的基本格式如下： INSERT INTO Tablename[(column1name,[column2name, ...])] SELECT columnlist FROM Tablelist WHERE conditions 　　在SQL Server2005中使用INSERT 语句向表或视图中添加数据时，规则如下： ● 如果将一个空字符串 (' ') 加载到 varchar 或 text 数据类型的列，则默认操作是加载一个零长度的字符串。 ● 如果INSERT语句违反约束或规则，或者包含与列的数据类型不兼容的值，则该语句将失败，并且数据库引擎将显示错误消息。 ● 如果INSERT是使用SELECT或EXECUTE加载多行，那么一旦加载的值中出现任何违反规则或约束的情况，就会导致终止整个语句，且不会加载任何行。 ● 当向远程数据库引擎实例的表中插入值且没有为所有列指定所有值时，用户必须标识将向其中插入指定值的列。 　　下面的例子应用INSERT语句向musiclist表中插入一条新的数据。 insert into musiclist values (301,'花心','周华健','春去春回来，花谢花会在开......') 　　查询musiclist表中新插入的这条数据，如图7-63所示，显示数据插入成功。 图7-63 向表中插入数据 7.2.4 修改表中的数据 　　UPDATE语句用来修改现有数据记录中的值。使用它可以修改表中一个记录、一组记录或所有记录中的一列值或所有列值。UPDATE语句的基本格式如下： UPDATE Tablename SET column1name = value1,[ column2name = value2, ...] [WHERE conditions] 　　在Tablename中指出添加记录的表；在关键字SET之后指定列和列的新值；UPDATE语句中可以包含WHERE子句，也可以不包含条件，WHERE子句的conditions指定要修改的记录的条件。 　　在下面三种情况下使用UPDATE语句，则会返回错误。 ● 如果对行的更新违反了某个约束或规则，或违反了对列的 NULL 设置，或者新值是不兼容的数据类型，则取消该语句、返回错误并且不更新任何记录。 ● 当 UPDATE 语句在表达式求值过程中遇到算术错误(溢出、被零除或域错误)时，则不进行更新。批处理的剩余部分不再执行，并且返回错误消息。 ● 如果对参与聚集索引的一列或多列的更新，导致聚集索引和行的大小超过 8,060 字节，则更新失败并且返回错误消息。 　　下面的例子是用Update语句修改musiclist表中新插入的记录的歌曲名字段。 update musiclist set musicname='雨人' where id=301 　　查询musiclist表中新修改过的这条数据，如图7-64所示，显示数据修改成功。 图7-64 修改表中的数据 7.2.5 删除表中的数据 　　DELETE语句的基本格式如下： DELETE FROM Tablename [WHERE conditions] 　　使用DELETE进行删除时，应注意以下几个问题： ● DELETE语句仅删除记录，不会删除表结构。 ● DELETE语句不能删除单个列的值，而是删除整个记录，单个列的值应该用UPDATE修改。 ● DELETE语句删除记录时也会产生关联完整性问题。. 　　下面的例子是用DELETE语句删除新插入的纪录。 delete from musiclist where id=301 　　查询musiclist表中删除的这条数据，如图7-65所示，显示数据删除成功。 图7-65 删除表中的数据 7.3 SQL Server 2005其他数据库对象 　　SQL Server中除了表之外，还有很多重要的数据库对象，如视图、索引、各种约束以及存储过程、触发器等。下面简单介绍，以期读者能够更好的研读后面的实例。 7.3.1 视图 　　视图是由基于一个或多个表上的一个查询所定义的虚拟表，它将该查询的具体标准暂时保存起来。也就是说，存储一个视图，只需要存储它的定义，而不必像实际的数据表那样需要在数据库中占据着物理空间。表需要的磁盘空间要多于视图。且表是静态的，而视图是动态的。创建视图的语法格式如下： CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH [ ,...n ] ] AS select_statement [ ; ] [ WITH CHECK OPTION ] 　　上面语法中各参数的含义如表7-5所示。 表7-5 创建视图的语法说明 参 数 说 明 schema_name 视图所属架构的名称 view_name 视图的名称。视图名称必须符合有关标识符的规则。可选择是否指定视图所有者名称 (续表) 参 数 说 明 column 视图中的列使用的名称 AS 指定视图要执行的操作 select_statement 定义视图的 SELECT 语句。该语句可以使用多个表和其他视图 CHECK OPTION 强制针对视图执行的所有数据修改语句都必须符合在 select_statement 中设置的条件 　　 　　下面的例子创建视图music_view，查询musiclist表中演唱者为“王菲”的歌曲信息(包括ID、歌曲名称、演唱者和歌词)。 CREATE VIEW music_view AS SELECT * from musiclist where singer='王菲'; 　　运行结果如图7-66所示。 图7-66 创建视图 　　查看视图music_view，结果如图7-67所示。 图7-67 查看视图 　　可以使用ALTER VIEW语句修改已经创建好的视图，下面的例子将上面创建的视图修改为查询musiclist表中演唱者为“周杰伦”的歌曲信息(包括ID、歌曲名称、演唱者和歌词)。 ALTER VIEW music_view AS SELECT * from musiclist where singer='周杰伦'; 　　修改成功后，查询视图music_view，结果如图7-68所示。 图7-68 修改视图 　　删除视图可以使用DROP VIEW view_name语句。例如，删除上面的视图可以使用下面的语句： Drop view music_view 7.3.2 索引 　　索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。在书籍中，索引就是内容和相应页号的清单。在数据库中，索引就是表中数据和相应存储位置的列表。索引可以大大减少数据库管理系统查找数据的时间。创建索引的语法格式如下： CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON ( column [ ASC | DESC ] [ ,...n ] ) 　　上面语法中各参数的含义如表7-6所示。 　　 表7-6 创建索引语法中各参数的含义 参 数 说 明 UNIQUE 创建一个唯一索引，即索引的键值不重复。在列包含重复值时，不能建唯一索引。如要使用此选项，则应确定索引所包含的列均不允许NULL 值，否则在使用时会经常出错 CLUSTERED 指明创建的索引为簇索引。如果此选项缺省，则创建的索引为非簇索引 NONCLUSTERED 指明创建的索引为非簇索引。数据页中包含了指向数据库中实际的表数据页的指针 index_name 指定所创建的索引的名称。索引名称在一个表中应是唯一的，但在同一数据库或不同数据库中可以重复 ASC | DESC 指定特定的索引列的排序方式。默认值是升序(ASC) Column 创建索引的列。如果使用两个或两个以上的列组成一个索引，则称为复合索引。一个索引中最多可以指定16个列，但列的数据类型的长度和不能超过900个字节 　　 　　下面的例子为musiclist表创建一个索引music_index，索引列为Id。其语法格式如下： CREATE UNIQUE INDEX music_index ON musiclist (Id); 　　运行结果如图7-69所示。 图7-69 创建索引 　　删除索引可以使用DROP INDEX index_name on table_name语句。例如删除上面的索引可以使用下面的语句： Drop INDEX music_index on musiclist 　　 7.3.3 约束 　　各种约束共同维护数据完整性。SQL Server中常用的约束包括主键约束、外键约束、唯一约束和默认值约束等。下面进行简单介绍。 　　1．主键约束 　　在数据库的每个表中，经常有一个列或列的组合，其值能唯一地标识表中的每一行，这样的一列或者多列称为表的主键。通过主键，可以强制表的实体完整性。每一个表中只能有一个主键，使用PRIMARY KEY进行定义。在使用PRIMARY KEY约束时，该列的空值属性必须定义为NOT NULL，也就是说主键列不能为空。 　　可以在创建表时创建单个 PRIMARY KEY 约束。如果表已存在，且没有 PRIMARY KEY 约束，则可以添加 PRIMARY KEY 约束。如果已存在 PRIMARY KEY 约束，则可以修改或删除它。一个表只能有一个 PRIMARY KEY 约束。 　　向表中添加主键的语法如下： ALTER TABLE table_name ADD [CONSTAINT constraint_name] PRIMARY KEY {(column[,…])} 　　上面语法中各参数的含义如表7-7所示。 表7-7 添加主键语法中各参数的含义 参 数 说 明 table_name 要更改的表名称。如果表不在当前数据库中或者不属于当前用户所有者，可以显示指定数据库和所有者 constraint_name 新约束的名称。约束的名称必须符合标识符规则，但其名称的首字母不能为#。如果没有提供constraint_name，约束使用系统生成的名称 column[,…] 新约束所引用的一列或多列(置于括号中)的列名 　　 　　下面的语句为musiclist表创建一个主键约束。语句如下： ALTER TABLE musiclist ADD CONSTRAINT PK_id PRIMARY KEY (Id); 　　此时若向musiclist表插入一条Id列重复的记录，则会产生错误。如下面的语句： insert into musiclist values (2,'aa','bb','cc') 　　运行结果如图7-70所示。 图7-70 添加数据 　　要想删除主键约束可以使用下面的语句： ALTER TABLE musiclist DROP CONSTRAINT PK_id; 　　2．外键约束 　　创建表时，可以创建 FOREIGN KEY 约束作为表定义的一部分。如果表已经存在，则可以添加 FOREIGN KEY 约束(假设该 FOREIGN KEY 约束被链接到了另一个或同一个表中，某个现有的 PRIMARY KEY 约束或 UNIQUE 约束)。一个表可含有多个 FOREIGN KEY 约束。在某些情况下，一些操作如INSERT等不希望FOREIGN KEY 约束的存在，这样就可以禁用现有的 FOREIGN KEY 约束。 　　禁用 FOREIGN KEY 约束将使表中的数据不经约束验证便可修改。在执行 INSERT 和 UPDATE 语句过程中，如果新数据违反约束，或约束应当仅应用于数据库中已有的数据，则应禁用 FOREIGN KEY 约束。 　　向表中添加外键的语法如下： ALTER TABLE table_name ADD [CONSTAINT constraint_name] [FOREIGN KEY] [(column[,…])] REFERENCE ref_table[(ref_column[,…n])] [ON DELETE{CASCADE|NO ACTION}] [ON UPDATE{CASCADE|ON ACTION}] 　　上面语法中各参数的含义如表7-8所示。 　　 　　 表7-8 添加外键语法中各参数的含义 参 数 说 明 table_name 要更改的表名称。如果表不在当前数据库中或者不属于当前用户所有者，可以显示指定数据库和所有者 constraint_name 新约束的名称。约束的名称必须符合标识符规则，但其名称的首字母不能为#。如果没有提供constraint_name，约束使用系统生成的名称 column[,…] 新约束所引用的一列或多列(置于括号中)的列名 ref_table FOREIGN KEY约束所引用的一列或多列(置于括号中)的列名 ref_column 新FOREIGN KEY约束所引用的 ON DELETE{CASCADE|NO ACTION}] 指定当表中被更改的行具有引用关系，并且该行所引用的行从父表中删除，要对被更改行采取的操作。默认设置为NO ACTION [ON UPDATE{CASCADE|ON ACTION}] 指定当表中被修改的行具有引用关系，并且该行所引用的行在父表中更新时，要对被更改行采取的操作。默认值为NO ACTION 　　 　　3．唯一约束 　　UNIQUE约束指定一个或多个列的组合的值具有唯一性。这有助于确保数据库中不会错误的添加重复的纪录。尽管UNIQUE约束和PRIMARY KEY约束都强制唯一性，但在下面两种情况下应使用UNIQUE约束而不是PRIMARY KEY约束。 ● 非主键的一列或组合。一个表可以定义多个UNIQUE约束，而只能定义一个PRIMARY KEY约束。 ● 允许空值的列。允许空值的列上可以定义UNIQUE约束，但不能定义PRIMARY KEY约束。PRIMARY KEY约束也可以引用UNIQUE约束。 　　向表中添加唯一约束的语法如下： ALTER TABLE table_name ADD [CONSTAINT constraint_name] UNIQUE {(column[,…n])} 　　上面语法中各参数的含义同表7-7。 　　下面的语句向musiclist表中的歌曲名称字段添加唯一约束。 ALTER TABLE musiclist ADD CONSTRAINT uni_singer UNIQUE (singer); 　　要想删除唯一约束可以使用下面的语句： ALTER TABLE musiclist drop CONSTRAINT uni_singer; 　　4．默认值约束 　　在定义表时，可以对某个列设定默认值。当对表添加记录时，若对设有默认值的列没有明确地指定值，系统就会自动用默认值进行添加。这样可以方便用户输入记录，并减少出错情况。 　　下面的语句为musiclist表的“演唱者”字段定义默认值约束，默认值为“未知”。 ALTER TABLE musiclist ADD CONSTRAINT music_def DEFAULT '未知' FOR singer ; GO 　　向musiclist表中添加一条数据： insert into musiclist (id,musicname,songwords) values(301,'123','ahkjdhkfha') 　　查询新插入的数据，结果如图7-71所示。singer字段的值为“未知”，表明添加默认值约束成功。 图7-71 添加默认值约束 　　5. 删除默认值 　　要想删除默认值约束可以使用下面的语句： ALTER TABLE musiclist drop CONSTRAINT music_def GO 7.3.4 存储过程 　　存储过程可以在最大程度上发挥出SQL的潜能，该功能可以被C语言和其他编程语言调用和执行。存储过程是一组经过压缩处理的经常使用的命令。存储过程允许程序员简单地将该过程作为一个函数来调用，而不是重复的执行过程内部的语句。创建存储过程的语法格式如下： CREATE { PROC | PROCEDURE } procedure_name [@parameter data_type } AS 　　上面语法中各参数的含义如表7-9所示。 表7-9 创建存储过程语法中各参数的含义 参 数 说 明 procedure_name 新存储过程的名称。过程名称必须遵循有关标识符的规则，并且在架构中必须唯一 @ parameter 过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数 data_type 参数以及所属架构的数据类型 sql_statement> 要包含在过程中的一个或多个 Transact-SQL 语句 　　 　　下面的例子为musiclist表创建一个存储过程music_proc，用来查询演唱者为“王菲”的歌曲信息(包括ID、歌曲名称、演唱者和歌词)。 CREATE proc music_proc as select * from musiclist where singer='王菲' 　　执行存储过程，结果如图7-72所示。 图7-72 执行存储过程 　　可以使用ALTER PROC语句修改已经创建好的存储过程，下面的例子将上面创建的存储过程修改为查询musiclist表中演唱者为“周杰伦”的歌曲信息(包括ID、歌曲名称、演唱者和歌词)。 alter proc music_proc as select * from musiclist where singer='周杰伦' 　　执行上面的程序，结果如图7-73所示。 图7-73 执行修改过的存储过程 　　删除存储过程可以使用DROP PROC proc_name语句。例如，删除上面的视图可以使用下面的语句： drop proc music_proc 7.3.5 触发器 　　触发器是一种特殊类型的存储过程。它主要是通过事件进行触发而被执行的。而存储过程可以通过存储过程名字而被直接调用。当对表进行诸如UPDATE、INSERT、DELETE等操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。通常可以在触发器内编写一段自动执行的程序，用于保证数据操作的完整性，从而扩展了对默认值、约束和规则的完整性检查。创建触发器的语法格式如下： CREATE TRIGGER trigger_name ON { table | view } { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [DELETE] } AS sql_statement [ ...n ] } } 　　上面语法中各参数的含义如表7-10所示。 　　 　　 表7-10 创建触发器语法中各参数的含义 参 数 说 明 trigger_name 触发器的名称，并且在数据库中必须唯一 table | view 在其上执行触发器的表或视图，有时称为触发器表或触发器视图 AFTER 指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。如果仅指定FOR关键字，则AFTER是默认设置。AFTER不能作用于视图 INSTEAD OF 指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作 DELETE 指明是DELETE触发器，每当一个DELETE语句从表中删除一行时激发触发器 INSERT 指明是INSERT触发器，每当一个INSERT语句向表中插入一行时激发触发器 UPDATE 指明是UPDATE触发器，每当UPDATE语句修改由OF子句指定的列值时，激发触发器。如果忽略OF子句，每当UDPATE语句修改表的任何列值时，DBMS都将激发触发器 AS 触发器要执行的操作 sql_statement 触发器的条件和操作 　　 　　下面的例子为musiclist表创建一个触发器music_delete_tri，当删除musiclist表中数据时，输出字符串“记录删除成功”。代码如下： CREATE TRIGGER music_delete_tri ON musiclist AFTER DELETE AS PRINT '数据已经删除' GO 　　删除musiclist表中的一条记录，结果如图7-74所示。 图7-74 删除数据 　　可以使用ALTER TRIGGER tri_name语句修改已经创建好的触发器，下面的语句修改上面创建的触发器，将语句修改为向表中添加数据时，输出字符串“数据已经插入”。代码如下： ALTER TRIGGER music_delete_tri ON musiclist AFTER INSERT AS PRINT '数据已经插入' GO 　　删除触发器可以使用DROP TRIGGER tri_name语句。例如删除上面的触发器可以使用下面的语句： DROP TRIGGER music_delete_tri 7.4 本章小结 　　本章主要介绍了SQL Server 2005数据库的一些基础应用。第一节主要介绍了数据库、数据表、视图、存储过程和安全性的管理，以及数据库的附加、备份、恢复、数据的导入导出操作。这些内容都是在SQL Server 2005中经常应用到的技术。第二节介绍了SQL语句操作数据表的应用，数据表最为重要的4种操作，分别为查询、添加、修改和删除。第三节介绍了SQL Server 2005中视图、索引、约束、存储过程和触发器的语法结构和使用方法。 　　 　　 　　 　　 　　 　　 　　 212 　　 　　 211