数据库系统概论数据操纵语言DML实验报告共24页.docx

数据库系统概论数据操纵语言DML实验报告共24页.docx

《数据库系统概论数据操纵语言DML实验报告共24页.docx》由会员分享，可在线阅读，更多相关《数据库系统概论数据操纵语言DML实验报告共24页.docx（28页珍藏版）》请在冰豆网上搜索。

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

数据库实验报告

题目（tímù）：

数据库操纵（cāozòng）语言DML姓名（xìngmíng）：

李军毅日期：

2016-4-10

实验目的

1.熟悉SQL语言中DML的功能。

2.初步了解如何进行查询优化。

3.初步了解SQL语句的查询计划。

实验平台

1.OS：

WindowsXP/7

2.DBMS：

SQLServer2008

实验用时

两次上机

实验内容

一、熟悉SQL语句的DML功能

1.创建数据库，建立第二章习题5中的4个表，并插入数据。

2.完成SQL查询。

3.VIEW。

4.UPDATE和DELETE。

二、初步了解查询优化

1、配置SQLServer，导入dbcourse数据库

2、在原始papers表中查询title属性

3、在papers表中的title属性列上建立unique索引

4、在papers表中查询（cháxún）文章的作者在authors表中存在且文章发表的期刊在journals表中存在的文章的org

5、提交两个对papers表的查询，一个查询输出（shūchū）所有的列，另一个查询只输出title列。

三、初步（chūbù）了解查询处理流程

实验中出现的问题

预备知识

一、数据操纵语言

数据库数据操纵语言DML（Data ManipulationLanguage），它使用户能够查询数据库以及操作已有数据库中的数据的计算机语言。

DML具体包含查询，删除，更新，插入四种操作。

二、SQL

SQL（StructuredQueryLanguage）结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL语言包括三种主要程序设计语言类别的语句：

数据定义语言（DDL），数据操作语言（DML）及数据控制语言（DCL）。

三、SQLServer临时表

SQLServer包含一个自带的系统数据库——tempdb。

它用来存放用户创建的临时对象。

临时对象分为（fēnwéi）全局临时对象和区域临时对象。

全局临时对象所有用户可见，区域临时对象仅当前连接对象可见。

每当SQLServer重启后，tempdb数据库会被重新创建。

临时（línshí）表的创建：

临时表的创建与表的创建方法一样，只不过本地临时表的名称前面有一个编号符 （#table_name），而全局（quánjú）临时表的名称前面有两个编号符 （##table_name）。

如：

CREATE TABLE #Temp （cola INT PRIMARY KEY）

将创建一个名为Temp的临时表。

临时表的其他数据操作和表的一致。

实验内容

一、熟悉SQL语句的DML功能

1.创建数据库，建立第二章习题5中的4个表，并插入数据。

如下图所示：

createtableS

（Snovarchar（5）primarykey,

Snamevarchar（10）null,

Statusintnull,

Cityvarchar（10）null

）;

createtableP

（Pnovarchar（5）primarykey,

Pnamevarchar（10）null,

Colorvarchar（10）null,

Weightintnull

）;

createtableJ

（Jnovarchar（5）primarykey,

Jnamevarchar（10）null,

Cityvarchar（10）null

）;

createtableSPJ

（Snovarchar（5）notnull,

Pnovarchar（5）notnull,

Jnovarchar（5）notnull,

Qtyintnull,

primarykey（Sno,Pno,Jno）,

foreignkey（Sno）referencesS（Sno）,

foreignkey（Pno）referencesP（Pno）,

foreignkey（Jno）referencesJ（Jno）,

）;

插入（chārù）数据：

insertintoS

values（'S1','精益','20','天津（tiānjīn）'）,（'S2','盛锡','10','北京（běijīnɡ）'）,

（'S3','东方红','30','北京'）,（'S4','丰泰盛','20','天津'）,（'S5','为民','30','上海'）;

insertintoP

values（'P1','螺母','红','12'）,（'P2','，螺栓','绿','17'）,

（'P3','螺丝刀','蓝','14'）,（'P4','螺丝刀','红','14'）,

