Oracle数据库学习One

Oracle 数据库
1、保存数据
2、管理数据，增删改查
3、保存数据
大型数据库 Oracle、db2 付费安全性高
中型数据库 sql server、mysql
小型数据库 access，sqlite

数据库的发展
1、层次模型数据库 树
2、网状模型数据库 蜘蛛网
3、关系型数据库
对象和对象之间存在关系，使用二维关系保存对象

primary key 主键 不能为空 且唯一
foreign key 外键 可以为空 若不为空 则必须在所依赖的表中依赖的字段值存在

Oracle 关系型数据库
也是一家提供产品和服务的产商

sql 第四代语言 只关心做什么，不关心怎么做
sqlplus 执行sql或者pl/sql命令并显示结果
pl/sql过程化查询语言
buffer 存放sql plus工具上最新执行的一条sql语句

四种约束 constraint
实体完整性约束：针对主键，主键值必须唯一
参照完整性约束：外键值可以为空，如果不为空，
则必须在他所依赖的表中存在
列级约束：他的值必须匹配它所定义的数据类型
用户自定义约束：check 选择性约束

五种分类：针对sql命令
查询语句
select
数据操作语言：DML
insert，update，delete
数据定义语言
create，alter，drop，rename，truncate
数据控制语言
grant授权，revoke回收权限

六种对象：数据库中的对象
表：数据库最基本的对象
视图：一张虚表，从一张表或多张表查询的数据组成的一张虚表
序列：主要用来生成主键的值
同义词：为对象取别名
存储单元：pl/sql
将一条或者多条语句通过判断或者循环连接

登陆 sqlplus
切换 conn
展示用户 show user
切换语言 alter session set nls_date_language=english
启动数据库 startup
启动监听器 lsnrctl start
关闭数据库 shutdown

查询语句
select 内容 （加空格 加别名 使用“”可以区分大小写和特殊字符）
from 地方 （没有地方用dual代表）

处理空值
nvl(可能出现空值的字段，默认值)

字段拼接 ||

以下操作需要先定位
替换内容 c
追加内容 a
插入一行 i
删除一行 del

save filename 把文件命令保存在工具上
get filename 把文件命令显示在工具上
start filename 执行文件命令
spool filename 将工具中内容写入文件中
spool off 结束写入 必须一起用
exit 退出

设置字段格式
col 字段名 for a数字
clear col；

排序 order by
升序 asc
降序 desc
若按多个排序，先按第一个相同后，按第二个排序
空值默认为无穷大
最后执行

限定性查询 where
放在 from之后
后面跟 表达式 字段名 比较符 常量值
用单引号区分大小写
between 小数 and 大数
in(list) 在集合中
like 模糊匹配
is null 是空
and 逻辑与 or 逻辑或 not 逻辑非

通配符
%可以匹配0个或者多个任意字符
_可以匹配一个任意字符

转义符 escape ‘转义字符’
转义 转义字符 后面的 字符

单值函数
lower 转为小写
upper 转为大写
initcap 首字母大写其他小写
concat 将两个字符合成一个
substr 求子串
length 求长度
nvl 处理空字符串
数值上的单值函数
round 四舍五入
trunc 只舍不取
mod 取余
日期上的单值函数
sysdate 当前时间
add_months(,)在一个日期后加上月数的日期
next_day(,)即将来的星期几 是几号
last_day 一个日期所在月的最后一天的日期
round四舍五入 根据什么四舍五入就看比他小一级的
trunc 只舍不取 只看比他小一级的
转换函数
to_char将日期、数字转换为字符类型
$货币符号 分隔符l显示本地符号
9不满位不会强制补齐
0不满位会强制补齐

千年虫问题
rr 0-49代表本世纪 50以上代表上世纪
yy 代表和当前时间所处的同一个世纪

多表查询
实质也是一张表的查询
多张表连接一起会产生笛卡尔积
通过最少n-1个条件消除笛卡儿积
1、等连接
2、不等连接
3、外连接（+）放在查询数据少的一方
。。right/left join 。。 on
4、全连接
5、自连接
6、集合连接
union 并集
union all 并集不会消除重复的行
minus 差集
intersect 交集
rownum 伪列
rowid数据保存到文件中的物理位置

多值函数
avg 求平均值
count 求个数
max 求最大值
min 求最小值
sum 求和

