还剩25页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
数据库原理与应用实验指导书信息管理与信息系统教研室辽宁工业大学2010年3月目录TOC\o1-3\h\z\u实验1数据库管理工具的使用及数据库的管理1实验2数据表的管理4实验3数据查询6实验4索引和视图及数据完整性9实验5Transact-SQL程序设计15实验6存储过程和触发器18实验7数据库的安全管理、备份恢复、导入导出22实验1数据库管理工具的使用及数据库的管理项目1数据库管理工具的使用
一、实验目的1.了解SQLServer2000安装对软、硬件的要求,学会安装方法2.了解SQLServer的注册和配置方法3.了解SQLServer2000包含的主要组件及其功能4.熟悉企业管理器和查询分析器的界面及基本使用方法5.对数据库及其对象有一个基本了解
二、实验准备1.了解安装SQLServer2000的软、硬件要求2.了解SQLServer2000支持的身份验证模式Windows的两种服务账户本地系统账户和域用户账户3.了解SQLServer各组件的主要功能4.对数据库、表和数据库对象有一个基本了解5.了解在查询分析器中执行SQL语句的方法
三、实验内容及步骤1.根据软、硬件环境的要求,安装SQLServer20002.通过“开始”→“程序”→MicrosoftSQLServer→“服务管理器”,打开“SQLServer服务管理器”窗口,选择SQLServer服务并且通过该窗口“启动”和“停止”SQLServer服务3.通过“开始”→“程序”→MicrosoftSQLServer→“企业管理器”打开企业管理器窗口4.在企业管理器中、使用“注册向导”注册服务器查看本地已住册的SQLServer搜索网络上另一台计算机,并且注册该机上的SQLServer,注册时使用“Windows认证模式”或“用我的SQLServer账户信息自动登录”的连接方式5.从SQL企业管理器中注销网络SQLServer6.打开本地服务器的属性对话框,查看以下信息产品名称、主机操作系统、产品的版本、操作平台、主机内存等7.利用企业管理器访问系统自带的pubs数据库
(1)以系统管理员身份登录到企业管理器
(2)在企业管理器的树形目录中找到pubs数据库并展开,查看该数据库的所有对象,如表、视图、存储过程、默认和规则等
(3)选择pubs数据库的“表”选项,在右窗格中将列出pubs数据库的所有表(包括系统表和用户表),选择用户表titles,右击,弹出快捷菜单,从中选择“打开表”→“返回所有行”菜单项,打开titles表,查看其内容8.利用查询分析器访问pubs数据库的表(l)通过“开始”→“程序”→MicrosoftSQLServer→“查询分析器”方式运行SQLServer查询分析器,或者在企业管理器中,选择“工具”→“SQL查询分析器”菜单项,运行SQLServer查询分析器
(2)在查询分析器窗口中,选择“查询”→“更改数据库…”菜单项,或者单击常用工具栏中的更改数据库下拉框的下拉按钮,选择要操作的pubs数据库
(3)在查询分析器的编辑窗口中,输入以下代码SELECTtype,avg(price)FROMtitlesWHEREroyalty=10GROUPBYtype
(4)选择查询分析器的“查询”→“分析”菜单项,或者单击常用工具栏上“分析查询”按钮,查询分析器将对输入的代码进行语法分析,并由消息窗格给出分析结果报告
(5)选择查询分析器的“查询”→“执行”菜单项,或者单击常用工具栏上“执行查询”按钮,SQLServer将编译窗口中的代码,并在结果窗格中显示查询结果,如图1所示图1查询分析器的编辑窗口
四、实验思考1.查询分析器的作用是什么?可以有几种启动查询分析器的方法?2.改变查询分析器的当前数据库,使用什么方法?项目2数据库的管理
一、实验目的1.了解SQLServer数据库的逻辑结构和物理结构的特点2.学会使用企业管理器对数据库进行管理3.学会使用Transact-SQL语句对数据库进行管理
二、实验准备1.确定能够创建数据库的用户是系统管理员,或是被授权使用CREATEDATABASE语句的用户2.确定要创建的数据库名、所有者(即创建数据库的用户)、数据库大小(最初的大小、最大的大小、是否允许增长及增长的方式)和存储数据的文件3.了解常用的创建数据库方法
三、实验内容及步骤1.在企业管理器中创建studentsdb数据库
(1)运行SQLServer管理器,启动企业管理器,展开服务器“(LOCAL)(WindowsNT)”
(2)右击“数据库”项,在快捷菜单中选择“新建数据库”菜单项在新建数据库对话框的名称文本框中输入学生管理数据库名studentsdb2.选择studentsdb数据库,在其快捷菜单中选择“属性”菜单项,查看“常规”、“数据文件”、“事务日志”、“文件组”、“选项”和“权限”页面3.打开studentsdb数据库的“属性”对话框,在“数据文件”选项卡中修改studentsdb数据文件的“分配的空间”大小为2MB指定“最大文件大小”为5MB在“事务日志”选项卡中修改studentsdb数据库的日志文件的大小在每次填满时自动递增5%4.在企业管理器中删除studentsdb数据库5.启动查询分析器,在查询分析器中使用Transact-SQL语句CREATEDATABASE创建studb数据库然后通过系统存储过程sp_helpdb查看系统中的数据库信息6.在查询分析器中使用Transact-SQL语句ALTERDATABASE修改studb数据库的设置,指定数据文件大小为5MB,最大文件大小为20MB,自动递增大小为1MB7.在查询分析器中为Studb数据库增加一个日志文件,命名为Studb_Log2,大小为5MB,最大文件大小为10MB8.使用企业管理器将studb数据库的名称更改为student_db9.使用Transact-SQL语句DROPDATABASE删除student_db数据库10.创建一个Company数据库,所有文件存放到D盘的SQL目录下该数据库的主数据文件逻辑名称为Company_data,物理文件名为Company.mdf,初始大小为10MB,最大尺寸为无限大,增长速度为10%;数据库的日志文件逻辑名称为Company_log,物理文件名为Company.ldf,初始大小为1MB,最大尺寸为5MB,增长速度为1MB11.添加一个包含两个数据文件的文件组和一个事务日志文件到Company数据库中12.删除Company数据库中的一个数据文件,然后删除此数据库
四、实验思考新数据库是否包含SQLServer在主设备中生成的模型数据库内的所有对象?实验2数据表的管理
一、实验目的1.学会使用企业管理器和Transact-SQL语句CREATETABLE和ALTERTABLE创建和修改表2.学会在企业管理器中对表进行插入、修改和删除数据操作3.学会使用Transact-SQL语句对表进行插入、修改和删除数据操作4.了解SQLServer的常用数据类型
二、实验准备1.了解在企业管理器中实现表数据的操作,如插入、修改和删除等2.掌握用Transact-SQL语句对表数据进行插入(INSERT)、修改(UPDATE)和删除(DELETE或TRANCATETABLE)操作
三、实验内容1.启动企业管理器,展开studentsdb数据库文件夹2.在studentsdb数据库中包含有数据表student_info、curriculum、grade,这些表的数据结构如图
1、图2和图3所示图1学生基本情况表student_info图2课程信息表curriculum图3学生成绩表grade3.在企业管理器中创建student_info、curriculum表4.在企业管理器中,将student_info表的学号列设置为主键,非空5.使用Transact-SQL语句CREATETABLE在studentsdb数据库中创建grade表6.student_info、curriculum、grade表中的数据如图
4、图5和图6所示图4student_info的数据图5curriculum的数据图6grade的数据7.在企业管理器中为student_info、curriculum、grade表添加数据8.使用Transact-SQL语句INSERTINTO…VALUES向studentsdb数据库的grade表插入以下数据学号课程编号分数00040001809.使用Transact-SQL语句ALTERTABLE修改curriculum表的“课程编号”列,使之为非空10.使用Transact-SQL语句ALTERTABLE修改grade表的“分数”列,使其数据类型为real11.使用Transact-SQL语句ALTERTABLE修改student_info表的“姓名”列,使其列名为“学生姓名”,数据类型为varchar
(10),非空sp_renamestudent_info.姓名学生姓名ALTERTABLEstudent_infoALTERcolumn学生姓名varchar10notnull12.分别使用企业管理器和Transact-SQL语句DELETE删除studentsdb数据库的grade表中学号为’0004’的成绩记录DELETEgradeWHERE学号=‘0004’13.使用Transact-SQL语句UPDATE修改studentsdb数据库的grade表中学号为’0003’、课程编号为’0005’、分数为90的成绩记录UPDATEgradeSET分数=90WHERE学号=’0003’and课程编号=‘0005’14.使用Transact-SQL语句ALTER…ADD为studentsdb数据库的grade表添加一个名为“备注”的数据列,其数据类型为VARCHAR
(20)ALTERTABLEgradeADD备注VARCHAR
(20)NULL15.分别使用企业管理器和Transact-SQL语句DROPTABLE删除studentsdb数据库中grade表
四、实验思考1.使用T-SQL语句删除在studentsdb数据库的grade表添加的“备注”数据列2.在企业管理器中,studentsdb数据库的student_info表的数据输入时,如果输入相同学号有什么现象?怎样避免该情况的发生?实验3数据查询
一、实验目的1.掌握使用Transact-SQL的SELECT语句进行基本查询的方法2.掌握使用SELECT语句进行条件查询的方法3.掌握嵌套查询的方法4.掌握多表查询的方法5.掌握SELECT语句的GROUPBY和ORDERBY子句的作用和使用方法;6.掌握联合查询的操作方法7.掌握数据更新语句INSERTINTO、UPDATE、DELETE的使用方法
二、实验准备1.了解SELECT语句的基本语法格式和执行方法2.了解嵌套查询的表示方法3.了解UNION运算符的用法4.了解SELECT语句的GROUPBY和ORDERBY子句的作用5.了解IN、JOIN等子查询的格式6.了解INSERTINTO、UPDATE、DELETE的格式与作用
三、实验内容和步骤1.在studentsdb数据库中,使用下列SQL语句将输出什么?1SELECTCOUNT(*)FROMgrade
(2)SELECTSUBSTRING(姓名,1,2)FROMstudent_info
(3)SELECTUPPER(’kelly’)
(4)SELECTReplicate(’kelly’3)
(5)SELECTSQRT(分数)FROMgradeWHERE分数=85
(6)SELECT2,3,POWER(2,3)
(7)SELECTYEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE())2.在studentsdb数据库中使用SELECT语句进行基本查询
(1)在student_info表中,查询每个学生的学号、姓名、出生日期信息
(2)查询学号为0002的学生的姓名和家庭住址
(3)找出所有男同学的学号和姓名3.使用SELECT语句进行条件查询
(1)在grade表中查找分数在80~90范围内的学生的学号和分数
(2)在grade表中查询课程编号为0003的学生的平均分
(3)在grade表中查询学习各门课程的人数
(4)将学生按出生日期由大到小排序
(5)查询所有姓“张”的学生的学号和姓名SELECT学号姓名FROMstudent_lnfoWHERE姓名LIKE’张%‘4.嵌套查询
(1)在student_lnfo表中查找与“刘卫平”性别相同的所有学生的姓名、出生日期SELECT姓名,出生日期FROMstudent_lnfoWHERE性别=(SELECT性别FROMstudent_lnfoWHERE姓名=’刘卫平‘)
(2)使用IN子查询查找所修课程编号为
0002、0005的学生学号、姓名、性别SELECT学号,姓名,性别FROMstudent_infoWHEREstudent_info.学号IN(SELECT学号FROMgradeWHERE课程编号IN(‘0002’,‘0005’))
(3)列出学号为0001的学生的分数比0002的学生的最低分数高的课程编号和分数SELECT课程编号,分数FROMgradeWHERE学号=’0001’AND分数>ANY(SELECT分数FROMgradeWHERE学号=‘0002’)
(4)列出学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数5.多表查询
(1)查询分数在80~90范围内的学生的学号、姓名、分数SELECTstudent_info.学号,姓名,分数FROMstudent_info,gradeWHEREstudent_info.学号=grade学号AND分数BETWEEN80AND90
(2)查询学习“C语言程序设计”课程的学生的学号、姓名、分数SELECTstudent_info.学号,姓名,分数FROMstudent_infoINNERJOINgradeONstudent_info.学号=grade.学号INNERJOINcurriculumON课程名称=‘C语言程序设计’
(3)查询所有男同学的选课情况,要求列出学号、姓名、课程名称、分数
(4)查询每个学生的所选课程的最高成绩,要求列出学号、姓名、课程编号、分数
(5)查询所有学生的总成绩,要求列出学号、总成绩,没有选修课程的学生的总成绩为空提示使用左外连接
(6)为grade表添加数据行学号
0004、课程编号为
0006、分数为76查询所有课程的选修情况,要求列出课程编号、课程名称、选修人数,curriculum表中没有的课程列值为空提示使用右外连接
6、使用UNION运算符将student_lnfo表中姓“张”的学生的学号、姓名与curriculum表的课程编号、课程名称返回在一个表中,且列名为u_编号、u_名称7.数据更新1)创建totalgrade表,具有数据列学号、姓名、总成绩CREATETABLEtotalgrade(学号char
(4)NOTNULL,姓名varchar
(8)Null,总成绩decimal
(52)NULL)
(2)使用INSERTINTO语句通过student_lnfo表更新totalgrade表的学号、姓名列数据INSERTINTOtotalgradeSELECT学号,姓名,总成绩=0FROMstudent_info
(3)使用UPDATE语句通过grade表更新totalgrade表的总成绩列数据,使totalgrade表中每个学生的总成绩为grade表中该学生各成绩之和UPDATEtotalgradeSET总成绩=(SELECTSUM(分数)FROMgradeWHEREtotalgrade.学号=grade.学号)FROMgrade
(4)删除totalgrade表中没有总成绩的学生记录
四、实验思考1.查询所有没有选修课程的学生信息,返回结果包括学号、姓名、性别2.在student_lnfo表和grade表之间实现交叉连接3.查询每个学生的所选课程的成绩,并列出学号生成分组汇总行(总成绩)和明细行(各课成绩)提示使用SELECT语句的COMPUTE选项实验4索引和视图及数据完整性项目1索引和视图
一、实验目的1.学会使用企业管理器和Transact-SQL语句CREATEINDEX创建索引2.学会使用企业管理器查看索引3.学会使用企业管理器和Transact-SQL语句DROPINDEX删除索引4.掌握使用企业管理器、向导等创建、管理和删除全文索引,并使用全文索引查询信息的方法5.掌握使用企业管理器和Transact-SQL语句CREATEVIEW创建视图的用法6.掌握系统存储过程sp_rename的用法7.掌握使用Transact-SQL语句ALTERVIEW修改视图的方法
二、实验准备1.了解聚集索引和非聚集索引的概念2.了解使用Transact-SQL语句CREATEINDEX创建索引的语法3.了解使用企业管理器创建索引的步骤4.了解Transact-SQL语句DROPINDEX删除索引的用法5.了解创建视图的Transact-SQL语句CREATEVIEW的语法格式及用法6.了解修改视图的Transact-SQL语句ALTERVIEW的语法格式7.了解视图更名的系统存储过程sp_rename的用法8.了解删除视图的Transact-SQL语句DROPVIEW的用法
三、实验内容l.分别使用企业管理器和Transact-SQL语句为studentsdb数据库的student_info表和curriculum表创建主键索引2.使用企业管理器按curriculum表的课程编号列创建唯一性索引3.分别使用企业管理器和Transact-SQL语句为studentsdb数据库的grade表的“分数”字段创建一个非聚集索引,命名为grade_indexCREATEINDEXgrade_lndexONgrade(分数)4.为studentsdb数据库的grade表的“学号”和“课程编号”字段创建一个复合唯一索引,命名为grade_id_c_indCREATEUNIQUEINDEXgrade_id_c_indONgrade(学号,课程编号)5.分别使用企业管理器和系统存储过程sp_helpindex查看grade表和student_info表上的索引信息sp_helpindexgrade6.使用企业管理器的‘向导”工具对grade表创建一个聚集索引和唯一索引7.使用系统存储过程sp_rename将索引grade_index更名为grade_indsp_rename‘grade.grade_index’,’grade_ind’,‘INDEX’8.分别使用企业管理器和Transact-SQL语句DROPINDEX删除索引grade_ind再次使用系统存储过程sp_helpindex查看grade表上的索引信息DROPINDEXgrade.grade_ind9.分别使用企业管理器和系统存储过程sp_fulltext_database为studentsdb数据库启用全文检索10.分别使用企业管理器和系统存储过程sp_fulltext_catalog为studentsdb数据库建立全文目录,命名为FT_stu为student_info表建立全文索引数据元executesp_fulltext_catalog‘FT_stu’,‘create’execsp_fulltext_table‘student_info’,‘create’,‘FT_stu’,‘PK_student_info’在建立全文索引数据元之前保证已为student_info表建立了PK_student_info索引11.为student_info表设置全文索引列名为’家庭住址’在全文目录中注册该表,激活表的全文检索能力column‘student_info’,’家庭住址‘,’add‘executesp_fulltext_table‘student_info’,‘activate‘12.填充全文目录,检查全文目录填充情况executesp_fulltext_catalog‘FT_stu’,‘start_full‘While(fulltextcatalogproperty(‘FT_stu’,‘populateStatus’)0)BEGINwaitfordelay,‘0030’——如果全文目录正处于填充状态,则等待30s后再检测一次END13.在student_info表中,分别使用CONTAINS和FREETEXT函数通过全文目录检索家庭住址包含25号的记录SELECT姓名,家庭住址FROMstudent_infoWHERECONTAINS(家庭住址,’25号’)14.使用系统存储过程从studentsdb数据库中禁用全文检索15.在studentsdb数据库中,以student_info表为基础,使用企业管理器建立名为v_stu_i的视图,使视图显示学生姓名、性别、家庭住址16.在studentsdb数据库中,使用Transact-SQL语句CREATEVIEW建立一个名为v_stu_c的视图,显示学生的学号、姓名、所学课程的课程编号,并利用视图查询学号为‘0003’的学生情况17.基于student_info表、curriculum表和grade表,建立一个名为v_stu_g的视图,视图中具有所有学生的学号、姓名、课程名称、分数使用视图v_stu_g查询学号为0001的学生的所有课程与成绩18.分别使用企业管理器和Transact-SQL语句修改视图v_stu_c,使之显示学号、姓名、每个学生所学课程数目19.使用Transact-SQL语句ALTERVIEW修改视图v_stu_i,使其具有列名学号、姓名、性别ALTERVIEWv_stu_i(学号,姓名,性别)ASSELECT学号,姓名,性别FROMstudent_info20.使用系统存储过程sp_rename将视图v_stu_i更名为v_stu_infosp_renamev_stu_i,v_stu_info21.利用视图v_stu_i为student_info表添加一行数据学号为
0015、姓名为陈婷、性别为女22.利用视图v_stu_i删除学号为0015的学生记录23.利用视图v_stu_g修改姓名为刘卫平的学生的高等数学的分数为8424.使用Transact-SQL语句DROPVIEW删除视图v_stu_c和v_stu_g
四、实验思考1.是否可以通过视图v_stu_g修改grade表中学号列数据?2.通过对视图上的操作,比较通过视图和基表操作数据的异同项目2数据完整性
一、实验目的1.掌握企业管理器和Transact-SQL语句(CREATERULE、DROPRULE)创建和删除规则的方法2.掌握系统存储过程sp_bindrule、sp_unbindrule绑定和解除绑定规则的操作方法,以及sp_help、sp_helptext查询规则信息、sp_rename更名规则的方法3.掌握企业管理器和Transact-SQL语句(CREATEDEFAULT、DROPDEFAULT)创建和删除默认对象的方法4.掌握系统存储过程sp_bindefault、sp_unbindefault绑定和解除绑定默认对象的操作方法,以及sp_help、sp_helptext查询规则信息、sp_rename更名规则的方法5.掌握企业管理器和Transact-SQL语句(CREATETABLE、ALTERTABLE)定义和删除约束的方法,并了解约束的类型
二、实验准备1.了解数据完整性概念2.了解创建规则和删除规则的语法,了解绑定规则和解除绑定规则的语法3.了解创建默认对象和删除默认对象的语法,了解绑定和解除绑定默认对象的语法4.了解约束的类型、创建约束和删除约束的语法
三、实验内容和步骤1.为studentdb数据库创建一个规则,限制所输入的数据为7位0~9的数字
(1)复制student_info表命名为stu_phone,在stu_phone表中插入一列,列名为“电话号码”完成以下代码实现该操作SELECT*INTOstu_phoneFROMstudent_infoALTERTABLEstu_phoneADD_______CHAR
(7)NULLstu_phone表结构如图1所示图1stu_phone表结构
(2)创建一个规则phone_rule,限制所输入的数据为7位0~9的数字实现该规则的代码为CREATE_______phone_ruleAS@PhoneLIKE‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9]’
(3)使用系统存储过程sp_bindrule将phone_rule规则绑定到stu_phone表的“电话号码”列上实现该操作的代码为sp_bindrule______’stu_phone.电话号码’
(4)输入以下代码,进行一次插入操作INSERTINTOstu_phone(学号,姓名,电话号码)VALUES(’0009’,‘王国强’,‘1234yyy‘)产生以下出错信息服务器消息513,级别16,状态1,行1列的插入或更新与先前的CREATERULE语句所强制的规则冲突该语句己终止冲突发生于数据库’studentsdb’表’stu_phone’,列’电话号码’试分析为什么会产生该出错信息?如果要实现插入操作,应修改INSERTINTO语句中的哪个值?phone_rule规则能否对其他操作(如DELETE)进行规则检查?2.使用企业管理器实现实验内容1的每个操作3.创建一个规则stusex_rule,将其绑定到stu_phone表的“性别”列上,保证输入的性别值只能是“男”或“女”4.使用系统存储过程sp_help查询stusex_rule规则列表,使用sp_helptext查询stusex_rule规则的文本,使用sp_rename将stusex_rule规则更名为stu_s_rule5.删除stu_s_rule规则注意stu_s_rule为stusex_rule更名后规则名,是否仍然绑定在stu_phone表的“性别”列上,应如何操作才能删除它6.在studentdb数据库中,建立日期、货币和字符等数据类型的默认对象
(1)在查询分析器中,完成以下代码,创建默认对象df_date、df_char、df_money一创建日期型默认对象df_dateCREATE_________df_dateAS’2006-4-12’GO一创建字符型默认对象df_charCREATEDEFAULTdf_char___________’unknown’GO一创建货币型默认对象df_moneyCREATEDEFAULT__________AS$100GO
(2)输人以下代码,在studentsdb数据库中创建stu_fee数据表CREATETABLEstu_fee(学号char
(10)NOTNULL,姓名char
(8)NOTNULL,学费money,交费日期datetime,电话号码char7
(3)使用系统存储过程sp_bindefault将默认对象df_date、df_char、df_money分别绑定在stu_fee表的“学费”、“交费日期”、“电话号码”列上_________df_money,’stu_fee.学费’GOsp_bindefault________,’stu_fee.交费日期’GOsp_bindefaultdf_char’stu_fee.电话号码’GO
(4)输人以下代码,在stu_fee表进行插入操作INSERTINTOstu_fee(学号,姓名)values(’0001’,’刘卫平’)INSERTINTOstu_fee(学号,姓名,学费)values(‘0001’,张卫民,$120)INSERTINTOstu_fee(学号,姓名,学费,交费日期)VALUES(‘0001’,马东’,$110,’2006-5-12‘)分析stu_fee表中插入记录的各列的值是什么?
(5)完成以下代码解除默认对象df_char的绑定,并删除之__________’stu_fee.电话号码’__________DEFAULTdf_Char按同样的方式,删除默认对象df_date、df_money7.使用企业管理器创建默认对象df_date、df_money,并将它们分别绑定到stu_fee表的“学费”、“交费日期”、“电话号码”列上,在插入操作中观察stu_fee表的数据变化情况,操作完成后,删除这些默认对象8.为student_info表添加一列,命名为“院系”,创建一个默认对象stu_d_df,将其绑定到student_info表的“院系”列上,使其默认值为“信息院”,对student_info表进行插入操作,操作完成后,删除该默认对象分别使用企业管理器和查询分析器实现9.在studentsdb数据库中用CREATETABLE语句创建表stu_con,并同时创建约束(l)创建表的同时创建约束表结构如图2所示约束要求如下
①将学号设置为主键(PRIMARYKEY),图2要创建的表的结构主键名为pk_sid.
②为姓名添加唯一约束(UNIQUE),约束名为uk_name.
③为性别添加默认约束(DEFAULT),默认名称为df_sex,其值为“男”
④为出生日期添加属性值约束CHECK〕,约束名为ck_bday,其检查条件为出生日期‘1988-1-l’
(2)在stu_con表中插入如表1-1所示的数据记录表1-1在stu_con表中插入的数据学号姓名性别出生日期家庭住址0009张小东1989-4-60010李梅女1983-8-50011王强1988-9-100012王强1989-6-3分析各约束在插入记录时所起的作用,查看插入记录后表中数据与所插入的数据是否一致?
(3)使用ALTERTABLE语句的DROPCONSTRAINT参数项在查询分析器中删除为stu_con表所建的约束10.用企业管理器完成实验内容9的所有设置11.在查询分析器中,为studentsdb数据库的grade表添加外键约束(FOREIGNKEY),要求将“学号”设置为外键,参照表为student_info,外键名称为fk_sid使用系统存储过程sp_help查看grade表的外键信息在grade表中插入以下记录,观察SQLServer会做何处理,为什么?如何解决所产生的问题?学号课程编号分数0100000178使用查询分析器删除grade表的外键fk_sid
四、实验思考1.在SQLServer2000中可采用哪些方法实现数据完整性?2.比较默认对象和默认约束的异同实验5Transact-SQL程序设计
一、实验目的1.掌握Transact-SQL的数据类型、常量变量、表达式等概念2.掌握程序中注释的基本概念和使用方法3.掌握程序中的流程控制语句4.掌握SQLServer2000中常用函数的用法5.掌握游标的概念和声明方法,以及使用游标进行数据的查询、修改、删除操作等
二、实验准备1.了解程序中注释的语法格式2.了解程序中的流程控制语句IF-ELSE、CASE、WHILE等控制流语句3.了解系统提供的常用数学函数、日期和时间函数、字符串函数和数据类型转换函数的用法4.了解函数的使用方法5.了解游标的使用方法
三、实验内容和步骤1.在查询分析器中,选择studentsdb数据库,输入以下代码DECLARE@stu_namevarchar(l0)SELECT@stu_name=姓名FROMstudent_infoWHERE姓名LIKE‘张%’SELECT@stu_name观察显示的结果,与student_info表中数据进行比较,@stu_name赋值的是SELECT结果集中的哪个数据?2.定义int型局部变量@grademax、@grademin、@gradesum,在grade表中查找最高分、最低分和总分,分别赋给@grademax、@grademin、@gradesum,并显示DECLARE@grademaxint,@grademinint,@gradesumintSELECT@grademax=max(分数),@grademin=min(分数),@gradesum=sum(分数)FROMgradeSELECT@grademax,@grademin,@gradesum3.使用SET命令将查询的结果数目赋值给int型局部变量@row给下面代码中的划线处填上适当的内容,以完成上述操作DECLARE@rowsSET________=(SELECTCOUNT(*)FROMgrade)________@rows一显示@rows的值4.以下代码在curriculum表中插入新记录DECLARE@intCIdint,@intErrorCodeintINSERTINTOcurriculum(课程编号,课程名称,学分)VALUES(’0006’,’VB程序设计’,2)SELECT@intCId=@@identity,@intErrorCode=@@errorSELECT@intCId,@intErrorCode将该代码段连续执行两次,观察两次显示的信息及curriculum表中数据的变化,为什么前后两次执行时显示的信息会不同?5.在studentsdb数据库的student_info表中,以“性别”为分组条件,分别统计男生和女生人数6.在grade表中,使用适当函数找出“高等数学”课程的最高分、最低分和平均分7.定义一个datetime型局部变量@studate,以存储当前日期计算student_info表中的学生的年龄,并显示学生的姓名、年龄在以下代码的划线部分填入适当内容,以实现上述功能DECLARE__________datetimeSET@studate=__________一给@studate赋值为当前日期SELECT姓名,__________(@studate)-year(出生日期)AS年龄FROMstudent_info8.运行以下代码,写出运行结果DECLARE@anit,@bnitSET@a=168SET@b=73SELECT@a&@b,@a│@b,@a∧@b9.在局部变量@stu_id中存储了学号值编写代码查询学号为0001的学生的各科平均成绩,如果平均分>=60则显示“你的成绩及格了,恭贺你!!”,否则显示“你的成绩不及格”IF((SELECTAVG(分数)FROMgradewhere学号=’0001’)60)PRINT’你的成绩不及格’ELSEPRINT’你的成绩及格了,恭贺你!!’10.运行以下代码段,写出运行的结果DECLARE@counterintSET@counter=1WHILE@counter10BEGINSELECT‘@counter的值现在为‘+CONVERT(CHAR2,@counter)SET@counter=@counter+1END11.查询grade表如果分数大于等于90.显示A;如果分数大于等于80小于90,显示B;如果分数大于等于70小于80,显示C;如果分数大于等于60小于70,显示D;其他显示E在以下代码的划线部分填入适当内容完成上述功能SELECT学号,分数,等级=CASE_____________分数>=90THEN‘A’WHEN分数>=80AND分数<90_____________‘B’WHEN分数>=70AND分数<80THEN‘C’WHEN分数>=60AND分数<70THEN_____________ELSE’E’ENDFROMgrade12.计算grade表的分数列的平均值如果小于80,则分数增加其值的5%;如果分数的最高值超过95,则终止该操作在以下代码划线处填入适当的内容以完成上述功能WHILESELECT_____________(分数)FROMgrade)<80BEGINUPDATEgradeSET分数=分数*l.05if(SELECTMAX(分数)FROMgrade)_____________BREAKELSE_____________ENDI3.编写代码计算并显示@n=1+2+3十…+2014.编写代码计算并显示1~100之间的所有完全平方数例如,81=92,则称81为完全平方数15.计算1~100以内的所有素数16.在studentsdb数据库中,使用游标查询数据
(1)打开查询分析器,声明一个stu_cursor游标,要求返回student_info表中性别为“男”的学生记录,且该游标允许前后滚动和修改
(2)打开stu_cursor游标
(3)获取并显示所有数据
(4)关闭该游标17.使用游标修改数据(l)打开stu_cursor游标
(2)将姓马的男同学的出生日期的年份加1
(3)关闭stu_cursor游标18.声明游标变量@stu_c,使之关联stu_cursor游标,利用@stu_c查询年龄在6~9月份出生的学生信息19.使用系统存储过程sp_cursor_list显示在当前作用域内的游标及其属性
四、实验思考1.使用游标对数据库的表进行删除和修改等操作2.流程控制语句与其他编程语言提供的语句有何差别?3.区分局部变量与全局变量的不同,思考全局变量的用处实验6存储过程和触发器
一、实验目的1.掌握通过企业管理器和Transact-SQL语句CREATEPROCEDURE创建存储过程的方法和步骤2.掌握使用企业管理器和Transact-SQL语句EXECUTE执行存储过程的方法3.掌握通过企业管理器和Transact-SQL语句CREATETRIGGER创建触发器的方法和步骤4.掌握引发触发器的方法5.掌握使用系统存储过程管理存储过程和触发器的方法6.掌握事务、命名事务的创建方法,不同类型的事务的处理情况
二、实验准备1.了解存储过程的基本概念和类型2.了解创建存储过程的Transact-SQL语句的基本语法3.了解查看、执行、修改和删除存储过程的Transact-SQL语句的用法4.了解触发器的基本概念和类型5.了解创建触发器的Transact-SQL语句的基本语法6.了解查看、修改和删除触发器的Transact-SQL语句的用法7.了解创建事务、处理事务的方法和过程8.了解锁机制
三、实验内容和步骤1.在查询分析器中输入以下代码,创建一个利用流控制语句的存储过程letters_print,该存储过程能够显示26个小写字母CREATEPROCEDUREletters_printASDECLARE@countintSET@count=0WHILE@count26BEGINPRINTCHAR(ASCII’a’+@count)SET@count=@count+1END单击查询分析器的“执行查询”按钮,查看studentsdb数据库的存储过程是否有letters_print使用EXECUTE命令执行letters_print存储过程2.输入以下代码,创建存储过程stu_info,执行时通过输入姓名,可以查询该姓名对应的学生的各科成绩CREATEPROCEDUREstu_info@namevarchar
(40)ASSELECTa.学号,姓名,课程编号,分数FROMstudent_infoaINNERJOINgradetaONa.学号=ta.学号WHERE姓名=@name使用EXECUTE命令执行存储过程stu_info,其参数值为“马东”如果存储过程stu_info执行时没有提供参数,能按默认值查询(设姓名为“刘卫平”),如何修改该过程的定义?3.使用studentsdb数据库中的student_info表、curriculum表、grade表
(1)创建一个存储过程stu_grade,查询学号为0001的学生的姓名、课程名称、分数
(2)执行存储过程stu_grade,查询0001学生的姓名、课程名称、分数
(3)使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g4.使用student_info表、curriculum表、grade表
(1)创建一个带参数的存储过程stu_g_p,当任意输入一个学生的姓名时,将从3个表中返回该学生的学号、选修的课程名称和课程成绩
(2)执行存储过程stu_g_p,查询“刘卫平”的学号、选修课程和课程成绩
(3)使用系统存储过程sp_helptext,查看存储过程stu_g_p的文本信息5.使用student_info表
(1)创建一个加密的存储过程stu_en,查询所有男学生的信息
(2)执行存储过程stu_en,查看返回学生的情况
(3)使用Transact-SQL语句DROPPROCEDURE删除存储过程stu_en6.使用grade表
(1)创建一个存储过程stu_g_r,当输入一个学生的学号,通过返回输出参数获取该学生各门课程的平均成绩
(2)执行存储过程stu_g_r,输入学号0002
(3)显示0002号学生的平均成绩7.输入以下代码,复制student_info表命名为stu2,为stu2表创建一个触发器stu_tr,当stu2表插入一条记录时,为该记录生成一个学号,该学号为学号列数据的最大值加l一复制student_info表命名为stu2SELECT*INTOstu2FROMstudent_infoGO一为stu2表创建一个INSERT型触发器stu_trCREATETRIGGERstu_trONstu2FORINSERTASDECLARE@maxchar
(4)SET@max=(SELECTMAX(学号)FROMstu2)SET@max=@max+1UPDATEstu2SET学号=REPLICATE(’0’,4一len(@max))+@maxFROMstu2INNERJOINinsertedonstu2.学号=inserted.学号执行以上代码,查看studentsdb数据库中是否有stu2表,展开stu2,查看其触发器项中是否有stu_str触发器在查询分析器的编辑窗口输入以下代码INSERTINTOstu2(学号姓名性别)VALUES(’0001’’张主’,‘女’)运行以上代码,查看stu2表的变化情况,为什么插入记录的学号值发生了改变?8.为grade表建立一个名为insert_g_tr的INSERT触发器,当用户向grade表中插入记录时,如果插入的是在curriculum表中没有的课程编号,则提示用户不能插入记录,否则提示记录插入成功请进行插入测试,分别输入以下数据学号课程编号分数00040003760005000769观察插入数据时的运行情况,说明为什么?9.为curriculum表创建一个名为del_c_tr的DELETE触发器,该触发器的作用是禁止删除curriculum表中的记录10.为student_info表创建一个名为Update_s_tr的UPDATE触发器,该触发器的作用是禁止更新student_info表中的“姓名”字段的内容11.使用Transact-SQL语句DROPTRIGGER删除Update_s_tr触发器12.为student_info表建立删除触发器如del_s_tr,要求当student_info表的记录被删除后,grade表中相应的记录也能自动删除13.在studentsdb数据库中,执行以下事务处理过程,说明其为隐性事务、显性事务、自动式事务
(1)BEGINTRANSACTIONINSERTINTOstudent_info(学号,姓名)VALUES(’0009’,’李青’)COMMITTRANSACTION
(2)SETIMPLICIT_TRANSACTIONSONGOINSERTINTOgrade(学号,课程编号)VALUES(’0005’,’0007’)GOIF((SELECTcount*)FROMculrriculumWHERE课程编号=’0007’)=0)ROLLBACKTRANSACTIONELSECOMMITTRANSACTIONSETIMPLICIT_TRANSACTIONSOFFSETIMPLICIT_TRANSACTIONSON的作用是什么?这里的事务由哪个语句启动?分析IF语句的功能,在grade表中插入“课程编号”的值为‘0007‘时,执行哪个事务管理语句(ROLLBACKTRANSACTION还是COMMITTRANSACTION?如果“课程编号”的值为’0003‘时,情况又如何?
(3)在
(1)、
(2)的基础上,执行以下事务INSERTINTOstudent_info(学号,姓名)VALUES(’0009’,’王晶’)GO该事务能否完成,为什么?14.分析以下嵌套事务处理过程具有几级嵌套,每级形套的事务名称是什么?BEGINTRANSACTIONoutertranINSERTINTOstudent_info(学号,姓名)VALUES(’0010’,’王晶’)BEGTNTRANSACTIONinnertranlSELECT@@TRANCOUNTINSERTINTOstudent_info(学号,姓名)VALUES(’0011’,’张磊’)BEGTNTRANSACTIONinnertran2SELECT@@TRANSACTIONINSERTINTOstudent_info(学号,姓名)VALUES(’0012’’陈进’)COMMITTRANSACTIONinnertran2COMMITTRANSACTIONinnertranlCOMMITTRANSACTIONoutertran说明在每个BEGINTRANSACTION语句和COMMITTRANSACTION语句块中当前事务数@@TRANCOUNT的值是多少?15.设计一个“选课”事务,每选一门课程,总学分增加该课程的学分数,如果所选课程的总学分>10,则选择的课程取消,即事务回滚(l)在studentsdb数据库中,从student_info表中复制表,并为stu_ch表添加一列,命名为“总学分”SELECT学号,姓名,性别INTOstu_chFROMstudent_infoALTERTABLEstu_chADD总学分intGO
(2)建立一个命名事务ch_c,当学号为@Sid的学生所选的课程(课程编号为@cid)的总学分没有超过10时,将学号和课程编号值(@sid,@cid)添加到grade表中,同时修改stu_ch表中的总学分,使总学分为当前总学分十所选课程的学分值(@c_h)否则,取消该事务,实现回滚DECLARE@sidchar
(4),@cidchar
(4),@c_hintSET@sid=‘0004’一学号为0004SET@cid=‘0003’一课程编号为0003SET@c_h=(SELECT学分FROMcurriculumWHERE课程编号=@cid)---@c_h是课程编号为@cid的课程的学分值BEGINTRANSACTIONch_cINSERTINTOgrade(学号,课程编号)VALUES(@sid,@cid)UPDATEstu_chSET总学分=总学分+@c_hWHERE学号=@sid------使stu_ch表的总学分列的值加@c_hIF((SELECT总学分FROMstu_chWHERE学号=@sid)>10)-----判断学号为@sid的学生总学分是否10BEGINROLLBACKTRANSACTIONch_c-----是,则回滚,取消INSERT和UPDATE操作PRINT‘总学分超过10’ENDELSECOMMITTRANSACTIONch_c
(3)连续执行事务ch_c,每次执行给@cid的赋值分别为‘0003’、‘0004’、‘0005’、‘0001’,观察事务ch_c处理结果及stu_ch表与grade表的变化比较@cid值为’0001’时,与其他取值执行时的不同结果,为什么?16.使用SELECT语句为student_info表添加表级锁定(NOLOCK),通过系统存储过程sp_lock查看有关锁的信息,注意锁的信息存储的数据库在SQLServer2000企业管理器中,观察用户对资源的锁定
四、实验思考l.用系统存储过程sp_helptext查看系统存储过程是怎么编写出来的2.用系统存储过程sp_helptrigger查看前面各触发器的类型3.为student_info表建立一个可以处理插入数据的事务的触发器实验7数据库的安全管理、备份恢复、导入导出项目1数据库的安全管理
一、实验目的1.掌握SQLServer的安全机制2.掌握服务器的安全性的管理3.掌握数据库用户的管理4.掌握权限的管理
二、实验准备1.了解SQLServer的安全机制2.了解登录账号的创建、查看、禁止、删除方法3.了解更改、删除登录账号属性的方法4.了解数据库用户的创建、修改、删除方法5.了解数据库用户权限的设置方法6.了解数据库角色的创建、删除方法
三、实验内容和步骤1.使用企业管理器注册向导注册一个SQLServer服务器,选择“Windows身份验证模式”注册如果已完成注册操作,先删除当前SQLServer注册项,再重新注册2.在企业管理器中通过SQLServer服务器属性对话框对SQLServer服务器进行混合认证模式配置3.在Windows中创建用户账户,命名为“st学号”.如st10使用企业管理器为该用户创建一个用于SQLServer的登录账户,使用Windows身份验证,服务器角色为systemadministrators,授权可访问的studentsdb数据库,及数据库中的访问角色为public和dbo4.使用系统存储过程sp_grantlogin为实验内容3创建的Windows用户授权,使其具有登录SQLServer的权限5.使用系统存储过程sp_addlogin创建混合模式验证的SQLServer登录,指定用户名为“st_学号”(不必在Windows中建立该用户,下面以st_11为例),密码为学号,默认数据库为studentsdb6.使用企业管理器或系统存储过程sp_grantdbaccess为登录账户st_11建立数据库用户账户,指定用户名为st_user7.使用企业管理器或系统存储过程sp_addsrvrolemember将登录账户st_11添加为固定服务器角色sysadmin,使st_11拥有角色sysadmin所拥有的所有权限8.使用企业管理器或系统存储过程sp_addrole为studentsdb数据库创建自定义数据库角色student,并使student具有INSERT、DELETE、UPDATE对象权限和CREATETABLE语句权限9.使用企业管理器或系统存储过程sp_addrolemember将st_user添加为数据库角色student的成员,使它具有student的所有权限10.使用企业管理器或系统存储过程sp_helprotect查看表student_info所具有的权限11.分别在studentdb数据库的grade表和student_info表中进行插入/删除记录的操作,查看操作结果是否具有相应的权限12.使用企业管理器或系统存储过程sp_revokedbaccess从当前studentsdb数据库中删除用户账户st_user13.使用系统存储过程sp_defaultdb修改SQLServer登录账户st_11的默认数据库为master14.使用企业管理器或系统存储过程sp_droplogin删除SQLServer登录账户st_11
四、实验思考1.使用系统存储过程查看固定服务器角色和固定数据库角色说明固定服务器角色和固定数据库角色各具有什么数据库管理权限?2.master数据库中的guest用户能否被删除,为什么?项目2数据库的备份与恢复
一、实验目的1.掌握备份和还原的基本概念2.掌握备份和还原的几种方式3.掌握使用企业管理器和Transact-SQL语句进行数据库的备份和还原的操作方法
二、实验准备1.了解备份和还原的基本概念2.了解使用企业管理器和Transact-SQL语句进行数据库备份的操作方法3.了解使用企业管理器和Transact-SQL语句进行数据库还原的操作方法
三、实验内容和步骤1.在企业管理器SQLServer文件夹下,右击“管理”→“备份”文件夹,在其中新建一个备份设备,命名为St_bk2.将studentsdb数据库完全备份到St_bk设备中,命名为st_bk备份,备份完成后验证备份
3、删除studentsdb数据库中的grade表4.利用数据库备份st_bk对studentsdb数据库进行恢复,比较恢复前后数据库的不同5.新建备份设备,命名为st_log,将studentsdb数据库日志备份到st_log中,并验证备份注意日志备份不能在简单恢复模型下进行,可以在企业管理器中打开要备份的数据库的“属性”对话框,选择“选项”选项卡的“故障还原”项的“模型”下拉框的“完全”或“大容量日志记录”恢复模型,然后再进行备份6.利用日志备份st_log对studentsdb数据库进行恢复7,使用Transact-SQL语句BACKUPDATABASE和RESTOREDATABASE对studentsdb数据库进行备份和还原8.使用Transact-SQL语句BACKUPDATABASE和RESTORELOG对studentsdb数据库进行日志备份和还原
四、实验思考1.哪些数据库文件应该定期备份?2.比较不同恢复模型下数据库的备份和还原操作的差异项目3数据库的导入、导出
一、实验目的1.掌握用企业管理器在SQLServer之间导入/导出数据的方法2.掌握用企业管理器在SQLServer和Excel之间导入/导出数据的方法3.掌握用企业管理器在SQLServer和文本文件之间导入/导出数据的方法4.掌握用企业管理器配置发布、分发、订阅服务器的方法及步骤5.掌握创建发布的方法及步骤6.掌握订阅发布的方法及步骤
二、实验准备1.了解使用企业管理器在SQLServer之间、SQLServer与其他数据文件之间导入/导出数据的方法及步骤2.了解复制的工作原理及机制3.了解用企业管理器设置发布、分发、订阅服务器的方法及步骤
三、实验内容和步骤1.使用企业管理器的DTS将studentsdb数据库导入到新的sl数据库,使sl数据库包含student_Info表和grade表2.将studentsdb数据库的grade表的每个学生的总成绩汇总为一个数据表,导入到数据库sl,且命名为total,包含列名为学号、总成绩3.在Excel2000中建立一个工作表grd,保存为工作簿文件stu.xls,其中包含以下数据项学号课程编号分数000500019500050002840005000375000600016800060003920006000579将数据文件stu.xls的数据导入到数据库sl的grade表的末尾,查看grade表是否增加了这6条记录4.使用windows的“记事本”建立一个文本义件grd1.txt,其中包含以下数据项学号课程编号分数0007000189000700047800080002670008000485文本格式为“ANSI”将文件grdl.txt的数据导入到sl数据库的grade表的末尾,完成后查看grade表是否增加了这4条数据记录注意导入时,源文件的格式为ANSI,分隔符为{,},第一行文字不需要时,选择跳过1行5.将studentsdb数据库的student_Info的数据导出为Excel2000文件stu_il.xls,并在Excel中打开该文件,查看与student_Info表的数据是否一致6.将studentsdb数据库的student_Info表的数据列学号、姓名、性别导出为文本文件stu_i2.txt,以分号“;”分隔,并在记事本中打开该文件,查看与student_Info表的数据是否一致7.使用企业管理器SQLServer为配置发布服务器和分发服务器选择“工具”“配置发布、订阅服务器和分发服务器”菜单项,采取自动启动SQLServer代理,代理服务器的账户为SQLAgent提示在SQLServer配置代理服务器时,若采用Windows验证模式登录SQLServer,则应先在Windows中建立用户,命名为SQLAgent,并使其“隶属于”administrator,并且设置密码SQL代理服务器的“服务启动账户”使用“本账户”,账户名输入为“\SQLAgent”,密码为该账户在Windows中定义时的密码8.创建快照复制发布内容从pubs数据库创建快照发布,允许排队更新订阅,订阅服务器的类型SQLServer2000服务器,将pubs数据库的authors表作为发布项目,命名为Pubs,选择authors表的au_id、au_Iname、au_fnamc、city、contract、msrpl_tran_version列数据为pubs发布的数据,采取署名订阅方式,按照调度运行快照代理程序查看企业管理器的控制树的“复制”→“发布内容”文件夹,是否有名为pubs的发布内容9.创建对发布服务器的pubs发布的强制订阅订阅数据库为studentsdb,采用排队更新订阅,连续地运行分发代理程序查看企业管理器的控制树的“复制”→“订阅”文件夹.应有pubs的订阅项,订阅数据库为studentsdb10.启动快照代理程序,查看订阅的内容在企业管理器控制树的“复制监视器”→“代理程序”文件夹中,右击“快照代理程序”,选择“启动代理程序”菜单项展开studentsdb数据库的表文件夹,可以看到订阅的内容,查看是否和发布的数据一致11.新建一个数据库,命名为sl为数据库sl创建请求订阅启动请求订阅,查看sl数据库的表文件夹中是否包含了authors表,其中列数据与pubs发布的数据是否一致12.在企业管理器中删除请求订阅13.在企业管理器中禁用发布如果再次进行数据发布时,需要如何操作?
四、实验思考1.将studentsdb数据库中student_info表,导出为Access2000的数据文件,并在Access中查看内容2.比较强制订阅和请求订阅的差别。