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

达梦角色说明

权限说明表

创建角色

/*
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 :允许访问的时间段
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 '身份证号';

插入数据到表中

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

查询表数据

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

查询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：索引全扫描，不需要扫描表。

案例

案例1：

按照以下sql顺序创建相应的内容

1. 创建角色
2. 给角色授权
3. 创建表空间
4. 创建用户
5. 给用户分配角色
6. 创建模式
7. 创建序列
8. 创建表

/* 创建 角色 */
create role ORDINARY;

/*授予角色指定权限*/
grant 
CREATE TABLE,
CREATE VIEW,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE INDEX,
CREATE LINK,
BACKUP DATABASE,
CREATE CONTEXT INDEX,
CREATE PACKAGE,
CREATE REPLICATE,
CREATE SYNONYM,
CREATE PUBLIC SYNONYM,
INSERT TABLE,
UPDATE TABLE,
DELETE TABLE,
SELECT TABLE,
INSERT VIEW,
UPDATE VIEW,
DELETE VIEW,
SELECT VIEW,
EXECUTE PROCEDURE,
EXECUTE PACKAGE,
SELECT SEQUENCE,
CREATE MATERIALIZED VIEW,
SELECT MATERIALIZED VIEW,
CREATE DOMAIN,
USAGE DOMAIN,
DUMP TABLE,
CREATE PARTITION GROUP,
USAGE PARTITION GROUP
to ORDINARY;

/*创建表空间*/
create tablespace MYTABLESPACE
datafile 'G:\DM8\install_new\data\MYTABLESPACE\MYTABLESPACE.DBF' 
size 128 
autoextend on 
maxsize 67108863 
CACHE = NORMAL;


/*创建用户*/
create user MY identified by "123456" password_policy 0
default tablespace MYTABLESPACE 
default index tablespace MYTABLESPACE ;

/*给用户分配角色*/
grant "ORDINARY","VTI" to MY;

/*创建模式*/
CREATE SCHEMA MYSCHEMA  AUTHORIZATION MY;

/*创建序列*/
CREATE SEQUENCE MYSCHEMA.SEQ_MYTABLESPACE
INCREMENT BY 1
START WITH 1
MAXVALUE 9223372036854775807
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

/*创建表
若存在相同名称的表则先删除后创建
*/
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 '身份证号';

转载：https://blog.csdn.net/qq_38313548/article/details/128563750