SQL2.docx

SQL2.docx

《SQL2.docx》由会员分享，可在线阅读，更多相关《SQL2.docx（37页珍藏版）》请在冰豆网上搜索。

SQL2

3.1使用基本的SQL语句和子句

这部分介绍基本SQL语句和检索、修改、删除以及把数据插入表和视图中的子句。

SQL语句中常用的是SELECT、UPDATE、DELETE和INSERT四个语句。

FETCH语句也用在应用程序里来存取数据，这个语句在第四章介绍。

用SQL语句的例子将有助于开发SQL应用程序。

关于SQL语句语法和参数的详尽描述在AS/400DB2SQL参考中给出。

SQL语句可在一行或多行上写，续行的规则与主语言（写程序的语言）相一致。

注：

1.这里介绍的SQL语句可在SQL表和视图中运行，也可在数据库物理文件、逻辑文件上运行，表、视图和文件可在SQL集合里，也可在库里。

2.SQL语句里的字符串（例如WHERE或VALUES子句）是区分大小写的，也就是说，大写字母必须以大写体输入，小写字母必须以小写体输入。

WHEREADMRDEPT='a00'（无返回结果）

WHEREADMRDEPT='A00'（返回有效的部门号）

如果使用共享权分类排序，它不区分大小写母，那么比较时不区别大小写。

3.1.1INSERT语句

按以下方式之一，用INSERT语句在表或视图中加入新行：

在INSERT语句里规定加一行的各列的值。

规定加入多行的INSERT语句的块格式，详细内容请看5.1.2。

在INSERT语句里包含一个选择语句，它告诉SQL，新一行数据来自另一个表或

视图。

详细内容请看5.1.1。

注：

由于视图是由表建立的，实际上不包括数据，因此将涉及与视图相关的工作，详细内容请看5.8。

对插入的每一行，如果某列没规定缺省值，那么必须给定义为非空属性的列提供一个值。

用INSERT语句添加一行到表或视图中的格式为：

INSERTINTOtable-name

（column1,column2,...）

VALUES（value-for-column1,value-for-column2,...）

INTO子句规定列的名字，VALUES子句给在INTO子句里规定的列指定值。

在INSERT语句列表里命名的列，都必须在VALUES子句里提供一个值。

如果表中各列都有在VALUES子句里提供的值，那么列名可以省略。

如果某一列有一个缺省值，则键字DEFAULT可用做VALUES子句中的值。

列出要插入值的各列名是一个好办法，这是因为：

INSERT语句更具有描述性。

可以按列名顺序核对给定值。

有较好的数据独立性，表中定义的列序并不影响INSERT语句。

如果列定义为允许空值或有缺省值，就不需要在列名表中给名或为该列设定值，直接使用缺省值。

如果这列有缺省值，将缺省值放置到列中。

如果对列定义了DEFAULT但没有明显给出缺省值，SQL根据数据类型放置缺省值。

如果列设有定义缺省值，但定义允许空值（在列定义中没规定NOTNULL），SQL把空值放在此列中。

对数字列，缺省值为零。

对定长字符或图形列，缺省值为空格。

对变长字符或图形列，缺省值为零长度的串。

对日期、时间和时间标记列，缺省值为当前日期、时间或时间标记。

当插入记录

块时，缺省的日期/时间值在块写入时从系统中提取，这意味着对块里的每行分配相同的缺省值。

当程序试图插入表中已存在的行时，可能出现错误。

根据建立索引时所使用的可选项，多空值可以认为是重复值，也可不认为是重复值：

如果表中有一个主键、唯一键或唯一索引，不能插入行，SQL返回的SQLCODE为-803。

如果表中没有主键、唯一键或唯一索引，可以插入行且无错。

如果在运行INSERT语句时SQL发现错误，则停止数据插入。

如果规定了COMMIT（*ALL），COMMIT（*CS），COMMIT（*CHG）或COMMIT（*RR），则不会插入行。

由该语句已插入的行在用带有选择语句或块插入的INSERT语句时，删除这些行，如果规定了COMMIT（*NONE），则不删除已插入的行。

由SQL生成的表是用可重用删除记录参数为*YES来生成的，这就允许数据库管理重用表中做删除标志的行。

可用CHGPF命令修改其属性为*NO，这将导致INSERT语句总在表末添加行。

插入行的顺序不能保证就是其检索的顺序。

如果插入行无错，那么SQLCA的SQLERRD（3）字段的值为1。

注：

对成块INSERT或有选择语句的INSERT，能插入多行，插入的行数也反映在SQLERRD（3）中。

