PostgreSQL学习笔记（8）之常用SQL命令

CREATE DATABASE A;

--普通表
CREATE TABLE TAB_TABLEDUMP (ID INT );
INSERT INTO TAB_TABLEDUMP VALUES (1);
INSERT INTO TAB_TABLEDUMP VALUES (2);
INSERT INTO TAB_TABLEDUMP VALUES (3);

CREATE INDEX DUMP_INDEX ON TAB_TABLEDUMP(ID);

--主键约束
CREATE TABLE DUMP_PRIMARY (ID INT PRIMARY KEY);
INSERT INTO DUMP_PRIMARY VALUES(1);

--非空约束
CREATE TABLE DUMP_NOTNULL (ID INT NOT NULL);
INSERT INTO DUMP_NOTNULL VALUES(1);

--唯一键约束
CREATE TABLE DUMP_UNIQUE (ID INT UNIQUE);
INSERT INTO DUMP_UNIQUE VALUES(1);

--检查约束
CREATE TABLE DUMP_CHECK(ID INT CHECK(ID >0));
INSERT INTO DUMP_CHECK VALUES(1);

--外键约束
CREATE TABLE DUMP_FOREIGN (COL INT REFERENCES DUMP_PRIMARY(ID));
INSERT INTO DUMP_FOREIGN VALUES(1);

--表依赖于表
CREATE TABLE NEW_TABLE AS SELECT * FROM DUMP_PRIMARY;
INSERT into new_table VALUES(2);

--视图依赖表
CREATE VIEW VIEW_DUMP AS SELECT * FROM TAB_TABLEDUMP;

--物化视图依赖表
CREATE MATERIALIZED VIEW VIEW_METER AS SELECT * FROM TAB_TABLEDUMP;

--创建依赖表和视图
CREATE TABLE DUMP_STUDENT (id int primary key,name varchar(50));
CREATE TABLE DUMP_STUDENTLOG (op_time timestamp,db_user varchar(40),op_type varchar(20));
CREATE VIEW VIEW_DUMP_STUDENT AS SELECT * FROM DUMP_STUDENT;

--函数（函数依赖表）
CREATE OR REPLACE FUNCTION DUMP_FUNCTION()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO DUMP_STUDENTLOG VALUES(now(), user, TG_OP);
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

--语句级触发器
CREATE TRIGGER STATEMENT_TRIGGER 
AFTER INSERT OR DELETE OR UPDATE ON VIEW_DUMP_STUDENT
FOR STATEMENT EXECUTE PROCEDURE DUMP_FUNCTION();


INSERT INTO VIEW_DUMP_STUDENT VALUES(1,'April'),(2,'Harris');

--行级触发器
CREATE TRIGGER ROW_TRIGGER 
AFTER INSERT OR DELETE OR UPDATE ON DUMP_STUDENT
FOR EACH ROW EXECUTE PROCEDURE DUMP_FUNCTION();


INSERT INTO DUMP_STUDENT VALUES(3,'Mary'),(4,'Bob');

--存储过程
CREATE OR REPLACE PROCEDURE DUMP_PROCEDURE ()
AS $$
BEGIN
INSERT INTO TAB_TABLEDUMP VALUES(4);
END;
$$ LANGUAGE plpgsql;
CALL DUMP_PROCEDURE()

create user A with password '1234';
alter user A with createdb; 
GRANT SELECT ON TAB_TABLEDUMP TO A;
CREATE TABLE TEST_REVOKE(A INT);
INSERT INTO TEST_REVOKE VALUES(1);
REVOKE SELECT on TEST_REVOKE from "postgres";

select current_database();

show data_directory;

select * from pg_tablespace;

\dnS
select * from information_schema.schemata;
SELECT nspname FROM pg_namespace;

\dt
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;

\d tablename
select * from information_schema.columns where table_schema='public' and table_name='XX';

\di
select * from pg_index;  #包含关于索引的一部分信息

\dv
select * from pg_views where schemaname = 'public';
select * from information_schema.views where table_schema = 'public';

select * from information_schema.triggers;

select * from information_schema.sequences where sequence_schema = 'public';

select * from pg_constraint where contype = 'p'
select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'cc';