山西农业大学数据库系统原理实验报告

《数据库原理》实验报告
题目：交互式SQL语言1班级：学号：姓名：日期：
一、实验目的
1.了解MS SQL Server2008的工作环境和系统架构；
2.掌握数据库的交互式SQL工具，通过SQL语言对数据库进行熟练操作；
3.完成课本《数据库系统概论》第三章习题的第三题。

二、实验平台
采用MS SQL Server2008为RDBMS，利用查询分析器作为交互查询工具对数据库进行操作。

三、实验内容
数据库管理系统的安装和启动，新建数据表、索引和视图，修改数据表，更新数据，备份数据库，删除数据库。

四、实验步骤
1.SQL Server2008的启动
开始—所有程序—Microsoft SQL Server2008—SQL Server Management Studio—选择windows身份验证连接到服务器。

2.新建数据库【请写出具体的SQL语句】
工具栏—“新建查询”按钮打开查询分析器窗口—使用SQL语句新建数据库SPJ create database spj
3.定义基本表【请写出具体的SQL语句】
①创建供应商表S，属性包括：SNO(char,主码)，SNAME(char,非空,唯一)，STATUS(smallint)，CITY(char)。

②创建零件表P，属性包括：PNO(char,主码)，PNAME(char,非空)，STATUS(char)，CITY(smallint)。

③创建工程表J，属性包括：JNO(char,主码)，JNAME(char,非空,唯一)，CITY(char)。

④创建供应关系表SPJ，属性包括：SNO(char,主码)，PNO(char,主码)，JNO(char,
主码)，QTY(int)，且SNO、PNO、JNO分别是S、P、J表中SNO、PNO、JNO的外码。

create table s
(sno char(20)primary key,
sname char(20)not null unique,
status smallint,
city char(20));
create table p
(pno char(20)primary key,
pname char(20)not null,
status char(20),
city smallint);
create table j
(jno char(20)primary key,
jname char(20)not null unique,
city char(20));
create table spj
(sno char(20),
pno char(20),
jno char(20),
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));
4.插入数据【请写出具体的SQL语句，写出前三行，剩余部分用省略号代替即可】
①插入S表中的数据；
②插入P表中的数据；
③插入J表中的数据；
④插入SPJ表中的数据。

