Oracle编程入门经典第2章 SQLPlus和基本查询

2013-01-06 01:22 夜雨瞳阅读(1521) 评论(0) 编辑收藏举报

怎样使用SQL*Plus工具连接数据库以及执行查询
怎样配置SQL*Plus，以格式化查询结果
SQL语句的不同种类
怎样在数据库上编写查询来查看数据
怎样修改存储在数据库中的数据

2.1 SQL*Plus简介

SQL*Plus是一个用于连接Oracle数据库的工具，具有可以满足Oracle用户和管理员需求的大量功能，包括：

在数据库中执行SQL和PL/SQL
更新数据库中的数据
执行数据查询
将查询结果集格式化为报表
建立、编辑、检索和执行SQL脚本
帮助Oracle用户调整SQL查询
管理数据库
描述数据库中的表和PL/SQL对象
将数据从一个数据库复制到另一个数据库
向用户发送消息，接受这些用户的输入

2.1.1 SQL、PL/SQL和SQL*Plus之间的区别

SQL：结构化查询语言，或者是用来将数据放入数据库，从数据库检索数据，控制事务处理以及管理数据库的语言。

PL/SQL：Oracle的过程化编程语言，用户可以使用它编写在数据库中执行的定制程序以及过程代码。

SQL*Plus：是用户可以用来编写SQL和PL/SQL脚本的接口，即一个接口工具。

以账户（即用户名称）：scott，默认密码（即口令）：tiger 登录数据库（创建数据库，会自动分配一个演示账号scott）

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 12月 29 10:34:34 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> begin

2 for cur in (select * from DEPT) loop

3 dbms_output.put_line(cur.dname || ' is the department name now.');

4 end loop;

5 end;

6 /

PL/SQL 过程已成功完成。

SQL>

2.1.2 启动SQL*Plus

1. 命令行模式的SQL*Plus

Unix：$ORACLE_HOME/bin/sqlplus
Windows：%ORACLE_HOME5/bin/sqlplus.exe

这个目录应该在用户的PATH环境变量中。

SYS是数据词典的所有者，只应用于管理的目的。SYS的默认密码是CHANGE_ON_INSTALL。
SYSTME是一个管理员账号。SYSTEM的默认密码是MANAGER。
SCOTT是一个演示账号。SCOTT的默认密码是TIGER。

试验：连接Oracle

(1) 在DOS或者XTERM中，在提示符下输入sqlplus，打开一个SQL*Plus会话。

(2) 使用用户名SCOTT和密码TIGER登录。

工作原理

2. 通过网络连接

配置C:\oracle\ora92\network\ADMIN的文件sqlnet.ora

# SQLNET.ORA Network Configuration File: C:\oracle\ora92\network\admin\sqlnet.ora

# Generated by Oracle configuration tools.

# NAMES.DEFAULT_DOMAIN = Infomation

# SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

启动配置助理Oracle Net Configuration Assistant，如下图所示：

2.2 快速而简单地查询数据库

试验：

启动SQL*Plus，使用密码TIGER，作为SCOTT连接数据库（或以win+R，输入sqlplus）。

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

BONUS

DEPT

EMP

SALGRADE

工作原理

USER_TABLES是Oracle数据词典中的一个视图。当SQL*Plus中遇到排版乱序（如行不够显示）特殊问题的时候，可以有三种选择：

忍受它。
查询少量的列。
格式化结果（用户可以让标题每10、100、10000行重复一次或者根本不重复）。

2.2.1 格式化用户结果

1. COLUMN

SQL> select ename,sal from emp where ename like 'A%'

2 /

ENAME SAL

---------- ----------

ALLEN 1600

ADAMS 1100

SQL> column sal format $9,999.99

SQL> select ename,sal from emp where ename like 'A%'

2 /

ENAME SAL

---------- ----------

ALLEN $1,600.00

ADAMS $1,100.00

SQL> describe user_objects

名称 是否为空? 类型

----------------------------------------- -------- ----------------

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(18)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

2. PAUSE

SQL> set pause on

一页之后，停止滚屏

SQL> set pause off

全部显示（一页的大小默认PAGESIZE是14）

3. PAGESIZE

试验：设置用户页面大小

从数据词典的ALL_OBJECTS视图中选择ROWNUM和OBJECT_NAME。

SQL> set pagesize 10

SQL> select rownum,object_name from all_objects where rownum<20

2 /

ROWNUM OBJECT_NAME

---------- ------------------------------

1 /1005bd30_LnkdConstant

2 /10076b23_OraCustomDatumClosur

3 /10297c91_SAXAttrList

4 /103a2e73_DefaultEditorKitEndP

5 /10501902_BasicFileChooserUINe

6 /105072e7_HttpSessionBindingEv

7 /106ba0a5_ArrayEnumeration

