数据库建立及查询
一、 实验目的
1．熟练掌握用SQL语句实现基本表的创建。
2．熟练掌握用SQL语句实现数据插入、数据更新和数据查询。 3．熟练掌握用SQL语句实现数据的简单查询和连接查询。 二、 实验环境
SQL Server2000 三、 预习要求
SQL Server2000的查询分析器和企业管理器的使用，SQL数据定义和数
据操纵语言。
四、 实验内容
(一) 用SQL语句建立如下mySPJ数据库，包括S，P，J，和SPJ四个基本表（四张表的
数据见附录部分 ，要求实现关系的三类完整性。 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。
(二) 分别使用插入、删除、修改的方式更新基本表中的数据。 1.利用Insert 语句将习题中给出的示例记录插入各表。 Insert into SPJ values('s1','p1','j1',200)
2.利用Update更新表中的记录：
①将p表中的所有红色零件的重量增加5。
update P set WEIGHT=WEIGHT+5 where COLOR='红'
②将spj表中所有天津供应商的QTY属性值减少10。用子查询。
update SPJ set QTY=QTY-10 where SNO=any(select SNO from S where CITY='天津')
3.利用Delete语句删除p表中的所有红色零件的记录。 delete from P where COLOR='红'
(一) 完成下面的简单查询：
①查询所有“天津”的供应商明细；
select * from S where CITY='天津'
②查询所有“红色”的14公斤以上的零件。
select * from P where COLOR='红' and WEIGHT>=14
③查询工程名称中含有“厂”字的工程明细。
select * from J where JNAME like '%厂%'
(二) 完成下面的连接查询：
①等值连接：求s表和j表的相同城市的等值连接。
select * from J,S where J.CITY=S.CITY;
②自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。
select S.SNAME,J.JNAME,P.PNAME
from S,J,P,SPJ
where S.SNO=SPJ.SNO and J.JNO=SPJ.JNO and P.PNO=SPJ.PNO;
③笛卡尔积：求s和p表的笛卡尔积
select * from P,S
④左连接：求j表和spj表的左连接。
select * from J,SPJ where J.JNO*=SPJ.JNO;
⑤右连接：求spj表和j表的右连接。
select * from J,SPJ where SPJ.JNO=*J.JNO;
(三) 分组查询：
1. 求各种颜色零件的平均重量。
select COLOR,AVG(WEIGHT) from P group by P.COLOR 2. 求北京供应商和天津供应商的总个数。 select S.CITY,COUNT(*) from S group by S.CITY; 3. 求各供应商供应的零件总数。 select SUM(QTY) from SPJ;
4. 求各供应商供应给各工程的零件总数。 select SNO,SUM(QTY) from SPJ group by SPJ.SNO; 5. 求使用了100个以上P1零件的工程名称。
select J.JNAME from J where J.JNO in(select SPJ.JNO from SPJ where
SPJ.PNO='p1' and SPJ.QTY>100);
6. 求各工程使用的各城市供应的零件总数。
select S.CITY,sum(SPJ.QTY) from SPJ,S where S.SNO=SPJ.SNO group by S.CITY; (四)
嵌套查询：
1. in连接谓词查询：
① 查询没有使用天津供应商供应的红色零件的工程名称。
select J.JNAME from J where JNO in (select JNO from SPJ,P where J.CITY!='天津' and P.COLOR='红');
② 查询供应了1000个以上零件的供应商名称。(having)
select J.JNAME from J where J.JNO in (select SPJ.JNO from SPJ group by SPJ.JNO having SUM(SPJ.QTY)>1000);
2. 比较运算符：求重量大于所有零件平均重量的零件名称。
select P.PNAME from P where P.WEIGHT>=any(select avg(WEIGHT) from P);
五、 实验方法和步骤
（一）使用Microsoft SQL Server企业管理器和查询分析器建立数据库mySPJ：
1.打开“开始－>程序－>Microsoft SQL Server－>企业管理器”；
2.在企业管理器左边的树标签中依次打开“Microsoft SQL Server－> SQL Server组－>(local)(Windows NT)－>数据库”， (local)(Windows NT)前的红色标记转化为绿色标记表明NT服务已启动；
3.从企业管理器的“工具”菜单中选择“SQL 查询分析器”，打开查询分析器后，在其窗口书写创建数据库mySPJ的SQL语句，点击执行按钮（或F5键）执行该SQL语句；
4.在企业管理器左边的树标签中查看数据库是否建立成功。 （二）在数据库mySPJ中建立S，P，J，和SPJ四个基本表：
按照实验内容给出的基本表结构在查询分析器窗口中书写SQL语句分别建立各表，并设置主键和外键约束；
create table s (pno char 10 not null, (sno char 10 not null, pname char 10 , sname char 10 , color char 10, stattus int 4, weight int 4) city char 10) create table j create table p (jno char 10 not null,
jnome char 10, (sno char 10 not null , city char 10) pno char 10 not null , jno char 10 not null , create table spj qty int 4)
（三）更新表数据：
1.利用Insert 语句将习题中给出的示例记录插入各表。
insert into s
values(s1,精益，20，天津); values(s2,盛锡，10，北京); values(s3,东方红，30，北京); values(s4,丰泰盛，20，天津); values(s5,为民，30，上海); values(s6,一拖，30，洛阳);
insert into p
values(p1,螺母，红，12); values(p2,螺栓，绿，17); values(p3,螺丝刀，蓝，14); values(p4,螺丝刀，红，14); values(p5,凸轮，蓝，40); values(p6,齿轮，红，30); 得到如下图表：
insert into j
values(J1,三建，北京); values(J2,一汽，长春); values(J4,弹簧厂，天津); values(J5,造船厂，天津); values(J6,机车厂，唐山); values(J7,无线电厂，常州); values(J8,半导体厂，南京); 得到如下图表：
insert into spj
values(s1,p1，j1，200); values(s1,p1，j3，100); values(s1,p1，j4，700); values(s1,p2，j2，100); values(s2,p3，j1，400); values(s2,p3，j2，200); values(s2,p3，j4，500); values(s2,p3，j5，400); values(s2,p5，j1，400); values(s2,p5，j2，100); values(s3,p1，j1，200); values(s3,p3，j1，200);
values(s4,p5，j1，100); values(s4,p6，j3，300); values(s4,p6，j4，200); values(s5,p2，j4，100); values(s5,p3，j1，200); values(s5,p6，j2，200); values(s5,p6，j4，500)； 得到如下图表：
2.利用Update更新表中的记录。 将p表中的所有红色零件的重量增加5。
update p
set weight=weight+5 where color='红'；
将spj表中所有天津供应商的QTY属性值减少10。 u pdate spj set qty=qty-10 where sno in (select sno from s
where city='天津');
3.利用Delete语句删除表中满足条件的记录。
delete from spj where pno in (select pno from p
where color='红'); delete from p
where color='红'; 六、实验中的问题
1、对SQL软件不熟悉，在使用时总是出现一些错误； 解决办法：通过自己练习和询问同学，熟悉软件的操作
2、删除表中数据时，没有先在有其外键的表中删除，导致删除失败。解决方法，先在SPJ表中删除又关的数据，再在主表中删除，就可完成删除的操作。 七、思考题
如何用SQL语句实现关系的三类完整性？
1 实体完整性是通过不允许输入空值或NULL.来实现的。 2 参照完整性通过设置外键设置。
3 用户自定义的完整性约束。可以通过自定义实现来实现。如设置字节长度。
