数据库实验内容部分参考答案

1章 SQL基础应用型实验

SQL语言的初步使用

．局部变量与全局变量的使用
定义及使用局部变量
定义一个int的整型变量＠a，并分别给其赋值45和345。
定义一个长度为9的可变长型字符变量＠b，并分别给其赋值“Welcome to Zhaoqing”和
Zhaoqing”。
全局变量的使用
返回当前SQL Server服务器的版本。
返回当天试图连接到本服务器的连接的数量。
．常量函数及系统函数的使用
数学函数的使用
2、1.2的绝对值，256的平方根及6的平方。
字符串函数的使用
返回字符串“Abcd”第一个字符的ASCII码值。
分别将字符串“china”、“MACHINA”转换成大写、小写字母。
将字符串“CHINA”、 “ machine ”、“PRESS”首尾连接，连接前使用RTRIM和LTRIM
machine ” 的左边、右边及左右两边的空格。
machine ')+'PRESS'
machine ')+'PRESS'
machine '))+'PRESS'
使用SUBSTRING函数从字符串“CHINA MACHINE PRESS”中返回字符串
MACHINE”，并使用REVERSE函数将字符串“MACHINE”逆序返回。
MACHINE PRESS',7,7)

日期、时间函数的使用
使用GETDATE（）函数返回系统当前日期及当前日期的年份、月份及日期。
使用DATEDIFF函数返回某个给定日期与当前系统日期相差的天数。
系统函数的使用
显示正在使用的用户名、数据库名及服务器名。
返回当前主机标识及主机名称。
．创建及删除用户定义的数据类型
通过查询分析器为master数据库创建一个名为birthday的用户定义数据类型（要求基于
类型），并且该数据类型允许空值。
通过查询分析器删除master数据库中的birthday数据类型。
数据库的定义与管理

．用Transact-SQL语句创建数据库。
primary
(name=shiyan_data,
filename='D:\data\shiyan_data.mdf',
size=3MB,
maxsize=20MB,
filegrowth=1MB)
(name=shiyan_log,
filename='D:\data\shiyan_log.ldf',
size=1MB,
maxsize=5MB,
filegrowth=1MB)
．用Transact-SQL语句查看与修改数据库属性

use mydb1
file(name=mydb1_log,maxsize=100MB)
file(name=mydb1_log,size=5MB)

use mydb1
filename=' C:\Program Files\Microsoft SQL Server\MSSQL\Data \ mydb1fz.ndf',
size=2mb,
maxsize=10mb,
filegrowth=1mb)
．删除数据库MyDB1
数据表的定义与管理

、（给出 对应的SQL语句）
primary key,
sname char(20) not null,
ssex char(2),
sage smallint,
sdept char(20)
primary key,
cname char(40),
cpno char(4) references course(cno),
ccredit smallint
cno char(4),
grade smallint
primary key(sno,cno),
李勇','男',20,'计算机系')
刘晨','女',19,'信息系')
王敏','女',18,'数学系')
张立','男',19,'信息系')
数据库',null,4)
高等数学',null,2)
信息系统',null,4)
操作系统',null,3)
数据

结构',null,4)
数据处理',null,2)
语言程序设计',null,4)
．利用查询分析器在数据库ShiYan中建立以下四个数据表（S表，P表，J表，SPJ表）。
primary key,
sname varchar(20)not null,
status smallint,
city varchar(30),

pname varchar(20) not null,
color char(6),
weight real default(0)
jname varchar(20) not null,
city varchar(30)
table spj
pno char(4),
jno char(4),
qty int not null check(qty>=1 and qty<=100) [check(qty between 1 and 100)],
或者
table spj
pno char(4),
jno char(4),primary key(sno,pno,jno),
qty int not null constraint c1 check(qty between 1 and 100),
scdb数据库数据的输入方法，
insert into语句输入，这里就不给出具体的语句了。
．数据表的删除操作。
Transact-SQL删除数据表MyTab2。

