PostgreSQL（二）基本语法

数据库相关：

shell 符号：>
pg 符号：=#


#创建数据库，切换系统账号
> su - postgres
> createdb testdb
> dropdb testdb

#或者连接到数据库创建
> psql
> psql testdb
> psql -h localhost -p 5432 -U postgres testdb
testdb=#

CREATE DATABASE test
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'zh_CN.UTF-8'
    LC_CTYPE = 'zh_CN.UTF-8'
    TABLESPACE = pg_default;
	
testdb=# DROP DATABASE test;
testdb=# DROP DATABASE if exists test;

#查看数据库属性
testdb=# \l
testdb=# SELECT * FROM pg_database; 

# 切换数据库
testdb=# \c test;

# 查看当前连接(数据库存在连接时无法删除)
testdb=# SELECT * FROM pg_stat_activity;
testdb=# SELECT datid,datname,pid,usesysid,usename,application_name FROM pg_stat_activity WHERE datname='aaa';

> pg_ctl kill TERM PID

# 或者调用 pg_terminate_backend 直接删除
testdb=# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='test' AND pid<>pg_backend_pid(); 


# 更改数据库名称
testdb=# UPDATE pg_database SET datname = 'demo' where datname = 'test'; 
testdb=# ALTER DATABASE demo RENAME TO demodb; 

# 更改数据库其他配置（字符集更改需重建数据库）
testdb=# ALTER DATABASE demodb OWNER TO pg_monitor;
testdb=# ALTER DATABASE demodb SET TABLESPACE pg_default;

# 查看数据库大小
testdb=# select pg_size_pretty(pg_database_size('testdb'));
testdb=# select datname, pg_database_size(datname)/1024/1024 AS sizeMB from pg_database; 

表对象相关：

# 数据类型参考：
https://www.postgresql.org/docs/current/static/datatype.html

# 查看数据库所有表
testdb=# \dt
testdb=# SELECT * FROM pg_tables WHERE schemaname='public';

# 创建表
testdb=# 
CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

CREATE TABLE cities (
    name            varchar(80),
    location        point
)TABLESPACE pg_default;
	
# 删除表
DROP TABLE cities;

# 插入数据
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

INSERT INTO weather VALUES 
('Piter', 0, 50, 0.88, '2016-02-22'),
('San Abama', 50, 22, 0.02, '2010-08-11');

SELECT * FROM weather;

# 查看表定义
testdb=# \d weather

SELECT a.attnum,a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,b.description
FROM pg_class c
INNER JOIN pg_attribute a ON a.attrelid = c.oid and a.attnum > 0
INNER JOIN pg_type t ON a.atttypid = t.oid
LEFT JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid
WHERE c.relname = 'weather'
ORDER BY a.attnum;

# 更改表所有者
ALTER TABLE public.cities OWNER to postgres;

# 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('weather'));

SELECT tablename
,pg_size_pretty(pg_total_relation_size(cast(tablename as varchar(300))))
FROM pg_tables WHERE schemaname='public';

# 视图（TEMPORARY 为会话级别）
CREATE VIEW vweather AS SELECT * FROM weather;
CREATE TEMPORARY VIEW vweather2 AS SELECT * FROM weather;

SELECT * FROM pg_views WHERE viewname LIKE 'vweather%';

DROP VIEW vweather;
DROP VIEW vweather2;

模式：

# 创建模式(与 sql server 一样的意思)
testdb=# CREATE SCHEMA kkschema;
testdb=#
CREATE TABLE kkschema.cities (
    name            varchar(80),
    location        point
)TABLESPACE pg_default;

testdb=# SELECT * FROM testdb.kkschema.cities;

testdb=# SELECT * FROM pg_tables WHERE schemaname in('public','kkschema');

# 删除模式 与 强制删除模式
testdb=# DROP SCHEMA kkschema;
testdb=# DROP SCHEMA kkschema CASCADE;


# 其他：SQL语法都一样。创建表时创建的索引语法与mysql一样，其他都差不多。
# 其他：PG 的表连接方式和分析函数与 ORACLE、MSSQL 一样，SQL标准，都比mysql多。

# 分页查询
testdb=# SELECT * FROM public.weather LIMIT 1 OFFSET 2 ;

表空间：

testdb=# \db
testdb=# SELECT * FROM pg_tablespace;

# 创建表空间
> mkdir -p /usr/local/pgsql/data/ts_user01
> chown postgres:root /usr/local/pgsql/data/ts_user01

testdb=# CREATE TABLESPACE ts_user01 location '/usr/local/pgsql/data/ts_user01';

#更改表所在表空间
testdb=# ALTER TABLE public.cities SET TABLESPACE ts_user01;
testdb=# \d public.cities

# 删除表空间
testdb=# DROP TABLESPACE ts_user01;

# 表空间下的所有表对象
testdb=# SELECT * FROM pg_tables WHERE tablespace='ts_user01';

# 查看表空间大小
testdb=# SELECT pg_size_pretty(pg_tablespace_size('ts_user01'));
testdb=# SELECT spcname,pg_size_pretty(pg_tablespace_size(spcname)) AS size FROM pg_tablespace;

序列：

# 序列号，与 ORACLE、MSSQL 一样
testdb=# 
CREATE SEQUENCE public.id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

testdb=# SELECT currval('id_seq');
testdb=# SELECT nextval('id_seq');

#查看定义
testdb=# \d public.id_seq

# 更改初始值
testdb=# ALTER SEQUENCE public.id_seq RESTART WITH 100;
testdb=# SELECT setval('id_seq', 10);

# 删除SEQUENCE
DROP SEQUENCE IF EXISTS public.id_seq;

临时表：

# 临时表（TEMPORARY 为会话级别）
# CREATE TEMP TABLE tbl_name ON COMMIT {PRESERVE ROWS|DELETE ROWS|DROP};
# PRESERVE ROWS：默认值，事务提交后保留临时表和数据
# DELETE ROWS：事务提交后删除数据，保留临时表
# DROP：事务提交后删除表

BEGIN;
CREATE TEMP TABLE tab1(col int) ON COMMIT PRESERVE ROWS;
INSERT INTO tab1(col) VALUES(100);
COMMIT;
SELECT * FROM tab1;

BEGIN;
CREATE TEMP TABLE tab2(col int) ON COMMIT DELETE ROWS;
INSERT INTO tab2(col) VALUES(100);
COMMIT;
SELECT * FROM tab2;

BEGIN;
CREATE TEMP TABLE tab3(col int) ON COMMIT DROP;
INSERT INTO tab3(col) VALUES(100);
COMMIT;
SELECT * FROM tab3;

DROP TABLE tab1;
DROP TABLE tab2;

# UNLOGGED 表：不记录日志，相当于临时表，但写入快。看起来与普通表一样。

CREATE UNLOGGED TABLE tab4(col int);

INSERT INTO tab4 SELECT generate_series(1,10000);

SELECT COUNT(*) FROM tab4;

DROP TABLE tab4;

# 函数  https://www.postgresql.org/docs/10/static/sql-createfunction.html