3.1.2UPDATE语句

若要改变表中的数据，可以使用UPDATE语句。

通过该语句，可以改变每行中的一列或多列值，而这列要满足WHERE子句的查询条件。

（根据有多少行满足WHERE子句指定的查询条件而定），UPDATE语句的结果是表的零或多行的一列或多列改变。

UPDATE语句格式如下：

UPDATE表名

SET列-1=值-1,

列-2=值-2,...

WHERE检索条件...

例如，假设一个雇员进行重定位，为了修改CORPDATA.EMPLOYEE表中雇员数据的几项来反映这种变化，规定如下：

UPDATECORPDATA.EMPLOYEE

SETJOB=:

PGM-CODE,

PHONENO=:

PGM-PHONE

WHEREEMPNO=:

PGM-SERIAL

使用SET子句为要修改的列设定新值。

SET子句给出要修改的列名以及要修改的值，规定值可以是：

列名。

用同一行中另一列的内容替换列的当前值。

常量。

用SET子句提供的值替换列的当前值。

空值。

使用键字NULL，以空值替换列的当前值。

当建立表时，列必须定义为可

包括空值的属性，否则出错。

主变量。

用主变量的内容替换列的当前值。

专用寄存器。

用专用寄存器值替换列的当前值；例如，USER。

表达式。

用表达式结果值替换列的当前值。

表达式可以包括列表中的任何值。

键字DEFAULT。

用列的缺省值替换列的当前值。

列必须定义过缺省值，或者允

许空值，否则出错。

下面是使用多个不同值的语句例子：

UPDATEWORKTABLE

SETCOL1='ASC',

COL2=NULL,

COL3=:

FIELD3,

COL4=CURRENTTIME,

COL5=AMT-6.00,

COL6=COL7

WHEREEMPNO=:

PGM-SERIAL

为了规定需要修改的行，使用WHERE子句：

要修改一行，使用仅选择一行的WHERE子句。

要修改多行，使用选择需要修改那些行的WHERE子句。

可以省略WHERE子句，如果这样，SQL将修改表或视图中提供值的每一行。

如果数据库管理在运行UPDATE语句时发现错误，它将停止修改并返回负的SQLCODE。

如果规定COMMIT（*ALL），COMMIT（*CS），COMMIT（*CHG）或COMMIT（*RR），那么表中无行发生改变（如果有由这个语句发生改变的行，将变回以前值）。

如果规定COMMIT（*NONE），已发生改变的行不变回以前的值。

如果数据库管理没有找到满足查询条件的行，返回的SQLCODE为+100。

注：

带有WHERE子句的UPDATE语句可修改多行。

修改的行数也反映在SQLERRD（3）中。

3.1.3DELETE语句

若要从表中取消几行，使用DELETE语句。

当删除一行时，就取消了整行。

DELETE不能取消行中的某些列。

DELETE语句的结果是删除表中的零行或多行（根据有多少行满足WHERE子句设定的查询条件而定）。

如果在DELETE语句中没有WHERE子句，SQL将取消表中的全部行。

DELETE语句格式为：

DELETEFROM表名

WHERE检索条件

例如，假设部门D11被移到另一个地方，可以按下面方法删除CORPDATA.EMPLOYEE表里WORKDEPT值为D11的每一行：

DELETEFROMCORPDATA.EMPLOYEE

WHEREWORKDEPT='D11'

WHERE子句告诉SQL要从表中删除哪些行。

SQL删除所有符合基本表查询条件的行。

可以省略WHERE子句，但最好还是包括它，因为没有WHERE子句的DELETE语句将从表或视图中删除所有行。

若要删除表定义和表内容，要用DROP语句（请参看AS/400DB2SQL参考）。

SQL在运行DELETE语句时如发现错误，将停止删除数据并返回负的SQLCODE。

如果规定COMMIT（*ALL），COMMIT（*CS），COMMIT（*CHG），COMMIT（*RR），表中设有行被删除（如果有由该语句已删除的行，将保留从前值）。

如果规定COMMIT（*NONE），则已删除的行不保留从前值。

如果SQL未找到满足查询条件的行，SQLCODE返回+100。

注：

带WHERE子句的DELETE语句可删除多行。

删除行的数目也反映在SQLERRD（3）中。

3.1.4SELECTINTO语句

可以在程序中用SELECTINTO语句检索指定行（例如，一个雇员行）。

这里给出的格式和语法都是最基本的。

实际上SELECTINTO语句可比本章中所举的例子变化更多。

SELECTINTO语句包括以下几部分：