ROWNUM OBJECT_NAME

---------- ------------------------------

8 /106faabc_BasicTreeUIKeyHandle

9 /10744837_ObjectStreamClass2

10 /1079c94d_NumberConstantData

11 /10804ae7_Constants

12 /108343f6_MultiColorChooserUI

13 /10845320_TypeMapImpl

14 /10948dc3_PermissionImpl

ROWNUM OBJECT_NAME

---------- ------------------------------

15 /1095ce9b_MultiComboBoxUI

16 /109a284b_OracleXMLStaticQuery

17 /109cbb8e_SpanShapeRendererSim

18 /10a45bfe_ProfilePrinterErrors

19 /10a793fd_LocaleElements_iw

已选择19行。

19条记录，2页，这里显示屏幕大小占10行。

3. LINESIZE

LINESIZE默认为80。通过此方式，用户不会因为输出数据超过了窗口的右边限制而遗失数据。

4. FEEDBACK

查询结果末尾输出N行。

SQL> show feedback

用于6或更多行的 FEEDBACK ON

SQL> select empno,ename,job from emp where rownum<7;

SQL> set feedback off

SQL> select empno,ename,job from emp where rownum<7;

SQL> set feedback 3

SQL> select empno,ename,job from emp where rownum<3;

5. NUMFOFRMAT

NUMFORMAT的默认值是10。

SQL> set numformat 9999999.99

SQL> select sal from emp;

6. LONG

LONG的默认值是80.如果用户要查询具有LONG列的表或者视图，那么就只会显示这个特定列的前80个字符。如果使LONG列更长，就可以显示这个列中的更多数据。

SQL> select text from all_views where view_name='USER_TABLES';

TEXT

---------------------------------------------------------------------

select o.name, decode(bitand(t.property, 4194400), 0, ts.name, null),

