数据库系统概论——数据操纵语言dml实验报告

《数据库系统概论——数据操纵语言dml实验报告》由会员分享，可在线阅读，更多相关《数据库系统概论——数据操纵语言dml实验报告（21页珍藏版）》请在金锄头文库上搜索。

1、 数据库实验报告数据库实验报告 题目：题目： 数据库操纵语言数据库操纵语言 DML 姓名：姓名： 李军毅李军毅 日期：日期：2016-4-10实验目的实验目的1.熟悉 SQL 语言中 DML 的功能。 2.初步了解如何进行查询优化。 3.初步了解 SQL 语句的查询计划。实验平台实验平台1.OS： Windows XP/72.DBMS： SQL Server 2008实验用时实验用时两次上机实验内容实验内容一、熟悉一、熟悉 SQL 语句的语句的 DML 功能功能1. .创建数据库，建立第二章习题创建数据库，建立第二章习题 5 5 中的中的 4 4 个表，并插入数据。个表，并插入数据。 2.2.

2、完成完成 SQLSQL 查询。查询。 3.VIEW3.VIEW。 4.UPDATE4.UPDATE 和和 DELETEDELETE。二、初步了解查询优化二、初步了解查询优化1 1、配置、配置 SQLSQL ServerServer，导入，导入 dbcoursedbcourse 数据库数据库 2 2、在原始、在原始 paperspapers 表中查询表中查询 titletitle 属性属性 3 3、在、在 paperspapers 表中的表中的 titletitle 属性列上建立属性列上建立 uniqueunique 索引索引 4 4、在、在 paperspapers 表中查询文章的作者在表中查

3、询文章的作者在 authorsauthors 表中存在且文章发表的期刊在表中存在且文章发表的期刊在 journalsjournals 表中存在的文章的表中存在的文章的 orgorg 5 5、提交两个对、提交两个对 paperspapers 表的查询，一个查询输出所有的列，另一个查询只输出表的查询，一个查询输出所有的列，另一个查询只输出 titletitle 列。列。三、初步了解查询处理流程三、初步了解查询处理流程实验中出现的问题实验中出现的问题预备知识预备知识一、数据操纵语言一、数据操纵语言 数据库数据操纵语言 DML (Data Manipulation Language） ，它使用户能够查

4、询数据库以 及操作已有数据库中的数据的计算机语言。DML 具体包含查询，删除，更新，插入四种操 作。二、二、SQL SQL（Structured Query Language)结构化查询语言，是一种数据库查询和程序设计语言， 用于存取数据以及查询、更新和管理关系数据库系统。SQL 语言包括三种主要程序设计语 言类别的语句：数据定义语言（DDL） ，数据操作语言（DML）及数据控制语言（DCL） 。三、三、SQL Server 临时表临时表 SQL Server 包含一个自带的系统数据库tempdb。它用来存放用户创建的临时对象。 临时对象分为全局临时对象和区域临时对象。全局临时对象所有用户可见

