Oracle数据库知识.docx
- 文档编号:30215797
- 上传时间:2023-08-07
- 格式:DOCX
- 页数:32
- 大小:95.08KB
Oracle数据库知识.docx
《Oracle数据库知识.docx》由会员分享,可在线阅读,更多相关《Oracle数据库知识.docx(32页珍藏版)》请在冰豆网上搜索。
Oracle数据库知识
----------------------------Oracle的入门---------------------------
一、Oracle优势、特点
1、容量大:
支持20000个用户的并发用户,支持最大的数据量512PB(1024×1024GB)
2、数据安全和数据完整性
3、支持分布式数据处理
4、可移植性:
不同平台移植的时候,代码改动量很小(移植代码改动量只有4%)
二、Oracle简介
1、Oracle的两类工作:
1)DBA:
数据库的维护和管理
2)DBS:
基于Oracle做开发
2、模型:
关系-对象模型
3、Oracle数据库基于客户端/服务器(C/S)技术
三、Oracle体系结构
Oracle服务器由实例和数据库组成。
实例与数据库之间的关系:
实例存在于内存中,数据库存在于硬盘中,用户只能通过操作实例来间接操作硬盘中的数据库文件,可以多个实例对应一个数据库文件。
(Oracle并发性,多个用户操作同一个数据库,其实一个用户对应一个实例,多个实例对应一个数据库)
1、实例(instance)
1)内存结构:
程序全局区PGA,系统全局区SGA(共享池、数据缓冲区、日志缓冲区)
2)进程结构:
PMON,SMON,DBWR,LGWR,CKPT,RECO等
2、数据库(database)
1)物理结构:
数据文件,控制文件,日志文件等
2)逻辑结构:
表空间,段,区,数据块等
模式是对用户所创建的数据库对象的总称。
模式对象包括表、视图、索引、同义词、序列、过程和程序包等。
四、用户及权限管理
基本操作:
CMD命令行中输入:
sqlplus,然后输入用户名和密码进入
系统内置用户:
1)SYS/无密码:
连接输入/assysdba;
2)SYSTEM/安装时设定(admin):
输入:
system/admin
3)SCOTT/triger:
输入scott/trigger
切换用户(连接):
命名conn用户/密码;
设置行宽:
setlinesize大小数字;
设置页高:
setpagesize大小数字;
显示当前用户:
showuser;
用户管理:
1)创建用户:
createuser用户名identifiedby密码;
2)修改密码:
alteruser用户名identifiedby新密码;
3)解锁:
alteruser用户名accountunlock;
4)加锁:
alteruser用户名accountlock;
5)删除用户:
dropuser用户名;
权限管理:
1)连接:
grantconnectto用户名;
2)资源:
grantresourceto用户名;
3)表操作权限:
grant操作类型(insert,delete,update,select,all)on表名to用户名;
收回权限:
revoke权限from用户名;
----------------------------SQL基础与SQL函数----------------------------
一、Oracle的数据类型
字符类型
char
固定长度(2000)
varchar2
可变长度(4000)以国际标准的字符为单位
汉字占2个位置
nchar
固定长度。
以NLS国家(本地)语言为字符单位
汉字占1个位置
nvarchar2
可变长度
数值类型
number(p,s)
支持所有的SQL的数值类型
p:
表示数值的总位数。
可省略
s:
表示精确的位数。
可省略
s为正数时:
表示小数点后精确s位
s为负数时:
表示小数点前精确s位
小提示:
number(*,n)整数是任意,小数点取n位
时间日期类型
date
包含:
年月日时分秒
timestamp
包含:
年月日时分秒(秒精确到6位)
小提示:
设置当前会话中的日期显示格式
时间表示方式:
年yyyy、月mm、日dd、时hh、分mi、秒ss
altersessionsetnls_date_format='yyyy-mm-dd’;
空值类型
null
无值
大对象类型
blob
大二进制对象类型,容量4GB
clob
大字符对象类型,容量4GB
long
大字符对象类型,容量2GB(可变)
bfile
二进制文件对象类型,容量4GB
raw
非结构的二进制,容量2000Byte
longraw
非结构的二进制,容量2GB
特殊类型(伪列)
rowid
伪列,可查不可更新。
(编码标识)
Rownum
伪列,可查不可更新。
(数字标识)
二、回顾SQL基础
SQL主要以下几部分组成:
1、DDL数据定义语言:
主要是以create、alter、drop开头
2、DML数据操纵语言:
insert、delete、update、select等
3、DCL数据控制语言:
主要是以grant、revoke开头
4、TCL事务控制语言:
commit、rollback、savepoint等
5、FCL流程控制语言:
分支、循环语句等
DDL练习:
创建以下几个表:
添加适当的约束
学生信息表
课程表
成绩表
学号
课程编号
成绩id
姓名
课程名称
学号(外键)
性别
备注说明
课程编号(外键)
年龄
笔试成绩
生日
机试成绩
地址
DML应用:
1、基本操作
1)插入:
insertinto表名[(字段列表)]values(值列表);
2)删除:
delete表名[where条件]--无条件删除所有
3)修改:
update表名set字段名=值[,字段名=值….][where条件]
----无条件修改所有
4)查询:
select<字段列表|*>from表名[where条件]
2、常用符号:
1)关系:
>,>=,<,<=,=,!
=或<>
2)逻辑:
and、or、not
3)区间:
between小值and大值;
4)包含:
in(值列表);
5)模糊:
like‘模糊字符串’;
通配符:
%:
匹配任意个字符,_:
匹配单个字符
6)空值:
isnull,判断非空:
isnotnull
3、聚合函数:
count、max、min、sum、avg
4、复杂查询:
1)单表查询
select<字段列表|*>from表名
[where分组前条件]
[groupby分组字段]
[having分组后条件]
[orderby排序字段]
2)多表联接
1、内联接:
innerjoin:
取公共字段一样的部分。
2、外联接:
1)左外:
left[outer]join:
显示所有left左边的表中的数据,第二表无对的数据全部为null
2)右外:
right[outer]join:
同左外思路一样。
3)完整:
full[outer]join:
显示两表所有数据。
一样的合并
3、交叉联接:
crossjoin。
(可以直接使用,逗号替换crossjoin)
注:
crossjoin条件为:
where
3)子查询
1、子查询可以做为查询的条件。
注:
当子查询的结果集为多个时。
不能直接使用=,>,<…..之类的符号
解决办法:
=和!
=使用:
in和notin来替代
满足所有结果集:
在>或<后面跟:
all
满足结果集中任意某:
在>或<后面跟:
any,some
2、子查询做为临时表:
示例1:
select*from(子查询)别名;
将子查询的结果集做为一个表使用。
4)集合查询
1)union和unionall
2)交集:
intersect:
取相交的数据(完全一样)
3)减集:
minus:
用第一个结果集减去和第二个结果集中一样的数据。
5、排名(分析)函数:
所有的排名函数必须和over子名一起使用
over([partitionby分组列]orderby排序列)
1)row_number():
编号不并列不跳空。
例:
1,2,3,4……
2)rank():
编号会并列,且会跳空。
例:
1,2,2,4,5,6……
3)dense_rank():
编号会并列,但不跳空。
例:
1,2,2,3,4,4,4,5…..
三、SQL函数
1、字符函数
1)lower(字符串):
将字符串转换为小写字母
2)upper(字符串):
将字符串转换为大写字母
3)length(字符串):
计算字符串的字符数量。
4)lengthb(字符串):
计算字符串的字节数。
5)trim,ltrim,rtirm(字符串):
去掉空格。
trim两边,ltrim左边,rtrim右边
6)concat(字符串1,字符串2):
连接字符串1和字符串2。
可以直接使用||连接
7)instr(源字符串,查找字符串[,开始位置]):
从“源字符串”中查询出“查找字符串”第一次出现的位置。
8)substr(源字,开始,长度):
从“源字”中截取“开始”位置的“长度”个字符。
9)replace(源字,旧字,新字);
从“源字”字符串中将“旧字”的字符串用“新字”的字符串替换。
如果没有“新字”就是直接将“旧字”删除。
注:
oracle中位置是从1开始。
和sqlserver一样。
2、数学函数
1)abs(x):
x的绝对值
2)floor(x):
返回小于等于x的最大整数值
3)ceil(x):
返回大于等于x的最小整数值
4)power(x,y):
求x的y次幂
5)round(x,y):
将x精确到小数点后y位。
注:
无y表示取整。
(四舍五入)
6)trunc(x,y):
将x精确到小数点后y位。
注:
无y表示取整。
(不四舍五入)
3、日期函数
1)add_months(日期,数字):
返回“日期”加上“数字”月之后的日期。
2)last_day(日期):
返回“日期”月的最后一天的日期
3)months_between(日期1,日期2):
返回“日期1”到“日期2”之间的月份差。
(日不同返回小数)
4)next_day(日期,本地星期):
返回当前”日期”后第1个“本地星期”的日期5)round(日期,格式):
将“日期”精确到“格式”。
(四舍五入)
6)trunc(日期,格式):
将“日期”精确到“格式”。
(不四舍五入)
----------------------------Oracle中的分区表和锁----------------------------
一、表空间:
是数据库的逻辑结构,是数据文件之上的逻辑划分,可以由多个数据文件组成
创建表空间:
createtablespace名字
datafile‘数据文件.dbf’,--完整路径
size大小(k或m),
[autoextendon];--自动增长
修改数据文件大小
alterdatabasedatafile‘数据文件.dbf’resize大小;
修改自动扩展
alterdatabasedatafile‘数据文件.dbf’autoextendon或off;
改名
altertablespace原名renameto新名;
追加表空间的数据文件
altertablespaceadddatafile‘数据文件.dbf’size大小;
二、分区表:
将一张表划分为多个分区。
每个分区可以存储在不同的表空间中(不同的磁盘上)
1、作用
•用户可以只对特定分区进行操作
•将不同的分区存储在不同的磁盘,提高访问性能和安全性
•可以独立地备份和恢复每个分区
2、分类
1)范围:
以表中的一个列或一组列的值的范围分区,只能取下限。
createtable(…..)partitionbyrange(分区字段)(
partition分区名valueslessthan(值)[tablespace表空间名],
partition分区名valueslessthan(值)[tablespace表空间名],
….
partition分区名valueslessthan(maxvalue)[tablespace表空间名],
);
2)列表
createtable(…..)partitionbylist(分区字段)(
partition分区名values(值列表)[tablespace表空间名],
partition分区名values(值列表)[tablespace表空间名],
….
partition分区名values(default)[tablespace表空间名],
);
3)散列:
系统自动的随机将数据放到个个分区中
createtable(…..)partitionbyhash(分区字段)(
partition分区名[tablespace表空间名],
partition分区名[tablespace表空间名],
….
partition分区名[tablespace表空间名],
);
或
createtable(…..)partitionbyhash(分区字段)partitions分区数量;
注:
通过查询看:
select*frimuser_tab_partitions//查看分区的名字
4)复合:
就是在分区中又创建了子分区
createtable()
partitionby分区类型(字段)
subpartitionby子分区类型(字段)
(
partition分区名1valueslessthan(值)[tablespacespace]--示例范围分区
(
subpartition子分区1values(值列表),--示例列表分区
……
),
……..
)
3、分区操作
1)查询:
select*from表名partition(分区名);
2)删除:
delete表名partition(分区名);
3)修改:
update表名partition(分区名)set字段=值….;
4、分区维护
1)添加:
1、范围时:
altertable表名addpartition新分区名valueslessthan(值)[tablespace表空间名];
注:
1)添加的新分区范围必须大于最后一个2)如果使用了maxvalue就不能添加
2、列表时:
altertable表名addpartition新分区名values(值列表)[tablespace表空间名];
3、散列时:
altertable表名addpartition新分区名[tablespace表空间名];
2)拆分:
1、范围时:
altertable表名splitpartition原分区at(值)into(partition新分区1,partition新分区2);
2、列表时:
altertable表名splitpartition原分区values(值)into(partition新分区1,partition新分区2);
3)合并:
altertable表名mergepartitions分区1,分区2.intopartition合并后分区名;
4)改名:
altertable表名renamepartition源分区to新分区;
5)删除:
altertable表名droppartition源分区;//直接删除分区,并删除数据
6)清空:
altertable表名truncatepartition源分区;//直接清空数据,速度比delete快
7)设置表空间:
altertable表名movepartition分区名tablespace表空间;
三、锁:
是数据库用来控制共享资源并发访问的一种机制,保证数据的完整性和一致性。
1、分类:
1)按保护的对象:
数据锁、字典锁、内部锁。
2)数据锁主要有:
表级锁(TM)、行级锁(TX)
3)基本锁模式:
共享锁(sharelocks)、排它锁(exclusivelocks)
系统锁模式、级别
级别
锁模式
描述
0
none
无
1
null
空
2
rowshare
行共享rs
3
rowexclusive
行排它rx
4
share
(表)共享锁s
5
sharerowexclusive
(表)共享行排它
6
exclusive
(表)排它锁x
2、加锁:
1、做更新时,自动的加行排它锁。
2、行级锁:
select*from表名[条件]
[forupdate]--行排它锁
[wait秒或nowait];--判断是否已经上锁。
(避免无限挂起)
3、表级锁:
locktable表名in锁模式mode;
锁模式:
rowshare、rowexclusive、share、sharerowexclusive、exclusive
3、解锁:
commit提交或rollback回滚,以后就自动的解锁。
4、死锁:
多个会话同时锁定了某表,此时所有的会话都无法操作,出现死锁状态,死锁系统无法自动解除。
示例1:
两个会话,同时给某表上共享锁,然后都做更新操作。
示例2:
1)会话1更新编号为1的数据,会话2更新编号为2的数据。
2)会话1更新编号为2的数据
3)会话2更新编号为1的数据。
----------------------------数据库对象----------------------------
模式是数据库对象的集合。
一、同义词(synonym):
是Oracle对象取的一个别名。
主要作用:
隐藏对象名和模式名,提高数据安全性。
分类:
私有同义词、公有同义词
私有:
create[orreplace]synonym同义词名for对象名;
公有:
createpublicsynonym同义词名for对象名;
删除:
drop[public]synonym同义词名
注:
1、私有同义词不能和当前模式下的任何对象同名。
2、使用同义词必须要有同义词对应对象的访问权限。
3、私有和公有同名时,使用私有同义词
二、序列(sequence):
按一定的规则提供连续数字的对象。
创建:
createsequence序列名
[incrementby步长]
[startwith起始值]
[maxvalue最大值]
[minvalue最小值]
[cycle或nocycle]--cycle循环(设置后maxvalue必须大于cache值),nocycle不循环
[cache缓存值]--默认为20
修改:
altersequence序列名参数设置;
删除:
dropsequence序列名;
序列的属性:
nextval:
下一个序列值。
(非循环序列,最后一次不能使用)
currval:
当前的序列值。
(第一次不能使用)
三、视图(view):
是一个虚似表,其数据引用于原始基表。
优点:
1)提供了另外一种级别的表安全性
2)隐藏数据的复杂性
3)简化的用户的SQL命令
4)隔离基表结构的改变
5)通过重命名列,从另一个角度提供数据
创建:
create[orreplace]view视图名as查询语句
[withcheckoption]--更新视图数据时,数据必须满足视图的查询条件
[withreadonly];--只读
注:
使用rownum时必须取别名
删除:
dropview视图名;
键保留表:
视图中对多表操作时,某表的主键在视图中依然可以做为主键使用。
那么某表就是键保留表。
只能操作键保留表,不能操作非键保留表。
四、索引(index):
用于提高查询速度的对象。
单独的存储在硬盘上。
优点:
提高查询的速度。
缺点:
降低更新的速度。
所有索引的应用条件:
1、数据量大2、索引列频繁的做为查询的条件使用。
索引的类型:
1)普通:
createindex索引名on表名(字段名升或降);
在查询时使用了索引“字段名”,那么系统自动的会使用索引。
适用环境:
数据重复较少。
2)唯一:
createuniqueindex索引名on表名(字段名升或降);
适用环境:
数据不重复
3)组合:
createindex索引名on表名(字段1,字段2);
当查询同时使用了字段1和字段2做为条件时或单独使用字段1时,会启用索引
仅仅将字段2做为查询条件时,并不会启用索引
适用环境:
2个字段经常一起做为条件查询
4)反向键:
createindex索引名on表名(字段名升或降)reverse;
适用环境:
列中的数据前面的相似度高。
示例:
wuhan20130001,wuhan20130002,wuhan20130003…..
反向键:
10003102nahuw,20003102nahuw,30003102nahuw….
5)位图:
createbitmapindex索引名on表名(字段名升或降);
适用环境:
列的数据重复度高。
6)基于函数:
createindex索引名on表名(函数名(字段名));
适用环境:
列和函数一起使用。
7)分区:
全局(global)分区:
将所有分区表的数据做为一个整体建立索引。
本地(local)分区:
将所有分区都独立的建立索引。
createindex索引名on表名(字段名)global或local
[partitionby分区类型(字段)
(
partition分区1…….,
partition分区2…..
)
];
适用环境:
分区表适用。
8)索引组织表:
createtable表名(….)organizationindex;
适用环境:
表中数据几乎是固定不变。
----------
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 知识