SQLPLUS 教程（二）

1．2数据操纵语言

数据操纵语言（DML：Data Manipulation Language）给用户或应用程序提供访问数据库系统的接口。

1．2．1数据库的基本查询

SQL语言中最主要、最核心的部分是它的查询功能。所谓查询就是从数据库中提取满足用户需要的数据，查询是由SELECT命令实现的。在SQL语言中，许多操作都涉及到SELECT命令。例如，半SELECT命令查询到的数据插入到另外一张基表中；使用SELECT命令用满足条件的数据创建一个视图等等。因此SELECT命令也是SQL语言中最灵活、最复杂的命令。我们将从简单的SELECT命令开始，逐步深入讨论。

通常，一个SELECT命令可以分解成三个部分：查找什么数据，从哪里查找，查找条件是什么。因此，SELECT命令可以分成以下几个子句：SELECT子句和FROM子句（这两个子句是每条SELECT命令必须有的），WHERE子句（是可选的），另外带可以选择GROUP BY 子句、HAVING子句和ORDER BY子句等。它的基本语法是

SELECT <查询内容>

FORM<表名>

WHERE <条件>

GROUP BY<分组内容>

HAVING <组内条件>

ORDER BY<排序内容>

 

1、SELECT子句

SELECT子句的<查询内容>指出要查找的数据，FROM子句的<表名>指出查找过程中涉及到的基表，这两个子句必不可少。

例1、查找整个基表的内容。

SQL>SELECT * FROM EMP；

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-94	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-95	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-95	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-95	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-95	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-95	2850		30
7782	CLARK	MANAGER	7839	09-Jun-95	2450		10
7788	SCOTT	ANALYST	766	09-Nov-95	3000		20
7839	KING	PRESIDENT		17-Nov-95	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-95	1500	0	30
7876	ADAMS	CLERK 7788		23-Sep-95	1100		20
7900	JAMES	CLERK 7698		03-Dec-95	950		30
7902	FORD	ANALYST	7566	03-Dec-94	3000		20
7934	MILLER	CLERK	7782	23-Jan-94	1300		10
14 rows selected.

“*”号意味着查找表中所有列。

若SELECT子句中选择一个列名，则将得到这一列的所有数据。

例2、查看雇员名字。

SQL>SELECT ENAME FROM EMP；

ENAME

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

14rows selected.

若查找多列数据，则用逗号将各个列名分隔开。

例3、查看雇员编号、名字和工种。

SQL>SELECT EMPNO，ENAME，JOB

     FROM EMP；

EMPNO	ENAME	JOB
7369	SMITH	CLERK
7499	ALLEN	SALESMAN
7521	WARD	SALESMAN
7566	JONES	MANAGER
7654	MARTIN	SALESMAN
7698	BLAKE	MANAGER
7782	CLARK	MANAGER
7788	SCOTT	ANALYST
7839	KING	PRESIDENT
7844	TURNER	SALESMAN
7876	ADAMS	CLERK 7788
7900	JAMES	CLERK 7698
7902	FORD	ANALYST
7934	MILLER	CLERK
14 rows selected.

系统在显示查询结果时，各列的显示顺序由它们在SELECT 子句中出现的顺序决定。如例3中希望显示顺序是JOB，ENAME，EMPNO，则可以输入

SQL>SELECT JOB，ENAME， EMPNO

     FROM EMP；

JOB	ENAME	EMPNO
CLERK	SMITH	7369
SALESMAN	ALLEN	7499
SALESMAN	WARD	7521

………………

14 rows selected.

在显示查询结果时，各列的标题就是基表中的列名，如果希望显示的列标题不同于列名，可在SELECT子句中定义列名的别名，方法是在列名后加一个空格，然后写上它的别名。

例4、SQL>SELECT ENAME EMPLOYEE

     FROM EMP；

EMPLOYEE

SMITH

ALLEN

WARD

…………

14 rows selected.

如果SELECT子句中有表达式，则这个表达式就是显示信息时的标题，当然，也可以为表达式定义一个别名，便于明确表达式的意义。

