前言

Oracle 数据库系统是美国 Oracle 公司(甲骨文)提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器 (Client/Server) 或 B/S 体系结构的数据库之一，比如 SilverStream 就是基于数据库的一种中间件。 Oracle 数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系型数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能，但它的所有知识，只要在一种机型上学习了 Oracle 知识，便能在各种类型的机器上使用它。

Oracle 的基本使用

连接命令

sqlplus /nolog

进入 sqlplus 环境。其中 /nolog 是不登陆到数据库服务器的意思，如果没有 /nolog 参数， sqlplus 会提示你输入用户名和密码。

C:\Users\Administrator>sqlplusSQL*Plus: Release 11.1.0.7.0 - Production on 星期三 10月 16 10:30:42 2019Copyright (c) 1982, 2008, Oracle. All rights reserved.请输入用户名:

conn[etc]

用法: conn 用户名 / 密码 @网络服务名 [as sysdba/sysoper] 当用特权用户身份连接时，必须带上 as sysdba 或是 as sysoper

以系统管理员 (sysdba) 身份连接数据库

SQL> conn / as sysdba已连接。

创建用户

SQL> create user huang identified by 123456;用户已创建。

用户授权

SQL> grant create session,connect,resource to huang;授权成功。

连接到数据库

SQL> conn huang/123456已连接。

show user

显示当前用户名

SQL> show userUSER 为 "HUANG"

passw[ord]

用于修改用户的密码，如果要想修改其它用户的密码，需要用 sys/system 登录。

SQL> passw更改 HUANG 的口令旧口令:新口令:重新键入新口令:口令已更改

disc[onnect]

用于断开与当前数据库的连接 (不退出 sqlplus )

SQL> disc从 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options 断开SQL>

exit

用于断开与当前数据库的连接 (同时退出 sqlplus )

SQL> exit从 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options 断开C:\Users\Administrator>

文件操作命令

start 和 @

运行 SQL 脚本

SQL> @ d:\c.sql表已创建。

或是

SQL> start d:\c.sql表已创建。

edit

该命令可以编辑指定的 SQL 脚本

SQL> edit d:\c.sql

这样会把 d:\c.sql 这个文件打开

spool

该命令可以将 sqlplus 屏幕上的(查询)内容输出到指定文件中去

SQL> spool d:\d.sql SQL> select ASSETNUM 序号,DESCRIPTION 描述 from ASSET; SQL> spool off

交互式命令

&

可以替代变量，而该变量在执行时，需要用户输入， Oracle 会提示用户输入值

SQL> select * from ACCOUNTDEFAULTS where ORGID='&ORGID';输入 orgid 的值:

显示和设置环境变量

可以用来控制输出的各种格式，如果希望永久的保存相关设置，可以修改 glogin.sql 脚本

linesize

设置显示行的宽度，默认是 80 个字符

SQL> show linesizelinesize 80SQL> set linesize 90SQL> show linesizelinesize 90

pagelize

设置每页显示的行数目，默认是 14 ，用法和 linesize 一样

SQL> show pagesizepagesize 14SQL> set pagesize 20SQL> show pagesizepagesize 20

Oracle 用户管理

创建用户

在 Oracle 中要创建一个新的用户使用 create user 语句， 一般是具有 dba (数据库管理员)的权限才能使用。

create user 用户名 identified by 密码 ; SQL> create user zhangsan identified by 123456;create user zhangsan identified by 123456*第 1 行出现错误:ORA-01031: 权限不足

我们连接到 sysdba 创建用户

SQL> conn / as sysdba已连接。SQL> create user zhangsan identified by 123456;用户已创建。

给用户修改密码

如果给自己修改密码可以直接使用

password 用户名 SQL> password huang更改 huang 的口令旧口令:新口令:重新键入新口令:口令已更改

如果给别人修改密码则需要具有 dba 的权限，或是拥有 alter user 的系统权限

alter user 用户名 identified by 新密码 SQL> alter user zhangsan identified by 12345678;用户已更改。

删除用户

一般以 dba 的身份去删除某个用户， 如果用其它用户去删除用户则需要具有 drop user 的权限。

drop user 用户名 [cascade] SQL> drop user zhangsan;用户已删除。

如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数 cascade ;

权限和角色

权限

创建的新用户是没有任何权限的， 甚至连登陆的数据库的权限都没有， 需要为其指定相应的权限；要使用有能力授权的用户，如 sys 、 system 。

权限包含系统权限和对象权限

• 系统权限：用户对数据库的相关权限
• 对象权限：用户对其他用户的数据对象操作的权限

角色

角色是指由系统权限集合。通常给某个用户授予权限时如果没有角色存在的话，那么需要一条一条的操作，角色的存在就是使得授权变得很方便。通常一个角色由多个系统权限组成。常用的角色有三个 connect (7种权限)、 dba 、 resource (在任何表空间建表)。

connect 角色：是授予最终用户的典型权利，最基本的

• alter session 修改会话
• create cluster 建立聚簇
• create database link 建立数据库链接
• create sequence 建立序列
• create session 建立会话
• create synonym 建立同义词
• create view 建立视图

resource 角色： 是授予开发人员的

• create cluster 建立聚簇
• create procedure 建立过程
• create sequence 建立序列
• create table 建表
• cteate trigger 建立触发器
• create type 建立类型

dba 角色：拥有系统所有系统级权限

使用 grant 命令给用户分配权限：

grant 【权限名】 to 【用户名】

• 分配角色

grant 【角色名】 to 【用户名】

• 收回权限

revoke 【权限名】 from 【用户名】

用户管理的综合案例

