PG数据库

PostgreSQL 数据库日常应用一、连接与基础命令1. 命令行连接bash运行# 本地 psql -U postgres -d dbname # 远程 psql -h 192.168.x.x -p 5432 -U postgres -d dbname2. psql 常用元命令\l列出所有数据库\c dbname切换数据库\dt查看当前库所有表\d tablename查看表结构\du查看所有用户 / 角色\q退出\?帮助二、库与表操作1. 数据库sqlCREATE DATABASE testdb; DROP DATABASE IF EXISTS testdb; -- 查看库大小 SELECT pg_size_pretty(pg_database_size(testdb));2. 建表常用模板sqlCREATE TABLE t_user ( id SERIAL PRIMARY KEY, name VARCHAR(32) NOT NULL, phone VARCHAR(11), create_time TIMESTAMP DEFAULT now() ); -- 注释 COMMENT ON TABLE t_user IS 用户表; COMMENT ON COLUMN t_user.name IS 姓名;3. 表结构修改sqlALTER TABLE t_user ADD COLUMN age INT; ALTER TABLE t_user RENAME COLUMN age TO user_age; ALTER TABLE t_user ALTER COLUMN phone SET NOT NULL; DROP TABLE IF EXISTS t_user;三、数据 CRUD1. 插入sqlINSERT INTO t_user(name, phone) VALUES (张三, 13800138000); -- 批量插入 INSERT INTO t_user(name) VALUES (李四),(王五); -- 存在则更新UPSERT INSERT INTO t_user(id,name) VALUES (1,张三) ON CONFLICT (id) DO UPDATE SET nameEXCLUDED.name;2. 查询sqlSELECT * FROM t_user WHERE id1; -- 分页 SELECT * FROM t_user LIMIT 10 OFFSET 0; -- 模糊不区分大小写 SELECT * FROM t_user WHERE name ILIKE %张%; -- 聚合 SELECT count(*) FROM t_user;3. 更新 删除sqlUPDATE t_user SET phone13900001111 WHERE id1; DELETE FROM t_user WHERE id1; TRUNCATE TABLE t_user; -- 清空表快不可回滚四、用户与权限1. 创建用户sqlCREATE USER app_user WITH LOGIN PASSWORD 123456;2. 只读权限sqlGRANT CONNECT ON DATABASE testdb TO readonly_user; GRANT USAGE ON SCHEMA public TO readonly_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user; -- 未来新建表自动授权 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;3. 读写权限sqlGRANT SELECT,INSERT,UPDATE,DELETE ON t_user TO app_user; REVOKE DELETE ON t_user FROM app_user;五、索引与常用维护1. 建索引sqlCREATE INDEX idx_user_phone ON t_user(phone);2. 查看执行计划sqlEXPLAIN ANALYZE SELECT * FROM t_user WHERE phone13800138000;3. 表统计信息 / 清理sqlANALYZE t_user; -- 更新统计信息 VACUUM ANALYZE t_user;-- 清理死元组分析4. 查看表大小sqlSELECT pg_size_pretty(pg_relation_size(t_user));六、备份与恢复1. 备份pg_dumpbash运行pg_dump -U postgres -d testdb -f testdb.sql2. 恢复bash运行psql -U postgres -d testdb -f testdb.sql七、常用系统查询sql-- 查看连接数 SELECT count(*) FROM pg_stat_activity; -- 查看慢查询/活跃SQL SELECT query,state,pid FROM pg_stat_activity WHERE state!idle; -- 杀进程 SELECT pg_terminate_backend(pid);