大型数据库课件

SQLServer2000概述SQLServer2000简介SQLServer2000是运行于Windows操作系统的的关系型数据库管理系统(RDBMS--RelationalDataBaseManageSystem)，是新一代电子商务、数据仓库和数据库解决方案何为关系型数据库？为什么SQLServer2000往往成为中小型企业的首选？容易上手兼容性好，与windows操作系统结合紧密功能完备注意区分“数据库系统”与“数据库”的这两个概念SQLServer2000的安装（演示）Windows各个版本所支持的SQLServer2000版本（仅指服务器组件）操作系统企业版标准版个人版开发版Windows2000(Advanced)Server支持支持支持支持Windows2000Professional支持支持MicrosoftWindows98/XP支持注意：各个版本的客户端软件可以在任何版本的windows上运行。测试安装是否成功启动服务：netstartmssqlservernetstartMSSQL$TEACHER连接到SQLServer：

osql–Usa–P<password>osql–Usa–P<password>–S<machine>\<instance>Client/Server结构回应SQL请求ClientSQLServerSQLServer利用C/S结构把工作负载分成在C端和S端运行的任务客户端：商业逻辑处理，把数据呈现给用户服务器端：定义数据库结构、存储数据，管理数据库，并进行多用户的并发处理三层C/S结构发送命令处理命令后请求数据返回数据处理数据后返回结果ClientApplicationServerDatabaseServer表示服务的最小商业逻辑商业逻辑数据访问和管理熟悉SQLServer2000启动SQLServer2000服务器使用服务管理器启动使用企业管理器启动使用“管理工具”中的“服务”启动在DOS命令下启动SQLServer2000界面熟悉选课数据库Xk在SQLServer2000下增加Xk数据库五张用户表：Class表（班级表）、Course表（课程表）、Student表（学生表）、Department表（部门表）、StuCou表（学生选课表）表之间的关系在Xk下实现一个简单的查询使用查询分析器进行查询USEXkGOSELECT*FROMSTUDENTGO使用osql实用程序进行查询总结SQLServer2000的安装了解两层和三层的C/S体系结构及SQLServer数据库在C/S结构中的位置及作用学会启动数据库服务器熟悉选课数据库Xk，并能够使用查询分析器和osql实用工具实现简单的查询注意理解并区分几个概念：数据库管理系统、数据库、服务器、服务器实例

SQLServer安全管理问题

赵老师当了00电子商务班的班主任，他要能查到全校的课程信息以及本班学生的选课信息，如何让他有权查到这些信息。安全管理一个用户要对某一个数据库进行操作，必须同时满足两个条件：能连接到SQLServer服务器（连接权）有执行该操作的权限（访问权）安全管理房子(SQLServer)大门验证授权SQLServer验证模式SQLServer两种验证模式

1.仅Windows验证

2.SQLServer和Windows的混合验证设置安全认证模式登录管理必须有合法的登录账号才能建立与SQLServer的连接【例】为新老师创建登录账号

EXECsp_addlogin‘logzhao’,’01’验证登录帐号能否对数据库进行访问。数据库用户管理有了登录帐号后，还要对应数据库用户才能连接数据库【例】在数据库中创建用户dbuserzhao，对应的登录帐号是logtzhao。

execsp_grantdbaccess‘logzhao','dbuserzhao‘验证与数据库的连接和访问授予权限权限分为：

1.语句权限：createtable,view,triggeretc…2.对象权限:select,insert,exec,update,delete,exec【例】授予用户权限

grantselectoncoursetodbuserzhao【例】验证登录账号logzhao能否访问course表解决问题

解决赵老师能查询本班学生的选课信息CreateviewcselasSelectstucou.stuno,couno,willorderfromstucou,student,classWherestucou.stuno=student.stunoandstudent.classno=class.classnoandclassname=’00电子商务’步骤1.创建00电子商务班的选课信息视图解决问题步骤2.把访问该视图的权限授予赵老师步骤3.验证赵老师能否访问视图解决问题赵老师能了解本班某课程的选课情况步骤1.创建能查询本班指定课程选课信息的存储过程createprocc_cou@kcchar(10)AsSelectstuno,couname,willorderfromcsel,courseWherecsel.couno=course.counoandCouname=@kc解决问题步骤2.把执行该存储过程的权限授予赵老师

grantexeconc_coutodbuserzhao

步骤3.验证赵老师能否执行存储过程撤消权限【例】撤消赵老师执行上面存储过程的权限问题假如学校新增10个班主任，他们都要在student表中添加、修改和删除学生，要个个设置权限，方便吗？角色角色角色是一组权限的命名集合。SQLServer中角色分为：服务器角色和数据库角色。数据库角色标准角色：有角色成员。应用程序角色：无角色成员，需要激活才能使用。解决问题步骤1：创建班主任角色

usexk

EXECsp_addrole‘m_role’步骤2：对角色授权步骤3：创建各班主任登录sp_addlogin‘logteac1’,’01’sp_addlogin‘logteac2’,’02’步骤4：创建各登录对应的用户

sp_grantdbaccess‘logteac1','dbuser1'解决问题步骤5：使用户成为角色成员

sp_addrolemember‘m_role’,’dbuser1’sp_addrolemember‘m_role’,’dbuser2’步骤6：验证插入权限应用程序角色

创建应用程序角色

sp_addapprole角色名，密码

激活应用程序角色sp_setapprole角色名，密码删除删除登录、用户、角色

