达梦相关文档 #

jdbc:dm://localhost:5236?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8

-- 创建表空间设定自动扩展
create tablespace app_demo_local_db datafile 'app_demo_local_db.dbf' size 128 autoextend on;
-- 创建用户默认表空间
create user app_demo_local_db identified by "app_demo_local_db@ADMIN" default tablespace app_demo_local_db;
-- 给用户赋值DBA权限
grant dba to app_demo_local_db;

-- 建表示例
--   查询当前数据库的字符集种类，0 代表 GB18030；1 代表 UTF-8。
--   注意：字符集决定 varchar(字节长度) 类型的汉字存储长度。https://eco.dameng.com/document/dm/zh-cn/sql-dev/dmpl-sql-datatype.html#3.1%20%E5%AD%97%E7%AC%A6%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B
--        GB18030 ：1 个汉字 = 2-4字节
--        UTF-8   ：1 个汉字 = 3字节；
SELECT
  DECODE(unicode, 0, 'GB18030', 1, 'UTF-8') as "当前字符集",
  cast(PAGE() / 1024 as varchar)            as "页大小",
  LENGTHB('中文')                            as "测试字符编码长度，GB18030：返回 4；UTF-8：返回 6"
;
drop table if exists TAB_NAME;
CREATE TABLE TAB_NAME
(
  ID             BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '数据ID，主键自增',
  NICK_NAME      VARCHAR(32)  NOT NULL DEFAULT '' COMMENT '昵称',
  REAL_NAME      VARCHAR(32)  NOT NULL DEFAULT '' COMMENT '真实姓名',
  AVATAR         VARCHAR(500) COMMENT '用户头像',
  USERNAME       VARCHAR(15)  NOT NULL COMMENT '登录账号',
  PHONE          VARCHAR(24)  NOT NULL DEFAULT '' COMMENT '手机号（密文）',
  EMAIL          VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '邮箱',
  ROLES          VARCHAR(200) NOT NULL COMMENT '角色 ID 集合，tab_role.id',
  CREATE_TIME    CHAR(14)     NOT NULL COMMENT '创建时间，yyyyMMddHHmmss',
  CREATE_USER_ID BIGINT       NOT NULL DEFAULT 0 COMMENT '创建用户ID',
  UPDATE_TIME    CHAR(17)     NOT NULL COMMENT '修改时间，yyyyMMddHHmmssSSS',
  UPDATE_USER_ID BIGINT       NOT NULL DEFAULT 0 COMMENT '修改用户ID',
  DELETED        TINYINT      NOT NULL DEFAULT 0 COMMENT '是否逻辑删除。 [0:否,1:是]',
  CONSTRAINT UK_USERNAME UNIQUE (USERNAME)
);
COMMENT ON TABLE TAB_NAME IS '用户表';
CREATE INDEX IDX_PHONE ON TAB_NAME (PHONE);
CREATE INDEX IDX_CREATE_TIME ON TAB_NAME (CREATE_TIME);