．数据表的修改操作。
(1)为S表新建一属性名为PHONE的列，其数据类型为长度为12个字符的字符串类型，默认值
000000000000”。
为J表的JNAME列创建一个列值不为空的约束条件NotnullJname。
(3) 删除J表中JNAME属性列上取值不为空的约束条件NotnullJname。
use shiyan
为P表的PNO属性列创建一个该属性列值互不重复的约束条件UnqPno。
删除P表中PNO属性列上取值不重复的约束条件UnqPno。
为SPJ表的QTY属性列建立取值必须是100的整数的约束条件QTYNum。

．索引的创建操作。
(1) 在S表的SNO属性列上建立一个名为SnoIndex的唯一索引。
(2) 为P表的PNO列创建一个名为PnoIndex的唯一索引，索引值按降序排列。
use shiyan
为J表的JNAME属性列创建一个名为JnameIndex的非聚簇索引。
为SPJ表的SNO、PNO、JNO三属性列创建一个名为SPJIndex的非聚簇索引，且按SNO属
PNO属性值和JNO属性值降序排列。
．索引的删除操作
删除S表上的SnoIndex索引。
删除P表上的PnoIndex索引。
.Pnoindex
删除J表上的JnameIndex索引。
删除SPJ表上的SPJIndex索引。
单表查询

．查询为工程J1供应零件的供应商号码SNO。
shiyan
distinct sno
spj
jno='j1'
．查询为工程J1供应零件P1的供应商号码SNO。
shiyan
distinct sno
spj
jno='j1' and pno='p1'
．找出所有供应商的名称和所在城市。
shiyan
sname,city
s
．找出零件的所有信息，以及仅找出零件的颜色和重量。
shiyan
*
p
shiyan
pno,pname,color,weight
p
．找出使用供应商S1所供应零件的工程号码。
shiyan
sno,jno
spj
sno='s1'
．找出为工程供应零件的总数量不低于500的供应商号码及供应总数量，结果按供应商号码分类并

shiyan
sno,sum(qty) TotalQTY
spj
by sno
sum(qty)>=500
by TotalQTY desc
．从J表中分别检索出第1条及前33%的工程项目信息。
shiyan
top 1 *
j
top 33 percent *
j
．统计P表中颜色为红色的

零件的个数，并指定该查询列的名称为“红色零件数”。
shiyan
count(color) as '红色零件数'
p
color='红'
by color

'红色零件数'=count(*)
p
color='红'
．查询P表中各零件编号，名称及重量按86%计算后的信息，其中重量按86%计算后的查询列名
。
shiyan
pno,pname,零件净重=0.86*weight
p
．查询SPJ表，要求查询结果式样为“供应商S1为工程项目J1供应零件P1的数量为300”。
shiyan
'供应商'+sno+'为工程项目'+jno+'供应零件'+pno+'的数量为'+ ltrim(str(qty))
spj
．查询S表STATUS值大于20且小于40，或SNAME字段值的第一个字为“精”或第三个字为

shiyan
*
s
(status>20 and status<40) or (sname like '精%' or sname like '__[益,
]%')
．查询J表中JNAME值为三建和机车厂的工程项目信息。
*
j
jname in ('三建','机车厂')
多表查询

