秋数据库应用实验指导

1、用实 梁文娟 目录 实验1 SQL Server的安装及管理工具的使用2 实验2交互式SQL2 实验3数据完整性和安全性管理13 实验4触发器和存储过程实验23 实验5数据库备份与恢复26 实验6数据库设计综合应用27 实验 1SQL Server 的安装及管理工具的使用 一、实验目的 1、通过对某个商用数据库管理系统的使用，了解DBMS 的工作原理和系统构架。 在此推荐数据库管理系统SQL Server 2005，该系统已经在机房 FTP上。你也可以从微软官 方网站上下载该系统。 2、熟悉对 DBMS 的操作 二、实验内容 1、根据安装文件的说明安装数据库管理系统。在安装过程中记录安装的选择

2、，并且对所作 的选择进行思考，为何要进行这样的配置，对今后运行数据库管理系统会有什么影响。 2、学会启动和停止数据库服务，思考可以用哪些方式来完成启动和停止。 3、掌握管理工具的使用 (1) SQL Server Management Studio 的使用 连接服务器的方法 查询编辑器的使用 (2) 配置管理器的使用 4、了解数据库系统的逻辑组件： 它们主要是数据库对象，包括基本表、视图、触发器、存储过程、约束等。今后将学 习如何操作这些数据库对象。 三、实验报告要求 1、给出安装过程中出现的错误及解决方法。 2、根据你所了解的SQL Server工具，写出部分主要组件的功能(以 SQL Se

3、rver2005为 例，写出 SQL Server Management Studio 、配置管理器)。 实验 2 交互式 SQL 2.1 创建数据库及表 一、实验目的 本次实验了解 SQL Server 2005 的启动，熟悉如何使用 SSMS 和 SQL 建立数据库和 表，并加深对于完整性的理解。 二、背景知识 在使用数据库的过程中，接触最多的就是数据库中的表。表是数据存储的地方，是数 据库中最重要的部分，管理好表也就管理好了数据库。 表是由行和列组成的。创建表的过程主要就是定义表的列的过程。表的列名在同一个表 中具有唯一性，同一列的数据属于同一种数据类型。除了用列名和数据类型来指定列的属

4、 性外，还可以定义其它属性：是否为空、默认值、标识符列、全局唯一标识符列等。 约束是 SQL Server 提供的自动保持数据库完整性的一种方法，定义了可输入表或表的 单个列中的数据的限制条件。在SQL Server中有5种约束：主关键字约束(Primary Key Constraint)、外关键 字约束(Foreign Key Constraint)、惟一性 约 束(Unique Constraint)、检查约束(Check Constraint)和默认约束(Default Constraint)。 三、实验内容 方法1:在SSMS中创建数据库和表 在本练习中，将使用 SQL Server2

5、005数据库管理系统创建 Student数据库，并在 Student数 据库中创建表Student,使你能够掌握使用SQL Server2005数据库管理系统创建数据库和表 的方法。 1) 启动 SQL Server2005 : 依次单击开始 所有程序SQL Server 2005 SQL Server Management Studio 启动 SQL Server 2005数据库管理系统 2) 登录数据库服务器： 点击“连接到服务器“对话框中的连接按钮连接到SQL Server 2005数据库服务器。 3) 创建数据库Student 在SQL Server 2005数据库管理系统的左边栏“对

6、象资源管理器”中右击数据库对象, 在弹出的快捷菜单中单击“新建数据库”命令。 在弹出的“新建数据库”对话框右侧的数据库名称中输入数据库名称“Stude nt”, 然后单击确定。 拟顶？- 号*申_ 熾;川酣 Stud.Tit 文件黑盘文件組 WftAdMMB）自动增卡 FfillUfiY Stnd “Student”，并右击 Student数 据库中的表工程，在弹出的快捷菜单中单击“新建表”命令。 在右侧在工作区中输入“Stude nt”表的信息，该表具有如下列: 列名 数据类型 完整性约束 Sno CHAR(5) 主码 Sn ame CHAR(10) 非空 Ssex BIT 无 Sage I

7、NT 无 Sdept CHAR(15) 无 s Mfcrosoft SQL Server Man-aigement StudFo Enprc-s- Snarrw Ssex L jsi adept 1TH1IMKP A CfXEQLCKP. . NT -字*甘0阿會 Wfe 曲l股电 ? Snol比*啊 d-ia-TC K bit Ink EJ Eun 妊W) 荟BE 皺认即St 单击文件菜单中的保存命令保存该表，并取名为Stude nt”。 方法二、使用SQL语句创建数据库和表 1）单击工具栏中的“新建查询”按钮，并在弹出的“连接到服务器”对话框中单击 “连接”按钮，新建一个 SQL脚本。 2