例5、计算奖金占工资的比率。

SQL>SELECT ENAME，SAL，COMM，COMM/SAL“C/S RATIO”

     FROM EMP；

ENAME	SAL	COMM	C/S RATIO
MARTIN	1250	1400	1.12
WARD	1250	500	0.4
ALLEN	1600	300	0.1875
……	……	……
14 rows selected.

当别名中有诸如空格或斜线（/）这样的字符时，必须用双引号括起来。注意：别名只能用在SELECT子句中，其他子句不能使用，并且别名仅在使用它的SELECT命令中有效，而不影响其他查询语句的结果。

 

2、WHERE子句

前面讨论的都是无条件查询，即选中基表中的所有行，我们还可以通过SELECT命令中的WHERE子句来选择指定的行。

例6、查找在10号部门工作的雇员。

SQL>SELECT ENAME

     FROM EMP

     WHERE DEPTNO=10；

ENAME
CLARK
KING
IMILLER
3 rows selected.

其中“DEPTNO=10”就是查询条件。系统在执行这种条件查询时，逐行地对表中的数据进行比较，检查它们是否满足条件，如果满足条件，则取出该行的有关信息，如不满足，则不取该行的信息。使用WHERE子句时，应注意列值的表示方法，若该列为字符型，需使用单引号（‘’）将字符串括起来，如：WHERE ENAME=‘SMITH’，而且应注意单引号内的字符串要区分大小写形式。若该列为数字型，则不必使用引号，如WHERE DEPTNO=20。此外列值还可以取其他列（或其他基表的列）的值，如：WHERE EMP.DEPTNO=DEPT.DEPTNO，这里列值取另一个基表中的列，这实际上是一种连接形式。关于联接将在1.6.1小节中具体论述。

在HWERE子句的条件中可以使用下列比较运算符：

运算符		含义
=		相等
！=或< >		不等于
>		大于
>=		大于等于
<		小于
<=		小于等于
IN（列表）		等于列表中的任意值
BETWEEN 值1 AND 值2		大于等于值1并且小于等于值2（即在值1和值2之间）
LIKE%或_		模式匹配。“%”匹配0个或任意多个字符。“_”匹配一个字符
IS NULL		空值
IS NOT NULL		非空值

（1）IN

在查找中，经常会遇到要求表的列是某几个值中的一个。

例7、要求查找有哪些职员和分析员。这里就要在EMP表中检查JOB列是否为‘CLERK’或‘ANALYST’，即JOB列的值为‘CLERK’和‘ANALYST’中的一个即可。对于这样的查询，一般可以用或（OR）操作来完成：

SQL>SELECT ENAME，JOB

     FROM EMP

     WHERE JOB=‘CLERK’ OR JOB=‘ANALYST’；

如果JOB列还可以取多个值，则还需要多个OR操作，如果使用IN，就可以用一个简单的WHERE子句表示这种要求：

SQL>SELECT ENAME，JOB

     FROM EMP

     WHERE JOB IN （‘CLERK’，‘ANALYST’）；

ENAME	JOB
SMITH	CLERK
SCOTT	ANALYST
ADAMS	CLERK
JAMES	CLERK
IFORD	ANALYST
MILLER	CLERK
6 rows selected.

谓词IN的作用就是检查列值是否等于它后面括弧内的一组值中的某一个，如果等于其中某一个值，则被查询出来。

我们还可以使用NOT IN来表示与IN完全相反的含义，即选择那些不在列表中的行。

例8、查找不是职员和分析员的雇员姓名。

SQL>SELECT ENAME，JOB

     FROM EMP

     WHERE JOB NOT IN （‘CLERK’，‘ANALYST’）；

（2）BETWEEN…AND

在查找中，如果要求某列的数值在某个区间范围内，则可以使用BETWEEN…AND。

例9、查找哪些雇员的工资在$2000到$3000之间，可以写成

