《网络数据库SQL》实验指导书.doc

附录 网络数据库SQL实验指导书主编 虞益诚实验课程： 网络数据库SQL适用专业： 信息管理与信息系统&经济管理类等上海应用技术学院2007年9月13实验一 SQL Sever实例安装、管理工具的使用及数据库的创建与管理1. 实验目的（1） 理解SQL Sever 2000服务器安装的过程与方法。（2） 掌握企业管理器与查询分析器的基本使用方法。（3） 熟悉数据库、表及其他数据库对象。（4） 了解SQL Sever 2000数据库的逻辑结构和物理结构及其结构特点。（5） 了解SQL Sever的基本数据类型及空值的概念。（6） 掌握在企业管理器中创建数据库。2. 实验准备（1） 了解SQL Sever 2000各种版本安装的软、硬件要求和安装的过程。（2） 了解SQL Sever 2000各组件的主要功能。（3） 对数据库及其他数据库对象有一个基本了解。（4） 了解在查询分析器中执行SQL语句的方法。3. 实验内容3.1 安装SQL Sever 2000根据软硬件环境，选择一个合适的SQL Sever 2000版本。3.2 利用企业管理器访问系统自带的Northwind数据库（1）启动SQL Sever 2000服务管理器。（2）以系统管理员身份登录到企业管理器。（3）在企业管理器的树形目录中展开数据库，找到Northwind并展开，则列出该数据库的所有对象，如表、视图、存储过程、默认规则等。（4）选中“表”，将列出Northwind数据库的所有表（包括系统表和用户表），在此以用户表Orders为例，选中该表，单击鼠标右键，出现一快捷菜单，执行“打开”“返回所有行”菜单项，打开该表，查看其内容。3.3 利用查询分析器访问Northwind数据库的表在查询分析器的输入窗口，输入如下T-SQL语句：use Northwind / goselect * from Orders / go把执行结果与用企业管理器打开的表的内容比较。3.4 分别使用企业管理器和T-SQL语句建数据库GZGL（1）在企业管理器中创建数据库GZGL要求数据库GZGL初始大小为10MB,最大为40MB,数据库自动增长，增长方式是按5%比例增长;日志文件初始为2MB，最大可增长到10MB,按1MB增长。数据库的逻辑文件名和物理文件名均采用默认值，分别为GZGL_DATA和C:program filesmicrosoft MSSQLDATA GZGL_DATA.MDF,事务日志的逻辑文件名和物理文件名也均采用默认值，分别为 GZGL_LOG和 C: program filesmicrosoft MSSQLDATA GZGL_ LOG.LDF。以系统管理员ADMINSTRATOR或SQL Server2000的sa用户及授权使用 CREATE DATABASE语句的用户登录 SQL SERVER服务器，启动企业管理器，右击数据库节点，在弹出的快捷菜单中选择“新建数据库”，然后，输入数据库名：GZGL；选择“数据文件”选项卡，设置增长方式和增长比例；选择“事务日志” 选项卡，设置增长方式和增长比例。在“数据文件”选项卡和“事务日志” 选项卡中，可以分别指定数据库文件和日志文件的物理路径等特性。（2）在企业管理器中删除创建的GZGL数据库在企业管理器中右击数据库：GZGL，在弹出的快捷菜单中选择“删除”。（3）用T-SQL语句创建数据库GZGL按照上述要求创建数据库GZGL。启动查询分析器，在“查询”窗口中输入如下T-SQL语句：create database GZGLon（name=GZGL_data，filename=c：program filesmicrosoftmssqldataGZGL_data.Mdf，size=10mb，maxsize=40mb，filegroth=5%）log on（name=GZGL_log，filename=c：program filesmicrosoftmssqldataGZGL_log.ldf，size=2mb，maxsize=10mb，filegrowth=1mb) / go单击快捷工具栏的执行图标或按F5执行上述语句，并用企业管理器查看执行结果。4. 练习用企业管理器和查询分析器查看Northwind数据库Orders和Customers表的内容。 实验二 数据表及其数据的创建与管理1. 实验目的（1） 掌握使用T-SQL语句创建数据表。（2） 掌握在企业管理器中对表进行插入、修改和删除数据的操作。（3） 掌握使用T-SQL语句对表进行插入、修改和删除数据的操作。（4） 了解T-SQL语句对表数据库操作的灵活控制功能。2. 实验准备（1） 明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREAT DATABASE 语句的用户。（2） 确定数据库所包含的表以及各表的结构，了解SQL Sever 2000的常用数据类型，以创建数据库的表。（3） 了解创建数据表的常用方法。（4） 理解对数据库表的插入、修改、删除都属于对数据的更新操作，对表数据的操作可以在企业管理器中进行，也可以由T-SQL语句实现。（5） 掌握T-SQL中用于对表数据进行插入、修改和删除的命令分别是INSERT、UPDATEH和DELETE（或TRANCATE TABLE）。（6） 了解使用T-SQL语句对表数据进行插入、修改及删除时，比在企业管理器中操作表数据灵活，功能更强大。3. 实验内容3.1 实验内容1)创建用于企业管理的员工管理数据库，数据库名为GZGL，包含员工的信息、部门信息以及员工的工资信息。数据库GZGL包含下列三个表： JBQK:职工基本情况表、BMXX:部门信息表、GZQK: 职工工资情况表。各表的结构分别如附表-1、附表-2、附表-3所示。附表-1 JBQK表结构列名数据类型与长度空 否说 明Employee_IDChar(6)Not Null职工编号NameChar(10)Not Null姓名BirthdayDate time(8)Not Null 出生日期SexBit(1)Not Null性别Code_IDChar(18)Not Null身份证号码AddressChar(20)Null地址Post_IDChar(6)Null邮编Phone_IDChar(12)Null电话号码Email AddressChar(30)Null电子邮件地址Department_IDChar(3)Not Null职工部门编号附表-2 BMXX表结构列名数据类型与长度空 否说 明Department_ID字符型Char(3)Not Null部门编号Department_ Name字符型Char(20)Not Null部门名称ManagerChar(10)Not Null主管经理姓名Note文本text(16)Null备注附表-3 GZQK表结构列名数据类型与长度空 否说 明Employee_ID字符型Char(6)Not Null职工编号wage小数型decimal（12,2）Not Null基本工资prize小数型decimal（12,2）Not Null奖金InCome小数型decimal（12,2）Not Null*应发工资(冗余字段为便于理解而设置可略)OutCome小数型decimal（12,2）Not Null应扣工资RealCome小数型decimal（12,2）Not Null*实发工资(冗余字段为便于理解而设置可略)3.2 实验步骤（1）通过企业管理器在GZGL中分别创建表jbqk、bmxx和gzqk 在企业管理器中选中数据库：GZGL右击GZGL新建表输入jbqk表中各字段信息单击保存图标输入表名jbqk，即创建了表jbqk。按同样的操作过程创建表bmxx和gzqk。（2）在企业管理器中删除创建的jbqk、bmxx和gzqk表在企业管理器中选择数据库GZGL的表jbqk单击右键删除，即删除了表jbqk。按同样的操作过程删除表bmxx和gzqk。（3）使用T-SQL语句创建jbqk、bmxx和gzqk表启动查询分析器在“查询”窗口中输入如下T-SQL语句：use GZGL / gocreate table jbqk(employee_id char(6) not null, name char(10) not null, birthday datetime not null,sex bit not null, Code_ID char(18), address char(20), Post_ID char(8),phone_ID_number char(12), email_address char(30),department_id char(3) not null,) / go单击快捷工具栏的执行图标或按F5，执行上述语句，即可创建表jbqk。用同样的操作过程创建表bmxx和gzqk，并在企业管理器在查看结果。（4） 在企业管理器中向数据库GZGL中的表输入数据在企业管理器中向jbqk表插入记录，选择并鼠标右击表jbqk“返回所有行”逐字段输入各记录值，输入完后关闭窗口。用同样的方法可完成向bmxx和gzqk表插入记录。（5） 在企业管理器中将表jbqk中编号为020805的记录的部门号改为003（6） 在企业管理器中选择表并鼠标右击表jbqk选择“返回所有行”将光标定位至编号为020805的记录的Employee_ID字段，改为003。（7） 在企业管理器中删除数据库GZGL表数据在企业管理器中删除表jbqk的第3、6行和gzqk的第2、11行。在企业管理器中选择并鼠标右击表jbqk选择“返回所有行”选择要删除的行单击鼠标右键删除关闭表窗口。用同样的方法在企业管理器删除表bmxx的第4行和第7行。（8） 使用T-SQL命令修改数据库GZGL中的表数据 使用T-SQL命令分别向GZGL数据库的Jbqk、Bmxx和Gzqk表中插入一行记录。启动查询分析器，在“查询”窗口中输入如下T-SQL语句：use GZGL / goinsert into jbqkvalues（011112，李志林，1973_5_3，1交通路10号，210002，4055633，SQL126.COM，5） / goinsert into bmxxvalues（2，人力资源部，张之中，null） / goinsert into gzqkvalues(011112,1200, 400, 1600,100,1500) / go单击快捷工具栏的执行图标或按F5，执行上述语句。在企业管理器中分别打开GZGL数据库的Jbqk、Bmxx和Gzqk表，观察其变化。（9） 使用T-SQL命令修改表中的某个记录的字段值启动查询分析器在“查询”窗口中输入如下T-SQL语句：use GZGL / goupdate gzqkset income=1800where employee_id=040608 / go单击快捷工具栏的执行图标或按F5，执行上述语句将编号为040608的职工应发工资改为1800。在企业管理器中分别打开GZGL数据库的Gzqk表，观察其变化。（10） 使用T-SQL命令修改Gzqk表中所有记录的值启动查询分析器在“查询”窗口中输入如下T-SQL语句：use GZGL / goupdate gzqkset RealCome = RealCome +500 / go 单击快捷工具栏的执行图标或按F5，执行上述语句将所有职工的收入增加500。可见，使用T-SQL语句操作表数据比在企业管理器中操作表数据更为灵活。（11） 使用TRANCATE TABLE语句删除表中所有行use GZGL / gotrancate table gzqk / go单击快捷工具栏的执行图标或按F5，执行上述语句，将删除Gzqk表中的所有行。4. 练习(1)用企业管理器和查询分析器创建“学生”数据库，并在其中库中创建学生表（STUDENT）、课程表（COURSE）和选课表（SC），库的大小、表的名字与结构自定。 (2)向实验二建立的表中输入数据，并修改其中的一条或多条数据，再删除部分或全部数据，最后用企业管理器查看数据变化情况。实验三 视图的管理与使用及数据查询与更新1. 实验目的（1） 掌握视图的管理与使用方法。（2） 重点掌握SELECT语句的使用方法及子查询（嵌套查询）的使用。（3） 掌握连接查询的使用。（4） 掌握SELECT语句的统计函数的作用和使用方法。（5） 掌握SELECT语句的GROUP BY和ORDER BY子句的作用和使用方法。2. 实验准备（1） 了解视图的概念和使用方法。（2） 了解SELECT语句的基本语法格式与执行方法。（3） 了解子查询（嵌套查询）的表示方法。（4） 了解SELECT语句的统计函数的作用。（5） 了解SELECT语句的GROUP BY和ORDER BY子句的作用。3. 实验内容3.1 管理视图SQL Server 2000提供了使用SQL Server 企业管理器和Transact-SQL 命令语句两种方法来管理视图。在此仅枚举后者实例。基于学生表建立用来显示学生姓名、性别、校名等信息的学生信息视图1。CREATE VIEW 学生信息浏览ASSELECT 学号,姓名,性别, 班级,编号,学分,校名 FROM 学生 where学分290 / go3.2 基本SELECT语句的使用（1） 根据实验二给出的数据表结构，查询每个职工的所有数据。在查询分析器的编辑窗口输入并执行如下语句。use GZGL / goselect * from jbqk / go（2） 查询每个职工的地址和电话，在查询分析器的编辑窗口输入如下语句并执行。use GZGL / goselect address，phone_ID from jbqk / go（3） 查询Employee_ID为000001职工地址和电话，在查询分析器的编辑窗口输入如下语句并执行。use GZGL / goselect address，phone_ID from jbqk where employee_id=000001 / go（4） 查询Jbqk表中女职工的地址和电话，在查询分析器窗口中输入如下语句并执行。use GZGL / goselect address as地址，phone_ID as电话号码 from jbqk where sex=0 / go（5） 计算每个职工的实际收入。在查询分析器的编辑窗口中输入如下语句并执行。use GZGL / goselect employee_id，实发工资=income-outcome from jbqk / go（6） 找出所有收入在1500-2500元之间的职工编号。在查询分析器的编辑窗口中输入如下语句并执行。use GZGL / goselect department_id，income as应发工资 from jbqk where income between 1500 and 2500 / go（7） 找出所有复姓“诸葛”职工所在部门的编号，并在查询分析器的窗口中输入并执行如下语句。use GZGL / goselect department_id from jbqk where name like诸葛% / go3.3 连接查询的使用（1）查询每个职工的情况及其薪水的情况。use GZGL / goselect jbqk.*，gzqk.* from jbqk，gzqkwhere jbqk。employee_id=gzqk。employee_id / go（2）查询“营销部”实际收入在3000以上职工的姓名及其薪水等详情。在查询分析器的编辑窗口中输入并执行如下语句。use GZGL / goselect a.Employee_ID, , c.Department_ Name,c.Manager,b.wage, b.prize, b.outcome,b.RealComefrom jbqk as a ,gzqk as b ,bmxx as cwhere a.employee_id=b.employee_id and a.department_id=c.department _id and c.department_name=营销部 and Realcome3000 / go3.4 统计函数ROUP BY、ORDER BY子句的使用（1）求职工的平均收入。在查询分析器的编辑窗口中输入并执行如下语句。use GZGL / goselect Employee_ID,avg（Realcome） from gzqk / go（2）求营销部职工的总人数。在查询分析器的编辑窗口中输入如下语句并执行。use GZGL / goselect count(employee_id) from jbqkwhere employee_id= (select department_id from bmxx where departmentname=营销部) / go（3）求各部门的职工数。在查询分析器的编辑窗口中输入如下语句并执行。use GZGL / goselect Employee_ID,count（employee_id） from jbqkgroup by department_id / go（4）将各职工按收入由低到高排列。在查询分析器的编辑窗口中输入并执行如下语句。use GZGL / goselect jbqk.*，gzqk.* from jbqk，gzqk where jbqk.employee_id=gzqk.employee_idorder by income / go3.5 子查询的使用（1）查询在营销部工作的职工的情况。在查询分析器的编辑窗口中输入如下语句并执行。use GZGL / goselect * from jbqk where department_id= （select department_idfrom bmxxwhere departmentname= 营销部） / go（2）查找营销部年龄不低于财务部职工最大年龄的职工的姓名。在查询分析器的编辑窗口中输入并执行如下语句。use GZGL / goselect name from jbqkwhere department_id in （select department_id infrom bmxxwhere department_name=营销部）andbirthdayall（select birthday from jbqkwhere department_id in（select department_idfrom bmxxwhere departmentname= 财务部） / go4. 练习对“信息管理”数据库的学生、班级、成绩和课程表进行视图和各种查询（包含简单查询、连接查询、子查询和模糊查询，以及分组和排序）管理。实验四 SQL程序设计、函数与规则等的创建与管理1. 实验目的（1）理解SQL程序结构与流控制语句的使用和具体设计。（2）熟悉变量与函数的使用方法。（3）掌握规则等的创建、使用与管理。（4）掌握默认值等的创建、使用与管理。 2. 实验准备（1）了解程序结构与流控制语句的内涵和SQL程序结构语句的编写技能。（2）了解与预习变量与函数等的内涵与使用方法。 （3）理解与预习规则等的创建、使用与管理的方法和技能。（4）理解与预习默认值等等的创建、使用与管理的方法和技能。 3. 实验内容3.1 数据的导入与导出DTS 导入导出向导可以帮助用户交互式地在源、目标数据源间进行数据的导入、导出和转换。DTS 导入向导则可从别的数据源中将数据导入SQL Server，并实现数据格式的转换。DTS 导出向导可以完成将SQL Server数据库中的数据转换为其它数据格式，并输出到其它数据文件。在你所登录的SQL Server 2000实例中新建“信息管理”数据库，而后利用企业管理器按教学要求并参考教材P259P263完成将外存中学生表、成绩表、课程表、班级表导入到信息管理数据库中。3.2 函数的使用方法利用查询分析器按授课要求选择ABS、RAND、SQRT、ROUND、AVG、SUM、ASCII、CHAR、STR 、SUBSTRING、LOWER、UPPER、LEFT、RIGHT、REPLACE等完成教材P352P365的实验练习。3.3 SQL程序设计（1）IF.ELSE 结构实例。利用查询分析器完成“判断数值大小，打印运算结果”的程序编写。declare u int, v int, w intselect u = 40, v =50, w=60if u vprint u v - 打印字符串“u v”else if v wprint v welse print w v（2）case结构实例。利用查询分析器完成调整课程表课时，学分为2的原课时上调10% ，学分为3的原课时上调3%，学分为4的原课时上调5%, 其它的上调5%。use 信息管理update 课程set 课时=casewhen 学分=2 then 课程*1.10when 学分=3 then 课程*1.30when 学分=4 then 课程*1.50else课程*1.05end（3）while结构实例。利用查询分析器完成使用循环计算所给局部变量的变化值。declare x int, y int, z intselect x = 7, y=8while x 10beginprint x -打印变量x 的值while y =1985_01_01and 出生日期=1960_01_01sp_bindrule bdr, 学生.出生日期goSp_unbindrule 学生.出生日期goDROP RULE birth_date_rulego3.5 默认值的创建与绑定（1）默认值的创建与删除创建默认值有两种方法：使用企业管理器和使用Transact-SQL语言（语法：CREATE DEFAULT default_name AS condition_expression）创建规则。删除默认值也有两种方法：使用企业管理器和使用Transact-SQL语言（语法：DROP DEFAULT default_name ,.n）删除默认值。删除一个默认值前，必须先将与其绑定的对象解除绑定。（2）默认值的绑定与解除默认值的绑定有两种方法：使用企业管理器和使用Transact-SQL语言（sp_bindefault）绑定默认值。解除绑定也有两种方法：使用企业管理器和使用Transact-SQL语言（sp_unbinddefault）。试创建成绩默认值grade_defa，并将其绑定到“成绩”表的成绩和补考成绩两个字段上，嗣后再将其删除，在此，仅以Transact-SQL语言为例加以表述，企业管理器方法可自行尝试。CREATE DEFAULT grade_defaAS 85 / gosp_bindefault grade_defa,成绩.成绩sp_bindefault grade_defa,成绩.补考成绩 / gosp_unbindefault 成绩.成绩sp_unbindefault 成绩.补考成绩 / goDROP DEFAULT grade_defa / go3.6 数据库的管理(选做) （1）备份设备的创建与删除（2）数据库的备份（3）数据库的恢复试创建磁盘备份设备，并备份数据库和日志文件（将数据库备份到称为test的逻辑备份设备上，并将日志备份到称为 testLog1 的逻辑备份设备上），最后还原数据库和删除备份设备。在此，仅以Transact-SQL语言为例加以表述，企业管理器方法可自行尝试。USE master EXEC sp_addumpdevice disk , test , c:testtest.dat EXEC sp_addumpdevice disk, testLog1, c:testtestLog1.dat / goBACKUP DATABASE 信息管理 TO testBACKUP LOG 信息管理 TO testLog1 / goRESTORE DATABASE 信息管理 from testRESTORE LOG 信息管理 FROM testLog1EXEC sp_dropdevice testEXEC sp_dropdevice test Log1 / go4. 练习参照教材进行变量、函数、数据库管理、程序结构与流控制语句、规则与默认值等的管理的练习。实验五 SQL存储过程和触发器的使用及SQL安全性等综合管理1. 实验目的（1）掌握存储过程的内涵、存储过程的创建与管理和使用方法。（2）掌握触发器的内涵、存储过程的创建与管理和使用方法。（3）熟悉认证模式的内涵、浏览与设置。（4）掌握登录账户与数据库用户的管理方法。（5）掌握固定服务器角色和固定数据库角色及其成员的管理方法。（6）掌握用户权限管理的方法。 2. 实验准备（1）了解存储过程与触发器的概念和使用方法。（2）了解认证模式的内涵与设置过程。（3）了解登录账户与据库用户的管理理念与具体方法。（4）了解固定服务器角色和固定数据库角色的管理及其成员的设置方法。（5）了解用户权限管理的内涵与方法。3. 实验内容3.1 创建与调用存储过程1）创建存储过程（1）基于“信息管理”数据库下的“学生”表，创建一个带SELECT查询语句的名为“查询_pro”的存储过程（存储过程只能建立在当前数据库上，故需先打开指定的数据库）。USE 信息管理 / goCREATE PROC 查询_proAS SELECT 学号, 姓名, FROM 学生 where 区域=西南 Order by学号 DESC / go（2）创建添加、修改、删除职工记录的存储过程Employee_Add、Employee_Update、Employee_Delete（程序代码略）。2）调用与运行存储过程可用EXECUTE语句运行一个存储过程，也可令存储过程自动运行。由sysadmin固定服务器角色使用sp_procoption过程可设置存储过程为自动运行。当一个存储过程标识为自动运行，在每次启动SQL Server 2000时，该存储过程便会自动运行。在此我们主要关注的是前者。调用运行刚才所建的“查询_pro”存储过程，只需在查询分析器的编辑窗口中输入并执行如下语句。 Excute 查询_pro / GO3.2 创建触发器通过触发器实现可实现对数据库GZGL下的表的相关操作。（1）当用户向Jbqk表插入或修改一条记录时，通过触发器触发完成某些操作（在触发器内引用 SELECT 语句显示相关信息）。USE GZGL / goCREATE TRIGGER 插入或修改检测ON JBQKFOR INSERT,UPDATEASSELECT * FROM insertedPRINT 可以在这里插入其它T-SQL语句，可以使用inserted表 / go（2）删除Bmxx表中的一记录的同时，通过触发器触发完成某些操作（行为自定，程序代码略）。（3）创建一个触发器，当用户试图在学生表中添加或修改数据时，触发器会触发完成删除记录并向客户端显示一条消息。USE 信息管理 / goCREATE TRIGGER 删除班级ON cjFOR DELETEASPRINT 使用DELETE触发器从学生库中删除相关行开始DELETE 学生 FROM 学生,deleted WHERE 学生.班级编号 = deleted.班级编号PRINT 使用DELETE触发器从学生库中删除相关行结束3.3 认证模式浏览与设置启动企业管理器下的SQL Server属性对话框，通过SQL Server属性下的安全选项卡浏览与设置认证模式。3.4 添加登录账户可以使用企业管理器和Transact_SQL语句添加登录账户。（1）展开服务器组及要创建登录账户的服务器节点下的安全性节点，实施创建登录账户，或用存储过程语句sp_addlogin来创建新登录账户。（2）例：创建一个名为“数据库”，密码为“jsj200406”，默认数据库为“信息管理” 的登录账户。EXEC sp_addlogin 数据库, jsj200406, 电子商务（3）然后用sp_password、sp_defaultdb 、sp_defaultlanguage 、sp_helplogins 、sp_revokelogin、sp_denylogin、sp_droplogin等系统存储过程语句完成查询与维护登录账户。3.5 创建、查看、删除数据库用户使用企业管理器和Tra