INSERT
INTO s
VALUES('S1','精益',20,'天津'),
('S2','盛锡',10,'北京'),
('S3','东方红',30,'北京'),
('S4','丰泰盛',20,'天津'),
('S5','为民',30,'上海')
INSERT
INTO p
VALUES('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30)
INSERT
INTO j
VALUES('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京')
INSERT
INTO spj
VALUES('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500)
5.更新数据【请写出具体的SQL语句】
①（习题8）把全部红色零件的颜色改为蓝色；update p
set status='蓝'
where status='红'
②（习题9）由S5供应给J4的零件P6改为由S3供应；
update spj
set sno='s3'
where jno='j4'and pno='p6'and sno='s5';
③（习题10）删除供应商S2的记录，并从供应关系中删除相应记录；delete from spj
where sno in
(select sno
from s
where sno='s2')
delete from s
where sno='s2'
6.新建索引和视图【请写出具体的SQL语句】
①在SPJ表上建立关于SNO、PNO、JNO升序的唯一索引SPJ_INDEX；create unique index spj_index
on spj(sno,pno,jno)
②为三建工程项目建立一个供应情况的视图SPJ_VIEW，包括SNO、PNO和QTY共3个属性。

create view spj_view
as
select sno,pno,qty
from spj
7.备份数据库
将数据库SPJ备份以便下次实验继续使用。

8.删除数据库对象【请写出具体的SQL语句】
①删除数据表SPJ；
drop table spj
②删除索引SPJ_INDEX；
drop index spj_index
③删除视图SPJ_VIEW；
drop view spj_view
④删除数据库SPJ。

drop database spj
《数据库原理》实验报告
题目：交互式SQL语言2班级：学号：姓名：日期：
一、实验目的
4.掌握数据库的交互式SQL工具，通过SQL语言对数据库进行熟练操作；
5.完成课本《数据库系统概论》第三章习题的第四、第五、第十一题。

二、实验平台
采用MS SQL Server2008为RDBMS，利用查询分析器作为交互查询工具对数据库进行操作。

三、实验内容
数据库的还原，查询。

四、实验步骤
1.还原数据库
将上一次备份过的数据库还原，以便进行下一步操作。

2.简单查询（只涉及一个表）【请写出具体的SQL语句】
①习题3-5-1；找出所有供应商所在的城市和姓名
select sname,city
from s
②习题3-5-2；找出所有零件的名称、颜色、重量
select pname,status,city
from p
③习题3-5-3；找出使用s1所供应零件的工程号码
select jno
from spj
where sno='s1'
④习题2-5-1；求供应工程j1零件的供应商号码sno
select sno
from spj
where jno='ji'
⑤习题2-5-2。

求供应工程j1零件p1的供应商号码sno
select sno
from spj
where jno='j1'and pno='p1'
3.视图查询（在视图上进行查询）【请写出具体的SQL语句】
①习题3-11-1；找出三建工程项目使用的各种零件代码及其数量
select sno,qty
from spj_view
②习题3-11-2。

找出s1的供应情况
select*
from spj_view
where sno='s1'
4.复杂查询（涉及多个表，可用连接查询、嵌套查询、集合查询等多种方式实现）【请写出具体的SQL语句】
①习题3-5-4；（提示：连接表P和SPJ）找出工程项目j2使用的各种零件名称及其数量
select pname,qty
from spj,p
where p.pno=spj.pno and spj.jno='j2'
②习题3-5-5；找出上海厂商供应的所有零件号码（提示：连接表S和SPJ）select pno
from spj,s
where s.sno=spj.sno and s.city='上海'
③习题2-5-3；求供应工程j1零件为红色的供应商号码（提示：方法1，连接查询，连接表P和SPJ，并且工程号为J1，颜色为红；方法2，嵌套查询，内层查询红色零件的编号，外层查询中再加入工程号为J1的条件。

）
方法1：select sno
from spj,p
where spj.pno=p.pno and jno='j1'and status='红'
方法2：select sno
from spj
where jno='j1'and pno in(select pno
from p
where status='红')
④习题3-5-6；找出使用上海产的零件的工程名称（提示：方法1，连接查询，连接表J、S和SPJ；方法2，用带IN谓词的嵌套查询）
方法1：select jname
from j,s,spj
where spj.sno=s.sno and j.jno=spj.jno and s.city='上海'
方法2：select jname
from j
where jno in(select jno
from spj
where sno in(select sno
from s where
city='上海'))
⑤习题3-5-7；找出没有使用天津产的零件的工程号码（提示：方法1：用带NOT EXIST谓词的嵌套查询实现；方法2：用集合操作实现，前一个查询得到所有工程号码，后一个查询得到使用天津产的零件的工程号码，然后做EXCEPT 运算即可。

）
方法1：select distinct jno
from j
where not exists
(select*
from s,spj
where s.sno=spj.sno and j.jno=spj.jno and s.city='天津')
方法2：select jno
from j
except
select jno
from s,spj where s.sno=spj.sno and s.city='天津'
《数据库原理》实验报告
题目：存储过程班级：学号：姓名：日期：
一、实验目的
掌握用存储过程对数据库对象进行增、删、改、查等逻辑操作。

二、实验平台
MS SQL Server2008及其PL/SQL引擎。

三、实验内容
用存储过程对数据库对象进行各种逻辑操作。

四、实验步骤
1.还原数据库
还原学生-选课数据库，以便进行下一步操作。

2.不带参数的存储过程
编写存储过程，通过存储过程对数据库对象进行增、删、改、查操作。

【这部分不需要写出具体代码，对课件中的示例进行练习即可】
3.带参数的存储过程【请写出具体的PL/SQL语句】
①编写一个带参数的存储过程，通过输入学号、课程号、新的分数三个参数实现对选课表中学生成绩的修改，当输入的学号或课程号不存在时报错；create proceduce scg(sno_s char(9),cno_s char(4),grade_s smallint) as declare
grade_a smallint;
begin
select grade into grade_a
from sc
where sno=sno_s and cno=cno_s;
if grade_a is null then
rollback;
return;
end if;
update sc
set grade=grade_s
where sno=sno_s and cno=cno_s;
commit;
end;
②编写一个带参数的存储过程，通过输入转入账户、转出账户、转账金额三个参数实现转账功能，并可以进行“账户是否存在”和“余额是否足够”两项逻辑判断。

CREATE PROCEDURE TRANSFER(inAccount INT，outAccount INT，amount FLOAT) AS DECLARE
totalDeposit FLOAT;
BEGIN
SELECT total INTO totalDeposit
FROM ACCOUNT WHERE ACCOUNTNUM=outAccount;
IF totalDeposit IS NULL THEN
ROLLBACK;
RETURN;
END IF;
IF totalDeposit<amount THEN
ROLLBACK;
RETURN;
END IF;
UPDATE account SET total=total-amount WHERE ACCOUNTNUM=outAccount; UPDATE account SET total=total+amount WHERE ACCOUNTNUM=inAccount; COMMIT;
END;。