实验三SQL语句的使用(一)

1、1 实验三：SQL语句的使用（一）1.1 实验背景SQL（Structure Query Language，结构化查询语言）是目前在关系数据库领域中真正的标准语言。虽然它叫做查询语言，但实际上不仅仅只能用来执行查询，还能用来执行数据的更新处理。教材的第一章谈到的数据库管理系统（DBMS）具有的所有功能SQL都能帮助完成，而且对于数据库管理员（DBA）来说，他管理数据库用到的一定是SQL。如果按照DBMS的功能来分类SQL的话，可以参考下表：表 1 DBMS功能与SQL分类DBMS功能SQL分类SQL代表语句数据定义功能DDL（Data Definition Language）Create（对象

2、创建）Drop（对象删除）Alter（对象修改）数据操作功能DML（Data Manipulation Language）Select（数据查询）Insert（数据新增）Update（数据修改）Delete（数据删除）数据库运行、维护功能DCL（Data Control Language）Grant（授予权限）Revoke（收回权限）上表表述了最常见的SQL，实际上SQL非常丰富，而且随着时代的发展，SQL也不断地发展，而且SQL被国际标准化组织（ISO）制定成了国际标准，并且随着SQL新增要求，不断推出更新版本的国际标准。目前最主要的是1999年推出的SQL-99标准。下表是SQL的几个标准

3、的版本。表 2 DBMS功能与SQL分类SQL标准发表年月发表组织备注SQL-86：X.135-19861986.10ANSISQL-89：X.3.168-19891989.4ANSISQL-89：X.3.135-19891989.10ANSISQL-92：ISO/IEC 9075:19921992.8ISO/ANSI简称SQL-2SQL-99：ISO/IEC 9075:19991999ISO/ANSI简称SQL-3从上表可以看出SQL标准是在不断更新当中的，但是自1996年后标准的关注度并不再提及得很重要的地位（实际上1999年后，SQL标准仍然在不断推出）。其中的原因有很多。通过下面两篇摘

4、自网上的文章可以看出SQL的发展现状。文章：“SQL标准风光不再”（CSDN，作者：BUILDER.COM，发表于2002年 ）对很多公司来说，在选择关系数据库管理系统（RDMS）时SQL标准的兼容性无疑是一重要的考虑因素，那么，为什么这一标准对数据库产品的开发商而言反倒成了可选的考虑标准呢？其实专有技术在带来产品优势的同时往往还伴随着产品质量的下降或者数据移植性的丧失，对厂商和客户的关系而言，这些后果总是有得有失。下面就让我们探讨下ANSI SQL标准支持力度下降的原因以及标准自身的有效性。 为什么要建立标准？ 为什么说技术标准相当重要？原因很多，最首要的一条就是产品的用户在购买产品之前能保

5、证产品能达到它所宣称的功能。如果你能和某家厂商形成伙伴关系，保证你的数据管理系统一切正常，难道这样不好吗？再比如，你在选择RDMS的时候首先考虑的是其稳定、资源的有效利用率还是因为它支持竞争产品并不拥有的视图和事务功能呢？ 有一个标准千般好，至少，第3方厂商能由此创建出可应用于整个市场而非某一特定平台的工具和实用程序。同样的，单一成品在通过标准认证的情况下更可以增加其可用资源的比重。产品认证有助于保证产品具有合格的质量和功能特性，反之，那些没有经过标准认证的“假冒品”到底能在多大程度上遵守标准规范就要大打折扣了！ 针对产品的标准认证程序具有很多优点。然而，不幸的是，恰恰是SQL不具有能满足这一

6、目标的标准控制体。 为什么标准没人听？ 将近10年来，美国标准和技术研究所（NIST，该组织接受美国商务部的管理）一直负责提供RDMS的标准测试。6年前，NIST停止执行ANSI/SQL标准的强制检测，他们认为，由于厂商缺乏标准兼容性的义务而令标准测试流于形式，标准本身在很大程度上被严重忽略了。 今天，大多数数据库开发商都宣称其产品遵守SQL-92标准，该标准于1992年发布。然而，当前的标准实际上已经发展到了SQL-99。NIST放弃对标准的控制原因在于： (1)数据库标准领域的范围太大。(2)由于财政上的原因，只有业内财大气粗的顶级RDMS开发商才有能力制定和实现标准。这两大因素在极大程度