sp_droploginsp_dropusersp_droprole企业管理器用法本章小结登录管理——使得可以连到数据库服务器用户管理——使得可以连到数据库对用户授权角色管理——权限的集合

Transact-SQL语言Transact-SQL语言概述

第1节单表查询问题：我们需要对数据进行哪些维护操作？如何对数据进行查询、统计?结构化查询语言（StructuredQueryLanguage）。这种语言的语法结构类似于英语，易学易用，书写随意。[例如前面第一章讲过的例子]

Transact-SQL语言的组成

1、数据定义语言DDL：用来定义和管理数据库中的对象。DDL主要的语句有:CREATTABLE /*创建表*/ALTERTABLE /*修改表的属性*/DROPTABLE /*删除表*/2、数据操作语言DML：用来操作数据库中的对象和数据，是T-SQL中最常用的部分。DML主要的语句有：

SELECT /*从一个表或多个表中检索数据*/DELETE /*从表中删除数据*/INSERT /*向一个表中添加数据*/UPDATE /*修改表中已有的数据*/

3、数据控制语言DCL：用来控制用户对数据库对象操作的权限。主要的命令有：GRANT /*授予权限*/REMOVE /*回收所授予的权限*/操作数据打开一个数据库

使用SELECT子句SELECT子句主要用于检索数据，其基本格式为：SELECT选择列表FROM表的列表WHEAR检索的条件【问题】从Student表中检索学号为00000001的学生的姓名（StuName），要求显示学生的学号和姓名【练习】要求检索系部编号为01的班级信息，要求显示班级编号和班级名称

1、*的使用【例】显示学生表中所有信息【例】从学生表中检索学生所在班的班级编码2、使用DISTINCT【例】从学生表中检索学生所在班的班级编码，要求清除值相同的那些行

3、使用TOPn[PERCENT]【例】从学生表中检索所有的信息，要求只显示前6行数据

4、修改检索结果中列的标题【问题】检索学生表中的学号、班级编码和姓名信息，并修改列标题

方法有三：（1）将要显示的列标题用单引号括起来后接等号（＝），后接要检索的列名（2）将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用空格隔开（3）将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用AS关键字

【练习】使用其他两种方法

5、在查询结果中显示字符串【问题】检索课程表的信息，要求给出检索结果为：课程名称 课程编码SQLServer实用技术课程编码为： 001……

课程编码为： …………

课程编码为： ……

思考：如何检索课程编码是001的课程名称？

6、使用WHERE限制检索的条件【例】检索课程编码为001的课程名称，要求只显示课程名称

【练习】检索上“Linux操作系统”课程的教师名

7、表达式作为SELECT语句中的列【例】要求检索课程表的课程信息，并显示报名人数和限制选课人数之比思考：如何将上面的检索结果按报名人数和限制选课人数之比的升序排列？

8、使用ORDERBY子句重新排列检索结果

【升序例子】【降序例子】也可以在ORDERBY子句中指定多个列【例】检索课程表的教师名、课程号、课程名，要求检索结果首先按教师名降序排列，教师名相同时，则按课程号的升序排列9、使用IN（NOTIN）关键字【例】检索课程编码为‘001’‘004’‘013’的课程名称方法一：使用逻辑运算符OR方法二：使用IN关键字（比使用逻辑运算符更为简单）问题

检索开设了“信息技术”类课程的教师名及其所开的课程名，要求先按教师名升序排列，教师名相同时，按课程名的降序排列，并将显示的列名改为中文。操作数据（续）1、使用LIKE（NOTLIKE）关键字通配符：% 匹配包括0个或多个字符的字符串_ 匹配任何一个字符[] 匹配任何在范围内的单个字符，例如：[m-p][^] 匹配任何不在范围内的单个字符，例如：[^m-p]、[^mnop]通配符和字符串要括在单引号中