SQL> create user zhangsan identified by 123456; /*创建用户*/用户已创建。SQL> conn zhangsan/123456; /*新建用户没有 session (登陆)权限*/ERROR:ORA-01045: 用户 ZHANGSAN 没有 CREATE SESSION 权限; 登录被拒绝警告: 您不再连接到 ORACLE。SQL> show userUSER 为 ""SQL> conn / as sysdba;已连接。SQL> grant create session to zhangsan; /*使 zhangsan 能够被连接*/授权成功。SQL> conn zhangsan/123456;已连接。SQL> show user;USER 为 "ZHANGSAN"SQL> conn / as sysdba;已连接。SQL> grant resource to zhangsan; /*让zhangsan 能够在任何表空间下建表*/授权成功。SQL> create table users(name varchar(10),age number(3)); /*在 sys 角色下创建一个简单的表 users */表已创建。SQL> insert into users values('张三',22); /*插入数据*/已创建 1 行。SQL> insert into users values('李四',24);已创建 1 行。SQL> select * from users; /*查询*/NAME AGE-------------------- ----------张三 22李四 24SQL> conn zhangsan/123456;已连接。SQL> select * from sys.users; /*新建的用户没有查询 sys 表的权限*/select * from sys.users*第 1 行出现错误:ORA-00942: 表或视图不存在SQL> conn / as sysdba;已连接。SQL> grant select on users to zhangsan; /*登录到 sys 给 zhangsan 授权让 zhangsan 可以查看 sys 下的 users 表*/授权成功。SQL> conn zhangsan/123456;已连接。SQL> select * from sys.users; /* 登录到 zhangsan 下查看 users 表*/NAME AGE-------------------- ----------张三 22王五 24SQL> update sys.users set name='张小三' where name='张三'; /*这时如果想要更新 sys.users 中的数据，会提示 "ORA-01031: 权限不足 " 。因为 sys 只给了 zhangsan 查看的权利，如果仍然想更新，要到 sys 下进行授权*/update sys.users set name='张小三' where name='张三'*第 1 行出现错误:ORA-01031: 权限不足SQL> conn / as sysdba已连接。SQL> grant update on users to zhangsan; /*登录到 sys 给 zhangsan 授权让 zhangsan 可以更新 sys 下的 users 表*/授权成功。SQL> conn zhangsan/123456;已连接。SQL> update sys.users set name='张小三' where name='张三';已更新 1 行。SQL> select * from sys.users;NAME AGE-------------------- ----------张小三 22王五 24SQL> revoke resource from zhangsan; /*登陆到 sys 下回收 resource 权限*/撤销成功。SQL> revoke select on users from zhangsan; /*登陆到 sys 下回收 select 权限*/撤销成功。SQL> conn zhangsan/123456;已连接。SQL> select * from sys.users; /* 这时 sys 就不能再查询 sys.users 的数据了*/select * from sys.users*第 1 行出现错误:ORA-00942: 表或视图不存在

使用 profile 管理用户口令

profile 是口令限制，资源限制的命令集合。当建立数据库时， Oracle 会自动建立名称为 default 的 profile。当建立用户没有制定 profile 选项，那 Oracle 就会将 default 分配给用户。

帐号锁定

指定登录时最多可以输入密码的次数，也可以指定用户锁定的时间，以天为单位。一般用 dba 的身份去执行命令。

指定用户 huang 最多只能尝试三次登录，锁定时间为 2 天。

SQL> conn / as sysdba;已连接。SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;配置文件已创建SQL> alter user huang profile lock_account;用户已更改。

按 CTRL + C 退出来验证账号锁定

C:\Users\Administrator>sqlplusSQL*Plus: Release 11.1.0.7.0 - Production on 星期三 10月 30 11:13:48 2019Copyright (c) 1982, 2008, Oracle. All rights reserved.请输入用户名: huang输入口令:ERROR:ORA-01017: 用户名/口令无效; 登录被拒绝请输入用户名: huang输入口令:ERROR:ORA-01017: 用户名/口令无效; 登录被拒绝请输入用户名: huang输入口令:ERROR:ORA-28000: 帐户已被锁定SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus

账户解锁

SQL> alter user huang account unlock;用户已更改。SQL> conn huang;输入口令:已连接。

终止口令

为了让用户定期修改密码，可以使用终止口令的指令完成，同样这个命令也要 dba 身份来操作。

给 huang 创建一个 profile 文件，要求该用户每隔 10 天要修改登录密码，宽限期 2 天

SQL> create profile huang limit password_life_time 10 password_grace_time 2;配置文件已创建SQL> alter user huang profile huang;用户已更改。

解锁方式同上

口令历史

如果希望用户在修改密码时，不能使用以前用过的密码，可以使用口令历史，这样 Oracle 就会将口令修改的信息存放在数据字典中，这样当用户修改密码时， Oracle 就会对新密码与就得进行对比，如果一样提示用户重新输入。

SQL> create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10;配置文件已创建SQL> alter user huang profile password_history;用户已更改。SQL> alter user huang identified by 12345678;用户已更改。SQL> alter user huang identified by 12345678;alter user huang identified by 12345678*第 1 行出现错误:ORA-28007: 无法重新使用口令

password_reuse_time 10 表示 10 天后口令可重复使用。

删除 profile

SQL> drop profile password_history cascade;配置文件已删除。

cascade 表示如果已经将 profile分 配给某个用户时，仍要删除 profile，就要加上 cascade。

Oracle 表的管理

表名和列的命名规则

• 必须以字母开头
• 长度不能超过 30 个字符
• 不能使用 Oracle 的保留字
• 只能使用如下字符 A-Z ， a-z ， 0-9 ， $ , # 等

Oracle 支持的数据类型

字符型

char 定长，最长2000字符

例如： char(10) 存储内容为“小黄”时，前4个字符放‘小黄’，后六位由空格补齐

优点是：效率高，查询速率快。如身份证的字段可以设置成 char(18)

varchar2 变长最大 4000 字符( Oracle 推荐使用)

varchar2(10) 存储内容为“小黄”时， Oracle 分配 4 个字符

clob(character large object) 字符型大对象，最大 4G

数字类型

number 范围 -10 的 38 次方到 10 的 38 次方，可以是整数，也可以是小数

number(5，2) 表示一个小数有5位有效数字， 2 位是小数

例如：定义一个范围在 -999.99-999.99 的数字可以用 number(5，2)，定义一个范围在 -99999-99999 可以用 number(5)

日期类型

date 包含年月日和时分秒

timestamp Oracle 对 date 类型的扩展，可以精确到毫秒。

图片类型

blob 二进制数据，可以存放图片，音频，视频最大 4G ，这个类型允许我们将大文件存储进数据库，但是一般在数据库里，存放的应该是这些文件的路径，如果对安全性有要求，可以将文件放入数据库(一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放的)。

