【随用随查】PostgreSQL 实践笔记

# 创建数据库
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;

\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

psql -d postgres

CREATE DATABASE postgres WITH OWNER postgres;

CREATE USER postgres SUPERUSER;