【练习】解释下面的通配符表示的意义％D%_[^a][[] ——如果要查找通配符本身，需要将它们用方括号括起来[%]D【问题】检索以“制作”两字结尾的课程名（使用LIKE）【练习】检索姓名的第二个字为“丽”的学生信息，要求显示学生学号和姓名【问题】检索不姓“刘”的学生信息（使用NOTLIKE）

2、使用ISNULL关键字——用于检索列中没有赋值的行【问题】检索课程表中教师未定的课程名称和教师名

3、使用BETWEEN…AND（NOTBETWEEN…AND）关键字——用于检索在某一特定范围内的信息【问题】检索报名人数大于等于30并且小于等于40的课程信息，要求显示课程名称和报名人数（使用BETWEEN…AND）

[注意]WHEREWillNumBETWEEN30AND40包括30和40这两个值【问题】对上例使用NOTBETWEEN…AND也可以在WHERE字句中使用前面讲过的逻辑运算符检索某一范围内的信息【练习】检索报名人数比限制选课人数多一倍以及一倍以上的课程信息，要求显示课程名称、报名人数和限制选课人数之比，并按该比例的降续排列

4、使用UNION子句——用于将两个或多个检索结果合并成一个结果

【问题】从班级表中检索班级名，从系部表中检索系部名，并将这两个检索结果合并起来

使用时注意：（1）所有检索中的列数和列的顺序必须相同（2）所有检索中按顺序对应列的数据类型必须兼容（相同或者可以明确地转换）

[扩展]（1）修改列标题（2）按新的列标题排序

5、使用COMPUTE子句——用来计算总计或进行分组小计【问题】检索课程表中的课程编码、课程名称、课程分类、报名人数，并计算平均报名人数

[说明]总计值或小计值作为附加新行出现在检索结果中。该子句用在WHERE子句之后

从检索结果可以看到：检索结果中有两个结果集（1）包含选择的列的所有明细行（2）包含COMPUTE子句中所指定的聚合函数的合计

[使用聚合函数]AVG()、MAX()、MIN()、SUM()聚合函数也可以出现在SELECT子句中，例PAGE39【例2.10】

[扩展]（1）只检索课程分类为“信息技术”的课程信息（2）可以在检索结果中显示多个聚合函数的合计

上面的例子是对显示的所有行进行总计，如何对信息进行分组小计呢？

6、使用COMPUTE…BY子句【例】按类检索课程表中的课程编码、课程名称、课程分类、报名人数，并计算每类课程的平均报名人数

从检索结果可以看到：检索结果中有多个组，每个组有两个结果集这两个结果集中的内容同上

[注意]必须使用ORDERBY排序要分组的列

【练习】检索周二上课的课程名称和教师名，并对符合条件的课程门数进行统计（提示：先完成前一部分，再使用聚合函数COUNT进行统计）

问题问题

COMPUTE子句是在一个明细行的基础上增加一个总计或小计（有BY时），但如果只想统计每类课程的平均报名人数，该如何处理？操作数据（续）1、使用GROUPBY子句【问题】按课程分类统计每类课程的平均报名人数

与上面例子的区别：只有一个结果集[说明]（1）该子句用于将检索结果按照GROUPBY后指定的列进行分组，该子句写在WHERE子句后面（2）GROUPBY子句经常用于SELECT子句中包含有聚合函数的情况。此时，SELECT子句中选项列表中出现的列，只能是GROUPBY子句中的列或者包含在聚合函数中。

2、使用HAVING子句【问题】上面的例子中，如果只统计“信息技术”类课程的平均报名人数

[说明]HAVING经常用在GROUPBY子句之后，用于限定结果集中的分组

也可以使用WHERE子句可以看到，HAVING子句是对结果进行过滤，而WHERE是对原始记录进行过滤。

[注意]HAVING子句中的列只能是GROUPBY子句中或者聚合函数中的列

【问题】检索平均报名人数大于30人的课程类和每类平均报名人数

【练习】在课程表中，按所开设课程的系部编码分别统计总共报名人数如果问：系部编码为01的系所开设的课程总共有多少学生报名，如何编程实现？

3、使用子查询——在一个查询中包含另一个查询【问题】检索报名人数大于平均报名人数的课程编码、课程名称和报名人数STEP1：如何求平均报名人数STEP2：完成题目要求

本节小结本节小结

SQL语言的组成

SELECT语句的基本格式介绍了各种单表查询语句

多表查询第2节多表查询如果问：“计算机应用工程系”下属的班级有哪些，应该查询哪些表格？这就是多表查询了。

如果在多个表中进行查询，结果会怎样？USEXkGOSELECT*FROMDepartment,ClassGO结果集中，有54行（3×18），5列（2＋3）。

问题:[笛卡儿积]运算过程？

在笛卡儿积的结果中，分析存在的问题以及解决方法：（1）两列DepatNo的列值不同解决方法：加连接条件，成为相等连接【注】为了避免笛卡儿积，必须在WHERE子句中给出表格的连接条件：通常对于N个表格的检索，要有N-1个连接条件（2）出现了相同的列：解决方法：去掉相同的列，成为自然连接（3）加入“计算机应用工程系”的条件，成为带有选择条件的连接

【问题】检索“01电子商务”班的学生选修课程的情况，要求显示班级名称、学生学号、学生姓名、课程名称和上课时间

[分析]班级名称、学生学号、学生姓名、课程名称、上课时间这些信息是分别在班级表、学生表、学生选课表和课程表中的，因此需要在多个表中进行查询。同时，必须在WHERE子句中给出连接条件。

[注意]在引用的多表中，如果列名在多个表中同名，为了避免列名不明确，在SELECT子句中必须在列名前加上表的前缀，即“表名.列名”。【练习】检索“林斌”同学的选修课信息（课程编号、课程名称、上课时间）

【练习】查询每个系所开课程的门数，显示系代码、系名称、开课门数【练习】显示所有系的班级名称，要求计算各系班级数量，显示要求如下：多表查询1、非相等连接（比较连接）——连接条件不是等号，而是比较运算符【问题】检索各班可以选择的不是本系开设的选修课程的信息（课本的例子）

2、自连接——自己和自己的连接【问题】检索课程类相同而系部编码不同的课程信息（意味着同一类的课程有多个系部开设），要求显示课程编码、课程名称、课程分类和系部编码（课本的例子）

[使用别名]【练习】检索学分大于等于2.5的课程信息，显示课程名称、学分、开设该课程的系部名称、报名人数，要求使用别名

外连接上面介绍的都是内连接，以下是外连接：【问题】检索学生的选课信息，要求显示学生学号、课程编码、课程名称1、左外连接

2、右外连接

3、全连接

4、交叉连接

使用EXISTS关键字如果要解决这样的问题：【问题】检索已经报名选修了课程的学生的学号和姓名[分析]采用上面所学的方法能否解决？使用EXISTS关键字——测试跟随的子查询的行是否存在

[要点]一旦找到，立即停止找到则返回TRUE，否则返回FALSE，而不是返回一些行

使用NOTEXISTS

【问题】查询没有选修’001’课程的学生学号和姓名。selectstuno,stunamefromstudentwherenotexists(select*fromstucouwherestuno=student.stunoandcouno='001')本节小结多表查询非相等连接自连接使用别名外连接（左外连接、右外连接、全连接）和交叉连接使用EXISTS关键字

Transact-SQL语言编程编程基础知识问题如何编制类似C语言的基本程序？要能编程须具备哪些结构？

引用数据库对象的规则Transact-SQL的语法元素SQLServer中标识符的命名规则练习1问题：下面的标识符是否符合SQLServer的命名规则：$table@table#_var_1orderdetailsorder常量和变量常量：字符串常量数值常量日期常量uniqueidentifier常量注：引用数值常量不用单引号，引用日期、字符串常量时需要加单引号变量变量：在程序运行过程中其值是变化的量局部变量（以@开头；使用DECLARE语句声明）【问题】DECLARE@var1int,@var2moneySELECT@var1=50,@var2=$29.95SELECT@var1,@var2

全局变量（也称配置函数，是系统定义的变量，以@@开头；用户只能使用）例如：@@version@@rowcount练习2问题：编写程序计算两个整数之和[课本P99【例3.32】]运算符算术运算符（+、－、*、/、%）比较运算符（=、>、<、>=、<=、<>、!=、!<、!>）逻辑运算符（NOT、OR、AND、IN、LIKE、BETWEEN、EXISTS）字符串连接运算符（＋）赋值运算符（=）位运算符（&、|、^、~）常用函数（自学）思考：如何将一个数字转换为一个字符串？如何截取字符串？如何根据一个人的出生年月，使用日期函数计算年龄？如何对一个数进行取整？进行四舍五入？批处理批处理的特点一次发给数据库服务器的所有的SQL语句，由GO作为结束标志。编译成一个可执行单元——执行计划执行时，每次执行计划中的一条语句特别注意：如果在一个批处理中，某条语句存在语法错误，SQLServer将不执行批处理中的任何语句。【例】

流控语句【问题】如何用SQL语句编程实现：求1+2+3+4+…+100=?编程思想：代码：分析：WHILE、BEGIN…END、IF…ELSE…、GOTO的使用用法其他流控语句的使用用法BRAEK：在循环语句中，用于退出本层循环CONTINUE：在循环语句中，结束本次循环，重新转到下一次循环条件的判断练习3编写程序，计算n!（n=10），并将结果显示出来流控语句CASE语句（开关语句）【例】P108例3.37【练习4】在Xk数据库中显示班级编码、班级名称和系部名称（当系部编码为01时，显示系部名称为“计算机系”；当系部编码为02时，显示系部名称为“建筑系”；当系部编码为03时，显示系部名称为“旅游系”），要求使用CASE语句流控语句WAITFOR语句：WAITFORDELAY‘time’——延迟一段时间WAITFORTIME‘time’——等待到一个时刻本章总结重点掌握：SQLServer中标识符的命名规则学会使用局部变量和全局变量学会使用一些常用的函数流控语句学会编写简单的SQL程序

触发器引例思考：当学生报名选修SQLServer实用技术课程时，WillNum应自动加1，如何处理？触发器的作用在指定的表中数据发生变化时被调用以响应INSERT、UPDATE或DELETE事件强制执行业务规则创建简单的触发器【问题】创建一个触发器Test1，要求每当在Student表中修改数据时，向客户端显示一条“记录已修改！”的消息SQL格式：CREATETRIGGERtrigger_nameOntable[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][UPDATE][DELETE]}ASsql_statement在企业管理器中查看触发器信息FOR/AFTER/INSTEADOF的比较【问题】将上例中触发器中的FORUPDATE改为INSTEADOFUPDATE，查看执行结果有何不同。总结：使用FOR/AFTER时，执行触发SQL语句（INSERT/UPDATE/DELETE)：在触发SQL语句成功执行后，再执行触发器使用INSTEADOF时：执行触发器而不执行触发SQL语句练习1创建一个触发器，要求每当在StuCou表中插入数据时，向客户端显示一条“记录已添加！”的消息。创建一个触发器，要求每当用户插入student表的记录时，自动显示表中所有内容INSERTED表和DELETED表系统为每个触发器都自动创建了INSERTED表和DELETED表INSERTED表用于存放插入操作时的行；DELETED表用于存放删除操作时的行这两个表的结构和被触发器作用的表的结构相同用户不能对这两个表进行修改，但可以读取练习2创建一个触发器，要求每当用户插入student表的记录后，自动显示Student表中插入的记录创建触发器【问题】创建一个触发器SetWillNum，要求当插入、更新、删除StuCou表的选课记录时，能更新Course表中相应的报名人数。说明：该触发器只适用于每次新增、删除一条记录的情况。创建批量数据触发器思考：若有批量的数据新增、插入或删除，应该如何修改上例中的触发器？处理多条记录的触发器简单、易维护，但每次都更新Course中每条记录的报名人数，效率不高IFUPDATE的应用【问题】创建一个触发器Test1，要求每当student表中修改pwd列的数据时，向客户端显示一条消息“密码已更改”的消息。特点：只有当IFUPDATE指明的列发生修改时，才触发执行触发器。级联更新和删除：【练习】创建触发器，当修改student表中的学号字段后，自动修改stucou表中相应的学号。（以前采用外键方式）