（'P5','凸轮','蓝','40'）,（'P6','齿轮','红','30'）;

insertintoJ

values（'J1','三建（sānjiàn）','北京（běijīnɡ）'）,（'J2','一汽','长春（chánɡchūn）'）,

（'J3','弹簧厂','天津'）,（'J4','造船厂','天津'）,

（'J5','机车厂','唐山'）,（'J6','无线电厂','常州'）,（'J7','半导体厂','南京'）;

insertintoSPJ

values（'S1','P1','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.完成SQL查询。

1）求供应工程J1零件P1的供应商号码SNO。

selectSno

fromSPJ

whereJno='J1'andPno='P1';

2）求供应工程J1零件为红色的供应商号码（hàomǎ）SNO，并按其供应数量之和降序排列显示。

selectSno

fromSPJ,P

whereJno='J1'andSPJ.Pno=P.PnoandP.Color='红'

orderbyQtyDESC;

3）求没有使用天津供应商生产的红色（hóngsè）零件的工程号。

selectdistinctJno

fromSPJ

whereJnonotin（

selectJno

fromSPJ,S,P

whereSPJ.Sno=S.SnoandS.City='天津（tiānjīn）'andSPJ.Pno=P.PnoandP.Color='红'）;

4）求至少（zhìshǎo）用了供应商S1所供应的全部零件的工程号。

selectdistinctJno

fromSPJ

wherePnoin（

selectPno

fromSPJ

whereSno='S1'）;

5）求对所有工程都提供了同一零件（línɡjiàn）的供应商号码。

selectdistinctSno

fromSPJS1

wherenotexists（

select*

fromJ

wherenotexists（

select*

fromSPJS2

whereS1.Sno=S2.SnoandS1.Pno=S2.PnoandJ.Jno=S2.Jno）

6）求满足下面要求的供应商号码，该供应商供应给某个工程零件P1的数量（shùliàng）大于等于这个工程被供应的零件P1的平均数量。

selectx_sno

from（selectSno,AVG（Qty）

fromSPJ

wherePno='P1'

groupbySno）asX（x_sno,x_avg）,

（selectSUM（Qty）

fromSPJ

wherePno='P1'）asY（y_sum）

whereY.y_sum>=X.x_avg;

7）求至少有一个供应商或工程（gōngchéng）所在的城市。

selectCity

fromS

union

selectCity

fromJ

3.VIEW。

1）为‘三建’工程项目建立一个供应情况视图（shìtú），包括供应商代码SNO、零件代码PNO、供应数量QTY。

createviewpro_view

as

selectSno,Pno,Qty

fromSPJ,J

whereSPJ.Jno=J.JnoandJ.Jname='三建（sānjiàn）';

2）针对（zhēnduì）上述视图，找出三建工程项目使用的各种零件代码及其数量；

selectPno,sum（Qty）sum

frompro_view

groupbyPno;

3）找出供应（gōngyìng）商S1的供应情况。

selectSno,Pno,Qty

frompro_view

whereSno='S1';

4.UPDATE和DELETE。

1）将所有工程中红色零件的使用（shǐyòng）数量加100。

updateSPJ

setQty=Qty+100

wherePnoin（

selectPno

fromP

whereP.Color='红'

）;

2）删除工程J1和J2都使用的零件及相关记录。

（提示（tíshì）：

通过建立临时表的方式删除，临时表的创建方式参见“预备知识”部分）

首先（shǒuxiān）建立临时表：

CREATETABLE#SPJ_Temp（colaINTPRIMARYKEY）

CREATETABLE#SPJ_Temp（T_Pnovarchar

（2）PRIMARYKEY）

然后往临时表中插入工程J1和J2都使用的零件及相关（xiāngguān）记录：

insert

into#SPJ_Temp（T_Pno）

selectdistinctPno

fromSPJ

whereJno='J1'orJno='J2';

最后根据（gēnjù）临时表中的数据删除SPJ和P表中的记录：

delete

fromSPJ

wherePnoin（

selectPno

from#SPJ_Temp）;

delete

fromP

wherePnoin（

selectPno

from#SPJ_Temp）;

二、初步（chūbù）了解查询优化