建表语句

建表

• 学生表

create table student( /*表名*/id number(4), /*学号*/name varchar2(20), /*姓名*/sex char(2), /*性别*/birthday date, /*出生日期*/sal number(6,2) /*奖学金*/);SQL> desc student; /*查看表结构*/名称 是否为空? 类型----------------------------------------- -------- ----------------------------ID NUMBER(4)NAME VARCHAR2(20 CHAR)SEX CHAR(2 CHAR)BIRTHDAY DATESAL NUMBER(6,2)

• 班级表

create table class(c_id number(2),c_name varchar2(40));SQL> desc class;名称 是否为空? 类型----------------------------------------- -------- ----------------------------C_ID NUMBER(2)C_NAME VARCHAR2(40 CHAR)

修改表

SQL> alter table student add (c_id number(2)); /*添加字段*/表已更改。SQL> alter table student modify (name varchar2(50)); /*修改字段的长度*/表已更改。SQL> alter table student modify (name char(20)); /*修改字段的类型(表中不能有数据)*/表已更改。SQL> alter table student rename column name to s_name; /*修改字段的名字(表中不能有数据)*/表已更改。SQL> alter table student drop column sex; /*删除一个字段(慎重使用)*/表已更改。SQL> rename student to stu; /*修改表的名字*/表已重命名。SQL> desc stu;名称 是否为空? 类型----------------------------------------- -------- ----------------------------ID NUMBER(4)S_NAME CHAR(20 CHAR)BIRTHDAY DATESAL NUMBER(6,2)C_ID NUMBER(2)SQL> drop table stu; /*删除表*/表已删除。SQL> desc stu;ERROR:ORA-04043: 对象 stu 不存在

操作表

使用 student 表

SQL> insert into student values(1,'张三','男','01-1月-19',8888.88); /*添加数据，所有字段必须都插入， Oracle 中默认的日期格式‘DD-MON-YY’ (日-月-年)*/已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY---------- ---------------------------------------- ---- --------------SAL----------1 张三 男 01-1月 -198888.88SQL> set linesize 300; /*设置行的宽度，这样就好看多了*/SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- -------------- ----------1 张三 男 01-1月 -19 8888.88SQL> alter session set nls_date_format='YYYY-MM-DD'; /*修改日期的默认格式。注意，这种修改方法，只对当前会话有效。而不是当前的 sql*plus 窗口。即如果你这样修改之后，又使用 connect 命令以其他用户连接到数据库或者是连接到其他的数据库，则这个日期格式就失效了，又恢复到缺省的日期格式。要想永久改变日期输入格式是需要改注册表的，还有一个方法是使用函数。*/会话已更改。SQL> insert into student values(2,'李四','男','2019-01-02',8888.88);已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张三 男 2019-01-01 8888.882 李四 男 2019-01-02 8888.88SQL> insert into student(id,name) values(3,'王五'); /*插入部分字段,前提是未插入的字段允许为 null */已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张三 男 2019-01-01 8888.882 李四 男 2019-01-02 8888.883 王五SQL> insert into student(id,name,sex) values(4,null,null); /*插入空值*/已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张三 男 2019-01-01 8888.882 李四 男 2019-01-02 8888.883 王五4SQL> select * from student where name=null; /*查询 name 为空的一条记录，错误的做法*/未选定行SQL> select * from student where name is null; /*查询 name 为空的一条记录，正确的做法*/ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------4SQL> select * from student where name is not null; /*查询 name 不为空的一条记录*/ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张三 男 2019-01-01 8888.882 李四 男 2019-01-02 8888.883 王五SQL> update student set sex='女' where id='1'; /*修改一个字段*/已更新 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张三 女 2019-01-01 8888.882 李四 男 2019-01-02 8888.883 王五4SQL> update student set sex='女',name='李小四' where id='2'; /*修改多个字段*/已更新 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张三 女 2019-01-01 8888.882 李小四 女 2019-01-02 8888.883 王五4SQL> update student set name='赵六' where name is null; /*修改含有 null 值的字段*/已更新 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张三 女 2019-01-01 8888.882 李小四 女 2019-01-02 8888.883 王五4 赵六SQL> insert into student values(1,'张三','女','2019-01-01',8888.88); /*增加一条重复的记录*/已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张三 女 2019-01-01 8888.882 李小四 女 2019-01-02 8888.883 王五4 赵六1 张三 女 2019-01-01 8888.88SQL> select distinct id,name from student; /*取消重复行，查询时在 select 后面加上 distinct 即可将重复数据略去*/ID NAME---------- ----------------------------------------3 王五2 李小四1 张三4 赵六SQL> delete from student where id='4'; /*删除数据，删除一条记录*/已删除 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张三 女 2019-01-01 8888.882 李小四 女 2019-01-02 8888.883 王五1 张三 女 2019-01-01 8888.88SQL> delete from student; /*删除所有记录，表结构还在，会记录日志，这种删除是可以恢复的，速度会稍慢*/已删除4行。SQL> select * from student;未选定行SQL> truncate table student; /*删除所有记录，表结构还在，不记录日记，所有这种删除无法找回数据，但是速度很快*/表被截断。SQL> desc student;名称 是否为空? 类型----------------------------------------- -------- ----------------------------ID NUMBER(4)NAME VARCHAR2(20 CHAR)SEX CHAR(2 CHAR)BIRTHDAY DATESAL NUMBER(6,2)SQL> drop table student; /*删除表的结构和数据*/表已删除。SQL> desc student;ERROR:ORA-04043: 对象 student 不存在SQL> create table student( /*表名*/ /*恢复数据，用 delete from student 时数据可恢，使用 student 表*/2 id number(4), /*学号*/3 name varchar2(20), /*姓名*/4 sex char(2), /*性别*/5 birthday date, /*出生日期*/6 sal number(6,2) /*奖学金*/7 );表已创建。SQL> insert into student values(1,'张小三','女','2019-01-01',8888.88); /*插入数据*/已创建 1 行。SQL> insert into student values(2,'李小四','女','2019-01-01',8888.88);已创建 1 行。SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张小三 女 2019-01-01 8888.882 李小四 女 2019-01-01 8888.88SQL> savepoint sp; /*首先要设置一个保存点*/保存点已创建。SQL> delete from student; /*删除数据*/已删除2行。SQL> select * from student; /*查询验证数据是否被删掉*/未选定行SQL> rollback to sp; /*回滚数据*/回退已完成。SQL> select * from student; /*查询验证数据回滚是否成功，可以设置多个保存点，但是如果不做处理，新的保存点会默认覆盖前一个保存点*/ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- ---------- ----------1 张小三 女 2019-01-01 8888.882 李小四 女 2019-01-01 8888.88

