postgresql上手

安装

在ubuntu下安装

sodu apt-get install postgresql

在mac下安装

brew install postgresql

终端打印：

2021-04-13
==> postgresql
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
  https://www.postgresql.org/docs/13/app-initdb.html

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start

安装完成后在终端执行postgresql -V 会返回版本号，另外可以执行卸载 brew remove postgresql 然后重新重新安装。默认的端口号是5432。

启动服务

开机自启动：brew services start postgresql

手动开启：pg_ctl -D /usr/local/var/postgres start

添加新用户和数据库

初次安装后，默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是，同时还生成了一个名为postgres的Linux系统用户。

进入pg命令行模式：：psql [databaseName] ，databaseName如果不传会默认取当前用户名。由于当前已经默认生成了一个名为postgres的数据库，所以可以用指令 psql postgres 进行登录。

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
// -U指定用户，-d指定数据库，-h指定服务器，-p指定端口
// psql -U name, 指定用户名和数据库名为同一个name并连接

如果要配合已经现有项目使用，需要根据项目配置创建数据库和用户，举例某一个项目的database.yml为：

development:
  adapter:  postgresql
  host:     localhost
  encoding: unicode
  database: tiange_development
  pool:     5
  username: postgres
  password: postgres
  template: template0

test:
  adapter:  postgresql
  host:     localhost
  encoding: unicode
  database: tiange_test
  pool:     5
  username: postgres
  password: postgres
  template: template0

production:
  adapter:  postgresql
  host:     localhost
  encoding: unicode
  database: tiange_production
  pool:     50
  username: postgres
  password: postgres
  template: template0

项目配置的用户是postgres，数据库名称tiange_development，密码是postgres。执行psql postgres进入命令行模式，继续 按以下的步骤进行。

修改用户密码

\password postgres

如果提示 role "postgres" does not exit... 可能是因为postgres这个用户还不是超级用户,可以执行

CREATE USER postgres SUPERUSER;
// 返回 CREATE ROLE

再次执行修改密码的指令即可。

创建数据库

CREATE DATABASE tiange_development OWNER postgres;

创建tiange_development数据库，并且指定所有者为用户postgres

赋权

GRANT ALL PRIVILEGES ON DATABASE tiange_development to postgres;

这里将tiange_development数据库的所有权限都赋予postgres，否则postgres只能登录控制台，没有任何数据库操作权限

最后执行\q退出数据库控制台。

控制台常用指令

\h：查看SQL命令的解释，比如\h select。
?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

数据库常用操作

# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

CREATE TABLE users(
  name TEXT NOT NULL CHECK(CHAR_LENGTH(name) > 4 && CHAR_LENGTH(name) < 15),
  age INT CHECK(age >= 0) DEFAULT 18
);
// 创建一张新表，包含name和age，name不能为空，长度大于4小于16，age为整型，可不传，默认18

CREATE TYPE mode AS ENUM('pending','inprogress','finished');
CREATE TABLE order(
  state mode
);
// 创建一张表，state只能取枚举类型mode中的一个

# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

# 选择记录
SELECT * FROM user_tbl;

# 更新数据
UPDATE user_tbl set name = '李四' WHERE name = '张三';

# 删除记录
DELETE FROM user_tbl WHERE name = '李四' ;

# 添加栏位
ALTER TABLE user_tbl ADD email VARCHAR(40);

# 更新结构
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
ALTER TABLE user_tbl ALTER COLUMN signup_date TYPE char(32);

# 更名栏位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

# 删除栏位
ALTER TABLE user_tbl DROP COLUMN email;

# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;

# 删除表格
DROP TABLE IF EXISTS backup_tbl;
DROP TABLE user_tbl;

常用数据类型

• char
• varchar
• SMALLINT/INT/BIGINT/DECIMAL 分别对应2/4字节整型数字
• TEXT
• NUMERIC 浮点数
• DOUBLE PRECISION 双精度浮点数
• BYTEA
• DATE
• INTERVAL
• BOOLEAN
• ENUM