数据库实验报告(sql)

向百度文库上传资料，为什么总是提示“文档附件上传成功，但与已有... 2010-08-26 百度文库为什么上传文档一直失败，说我的文档附件与已有附件重复。 87 2011-03-06 百度文库上传文档为什么会出现 文档附件与已有文档重复啊 怎么处理... 9 2010-08-05 为什么百度文库上传文档，老说重复 8 2010-06-12 百度文库中您的文档附件与已有文档重复 3 更多关于文档附件上传成功,但与已有文档相似度较高的问题>> 百度文库的相关知识 文档 格式 播放器 小说 2013-01-03 百度文库的文档下载不了！！！！！！！！ 7 2011-04-19 百度文库的文档地址是什么 20 2011-03-27 百度文库文档删除是怎么回事？ 10 2011-03-26 百度文库文档不见了，财富值也没了 8 2011-04-06 百度文库 看不见文档内容~！ 24 更多关于百度文库:文档的问题>> 按默认排序|按时间排序 其他1条回答 检举|2013-04-24 09:35蓝鲨SEO|二级 那说明你是盗版了啊，一定要原创哦 评论|00 等待您来回答 1回答图上的那个itunes store打开总是空白，5s.求解答 2回答这种植物叫什么名字 2回答为什么qq农场安装到手机以后不能使用，我的手机系统是安卓系统 1回答求一个剧本，什么类型都行，动漫游戏的皆可，要健康向上，让古板的... 1回答这个女生叫什么名字？ 0回答 10 求《苹果》无删减版，有中文字幕的， 1回答 10 求一个flash动画？？？（要有动画片名字) 1回答我是在itunes上备份的，怎么弄，给我个步骤 谢谢了 没有感兴趣的问题？试试更多等待您来回答的问题>> 《数据库实验》

1 熟悉SQL Server环境及其基本工具的使用

实验学时：0.5学时 实验类型：验证 实验要求：必做 一、实验目的

熟悉SQL Server 2008的界面环境。掌握SQL Server 2008管理工具的使用，包括企业管理器和查询分析器。了解在查询分析器中执行SQL语句的方法。了解数据库及数据库对象。熟悉服务器的管理与使用。 二、实验内容

1、打开企业管理器----SQL Server Management Studio。

SQL Server Management Studio 是SQL Server 2008的主要管理工具和开发工具，习惯上依然把它称为企业管理器，它将早期版本的SQL Server 中的企业管理器、查询分析器及Analysis Manager等功能整合到同一个环境中，并能和所有组件协同工作。

打开SQL Server Management Studio后弹出连接服务器对话框，在“服务器名称”栏的下拉里选择“浏览更多”，展开数据库引擎，选择据计算机名，确认，连接。

2、在企业管理器左边的树型结构中查看数据库Northwind，观察该数据库中的所有数据库对象，如表、视图、存储过程、默认、规则等。

注： Northwind and pubs 示例数据库下载地址 ：

http://www.microsoft.com/en-us/download/confirmation.aspx?id=23654

安装时最好启动SQL Server Management Studio，装完后，右击“数据库”，选“附加”：

点击“添加”，选择之前示例数据库安装的路径(C:\\SQL Server 2000 Sample Databases)，找到NORTHWND.MDF和PUBS.MDF两个文件，分别确定：

附加好后，“数据库”列表下即有Northwind和pubs。

3、单击Northwind下的“表”节点，在企业管理器右边将列出该数据库中的所有表（包括系统表和用户表），在此以用户表Employees表为例，打开该表，查看表中数据情况。

4、关闭企业管理器。 5、打开查询编辑器。

6、在查询编辑器中输入以下T-SQL语句，看看执行结果是什么？

USE Northwind

SELECT * FROM Employees

步骤：点击“新建查询”，输入以上代码，点击执行，显示结果如下图所示：

7、关闭查询编辑器。

8、查看服务管理器的工作情况，以及当前的服务器名。 在菜单栏，点【视图】选择【已注册的服务器】

9、在企业管理器中，新建一个名为ServerGroup的服务器组（顶层组），并在该服务器组下面注册相邻同学所用的服务器。

注意：需关闭相邻同学计算机的windows防火墙

步骤：在工具栏选择【视图】—【已注册的服务器】，然后展开数据库引擎，右键点击“LocalServerGroups”，选择【新建服务器组】，组名即“ServerGroup”。

在新建的“ServerGroup”点右键，选择【新建服务器注册】

在“服务器名称”里，输入相邻同学计算机的IP，选择SQL Server身份验证，一般默认登录名“sa”，密码“123”，例如连接到“115.156.229.177”点击测试：

或者在“服务器名称”的下拉栏里选择“浏览更多”，在“网络浏览器”的“数据库引擎”中，找到相邻同学的服务器名称。

点击保存。

选择保存后可看到“ServerGroup”服务器组下面多出了该相邻同学的服务器

