Oracle数据库(基本概念和常用语句)

一、基本概念

Oracle的简介

Oracle Database，又名 Oracle RDBMS，简称 Oracle。Oracle 数据库系统是美国 Oracle 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器（client/server）或B/S体系结构的数据库之一，比如 SilverStream 就是基于数据库的一种中间件。Oracle 数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系型数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能，但它的所有知识，只要在一种机型上学习了Oracle知识，便能在各种类型的机器上使用它。

超码、候选码、主码和外码

超码：一个或多个属性的集合，这些属性的组合可以使我们在一个实体集中唯一的标识一个实体；
候选码：如果任意超码的真子集不能包括超码，则称其为候选码；
主码：被数据库设计者选中的，用来在同一实体集中区分不同实体的候选码；此外，应该选择哪些从不或极少变化的属性。
超码包括候选码，候选码包括主码
例如：学生是一个实体，则学生的集合是一个实体集，而超码是用来在学生的集合中区分不同的学生。假设学生（实体）具有多个属性：学号，身份证号，姓名，性别。因为通过学号可以找到唯一一个学生，所以{学号}是一个超码，同理{学号,身份证号}、{学号,身份证号,姓名}、{学号,身份证号,姓名，性别}、{身份证号}、{身份证号，姓名}、{身份证号，姓名、性别}也是超码.在这里，因为不同的学生可能拥有相同的姓名，所以姓名不可以区别一个学生，既{姓名}不是一个超码，{性别}、{姓名、性别}也不是。只有{学号}、{身份证号}都是候选码；另外，如果性别和姓名可以唯一标识一个学生，则{姓名，性别}也为超码。

Oracle体系结构

表空间

1. 表空间是数据库中最大的逻辑单位，一个 Oracle 数据库至少包含一个表空间，就是名为SYSTEM的系统表空间。
2. 每个表空间是由一个或多个数据文件组成的，一个数据文件只能与一个表空间相关联。
3. 表空间的大小等于构成该表空间的所有数据文件大小之和。
4. Oracle 数据库中的典型表空间是：SYSTEM 表空间 、DATA 表空间、USER 表空间、TOOLS 表空间、TEMP 表空间

段

段是构成表空间的逻辑存储结构，段由一组区组成。按照段所存储数据的特征，将段分为四种类型，即数据段、索引段、回退 段和临时段。

区

区为段分配空间，它由连续的数据块组成。 当段中的所有空间已完全使用时，系统自动为该段分配一个新区。区不能跨数据文件存在，只能存在于一个数据文件中。

数据块

数据块是Oracle服务器所能分配、读取或写入的最小存储单元。 Oracle服务器以数据块为单位管理数据文件的存储空间。

模式

模式是对用户所创建的数据库对象的总称。 模式对象包括表、视图、索引、同义词、序列、过程和程序包等

创建用户

[]表示可选：
CREATE USER username —username用户名
IDENTIFIED BY password —password密码
[DEFAULT TABLESPACE tablespace] —表空间
[TEMPORARY TABLESPACE tablespace];

权限

权限指的是执行特定命令或访问数据库对象的权利权限有两种类型，系统权限和对象权限

1. 系统权限允许用户执行某些数据库操作，如创建表就是一个系统权限
2. 对象权限允许用户对数据库对象（如表、视图、 序列等）执行特定操作
   角色是一组相关权限的组合，可以将权限授予角色，再 把角色授予用户，以简化权限管理.如：DBA就是一个角色

授权

GRANT CONNECT TO 用户名：CONNECT角色允许用户连接值数据库，不可以创建数据库对象。

事务

事务在数据库中是工作的逻辑单元，单个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制，可以确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。事务具有以下特性：
1.原子性(Atomicity)：一个事务里面所有包含的SQL语句都是一个整体，是不可分割的，要么不做，要么都做。
2. 一致性(Consistency)：事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
3.隔离性(Isolation)：数据库允许多个并发事务同时对其中的数据进行读写和修改的能力，隔离性可以防止事务在并发执行时，由 于他们的操作命令交叉执行而导致的数据不一致状态。
4.持久性 (Durability) : 当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。
一组SQL语句操作要成为事务，数据库管理系统必须保证这组操作的原子性（Atomicity）、一致性（consistency）、隔离性（Isolation）和持久性（Durability），这就是ACID特性。

视图

二、数据库语句

