常用查询

查看库下所有的表名
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='TEST_DATABASE';

SELECT A_T_C.COLUMN_NAME AS COLUMN_NAME,A_T_C.DATA_TYPE AS DATA_TYPE,A_C_C.COMMENTS AS COMMENTS 
FROM ALL_TAB_COLUMNS AS A_T_C LEFT JOIN ALL_COL_COMMENTS AS A_C_C 
ON A_T_C.TABLE_NAME=A_C_C.TABLE_NAME AND A_T_C.COLUMN_NAME=A_C_C.COLUMN_NAME 
WHERE A_T_C.TABLE_NAME='TEST_TABLE';

创建表空间

https://eco.dameng.com/document/dm/zh-cn/start/dm-create-tablespace.html
create tablespace "DATA" datafile '/opt/dmdbms/data/DATA.DBF' size 128 ;
create tablespace ZZZZZ datafile 'd:\AAA.DBF' size 128 CACHE = NORMAL;

创建用户

# 创建用户 TEST ，密码 “Dameng@123”，使用散列算法 SHA512 ，使用存储加密密钥为 “12345
# 指定表空间为 TEST，索引表空间为 TEST，授予 “PUBLIC” 和 “SOI” 权限
create user "TEST" identified by "Dameng@123" hash with SHA512 salt
encrypt by "123456"
default tablespace "TEST"
default index tablespace "TEST";

grant "PUBLIC","SOI" to "TEST";

创建模式（库）

https://eco.dameng.com/document/dm/zh-cn/start/dm-user-mode-relation.html

-- 创建库
CREATE SCHEMA "TEST_DATABASE";

-- 级联删库
DROP SCHEMA IF EXISTS "TEST_DATABASE" CASCADE;

创建表

CREATE TABLE "TEST_DATABASE"."TEST_TABLE"
(
"ID" INT IDENTITY(1, 1) PRIMARY KEY,
"KEY" CHAR(10),
"VALUE" CHAR(10),
"UPDATE_TIME" DATETIME(6) DEFAULT CURRENT_TIMESTAMP()
);

COMMENT ON TABLE "TEST_DATABASE"."TEST_TABLE" IS '测试表';
COMMENT ON COLUMN "TEST_DATABASE"."TEST_TABLE"."KEY" IS '键';
COMMENT ON COLUMN "TEST_DATABASE"."TEST_TABLE"."VALUE" IS '值';
COMMENT ON COLUMN "TEST_DATABASE"."TEST_TABLE"."UPDATE_TIME" IS '更新时间';

# 修改表
alter table "TEST_DATABASE"."TEST_TABLE" add column("REMARK" CHAR(10));
COMMENT ON COLUMN "TEST_DATABASE"."TEST_TABLE"."REMARK" IS '备注';

alter table "TEST_DATABASE"."TEST_TABLE" modify "KEY" VARCHAR(50);
alter table "TEST_DATABASE"."TEST_TABLE" modify "VALUE" VARCHAR(50);
alter table "TEST_DATABASE"."TEST_TABLE" modify "REMARK" VARCHAR(50);

读写数据

INSERT INTO "TEST_DATABASE"."TEST_TABLE" ("KEY","VALUE","REMARK") VALUES ('hello','world','测试');
INSERT INTO "TEST_DATABASE"."TEST_TABLE"("KEY","VALUE","REMARK") VALUES ('DM','database','关系型数据库');
INSERT INTO "TEST_DATABASE"."TEST_TABLE"("KEY","VALUE","REMARK") VALUES ('springboot','java','后端语言');

UPDATE "TEST_DATABASE"."TEST_TABLE" SET "KEY"='DM8' WHERE "KEY"='DM';

SELECT * FROM "TEST_DATABASE"."TEST_TABLE";