SQL>SELECT ENAME，JOB，SAL

     FROM EMP

            WHERE  SAL BETWEEN 2000 AND 3000；

ENAME	JOB	SAL
JONES	MANAGER	2975
BLAKE	MANAGER	2850
CLARK	MANAGER	2450
SCOTT	ANALYST	3000
FORD	ANALYST	3000
5 rows selected.

与此相反，NOT BETWEEN……AND则选择列值不在该范围内的信息。

例10、SQL>SELECT ENAME，JOB，SAL

     FROM EMP

             WHERE  SAL NOT BETWEEN 2000 AND 3000；

（3）LIKE（模式匹配）

在查找中，有时需要对字符串进行比较。在比较中，有的要求两个字符串完全相同，有的要求部分字符相同，而其余的字符可以任意。LIKE就可以用来搜索所有的数据，来查找与你描述的模式相匹配的行。

LIKE提供两种字符串匹配方式：

一种用下划线（_）表示，称为定位标志；另一种用百分号（%）表示，称为通配符。

在检测一个字符串时，如果有一个字符可以任意，则在该字符串位置上用下划线表示。

例11、选择名字以W开头，后面仅有三个字母的雇员信息。

显然，在查找中，要求名字的第一个字母是W，后面仅有三个字母，可以是任意字母，因此后面三位可以用三个下划线来表示：

SQL>SELECT ENAME，DEPTNO

     FROM EMP

     WHERE ENAME LIKE‘W_ _ _’；

ENAME	DEPTNO
WARD	30
1 row selected.

若在查找时只需包含某个字符串，其他字符任意的情况下，可用通配符%来表示。

例12、SQL>SELECT ENAME，DEPTNO

     FROM EMP

     WHERE ENAME LIKE‘S%’；

ENAME	DEPTNO
SMITH	20
SCOTT	20
2 rows selected.

通配符%还可以与下划线一起使用。

例13、查找名字以字母M开头，以R结尾，并且第三个字母为L的雇员名字、工种和所在部门号。

那么，在查找中，名字的第一个字母是M，第三个字母是L，第二个字母可以是任意的，该位置可用一个下划线表示。最后一个字母是R，而第三个字母和最后一个字母之间可以是任意长度的字符串，所以在字母L和R之间使用通配符%，其命令语句如下：

SQL>SELECT ENAME，JOB

     FROM EMP

     WHERE ENAME LIKE‘M_L%R’；

ENAME	DEPTNO
MILLER	CLERK
1 row selected.

总之，我们可以使用通配符%匹配任意长度的字符串，使用‘_‘匹配一个任意字符。此外我们还可以使用NOT LIKE选择列值不匹配的行。

例14、查找哪些雇员的工种名不以SALES开头。

SQL>SELECT ENAME，JOB

     FROM EMP

     WHERE JOB NOT IN LIKE‘SALES%’；

（4）IS NULL和IS NOT NULL

我们使用的数据库一般是反映实际的具体情况，但有时某些数据在实际情况中还不存在，例如：新来的雇员还未分配到部门工作，那么他的DEPTNO就不存在，他的DEPTNO值既不等于0也不等于其他值，而是一个未知的值，我们称它的DEPTNO值为空值。空值实际是指一种未知的、不存在的或不可应用的数据，通常用NULL表示。NULL仅仅是一个符号，它不等同于零，所以它不能象零那样进行算术运算。NULL不能与等号之类的运算符连用，而应该使用关键字IS。

例15、选择没有奖金的雇员信息（即奖金为空值的雇员信息）

SQL>SELECT ENAME，JOB

     FROM EMP

     WHERE COMM IS NULL；

ENAME	JOB
SMITH	CLERK
JONES	MANAGER
BLAKE	MANAGER
CLARK	MANAGER
SCOTT	ANALYST
KING	PRESIDENT
ADAMS	CLERK
JAMES	CLERK
FORD	ANALYST
MILLER	CLERK
10 rows selected.

若查找列值为非空的信息，则使用IS NOT NULL。

例16、选择有奖金的雇员。