1、需要的每列的名称。

2、包含检索数据的每个主变量的名称。

3、包含数据的表或视图的名称。

4、包含需要信息的唯一标识行的查询条件。

5、用于数据分组的每列的名称。

6、包含需要信息的唯一标识组的查询条件。

7、返回多行中某行的结果顺序。

SELECTINTO语句格式为：

SELECT列名

INTO主变量

FROM表或视图名

WHERE检索条件

GROUPBY列名

HAVING检索条件

ORDERBY列名

必须指定SELECT，INTO和FROM子句，其它子句都是可选的。

INTO子句命名主变量（程序中使用的放列值的变量）。

SELECT子句规定的第一列值放到INTO子句的第一个主变量里。

第二个值放到第二个主变量中，以此类推。

SELECTINTO语句的结果表只包括一行。

例如，CORPDATA.EMPLOYEE表中的每一行有唯一的EMPNO（雇员号）列，如果WHERE子句在EMPNO列上有一个相等的比较，这个表的SELECTINTO语句的结果确切为一行（或没有行）。

找到多行是错误的，只能返回一行。

规定ORDERBY子句，能控制在这种错误条件下返回哪行。

如果用ORDERBY子句，返回结果表的第一行。

如果需要多行的选择语句结果，使用DECLARECURSOR语句来选择行，接着用FETCH语句一次性的把列值移入主变量的一或多行。

CURSORS的使用在第四章讲述。

FROM子句规定包含数据的表（或视图）。

假定CORPDATA.DEPARTMENT表里的每个部门都有唯一的部门号，要想从此表中检索部门C01的部门名和经理号，为此，程序设置PGM-DEPT值为C01并执行下列语句：

SELECTDEPTNAME,MGRNO

INTO:

PGM-DEPTNAME,:

PGM-MGRNO

FROMCORPDATA.DEPARTMENT

WHEREDEPTNO=:

PGM-DEPT

当运行这个语句时，结果为一行：

PGM-DEPTNAME

PGM-MGRNO

INFORMATIONCENTER

000030

这些值都分配给主变量PGM-DEPTNAME和PGM-MGRNO。

如果SQL找不到满足查询条件的行，返回SQLCODE+100。

如果SQL在运行选择语句时发现错误，将返回负的SQLCODE。

如果SQL发现主变量多于结果，返回+326。

检索视图中的数据与检索表中的数据方法一样，但用视图做修改、插入或删除数据时，有一些限制。

这些限制将在5.8中讲述。

3.1.5数据检索错误

如果SQL发现检索字符或图形列过长不能装入主变量中，SQL做以下操作：

当把值分配给主变量时，将截断数据尾部。

设置SQLCA中的SQLWARN0和SQLWARN1值为‘W’。

如果提供指示器变量，则设置为截断前值的长度。

如果SQL在运行语句时发现数据映象错误，将发生下面两件事之一：

如果错误发生在SELECT列表的表达中且提供指示器变量表示错误的表达式：

—对应这个出错表达式，SQL返回-2给指示器变量。

—SQL返回那行的所有有效数据。

—SQL返回正的SQLCODE。

如果不提供指示器变量，SQL在SQLCA中将返回相应的负SQLCODE。

数据映象错误包括：

+138——完成子串功能的自变量无效。

+180——日期、时间或时间标记的串表达式语法无效。

+181——日期、时间或时间标记的串表达式值无效。

+183——日期/时间表达式结果无效。

结果日期或时间标记不在日期或时间标记的

有效范围之内。

+191——MIXED数据不符合正确的形式。

+304——数字转换错误。

（例如，溢出，下溢或零做除数错）。

+331——字符无法转换。

+420——在CAST自变量中的字符无效。

+802——数据转换或数据映象错。

对数据映象错误，SQLCA仅报告检测到的最后错误。

对应于每列错误结果，相应的指示器变量为-2。

如果全选择包括在选择列表中的DISTINCT且选择列表中的某列包括无效的数字数据，如果查询按一种分类完成，则认为数据等于空值。

如果使用已有的索引，则认为数据不为空。

依照不同情况，ORDERBY子句的数据映象错误的影响为：

当在SELECTINTO或FETCH语句里分配数据给主变量时，如果发生数据映象错误，且在ORDERBY子句里使用同一表达式时，结果记录按表达式值排序。

没有排序就认为它为空（高于所有其它值）。

这是因为在分配主变量之前计算表达式的值。

如果在选择列表计算表达式值且在ORDERBY子句里使用此表达式时发生数据映象错误，那么结果列正常排序，就好像它为空值（高于所有其它值）。