Oracle 的基本查询

PL/SQL 软件中两个命令

SQL> clear /*清屏命令*/SQL> set timing on; /*打开显示操作时间*/SQL> select * from student;ID NAME SEX BIRTHDAY SAL---------- ---------------------------------------- ---- -------------- ----------1 张小三 女 01-1月 -19 8888.882 李小四 女 01-1月 -19 8888.88已用时间: 00: 00: 00.00 /*这里显示操作时间*/SQL> set timing off; /*关闭操作时间*/SQL> select count(*) from student; /*查询所有记录数*/COUNT(*)----------2

注意
Oracle 的字段不区分大小写，实体区分大小写。

表基本查询

scott 用户存在的几张表 ( emp ， dept )，本次实例采用 emp 和 dept 表。

使用算术表达式

显示每个雇员的月收入

SQL> select ename "姓名",sal 月工资 from emp;姓名 月工资-------------------- ----------SMITH 800ALLEN 1600WARD 1250JONES 2975MARTIN 1250BLAKE 2850CLARK 2450SCOTT 3000KING 5000TURNER 1500ADAMS 1100姓名 月工资-------------------- ----------JAMES 950FORD 3000MILLER 1300已选择14行。

显示每个雇员的年收入

SQL> select ename "姓名",sal * 12 as "年收入" from emp;姓名 年收入-------------------- ----------SMITH 9600ALLEN 19200WARD 15000JONES 35700MARTIN 15000BLAKE 34200CLARK 29400SCOTT 36000KING 60000TURNER 18000ADAMS 13200姓名 年收入-------------------- ----------JAMES 11400FORD 36000MILLER 15600

这里的中文最好用引号引上,尽量不要用中文。

如果计算表达式中有一个 null 值，那么计算结果就为 null ，如何处理 null 值？

SQL> select sal * 13 + nvl(comm,0) * 13 "年薪",ename,comm from emp;年薪 ENAME COMM---------- -------------------- ----------10400 SMITH24700 ALLEN 30022750 WARD 50038675 JONES34450 MARTIN 140037050 BLAKE31850 CLARK39000 SCOTT65000 KING19500 TURNER 014300 ADAMS年薪 ENAME COMM---------- -------------------- ----------12350 JAMES39000 FORD16900 MILLER已选择14行。

nvl(comm,0) 的意思是：如果 comm 为 null ，那么按 0 计算，不是 0 按本身计算。

用 “||” 来连接字符串

SQL> select ename || ' is a ' || job from emp; /*这句话表示:姓名为 XX 是做 XX 工作的*/ENAME||'ISA'||JOB------------------------------------------------------------------------------------------------------SMITH is a CLERKALLEN is a SALESMANWARD is a SALESMANJONES is a MANAGERMARTIN is a SALESMANBLAKE is a MANAGERCLARK is a MANAGERSCOTT is a ANALYSTKING is a PRESIDENTTURNER is a SALESMANADAMS is a CLERKENAME||'ISA'||JOB------------------------------------------------------------------------------------------------------JAMES is a CLERKFORD is a ANALYSTMILLER is a CLERK已选择14行。

使用 where 字句

显示工资高于 3000 的员工

SQL> select ename,sal from emp where sal > 3000;ENAME SAL-------------------- ----------KING 5000

查找 1982-1-1 后入职的员工

SQL> select ename from emp where hiredate > '1982-1-1';ENAME--------------------SCOTTADAMSMILLER

查找工资在 2000-2500 之间的员工，并且显示员工的工资

SQL> select ename,sal from emp where sal >=2000 and sal <=2500;ENAME SAL-------------------- ----------CLARK 2450

使用 like 操作符

%：表示 0 到多个字符
_：表示任意单个字符

显示首字母为 S 的员工

SQL> select ename from emp where ename like 'S%';ENAME--------------------SMITHSCOTT

显示第三个字母为大写 O 的所有员工的姓名和工资

SQL> select ename,sal from emp where ename like '__O%'; /*注意: 两个 _ 符号*/ENAME SAL-------------------- ----------SCOTT 3000

在 where 条件中使用 in

显示 empno 为 7844 , 7839 , 123 , 456 的雇员情况

SQL> select * from emp where empno in(7844,7839,123,456);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7839 KING PRESIDENT 1981-11-17 5000 107844 TURNER SALESMAN 7698 1981-09-08 1500 0 30

这种查询效率很高

使用 is null 操作符

显示没有上级的雇员情况

SQL> select * from emp where mgr is null;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7839 KING PRESIDENT 1981-11-17 5000 10

使用逻辑操作符号

查询工资高于 500 或者是岗位为 MANAGER 的雇员，同时还要满足他们的姓名首字母为大写的 J

SQL> select * from emp where (sal >500 or job='MANAGER') and ename like 'J%';EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7566 JONES MANAGER 7839 1981-04-02 2975 207900 JAMES CLERK 7698 1981-12-03 950 30

这里的括号不可以忘记,否则条件就变了,因为 and 的优先级高于 or 。

使用 order by 子句

按照工资从高到低的顺序显示雇员和工资

SQL> select ename,sal from emp order by sal desc;ENAME SAL-------------------- ----------KING 5000FORD 3000SCOTT 3000JONES 2975BLAKE 2850CLARK 2450ALLEN 1600TURNER 1500MILLER 1300WARD 1250MARTIN 1250ENAME SAL-------------------- ----------ADAMS 1100JAMES 950SMITH 800已选择14行。

desc 为降序， asc 为升序(默认)