此时双击它，输入用户名和密码即可

10、删除第9题中所作的服务器注册，删除服务器组ServerGroup。 三、课后练习题

1、分别通过企业管理器和查询编辑器查看pubs数据库中publishers、authors两个表的所有数据。

2、删除默认的服务器注册，并重新注册。

2 数据库的创建和管理

实验学时：1.5学时 实验类型：验证 实验要求：必做 一、实验目的

熟练掌握使用企业管理器和Transact-SQL语言两种方法创建、修改和删除数据库。掌握管理数据库的有关系统存储过程。 二、实验内容

1、利用企业管理器创建满足以下要求的数据库： 1）数据库名为mydb；

2）有三个数据文件，分别是mydb1.mdf、mydb2.ndf和mydb3.ndf（文件的逻辑名与文件主名相同），以上文件的初始容量为50MB，文件自动增长的增量为5MB，文件的最大容量为150MB；

3）有两个日志文件，分别是mydblog1.ldf和mydblog2.ldf（文件的逻辑名与文件主名相同），以上文件的初始容量为25MB，文件自动增长的增量为原来容量的10％，文件的最大容量为100MB；

4）所有文件都存放在D:\\MyDB中（该文件夹应该先行创建）。

具体步骤如下：右键点击“数据库”，选择“新建数据库”

输入数据库名称：

建好后：

2、利用Transact-SQL语言创建满足以下要求的数据库： 1）数据库名为testdb；

2）有四个数据文件，分别是testdb1.mdf、testdb2.ndf、testdb3.ndf和testdb4.ndf（文件的逻辑名与文件主名相同），以上文件的初始容量为10MB，文件自动增长的增量为1MB，文件的最大容量为30MB；

3）其中testdb1.mdf和testdb2.ndf属于主文件组PRIMARY，testdb3.ndf和testdb4.ndf属于用户自定义文件组testdbfg1；

4）有两个日志文件，分别是testdblog1.ldf和testdblog2.ldf（文件的逻辑名与文件主名相同），以上文件的初始容量为5MB，文件自动增长的增量为原来容量的10％，文件的最大容量为20MB；

5）所有文件都存放在D:\\mydata中。（该文件夹要事先建好！）

具体步骤如下：

点击“新建查询”，输入以下代码，点击执行，显示“命令已成功完成”后，在“对象资源管理器”中选中“数据库”，点击“刷新”按钮，列表即可见到“testdb”。

CREATE DATABASE testdb ON PRIMARY

( NAME = testdb1, FILENAME = 'D:\\mydata\\testdb1.mdf' , SIZE = 10MB , MAXSIZE = 30MB, FILEGROWTH = 1MB ),

( NAME = testdb2, FILENAME = 'D:\\mydata\\testdb2.ndf' , SIZE = 10MB , MAXSIZE = 30MB, FILEGROWTH = 1MB ),

FILEGROUP testdbfg1

( NAME = testdb3, FILENAME = 'D:\\mydata\\testdb3.ndf' , SIZE = 10MB , MAXSIZE = 30MB, FILEGROWTH = 1MB),

(NAME = testdb4, FILENAME = 'D:\\mydata\\testdb4.ndf' , SIZE = 10MB , MAXSIZE = 30MB, FILEGROWTH = 1MB) LOG ON

( NAME = testdblog1, FILENAME = 'D:\\mydata\\testdblog1.ldf' , SIZE = 5MB , MAXSIZE = 20MB , FILEGROWTH = 10% ),

( NAME = testdblog2, FILENAME = 'D:\\mydata\\testdblog2.ldf' , SIZE = 5MB , MAXSIZE = 20MB , FILEGROWTH = 10% ) GO

3、利用系统存储过程sp_helpdb查看数据库mydb的信息。 步骤：点击“新建查询”，输入

USE mydb GO

sp_helpdb mydb

后执行，结果如下图所示：

4、利用企业管理器修改数据库mydb，删除其中的数据文件mydb3.ndf。

5、利用Transact-SQL语言修改数据库mydb，增加一个文件组mydbfg1，其中包含数据文件mydb4.ndf和mydb5.ndf（文件的逻辑名与文件主名相同），以上文件的初始容量为50MB，文件自动增长的增量为5MB，文件的最大容量为150MB。

步骤：点击“新建查询”，输入以下代码，点击执行

ALTER DATABASE mydb ADD FILEGROUP mydbfg1 ALTER DATABASE mydb ADD FILE

(NAME = mydb4, FILENAME = 'D:\\MyDB\\mydb4.ndf' , SIZE = 50MB , MAXSIZE = 150MB, FILEGROWTH = 5MB),

(NAME = mydb5, FILENAME = 'D:\\MyDB\\mydb5.ndf' , SIZE = 50MB , MAXSIZE = 150MB, FILEGROWTH = 5MB) TO FILEGROUP mydbfg1

