DM 模式 SQL

模式简介

模式即 SCHEMA，是数据库的对象，可以将其理解为一个独立空间，不同的模式下可以拥有相同的名称的表而不会发生冲突，只要有权限，不同模式下的表可以相互查询调用，在模式中可以拥有以下对象：

表、视图、索引、触发器、存储过程、函数、序列、全文索引、包、同义词、类、外部链接。

模式与用户的关系

• 在达梦数据库中，系统为每个用户都自动创建了一个与用户名同名的模式作为默认模式，用户还可以用模式定义语句创建其它模式。
• 一个用户可创建多个模式，但一个模式只归属一个用户，一个模式中的对象（表、视图等）可以被该用户使用，也可以授权给其它用户使用。

SQL

查询模式

# 查询所有模式
SQL> select name from sysobjects where type$='SCH';

LINEID     NAME
---------- ----------
1          CTISYS
2          SYS
3          SYSAUDITOR
4          SYSDBA
5          SYSSSO
6          TESTSA1


# 查看目前所有用户与模式的对应关系。
SQL> select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;

行号       USERNAME   SCHENMA
---------- ---------- ----------
1          SYSSSO     SYSSSO
2          SYSDBA     SYSDBA
3          SYSAUDITOR SYSAUDITOR
4          SYS        SYS
5          SYS        CTISYS

创建模式

在创建模式时要指定归属的用户名，可以在创建模式的同时创建中的对象，但通常是分开进行的。

# 创建模式的SQL命令格式
<模式定义子句1> | <模式定义子句2>

# 子句说明
create schema <模式名> [authorization <用户名>][<ddl_grant 子句>{<ddl_grant 子句>}];   # 模式定义子句1
create schema authorization <用户名> [<ddl_grant 子句>{<ddl_grant 子句>}];             # 模式定义子句2
#   <用户名>指明给哪个用户创建模式,如果默认用户名,则默认给当前用户创建模式.
#   语法格式中其它部分都是可选项,如<基表定义>,<基表修改>,<视图定义>等子句.

#   <ddl_grant 子句>
<基表定义> | <域定义> | <索引定义> | <视图定义> | <序列定义> | <存储过程定义> | <触发器定义> | <特权定义> | <全文索引定义> | <同义词定义> | <包定义> | <类定义> | <类体定义> | <外部链接定义> | <物化视图定义> | <物化视图日志定义> | <注释定义>

实例

# 创建用户testuser
SQL> create user testuser identified by Network2020;


# 为testuser用户增加一个模式,模式名为testsa1,并在testsa1模式中定义一张表table1
SQL> create schema testsa1 authorization testuser;
2   create table testsa1.table1 (id int, name varchar(20), password char(128));
3   /


# 为testuser用户增加一个模式,模式名为testsa2
SQL> create schema testsa2 authorization testuser;
2   /

创建模式注意事项

1. 模式名不可与其所在数据库中其它的模式名重复；在创建新模式时，若存在同名的模式，则该命令不能被执行.
2. 使用该语句的用户必须具有 DBA 或 CREATE SCHEMA 权限.
3. 模式一旦定义，该用户所建表，视图均属于该模式，其它用户访问该用户所建立的表或视图等均需要在表明或视图名前冠以模式名；而建表者访问自己当前模式所建立的表或视图时可忽略模式名；若没有指定当前模式，则系统自动以当前用户名作为模式名.
4. 模式定义语句不允许与其它SQL与语句一起执行.
5. 在DM管理工具中使用该SQL语句必须以/结束.

切换模式

当一个用户有多个模式时，可指定一个模式为当前默认模式。

# 设置当前模式的SQL命令格式
set schema <模式名>;

# 示例
[root@lp]# ./disql testuser/xxxxx@localhost:5236    # 以testuser用户登录数据库
SQL> set schema testsa2;                            # 将当前模式设置为testsa2

删除模式

# 删除模式的SQL命令格式
drop schema <模式名> [RESTRICT | CASCADE];
# 若使用RESTRICT选项,则只有当模式为空时删除才能成功,否则当模式中存在数据库对象时,删除会失败;默认选项为RESTRICT.
# 若使用CASCADE选项,则整个模式,模式中的对象,以及与该模式相关的依赖关系都被删除.

示例

# 以SYSDBA用户登录数据库
[root@k1 bin]# ./disql SYSDBA/SYSDBA@localhost:5236

# 删除testsa2模式
SQL> drop schema testsa2;
executed successfully
used time: 27.623(ms). Execute id is 59300.

# 删除testsa1模式
SQL> drop schema testsa1;
drop schema testsa1;
[-5001]:Error in line: 1
Not empty schema [TESTSA1].
used time: 0.534(ms). Execute id is 0.
# 删除失败,因为testsa1模式不为空,存在数据表对象table1,无法删除非空的模式.

# 使用cascade选项删除testsa1模式
SQL> drop schema testsa1 cascade;
executed successfully
used time: 14.793(ms). Execute id is 59301.

删除模式注意事项

1. 被删除模式必须是当前数据库中已存在的模式.
2. 执行删除模式的用户必须具有DBA权限，或者是该模式的所有者.
3. 在删除用户时，会删除用户底下所有的模式，所以在达梦数据库中，如果一个用户有多个模式，在删除一个模式时，千万注意不能使用drop user的方式，要用drop schema删除。

总结

• 不同的 schema 之间它们没有直接的关系，不同的 schema 之间的表可以同名, 也可以互相引用（但必须有权限），在没有操作别的schema的权限时，每个用户只能操作它自己的schema下的所有的表；
• 用户访问非默认模式下的对象时，需要在对象名前面添加模式名（模式名.对象名），访问默认模式下的对象可以不添加模式名；
• 用户的默认模式无法单独删除，需要直接删除用户，而非默认模式可以单独删除；
• 在删除用户时，会删除用户底下所有的模式，所以不要轻易做删除用户的操作。