简介
数据库排名：https://db-engines.com/en/ranking
官网：https://www.postgresql.org/
中文社区文档
菜鸟教程
易百教程
中文社区
mac 安装 brew install postgresql
启动服务：pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
mac安装postgresql后不会创建用户名数据库，执行命令：createdb
1
2
3
4
5
6
7
8
9
10
11
12
13 | ➜ ~ psql --version
psql (PostgreSQL) 11.3
➜ ~ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+------------+----------+---------+-------+---------------------------
nocilantro | nocilantro | UTF8 | C | C |
postgres | nocilantro | UTF8 | C | C |
template0 | nocilantro | UTF8 | C | C | =c/nocilantro +
| | | | | nocilantro=CTc/nocilantro
template1 | nocilantro | UTF8 | C | C | =c/nocilantro +
| | | | | nocilantro=CTc/nocilantro
(4 rows)
|
1
2
3
4
5
6
7
8
9
10
11
12
13 | ➜ ~ createdb test
➜ ~ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+------------+----------+---------+-------+---------------------------
nocilantro | nocilantro | UTF8 | C | C |
postgres | nocilantro | UTF8 | C | C |
template0 | nocilantro | UTF8 | C | C | =c/nocilantro +
| | | | | nocilantro=CTc/nocilantro
template1 | nocilantro | UTF8 | C | C | =c/nocilantro +
| | | | | nocilantro=CTc/nocilantro
test | nocilantro | UTF8 | C | C |
(5 rows)
|
登录PostgreSQL控制台：psql
| nocilantro=# select now();
now
-------------------------------
2019-09-08 13:44:33.554029+08
(1 row)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 | ➜ ~ psql test
psql (11.3)
Type "help" for help.
test=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+------------+----------+---------+-------+---------------------------
nocilantro | nocilantro | UTF8 | C | C |
postgres | nocilantro | UTF8 | C | C |
template0 | nocilantro | UTF8 | C | C | =c/nocilantro +
| | | | | nocilantro=CTc/nocilantro
template1 | nocilantro | UTF8 | C | C | =c/nocilantro +
| | | | | nocilantro=CTc/nocilantro
test | nocilantro | UTF8 | C | C |
(5 rows)
test=# \q
➜ ~
➜ ~ psql test
psql (11.3)
Type "help" for help.
test=# select now();
now
-------------------------------
2019-09-08 13:49:09.210043+08
(1 row)
|
1
2
3
4
5
6
7
8
9
10
11
12 | ➜ ~ dropdb test
➜ ~ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+------------+----------+---------+-------+---------------------------
nocilantro | nocilantro | UTF8 | C | C |
postgres | nocilantro | UTF8 | C | C |
template0 | nocilantro | UTF8 | C | C | =c/nocilantro +
| | | | | nocilantro=CTc/nocilantro
template1 | nocilantro | UTF8 | C | C | =c/nocilantro +
| | | | | nocilantro=CTc/nocilantro
(4 rows)
|
操作数据表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41 | test=# create table table1 (title varchar(255), content text);
CREATE TABLE
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+------------
public | table1 | table | nocilantro
(1 row)
test=# \d table1
Table "public.table1"
Column | Type | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
title | character varying(255) | | |
content | text | | |
test=# alter table table1 rename to table2;
ALTER TABLE
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+------------
public | table2 | table | nocilantro
(1 row)
test=# drop table table2;
DROP TABLE
test=# \dt
Did not find any relations.
➜ ~ cat db.sql
create table table1 (title varchar(255), content text);
test=# \i db.sql
CREATE TABLE
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+------------
public | table1 | table | nocilantro
(1 row)
|
字段类型
数值型：integer(int), real, serial
文字型: char, varchar, text
布尔型: boolean
日期型: date, time, timestamp
特色类型: Array, 网络地址型(inet), JSON型, XML 型
修改密码
输入 \password，然后再输入两次密码即可
| nocilantro=# \password
Enter new password:
Enter it again:
|
常用操作
查看表索引: select * from pg_indexes where tablename='test_table';
创建索引: create index name_index on test_table (name);
查看数据库中所有表名: select tablename from pg_tables where tablename not like 'pg%' and tablename not like 'sql_%';
查看某个表中的字段名: select column_name from information_schema.columns where table_schema='public' and table_name='test_table'