《SQL万能数据库系统课程设计.doc》由会员分享，可在线阅读，更多相关《SQL万能数据库系统课程设计.doc（55页珍藏版）》请在三一办公上搜索。
1、SQL SERVER大型数据库实训报告题目： 填写系统名称 专业： 班级： 学生姓名： 学号： 2011年7月信息工程学院第1单元 数据库和表的创建一、实训目的1、了解数据库的逻辑结构、物理结构及其特点2、学会利用企业管理器对数据库进行操作3、学会利用Transact-SQL语句对数据库进行操作二、实训任务1、建立数据库，删除该数据库2、建立新数据库3、更改数据库属性4、设计表结构5、建立表，删除表6、更改表7、向表中插入数据，删除表中的数据，更改表中的数据三、实训要求1、数据库、表的创建、更改和删除要求用SQL命令和企业管理器两种方式完成2、要求数据库中至少包含两个文件组，主文件组中至少包含
2、两个数据文件，明确规定数据库存放的物理路径，文件大小，增长方式等属性。3、建立四张以上的用户表，字段类型要求合理、合法，为空性要求要合理。4、每张表给出至少三十条以上记录作为后续实训数据，实验数据要求合理5、表结构要用表格的形式给出，并添加到实训内容中6、将涉及到的SQL命令拷贝到“实训内容及步骤”中7、将实训过程、结果通过截屏的方式添加到“实训内容及步骤”中，并用适当的文字进行说明四、实训内容及步骤1、建立数据库，删除该数据库SQL命令方式建立和删除数据库：在“SQL Server Management Studio”窗口中单击“新建查询”按钮新建一个查询窗口，在“查询分析器”窗口中输入如下
3、Transact-SQL语句：CREATE DATABASE PXSCJON(NAME=PXSCJ_DATA,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataPXSCJ.mdf,SIZE=3MB,FILEGROWTH=1MB)LOG ON(NAME=PXSCJ_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataPXSCJ.ldf,SIZE=1MB,FILEGROWTH=10%)输入完毕后，单击“执行”按钮。当命令成功执行后，在“对象资源管理器”中
4、展开“数据库”可以看到，新建的数据库“PXSCJ”就显示于其中。删除数据库在“查询分析器”窗口中输入如下Transact-SQL语句：DROP DATABASE PXSCJGO企业管理器方式建立和删除数据库：第一步 以系统管理员身份登录计算机，在桌面上单击“开始”“所有程序” “Microsoft SQL Server2005”选择并启动“SQL Server Management Studio”。如图（1）所示。第二步 选择“对象资源管理器”中的“数据库”，右击鼠标，在弹出的快捷菜单中选择“新建数据库”菜单项，打开“新建数据库”窗口。如图（2）所示。第三步 在“新建数据库”窗口的左上方选择“
5、常规”选择页，在“数据库名称”文本框中填写要创建额数据库名称“PXSCJ”,其初始值大小为3MB，最大大小不限制，允许数据库自动增长，按1MB增长。日志文件初始值为1MB，最大大小不限制，允许数据库自动增长，增长方式是按10%比例增长。文件路径选择系统默认设置的路径。如图（3）所示。第四步 在“新建数据库”窗口的左上方选择“文件组”选择页。单击右下角的“添加”按钮，这时在“PRIMARY”行的下面会出现新的一行。在这行的“名称”列输入“NGroup”，单击“确定”按钮。如图（4）所示。完成数据库的建立。第五步 在“对象资源管理器”窗口中选择要删除的数据库“PXSCJ”右击鼠标，在弹出的窗户中选
6、择“删除”菜单项，在打开的“删除对象”窗口中单击“确定”即可。2、建立新数据库方法同“PXSCJ”建立方法相同。不再重复操作。3、更改数据库属性在数据库被创建后，数据文件和日志文件名就不能改变了。对已存在的数据库可以进行的修改包括：l 增加或删除数据文件：l 改变数据文件的大小和增长方式：l 改变日志文件的大小和增长方式：l 增加或删除日志文件：l 增加或删除文件组：l 数据库的重命名。SQL命令方式更改数据库属性：ALTER DATABASE database_name ADD FILE ,n TO FILEGROUP filegroup_name /*在文件组中增加数据文件*/| ADD
7、LOG FILE ,n /*增加日志文件*/| REMOVE FILE logical_file_name /*删除数据文件*/| ADD FILEGROUP filegroup_name CONTAINS FILESTREAM /*增加文件组*/| REMOVE FILEGROUP filegroup_name /*删除文件组*/| MODIFY FILE /*更改文件属性*/| MODIFY NAME = new_dbname /*数据库更名*/| MODIFY FILEGROUP filegroup_name /*更改文件组属性*/| SET ,.n WITH /*设置数据库属性*/|
8、COLLATE collation_name /*指定数据库排序规则*/;企业管理器方式更改数据库属性：第1步 选择需要进行修改的数据库PXSCJ，右击鼠标，在出现的快捷菜单中选择“属性”菜单项，如图2.5所示。第2步 选择“属性”菜单项后，出现“数据库属性-PXSCJ”窗口。从图中的选项卡列表中可以看出，它包括8个选项卡。所有对数据库的操作可通过此处进行。4、设计表结构1学生信息表结构创建学生信息表，表名为“XSB”,表结构如表1所示。表1 学生信息表（XSB）结构列名数据类型长度是否可空默认值说明学号char6无主键,由年级号, 班级号,序号组成姓名char8无性别bit默认11: 男 0
9、: 女出生时间datetime默认无专业char12无总学分int默认00总学分52GO 命令方式更改表中数据：USE PXSCJGOUPDATE XSBSET 总学分= 总学分+10GOUPDATE XSB SET 专业= 软件工程, 备注= 提前修完学分, 学号= 081261 WHERE 姓名= 罗林琳GOSELECT * FROM XSB2企业管理器方式向表中插入数据：将光标定位在当前表尾的下一行，然后逐列输入列的值。每输入完一列的值，按回车键，光标将自动跳到下一列，便可编辑该列。企业管理器方式删除表中数据：在表数据窗口中定位需删除的记录行，单击该行最前面的黑色箭头处选择全行，右击鼠标
10、，选择“删除”菜单项 企业管理器方式更改表中数据：先定位被修改的记录字段，然后对该字段值进行修改，修改之后将光标移到下一行即可保存修改的内容。五、遇到的问题及解决办法六、实训评价（教师填写） 第2单元 数据库查询及T-SQL语言一、实训目的1、掌握使用SQL语句进行查询的基本方法2、掌握视图创建更改删除。3、掌握游标创建更改删除4、掌握T-SQL语言二、实训任务1、写一个SQL语句，实现查询，实现选择一个表中指定的列，修改查询结果中的列标题2、写一个SQL语句，实现查询，要求消除结果集中的重复行、限制结果集返回行数3、查询数据库表中某字段值大于等于一定值的记录4、用LIKE谓词查询指定字段中包
11、含指定字符串的记录5、写一个SQL语句，实现查询，要求查询条件中使用BETWEEN AND关键字6、用IN实现子查询、用JOIN关键字实现跨表查询7、对某字段进行汇总8、利用GROUP BY子句对查询结果分组9、对查询结果按某字段排序10、创建、修改、删除游标11、从游标中读取数据12、创建视图13、查询视图14、用循环语句实现查找某字段中最小值15、利用内置函数得到某字段的最大值16、建立一个用户自定义函数实现求某字段平均值17、删除用户自定义函数三、实训要求1、视图的创建、更改和删除要求用SQL命令和企业管理器两种方式完成2、将涉及到的SQL命令拷贝到“实训内容及步骤”中3、将实训过程、结
12、果通过截屏的方式添加到“实训内容及步骤”中4、在需要的时候对实训过程进行一定的文字说明四、实训内容及步骤1、用SQL语句，实现查询，实现选择一个表中指定的列，修改查询结果中的列标题查询XSB表中计算机系同学的学号，结果中列的标题为numberSQL语句：SELECT 学号AS numberFROM XSBWHERE 专业= 计算机执行结果：2、写一个SQL语句，实现查询，要求消除结果集中的重复行、限制结果集返回行数对XSB表只选择专业和总学分，消除结果集中的重复行。SQL语句：SELECT DISTINCT 专业,总学分FROM XSB执行结果：对XSB表选择姓名、专业和总学分，返回结果集的前
13、6行SQL语句：SELECT TOP 6 姓名,专业,总学分FROM XSB执行结果：3、查询数据库表中某字段值大于等于一定值的记录在XSB表中统计总学分在50分以上的人数。SQL语句：SELECT COUNT(总学分) AS 总学分在分以上的人数 FROM XSB WHERE 总学分=50;执行结果：4、用LIKE谓词查询指定字段中包含指定字符串的记录查询XSB表中学号倒数第3个数字为1，且倒数第1个数在12之间的学生学号、姓名及专业。SQL语句：SELECT 学号,姓名,专业 FROM XSB WHERE 学号LIKE %1_12 执行结果：5、写一个SQL语句，实现查询，要求查询条件中使
14、用BETWEEN AND关键字查询CJB表中成绩在90100之间的学号、课程号、成绩。SQL语句：SELECT * FROM CJB WHERE 成绩 BETWEEN 90 and 100执行结果：6、用IN实现子查询、用JOIN关键字实现跨表查询查找选修了课程号为206的课程的学生情况SQL语句：SELECT *FROM XSBWHERE 学号IN( SELECT 学号FROM CJB WHERE 课程号= 206)执行结果：查找选修了206号课程且成绩在80分以上的学生姓名及成绩SQL语句：SELECT *FROM XSBWHERE 学号IN( SELECT 学号FROM CJB WHER
15、E 课程号= 206)执行结果：7、对某字段进行汇总统计XSB的每个专业的男生、女生人数、总人数及学生总人数。SQL语句：SELECT 专业, 性别, COUNT(*) AS 人数FROM XSBGROUP BY 专业,性别执行结果：8、利用GROUP BY子句对查询结果生成一个结果集，分别根据专业和性别对人数进行分组。SQL语句：SELECT 专业, COUNT(*) AS 学生数FROM XSBGROUP BY 专业执行结果：9、对查询结果按某字段排序将计算机专业学生的“计算机基础”课程成绩按降序排列。SQL语句：SELECT 姓名, 课程名, 成绩FROM XSB, KCB, CJBWH
16、ERE XSB.学号= CJB.学号AND CJB.课程号= KCB.课程号AND 课程名= 计算机基础AND 专业= 计算机ORDER BY 成绩DESC执行结果：10、创建、修改、删除游标定义游标XS_CUR，然后打开该游标，输出行数，最后删除游标。SQL语句：DECLARE XS_CUR CURSORLOCAL SCROLL SCROLL_LOCKSFORSELECT 学号, 姓名, 总学分FROM XSBFOR UPDATE OF 总学分OPEN XS_CURSELECT 游标XS_CUR数据行数 = CURSOR_ROWSDEALLOCATE XS_CUR11、从游标中读取数据从游标
17、XS_CUR2中提取数据。设该游标已经声明。OPEN XS_CUR2FETCH FIRST FROM XS_CUR2执行结果：12、创建视图CREATE VIEW CS1_XSAS SELECT *FROM XSBWHERE 专业= 计算机第1步：启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库PXSCJ”，选择其中的“视图”项，右击鼠标，在弹出的快捷菜单上选择“新建视图(N)”菜单项。 第2步：在随后出现的“添加表”窗口中，添加所需要关联的基本表、视图、函数、同义词。第3步：基表添加完后，在“视图”窗口的“关系图”窗口中显示了基表的全部列信息。
18、第4步：上一步完成后，单击面板上的“保存”按钮，出现“保存视图”对话框在其中输入视图名“CS_XS”，并单击“确定”按钮，便完成了视图的创建。13、查询视图启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库PXSCJ视图”，选择“dbo.CS_XS”，右击鼠标，在弹出的快捷菜单中选择“修改”菜单项，可以查看并修改视图结构，选择“打开视图”菜单项，将可查看视图数据内容。14、用循环语句实现查找某字段中最小值15、利用内置函数得到某字段的最大值SELECT MAX(成绩) AS 课程的最高分FROM CJB WHERE 课程号= 10116、建立一个用
19、户自定义函数实现求某字段平均值创建用户定义函数，实现计算全体学生某门功课平均成绩的功能。CREATE FUNCTION average(cnum char(20) RETURNS intASBEGINDECLARE aver intSELECT aver=( SELECT avg(成绩) FROM CJBWHERE 课程号=cnumGROUP BY 课程号)RETURN averEND17、删除用户自定义函数删除刚刚创建的用户定义函树average:DROP FUNCTION dbo.average 五、遇到的问题及解决办法六、实训评价（教师填写）第3 单元 数据完整性、存储过程和触发器一、实
20、训目的1、掌握数据完整性、索引的含义及其用法2、掌握存储过程、触发器定义、修改、删除，存储过程的调用二、实训任务1、在某张表上建立索引2、删除创建的索引3、创建默认值约束4、帮定默认值约束5、删除默认值约束6、创建check约束7、删除check约束8、创建规则9、帮定规则、解除帮定规则10、删除规则11、创建、删除PRIMARY KEY约束、UNIQUE约束12、实现参照完整性13、分别创建带参数的、带output参数的存储过程、修改存储过程14、执行存储过程15、删除存储过程16、创建、修改、删除触发器三、实训要求1、各种操作要求用SQL命令和企业管理器两种方式完成2、将涉及到的SQL命令
21、拷贝到“实训内容及步骤”中3、将实训过程、结果通过截屏的方式添加到“实训内容及步骤”中4、在需要的时候对实训过程进行一定的文字说明四、实训内容及步骤1、在某张表上建立索引1SQL方式创建索引为KCB表的“课程名”列创建索引CREATE INDEX kc_name_ind ON KCB(课程名)2企业管理器方式创建索引：第一步 启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库”，选择“表”中的“dbo.XSB”，右击其中的“索引”项，在弹出的快捷菜单上选择“新建索引”菜单项。第二步 在弹出的“新建索引”窗口中输入索引名称PX_XSB，选择索引类型为
22、“聚集”，勾选“唯一”复选框，单击新建索引窗口的“添加”按钮，在弹出的“选择要添加到索引键的表列”窗口中选择要添加的列，添加完毕后，单击“确定”按钮，完成索引的创建工作。2、删除创建的索引1SQL方式创建索引DROP INDEX index_name ON table_or_view_name ,.n | table_or_view_name.index_name ,.n 2企业管理器方式创建索引：启动SQL Server Management Studio，在对象资源管理器中展开数据库“PXSCJ表dbo.XSB索引”，选择其中要删除的索引，单击鼠标右键，在弹出的快捷菜单上选择“删除”菜单项
23、。在打开的“删除对象”窗口中单击“确定”按钮即可。3、创建默认值约束在定义表时定义一个字段的默认值约束。USE KCSJGOCREATE TABLE XSB2(学号 char(6) NOT NULL,姓名 char(8) NOT NULL,性别 bit NOT NULL DEFAULT 1,出生时间 datetime NOT NULL,专业 char(12) NULL,总学分 int NULL,备注 varchar(500) NULL,入学日期 datetime DEFAULT getdate() /*定义默认值约束*/)GO4、绑定默认值约束在KCSJ数据库中定义表book及名为today的
24、默认值，然后将其绑定到book表的hire_data列。USE KCSJGOCREATE TABLE book( book_id char(6), name varchar(20) NOT NULL, hire_date datetime NOT NULL,)GOCREATE DEFAULT today1 AS getdate()GOEXEC sp_bindefault today, book.hire_date5、删除默认值约束解除默认值对象today与表book的hire_date列的绑定关系，然后删除该对象。EXEC sp_unbindefault book.hire_dateGODRO
25、P DEFAULT today6、创建check约束创建一个表student1，只考虑“学号”和“性别”两列，性别只能包含男或女。USE KCSJGOCREATE TABLE student1 (学号char(6) NOT NULL, 性别char(1) NOT NULL CHECK(性别IN (男, 女) )2.在PXSCJ数据库的CJB表中，学生每门功课的成绩一般在0100的范围内。如果对用户的输入数据要施加这一限制，可按如下步骤进行。第1步：启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库KCSJ表”，选择“约束”，右击鼠标，在出现的快捷菜
26、单中选择“新建约束”菜单项。第2步：在打开的“表设计器”窗口中选择“成绩”属性列，右击鼠标，在弹出的快捷菜单中选择“CHECK约束”菜单项。第3步：在打开的 “CHECK约束”窗口中，单击“添加”按钮。在“常规”属性区域中的“表达式”栏后面输入相应的CHECK约束表达式为“成绩=0 AND 成绩=100”。 在“CHECK约束”窗口中选择“关闭”按钮，并保存修改，完成“CHECK约束”的创建。 7、删除check约束1.删除CJB表“成绩”字段的CHECK约束。ALTER TABLE CJB DROP CONSTRAINT cj_constraint 2.在对象资源管理器中展开“数据库KCSJ
27、表”，选择“约束”，右击鼠标，在出现的快捷菜单中选择“删除”菜单项。8、创建规则9、绑定规则、解除绑定规则10、删除规则11、创建、删除PRIMARY KEY约束、UNIQUE约束创建XSB1表（假设XSB1表未创建），并对“学号”字段创建PRIMARY KEY约束，对“姓名”字段定义UNIQUE约束。USE PXSCJGOCREATE TABLE XSB1(学号char(6) NOT NULLCONSTRAINT XH_PK PRIMARY KEY,姓名char(8) NOT NULLCONSTRAINT XM_UK UNIQUE,性别bit NOT NULLDEFAULT 1,出生时间da
28、te NOT NULL,专业char(12) NULL,总学分int NULL,备注varchar(500) NULL)删除例.14中创建的PRIMARY KEY约束和UNIQUE约束。ALTER TABLE XSB1 DROPCONSTRAINT XH_PK, XM_UKGO 2.利用企业管理器方式创建和删除PRIMARY KEY约束（1）创建PRIMARY KEY约束。当创建主键时，系统将自动创建一个名称以“PK_”为前缀、后跟表名的主键索引，系统自动按聚集索引方式组织主键索引。（2）删除PRIMARY KEY约束。在对象资源管理器中选择“dbo.XSB”表图标，右击鼠标，在弹出的快捷菜单
29、中选择“修改”菜单项，进入“表设计器”窗口。选中“XSB表设计器”窗口中主键所对应的行，右击鼠标，在弹出的快捷菜单中选择“移除主键”菜单项即可。3利用企业管理器方式创建和删除UNIQUE约束（1）创建UNIQUE约束。进入XSB表的“表设计器”窗口，选择“学号”属性列并右击鼠标，在弹出的快捷菜单中选择“索引/键”菜单项，打开“索引/键”窗口。在窗口中单击“添加”按钮，然后保存修改即可。2）删除UNIQUE约束。 “索引/键”窗口，选择要删除的UNIQUE约束，单击左下方的“删除”按钮，单击“关闭”按钮，保存表的修改即可。12、实现参照完整性1.SQL创建stu表，要求stu表中所有的学生学号都
30、必须出现在XSB表中，假设已经使用“学号”列作为主键创建了XSB表。USE PXSCJGOCREATE TABLE stu(学号char(6) NOT NULL FOREIGN KEY (学号) REFERENCES XSB (学号),姓名char(8) NOT NULL,出生时间datetime NULL)第1步：在对象资源管理器中展开“数据库PXSCJ”，选择“数据库关系图”，右击鼠标，在出现的快捷菜单中选择“新建数据库关系图”菜单项，打开“添加表”窗口。第3步：在出现的“添加表”窗口中选择要添加的表，本例中选择了表XSB和表CJB。单击“添加”按钮完成表的添加，之后单击“关闭”按钮退出窗
31、口。将XSB表中的“学号”字段拖动到从表CJB中的“学号”字段。第5步：在弹出的“表和列”窗口中输入关系名、设置主键表和列名，单击“表和列”窗口中的“确定”按钮，再单击“外键关系”窗口中的“确认”按钮。13、分别创建带参数的、带output参数的存储过程、修改存储过程从PXSCJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接收与传递参数精确匹配的值。USE PXSCJGOCREATE PROCEDURE student_info1 name char (8), cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分FROM XSB a IN
32、NER JOIN CJB bON a.学号= b.学号INNER JOIN KCB tON b.课程号= t.课程号WHERE a.姓名=name and t.课程名=cnameGO创建一个存储过程do_insert，作用是向XSB表中插入一行数据。创建另外一个存储过程do_action，在其中调用第一个存储过程，并根据条件处理该行数据，处理后输出相应的信息。第一个存储过程：CREATE PROCEDURE dbo.do_insert ASINSERT INTO XSB VALUES(091201, 陶伟, 1, 1990-03-05, 软件工程,50, NULL);第二个存储过程：CREAT
33、E PROCEDURE do_action X bit, STR CHAR(8) OUTPUTASBEGIN EXEC do_insertIF X=0BEGINUPDATE XSB SET 姓名=刘英, 性别=0 WHERE 学号=091201SET STR=修改成功ENDELSEIF X=1BEGINDELETE FROM XSB WHERE 学号=091201SET STR=删除成功ENDEND对例7.2中创建的存储过程student_info1进行修改，将第一个参数改成学生的学号。USE KCSJGOALTER PROCEDURE student_info1number char(6),
34、cname char(16) AS SELECT 学号, 课程名, 成绩FROM CJB, KCBWHERE CJB.学号=number AND KCB.课程名=cnameGO（1）创建存储过程。例如，如果要通过图形向导方式定义一个存储过程来查询PXSCJ数据库中每个同学各门功课的成绩，那么其主要步骤如下：启动SQL Server Management Studio，在对象资源管理器中展开“数据库PXSCJ”，选择其中的“可编程性”，右击“存储过程”，在弹出的快捷菜单中选择“新建存储过程”菜单项，打开“存储过程脚本编辑”窗口，如图7.1所示。在该窗口中输入要创建的存储过程的代码，输入完成后单击
35、“执行”按钮，若执行成功则创建完成。14、执行存储过程删除PXSCJ数据库中的student_info存储过程。USE PXSCJGOIF EXISTS(SELECT name FROM sysobjects WHERE name=student_info)DROP PROCEDURE student_info（2）执行存储过程。在PXSCJ数据库的“存储过程”目录下选择要执行的存储过程，例如student_info1，右击鼠标，选择“执行存储过程”菜单项。在弹出的“执行过程”窗口中会列出存储过程的参数形式，如果“输出参数”栏为“否”，则表示该参数为输入参数，用户需要设置输入参数的值，在“值”一栏中输入即可，如图7.2所示。单击“确定”按钮，SSMS的结果显示窗口将列出存储过程运行的结果。15、删除存储过程删除PXSCJ数据库中的student_info存储过程。USE PXSCJGOIF EXISTS(SELECT name FROM sysobjects WHERE name=student_info)DROP PROCEDURE student_info删除存储过程。选择要删除的存储过程，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项，根据提示删除该存储过程。16、创建、修改、删除触发器