【练习】在student表上创建触发器del_trg，当删除表中某一个学生的记录时，能自动删除掉该学生的所有成绩记录重命名、删除触发器重命名触发器SQL格式：sp_renameoldname,newname删除触发器SQL格式：DROPTRIGGERtrigger_name说明：删除触发器所在的表时，SQLServer将会自动删除与该表相关的触发器管理触发器禁用触发器

ALTERTABLEtable_nameDISABLETRIGGERtrigger_name恢复使用触发器

ALTERTABLEtable_nameENABLETRIGGERtrigger_name显示数据库中有哪些触发器请参考联机丛书“sysobjects系统表”使用注意事项总结CREATETRIGGER要在单个批处理中执行ONtable中的表格只能是一个在同一条CREATETRIGGER语句中，可以为多个事件（INSERT/DELETE/UPDATE定义相同的触发器操作）可以为每个事件（INSERT/DELETE/UPDATE）创建多个触发器本章小结理解：触发器的作用创建、管理触发器的方法（重点）如何设计触发器以简化系统设计（进一步的要求）

存储过程概述问题要把某完成功能的SQL做成类似C语言的函数，供需要时调用，如何做？

什么是存储过程？是一组被编译在一起的T-SQL语句的集合，它们被集合在一起以完成一个特定的任务。存储过程的分类系统存储过程扩展存储过程（提供从SQLServer到外部程序的接口，以便进行各种维护活动）用户自定义的存储过程存储过程的作用从以下几个方面考虑：模块化编程：创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。快速执行：存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。减少网络通信量有了存储过程后，在网络上只要一条语句就能执行一个存储过程。安全机制通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。存储过程和视图的比较视图存储过程语句只能是SELECT语句可以包含程序流、逻辑以及SELECT语句输入、返回结果不能接受参数，只能返回结果集可以有输入输出参数，也可以有返回值典型应用多个表格的连接查询完成某个特定的较复杂的任务创建、执行简单的存储过程创建格式：CREATEPROCEDUREproc_nameASSql_statements【问题】创建一个名为p_Student的存储过程，返回student表中班级编码为20000001的学生信息执行格式：EXECproc_name练习1创建一个名为p_StuByClass的存储过程，返回“00电子商务”班的所有学生的信息执行该存储过程，并使用企业管理器查看该存储过程的定义创建带输入参数的存储过程创建格式：CREATEPROCEDUREproc_name@para_namedatatype[=default],…ASSql_statements【例】创建一个名为p_StudentPara的存储过程，该存储过程根据给定的班级编码显示该班级所有学生的信息练习2创建一个名为p_StuByPara的存储过程，根据给定的班级编码和学生学号，显示相应学生的信息执行带输入参数的存储过程执行格式：EXECproc_name[@para_name=]value,…指定参数名的格式不指定参数名的格式思考：当缺省参数执行存储过程时，如何处理？带输出参数的存储过程定义格式同上，同时指明OUTPUT关键字创建、执行方法：P202-203【练习】创建存储过程P_ClassNum，要求能根据用户给定的班级代码，统计该班的人数，并将人数通过输出变量返回给用户。如要查询班级代码为’20000002’的学生人数，如何调用存储过程

