Oracle数据库对象

1 数据库对象
2 表
    2.1 表的数据类型
    2.2 表的约束
3 表空间
    3.1 表空间是什么
    3.2 表空间的语法
    3.3 示例代码
4 用户
5 用户授权（DCL）
    5.1 系统权限
    5.2 对象权限
6 视图
    6.1 视图是什么
    6.2 视图的语法
    6.3 代码示例
    6.4 读写视图
7 序列
    7.1 索引是什么
    7.2 索引的语法
    7.3 代码示例
8 索引
    8.1 索引是什么
    8.2 索引的语法
    8.3 示例代码
    8.4 创建索引的建议
9 同义词
    9.1 同义词是什么
    9.2 同义词的语法

1 数据库对象

Oracle中所有具有特殊功能的组件，统称为数据库对象。

所有的数据库对象都是使用DDL（数据定义语言）来创建（create）、修改（alter）和移除（drop）的。

---

数据库对象分为系统级别的和用户级别的。

• 系统级别：属于Oracle数据库的数据库对象。如：表空间、用户。
• 用户级别：属于某个用户的数据库对象。如：表、视图、序列、索引、同义词。

2 表

表就是一个用于存储数据的数据库对象。

-- 创建表
-- 需求：创建一个tb_user表,用户编号，用户名，年龄
create table tb_user(
    -- 创建一个整型，长度为10位的用户编号
    user_id number(10) not null primary key,
    -- 可变长度的字符串
    user_name varchar2(50) not null,
    -- 年龄
    age number(3)
);

-- 查看当前用户的表
-- tabs是Oracle用于存储当前用户表信息的一个内置表
select * from tabs;

-- 查看表tb_user的结构
desc tb_user; 

-- 修改表
-- 增加字段
-- 语法：alter table <表名> add (<字段名> <数据类型>[(大小)][not null],<字段名> <数据类型>[(大小)][not null]...);
-- 需求：增加一个性别的
alter table tb_user add (user_sex char(3) ); 

-- 更新字段属性
-- 语法：alter table <表名> modify (<字段名> <数据类型>[(大小)][not null],<字段名> <数据类型>[(大小)][not null]...);
-- 需求：更新性别字段的属性为不可以为null
-- 注意：修改是通过字段名来标识修改
alter table tb_user modify (user_sex char(3) not null);

-- 更新字段名
-- 语法：alter table <表名> rename column <旧字段名> to <新字段名>
-- 需求：修改年龄字段age为user_age
alter table tb_user rename column age to user_age; 

-- 删除字段
-- 语法：alter table <表名> drop (<字段名1>,<字段名2> ...);
alter table tb_user drop (user_age,user_sex); 

-- 删除表
drop table tb_user;

2.1 表的数据类型

类型	说明
varchar2	可变长度，字符串类型。使用varchar时Oracle会自动转成varchar2。英文字符占一个字节；GBK编码汉字占2个；UTF-8编码占3个字节。
nvarchar2	可变长度，字符串类型。使用varchar时Oracle也会自动转成varchar2。 如果是UTF-8，英文字符和汉字都是占个3字节就；GBK编码英文和汉字都是2个字符。
char	固定长度字符串类型。 英文字符占一个字节；GBK编码汉字占2个；UTF-8编码占3个字节。
nchar	固定长度字符串类型。 如果是UTF-8，英文字符和汉字都是占个3字节；GBK编码英文和汉字都是2个字符。
boolean	布尔类型
number(p,s)	p表示长度，s表示精度，如果s不写或为0都表示整数，所以s有值时表示浮点型。如： number(10,0)：表示一个可以存储10位长度的整数； number(10,2)：表示一个整数部分是8位，小数部分是2位的浮点型。p最大值38位。
clob	大文本类型，类似于mysql的text。最大存4G文本。
blob	存储二进制数据，最大也是4G。

2.2 表的约束

表的约束有：主键约束（primary key）、非空约束（not null）、检查约束、唯一约束、外键约束。

-- 创建表
create table tb_user(
   user_id number(10) not null primary key,
   user_name varchar2(50) not null,
   user_sex char(3)
); 

