Kingbase金仓数据库常用语法

SELECT
	*
FROM
	information_schema.COLUMNS
WHERE
	TABLE_NAME = 'student'

查询public模式下的表信息

SELECT
	*
FROM
	information_schema.TABLES
WHERE
	table_schema = 'public';

插入多个数据

INSERT INTO "student" ("id", "name")
VALUES (1, 'name1'),(2, 'name2'),(3, 'name3');

删除外键

ALTER TABLE "congbiao" DROP CONSTRAINT congbiao_waijian_id_fkey;

添加外键

-- 添加外键 如果在删除外键的时候把主表id删除了，子表数据遗留，再次添加外键时候，子表的外键找不到对应的主表id，导致添加外键失败
ALTER TABLE "congbiao"ADD CONSTRAINT congbiao_waijian_id_fkey FOREIGN KEY ("waijian_id") REFERENCES "zhubiao"("id") ON DELETE CASCADE;

禁用和开启外键

alter table congbiao disable constraint congbiao_waijian_id_fkey;
alter table congbiao enable constraint congbiao_waijian_id_fkey;

查索引

SELECT
    "indexname" AS INDEX_NAME
FROM
    pg_indexes

查模式下表

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

表注释

COMMENT ON TABLE "table" IS '注释内容';

字段注释

COMMENT ON COLUMN "table"."column" IS '注释内容';

查看数据库版本

SELECT VERSION

查看数据库模式

SHOW database_mode

添加字段

ALTER TABLE student ADD COLUMN name varchar(100) NOT NULL DEFAULT '';

删除字段

ALTER TABLE student DROP COLUMN name;

修改字段默认值

ALTER TABLE student ALTER COLUMN age SET DEFAULT 18;

重命名表

ALTER TABLE student RENAME TO teacher;

重命名字段

ALTER TABLE student RENAME COLUMN "name" TO "age";

修改字段类型

ALTER TABLE student 
	ALTER COLUMN "age" TYPE int;

修改字段不为空

ALTER TABLE student ALTER COLUMN id SET NOT NULL;

修改字段可以为空

ALTER TABLE student ALTER COLUMN id drop NOT NULL;

设置字段默认值

ALTER TABLE student ALTER COLUMN id SET DEFAULT '1';

添加主键

ALTER TABLE student ADD PRIMARY KEY (id);

ALTER TABLE student ADD CONSTRAINT student_pk PRIMARY KEY (id);

建立普通索引

CREATE INDEX student_index_name ON student USING BTREE (id,age);

CREATE INDEX ON student (id,age);

建立唯一索引

CREATE UNIQUE INDEX unique_index_name ON student USING BTREE(id);

CREATE UNIQUE INDEX ON student (id);

索引重命名

ALTER INDEX student_index_name RENAME TO new_index_name;

删除索引

DROP INDEX unique_index_name;

添加唯一约束

ALTER TABLE student ADD CONSTRAINT student_unique_index_name UNIQUE (id,age);

ALTER TABLE student ADD UNIQUE (age);

删除唯一约束

ALTER TABLE student DROP CONSTRAINT student_unique_index_name;

添加序列
这个版本已支持AUTO_INCREMENT：
KingbaseES V008R006C008B0025 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

-- 自增从1开始
CREATE SEQUENCE IF NOT EXISTS "Students_id_seq" START 1;
-- 应用在student表id字段
ALTER TABLE "student" ALTER COLUMN "id" SET DEFAULT nextval('Students_id_seq');

插入内容有 ’ 插入不进入
错误:

INSERT INTO "student" ("name") VALUES('\'');

正确:需要加上E转义

INSERT INTO "student" ("name") VALUES(E'\'');