【练习】创建存储过程Cou_credit，要求能根据用户给定的学分值，统计满足该学分值的课程数目，并把它返回给调用程序。如要统计2个学分的课程门数，该如何调用上述存储过程。【练习】创建名为mod_credit的存储过程，能修改用户指定课程号的课程学分值，修改值也由用户指定。修改存储过程修改格式：ALTERPROCEDUREproc_name@para_namedatatype[=default],…[WITHENCRYPTION]ASSql_statements重命名、删除存储过程重命名：使用企业管理器注意：要修改两个地方删除格式：DROPPROCEDUREproc_name重编译处理（RECOMPILE）在创建时指明重编译在执行时指明重编译通过系统存储过程设定重编译选项系统存储过程、扩展存储过程sp_addlog——创建登录ID问题：为何执行该存储过程时可以带一个、两个或者三个参数？xp_cmdshell——执行操作系统命令xp_enumgroups——列出域中的组的信息xp_loginconfig——报告SQLServer的登录安全信息本章总结存储过程的作用创建、执行简单的存储过程创建、执行带参数（输入、输出）的存储过程（重点）重点：存储过程的创建和执行，特别是带有参数的存储过程

事务与游标编程问题问题在银行等重要交易中，要使一组SQL语句同时执行或撤消，如何做？要对查询结果进行一行行的操作，如何做到？事务概述事务是作为单个逻辑工作单元执行的一系列操作。思考：事务和批处理的区别？事务的属性（ACID)原子性：原子工作单元一致性：保证数据的一致性隔离性：并发事务之间所做的修改要隔离并发操作：几个用户程序同时读写一个数据的情况持久性：对系统的影响要持久简单事务编程格式：BEGINTRANSACTIONCOMMITTRANSACTIONROLLBACKTRANSACTION【问题】简单的事务提交和回滚说明：如果没有明确给出BEGINTRANSACTION语句，则SQLServer是将每个SQL语句都当成一个事务进行执行事务的嵌套嵌套事务只有在提交了最外层的事务后，数据才执行永久修改嵌套事务时，ROLLBACK语句只能将所有内层事务回滚到最外层的BEGINTRANSACTION语句封锁利用封锁技术可以有效地保证数据的一致性封锁粒度：可以锁定在行、列、表、数据库等不同的单元上SQLServer具有自动和强制封锁的功能锁模式锁模式：共享锁（ShareLock）排他锁（ExclusiveLock）更新锁（UpdateLock）各种锁模式的兼容性：

