Oracle总结.docx

Oracle总结.docx

《Oracle总结.docx》由会员分享，可在线阅读，更多相关《Oracle总结.docx（84页珍藏版）》请在冰豆网上搜索。

Oracle总结

基本术语

1、关系：

一个由“行”和“列”组成的二维表。

表格的名称为关系名。

表中的每一列表示关系的一个属性，每列的名字为一个属性名。

每一行表示一个记录，代表一个物理实体。

2、域：

属性的取值范围。

3、候选键：

若表中的某一个属性或属性组的取值能够唯一标识一行记录，则该属性或属性组为候选键。

4、外键：

若X是表A的一个属性组合，同时X也是另一个表B的主属性，则X是表A关于表B的外键。

5、关系的完整性约束：

实体完整性，参照完整性，用户自定义完整性。

数据建模

1、定义：

将现实世界中客观存在的事物以数据的形式存储到计算机中并进行处理,对其进行分析,抽象,进而确定数据的结构以及数据间的内在联系.这个过程就叫数据建模。

2、要求：

能够比较真实的模拟现实世界；容易为人理解；便于计算机实现。

3、三要素：

数据结构；数据操作；完整性约束。

实体－关系（E-R）模型

1、联系：

一对一联系（1：

1）如系主任和院系的关系；一对多联系（1：

n）如教师和课程的关系；多对多联系（m：

n）如学生和课程的关系。

2、E-R图中实体用矩形表示，属性用椭圆表示，实体之间的关系用菱形表示，连接线标注联系的类型

范式

1、第一范式（1NF）：

如果关系模式R中的每一个关系r的属性值都是不可分的，那么称R是第一范式的模式。

所有的关系都必须满足第一范式。

2、第二范式（2NF）：

所有表必须符合第一范式；表中所有非主属性都完全函数依赖于任一候选键。

3、第三范式（3NF）：

所有表必须符合第二范式；表中每一个非主属性都不传递依赖于任何候选键。

Oracle体系结构

1、Oracle数据库由三种类型的文件组成：

数据文件（.DBF）、日志文件（.LOG）、控制文件（.CTL）。

其他还有一些参数文件（.ORA）

2、如果只对数据库中的信息进行查询操作，则不会产生日志信息。

日志文件的损坏造成的后果比数据文件的损坏更严重。

3、每个数据库包含多个日志文件组，每组日志文件包含一个或多个日志文件；同一组中的日志文件具有相同的信息。

4、Oracle数据库由多个表空间构成，而表空间又由许多段组成，段由多个盘区组成，盘区又由多个数据块组成。

5、数据块是最小的逻辑存储单元。

盘区是磁盘空间分配的最小单元。

6、段是用于存储对象的单位，分五种类型：

数据段、索引段、临时段、LOB段、回退段。

7、每一个表空间是由一个或多个数据文件组成，一个数据文件只可以属于一个表空间。

8、内存结构：

系统全局区（SGA）、程序全局区（PGA）、排序区（SORTAREA）、大池（LARGEPOOL）、Java池（JavaPOOL）

9、系统全局区（SGA）分为数据缓冲区、日志缓冲区、共享池。

①数据缓冲区用于存储从磁盘数据文件读取的数据，并为所有用户共享。

由许多相同大小的缓冲块（与数据块大小相同）组成。

缓冲块分为：

脏缓冲块、空闲缓冲块、命中缓冲块。

管理缓冲块的两个列表：

写入列表（DIRTY）、最近最少使用列表（LRU）。

②日志缓冲区用于存储数据库的修改信息，是一个循环缓冲区。

其大小由LOG_BUFFER参数决定。

③共享池用于缓存与SQL或PL/SQL语句、数据字典、资源锁以及其他控制结构相关的数据。

包括库缓冲区、数据字典缓冲区、用户全局区。

10、后台进程：

DBWR（数据库写进程）、LGWR（日志写进程）、CKPT（检查点进程）、ARCH（归档进程）、SMON（系统监控进程）、PMON（进程监视进程）、RECO（恢复进程）、Dnnn。

11、内存区域和后台进程合称为一个Oracle实例。

12、在多进程系统中，进程分为用户进程、服务器进程、后台进程。

13、专用服务器操作模式下，用户进程与服务器进程数的比例是1：

1，一般用于“在线事务处理”；共享服务器操作模式可以实现只运行少量的服务器进程，为大量用户进程提供服务。

14、数据字典是由一系列只读的基础表和视图组成。

