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