group by 值相同的分一个组 放在where后
对组函数过滤使用having 放在group by 后
出现在select 后的字段，没出现在组函数中。就必须放在group by后面
多个字段同时分组，多个字段值都想用分为一组

sql 执行流程
select 5
from 1
where 2
group by 3
having 4
order by 6

嵌套查询
where字句中
from 子句中
having子句中
注意子查询返回的是单值还是多值

数据库设计的步骤
1、需求分析
2、设计阶段
3、建立文档 把需求分析和er图通过文档描述
4、建立表实例图
5、产品 写建表语句
实体关系模型图
实体 属性 关系
#代表唯一
*代表不为空
o代表任意
有连接就是有关系
一点是一关系
三个角 是多个关系
实现 mustbe 必须依赖另一张表 外键必须不为空
虚线 maybe 外键可以为空
联合主键 两个或两个以上构成主键
第一范式 所有字段必须是单值
第二范式 所有属性的值必须依赖uid值
第三范式 所有非uid属性不能依赖非uid值

外键（联合外键）
依赖另一张表的主键或者唯一键
只要依赖的表是联合主键就需要在该表中联合外键

创建表
create table 表名
字段名 字符类型(size) constraint 约束名 列级约束，
表级约束

命名规则
以字母开头 1-30字符 由字母数字——#$组成
不能使用相同名子 不能使用保留字
数据类型
varchar 可变长字符串
varchar2 可变长字符串 oracle特有
char 不可变长字符串
clob 定义大数据字符串 最大2G
数字的数据类型
number 定义整数
number(总位数,小数位数)d定义小数
日期的数据类型
date
二进制数据类型
blob

默认值—-插入数据时
default 默认值

列级约束以及表级约束
primary key 主键
not null 非空
unique 唯一
check 选择
foreign key 外键
查看约束名
select constraint_name
from user_constraints

列级约束 只能作用一列
表级约束 能作用多个字段
联合主键，联合外键，联合唯一键只能用表级约束

1)列级约束
d)选择性约束
1)列级约束:字段名 数据类型 取名字
check(字段名 in(值1,值2)),
2)表级约束:字段名 数据类型,
取名字 check(字段名 in(值1,值2)),
e)外键约束
1)列级约束:字段名 数据类型 取名字
references 表(字段),
2)表级约束:字段名 数据类型,
取名字 foreign key(字段)
references 表(字段),
3)联合外键:依赖的表的是联合主键，
就要用联合外键
字段名1 数据类型,
字段名2 数据类型,
取名字 foreign key(字段1,字段2)
references 表(字段1,字段2),

6)读ER图哪些数据
ER—实体关系模型图
a)有哪些实体，圆角的矩形就是一个实体。
一个实体就是一张表。
大写字母是实体名字—-后期可以作为表名
b)每个字体中有哪些字段。
用小写字母—-字段名
#代表唯一性
*代表非空
o代表没有任何要求，可以为null,
c)实体与实体之间的关系.通过外键来维护
创建表的时候需要给某张表增加外键
1)一对一:把外键放在任何的一方,
如果出现了must be,
应该把外键放在mustbe一方
2)一对多:把外键放在多的一方
3）多对多:创建一张桥表，
通过桥表把多对多的关系转换2个
一对多的关系。
4)may be可能依赖和must be必须依赖
must be外键的值不能为空
may be外键的值可以为空
d) | 代表联合主键

插入
insert into 表名（要插入的字段名）
values （值）

更新
update 表名
set 字段名 = value
where //
删除
delete from 表名
where

事务
一个操作单元中的一系列操作，是一个不可分
的原子操作，要么同时成功 要么同时失败
保证数据完整性
1、原子性
2、一致性
3、隔离性
4、持久性

正常结束
commit exit
写入数据库 之前数据丢失 回滚点擦除
rollback
回滚
恢复到事务开始前的状态
通过savepoint rollback回滚到特定位置

数据字典
根据权限可以分为以下几类
user：用户创建对象对应的数据字典表
all：用户能访问对象对应得数据字典表 all_tables..
dba：所有对象对应的数据字典表
v$：描述系统性能相关的数据字典表 v$parameter
dictionary:描述数据字典表 相关信息的数据字典表
IND：user_index
table_privileges :数据表权限