6、按照以下命令语句将数据库mydb的名字改为mytestdb：

USE mydb GO

sp_renamedb 'mydb','mytestdb'

7、利用企业管理器删除数据库mytestdb。

直接右键点击mytestdb选择删除即可，注意要关闭当前的SQL语句编写界面。 8、利用Transact-SQL语言删除数据库testdb。

步骤：点击“新建查询”，输入DROP DATABASE testdb 后执行。 三、课后练习题

1、分别通过企业管理器和查询分析器创建一个名为Students的数据库，要求该数据库至少包含一个数据文件和一个日志文件，其余参数自定。

2、思考以下问题：

1）对于一个已经建立的数据库，可否修改或删除其中的主数据文件？

2）对于一个已经建立的数据库，其中数据文件的初始容量是不是可以任意修改（包括增加容量和缩小容量）？

3）删除一个数据库之后，该数据库有关的操作系统文件是否还存在？

3 表的创建和管理

实验学时：1.5学时 实验类型：验证 实验要求：必做 一、实验目的

熟练掌握使用企业管理器和Transact-SQL语言两种方法创建、修改和删除表。掌握管理表的有关系统存储过程。 二、实验内容

1、创建数据库xsgl，其中有一个数据文件名为xsgldata.mdf和一个日志文件名为xsgllog.ldf（文件的逻辑名与文件主名相同）；数据文件的初始容量为50MB，文件自动增长的增量为5MB，文件的最大容量为150MB；日志文件的初始容量为25MB，文件自动增长的增量为原来容量的10％，文件的最大容量为100MB；所有文件都存放在D:\\mydata中。

2、在数据库xsgl中，利用企业管理器创建以下表格： 1）表格名为xs（学生基本情况表）； 2）表格中各个属性的定义如下： 列名 含义 数据类型 长度 能否取空值 xh int no 学号 xm char 8 yes 姓名 xb char 2 yes 性别 nl tinyint yes 年龄 zy char 16 yes 专业 jtzz char 50 yes 家庭住址 备注 主码 具体步骤如下：将“对象资源管理器”中的“xggl”数据库展开，找到其下的“表”，右键点击，选择“新建表”

按照上面表格的内容填写“列名”、“数据类型”，并取消或选择“允许NULL值”前的

选项，并右键点击“xh”，选择将其设为主键，如下图：

填好信息后，单击“保存”按钮，出现如下对话框，输入名字为xs即可：

3、按照以下步骤向表格xs中添加如下记录： xh xm xb nl zy jtzz （学号） （姓名） （性别） （年龄） （专业） （家庭住址） 200809412 庄小燕 24 女 计算机 上海市中山北路12号 200809415 25 洪波 男 计算机 青岛市解放路105号 200109102 23 肖辉 男 计算机 杭州市凤起路111号 200109103 柳嫣红 22 女 计算机 上海市邯郸路1066号 200307121 张正正 20 男 应用数学 上海市延安路123号 200307122 21 李丽 女 应用数学 杭州市解放路56号 1）打开企业管理器； 2）找到数据库xsgl中的表格xs；

3）在表格xs上右击鼠标，执行快捷菜单命令“编辑前200行”， 4）在接着出现的记录录入界面上添加记录。

添加完后如下图所示：

4、向表xs中增加“入学时间”属性列，其列名为rxsj，数据类型为datetime型。 5、将表xs中nl（年龄）列的数据类型改为int型。

注意：sql server 2008默认在列创建好后，禁止修改其属性。因此需要在菜单栏的

【工具】-【选项】，弹出选项窗口中点击Designers，将“阻止保存要求从新创建表的更改”前的勾去掉。 6、在数据库xsgl中，利用Transact-SQL语言创建以下表格： 1）表格名为kc（课程情况表）； 2）表格中各个属性的定义如下： 列名 含义 数据类型 长度 能否取空值 kch char 4 no 课程号 kcm char 20 yes 课程名 xss int yes 学时数 xf int yes 学分 步骤：点击“新建查询”，输入以下代码，点击执行

USE xsgl GO

CREATE TABLE kc (

kch char(4) NOT NULL primary key, kcm char(20) NULL, xss int NULL, xf int NULL

)

备注 主码 命令成功完成后，“对象资源管理器”中的“xggl”数据库展开后，其下的“表”中便多出了dbo.kc ，如下图所示：

7、利用企业管理器和Transact-SQL语言两种方式删除表kc。 步骤：点击“新建查询”，输入以下代码，点击执行

DROP TABLE kc

8、利用系统存储过程sp_help和sp_spaceused查看表xs的基本信息和所用存储空间的信息。

如果新建查询的可用数据库就是xsgl ，则直接sp_help xs 和 sp_spaceused xs

如下图：三角选择；

否则分别用一下代码：use xsgl

GO

sp_help xs

及：

use xsgl

GO

sp_spaceused xs

，如果不是，可以点击右边下