数据字典中的内容都被保存在SYSTEM表空间中。

15、数据字典视图分为三类：

USER视图（包含该用户模式下所有对象的信息）、ALL视图（包含该用户模式下所有对象的信息以及该用户可以访问的属于其他模式的对象的信息）、DBA视图（包含全部数据库对象的信息）。

Oracle权限管理（系统权限、对象权限）

1、lsnrctlstart－监听器启动命令；oradim－starup－sid数据库服务名——实例启动命令

2、createuser用户名identifiedby密码－创建用户

3、grant[createsession]/connectto用户名－授权用户可以连接到数据库

4、grant[createtableto]/resource用户名－授权用户可以创建表

5、grantunlimitedtablespaceto用户名－授权用户可以使用表空间

6、revokecreatesessionfrom用户名－撤销用户连接数据库的权限…..其他类似.

7、select*fromuser_sys_privs－查询当前用户所具有的权限

8、grantselect（insert）on对象名（即表名等）to用户名－将查询（插入）权限赋给用户

9、grantallon对象名（即表名等）to用户名－将对象上的所有权限赋给用户

10、grantselect（insert）on对象名（表名等）topublic－将查询（插入）权限赋给所有用户

11、select*fromuser_tab_privs－查询当前用户对哪些对象有什么权限

12、grantupdate/insert（列名）on表名to用户名－只能修改/插入表中的固定列

13、grant权限to用户名withadminoption

14、alteruser用户名account[lock|unlock]锁定账户|解锁账户

15、dropuser用户名cascade删除用户

16、角色就是权限的集合

grantrole角色名

注：

分别对role进行权限授权，再给用户付给角色对于有些权限是不能放入角色内的，有些权限级别太高只能直接赋于用户，比如说unlimitedtablespace

角色不属于任何用户

SQL基础

1、SQL（结构查询语言）分类：

1数据操作语句（DML）：

主要包括select、insert、update、delete。

2数据定义语句（DDL）：

主要包括create、alter、drop。

3数据控制语句（DCL）：

主要包括grant、revoke。

2、表的命名规则：

1长度必须在30个字节以内（最少一个字节）

2必须以字母开头，可以包含数字、字母、下划线、$、#

3不能使用保留字

4每个列名必须在表中是唯一的且不能有重复的列名

3、多行注释用/**/,单行注释用——

4、内建数据类型

ⅰ字符数据类型

1char：

固定长度字符串，最大长度2000字节，少了补空格

2nchar：

用来存储unicode字符集，最大长度2000字节

3varchar2：

存储可变长度的字符，最大长度4000字节

4nvarchar2：

存储可变长度的unicode字符集，最大长度4000字节

ⅱ大对象数据类型

5clob⑥Nclob⑦blob⑧BFIFE

ⅲ数字数据类型

⑨number

ⅳ二进制数据类型

⑩row⑾longrow

ⅴ日期类型

⑿date：

存储日期类型

⒀timestamp：

用亚秒的粒度保存时间

ⅵ行数据类型（伪列类型）

⒁rowid（保存的是记录的物理地址）：

18位64进制,包括对象的ID（6）、相对的文件

号（3）、数据块号（6）、块内行号（3）

5、主键（primarykey）约束

1被定义为primarykey的字段或字段组中不能为空，也不能重复。

2每个表中只能有一个primarykey,但primarykey可以由多个字段组成。

例:

createtabletest

（idnumberconstrainttest_pkprimarykey,

namevarchar2（10）,

agenumber

）

或者

createtabletest

（idnumber,

namevarchar2（10）,

agenumber,

constrainttest_pkprimarykey（id）

）

或者

createtabletest

（idnumber,

namevarchar2（10）,

agenumber

）

altertabletestaddconstrainttest_pkprimarykey（id）

联合主键：

altertabletestaddconstrainttest_pkprimarykey（id,name）

删除主键约束：

altertabletestdropconstrainttest_pk

6、非空（notnull）约束

1某字段定义NOTNULL约束后，该字段中则不能包含NULL值

2一个表中可以为多个字段定义NOTNULL约束

3NOTNULL约束只能在列级定义

例：

createtabletest

（idnumber,

namevarchar2（10）notnull,

agenumber,

sexvarchar2

（2）default男—缺省时为男

）

删除NOTNULL约束:

：

altertabletestmodifynamenull

重新定义NOTNULL约束：

altertabletestmodifyagenotnull

7、唯一（unique）约束

