oracle体系结构2.0

数据库学习，oracle数据库一般是企业使用，学习数据库会对编码有很好的衔接，不用面向百度CV编程。

Oracle 2.0

数据库基础

数据库管理系统

数据

能输入到计算机并被处理的符号总称

数据处理

对数据收集\存储\加工\传播

目的:抽取推导出对人们有价值的信息

数据处理三阶段

人工管理阶段

文件系统阶段

数据库系统阶段

数据库

长期存储在计算机

有组织的管理

具有较小的数据冗余特点

多个用户共享

数据集合

关系数据库

建立在关系模型基础上的数据库，现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。

数据模型

数据库系统中,用于抽象\表示\处理现实世界中的一种形式架构

概念模型

现实世界到信息世界的第一层抽象,常用E-R图表示

逻辑模型

用户从数据库看到的模型，是具体的DBMS所支持的数据模型

层次模型

网状模型

关系模型

1970年IBM公司E.F.Codd提出

关系数据结构

现实世界的实体

实体间的各种联系

用关系表示

关系就是一张由行和列组成的二维数据表

关系操作集合

选择

投影

连接

增加

删除

修改

关系完整性约束

实体完整性

参照完整性

用户定义的完整性

物理模型

面向计算机物理表示的模型，描述了数据在存储介质上的组织结构，与具体的DBMS有关，也与操作系统和硬件有关。

工具的使用:Navicat

Oracle安装\卸载

本地连接\网络连接

物理结构

文件系统

控制文件

包含维护和验证数据库完整性的必要信息、例如，控制文件用于识别数据文件和重做日志文件，一个数据库至少需要一个控制文件 名字通常为Ctr*.ctl格式

包含以下信息

数据库名

数据库文件和日志文件的名字和位置

数据库建立日期

为了安全起见，允许被镜像

每一次ORACLE数据库的实例启动时，它的控制文件用于标识数据库和日志文件，当着手数据库操作时它们必须被打开。

当数据库的物理组成更改时，ORACLE自动更改该数据库的控制文件。数据恢复时，也要使用控制文件。

数据文件

每一个ORACLE数据库有一个或多个物理的数据文件(data file)。存储数据的文件 .dbf

一个数据库的数据文件包含全部数据库数据。逻辑数据库结构(如表、索引)的数据物理地存储在数据库的数据文件中。

特征

一个数据文件仅与一个数据库联系

一旦建立，数据文件不能改变大小

一个表空间（数据库存储的逻辑单位）由一个或多个数据文件组成。

数据文件中的数据在需要时可以读取并存储在ORACLE内存储区中。

日志文件

含对数据库所做的更改记录，这样万一出现故障可以启用数据恢复。一个数据库至少需要两个重做日志文件 .log

参数配置文件

定义Oracle 例程的特性，例如它包含调整SGA 中一些内存结构大小的参数 ，初始化参数文件InitSID.ora

归档文件

是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。

密码文件

认证哪些用户有权限启动和关闭Oracle例程

后台进程、视图、触发器、包

存储结构、触发器

逻辑结构

数据库（柜子）

表空间（抽屉）

概念：组织数据文件的方法。每一个表空间由一个或多个数据文件组成，一个数据文件只能与一个表空间联系

典型数据表空间

系统表空间

临时表空间

工具表空间

用户表空间

数据及索引表空间

回滚表空间

数据段/文件（文件夹）

若干个数据区间构成数据段

数据段

数据段也称为表段，它包含数据并且与表和簇相关。当创建一个表时，系统自动创建一个以该表的名字命名的数据段。

索引段

包含了用于提高系统性能的索引。一旦建立索引，系统自动创建一个以该索引的名字命名的索引段。

回滚段

包含了回滚信息，并在数据库恢复期间使用，以便为数据库提供读入一致性和回滚未提交的事务，即用来回滚事务的数据空间。当一个事务开始处理时，系统为之分配回滚段，回滚段可以动态创建和撤销。系统有个默认的回滚段，其管理方式既可以是自动的，也可以是手工的。

临时段

它是Oracle在运行过程中自行创建的段。当一个SQL语句需要临时工作区时，由Oracle建立临时段。一旦语句执行完毕，临时段的区间便退回给系统。

