【达梦8】sql语句学习笔记

---

👇👇👇👇公众号内 无障碍无限制 阅读👇👇👇👇

发送关键字获取内容： 数据库

👆👆👆👆公众号内无障碍无限制阅读👆👆👆👆👆👆

---

达梦角色说明

角色名称	角色简单说明
DBA	DM 数据库系统中对象与数据操作的最高权限集合，拥有构建数据库的全部特权，只有 DBA 才可以创建数据库结构
RESOURCE	可以创建数据库对象，对有权限的数据库对象进行数据操纵，不可以创建数据库结构
PUBLIC	不可以创建数据库对象，只能对有权限的数据库对象进行数据操纵
VTI	具有系统动态视图的查询权限，VTI 默认授权给 DBA 且可转授
SOI	具有系统表的查询权限
SVI	具有基础 V 视图的查询权限
DB_AUDIT_ADMIN	数据库审计的最高权限集合，可以对数据库进行各种审计操作，并创建新的审计用户
DB_AUDIT_OPER	可以对数据库进行各种审计操作，但不能创建新的审计用户
DB_AUDIT_PUBLIC	不能进行审计设置，但可以查询审计相关字典表
DB_AUDIT_VTI	具有系统动态视图的查询权限，DB_AUDIT_VTI 默认授权给DB_AUDIT_ADMIN
DB_AUDIT_SOI	具有系统表的查询权限
DB_AUDIT_SVI	具有基础 V 视图和审计 V 视图的查询权
DB_POLICY_ADMIN	数据库强制访问控制的最高权限集合，可以对数据库进行强制访问控制管理，并创建新的安全管理用户
DB_POLICY_OPER	可以对数据库进行强制访问控制管理，但不能创建新的安全管理用户
DB_POLICY_PUBLIC	不能进行强制访问控制管理，但可以查询强制访问控制相关字典表
DB_POLICY_VTI	具 有 系 统 动 态 视 图 的 查 询 权 限 ， DB_POLICY_VTI 默 认 授 权 给DB_POLICY_ADMIN 且可转授
DB_POLICY_SOI	具有系统表的查询权限
DB_POLICY_SVI	具有基础 V 视图和安全 V 视图的查询权限
DB_OBJECT_ADMIN	可以在自己的模式下创建各种数据库对象并进行数据操纵，也可以创建和删除非模式对象
DB_OBJECT_OPER	可以在自己的模式下创建数据库对象并进行数据操纵
DB_OBJECT_PUBLIC	不可以创建数据库对象，只能对有权限的数据库对象进行数据操纵
DB_OBJECT_VTI	具 有 系 统 动 态 视 图 的 查 询 权 限 ， DB_OBJECT_VTI 默 认 授 权 给DB_OBJECT_ADMIN 且可转授
DB_OBJECT_SOI	具有系统表的查询权限
DB_OBJECT_SVI	和 SVI 权限一样

权限说明表