1某字段定义了unique约束，则该字段不能包含重复的值。

2一个表中可以为多个字段定义unique约束，因此unique约束既可以在列级定义，也可以在表级定义

3若一个字段仅定义了unique约束，而没有定义NOTNULL约束，则该字段可以包含多个NULL值

例：

createtabletest

（idnumberconstrainttest_pkprimarykey,

namevarchar2（10）,

agenumber,

phonevarchar2（50）

）

altertabletestaddconstrainttest_ukunique（phone）

删除UNIQUE约束

altertabletestdropconstrainttest_uk

8、检查（check）约束

1在check约束的表达式中，必须引用表中一个或多个字段，并且表达式的运算结果必须是一个布尔值

2Check约束既可以在列级定义，也可以在表级定义

3同一字段可以定义多个check约束，也可以同时定义check约束和NOTNULL约束

创建方式同理primarykey和unique

例：

createtabletest

（idnumber,

namevarchar2（10）,

agenumber,

phonevarchar2（50）,

）

altertabletestaddconstrainttest_ckcheck（age>6andage<14）

删除CHECK约束

altertabletestdropconstrainttest_ck

9、外键（foreignkey）约束

1被定义froeignkey约束的字段的取值只能是相关表中引用字段的值或NULL值

2可以为多个字段的组合定义froeignkey约束。

因此，froeignkey既可以在列级定义，也可以在表级定义。

3自引用：

定义了froeignkey约束的外键字段和被引用的主键位于同一表中

4可以同时定义froeignkey约束和notnull约束。

例:

createtable业务员

（

业务员编号number,

姓名varchar2（10）,

constraint业务员_pkprimarykey（业务员编号）

）

createtable客户

（

客户编号number,

姓名varchar2（10）,

业务员编号number,

constraint客户_pkprimarykey（客户编号）

）

altertable客户addconstraint客户_rkforeign（业务员编号）references业务员（业务员编号）

删除FROEIGNKEY约束：

altertable客户dropconstraint客户_rk

删除外键约束时如果有别的表进行参照，要强制删除必须加cascade

10、使约束失效或有效

altertable表名disableconstraint约束名----失效

altertable表名enableconstraint约束名----有效

11、修改表

①添加列

altertable表名add（列一,列二）

如altertabletestadd（idnumber,namevarchar2（10））

②修改列数据类型

altertable表名modify（列名新类型）

如altertabletestmodify（idvarchar2（10））

③修改列名

altertable表名renamecolumn老列名to新列名

④修改表名

altertable表名renameto新表名

5删除列

altertabletestdropcolumu列名--删除某列

altertabletestdrop（列名1,列名2,…）--删除多个列（括号内不能为全部列）

altertabletestdrop（列名）cascadeconstraints

6给表和列加注解

commentontable表名is“注解内容”

commentoncolumn表名.列名is“注解内容”

12、拷贝表

createtable表1asselect*from表2—>将表2的内容复制到表1中

createtable表1asselect*from表2where1=2（恒不等式）—>仅仅将表2的结构拷贝给表1

13、表特性

createtablestudent

（

idnumberprimarykey,

namevarchar2（10）notnull,

phonevarchar2（20）unique

）

tablespaceUSERS--表空间users

storage--空间管理

（

initial64K--第一个盘区的大小

next64K--第一个盘区填满后，下一个盘区的大小

minextents1--最少的盘区数

maxextentsunlimited--最大的盘区数

pctincrease10--每个盘区相对上个盘区增长的百分比

）

pctfree10--一个块中最少要留10%的空闲空间

pctused20--当数据库的使用空间大于20%，则数据块标识为不可用

initrans1--初始事务数

maxtrans255；--最大事务数

1、查询所有数据：

select*from表名

2、查询特定列：

select列名1，列名2，…，列名nfrom表名

若要查询前N条记录，则可以使用rownum来控制

例如:

select*fromempwhererownum<7查询的是前6条记录

3、使用算数表达式

1对数字类型进行算术运算可以使用加减乘除等操作

②对日期类型的进行算术运算只可以使用加减操作

③对字符串使用连接运算符||

如selectename，ename||’的工作是’||jobfromemp

4、使用字段别名：

select列名1as别名1，列名2别名2from表名

①列名不区分大小写，别名也不区分大小写，as可以省略

②别名的字符之间不能使用空格，若要用空格，则需要用双引号括起来，如果想要保存小写，则也必须使用双引号

如selectenameas姓名，job工作，sal“Money”fromemp