1、配置（pèizhì）SQLServer，导入dbcourse数据库

2、在原始（yuánshǐ）papers表中查询title属性

语句1：

在papers表中查询所有列的title，并按title排序输出

SQL语句：

selecttitlefrompapersorderbytitle;

用时为2分41秒

语句（yǔjù）2：

在papers表中查询title大于kkk字符串的title

SQL语句（yǔjù）：

selecttitlefrompaperswheretitle>'kkk';

用时为50秒

3、在papers表中的title属性列上建立unique索引，再次执行步骤二中的查询（cháxún）语句，并记录和查看这次系统执行的时间变化。

createuniqueindexindex_nameonpapers（title）;

语句（yǔjù）1：

在papers表中查询所有列的title，并按title排序输出

SQL语句（yǔjù）：

selecttitlefrompapersorderbytitle;

用时为1分29秒

语句（yǔjù）2：

在papers表中查询title大于kkk字符串的title

SQL语句：

selecttitlefrompaperswheretitle>'kkk';

用时为37秒

4、在papers表中查询（cháxún）文章的作者在authors表中存在且文章发表的期刊在journals表中存在的文章的org。

SQL语句（yǔjù）1：

selectdistinctpapers.orgfrompaperswherepapers.authorin（selectauthors.namefromauthors）andpapers.journalin（selectjournals.namefromjournals）

执行（zhíxíng）计划：

SQL语句（yǔjù）2：

selectdistinctpapers.orgfrompapers,journals,authorswherepapers.author=authors.nameandpapers.journal=journals.name

执行（zhíxíng）计划：

两次查询执行（zhíxíng）计划的不同：

语句2每执行一次select操作，从from语句进入（jìnrù）表时都要进行一次位图步骤，语句1则没有。

innerjoin只返回两个（liǎnɡɡè）表中联结字段相等的元组，而semijoin不仅返回两个表中联结字段字段相等的元组，还返回其中一个表剩余未匹配上的元组。

5、提交两个对papers表的查询，一个查询输出所有的列，另一个查询只输出title列。

提交查询：

selecttitlefrompapers;用时1分33秒

再次提交（tíjiāo）查询：

select*frompapers;用时5分58秒

两次查询用时不同（bùtónɡ）原因：

因为title列上建有unique索引，title如果为空，那么便不会去匹配此元组，而查询所有（suǒyǒu）列需要遍历整个表。

三、初步了解（liǎojiě）查询处理流程

使用（shǐyòng）提供的dbcourse数据库，进行数据查询操作，并通过查看语句的执行计划，分析各查询子句的处理顺序。

用伪码的形式叙述出这一处理过程。

SQL语句（yǔjù）1：

在papers表中查询title大于kkk字符串的title

selecttitlefrompaperswheretitle>'kkk';

处理流程为：

扫描title列的索引index_title--->>按照where条件挑选出需要的字段title（即执行select子句）

SQL语句2：

在papers表中查询文章的作者在authors表中存在且文章发表的期刊在journals表中存在的文章的org

selectdistinctpapers.orgfrompapers,journals,authorswherepapers.author=authors.nameandpapers.journal=journals.name

处理流程为：

扫描（sǎomiáo）papers表--->>扫描journals表-->>扫描authors表--->>按照where条件挑选出联结字段相等的元组

实验中出现（chūxiàn）的问题

1、不知道怎么建临时表，也不知道临时表的作用（zuòyòng），后来意识到临时表使用时前面都有#。

2、在做“供应商供应给某个工程零件P1的数量大于等于（děngyú）这个工程被供应的零件P1的平均数量”题时始终没有写对，后来通过查询书，可以使用（shǐyòng）基于子表的查询。

内容总结

（1）数据库实验报告

题目：

数据库操纵语言DML姓名：

李军毅日期：

2016-4-10

实验目的

熟悉SQL语言中DML的功能

（2）如下图所示：

createtableS

（Snovarchar（5）primarykey,

Snamevarchar（10）null,

Statusintnull,

Cityvarchar（10）null

）

（3）createtableJ

（Jnovarchar（5）primarykey,

Jnamevarchar（10）null,

Cityvarchar（10）null

）