权限	说明
ALTER DATABASE	修改数据库
RESTORE DATABASE	恢复数据库
CREATE USER	创建用户
ALTER USER	修改用户
DROP USER	丢弃用户
CREATE ROLE	创建角色
CREATE SCHEMA	创建模式
CREATE TABLE	创建数据表
CREATE VIEW	创建视图
CREATE PROCEDURE	创建存储过程
CREATE SEQUENCE	创建序列
CREATE TRIGGER	创建触发器
CREATE INDEX	创建索引
CREATE CONTEXT INDEX	创建上下文索引
BACKUP DATABASE	备份数据库
CREATE LINK	创建链接
CREATE REPLICATE	创建副本
CREATE PACKAGE	创建包
CREATE SYNONYM	创建同义词
CREATE PUBLIC SYNONYM	创建公共同义词
ALTER REPLICATE	修改副本
DROP REPLICATE	删除副本
DROP ROLE	丢弃角色
ADMIN ANY ROLE	管理任何角色
ADMIN ANY DATABASE PRIVILEGE	管理任何数据库权限
GRANT ANY OBJECT PRIVILEGE	授予任何对象特权
CREATE ANY SCHEMA	创建任何模式
DROP ANY SCHEMA	丢弃任何模式
CREATE ANY TABLE	创建任何数据表
ALTER ANY TABLE	修改任何数据表
DROP ANY TABLE	丢弃任何数据表
INSERT TABLE	插入数据表
INSERT ANY TABLE	插入任何数据表
UPDATE TABLE	更新数据表
UPDATE ANY TABLE	更新任何数据表
DELETE TABLE	删除数据表
DELETE ANY TABLE	删除任何数据表
SELECT TABLE	查询数据表
SELECT ANY TABLE	查询任何数据表
REFERENCES TABLE	引用表
REFERENCES ANY TABLE	引用任何表
GRANT TABLE	授权数据表
GRANT ANY TABLE	授权任何数据表
CREATE ANY VIEW	创建任何视图
ALTER ANY VIEW	修改任何视图
DROP ANY VIEW	丢弃任何视图
INSERT VIEW	插入视图
INSERT ANY VIEW	插入任何视图
UPDATE VIEW	更新视图
UPDATE ANY VIEW	更新任何视图
DELETE VIEW	删除视图
DELETE ANY VIEW	删除任何视图
SELECT VIEW	查询视图
SELECT ANY VIEW	查询任何视图
GRANT VIEW	授权视图
GRANT ANY VIEW	授权任何视图
CREATE ANY PROCEDURE	创建任何存储过程
DROP ANY PROCEDURE	丢弃任何存储过程
EXECUTE PROCEDURE	执行存储过程
EXECUTE ANY PROCEDURE	执行任何存储过程
GRANT PROCEDURE	授权存储过程
GRANT ANY PROCEDURE	授权任何存储过程
CREATE ANY SEQUENCE	创建任何序列
DROP ANY SEQUENCE	丢弃任何序列
SELECT SEQUENCE	查询序列
SELECT ANY SEQUENCE	查询任何序列
GRANT SEQUENCE	授权序列
GRANT ANY SEQUENCE	授权任何序列
CREATE ANY TRIGGER	创建任何触发器
DROP ANY TRIGGER	丢弃任何触发器
CREATE ANY INDEX	创建任何索引
ALTER ANY INDEX	修改任何索引
DROP ANY INDEX	丢弃任何索引
CREATE ANY CONTEXT INDEX	创建任何上下文索引
ALTER ANY CONTEXT INDEX	修改任何上下文索引
DROP ANY CONTEXT INDEX	丢弃任何上下文索引
CREATE ANY PACKAGE	创建任何包
DROP ANY PACKAGE	丢弃任何包
EXECUTE PACKAGE	执行包
EXECUTE ANY PACKAGE	执行任何包
GRANT PACKAGE	授权包
GRANT ANY PACKAGE	授权任何包
CREATE ANY LINK	创建任何链接
DROP ANY LINK	丢弃任何链接
CREATE ANY SYNONYM	创建任何同义词
DROP ANY SYNONYM	丢弃任何同义词
DROP PUBLIC SYNONYM	丢弃公共同义词
SELECT ANY DICTIONARY	查询任何同义词
ADMIN REPLAY	管理重演
ADMIN BUFFER	管理缓冲区
CREATE TABLESPACE	创建表空间
ALTER TABLESPACE	修改表空间
DROP TABLESPACE	丢弃表空间
ALTER ANY TRIGGER	修改任何触发器
CREATE MATERIALIZED VIEW	创建物化视图
CREATE ANY MATERIALIZED VIEW	创建任何物化视图
DROP ANY MATERIALIZED VIEW	丢弃任何物化视图
ALTER ANY MATERIALIZED VIEW	修改任何物化视图
SELECT MATERIALIZED VIEW	查询物化视图
SELECT ANY MATERIALIZED VIEW	查询任何物化视图
CREATE ANY DOMAIN	创建任何域
DROP ANY DOMAIN	丢弃任何域
CREATE DOMAIN	创建域
GRANT ANY DOMAIN	授权任何域
GRANT DOMAIN	授权域
USAGE ANY DOMAIN	使用任何域
USAGE DOMAIN	使用域
CREATE ANY CONTEXT	创建任何上下文
DROP ANY CONTEXT	丢弃任何上下文
GRANT ANY CONTEXT	授权任何上下文
COMMENT ANY TABLE	注释任何数据表
DUMP ANY TABLE	转储任何数据表
DUMP TABLE	转储数据表
ADMIN JOB	管理任务
CREATE ANY DIRECTORY	创建任何目录
DROP ANY DIRECTORY	丢弃任何目录
ALTER ANY SEQUENCE	修改任何序列
CREATE PROFILE	创建资源限制
ALTER PROFILE	修改资源限制
DROP PROFILE	丢弃资源限制
CREATE PARTITION GROUP	创建分区组
CREATE ANY PARTITION GROUP	创建任何分区组
DROP ANY PARTITION GROUP	丢弃任何分区组
USAGE PARTITION GROUP	使用分区组
USAGE ANY PARTITION GROUP	使用任何分区组

