1.创建表
--判断表是否存在，不存在创建表
DROP TABLE IF EXISTS eform_ceshi_text;
CREATE TABLE eform_ceshi_text (ID VARCHAR ( 50 ) NOT NULL,createTime TIMESTAMP ( 6 ) NOT NULL,modifiedTime TIMESTAMP ( 6 ) NOT NULL,createId VARCHAR ( 50 ) NOT NULL,updateId VARCHAR ( 50 ) NOT NULLaaa int4
);
2.添加、删除主键
--添加主键
ALTER TABLE eform_ceshi_text ADD CONSTRAINT eform_text_pkey PRIMARY KEY (id);
--删除主键
ALTER TABLE eform_ceshi_text DROP CONSTRAINT "eform_text_pkey";
3.批量添加、删除字段
--批量添加字段
ALTER TABLE eform_ceshi_text ADD COLUMN name character varying not null, ADD COLUMN age int4 not null, ADD COLUMN birth character varying not null, ADD COLUMN test character varying not null;
--删除表字段
alter table eform_ceshi_text drop test;
4.创建、删除索引
--创建索引
create index ix_age on eform_ceshi_text(age);
--删除索引
DROP INDEX ix_age;
5.添加、查询表注释
--添加表注释
comment on table eform_ceshi_text is '测试表';
--添加表字段注释(comment on column 【表名.字段名】 is 【注释】;)
COMMENT ON COLUMN eform_ceshi_text.age IS '年龄';
--查询字段注释，objsubid为表中字段的序号，从左侧从1开始
select description from pg_description join pg_class on pg_description.objoid = pg_class.oid where relname = '年龄';
--查询表名、表注释
select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname;
6.修改字段类型、字段名、表名
--修改字段类型(如：ID 字段 原类型为 character varying(50) 新类型为integer)
--alter table 【表名】 alter column 【字段名】 type 【字段类型】 using 【字段新类型】;
alter table eform_ceshi_text alter column birth type integer using to_number(birth,'9');
alter table eform_ceshi_text alter COLUMN name type varchar(20);
ALTER TABLE eform_ceshi_text alter COLUMN createId type VARCHAR(100);
--修改字段名
alter table eform_ceshi_text rename name to uname;
--修改表名
alter table eform_ceshi_text rename to eform_ceshi_text2;
7.查询视图、表、存储过程
-----查询视图、表、存储过程------------------------
select table_name from information_schema.tables where table_schema = 'public' and table_type = 'VIEW'; --视图
select table_name from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE';--表
SELECT ROUTINE_NAME FROM information_schema.Routines WHERE ROUTINE_TYPE = 'PROCEDURE';--存储过程
8.查询数据库表字段、类型、长度
--查询数据库表字段、类型、长度
select column_name as Name,udt_name as Type,CHARACTER_MAXIMUM_LENGTH as Length,1 as Exist from information_schema.columns where table_schema='public' and table_name= 'eform_ceshi_text';
9.查询表是否存在
--查询表是否存在
select table_name from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE' and table_name= 'eform_ceshi_text';
select count(*) from pg_class where relname = 'eform_ceshi_text';
10.查询视图列、视图是否存在
--查询视图列
select column_name as Name,udt_name as Type,CHARACTER_MAXIMUM_LENGTH as Length from information_schema.columns where table_schema='public' and table_name='sys_grouplist';
--查询视图是否存在
select table_name from information_schema.tables where table_schema = 'public' and table_type = 'VIEW' and table_name = 'sys_grouplist';
11.查询存储过程是否存在、存储过程参数
--查询存储过程是否存在
SELECT ROUTINE_NAME FROM information_schema.Routines WHERE ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_NAME = 'transfer'
--查询存储过程参数
SELECT CASE WHEN pg_proc.proretset THEN 'setof ' || pg_catalog.format_type(pg_proc.prorettype, NULL) ELSE pg_catalog.format_type(pg_proc.prorettype, NULL) END, pg_proc.proargtypes, pg_proc.proargnames, pg_proc.prosrc, pg_proc.proallargtypes, pg_proc.proargmodes, pg_language.lanname
FROM pg_catalog.pg_proc JOIN pg_catalog.pg_namespace ON (pg_proc.pronamespace = pg_namespace.oid) JOIN pg_catalog.pg_language ON (pg_proc.prolang = pg_language.oid)
where pg_catalog.pg_proc.proname ='transfer' or pg_catalog.pg_proc.proname ='transfer2' ;
12.查询表字段是否存在、表是否存在指定索引查询视图
--根据表字段是否存在
SELECT column_name as columnName,udt_name as dataType FROM information_schema.columns WHERE TABLE_NAME ='eform_ceshi_text' and column_name = 'createid';
--查询表是否存在指定索引
select 1 from pg_class t,pg_class i where t.relkind = 'r' and t.relname = 'eform_ceshi_text' and i.relname = 'ix_age';
13.if判断语句
--执行IF判断语句格式do $$ DECLARE var1 text; var2 int4;[...] BEGIN--IF语句IF NOT EXISTS(select 1 from pg_class t,pg_class i where t.relkind = 'r' and t.relname = 'eform_ceshi_text' and i.relname = 'ix_age') THENcreate index ix_age on eform_ceshi_text(age);END IF;END;$$
14.方法/函数用法
--方法/函数用法：CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $variable_name$ DECLARE var1 text; var2 int4;[...] BEGIN < function_body > [...] RETURN { variable_name | value } END; LANGUAGE plpgsql;]-- 调用方法select function_name (arguments) ;