8、）在右侧的SQL脚本输入框中输入如下 SQL代码： CREATEDATABASE STUDENT -仓U建 STUDENT数据库 GO USESTUDENT GO - 转至U STUDENT数据库 在STUDENT数据库中创建表STUDENT CREATETABLE STUDENT ( Sno CHAR( 5) PRIMARYKEY, Sname CHAR： 10 )NOTNULL, Ssex BIT , Sage INT , Sdept CHAR 15) ) 单击工具栏中的执行按钮，运行SQL语句，完成数据库与表的创建。 四、实验任务 1.用两种方法建立一个新数据库HRM （也可任意取名）。

9、 2在数据库HRM中，建立如下所示的三个表： Employee 表 列名 数据类型 长度 是否允许为空 说明 EmployeelD Char 6 :否 员工编号，主键 Name Char 10 否 B名 Birthday Datetime 8 否 出生日期 Sex Bit 1 否 性别 Address Char 20 是 地址 Zip Char 6 是 邮编 Phon eNumber Char 12 是 电话号码 EmailAddress Char 30 是 电子邮件地址 Departme ntID Char 3 否 员工部门号，外键 Departme nts 表 列名 数据类型 长度 是否允

10、许为空 说明 Departme ntID Char 3 否 员工部门号，主键 Departme ntName Char 20 否 部门名 Note Text 16 是 备注 Salary 表 列名 数据类型 长度 是否允许为空 说明 EmployeeID Char 6 否 员工编号，外键 In come Float 8 否 收入 OutCome Float 8 否 支出 3对三张表输入数据（可任意输入，但注意要符合完整性要求，否则会出错，在本实验 中，外键设置可先不做要求） 五、实验报告要求 1、要求给出三个表创建的 SQL语句，要求建表时给出主外键约束、唯一约束、取空 值约束、用户自定义的约

11、束等。（注：在实验报告上只需写出其中一个表的SQL语句即 可） 2、举例说明如何操作会违背完整性（实体完整性、参照完整性）。 2.2 SQL数据查询语句 、实验目的 了解SQL Server 2005查询编辑器的启动，熟悉如何在SQL Server 2005查询编辑 器查询记录。 掌握SELECT语句的基本语法和查询条件表示方法 掌握GROUP BY和ORDER BY子句的作用和使用方法 掌握连接查询和子查询的使用方法 二、实验内容 1、单击工具栏中的“新建查询”按钮，并在弹出的“连接到服务器”对话框中单击“连 接”按钮，新建一个 SQL脚本。 2、在SQL脚本中写数据查询语句执行; 从表中查

12、询数据： 在查询分析器的查询窗口中输入SQL语句，如图所示。 lnx| 查询-TE ACHER331 .Test JE ACHER331 Administrator - C:DocumeiAs and SettingfV select * from tesi I :| 点击按钮，执行该SQL语句，在查询窗口下部出现一个输出窗口，如图所示。 三、实验任务 1对上节建立的表输入数据: Departme nts 表: Departme ntID Departme ntName Note 1 财务部 财务部 2 研发部 研发部 3 人力资源部 人力资源部 Employee 表 EmployeelD N

13、ame Birthday Sex Address Zip Phon eNumber EmailAddress Departme ntID 1001 李勇 78-3-12 0 河南 475001 3880378 lyhe nu .edu.c n 1 1002 ” 王敏 80-11-2 1 河南 475002 0378311 wmhe nu .edu.c n 1 1003 刘晨 78-6-22 0 河南 475003 0378322 lche nu .edu.c n 1 2001 张立 78-8-1 0 河南 475004 0378333 zlhe nu .edu.c n 2 2002 刘毅 82

14、-1-23 0 河南 475005 0378344 lyhe nu .edu.c n 2 2003 张玫 81-3-15 1 河南 475006 0378355 zmhe nu .edu.c n 2 3001 徐静 76-8-12 1 河南 475007 0378366 xjhe nu .edu.c n 3 3002 赵军 79-2-19 0 河南 475008 0378377 zjhe nu .edu.c n 3 Salary 表 EmployeeID In come OutCome 1001 3600 1500 1002 3300 1000 1003 3700 P1200 2001 400

15、0 1600 2002 3800 1800 2003 3800 1500 3001 4200 2000 3002 4100 1800 2、练习下面简单的查询语句: a) 查询每个雇员的所有信息 b) 查询每个雇员的地址和电话 c) 查询 EmployeeID 为 000001 的雇员的地址和电话。 d) 查询女雇员地址和电话，并用 AS 子句将结果中各列的标题分别指定为“地址”和 “电话”。 e) 计算每个雇员的实际收入。 f) 找出所有姓王的雇员的部门号。 思考：找出所有地址中含有“中山”的雇员的号码和部门号。 3、练习多表连接查询和嵌套查询： a) 查询每个雇员的情况及工资情况(工资=In

16、come - Outcome ) b) 查询财务部工资在 2200 元以上的雇员姓名及工资情况 c) 查询研发部在 1966 年以前出生的雇员姓名及其工资详情 d) 查询人力资源部雇员的最高和最低工资 e) 将各雇员的情况按工资由低到高排列 f) 求各部门的雇员数 g) 找出所有在财务部和人力资源部工作的雇员的编号 h) 统计人力资源部工资在 2500 以上雇员的人数 i) 求财务部雇员的总人数 j) 求财务部雇员的平均工资 k) 查找比所有财务部的雇员工资都高的雇员的姓名 l) 查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名 m) 查找在财务部工作的雇员的情况 四、实验报告要求 1、写出