创建角色

/*
ORDINARY: 自定义的角色名称 
*/
create role ORDINARY;

/*
将ORDINARY角色归属于 PUBLIC、RESOURCE、VTI角色下
*/
grant "PUBLIC","RESOURCE","VTI" to ORDINARY;

/*
授予ORDINARY角色权限,详细请查看权限说明表
*/
grant 
CREATE SCHEMA,
CREATE TABLE,
CREATE VIEW,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE INDEX,
CREATE CONTEXT INDEX,
BACKUP DATABASE,
CREATE LINK,
CREATE REPLICATE,
CREATE PACKAGE,
CREATE SYNONYM,
CREATE PUBLIC SYNONYM,
ALTER REPLICATE,
INSERT TABLE,
UPDATE TABLE,
DELETE TABLE,
SELECT TABLE,
GRANT TABLE,
INSERT VIEW,
UPDATE VIEW,
DELETE VIEW,
SELECT VIEW,
GRANT VIEW,
SELECT SEQUENCE,
EXECUTE PACKAGE,
GRANT PACKAGE,
CREATE PROFILE,
ALTER PROFILE 
to ORDINARY;

提示：

1. 安全严格管控的生产系统推荐授予以下角色

"PUBLIC","VTI","SOI"

2. 若需要放开create权限，则推荐授予以下角色

 "RESOURCE","PUBLIC","VTI","SOI"

创建用户

/*
说明：
MY：自定义的用户名
123456：密码
password_policy 0：密码策略
not_allow_ip：不允许访问的ip
allow_ip ：允许访问的ip
allow_datetime :允许访问的时间段
default tablespace MYTABLESPACE ：默认使用的表空间
default index tablespace MYTABLESPACE: 默认使用的索引表空间 
*/
create user MY identified by "123456" password_policy 0
-- allow_ip "192.168.0.188"
-- allow_datetime "2023-01-05" "16:12:51" to "2023-01-31" "16:12:51"
default tablespace MYTABLESPACE 
default index tablespace MYTABLESPACE ;

/*
授予用户角色
*/
grant "PUBLIC","SOI","ORDINARY","VTI" to MY;

/*授予用户权限，具体权限参考权限说明*/
grant UPDATE TABLE,SELECT TABLE,GRANT TABLE to MY;

创建模式

/*
说明：
MYSCHEMA  ：自定义的模式名称
MY：模式授权给指定的用户
*/
CREATE SCHEMA MYSCHEMA  AUTHORIZATION MY;

创建表空间

/*
说明：
MYTABLESPACE : 自定义的表空间名称
'G:\DM8\install_new\data\MYTABLESPACE\test.DBF' ：表空间数据文件地址
128：数据文件大小
autoextend ：默认自动扩充
67108863 ：数据文件扩充上线值
NORMAL: 普通缓冲区
*/
create tablespace MYTABLESPACE
datafile 'G:\DM8\install_new\data\TEST\test.DBF' 
size 128 
autoextend on 
maxsize 67108863 
CACHE = NORMAL;

创建序列

/*
说明：
MYTABLESPACE.SEQ_MYTABLESPACE: 模式名称.序列名称
INCREMENT BY 1 ：自增1
START WITH 1：从1开始
MAXVALUE：最大值
MINVALUE：最小值
NOCACHE：不缓存
NOORDER：非顺序
*/
CREATE SEQUENCE MYSCHEMA.SEQ_MYTABLESPACE
INCREMENT BY 1
START WITH 1
MAXVALUE 9223372036854775807
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

创建表

/*
说明：
MYSCHEMA.T_USER: 模式名称.表名
MYTABLESPACE：表空间名
*/
-- 若存在相同名称的表则先删除后再创建
DROP TABLE IF EXISTS MYSCHEMA.T_USER;
CREATE TABLE MYSCHEMA.T_USER
(
id INTEGER NOT NULL,
name VARCHAR2(255),
phone VARCHAR2(50),
idCard VARCHAR2(50),
NOT CLUSTER PRIMARY KEY(id ),
UNIQUE(phone),
UNIQUE(idCard)) STORAGE(ON MYTABLESPACE, CLUSTERBTR) ;