7、上促使具体的数据库产品背离了相关的标准，从而导致数据管理领域的技术混乱，而这却正是ANSI标准所试图防范的结果。 哎，这样一来还谈什么标准！ 产品的一致性造就平庸 各类数据库开发商仅在不同程度上遵守ANSI/SQL标准，其目的无非是令他们的产品能具有更具竞争性的优势，同时满足客户的需求。不过，这些产品好歹还能说是遵守ANSI/SQL规范的。为了实现产品的兼容性和标准性，数据库产品基本上必须满足以下3个条件： 生产商必须创建可以设置的标志，通过它在用到非标准查询语句或者功能时可以产生有关的警告。 生产商必须明确地指出违背ANSI/SQL标准的函数属于产品本身的“扩展（extensions）”，并

8、按照第1条所规定的那样提供标志特性。 数据库必须支持“Entry Level SQL92”，该标准版本由以下要素组成：表、列、数据类型、键索引、schema操作、行和表约束、视图、基本关系操作以及编程语言绑定等。 举个例子，我从Oracle产品下特有的SQL，也就是PL/SQL总结出以下并不遵守规范的技术扩展。 PL/SQL标志扩展： 数组接口包括FOR子句 SQLCA、ORACA和SQLDA数据结构 动态SQL包括DESCRIBE语句 嵌入式PL/SQL代码块 自动数据类型转换 DATE、COMP-3、NUMBER、RAW、LONG RAW、VARRAW、ROWID和VARCHAR数据类型

9、ORACLE OPTION语句用于指定运行时选项 用于用户退出的EXEC IAF和EXEC TOOLS语句 CONNECT语句 TYPE和VAR数据类型等价语句 AT db_name子句 DECLARE.DATABASE, .STATEMENT和 .TABLE语句 WHENEVER语句的SQLWARNING约束 WHENEVER语句的DO 和STOP行为 COMMIT语句中的COMMENT和FORCE TRANSACTION子句 ROLLBACK语句的FORCE TRANSACTION和TO SAVEPOINT子句 COMMIT和ROLLBACK语句的RELEASE参数 INTO子句中主机变量

10、和WHENEVER.DO标签的Optional冒号前缀 我本来还想给大家提出一个针对MS SQL Server的查询语言Transact-SQL的类似兼容性列表，但我没有找到有关的ANSI/SQL标准兼容性说明文档。如果你想知道你在使用SQL Server时所用到的函数是否与标准相一致，你不妨利用 set fipsflagger选项显示警告信息。 我举出以上的例子是为了说明以下事实：虽然数据库开发商宣称自己的产品仍然处于ANSI RDMS标准的参数控制范围之内，但是，就算你达到了应用程序编程的技术水准，在没有综合考虑的情况下你也无法在不同类型的数据库产品之间顺利迁移。 标准失去了应有的意义。没

11、有强制标准的执行部门，因而也不存在真正的标准产品。所谓的“遵守ANSI/SQL标准”不过是在市场销售时的一个漂亮口号而已。对数据库的应用或者应用程序的开发而言并没有太大的意义。 ANSI SQL建议 自1996年NIST的数据管理标准程序被放弃之后，现在已经没有什么认证或者其他机制能检验数据库厂商是否遵守了ANSI SQL标准。市场上的公众要求和主流产品都一个劲地只为保护客户投资的利益着想，生产商宁肯向客户提供更好的产品特性而不刻意追求产品的标准性，其情势宛如标准荡然无存。 我们只有两个选择：要不推出某种形式的标准强制执行系统，要不在某些违背标准的特性成为事实上的标准之前把SQL标准降格为某种

12、产品建议。文章：“99后SQL标准”（DBA Notes，作者：FENG，发表于2009）SQL:2003这个版本针对 SQL:99 的一些问题进行了改进，支持 XML，支持 Window 函数、Merge 语句等。对于 Merge 语句，很多从事数据仓库的朋友耳熟能详了。这个东西也是先有了事实标准然后纳入规范的。且说 SQL:99 发布后，各大数据库厂商纷纷宣布新的版本中对该标准的支持，这是他们一贯的姿态。SQL:2006继续增强 XML方面的特性。这个版本发布后，几乎没什么动静。增强 XML 对数据处理的能力。实际上，至少在现在应用中发现 XML 多少让大家都高估了它，也或许背后有商业力量