如果用分类实现ORDERBY子句，则排序的结果列好像是空值。

如果用索引实现ORDERBY子句，在下面的情况里，结果列按索引中表达式的实际值排序：

—表达式为日期列，其格式为*MDY，*DMY，*YMD，或*JUL，由于日期不在

有效范围内，将出现日期转换错误。

—表达式为字符列，字符不转换。

—表达式为十进制列，检查无效的数字值。

3.1.6SELECT子句

用SELECT子句（选择语句的第一部分），设定要检索的每列名称，例如：

SELECTEMPNO，LASTNAME，WORKDEPT

可以指定检索一列，或者至多8000列。

检索的列值按SELECT子句指定的顺序进行。

如果想检索所有的列（按出现在行中的相同顺序），那么用星号（*）不必写列名：

SELECT*

在应用程序里用选择语句时，给出列名会给程序更多的数据独立性。

这是因为：

1、看源码语句时，很容易发现SELECT子句命名的列和INTO子句命名的主变量之间的对应关系。

2、如果在存取的表或视图中添加一列，并使用“SELECT*”，同时由源码生成程序，那么INTO子句中没有与新列相匹配的主变量。

这列使你在SQLCA（SQLWARN4包括一个“W”）里得到一个警告（而不是错误）。

3.1.7WHERE子句

WHERE子句设定一个检索条件，用来识别需要检索、修改、或删除的行，用SQL语句处理的行数由满足WHERE子句查询条件的行数决定。

检索条件由一个或多个谓词组成，一个谓词指定一个检测，SQL对表的规定行进行操作。

在下例中，WORKDEPT=‘C01’是一个谓词，WORKDEPT和‘C01’都是表达式，等号（=）是比较运算符。

注意，字符值放在引号（’）内，数字值不用放在引号内，这种规定适合SQL中的所有常量值。

例如，为了指定想要的部门号为C01的行，可以规定：

WHEREWORKDEPT=‘C01’

在这个例子里，检索条件由一个谓词；WORKDEPT=‘C01’组成。

如果检索条件包含字符或UCS-2图形列谓词，那么当查询运行时有效的分类顺序应适用于这些谓词。

详细信息请看3.7。

3.1.7.1在WHERE子句中使用表达式

WHERE子句中的表达式命名或规定要与之相比较的内容。

SQL语言求值的表达式可以是一个字符串，日期/时间/时间标记，或数值。

表达式可以是：

一个列名。

例如：

WHEREEMPNO=‘000200’

EMPNO命名一列，该列定义为6字节的字符值。

相等比较（也就是X=Y或X<>Y）

可以在字符数据中实现，其它比较类型也可以由字符数据评估。

不能把字符串和数据进行比较，也不能在字符数据中执行算术运算（即使EMPNO

是可出现数字的字符串）。

可以增加/减少日期/时间值。

表达式可识别进行加（+）、减（-）、乘（*）、除（/）、乘方（**）或合并（CONCAT或￤￤）运算的两个值，结果为一个值。

表达式的操作数可能是：

一个常量（也就是，一个常数值）。

一列。

一个主变量。

函数返回值。

专用寄存器。

另一个表达式。

例如：

WHEREINTEGER（PRENDATE-PRSTDATE）>100

当未用圆括号指定求值顺序时，表达式按以下顺序求值：

1、前缀运算符2、幂运算

3、乘、除和合并运算4、加减运算

同一级的运算符按由左到右的顺序计算。

常量规定表达式为文字值。

例如：

WHERE40000

SALARY是列名，定义为9位数字压缩十进制值（DECIMAL（9,2））。

它与数字常量40000比较。

主变量标识应用程序中的变量。

例如：

WHEREEMPNO=:

EMP

专用寄存器标识由数据库管理生成的特殊值。

例如：

WHERELASTNAME=USER

空值规定未知值的条件。

WHEREDUE-DATEISNULL

检索条件不必限制算术或比较运算符分开的两个列名或常量，可以指定由AND或OR来规定复杂的检索条件。

无论多么复杂，当对一行求值时，都会提供一个TRUE或FALSE值。

也有一个未知的真值，实际上是假的，也就是说，如果一行值为空，这个空值不作为检索结果返回。

因为它不小于，等于或大于检索条件指定的值。

更复杂的查询条件和谓词将在5.3中讲述。

全面理解WHERE子句，需要了解SQL怎样计算检索条件和谓词，怎样比较表达式值。

这方面内容在AS/400DB2SQL参考中介绍。

3.1.7.2比较运算符

