达梦数据库之模式对象管理-语法汇总！！

语法特征之模式对象管理

一、状态与模式

1.1状态

达梦的数据库状态分为三种：配置（MOUNT）、打开（OPEN）和挂起（SUSPEND）。

数据库状态	是否允许访问数据库对象	数据库模式修改、控制文件维护、归档配置	磁盘写入
配置状态	否	是	否
打开状态	是	否	是
挂起状态	是	否	否

数据库状态转换:

MOUNT和SUSPEND不能直接转换。不能从配置直接转到挂起，也不能直接从挂起转到配置。
数据库状态查看：

select instance_name,"V$INSTANCE"."STATUS$"  from v$instance;

1.2模式

达梦数据库的三种模式：普通模式（NORMAL）、主库模式（PRIMARY）、备库模式（STANDBY）。

数据库模式查看：

select instance_name,"V$INSTANCE"."MODE$" from v$instance;

其区别是主库模式会强制生成redo日志，发送到备库。备库模式接收主库发过来的redo日志重做数据。

对于新初始化的库，首次启动不允许使用 mount 方式，需要先正常启动并正常退出，然后才允许 mount 方式启动。

1.3对应关系

数据库模式	默认启动状态
普通模式（NORMAL）	打开状态（OPEN）
主库模式（PRIMARY）	配置状态（MOUNT）
备库模式（STANDBY）	配置状态（MOUNT）

二、SQL语句之表

2.1表空间

2.1.1创建表空间

指定表空间名及其所拥有的数据文件列表。

//创建表空间
create tablespace BOOKSHOP1 datafile '/home/dmdba/dm8/data/TT.DBF' size 128;
//删除表空间
drop tablespace BOOKSHOP1;
//修改表空间名
alter tablespace BOOKSHOP1 rename to BOOK;
//修改表空间状态为脱机状态（断开数据库与所有人的连接）
alter tablespace BOOKSHOP1 offline;
//修改表空间状态为联机
alter tablespace BOOKSHOP1 online;
//把表空间绑定到缓冲区（KEEP，NORMAL）
alter tablespace BOOKSHOP1 cache="KEEP";

2.1.2查询表空间表

//查询表空间下有哪些表
select table_name,tablespace_name from dba_tables;
//查看BOOKSHOP1表空间下有哪些表
select table_name,tablespace_name from dba_tables where tablespace_name='BOOKSHOP1';

2.2创建模式及表

2.2.1模式

2.2.1.1创建模式

创建模式T_test，模式拥有者为SYSDBA。
注意：在模式下创建表需要有create table权限，在其他用户的模式中创建新表需要有create any table数据库权限。

CREATE SCHEMA T_test AUTHORIZATION SYSDBA;

2.2.1.2设置当前模式语句

当前用户切换到T_test模式下。

set schema T_test;

2.2.1.3删除模式

//当前模式为空
drop schema T_test restrict;
//删除整个模式中的对象、依赖关系
drop schema T_test cascade;

2.2.1.4查看模式

查看当前有哪些模式

select distinct object_name table_schema from all_objects where object_type='SCH';

2.2.2创建普通表

注意：被引用表要在引用表前定义
SQL语句创建：

CREATE TABLE T_test.info
(ID INT IDENTITY(1,1) PRIMARY KEY,
bookId int Not Null references T_test.bookInfo(bookId),
test VARCHAR(50) NOT NULL) STORAGE (ON BOOKSHOP1);
指定模式名（模式名.表名，模式名_表名，）以及storage指定存储信息（on 表空间名）。
STORAGE
( INITIAL 5,//初始簇数目，建表时分配的簇数目，缺省为1
MINEXTENTS 5,//最小保留簇数目，当删除表中的记录后，如果表使用的簇数目小于这个值，就不再释放表空间，缺省为1
NEXT 2,//表空间不够时，分配簇个数，缺省为1
ON 表空间名,
FILLFACTOR 85//填充比例，插入数据时，数据页的充满程度
);

DM管理工具创建：
在指定的模式名下，选择“表”，右键选择“新建表”，可以设计表名、字段名等。

选择“存储”，可选择该表所对应的表空间。如果不设置默认为Main。

2.2.3创建行表

行表即普通表。

2.2.4创建列表

列表（HUGE表）：以列为单位进行存储，每一个列的所有行数据都存储在一起。

一个指定的页面中存储的都是某一个列的连续数据。（物理结构上存储）（列表使用HFS存储机制）

相同的表数据在行存储表与列存储表中的不同存储方式（图片来自网络）

2.2.4.1创建HTS（HUGE TABLESPACE）表空间