共享锁更新锁排他锁共享锁YESYESNO更新锁YESNONO排他锁NONONO死锁当两个事务trans1和trans2在下列的状态时，将产生死锁：Trans1：存取数据项X和YTrans2：存取数据项Y和X【说明】如果事务Trans1封锁了数据项X，事务Trans2封锁了数据项Y，则Trans1等待Trans2释放Y上的锁，Trans2等待Trans1释放X上的锁。因此，Trans1和Trans2都无限地等待对方打开锁住的数据项，则产生死锁。死锁（续）【死锁示例】说明：SQLServer能自动发现并解除死锁避免死锁的措施：P.237游标引例以前：使用SELECT语句对表格进行查询，返回的结果集包括所有满足条件的行。思考：如果要求每次只显示表格（例如上面的Course表）中的一行，该如何处理？——这在将T-SQL嵌入到其他高级语言（如C、VC、Delphi等）的编程中经常用到。游标概述可以将游标看作一种特殊的指针，它可以指向与它相关联的结果集中的任意一行，以便对当前位置的行进行处理。游标提供了对一个结果集进行逐行处理的能力：在结果集中定位特定行从结果集的当前位置检索行支持对结果集中当前位置的行进行数据处理（修改/删除）使用游标的步骤声明游标打开游标处理数据（读取/修改/删除）——可以和其他T-SQL语句配合灵活使用关闭游标（与打开游标配对）删除游标（与声明游标配对，此时释放分配给游标的所有资源）【问题】游标基本使用的例子相关语法格式声明游标DECLAREcursor_nameCURSOR[FORWORD_ONLY|SCROLL]FORselect_statements打开游标OPENcursor_name使用游标FETCH[NEXT|PRIOR|FIRST|LAST]FROMcursorInto@variable_name关闭游标CLOSEcursor_name删除游标DEALLOCATIONcursor_name练习1声明一个名为CrsStudent的游标，使其与“00电子商务班”的所有学生信息（学号、姓名）相关联，并逐行显示前三条学生信息如果遍历整个结果集？使用@@fetch_status:该全局变量/配置函数返回被最后FETCH语句执行的游标的状态，返回类型为int：0：FETCH语句成功－1:FETCH语句失败或此行不在结果集中－2：被提取的行不存在练习2使用游标遍历Course表，输出报名人数最多的课程的信息（课程编码、课程名称、报名人数）练习3建立一个存储过程，利用游标求course表中某系（用户给定系代码）所开课程的最大报名人数，并把课程名称和最大报名人数作为输出变量返回给用户。

本章小结事务——掌握锁——了解重点：掌握事务和游标的使用步骤、方法