5、去掉重复行使用关键字distinct：

selectdistinct列1，列2..from表名

6、空值NULL

①空值不等于0或空格

②一个数字类型的与空值相运算，结果为空值；字符串与空值相运算，结果为本字符串7、排序：

使用orderby子句，升序（asc默认），降序（desc）

1排序规则

⑴日期早的在前，晚的在后；数字小的在前，大的在后

⑵字符串按英文字母的顺序依次比较，中文则根据其拼音按英文字母顺序依次比较

⑶升序NULL在后，降序NULL在前

⑷多字段排序时，先按第一个排序，然后再按第二个排序，依次类推

2可以使用字段的别名来进行排序，也可以使用某列在该表中的列数来排序

如selectename,deptno部门编号fromemporderby部门编号desc

如select*fromemporderby6,因为sal在表中是第6列，故是按sal排序

8、条件查询where:

select*from表名where布尔表达式

①在表达式=的右边如果是字符串或者日期则要用单引号括起来

②字符串大小写敏感

③日期格式敏感，默认为日-月-年，如02-8月-2009（必须有月字）

9、比较运算符

①大于>，小于<，等于=，大于等于>=，小于等于<=，不等于!

=或者<>，不大（小）于!

>（<）

Select*fromempwheresal>=1500

②between…and…（介于两者之间）:

小的值必须放左边，大的值放右边

Select*fromempwheresalbetween1500and3000

等价于select*fromempwheresal>=1500andsal<=3000

③in和notin运算符

Select*fromempwhereenamein（‘SCOTT’,’KING’,’AAA’）

注：

如果in中有null，则忽略NULL；如果notin中有null，则结果为空

如Select*fromempwhereenamenotin（‘SCOTT’,’KING’,null）结果为空

10、判断空值：

isnull或者isnotnull

Select*fromempwherecommisnotnull

注：

null值和null值是永远不等的

11、逻辑运算符：

and、or、not

Select*fromempwherenotsal>3000anddeptno=10

12、模糊查询：

like

①%表示0或多个字符，_表示一个字符

Select*fromempwhereenamelike‘%N’查询名字以N结尾的信息

Select*fromempwhereenamelike‘_L%’查询名字第二个字母是L的信息

②escape表示按照什么为转义字符

Select*fromempwhereenamelike‘_|_%’escape‘|’查询名字第二个是_的信息

③单引号的处理:

使用两个单引号，前面的表示转义，后面的表示要插入的单引号

Insertintoemp（ename）values（‘Tom’’sacat’）插入的名字为Tom’sacat

13、表间数据拷贝

①整表拷贝insertinto表名1select*from表名2

两表中的字段从左到右必须对应，可以名字不同，但数据类型必须相同

②拷贝多列insertinto表名1（列1，列2,…）select列1，列2，…from表名2

列的数据类型必须对应

③向多表擦入数据

Insertall

When表达式1theninto表1

When表达式2theninto表2

When表达式3theninto表3

Select*from表4

例如：

要将emp表中部门号为10的记录插入表dept10中，部门号为20的记录插入dept20中，部门号为30的记录插入到dept30中。

Createtabledept10asselect*fromempwhere1=2

Createtabledept20asselect*fromempwhere1=2

Createtabledept30asselect*fromempwhere1=2

Insertall

Whendeptno=10thenintodept10

Whendeptno=20thenintodept20

Whendeptno=30thenintodept30

Select*fromemp

14、通过select查询更新

①update+select

例如:

将dept10中empno=7782员工的记录更新为dept20中名字为SCOTT的记录

updatedept10set（empno,name,job,mgr,hiredate,sal,comm,deptno）

=（select*fromdept20wherename='SCOTT'）whereempno=7782

若去掉最后的whereempno=7782,则将会把dept10中的所有记录更新为dept20中名字为SCOTTt的记录

②delete+select

例如：

将和名为SCOTT的在同一个部门的员工从emp表中删除

deletefromempwheredeptno=（selectdeptnofromempwherename='SCOTT'）

函数

一、数值函数

1、abs（）：

返回绝对值

selectabs（100）,abs（-100）,abs（0）fromdual返回结果为100，100，0

2、sigh（）：

返回正负值（正数返回1，负数返回-1，0返回0）

selectsign（100）,sign（-100）,sign（0）fromdual返回结果为1，-1，0

3、ceil（）：

返回比数值大的最小整数

selectceil（3.1）,ceil（2.8+1.3）,c