9、在数据库Northwind中利用系统存储过程sp_depends查看表Employees的相关性关系。

步骤：点击“新建查询”，输入以下代码，点击执行

use Northwind GO

sp_depends 'Employees'

10、利用系统存储过程sp_rename将表xs重命名为Students。 步骤：点击“新建查询”，输入以下代码，点击执行

USE xsgl GO

sp_rename 'xs','Students'

三、课后练习题

1、创建一个名为cpxs的数据库，在该数据库中分别通过企业管理器和查询分析器创建一个名为cp的表（产品表），表格中各个属性的定义如下： 列名 含义 数据类型 长度 能否取空值 备注 cpbh 产品编号 char 6 no 主码 cpmc 产品名称 char 30 no jg money yes 价格 kcl int yes 库存量 2、思考以下问题：

1）表格的相关性是指什么？

2）表格的相关性对删除表格的操作有没有影响？ 3）如果要在一个已经存在的表格上删除一列并增加一列，能不能用一个ALTER TABLE语句来完成？

三、课后练习题

1、利用三种方法为表xs按xs_department（专业）降序建立名为zy_index的非簇集索引。

2、思考题：为什么建立合理的索引可以提高表数据的访问速度？ 3、思考题：一个表中索引是不是越多越好？

4 表数据的简单查询

实验学时：1学时 实验类型：设计 实验要求：必做 一、实验目的

掌握SELECT语句的基本用法。掌握计算列的用法。掌握WHERE子句中各类查询条件的写法。掌握ORDER BY子句的用法。 二、实验内容

1、查询pubs数据库的authors表中的作者的姓（au_lname）、名（au_fname）和电话号码（phone）。

步骤：点击“新建查询”，输入以下代码，点击执行

USE pubs GO

SELECT au_lname,au_fname,phone FROM authors

运行结果：

2、使用TOP关键字，从Northwind数据库的customers表中搜索返回前20%的数据。 步骤：点击“新建查询”，输入以下代码，点击执行

USE Northwind GO

SELECT TOP 20 PERCENT * FROM customers

结果如下图：

3、使用WHERE 子句从Northwind 数据库的Products表中检索出所有单位价格（UnitPrice）超过50美元的货物名称（ProductName）、货物代号（ProductID）以及每单位重量（QuantityPerUnit）。

步骤：点击“新建查询”，输入以下代码，点击执行

USE Northwind GO

SELECT ProductName,ProductID,QuantityPerUnit FROM Products WHERE UnitPrice>50 结果如下图：

4、查询在Northwind数据库的Employees表中以字母A作Firstname第一个字母的雇员的Firstname和Lastname。

步骤：点击“新建查询”，输入以下代码，点击执行

USE Northwind GO

SELECT Firstname,Lastname

FROM Employees

WHERE Firstname like 'A%'

运行结果如下图：

5、查询Northwind数据库Employees表中所有雇员的Firstname和Lastname，并按生日BirthDate从小到大进行排列。

步骤：点击“新建查询”，输入以下代码，点击执行

USE Northwind GO

SELECT Firstname,Lastname FROM Employees ORDER BY BirthDate

运行结果如下图：

6、复制xscjglxt.rar，解压缩到适当的文件夹，然后将其中的数据文件和日志文件附加

到数据库服务器中，其数据库名为xscjglxt。 第7～12题在数据库xscjglxt（学生成绩管理系统数据库）中完成。 7、查询2007年入学的学生的学号和姓名。

USE xscjglxt GO

SELECT xs_no,xs_name FROM xs

WHERE xs_rxsj=2007

8、查询选修了课程的学生学号。

USE xscjglxt GO

SELECT xs_no FROM fs

WHERE kc_selected_status='y'

9、查询选修了课程号为“0626050”的课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。

USE xscjglxt GO

SELECT xs_no,fs FROM fs

WHERE kc_no=0626050 ORDER BY fs DESC

10、查询选修了课程号为“0626050”的课程且成绩在80~90之间的学生学号和成绩，并将成绩乘以0.8之后输出。

USE xscjglxt GO

SELECT xs_no,fs*0.8 FROM fs

WHERE kc_no=0626050 AND fs BETWEEN 80 AND 90

11、查询海洋食品工程专业或计算机科学与技术专业的姓张的学生的信息。

USE xscjglxt GO

SELECT xs_no,xs_name,xs_rxsj,xs_csrq,xs_department FROM xs

WHERE (xs_department='海洋食品工程' OR xs_department='计算机科学与技术') AND xs_name LIKE '张%'

12、查询缺少了成绩的学生的学号和课程号。

USE xscjglxt GO

SELECT xs_no,kc_no FROM fs

WHERE fs is null

三、课后练习题

以下题目在数据库xscjglxt中完成。