-- -- 当自增使用 IDENTITY(1,1) 自增时，无法插入主键，需要 SET IDENTITY_INSERT
-- SET IDENTITY_INSERT TAB_NAME ON;
-- INSERT INTO TAB_NAME(ID, USERNAME, CREATE_TIME)
-- VALUES(1000, 'xxx', TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')) ;
-- SET IDENTITY_INSERT TAB_NAME OFF;

-- 查询表字段信息
SELECT
  all_tab_cols.table_name
, c.comments AS table_comments
, all_tab_cols.column_id
, all_tab_cols.column_name
, b.comments AS column_comments
, all_tab_cols.data_type
, all_tab_cols.nullable
, all_tab_cols.data_length
, all_tab_cols.data_scale
, cols.column_position
FROM
  all_tab_cols
    LEFT JOIN all_col_comments b ON b.table_name = all_tab_cols.table_name AND b.column_name = all_tab_cols.column_name AND all_tab_cols.owner = b.owner
    LEFT JOIN all_tab_comments c ON c.table_name = b.table_name AND c.owner = b.owner
    LEFT JOIN
    (SELECT
       dcc.table_name
     , dcc.column_name
     , dcc.column_position
     , dcc.index_owner
     FROM
       all_ind_columns dcc
         JOIN all_constraints dc ON dcc.index_name = dc.index_name AND dc.constraint_type = UPPER('p') AND dcc.index_owner = dc.owner) cols
    ON cols.table_name = b.table_name
      AND cols.column_name = b.column_name
      AND cols.index_owner = all_tab_cols.owner
WHERE
    all_tab_cols.owner = UPPER('APP_DEMO_LOCAL_DB') --表的拥有者
AND all_tab_cols.table_name IN (UPPER('tab_city')) --表名
ORDER BY
  all_tab_cols.table_name
, all_tab_cols.column_id ASC
;

-- -- 删除用户
-- drop user if exists app_demo_local_db cascade;
-- -- 删除表空间
-- drop tablespace if exists app_demo_local_db;
-- -- 创建表空间设定自动扩展
-- create tablespace app_demo_local_db datafile 'app_demo_local_db.dbf' size 128 autoextend on;
-- -- 创建用户默认表空间
-- create user app_demo_local_db identified by "app_demo_local_db@ADMIN" default tablespace app_demo_local_db;
-- -- 给用户赋值DBA权限
-- grant dba to app_demo_local_db;
-- -- 修改用户密码：ALTER USER 用户名 IDENTIFIED BY "新密码"; 修改 SYSDBA 密码示例：
-- ALTER USER SYSDBA IDENTIFIED BY "dmadmin@2024";
-- 查询用户和表空间
select username, user_id, default_tablespace, profile from dba_users where username = 'app_demo_local_db';
-- -- 查看当前用户模式名
select sys_context('USERENV','CURRENT_SCHEMA');
-- 查询所有用户的表，再按 owner 过滤
select * from all_tables where owner = 'APP_DEMO_LOCAL_DB';
-- 查询包括系统表
select * from dba_tables;
-- 查询当前用户的表
select * from user_tables;
-- 查询建表 DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', '表名', '模式名');
-- 查询表说明，再按表名过滤
select * from all_tab_comments where table_name = 'TAB_USER';
-- 查询当前用户的表说明
select * from user_tab_comments;
-- 查看所有表字段，再按 table_name 过滤
select * from all_tab_columns where table_name='TAB_USER';
-- 查询当前用户的表字段，再按 table_name 过滤
select * from user_tab_columns where table_name='TAB_USER';
-- 查询字段说明
select * from user_col_comments where table_name = 'TAB_USER';
-- 查询全部索引
select * from all_indexes;
-- 查询当前用户的索引
select * from user_indexes;
-- 查询索引
SELECT TABLE_OWNER as "模式名", TABLE_NAME as "表名", COLUMN_NAME as "列名", INDEX_NAME as "索引名" from DBA_IND_COLUMNS
-- 查询用户的存储过程
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' and OWNER = USER;
-- 处理锁表
--   找到表名对应的SESS_ID,执行: sp_close_session(140670650200624);
select a.*,b.NAME,c.SESS_ID from v$lock a left join sysobjects b on b.ID=a.TABLE_ID left join v$sessions c on a.TRX_ID=c.TRX_ID;


-- 执行服务器上的数据库脚本
start /home/dmdba/dmdba/sqls/call.sql ;

-- 连接并执行SQL语句的两种方法（SQL语句可以换成 start /sqls/call.sql ）：
/opt/dmdbms/bin/disql SYSDBA/'"SYSDBA001"' -E "select table_name from user_tables limit 5;select * from user_tab_comments limit 5;"
/opt/dmdbms/bin/dmsql -u SYSDBA -p SYSDBA001 -d APP_DEMO_LOCAL_DB -q "select id, username from APP_DEMO_LOCAL_DB.TAB_USER where id in (76,77)" -o output.txt

-- 批量执行SQL
disql SYSDBA/'"SYSDBA001"' <<EOF
select table_name from user_tables limit 5;
select * from user_tab_comments limit 5;
EOF

-- 批量执行SQL
disql SYSDBA/'"SYSDBA001"' <<EOF
select id, pdf from OB_FILE limit 10;
-- 退出DISQL
exit;
EOF

-- 全量备份数据库
dexp SYSDBA/'"这里密码"' file=/nfs/db.dmp full=y

-- 备份表
dexp SYSDBA/'"这里密码"' file=/nfs/db_tables.dmp tables=TAB_USER,TAB_ROLE

-- 还原表
dimp SYSDBA/'"这里密码"' file=/nfs/db_tables.dmp tables=TAB_USER,TAB_ROLE

-- 查询结果写入文件
spool /tmp/db.txt no_print
select id,username from tab_user;
spool off

-- CLOB字段导出
cat > /tmp/table_clob.ctl <<EOF
LOAD DATA
INFILE '/tmp/table_clob.txt'
INTO TABLE APP_DEMO_LOCAL_DB.clob_column
FIELDS '|'
(id, clob_column)
EOF

/home/dmdba/dmdbms/bin/dmfldr userid=SYSDBA/'"dmadmin@2024"'@localhost:5236 control=\'/tmp/table_clob.ctl\' mode=\'out\' LOB_FILE_NAME=\'table_clob.lob\'