SQL>SELECT ENAME，JOB

     FROM EMP

     WHERE COMM IS NOT NULL；

（5）逻辑运算符NOT，AND，OR

在WHERE子句中，也可以通过逻辑运算符连接多个条件，构成一个更复杂的条件进行查询。在WHERE子句中可以使用三种逻辑运算符：

运算符	名称含义
NOT	逻辑非选择不满足条件的行
AND	逻辑与选择列值同时满足多个条件的行
OR	逻辑或选择列值满足任一条件的行

在WHERE子句中，关系比较符的优先级高于逻辑运算符。在逻辑运算符中，逻辑非（NOT）的优先级最高，逻辑与（AND）次之，逻辑或（OR）优先级最低。

例17、查找20号部门里不是职员的雇员信息。

SQL>SELECT ENAME，JOB

     FROM EMP

     WHERE DEPTNO=20

     AND JOB！=‘LERK’；

ENAME	JOB
JONES	MANAGER
SCOTT	ANALYST
FORD	ANALYST
3 records selected.

例18、查找在20号部门工作或不是职员的雇员信息。

SQL>SELECT ENAME，JOB，DEPTNO

     FROM EMP

     WHERE DEPTNO=20

     OR JOB！=‘CLERK’；

ENAME	JOB	EPNTO
SMITH	CLERK	20
ALLEN	SALESMAN	30
WARD	SALESMAN	30
JONES	MANAGER	20
MARTIN	SALESMAN	30
BLAKE	MANAGER	30
CLARK	MANAGER	10
SCOTT	ANALYST	20
KING	PRESIDENT	10
TURNER	SALESMAN	30
ADAMS	CLERK	20
FORD	ANALYST	20
13 rows selected.

综合上所述，在WHERE子句中，可以使用关系运算符、逻辑运算符以及特殊的运算符LIKE等构成条件，当条件满足时，则取有关的数据。这些运算符的优先顺序如下：

①=，！=，<>，>，>=，<，<=

 [NOT] BETWEEN…AND，[NOT]IN，[NOT]LIKE，IS [NOT]NULL

②NOT

③AND

④OR

括弧（）可以改变上述运算符执行的顺序。

 

3、ORDER BY子句

通常使用SELECT命令查找数据时，查询结果按各行在表中的顺序显示，当需要按照某种特定的顺序显示时，可以通过ORDER BY子句来改变查询结果的显示顺序。ORDER BY子句的格式是：

SELECT ……

FROM……

[WHERE……]

ORDER BY<列名>[ASC/DESC][，<列名>[ASC/DESC]]……；

在ORDER BY子句中，<列名>指出查询结果数据按该列排序，选项[ASC/DESC]表示按升序还是降序排列，选择ASC为升序显示，选择DESC为降序显示，该项缺省为升序形式。

例19、按字母顺序显示雇员的名字。

SQL>SELECT ENAME

     FROM EMP

     ORDER BY ENAME；

ENAME
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
14 rows selected.

如果按降序排列，必须加上关键字“DESC”。

例20、按部门号降序显示。

SQL>SELECT *

     FROM EMP

     ORDER BY DEPTNO DESC；

DEPTNO	DNAME	LOC
40	OPERATIONS	BOSTON
30	SALSE	CHICAGO
20	RESEARCH	DALLAS
10	ACCOUNTING	NEW YORK
4 rows selected.

如果按多列进行排序时，应分别指出它们相应的列名及有关的递增或递减方式。选择按多列排序，首先由第一个列名确定顺序，若第一排序列值相同，再按第二个列名排序，第二排序列值相同，再按第三列排序，依此类推。

例21、查找工资高于$2000的雇员信息，按部门号和雇员名字排序。

SQL>SELECT ENAME，SAL，DEPTNO

     FROM EMP

     WHERE SAL>2000

     ORDER BY DEPTNO，ENAME；

ENAME	SAL	DEPTNO
CLARK	12450
SCOTT	3000
KING	5000	10
FORK	3000	20
JONES	2975	20
BLAKE	2850	30
6 rows selected.

