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

内嵌视图

对象视图

物化视图