Oracle 基础--【表空间与表】【约束】【视图】
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wangtongxue123456/article/details/79681846
- Oracle 基础
- 用户和表空间
- 用户
- 表空间
- 查看用户的表空间
- 设置用户默认或临时表空间 (普通用户没有次权限)
- 查看表空间储存位置
- 表
- 表中的数据类型
- 创建表
- 修改表
- 操作表中数据
- 约束
- 非空约束
- 主键约束
- 外键约束
- 唯一约束
- 查看约束
- 检查约束
- 默认值约束
- 视图
- 关系视图
- 内嵌视图
- 对象视图
- 物化视图
- 用户和表空间
Oracle 基础
用户和表空间
用户
- #### 系统用户
- sys,system （系统用户）
- sysman (操作企业管理器)
- scott
- #### 创建 scott
sys@CNHTM> create user scott identified by tiger; --创建 scott 用户并设置密码 tiger
sys@CNHTM> grant connect,resource to scott; --赋予权限- #### 登录
[USERNAME/PASSWORD][@SERVER][as sysdba|sysoper]
-- 如果连接远程orcle 需要输入用户名
orcle 就是设置自己设置的服务名
--连接登录实例
sql>connect scott/tiger- #### 查看用户登录
show user- #### desc dba_users数据字典（desc 查看表结构）
desc dba_users表空间
我们知道oarcle数据库真正存放数据的是数据文件（datafiles），Oarcle表空间（tablespaces）实际上是一个逻辑的概念，他在物理上是并不存在的，那么把一组data files 捻在一起就成为一个表空间。
表空间属性：
一个数据库可以包含多个表空间，一个表空间只能属于一个数据库 一个表空间包含多个数据文件，一个数据文件只能属于一个表空间 表这空间可以划分成更细的逻辑存储单元
- 查看用户的表空间
- 数据字典 表空间 dba_tablespaces、user_tablespaces 数据字典（用来查看登录用户所有的表空间） 前一个是为管理员提供，后一个为普通用户提供 查看用户表空间 dba_users,user_users 数据字典 查看用户默认表空间和临时表空间 SELECT DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='SYSTEM' -- USERNAME 用户名
- 设置用户默认或临时表空间 (普通用户没有次权限)
ALTER USER username DEFAULT|TEMPORARY TABLESPACE tablespace_name
--username 登录用户名
--DEFAULT 默认表空间 TEMPORARY 临时表空间
--tablespace_name 表空间名
--实例
SQL> ALTER USER system DEFAULT TABLESPACE system;- #### 创建表空间
CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'XX.dbf' SIZE xx
--TEMPORARY 创建临时表空间时添加
--tablespace_name 表空间的名字
--XX.dbf 文件名
--SIZE 数据文件的大小
sql>create TABLESPACE test1_tablespace datafile 'test1file.dbf' size 10m --创建永久表空间
sql>create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10m --创建临时表空间- 查看表空间储存位置
数据字典 dba_data_files
- #### 修改表空间的状态
ALTER TABLESPACE tablespace_name OFFLINE|ONELINE
--实例
sql> ALTER TABLESPACE test1_tablespace OFFLINE --脱机状态
sql> select status from dba_tablespace where tablespace_name='test1_tablespace' --查看表空间状态
sql> ALTER TABLESPACE test1_tablespace onelie --联机状态- #### 修改表空间只读或可读写状态(脱机状态无法修改）
ALTER TABLESPACE tablespace_name READ ONLY | ORAD WRITE
--默认联机状态就是读状态
SQL> alter tablespace test1_tablespace read only --只读状态
sql> alter tablespace test1_tablespace read write --读写状态- #### 表空间修改数据文件
- 增加数据文件
ALTER TABLESPACE tablespace_name ADD DATAFILE 'XX.dbf' size xx - 删除数据文件(不能删除表空间的第一个数据文件及创建表空间时创建的数据文件)
ALTER TABLESPACE tablespace_name DROP DATAFILE 'XX.dbf'
- 增加数据文件
- #### 删除表空间
DROP TABLESPACE tablespace_name [INCLUDING CONTENTS]
--INCLUDING CONTENTS 添加后 会把该表空间下的数据文件也一并删除表
- ### 什么是表 表存在表空间中，表是数据库的基本储存单位，表为二维结构，有行和列组成
- ### 表的约定 1、每一列数据必须具有相同数据类型 2、列名唯一 3、每一行数据的唯一性
- 表中的数据类型
- 字符型 CHAR(N) -n max：2000、NCHAR(N) -n max:1000 不可表 VARCHAR2(N)-n max 4000,NVARCHAR2(n) -n max:2000 可变
- 数值型 NUMBER(p,s) number类型的语法很简单：number(p,s)： p：精度位，precision，是总有效数据位数，取值范围是38，默认是38，可以用字符*表示38。 s：小数位，scale，是小数点右边的位数，取值范围是-84~127，默认值取决于p，如果没有指定p，那么s是最大范围，如果指定了p，那么s=0。 最高整数位数＝p-s s正数，小数点右边指定位置开始四舍五入 s负数，小数点左边指定位置开始四舍五入 s是0或者未指定，四舍五入到最近整数 当p小于s时候，表示数字是绝对值小于1的数字，且从小数点右边开始的前s-p位必须是0，保留s位小数。 p>0，对s分2种情况： 1. s>0 精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；如果s>p，小数点右边至少有s-p个0填充。 2. s<0 精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s| FLOAT(n)
- 日期型 DATE --储存范围 公元前4712年1月1日到公元前9999年12月31日 可以精确到秒 TIMESTAMP -- 可以精确到小数秒
- 其他类型 BLOB --最大储存4个G ,储存二进制 CLOB --最大储存4个G ，储存字符串
- 创建表 create table table_name( column datatype);– 创建表的语法
- 修改表 添加字段 alter table table_name add column_name datatype;
更改字段的数据类型
alter table table_name MODIFY column_name datatype;
删除字段
alter table table_name DROP COLUMN column_name;
修改字段名
alter TABLE table_name RENAME COLUMN column_name TO new_column_name;
修改表名
RENAME table_name to new_table_name;
- ### 删除表 TRUNCATE 删除表中数据（比delete速度快） TRUNCATE TABLE table_name;
DROP 删除表 DROP TABLE table_name;
- 操作表中数据
- #### 添加数据
INSERT INTO table_name (column1,…)values(value1,…)
- #### 复制表中数据
- 在建表的时候复制 CREATE TABLE table_new AS SELECT column1,.. | * from table_old;
- 再添加时复制
INSERT INTO table_new [(column1,…)] select column1,…|*from table_old;
- 删除表中数据
– DELTE 语句 DELETE FROM table_name [WHERE conditions];
约束
约束条件也称作完整性约束条件，是在数据表上强制执行的一些数据检验规则，当执行DML操作时必须符合约束条件，否则不能成功执行。 约束条件包括有非空(Not null)、唯一(Unique)、主键(Primary Key)、外键(Foreign Key)和检查(Check)。 约束条件可以在建表时建立也可以在建表后建立。约束条件需要命名，可以由用户指定，否则由系统按照SYS_Cn的默认格式来命名，n是一串数字。
非空约束
约束字典 user_constraints
非空（Not Null）约束用于确保字段值不为空。非空约束是五个约束条件中唯一一个只能定义在列级的约束条件。非空约束条件可以在建表时建立，也可以在建表后建立。
创建表时添加非空约束
CREATE TABLE table_name(
column_name datatype NOT NULL,..
)建表后添加非空约束
ALTER TABLE table_name MODIFY(column datatype NOT NULL);建表后去除非空约束
ALTER TABLE table_name MODIFY(column datatype NULL);主键约束
主键是能确定一条记录的唯一标识的一个字段和多个字段组合 主键约束条件从功能上看相当于非空且唯一。主键可以是单字段也可以是多字段组合。在一个表上只允许建立一个主键，而其他约束条件则没有明确的个数限制。
在创建表时添加剂主键
CREATE TABLE table_name(
column_name datatype PRIMARY KEY
)
CREATE TABLE table_name(
column_name datatype，
constraint constraint_name PRIMARY KEY(column1_name,..)
)
--constraint_name 约束的名字在修改表时添加主键
ALTER TABLE table_name ADD CONSTRAINT column_name PRIMARY KEY(column_name1,...)
--column_name 约束的名字更改主键约束的名字
ALTER TABLE table_name rename CONSTRAINT old_name TO new_name删除主键约束
ALTER TABLE table_name
DISABLE | ENABLE CONSTRAINT constraint_name;
--DISABLE 禁用约束 ， ENABLE 启用被禁用约束
--constraint_name 约束名字
--删除约束 (两种都可以)
ALTER TABLE table_name
DROP CONSTAINT constraint_name
ALTER TABLE table_name
DROP PRIMARY KEY[CASCAD]外键约束
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键,外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字 外键约束条件定义在两个表的两个字段或一个表的两个字段上，用于保证相关两个字段的关系。外键约束包括两个方面的数据约束： （1）：子表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL； （2）：当主表参照的值被子表参照时，主表的该行记录不允许被删除。
CREATE TABLE table1(
column_name datatype REFERENCES table2(column_name,...)
)
CREATE TABLE table1(
column_name datatype,
CONSTRAINT constraint_name FOREIGN KEY(column_name)REFERENCES table_name(column_name)[ON DELETE CASCADE]
)
-- constraint_name 外键约束名
-- table_name 关联的主表
-- ON DELETE CASCADE 可选 选择后增加级联删除的功能修改表的时候添加外键约束
ALTER TABLE table1_name CONSTRAINT constraint_name FOREIGN KEY(column_name)REFERENCES table_name(column_name)[ON DELETE CASCADE]
--table1_name 添加外键的表
--table_name 关联的主表删除外键约束
--禁用外键约束
ALTER TABLE table_name DISABLE | ENABLE CONSTRAINT constraint_name
-- DISABLE 禁用主键约束
-- ENABLE 启用主键约束
--删除外键约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;唯一约束
唯一约束保证值得唯一性，区别主键，主键不允许值为空，唯一约束允许一个值为NULL，主键在一个表中只能有一个，唯一约束可以有多个。
再创建表时设置唯一约束
CREATE TABLE table_name (
column_name datatype UNIQUE
)
--第二种方式
CREATE TABLE table_name(
column_name datatype,
CONSTRAINT constraint_name UNIQUE(column_name)
)修改表时添加唯一约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_name)删除唯一约束
ALTER TABLE table_name DISABLE | ENABLE CONSTRAINT constraint_name
-- DISABLE 禁用约束
-- ENABLE 启用约束
--删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;查看约束
select constraint_name,constraint_type,status from user_constraints WHERE table_name='TESTUL';检查约束
检查约束的作用：让字段值更具有意义。 创建表时设置
CREATE TABLE table_name (
column_name datatye CHECK(expressions)
)
CREATE TABLE table_name (
column_name datatye,
CONSTRAINT constraint_name CHECK(expressions)
)在修改表时添加
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(expressions)删除唯一约束
ALTER TABLE table_name DISABLE | ENABLE CONSTRAINT constraint_name
-- DISABLE 禁用约束
-- ENABLE 启用约束
--删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;默认值约束
默认值约束的作用对象为列，每个列只能有一个默认值约束。
创建默认值约束
CREATE TABLE table_name(
column_name datatype default def_name
)修改默认值约束
ALTER TABLE table_name MODIFY column_name datatype default def_name;视图
视图是数据库中特有的对象，视图用于储存查询，但不会储存数据（物化视图外）。可以利用视图进行查询、插入、更新和删除数据.Orcle中有4中视图。
关系视图
关系视图是四种视图中最简单，同时也是最常用的视图，可以将关系视图看做对简单或复杂的定义。它的输出可以看做一个虚拟的表，该表的数据是有其他基础数据提供。由于关系视图并不储存真正的数据，因此占用数据库资源也较少。
创建关系视图
CREATE VIEW view_table_name as select * from table_name 修改关系视图
CREATE OR REPLACE view_table_name as select * from tale_name
-- CREATE OR REPLACE 命令用于创建或者替换视图。删除视图
drop view view_table_name