-- 检查约束:所谓的检查约束就是，限制字段输入的值。
-- 需求：性别，只能输入男或者女
-- 给表加约束，其实就是修改表
-- alter table <表名> add constraint <约束名> check (<字段名 in ('值1','值2'...)>);
alter table tb_user add constraint ch_user_sex check (user_sex in('男','女'));
-- 测试
insert into tb_user values(1,'张三','男');
insert into tb_user values(2,'李四','妖'); 

-- 唯一约束：用于约束字段的值不能出现重复数据
-- 需求：user_name的名字不可以重复
-- alter table <表名> add constraint <约束名> unique (<字段名1>...)
alter table tb_user add constraint uni_user_name unique (user_name);
--测试
-- 数据库里面已经有张三的名字的记录，如果再插入，就失败。
insert into tb_user values(2,'张三','男'); 

-- 外键约束
-- 外键等同外键约束
-- 外键的作用是为了让我们快速了解表与表之间关系
-- 外键约束的作用是为了保证数据的完整性
-- 所谓的数据的完整性，就是如果一条数据已经有其他的数据引用了它，就不能删除。必须要将它的子记录删除，才可以删除。
-- 需求：创建一个用户类型表，一个用户表，一个用户类型有多个用户。
-- 用户类型
create table tb_user_type(
  type_id number(10) not null primary key,
  type_name varchar(50) not null
);
-- 创建表
drop table tb_user;
create table tb_user(
   -- 编号
   user_id number(10) not null primary key,
   user_name varchar2(50) not null,
   user_sex char(3),
   type_id number(10) not null
);
-- 将用户表的type_id和用户类型表创建外键约束
alter table tb_user add constraint fk_type_id foreign key(type_id) references tb_user_type(type_id);

3 表空间

3.1 表空间是什么

表空间是一个存储用户数据的系统级别的数据库对象。它是Oracle最大的存储单元：每创建一个表空间必须创建一个数据文件。

---

推论：创建用户一定要有表空间存储该用户的数据。

如果创建用户不指定表空间，系统会自动将用的数据放在USERS表空间里面。

3.2 表空间的语法

-- 创建表空间
create tablespace <表空间名>
    -- 属性
    -- 用于指定创建的数据文件，注意如果不写路径放在$(ORACLE_HOME)/database
    -- 如：F:\app\ranger\product\11.2.0\dbhome_1\database
    datafile '路径/文件名.DBF'
    -- 指定文件的初始大小
    size <大小>
    -- 数据超出了文件的大小，是否自动扩展
    autoextends on|off  --on表示支持，off表示不支持，默认是off
    -- 如果支持文件自动扩展，每次扩展的大小，如果不设置默认扩展的就是初始大小
    next <大小>;

3.3 示例代码

-- 创建一个表空间
-- 表空间是一个系统级别的数据库对象
-- 所有必须需要使用管理员创建
create tablespace xust_ts
  datafile 'xust_ts.dbf'
  size 100m
  autoextend on
  next 100m;

-- 查看表空间
-- 内置表
select * from dba_data_files;  

-- 删除
-- 1.逻辑删除：仅仅删除数据文件和数据库的关联。文件不会删除。
 drop tablespace xust_ts;
-- 2.物理删除：同时把数据文件也删除
drop tablespace xust_ts including contents and datafiles; 

-- 表空间的修改（了解）
-- 表空间是支持一个表空间多个数据文件的。
-- 这样做的好处，就是可以实现一个表空间的数据放在多个硬盘上
-- 给表空间增加一个文件
-- alter tablespace <表空间名> add  datafile '路径/文件名.DBF' size <大小>;
alter tablespace xust_ts add datafile 'xust_ts_02.dbf' size 10M;

-- 删除一个文件
-- 注意，有数据的表空间禁止删除，否则会丢失数据
-- alter tablespace <表空间名> drop  datafile '路径/文件名.DBF';
alter tablespace xust_ts drop datafile 'xust_ts_02.dbf';

4 用户

-- 创建用户的语法
create user <用户名>
    -- 属性
    -- 密码
    identified by <密码>
    -- 指定表空间
    default tablespace <表空间名>
    -- 注意：用户也是一个系统级别的数据库对象，所以必须使用管理员创建

