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

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

2、rs表中的title属性列上建立unique索引4、在papers表中查询文章的作者在authors表中存在且文章发表的期刊在 journals表中存在的文章的org5、提交两个对papers表的查询，一个查询输出所有的列，另一个查询只输出title列。三、初步了解查询处理流程实验中出现的问题预备知识一、数据操纵语言数据库数据操纵语言DML (Data Manipulation Language），它使用户能够查询数据库以及操作已有数据库中的数据的计算机语言。DML具体包含查询，删除，更新，插入四种操作。二、SQLSQL（Structured Query Language)结构化查询

3、语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL语言包括三种主要程序设计语言类别的语句：数据定义语言（DDL），数据操作语言（DML）及数据控制语言（DCL）。三、SQL Server 临时表SQL Server包含一个自带的系统数据库tempdb。它用来存放用户创建的临时对象。临时对象分为全局临时对象和区域临时对象。全局临时对象所有用户可见，区域临时对象仅当前连接对象可见。每当SQL Server重启后，tempdb数据库会被重新创建。临时表的创建：临时表的创建与表的创建方法一样，只不过本地临时表的名称前面有一个编号符 (#table_n

4、ame)，而全局临时表的名称前面有两个编号符 (#table_name)。如：CREATE TABLE #Temp (cola INT PRIMARY KEY)将创建一个名为Temp的临时表。临时表的其他数据操作和表的一致。实验内容一、熟悉SQL语句的DML功能1.创建数据库，建立第二章习题5中的4个表，并插入数据。如下图所示： create table S(Sno varchar(5) primary key, Sname varchar(10) null, Status int null, City varchar(10

5、) 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(10) null, City varchar(10) null ); create table SPJ (Sno varchar(5) not null, Pno varchar(5) not null, Jno varchar(5) not

6、 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('S1','精益','20','天津'),('S2','盛锡','10','北京'), ('S3

7、','东方红','30','北京'),('S4','丰泰盛','20','天津'),('S5','为民','30','上海');insert into Pvalues('P1','螺母','红','12'),('P2','，螺栓','绿','17'), ('P3',

8、9;螺丝刀','蓝','14'),('P4','螺丝刀','红','14'), ('P5','凸轮','蓝','40'),('P6','齿轮','红','30'); insert into Jvalues('J1','三建','北京'),('J2','一汽','长春'),

9、 ('J3','弹簧厂','天津'),('J4','造船厂','天津'), ('J5','机车厂','唐山'),('J6','无线电厂','常州'),('J7','半导体厂','南京');insert into SPJvalues('S1','P1','J1','200'),('S1&#

10、39;,'P1','J3','100'), ('S1','P1','J4','700'),('S1','P2','J2','100'), ('S2','P3','J1','400'),('S2','P3','J2','200'), ('S2','P3','

11、J4','500'),('S2','P3','J5','500'), ('S2','P5','J1','400'),('S2','P5','J2','100'), ('S3','P1','J1','200'),('S3','P3','J1','200'),

12、 ('S4','P5','J1','100'),('S4','P6','J3','300'), ('S4','P6','J4','200'),('S5','P2','J4','100'), ('S5','P3','J1','200'),('S5','P6&

13、#39;,'J2','200'), ('S5','P6','J4','500');2.完成SQL查询。1）求供应工程J1零件P1的供应商号码SNO。select Snofrom SPJwhere Jno='J1' and Pno='P1'2）求供应工程J1零件为红色的供应商号码SNO，并按其供应数量之和降序排列显示。select Snofrom SPJ,Pwhere Jno='J1' and SPJ.Pno=P.Pno and P.Color='

14、;红'order by Qty DESC;3） 求没有使用天津供应商生产的红色零件的工程号。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） 求至少用了供应商S1所供应的全部零件的工程号。select distinct Jnofrom SPJwhere Pno in(select Pnofrom SPJwhere Sno=

15、9;S1');5） 求对所有工程都提供了同一零件的供应商号码。select distinct Snofrom SPJ S1where not exists( select * from J where not exists( select * from SPJ S2 where S1.Sno=S2.Sno and S1.Pno=S2.Pno and J.Jno=S2.Jno)6） 求满足下面要求的供应商号码，该供应商供应给某个工程零件P1的数量大于等于这个工程被供应的零件P1的平均数量。select x_snofrom (select Sno,AVG(Qty) from SPJ whe

16、re Pno='P1' group by Sno) as X(x_sno,x_avg), (select SUM(Qty) from SPJ where Pno='P1') as Y(y_sum)where Y.y_sum>=X.x_avg;7） 求至少有一个供应商或工程所在的城市。select Cityfrom Sunionselect Cityfrom J3.VIEW。1）为三建工程项目建立一个供应情况视图，包括供应商代码SNO、零件代码PNO、供应数量QTY。create view pro_viewasselect Sno,Pno,Qtyfrom S

17、PJ,Jwhere SPJ.Jno=J.Jno and J.Jname='三建'2）针对上述视图，找出三建工程项目使用的各种零件代码及其数量；select Pno,sum(Qty) sumfrom pro_viewgroup by Pno;3）找出供应商S1的供应情况。select Sno,Pno,Qtyfrom pro_viewwhere Sno='S1'4.UPDATE和DELETE。1）将所有工程中红色零件的使用数量加100。update SPJset Qty=Qty+100where Pno in (select Pnofrom Pwhere P.Col

18、or='红');2） 删除工程J1和J2都使用的零件及相关记录。（提示：通过建立临时表的方式删除，临时表的创建方式参见“预备知识”部分） 首先建立临时表：CREATE TABLE #SPJ_Temp (cola INT PRIMARY KEY)CREATE TABLE #SPJ_Temp (T_Pno varchar(2) PRIMARY KEY)然后往临时表中插入工程J1和J2都使用的零件及相关记录： insert into #SPJ_Temp(T_Pno)select distinct Pnofrom SPJwhere Jno='J1' or Jno=

19、9;J2'最后根据临时表中的数据删除SPJ和P表中的记录：deletefrom SPJwhere Pno in ( select Pno from #SPJ_Temp);deletefrom Pwhere Pno in ( select Pno from #SPJ_Temp); 二、初步了解查询优化1、配置SQL Server，导入dbcourse数据库2、在原始papers表中查询title属性 语句1：在papers表中查询所有列的title，并按title排序输出SQL语句：select title from papers order by title;用时为2分41秒语句2：在

20、papers表中查询title大于kkk字符串的titleSQL语句：select title from papers where title > 'kkk'用时为50秒3、 在papers表中的title属性列上建立unique索引，再次执行步骤二中的查询语句，并记录和查看这次系统执行的时间变化。create unique index index_name on papers(title);语句1：在papers表中查询所有列的title，并按title排序输出SQL语句：select title from papers order by title;用时为1分29秒语

21、句2：在papers表中查询title大于kkk字符串的titleSQL语句：select title from papers where title > 'kkk'用时为37秒4、 在papers表中查询文章的作者在authors表中存在且文章发表的期刊在journals表中存在的文章的org。 SQL语句1：select distinct from papers where papers.author in (select from authors) and papers.journal in (select journ

22、 from journals)执行计划：SQL语句2：select distinct from papers, journals, authors where papers.author = and papers.journal = 执行计划：两次查询执行计划的不同： 语句2每执行一次select操作，从from语句进入表时都要进行一次位图步骤，语句1则没有。inner join 只返回两个表中联结字段相等的元组，而semi join不仅返回两个表中联结字段字段相等的元组，还返回其中一个表剩余未匹配上的元组。5、提交两个对papers表的查询，一个查询输出所有的列，另一个查询只输出title列。提交查询：select title from papers; 用时1分33秒再次