概述
本文是本人观看韩顺平老师的玩转Oracle视频而写的笔记，特此感谢韩顺平老师。
一、Oracle基本知识
安装
oracle安装时会自动生成sys用户和system用户
（1）sys用户是 超级用户 ，具有最高权限，具有sysdba（超级管理员）角色，有create database的权限等。默认密码：manager
（2）system用户是 管理操作员，权限仅次于sys用户，具有sysoper（系统操作员）角色，没有create database的权限。默认密码：change_on_install
（3）一般对数据维护的操作，用system用户足矣
数据库分类：
小型数据库（负载小100人以内；成本最低；安全性不高）:access、foxbase
中型数据库（负载日访问量5000-15000；成本万元以内；安全性较高）：MySQL、SQL、server、informix
大型数据库（负载可以处理海量数据；成本高、安全性能高）：Sybase、Oracle、db2
负载量：Sybase<Oracle<db2
用户与方案的关系：
Oracle是以方案的方式来管理数据对象的，用户被创建后，Oracle会自动给其创建一个方案，方案名称与用户名称一样，方案中有很多数据对象
二、Oracle基本使用
连接操作命令
1、切换身份登入命令 conn【ect】
用法：conn 身份/密码 @ 网络服务名（数据库名字）[as sysdba/sysoper]；当特权用户身份连接时，必须带上as sysdba（权限最高：超级用户）或 as sysoper（权限次之：管理身份）
SQL> conn system/Ww18315831096
已连接。
SQL> connect sys/Ww18315831096 as sysdba
已连接。
2、断开与当前数据库连接 disc【onnect】
SQL> disc
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
3、断开与数据库的连接同时退出SQL plus： exit
4、显示当前用户名 show user
SQL> show user
USER 为 "SYSTEM"
5、修改用户密码：passw【ord】
SQL> password
更改 SCOTT 的口令
旧口令:
新口令:
重新键入新口令:
SP2-0650: 新的口令不匹配
口令未更改
注：如果想修改其他用户的密码，需要syssystem登陆
6、解锁scott用户：alter（改变更改） user scott account （账户账目）unlock（开启解锁）;
7、设置scott密码生效：alter user scott identified（认定指认） by tiger;
请输入用户名: system
输入口令:
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter user scott account unlock;
用户已更改。
SQL> alter user scott identified by tiger;
用户已更改。
文件操作命令
1、运行sql脚本：start 或@文件路径及文件名字
SQL> start E:computerJavaoracletestjiaobenaa01
2、编辑指定的SQL脚本：edit 文件路径及文件名字
SQL> edit E:computerJavaoracletestjiaobenaa01
3、将sql plus屏幕上的内容输出至指定文件中去
用法:
spool d:b.sql //文件建立
并输入
spool off //之后文件里就有spool d:b.spl之后屏幕上所有内容
SQL> spool E:computerJavaoracletestjiaobenaa04.sql;
SQL> start E:computerJavaoracletestjiaobenaa01
ENAME SAL
---------- ----------
小红2 4000
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 800
KING 5000
TURNER 1500
ENAME SAL
---------- ----------
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
小红 78.9
小红2 78.9
已选择17行。
SQL> spool off;
交互式命令
1、替代变量 ：& （该变量在执行时，需要用户输入）
SQL> select *from emp where ename='&name';
输入 name 的值: JAMES
原值 1: select *from emp where ename='&name'
新值 1: select *from emp where ename='JAMES'
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
2、编辑指定的SQL脚本：edit 文件路径及文件名字
edit +文件路径+文件名
SQL> edit C:Users王振DesktopJAVA开发数据库安装jiaobenaa01.sql;
3、将sql plus屏幕上的内容输出至指定文件中去:spool 同上
显示和设置环境变量
概述：用来控制输出的各种格式，set show 如果希望永久的保存相关的设置，可以去修改 glogin.sql脚本
1、linesize
说明：设置显示行的宽度，默认80个字符
sql>show linesize //显示一行有多少字符
sql>set linesize 20 //设置一行可以显示多少字符
SQL> show linesize
linesize 80
SQL> set linesize 20
SQL> show linesize
linesize 20
2、pagesize
说明：设置每页显示的行数目
用法同上
SQL> show pagesize;
pagesize 14
SQL> set pagesize 2
SQL> show pagesize
pagesize 2
三、Oracle用户管理
1、创建用户
概述：在Oracle中要创建一个新用户使用 create user语句，一般具有dba（数据管理员）的权限才能使用。
用法;create user 新用户名 identified by 密码
普通用户：
SQL> show user;
USER 为 "SCOTT"
SQL> create user xiaoming identified by m123;
create user xiaoming identified by m123
*
第 1 行出现错误:
ORA-01031: 权限不足
具有dba权限的用户
SQL> conn system/Ww18315831096
已连接。
SQL> create user xiaoming identified by m123;
用户已创建。
SQL> conn system/Ww18315831096
已连接。
SQL> create user testuser indentified by testuser;
create user testuser indentified by testuser
*
第 1 行出现错误:
ORA-00922: 选项缺失或无效
SQL> create user testuser identified by testuser;
用户已创建。
SQL> conn testuser/testuser;
ERROR:
ORA-01045: user TESTUSER lacks CREATE SESSION privilege; logon denied
警告: 您不再连接到 ORACLE。
SQL> conn system /Ww18315831096
已连接。
SQL> grant connect,resource to testuser;
授权成功。
SQL> connect testuser/testuser;
已连接。
2、修改用户密码
用法 ：passw【ord】【用户名】
SQL> password
更改 SCOTT 的口令
旧口令:
新口令:
重新键入新口令:
SP2-0650: 新的口令不匹配
口令未更改
注：如果想修改其他用户的密码，需要syssystem登陆
3、删除用户
概述：一般以dba的身份去删除某个用户，如果其他用户去删除用户须具有drop（删除） user的权限
用法：drop user 用户名【cascade】
注：若果被删除的用户，已经创建了表，那么就需要在删除时带一个参数 cascade 删除其所有数据
SQL> drop user xiaomig;
用户已删除。
4、用户管理的综合案例
概述：创建的新用户是没有任何权限的，没有创建表，甚至连登入数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令 revoke。
（1）赋予权限/角色grant
<1>希望xiaoming’可以去连接到数据库
SQL> show user
USER 为 "SYSTEM"
SQL> grant （授予）connect to xiaoming;
授权成功。
SQL> conn xiaoming/mm123
已连接。
<2>希望xiaoming这个用户可以去查询emp表
例子1：
1、建立test表
SQL> grant resource to xiaoming;
授权成功。
SQL> connect xiaoming/mm123
已连接。
SQL> create table test1(userId varchar2(30),username varchar2(30));
表已创建。
SQL> select *from test1;
未选定行
SQL> desc test;
名称 是否为空? 类型
---
USERID VARCHAR2(30)
USERNAME
2、希望xiaoming这个用户可以去查询scott的emp表
grant select on emp to xiaoming
这里涉及对象权限的问题
SQL> conn scott/tiger
已连接。
SQL> grant select on emp to xiaoming;
授权成功。
SQL> connect xiaoming/mm123;
已连接。
SQL> select *from scott.emp;
.............
已选择14行。
3、希望xiaoming这个用户可以去修改scott的emp表
grant update（修改） on emp to xiaoming
4、希望xiaoming这个用户可以去修改、删除、查询、添加scott的emp表
grant all on emp to xiaoming
5、希望xiaoming可以把此权限赋予别人
——如果是对象权限，就带上with grant option：
grant select on emp to xiaoming with grant option；
SQL> connect scott/tiger
已连接。
SQL> grant select on emp to xiaoming with grant option;
授权成功。
SQL> grant select on scott.emp to xiaohong;
授权成功。
——如果是系统权限：with admin option：
grant connect xiaoming with admin option；
（2）收回权限/角色revoke
scott 想收回xiaoing‘对emp表的查询权限revoke select on emp from xiaoming
SQL> conn scott/tiger;
已连接。
SQL> revoke select on emp from xiaoming;
撤销成功。
SQL> connect xiaoming/mm123
已连接。
SQL> select *from scott.emp;
select *from scott.emp
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
scott-------->xiaoming----------->xiaohong
注：
1、对象权限：如果scott把xiaoming 对emp表的权限收回，则xiaohong的权限也收回
2、系统权限：收回xiaoming的权限，但是xuiaohong的权限不会收回
SQL> grant connect to testuser with admin option;
授权成功。
SQL> create user testUser1 identified by testuser1;
用户已创建。
SQL> connect testuser/testuser;
已连接。
SQL> show user;
USER 为 "TESTUSER"
SQL> grant connect to testuser1;
授权成功。
SQL> connect testuser1/testuser1;
已连接。
SQL> show user;
USER 为 "TESTUSER1"
SQL> connect system/Ww18315831096;
已连接。
SQL> revoke connect from testuser;
撤销成功。
SQL> connect testuser1/testuser1;
已连接。
5、使用profile管理用户口令
概述：profile是口令限制、资源限制的命令集合，当建立数据库时，oracle会自动建立名为default的profile。当建立用户没有指定profile选项时，oracle会将default分配给用户。
（1）、账户锁定
概述：指定该账户（用户）登入时最多可以输入密码次数，也可以指定用户锁定的时间（天）一般用dba的身份去执行该命令。
创建profile文件——》创建规则保存在profile的文件里
SQL>create profile 规则名字（自由） limit failed_login_attempts 最多输入密码次数 password_lock_time 锁定天数；
SQL>alter user 用户名 profile 规则名字（自由）;
例子：指定scott这个用户最多只能尝试3 次登入，锁定时间为2天。
SQL> conn system/Ww18315831096
已连接。
SQL> create profile aaa1 limit failed_login_attempts 3 password_lock_time 2;
配置文件已创建
SQL> alter user xiaoming profile aaa1;
用户已更改。
（2）、解锁账户
alter user 用户名 account unlock；
SQL> alter user xiaoming account unlock;
用户已更改。
（3）、终止口令
为让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba身份来操作。
SQL>create profile 文件名 limit password_life_time 期限数 password_grace_time 宽限期;
SQL>alter user 用户名 profile文件名;
例子：给前面创建的用户xiaoming创建一个profile文件，要求每隔10天要修改自家的等入密码，宽限期为2天。
SQL> conn system/Ww18315831096
已连接。
SQL> create profile aaa2 limit password_life_time 10 password_grace_time 2;
配置文件已创建
SQL> alter user xiaoming profile aaa2;
用户已更改。
（4）、口令历史
概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oracle就会将修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
例子：
<1>建立profile
SQL>create profile 文件名imit password_life_time 10 password_grace_time 2 password_reuse_time 10;
//password_reuse_time 指定口令可重用时间即10天后就可以重用
<2>分配给某个用户
<3>删除profile
SQL>drop profile password_history【cascade】
注：oracle 中的权限有两种系统权限和对象权限
1、系统权限：用户对数据库的相关权限（140多种）
2、对象权限：用户对其他用户的数据对象操作的权限
对表的操作
select（查询）
insert（添加）
update（修改）
delete（删除）
all(selectnsertupdatedelete的总称)
create
index
…
数据对象：比如视图、表、过程等
角色：如connect角色，其有7中权限
角色分为两种预定义角色和自定义角色
四、Oracle的表的管理
1、命名规则
——必须以字母开头
——长度不能超过30字符
——不能使用Oracle保留字
——只能使用如下字符A-Z、a-z、0-9、$、#等
2、Oracle支持的数据类型
（1）、字符型
<1>char（） 定长 最大2000字符 （注：查询速度快，效率高，但是浪费空间）例子：char（10）’小韩‘前四个字放’小韩’，后面6个字符用空格补全。实际存放’小韩 ‘
<2>varchar2（） 变长 最大4000字符（变长，节省空间）
<3>clob(character large objiect) 字符型大对象 最多4G
（2）数字型
number 范围 -10的 38次方至10的38次方，可以表示整数也可以表示小数
例子：
number（5,2）表示一个小数有5位有效数，2位小数。范围-999.99——999.99
number（5）表示5位整数，范围-99999——99999
（3）日期类型
data 包括年月日和时分秒
timestamp 这是Oracle9i对data数据类型的扩展
（3）图片
blob 二进制数据 可以存放图片/声音 /视频等 4G
注：一般来讲数据库里不存放视频图片声音等，只存放它们路径。把视频图片声音等存放在文件夹里，这样好管理。但是如果图片和声音等保密性很高，不能随便读取，可以将其存放在数据库里。
3、建表
（1）学生表
SQL>create table student( ——表名
xh number(4), ——学号
xm varchar2(20),——姓名 // xm varchar(20)
sex char(2),——性别
brithday date,——出生日期
sal number(7,2)——奖学金
);
注：表空间
（2）班级表
SQL>create table class(
classid number(2),
cname varchar(20)
);
4、修改表
（1）添加一个字段
SQL>alter table 表的名字 add （。。。）；
例：SQL>alter table student add (classid number(2));
(2)修改字段的长度
SQL>alter table 表的名字 modify(…);
例：SQL>alter table student modify(xm varchar2(30));
(3)修改字段的类型/或是名字（不能有数据）
SQL>alter table 表名 modify(…)
例：SQL>alter table student modify(xm char(30));
(4)删除一个字段（尽量别用 ）
SQL>alter table 表的名字 drop column 字段名;
例：SQL>alter table student drop column sal;
（5）修改表的名字
SQL>rename 原表名 to 新表名；
例：SQL>rename student to stu;
5、删除表
SQL>drop table 表名；
例：SQL>drop table student;
6、查看表结构
SQL>desc student;
7、查看表的具体内容
（1）全部内容
SQL>select *from student;
（2）部分内容
SQL>select ename,sal from emp;
8、添加数据
(1)插入所有字段
insert into 表名 values（ ， ， ，…）
例：insert into student values(1,‘小明’，’男‘，’21-07-2019‘，2000)；
注：oracle中默认的日期格式’DD-MON-YYYY‘，即日-月-年
更改日期格式：alter session set nls_date_format =‘YYYY-MM-DD’;
修改之后，可以用我们熟悉的格式添加日期：insert into student values（2，‘小明2’,‘男’，‘2019-07-21’,200.12）;
（2）插入部分字段
insert into student（xh,xm,sex）valuses(2,‘小明2’，‘女’);
(3)插入空值
insert into student (xh,xm,sex,brithday)values(3,‘小明3’,‘男’,null);
(4)修改一个字段
update student set sex=‘女’ where xh=‘4’;
select *from student ;
XH XM SEX SAL CLASSID BRITHDAY
....
3 xiaoming3 男 201.00 2
....
例：把所有男性的薪水，变为原来一半
update student set sal=sal/2 where sex=‘男’；
SQL> update student set sal=sal/2 where sex='男';
1 row updated
SQL> select *from student;
XH XM SEX SAL CLASSID BRITHDAY
---
3 xiaoming3 男 100.50 2
(5)修改多个字段
update student set sex=‘男’,xh=1 where xm=‘xiaoming3’
(6)修改含有null值的数据
update student set sex=‘女’,xh=0 where brithday is null;
9、删除数据
(1)delete from student;
删除所有记录，表结构还在，写日志，可以回恢复的，速度慢
SQL> insert into student values(3,'xiaoming3','男',201,2,null);
1 row inserted
SQL> savepoint aa;
Savepoint created
SQL> delete from student;
1 row deleted
SQL> rollback to aa;
Rollback complete
SQL> select *from student;
XH XM SEX SAL CLASSID BRITHDAY
----- -------------------- --- --------- ------- -----------
3 xiaoming3 男 201.00 2
（2）drop table student;
删除表的结构和数据
（3）delete from student where xh=‘a001’;
删除一条记录
（4）truncate table student;
删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
注：
回滚：SQL>rollbackl;
Savepoint（保存点）:SQL>savepoint 名字;
五、查询
1、Oracle基本查询
emp表是employee 雇员信息表
empno 就是雇员的员工编号， employee number
ename 员工姓名， emlployee name
job 员工的职位
mgr 上级编号
hiredate受雇日期
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ----------- ---------
dept是department的简写，部门的意思，也就是部门表
deptno 部门编号
dname 部门名称
loc 部门地址
DEPTNO DNAME LOC
---------- -------------- -------------
（1）表的基本查询
<1>查看表的结构
desc 表名;
例：
SQL> desc dept;
名称 是否为空? 类型
---
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
<2>查询所有列
select *from 表名; 注：切记使用select * 因为这样速度慢
例：SQL> select *from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
<3>查询指定列
select 列名1,列名2 from 表名；
例：SQL> select loc from dept;
LOC
-------------
NEW YORK
DALLAS
CHICAGO
BOSTON
<4>查询所有非重复行
select distinct 列名1,列名2 from 表名;
例：
SQL> select deptno,job from emp;
DEPTNO JOB
---------- ---------
20 CLERK
30 SALESMAN
30 SALESMAN
20 MANAGER
30 SALESMAN
30 MANAGER
10 MANAGER
20 ANALYST
10 PRESIDENT
30 SALESMAN
20 CLERK
DEPTNO JOB
---------- ---------
30 CLERK
20 ANALYST
10 CLERK
已选择14行。
SQL> select distinct deptno,job from emp;
DEPTNO JOB
---------- ---------
20 CLERK
30 SALESMAN
20 MANAGER
30 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
已选择9行。
例题：查询Smith的工作、薪水、所在部门
SQL> select job,sal,deptno from emp where ename ='SMITH';
JOB SAL DEPTNO
---
CLERK 800 20
注：数据库中的内容大小写区分，但是Oracle语句大小写不区分
（2）在Oracle中使用算术表达式
例子：计算年工资（=月工资13+月奖金13）
select sal *13+comm *13,ename,comm from emp;
SQL> select sal*13+comm*13,ename,comm from emp;
SAL*13+COMM*13 ENAME COMM
-------------- ---------- ----------
SMITH
24700 ALLEN 300
22750 WARD 500
JONES
34450 MARTIN 1400
BLAKE
CLARK
SCOTT
KING
19500 TURNER 0
ADAMS
SAL*13+COMM*13 ENAME COMM
-------------- ---------- ----------
JAMES
FORD
MILLER
已选择14行。
注：但是这里存在一个问题：有的人的工资就为空了
这里要说一下，在oracle中由一个这样的问题，在运算式中如果有一项为NULL，那么最终的运算结果也为NULL
解决方法：nvl(comm,0)意思是如果comment为空，则用0替代，不为空则用自身值
SQL> select sal*13+nvl(comm,0)*13,ename,comm from emp;
SAL*13+NVL(COMM,0)*13 ENAME COMM
--------------------- ---------- ----------
10400 SMITH
24700 ALLEN 300
22750 WARD 500
38675 JONES
34450 MARTIN 1400
37050 BLAKE
31850 CLARK
39000 SCOTT
65000 KING
19500 TURNER 0
14300 ADAMS
SAL*13+NVL(COMM,0)*13 ENAME COMM
--------------------- ---------- ----------
12350 JAMES
39000 FORD
16900 MILLER
已选择14行。
（3）使用列的别名
select ename “姓名”，sal*12 as “年收入” from emp;
SQL> select ename "姓名",sal*12"年收入"from emp;
姓名 年收入
---------- ----------
SMITH 9600
ALLEN 19200
WARD 15000
JONES 35700
MARTIN 15000
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
姓名 年收入
---------- ----------
JAMES 11400
FORD 36000
MILLER 15600
已选择14行。
SQL> select ename as "姓名",sal*12"年收入"from emp;
姓名 年收入
---------- ----------
SMITH 9600
ALLEN 19200
WARD 15000
JONES 35700
MARTIN 15000
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
姓名 年收入
---------- ----------
JAMES 11400
FORD 36000
MILLER 15600
已选择14行。
（4）处理NULL值
使用nvl函数来处理（同上）
（5）连接字符串
select ename ||‘is a’|| job from emp;
SQL> select ename ||'is a'||job from emp;
ENAME||'ISA'||JOB
-----------------------
SMITHis aCLERK
ALLENis aSALESMAN
WARDis aSALESMAN
JONESis aMANAGER
MARTINis aSALESMAN
BLAKEis aMANAGER
CLARKis aMANAGER
SCOTTis aANALYST
KINGis aPRESIDENT
TURNERis aSALESMAN
ADAMSis aCLERK
ENAME||'ISA'||JOB
-----------------------
JAMESis aCLERK
FORDis aANALYST
MILLERis aCLERK
已选择14行。
(6)使用where语句
<1>如何显示工资高于3000的员工并把薪水打出
select ename,sal from emp where sal>3000;
SQL> select ename,sal from emp where sal>3000;
ENAME SAL
---------- ----------
KING 5000
<2>查找查找1982.1.1后入职的员工
select ename,hiredate from emp where hiredate>‘1-1月-1982’;
SQL> select ename,hiredate from emp where hiredate>'1-1月-1982';
ENAME HIREDATE
---------- --------------
SCOTT 19-4月 -87
ADAMS 23-5月 -87
MILLER 23-1月 -82
<3>显示工资在2000到2500的员工情况
select *from emp where sal>2000 and sal<2500;
SQL> select *from emp where sal>2000 and sal<2500;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
<4>显示工资为800和2450的员工情况
select *from emp where sal=800 or sal=2450;
SQL> select *from emp where sal=800 or sal=2450;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
（7）使用like操作符
%：表示任意0到多个字符
_：表示任意单个字符
问题：
<1>显示首字母为S的员工姓名及工资
select ename,sal from emp where ename like’S%’;
SQL> select ename,sal from emp where ename like'S%';
ENAME SAL
---------- ----------
SMITH 800
SCOTT 3000
<2>显示第三个字符为大写O的所有员工的姓名和工资
select ename,sal from emp where ename like’__O%’;
注：__ 为两条下划线,代表前两个字符不固定
SQL> select ename,sal from emp where ename like'__O%';
ENAME SAL
---------- ----------
SCOTT 3000
（8）在where语句中使用in
显示empno为7844,7369,7499…的雇员姓名等
select *from emp where empno in (7844,7369,7499…);
SQL> select *from emp where empno in(7844,7369,7499);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
（9）使用is null操作符
显示没有上级的雇员情况
select *from emp where mgr is null;
SQL> select *from emp where mgr is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-11月-81 5000
10
（10）逻辑操作运算符
查询工资高于4000或是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写J
select *from emp where (sal>500 or job=‘MANAGER’ )and ename like’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 02-4月 -81 2975
20
7900 JAMES CLERK 7698 03-12月-81 950
30
（11）order by 语句
注：升序asc（默认）、降序desc
问题
<1>按照工资升序显示雇员信息
select *from emp order by sal;
SQL> select *from emp order by sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7900 JAMES CLERK 7698 03-12月-81 950
30
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
7934 MILLER CLERK 7782 23-1月 -82 1300
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
已选择14行。
<1>按照部门号升序而雇员工资降序排序
select deptno,sal,ename from emp order by deptno , sal desc;
SQL> select deptno,sal,ename from emp order by deptno , sal desc;
DEPTNO SAL ENAME
---------- ---------- ----------
10 5000 KING
10 2450 CLARK
10 1300 MILLER
20 3000 SCOTT
20 3000 FORD
20 2975 JONES
20 1100 ADAMS
20 800 SMITH
30 2850 BLAKE
30 1600 ALLEN
30 1500 TURNER
DEPTNO SAL ENAME
---------- ---------- ----------
30 1250 MARTIN
30 1250 WARD
30 950 JAMES
已选择14行。
（12）使用列的别名排序
select ename，sal*12 "年薪"from emp order by "年薪"desc;
注：中文别名需要“”括起来，英文可以不需要“”
SQL> select ename,sal*12"年薪"from emp order by "年薪"desc;
ENAME 年薪
---------- ----------
KING 60000
FORD 36000
SCOTT 36000
JONES 35700
BLAKE 34200
CLARK 29400
ALLEN 19200
TURNER 18000
MILLER 15600
WARD 15000
MARTIN 15000
ENAME 年薪
---------- ----------
ADAMS 13200
JAMES 11400
SMITH 9600
已选择14行。
2、Oracle复杂查询
（1）数据分组——max 、min、avg（平均）、sum、count（计数）
问题：
<1>显示所有员工中最高工资和最低工资
select max(sal),min(sal)from emp;
SQL> select max(sal),min(sal)from emp;
MAX(SAL) MIN(SAL)
---------- ----------
5000 800
<2>显示所有员工的平均工资和工资总和
select avg(sal),sum(sal)from emp;
SQL> select avg(sal),sum(sal)from emp;
AVG(SAL) SUM(SAL)
---------- ----------
2073.21429 29025
<3>计算有多少员工
select count(ename)from emp;
SQL> select count(ename)from emp;
COUNT(ENAME)
------------
14
扩展：
<4>显示工资最高的员工的名字，工作岗位
select ename ,sal from emp where sal=(select max(sal)from emp);
SQL> select ename ,sal from emp where sal=(select max(sal)from emp);
ENAME SAL
---------- ----------
KING 5000
<5>显示工资高于平均年工资的员工信息
select *from emp where sal>(select avg(sal)from emp);
SQL> select *from emp where sal>(select avg(sal)from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1000 小红2 CLERK 9999 05-9月 -99 4000 1000 10
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
已选择6行。
（2）group by （分组）和having 子句
group by ：用于查询的结果分组统计
having子句：用于限制分组显示结果
<1>显示每个部门的平均工资和最高工资
select avg(sal),max(sal),deptno from emp group by deptno;
SQL> select avg(sal),max(sal),deptno from emp group by deptno;
AVG(SAL) MAX(SAL) DEPTNO
---------- ---------- ----------
1566.66667 2850 30
2175 3000 20
2916.66667 5000 10
<2>显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),max(sal),deptno,job from emp group by deptno,job;
SQL> select avg(sal),max(sal),deptno,job from emp group by deptno,job;
AVG(SAL) MAX(SAL) DEPTNO JOB
---------- ---------- ---------- ---------
950 1100 20 CLERK
1400 1600 30 SALESMAN
2975 2975 20 MANAGER
950 950 30 CLERK
5000 5000 10 PRESIDENT
2850 2850 30 MANAGER
1300 1300 10 CLERK
2450 2450 10 MANAGER
3000 3000 20 ANALYST
已选择9行。
扩展：显示每个部门的每种岗位的平均工资和最低工资，按部门升序
select avg(sal),max(sal),deptno,job from emp group by job,deptno order by deptno;
SQL> select avg(sal),max(sal),deptno,job from emp group by job,deptno order by deptno;
AVG(SAL) MAX(SAL) DEPTNO JOB
---------- ---------- ---------- ---------
1300 1300 10 CLERK
2450 2450 10 MANAGER
5000 5000 10 PRESIDENT
3000 3000 20 ANALYST
950 1100 20 CLERK
2975 2975 20 MANAGER
950 950 30 CLERK
2850 2850 30 MANAGER
1400 1600 30 SALESMAN
已选择9行。
<3>显示平均工资低于2000的部门号和他的平均工资
select deptno ,avg(sal)from emp group by deptno having avg(sal)>2000 order by deptno;
SQL> select deptno ,avg(sal)from emp group by deptno having avg(sal)>2000 order by deptno;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2175
（3）对数据分组的总结
<1>分组函数只能出现在选择列表中、having、order by 子句中
<2>如果在select语句中同时包含group by，having，order by那么他们的顺序是group by、having ，order by
<3>在选择序列中如果有列、表达式和分组函数，那么这些列和表达式必须 有一个出现在group by子句中，否则会出错
如：select deptno，avg（sal），max（sal）from emp group by deptno having avg（sal）<2000;这里deptno就一定要出现在group by中
（4）多表查询
原则：如果有两张表至少有一个条件来排除笛卡尔集，如果有N张表至少有N-1个条件来排除笛卡尔集
笛卡尔集：
说明：多表查询是指基于两个和两个以上的表或视图的查询。如：显示sales部门位置和员工的姓名，这种情况下使用到dept表和empty表
问题：
<1>显示雇员名，雇员工资及所在部门的名字【笛卡尔集】
错误：select ename,sal,dname from emp, dept;
SQL> select ename,sal,dname from emp, dept;
ENAME SAL DNAME
---------- ---------- --------------
SMITH 800 ACCOUNTING
ALLEN 1600 ACCOUNTING
WARD 1250 ACCOUNTING
JONES 2975 ACCOUNTING
MARTIN 1250 ACCOUNTING
BLAKE 2850 ACCOUNTING
CLARK 2450 ACCOUNTING
SCOTT 3000 ACCOUNTING
KING 5000 ACCOUNTING
TURNER 1500 ACCOUNTING
ADAMS 1100 ACCOUNTING
ENAME SAL DNAME
---------- ---------- --------------
JAMES 950 ACCOUNTING
FORD 3000 ACCOUNTING
MILLER 1300 ACCOUNTING
SMITH 800 RESEARCH
ALLEN 1600 RESEARCH
WARD 1250 RESEARCH
JONES 2975 RESEARCH
MARTIN 1250 RESEARCH
BLAKE 2850 RESEARCH
CLARK 2450 RESEARCH
SCOTT 3000 RESEARCH
ENAME SAL DNAME
---------- ---------- --------------
KING 5000 RESEARCH
TURNER 1500 RESEARCH
ADAMS 1100 RESEARCH
JAMES 950 RESEARCH
FORD 3000 RESEARCH
MILLER 1300 RESEARCH
SMITH 800 SALES
ALLEN 1600 SALES
WARD 1250 SALES
JONES 2975 SALES
MARTIN 1250 SALES
ENAME SAL DNAME
---------- ---------- --------------
BLAKE 2850 SALES
CLARK 2450 SALES
SCOTT 3000 SALES
KING 5000 SALES
TURNER 1500 SALES
ADAMS 1100 SALES
JAMES 950 SALES
FORD 3000 SALES
MILLER 1300 SALES
SMITH 800 OPERATIONS
ALLEN 1600 OPERATIONS
ENAME SAL DNAME
---------- ---------- --------------
WARD 1250 OPERATIONS
JONES 2975 OPERATIONS
MARTIN 1250 OPERATIONS
BLAKE 2850 OPERATIONS
CLARK 2450 OPERATIONS
SCOTT 3000 OPERATIONS
KING 5000 OPERATIONS
TURNER 1500 OPERATIONS
ADAMS 1100 OPERATIONS
JAMES 950 OPERATIONS
FORD 3000 OPERATIONS
ENAME SAL DNAME
---------- ---------- --------------
MILLER 1300 OPERATIONS
已选择56行。
正确：select a1.ename,a1.sal ,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;
（或者：
1>select ename,sal,dname from emp ,dept where emp.deptno=dept.deptno;
2>select emp.ename,emp.sal,dept.dname from dept ,emp where emp.deptno=dept.deptno;）
SQL> select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;
ENAME SAL DNAME
---------- ---------- --------------
CLARK 2450 ACCOUNTING
KING 5000 ACCOUNTING
MILLER 1300 ACCOUNTING
JONES 2975 RESEARCH
FORD 3000 RESEARCH
ADAMS 1100 RESEARCH
SMITH 800 RESEARCH
SCOTT 3000 RESEARCH
WARD 1250 SALES
TURNER 1500 SALES
ALLEN 1600 SALES
ENAME SAL DNAME
---------- ---------- --------------
JAMES 950 SALES
BLAKE 2850 SALES
MARTIN 1250 SALES
已选择14行。
<2>显示部门号为10的部门名、员工名和工资
select dept.dname,emp.ename,emp.sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;
SQL> select dept.dname,emp.ename,emp.sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;
DNAME ENAME SAL
-------------- ---------- ----------
ACCOUNTING CLARK 2450
ACCOUNTING KING 5000
ACCOUNTING MILLER 1300
<3>显示各个员工的姓名、工资及其工资的级别
select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal;
SQL> select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
ENAME SAL GRADE
---------- ---------- ----------
SCOTT 3000 4
FORD 3000 4
KING 5000 5
已选择14行。
扩展：
<4>显示雇员名，雇员工资及其所在部门的名字，并按部门排序
select emp.ename,emp.sal,dept.dname ,emp.deptno from emp,dept where emp.deptno=dept.deptno order by emp.deptno;
SQL> select emp.ename,emp.sal,dept.dname ,emp.deptno from emp,dept where emp.deptno=dept.deptno order by emp.deptno;
ENAME SAL DNAME DEPTNO
---------- ---------- -------------- ----------
CLARK 2450 ACCOUNTING 10
KING 5000 ACCOUNTING 10
MILLER 1300 ACCOUNTING 10
JONES 2975 RESEARCH 20
FORD 3000 RESEARCH 20
ADAMS 1100 RESEARCH 20
SMITH 800 RESEARCH 20
SCOTT 3000 RESEARCH 20
WARD 1250 SALES 30
TURNER 1500 SALES 30
ALLEN 1600 SALES 30
ENAME SAL DNAME DEPTNO
---------- ---------- -------------- ----------
JAMES 950 SALES 30
BLAKE 2850 SALES 30
MARTIN 1250 SALES 30
已选择14行。
<2>现实FORD的上级
select worker.ename,bose.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=‘FORD’;
SQL> select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD';
ENAME ENAME
---------- ----------
FORD JONES
（5）子查询
说明：嵌入在其他SQL语句中的select语句，也叫嵌套查询
<1>单行子查询：只返回一行数据的子查询语句
显示与SMITH同一部门的所有员工
select ename from emp where deptno=(select deptno from emp where ename=‘SMITH’);
SQL> select ename from emp where deptno=(select deptno from emp where ename='SMITH');
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
FORD
<2>多行子查询：返回多行数据的子查询
查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
select *from emp where job in(select distinct job from emp where deptno=10 )；
SQL> select *from emp where job in (select distinct job from emp where deptno=10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7788 SCOTT CLERK 7566 19-4月 -87 800 20
7369 SMITH CLERK 7902 17-12月-80 800 20
1000 小红2 CLERK 9999 05-9月 -99 4000 1000 10
9998 小红2 MANAGER 9999 05-5月 -98 78.9 55.33 10
9999 小红 MANAGER 7782 05-5月 -98 78.9 55.33 10
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
已选择12行。
<3>在多行子查询中使用all操作符
显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
SQL> select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
ENAME SAL DEPTNO
---------- ---------- ----------
JONES 2975 20
SCOTT 3000 20
FORD 3000 20
KING 5000 10
扩展：
其他查询方法
select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
SQL> select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
ENAME SAL DEPTNO
---------- ---------- ----------
JONES 2975 20
SCOTT 3000 20
KING 5000 10
FORD 3000 20
<4>在多行子查询中使用any操作符
显示工资比部门30的任意一个员工的工资高的与员工的姓名、工资和部门
select ename ,sal,deptno from emp where sal>any(select sal from emp where deptno=30);
SQL> select ename ,sal,deptno from emp where sal>any(select sal from emp where deptno=30);
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
BLAKE 2850 30
CLARK 2450 10
ALLEN 1600 30
TURNER 1500 30
MILLER 1300 10
WARD 1250 30
MARTIN 1250 30
ENAME SAL DEPTNO
---------- ---------- ----------
ADAMS 1100 20
已选择12行。
扩展：不同的查询方法
select ename,sal,deptno from emp where sal>(select min(sal)from emp where deptno=30);
<5>多列子查询
单行子查询：查询只返回单列、单行数据
多行子查询：查询返回单列多行数据，都是针对单列而言的。
多列子查询：查询返回多列数据
查询与SMITH的部门和岗位完全相同的所有雇员
select *from emp where(deptno,job)=(select deptno,job from emp where ename=‘SMITH’);
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 17-12月-80 800
20
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
<6>在from子句中使用子查询
说明：当在from子句中使用子查询时，该查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询生成的表（内嵌视图）赋予一个别名（尽量不要加as）
显示高于自己部门的平均工资的员工的信息
第一步：查询出各个部门的平均工资和部门号，生成一个表
select avg(sal),deptno from emp group by deptno;
第二步：把上面的查询看作是一张 子表
select a1.ename,a1.sal,a1.deptno,a2.mysal from emp a1,(select deptno,avg(sal) mysal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.mysal;
SQL> select a1.ename,a1.sal,a1.deptno,a2.mysal from emp a1,(select deptno,avg(sal) mysal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.mysal;
ENAME SAL DEPTNO MYSAL
---------- ---------- ---------- ----------
ALLEN 1600 30 1566.66667
JONES 2975 20 2175
BLAKE 2850 30 1566.66667
SCOTT 3000 20 2175
KING 5000 10 2916.66667
FORD 3000 20 2175
已选择6行。
（6）分页查询
Oracle分页有三种方式
<1>rownum分页
第一步：做一个子查询
select *from emp;
第二步：显示 rn（rownum 、Oracle分配的）
select a1.*,rownum rn from (select *from emp) a1;
第三步：挑选出前10行（Oracle中一个select语句中只能用一个rowmun）
select a1.*,rownum rn from (select *from emp) a1 where rownum<=10;
第四步：跳出第六行到第十行
select *from（select a1. *，rownum rn from(select *from emp) a1 where rownum<=10）where rn>=6;
SQL> select *from(select a1.*,rownum rn from(select *from emp) a1 where rownum <=10)where rn>=6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO RN
---------- ----------
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30 6
7782 CLARK MANAGER 7839 09-6月 -81 2450
10 7
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20 8
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO RN
---------- ----------
7839 KING PRESIDENT 17-11月-81 5000
10 9
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30 10
注：几个查询的变化
1>指定查询列，只需修改最里层
部员名字和薪水
select from(select a1.,rownum rn from(select ename ,sal from emp) a1 where rownum <=10)where rn>=6；
SQL> select *from(select a1.*,rownum rn from(select ename ,sal from emp) a1 where rownum <=10)where rn>=6;
ENAME SAL RN
---------- ---------- ----------
BLAKE 2850 6
CLARK 2450 7
SCOTT 3000 8
KING 5000 9
TURNER 1500 10
2>排序，只需修改最底层
按薪水排序输出
select from(select a1.,rownum rn from(select ename ,sal from emp order by sal) a1 where rownum <=10)where rn>=6;
SQL> select *from(select a1.*,rownum rn from(select ename ,sal from emp order by sal) a1 where rownum <=10)where rn>=6;
ENAME SAL RN
---------- ---------- ----------
MILLER 1300 6
TURNER 1500 7
ALLEN 1600 8
CLARK 2450 9
BLAKE 2850 10
<2>根据ROWID来分（效率最高）
select * from t_xiaoxi where rowid in (select rid from (select rownum rn,rid from (select rowid ,rid,cid from t_xiaoxi order by cid desc)where rownum <10000)where rn >9980)order by cid desc;
第一步：select deptno ,ename,sal from emp order by sal desc; （rowid——deptno rid——ename cid——sal t_xiaoxi——emp）
第二步：select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10
第三步：select a2.* from (select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10) a2 where rn >2;
第四步：select * from emp where deptno in ( select a2.* from (select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10) a2 where rn >2)order by sal desc;
第一步：select deptno,ename,sal from emp order by sal desc ; （rowid——deptno rid——ename cid——sal t_xiaoxi——emp）
SQL> select deptno,ename,sal from emp order by sal desc;
DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
20 FORD 3000
20 SCOTT 3000
20 JONES 2975
30 BLAKE 2850
10 CLARK 2450
30 ALLEN 1600
30 TURNER 1500
10 MILLER 1300
30 WARD 1250
30 MARTIN 1250
DEPTNO ENAME SAL
---------- ---------- ----------
20 ADAMS 1100
30 JAMES 950
20 SMITH 800
已选择14行。
第二步： select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10;
SQL> select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10;
DEPTNO ENAME SAL RN
---------- ---------- ---------- ----------
10 KING 5000 1
20 SCOTT 3000 2
20 FORD 3000 3
20 JONES 2975 4
30 BLAKE 2850 5
10 CLARK 2450 6
30 ALLEN 1600 7
30 TURNER 1500 8
10 MILLER 1300 9
已选择9行。
第三步：select a2.* from (select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10) a2 where rn >2;
SQL> select a2.* from (select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10) a2 where rn >2;
DEPTNO ENAME SAL RN
---------- ---------- ---------- ----------
20 FORD 3000 3
20 JONES 2975 4
30 BLAKE 2850 5
10 CLARK 2450 6
30 ALLEN 1600 7
30 TURNER 1500 8
10 MILLER 1300 9
已选择7行。
第四步：select * from emp a3 where deptno in ( select a2.* from (select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10) a2 where rn >2)order by a3.sal desc; （出现错误）
SQL> select * from emp a3 where deptno in ( select a2.* from (select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10) a2 where rn >2)order by a3.sal desc;
select * from emp a3 where deptno in ( select a2.* from (select a1.*, rownum rn from (select deptno ,ename,sal from emp order by sal desc)a1 where rownum <10) a2 where rn >2)order by a3.sal desc
*
第 1 行出现错误:
ORA-00913: 值过多
<3>按分析函数分（效率最低.)
select from(select t.,row_number() over (order by cud desc) rk from t_xiaoxi t)where rk<10000)where rn>9980;
其t_xiaoxi为表名称，cid为表的关键字段，取按cid降序排序后的第9981—9999条记录，t_xiaoxi表中有70000条记录
注：数据库在执行SQL时，从后到前扫描，尽量把优化条件放在后面
（6）用查询结果创建新表
这个命令是一种快捷建表方法
creat table mytable (id,name,sal,job,deptno)as( select empno,ename,sal,job,deprno from emp);
（7）合并查询（效率快）
有时在实际应用中，为合并多个select语句的结果，可以使用集合操作符号union（联合并）、union all、intersect、minus（减去）
——union： 取并集 用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复行
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER';
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 MANAGER
CLARK 2450 MANAGER
FORD 3000 ANALYST
JONES 2975 MANAGER
KING 5000 PRESIDENT
SCOTT 3000 ANALYST
已选择6行。
——union all 用于取得两个结果集的并集，当使用该操作符时，不会去掉结果集中重复行
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job=‘MANAGER’;
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 MANAGER
BLAKE 2850 MANAGER
SCOTT 3000 ANALYST
KING 5000 PRESIDENT
FORD 3000 ANALYST
JONES 2975 MANAGER
BLAKE 2850 MANAGER
CLARK 2450 MANAGER
已选择8行。
——intersect 取交集
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job=‘MANAGER’;
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='MANAGER';
——minus（减去）：取差集（前面的减后面的）
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job=‘MANAGER’;
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 ANALYST
KING 5000 PRESIDENT
SCOTT 3000 ANALYST
七、Oracle数据库的创建
1、创建新的数据库
两种方法：
（1）通过Oracle提供的向导工具
（2）手工步骤直接创建
八、数据库的操作
1、在java程序中如何操作Oracle（有待解决 第12 讲）
（1）java中连接Oracle
案例：写一个ShowEmp.Java，分页显示emp表的用户信息
这里出现了问题，课程无法进行，需要寻求老师帮助
package com.sp;
import java.sql.*;
//演示如何使用 jdbc_odbc桥连接方式 即数据源的方式
public class TestOra {
public static void main(String[] args) {
// TODO 自动生成的方法存根
try {
//1、加载驱动
Class.forName("sun.jdbc.odbc.JdncOdbcDriver");
//2.得到连接
Connection ct=DriverManager.getConnection("jdbc:odbc:testsp","scoot","tiger");
Statement sm=ct.createStatement();
ResultSet rs=sm.executeQuery("select*from emp");
while(rs.next()) {
//用户名
System.out.println("用户名："+rs.getString(2));
}
}catch(Exception e) {
e.printStackTrace();
//TODO:handle exception
}
}
}
2、如何在Oracle中操作数据
（1）使用to_date函数(使用特定格式插入日期值)
插入列带有日期的表，并按照年-月-日的格式插入
insert into emp values(9999,‘小红’,‘MANAGER’,7782,to_date(‘1998-05-05’,‘yyyy-mm-dd’),78.9,55.33,10);
或：
insert into emp values(9999,‘小红’,‘MANAGER’,7782,to_date(‘1998/05/05’,‘yyyy/mm/dd’),78.9,55.33,10);
SQL> select *from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
..........
9999 小红 MANAGER 7782 05-5月 -98 78.9 55.33
10
已选择15行。
（2）使用子查询插入数据
当使用values子句时，一次子查询能插入一行数据
当使用子查询插入数据时，一条insert语句可以插入大量的数据
当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据
方法：运用insert语句结合select查询
第一步:建表
SQL> create table myemp1(myid number(4),myname varchar2(50),mydeptno number(5));
表已创建。
第二步：从emp表中导入10号部门的数据
SQL> insert into myemp1(myid,myname,mydeptno) select empno,ename,deptno from emp where deptno=10;
已创建5行。
（3）使用子查询更新数据
使用update语句更新数据，既可以使用表达式或者数值直接修改数据，也可以使用子查询修改数据
问题：希望员工Scott的岗位、工资、补助与员工Smith一样
方法一：使用表达式或者数值直接修该数据
方法二：使用子查询修改数据
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename =‘SMITH’)where ename =‘SCOTT’;
SQL> update emp set (job,sal,comm)=(select job,sal,comm from emp where ename ='SMITH')where ename ='SCOTT';
已更新 1 行。
3、Oracle事务处理
概念：事务是用于保证数据的一致性，由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败
dml:数据操作语言(增加、删除、 修改)
例子：网上转账就是典型的要用事务来处理，用以保证数据的一致性
（1）事务和锁
当执行事务操作时（dml语句），Oracle会在被作用的表上加锁，防止其他用户改变表的结构 。会形成等待队列。
（2）提交事务
使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁，当commit语句结束事务后，其他会话将可以查看到事务变化后的新数据
commit;
注：如果不手动提交，退出的话exit后自动提交
（3）保存点
savepoint：保存点
savepoint a1;
概念：事务中的一点，用于取消部分事务。可以设值多个保存点。
（4）回退事务
当结束事务时，会自动的删除该事务所定义的所有保存点。，当执行rollback时，通过指定保存点可以回退到指定的点这里。
rollback a1;
注：必须保证没有commit（提交事务）
（5）在Java中使用事务（这个还是不会，有待解决第13讲后半段）
（6）只读事务
只读事务是指只允许执行查询操作，而不允许执行任何其他dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。
<1>设置只读事务
set transaction read only
事例：
第一步：系统管理员设置只读事务
请输入用户名: system
输入口令:
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set transaction read only;
事务处理集。
第二步：普通用户scott向emp表中插入一行数据
请输入用户名: scott
输入口令:
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>insert into emp values(1000,'小红2','CLERK',9999,'5-9月-1999',4000,1000,10);
已创建 1 行。
SQL> select*from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1000 小红2 CLERK 9999 05-9月 -99 4000 1000 10
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT CLERK 7566 19-4月 -87 800 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
9999 小红 MANAGER 7782 05-5月 -98 78.9 55.33 10
9998 小红2 MANAGER 9999 05-5月 -98 78.9 55.33 10
已选择17行。
第三步：查看system用户能否，查询到emp表的变化
SQL> select *from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT CLERK 7566 19-4月 -87 800 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
9999 小红 MANAGER 7782 05-5月 -98 78.9 55.33 10
9998 小红2 MANAGER 9999 05-5月 -98 78.9 55.33 10
已选择16行。
果然system没有查询到变化
4、SQL函数使用
（1）字符函数
字符函数是哦oracle中最常用的函数
——lower（char）：将字符串转化为小写格式
——upper（char）：将字符串转化为大写格式
——length（char）：返回字符串长度
——substr（char，m，n）：取字符串的字串
——replace(char1,serch.string,replace_string)用replace_string替换char1字符串中的serch.string
——instr(char1,char2,[,n[,m]])取子串在字符串中的位置
<1>将所有员工的名字按小写方式显示
SQL> select lower(ename)from emp;
LOWER(ENAM
----------
小红2
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller
小红
小红2
已选择17行。
<2>将所有员工的名字按大写字母方式显示
SQL> select upper(ename)from emp;
UPPER(ENAM
----------
小红2
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
小红
小红2
已选择17行。
<3>显示正好为5个字符的员工姓名
SQL> select ename from emp where length(ename)=5;
ENAME
----------
SMITH
ALLEN
JONES
BLAKE
CLARK
SCOTT
ADAMS
JAMES
<4>以首字母大写、其余字母小写的方式显示所有员工姓名
1、//没有合并起来
SQL> select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))from emp;
UP LOWER(SUBSTR(ENAME
-- ------------------
小 红2
S mith
A llen
W ard
J ones
M artin
B lake
C lark
S cott
K ing
T urner
A dams
J ames
F ord
M iller
小 红
小 红2
已选择17行。
2、//合并起来
SQL> select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))from emp;
UPPER(SUBSTR(ENAME,1
--------------------
小红2
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams
James
Ford
Miller
小红
小红2
已选择17行。
<5>显示所有员工的姓名，用“我是A”替换所有“A”
SQL> select replace(ename,'A','我是A')from emp;
REPLACE(ENAME,'A','我是A')
--------------------------------------------------
小红2
SMITH
我是ALLEN
W我是ARD
JONES
M我是ARTIN
BL我是AKE
CL我是ARK
SCOTT
KING
TURNER
我是AD我是AMS
J我是AMES
FORD
MILLER
小红
小红2
已选择17行。
（2）数学函数
数学函数的输入参数和返回值的数据类型都是数字类型（number）的，数学函数包括cos，cosh，ln，log，sin，sinh，sqrt，tan，acos，asin，atan，round等
最常用：
——round(n,[m])四舍五入、如果省掉m，则四舍五入到整数；如果m是整数，则四舍五入到小数点的m位后；如果m位负数，则四舍五入到小数点的m位前
——trunc(n,[m])截取数字，如果省掉m，就截去小数部分，如果m是整数就截取到小数点的m位后，如果是负数就截取到小数点的m位前
——mod(m,n)取模
——floor(n) 向下取整 返回小于或等于n的最大整数
——ceil(n)向上取整 返回大于或等于n的最小整数
其余数学函数：
abs(n)返回数字n的绝对值
select abs（-13）from dual; ///dual是可以用来做测试的表
acos（n）返回数字的反余弦
asin（n）返回数字的反正弦
atan（n）正切
cos（n）余弦
exp（n）返回e的n次幂
log（m，n）返回对数
power（m，n）返回m的n次幂
案例：显示一个月位30天的所有员工的日薪金，忽略余数
SQL> select floor(sal/30),ename from emp;
FLOOR(SAL/30) ENAME
------------- ----------
133 小红2
26 SMITH
53 ALLEN
41 WARD
99 JONES
41 MARTIN
95 BLAKE
81 CLARK
26 SCOTT
166 KING
50 TURNER
36 ADAMS
31 JAMES
100 FORD
43 MILLER
2 小红
2 小红2
已选择17行。
（3）日期函数
处理date类型数据的函数
默认情况下日期格式是dd-mm-yy 即12-7月-78
——sysdate：该函数返回系统时间
SQL> select sysdate from dual;
SYSDATE
--------------
23-7月 -19
——add_months(d,n)
——last_day(d)：返回指定日期所在月份的最后一天
例：
<1>显示已经入职八个多月的员工
SQL> select ename from emp where sysdate>add_months(hiredate,8);
ENAME
----------
小红2
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
小红
小红2
已选择17行。
<2>显示满10年服务年限的员工的姓名和受雇佣日期
SQL> select ename,hiredate from emp where sysdate>=add_months(hiredate,12*10);
ENAME HIREDATE
---------- --------------
小红2 05-9月 -99
SMITH 17-12月-80
ALLEN 20-2月 -81
WARD 22-2月 -81
JONES 02-4月 -81
MARTIN 28-9月 -81
BLAKE 01-5月 -81
CLARK 09-6月 -81
SCOTT 19-4月 -87
KING 17-11月-81
TURNER 08-9月 -81
ADAMS 23-5月 -87
JAMES 03-12月-81
FORD 03-12月-81
MILLER 23-1月 -82
小红 05-5月 -98
小红2 05-5月 -98
已选择17行。
<3>对于每个员工，显示其加入公司的天数
SQL> select floor(sysdate-hiredate )"入职天数",ename from emp;
入职天数 ENAME
---------- ----------
7261 小红2
14097 SMITH
14032 ALLEN
14030 WARD
13991 JONES
13812 MARTIN
13962 BLAKE
13923 CLARK
11783 SCOTT
13762 KING
13832 TURNER
11749 ADAMS
13746 JAMES
13746 FORD
13695 MILLER
7749 小红
7749 小红2
已选择17行。
<4>找出各月份倒数第三天受雇的所有员工
SQL> select ename ,hiredate,last_day(hiredate)from emp where last_day(hiredate)-hiredate=2;
ENAME HIREDATE LAST_DAY(HIRED
---------- -------------- --------------
MARTIN 28-9月 -81 30-9月 -81
（4）转换函数
<1>转换函数用于将数据类型转换成另一种数据类型。在实际情况下，Oracle server允许值的数据类型和实际不同，这时Oracle server会隐含的转化数据类型（并不适合所有情况）
比如：
create table t1（ id number）；
insert into t1 values（‘10’）；------》Oracle会将‘10’自动转换整数10
create table t2 （id varchar2（10））；
insert into t2 values（1）；-----》Oracle会将1自动转化成‘1’
1、to_char
显示年月日 时分秒
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2019-07-23 17:47:43
显示薪水
SQL> select to_char(sal,'L99999.99') "薪水" from emp where ename='SMITH';
薪水
-------------------
￥800.00
SQL>
yy:两位数字的年份 2004----->04
yyyyy:四位数字的年份 2004
mm：两位数字的月份 8月—>08
dd：两位数字的天 30号---->30
hh24:8点----->20
hh12:8点------>08
mi、ss------->显示分、秒
9：显示数字，并忽略前面的0
0：显示数字，如位数不足，则用0补齐
.：在指定位置显示小数点
,：在指定位置显示逗号
$：在数字前加美元符号
L：在数字前加本地货币符号
C：在数字前加国际货币符号
G：在指定位置显示分隔符
D：在指定位置显示小数点符号（.）
案例：
<1>显示1980年入职的员工
SQL> select hiredate,ename from emp where to_char(hiredate,'yyyy')=1980;
HIREDATE ENAME
-------------- ----------
17-12月-80 SMITH
<2>显示12月份入职的员工
SQL> select hiredate,ename from emp where to_char(hiredate,'mm')=12;
HIREDATE ENAME
-------------- ----------
17-12月-80 SMITH
03-12月-81 JAMES
03-12月-81 FORD
2、to_date：用于将字符串转换成date类型的数据
（5）sys_context函数
使用格式： sys_context(‘userenv’,‘XX’) xx可变
-terminal ：当前会话客户所对应的终端的标识符
-lanuage：语言
-db-name：当前数据库名称
-sla_date_format：当前会话客户所对应的日期格式
-session_user：当前会话客户所对应的数据库用户名
-current_schema：当前会话客户所对应的默认方案名
-host：返回数据库所在主机的名称
查询正在使用那个数据库
SQL> select sys_context('userenv','db_name') "正在使用的数据库" from dual;
正在使用的数据库
------------------------------------------------------------------------------------------------------------------------
orcl
正在使用哪个方案
SQL> select sys_context('userenv','current_schema') "正在使用哪个方案" from dual;
正在使用哪个方案
------------------------------------------------------------------------------------------------------------------------
SCOTT
九、数据库管理
1、数据库管理员
（1）、职责：
安装和升级Oracle数据库
建库、表空间、表、视图、索引…
制定并实施备份与恢复计划
数据库权限管理、调优、故障排除
对于高级dba，要求能参与项目开发，会编写sql语句、存储过程、触发器、规则、约束、包
（2）sys与system的区别
<1>存储数据的重要性不同
sys：所有Oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于Oracle的运行至关重要，由数据库自己维护，任何用户不能手动更改，sys用户拥有dba（数据库管理员）、sysdba（系统管理员）、sysoper（系统操作员）角色或权限，是Oracle的最高用户。
system：用于存放次一级的内部数据，如Oracle的一些特性或工具的管理信息，system用户拥有dba（数据库管理员）、sysdba（系统管理员）角色或系统权限
<2>权限不同
sys用户必须以as sysdba或者as sysoper形式登入，不能以normal(正常)方式登入数据库
system 如果正常登入，它其实是一个普通的dba用户，但如果以as sysdba登入，其结果实际上是作为sys用户登入的。
SQL> connect system/Ww18315831096 as sysdba;
已连接。
SQL> show user;
USER 为 "SYS"
注：dba用户是指具有dba角色的数据库用户，特权用户可以执行启动实例，关闭实例等特殊操作，而dba用户只有在启动数据库后才能够执行各种管理
（3）管理初始化参数
设置实例或是数据库的特征
显示初始化参数：show parameter 命令
修改参数：
需要说明的是，如果想修改初始化参数，可到init.ora文件中修改。
如修改实例名字
2、数据库（表）的逻辑备份与恢复
（1）简介
逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程；
逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库的过程；
物理备份即可在数据库open的状态下进行也可在关闭数据库后进行，但是逻辑备份和恢复只能在open的状态下进行
（2）导出
特别说明：再导入和导出时，要到Oracle目录的bin目录下操作
导出分:导出表、导出方案、导出数据库
导出使用exp命令完成，该命令常用的选项：
——userid：用于指定执行导出操作的用户名、口令、连接字符串
——tables：用于指定执行导出的表
——ower：用于指定执行导出操作的方案
——full=y：用于指定执行导出操作的数据库
——inctype：用于指定执行导出操作的增量类型
——rows：用于指定执行导出操作是否要导出表中的数据
——file：用于指定导出文件名
1、导出表
（1）导出自己的表上网查也没解决、导出表失败没法解决
exp userid=scott/tiger@myoral tables=(emp,dept…) file = d:el.dmp
D:Javadatabase_installproduct11.2.0dbhome_1BIN>exp userid=scott/tiger@oral tables=(emp) file = c:el.dmp
Export: Release 11.2.0.1.0 - Production on 星期三 7月 24 16:31:10 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
EXP-00056: 遇到 ORACLE 错误 12154
ORA-12154: TNS: 无法解析指定的连接标识符
EXP-00000: 导出终止失败
（2）导出其他方案的表
用户导出其他方案的表需要dba权限或是exp_full_database的权限。
比如system就可以导出scott的表
exp userid=system/manager@myoral tables=(scott.emp) file=d:e2.dmp;
2、导出表的结构
（1）导出表的结构
exp userid=scott/tiger@oral tables=(emp) file=d:e1.dmp rows=n;
（2）使用直接导出的方式
exp userid=scott/tiger@oral tables=(emp) file=d:e1.dmp direct=y；
这种方式比默认的常规方法速度快
这是需要数据库的字符集与客户端的字符集完全一致，否则会报错
3、导出方案
导出方案使用的是export 工具
（1）道出自己方案
exp scott/tiger@oral owner=scott file=d:scott.dmp
（2）导出其他方案
需要dba权限或者exp_full_database 的权限，列如system用户可以导出任何方案（不可导出sys的方案）
exp system/Ww18315831096@oral owner=(scott) file=d:scott.dmp
4、导出数据库
使用export工具，要求用户具有dba权限或exp_full_database权限
exp userid=用户/密码@数据库名称 full=y inctype=complete file=d:x.dmp
inctype=complete :增量备份
（3）导入
1、导入表
（1）导入自己的表
imp userid=scott/tiger@oral tables=(emp) file=d:xx.dmp
（2）导入表到其他用户
须有dba权限或imp_full_database
imp userid=system/Ww18315831096@oral tables=(emp) file=d:xx.dmp touser=scott
（3）导入表的结构
imp userid=scott/tiger@oral tables=(emp) file=d:xx.dmp rows=n
（4）导入数据
表已存在
imp userid=scott/tiger@oral tables=(emp) file=d:xx.dmp ignore=yb
2、导入方案
使用import工具，须有dba或者imp_full_database权限
（1）导入自身的方案
imp userid=scott/tiger file=d:xx.dmp
（2）导入其他方案
要求该用户具有dba权限
imp userid=system/Ww18315831096 file=d:xx.dmp fromuser=system touser=scott
3、导入数据库
imp userid=system/Ww18315831096 full=y file=d:xx.dmp
3、数据字典和动态性能视图
（1）概念
数据字典：Oracle数据库中重要的组成部分，它提供了数据库的一些系统信息
动态性能视图：记载了例程启动后的相关信息
（2）数据字典
数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户。
用户只能在数据字典上执行查询操作（select语句），而其维护和修改是由系统自动完成的。
<1>数据字典的组成
数据字典包括数据字典基表和数据字典视图，
数据字典基表：存储数据库的基本信息（普通用户不能直接访问）
数据字典视图：基于数据字典基表所建立起来的视图（普通用户可以通过查询数据字典视图取得系统信息），数据字典视图主要包括user_xxx、all_xxx、dba_xxx三种类型
<2>查询数据字典
——user_tables
用于显示当前用户所拥有的的所有表，只返回用户所对应发难对的表
select table_name from user_tables
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
MYEMP1
STUDENT
已选择6行。
——all_tables
显示当前用户可以访问的所有表。他不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的表
SQL> select table_name from all_tables;
TABLE_NAME
------------------------------
DUAL
SYSTEM_PRIVILEGE_MAP
.......
已选择105行。
——dba_tables
显示所有方案拥有的数据库表，但是查询这种数据库字典视图，要求用户有dba角色或select any table系统权限
SQL>select table_name from dba_tables;
.......
已选择2785行。
<3>用户名、权限、角色
建立用户时，Oracle会把用户的信息存放到数据库字典中，当给用户授予权限或角色时，Oracle会将权限和角色的饿信息存放到数据字典中
——通过查询dba_users可以显示所有数据库用户的详细信息
显示dba-users的结构
SQL> desc dba_users;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
查询用户名称
SQL> select username from dba_users;
USERNAME
------------------------------
SYSTEM
SYS
MGMT_VIEW
DBSNMP
SYSMAN
XIAOMING
SCOTT
OUTLN
OLAPSYS
SI_INFORMTN_SCHEMA
OWBSYS
ORDPLUGINS
XDB
ANONYMOUS
CTXSYS
ORDDATA
OWBSYS_AUDIT
APEX_030200
APPQOSSYS
WMSYS
EXFSYS
ORDSYS
MDSYS
FLOWS_FILES
SPATIAL_WFS_ADMIN_USR
SPATIAL_CSW_ADMIN_USR
HR
APEX_PUBLIC_USER
OE
DIP
SH
IX
MDDATA
PM
BI
XS$NULL
ORACLE_OCM
已选择37行。
——通过查询数据字典视图dba_sys_privs，可以显示用户具有的系统权限
——通过查询数据字典dba_tab_privs可以显示用户具有的对象权限
——通过查询数据字典dba_col_privs可以显示数据具有的列权限
——通过查询数据库字典视图dba_role_privs可以显示用户具有的角色
查看dba_role_privs的结构
SQL> desc dba_role_privs;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
查看SCOOT具有哪些角色
SQL> select *from dba_role_privs where grantee='SCOTT';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT RESOURCE NO YES
SCOTT CONNECT NO YES
例子：查询一个角色包含的权限
系统权限：select *from dba_sys_privs where grantee=‘角色名’;
也可这样查看：select *from role_sys_privs where role='角色名';
对象权限：select *from dba_tab_privs where grantee=‘角色名’;
Oracle有多少角色：select *from dba_roles;
查看用户有哪些角色：select *from dba_role_privs where grantee=‘用户名（大写）’
查询 Oracle中一共有多少系统权限，一般是dba
select *from system_privilege_map order by name;
查询 Oracle中一共有多少对象权限，一般是dba
select distinct privilege from dba_tab_privs;
显示当前用户可以访问的数据字典视图
select *from dict where comments like ‘%grant%’
SQL> select table_name from dict where comments like'%grant%';
TABLE_NAME
------------------------------
USER_AUDIT_STATEMENT
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
ALL_COL_PRIVS
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD
COLUMN_PRIVILEGES
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
TABLE_PRIVILEGES
已选择20行。
显示当前数据库的全称
select *from global_name
<4>其他说明
数据字典记录有Oracle数据库的所有系统信息。通过查询数据字典可以取得以下系统信息：
对象定义情况
对象占用空间大小
列信息
约束信息
（3）动态性能视图
动态性能视图用于记录当前例程的活动信息，当启动Oracle server时，系统就会建立动态性能视图，当停止Oracle server时系统就会删除动态性能视图。Oracle的所有动态性能视图都是以 v _$ 开始的，并且Oracle为每个动态性能视图提供相应的同义词，并且同义词以 v$ 开始的， 例如： v _ d a t a f i l e 的 同 义 词 为 v datafile的同义词为v datafile的同义词为vdatafile；动态性能视图的所有者为sys，一般情况下，由dba或者特权用户查询动态性能视图
（4）管理表空间和数据文件
1、数据库的逻辑结构
Oracle中逻辑结构包括表空间、段、区和块
数据库由表空间构成，表空间由段构成，段由区构成，区由Oracle块构成，这样一种逻辑结构可以提高数据库的效率
2、表空间
（1）介绍
表空间是数据库的逻辑组成部分，从物理上讲，数据库存放在数据文件中；从逻辑上讲，数据库则是存放在表空间中，表空间由一个或者多个数据文件组成。表空间用于逻辑上组织数据库的数据，通过表空间可以达到以下作用：
<1>控制数据库占用的磁盘空间
<2>dba可以将不同数据类型部署到不同的位置，有利于提高I/O性能，同时有利于备份和恢复等管理操作
（2）创建表空间
使用create tablesspace命令完成
注;需特权用户或dba完成，如果一般用户建表空间需有create tablespace 的系统权限
（3）建立数据表空间
create tablespace data01 datafile ‘d:testdata01.dbf’ size 20m uniform size 128k;
注：执行完上述命令之后，会建立名为data01.dbf的数据文件，表空间大小20M，区的大小为128k
表空间不可超过500M
SQL> create tablespace data01 datafile 'D:Javatestdata01.dbf' size 20m uniform size 128k;
表空间已创建。
（3）使用表空间
create table mypart（deptno number（4），dname varchar2（14），loc varchar2（13））tablespace data01；
注:将表mypart建在表空间data01中
SQL> create table mypart(deptno number(4),dname varchar(14),loc varchar(13))tablespace data01;
表已创建。
（4）改变表空间的状态
当建立表空间的时候，表空间处于联机（online）状态，此时表空间可以访问，并且该表空间是可读的，即可以查询该表空间的数据，而且还可以在表空间中执行各种语句。但是进行系统维护时，可能需要改变表空间的状态，一般情况下，由特权用户或dba来操作
<1>使表空间脱机
alter tablespace 表空间名 offline；
SQL> alter tablespace data01 offline;
表空间已更改。
<2>使表空间联机
alter tablespace 表空间名 online;
SQL> alter tablespace data01 online;
表空间已更改。
<3>只读表空间
当建立表空间时，表空间可以读写，如果不希望在该表空间中执行update、delete、insert操作，那么可以将表空间修改为只读
alter tablespace 表空间名 read only；
SQL> alter tablespace data01 read only;
表空间已更改。
<4>表空间可读可写
alter tablespace 表空间名 read write
SQL> alter tablespace data01 read write;
表空间已更改。
<5>表空间的一些操作
1、知道表空间名字，显示表空间包含的表
select *from all_tables where tablespace_name=‘表空间名’
2、知道表名，查看该表属于哪个表空间
select tablespace_name,table_name from user_tables where table_name=‘EMP’;
SQL> select tablespace_name,table_name from user_tables where table_name='EMP';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
USERS EMP
3、显示表空间信息
查询数据字典视图dba_tablespaces，显示表空间的信息：select tablespace_name from dba_tablespaces；
4、显示表空间包含的数据文件
查询数据字典视图dba_data_files，可显示表空间所包含的数据文件：select file_name,bytes from dba_data_files where tablespace_name=‘表空间名’;
<6>删除表空间
注;需特权用户或dba完成，如果一般用户建表空间需有create tablespace 的系统权限
drop tablespace 表空间 including contents and datafiles;
说明：including contents 表示删除表空间时，删除该空间的所有数据库对象，而datafiles表示将数据库文件也删除
SQL> drop tablespace DATA01 including contents and datafiles;
表空间已删除。
<7>扩展表空间
表空间是由数据文件组成的，表空间的大小实际上就是数据文件相加后的大小，那么我们可以向兴，假定表employee存放到data01表空间上，初始大小是2M，当数据满2M后，如果继续向employee表中插入数据，这样就会显示空间不足的错误。
案例：
1、建立一个表空间
SQL> create tablespace data01 datafile 'D:Javatestdata01.dbf' size 1m uniform size 64k;
表空间已创建。
2、在该表空间上建立一个普通的表mydment结构和dept一样
SQL> desc scott.dept;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> create table mypart(deptno number(4),name varchar(20),loc varchar2(20)) tablespace data01;
表已创建。
SQL>
3、向表中加入数据insert into mydart select *from dept
SQL> insert into mypart select *from scott.dept;
已创建4行。
SQL> insert into mypart select *from mypart;
已创建464行。
SQL> insert into mypart select *from mypart;
insert into mypart select *from mypart
*
第 1 行出现错误:
ORA-01653: 表 SYSTEM.MYPART 无法通过 8 (在表空间 DATA01 中) 扩展
4、当一定时候就会出现无法扩展的问题怎么办？
5、扩展表空间，为其增加更多存储空间，有四种方法：
第一种：增加数据文件
alter tablespace data01 add datafile 'd:javatestdata02.dbf’size 2m
SQL> alter tablespace data01 add datafile 'd:javatestdata02.dbf' size 2m;
表空间已更改。
SQL> insert into mypart select *from mypart;
已创建29696行。
第二种：手动改变已存在数据文件大小
alter database datafile 'd:javatestdata01.dbf’resize 4m;
SQL> alter database datafile 'd:javatestdata01.dbf'resize 4m;
数据库已更改。
第三种：允许已存在的数据文件自动增长
alter database datafile ‘d:javatestdata01.dbf’ autoextend on next 10m maxsize 500m;
SQL> alter database datafile 'd:javatestdata01.dbf' autoextend on next 10m maxsize 500m;
数据库已更改。
第四种方法：新增数据文件，并且允许数据文件自动增长
alter tablespace data01 add datafile 'd:javatestdata03.dbf’size 2m autoextend on next 10m maxsize 500m;
SQL> alter tablespace data01 add datafile'd:javatestdata04.dbf'size 1m autoextend on next 1m maxsize 500m;
表空间已更改。
（5）移动数据文件
例：移动数据文件data0.dbf的副本data06.dbf到磁盘，然后恢复
第一步：确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name=‘d: javatestdata02.dbf’;
第二步: 使表空间脱机
保持数据文件的一致性
alter tablespace data01 offline；
第三步：使用命令移动数据文件到指定的目标位置
host move ‘d:javatestdata01.dbf’ ‘c:data01.dbf’
第四步：执行数据文件
在物理上移动数据后，还须执行alter tablespace 命令对数据库进行逻辑修改
alter tablespace data01 rename datafile ‘d:javatestdata01.dbf’ to ‘c:data01.dbf’;
第五步：使表空间联机
alter tablespace data01 online;
（5）其他表空间（补全）
除了最常用的数据表空间，还有：
索引表空间
undo表空间
临时表空间
非标准块的表空间
4、维护数据的完整性
（1）介绍
数据完整性用于确保数据库数据遵从一定的商业和逻辑规则。在Oracle中，数据完整性可以用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，约束易于维护并且具有自豪的性能，所以作为维护数据库的首选
（2）约束
1>介绍
约束用于确保数据库数据满足特定的商业规则。在Oracle中，约束包括：not null(非空)、unique（唯一）、primary key（主键）、foreign key（外键）和check五种
2>五种约束
<1>not null（非空）
如果某列上定义了not null，那么插入数据时，必须为该列提供数据
<2>unique（唯一）
当定义了唯一之后，该列值不可重复，但可为NULL
<3>primary key（主键）
用于唯一的标示表行的数据，当定义主键约束后，该列不可重复且不可为NULL
注：一张表最多有一个主键（primary key）约束，可以有多个unique（唯一）约束
<4>foreign key（外键）
用于定义主表与从表的关系，外键约束要定义在从表上，主表必须具有主键约束或unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或者为NULL
<5>check
用于强制数据必须满足以下条件。
例：假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在就会报错。
案例：
1、商店售货系统表设计案例一
现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：商品goods（商品类别category、商品号goodsID、商品名goodsName、单价unitprice、供应商provider），客户customer（客户号customerID、姓名name、性别sex、住址address、电邮email、身份证cardID），购买（客户号customerID、商品号goodsID、购买数量nums）。请用SQL语言实现下列功能
（1）每个表的主外键
（2）客户的姓名不能为空值
（3）单价必须大于0，购买数量必须在1—30之间
（4）电邮不可重复
（5）用户性别必须是男或女，默认为男
SQL> create table goods(
2 goodsid char(3) primary key,
3 goodsname varchar2(10),
4 unitprice number(10,2) check(unitprice>0),
5 category varchar2(10),
6 provider varchar2(10));
表已创建。
SQL> create table customer(
2 customerid varchar2(10) primary key,
3 name varchar2(10) not null,
4 address varchar2(10),
5 email varchar2(10) unique,
6 sex char(2) default '男' check（sex in('男','女')),
7 cardid char(18));
表已创建。
SQL> create table purchase(
2 customerid varchar2(10) references customer(customerid),
3 goodsid char(3) references goods(goodsid),
4 nums number(2)check(nums between 1 and 30));
表已创建。
2、商店售货系统表设计案例二
如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束。但要注意：增加not null约束时，需要使用modify选项，而增加 其他约束时使用add选项。
（1）每个表的主外键
（2）客户的姓名不能为空值--------增加商品名也不能为空
（3）单价必须大于0，购买数量必须在1—30之间
（4）电邮不可重复---------增加身份证也不能为空
（5）用户性别必须是男或女，默认为男
（6）增加客户的住址只能是’海淀‘、’朝阳‘、’东城‘、’西城‘、’通州‘、’崇文‘
SQL> alter table goods modify goodsname not null --商品名不允许为空;
表已更改。
SQL> alter table customer add constraint cardid_unique unique(cardid) --constraint:约束 cardid_unique:自己起的约束（unique(cardid)）的名字（可以随便起）;
表已更改。
SQL> alter table customer add constraint address_check check(address in ('东城','西城','海淀','朝阳','通州','崇文'));
表已更改。
3>删除约束
alter table表名drop constraint 约束名称；
注：在删除约束的时候可能出现错误，比如：
alter table 表名 drop primary key;
这是因为如果两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项：alter table 表名drop primary key cascade;
SQL> alter table customer drop primary key;
alter table customer drop primary key
*
第 1 行出现错误:
ORA-02273: 此唯一/主键已被某些外键引用
SQL> alter table customer drop primary key cascade;
表已更改。
（3）表级定义和列级定义
<1>表级定义：定义了所有列之后，再去定义约束，这里需要注意：not null只能在列级定义
例：
SQL> create table goods1(
2 goodsid char(3),
3 goodsname varchar2(10),
4 unitprice number(10,2),
5 category varchar2(10),
6 provider varchar2(10),
7 constraint gooid_primaryKey primary key(goodsid),
8 constraint unitprice_check check(unitprice>0));
表已创建。
SQL> create table purchase1(
2 customerid varchar2(10),
3 goodsid char(3),
4 nums number(2)check(nums between 1 and 30),
5 constraint doodsid_foreignKey foreign key(goodsid) references goods1(goodsid),
6 constraint nums_check check(nums between 1 and 30));
表已创建。
<2>列级定义：定义列的同时定义约束
例：
SQL> create table goods(
2 goodsid char(3) primary key,
3 goodsname varchar2(10),
4 unitprice number(10,2) check(unitprice>0),
5 category varchar2(10),
6 provider varchar2(10));
SQL> create table purchase(
2 customerid varchar2(10) references customer(customerid),
3 goodsid char(3) references goods(goodsid),
4 nums number(2)check(nums between 1 and 30));
5、管理索引
（1）介绍
索引是用于加速数据存储的数据对象。合理的使用索引可以大大降低I/O次数，从而提高数据访问性能
（2）索引分类
<1>单列索引：基于单个列建立的索引
create index 索引名 on 表名（列名）
<2>复合索引：基于两列或多列建立的索引，在同一张表上可以有多个索引，但要求列的组合必须不同（扫描是从后往前进行的，所以尽量把能够缩小范围大的索引放到后面）
create index 索引名 on emp（ename，job）
create index 索引名 on emp（job，ename）
（ename，job）和（job，ename）是不同的
（3）使用原则
<1>现在大表上建立索引才有意义
<2>在where子句或是连接条件上经常引用的列上建立索引
<3>索引的层次不要超过4层(多级索引)
（4）索引缺点
<1>建立索引系统要用大约是表1.2倍的内存、
<2>更新数据，系统必须要有额外的时间来同时对索引进行更新，以维持数据和缩印的一致性
注：有索引的表在进行插入、删除、修改等操作时比没有索引花费更多的系统时间
（5）其他索引
按照数据存储方式，可分为B*数、反向索引、位图索引；
按照索引列的个数：单列索引、复合索引
按照索引列值的唯一性：唯一索引、非唯一索引
此外还有：函数索引、全局索引、分区索引等
6、管理权限和角色
（1）系统权限
1、什么是系统权限
执行某种特定的数据库操作，要赋予相应系统权限
2、系统权限有哪些
查询所有系统权限：select *from system_privilege_map order by name;
3、怎样赋系统权限
一般是dba或者拥有grant any privilege的系统权限的用户。在授予权限时，如果带有with admin option选项的时候，被授予的用户可以授予其他用户或角色该系统权限
<1>grant create session，create table to ten withadmin option;
<2>grant create view to ten;
4、删除权限
revoke 权限名 from 用户名；
（2）对象权限
1、什么是对象权限
要访问其他方案的对象，要赋予相应的对象权限
2、对象权限有那些
3、怎样赋对象权限with grant option;
（3）角色
权限的命令集合，简化权限的管理
查看oracle用户具有的权限和角色
1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5.查看所有角色：
select * from dba_roles;
6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
比如我要查看用户 wzsb的拥有的权限：
SQL> select * from dba_sys_privs where grantee='WZSB';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
WZSB CREATE TRIGGER NO
WZSB UNLIMITED TABLESPACE NO
比如我要查看用户 wzsb的拥有的角色：
SQL> select * from dba_role_privs where grantee='WZSB';
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
WZSB DBA NO YES
查看一个用户所有的权限及角色
select privilege from dba_sys_privs where grantee='WZSB'
union
select privilege from dba_sys_privs where grantee in
(select granted_role from dba_role_privs where grantee='WZSB' );
1、预定义角色
（1）connect角色
包含的系统权限：
alter session：更改会话
create cluster：创建簇
create database link： 创建数据库链接
create session：创建会话
create table：创建表
create view：创建视图
create sequence：创建序列
（2）resource（资源）角色
resource角色隐含了unlimited tablespace系统权限
create cluster ：创建簇、集群、组
create indextyper：创建索引
create table：创建表
create sequence：创建序列
create procedure：创建程序
create trigger：创建触发器
（3）dba 角色
dba角色具有所有系统权限，及with admin option选项。dba不具备sysdba和sysoper的特权，dba没有启动和关闭数据库的权限
2、自定义角色
一般为dba用户或者具有create role的系统权限。在建立角色是可以指定验证方式（不验证、数据库验证等）
（1）建立角色
<1>不验证
如果角色是公用的角色，可以采用不验证的方式建立角色：
create role 角色名 not identified;
<2>数据库验证
采用这种方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令。在建立这种角色时，需要为其提供口令：
create role 角色名 identified by 口令（密码）;
SQL> conn system/Ww18315831096
已连接。
SQL> create role testrole identified by testrole;
角色已创建。
（2）角色授权
给角色授权与给用户授权无太大区别，系统权限的unlimited tablespace 和对象权限的with grant option 选项不可授予角色
系统权限：grant create session to 角色名 with admin option;
对象权限：grant all on scott.emp to 角色名;
SQL> grant create session to testrole with admin option;
授权成功。
SQL> conn scott/tiger;
已连接。
SQL> grant all on emp to testrole;
授权成功。
（3）角色分配给用户
由dba或者具有grant any role系统权限的用户
grant 角色名 to 用户名 【with admin option】
SQL> conn system/Ww18315831096
已连接。
SQL> grant testrole to testuser with admin option;
授权成功。
（4）删除角色
drop role 角色名;
（5）显示角色信息
<1>显示所有角色：select *from dba_roles
<2>显示角色具有的权限：select privilege，admin_option from role_sys_privs where role=‘角色名’
十、PLSQL
第一部分：基础
1、PL/SQL介绍
PL/SQL是过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。
优点：
提供应用程序的运行性能
模块化的设计思想
减少网络传输量
提高安全性
缺点：
移植性不好
2、开发工具
（1）SQLplus开发工具
例子：编写一个存储过程，该过程可以向某表添加数据
<1>创建一个表
SQL> create table test_table(name varchar2(20)not null,ranking number(3)unique);
表已创建。
<2>创建过程
SQL> create or replace procedure test_insert is
2 begin
3 --执行部分
4 insert into test_table values('小红',1);
5 end;
6 /
过程已创建。
注：此过程无参数
<3>调用过程
①exec 过程名（参数1，参数2…）
②call 过程名（参数1，参数2…）
SQL> exec test_insert;
PL/SQL 过程已成功完成。
（2）pl/sql developer开发工具（集成开发环境IDE）
和上面都差不多
2、PL/SQL基础
（1）编写规范
①注释
单行注释：–
多行注释：/* */
②标识符号的命名规范
<1>定义变量：建议用v_开头
<2>定义常量：建议用c_开头
<3>定义游标：建议用_cursor 作为后缀
<4>定义例外时：建议用e_开头
（2）块
<1>块（block）是pl/sql的基本程序单元
<2>块的结构
pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分
declare（声明）
/ *定义部分——定义常量、变量、游标、复杂数据类型 */
begin
/ * 执行部分——要执行的pl/sql语句和SQL语句* /
exception（ 例外 ）
/ * 例外处理部分——处理运行的各种错误*/
end;
注：
定义部分是从declare开始的，该部分是可选的；
执行部分是从begin开始，此部分不可省；
例外处理部分是从exception开始的，该部分是可选的。
例：
当使用dbms_output包时必须要先激活服务器输出
激活/关闭服务器输出语句：
①set serveroutput on；–打开输出选项
②set serveroutput off；–关闭输出选项、
dbms_output.put_line(’…’||v_ename);–输出一串字符串。dbms_output是一个包，put_line是包的块
&地址符表示从控制台输入变量
PL/SQL中结束输入使用‘/’字符
SQL> set serveroutput off;
SQL>
SQL>
SQL> --关闭输出
SQL> set serveroutput off;
SQL> --开启输出
SQL> set serveroutput on;
SQL> declare
2 --定义变量
3 v_ename varchar2(20);
4 v_sal number(7,2);
5 begin
6 --执行部分
7 --输入no查询符合empno=no的人名，赋予变量v_ename
8 select ename,sal into v_ename,v_sal from emp where empno=&no;
9 --在控制台显示用户名
10 dbms_output.put_line('雇员名：'||v_ename||'薪水是：'||v_sal);
11 --异常处理
12 exception
13 --未找到数据异常
14 when no_data_found then
15 dbms_output.put_line('数据输入有误，请重输！');
16 end;
17 /
数据输入有误，请重输！
PL/SQL procedure successfully completed
第二部分：PL/SQL
1、过程
介绍：过程用于执行特定的操作，当建立过程时，既可以指定输入参数（in），也可以指定输出参数（out）。通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在PL/SQL中可以使用create procedure命令建立过程。
结构;
create procedure 块的名字（参数1，参数2…）is
begin
–执行部分
end；
/
例：
1、实现根据雇员名修改工资
①创建过程
SQL> --实现根据雇员名修改工资
SQL> create or replace procedure test_update(newSal number,theName varchar2) is
2 begin
3 --执行部分，根据用户名修改工资
4 update emp a1 set a1.sal=newSal where a1.ename=theName;
5 end;
6 /
Procedure created
②调用过程
–在PL/SQL中调用过程
SQL> exec test_update(10,'SCOTT');
PL/SQL procedure successfully completed
–在Java中调用过程
//演示Java程序调用存储过程案例
package testOracle_test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class TestOracleTest1 {
public static void main(String[] args) {
// TODO 自动生成的方法存根
try {
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
//3.创建CallableStatement
CallableStatement cs=ct.prepareCall("{call test_update(?,?)}");
//4.给？赋值
cs.setInt(1,100);
cs.setString(2, "SMITH");
//5.执行
cs.execute();
//6.关闭
cs.close();
ct.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
2、函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。使用create function建立函数
案例:
创建函数
--函数
--输入雇员姓名，返回该雇员的年薪
create or replace function test_fun1(Name varchar2)--name相当于形参
return number is yearSal number(7,2); --类型 is变量名 类型范围/大小
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=Name;
return yearSal;
end;
在PL/SQL中调用函数
SQL> --在plsql中调用函数
SQL> var abc number;--建立形参
SQL> call test_fun1('SCOTT')into:abc;
Method called
abc
---------
9600
3、包
包用于在逻辑上组合过程和函数，由包规范和包体组成。用create package 命令创建包，用create package body命令创建包体。包规范里面是函数和过程的声明，包体内是函数和过程的实现。
创建包规范
SQL> --创建包
SQL> create package test_Package is
2 --包规范
3 --声明过程udata_sal和函数revert_sal
4 procedure updata_sal(name varchar2,sal number);
5 function revert_sal(name varchar2)return number;
6 end;
7 /
Package created
创建包体
SQL> --创建包体
SQL> create package body test_Package is
2 --实现过程udata_sal
3 procedure updata_sal(name varchar2,sal number) is
4 begin
5 update emp a1 set a1.sal=sal where a1.ename=name;
6 end;
7 --实现函数revert_sal
8 function revert_sal(name varchar2)
9 return number is sal number(7,2);
10 begin
11 select a1.sal into sal from emp a1 where a1.ename=name;
12 return sal;
13 end;
14 end;
15 /
Package body created
调用包的过程和函数：在过程和函数前须带有包名；如果要访问其他方案的包，还需要在包名前加方案名
SQL> exec test_Package.updata_sal('SCOTT',1);
PL/SQL procedure successfully completed
4、触发器
触发器是指隐含执行的存储过程。当定义触发器时，必须指定触发的事件触发的操作，常用的触发事件包括insert、update、delete语句，而触发操作实际就是一个PL/SQL块。使用create trigger建立触发器。
5、变量
①标量类型（scalar）
——常用类型
在PL/SQL中定义变量和常量的语法如下：
名称 【constant】数据类型【not null】【:= | default expr】
constant:指定常量，需要指定他的初始值，且其值是不能改变的
not null：制定变量值不能为空
:= ：给变量后者常量指定初始值
default：用于指定初始值
expr：指定初始值的PL/SQL表达式，可以是文本值、其他变量、函数等
标量定义的案例：
<1>定义一个变长字符串
v_name varchar2(10);
<2>定义一个小数
v_num number（6,2);
<3>定义一个小数并给一个初始值为5.4：=是PL/SQL的赋值号
v_num number（6,2):=5.4;
<4>定义一个日期类型的数据
v_date date；
<5>定义一个布尔类型变量，不能为空，初始值为false
v_bool boolean not null default false
例：输入员工号，显示雇员姓名、工资、个人所得税（税率:0.03）
SQL> set serveroutput on;
SQL>
SQL> --标量的使用
SQL> declare
2 --声明
3 c_TaxRate number(3,2):=0.03;--将税率定义为常量
4 v_Name varchar2(5);--用户名
5 v_sal number(7,2);--工资
6 v_tax_sal number(7,2);--税后工资
7 begin
8 --执行
9 select ename,sal into v_Name,v_sal from emp where empno=&empno1;
10 --计算
11 v_tax_sal:=v_sal*(1-c_TaxRate);
12 --谁出
13 dbms_output.put_line('雇员名;'||v_Name||'税前工资：'||v_sal||'税后工资：'||v_tax_sal);
14 end;
15 /
雇员名;SCOTT税前工资：800税后工资：776
PL/SQL procedure successfully completed
标量——使用%type属性，它会按照数据库来确定你定义的变量的类型和长度
标识符名 表名.列名%type;
如:v_name emp.ename%type;
②复合类型（composite）：用于存放多个数据
<1>PL/SQL记录
类似于结构体，当引用PL/SQL记录成员时，必须要加记录变量作为前缀（记录变量.记录成员）
例：
<2>PL/SQL表
相当于数组，PL/SQL表的下表可以为负数，并且表元素的下标没有限制
例：
<3>嵌套表
<4>varray(动态表)——变长数组
③参照类型（reference）
参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使应用程序共享相同对象，从而降低占用的空间。参照变量分为：游标变量（ref cursor）和对象类型变量（ref obj_type）
<1>游标变量（ref cursor）
当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了
例：
SQL> --youbiao
SQL> declare
2 --定义游标类型
3 type test_emp_cursor is ref cursor;
4 --定义游标变量
5 test_cursor test_emp_cursor;
6 --定义变量
7 v_name emp.ename%type;
8 v_sal emp.sal%type;
9 begin
10 --执行
11 --把test_cursor和一个select语句结合
12 open test_cursor for select ename,sal from emp where deptno=&empno1;
13 --循环取出
14 loop--循环语句
15 --fetch取出
16 fetch test_cursor into v_name,v_sal;
17 --判断是否退出，判断 test_cursor是否为空
18 exit when test_cursor%notfound;
19 --打印
20 dbms_output.put_line('雇员名：'||v_name||'薪水：'||v_sal);
21 end loop;--结束循环
22 end;
23 /
雇员名：小红2薪水：4000
雇员名：CLARK薪水：2450
雇员名：KING薪水：5000
雇员名：MILLER薪水：1300
雇员名：小红薪水：78.9
雇员名：小红2薪水：78.9
PL/SQL procedure successfully completed
<2>对象类型变量（ref obj_type）
④lob（large object）
6、控制结构
（1）条件分支语句（各种if语句）
<1>if----then
if … then
…
end if;
例：编写一个过程，输入雇员名，如果该雇员工资低于2000，则该雇员工资加10%
<2>if–then–else二重条件分支
if … then
…
else
…
end if;
编写一个过程，输入雇员名，如果该雇员工资低于2000，则该雇员工资加10%；如果高于2000，增减5%
SQL> create or replace procedure test_updateSal(testName varchar2) is
2 --定义
3 v_sal emp.sal%type;
4 v_updateSal emp.sal%type;
5 v_Subsidy number(3,2);
6 begin
7 --执行
8 select sal into v_sal from emp where ename=testName;
9 --判断
10 if v_sal<2000 then
11 v_Subsidy:=0.1;
12 else
13 v_Subsidy:=0.05;
14 end if;
15 update emp set sal=v_sal*(1+v_Subsidy)where ename=testName;
16 select sal into v_updateSal from emp where ename=testName;
17 dbms_output.put_line('雇员名：'||testName||'改动前的薪水：'||v_sal||'改动后的薪水'||v_updateSal||'补助：'||v_Subsidy);
18 end;
19 /
Procedure created
SQL> exec test_updateSal('SCOTT');
雇员名：SCOTT改动前的薪水：968改动后的薪水1064.8补助：.1
PL/SQL procedure successfully completed
<3>if—then–elsif–else多重条件分支
编写一个过程，输入雇员名，如果该雇员工为boos，则该雇员工资加10%；如果为manager，增加5%
，其他人增加1%；
if … then
…
elsif …then
…
else
…
end if;
（2）循环语句
<1>loop循环
loop
…
–退出条件
exit when …;(判断)
…
end loop;
至少循环一次,类似于do { }while（）
<2>while循环
while …(判断)loop
…
end loop;
<3>for循环
for i in reverse 1…10 loop
…
end loop;
控制变量i，在隐含中就不停的增加
（3）顺序控制语句：goto语句和null
①goto语句
用于跳转到特定标号去执行语句。
基本语法:goto lable ,其中lable是已经定义好的标号名
SQL> declare
2 i int :=1;
3 begin
4 loop
5 dbms_output.put_line('输出i='||i);
6 if i=10 then
7 goto end_loop;
8 end if;
9 i:=i+1;
10 end loop;
11 <<end_loop>>
12 dbms_output.put_line('循环结束');
13 end;
14 /
输出i=1
输出i=2
输出i=3
输出i=4
输出i=5
输出i=6
输出i=7
输出i=8
输出i=9
输出i=10
循环结束
PL/SQL procedure successfully completed
②null语句
null语句不会执行任何操作，并且直接将控制传递到下一条语句，使用null语句可以提高程序可读性
7、编写分页过程
（1）无返回值存储过程
案例1：编写一个过程，向book表中添加书，要求通过java程序调用该过程。
①创建过程
--建表
create table book(name varchar2(20),publishhouse varchar2(20));
--编写过程
create or replace procedure test_book (name in varchar2,publishhouse varchar2) is--in表示这是一个输入参数，如果不写默认为in；out表示是一个输出参数
begin
insert into book values(name,publishhouse);
end;
②Java调用无返回值过程
//演示Java程序调用存储过程案例
package testOracle_test;
import java.sql.*;
public class TestOracleTest2 {
public static void main(String[] args) {
// TODO 自动生成的方法存根
try {
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
//3.创建CallableStatement 调用过程
CallableStatement cs=ct.prepareCall("{call test_book(?,?)}");
//4.给？赋值
cs.setString(1, "笑傲江湖");
cs.setString(2, "人民出版社");
//5.执行
cs.execute();
//6.关闭
cs.close();
ct.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
（2）有输入输出的存储过程
案例2：
①创建过程
SQL> create or replace procedure test_out_in(empno01 in number,name01 out varchar2,sal01 out varchar2,job01 out varchar2) is
2 begin
3 select ename,sal,job into name01,sal01,job01 from emp where empno=empno01;
4 end;
5 /
Procedure created
②在java中调用
package testData01_01;
import java.sql.*;
public class DataDemo {
public static void main(String[] args) {
// TODO 自动生成的方法存根
try {
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
//3.创建CallableStatement 调用过程
CallableStatement cs=ct.prepareCall("{call test_out_in(?,?,?,?)}");
//4.给？赋值
cs.setInt(1, 7788);
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
cs.registerOutParameter(3, java.sql.Types.DOUBLE);
cs.registerOutParameter(4, java.sql.Types.VARCHAR);
//5.执行
cs.execute();
//6.取出返回值 注意问号顺序 注：只返回两个值，不返回三个值
String name=cs.getString(2);
String job=cs.getString(4);
System.out.println("7788的名字"+name+"工作"+job);
//7.关闭
cs.close();
ct.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
（3）有返回值的存储过程，返回的是个结果集
注：由于Oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用package
案例3：
①构建一个包
create or replace package testpackage as
--在包中建立一个test_cursor游标
type test_cursor is ref cursor;
end testpackage;
②过程构建
--过程创建
create or replace procedure test_fun2(empno01 in number,p_cursor out testpackage.test_cursor)is--p_cursor out testpackage.test_cursor定义一个p_cursor参数，类型为定义好的游标test_cursor
begin
--打开游标
open p_cursor for select *from emp where deptno=empno01;
end;
③在java中调用 出现错误，未解决
package testData01_01;
import java.sql.*;
public class DataDemo {
public static void main(String[] args) {
// TODO 自动生成的方法存根
try {
//1、加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
//3.创建CallableStatement 调用过程
CallableStatement cs=ct.prepareCall("{call test_fun2(?,?)}");
//4、赋值
cs.setInt(1,10);
cs.registerOutParameter(2,java.sql.Types.REF_CURSOR);
//5、执行
cs.execute();
//6、得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
//7、取出结果
while(rs.next()) {
System.out.println(rs.getInt(1)+rs.getString(2));
}
//关闭资源
cs.close();
ct.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
（4）编写分页过程：略
8、例外处理
（1）处理预定义例外
预定义例外是由PL/SQL所提供的系统例外。导尿管PL/SQL应用程序违反了Oracle规定的限制时，则会隐含的触发一个内部例外。PL/SQL为开发人员提供了二十多个预定义例外。常用的如下：
<1>case_not_found：在开发PL/SQL块中编写case语句时，如果在when字句中没有包含必须的条件分支，就会触发case_not_found的例外：
create or replace procedure test_exception(num number)is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=num;
case
when v_sal<1000 then
update emp set sal=sal+100 where empno=num;
when v_sal<2000and v_sal>1000 then
update emp set sal=sal+200 where empno=num;
end case;
exception
when case_not_found then
dbms_output.put_line('case语句没有与'||v_sal||'相匹配的');
end;
<2>cursor_already_open：当有表重新打开时，会隐含的触发例外cursor_already_open
declare
cursor emp_cursor is select ename,sal from emp;
begin
open emp_cursor; --打开了游标
for emp_record1 in emp_cursor loop --再次打开游标
dbms_output.put_line(emp_record1.ename);
end loop;
exception
when cursor_already_open then
dbms_output.put_line('游标已打开');
end;
<3>dup_val_on_index：在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup_val_on_index例外
begin
insert into dept values(10,'公安部','北京');
exception
when dup_val_on_index then
dbms_output.put_line('在deptno列上不能出现重复值');
end;
<4>invaild_cursor：当试图在不合法的游标上操作时，会触发invaild_cursor例外
例如：视图从没有打开的游标上提取数据，或是关闭没有打开的游标
declare
cursor emp_cursor is select ename,sal from emp;
emp_record emp_cursor%rowtype;
begin
--open emp_cirsor;--打开游标
fetch emp_cursor into emp_record;
dbms_output.put_line(emp_record.ename);
close emp_cursor;
exception
when invalid_cursor then
dbms_output.put_line('请检查游标是否打开');
end;
<5>invalid_number：当输入的数据有误时，触发invalid_number例外
begin
update emp set sal=sal+'1oo';
exception
when invalid_number then
dbms_output.put_line('输入的数字有误');
end;
<6>no_data_found：没有返回行就会触发no_data_found
declare
--定义
v_name emp.ename%type;
begin
select ename into v_name from emp where empno=&no;
dbms_output.put_line('名字：'||v_name) ;
exception
when no_data_found then
dbms_output.put_line('编号不存在！');
end;
<7>too_many_rows：当执行select into语句时，如果返回超过了一行，则会触发该例外
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp;
exception
when too_many_rows then
dbms_output.put_line('返回了多行');
end;
<8>zero_divide：当执行数学运算语句时，如:2/0则会触发该例外
<9>value_error：当执行赋值操作时，如果变量的长度不足以满足所容纳的实际数据的长度时，就会触发该例外
declare
v_ename varchar2(2);
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line(v_ename);
exception
when value_error then
dbms_output.put_line('变量大小不足');
end;
（2）其他预定义例外
<1>logon_denide：用户名非法登入时触发
<2>not_logged_on：用户未登入就执行dml操作
<3>storage_error：超出内存空间或内存被损坏
<4>timeout_on_resourse：Oracle在等待资源时，超时
（3）非定义例外
（4）自定义例外
--自定义例外
create or replace procedure ex_test(num number) is
--定义一个例外
myexception exception;
begin
--更新用户
update emp set sal=sal+1000 where empno=num;
--
if sql%notfound then
raise myexception;
end if;
exception
when myexception then
dbms_output.put_line('没有更新任何用户');
end;
十一、Oracle视图
（1）定义
视图是一个虚拟的表，其内容由查询定义。同真的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中已存储的数据值的形式存在，行和列数据来自由定义视图的查询所引用的表，并在引用视图是动态生成
（2）视图与标的区别
<1>表需要占用空间，视图不需要
<2>视图不能添加索引
<3>使用视图可简化复杂查询，比如学生选课系统
<4>视图使用利于提高安全性，比如不同用户插不同的表
注：表的结构过于复杂时，可以使用视图
（3）创建视图
create view 视图名 as select语句【with read only】
（4）创建或修改视图
create or replace view 视图名 as select语句 【with read only】
（5）删除视图
drop view 视图名
--创建视图，将emp表sal<1000的雇员信息映射到该视图（view）
create view test_view01 as select *from emp where sal<1000;
--为简化操作，用一个视图解决显示雇员编号，姓名和部门名称
create view test_view02 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;
注：
pl/sql清洗命令：clear
sql plus 清屏：clear screen
查看错误信息：show error
关闭数据库：shutdown
开启数据库;startup
不等于：<>
最后
以上就是着急荔枝为你收集整理的Oracle数据库学习笔记的全部内容，希望文章能够帮你解决Oracle数据库学习笔记所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复