DBA人员使用 1、v$version 2 DBA
开发人员使用：
1_user只能看本用户schema下的表
user_indexs user_tables user_views
user_users user_objects user_constraint
2_all_*可以看其他用户的表（需要权限）

数据字典中常用视图的定义
1 dictionary 存放字典中所有表信息
2 user_objects 存放了当前用户下的所有对象
3 user_constraints 查看当前用户下所有约束信息
4 user_cons_column 查看和约束相关的列
5 dict_columns 可以产看数据字典中所有视图对应的列
6 user_users 存放当前用户信息
all_user 存放所有用户信息-——
7 user_indexes 存放当前用户索引信息
all_indexed 存放数据库所有用户索引信息
8 user_tables 存放当前用户所有表信息
all_tables 存放数据库所有表信息
9 user_views 存放当前用户所有视图信息
all_views 存放数据库中所有用户视图信息
10 user_synonyms 存放当前用户所有同义词
create synonym aaa for table_name

改变表列
1、增加表列
alter table table_name
add (字段名。。。。。。。。。)
2、修改表列
alter table table_name
modify (……….)
3、删除表列
alter table table_name
drop column (字段名)
注 1、修改表列同时可以增加not null 约束，不可以增加其他约束
2、缩小列的宽度 一、该列为空 二、表里没记录
三、缩小的宽度为表中最大数据宽度
3、如要改变列的类型只有该列为空 或者表里没有记录
4、增加的默认值只影响后面插入的值
5、假设一列有空值，不能加非空约束
6、删除表列

改变约束
增加约束
add constraint 约束名 type(column);
not null 只能增加在列或修改列时候完成
删除约束
alter table table_name
drop constraint constraint_name(cascade)
使约束失效 /生效
alter table table_name
disable/enable constraint constraint_name;

删除表对象
drop table table_name (cascade constraints)
不能回滚 删除表中所有数据 删除表中索引
删除表结构
重命名对象
rename s_ord to s_order;
alter table table_name
rename column column_name to new_name;
清空表
truncate table s_item;
删除表中记录 释放表所占用的空间ddl语句
不会触发触发器动作
truncate和delete的区别
truncate不可以回滚 delete可以
在删除大量数据是truncate更快
truncate可以释放表空间 delete不可以
truncate 操作表象是表 delete可以是表视图或者同义词
加注释
comment on table table_name is ‘注释’
清除注释
comment on table table_name id ”;
查询注释
select comments
from all_table_comments
where table_name = ”;

sequence 序列 能够产生连续的整数
创建sequence
create sequence name
increment by步长
start with 起始
maxvalue
minvalue
cycle/nocycle循环
cache/nocache缓冲
各项不分先后
当increment by n为正数时
默认maxvalue 为1E+27 默认minvalue 为 -1
当increment by n为负数时
默认maxvalue 为-1 默认minvalue为-1E+27
cache 可以每次预产生 15/20个
cycle 用于循环 不建议使用
使用sequence
通过伪列nextval和currval进行调用
1、nextval每次sequence下一个值
2、currnal 获得sequence当前值
注：第一次没有通过nextval调用前，不能通过currval调用
序列和表的关系
insert into table_name(id)
values (sequenceName.nextval);
修改sequence
alter sequence sequence_nam
increment by 22//之类
删除 drop sequence sequence_name;

视图view
一个或者多个表的部分数据
一条有名字的select 语句
作用:为了提高检索效率
创建视图
create (or replace) (force/noforce) view view_name
as
select ….
（with check option）
（with read only）
or replace 有相同的视图 替换
force 如果源表不存在 先创建视图 虽然有视图 但是不能使用
with check option 表示可以插入 更新删除
但是需要满足查询中的条件
with read only 只读

视图的分类
简单视图
来源于一个表 不包括函数 分组 可以进行dml操作
复杂视图
来源于多个表 包括函数以及进行分组，不能进行dml操作

修改视图 创建视图的时候加上replace
删除视图 drop view view_name;

索引 为了加快检索速度
1、创建索引
自动创建：在创建 主键 唯一键 以及使用时会生成索引
手动创建：create index index_name on table 字段名
2、使用索引场所
列频繁使用于where 字句或连接条件中
列的取值范围很广
表很大 记录数量较多
查询返回结果占记录数的百分比在2~4%
索引并不是越多越好
3、删除索引
drop index index_name
4 在数据字典上查询索引
select index_name
from user_indexes
where table_name = ”;