5、，区域临时对象 仅当前连接对象可见。每当 SQL Server 重启后，tempdb 数据库会被重新创建。 临时表的创建：临时表的创建与表的创建方法一样，只不过本地临时表的名称前面有 一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (#table_name)。如： CREATE TABLE #Temp (cola INT PRIMARY KEY)将创建一个名为 Temp 的临时表。 临时表的其他数据操作和表的一致。实验内容实验内容一、熟悉一、熟悉 SQL 语句的语句的 DML 功能功能1. .创建数据库，建立第二章习题创建数据库，建立第二章习题 5 5 中的中的 4

6、 4 个表，并插入数据。个表，并插入数据。 如下图所示：如下图所示：create table S(Sno varchar(5) primary key,Sname varchar(10) null,Status int null,City varchar(10) null);create table P(Pno varchar(5) primary key,Pname varchar(10) null,Color varchar(10) null,Weight int null);create table J(Jno varchar(5) primary key,Jname varchar(1

7、0) null,City varchar(10) null);create table SPJ(Sno varchar(5) not null,Pno varchar(5) not null,Jno varchar(5) not null,Qty int null,primary key(Sno,Pno,Jno),foreign key (Sno) references S(Sno),foreign key (Pno) references P(Pno),foreign key (Jno) references J(Jno),);插入数据：插入数据： insert into Svalues(S

8、1,精益,20,天津),(S2,盛锡,10,北京),(S3,东方红,30,北京),(S4,丰泰盛,20,天津),(S5,为民,30,上海); insert into Pvalues(P1,螺母,红,12),(P2,，螺栓,绿,17),(P3,螺丝刀,蓝,14),(P4,螺丝刀,红,14),(P5,凸轮,蓝,40),(P6,齿轮,红,30);insert into Jvalues(J1,三建,北京),(J2,一汽,长春),(J3,弹簧厂,天津),(J4,造船厂,天津),(J5,机车厂,唐山),(J6,无线电厂,常州),(J7,半导体厂,南京);insert into SPJvalues(S1,P

9、1,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,500),(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);2.2.完成完成 SQLSQL 查询。查

10、询。 1 1）求供应工程）求供应工程 J1J1 零件零件 P1P1 的供应商号码的供应商号码 SNOSNO。 select Snofrom SPJwhere Jno=J1 and Pno=P1;2 2）求供应工程）求供应工程 J1J1 零件为红色的供应商号码零件为红色的供应商号码 SNOSNO，并按其供应数量之和降序排，并按其供应数量之和降序排 列显示。列显示。 select Snofrom SPJ,Pwhere Jno=J1 and SPJ.Pno=P.Pno and P.Color=红 order by Qty DESC;3 3）求没有使用天津供应商生产的红色零件的工程号。求没有使用天津供

11、应商生产的红色零件的工程号。 select distinct Jnofrom SPJwhere Jno not in(select Jnofrom SPJ,S,Pwhere SPJ.Sno=S.Sno and S.City=天津 and SPJ.Pno=P.Pno and P.Color=红);4 4）求至少用了供应商求至少用了供应商 S1S1 所供应的全部零件的工程号。所供应的全部零件的工程号。 select distinct Jnofrom SPJwhere Pno in(select Pnofrom SPJwhere Sno=S1);5 5）求对所有工程都提供了同一零件的供应商号码。求对

12、所有工程都提供了同一零件的供应商号码。 select distinct Snofrom SPJ S1where not exists(select *from Jwhere not exists(select *from SPJ S2where S1.Sno=S2.Sno and S1.Pno=S2.Pno and J.Jno=S2.Jno)6 6）求满足下面要求的供应商号码，该供应商供应给某个工程零件求满足下面要求的供应商号码，该供应商供应给某个工程零件 P1P1 的数量大的数量大 于等于这个工程被供应的零件于等于这个工程被供应的零件 P1P1 的平均数量。的平均数量。 select x_s

13、nofrom (select Sno,AVG(Qty)from SPJwhere Pno=P1group by Sno) as X(x_sno,x_avg),(select SUM(Qty)from SPJwhere Pno=P1) as Y(y_sum)where Y.y_sum=X.x_avg;7 7）求至少有一个供应商或工程所在的城市。求至少有一个供应商或工程所在的城市。 select Cityfrom Sunionselect Cityfrom J3.VIEW3.VIEW。 1 1）为）为三建三建工程项目建立一个供应情况视图，包括供应商代码工程项目建立一个供应情况视图，包括供应商代码

14、SNOSNO、零件代、零件代 码码 PNOPNO、供应数量、供应数量 QTYQTY。 create view pro_viewasselect Sno,Pno,Qtyfrom SPJ,Jwhere SPJ.Jno=J.Jno and J.Jname=三建;2 2）针对上述视图，找出三建工程项目使用的各种零件代码及其数量；）针对上述视图，找出三建工程项目使用的各种零件代码及其数量； select Pno,sum(Qty) sumfrom pro_viewgroup by Pno;3 3）找出供应商）找出供应商 S1S1 的供应情况。的供应情况。 select Sno,Pno,Qtyfrom pr

15、o_viewwhere Sno=S1;4.UPDATE4.UPDATE 和和 DELETEDELETE。 1 1）将所有工程中红色零件的使用数量加）将所有工程中红色零件的使用数量加 100100。 update SPJset Qty=Qty+100where Pno in (select Pnofrom Pwhere P.Color=红 );2 2）删除工程删除工程 J1J1 和和 J2J2 都使用的零件及相关记录。都使用的零件及相关记录。 （提示：通过建立临时表的方（提示：通过建立临时表的方 式删除，临时表的创建方式参见式删除，临时表的创建方式参见“预备知识预备知识”部分）部分）首先建立临时表：首先建立临时表： CREATE TABLE #SPJ_Temp (cola INT PRIMARY KEY)CREATE TABLE #SPJ_Temp (T_Pno varchar(2) PRIMARY KEY)然后往临时表中插入工程然后往临时表中插入工程 J1J1 和和 J2J2 都使用的零件及相关记录：都使用的零件及相