1、查询2007年入学的所有学生的姓名，要求查询结果按学号的升序排列。 2、查询学时数为48或者64的课程的课程名。 3、查询所有课程名称中包含“数据”的课程的信息。 4、查询所有选课记录的课程编号（要求不重复显示）。

5、查询学号为“070604222”的学生所选课程的课程编号和所得的成绩。

5 表数据的维护

实验学时：1学时 实验类型：验证 实验要求：必做 一、实验目的

熟练掌握使用Transact-SQL语言和通过企业管理器输入表数据、修改表数据和删除表数据的操作。 二、实验内容

1、复制xscjglxt.rar，解压缩到适当的文件夹，然后将其中的数据文件和日志文件附加到数据库服务器中，其数据库名为xscjglxt。

2、在数据库xscjglxt中建立一个名为History_Student的表，其结构与表xs完全一样。

3、在表History_Student中插入一条记录，其学号为070604101，姓名为黄艳春，性别为女，出生日期为1987-02-14，入学年份为2007，所学专业为计算机科学与技术。

USE xscjglxt GO

INSERT INTO History_Student

VALUES ('070604101','2007','黄艳春','1987-02-14','女','计算机科学与技

术')

4、试将表xs中的所有记录插入到表History_Student中去，其SQL命令为：

USE xscjglxt GO

INSERT INTO History_Student SELECT * FROM xs

5、将表xs中学号为“070604222”的学生的所学专业改为应用数学。 USE xscjglxt

GO

UPDATE xs

SET xs_department='应用数学' WHERE xs_no='070604222'

6、将表xs中所有学生的入学年份增加1年。

USE xscjglxt GO

UPDATE xs

SET xs_rxsj=xs_rxsj+1

7、删除表fs中学号为“070604222”的学生选修的课号为“0690252”的记录。

USE xscjglxt GO

DELETE FROM fs

WHERE xs_no='070604222' AND kc_no='0690252'

8、删除所有学生的选课记录。 三、课后练习题

以下题目在数据库xscjglxt中完成。

1、通过企业管理器，在xs、kc、和fs四个表中各输入10条记录。

2、将一条新的学生记录（学号：070604102；姓名：陈冬；性别：男；出生日期：1986-4-24；入学年份：2007；所学专业：计算机科学与技术）插入表xs中。

3、插入一条选课记录（“070604102”，“0620010”）到表fs。 4、删除课程号为“1075201”的课程记录。 5、将所有学生的选课记录的成绩置空。

6 视图管理

实验学时：1学时 实验类型：设计 实验要求：必做 一、实验目的

掌握视图的定义与维护操作，加深对视图在关系数据库中作用的理解。 二、实验内容

在数据库xscjglxt中完成以下操作：

1、 建立计算机科学与技术专业学生的视图C_Student，并要求进行修改和插入操作时

仍需保证该视图只有计算机科学与技术专业的学生，视图的属性名为xs_no，xs_name，xs_sex，xs_csrq，xs_department. USE xscjglxt

GO

CREATE VIEW C_Student AS

SELECT xs_no,xs_name,xs_sex,xs_csrq,xs_department FROM xs

WHERE xs_department='计算机科学与技术'

对于视图的使用，将其当做基本表即可，可用下面语句用来显示C_Student表中的所有计算机科学与技术专业的学生：

SELECT * FROM C_Student

或者直接在左侧目录【xscjglxt】下的【视图】下多出的“dbo.C_student”上点击右

键，在弹出选项里选择“编辑前200行”。 2、建立学生的学号(xs_no)、姓名(xs_name)、选修课程名(kc_name)及成绩(fs)的视图xscjd。本视图由三个基本表的连接操作导出，其SQL语句如下：

USE xscjglxt GO

CREATE VIEW xscjd AS

SELECT xs.xs_no, xs.xs_name, kc.kc_name, fs.fs FROM xs,kc,fs

WHERE xs.xs_no= fs.xs_no AND kc.kc_no=fs.kc_no

3、定义一个反映学生出生年份的视图Student_birth。

USE xscjglxt GO

CREATE VIEW Student_birth(xs_no, xs_name, xs_csnf) AS

SELECT xs_no, xs_name, YEAR(xs_csrq) FROM xs GO SELECT *

FROM Student_birth

4、删除视图Student_birth。

USE xscjglxt GO

DROP VIEW Student_birth

5、在计算机科学与技术专业的学生视图C_Student中找出年龄小于26岁的学生姓名(xsxm)和出生日期(xscsrq)。

USE xscjglxt GO

SELECT xs_name,xs_csrq FROM C_Student

WHERE DATEDIFF(YEAR,xs_csrq,GETDATE())<26

6、在xscjd视图中查询成绩在85分以上的学生学号(xh)、姓名(xsxm)和课程名称(kcm)。

USE xscjglxt GO

SELECT xs_no,xs_name,kc_name FROM xscjd WHERE fs>85

7、将计算机科学与技术专业的学生视图C_Student中学号为“050604132”的学生姓名改为“黄海”。

USE xscjglxt GO

UPDATE C_Student SET xs_name='黄海' WHERE xs_name='黄河'

GO SELECT * FROM C_Student

8、向计算机科学与技术专业的学生视图C_Student中插入一个新的学生记录，其中学号为“050604114”，姓名为“王海”，性别为“男”。

USE xscjglxt GO

INSERT INTO C_Student

VALUES('050604114','王海','男',NULL,'计算机科学与技术')

GO SELECT * FROM C_Student

9、删除计算机科学与技术专业的学生视图C_Student中学号为“070604126”的记录。

USE xscjglxt GO

DELETE FROM C_Student WHERE xs_no=070604126

三、课后练习题

1、建立软件工程专业学生的视图。

2、建立软件工程专业学生的视图，并要求进行修改和插入操作时仍须保证该视图只有软件工程专业的学生。

3、建立计算机科学与技术专业选修了“0690051”号课程且成绩在90分以上的学生视图。

7 数据类型的使用

实验学时：0.5学时 实验类型：设计 实验要求：选做 一、实验目的

熟练掌握使用企业管理器和Transact-SQL语言两种方法创建、修改和删除表。掌握管理表的有关系统存储过程。 二、实验内容

1、如果要设计一个表用来存放商品的基本信息，包括商品编号、商品名称、品牌商标、型号、产地、生产厂商、生产日期、保质期、进货价格、销售价格和商品图片等。请设计出这个表的表结构，包括表名、列名、列的数据类型和长度等等。

2、在数据库xscjglxt中，利用系统存储过程创建一个名为usertype的用户自定义数据类型，要求该类型是最多可以容纳30个字符的可变长字符串，不允许为空，默认值为“目前未知”。

USE xscjglxt GO

sp_addtype 'usertype','varchar(30)','not null' GO

CREATE DEFAULT UsertypeDefault AS '目前未知' GO

sp_bindefault UsertypeDefault,'usertype'

在fs表中测试：新建一列“laoshi”，类型选择“usertype”，再对该表进行编辑时，如下所示。

删除用户自定义数据类型，接触绑定：

USE xscjglxt GO

sp_unbindefault 'usertype' --先解除usertype与默认值的绑定 GO

DROP DEFAULT UsertypeDefault --然后再删除默认值 GO

sp_droptype 'usertype'

--最后把usertype这个自定义类型删除

三、课后练习题

8 表数据的高级查询

实验学时：2学时 实验类型：设计 实验要求：必做 一、实验目的

本实验的目的是使学生熟练掌握SQL Server查询分析器的使用方法，加深SQL语言查询语句的理解。熟练掌握数据查询中的分组、统计、计算和集合的操作方法。 二、实验内容

在数据库xscjglxt中完成以下操作。

1、 查询所有学生的学号、姓名及其所选修的课程名和成绩。 USE xscjglxt

GO

SELECT fs.xs_no,xs.xs_name,kc.kc_name,fs.fs FROM xs,kc,fs

WHERE xs.xs_no=fs.xs_no AND kc.kc_no=fs.kc_no

2、 查询选修了“数据库”且成绩在90以上的学生学号、姓名和成绩。

USE xscjglxt GO

SELECT fs.xs_no,xs.xs_name,fs.fs FROM xs,kc,fs

WHERE xs.xs_no=fs.xs_no AND kc.kc_no=fs.kc_no AND kc.kc_name='数据库' AND fs.fs>90

3、 统计xs表中计算机科学与技术专业的学生人数。 USE xscjglxt

GO

SELECT COUNT(xs_no) FROM xs

WHERE xs_department='计算机科学与技术'

4、 统计fs表中“0626050”号课程的平均分。 USE xscjglxt

GO

SELECT AVG(fs) FROM fs

WHERE fs.kc_no=0626050

5、 统计选修了课程的学生人数。

USE xscjglxt

GO

SELECT COUNT(xs_no) FROM fs

WHERE fs.kc_selected_status='y'

6、 分组统计fs表中各门课程的平均分和选修人数。 USE xscjglxt

GO

SELECT fs.kc_no,kc.kc_name, CAST(

AVG(CASE

WHEN fs.fs>=0 THEN CONVERT (decimal(10,2),fs.fs) ELSE NULL

END) AS decimal(10,2) ) AS 平均分,

COUNT(CASE

WHEN kc_selected_status='y' THEN 1 ELSE NULL

END) AS 选修人数 FROM fs,kc

WHERE kc.kc_no=fs.kc_no GROUP BY fs.kc_no,kc.kc_name

7、 查询fs表中课程最高分和最低分之差大于12分的课程号。

USE xscjglxt GO

SELECT kc_no FROM fs

GROUP BY kc_no

HAVING (MAX(fs)-MIN(fs))>12

8、 查询fs表中多于3名学生选修的并且课程号以“06”开头的课程的平均分。

USE xscjglxt GO

SELECT kc_no,CAST(AVG(CONVERT (decimal(10,2),fs)) AS

decimal(10,2))AS 平均分

FROM fs

WHERE kc_no LIKE '06%' AND kc_selected_status='y' GROUP BY kc_no

HAVING COUNT(xs_no)>=3

9、 查询选修超过2门课的学生学号。 USE xscjglxt

GO

SELECT xs_no FROM fs

GROUP BY xs_no HAVING COUNT(CASE

WHEN kc_selected_status='y' THEN kc_selected_status

ELSE NULL END)>2

10、查询没有选修“0626050 ”号课程的学生姓名。 USE xscjglxt

GO

SELECT xs_name FROM xs

WHERE NOT EXISTS(SELECT * FROM fs WHERE xs.xs_no=fs.xs_no AND

kc_no=0626050)

11、查询xs表中与学号为“080101320”的学生同年出生的学生的姓名和出生日期。

USE xscjglxt GO

SELECT xs_name,xs_csrq FROM xs

WHERE DATENAME(YEAR,xs_csrq)=(SELECT DATENAME(YEAR,xs_csrq)FROM xs

WHERE xs_no=080101320)

AND xs_no!=080101320

12、 查询课程号为“0626050”的课程成绩高于李炎的学生学号和成绩。

USE xscjglxt GO

SELECT xs_no,fs FROM fs

WHERE kc_no=0626050 AND fs>(SELECT fs.fs

FROM fs,xs

WHERE fs.xs_no=xs.xs_no AND xs.xs_name='李炎' AND fs.kc_no=0626050 )

13、

查询其他专业中比计算机科学与技术专业的学生年龄都小的学生。 USE xscjglxt

GO

SELECT xs_name FROM xs

WHERE xs_csrq>(SELECT MAX(xs_csrq)

FROM xs

WHERE xs_department='计算机科学与技术')

14、 查询至少选修了学号为“070604221”的学生所选修全部课程的学生学号和姓名。

USE xscjglxt

GO

SELECT fs.xs_no,xs.xs_name FROM xs,fs

WHERE xs.xs_no=fs.xs_no AND kc_no IN ( SELECT kc_no

FROM fs

WHERE xs_no=070604221 )

AND xs.xs_no!=070604221 GROUP BY fs.xs_no,xs.xs_name

HAVING COUNT(kc_no)>=(SELECT COUNT(kc_no)

FROM fs

WHERE xs_no=070604221)

三、课后练习题

1、复制SPJ.rar，解压缩到适当的文件夹，然后将其中的数据文件和日志文件附加到数

据库服务器中，其数据库名为SPJ。它包括S、P、J、SPJ 4个关系模式：

S(SNO,SNAME,STATUS,CITY); P(PNO,PNAME,COLOR,WEIGHT); J(JNO,JNAME,CITY); SPJ(SNO,PNO,JNO,QTY)

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成；

零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成； 工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成；

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

今有若干数据如下： S表

P表

J表

SPJ表

第2～8题在数据库SPJ中完成。 2、查询重量最轻的零件的零件代码。 3、查询由供应商S1提供零件的工程项目名。 4、查询同时为工程J1和J2提供零件的供应商代码。 5、查询同时为位于上海的工程提供零件的供应商代码。

6、查询同时为位于上海或北京的工程提供红色零件的供应商代码。 7、查询供应商和工程所在城市相同的供应商能提供的零件代码。

8、查询上海供应商不提供任何零件的工程代码。

USE SPJ GO

SELECT JNO FROM J

WHERE NOT EXISTS(SELECT JNO FROM SPJ WHERE J.JNO=SPJ.JNO AND SPJ.SNO='S5')

9 数据库的完整性设计

实验学时：1学时 实验类型：设计 实验要求：必做 一、实验目的

熟练掌握使用企业管理器和Transact-SQL语言两种方法创建、修改和删除表。掌握管理表的有关系统存储过程。 二、实验内容

在数据库xscjglxt中完成以下操作。 1、在xs表中定义xs_no为主键。

2、在fs表中定义xs_no和kc_no联合构成主键。 3、定义kc表中的kc_name列满足唯一性约束。 4、定义约束使得fs表的fs列的值必须在0~100之间。

5、在xs表中添加一个合理的CHECK约束，使得在xs表中插入学生记录是，保证学生的年龄在16~25之间。

6、定义合理的约束使得在fs表中插入记录时，其xs_no和kc_no的值必须分别在xs表和kc表中出现。（用外键约束实现）

7、在kc表中删除一个记录时，必须同时把fs表中具有同样kc_no值的记录一起删除。 8、创建规则，要求其字符串格式必须以“0”（零）开头，后面是5位数字，并将其与js表中的jsbh列绑定。

USE xscjglxt GO

CREATE TABLE js

( jsbh varchar(20) NOT NULL) GO

CREATE RULE jsbh_rule

AS @jsbh LIKE'0[0-9][0-9][0-9][0-9][0-9]' GO

sp_bindrule jsbh_rule,'js.[jsbh]' --绑定规则到js表的jsbh字段 最后解除绑定：sp_unbindrule 'js.jsbh'

10、 创建默认值为“Unknow”，并将其与xs表中的xs_name列绑定。

USE xscjglxt

GO

CREATE DEFAULT xs_namedefault AS 'Unknow' GO

sp_bindefault xs_namedefault,'xs.xs_name' GO

最后删除默认和解除绑定:

DROP DEFAULT xs_namedefault go

sp_unbindefault 'xs.xs_name'

三、课后练习题

1、复制SPJ.rar，解压缩到适当的文件夹，然后将其中的数据文件和日志文件附加到数

据库服务器中，其数据库名为SPJ。（其中数据表S、P、J和SPJ的关系模式及其含义见实验八）

2、在数据库SPJ中，合理地为库中每一个数据表定义主键和外键。 3、在数据库SPJ中，创建一张关系图管理库中每一个数据表之间的关系。 4、

以下将创建一个学生—课程数据库，包括学生关系Student、课程关系Course和选修关系SC：

例1．建立学生-课程数据库xskc，创建Student表，并将Student表中的Sno属性定义为主键

CREATE DATABASE xskc GO

USE xskc GO

CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20)

)

