PostgreSQL基本语法

简介

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。
PostgreSQL 开发者把它念作 post-gress-Q-L。
PostgreSQL 的 Slogan 是 “世界上最先进的开源关系型数据库”。

创建数据库

1. 删除数据库

DROP DATABASE IF EXISTS myDatabase;

2. 创建数据库

CREATE DATABASE myDatabase
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

COMMENT ON DATABASE myDatabase
    IS '测试数据库';

创建模式

1. 删除模式

DROP SCHEMA IF EXISTS myschema cascade;

2. 创建模式

CREATE SCHEMA myschema
    AUTHORIZATION postgres;

COMMENT ON SCHEMA myschema
    IS '测试模式';

GRANT ALL ON SCHEMA myschema TO postgres;

GRANT ALL ON SCHEMA myschema TO PUBLIC;

创建表

1. 删除表

DROP TABLE IF EXISTS myschema.mytable;

2. 创建表

CREATE TABLE myschema.mytable
(
    id integer NOT NULL,
    name character varying(32) COLLATE pg_catalog."default" NOT NULL,
    gender character(1) COLLATE pg_catalog."default",
    age integer,
    CONSTRAINT mytable_pkey PRIMARY KEY (id),
    CONSTRAINT mytable_name_key UNIQUE (name)
,
    CONSTRAINT mytable_age_check CHECK (age > 0)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE myschema.mytable
    OWNER to postgres;
COMMENT ON TABLE myschema.mytable
    IS '测试表';

3. 清空表

TRUNCATE TABLE myschema.mytable;

增删改查

1. 查询数据

SELECT id, name, gender, age
	FROM myschema.mytable
	WHERE <condition>;

2. 插入数据

INSERT INTO myschema.mytable(
	id, name, gender, age)
	VALUES (?, ?, ?, ?);

3. 更新数据

UPDATE myschema.mytable
	SET id=?, name=?, gender=?, age=?
	WHERE <condition>;

4. 删除数据

DELETE FROM myschema.mytable
	WHERE <condition>;

查询小技巧

1. 查询表名、表注释

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_%' 
		AND relname LIKE <tablename> AND obj_description(relfilenode,'pg_class') LIKE <table_description>
	ORDER BY relname;

2. 查询字段注释

SELECT description 
	FROM pg_description JOIN pg_class ON pg_description.objoid = pg_class.oid 
	WHERE relname = <tablename> AND description LIKE <column_description>;

3. 查询字段名、字段注释

SELECT col_description(a.attrelid,a.attnum) AS comment,format_type(a.atttypid,a.atttypmod) AS type,a.attname AS name, a.attnotnull AS notnull   
	FROM pg_class AS c,pg_attribute AS a 
	WHERE a.attrelid = c.oid AND a.attnum > 0 AND c.relname = <tablename> AND col_description(a.attrelid,a.attnum) LIKE <column_description>;

后续

以后有时间将继续补充。。。。。。