17、与上述查询任务相对应的SQL 查询语句(注：在实验报告上写出2-d、2-e、 2-f 及 3 中所有练习的 SQL 语句) 2、并记录在实验过程中遇到的问题、解决办法及心得体会。2.3 视图的定义和操作 一、实验目的： 理解视图的概念，掌握视图的使用方法。 二、预计实验时间 ：上机一次 三、实验过程 1 对应 HRM 数据库，参照前面实验中完成的查询，按如下要求自行设计视图： 1)基于单个表按投影操作定义视图。 举例：定义一个视图用以查看所有员工的编号、姓名和出生日期。 2)基于单个表按选择操作定义视图。 举例：定义一个满足 sex= true 的员工的所有信息的视图。 3) 基于单个表按选择

18、和投影操作定义视图。 举例：定义一个视图用以查看部门号码为2 的所有员工的姓名、电话和邮件地 址。 4）基于多个表根据连接操作定义视图。 举例：定义一个视图用以查看所有员工的姓名、部门名及工资。 5）基于多个表根据嵌套查询定义视图。 举例：定义一个比所有财务部的雇员工资都高的雇员的信息的视图 6）定义含有虚字段（即基本表中原本不存在的字段）的视图。 举例：定义一个视图用以查看所有雇员的编号、姓名、年龄。 2. 分别在定义的视图上进行查询、插入、更新和删除操作，分情况（查询、更新）讨 论哪些操作可以成功完成，哪些不能成功完成，并分析原因。 四、实验报告要求： 1在实验报告中要给出上述任务的视图定

19、义语句；（注：实验报告上写出4、5、6的SQL 语句） 2、分情况讨论哪些操作可以成功完成，哪些不能成功完成。 查询操作：举出一个进行查询操作的例子。更新操作：更新操作包含插入、更新、删 除，针对不能成功完成更新操作的情况举出两个例子，并分析原因。 2.4 SQL数据更新语句 一、实验目的 加深对SQL数据更新（插入、修改及删除）语句的基本语法格式的掌握 掌握单个元组及多个元组的插入、修改及删除操作的实现过程 加深对更新操作时数据库中数据一致性问题的了解 加深对约束条件在数据更新操作执行中的作用问题的了解 二、实验内容 方法一：在SSMS中插入、删除及修改数据， 方法二：在查询编辑器中写 SQ

20、L插入、删除及修改数据 三、实验任务 设有一个SPJ数据库，包括S, P, J, SPJ四个关系模式: S (SNO , SNAME , STATUS , CITY ) 表-厂袁-dbo. F f表-dbo. J f摘要 列名 数据类型 允许空 SIS diar(2) SNAIVE varchar(20 0 STATUS char (4) 0 CITY char CIO) 0 P (PNO, PNAME , COLOR , WEIGHT ) 列名数据类型允许空 SZEI char (2) PNAME varchar(20) COLOR diar(4) WEIGHT int J (JNO, JN

21、AME , CITY ) 1列容 数据类型 允许空 1 JNO char (2) JNAME 0 CITY char(lD) 0 SPJ ( SNO, PNO , JNO, QTY ) 列名 数据类型 允许空 char (2) PNO char JNO char QTY int 0 供应商表 S由供应商代码（SNO）、供应商姓名（SNAME ）、供应商（STATUS）、供应商 所在城市（CITY ）组成； 零件表 P由零件代码（PNO）、零件名（PNAME ）、颜色（COLOR ）、重量 （WEIGHT ） 组成； 工程工程表 J由工程工程代码（ JNO）、工程工程名（JNAME ）、工程工程

22、所在城市 （CITY ）组成； 供应情况表 SPJ由供应商代码（SNO）、零件代码（PNO ）、工程工程代码（JNO）、供 应数量（QTY ）组成，表示某供应商供应某种零件给某工程工程的数量为QTY。试完成以 下操作： 1在S表中插入元组“ S6,华誉，40,广州” 2在J表中插入元组“ j8,传感器厂” 3 对每一个供应商，求他为各种工程供应零件的总数量，并将此结果存入数据库 4. 将P表中PNO值为p6的元组的color属性值改为绿，weight属性值改为60 5. 将SPJ表中前4个元组的qty属性值统一修改为 300 6. 将S表中city属性名含有“京”或“津”的相应 status属