照部门号升序而雇员工资降序排列(知道某和部门最高工资的员工和最低工资的员工是谁)

SQL> select * from emp order by deptno asc,sal desc;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7839 KING PRESIDENT 1981-11-17 5000 107782 CLARK MANAGER 7839 1981-06-09 2450 107934 MILLER CLERK 7782 1982-01-23 1300 107788 SCOTT ANALYST 7566 1987-04-19 3000 207902 FORD ANALYST 7566 1981-12-03 3000 207566 JONES MANAGER 7839 1981-04-02 2975 207876 ADAMS CLERK 7788 1987-05-23 1100 207369 SMITH CLERK 7902 1980-12-17 800 207698 BLAKE MANAGER 7839 1981-05-01 2850 307499 ALLEN SALESMAN 7698 1981-02-20 1600 300 307844 TURNER SALESMAN 7698 1981-09-08 1500 0 30EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 307521 WARD SALESMAN 7698 1981-02-22 1250 500 307900 JAMES CLERK 7698 1981-12-03 950 30已选择14行。

使用列的别名排序

SQL> select ename,sal * 12 "年薪" from emp order by "年薪" asc;ENAME 年薪-------------------- ----------SMITH 9600JAMES 11400ADAMS 13200WARD 15000MARTIN 15000MILLER 15600TURNER 18000ALLEN 19200CLARK 29400BLAKE 34200JONES 35700ENAME 年薪-------------------- ----------SCOTT 36000FORD 36000KING 60000已选择14行。

Oracle 的复杂查询

在实际应用中经常需要执行复杂的数据统计,经常需要现实多张表的数据，所以经常要用到数据分组函数如 max() , min() , avg() , sum() , count() 等。

分组函数

显示所有员工中最高工资和最低工资

SQL> select max(sal),min(sal) from emp;MAX(SAL) MIN(SAL)---------- ----------5000 800

注意
如果列里面有一个分组函数，其它的都必须是分组函数， 否则就出错；如本例中不能写成：

SQL> select ename, sal from emp where sal = max(sal);select ename, sal from emp where sal = max(sal)*第 1 行出现错误:ORA-00934: 此处不允许使用分组函数

因为： max 是分组函数，而 ename 不是分组函数。

查询最高，最低工资的员工(利用子查询)

SQL> select ename,sal from emp where sal = (select max(sal) from emp);ENAME SAL-------------------- ----------KING 5000SQL> select ename,sal from emp where sal = (select min(sal) from emp);ENAME SAL-------------------- ----------SMITH 800

显示工资最高的员工的名字，工作岗位

SQL> select ename,job from emp where sal = (select max(sal) from emp);ENAME JOB-------------------- ------------------KING PRESIDENT

显示工资高于平均工资的员工信息

首先我们可以查询所有员工的平均工资

SQL> select avg(sal) from emp;AVG(SAL)----------2073.21429

然后再查询高于平均工资的员工信息

SQL> select * from emp where sal > 2073;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7566 JONES MANAGER 7839 1981-04-02 2975 207698 BLAKE MANAGER 7839 1981-05-01 2850 307782 CLARK MANAGER 7839 1981-06-09 2450 107788 SCOTT ANALYST 7566 1987-04-19 3000 207839 KING PRESIDENT 1981-11-17 5000 107902 FORD ANALYST 7566 1981-12-03 3000 20已选择6行。

当然也可以利用子查询

SQL> select * from emp where sal > (select avg(sal) from emp);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7566 JONES MANAGER 7839 1981-04-02 2975 207698 BLAKE MANAGER 7839 1981-05-01 2850 307782 CLARK MANAGER 7839 1981-06-09 2450 107788 SCOTT ANALYST 7566 1987-04-19 3000 207839 KING PRESIDENT 1981-11-17 5000 107902 FORD ANALYST 7566 1981-12-03 3000 20已选择6行。

如果这里不是 * 号而是 ename,sal 等字段与分组函数那么在语句的最后要加上 group by ename,sal…(与 select 后的字段一致)

SQL> select ename,sal from emp where sal > (select avg(sal) from emp) group by ename,sal;ENAME SAL-------------------- ----------JONES 2975SCOTT 3000KING 5000BLAKE 2850CLARK 2450FORD 3000已选择6行。

group by 和 having 子句

group by 用于对查询结果分组统计
having 子句用于限制分组显示结果

显示每个部门的平均工资和最高工资

SQL> select avg(sal),max(sal),deptno from emp group by deptno;AVG(SAL) MAX(SAL) DEPTNO---------- ---------- ----------1566.66667 2850 302175 3000 202916.66667 5000 10

分组字段依据必须出现在查询结果中，否则结果可读性太差。

显示每个部门的每种岗位的平均工资和最低工资

SQL> select avg(sal),min(sal),deptno,job from emp group by job,deptno;AVG(SAL) MIN(SAL) DEPTNO JOB---------- ---------- ---------- ------------------2975 2975 20 MANAGER5000 5000 10 PRESIDENT1300 1300 10 CLERK1400 1250 30 SALESMAN3000 3000 20 ANALYST2850 2850 30 MANAGER2450 2450 10 MANAGER950 950 30 CLERK950 800 20 CLERK已选择9行。

显示平均工资低于 2000 的部门号和它的平均工资与最高工资

SQL> select avg(sal),max(sal),deptno from emp group by deptno having avg(sal) < 2000;AVG(SAL) MAX(SAL) DEPTNO---------- ---------- ----------1566.66667 2850 30

对分组函数的总结

• 分组函数只能出现在选择列表, having , group by , order by 子句中
• 如果在select语句中同时包含有 group by , having , order by ,那么顺序为 group by , having , order by
• 在选择列中如果有列,表达式,和分组函数,那么这些列表达式必须有一个出现在 group by 子句中,否则出错 如:

select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal) > 2000; /*这里的deptno就一定要出现在group by中*/

多表查询

基于两个或两个以上的表或是视图的查询，查单表满足不了要求，如部门和员工的关系。