create user xust
    -- 密码
    identified by xust
    -- 指定表空间
    default tablespace xust_ts;

-- 查看用户详细信息
select * from dba_users;

-- 查看用户简单信息
select * from all_users;

-- 修改用户
-- 修改状态
-- 锁定用户
alter user xust account lock;

-- 解锁用户
alter user xust account unlock; 

-- 修改密码
-- 语法：alter user <用户名>  identified by <新密码>;
alter user xust identified by orcl; 

-- 删除用户
-- 加上cascade，删除用户也把用户的数据删除
-- drop user <用户名> cascade
drop user xust cascade;

5 用户授权（DCL）

Oracle是一个非常安全的数据库。用户如果没有授权，是不能做任何操作的，包括登录。

用户的权限的类型有两种：

1. 系统权限：就是用户访问自身模式（用户空间）的权限
2. 对象权限：就是用户访问另一个用户的模式（用户空间）的数据库对象（表和视图）权限

5.1 系统权限

/*
语法：
grant <系统权限>|<角色> to <用户名>
*/

-- 用户登录的权限
-- 用户的每个一个系统权限都要先授权，后使用
grant create session to xust;

-- 查看系统权限
select * from dba_sys_privs;

-- 有很多的系统权限，如何记住？
-- 规律：
    -- create 数据库对象类型
    -- 如：创建表 create table
    -- alter 数据库对象类型
    -- drop 数据库对象类型
    -- 注意：用户级别的数据库对象，create权限包括alter,drop
grant create table to xust; 

-- 问题：如果系统权限一个一个授予，很麻烦。
-- Oracle通过角色来解决这个问题。一个角色可以有多个系统权限

-- 查看系统的角色
select * from dba_roles;

-- 常用的角色
    -- DBA：授予DBA，说明这个是管理员
    -- RESOURCE：授予基础操作的权限
    -- CONNECT：授予登录的权限 

-- 查看角色的权限
select * from dba_sys_privs where grantee='RESOURCE';
select * from dba_sys_privs where grantee='CONNECT'; 

-- 使用角色授权
grant connect , resource to xust; 

-- 查询当前用户的系统权限
select * from user_sys_privs; 

-- 查看角色
select * from user_role_privs; 

-- 查看当前用户角色权限
select * from role_sys_privs; 

-- 系统撤权
-- 语法:revoke <系统权限>|<角色> from <用户名>
revoke resource from xust;

5.2 对象权限

/**
对象权限授权语法：
grant insert,delete,update,select on 被访问的用户名.表名|视图名 to 被授权的用户名
*/

-- 需求：授予xust用户，访问scott用户的emp表的权限
grant select on scott.emp to xust; 

select * from scott.emp;

-- 需求：将scott用户的emp,dept,salgrade表复制到xust里面
grant select on scott.emp to xust;
grant select on scott.dept to xust;
grant select on scott.salgrade to xust;
-- 复制表
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
create table salgrade as select * from scott.salgrade; 


-- 查看当前用户的对象权限
select * from user_tab_privs; 

-- 撤销对象权限语法：revoke insert,delete,update,select on 被访问的用户名.表名|视图名 from 被授权的用户名
-- 撤销xust对scott用户的对象权限
revoke select on scott.emp from xust;
revoke select on scott.dept from xust;
revoke select on scott.salgrade from xust;

6 视图

6.1 视图是什么

视图是一个由一个或者多个表查询的结果组成的虚拟表。组合这个视图的表称为基表。其实就是将查询的语句使用一个数据库对象保存起来。用于实现复杂查询的SQL语句重复使用。

6.2 视图的语法

-- 创建视图
create [or replace] view <视图名> as
  select 语句; 

-- or replace，如果有这关键字，表示如果视图已经存在，就覆盖

6.3 代码示例

-- 需求：创建一个视图，查询员工的信息和部门信息
-- 视图的结果不能有重名的列
-- 首先授权
grant create view to xust;
create or replace view vw_emp_all as 
    select emp.*,dept.dname,dept.loc from emp,dept where emp.deptno = dept.deptno;    

