Oracle基础(表空间、用户、授权、表、数据类型、数据导入导出等)

1 创建表空间

1.1 概述

1.2 语法：

– 表空间类型及名称，默认不指定类型（永久）
create [temporary | undo] tablespace 表空间名称
– 数据文件的位置及大小
datafile 存储的表空间地址 size 初始大小
– 是否自动扩展，默认 ‘off’
[autoextend off] | [autoextend on next n maxsize m]
– 是否产生日志，默认 ‘loggin’
[loggin | nologgin]
– 段空间自动管理，默认 ‘auto’ 推荐
[segment space management auto]
– 表空间管理方式，dictionary | local(默认，推荐)
[extent management local [uniform size n]]

1.3 示例：

create tablespace waterspace
datafile 'C:\oracleData\waterspace.dbf'
size 100m
autoextend on
next 10m;

2 创建用户

2.1 语法

– DBA 用户执行，默认 users 表空间（不推荐）
create user identified by ;

– 实际开发中
create user identified by
default tablespace <tablespace_name> – 默认表空间
temporary tablespace temp – 临时表空间
quota unlimited on <tablespace_name> – 表空间额度

grant create session to ; – 授权（可以登录）

2.2 示例

create user zhangsan identified by wateruser
default tablespace waterspace
temporary tablespace temp 
quota unlimited on waterspace;

grant create session TO zhangsan;

2.3 用户授权类型

grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限，允许用户登录数据库
grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限
grant drop on tablename to zhangsan;//授予删除表的权限
grant insert on tablename to zhangsan;//授予插入的权限
grant update on tablename to zhangsan;//授予修改表的权限
grant insert(id) on tablename to zhangsan;
grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update
grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限
grant dba to username;//授予DBA权限

3 表的创建、修改、删除

3.1 表创建

3.1.1 概述

1. 表：用于 ‘存储数据’ – 是我们最常见的数据库对象
2. 表设计注意事项
   (1) 表设计时，尽量遵从 ‘第三范式（3NF）’
   (2) 名称不能超过 30 个字符 – 超过会报错
   (3) 名称只能以 ‘字母’ 大头，可由’数字’、 ‘_’、 ‘$’ 或 ‘#’ 组成

3.1.2 语法

CREATE TABLE schema_name.table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ...
    table_constraint
 );

3.1.3 示例

-- 创建业主表（T_OWNERS）
create table T_OWNERS(
        ID NUMBER PRIMARY KEY, -- 主键
        NAME VARCHAR2(30) NOT NULL, -- 业主名称
        ADDRESSID NUMBER NOT NULL,  -- 地址ID
        HOUSENUMBER VARCHAR2(30) NOT NULL, -- 门牌号
        WATERMETER VARCHAR2(30) NOT NULL, -- 水表编码
        ADDDATE DATE NOT NULL, -- 登记日期
        OWNERTYPEID NUMBER NOT NULL, -- 业主类型ID
        CONSTRAINT FK_T_OWNERS_ADDRESSID FOREIGN KEY(ADDRESSID) REFERENCES T_ADDRESS(ID),
        CONSTRAINT FK_T_OWNERS_OWNERTYPEID FOREIGN KEY(OWNERTYPEID) REFERENCES T_OWNERTYPE(ID)
);

3.1.4 表的数据类型

1、字符型
(1)CHAR：固定长度的字符类型，最多存储2000个字符
(2)VARCHAR2：可变长度的字符类型，最多存储4000个字节
(3)LONG：大文本类型，最大可以存储2个G

2、数值型
NUMBER：数值类型
例如：NUMBER(5) 最大可以存储数为 99999
NUMBER(5,2) 最大存储的数为 999.99

3、日期型
(1)DATE：日期时间型，精确到秒
(2)TIMESTAMP：精确到秒的小数点后9位

4、二进制类型(大数据类型)
(1)CLOB：存储字符，最大可以存4个G
(2)BLOB：存储图像、声音、视频等二进制数据，最多可以存4个G

3.2 表修改

3.2.1 插入表数据

语法：insert into 表名(column1,colunm2...) values((column1,colunm2...) where 条件表达式

3.2.2 增加字段

语法：

ALTER TABLE 表名称 ADD(列名 | 类型 [DEFAULT 默认值], 列明 | 类型 [DEFAULT 默认值]...) 

示例：

ALTER TABLE T_OWNERS ADD
(
      PEMARK VARCHAR2(20),
      OUTDATE DATE
)

3.2.3 修改字段

语法：
ALTER TABLE 表名称 MODIFY(列名 | 类型 [DEFAULT 默认值], 列明 | 类型 [DEFAULT 默认值]…)

示例：

ALTER TABLE T_OWNERS MODIFY
(
      PEMARK CHAR(20),
      OUTDATE TIMESTAMP
)

3.2.4 修改字段名

语法：

ALTER TABLE 表名称 RENAME COLUMN 原列表 TO 新列名

示例：
ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXITDATE

3.2.5 删除字段名

语法：

ALTER TABLE 表名称 DROP COLUMN 列明1,列明2...;

– 示例
ALTER TABLE T_OWNERS DROP COLUMN REMARK,OUTDATE;

3.3 表删除

删除表和结构

drop table T_OWNERS

删除表有日志，可以恢复

delete table T_OWNERS

清空表，无日志，不可以恢复

truncate table

4、数据库导入/导出

4.1整库导入/导出

4.1.1 整库导出

exp 用户名/用户秘密 full = y file=文件名称 --文件名称，建议使用.dmp后缀

4.1.2 整库导入

imp 用户名/用户秘密 full = y file=文件名称 --文件名称，建议使用.imp后缀

4.2 按用户导入/导出

4.2.1 指定用户导出

exp 用户名/用户秘密 owner = 用户名称 file=文件名称 --文件名称，建议使用.dmp后缀

4.2.2 指定用户导入

imp 用户名/用户秘密 file=文件名称 fromuser=用户名称 --文件名称，建议使用.imp后缀

4.3 按表导入/导出

4.3.1 按表导入

exp 用户名/用户秘密 file=文件名称 tables=表名称（多个用逗号隔开） --文件名称，建议使用.dmp后缀

4.3.2 按表导出

imp 用户名/用户秘密 file=文件名称 tables=表名称（多个用逗号隔开）