SQL支持下面列出的比较运算符：

=等于

<>不等于

<小于

>大于

<=小于等于（或不大于）

>=大于等于（或不小于）

3.1.7.3NOT键字

可在谓词前加NOT键字，用来指定谓词的相反值。

（也就是说，如谓词是FALSE，它为TRUE；反之也可）。

NOT仅管其后的谓词，并不管WHERE子句的所有谓词。

例如，想要除了在部门C01工作的所有雇员，可以规定：

WHERENOTWORKDEPT=‘C01’

它等价于：

WHEREWORKDEPT<>‘C01’

3.1.8GROUPBY子句

不用GROUPBY子句，SQL列功能的应用只返回一行。

用GROUPBY时，功能适用于一组，组中有多少行就返回多少行。

GROUPBY子句允许找到多行的一组特性，而不是一行的特性。

当规定GROUPBY子句时，SQL把选择的行分组，使每组的行在一列或多列有相匹配的值。

接下来，SQL处理每组产生的单行结果。

可以在GROUPBY子句中规定一列或多列给行分组。

在SELECT语句中规定的项是行的每组属性，而不是表或视图各行的属性。

例如，CORPDATA.EMPLOYEE表中有几组行，每组都由描述专门部门成员的行组成，为了找出每个部门人员的平均工资，可以规定：

PIC8

结果为几行，每个部门对应一行。

注：

1、给行分组并不意味给它们排序。

分组是选择的行放在一组，SQL处理组中取得的特性，排序是按升序或降序把所有行放到结果表中。

（3.1.10详细介绍如何做）。

2、如果在GROUPBY子句中规定的列有空值，有空值行的数据产生一行结果。

3、如果分组发生在字符或UCS-2图形列上，当对组运行查询时分类序列起作用。

详情请看3.7。

当用GROUPBY时，要命名SQL用来分组的列。

例如，假定你需要一个从事主要项目工作的人数表，它取自该表CORPDATA.PROJECT表。

可以规定：

PIC9

结果是公司当前主要项目和从事每一项目的人数的列表。

可以按多列指定需要分组的行。

例如，能用选择语句来查找每个部门男职工和女职工的平均工资，它们来自CORPDATA.EMPLOYEE表。

可在规定：

PIC10

由于这个例子没用WHERE子句，SQL将检查并处理CORPDATA.EMPLOYEE表里的所有行。

这些行首先按部门号分组，然后（在每个部门内）按性别分组，这些工作将在SQL获取每组平均工资值之前完成。

3.1.9HAVING子句

对用GROUPBY子句选择的分组，可以使用HAVING子句规定检索条件。

HAVING子句表明只要满足子句条件的那些组。

由HAVING子句规定的检索条件测试每组的属性，而不是组里每行的属性。

HAVING子句在GROUPBY子句之后，可包括由WHERE子句规定的同种检索条件。

另外，可以用HAVING子句规定列函数。

例如，假定想检索每个部门妇女的平均工资，可以使用AVG列函数，按WORKDEPT分组，同时规定WHERE子句的条件为SEX=‘F’。

为了仅要选取部门中教育水平大于等于16（大学毕业）的全部女职员时，使用HAVING子句。

HAVING子句可以检测组的属性。

此时，测试是MIN（DELEVEL）：

PIC11

可以在HAVING子句里用AND或OR连接多个谓词，对于检索条件的任何谓词都可以使用NOT健字。

注：

如果打算修改一列或删除一行，不能在DECLARECURSOR语句中的SELECT中包含GROUPBY子句或HAVING子句。

没有列函数的自变量的谓词能用在WHERE或HAVING子句中，用WHERE子句给选择准则编码通常是更有效的，它在查询处理的初始段进行处理，HAVING选择在结果表的录入处理时完成。

如果检索条件包括字符或UCS-2图形列谓词，查询运行时有效的分类排序也适合于这些谓词。

详情请见3.7。

3.1.10ORDERBY子句

用ORDERBY子句可以指定按某个顺序进行检索来选择行，按列值的升序或降序进行分类排序。

ORDERBY子句的使用与GROUPBY子句类似：

当按分类顺序检索行时，规定列名或SQL使用的一些列。

例如，按部门号的字母顺序，检索女职员的名字和部门号，可用下面的选择语句：

PIC12

注：

1、所有在ORDERBY子句中命名的列也必须在SELECT列表中命名。

2、空值为最高值排序。

用表达式，列函数，或不是列名的其它内容排序，可在选择列表里规定AS子句。

AS子句可命名结果列，这个名字可在O