创建表

 ysbm        varchar2(10),
 xm           varchar2(50) not null,
 xb            char(1),
 ksbm        varchar2(10),
 constraint pk_dw_doctor primary key(ysbm),---主键
 constraint fk_dw_doctor_ksbm    foreign key (ksbm)references dw.dept(ksbm),--外键  
check (xb = '1' or xb = '2')---性别
)
tablespace 表空间名;---表空间

创建一个包含现有表的数据的表
create table 表名 as(select * from 已存在的表 [where 条件])
修改表

alter table 表名 add(增加)|modify(修改)|dorp(删除) column(列)|coloumnnanme(列名)[definition]
alter table 表名 add 字段名 数据类型;---修改字段
alter table 表名 constraint 字段名;
alter table 表名 drop column 字段名;---删除
alter table 表名 modify 字段名 类型;---修改字段大小，只能改大，不能该小

重命名表名
rename 旧表名 to 新表名
删除表名（drop删除列及其内容）
drop table 表名
截断表（删除数据，表、列名没有被删除）
truncate table 表名
添加

insert into 表名[(colunmname,......)] values (datavalue,...);

更新

update 表名 set 列名=表达式|子查询，列名=[表达式|子查询],... [where 条件表达式];

删除

delete from 表名 [where 条件表达式];

事务命名

commit ---提交
rollback ---回滚

锁

lock table 表名 in exclusive mode;---排它锁

for update命令，防止其他用户获得同样记录上的锁

select * from 表名 [where 条件] for update;

查询语句select

select [all|distinct] 目标信息 from 表1 a，表2 b，... 
       [where 条件表达式]
       [group by <列名1> [having <分组条件>]]
       [order by <列名> [asc|desc]];

模糊查询like

---%:匹配任意长度子串
select * from 表名 where 列名 like '张%';
---_:匹配任意一个字符
select * from 表名 where 列名 like '张_';

集合运算

---union:并运算（union自动去重，union all不去重）
select * from 表名1 union select * from 表名2 
---intersect:交运算
---minus:差运算
---聚集函数：avg、min、max、sum、count、nvl

联查

select a.*,b.* from 表名1 a,表名2 b whrere a.列名=b.列名;

嵌套子查询

select * from 表名1 where 列名 in|not in(select * from 表名2 [where 条件表达式]);

将表2的数据插入到表1中

insert into 表名(列名1,列名2,...) select 列名1,列名2,... from 表名2 [where 条件表达式];

创建索引

---创建索引
create [unique][cluster] index 索引名 on 表名(列名[asc/desc]) tablespace 表空间名;
---删除索引
drop index 索引名;

创建视图

---创建视图
create view 视图名[(列名,列名,...)] as (子查询) [with check option];
---删除视图(若视图关联了其他视图，使用cascade删除视图)
drop view 视图名 [cascade];

创建序列号

---创建序列号
create sequence 序列号名 increment by 1 start with 1 maxvalue 999999 minvalue 1 nocycle;
---删除序列号
drop sequence 序列号名;

创建用户和表空间

create temporary tablespace jh_dw_temp  --test_temp为临时表空间的名称  一般以temp结尾（习惯）
tempfile 'D:\oracle\app\oradata\orcl\jh_dw_temp.dbf'--D:\Oracle\oradata\orcl：为安装数据库时的orcl目录，默认在c盘中，具体--位置以自己为准
size 50m--表空间的大小
autoextend on next 50m maxsize 20480m--超过初始设置的表空间的大小  开始自动以每50m扩展，最大到20480m
extent management local;--区间扩展方式   extent management 有两种方式 extent management local（本地管理）;extent --management dictionary（数据字典管理）
--创建数据表空间
create tablespace jh_dw_data
logging
datafile 'D:\oracle\app\oradata\orcl\jh_dw_data.dbf'
size 50m
autoextend on next 50m maxsize 20480m
extent management local
--创建用户并指定表空间
create user test identified by test  --user后面紧跟的是用户名  identified by 后面的是密码
default tablespace test_data temporary tablespace jtest_temp;  --刚刚创建的临时表空间和数据表空间
--给指定用户授权
grant connect,resource,dba to test;
--创建存储目录（导出时，dmp文件存到指定目录，导入时，要将dmp放）
create or replace directory platform_dir as 'd:/platform_dir'; 
--将目录操作权限赋予用户
grant read,write on directory platform_dir to  test;