例2.创建Course表和SC表，并将SC表中的Sno,Cno联合构成主键

USE xskc GO

CREATE TABLE Course (Cno CHAR(9) PRIMARY KEY, Cname CHAR(20), Cpno CHAR(4), Ccredit SMALLINT ) GO

CREATE TABLE SC

(Sno CHAR(9) NOT NULL, Cno CHAR(9) NOT NULL, Grade SMALLINT, PRIMARY KEY(Sno,Cno) )

例3.定义SC表中的参照完整性

USE xskc GO

ALTER TABLE SC ADD

FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno)

例4.显示说明参照完整性的违约处理示例

USE xskc GO

ALTER TABLE SC ADD

FOREIGN KEY (Sno) REFERENCES Student(Sno)

ON DELETE CASCADE

/*当删除student表中的元组时，级连删除SC表中相应的元组*/ ON UPDATE CASCADE,

/*当更新student表中的sno时，级连更新SC表中相应的元组*/ FOREIGN KEY (Cno) REFERENCES Course(Cno)

ON DELETE NO ACTION

/*当删除course表中的元组造成了与SC表不一致时拒绝删除*/

ON UPDATE CASCADE

/*当更新course表中的cno时，级联更新SC表中相应的元组*/

例5.定义SC表Grade属性不允许为空