COMMENT ON TABLE MYSCHEMA.T_USER IS '用户表';
COMMENT ON COLUMN MYSCHEMA.T_USER.id IS 'id主键';
COMMENT ON COLUMN MYSCHEMA.T_USER.name IS '姓名';
COMMENT ON COLUMN MYSCHEMA.T_USER.phone IS '手机号';
COMMENT ON COLUMN MYSCHEMA.T_USER.idCard IS '身份证号';

向已有的表添加索引

创建唯一索引

/* 
 说明：
 	unique ：索引类型
	MYSCHEMA.uq_name_phone_idcard：模式名.索引名称
	MYSCHEMA.T_USER : 模式名.表名
	name,phone,idCard:分别为表中的字段名称
	initial 1： 初始分配簇大小
	next 1：下次分配簇大小
	minextents：最小保留簇大小
*/
create unique  index MYSCHEMA.uq_name_phone_idcard on MYSCHEMA.T_USER(name,phone,idCard) storage(initial 1,next 1,minextents 1);

例子

给多个字段创建组合索引，并将索引存到指定的索引表空间中

# 格式：create index 索引名称 on 模式名.表名（字段名,...） tablespace 索引表空间名称;
create index idx_code_name_rId_rTable on MYSCHEMA.T_USER (code,name,rid,rtable) tablespace TS_ATTACHMENT_IDX;

创建空间索引

/* 
 说明：
	spatial：索引类型
	MYSCHEMA.uq_name_phone_idcard：模式名.索引名称
	MYSCHEMA.T_USER : 模式名.表名
	name,phone,idCard:分别为表中的字段名称
	initial 1： 初始分配簇大小
	next 1：下次分配簇大小
	minextents：最小保留簇大小
*/
create spatial  index MYSCHEMA.uq_name_phone_idcard on MYSCHEMA.T_USER(name,phone,idCard) storage(initial 1,next 1,minextents 1);

创建数组索引

/* 
 说明：
	array：索引类型
	MYSCHEMA.uq_name_phone_idcard：模式名.索引名称
	MYSCHEMA.T_USER : 模式名.表名
	name,phone,idCard:分别为表中的字段名称
	initial 1： 初始分配簇大小
	next 1：下次分配簇大小
	minextents：最小保留簇大小
*/
create array index MYSCHEMA.uq_name_phone_idcard on MYSCHEMA.T_USER(name,phone,idCard) storage(initial 1,next 1,minextents 1);

向已有的表添加新列

/*
说明:
MYSCHEMA.T_USER : 模式名.表名
sex:新的列名
*/
-- 向 MYSCHEMA.T_USER 表中添加新列sex	
alter table MYSCHEMA.T_USER add column(sex number(1) default (1) not null );
-- 给新列添加备注
comment on column MYSCHEMA.T_USER.sex is '性别 1男 0女';

插入数据到表中

/*
说明:
MYSCHEMA.T_USER : 模式名.表名
MYSCHEMA.SEQ_MYTABLESPACE.NEXTVAL:序列。格式：模式名.序列名.NEXTVAL
*/
insert into MYSCHEMA.T_USER (id,name,phone,idCard) 
values(MYSCHEMA.SEQ_MYTABLESPACE.NEXTVAL,'张三','15578711016','4507211196612266390');

查询

查询当前数据库实例信息

select * from v$instance;

查询数据库集群信息

select * from v$dm_arch_ini;

查询表数据

/*
说明：
MYSCHEMA.T_USER： 模式名.表名
*/
select * from MYSCHEMA.T_USER

查询所有定时任务的信息

SELECT * FROM sysjob.sysjobs;

查询sql执行计划

方式1

以树形的方式展示执行计划

explain select * from MYSCHEMA.T_USER

方式2

以结果集的方式展示执行计划

explain for select * from MYSCHEMA.T_USER

执行计划说明：

1. CSCN ：基础全表扫描，从头到尾，全部扫描
2. SSCN ：二级索引扫描, 从头到尾，全部扫描
3. SSEK ：二级索引范围扫描 ，通过键值精准定位到范围或者单值
4. CSEK ：聚簇索引范围扫描 ，通过键值精准定位到范围或者单值
5. BLKUP ：根据二级索引的ROWID 回原表中取出全部数据(b + a)
6. SSEK2：二级索引扫描即先扫描索引，再通过主键、聚集索引、ROWID等信息去扫描表；
7. CSEK2：聚集索引扫描只需要扫描索引，不需要扫描表；
8. SSCN：索引全扫描，不需要扫描表。