SQL> select a.ename,a.sal,b.dname from emp a,dept b where a.deptno = b.deptno;ENAME SAL DNAME-------------------- ---------- ----------------------------CLARK 2450 ACCOUNTINGKING 5000 ACCOUNTINGMILLER 1300 ACCOUNTINGJONES 2975 RESEARCHFORD 3000 RESEARCHADAMS 1100 RESEARCHSMITH 800 RESEARCHSCOTT 3000 RESEARCHWARD 1250 SALESTURNER 1500 SALESALLEN 1600 SALESENAME SAL DNAME-------------------- ---------- ----------------------------JAMES 950 SALESBLAKE 2850 SALESMARTIN 1250 SALES已选择14行。

如果不加 where 子句,就会产生笛卡尔集,所谓笛卡尔集,就是不加筛选,将所有的都查询出来。

结论：
两张表关联,至少需要一个条件排除笛卡尔集
三张表关联,至少需要二个条件排除笛卡尔集
以此类推:多表查询中,判断条件至少是表的个数 -1。

显示部门号为10的部门名,员工名和工资

SQL> select d.dname,e.ename,e.sal from emp e,dept d where e.deptno = d.deptno and d.deptno = 10;DNAME ENAME SAL---------------------------- -------------------- ----------ACCOUNTING CLARK 2450ACCOUNTING KING 5000ACCOUNTING MILLER 1300

显示各个员工的姓名,工资及工资的级别

SQL> select * from salgrade; /*先查看 salgrade 的表结构和记录*/GRADE LOSAL HISAL---------- ---------- ----------1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999SQL> select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;ENAME SAL GRADE-------------------- ---------- ----------SMITH 800 1JAMES 950 1ADAMS 1100 1WARD 1250 2MARTIN 1250 2MILLER 1300 2TURNER 1500 3ALLEN 1600 3CLARK 2450 4BLAKE 2850 4JONES 2975 4ENAME SAL GRADE-------------------- ---------- ----------SCOTT 3000 4FORD 3000 4KING 5000 5已选择14行。

这里用到了 between … and 子句,表示在 losal 和 hisal 之间。

显示雇员名,雇员工资及所在部门的名字,并部门排序

SQL> select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno order by e.deptno;ENAME SAL DNAME-------------------- ---------- ----------------------------CLARK 2450 ACCOUNTINGKING 5000 ACCOUNTINGMILLER 1300 ACCOUNTINGJONES 2975 RESEARCHFORD 3000 RESEARCHADAMS 1100 RESEARCHSMITH 800 RESEARCHSCOTT 3000 RESEARCHWARD 1250 SALESTURNER 1500 SALESALLEN 1600 SALESENAME SAL DNAME-------------------- ---------- ----------------------------JAMES 950 SALESBLAKE 2850 SALESMARTIN 1250 SALES已选择14行。

如果用 group by ,一定要把 e.deptno 放到查询列里面。

自连接

自连接是指在同一张表的连接查询。

显示某个员工的上级领导的姓名

SQL> select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename = 'FORD';ENAME ENAME-------------------- --------------------FORD JONES

根据 FORD 的名字找到 FORD 的 mgr 编号再根据这个编号找到 boss 的 empno,最后显示出来。

子查询

子查询:指嵌入在其他 SQL 语句中的 select 语句,也叫做嵌套查询。

单行子查询

指只返回一行数据的子查询语句。

显示与 SMITH 同一部门的所有员工

分两步:

查出 SMITH 所在部门

根据部门查出所有员工

SQL> select deptno from emp where ename = 'SMITH';DEPTNO----------20SQL> select * from emp where deptno = (select deptno from emp where ename = 'SMITH');EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH CLERK 7902 1980-12-17 800 207566 JONES MANAGER 7839 1981-04-02 2975 207788 SCOTT ANALYST 7566 1987-04-19 3000 207876 ADAMS CLERK 7788 1987-05-23 1100 207902 FORD ANALYST 7566 1981-12-03 3000 20

数据库在执行 SQL 是从左到右扫描的， 如果有括号的话，括号里面的先被优先执行。

多行子查询

返回多行数据的子查询

查询和部门 10 的工作相同的雇员的名字,岗位,工资,部门号

1.首先查出部门 10 的工作种类

SQL> select job from emp where deptno = 10;JOB------------------MANAGERPRESIDENTCLERK

发现有重复结果,所以在 job 前加上 distinct

SQL> select distinct job from emp where deptno = 10;JOB------------------CLERKPRESIDENTMANAGER

2.根据工作的种类查询

SQL> select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno = 10);ENAME JOB SAL DEPTNO-------------------- ------------------ ---------- ----------CLARK MANAGER 2450 10BLAKE MANAGER 2850 30JONES MANAGER 2975 20KING PRESIDENT 5000 10MILLER CLERK 1300 10JAMES CLERK 950 30ADAMS CLERK 1100 20SMITH CLERK 800 20已选择8行。

注意这里 job 之后用的是" in “而非” = ",因为等号 = 是一对一的。

all 操作符

显示工资比部门 30 的所有员工的工资高的员工的姓名,工资和部门号

SQL> select ename,sal,deptno from emp where sal > all (select sal from emp where deptno = 30);ENAME SAL DEPTNO-------------------- ---------- ----------JONES 2975 20SCOTT 3000 20FORD 3000 20KING 5000 10

也可以使用 max 方法

SQL> select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno = 30);ENAME SAL DEPTNO-------------------- ---------- ----------JONES 2975 20SCOTT 3000 20KING 5000 10FORD 3000 20

max 方法的效率较高,原因是使用 all 操作符时,要和子查询所得结果逐一比较而使用 max 只需和 sal 中的最大值比较,减少了比较的次数,缩短了时间。数据量较大是会比较明显,数据量较小基本看不出来。

any 操作符

显示工资比部门 30 的任意一个员工的工资高的员工的姓名,工资和部门号

SQL> select ename,sal,deptno from emp where sal > any (select sal from emp where deptno = 30);ENAME SAL DEPTNO-------------------- ---------- ----------KING 5000 10FORD 3000 20SCOTT 3000 20JONES 2975 20BLAKE 2850 30CLARK 2450 10ALLEN 1600 30TURNER 1500 30MILLER 1300 10WARD 1250 30MARTIN 1250 30ENAME SAL DEPTNO-------------------- ---------- ----------ADAMS 1100 20已选择12行。