视图视图概述什么是视图？基表（basetable)：独立存在的表视图——虚拟表。在SQL中只存储视图的定义，不存放视图所对应的记录问题问题如果经常要查询计算机系开设的课程，如何做到一次定义，多次使用？如果做到让00电子商务班的班主任只能看到本班的选课情况？视图概述(续）视图的作用使用户只关注所关心的数据提高了数据的安全性提高客户端的查询效率创建视图（1）视图为基表的行的子集【问题】创建视图v_Student，只显示Student表中班级编码为20000001的班级的学生信息特点：SELECT*用WHERE子句指明选择哪些行创建视图（2）视图为基表的列的子集【问题】创建视图v_Course，只显示Course表中的CouName列和SchoolTime列特点：SELECT子句中指明选择哪些列没有WHERE子句练习创建视图v_Class，只显示Class表中系部编码为01的系部的班级信息创建视图v_StuCouSub，只显示StuCou表中的StuNo列和CouNo列创建视图（3）视图为多个表、视图连接组成的复杂查询——最常用的情况【例】创建视图v_StuCou，显示学生的学号、姓名和所选的课程名称特点：多表查询（其实只需要在正确的select语句前加上CREATEVIEW…AS即可）练习创建视图v_CouByCmpt，显示计算机应用工程系承担的选修课程的信息（课程名称、授课老师、上课时间）创建视图（4）视图为基表的统计汇总【问题】将课本P52图2-2-27的查询结果作为视图v_CouByKind特点：视图定义中使用了GROUPBY子句注意：在下面的情况下必须明确指明视图每一列的列名：视图中的某一列是算术表达式、函数或者常量多表连接时，有两个或两个以上的列具有相同的列名思考：能否将课本P51图2-2-26的查询结果作为视图练习创建视图v_CouByDep，显示各个系部开设的选修课的数量和报名人数总结视图是一个虚拟表，创建视图的目的是为了方便查看数据创建视图的方法创建视图的注意事项修改视图格式：ALTERVIEWview_nameASselect_statement【问题】将v_Student进行修改，使其显示班级编码为20000002的学生信息加密视图WITHENCRYPTION

删除视图格式：DROPVIEWview_name重命名视图使用企业管理器使用系统存储过程sp_rename显示视图信息显示视图的定义信息sp_helptext显示视图的参照对象和字段信息

sp_depends更新视图INSERT操作UPDATE操作DELETE操作注意：由于视图实际上并不存储记录，因此更新视图的操作最终都转化为对基表的更新操作使用WITHCHECKOPTION：保证在进行视图的更新操作时，所插入、更新、删除的记录，满足视图定义中查询的条件表达式练习创建视图v_StuSQL，显示’00电子商务’班并选修了‘SQLServer实用技术’的学生信息（学号、姓名）。并要求加密视图。小结视图可被看成是虚拟表或存储查询使用视图可提高数据库性能和安全性对视图进行修改会影响基表数据

数据库日常维护与管理问题问题由于硬件或其它因素系统崩溃了，重要数据没有了怎么办？系统要升级，要把数据从一台机器移到另一台机器，如何做到？数据库备份使用企业管理器备份数据库使用SQL语句备份数据库：使用sp_addumpdevive：创建备份设备使用BACKUPDATABASE：备份数据库到备份设备上备份事务日志使用事务日志备份可以将数据库恢复到特定的即时点或恢复到故障点SQLServer2000中数据库恢复模型：简单恢复完全恢复大容量日志记录恢复使用企业管理器备份事务日志使用SQL语句备份事务日志（BACKUPLOG）还原数据库使用企业管理器还原数据库使用SQL语句还原数据库(RESTOREDATABASE)数据的导入和导出数据转换服务DTS【问题】使用DTS将Xk数据库中的Student表的数据导出到Excel表中分离和附加数据库使用bcp实用工具导入、导出数据本章小结数据库的备份和还原数据导入导出的几种方法：DTS分离和附加数据库利用SQL脚本配合bcp工具

数据库设计基础基本概念问题在软件开发过程中，数据库如何设计？在设计过程中要注意什么问题？

实体与联系实体：客观存在并可以相互区分的事物称为实体属性：实体所具有的某一特性联系：就是实体与实体之间的联系

联系的类型E-R图

选课学生课程学号姓名年龄课程号课程名学分课程号成绩学名关系模型关系模型概念E-R模型转换为关系模型方法：是一个实体或联系转换为一个表，属性就是表的列（字段）关系模型规范化I范式（1NF）每个属性都是不可再分的基本数据项，则称该关系是I范式。关键字

是能唯一标识每一行的列或列的组合。一个表可能有多个关键字，选定其中一个作为主关键字。其余的称为候选关键字。

关系模型

函数依赖

完全函数依赖部分函数依赖传递函数依赖II范式（2NF）II范式首先是I范式，而且每一个非主属性完全依赖于任何关键字。III范式（3NF）

III范式首先是II范式，且关系中的任何一个非主属性都不传递依赖于任何关键字数据完整性

数据完整性数据完整性就是关系（表）中数据的正确性和一致性。域完整性（用户定义的完整性）：要求表中列的值满足一定要求。如性别列的值只能是男或女。表完整性（实体完整性）：要求表中的行有一个唯一标识符，即表有主关键字。参照完整性（引用完整性）：要求有关系的表数据保持一致。外关键字（外键）外关键字（外键）外键作用：u

1.当在从表作Insert时，要保证外关键字的值一定在主表中存在。u

2.当在主表中修改了主关键字值，则在从表中要同步修改，或禁止修改主表。u

3.当在从表中修改外关键字值，要保证修改的值在主表中存在。

4.当删除主表记录，要注意从表中是否引用主关键字。若有，则禁止删除或同步删除从表记录。练习

练习对图书借阅管理作简单的需求分析，建立实体关系模型，画E-R图，然后转换为合理的数据库表。要求：1．确定各表主键2．按照3NF的要求验证表结构是否合理3．确定表间关系，明确外键。

4.考虑数据完整性本节小节实体和关系模型。

E-R图三要素。关系模型要规范到3NF、主键、外键保证数据完整性。

数据库与事务日志、表数据库的存储结构问题数据库设计完成后如何建到系统中去？当系统业务数据增长后，如何调整数据库容量呢？

数据库文件主文件：包含数据库的启动信息、数据信息——唯一的事务日志：包含恢复数据库的所有日志信息——至少一个次要文件：主文件中不包括的所有数据信息——零个、一个或多个物理文件（操作系统中实际存在的文件）和逻辑文件（数据库中的标志）数据库的存储结构（续）数据库文件组主文件组、次文件组、用户定义文件组可以提高数据库的查询性能设计文件和文件组的规则一个文件或文件组不能用于多个数据库，只能用于一个数据库一个文件只能是一个文件组的成员一个数据库的数据信息和事务日志信息总是放开存放的事务日志文件不能成为任何文件组的成员数据库对象关系图表视图存储过程用户角色规则默认用户定义的数据类型用户定义的函数全文目录系统数据库和示例数据库系统数据库master数据库：记录系统的所有系统级的信息model数据库：模板数据库msdb数据库：记录了有关SQLServerAgent服务的信息tempdb数据库：临时数据库，用于保存中间数据示例数据库Northwind数据库pubs数据库创建数据库使用企业管理器使用向导使用SQL语句问题：用SQL语句创建数据库sampdb，有一个数据文件20MB，一个日志文件10MB。

显示数据库信息

sp_helpdb创建数据库练习创建数据库名称newxk数据文件newxk_data.mdf,保存在c:\中,5MB-10MB,按1MB增长事务日志newxk_log.ldf,保存在c:\中,2MB-5MB,按10％增长创建名为Archive

的数据库，使用三个10MB的数据文件，包含在主文件组primary中，组中第一个文件Arch1为主要数据文件，同时有两个10MB的事务日志文件。管理数据库扩充数据库和事务日志的容量增加次要文件和事务日志文件【练习】为newdb数据库增加一个次要文件newdb_data2.【练习】将数据库sampdb的数据文件大小扩充到25MB

【练习】将sampdb的日志文件扩充到15M。

【练习】将sampdb数据库的数据文件压缩为3MB【练习】增加一个日志文件【练习】删除日志文件配置数据库（sp_dboption）缩小数据库和数据文件重命名数据库（sp_renamedb）删除数据库表数据库中的表的概念SQLServer的数据类型数据类别长度精度小数位数空值：没有输入的值，未知或未定义避免用空值直接参与运算尽量不要允许用空值（用默认值解决没有输入的问题）表操作创建表重命名表删除表修改表向表中输入数据使用企业管理器使用INSERT语句注意事项：对已经存在的值，系统不允许输入重复值对某些不允许为空的字段，不允许输入空值数据类型要和所对应的字段的定义相同values值列表的顺序要与表的字段顺序一致向表中输入数据（续）使用SELECT语句

INSERTtable_nameSELECTcolumn_list

FROMtable_listWHEREsearch_conditions注意事项：INSERT语句中和FROM子句中使用的表可以相同也可以不同，但必须使已经存在的表SELECT子句得到的结果集一定要和INSERT中指定的表兼容。（列的数量和顺序要相同）向表中输入数据（续）使用SELECTINTO语句

SELECTselect_list

INTOnew_table_nameFROMtable_listWHEREsearch_conditions注意事项：新表不能存在，否则会产生错误信息新表中的列和行是基于查询结果集的修改、删除表中的数据修改表中的数据问题：把学号为‘00000001’的学生姓名改为‘Alex’问题：把课程号在‘002’到‘005’之间的课程的报名人数增加1。涉及多个表的修改问题：把学号为’00000001’，选修课为’Java技术的开发应用’的自愿号（WillOrder）改为2。需要用到两个表StuCou和Course。

删除表中的数据DELETEtable_nameFROM…WHERE…问题：删除学号‘00000011’的学生的选课记录。涉及多个表的删除问题：学号为’00000002’的同学取消了’中餐菜肴制作’课程的选修，请在表StuCou中把他的选课信息删除。

本节小节数据库的存储结构（物理文件和逻辑文件）要求熟练掌握创建、删除和修改数据库的方法使用企业管理器和SQL语句创建、修改、删除表使用企业管理器和SQL语句输入、修改和删除表中的数据，尤其掌握多表的删除

应用系统开发问题问题如何开发一个MIS系统？系统目标在Delphi中使用SQLServer数据库表（1）在Delphi集成开发环境中通过菜单命令【File】→【New】→【Application】建立一个新的工程。（2）在【ADO】组件页上选择【ADOConnection】控件ADOConnection1放在表单上。（3）设置ADOConnection1的“ConnectionString”属性。如图16-1-1所示，在属性窗口中单击ConnectionString旁的“”按钮。（4）选中“UseConnectionString”，单击“Build”按钮。出现图16-1-2所示对话框。（5）选中“MicrosoftOLEDBProviderforSQLServer”，单击“下一步”，出现图16-1-3所示对话框。在Delphi中使用SQLServer数据库表（6）我们这里使用的是本地数据库。在“选择或输入服务器名称”中输入“(local)”。（7）选中“使用指定的用户名称和密码”。（8）在用户名称中输入“sa”，密码中输入相应的密码。（9）选中“允许保存密码”。（10）在“在服务器上选择数据库”下拉列表中选择数据库“Xk”。在Delphi中使用SQLServer数据库表（11）单击“测试连接”，测试成功后单击“确定”。（12）单击“确定”按钮，再单击“OK”按钮完成数据库连接的设置。（13）设置ADOConnection1的LoginPrompt”属性为“False”。（14）在【ADO】组件页上选择【ADOTable】控件ADOTable1放在表单上。设置ADOTable1的“Connection”属性为“AdoConnection1”，“TableName”属性为“Student”，“Active”属性为“True”。（15）在【DataAccess】组件页上选择【DataSource】控件DataSource1放在表单上。设置DataSource1的DataSet属性为ADOTable1。在Delphi中使用SQLServer数据库表（16）在【DataControls】组件页上选择【DBGrid】控件DBGrid1放在表单上。设置DBGrid1的DataSource属性为DatsSource1。（17）在【Stand】组件页上选择【Button】控件Button1放在表单上。设置Button1的Caption属性为“使用表”。（18）双击Button1按钮，在OnClick事件中添加如下代码。

AdoTable1.Active:=True;（19）在工具栏上单击“”按钮存盘。（20）在工具栏上单击“”运行按钮，运行后单击“使用表”按钮，可以看到Student表的相关数据。基于SQLServer的动态WEB开发实例（1）在Delphi集成开发环境中通过菜单命令【File】→【CloseAll】关闭所有文件。（2）在Delphi集成开发环境中通过菜单命令【File】→【New】→【Other】，选择ActiveX页，选中ActiveXLibrary，如图16-2-1所示，单击【OK】建立一个ActiveXDLL。（3）单击“”（SaveAll），工程名为“PXk”。（4）在Delphi集成开发环境中通过菜单命令【File】→【New】→【Other】，选择ActiveX页，选中ActiveServerObject，如图16-2-2所示。（5）单击“OK”按钮，出现如图16-2-3所示对话框，设置如下属性：

CoClassName：ASPObjectXkThreadingModel:Netural