如下图

执行顺序记住口诀：最右最上先执行。即缩进最多的最先执行，同级缩进的最上最先执行。

查询达梦数据库内存总量

 select
(select sum(n_pages) * page()/1024/1024 from v$bufferpool)||'MB' as 系统缓冲区大小BUFFER_SIZE,
(select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as 共享内存池大小mem_pool,
(select sum(n_pages) * page()/1024/1024 from v$bufferpool)+(select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as 内存总量TOTAL_SIZE
from  dual;

查询sql语句占用内存情况

SELECT "SESSID", MAX_MEM_USED||'KB',SQL_TXT FROM V$SQL_STAT order by MAX_MEM_USED DESC;

查询内存池使用信息

方式一

select  
name as 内存池名称,                      --内存池名称
is_shared as 是否是共享,                   --是否是共享的
is_overflow as 是否用到了备份池,                  --是否用到了备份池
org_size/1024.0/1024.0 || 'M' as 内存池初始大小,        --内存池初始大小
TOTAL_size/1024.0/1024.0 || 'M' as 内存池总大小_包括扩展的,     --内存池总大小(包括扩展的)
RESERVED_SIZE/1024.0/1024.0 || 'M' as 内存池总大_小包括扩展的,  --当前已分配大小(包括扩展的)
DATA_SIZE/1024.0/1024.0 || 'M' as 实际有效字节,      --实际有效字节
EXTEND_SIZE || 'M' as 实际有效字节,                --每次扩展多少
TARGET_SIZE || 'M' as 目标大小,                --目标大小
N_EXTEND_NORMAL as  TARGET范围内累计扩展次数,         --TARGET范围内累计扩展次数
N_EXTEND_EXCLUSIVE  as 超过TARGET累计扩展次数        --超过TARGET累计扩展次数
from  v$mem_pool
order by  TOTAL_size desc;

方式二

select  (
	CASE name 
	WHEN 'memory pool size in bytes' THEN '内存池总的大小' 
	WHEN 'memory used bytes' THEN '内存池使用的内存大小'
	WHEN 'memory used bytes from os' THEN '内存池从操作系统分配的大小'
	END CASE 
	) ,stat_val/1024.0/1024.0 from  v$sysstat where CLASSID=11 ; 

查询会话的内存使用量

SELECT
        A.CREATOR  ,
        B.SQL_TEXT ,
        SUM(A.TOTAL_SIZE)/1024.0/1024.0 || 'M' 当前总量_包括扩展,    --当前总量(包括扩展)
        SUM(A.DATA_SIZE) /1024.0/1024.0 || 'M' 实际使用量  --实际使用量
FROM
        V$MEM_POOL A,
        V$SESSIONS B
WHERE
        A.CREATOR = B.THRD_ID
GROUP BY
        A.CREATOR,
        B.SQL_TEXT
ORDER BY
        当前总量_包括扩展 DESC;

查看表空间使用情况

SELECT
        Upper(F.TABLESPACE_NAME) "表空间名"  ,
        D.TOT_GROOTTE_MB "表空间大小(M)" ,
        D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
        To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')|| '%' "使用比"   , 
        F.TOTAL_BYTES "空闲空间(M)",
        F.MAX_BYTES "最大块(M)"
FROM  (
        SELECT
                TABLESPACE_NAME                                   ,
                Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
                Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
        FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
      )  F,
      (
        SELECT
                DD.TABLESPACE_NAME,
                Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
        FROM SYS.DBA_DATA_FILES DD  GROUP BY  DD.TABLESPACE_NAME
      )  D
WHERE
        D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY
        2 desc;

查询指定表中的字段数

语法

select count(*) as 字段总数 from all_tab_columns where table_name='表名称' AND OWNER='模式名称';

例子

select count(*) as 字段总数 from all_tab_columns where table_name='T_USER AND OWNER='MYSCHEMA';

扩展

oracle 的查询表字段数

SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '表名称';

达梦DES加密解密

加密

DECLARE
BEGIN
--加密
-- 450111111111016019 明文
-- 1122334455667788 密钥key
select RAWTOHEX(CFALGORITHMSENCRYPT(
utl_i18n.string_to_raw('450111111111016019', 'UTF8'),
129,
utl_i18n.string_to_raw('1122334455667788', 'UTF8')));
END;
/

解密