decode(bitand(t.propert

SQL> set pagesize 1000;

SQL> set long 10000;

SQL> select text from all_views where view_name='USER_TABLES';

2.3 什么是SQL

SQL不是Oracle的专有技术；它是关系数据库管理系统的标准语言（最早是由IBM在二十世纪70年代建立，并于随后被ANSI/ISO标准化组织采纳，并被进一步提炼和升华。

SQL-92 ANSI标准的SQL标准中共有四个层次：

(1) 入门层次。大多数供应商所构建的SQL引擎都会符合层次1.

(2) 过滤层次。随着特性要求的增加，过滤层次介于入门层次和中间层次之间。

(3) 中间层次。中间层次的SQL-92兼容层次要求在数据库中提供大量超过了入门或者过滤兼容性的新特性。这些特性包括但不局限于如下内容：

动态SQL
用于参照完整性的级联删除
DATE和TIME数据类型
域
可变长度字符串
CASE表达式
数据类型之间的CAST函数

(4) 完全。这是最高和最终级别的ANSI SQL-92兼容层次。它向中间层次的标准又增加了更多的特性，这些特性包括但不局限于如下内容：

连接管理
BIT字符串数据类型
可延迟完整性约束
FROM子句中的派生表
CHECK子句中的子查询
临时表

以下的列表展示了用户在使用Oracle的时候将会使用不同类型的SQL：

数据操作语言（DML）
数据定义语言（DDL）
事务处理控制语句
会话控制语句
系统控制语句

2.4 表中内容

DESCRIBE <TABLE_NAME> or DESC <TABLE_NAME>

试验：描述表

SQL> describe dept;

名称 是否为空? 类型

----------------------------------------- -------- -------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

2.5 数据操作语言

2.5.1 查询

SELECT句子有：

WITH
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

2.5.2 WHERE子句

select <columns> from <data table> where <conditional_expression>;

A=B
B
A<B
A!=B;A<>B
BETWEEN A AND B
A LIKE B
A NOT <条件表达式>

1. 二元操作符

AND

2. 操作符和条件的优先级

操作优先级：

:=、-。
*、/。
+、-，||。

条件优先级：

=、!=、<、>、<=、>=。
IS [NOT] NULL、LIKE、[NOT] BETWEEN、[NOT] IN、EXISTS。
NOT。
AND。
OR。

3. 使用WHERE子句联接表

select a.*,b.* from a,b where a.ID=b.ID

4. 理解NULL

NULL是一个用来描述没有定义的内容的术语。NULL列意味着没有向列赋值。

在Oracle中，当执行IF COL_A=<某个值>这样的条件操作时，可能的返回值是TURE、FALSET UNKNOWN。

是否为空，用IS NULL或IS NOT NULL判断。

5. ORDER用户结果

order by <columns,columns..>

6. GROUP BY和HAVING

GROUP BY 子句可以用于在查询结果集中对记录进行分组，以汇总数据或者为整个组显示单选的汇总信息。

HAVING用户可以随同GROUP BY子句提供一个附加的子句，来过滤GROUP BY查询所返回的行组。这个HAVING子句只能够用于使用了GROUP BY子句的查询，并且通常会包含一个SQL集函数，例如AVG、SUM、MAX等。

SQL> select empno,avg(sal) from emp group by empno having avg(sal)>=1000;

EMPNO AVG(SAL)

---------- ----------

7499 1600

7521 1250

7566 2975

7654 1250

7698 2850

7782 2450

7788 3000

7839 5000

7844 1500

7876 1100

7902 3000

7934 1300

已选择12行。

2.5.3 联接

笛卡尔积

SQL> select t1.empno,t2.dname,t2.loc,t1.ename from emp t1,dept t2 where t1.deptno=t2.deptno;

1. 表别名

表别名（以前称为相关名称）是在FROM子句中用于各个表的“简短名称”，它们可以唯一地标识数据源，而且可以使用缩写。

2. 列表名

查询中的列也可以被赋予别名。

3. Oracle 9i 中的ANSI联接

用户能够执行的不同类型的联接如下所示：

自然联接
内连接
外联接
- 左联接
- 右联接
- 完全联接

4. 自然联接

使用自然联接两个表的时候，Oracle就会将第一个表中的那些列与第二个表中具有相同名称的列进行联接。

SQL> select empno,dname,loc,ename,sal from emp natural join dept where sal>1000;

EMPNO DNAME LOC ENAME SAL

---------- -------------- ------------- ---------- ----------

7499 SALES CHICAGO ALLEN 1600

7521 SALES CHICAGO WARD 1250

7566 RESEARCH DALLAS JONES 2975

7654 SALES CHICAGO MARTIN 1250

7698 SALES CHICAGO BLAKE 2850

7782 ACCOUNTING NEW YORK CLARK 2450

7788 RESEARCH DALLAS SCOTT 3000

7839 ACCOUNTING NEW YORK KING 5000

7844 SALES CHICAGO TURNER 1500

7876 RESEARCH DALLAS ADAMS 1100

7902 RESEARCH DALLAS FORD 3000

7934 ACCOUNTING NEW YORK MILLER 1300

已选择12行。

自然联接是一个理论上很好，但是实际应用性很差的特性示例。各个表中的联接列都必有要具有相同的名称。这将会强制设计者将要联接表的相关列命名为与所联接的表具有相同的名称。

5. 内联接

内联接就像自然联接一样，要在FROM子句中使用联接条件。双表中其中一个条关键联接列没有值，则不显示该记录。

Select <columns> from <data table> inner join <data table> where <conditional_expression>

6. 外联接

外联接扩展了内联接的结果。外联接的结果将会是所有满足联接条件的行，以及存在于一个表中的一些行，它们在其他表中没有相应的行来满足联接条件。

Select <columns> from <data table> left/right outer join <data table> on <conditional_expression>

SQL> select empno,dname,loc,ename,sal from emp left outer join dept on emp.deptno=dept.deptno;

EMPNO DNAME LOC ENAME SAL

---------- -------------- ------------- ---------- ----------

7934 ACCOUNTING NEW YORK MILLER 1300

7839 ACCOUNTING NEW YORK KING 5000

7782 ACCOUNTING NEW YORK CLARK 2450

7902 RESEARCH DALLAS FORD 3000

7876 RESEARCH DALLAS ADAMS 1100

7788 RESEARCH DALLAS SCOTT 3000

7566 RESEARCH DALLAS JONES 2975

7369 RESEARCH DALLAS SMITH 800

7900 SALES CHICAGO JAMES 950

7844 SALES CHICAGO TURNER 1500

7698 SALES CHICAGO BLAKE 2850

7654 SALES CHICAGO MARTIN 1250

7521 SALES CHICAGO WARD 1250

7499 SALES CHICAGO ALLEN 1600

7. 自联接

在用户建立的一些表中，用户可能会拥有所谓的自引用外键。这意味着一个列可以是相同表主键的一个外键。

2.5.4 集合操作符

通过使用四个集合操作符UNION、UNIONALL、INTERSECT和MINUS，Oracle提供将两个或者多个SQL查询结合进一个单独的语句的能力。

使用集合操作符的查询称为复合查询（compound query）。Oracle提供了一些编写复合查询时需要遵循的指南：

在构成复合查询的各个单独的查询中，SELECT表中值的数量和数据类型必须相匹配。
用户不许在复合查询所包含的任何单独的查询中规定ORDER BY子句。
用户不许在BLOB、LONG这样的大数据对象上使用集合操作符。
用户不许在集合操作符SELECT列表中使用嵌套或者数组这样的集合。

1. UNION

UNION语句可以将第一个查询中的所有行与第二个查询的所有行相加，消除重复行并且返回结果。

SQL> select empno,ename,job from emp where ename like 'F%' union select empno,ename,job from emp where ename like 'A%';

EMPNO ENAME JOB

---------- ---------- ---------

7499 ALLEN SALESMAN

7876 ADAMS CLERK

7902 FORD ANALYST

2. UNION ALL

UNION ALL语句与标准的UNION语句工作方式基本相同，只是不会从列表中滤除重复行。

SQL> select empno,ename,job from emp where ename like 'F%' union all select empno,ename,job from emp where ename like 'A%';

EMPNO ENAME JOB

---------- ---------- ---------

7902 FORD ANALYST

7499 ALLEN SALESMAN

7876 ADAMS CLERK

3. INTERSECT

INTERSECT这个集合操作符会获取两个查询，对值进行汇总，并且返回同时存在于两个结果集中的记录。

SQL> select empno,ename,job from emp where ename like 'F%' or ename like 'A%' intersect select empno,ename,job from emp where ename like 'A%';

EMPNO ENAME JOB

---------- ---------- ---------

7499 ALLEN SALESMAN

7876 ADAMS CLERK

4. MINUS

MINUS集合操作符会返回所有从第一个查询中的记录，但是没有从第二个查询中返回的那些记录。

SQL> select empno,ename,job from emp where ename like 'F%' or ename like 'A%' minus select empno,ename,job from emp where ename like 'A%';

EMPNO ENAME JOB

---------- ---------- ---------

7902 FORD ANALYST

2.6 其它DML语句

2.6.1 INSERT语句

SQL> insert into dept values(50,'INFORMATION','CHINA');

2.6.2 UPDATE语句

SQL> update dept set DNAME='OPERATION' where deptno=40;

2.6.3 DELETE语句

SQL> delete from dept where deptno=40;

2.7 提交和回滚

在Oracle中，在用户通知Oracle完成之前，用户对数据所做的改变（例如，INSERT、UPDATE、DELETE）都不会永久改变。这可以使用COMMIT的SQL语句完成。

当用户在数据库中改变数据的时候，用户可能会发现有的时候会因为这样或者那样的原因，需要撤销已经进行的改变。用户可以使用ROLLBACK语句。

试验：提交改变

另打开一个会话（即打开2个会话），用相同帐户登录，第一个会话插入一条记录，第二个会话查询。

SQL> insert into dept values(50,'OPERATIONS','BOSTON');

已创建 1 行。

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 OPERATIONS BOSTON

SQL>

第一个会话未COMMIT前，第二个会话的记录：

SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 12月 31 08:47:08 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL>

第一个会话：

SQL> commit;

第二个会话的记录：

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 OPERATIONS BOSTON

SQL>

工作原理

在我们的第一个会话没有提交事务处理之前，不能在第二个会话中看到对多张表所做的改变。一旦使用COMMIT语句在我们的第一个会话中完成了事务处理，那么我们就能够立即在第二个会话中看到数据。

2.8 回滚

回滚事务和提交事务相似，即使用ROLLBACK便可对未提交的事务进行回滚。

2.9 小结

文章根据自己理解浓缩，仅供参考。

摘自：《Oracle编程入门经典》清华大学出版社 http://www.tup.com.cn/

刷新页面返回顶部

夜雨瞳 <strong>人致力于一个目标，一种观念......是人在生活过程中追求完整之需要的一种表现 --- 罗曼.罗兰</strong>

Oracle编程入门经典 第2章 SQLPlus和基本查询

2.1 SQL*Plus简介

2.1.1 SQL、PL/SQL和SQL*Plus之间的区别

2.1.2 启动SQL*Plus

1. 命令行模式的SQL*Plus

试验：连接Oracle

2. 通过网络连接

2.2 快速而简单地查询数据库

2.2.1 格式化用户结果

1. COLUMN

2. PAUSE

3. PAGESIZE

3. LINESIZE

4. FEEDBACK

5. NUMFOFRMAT

6. LONG

2.3 什么是SQL

2.4 表中内容

2.5 数据操作语言

2.5.1 查询

2.5.2 WHERE子句

1. 二元操作符

2. 操作符和条件的优先级

3. 使用WHERE子句联接表

4. 理解NULL

5. ORDER用户结果

6. GROUP BY和HAVING

2.5.3 联接

1. 表别名

2. 列表名

3. Oracle 9i 中的ANSI联接

4. 自然联接

5. 内联接

6. 外联接

7. 自联接

2.5.4 集合操作符

1. UNION

2. UNION ALL

3. INTERSECT

4. MINUS

2.6 其它DML语句

2.6.1 INSERT语句

2.6.2 UPDATE语句

2.6.3 DELETE语句

2.7 提交和回滚

工作原理

2.8 回滚

2.9 小结

About

Oracle编程入门经典第2章 SQLPlus和基本查询