USE xskc GO

ALTER TABLE SC

ALTER COLUMN Grade SMALLINT NOT NULL

例6.创建部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主键

USE xskc GO

CREATE TABLE DEPT (Deptno NUMERIC(2), Dname CHAR(9) UNIQUE, Location CHAR(10), PRIMARY KEY (Deptno) )

例7.Student表的Ssex只允许取“男”或“女”

USE xskc GO

ALTER TABLE Student

ADD CONSTRAINT CK_Ssex_sex CHECK

(Ssex='男' OR Ssex='女') GO

例8.SC表的Grade值应该在0和100之间

USE xskc GO

ALTER TABLE SC

ADD CONSTRAINT SC_Grade_fanwei CHECK GO

(Grade>0 AND Grade<100)

10 数据库的安全性设计

实验学时：0.5学时 实验类型：验证 实验要求：必做 一、实验目的

加深对数据安全性的理解，并掌握SQL Server 2008中有关用户，角色及操作权限的管理方法。 二、实验内容

1、在SQL Server企业管理器中，设置SQL Server的安全认证模式。

SQL Server 2008中，用户分为2类，一类是服务器登录用户，一类是数据库登录用户，这两类用户综合起来才能完成对数据库的管理和权限设置。下面先介绍如何创建服务器登录账号。 SQL Server 2008的服务器登录方式有2种，一种是windows身份登录账号，一种是SQL Server身份登录账号。

1.1创建使用windows身份验证的SQL Server登录名

首先创建需要的windows用户。找到【控制面板】下的【管理工具】中找到【计算机管理】，打开【本地用户和组】节点，点击【用户】，在用户列表右击，选择【新用户】

在弹出的对话框中输入自己需要的用户名和密码，如下所示：

然后打开SQL Server Management Studio，在【对象资源管理器】中展开【安全性】节点，在【登录名】下面可查看已经存在的登录账号，右键点击【安全性】，选择【新建】—【登录】，点击下图所示【搜索】

使用【高级】按钮查找已经存在的用户：

确定后，在【对象资源管理器】下的登录名中，可以看到刚刚创建的用户：

本文来源：https://www.bwwdw.com/article/rxor.html