23、性值增加100 7 将供应商S2为“一汽”工程工程所供应的零件数量修改为2000 &将全部红色零件的颜色修改为浅红色 9. 由S5供给j4的零件p6改为由S3供应 10. 在SPJ表中新增一名为 SDATE的属性列，对该表中的每一元组在SDATE属性列上填 上实验当时的日期和时间 11. 删除所在城市为“广州”的供应商记录 12删除所有零件名称中第一个字为“螺”字的零件记录，并在供应情况表中删除相应的 记录 13删除S3和S4两供应商为“三建”工程供应“螺母”或“螺丝刀”零件的相应供应情 况数据信息 四、实验报告要求 1、 写出与上述任务相对应的 SQL更新语句（实验报告上写出3、5、10的S

24、QL语句） 2、并记录在实验过程中遇到的问题、解决办法及心得体会。 实验3数据完整性和安全性管理 3.1数据完整性管理 一、实验目的 掌握域完整性的实现方法。 掌握实体完整性的实现方法。 掌握参照完整性的方法。 二、实验内容 数据库的完整性设置。 三、实验步骤 可视化界面的操作方法： 实体完整性 1. 将student表的“ sno”字段设为主键：在表设计界面中，单击左边的行选定块，选定 “ sno”字段，单击工具按钮设置主键。如图1所示： , MbriaEHTl MM 541-+ HsriitegMiiMlfc liM&d S*HEJ W；D HS.l TIRCEJ ftAkiRiU Zft

25、LD 1! 口画 社区口 *眩2卫 -1 m uST&attJa Sct*- w? E | J| SAff 田p .披齟露41 田匚J *! J AdvwrALTBWorka J Mn vt ir a WoMJW J US .J ReDwtSef1- BRMHtS#r.rTn4C4 :-:w HU _j KUilK El HJWoCWjrw 田3诲吐閲 田 3 ihi.iiuiflffi 朗一I W阁 .+. jj 3 孑 WW|b(iLijf RfX 竺 wd*w3) flniaCn 削性I 丨 Q t 章#b r*:- 怪僵1 ntuis: tri 运 E冲 I 图1 1.将sc”表的“

26、sno”和eno”设置为主键：在表设计界面中，单击并按住Ctrl 键拖动左边的行选定块，选定sno和eno字段，单击工具按钮设置主键。如图 2所示: 1 MicirawH 鼠jL wvvr Muinjirffcmpnl! &budia 立件也辟吋覗图辿顼目側 Sifei+HiO XflfP sa（w?社反利恥） A.飙互越皿su皿核匸b $书耳心包is丘 2品目丸 叵，謝穷.11引筆 |立童责i!治理器 坯掘边-力1 T 巳注引的!3 S15 ft许空 m vvetwt 10 r ：22 am v araMJ5 刑若 t LE= L* ifin s4?pt 2站M曲 ES B 再 db.cti