表（纸）

数据（字）

数据区间

由物理上连续存放的块构成，他是Oracle存储分配最小的单元，一个或多个块组成一个数据区间

数据块

是Oracle数据库的硬盘存储单元。使用数据库工作时，Oracle使用数据块存储和检索硬盘上的数据。

实例

数据库实例（也称服务器Server）

就是用来访问一个数据库文件集的一个存储结构及后台进程的集合。它使一个单独的数据库可以被多个实例访问

简单来说就是，内存+进程=实例

SGA与PGA的区别

SGA（系统全局区）

系统内所有进程共享

当多个用户同时连接到一个例程时，所有用户进程、服务进程都可以共享这个SGA区

主要用途就是为不同用户之间的进程与服务进程提供一个交流平台

各种数据的操作主要就是在SGA区完成

PGA（系统缓冲区）

不共享

主要是为了某个用户进程所服务

只有这个用户服务进程本身才能访问它自己的PAG区

标识

实例在操作系统中用ORACLE_SID标识，在Oracle中用参数INSTANCE_NAME来标识，他们两个的值相同

数据库启动时，系统首先在服务器内存中分配系统全局区（SGA），构成了Oracle的内存结构，然后启动若干个常驻内存的操作系统进程，即组成了Oracle的 进程结构，内存区域和后台进程合称为一个Oracle实例。

事务控制

特征:ACID

原子性(Atomicity)

一致性(Consistency)

隔离性(Isolation)

持久性(Durability)

事务的组成

DML或select+TPL(commit,rollback)

DML或select+DDL(create,alter,drop..)

DML或select+DCL(数据控制语言:grant授权 revoke回收权限)

事务结束

显示结束

提交(COMMIT)

使用COMMIT命令实现,已成功的方式结束事务,组成事务的DML语句操作全部生效

回滚(ROLLBACK)

使用ROLLBACK命令实现,以失败的方式结束事务,组成事务的DML语句操作全部被取消

隐式结束

隐式提交

执行一个DDL(creat,alter,drop,truncate,rename)语句

执行一个DCL(grant,revoke)语句

从SQL*Plus正常退出(即使用EXIT或QUIT命令退出)

隐式回滚

从SQL*Plus中强行退出

客户端连接到服务器端异常中断

系统崩溃

事务开启

事务自动开启于上一个事务结束后,执行的第一个DML语句

设置保存点

如果在一个事务内,想要回滚到指定位置,不是回滚到事务的起始点,可以通过保存点(SAVEPOINT)来实现

SAVEPOINT savepointname;-----定义一个保存点语句ROLLBACK TO savepointname;------回滚到指定保存点

注意:以上两条语句不结束事务的执行

savepoint s1; rollback to s1;回到保存点s1 并没有结束事务 rollback;显示结束事务

锁

锁用来在多用户并发访问和操作数据库时,保证数据的一致性的一种机制

锁由Oracle自动管理,如一个DML操作,ORACLE默认的机制是在DML操作影响的行记录上自动加锁

锁在被相关的操作申请并持有后,会一直保持到事务的结束,事务结束后,锁才会被释放

查询语句不会锁定任何记录,如果在查询语句后面加FORUPDATE子句会锁定查询所影响的行记录

锁是由oracle自动管理，在DML操作上自动加锁，事务结束，锁释放。

查询语句不会加锁，但是在查询语句的后面加for update会加锁。

提交(COMMIT)或回滚(ROLLBACK)前的数据状态

数据变化前的状态可以被恢复

当前会话可以使用SELECT语句来验证DML操作后的结果

其它会话不能查看由当前用户的DML操作结果

受影响记录被锁定,也就是其它用户不能改变受影响记录中的数据

提交后的数据状态

在数据库中数据变化成为永久性的,先前的数据状态永久性的消失

所有用户/会话都可以查询到提交COMMIT后的结果

锁定的记录被释放,可以有效的被其它用户操作

所有的存储节点被清除

回滚后的数据状态

先前的数据状态被恢复

所有的存储节点被清除

锁释放

对象的管理