注意：如果排序列含有空值（如上述的DEPTNO），则含有空值的行首先显示，而不管选择升序还是降序形式。

GROUP BY子句和HAVING子句将放到1.5.2节“聚组函数”具体论述。

 

4、表达式

在SQL命令中还可以使用加（+）、减（—）、乘（*）、除（/）运算符构成的表达式，其中乘、除运算的优先级高于加、减运算。算术表达式的处理顺序从左到右，括弧可以改变处理的顺序。例如，12*（SAL+COMM）！=12*SAL+COMM。这些运算符可以用于SQL命令的SELECT子句、WHERE子句、ORDER BY子句以及后面还要讲述的GROUP BY子句及HAVING子句中，但不能在FROM子句中使用运算符。

例22、选择奖金高于其工资的5%的雇员。

SQL>SELECT ENAME，SAL，COMM，COMM/SAL

     FROM EMP

     WHERE COMM>0.05*SAL

     ORDER BY COMM/SAL DESC；

ENAME	SAL	COMM	COMM/SAL
MARTIN	1250	1400	1.12
WARD	1250	500	0.4
ALLEN	1600	300	0.1875
3 rows selected.

 

1．2．2数据更新：插入、修改和删除

在数据库应用中，经常要对存放的数据进行更新操作，以满足不断变化的需求，因此对数据进行插入、修改和删除就成为必不可少的一项工作。

 

1、数据的插入

当需要为一个基表增加新的数据时，就要向基表中插入数据，这是由INSERT命令来实现的。

INSERT命令有两种用法：一种是将指定的具体数值插入表中，通过关键字VALUES来实现；另一种是将其他表中满足条件的数据插入到一个表中，这是通过SELECT子句实现的。

在插入数据时，应首先确认基表已经创建，然后确定基表的结构，基表的各列顺序、类型以及是否为非空（NOT NULL），可以通过DESCRIBE命令来查看，以保证插入数据的类型与基表列的类型匹配。若插入字符型和日期型数据，要用单引号括起来。

（1）用VALUES插入。

向基表中插入指定数据的INSERT命令格式是

INSERT INTO <表名>[(列名表)]

VALUES（<值表>）；

其中：<表名>指出要插入数据的基表名；<列名表>指出在新插入行中的哪些列要插入数据，这是可选项，如果选择，则表示相应的列要插入数据，如果不选择，则默认表中所有的列均要插入数据；<值表>指出要插入列的具体值。

例1、为DEPT表插入一行新数据。

SQL>INSERT INTO DEPT

     VALUES （10，‘ACCOUNTING’，‘NEW YORK’）；

DEPT Table

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK

注意：当不选择<列名表>时，<值表>中的值应与基表的各列一一对应。如果在INSERT命令中给出列名，则要求<值表>中的值与<列名表>中的列名一一对应。

例2、

SQL>INSERT INTO DEPT（DNAME，DEPTNO）

     VALUES （‘ACCOUNTING’，10）；

 DEPT Table

DEPTNO	DNAME	LOC
10	ACCOUNTING

插入时，表名中的列如果未在INSERT的<列名表>中出现，那么这些列则为空值，如上例中的LOC列为空值。也可以为某列插入“NULL”，将该列置为空。插入空值的列，必须保证该列在建立时没有被定义成NOT UNLL属性，否则系统拒绝插入。

（2）用SELECT命令插入。

INSERT命令可以使用SELECT语句从其他基表中选择数据，插入基表中。其命令格式是

INSERT INTO<表名>[(列名表)]

SELECT 语句；

例3、

SQL>INSERT INTO EMP（EMPNO，ENAME，DEPTNO）

     SELECT ID，NAME，DEPARTMENT

     FROM OLD_EMP

     WHERE DEPARTMENT IN （10，20，30，40）；

同使用VALUES类似，在使用SELECT命令时，应保证INSERT<列名表>中的各列与SELECT子句中选中的各列一一对应，类型匹配。

