SQL实验指导.docx

资源描述

SQL实验指导.docx

《SQL实验指导.docx》由会员分享，可在线阅读，更多相关《SQL实验指导.docx（57页珍藏版）》请在冰豆网上搜索。

SQL实验指导.docx

SQL实验指导

MSSQLServer2005实验指导书

1.实验一、SQL2005初步

1.0实验内容与目的

（1）创建数据库Students；

（2）创建学生、课程、选课等3个数据表，并设置各种约束；表结构及约束如下：

✧学生S（序号SID、学号SNO、姓名Sname、性别SSex、生日SBirthday、年龄SAge、系部SDept、班级SClass），其中，序号--标识列、学号--主键、姓名--唯一键、性别--默认值”男”、年龄--计算列；

✧课程C（课程号CNO、课程名CName、先修课号Cpno、学分CCredit），其中，课程号--主键、先修课号--外键、学分--Check条件（1~8）；

✧选课SC（学号SNO、课程号CNO、成绩Grade），其中，（学号、课程号）--主键、学号--外键、课程号--外键、成绩--Check条件（0~100）；

（3）录入、编辑3个表的数据；若数据不符合约束条件，会出现失败情况，请注意分析；

（4）将建立的数据库文件拷贝出来（数据库脱机|分离），《实验二》上机时使用（数据库附加）；

（5）通过本次实验熟悉SSMS（企业管理器）的基本操作。

操作方法与要点如下：

1.1启动SSMS（俗称SQLServer企业管理器）：

✧“开始—程序—MicrosoftSQLServer2005—SQLServerManagementStudio”

1.2已注册服务器

✧打开窗口：

“<菜单>视图—已注册服务器”。

✧添加注册：

”数据库引擎<右>—新建—服务器注册”，”新建服务器注册—常规—服务器名称—浏览更多”，”查找服务器—本地服务器—数据库引擎—XX—确定”，”新建服务器注册—保存”。

✧移除注册：

”数据库引擎—XX<右>—删除”。

✧启动SQLServer服务：

”数据库引擎—XX<右>—启动”。

✧停止SQLServer服务：

”数据库引擎—XX<右>—停止”。

✧重启SQLServer服务：

”数据库引擎—XX<右>—重新启动”。

✧连接到对象资源管理器：

”数据库引擎—XX<右>—连接—对象资源管理器”。

✧查看与更改注册属性：

”数据库引擎—XX<右>—属性”。

说明：

在已注册服务器窗口中可注册多个SQLServer服务，包括本地（本机）、远程（网络上其它机器）的SQLServer服务器，可设置身份验证方式和连接属性。

1.3对象资源管理器

✧打开窗口：

“<菜单>视图—对象资源管理器”

✧连接SQL服务：

”连接—数据库引擎”，…

SQLServer8.0.xxx为SQLServer2000

SQLServer9.0.xxx为SQLServer2005

SQLServer10.0.xxx为SQLServer2008

✧断开连接：

”XX（SQLServer…）<右>—断开连接”。

✧启动、停止、重新启动SQL服务：

”XX（SQLServer…）<右>—启动|停止|重新启动”。

✧查看与更改服务器属性：

”XX（SQLServer…）<右>—属性”，”服务器属性—XX”，…

✧刷新服务器对象信息：

”XX（SQLServer…）<右>—刷新”。

，含连接、断开连接、刷新等。

注：

（1）SSMS（SQLServer企业管理器）为SQL服务器的客户端程序，必须刷新才能与SQL服务器同步。

（2）数据库操作多数是在”对象资源管理器”中进行，请同学们注意区分。

1.4摘要

✧打开窗口：

“<菜单>视图—摘要”

注：

摘要窗口显示对象资源管理器的选中对象内容。

1.5创建数据库

✧创建数据库：

”对象资源管理器—数据库<右>—新建数据库”，”新建数据库—数据库名称—<输入数据库名称>—确定”；确定前，可改变数据库文件目录：

”数据库文件—路径—

”；另外，还可改变初始大小、自动增长等。

”新建数据库—添加”可添加辅数据库文件或日志文件。

✧修改数据库：

”数据库—<右>--属性’’。

✧修改数据库名称：

”数据库—”，再次单击该数据库名。

1.6建表、修改表与表约束

✧建表：

”对象资源管理器—XX（SQLServer…）—数据库——表<右>—新建表”，输入列名、类型、可空等信息。

✧插入列：

”<选定一列><右>一插入列”。