创建和维护表

数据库对象

数据库的组成部分

表

约束

视图

索引

序列

同义词

创建表

create table A( aid NUMBER, name VARCHAR2(12), year NUMBER);

标明主键，主键一般用于标识用户ID

字段名 数据类型

修改表

添加列

默认将添加到最后一列,不能指定添加的位置

alter table 表名 add 列名类型alter table tb_shop add memo varchar2（100）；

修改列的数据类型

已有行的数据必须为空

修改数据类型的长度

修改长度原则

数值型修改长度

当长度向小改时,已有行的数该列必须为空;当长度向大改时,可以随意修改

字符型修改长度

当长度向小改时,只要修改后的值能容纳下当前已有数据的最大值即可,当长度向大改时,可以随意修改

ALTER TABLE DOSSIER MODIFY (SEX CHAR(2));

修改列的名字

ALTER TABLE DEPT1 RENAME COLUMN SEX TO 性别

修改列的默认值

默认值的修改不会影响已经存在的行,只影响新增加的行

ALTER TABLE DOSSIERMODIFY (SEX DEFAULT '男')

删除列

删除列原则

列可以有也可以没有数据

表中至少保留一列

列被删除后,不能再恢复

被外键引用的列,不能被删除

删除一列

删除多列

Truncate 册删除所有数据

truncate table 表名

删除表

DROP TABLE 表名

表中所有的数据和结构都被删除

任何视图和同义词被保留但无效

所有与其相关的约束和索引被删除

任何未完成的事务被提交

增删改查语句

select*from 表名（where条件）

delete from 表名（where条件）

update 表名set 修改的字段以及值 where条件

insert into 表名（字段名）values（该字段的值）（如果是添加该表的所有字段可以不用写字段名）

修改表中指定列的数据类型与类型长度

alter table 表名modify 列名类型alter table tb_shop modify memo varchar2（50）

删除表中指定的列

alter table 表名 drop column列名alter table tb_shop drop column memo；

向表中添加约束

alter table表名add constraint约束名（自己定义）约束类型名称（列名）alter table tb_shopType add constraint unqu_shoptype_showname unique（shoptype）；

重命名表

RENAME EMP1 TO EMP2;

截断表

TRUNCATE TABLE table

TRUNCATE和DELETE区别

TRUNCATE是DDL,只能删除表中所有记录,释放存储空间,使用ROLLBACK不可以回滚

DELETE是DML,可以删除指定记录,不释放存储空间,使用ROLLBACK可以回滚

相关数据字典

由Oracle服务器创建和维护的表,通过数据字典,可以很容易了解当前用户的对象信息.

查询数据字典

SELECT table_nameFROM user_tables;

查看数据字典结构

DESC user_tables

dual表（虚表）

是Oracle数据库中的一个虚表，它有一行一列，可以被数据库中的所有用户使用，不能向该表插入数据，但可以使用该表来选择系统变量，或者求一个表达式的值

约束(constraint)

定义在表上的一种强制规则

作用:防止无效的数据进入到表中

primary key:P

check(not null):C

foreign key:R

unique:U

除了NOT NULL约束外,建议给其它约束起名字命名规则为:表名_列名_约束类型

约束操作

定义约束

约束通常在创建表的同时被创建

列级别约束

表级别约束

NOT NULL

只能定义在列级上

UNIQUE

唯一约束

PRIMARY KEY

主键约束

FOREIGN KEY

外键约束

CHECK

检查性约束

追加约束

在表被创建后,如果有需求也可以临时添加约束

ALTER TABLE table ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(mgr) REFERENCES emp (empno);

删除约束

ALTER TABLE emp DROP CONSTRAINT emp_mgr_fk;

启用及禁用约束

约束可以被临时禁用和启用

约束禁用

ALTER TABLE table DISABLE CONSTRAINT constraint [CASCADE];

启用约束

ALTER TABLE table ENABLE CONSTRAINT constraint

相关数据字典

USER_CONSTRAINTS

查看表上所有的约束

USER_CONS_COLUMNS

查看与约束相关的列名,该字典对于那些由系统指定名字的约束特别有用

向表中添加约束