13、驱动吧。有几家公司不是要凭借 XML DB 超越对手来着? SQL:2008去年发布的。几乎没看到有技术圈子的人讨论这个事情。这个版本其实还是和 XML 较劲。从SQL:99 到 SQL:2008，可以看到标准修订的周期越来越短，多少也反映了对技术的需求变化之快。从更加详细的角度对SQL分类，实际上可以分为七类：表 3 SQL分类SQL分类SQL语句（SQL-99标准）模式语句Create Schema、Drop Schema、Create Domain、Alter Domain、Drop Domain、Create Table、Alter Table、Drop Table、Create Vi

14、ew、Drop View、Create Index、Drop Index、Create Assertion、Drop Assertion、Create Character Set、Drop Character Set、Create Collation、Drop Collation、Create Transaction、Drop Transaction、Create Trigger、Drop Trigger、Create Type、Drop Type、Create Ordering、Drop Ordering、Create Transform、Drop Transform、Create Proc

15、edure、Alter Procedure、Drop Procedure、Create Function、Alter Function、Drop Function、Create Rule、Alter Rule、Drop Rule、Grant、Revoke数据语句Select、Insert、Update、Delete、Declare Table、Declare Cursor、Open、Close、Fetch、Free Locator、Hold Locator事务语句Start Transaction、Commit、Rollback、Savepoint控制语句If、While、Call、Retur

16、n连接语句Connect、Disconnect会话语句Set Session、Set Time Zone诊断语句Get Diagnostics值得注意的是上表中的SQL-99语句会与你实际使用到的SQL有些区别，这主要由于不同DBMS产品存在与标准的差异，而且标准在更新当中。因此，使用的时候还需要参考具体DBMS的参考手册。1.2 实验目标能使用SQL语句解决实际的数据查询、数据更新（数据操纵）、数据控制。1.3 实验任务序号任务描述预期成果注意1在SQLServer中创建数据库表对象启动SQLServer，创建一个新的数据库SPJ。在此数据库中使用表结构设计器创建：S，P，J，SPJ四个表。

17、具体结构参见教材第二章习题5。2在表对象中输入数据具体数据参见教材第二章习题5。3求供应工程J1零件的供应商号码SNO使用SQL完成。4求供应工程J1零件P1的供应商号码SNO使用SQL完成。5求供应工程J1零件为红色的供应商号码SNO使用SQL完成。6求没有使用天津供应生产的红色零件的工程号JNO使用SQL完成。8找出所有供应商的姓名和所在城市使用SQL完成。9找出所有零件的名称、颜色、重量使用SQL完成。10找出使用供应商S1所供应零件的工程号码使用SQL完成。11找出工程项目J2使用的各种零件的名称及其数量使用SQL完成。12找出上海厂商供应得所有零件号码使用SQL完成。13找出使用上海

18、产的零件的工程名称使用SQL完成。14找出没有使用天津产的零件的工程号码使用SQL完成。15把全部红色零件的颜色改称蓝色使用SQL完成。16由S5供给J4的零件P6改为由S3供应，请做出必要的修改使用SQL完成。17从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录。使用SQL完成。18请将（S2, J6, P4, 200）插入供应情况关系中使用SQL完成。19为P表增加一列“Price”表示单价使用SQL完成。20找出使用了东方红供应商供应零件的所有工程、消耗的零件及价格使用SQL完成。21为S的SNO构建索引使用SQL完成。22为SPJ创建主码使用SQL完成。2 实验报告2.1 实验人信息姓名：_学号：_实验时间：_2.2 实验结果1. 写出创建S，P，J，SPJ四张表的SQL语句：2. 写出求供应工程J1零件的供应商号码SNO的SQL语句：3. 写出求供应工程J1零件P1的供应商号码SNO的SQL语句：4. 写出求供应工程J1零件为红色的供应商号码SNO的SQL语句：5. 写出求没有使用天津供应生产的红色零件的工程号JNO的SQL语句：6. 写出找出所有供应商的姓名和所在城市的SQL语句