sql索引创建与使用 sql建索引语句

背景

pgsql常见DDL语句，怎么建主键，怎么建索引，建表时自动建sequence，查询某个表是否存在，某个表是否存在某个字段，某个索引是否存在，查主键的名字

查 “是否存在”

• 获得当前数据库(catalog)和schema

--使用这两个函数
select current_database();
select current_schema();

• 查schema下有什么表

--查询当前schema下有什么表（方法一）
select * from pg_tables where schemaname=current_schema();

-- 查询当前schema下有什么表（方法二）
SELECT *
FROM information_schema.tables
WHERE table_catalog=current_database() and table_schema = current_schema();

-- 判断表是否存在(方法一)
select tablename from pg_tables where schemaname=current_schema() and tablename='communication'

-- 判断表是否存在(方法二)
SELECT table_name
FROM information_schema.tables
WHERE table_catalog=current_database() and table_schema=current_schema() and table_name='communication'

• 查表有什么字段

-- 查询表有什么列（字段信息，含类型等等）
SELECT *
FROM information_schema.columns
WHERE table_catalog=current_database() and table_schema = current_schema() and table_name = 'communication';

-- 判断某列是否存在
SELECT column_name
FROM information_schema.columns
WHERE table_catalog=current_database() and table_schema = current_schema() and table_name = 'communication'and column_name='dr';

• 查表有什么索引

--查询表有什么索引
SELECT *
FROM pg_indexes
WHERE schemaname = current_schema() AND tablename = 'communication';

--判断索引是否存在
SELECT indexname
FROM pg_indexes
WHERE schemaname = current_schema() AND tablename = 'communication' AND
indexname = 'idx_table_name';

• 查主键的名字（含是否存在主键）

--判断主键名字是什么，查无记录表示无主键（改一下relname的值，contype不需要改，p表示主键）
SELECT
    pg_constraint.conname AS pk_name
FROM
    pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
WHERE
    pg_class.relname = 'my_table'
AND pg_constraint.contype = 'p';

DDL

• 建表、删表 DDL（含自动创建sequence）

--建表（幂等）【不指定主键的名字】
--【主键默认是 "表名_pkey"，即 my_table_pkey】
--【serial或者serial8分别表示int4和int8的，这种写法会自动建sequence，且名字是 "表名_id_seq"，删除表的时候会级联删除该序列】
CREATE TABLE IF NOT EXISTS "my_table"
(
  "id"               SERIAL       NOT NULL,
  "file_id"          varchar(255) NOT NULL,
  "file_name"        varchar(255) NOT NULL,
  "dr"               int2 DEFAULT 0,
  PRIMARY KEY ("id")
);

--建表（幂等）【指定主键的名字】
CREATE TABLE IF NOT EXISTS "my_table2"
(
  "id"               SERIAL       NOT NULL,
  "file_id"          varchar(255) NOT NULL,
  "file_name"        varchar(255) NOT NULL,
  "dr"               int2 DEFAULT 0,
  CONSTRAINT "custom_name_of_my_table2" PRIMARY KEY ("id")
);

--删表（幂等）
drop table if exists "data_oss_upload";

• 列（增删改列）

--新增列（无法重复执行，无法判断是否已经存在）
alter table "communication" add COLUMN table_name varchar(512);

--删除列（无法重复执行，无法判断字段是否已存在）
alter table "communication" drop column dr;


--修改列类型和精度（可重复执行，只能修改列类型）
alter table "communication" alter column table_name type varchar(500);

--修改列名（不可重复执行，修改列名要用这个）
alter table "communication" rename column table_name to table_name2;

--列名和类型一起修改，结合用上面2句即可，貌似没一句话干两样的

• 注释

--对表注释(可重复执行，但需要表存在)
COMMENT ON TABLE "communication" IS '通信表';

--对列注释（可重复执行，但需要表、列存在）
COMMENT ON COLUMN "communication"."id" IS '自增主键';

• 索引

--建普通索引（幂等）
CREATE INDEX IF NOT EXISTS "idx_table_name" ON "communication" USING btree (
  "table_name" COLLATE "pg_catalog"."default" "pg_catalog"."bpchar_ops" ASC NULLS LAST
);

--建唯一索引（幂等）
create unique index IF NOT EXISTS data_app_api_app_id_api_no on data_app_api (app_id,api_no);

--删除索引（幂等）
drop INDEX IF EXISTS "idx_table_name";

--修改索引：先删后增即可

• 约束（注意别把 CONSTRAINT 写成复数）
  感觉有部分的约束其实是可以使用index来表示的，使用index以便实现幂等

--新增唯一约束（应该就是唯一索引的另外一种写法）
ALTER TABLE "communication" ADD CONSTRAINT "idx_communication_only" UNIQUE ("ds_id", "table_name", "tenant_id");
--貌似等价于
create unique index IF NOT EXISTS "idx_communication_only" on "communication" ("ds_id", "table_name", "tenant_id");

--新增普通约束（非唯一，也相当于索引）
ALTER TABLE "communication" ADD CONSTRAINT "idx_communication_only"("ds_id", "table_name", "tenant_id");

• 主键

• 表已经建好了，如何新增、修改、删除主键

--新增主键。不指定主键的名字，默认是"表名_pkey"，即 my_table_pkey 【不指定主键名字】
ALTER TABLE "my_table" ADD PRIMARY KEY(id,minute);

--新增主键。指定主键的名字（注意不要写错 ADD CONSTRAINTS）【指定主键名字】
ALTER TABLE "my_table" ADD CONSTRAINT custom_name_of_my_table6666 PRIMARY KEY (id);

• 新建表的时候顺带建主键

--创建表的顺带创建主键【不指定主键的名字，默认是 "表名_pkey"，即 my_table_pkey】
CREATE TABLE IF NOT EXISTS "my_table"
(
  "id"               SERIAL       NOT NULL,
  "file_id"          varchar(255) NOT NULL,
  "file_name"        varchar(255) NOT NULL,
  "dr"               int2 DEFAULT 0,
  PRIMARY KEY ("id")
);

--创建表的顺带创建主键  【指定主键的名字】
CREATE TABLE IF NOT EXISTS "my_table2"
(
  "id"               SERIAL       NOT NULL,
  "file_id"          varchar(255) NOT NULL,
  "file_name"        varchar(255) NOT NULL,
  "dr"               int2 DEFAULT 0,
  CONSTRAINT "custom_name_of_my_table2" PRIMARY KEY ("id")
);

• 删除主键（不知道主键名删不了）

--删除主键【知道名字的情况下】
ALTER TABLE "my_table" DROP CONSTRAINT IF EXISTS my_cons;

--主键名的情况下似乎删不了（可以查主键叫什么名）

• 创建序列（sequence）

--创建序列（幂等）【指定从某个值开始】
CREATE SEQUENCE IF NOT EXISTS api_no START 10000;