PostgreSQL 使用方法(二)

1 数据库管理

1.1 建立数据库

CREATE DATABASE database_name OWNER username;

1.2 删除数据库

DROP DATABASE database_name;

1.3 更改数据库

1.3.1 更改数据库名字

ALTER DATABASE database_name RENAME TO new_name;

1.3.2 更改数据库拥有者

ALTER DATABASE database_name OWNER TO new_owner;

1.4 查看所有数据库

\l

1.5 切换数据库

\c database_name

2 表的管理

2.1 建立表

CREATE TABLE table_name (id varchar(10) PRIMARY KEY NOT NULL, data json NOT NULL);

创建多个 PRIMARY KEY 的表:
CREATE TABLE table_name (id varchar(10) NOT NULL, data json NOT NULL, other text NOT NULL, PRIMARY KEY (id, data)) ;

2.2 删除表

DROP TABLE table_name;

2.3 更改表

2.3.1 添加关键字

ALTER TABLE table_name ADD COLUMN new_key varchar(40);

2.3.2 关键字更名

ALTER TABLE table_name RENAME COLUMN old_key TO new_key;

2.3.3 关键字类型更改

ALTER TABLE table_name ALTER COLUMN key TYPE varchar(40);

2.3.4 关键字增加/去除 NULL

ALTER TABLE table_name ALTER COLUMN key SET/DROP NOT NULL;

2.3.5 设置某几个关键字为 PRIMARY KEY

ALTER TABLE table_name ADD PRIMARY KEY (key1, key2, ...);

2.3.6 删除关键字

ALTER TABLE table_name DROP COLUMN key;

2.3.7 表名更改

ALTER TABLE old_table_name RENAME TO new_table_name;

2.4 查看当前数据库的所有表

\dt

2.5 查看表结构

\d table_name

类似于 mysql 中的
DESCRIBE table_name;

3 常用数据类型

3.1 int

长度为 4 bytes。

3.1 varchar(n)

是一个可变长度的字符串,不过最大字符个数为 n。比如:
'hello world'

3.2 text

是一个可变长度的字符串,没有长度限制。比如:
'hello world'

3.3 json

它实质上是一个字符串,其内部是一个列表,或者字符串,或者整数,或者字典,与 python3 中的基本数据类型有一一对应的关系。比如:

  • python3 中的
    1
    对应于 json 为:
    '1'
  • python3 中的
    'hello world'
    对应于 json 为:
    '"hello world"'
  • python3 中的
    [1, 2, 3]
    对应于 json 为:
    '[1, 2, 3]'
  • python3 中的
    {'name': 'nju', 'district': 'nanjing'}
    对应于 json 为:
    '{"name": "nju", "district": "nanjing"}'
  • python3 中的
    None
    对应于 json 为:
    'null'
  • python3 中的
    True
    对应于 json 为:
    'true'

3.4 array

对应 Python 中的 list 类型。

对于一维数组,声明为 type[],比如 json[];对于二维数组,声明为 type[][],比如 text[][]。

其值有两种表示:

  • '{1, 2, 3}'
  • ARRAY[1, 2, 3]

对于 json[],其值的书写比较特殊。比如,两个 json 放在一个数组里,每个 json 中的内容为对应 python3 中的 dict 类型,此时则需要 escape 单引号,写为:
'{\'{"name": "nju", "district": "nanjing"}\', \'{"name": "pku", "district": "beijing"}\'}'
或者
ARRAY['{"name": "nju", "district": "nanjing"}', '{"name": "pku", "district": "beijing"}']::json[]
后者用 :: 将 ARRAY[…] 强制转换为 json[] 类型。

3.6 date

对应 Python 中的 datetime.date 类型。

3.7 time

对应 Python 中的 datetime.time 类型。

3.8 timestamp

对应 Python 中的 datetime.datetime 类型。

3.9 NULL

是一个常量。

4 表内容的管理

4.1 查看表的所有内容

SELECT * FROM table_name;
或者
copy table_name to stdout;

4.2 查看表中记录的个数

SELECT COUNT(*) FROM table_name

4.3 将记录插入到表中

INSERT INTO table_name (id, data) VALUES('123', '{"name": "NJU", "district": "Nanjing"}');

如果有一列的数据不存在,则可以添加 NULL

4.4 更新表中内容

UPDATE table_name SET name = "NJU", location = "JiangSu" WHERE id = 1

4.5 删除表中记录

清空表:
DELETE FROM table_name;

删除某行记录:
DELETE FROM table_name WHERE id = 1 and location = "Beijing";

5 导入与导出

5.1 COPY 与 /copy

两者用法相同。

区别在于:

  • 前者不仅可以在 psql 中使用,也可以在 shell 中使用;而后者只能在 psql 中使用。
  • 前者只能通过超级用户使用;后者可以通过所有用户使用。

6 json 和 jsonb 的联系与区别