-- 查看视图
select * from user_views; 

-- 使用视图
select * from vw_emp_all;

-- 删除视图
drop view vw_emp_all;

6.4 读写视图（了解）

Oracle里面，单表视图是可以读写的。多表视图是不可以写的，只能读。

读写视图，操作最后影响的是基表的数据。

-- 读写视图（单表视图）, 很少用。
-- 用于限制另一个用户访问的字段
create or replace view vw_emp as
    select * from emp;

-- 测试
insert into vw_emp(empno,ename) values(2001,'test1');

7 序列

7.1 索引是什么

序列是一个数据库对象级别的计数器。

Oracle不支持像MySQL那样，可以使用一个关键字（auto_increment）支持ID的自增长。 Oracle是通过序列来实现ID的自增长的。

序列的主要作用就是用于实现Oracle的ID自增长。

7.2 索引的语法

create sequence <序列名>
   -- 属性
   -- 步长
   increment by <步长>
   -- 开始位置
   start with <开始位置>
-------------------------------------------------------------------------
   -- 最大值
   maxvalue <最大值>
   -- 不限制最大值
   nomaxvalue
   -- 最小值
   minvalue
   -- 计数大于最大值的时候，是否重新开始
   nocycle |cycle  nocyle表示不重新开始，cycle超过最大值，重新开始
   -- 缓存大小，默认是20
   cache  <大小>;

7.3 代码示例

-- 需求：创建一个用于emp表的序列
create sequence seq_emp
   increment by 1 --步长
   start with 1   --开始位置
   nomaxvalue     --没有最大值
   nocycle        --如果计数超过了最大值，不重新开始，直接报错
   cache 40;      --设置缓冲的计数的个数   

-- 查看序列
select * from user_sequences;

-- 使用序列
-- 1.获得当前序列值（计数）
-- 格式：序列名.currval
select seq_emp.currval from dual;
-- 2.获得下一个序列值
select seq_emp.nextval from dual;

-- 使用序列插入数据
insert into emp(empno,ename) values(seq_emp.nextval,'s1');

-- 删除序列
drop sequence seq_emp; 

-- (了解)当步长为负数时。是倒序序列
create sequence seq_emp_1
   increment by -1   --步长
   start with 5000   --开始位置
   maxvalue 5000     --没有最大值
   minvalue 1        --如果到了1，序列就无法往下计数。
   nocycle           --如果计数超过了最大值，不重新开始，直接报错
   cache 40;         --设置缓冲的计数的个数

select seq_emp_1.nextval from dual;

8 索引

8.1 索引是什么

索引是一个提高查询效率的数据库对象。

8.2 索引的语法

-- 创建语法
create [unique] index <索引名> on <表名>(<字段名1> asc |desc ,<字段名1>); 

-- unique:表示唯一索引，创建唯一索引后，会自动加上唯一约束。

8.3 示例代码

select count(*) from tb_user;
-- 没有索引
select * from tb_user where user_name ='name19990000';
-- 有索引
select * from tb_user_index where user_name ='name19990000';

-- 给emp表的ename创建一个唯一索引
create unique index ind_ename on emp(ename asc);
-- 给emp表的工资创建一个索引，降序
create index ind_sal on emp(sal desc);

-- 查看索引
select * from user_indexes;

-- 删除索引
drop index ind_sal;

8.4 创建索引的建议

1. 在经常查询的字段上创建索引
2. 表太小不要创建索引。10W
3. 字段的类型是大文本类型时，不要创建索引

9 同义词（了解）

9.1 同义词是什么

同义词就是一个数据库对象级别的别名。

作用：缩短表和视图名。

9.2 同义词的语法

-- 同义词
-- create [public] synonym <同义词> for < 表名|视图名>;
-- 需求：创建一个emp表的同义词e
-- 授权
grant create synonym to xust;
create synonym e for emp;

--查询别名
select * from e;
--插入数据
insert into e(empno ,ename ) values(999,'sy1'); 

-- 同义词属于用户
-- 公用同义词属于Oracle系统

注意：所有内置表都是一个公有同义词。记不清可以在里面找。