实验二

石 家 庄 铁 道 大 学

实 验 报 告

课程名称 数据库系统原理 数理系 学院 数1401 班 组桌号 实验者姓名 陈惜源 实验日期 2016 年 4 月 7 日

评分 教师签名

实验项目名称：数数据库和表的建立、数据操作 一、实验目的：SQL语言的使用

二、实验要求：在现有的数据库上进行各种查询操作，对视图的创建、使用等操作。

三、实验内容：

1、 开始→程序→Microsoft SQL Server→SQL Server Management Studio。 2、 在“连接到服务器”对话框中，选择“Windows身份验证”，点击“连接”，进 入SQL Server Management Studio操作界面。

3、单击“新建查询”按钮，在上部的空白区导入文件“实验二SQL语句.doc”中 的SQL语句，然后单击工具栏上 “执行”按钮（红色叹号）。建立起实验要操作的数据库test1，并在库中建立表、数据。下部的空白区显示该语句的运行情况。（注：以下操作均在上部的空白区输入 SQL语句，单击执行后，下部的空白区显示该语句的运行情况。）

4、在数据库test1中进行下列查询操作，将查询语句与结果写入实验报告。 1.查询所有供应商情况，先按城市升序排列，城市相同按供应商名称降序排列。 代码：

create database test2; Create table s2( Sno char(2) primary key, sname varchar(10) not null, status int, city varchar(10));

insert into s2 values('s1','精益',20,'天津'); insert into s2 values('s2','盛德',10,'北京'); insert into s2 values('s3','东方红',30,'北京'); insert into s2 values('s4','丰泰盛',20,'天津'); insert into s2 values('s5','为民',30,'上海');

SELECT * FROM s2 order by city,sname desc 截图

2.查询所有零件情况，先按零件名称升序排列，零件名称相同按重量降序排列。 代码：

create table p3( pno char(2) primary key, pname varchar(10) not null, color varchar(6), weight int);

insert into p3 values('p1','螺母','红',12); insert into p3 values('p2','螺栓','绿',17); insert into p3 values('p3','螺丝刀','蓝',14); insert into p3 values('p4','螺丝刀','红',14); insert into p3 values('p5','凸轮','蓝',40); insert into p3 values('p6','齿轮','红',30); select * from p3 order by pname,weight desc 截图

3.查询项目名中含有“厂”的项目情况。 代码

create table j1( jno char(2) primary key, jname varchar(20) not null, city varchar(10));

insert into j1 values('j1','三建','北京'); insert into j1 values('j2','一汽','长春'); insert into j1 values('j3','弹簧厂','天津') insert into j1 values('j4','造船厂','天津')

insert into j1 values('j5','机车厂','唐山'); insert into j1 values('j6','无线电厂','常州'); insert into j1 values('j7','半导体厂','南京'); select * from j1 where jname like '%厂' 截图

4.查询供应商名称中第二个字为“方”的供应商情况。 代码

select * from s join spj on s.sno=spj.sno where sname like '_方%' 截图

5.查询所有零件中的最大、最小、平均重量。 代码

select MAX(weight) 最重,MIN(weight) 最轻,AVG(weight) 平均重量 from P 截图

6.查询零件中名为“螺丝刀”的零件的种类数、平均重量。 代码

select COUNT(*) 零件种类数,AVG(weight) 平均重量 from P where pname='螺丝刀' 截图

7.查询供应商S1所供应的各种零件的名称和数量。 代码

select pname,qty from P join SPJ on P.pno=SPJ.pno where SPJ.sno='s1' 截图

8.查询工程J1所使用的各种零件的名称和数量。 代码

select pname 零件名,qty 数量 from P join SPJ on P.pno=SPJ.pno where jno='j1' 截图

9.查询没有使用红色螺丝刀的工程名称。

语句如下：

SELECT jname 工程名称 FROM p JoiN spj ON spj.pno=p.pno JOIN j ON j.jno=spj.jno WHERE p.color='红' 结果如图：

10.查询没有供应红色螺丝刀的供应商名称

SELECT sname 供应商名称 FROM s JoiN spj ON spj.sno=s.sno JOIN p ON spj.pno=p.pno WHERE p.color NOT LIKE '红' 结果如图：

11.查询所用零件数量超过500的工程项目号。

SELECT jno 工程项目号 FROM spj WHERE qty>=500 结果如图：

12.查询所用零件种类超过3种的工程项目名称。

SELECT jname 工程项目名称 FROM j WHERE jno IN (

SELECT jno FROM spj GROUP BY spj.jno

HAVING COUNT(*)>3) 结果如图：

13.查询使用了全部零件的工程项目名称。

SELECT JNAME FROM J WHERE NOT EXISTS (SELECT * FROM P WHERE NOT EXISTS (SELECT * FROM SPJ WHERE J.JNO=SPJ.JNO AND P.PNO=SPJ.PNO)) 结果如图：

14.查询至少供应了工程J1所使用的全部零件的供应商名称。

SELECT SNAME FROM S WHERE EXISTS ( SELECT * FROM SPJ

WHERE S.SNO=SPJ.SNO AND EXISTS

(SELECT PNO FROM SPJ WHERE JNO='J1')) 结果如图：

15.查询供应情况，显示内容为供应商名称、零件名、工程名称、数量。

SELECT SNAME 供应商名称,PNAME 零件名称, JNAME 工程名称,QTY 数量 FROM S

JOIN SPJ ON S.SNO=SPJ.SNO JOIN P ON P.PNO=SPJ.PNO JOIN J ON J.JNO=SPJ.JNO 结果如图：

16.查询“东方红”供应商供应情况，显示供应的零件名、工程名称、数量。

SELECT PNAME 零件名称,JNAME 工程名称, QTY 数量 FROM SPJ

JOIN S ON SPJ.SNO=S.SNO JOIN P ON SPJ.PNO=P.PNO JOIN J ON SPJ.JNO=J.JNO WHERE SNAME='东方红'

结果如图：

5、请为机车厂工程项目建立一个供应情况的视图，包括供应商代码，零件代码，供应数量。针对该试图完成如下查询并将SQL语句写入实验报告。

（1）查询机车厂工程项目使用的各种零件代码及其数量。 （2）查询处供应商S2的供应情况。 语句如下：

CREATE VIEW V_JC(SNO,PNO,QTY) AS

SELECT SNO,PNO ,QTY FROM SPJ JOIN J ON J.JNO=SPJ.JNO WHERE JNAME='机车厂' 5(1)

SELECT PNO,QTY FROM V_JC GROUP BY PNO,QTY 结果如图：

5(2)

SELECT * FROM V_JC WHERE SNO='S2' 结果如图：

四、实验总结

通过本次上机实验，我学到了在现有的数据库上进行各种查询操作，对视图的创建、使用等操作。了解到了如何对数据库进行查询操作，在做题的过程中也发现了自己的一些不足，比如：where后面不能加聚合函数，应该先把如何求的算法写出，再去写程序代码。提高了自己实际操作的水平。

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