达梦对象和模式管理

模式对象管理

1. 模式

1. 模式概念

一个模式仅能归属于一个用户，而一个用户可以包含多个模式。模式是一组对象的集合，使用模式可以使不同业务隔离。一个模式可以被多个用户访问。一个用户可以访问他所连接的数据库中有权限访问的任意模式中的对象。

 

1. 自动创建用户模式

系统为每一个用户自动建立了一个与用户名同名的模式作为其默认模式，用户还可以用模式定义语句建立其它模式。

执行：select * from sysobjects where type$='SCH';可以查询当前数据库下所有模式，图中标红的为创建用户ryh和ryh01时系统自动创建的模式

 

 

1. 手动创建模式

--在ryh用户下创建一个模式ryh2。如果不指定用户则默认放在当前用户下。例如使用sysdba用户执行create schema ryh2则模式ryh2会放在sysdba用户下。

create schema ryh2 authorization ryh;

 

执行：select a.name,a.type$,b.name,b.type$ from sysobjects a,sysobjects b where b.type$ = 'SCH' and a.id = b.pid and a.name = 'RYH';语句可以查询到ryh用户下所有模式

 

1. 设置模式

模式设置切换只能设置当前属于自己的模式，不能设置其他用户下的模式。设置后访问该模式下对象可以不用带模式名。

 

如图，当前用连接为sysdba用户。只能切换到属于sysdba用户下其它模式。ryh模式属于ryh用户因此不能切换。

 

使用ryh用户登录后可以切换到属于ryh用户下的莫斯ryh。

 

1. 删除模式

删除模式只有DBA权限用户或者属于自己的模式才可以删除。

--删除模式ryh1。选项restrict为判断当前模式不为空时会无法删除。Cascade为级联删除当前模式以及属于该模式下的所有对象。

drop schema ryh1 restrict/cascade;

--不写时默认为restrict

drop schema ryh1;

 

如图所示ryh1模式下对象不为空时无法删除。

 

使用级联删除方式可以直接删除。

 

1. 表

1. 创建表

创建表时，如果不适用模式名则默认在当前模式下创建。如果要在其它模式下创建则需要带模式名并且有对应的权限。创建表时可以指定表存储的表空间位置，如果不指定则默认存在对应用户所属的默认表空间。

--在模式ryh下创建test_02表并制定存储到main表空间。如果这里不指定就存储到该模式对应用户的默认表空间DB_DATA

create table ryh.test_02(id int,name varchar(20)) storage(on main);

 

执行：select owner,table_name,tablespace_name from all_tables where owner = 'RYH';可以查到ryh用户当前下的表以及存储对应表空间关系。

 

1. 指定创建表存储空间上线

使用diskspace limit语句可以指定表的存储空间上线。当表的索引已经数据占用空间超过指定占用空间大小时则不能再插入数据。

--指定存储上限

alter table ryh.test_01 modify diskspace limit 10;

 

1. 增加表字段

--增加表字段age

alter table ryh.test_01 add age int;

 

执行系统包：select dbms_metadata.get_ddl('TABLE','TEST_01','RYH');可以查询到新的增加的字段age

 

1. 设置字段默认值

--设置字段age如果填充值的时候默认填充10

alter table ryh.test_01 modify age default 10;

 

如图所示插入数据时不填充age值。查询的时候发现age默认填充为10。

 

1. 增加字段并设置默认值

--增加字段add_time并设置默认值为当前时间

alter table ryh.test_01 add add_time datetime default sysdate;

 

从图中可以看出增加字段并设置默认值时会在表内所有数据行都增加对应的默认值。如果此表由一千万行数据则增加字段时同时会修改此表的一千万行数据。会很花费时间，因此大表需要慎用。

 

1. 删除默认值

--删除字段默认值后就插入数据就不会再自动填充默认数据

alter table ryh.test_01 alter add_time drop default;

 

如图所示删除add_time默认值以执行：insert into ryh.test_01(id,name) values(4,'ryh4');插入数据不指定add_time值就没有当前时间。而age字段没有删除则依然默认为10

 

1. 删除字段

--删除字段add_time

alter table ryh.test_01 drop column add_time;

 

 

1. 修改表字段类型

--把age字段类型由int改为varchar

alter table ryh.test_01 modify age varchar(10);

 

 

 

上面三个步骤图可以看出修改字段类型时要注意数据是否可以转换，否则无法成功。一般来说数值转字符串都可以。从字符串转数值就需要看字符串内容是否符合数值要求。

 

1. 添加表注释

--添加表注释

comment on table ryh.test_01 is 'test_01_table';

 

执行：select * from all_tab_comments where table_name = 'TEST_01';可以查到刚才添加的表注释“test_01_table”

 

1. 添加字段注释

--给字段age添加注释

comment on column ryh.test_01.age is '10-40';

 

执行：select * from all_tab_comments where table_name = 'TEST_01';可以查到表TEST_01所有字段的注释内容。

 

1. 添加非空约束

--给已创建的表test_01中name字段添加非空约束

alter table ryh.test_01 modify name not null;

 

可以看到插入数据name为空的时候报违反非空约束

 

1. 添加主键约束

--给已添加的表增加主键约束

alter table ryh.test_01 add constraint p_con primary key(id);

 

执行：select * from all_constraints where owner = 'RYH';可以查到刚创建的约束信息

 

1. 添加外检约束

--给表test_02字段test_01_id添加外检约束关联到test_01表中的id字段

alter table ryh.test_02 add constraint f_con foreign key(test_01_id) references ryh.test_01(id);

 

执行：select * from all_constraints where owner = 'RYH';可以查到刚创建的约束信息。测试一下插入数据如下图所示：

 

提示违反引用约束。因为test_01表中没有id为10的数据。下面我们添加一下：

 

 

上面两幅图操作可以看出test_01表中添加id为10的数据再次插入数据到test_02表就正常了。

 

1. 唯一键约束

--给表test_01字段name添加唯一约束

alter table ryh.test_01 add constraint u_con unique(name);

 

插入已有的数据会报错。表示约束生效。如果表中已经有重复数据此时添加唯一约束也是会报错的。因此添加时需要注意。一般都是在创建表的时候添加。

 

如图所示。name字段有重复数据时是无法添加唯一约束的

 

1. 检验约束

--给表test_01字段age添加检验约束。要求年龄大于5才能插入

alter table ryh.test_01 add constraint check(age>5);

 

如图所示age字段为2时报违反检验约束。

 

1. 简单视图

视图是虚拟的表，不存放数据，实际数据仍然存放在表中，数据库中只存放视图的定义。查询视图时实际是查询视图定义中所查询的表。也可以把视图看成定义的简化查询。

1. 创建或修改视图

--创建视图v1，查询工资在1000到15000之间的职位并按照工资排序

create or replace view ryh.v1 as 

select a.employee_name,a.salary,b.job_title,b.min_salary,b.max_salary from 

(select employee_name,salary,job_id from dmhr.employee where salary >= 10000 and salary <= 15000) a,

dmhr.job b

where a.job_id = b.job_id

order by salary asc;

 

可以看到直接查询视图就可以显示对应定义的查询信息。

 

1. 删除视图

--删除视图

drop view ryh.v1;

 

1. 视图相关数据字典

select * from all_views where owner = 'RYH';

 

可以查到定义的视图已经相关视图的定义查询语句