．查询为工程J1供应红色零件的供应商号码SNO。
shiyan
sno
spj
jno='j1' and pno in (select pno
p
color='红')
．查询没有使用天津供应商生产的零件并且当前工程所使用零件的颜色全部为红色的工程号JNO。
shiyan
jno
spj
sno in (select sno
from s
where city<>'天津')
and
pno in (select pno
from p
where color='红')
．查询至少选用了供应商S1所供应的全部零件的工程号JNO。
distinct jno
spj as x
not exists ( select *
from spj as y
where sno='s1'
and not exists
(select *
from spj as z
where z.pno=y.pno and z.jno=x.jno)
．找出工程项目J2使用的各种零件的名称及其重量。
shiyan
pname,weight
p,spj
spj.jno='j2' and spj.pno=p.pno

pname,weight
p
pno in (select distinct pno from spj where jno='j2')
．找出上海厂商供应的所有零件号码。
shiyan
distinct pno
spj
sno=(select sno
s
city='上海')
．找出使用上海产的零件的工程名称。
shiyan
distinct jname
s,j,spj
spj.jno=j.jno and spj.sno=(select sno
s
s.city='上海')
．找出没有使用天津产的零件的工程号码。
shiyan
distinct jno
spj
sno in (select sno
s
city<>'天津')
．找出重量最轻的红色零件的零件编号PNO。
shiyan
pno
p
weight=(select min(weight)
p
color='红')

top 1 pno
p
color='红'
by weight
．找出供应商与工程所在城市相同的供应商提供的零件号码。
shiyan
distinct pno
s,j,spj
s.sno=spj.sno and j.jno=spj.jno and s.city=j.city
．找出所有这样的一些＜CITY，CITY，PNAME＞三元组，使得第一个城市的供应商为第二个城
PNAME。
shiyan
distinct s.city CITYA, j.city CITYB,p.pname PNAME
s,p,j,spj
s.sno=spj.sno and j.jno=spj.jno and p.pno=spj.pno

．重复第15题，但不检索两个CITY值相同的三元组。
shiyan
distinct s.city CITYA, j.city CITYB,p.pname PNAME
s,p,j,spj
s.sno=spj.sno and j.jno=spj.jno and p.pno=spj.pno and s.city<>j.city
．找出供应商S1为工程名中含有“厂”字的工程供应的零件数量总和。
shiyan
sum(qty) TotalQTY
spj
sno='s1' and jno in(select jno
j
jname like '%厂%')
查询优化

．查询P表中各工程项目编号，名称及重量按86%计算后的信息，其中重量按86%计算后的查询
。
SQL脚本：
零件净重=0.86*weight

．查询没有使用天津供应商生产的零件并且当前工程所使用零件的颜色全部为红色的工程号JNO。
SQL脚本：

天津') and
红')

数据更新

．在S表中插入元组“s6，华誉，40，广州，020********”。
shiyan
s
('s6','华誉',40,'广州','020********')
．在J表中插入元组“j8，传感器厂”。
shiyan

j(jno,jname)
('j8','传感器厂')
．对每一个供应商，求他为各种工程供应零件的总数量，并将此结果存入数据库。
shiyan
table TotalQTYofS(SNO char(4) not null unique,
int)

TotalQTYofS
sno,sum(qty)
spj
by sno
．将P表中PNO值为p6的元组的color属性值改为绿，weight属性值改为60。
shiyan
p
color='绿',weight=60
pno='p6'
．将SPJ表中前4个元组的qty属性值统一修改为300。
shiyan
rowcount 4
spj set qty=300
rowcount 0

shiyan
top (4) spj
qty=300
．将S表中city属性名含有“京”或“津”的相应status属性值增加100。
s
status=status+100
city like '%京%' or city like '%津%'
．将供应商s2为“一汽”工程项目所供应的零件数量修改为800。
shiyan
spj
qty=800
sno='s2' and jno=(select jno
j
jname='一汽')
．将全部红色零件的颜色修改为浅红色。
shiyan
p
color='浅红'
color='红'
* from p
．由s5供给j4的零件p6改为由s3供应，请在数据库中作必要的数据修改。
shiyan
spj
sno='s3'
sno='s5' and jno='j4' and pno='p6'
．在SPJ表中新增一列属性名为SDATE的属性列，对该表中的每一元组在SDATE属性列上填上

shiyan
table spj
SDATE datetime
spj
SDATE=getdate()
．删除所在城市为“广州”的供应商记录。
shiyan
s
city='广州'
．删除所有零件名称中第一个字为“螺”字的零件记录，并在供应情况表中删除相应的记录。
shiyan
*
spj
pno in(select pno
p
pname like '螺%')
p
pname like '螺%'
．删除s3和s4两供应商为“三建”工程供应“螺母”或“螺丝刀”零件的相应供应情况数据信

shiyan
*
spj
(sno='s3' or sno='s4') and jno in (select jno
j
jname='三建')
pno in (select pno
p
pname='螺母' or pname='螺丝
')
数据视图

．用Transact-SQL语言为S表的sno和sname属性建立名为S_View的视图。

shiyan
view S_View
sno,sname
s
．为三建工程项目建立一个名为SanJian_View的供应情况视图，该视图包括供应商代码（sno），
pno），供应数量（qty）。
shiyan
view SanJian_View
sno,pno,qty
spj
jno=(select jno
j
jname='三建')
．在SanJian_View中找出三建工程项目使用的各种零件代码及其数量。
shiyan
pno,sum(qty)
SanJian_View
by pno
．在SanJian_View中找出供应商s1的供应情况。
shiyan
*
SanJian_View
sno='s1'
．建立颜色为红色且重量大于10的零件视图P_View。
shiyan
view P_View
*
p
color='红' and weight>10
．将供应商号以及它为所有工程所供应零件的总数定义为一个视图S_Q_View。
shiyan
view S_Q_View(sno,TotalQTY)
sno,sum(qty)
spj
by sno
．在SPJ表上按属性SNO分组，求出每组在属性QTY属性上的最大值、最小值和平均值，且将
RVE_View中，然后查询视图RVE_View中供应商号为“S4”的记录。
shiyan
view RVE_View(sno,qtyMax,qtyMin,qtyMvg)

sno,max(qty),min(qty),avg(qty)
spj
by sno
*
RVE_View
sno='s4'
．将S_View视图中供应商号为s5的供应商名修改为“为国”，并查询S表的修改结果。
shiyan
S_View
sname='为国'
sno='s5'
* from S
．在S_View视图中插入一个新的记录，供应商号为“s6”，供应商名为“华誉” ，并查询S表的

shiyan
S_View
('s6','华誉')
* from S
．删除SanJian_View视图中供应商号为s1的相应记录。
shiyan
SanJian_View
sno='s1'
* from spj
．为视图S_View建立供应商名sname的视图S_Sname_View。
shiyan
view S_Sname_View
sname
S_View
．将视图S_View改名为SS_View。
sp_rename 's_view','ss_view','object'
．用Transact-SQL语言将视图SS_View作如下修改：在SS_View中新增一列，列名为city，并且
city属性值为“上海”。
view SS_View
select sno,sname,city
s
city='上海'
．用Transact-SQL语言删除视图SS_View。
view SS_View
触发器

．利用Transact-SQL语言为S表建立一个名为s_insert的insert触发器，通过s_insert触发器以保
S表中插入记录时status字段值要以1、2、3、4或5字符打头。
status字段值不是以1、2、3、4或5字符打头的，则拒绝插入该记录，并且输出
status字段值不是以1、2、3、4、5字符打头的！”（注意：只考虑每次向S表中插入一
。
字段值不是以1、2、3、4、5字符打头的！'
．利用企业管理器为P表建立一个名为p_weight的update触发器，通过p_weight触发器以保证修
P表中数据时，限制weight字段的值在1000以内。如果修改后的记录的weight字段值超过了1000，
weight字段值不在1000之内，请重新给定该
”，否则允许修改记录，并且输出提示信息“被修改记录的weight字段值满足要求，成功完
”。
from inserted join deleted
on inserte

d.pno=deleted.pno)>1000
被修改记录的weight字段值不在1000之内，请重新给定该字段值。'
被修改记录的weight字段值满足要求，成功完成修改操作。'
．利用Transact-SQL语言为J表建立一个名为j_delete的delete触发器，通过j_delete触发器保证
J中的记录时，该待删除记录在SPJ表中存在引用关系，并且对应的引用关系记
QTY值不小于50，则不能删除该工程项目记录，输出提示信息“不满足删除条件。”；
”。

不满足删除条件。'

相应的工程项目信息已经成功删除。'
．利用Transact-SQL语言修改触发器s_insert，修改后的s_insert触发器能保证在修改s表中数据记
status字段的值不允许修改。

．将触发器s_insert的名称更改为s_status_update。
．*查看数据表P中所有的inserted触发器，并观察其执行结果。
．*查看触发器j_delete的定义文本。
．*查看触发器p_weight的所有者和创建日期。
．*删除触发器s_status_update。