OracleOracle编程入门经典 第2节 SQLPlus和核心查询

  • 哪利用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,如下图所示:

Oracle 1

Oracle 2

Oracle 3

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/

相关文章