当前位置: 代码迷 >> 综合 >> PostgreSQL(PGsql)数据库简单使用
  详细解决方案

PostgreSQL(PGsql)数据库简单使用

热度:0   发布时间:2023-12-27 15:40:30.0

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) ;
  相关解决方案