河南科技大学数据库实验报告

实验一：数据定义及更新语句练习

一实验目的：熟练掌握用SQL语句实现数据库和基本表的创建、数据的更新。

二．实验内容：

（一）用SQL语句建立如下mySPJ数据库，包括S，P，J，和SPJ四个基本表(教材第二章习题5中的四个表)，要求实现关系的三类完整性。

S(SNO,SNAME,STA TUS,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。

（二）在数据库mySPJ中建立S，P，J，和SPJ四个基本表：

按照实验内容给出的基本表结构在查询分析器窗口中书写SQL语句分别建立各表，并设置主键和外键约束；

三完成情况

USE master

GO

CREATE DATABASE myspj

ON

(NAME=TEXTS_dat,

FILENAME='F:\数据库实验\myspjdat.mdf',

SIZE= 10,

MAXSIZE= 50,

FILEGROWTH= 5 )

LOG ON

(NAME='TEXTS_log',

FILENAME='F:\数据库实验\myspjlog.ldf',

SIZE= 5MB,

MAXSIZE= 25MB,

FILEGROWTH= 5MB)b

GO

create table s

(

sno char(9)primary key,

sname char(20),

statu int,

city char(20),

);

create table p

(

pno char(9)primary key,

pname char(20),

color char(20),

weigh int,

);

create table j

(

jno char(9)primary key,

jname char(20),

city char(20),

);

create table spj

(

sno char(9),

pno char(9),

jno char(9),

qty int,

primary key(sno,pno,jno),

foreign key (sno)references s(sno),

foreign key (pno)references p(pno),

foreign key (jno)references j(jno),

);

1.利用Insert 语句将习题中给出的示例记录插入各表。insert

into s

values('s1','精益','20','天津');

insert

into s

values('s2','盛锡','10','北京');

insert

into s

values('s3','东方红','30','北京'); insert

into s

values('s4','丰泰盛','20','天津'); insert

into s

values('s5','为民','30','上海'); insert

into p

values('p1','螺母','红','12'); insert

into p

values('p2','螺栓','绿','17'); insert

into p

values('p3','螺丝刀','蓝','14'); insert

into p

values('p4','螺丝刀','红','14'); insert

into p

values('p5','凸轮','蓝','40'); insert

into p

values('p6','齿轮','红','30'); insert

into j

values('j1','三建','北京');

insert

into j

values('j2','一汽','长春');

insert

into j

values('j3','弹簧厂','天津');

insert

into j

values('j4','造船厂','天津');

insert

into j

values('j5','机车厂','唐山');

insert

into j

values('j6','无线电厂','常州'); insert

values('j7','半导体厂','南京'); insert

into spj

values('s1','p1','j1','200'); insert

into spj

values('s1','p1','j3','100'); insert

into spj

values('s1','p1','j4','700'); insert

into spj

values('s1','p2','j2','100'); insert

into spj

values('s2','p31','j1','400'); insert

into spj

values('s2','p3','j2','200'); insert

into spj

values('s2','p3','j4','500'); insert

into spj

values('s2','p3','j5','400'); insert

into spj

values('s2','p5','j1','400'); insert

into spj

values('s2','p5','j2','100'); insert

into spj

values('s3','p1','j1','200'); insert

into spj

values('s3','p3','j1','200'); insert

into spj

values('s4','p5','j1','100'); insert

into spj

values('s4','p6','j3','300');

into spj

values('s4','p6','j4','200');

insert

into spj

values('s5','p2','j4','100');

insert

into spj

values('s5','p3','j1','200');

insert

into spj

values('s5','p6','j2','200');

2.利用Update更新表中的记录：

1将p表中的所有红色零件的重量增加5。

update p

set weigh=weigh+5

where color='红';

2将spj表中所有天津供应商的QTY属性值减少10。用子查询。

并记录在实验报告中。

update spj

set qty=qty-10

where sno in

(

select sno

from s

where city='天津'

);

3.利用Delete语句删除p表中的所有红色零件的记录。

delete

from p

where color='红';

思考题

（一）在为各表设定关键字时弹出的页面中有关键字和索引选项，试通过给基本表设定这两个项目并说明二者的区别。

答:关键字受主键影响，而索引不受主键影响

（二）在基本表中输入数据时，注意数据与字段的数据类型和长度以及能否为空的属性是否一致，保存时如有错误分析错误原因，及时改正并将错误报告写在实验报告中。

七实验总结：心得体会，建议等。

答：从这次实验中我明白一个道理：细节决定成败，尤其是要注意sql语言与c语言的差别。

实验二：简单查询和连接查询

一实验目的：熟练掌握用SQL语句实现的简单查询和多个数据表连接查询。

二实验内容：

(一)完成下面的简单查询：

①查询所有“天津”的供应商明细；

②查询所有“红色”的14公斤以上的零件。

③查询工程名称中含有“厂”字的工程明细。

(二)完成下面的连接查询：

①等值连接：求s表和j表的相同城市的等值连接。

②自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，

并按照供应、工程、零件排序。

③笛卡尔积：求s和p表的笛卡尔积

④左连接：求j表和spj表的左连接。

⑤右连接：求spj表和j表的右连接。

三完成情况

(三)完成下面的简单查询：

①询所有“天津”的供应商明细；

select*

from s

where city='天津';

②查询所有“红色”的14公斤以上的零件。

select pno

from p

where color='红'and weigh>14;

③查询工程名称中含有“厂”字的工程明细。

select*

from j

where jname like'%厂';

(四)完成下面的连接查询：

①值连接：求s表和j表的相同城市的等值连接。

select s.*,j.*

from s,j

where s.city=j.city;

②自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，

并按照供应、工程、零件排序。

select s.sname,p.pname,j.jname

from s,p,j,spj

where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno;

③笛卡尔积：求s和p表的笛卡尔

select*

from s,p

④左连接：求j表和spj表的左连接。

select j.*,sno,pno,qty

from j left outer join spj on (j.jno=spj.jno);

⑤右连接：求spj表和j表的右连接。

select j.*,sno,pno,qty

from spj right outer join j on (j.jno=spj.jno);

回答思考题提出的问题，

查看实验内容2中左连接和右连接的执行结果是否一致，为什么？

答：不一样，左连接是输出第一个表的全部，二右连接是输出第二个表的全部。

七实验总结：心得体会，建议等。

答：从这次实验中我明白一个道理：细节决定成败，尤其是要注意sql语言与c语言的差别。

实验三：分组查询和嵌套查询

一、实验目的：熟练掌握用SQL语句实现多个数据表的分组查询和嵌套查询。

二、实验内容：

完成下面的查询：

(一)完成下面的查询：

1、分组查询：

①求各种颜色零件的平均重量。

select color,A VG(weigh)weight

from p

group by color;

②求北京供应商和天津供应商的总个数。

select COUNT(city)个数

from s

where city='北京'or city='天津';

③求各供应商供应的零件总数。

select sno,sum(qty)数目

from spj

group by sno

order by数目asc;

④求各供应商供应给各工程的零件总数。

select sno,jno,sum(qty)数目

from spj

group by sno,jno;

⑤求使用了100个以上P1零件的工程名称。

select jname

from j

where jno in

(

select jno

from spj

where pno='p1'and qty>100

);

⑥求各工程使用的各城市供应的零件总数。

select jname

from j

where jno in

(

select jno

from spj

where pno='p1'and qty>100

);

（二）嵌套查询：

1.in连接谓词查询：

①查询没有使用天津供应商供应的红色零件的工程名称。

select jname

from j

where jno not in

(

select jno

from s,p,spj

where spj.sno=s.sno and

spj.pno=p.pno and

p.color='红'and

s.city='天津'

)

②查询供应了1000个以上零件的供应商名称。(having)

select sname

from s

where sno in

(

select sno

from spj

group by sno

having SUM(qty)>1000

)

2.比较运算符：求重量大于所有零件平均重量的零件名称。

select pname

from p

where weigh>(

select A VG(weigh)

from p

3.)Exists连接谓词：

①查询供应J1的所有的零件都是红色的供应商名称。

select sname

from s

where exists(

select*

from p,spj

where p.pno=spj.pno and

spj.sno=s.sno and

jno='j1'and

color='红');

②至少用了供应商S1所供应的全部零件的工程号JNO。

实验五：触发器和存储过程

一、实验报告要求：

(一)实验目的：理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过

程的编写，并初步掌握什么情况下使用事务。

(二)实验内容：

有一个小型的图书管理数据库，包含的表为：

bookstore（bookid，bookname，bookauthor，purchasedate，state）；--图书库存表

borrowcard（cardid，ownername）；--借书证表

borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表

写一个存储过程，实现借书操作，要求有事务处理。（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。（3）要求用触发器实现表的完整性控制。

（三）完成情况（附上设计的SQL语句）。建立数据库：

create database bookstor

use bookstor

create table bookstore

(

bookid int,

bookname char(20),

bookauthor char(20),

purchasedate char(20),

stat char(6),

primary key(bookid),

Check(stat in('在库','不在库')),

);

create table borrowcard

(cardid int primary key,

ownername char(20),);

create table borrowlog

(cardid int,

bookid int,

borrowdate char(20),

returndate char(20),

primary key(cardid,bookid),

foreign key(cardid)references borrowcard(cardid),

foreign key(bookid)references bookstore(bookid),);

存储过程：

create procedure[dbo].[jieshu](@cardid int,@bookid int,@borrowdate char(20),@returndate char(20))

as

begin transaction

insert

into borrowlog

values(@cardid,@bookid,@borrowdate,@returndate)

if exists(select*from bookstore,borrowcard where bookid=@bookid and stat='在库'and cardid=@cardid)

begin

update bookstore

set stat='不在库'

where bookid=@bookid

commit transaction

end

else

begin

if not exists(select*from bookstore where bookid=@bookid)

print'不存在该书'

if not exists(select*from borrowcard where cardid=@cardid)

print'没有此用户'

rollback transaction

End

查询语句：exec jieshu1002062,1,'20121103','2012124'

结果：

触发器:

create trigger stor

on borrowlog

after insert

as

if(new.borrowdate>new.returndate)

print'失败，还书时间不应早于结束时间'

begin

rollback transaction

end

（四）问题及解决：首先写出执行语句不成功的时候系统报告的错误信息。然后分析错误原因，并给出解决办法。

（五）回答思考题提出的问题，

思考题：

如何通过系统的设置实现类似的功能，而不需触发器？

答：建立数据库时定义完整性约束条件。