系统中有一个默认的HTS表空间，名字为HMAIN。可以自己创建HTS表空间。

create HUGE TABLESPACE HTS_NAME PATH '/home/dmdba/dm8/data/HHTS';

2.2.4.2创建HUGE表

HUGE表可以分为两种类型，一种是事务型HUGE表，一种是非事务型HUGE表。

其主要区别为是否能实现回滚。事务型执行一系列命令，要么全部执行，要么全部不执行，即出现错误后可以返回执行前的状态。
缺省为without delta;

//创建非事务型HUGE表
create HUGE table T1(A int,B int) storage(WITHOUT DELTA);
//创建事务型HUGE表
create HUGE table T2(A int,B int) storage(WITH DELTA);

2.2.4.3查看HUGE表定义

语法：

call SP_TABLEDEF('模式名','表名'）；
call SP_TABLEDEF('SYSDBA','T1');

2.2.5创建分区表

分区的目的：提高大数据量读写操作和查询的速率
具体操作：分区表作为分区主表，不存储数据，每一个分区以一个子表实体存在（主表名_分区名），子表删除后子表上的数据也会删除。

分区方式	分区依据
范围（range）水平分区	某些列上的值的范围
哈希（hash）水平分区	通过指定分区编号来均匀分配数据（IO设备散列分区）
列表（list）水平分区	表中的某个列的离散值集status 列的值在(‘A’，‘H’，‘O’)放在一个分区，在(‘B’，‘I’，‘P’)放在另一个分区
多级分区表	上述三种任意组合，多级分区

2.2.5.1创建范围分区表

语法：

create table 表名（
字段名 约束,
字段名 约束,
......
）
partition by range(字段名)（
partition p1 values less than(),
partition p2 values less than(),
partition p3 values equ or less than()
）;

2.2.5.2创建列表分区表

某些列上的数据无法通过范围划分，该列上的数据是相对固定的一些值。

CREATE TABLE sales(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY LIST(city)(
PARTITION p1 VALUES ('北京', '天津'),
PARTITION p2 VALUES ('上海', '南京', '杭州'),
PARTITION p3 VALUES ('武汉', '长沙'),
PARTITION p4 VALUES ('广州', '深圳')
);

2.2.5.3创建哈希分区表

根据一个哈希函数对数据计算，将数据均匀地分配到各个分区中。用户无法预测数据将被放到哪个分区中。
可以不指定分区表名，只设置分区个数，默认分区表名dmhashpart+分区号（从0开始）。

CREATE TABLE sales02(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city
CHAR(10)
)
PARTITION BY HASH(city)
PARTITIONS 4 STORE IN (ts1, ts2, ts3, ts4);

SELECT * FROM sales02 PARTITION (dmhashpart0);

2.2.5.4创建多级分区表

LIST-RANGE分区

DROP TABLE SALES;
CREATE TABLE SALES(
SALES_ID INT,
SALEMAN CHAR(20),
SALEDATE DATETIME,
CITY CHAR(10)
)
PARTITION BY LIST(CITY)//第一级分区LIST
SUBPARTITION BY RANGE(SALEDATE) SUBPARTITION TEMPLATE(//第二级
SUBPARTITION P11 VALUES LESS THAN ('2012-04-01'),
SUBPARTITION P12 VALUES LESS THAN ('2012-07-01'),
SUBPARTITION P13 VALUES LESS THAN ('2012-10-01'),
SUBPARTITION P14 VALUES EQU OR LESS THAN (MAXVALUE))
(
PARTITION P1 VALUES ('北京', '天津')
(
SUBPARTITION P11_1 VALUES LESS THAN ('2012-10-01'),
SUBPARTITION P11_2 VALUES EQU OR LESS THAN (MAXVALUE)
),
PARTITION P2 VALUES ('上海', '南京', '杭州'),
PARTITION P3 VALUES (DEFAULT)
);

p1下有两分区p11_1,p11_2，p2,p3下面分别有p11,p12,p13,p14四个分区。

2.2.6创建临时表

第一次执行DML语句，分配空间。会话或事务结束以后，临时表被自动清除。

分类	操作
事务级on commit delete rows	事务提交或回滚，删除表中数据
会话级on commit preserve rows	会话结束清空表，释放临时B树

CREATE GLOBAL TEMPORARY TABLE TMP_EMP(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(15) NOT NULL,
JOB VARCHAR(10))
ON COMMIT DELETE ROWS;

2.3向表中插入数据

使用SQL语句向表中插入数据：
注意：因为表info含有自增列，无法对自增列进行赋值，所以采用指字段赋值法。

insert into T_test.info(bookId,test) values(0153,'helloworld');

使用DM管理工具导入数据：
在模式下打开表，点击“浏览数据”，在列名处右键鼠标，选择“导入”，用写好数据的Excel文件导入。

使用了外键，外键中未查询到该插入数据；

set IDENTITY_INSERT "T_test".info off;

插入部分字段

insert into "T_test".info(bookId,test) values(12,'helloworld');

2.3.1自增列

• 一个表只能有一个自增列，可以在create table时一起使用Identity定义字段为自增列。

• 自增列使用于int、bigint类型字段。

• 不允许用update对自增列进行修改。

2.4管理表

2.4.1更改表

2.4.1.1更改表名

语法：

alter table 表名 rename to 新表名；

把表info改名为Newinfo；

//更改表名
alter table info rename to Newinfo;

2.4.1.2更改表中字段

//主键
//增加主键
alter table 模式名.表名 add primary key(字段名);
//删除主键
alter table 模式名.表名 drop constraint 字段名;
alter table 模式名.表名 alter column 字段名 set null;

2.4.2删除表

语法：

drop table （if exists）模式名.表名  (restrict|cascade);

2.4.3清空表

语法：

truncate table (模式名.)表名 (partition 分区表名);

2.4.4查看表定义

查看数据库里面有哪些表

select table_name from user_tables;

查看表定义：
语法：

call SP_TABLEDEF('模式名','表名');

查看自增列信息：

//查看表上自增列当前值
select ident_current('模式名.表名');
//查看表上自增列的种子信息
select ident_seed('模式名.表名');
//查看表上自增列的增量信息
select ident_incr('模式名.表名');

三、SQL语句之索引

索引和索引的表可以存在不同的表空间。如果在一个表空间可以更加方便地管理，不在一个表空间可以减少磁盘竞争。

创建索引的目的：提高查询效率

为什么索引能提高查询效率：

数据是使用B+树存放的，数据的真实查找都是通过主键来定位的。
每一个分支都代表着一条记录，第二层为主键的值，第三层为真实的数据。（图片来自网络）

• 主键是属于聚集索引（即像目录一样按顺序查找，聚集索引的例子参考：字典）

• 如果使用select语句查找一条数据的话，就是在上图中，按照主键值一条一条的去匹配。这样在数据量非常大的情况下，是不适合的。因此大表需要建立索引来加快查找速度。

• 添加索引就是添加上图这样的B树结构，只不过把主键值换成了索引字段值，真实的数据换成了主键值。通过索引找到主键值，再通过主键值去磁盘上取出对应的数据。

• 使用联合索引的话就是添加上图类似的多层的结构，每一层代表联合索引的字段。

3.1创建索引

3.1.1创建普通索引

create index 索引名 on 表名（字段名）;
//如果表不在当前模式下可以指定模式名
create index 索引名 on 模式名.表名（字段名）；
//explain 查看语句的执行情况
explain select * from T_TEST.NEWINFO;

3.1.2创建聚集索引

语法：

create cluster index 索引名 on 表名（字段名）；

注意：每个普通表有且仅有一个聚集索引，多次创建聚集不会报错，但是会覆盖之前的设置。
主键使用的是聚集索引。

3.1.3创建唯一索引

语法：

create unique index 索引名 on 模式名.表名（字段名）；

• 如果为bookid创建唯一索引，查看表定义不能看出是不是唯一索引，但是往表中插入数据时，bookID已经默认被unique修饰，不能设置为相同值了。
• DM8会为主键和unique修饰的字段自动创建唯一索引。

3.1.4创建函数索引

语法：

create index ind on T1(A+B);
select * from T1 where A+B>1;

3.1.5创建位图索引

针对含有大量相同值的列创建(位图索引与聚集索引不能构建在同一张表上）。

原理：用一串字符串来描述列值，该字符串的总长度为数据总量，字符串内部都用01来描述列的取值。在做联合查询的时候可以用对应的字符串做and操作快速定位到符合条件的记录位置。
例如：查询5条记录中未婚的男士的结果。第三条记录符合查询条件。（图片来自网络）

语法：

create bitmap index 索引名 on 表名(字段名);

3.2重建索引

目的：对表进行了修改，重建索引可以释放不需要的存储空间
语法：

SP_REBUILD__INDEX(模式名，索引ID);

3.3删除索引

删除索引分为两种：使用create index创建的索引；由于约束自动创建的索引；

3.3.1手动创建的索引

语法：

drop index 模式名.索引名;

3.3.2自动创建的索引

在建表的时候primary key或者使用unique约束的键会自动创建索引。
primary key-聚集索引
unique-唯一索引
这样的索引是无法使用drop删除的，删除时需要连同约束一起删除。
语法：

alter table 表名 drop constraint 字段名；

3.4查看索引信息

语法：

select indexdef(索引ID,0/1)//返回信息中是否增加模式名前缀

四、SQL语句之触发器、视图

定义：触发器定义某些与数据相关的事情发生时，数据库应该采取的操作。
特点：自动激发执行

4.1创建触发器

语法：

create (or replace) trigger 触发器名 (with encryption)
before | after |instead of
on 表名
(for each row (when条件))
begin
DMSQL程序语句
end;
//or replace 如果已经存在该触发器，则删除它重新创建
//before执行前触发，after执行后触发，instead of视图上的触发器（用触发器体内的操作代替原操作）

4.2触发器的分类

开启打印功能

set serveroutput on//生效一次
commit;

触发器分类	触发动作	意义
表级触发器	基于数据，insert,delete,update	1.元祖级（for each row) 2.语句级（for each statement)
时间触发器	基于时间	指定时间
事件触发器	基于特定系统事件	通过指定database或某个schema来表示某个事件触发器的作用区域

4.3管理视图

视图的特点：虚表，建立在基表上。

4.3.1创建视图

语法：

create (or replace) view as 查询说明

CREATE VIEW PURCHASING.VENDOR_EXCELLENT AS
SELECT VENDORID, ACCOUNTNO, NAME, ACTIVEFLAG, CREDIT
FROM PURCHASING.VENDOR
WHERE CREDIT = 1;

CREATE VIEW PRODUCTION.VENDOR_STATIS(VENDORID, PRODUCT_COUNT) AS
SELECT VENDORID, COUNT(PRODUCTID)
FROM PRODUCTION.PRODUCT_VENDOR
GROUP BY VENDORID
ORDER BY VENDORID;

SELECT * FROM PRODUCTION.VENDOR_STATIS;

4.3.2删除视图

语法：

drop view (if exists) 模式名.视图名 restrict|cascade;

4.3.3查询视图

与基表的查询基本一致。

4.3.4编译视图

基表定义可能发生了改变，导致视图不能用，重新编译视图。

alter view 模式名.视图名 compile;

4.3.5更新视图

对视图的更新最终都会转换到对基表的更新上。

UPDATE 模式名.视图名
SET 字段名 = 0 WHERE NAME = '人民邮电出版社';

4.4管理序列

4.4.1创建序列

语法：

create sequence (模式名.)序列名 序列参数；

4.4.2修改序列

语法：

alter sequence (模式名.)序列名 序列参数

4.4.2.1步长的修改

分类	序列值
修改前没有使用nextval访问序列	序列值=当前值+（新步长-旧步长）
修改前用nextval访问了序列	序列值=当前值+新步长值

4.4.3删除序列

语法：

drop sequence 模式名.序列名;

4.4.4查看序列

语法：

select * from dba_sequences where sequence_name='序列名';

4.5管理同义词

4.6管理物化视图

定义：由一个表或者几个基表导出的表，与视图不同的是，物化视图导出了真实的数据。

4.6.1创建物化视图

语法：

create materialized view ....

4.6.2刷新物化视图

语法：

refresh materialized view 模式名.物化视图名
fast//快速刷新，增量刷新
complete//完全刷新
force//默认选项，快速刷新可用时采用快速刷新，否则用完全刷新

4.7管理外部链接

外部链接本质上就是实现数据库与数据库之间的连接

4.7.1配置条件

（1）关闭源端、目的端服务器

./DmServiceDMSERVER stop

windows下可以通过数据库服务查看器关闭；
Linux下通过命令./DmServiceDMSERVER stop关闭服务器；

（2）分别配置dm.ini文件

（3）分别创建dmmal.ini文件

• 注意MAL_PORT不能和dm.ini中的PORT_NUM一样；
• 源端和目的端的dmmal.ini保持完全一致
• 两个MAL_INST的实例名不能一致，否则会报duplicate ini错误。
• MAL_INST_NAME应该与当前数据库下dm.ini中的INSTANCE_NAME一致。
  
  

-（4）重启目的端、源端服务器

4.7.2创建外部链接

create or replace public link 外部链接名 connect 'DAMENG|ORACLE|ODBC' with 登录名 identified by  登录口令 using 'IP/端口号 | description.....  |  IP /服务名  |网络服务名';

create link link1 connect 'DAMENG' with SYSDBA identified by SYSDBA using '192.168.2.106/5261';

4.7.3删除外部链接

drop link 外部链接名;

4.7.4使用外部链接

select * from 表名 link 外部链接名;
select * from 表名@外部链接名;