alter table表名add constraint约束名（自己定义）约束类型名称（列名）alter table tb_shopType add constraint unqu_shoptype_showname unique（shoptype）；

视图

创建视图

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名称[(column[, column]...)]AS 子查询[WITH CHECK OPTION [CONSTRAINT 约束名称]][WITH READ ONLY]

OR REPLACE:如果视图存在就覆盖，不存在就创建，用于修改视图

NOFORCE:默认值，如果基表没有，提示错误

FORCE：如果基表没有，也会创建视图，但是视图不可用

WITH CHECK OPTION:给视图的where后的条件加约束　

WITH READ ONLY:创建的视图只能是只读的

创建复杂视图

从视图检索数据

修改视图

基表是一张表，可以对视图进行增、删、改　　　注意：尽量不要对视图进行DML操作，因为视图的主要作用是用来查询的。

复杂视图

基表是多张表，不能对视图进行增、删、改

删除视图

DROP VIEW视图名称;

相关数据字典

视图和表的区别

1)视图只是一查询语句，它的数据来自基表，相当于虚表，查询出来的数据不占内存　简单视图可以DML，复杂视图不行，尽量不要对视图进行DML，视图主要用来查询

2)表是真正存在于数据库中，占内存。可以对表进行任何操作。

3)视图的操作和表一样。

序列

创建序列SEQUENCE

CREATE SEQUENCE [schema.] 序列名[INCREMENT BY n] 每次累加n,默认是1[START WITH n]　 从n开始[MAXVALUE n | NOMAXVALUE]序列最大值/无最大值[MINVALUE n | NOMINVALUE]序列最小值/无最小值[CYCLE | NOCYCLE] 循环、不循环[CACHE n | NOCACHE]; 高速缓存中预分配序列数，默认20

序列的两个属性

currval:当前值 再执行

nextval:下一个值 先执行

可以在sq直接使用序列

insert into tab_test values（seq_test.nextval，1，'张三'）

序列的使用

NEXTVAL列返回序列生成的下一个值CURRVAL列返回序列生成的当前值

可以在sq直接使用序列

insert into tab_test values（seq_test.nextval，1，'张三'）

修改序列

序列初始化

在第一次引用CURRVAL列之前，必须引用过一次NEXTVAL列，用于初始化序列的值，否则会出现错误提示

索引

rowid

数据库表中数据的物理地址

创建索引

1）自动：主键primary key、唯一unique

2）手动:

适合创建索引

表数据量很大

要查询的结果集在2%-4%左右

经常用来做WHERE条件中的列或者多表连接的列

查询列的数据范围分布很广

查询列中包含大量的NULL值,因为空值不包含在索引中

不适合创建索引

数据量很小的表

在查询中不常用来作为查询条件的列

频繁更新的表

索引列作为表达式的一部分被使用时，比如常查询的条件是SALARY*12，此时在SALARY列上创建索引是没有效果的

查询条件中有单行函数时，用不上索引

删除索引

删除索引后，索引中的数据及定义被删除，索引所占的数据空间被释放，但表中的数据仍然存在。

同义词

创建同义词

CREATE SYNONYM E FOR EMP;

函数

单行函数

特征

单行函数对单行操作

每行返回一个结果

有可能返回值与原参数数据类型不一致

单行函数可以写在SELECT、WHERE、ORDER BY子句中

有些函数没有参数，有些函数包括一个或多个参数

函数可以嵌套

字符函数

大小写转换

LOWER

返回字符串，并将所有的字符小写

UPPER

返回字符串，并将所有的字符大写

INITCAP

返回字符串并将字符串首字母变为大写，其余变为小写

字符处理

ASCII

返回与指定的字符对应的十进制数

CHAR

参数为整数，表示某个字符的Unicode码，返回对应的字符

SUBSTR

取子串

SUBSTR('String',1,3)

如果n2省略，取第n1位开始的所有字符

如果n1是负值，表示从参数的后面往前第abs(n1)位开始向右取长度为n2的子串，如果n2是负值或者为0，结果为NULL

CONCAT

连接两个字符串Sql标准，使用不方便，需要不停的嵌套