✧删除列：

”<选定一列><右>一删除列”。

✧调整列顺序：

选定列并按住上下拖动。

✧主键：

”<选定一列或多列><右>一设置主键”，选多列：

Ctrl或Shift+点击某列。

✧外键：

”<任选一列><右>一关系”。

设置级联更新：

”外键关系—INSERT和UPDATE规范—更新规则—层叠”，设置级联删除：

”外键关系—INSERT和UPDATE规范—删除规则—层叠”

✧Check约束：

”<任选一列><右>一Check约束”。

如性别SSex的Check约束条件：

（ssex=’男’ORssex=’女’），或（ssexin（‘男’,‘女’））

✧Unique约束：

”<任选一列><右>一索引/键”，”索引/键—添加”，”类型--<选择>唯一键”，”列—<选择列>”，”（名称）--<修改约束名>”。

✧默认值/缺省值：

”<选定列>—列属性—默认值或绑定”。

✧标识列：

列为整数类型（BigInt、Int、SmallInt、TinyInt、Numeric（n,0）、Decimal（n,0））时才可设置，也称自动列；操作：

”<选定列>—列属性—标识规范”，选”是”，”标识种子”设初值（如1000），”标识增量”设步长（如1）。

✧计算列：

”<选定列>—列属性—计算所得的列规范—（公式）”，输入该列的计算公式，如根据生日列SBirthday计算其年龄列Sage，Sage列公式为：

year（getdate（））-year（SBirthday）

✧修改表结构：

”数据库——表—<右>—修改”，其它操作同上。

相关菜单：

”表设计器”；工具栏：

”表设计器”

1.7数据库关系图

数据库关系图是一个体现表间外键关系的图，一个数据库可以创建多个关系图，一张表也可以出现在多个图中；关系图可以用来建立和维护外键关系。

✧新建数据库关系图：

”数据库——数据库关系图<右>—新建数据库关系图”。

✧查看和维护关系图：

”数据库——数据库关系图—<右>—修改”

✧关系图-添加表：

”<图空白处><右>—添加表”。

✧关系图-添加外键：

”<选中主表主键列>—<按住并拖放到外建表的列上面>”。

注：

若失败，则检查2张表的数据，看是否符合外键约束。

✧关系图-删除外键：

”<选中外键><右>—从数据库中删除关系”。

✧关系图-查看与编辑外键：

选中外键，在”属性”窗口中查看和修改。

可修改：

约束名、主外键表列、级联删除、级联更新等。

✧关系图-属性窗口：

”<菜单>视图--属性窗口”。

✧关系图-保存：

”<菜单>文件—保存”。

1.8表数据窗口与数据的插入、删除、修改

✧打开表数据窗口：

”数据库——表—<右>—打开表”

✧添加数据：

在表底部空行输入

✧修改数据：

选定网格控件中的一格，直接修改；焦点离开该记录行时提交修改

✧删除数据：

”<选定一行或多行><右>--删除”

✧复制数据：

”<选定一行或多行><右>--复制”，可粘贴到记事本、Word、Excel等之中；粘贴到Excel时若出现乱码，则Excel菜单”编辑—选择性粘贴”，”选择性粘贴—方式—Unicode—确定”

✧粘贴数据：

从Excel复制若干行列数据，”<选定表底部空行><右>--粘贴”，或”<选定表底部空行>--”。

粘贴时要求：

列数及相应类型匹配

✧刷新数据：

”<菜单>查询设计器—执行SQL”，或”<工具栏>查询设计器—

”。

注：

在其它窗口或用户修改了该表数据时，显示的表数据可能与SQL服务器内容不一致

编辑表数据时，出错的可能原因：

数据与主键（唯一性要求、非空要求）、外键（或null，或取值范围为主表的主键列数据集）、非空、Check、Unique等约束相冲突。

1.9数据库的分离与附加、脱机与联机操作

✧分离数据库：

”数据库—<右>--任务—分离”，”分离数据库—删除链接<勾选>--确定”。

说明：

（1）分离数据库需要断开所有对该库的连接，”分离数据库—消息”提示了当前连接情况；

（2）分离前，应该查看数据库属性，记住其文件名称及目录。

✧附加数据库：

”数据库<右>—附加”，”附加数据库—添加<找到数据库文件>--确定”。

注：

若附加的数据库名已存在，则附加操作会失败。

✧数据库脱机：

”数据库—<右>—脱机”。

说明：