27、Mient* | USTH20Cr3.:-3fll S vMcbartf) 堆定的r：HE匚k均噸国! laOtbderteT* Mi 陶 CD) MwaMgaST Tuvrs | 口星 曰 CU-3H) 巳俺再RWI 牺汗则I Hr 血 O例 I到也4旦囲FT 创 胡 1 口 fil 1倒旳拥到也 沖.对 |+I-!-I+I+|+L IjJ USTE2003 (S5L 5ei vti 9-.D. j W 曰二 S &.S.?PJBJE m 丄 j J ArJwrtireWrtgrkj |J AdF血r曲心hfD卿 jj 加；*ag b J Rapo-tS-arwr RepoHSdtTTewD

28、fi U心 is _i毀:e晖耳:s.團 a i眾烧霰 ffii 口 cto.nw 田 n dtwisc 注1 m dh-a.iB ffl 口 dbotudert + j枫田 3 _j同黑遢 + _j Q1MK14 油! 1SJJE i 否工J 参照完整性 4.将student”表和sc”表中的sno”字段设为参照：打开sc”表的设计界面，点击工 具栏按钮I,在弹出的属性(properties)对话框中点击新建”按钮，在主键表 (Primary key table)”下拉框中选择student”表，在其下的字段选择框中选择sno”，在外键表 (Foreign key table)”下拉框中选择

29、sc”表，在其下的字段选择框中选择sno”，单击关 闭即可。见图4。 许空 it studio R沱弓世HP畐H 陛雷 I * 1 奉 i dlL. 玄胖上EE3C凹 科逅 i+RitLi Xi蛀区 0WWwHf 柯 EJ： IrtuLkrll. J林便宜皿皿Lh E&dQ丨Li山匕*|竖少3呂, ViwehsCLQJ r-chwf3OI -一鳶一 一拖Ln住. IQQ汗97耳严I曲！疔!EiT# 巴19存的殴 *TF*fc|J M Cfl9& E3 e 11也i也i也1 SIH7目I &l创创创创 #1 m 址I址I也I创I 命令方式操作方法： 实体完整性 1将student”表的“ sno

30、”字段设为主键： 当student”表已存在则执行： alter table stude nt add con stra int pk_s no primary key (sno) 当student”表不存在则执行： Create table stude nt(s no CHAR(5) primary key , sn ame CHAR(10) NOT NULL, ssex CHAR(2), sage int, sdept CHAR(4) 注:可用命令drop table student” 删除student” 表 2. 添加一身份证号字段，设置其惟一性.(注:操作前应删除表中的所有记录) A

31、lter table stude nt add id char(18) unique (id) 3. 将sc”表的“ sno”和eno”设置为主键： 当“sc”表已存在则执行： alter table sc add con stra int PK_S noCno primary key (sno,cno) 当“ sc”表不存在则执行： Create table sc(s no CHAR(5), eno CHAR(2), grade INT NULL, con stra int PK_S noCno primary key (sno,cno) 域完整性 4. 将“ ssex”字段设置为只能取“男

32、”，“女”两值： 当student”表已存在则执行： alter table student add constraint CK_Sex check (ssex in ( 男 ,女 ) 当student”表不存在则执行： Create table student(sno CHAR(5) primary key , sname CHAR(10), ssex CHAR(2) check (ssex in ( 男 ,女) , sage int, sdept CHAR(4) 5. 设置学号字段只能输入数字 : alter table student add constraint CK_Sno_Form

33、at check (sno like 0-90-90-90-90-9) 6. 设置身份证号的输入格式 : alter table student add constraint CK_ID_Format check (id like 0-90-90-90-90- 90-91-20-90-90-90-10-90-30-90-90-90-9_) OR (id like 0-90-90-9090-90-90-90-90-10-90-30-90-90-90-9) 7. 设置 18位身份证号的第 7位到第 10 位为合法的年份 (1900-2050) alter table student add con

34、straint CK_ID_Format2 check (len(id)=18 and ( (convert(smallint,substring(id,7,4) )=1900) and(convert(smallint,substring(id,7,4) )=22 or ssex=女and sage=20 ) 参照完整性 9.将student”表和sc”表中的“ sno”字段设为参照： 当“ sc”表已存在则执行： alter table sc add constraint FP_sno foreign key (sno) references student(sno ) 当“ sc”表不存

35、在则执行： Create table sc(sno CHAR(5) constraint FP_sno foreign key references student(sno), cno CHAR(2),grade INT NULL, constraint PK_SnoCno primary key (sno,cno) ) 完整性验证 1 实体完整性：在“ student”表数据浏览可视化界面中输入学号相同的两条记录将会出 现错误如图 5 所示： 兗件如匡)US(u)顶目迪査询设计歡虽Ifl.(D ffincwj WE()昭助:也 爲富直血皿8皿活畐4 J 鼻移矛= 蟲i表視毗甲H画毋 巳注牺的

36、厢咅禺 U(3耳口也 石：j將屁屋引宰 居gbSQ吓 lz，d USTE003 (SQL Server 9.D.J3W- m) Iz 丄 sd&ii * J 11 _J Ij 田 国 田 Q 田 回 AfiTMtWgHcflW 抑址旳 Q RepfftServer J RsportSwverTsffpDB J箱M T _1数据岸心因 _i舌 国：:ass惩 ffl n dbo.ccase JI Z3 dbo.sc SI 3女腐 Jl db砧“阳 t nr _j 机 EB F 一i间义闻 庄_1可坍程愕 更逼梵fi!(比 I ?酹炉口銅角 i =li 妙空j-3這由貝|电对gjj亠|也対血H创跑

37、番、.|目q琶|门曲 图5 或者在命令窗口输入下面两条命令也会出现错误提示： insertintostudentvalues (95003， 张三，男 ,24, CS insertintostudentvalues (95001， 李四，女 ,21 , CS , 立中 ID iWsLQ WEQm 宣呦ta XIHEEJ 工員(D1E(J 帘 E：tD | f 1M5W # W - *-? jn-Kt*ni： 阳口00阳眞吨-号事3侍肾氐*切严 nl UOcnE. val ues | 卯 g-tlOi d/iam口

38、damc. vm 1-lshk |,1 9S口 1 丄| ! KOOi 闫 0 RTm (El i ：TT*MWr 消总览别14r伙呑1,耳芒行 JJjSiT P&.IU1ARV RXT PK iMi*uC H 不fife左巧矗业o . .UZEL 中捕小工配惚- 泊句自歸止. Jj |QQ：7Q737&4归押.；期叽律畅砂曇爭那 -J 图6 F面的语句用来验证“ SC”表中的实体完整性: insert into sc values(95002, 1O,65) in sert into sc values(95002, 10,90) MlfiFd-c&iAi XjQLLMjn jFrriint

39、 Studio- 文胖Q 曲辑唱 llffi(v) 畫顛目5 工且由口(凹社鱼) 那皐更皿6迢，由茹 &芳U 衆血I 8 盅材il 巨ti熬翳.1=引擎 皿 LiAh2tH3 去吕 | 曲 LISTB2DG3 笙LS旳MJ3W E 一1如丽 数抵阵咲胆 AchrtrtueWotksDW xxcg P epor i： Ser vw Rpurt5*tv*i-TAfnpDB 亠丄UUUUUU wffi田田田曲田曰 I* THjPF競現朋 Q LJ 5? iU 口序.岐惡 田 n 竝LQoufe dbD.sc 二 dfew -ss 3 b.-itudri Uffl 回文国 一I町*握性 +1 味丨迂专

40、码融園gi二兰丨军班二 X-dHiffeudiOfc 1 U5ir62003.Kl - SQtQuerrt.MiiPiiWv liiseL- 1117 0 皀:匚 丁？|丄|.|芒弐 ： ”= 1L 5S inhere匚 vb 1 口貫鬥总弓口口立丨章 1 ! r 9Q| -infl I Cl行受橐勁 消氫亡沁扱别K.恍專1. K 2 违反了 PRIHAPY KEV 均甬 1 PK Sra&C&o1 s 不睫在时象dbo. -中fS A_ J B 语句已撼止* UJ 行3：列】Ch 1Crw 旳汗蚓| NJe庄E3乳1ejh.| _js51|云，,构时也l 也4也讨注J亍旦；3D 1诒总心斗旷

41、 辺旳 !蠡谭己墨匪但WIS- usnem te.o kimJ 2.域完整性： 使用下面的语句验证ssex”字段的域完整性: insert into student values(95009,张匀,大,20,CS, 申 MIriir net? 讥3KQ 電谭itt? fl B(E) XAtU n所有程序 SQL Server 2005 SQL Server Management Studio 启动 SQL Server 2005数据库管理系统 连接成功后，右键你的实例，选择属性 在 属性 窗口中， 转到 Security( 安全性 )项，在 服务器身份验证 中设置为 SQL Server 和

42、Windows 身份验证模式 ， 确定，根据提示，应该重新启动sql 服务 然后执行下面的语句启用sa用户，同时清除sa的密码(能成功登陆后再根据你的 需要设置 ) EXEC sp_password null，null，sa ALTER LOGIN sa ENABLE 语句执行完成后，再用sa连接你的实例，应该就没有问题了 . 2. 设置登录账户 创建使用 Windows 身份验证 (SQL Server Management Studio) 的 SQL Server 登录 名 ?在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中 创建新登录名的

43、服务器实例的文件夹。 ?右键单击“安全性”文件夹，指向“新建”，然后单击“登录名”。 ?在“常规”页上的“登录名”框中输入一个Windows 用户名。 ?选择“ Windows 身份验证”。 ?单击“确定”。 创建使用 SQL Server 身份验证 (SQL Server Management Studio) 的 SQL Server 登录 名 ?在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中 创建新登录名的服务器实例的文件夹。 ?右键单击“安全性”文件夹，指向“新建”，然后单击“登录名”。 ?在“常规”页上的“登录名”框中输入一个新登录名

44、的名称。 ?选择“ SQL Server 身份验证”。 ?输入登录名的密码。 ?选择应当应用于新登录名的密码策略选项。通常，强制密码策略是更安全 的选择。 ?单击“确定”。 通过 Transact-SQL 创建使用 Windows 身份验证的 SQL Server 登录名 ?在查询编辑器中，输入以下Transact-SQL 命令： CREATE LOGIN FROM WINDOWS 。 GO 通过 Transact-SQL 创建使用 SQL Server 身份验证的 SQL Server 登录名 ?在查询编辑器中，输入以下 Transact-SQL 命令： CREATE LOGIN WITH

45、PASSWORD = 。 GO 3. 设置数据库用户 创建数据库用户 在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中 创建新登录名的服务器实例的文件夹。 依次展开数据库，某个具体数据库，“用户” 右键单击用户，选择“新建用户” 输入用户名，选择登录名，确定 通过 Transact-SQL 创建数据库用户( create user)： create user dba for login dba with default_schema=dbo 并指定数据库用户“dba” 的默认 schema 是“ dbo ”。这意味着 用户 “dba” 在执行“

46、 select * from t ”，实际上执行的是 “ select * from dbo.t ”。 4 设置数据库角色 新建角色 在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中 创建新登录名的服务器实例的文件夹。 依次展开数据库，某个具体数据库，“角色” 右击选中新建角色 通过 Transact-SQL 新建角色： CREATE ROLE role_name 在对象资源管理器中设置数据库角色 在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中 创建新登录名的服务器实例的文件夹。 依次展开数据

47、库，某个具体数据库，“用户” 选中某用户，右击“属性”，在展开的数据库用户属性对话框中进行设置 通过 Transact-SQL 设置数据库角色 通 过 加 入 数 据 库 角 色 ， 赋 予 数 据 库 用 户 “ dba” 权 限 ： exec sp_addrolemember db_owner, dba 此时， dba 就可以全权管理数据库 mydb 中的对象了。 如果想让SQL Server登陆帐户dba”访问多个数据库，比如mydb2。可以 让 sa 执行下面的语句： use mydb2 go create user dba for login dba with default_sch

48、ema=dbo go exec sp_addrolemember db_owner, dba go 此时， dba 就可以有两个数据库 mydb, mydb2 的管理权限了！ 5.设置管理对象的权限 在对象资源管理器中授权： 选中需要授权的登录名、用户名或角色名 右键单击选择“属性”，在弹出的该对象的属性窗口中选择“安全对象”， 进行权限分配 通过 Transact-SQL 分配权限：通过 GRANT 和 REVOKE 语句 四、实验任务 1、创建角色 ProgramerRole ，拥有创建表，存储过程，视图权限，拥有对 Salary 表的查 询、修改、插入权限 2、创建一个登录账号 Test

49、login 3、创建对应于这个登录账号的数据库用户TestUser 4、将用户 TestUser 添加到 TestRole 角色中 五、实验报告要求 1、写出与上述任务相对应的 SQL 语句 2、思考数据库服务器上的角色有哪些？权限类型有哪些？ 3、并记录在实验过程中遇到的问题、解决办法及心得体会。 实验 4 触发器和存储过程实验 4.1 触发器实验 一、实验目的 掌握触发器的创建、修改和删除操作。 掌握触发器的触发执行。 掌握触发器与约束的不同。 二、实验要求 1. 创建触发器。 2. 触发器执行触发器。 3. 验证约束与触发器的不同作用期。 4. 删除新创建的触发器。 三、实验内容 （一）

50、示例 1. 创建触发器 启动SQL Server查询编辑器，选择要操作数据库，如“sc （学生选课）”数据 库。 在查询命令窗口中输入以下CREATE TRIGGE语句，创建触发器。 为sc（学生选课）表创建一个基于 UPDATE操作和DELETE操作的复合型触发 器，当修改了该表中的成绩信息或者删除了成绩记录时，触发器被激活生效， 显示相关的操作信息。 - 创建触发器 CREATE TRIGGER tri_UPDATE_DELETE_sc ON sc FOR UPDAT，E DELETE AS - 检测成绩列表是否被更新 IF UPDATE(成绩) BEGIN - 显示学号、课程号、原成绩和

51、新成绩信息 SELECT INSERTED课程号，DELETED成绩 AS原成绩， INSERTED成绩AS新成绩 FROM DELETED， INSERTED WHERE DELETED.号 INSERTED学号 END - 检测是更新还是删除操作 ELSE IF COLUMNS_UPDATED( )=0 BEGIN - 显示被删除的学号、课程号和成绩信号 SELECT被删除的学号=DELETED学号，DELETED课程号, DELETED成绩AS原成绩 FROM DELETED END ELSE - 返回提示信息 PRINT 更新了非成绩列！ 点击快捷工具栏上的快捷按钮，完成触发器的创建。

52、 2. 触发触发器 在查询命令窗口中输入以下 UPDATE sc SET成绩=成绩+5 WHERE课程号=101 在查询命令窗口中输入以下 UPDATE sc SET 课程号 = 113 WHERE课程号=103 在查询命令窗口中输入以下 DELETE sc UPDATE sc语句，修改成绩列，激发触发器。 UPDATE sc语句修改非成绩列，激发触发器。 DELETE sc 语句，删除成绩记录，激发触发器。 WHERE课程号=102 3. 比较约束与触发器的不同作用期 在查询命令窗口中输入并执行以下ALTER TABLE 语句，为 sc 表添加一个约束， 使得成绩只能大于等于 0 且小于等于

53、 100。 ALTER TABLE sc ADD CONSTRAINT C成绩 CHECK成绩 =0 AND成绩 =100) 在查询命令窗口中输入并执行以下UPDATE sc语句，查看执行结果。 UPDATE sc SET 成绩=120 WHERE课程号=108 在查询命令窗口中输入执行以下UPDATE sc 语句，查看执行结果。 UPDATE sc SET成绩=90 WHERE课程号=108 从这部分实验中，我们可以看到，约束优先于触发器起作用，它在更新 前就生效，以对要更新的值进行规则检查。当检查到与现有规则冲突时，系 统给出错误消息，并取消更新操作。如果检查没有问题，更新被执行，当执 行

54、完毕后，再激活触发器。 4. 删除新创建的触发器 在查询命令窗口中输入 DROP TRIGGER语句，删除新创建的触发器。 DROP TRIGGER tri_UPDATE_DELETE_sc 点击快捷工具栏上的快捷按钮，删除触发器。 (二)练习 1. 在 Student 表中编写 insert 的触发器，假如每个班的学生不能超过 30个，如果低 于此数，添加可以完成；如果超过此数，则插入将不能实现。 2. 在SC表上编写update触发器，当修改 SC表中的grade字段时将其修改前后的信 息保存在 SC_log 表中。 四、实验报告要求 1、 写出与上述任务相对应的SQL 语句 2、并记录在

55、实验过程中遇到的问题、解决办法及心得体会。 4.2 存储过程实验 一、实验目的 掌握用户存储过程的创建操作。 掌握用户存储过程的执行操作。 掌握用户存储过程的删除操作。 二、实验内容 1、创建带输入参数的存储过程的存储过程。 2、执行所创建的存储过程。 3、删除所有新创建的存储过程。 三、实验步骤 对应于student数据库 (1) 创建一个无参存储过程 StuScoreInfo,查询以下信息：学号，姓名，性别，课程名称，考试成绩 (2) 创建一个带参数的存储过程stu_ info,该存储过程根据传入的学生编号在stude nt表中查询 此学生的信息 . (3) 创建一个带参数的存储过程Stu

56、Score Info2,该存储过程根据传入的学生编号和课程名称 查询以下信息 ：姓名，课程名称 ，考试成绩 . (4) 编写带参数的存储过程 ，根据传入的课程名称统计该课程的平均成绩. (5) 编写存储过程 ,根据传入的课程名统计这门课的成绩分布情况,即按照各分数段统计人数 四、实验报告要求 1、写出与上述任务相对应的 SQL 语句 2、并记录在实验过程中遇到的问题、解决办法及心得体会。 实验 5 数据库备份与恢复 一、实验目的 熟悉数据库备份及恢复机制； 了解 SQL Server 的数据备份和恢复机制； 掌握 SQL-Server 中数据库备份和恢复的方法。 二、实验内容 以管理员帐号登录

57、 SQL Server Management Studio ，以原有数据库 student 为基础， 请使用 Management Studio 界面方式或 T-SQL 语句实现以下操作： 1. 针对数据库 stu 创建完全数据库备份集 stu.bak ，目标磁盘为 D： user stu.bak ； 2. 在数据库 stu 中新建数据表 ceshi ，内容自定，然后针对数据库 stu 创建差异备 份； 3. 向数据库 stu 的数据表 ceshi 插入部分记录，然后针对数据库 stu 创建事务日志 备份； 4. 根据需要，将数据库恢复到数据库 stu 的最初状态； 5. 根据需要，将数据库恢

58、复到创建数据表 ceshi 后的状态； 6. 根据需要，将数据库恢复到在 ceshi 表插入记录后的状态； 7. 针对现有数据库 stu 创建完全文件和文件组备份集 stu_file ，目标磁盘为 D： user stu_file.bak ； 8. 在当前数据库中新建数据表 ceshi2 ，然后针对数据库 stu 创建差异文件和文件组 备份； 9. 向数据库 stu 的数据表 ceshi2 插入部分记录，然后针对数据库 stu 创建事务日志 文件和文件组备份； 10. 根据需要，将数据库以文件和文件组方式恢复到创建数据表 ceshi2 后的状态； 11. 根据需要，将数据库以文件和文件组方式恢

59、复到数据表 ceshi2 插入记录后的状 态； 三、思考 说明数据库的备份和恢复的基本步骤； SQL Server 中的完全备份、事务日志备份和增量备份形式的功能特点； 四、实验报告要求 1写出与上述任务相对应的SQL语句 2、并记录在实验过程中遇到的问题、解决办法及心得体会。 实验 6 数据库设计综合应用 一、实验任务与要求 目的与任务 ： 1. 掌握数据库设计和实现的基本过程 2. 掌握数据库模式设计、分析和实现的方法 3. 了解数据库应用系统软件开发的一般过程。 实验基本要求 ： （1）学习相关的预备知识 （2）按照数据库设计与实现过程完成数据库的设计，应用程序的开发，上机调试、运行 （

60、3）写出课程设计报告 二、课程设计基础知识 数据库设计与实现的基本过程包括需求分析、概念结构设计、逻辑结构设计、数据库的 物理设计以及数据库的实施和维护等内容。需求分析确定系统的数据需求和处理需求；概 念结构设计通过对用户需求的综合、归纳和抽象，形成一个独立的概念模型；逻辑结构设 计则将概念模型转换为具体的数据库管理系统所支持的数据模型；数据库的物理设计结合 应用确定数据模型的存储结构和存取方式。在这些工作完成之后，就可以建立数据库并开 始调试运行了，在数据库的运行过程中必须对其进行维护包括数据库的备份与恢复、 性能分析与改进等等。 1概念结构设计 ER 图 概念模型是用户需求的抽象，通常用实