CONCAT('Good'，‘String’)--->GoodString

LENGTH

返回字符串的长度

LENGTH('String')结果：6

INSTR

求位置

INSTR('String','r')

结果：3

LPAD

LPAD(SAL,10,'*')

结果：******5000

RPAD

RPAD(SAL,10,'*')

结果：5000******

TRIM

TRIM('S' FROM 'SSMITH')

leading：从左边去除

trailing：从右边去除

both：从左右去除，both可以省略

trim(' hello '):直接写表示去除首尾空格

结果：MITH

LTRIM

删除左边出现的字符串

RTRIM

删除右边出现的字符串

REPLACE

将一个字符串中的子字符串替换成其他的字符串

REPLACE('abc','b','d')结果：adc

数值函数

ROUND

四舍五入到小数点的第n位

ROUND(45.926,2)----->45.93

TRUNC

截取到小数点的第n位

TRUNC(45.926,2)--->45.92

MOD

取m除以n后得到的余数

MOD(1600,300)--->100

日期函数

日期可以+-数字

'10-AUG-06' +15 ---->'25-AUG-06'

SYSDATE

用来得到系统的当前日期

日期和日期之间可以减,不能加

'10-AUG-06'-'4-AUG-06' --->6天

RR日期格式

用来判定按照DD-MON-RR格式给定的日期实际代表的日期是多少

MONTHS_BETWEEN

返回两个日期类型数据之间间隔的自然月数

MONTHS_BETWEEN(SYSDATE,HIREDATE)---->查询员工服务的月数

ADD_MONTHS

返回指定日期加上相应的月数后的日期

ADD_MONTHS(HIREDATE,3)---->查询员工转正后的日期,3个月试用期

NEXT_DAY

返回某一日期的下一个指定日期

NEXT_DAY('02-2月-06','星期一')---->返回在02-2月-06之后的下一个周一是什么日期

LAST_DAY

返回指定日期当月最后一天的日期

LAST_DAY('02-2月-06')---->返回06年2月2日所在月份的最后一天

ROUND

对日期进行四舍五入

ROUND(HIREDATE,'MONTH')---->入职日期按月四舍五入

TRUNC

对日期进行截断,不考虑四舍五入

TRUNC(HIREDATE,'MONTH')---->入职日期按月截断

EXTRACT

返回日期类型数据中的年份、月份或者日

EXTRACT(MONTH FROM HIREDATE)---->员工入职月份

日期格式

yyyy/yy/y

2018/18/8

Q

季度-报表时使用

MM

月份

RM

罗马数字月份

WW/W

本年/本月第几周

DY

本周星期几

DDD/DD/D

年/月/周内第几天

HH24

一天的小时数（00-23）

MI

分钟（00-59）

SS

秒（00-59）

转换函数（显式转换）

to_char(timestamp,text)

把timestamp转换成string

to_char(int,text)

TO_CHAR(SAL,'$99,999')

可以提取年、月、日、时、分、秒

必须用单引号引起来并且是大小写敏感的

TO_CHAR(HIREDATE,'MM/YY')

注意:格式中的宽度一定要超过实际列宽度,否则会显示为###

把int4/int8转换成string

date/数值-->字符

to_ char(double precision,text)

把real/double precision 转换成 string

to_ char(numeric,text)

把numeric 转换成string

to_date(text,text)

把string 转换成date

to_ timestamp(text,text)

把 string 转换成timestamp

to_number(text,text)

把 string 转换成numeric

隠式转换

通用函数

对空值(NULL)的操作

NVL

NVL(COMM,0):如果comm为NULL，则用0来代替，否则，还是它自己。

NVL2

语法:NVL2(expr1,expr2,expr3)

如果expr1不是null,返回expr2,否则返回expr3

NULLIF

语法: NULLIF(expr1,expr2)

比较两个表达式,如果相等，返回null,否则，返回第一个表达式

COALESCE

返回第一个不为NULL的值

条件处理函数

CASE表达式

等值比较,范围比较：CASE WHEN 表达式 THEN 结果 WHEN 表达式 THEN 结果 ELSE　结果　　　　　　END

