实验目的
1. 熟悉SQL语言中DML的功能。 2. 初步了解如何进行查询优化。 3. 初步了解SQL语句的查询计划。
实验平台
1. OS：
Windows XP/7 2. DBMS：
SQL Server 2008
实验用时
两次上机（4月21号之前提交实验报告）
预备知识
一、数据操纵语言
数据库数据操纵语言DML (Data Manipulation Language），它使用户能够查询数据库以及操作已有数据库中的数据的计算机语言。DML具体包含查询，删除，更新，插入四种操作。
二、SQL
SQL（Structured Query Language)结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL语言包括三种主要程序设计语言类别的语句：数据定义语言（DDL），数据操作语言（DML）及数据控制语言（DCL）。
三、SQL Server 临时表
SQL Server包含一个自带的系统数据库——tempdb。它用来存放用户创建的临时对象。临时对象分为全局临时对象和区域临时对象。全局临时对象所有用户可见，区域临时对象仅当前连接对象可见。每当SQL Server重启后，tempdb数据库会被重新创建。
临时表的创建：临时表的创建与表的创建方法一样，只不过本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (##table_name)。如：
CREATE TABLE #Temp (cola INT PRIMARY KEY) 将创建一个名为Temp的临时表。
临时表的其他数据操作和表的一致。
实验活动
注：请各位同学用SQL语句完成今天实验的所有步骤（系统视图的查看除外），并在实验报告中记录每条语句。每个操作完成之后，观察相关系统表的改变并分析原因。
一、 熟悉SQL语句的DML功能
步骤一：创建数据库，建立第二章习题5中的4个表，并插入数据。 插入单条元组的sql语法结构： INSERT INTO <表名> VALUES (<属性值1> [, <属性值2>…] ) eg: INSERT INTO UnitMeasure
VALUES (N'FT', N'Feet', '20080414');
插入多条元组的sql语法结构： INSERT INTO <表名> VALUES (<属性值1> [, <属性值2>…] )[,(<元组2>)…] eg: INSERT INTO UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923');
S表(供应商表) 字段名 数据类型 长度 可否为空 备注 SNO varchar 5 Not Null 供应商代码，主键 SNAME varchar 10 Null 供应商姓名 STATUS Int Null 供应商状态 CITY Varchar 10 Null 供应商所在城市 注：STATUS为系统关键字，可换用其他表示或[ ]加以区分。
P表(零件表) 字段名 数据类型 长度 可否为空 备注 PNO varchar 5 Not Null 零件代码，主键 PNAME varchar 10 Null 零件名 COLOR Varchar 10 Null 颜色 WEIGHT Int Null 重量 注：WEIGHT为系统关键字，可换用其他表示或[ ]加以区分。
J表(工程项目表) 字段名 数据类型 长度 可否为空 备注 JNO varchar 5 Not Null 工程项目代码，主键 JNAME varchar 10 Null 工程项目名 CITY Varchar 10 Null 工程项目所在城市
SPJ表(供应情况表)
字段名 数据类型 长度 可否为空 备注 SNO varchar 5 Not Null 供应商代码 PNO varchar 5 Not Null 零件代码 JNO Varchar 5 Not Null 工程项目代码 QTY Int Null 供应数量 注：指定(SNO，PNO，JNO)为PRIMARIRY KEY。并为表SPJ的属性列SNO、PNO、JNO分别添加外键约束，参照关系如下表所示：
参照列 SPJ.SNO SPJ.PNO SPJ.JNO
SNO S1 S2 S3 S4 S5
PNO P1 P2 P3 P4 P5 P6
JNO J1 J2 J3 J4 J5 J6 J7
SNO S1 S1 S1 S1 S2 S2 S2 供应情况表 SPJ PNO JNO P1 J1 P1 J3 P1 J4 P2 J2 P3 J1 P3 J2 P3 J4 QTY 200 100 700 100 400 200 500 备注 工程项目表 J JNAME CITY 三建 北京 一汽 长春 弹簧厂 天津 造船厂 天津 机车厂 唐山 无线电厂 常州 半导体厂 南京 备注 PNAME 螺母 螺栓 螺丝刀 螺丝刀 凸轮 齿轮 零件表 P COLOR 红 绿 蓝 红 蓝 红 WEIGHT 12 17 14 14 40 30 备注 供应商表 S SNAME STATUS 20 精益 10 盛锡 30 东方红 20 丰泰盛 30 为民 CITY 天津 北京 北京 天津 上海 备注 被参照列 S.SNO P.PNO J.JNO S2 S2 S2 S3 S3 S4 S4 S4 S5 S5 S5 S5
P3 P5 P5 P1 P3 P5 P6 P6 P2 P3 P6 P6 J5 J1 J2 J1 J1 J1 J3 J4 J4 J1 J2 J4 500 400 100 200 200 100 300 200 100 200 200 500 步骤二：完成以下SQL查询（注：结果注意去重） Select语句的sql语法结构：
SELECT [ ALL | DISTINCT ] <目标列表达式> [, <目标列表达式>]… FROM <表名或视图名> [, <表名或视图名>]… [WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC | DESC]];
1. 求供应工程J1零件P1的供应商号码SNO。 2. 求供应工程J1零件为红色的供应商号码SNO，并按其供应数量之和降序排列显示。 3. 求没有使用天津供应商生产的红色零件的工程号。 4. 求至少用了供应商S1所供应的全部零件的工程号。 5. 求对所有工程都提供了同一零件的供应商号码。
6. 求满足下面要求的供应商号码，该供应商供应给某个工程零件P1的数量大于等于
这个工程被供应的零件P1的平均数量。 7. 求至少有一个供应商或工程所在的城市。 步骤三：VIEW。
创建视图语句的sql语法结构：
CREATE VIEW <视图名> [(<列名> [,<列名>]…)] AS <子查询>
[WITH CHECK OPTION];
1. 为‘三建’工程项目建立一个供应情况视图，包括供应商代码SNO、零件代码PNO、
供应数量QTY。
2. 针对上述视图，找出三建工程项目使用的各种零件代码及其数量； 3. 找出供应商S1的供应情况。
步骤四：UPDATE和DELETE。 Update语句的sql语法结构： UPDATE <表名>
SET <列名>=<表达式> [, <列名>=<表达式>]… [WHERE <条件表达式>];
Delete语句的sql语法结构：
DELETE
FROM <表名>
[WHERE <条件表达式>];
1. 将所有工程中红色零件的使用数量加100。 2. 删除工程J1和J2都使用的零件及相关记录。（提示：通过建立临时表的方式删除，临时表的创建方式参见“预备知识”部分）
二、 初步了解查询优化
使用提供的dbcourse数据库，进行大数据量上的数据查询操作，并通过修改查询语句和在表上建立索引等机制，优化查询的效率。在实验报告中记录修改的查询语句以及修改原因、增加的索引机制以及增加原因、并加每一步优化后的查询结果截图说明。 步骤一： 配置SQL Server，导入dbcourse数据库（注：若dbcourse数据库已存在，这一步可跳过） 在SSMS界面对象资源管理器中，右键选择数据库，在属性中配置数据库的最大服务器内存为2G。 附件中的dbcourse.bak是数据的备份文件，请根据之前练习过的还原数据库的方法从这个备份文件中还原数据库，之后在这个数据库上进行操作。在还原数据库时，选择有足够大空间的磁盘用来还原数据库、存放数据库文件。
数据库表的说明如下：
Author表 字段名 数据类型 长度 可否为空 备注 Id Int Not Null Id Name Varchar 30 Null 姓名 Sex Varchar 4 Null 性别 Age Int Null 年龄 Journal表 字段名 数据类型 长度 可否为空 备注 Id Int Not Null Id Name Varchar 100 Null 期刊名称 Addr Varchar 30 Null 地址 Class Varchar 4 Null 级别
Paper表 字段名 数据类型 长度 可否为空 备注 Id Int Not Null Id Title Varchar 500 Null 文章题目 author Varchar 60 Null 作者名 journal Varchar 100 Null 发表期刊 Year Varchar 4 Null 发表年份 keyword Varchar 500 Null 文章关键字 Org Varchar 300 Null 组织 步骤二： 在原始papers表中查询title属性 语句1：在papers表中查询所有列的title，并按title排序输出
SQL语句：
select title from papers order by title;
语句2：在papers表中查询title大于kkk字符串的title SQL语句： select title from papers where title > 'kkk'; 观察并记录这两个SQL语句的执行时间。
步骤三：在papers表中的title属性列上建立unique索引 建立索引：
create unique index index_name on papers(title);
再次执行步骤二中的查询语句，并记录和查看这次系统执行的时间变化。
步骤四：在papers表中查询文章的作者在authors表中存在且文章发表的期刊在journals表中存在的文章的org：
SQL语句1：
select distinct papers.org from papers where papers.author in (select authors.name from authors) and papers.journal in (select journals.name from journals)
SQL语句2：
select distinct papers.org from papers, journals, authors where papers.author = authors.name and papers.journal = journals.name
在SSMS中查看这两个查询语句的执行计划，分析两个查询计划的不同，说明semi join和inner join的区别。
步骤五：提交两个对papers表的查询，一个查询输出所有的列，另一个查询只输出title列。
提交查询：
select title from papers;
记录查询所需时间，再次提交查询：
select * from papers;
记录查询时间。猜测响应时间不同的原因。
三、 初步了解查询处理流程
使用提供的dbcourse数据库，进行数据查询操作，并通过查看语句的执行计划，分析各查询子句的处理顺序。用伪码的形式叙述出这一处理过程。 eg：select title from papers;
处理流程为：扫描papers表（即执行from子句）--->>挑选出需要的字段title（即执行select子句）
注：上例简单地用文字描述了语句的处理过程，在实验报告中请尽可能详细地用伪码形式叙述下列语句的处理过程。请结合查询语句的执行计划进行分析。 SQL语句1：在papers表中查询title大于kkk字符串的title select title from papers where title > 'kkk';
SQL语句2：在papers表中查询文章的作者在authors表中存在且文章发表的期刊在journals表中存在的文章的org
select distinct papers.org from papers, journals, authors where papers.author = authors.name and papers.journal = journals.name
实验要求
1． 完成上述实验过程，在上机过程中演示；
2． 了解和使用SQL数据操纵语言进行数据库的查询、添加、删除和修改操作； 3． 了解和使用SQLServer 的临时表；
4． 尝试在dbcourse上进行大数据量上的sql查询操作，并进行可能的查询优化。 5． 通过查看SQL的物理执行计划，初步了解逻辑查询计划。 6． 记录实验过程，并对各活动（包括错误活动）作分析说明。
因篇幅问题不能全部显示，请点此查看更多更全内容