脱机后可以拷贝其数据库文件和日志文件，复制的文件可在其它SQL服务器进行附加数据库操作。

✧数据库联机：

”数据库—<右>—联机”。

注意：

要拷贝某数据库的数据文件和日志文件，必须先将该库“脱机”或“分离”；“联机”状态的数据库文件处于打开状态，是不能复制的。

2.实验二SQL脚本文件与查询窗口

2.0实验内容与目的

实验目的：

（1）掌握CREATEDATABASE语句与其子句含义；

（2）掌握CREATETABLE语句格式，及列名、列类型、可空与非空、主键约束、外键约束（含级联更新、级联删除）、唯一键约束、Check约束、默认值、标识列、计算列等表示法；

（3）掌握INSERTINTO语句格式；了解约束对数据的影响；

（4）学习查询窗口的使用与操作。

实验内容：

（1）将《实验一》中的Students数据库附加到本机SQLServer服务器；

（2）使用脚本生成工具生成Students数据库的SQL脚本，编辑得到简洁CREATEDATABASE语句后，保存到Students.SQL文件中；

（3）利用脚本生成工具分别生成3个表的SQL脚本，编辑得到简洁CREATETABLE语句（含约束）后，粘贴到Students.SQL文件中，并保存。

（4）在Students.SQL文件中添加3个表的Insert语句，并保存；

（5）删除原Student数据库，利用Students.SQL文件重建数据库及3个表；

2.1SQL脚本生成

✧生成数据库脚本：

”数据库—<右>—编写数据库脚本为—CREATE到—新查询编辑器窗口”，复制其中的CREATEDATABASE语句；其中，USE[master]--选择当前数据库，GO--执行前面的SQL语句。

✧生成表脚本：

”数据库——表—<右>—编写表脚本为—CREATE到—新查询编辑器窗口”，复制其中的CREATETABLE语句，并改写成含各种约束条件的简洁语句形式。

✧生成insert脚本：

”数据库——表—<右>—编写表脚本为—INSERT到—新查询编辑器窗口”，修改其子句VALUES（…）中内容为要插入的数据。

✧生成数据库中所有对象的SQL脚本：

”数据库—<右>—任务—生成脚本”，在”脚本向导”窗口中按提示操作。

2.2查询窗口

✧新建查询窗口：

”<工具栏>标准—

”。

注：

查询窗口是一个纯文本编辑器。

✧连接：

”<菜单>查询—连接—连接”，或”<工具栏>SQL编辑器—

”。

注：

查询窗口只有处于连接状态，才可以执行窗口中的SQL语句。

✧断开连接：

”<菜单>查询—连接—断开连接”，或”<工具栏>SQL编辑器—

”

✧选择当前数据库：

”<工具栏>SQL编辑器—

”，或执行use<数据库名>语句。

✧执行SQL语句：

”<工具栏>SQL编辑器—

”，可先选中想要执行的SQL语句，再执行；否则，会执行查询窗口中的所有SQL语句。

注：

（1）可执行所有SQL语句，而不仅仅是SELECT语句；

（2）数据库中一旦CREATE创建了某对象，该对象会永久存储在数据库中；故再次CREATE该对象，会因对象已经存在而报错。

✧保存到文件：

”<菜单>文件—保存XX.sql”。

注：

称为SQL脚本文件，文件后缀名.SQL

✧打开SQL脚本文件：

在Windows资源管理器中双击，或”<菜单>文件—打开—文件”。

✧拖入对象名：

选中各数据库对象，如：

<某数据库>|<某表>|<某列>|…等，按住并拖到查询窗口中，可得到该对象名。

3.实验三SQL查询

3.0实验内容与目的

实验目的：

（1）掌握Select语句的语法格式及其应用；

（2）掌握查询设计器的使用。

实验内容：

（1）建表，请执行附录A.1、A.2中的SQL脚本；

（2）完成附录B.1或B.2中的练习题；并在查询窗口中输入相应的Select语句，验证结果；

（3）使用查询设计器生成Select语句，快速得到你需要的Select语句，并验证结果；

（4）习题答案，参见附录B“SQL查询练习题与参考答案”之B.1、B.2

3.1表数据窗口与查询设计器工具栏

✧打开表数据窗口：

”数据库——表—<右>—打开表”

✧查询设计器工具栏：

，分别为：

显示关系图窗格、显示条件窗格、显示SQL窗格、显示结果窗格、更改类型、执行SQL、验证SQL语句语法、添加/移出分组依据、添加表等。

