【随用随查】PostgreSQL 实践笔记

本文记录了 PostgreSQL 基本操作，入门的 SQL 语句以及高阶的联表查询等内容。本文自用，时常更新。PostgreSQL 和 MySQL 的 SQL 语句基本相同，应该选择哪个？可以参考文末的扩展阅读。

PostgreSQL轻松学

w3schoolSQL教程

PostgreSQL 英文文档

极客学院SQL教程

macOS

macOS 上可以使用 brew 安装

brew install postgres

Ubuntu

sudo apt install postgresql-11

sudo systemctl status postgresql

sudo -i -u postgres
psql

Mac 下使用 homebrew 安装 postgresql 及配置

mac 启动 postgreSQL 服务

pg_ctl -D /usr/local/var/postgres start

or

# 服务在后台运行
brew services start postgresql

连接远程数据库

psql -h IP或者主机地址 -p 端口 -U 用户 -d 数据库

psql -h 117.12.116.18 -p 5432 -U web -d test

控制台命令

\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构(包含索引)。
\du：列出所有用户。
\q：退出数据库。
\conninfo：列出当前数据库和连接的信息。

# 创建数据库
CREATE DATABASE name;
# 创建新表 
CREATE TABLE users (name VARCHAR(20), signup_date DATE);
# 插入数据 
INSERT INTO users(name, signup_date) VALUES('小猿大圣', '2017-01-15');
# 选择记录 
SELECT * FROM users;
# 更新数据 
UPDATE users set name = '小猿大圣' WHERE name = '小猿大圣';
# 删除记录 
DELETE FROM users WHERE name = '小猿大圣' ;
# 添加栏位 
ALTER TABLE users ADD email VARCHAR(40);
# 更新结构 
ALTER TABLE users ALTER COLUMN signup_date SET NOT NULL;
# 更名栏位 
ALTER TABLE users RENAME COLUMN signup_date TO signup;
# 删除栏位 
ALTER TABLE users DROP COLUMN email;
# 删除表格 
DROP TABLE backups;
# 删除数据，速度快，会保留表结构
TRUNCATE TABLE tablename;
# 删除数据以及外键
TRUNCATE TABLE tablename CASCADE;
# 求平均值
SELECT AVG(helper) FROM games;
# 求最大值
SELECT MAX(helper_num) FROM games;
# 求数量不重复
SELECT COUNT(DISTINCT open_id) FROM games;
# 求和
SELECT SUM(amount) FROM games; 
# 模糊查询
SELECT * FROM users WHERE name LIKE '小猿大%';
# 排序 ASC 升序 默认，DESC 降序
SELECT c1, c2 FROM tbl_name ORDER BY c1 ASC, c2 DESC;

# 查出表中有重复的 id 的记录，并计算相同 id 的数量
SELECT user_id,COUNT(user_id) FROM games GROUP BY user_id HAVING (COUNT(user_id)>1)
# 合集 合并 Helpers 表和 Games 表 的 user_id 字段 (ALL 是不去重)
SELECT user_id FROM games UNION ALL SELECT assistance_user_id FROM helpers;
# 统计上表中的字段重复的次数
SELECT count,COUNT(count) FROM (SELECT user_id,COUNT(user_id) FROM (SELECT user_id FROM games UNION ALL SELECT assistance_user_id FROM helpers ) AS foo;

如何从 csv 文件更新数据到数据库？ 点击我

如果将查询结果导出到 csv 文件

\copy (select distinct helpers.open_id from helpers left join users on helpers.open_id = users.open_id  where users.open_id is null union select open_id from users where id in (select user_id from exchanges)) TO '/tmp/rm_openid.csv' (format CSV);

\copy (select union_id  From users where open_id is null) TO '/tmp/filename.csv' (format CSV);

数据库备份

备份数据库数据

pg_dump --host 地址 --port 端口 --username 数据库的用户名 > 导出的文件 数据库名字

例子:

pg_dump --host xxxxx.com --port 3434 --username admin  > file.sql dataname

数据导入数据库

psql -d 数据库名字 -f 文件名 用户名

例子:

psql -d dataname -f file.sql admin

用户、角色、权限管理

PostgreSQL学习笔记(九) 用户、角色、权限管理

创建用户并赋予权限

CREATE USER www WITH PASSWORD 'u867#eDgg6#2@elo098OIUh';

CREATE DATABASE my_app_prod OWNER www;

alter role www login createdb;

1、macOS postgres 角色不存在

** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification): role "postgres" does not exist

解决

psql -d postgres

CREATE DATABASE postgres WITH OWNER postgres;

CREATE USER postgres SUPERUSER;

2、升级版本后，数据库无法启动

报错如下：

could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

FATAL:  database files are incompatible with server

brew postgresql-upgrade-database

MySQL与PostgreSQL：该选择哪个开源数据库？哪一个更好？

为什么选择PostgreSQL而不是MySQL