也可以使用 min 方法

SQL> select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno = 30);ENAME SAL DEPTNO-------------------- ---------- ----------ALLEN 1600 30WARD 1250 30JONES 2975 20MARTIN 1250 30BLAKE 2850 30CLARK 2450 10SCOTT 3000 20KING 5000 10TURNER 1500 30ADAMS 1100 20FORD 3000 20ENAME SAL DEPTNO-------------------- ---------- ----------MILLER 1300 10已选择12行。

原理同上

多列子查询

单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据， 都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。

查询与 SMITH 的部门和岗位完全相同的所有雇员

1.查询出 SMITH 的部门号,岗位

SQL> select deptno,job from emp where ename = 'SMITH';DEPTNO JOB---------- ------------------20 CLERK

2.显示结果

SQL> select * from emp where (deptno,job) = (select deptno,job from emp where ename = 'SMITH');EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH CLERK 7902 1980-12-17 800 207876 ADAMS CLERK 7788 1987-05-23 1100 20

在 from 子句中使用子查询

显示高于自己部门的平均工资员工信息

1.查询各个部门的平均工资和部门号

SQL> select avg(sal),deptno from emp group by deptno;AVG(SAL) DEPTNO---------- ----------1566.66667 302175 202916.66667 10

2.把上面的查询看作是一张子表

SQL> select e.ename,e.deptno,e.sal,ds.mysal from emp e,(select deptno, avg(sal) mysal from emp group by deptno) ds where e.deptno = ds.deptno and e.sal > ds.mysal;ENAME DEPTNO SAL MYSAL-------------------- ---------- ---------- ----------ALLEN 30 1600 1566.66667JONES 20 2975 2175BLAKE 30 2850 1566.66667SCOTT 20 3000 2175KING 10 5000 2916.66667FORD 20 3000 2175已选择6行。

将这两个表做关联查询,当在 from 子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图,当在 from 子句中使用子查询时,必须为子查询指定别名。

注意：别名不能用 as ,如:

SQL> select e.ename,e.deptno,e.sal,ds.mysal from emp e,(select deptno, avg(sal) mysal from emp group by deptno) as ds where e.deptno = ds.deptno and e.sal > ds.mysal;select e.ename,e.deptno,e.sal,ds.mysal from emp e,(select deptno, avg(sal) mysal from emp group by deptno) as ds where e.deptno = ds.deptno and e.sal > ds.mysal*第 1 行出现错误:ORA-00933: SQL 命令未正确结束

在 ds 前不能加 as ,否则会报错 (给表取别名的时候，不能加 as ;但是给列取别名，是可以加 as 的)。

Oracle 的分页

Oracle 的分页一共有三种方式:

根据 rowid 来分（效率最好）

按分析函数来分（效率次之）

按 rownum 来分（效率最差）

下面最主要介绍第三种：按 rownum 来分

SQL> select * from (select a.*,rownum rn from (select * from emp) a where rownum <= 15) where rn > 10;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- ----------7876 ADAMS CLERK 7788 1987-05-23 1100 20 117900 JAMES CLERK 7698 1981-12-03 950 30 127902 FORD ANALYST 7566 1981-12-03 3000 20 137934 MILLER CLERK 7782 1982-01-23 1300 10 14已用时间: 00: 00: 00.02

这条语句是用来将 11-15 条记录提取出来,拆分这条语句:

1.将所有想要的结果查询出来

SQL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7369 SMITH CLERK 7902 1980-12-17 800 207499 ALLEN SALESMAN 7698 1981-02-20 1600 300 307521 WARD SALESMAN 7698 1981-02-22 1250 500 307566 JONES MANAGER 7839 1981-04-02 2975 207654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 307698 BLAKE MANAGER 7839 1981-05-01 2850 307782 CLARK MANAGER 7839 1981-06-09 2450 107788 SCOTT ANALYST 7566 1987-04-19 3000 207839 KING PRESIDENT 1981-11-17 5000 107844 TURNER SALESMAN 7698 1981-09-08 1500 0 307876 ADAMS CLERK 7788 1987-05-23 1100 20EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------7900 JAMES CLERK 7698 1981-12-03 950 307902 FORD ANALYST 7566 1981-12-03 3000 207934 MILLER CLERK 7782 1982-01-23 1300 10已选择14行。已用时间: 00: 00: 00.08

2.将上一步的结果作为一个视图,给每一条记录加上一个 rn 编号并将前 15 条记录查询出来;其中 rownum 为 Oracle 的关键字,且在第一次在查询字段中使用 rownum 时,如果有条件限制在 where 子句中也要用 rownum,不可以用 rn。

SQL> select a.*,rownum rn from (select * from emp) a where rownum <= 15;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- ----------7369 SMITH CLERK 7902 1980-12-17 800 20 17499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 27521 WARD SALESMAN 7698 1981-02-22 1250 500 30 37566 JONES MANAGER 7839 1981-04-02 2975 20 47654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 57698 BLAKE MANAGER 7839 1981-05-01 2850 30 67782 CLARK MANAGER 7839 1981-06-09 2450 10 77788 SCOTT ANALYST 7566 1987-04-19 3000 20 87839 KING PRESIDENT 1981-11-17 5000 10 97844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 107876 ADAMS CLERK 7788 1987-05-23 1100 20 11EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- ----------7900 JAMES CLERK 7698 1981-12-03 950 30 127902 FORD ANALYST 7566 1981-12-03 3000 20 137934 MILLER CLERK 7782 1982-01-23 1300 10 14已选择14行。已用时间: 00: 00: 00.03

3.将前 15 条数据作为一个视图,提取出 11-15 条。

SQL> select * from (select a.*,rownum rn from (select * from emp) a where rownum <=15) where rn > 10;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- ----------7876 ADAMS CLERK 7788 1987-05-23 1100 20 117900 JAMES CLERK 7698 1981-12-03 950 30 127902 FORD ANALYST 7566 1981-12-03 3000 20 137934 MILLER CLERK 7782 1982-01-23 1300 10 14已用时间: 00: 00: 00.01

