PostgreSQL（二）基本语法

概述数据库相关： shell 符号：>pg 符号：=##创建数据库，切换系统账号> su - postgres> createdb testdb> dropdb testdb#或者连接到数据库创建> psql> psql testdb> psql -h localhost -p 5432 -U postgres testdbtestdb=#CREATE DATABASE

数据库相关：

shell 符号：>pg 符号：=##创建数据库，切换系统账号> su - postgres> createdb testdb> dropdb testdb#或者连接到数据库创建> psql> psql testdb> psql -h localhost -p 5432 -U postgres testdbtestdb=#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=# \ltestdb=# 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=# \dttestdb=# 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.88,'2016-02-22'),('San Abama',22,0.02,'2010-08-11');SELECT * FROM weather;# 查看表定义testdb=# \d weatherSELECT a.attnum,a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,b.descriptionFROM pg_class cINNER JOIN pg_attribute a ON a.attrelID = c.oID and a.attnum > 0INNER JOIN pg_type t ON a.atttypID = t.oIDleft JOIN pg_description b ON a.attrelID=b.objoID AND a.attnum = b.obJsubIDWHERE 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=# \dbtestdb=# SELECT * FROM pg_tablespace;# 创建表空间> mkdir -p /usr/local/pgsql/data/ts_user01> chown postgres:root /usr/local/pgsql/data/ts_user01testdb=# 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_seqSTART WITH 1INCREMENT BY 1NO MINVALUENO MAXVALUECACHE 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);# 删除SEQUENCEDROP 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 总结

以上是内存溢出为你收集整理的PostgreSQL（二）基本语法全部内容，希望文章能够帮你解决PostgreSQL（二）基本语法所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。