✧关系图窗格：

可添加或移出表---FROM子句。

多表时根据外键关系自动设置为内连接，可更改为左连接、右连接、全连接或交叉连接，也可删除连接。

✧条件窗格：

（列、别名、表、输出）---SELECT子句；（排序类型、排序次序）---ORDERBY子句；（筛选器、或..）---WHERE子句，同列之间---AND，异列之间---OR。

✧条件窗格<分组依据按下时>：

<分组依据=GroupBy>时，（列、别名、表、输出）---GROUPBY子句及SELECT子句；<分组依据!

=GroupBy>时，（列、别名、表、输出）---带集函数的SELECT列；<分组依据=Where>时，（筛选器、或..）---WHERE子句；<分组依据!

=Where>时，（筛选器、或..）---HAVING子句。

✧SQL窗格：

显示SELECT语句，在这里，看是不是你想要的Select语句！

✧结果窗格：

显示SELECT语句的执行结果。

✧查询属性窗口：

”<菜单>视图--属性窗口”。

可设置SELECT子句中的DISTINCT、TOPn、TOPnPERCENT；分组查询时，可设置GROUPBY的扩展选项：

WITHCUBE、WITHROLLUP；选中”关系图窗格--XX表”时，可设置表的别名；

3.2查询窗口与查询设计器

当前窗口为查询窗口时，系统会多出一个菜单--“查询”。

先将光标定位在想要得到的SQL语句的位置上，然后进行下面的操作。

✧查询设计器：

”<菜单>查询--在编辑器中设计查询”，弹出”查询设计器”窗口，该窗口包含三个窗格：

关系图窗格、条件窗格、SQL窗格，操作方法与3.1节相同，”查询设计器--确定”后会将SQL语句复制到查询窗口的当前光标位置。

3.3视图

视图定义与维护。

3.4索引

索引的定义与维护。

3.5同义词

同义词的定义与维护。

4.实验四T_SQL存储过程、函数、触发器、游标

4.0实验内容与目的

实验目的：

（1）学习存储过程、函数与触发器等编程方法，熟悉相关T_SQL语法；

（2）学习对存储过程、函数与触发器等进行调用与测试的方法；

（3）进一步熟悉SQL脚本文件，以及查询窗口的使用；

实验内容：

（1）案例1（基础）--必做；见4.1节

（2）案例2（提高）--选做；见4.2~4.5节

4.1案例1：

”学生-成绩”数据库

实验准备：

将附录A.1的SQL脚本复制到查询窗口，执行后，将会创建表并插入实验数据。

实验内容：

分别实现下列触发器、存储过程与函数，并测试其调用（触发）方法。

（1）触发器：

禁止插入数学系（MA）学生记录

createtriggertri_Stu

onStudentforinsert

begin

ifexists（select*frominsertedwhereSdept='MA'）

rollback;

end

测试方法：

打开学生表Student，插入一条系部为MA的记录；或用Insert语句插入。

（2）存储过程：

计算某系的学生人数

createprocedureGetStudentNumByDept

@deptchar

（2）,@Numintoutput

SELECT@Num=count（*）

FROMStudent

WHERE（Sdept=@dept）

测试方法：

declare@retint,@numOfStuint

EXEC@ret=GetStudentNumByDept'CS',@numOfStuoutput--不要漏写output

select@numOfStu学生人数,@ret返回码

或：

declare@numOfStuint

EXECGetStudentNumByDept'CS',@numOfStuoutput--不要漏写output

select@numOfStu学生人数

（3）函数：

计算某系的学生人数

createfunctionfn_GetStudentNumByDept（@deptchar

（2））

returnsint

begin

declare@Numint

SELECT@Num=count（*）

FROMStudent

WHERE（Sdept=@dept）

return@Num

end

测试方法：

selectdbo.fn_GetStudentNumByDept（'CS'）

或：

selectdistinctsdept,dbo.fn_GetStudentNumByDept（sdept）fromstudent

4.2案例2：

银行储蓄与信用帐户

案例2：

银行储蓄与信用帐户的存款、取款、转账业务，要求记录每一笔业务历史记录。

简易设计为2个表：

（1）帐户表（帐户号、身份证号、帐户余额、信用额度、开户日期、帐户状态），帐户状态：

有效、挂失、销户；

（2）交易记录（ID、帐户号、交易金额、交易时间、交易类型、对方帐户号），交易类型：

存款、取款、转出、转入。