在 Java 程序中只需要替换 15 和 10 这两个数字就可以实现分页了。

用查询结果创建新表

这个命令是一种快捷的建表方法

SQL> select * from (select a.*,rownum rn from (select * from emp) a where rownum <=15) where rn > 10;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- ----------7876 ADAMS CLERK 7788 1987-05-23 1100 20 117900 JAMES CLERK 7698 1981-12-03 950 30 127902 FORD ANALYST 7566 1981-12-03 3000 20 137934 MILLER CLERK 7782 1982-01-23 1300 10 14已用时间: 00: 00: 00.01SQL> create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;表已创建。已用时间: 00: 00: 00.64

创建好之后,我们可以查看一下表结构

SQL> desc mytable;名称 是否为空? 类型----------------------------------------------------- -------- ------------------------------------ID NUMBER(4)NAME VARCHAR2(10)SAL NUMBER(7,2)JOB VARCHAR2(9)DEPTNO NUMBER(2)SQL> desc emp;名称 是否为空? 类型----------------------------------------------------- -------- ------------------------------------EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9)MGR NUMBER(4)HIREDATE DATESAL NUMBER(7,2)COMM NUMBER(7,2)DEPTNO NUMBER(2)

可以看出，新建表的字段类型和查询表的一样。

合并查询

有时在实际应用中，为了合并多个 select 语句的结果，可以使用集合操作符号 union, union all, intersect (交集), minus (差集)

多用于数据量比较大的数据局库，运行速度快。

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

SQL> select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job = 'MANAGER';ENAME SAL JOB-------------------- ---------- ------------------BLAKE 2850 MANAGERCLARK 2450 MANAGERFORD 3000 ANALYSTJONES 2975 MANAGERKING 5000 PRESIDENTSCOTT 3000 ANALYST已选择6行。已用时间: 00: 00: 00.00

union all

该操作符与 union 相似，但是它不会取消重复行，而且不会排序。

SQL> select ename,sal,job from emp where sal > 2500 union all select ename,sal,job from emp where job = 'MANAGER';ENAME SAL JOB-------------------- ---------- ------------------JONES 2975 MANAGERBLAKE 2850 MANAGERSCOTT 3000 ANALYSTKING 5000 PRESIDENTFORD 3000 ANALYSTJONES 2975 MANAGERBLAKE 2850 MANAGERCLARK 2450 MANAGER已选择8行。已用时间: 00: 00: 00.01

intersect

使用该操作符用于取得两个结果集的交集。

SQL> select ename,sal,job from emp where sal > 2500 intersect select ename,sal,job from emp where job = 'MANAGER';ENAME SAL JOB-------------------- ---------- ------------------BLAKE 2850 MANAGERJONES 2975 MANAGER已用时间: 00: 00: 00.00

minus

使用改操作符用于取得两个结果集的差集， 他只会显示存在第一个集合中， 而不存在第二个集合中的数据。

SQL> select ename,sal,job from emp where sal > 2500 minus select ename,sal,job from emp where job = 'MANAGER';ENAME SAL JOB-------------------- ---------- ------------------FORD 3000 ANALYSTKING 5000 PRESIDENTSCOTT 3000 ANALYST已用时间: 00: 00: 00.00

集合操作要比 and， or 效率高很多。

操作数据

使用特定的格式插入日期值。

使用to_date函数

插入带有日期的表,并按照年月日格式插入

SQL> insert into emp values(9999,'huang','PERSIDENT',null,to_date('1997-01-01','YYYY-MM-DD'),800,1000,10);已创建 1 行。已用时间: 00: 00: 00.06

使用 to_date 函数可以插入任意形式的日期

使用子查询插入数据

一条 insert 语句可以插入大量的数据，当处理行迁移或者装载外部表的数据到数据库时, 可以使用子查询插入数据。

SQL> insert into mytable(id,name,deptno) select empno,ename,deptno from emp where deptno = 10;已创建4行。已用时间: 00: 00: 00.02SQL> select * from mytable;ID NAME SAL JOB DEPTNO---------- -------------------- ---------- ------------------ ----------7369 SMITH 800 CLERK 207499 ALLEN 1600 SALESMAN 307521 WARD 1250 SALESMAN 307566 JONES 2975 MANAGER 207654 MARTIN 1250 SALESMAN 307698 BLAKE 2850 MANAGER 307782 CLARK 2450 MANAGER 107788 SCOTT 3000 ANALYST 207839 KING 5000 PRESIDENT 107844 TURNER 1500 SALESMAN 307876 ADAMS 1100 CLERK 20ID NAME SAL JOB DEPTNO---------- -------------------- ---------- ------------------ ----------7900 JAMES 950 CLERK 307902 FORD 3000 ANALYST 207934 MILLER 1300 CLERK 107782 CLARK 107839 KING 107934 MILLER 109999 huang 10已选择18行。已用时间: 00: 00: 00.02

后面 4 行为新插入的数据。

使用子查询更新数据

希望员工 huang 的岗位、工资与 SCOOT 一样

SQL> update mytable set(job,sal) = (select job,sal from mytable where name='SCOTT') where name = 'huang';已更新 1 行。已用时间: 00: 00: 00.00SQL> select * from mytable;ID NAME SAL JOB DEPTNO---------- -------------------- ---------- ------------------ ----------7369 SMITH 800 CLERK 207499 ALLEN 1600 SALESMAN 307521 WARD 1250 SALESMAN 307566 JONES 2975 MANAGER 207654 MARTIN 1250 SALESMAN 307698 BLAKE 2850 MANAGER 307782 CLARK 2450 MANAGER 107788 SCOTT 3000 ANALYST 207839 KING 5000 PRESIDENT 107844 TURNER 1500 SALESMAN 307876 ADAMS 1100 CLERK 20ID NAME SAL JOB DEPTNO---------- -------------------- ---------- ------------------ ----------7900 JAMES 950 CLERK 307902 FORD 3000 ANALYST 207934 MILLER 1300 CLERK 107782 CLARK 107839 KING 107934 MILLER 109999 huang 3000 ANALYST 10已选择18行。已用时间: 00: 00: 00.02

name 的值要用大写, Oracle 对值的大小写是敏感的。