PostgreSQL

一、基本使用

1. 交互式终端psql

• 连接至数据库： psql -h <ip地址> -p <端口号>
• \d：查看所有表
• \d + 表名：查看表结构
• \timing：显示SQL语句执行时间

2. 表空间的使用

如果需要把不同的表放在不同的存储介质或不同的文件系统下，可以使用表空间

`CREATE TABLESPACE tablespace_name LOCATION 'directory'
[WITH (tablespace_option = value)]`

在创建数据库、表时可以指定表空间，以便将对象存储在特定的表空间上

表空间参数 tablespace_option ：如果在比 I/O子系统更快或更慢的磁盘上存储表空间，则配置项会变得很有意义

• seq_page_cost：在磁盘上顺序扫描获取一个页面的代价，默认1.0
• random_page_cost：在磁盘上随机扫描获取一个页面的代价，默认4.0
• effective_io_concurrency：可以执行并发异步磁盘 I/O 的数量

3. 创建数据库

CREATE DATABASE name
[ENCODING[=]encoding]
[TABLESPACE[=]tablespace_name]
[CONNECTION LIMIT[=]connlimit]

ENCODING：编码

TABLESPACE：表空间

3. 操作数据库表

创建数据库：

CREATE [[GLOBAL|LOCAL]{TEMPORARY|TEMP}|UNLOGGED] TABLE
[IF NOT EXIST] table_name([
{ column_name data_type [COLLATE collation][column_constraint
[...]]
|table_constraint
|LIKE source_table [like_option...]}
])
[INHERITS(parent_table[,...])]
[PARTITION BY{RANGE|LIST}({column_name|(expression)}
[COLLATE collation][opclass][,...])]
[WITH(storage_parameter[=value][,...])|WITH OIDS|WITHOUT OIDS]
[ON COMMIT{PERSERVE ROWS|DELETE ROWS|DROP}]
[TABLESPACE tablespace_name]

字段约束：

• 非空约束  NOT NULL
• 唯一约束  UNIQUE
• 主键约束  PRIMARY KEY
• 默认约束  DEFAULT
• 外键约束：`deptid   int   not null   REFERENCES department(deptid)`
• Check约束：createtime  timestamp CHECK(createtime > '1970-01-01- 00:00:00')

表约束：

• 外键约束：FOREIGN KEY (b,c) REFERENCE tb_test01 (b1,c1)
• Check约束：CHECK(createtime < modifytime)

删除数据库表：

删除表时如果该表被其他表引用（外键），直接删除会报错，需添加 CASCADE 连带删除子表的外键约束 ： `DROP TABLE employee CASCADE`

二、数据类型和运算符

1.数据类型

• 整型：smallint、integer、bigint
• 任意精度：numeric、decimal 
  decimal(6,2)：表示总共位数6位，小数部分位数2位。如果插入的数字小数位数超过2，则自动进行四舍五入
• 浮点：real 6位精度、 double precision 15位精度、float(n) 指定精度。浮点类型会造成精度丢失，需要准确计算应该使用numeric类型。浮点类型特殊值（Infinity、-Infinity、NaN）
• 序列：smallserial、serial、bigserial 可用作自增主键
• 货币：money 可以接受 ‘￥10000.00’货币格式，货币符号由数据库 lc_monetary 参数决定
• 字符串：char(n)、varchar(n)、text 。n表示最多存储n个字符，text无长度限制
• 二进制：bytea
• 日期时间：
  timestamp [with time zone]:8位日期时间
  date:4位日期
  time[with time zone]:时间
  interval [fields]：时间间隔
  系统内部所有与时区相关的日期时间存的都是UTC时间，可以使用timezone参数指定本地时间