C.注意事项1）必须以END结束2）THEN和ELSE后的数据类型要保持一致3）进行范围比较时，小范围在上，大范围在下4）尽量给CASE表达式起一个别名

DECODE

decode是case的简写形式,只能适用于等值判断，不能使用区间范围。

DECODE(DEPTNO, 10,'销售部', 20,'技术部', 30,'管理部', '无')

decode是oracle独有的函数

case是sql标准，所有数据库都支持。

函数嵌套

嵌套的函数是从最里层向最外层的顺序计算的

聚合函数(分组函数)

对数据行的集合进行操作并按组给出一个结果,这个结果可以直接输出,或者用来做判断条件

MAX

MIN

AVG

SUM

COUNT

除了count(*)之外,分组函数都不包括nullcount(*)包括null值

在分组函数中使用NVL函数NVL函数可以使分组函数强制包含含有空值的记录

GROUP BY

--SELECT后面如果有分组函数，普通的列，那么普通的列一定要出现在group by后面

--在group by后面出现的列，不一定必须出现在select后面。

HAVING

分组后的条件限定(分组函数) 和group by组合使用，不能单独使用

SELECT语句的执行顺序

1.先执行FROM2.WHERE 非分组条件限定 || 表和表的连接条件3.GROUP BY 需要分组的列4.HAVING 分组条件限定5.SELECT 显示数据6.ORDER BY 排序

其他函数(自定义函数)

UID

返回标识当前用户的唯一整数

USER

返回当前用户的名字

NVL(expr1,expr2)

如果expr1为NULL，返回expr2；不为NULL，返回expr1。注意expr1和expr2两者的类型要一致

NVL2(expr1,expr2,expr3)

expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型

NULLIF(expr1,expr2)

expr1和expr2相等返回NULL，不等返回expr1

PL/SQL

数据操作

增

INSERT INTO 表名 [(列名1 [,列名n...])]VALUES (值1 [, 值n...]);

插入多条：INSERT INTO表名 子查询;--同时插入多行数据

向表中增加新列

alter table 表名 add 列名类型alter table tb_shop add memo varchar2（100）；

删

DELETE [FROM] 表名[WHERE 条件];

删除表中指定的列

alter table 表名 drop column列名alter table tb_shop drop column memo；

改

UPDATE 表名SET 列名 = 修改后的列值 [, 列名 =修改后的列值][WHERE 条件];

修改表中指定列的数据类型与类型长度

alter table 表名modify 列名类型alter table tb_shop modify memo varchar2（50）

dual表（虚表）

是Oracle数据库中的一个虚表，它有一行一列，可以被数据库中的所有用户使用，不能向该表插入数据，但可以使用该表来选择系统变量，或者求一个表达式的值

表级查询

简单(单表)查询

SELECT * from 表名 where 条件

BETWEEN...AND

ORDER BY

ASC 升序

DESC 降序

IS NULL

is not null

LIKE

_：任意一个

%：任意多个

NOT

AND

OR

IN

