语法特征之模式对象管理
一、状态与模式
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 表名@外部链接名;