（3）INSERT命令中使用参数。

INSERT命令中还可以使用参数来接收用户输入的数据，每个参数以&为前缀，后接参数名（可以取列名）。

例4、

SQL>INSERT INTO DEPT

     VALUES （&DEPTNO，&DNAME，&LOC）；

在执行该命令时，SQL*PLUS将提示用户输入每个参数。重复执行该命令可以接收不同的数据，快速地向表中插入多条记录。

若参数对应的是日期型或字符型数据时，可以在参数上加引号，这样用户输入数据时就不用再输入引号了。

例5、

SQL>INSERT INTO DEPT

     VALUES （&DEPTNO，‘&DNAME’，‘&LOC’）；

（4）插入日期型数据。

在插入日期型数据时，日期必须带单引号且以缺省的ORACLE的日期格式出现，即‘DD-MON-YY’格式。

例6、加入一名雇员。

SQL>INSERT INTO EMP

     （EMPNO，ENAME，HIREDATE）

     VALUES （7963，‘STONE’，‘07-APR-95’）；

如果想输入当前的系统时间，可以使用SYSDATE。如果要插入一个非缺省格式的日期，可以使用TO-DATE函数进行转换。关于TO-DATE函数将在1.5函数一节中详细论述。

 

2、数据的修改

在ORACLE中，对数据的修改是使用UPDATE命令来实现的。UPDATE命令的格式如下：

UPDATE<列表名>

SET<列名1>=<表达式1>，<列名2>=<表达式2>……

[WHERE <条件>]；

UPDATE命令对由<表名>指定的基表进行修改。修改时，对表中满足条件的行将用<表达式>的值替换相应列的值。

例7、将Martin提升为经理。

SQL>UPDATE EMP

     SET JOB=‘MANAGER’

     WHERE ENAME=‘MARTIN’；

如果例7中不使用WHERE子句，则EMP表中的每一行都将被置为SET子句中的数值。UPDATE命令不但可以修改一行的某一列数据，还可以修改若干行的几列数据。

例8、将所有销售人员的工资增加20%，并转入部门40。

SQL>UPDATE EMP

     SET DEPTNO=40，SAL=SAL*（1+0.02）

     WHERE JOB=‘SALESMAN’；

（1）在UPDATE中使用SELECT命令。

在UPDATE命令中可以嵌入SELECT语句设置修改值。

例9、

SQL>UPDATE EMP

     SET （SAL，COMM）=

      （SELECT SALARY，COMM

       FROM OLD_EMP

       WHERE DEPARTMENT=10）

       WHERE DEPTNO=10；

需要修改的列必须出现在括号内，并以逗号隔开。被设置的值通过括号内的SELECT语句获得，注意：应保证SET<列名>与SELECT子句中选择的各列一一对应，并且SELECT语句最多返回一行结果，若未返回任何行，则被修改的各行的有关列被置成NULL。

（2）用NULL进行修改。

修改数据时，也可以将基表中的某列设置为NULL，这里使用空值是用等号而不是“IS”。

例10、

SQL>UPDATE EMP

     SET COMM=NULL

     WHERE JOB=‘SALESMAN’；

 

3、数据的删除

对表中数据进行删除是使用DELETE命令实现的，DELETE命令格式如下：

DELETE FROM<表名>

      [WHERE <条件>]；

DELETE命令是从由<表名>指定的表中删除满足<条件>的行。WHERE <条件>是可选项，若只删除指定的几行，则必须使用WHERE子句，如果WHERE子句缺省，则删除表中的全部数据。

例11、Martin离开公司，应从公司中除名。

SQL>DELETE FROM EMP

       WHERE ENAME=‘MARTIN’；

注意：不能删除一行的部分信息，只能把该列改为空值。

总之，我们可以使用INSERT，UPDATE和DELETE命令对数据库进行更新，但要注意：这些命令常带有WHERE子句，它仅影响你所选中的各行数据。这三个命令扩展ORACLE的功能，使它不仅能够查询，还允许直接对数据进行操作。