NULL处理:NVL(参数，n）

给列起别名，数字开头需要加双引号"123"

列名小写加""

连接操作符 || 数据库独有，使用方便，直接拼接即可

concat

消除重复行DISTINCT

Oracle默认日期格式：DD-MON-RR

关联：日期函数

多表查询

内连接

查询表中有对应关系的数据（一一对应）

等值连接

select * from emp e,dept dwhere e.deptno=d.deptno

非等值连接

外连接

表中没有关系的数据也要查询

左外连接

where d.deptno=e.deptno(+)

　　　　＝左边的表数据全显示，右边表没有对应数据的显示为空

右外连接

where e.deptno(+)=d.deptno

=右边的表数据全显示，左边表没有对应数据的显示为空

自连接

一张表通过别名的方式转换成多张表来使用

笛卡尔乘积

没有正确书写表和表的连接条件

SQL99标准写法

交叉连接(笛卡尔积)

CROSS JOIN

自然连接

对两个表之间相同名字和数据类型的列进行的等值连接

NATURAL JOIN

USING子句

指定用某个或某几个相同名字和数据类型的列作为连接条件

注意:NATURAL JOIN 子句和USING 子句相互排斥,不能同时使用

ON子句

指定任意连接条件,或指定要连接的列可以使用ON子句

左外链接

LEFT OUTER JOIN DEPT DON (E.DEPTNO=D.DEPTNO)

右外连接

RIGHT OUTER JOIN DEPT DON (E.DEPTNO=D.DEPTNO)

全连接

FULL OUTER JOIN DEPT DON (E.DEPTNO=D.DEPTNO)

子查询(放在比较运算符的右边from where having)

单行子查询

查询结果是一行一列

单行操作符:> >= < <= = <>

SELECT 字段名.……FROM表名 WHERE字段名=（SELECT MAX（字段名）FROM EMP）

示例：SELECT EMPNO，ENAME，SAL，HIREDATE FROM EMP WHERE SAL=（SELEC（SAL）FROM EMP）；

多行子查询

子查询的结果是一行一列

多行操作符:in any all

ANY

表示和子查询的其中一行结果进行比较，有一个满足条件即可。

> ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以。 相当于>min()

= ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

ALL

表示和子查询的所有行结果进行比较，每一行必须都满足条件。

> ALL:表示大于子查询结果集中的所有行，即大于最大值。 相当于>max()

= ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。

示例：SELECT ENAME，JOB，SAL，DEPTNO FROM EMP WHERE SAL>=ALL（SELECT SAL FROM EMP WHERE DEPTNO=20）；

相关子查询

示例：SELECT ENAME，JOB，SAL，DEPTNO FROM EMP A WHERE EXISTS（SELECT*FROM EMP B WHERE B.MGR =A.EMPNO）；

多列子查询

多列子查询--in

in：表示相应字段在子集合字段中对应的值。

示例：select*from emp where（sal，deptno）in（select min（sal），deptno from emp group by deptno）order by deptno：

多列子查询

子查询的结果是多行多列

多列操作符:in

子查询中空值问题

在FROM子句中使用子查询

分页查询

分页查询rownum

ROWNUM只能使用< <= =,不能使用> >= between and

ROWNUM和ORDER BY不能同时使用,否则,rownum顺序混乱无意义

是一个伪列，它会根据返回的记录生成一个序列化的数字。可以使用ROWNUM列返回查询结果集中前N条记录,能看到,但是在数据库不是真实存在de

示例：select e.*from（select rownum r，deptno，ename.job，mgr，hiredate from emp where rownum<16）e Where r>5 and r<11；

如果在子查询中需要分页rownum必须使用原名rownum

伪列rowid

是唯一标志记录物理位置的一个id，内容是当前行位于哪个文件，块，行的详细信息

高级子查询

嵌套子查询

子查询以嵌套的方式写在父查询的WHERE HAVING FROM 子句中

相关子查询

子查询不能独立运行。和主查询是互相依赖的关系。

主查询执行一行，子查询执行一圈。

　　相关子查询和嵌套子查询相比，效率低。

EXISTS操作符

NOT EXISTS操作符

分页查询rownum

是一个伪列，它会根据返回的记录生成一个序列化的数字。可以使用ROWNUM列返回查询结果集中前N条记录

示例：select e.*from（select rownum r，deptno，ename.job，mgr，hiredate from emp where rownum<16）e Where r>5 and r<11；

如果在子查询中需要分页rownum必须使用原名rownum

伪列rowid

是唯一标志记录物理位置的一个id，内容是当前行位于哪个文件，块，行的详细信息

层次查询

集合运算

UNION

联合

返回任一查询结果集包含的行,并且去除重复行,并且按照查询结果集的第一列升序排序

UNION ALL

完全联合

返回任一查询结果集包含的行,并且包含重复行,默认情况下不对结果集进行排序

INTERSECT

相交

返回多个查询结果集的共有行

MINUX

相减

返回第一个查询中存在而第二个查询中不存在的行记录

数据类型

字符类型

char

1、CHAR表示固定长度字符串，长度不够的用空格补充，最多可以存储2000字节

2、CHAR类型区分中英文，中文在CHAR中占两个字节，而英文只占一个字节

char(10):字符型 定长字符串

char(10)

字节类型

"你好"实际占6个字节+4个空字节 还是10个字节"abc"实际占3个字节+7个空字节 还是10个字节

char(10char)

字符型

"你好"实际占2个字符+8个空字符,还是10个字符"abc"实际占3个字符+7个空字符,还是10个字符

varchar2

VARCHAR2表示可变长度字符串，最多可以存储4000字节

在定义该数据类型时，应该指定其大小，与CHAR类型相比，使用VARCHAR2可以节省磁盘空间

varchar2(10):字符型 可变长度字符串

varchar2(10)

字节类型

"你好"实际占6个字节"abc"实际占3个字节

varchar2(10char)

字符型

"你好"实际占2个字符"abc"实际占3个字符

数值类型

number

可以存储正数、负数、零、定点数和精度为38位的浮点数

number(m,n)

M表示精度，代表数字的总位数；N表示小数点右边数字的位数

数值型

number

既表示整数又表示小数

number(5)

5位整数

number(7,2)

7位整数2位小数

日期类型

date

用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日，长度是7，7个字节分别表示世纪、年、月、日、时、分和秒

timestamp

用于存储日期的年、月、日以及时间的小时、分和秒值其中，秒值精确到小数点后6位，该数据类型同时包含时区信息

大对象类型

clob大字符串对象类型

用于存储可变长度的字符数据，最多可存储4GB数据，用于存储VARCHAR2类型不能存储的长文本信息

blob大二进制类型

用于存储较大的二进制对象，如图形、视频剪辑和声剪辑等，最多可以存储4GB数据

语言分类

1.操纵、2.定义、3.控制

数据定义语言DDL

用于创建、修改和删除数据库对象，如CREATE TABLE、ALTER TABLE、DROP TABLE等。DDL语句会自动提交事务

数据操纵语言DML

用于操纵数据库，包括INSERT、UPDATE、DELETE、SELECT等

数据控制语言DCL

用于执行授予权限和撤销权限的操作，包括GRANT（授予权限）、REVOKE（撤销权限）两条命令。DCL语句会自动提交事务

事务控制语言TCL

用于维护数据的一致性，包括COMMIT（提交事务）、ROLLBACK（回滚事务）和SAVEPOINT（设置保存点）3条语

DQL（数据查询语言）select+from

CCL（通用命令语言）set delete

创建用户并分配权限

默认用户

sys

执行备份、恢复、更改数据库的任务，此用户登录

超级用户，主要用于维护系统信息和管理实例，数据库中数据字典的所有表和视图都在sys模式中

system

对于日常的管理任务，此用户登录

默认管理员有DBA权限，拥有Oracle管理工具使用的内部表和视图，通常通过SYSTEM用户管理Oracle数据库的用户、权限和存储等

Scott

学习用户登录

普通用户，可以执行一般SQL语句，初始密码为tiger

表空间

创建表空间

create tablespace 名字 datafile '路径' size 大小M 默认自动无限增长，每次8M

删除表空间

Drop tablespace 名字

用户

创建用户时，如果没有指定，Oracle就将system设为默认表空间，将temp设为临时表空间

创建用户

创建之后处于锁定状态

Create user 用户名identified by 密码 default tablespace表空间名字

锁定用户

alter user 用户名 account lock

解除锁定

alter user 用户名 account unlock

删除用户

删除用户时必须指定级联参数 cascade

Drop user用户名 cascade

查看用户默认表空间

select 用户名，default_tablespace from dba_users

用户登录

conn 用户名/密码

权限与角色

权限

分配权限

将该角色的某个权限赋予给用户

grant 角色[权限] to 用户名

将某个用户下面的某张表的权限赋予给用户

Grant 表数据权限 on 用户名.表名 to用户名

撤销权限

Revoke角色[权限]from用户名

角色

connect

临时用户，特别是那些不需要创建表的用户，通常赋予该角色。

resource

更为可靠和正式的数据库用户可以授予该角色，可以创建表、触发器、过程等

dba

不要把该角色轻易授予一些不是很重要的用户

数据库管理员角色，拥有管理数据库的最高权限。一个具有DBA角色的用户可以撤销任何别的用户甚至别的DBA权限