达梦数据库常用sql

“SYSDBA”.“TEST”(模式.表名)

1.建表语句

CREATE TABLE "SYSDBA"."TEST"
(
"ID" INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
"NAME" VARCHAR(10) NOT NULL,
"VALUE" NUMBER(5,1),
"TYPE" INT,
"INSERT_TM" TIMESTAMP(6)
);

COMMENT ON TABLE "SYSDBA"."TEST" IS '测试表';
COMMENT ON COLUMN "SYSDBA"."TEST"."ID" IS '主键ID';
COMMENT ON COLUMN "SYSDBA"."TEST"."NAME" IS '名称';
COMMENT ON COLUMN "SYSDBA"."TEST"."VALUE" IS '值';
COMMENT ON COLUMN "SYSDBA"."TEST"."TYPE" IS '类型';
COMMENT ON COLUMN "SYSDBA"."TEST"."INSERT_TM" IS '插入时间';

---

2.插入语句

INSERT INTO "SYSDBA"."TEST"("NAME", "VALUE", "TYPE", "INSERT_TM") VALUES('test', 5.2, 1, sysdate);
INSERT INTO "SYSDBA"."TEST"("NAME", "VALUE", "TYPE", "INSERT_TM") VALUES('test1', 5.2, 2, sysdate);

---

3.更新语句

UPDATE "SYSDBA"."TEST" SET "VALUE" = 4.1 WHERE "ID" = 2;

---

4.删除语句

DELETE FROM "SYSDBA"."TEST" WHERE "ID" = 2;

---

5.查询语句

SELECT * FROM "SYSDBA"."TEST";

---

6.列操作语句

----------------- 添加列 ------------------
ALTER TABLE "SYSDBA"."TEST" ADD "PASSWORD" VARCHAR(20);
ALTER TABLE "SYSDBA"."TEST" ADD "PASSWORD" VARCHAR(20) NOT NULL;

----------------- 删除列 ------------------
ALTER TABLE "SYSDBA"."TEST" DROP "PASSWORD";

----------------- 修改列名 ------------------
ALTER TABLE "SYSDBA"."TEST" RENAME COLUMN "PASSWORD" TO "PW";

---