软件技术基础数据库

实验五数据库建立一、实验目的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。

（二）分别使用插入、删除、修改的方式更新基本表中的数据。

五、实验方法和步骤（一）使用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语句分别建立各表，并设置主键和外键约束；1.创建表SCREATE TABLE S(SNO CHAR(3) PRIMARY KEY NOT NULL,SNAME V ARCHAR(20) NOT NULL,STATUS INT NOT NULL,CITY CHAR(10) NOT NULL,)2.创建表PCREATE TABLE P(PNO CHAR(3) PRIMARY KEY NOT NULL,PNAME V ARCHAR(20) NOT NULL,COLOR CHAR(10) NOT NULL,WEIGHT CHAR(10) NOT NULL,)3.创建表JCREATE TABLE J(SNO CHAR(3) PRIMARY KEY NOT NULL,SNAME V ARCHAR(20) NOT NULL,STATUS INT NOT NULL,CITY CHAR(10) NOT NULL,)4.创建表SPJCREATE TABLE SPJ(SNO CHAR(3) NOT NULL,PNO CHAR(3) NOT NULL,JNO CHAR(3) NOT NULL,QTY CHAR(10) NOT NULL,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 语句将习题中给出的示例记录插入各表。

例如：在表spj中插入例表中的第一行，语句为：INSERT INTO SPJV ALUES('S1','P1','J1','200');其他记录插入方法与此相似。

2.利用Update更新表中的记录：1.将p表中的所有红色零件的重量增加5。

Sql语句：UPDATE PSET WEIGHT=WEIGHT+5WHERE COLOR='红';2.将spj表中所有天津供应商的QTY属性值减少10。

用子查询。

UPDATE SPJSET QTY=QTY-10WHERE SNO in(SELECT SNOFROM S2WHERE CITY='天津');3.利用Delete语句删除p表中的所有红色零件的记录。

答：DETELEFROM PWHERE COLOR='红';4.SQL语句执行后返回基本表查看更新后的结果，如果与期望不符，分析原因并记录在实验报告中。

六、思考题如何用SQL语句实现关系的三类完整性？答：三类完整性是指：实体完整性、参照完整性、与完整性。

实体完整性，是对主键的约束，是指元组在组成主键的属性上不能有空值，主键是区分实体的唯一性标识，因此不能为空。

参照完整性（引用完整性）：这是外键的约束，不允许引用不存在的元组。

外键与关系的主键相对应，外键或者取空值，或者取参照关系中的主键值。

参照完整性用于保证关系的有效性，并保证在输入、编辑或删除数据时数据库是完整的。

用户定义完整性（域完整性）：是针对某一具体关系数据库的约束条件。

它反映某一具体应用所涉及的数据必须满足的语义要求。

七、实验中出现的问题及解决法案1、当创建表的时候，用过primary key之后，该列就不能有重复的输入。

这违背了实体完整性；2、用NOT NULL限制的属性，就不能为空，必须有输入；3、Spj表中的pno、jno、sno不能出现s、p、j中没有的记录，因为有外键的约束。

Spj表必须参照s、p、j三个表创建。

八、实验总结通过这次试验，首先我熟悉了server2000的工作环境，了解了数据库的基本知识，如：分离数据库、附加数据库。

学会了手工创建表和用sql语句创建表，也学会了sql语句实现数据插入、数据更新。

实验六数据库查询一、实验目的1．熟练掌握用SQL语句实现视图操作。

2．理解触发器的含义，并初步掌握用SQL语句实现触发器的编写。

3．理解存储过程的含义，并初步掌握用SQL语句实现存储过程的编写。

二、实验环境SQL Server2000三、预习要求SQL Server2000的查询分析器和企业管理器的使用，存储过程和触发器的含义。

四、实验内容(一)完成下面的简单查询：①查询所有“天津”的供应商明细；②查询所有“红色”的14公斤以上的零件。

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

(二)完成下面的连接查询：①等值连接：求s表和j表的相同城市的等值连接。

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

③笛卡尔积：求s和p表的笛卡尔积④左连接：求j表和spj表的左连接。

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

(三)分组查询：1.求各种颜色零件的平均重量。

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

3.求各供应商供应的零件总数。

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

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

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

(四)嵌套查询：1.in连接谓词查询：①查询没有使用天津供应商供应的红色零件的工程名称。

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

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

五、实验方法和步骤参照实验五中给出的使用Microsoft SQL Server企业管理器和SQL查询分析器的方法，将实验内容中所要求的查询项目依次用SQL语句实现，并记录下执行结果。

源程序：/*查询所有“天津”的供应商明细*/select *from Swhere city='天津'/*查询所有“红色”的14公斤以上的零件*/select pnamefrom Pwhere color='红' and weight>=14/*查询工程名称中含有“厂”字的工程明细*/select *from Jwhere jname like '%厂'/*等值连接：求S表和J表的相同城市的等值连接*/select *from S,Jwhere J.city=S.city/*自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序*/select sname,pname,jnamefrom S,J,P,SPJwhere S.sno=SPJ.sno and J.jno=SPJ.jno and P.pno=SPJ.pnoorder by sname,jname,pname/*笛卡尔积：求S表和J表的笛卡尔积*/select *from S,P/*左连接：求J表和SPJ表的左连接*/ select J.jno,jname,city,sno,pno,qty from J,SPJwhere J.jno*=SPJ.jno;/*右连接：求J表和SPJ表的右连接*/ select J.jno,jname,city,sno,pno,qty from J,SPJwhere J.jno=*SPJ.jno;/*求各种零件的平均重量*/select color,avg(weight)from Pgroup by color;/*求北京供应商和天津供应商的总个数*/ select city,count(sno)总个数from Sgroup by cityhaving city='北京' or city='天津';/*求各供应商供应的零件总数*/select sname,sum(qty)总数from S,SPJwhere S.sno=SPJ.snogroup by sname/*求各供应商供应给各工程的零件总数*/ select sname,jname,sum(qty)总数from S,J,SPJwhere S.sno=SPJ.sno and J.jno=SPJ.jno group by sname,jname/*求供应给各工程的零件p1总数*/select jname,sum(qty)总数from S,J,SPJwhere S.sno=SPJ.sno and J.jno=SPJ.jno and pno='p1' group by jname/*求使用了100个以上P1零件的工程名称*/select jnamefrom J,SPJwhere J.jno=SPJ.jno and pno='p1'group by jnamehaving sum(qty)>=100;/*求各工程使用的各城市供应的零件总数*/select jname,S.city,sum(qty)总数from S,J,SPJwhere S.sno=SPJ.sno and J.jno=SPJ.jnogroup by jname,S.city/*查询没有使用天津供应商供应的红色零件的工程名称*/ select jnamefrom Jwhere jno in(select jnofrom SPJwhere pno in(select pnofrom Pwhere color!='红')and sno in(select snofrom Swhere city!='天津'))/*查询供应了1000个以上零件的供应厂商名*/select snamefrom Swhere sno in(select snofrom SPJgroup by snohaving sum(qty)>=1000)/*比较运算符：求重量大于所有零件平均重量的零件名称*/select pnamefrom Pwhere weight >=(select avg(weight)from P)六、思考题如何通过系统的设置实现类似的功能，而不需触发器？答：通过设置外键约束，并设置级联删除，可以不用触发器。