记录：359
场景：PostgreSQL数据库常用操作，创建用户、数据库、表、主键、索引、注释等。配置远程可连接、配置环境变量、常用命令行操作。表的增删改查等操作。
版本：
操作系统：CentOS 7.9
PostgreSQL版本：PostgreSQL 13.4
PostgreSQL帮助手册：
官网入口：https://www.postgresql.org/
文档入口：https://www.postgresql.org/docs
文档内容列表：https://www.postgresql.org/docs/current/
支持的命令列表：https://www.postgresql.org/docs/current/reference.html
psql命令行列表：https://www.postgresql.org/docs/current/app-psql.html
1.PostgreSQL数据库
1.1PostgreSQL
PostgreSQL: The World’s Most Advanced Open Source Relational Database.
PostgreSQL is a powerful, open source object-relational database systemthat has earned it a strong reputation for reliability, feature robustness, and performance.
解析：PostgreSQL是一个先进的开源关系型数据库。在可靠性、健壮性和性能方面赢得很高声誉。
1.2PostgreSQL历史版本下载
(1)下载地址
官网地址：https://ftp.postgresql.org/pub/source/
解析：下载需求的各版本移步此网站。
(2)命令下载
命令下载：wget https://ftp.postgresql.org/pub/source/v13.4/postgresql-13.4.tar.gz –no-check-certificate
(3)页面下载
浏览器登录到下载页面，选择对应版本。
2.安装PostgreSQL
注意：从1.3步骤下载的包是源码包，需要编译后才能安装使用。PostgreSQL启动运行一般使用Linux操作系统的普通用户。
(1)解压
解压命令：tar -xzvf /home/apps/software/postgresql-13.4.tar.gz -C /home/apps/module
(2)编译和安装
操作目录：cd /home/apps/module/postgresql-13.4
编译命令：make -C /home/apps/module/postgresql-13.4
安装命令：make install PREFIX=/home/apps/module/postgresql
(3)数据目录
创建命令：mkdir -p /home/data/postgresql/data
(4)创建Linux普通用户
创建Linux用户：useradd postgres
(5)PostgreSQL安装目录和数据目录赋权给普通用户
PostgreSQL安装目录和数据目录，所有权修改为postgres用户。
安装目录赋权：chown -R postgres:postgres /home/apps/module/postgresql/
数据目录赋权：chown -R postgres:postgres /home/data/postgresql/data/
3.普通用户postgres切换
切换到普通用户：su postgres
退出普通用户：exit postgres
解析：postgres用户做所有关于PostgreSQL的操作包括启动、运行、登录数据库等操作。
4.环境变量配置
(1)配置环境变量
修改命令：vi /home/postgres/.bash_profile
修改内容：
PGHOME=/home/apps/module/postgresqlexport PGHOMEPGDATA=/home/data/postgresql/dataexport PGDATAPATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin(2)环境变量生效
命令：source /home/postgres/.bash_profile
(3)环境变量开机启动生效配置
修改指令：vi /etc/profile
增加内容：source /home/postgres/.bash_profile
5.初始化PostgreSQL
命令：initdb
6.启动、重启和停止
启动命令：pg_ctl -D /home/data/postgresql/data -l /home/data/postgresql/data/postgres.log start
重启命令：pg_ctl -D /home/data/postgresql/data -l /home/data/postgresql/data/postgres.log restart
停止命令：pg_ctl stop
解析：-D，指定数据库数据存储目录；-l，指定日志输出目录。
7.登录PostgreSQL
本机登录不指定用户名和数据库名称，实际就是登录postgres，在postgres用户超级用户(Superuser)。
注意：首次登录是不需要密码，换句话说，默认命名是空的，因此，首次登录后需要修改密码。
7.1登录超级用户数据库postgres
(1)首次登录
命令：psql
(2)修改密码后登录
命令：psql
解析：需要输入密码。提示：Password for user postgres: 。
7.2登录普通用户数据库
登录命令：psql -U hangzhou -d hzdb
解析：psql，使用命令行登录；-U，指定用户；-d，指定数据库。
8.修改登录密码
修改命令：\password
口令：demo123456
解析：需输入两次；\是PostgreSQL的命令语法。
9.退出登录
退出登录的PostgreSQL。
命令：\q
解析：\是PostgreSQL的命令语法。
10.设置远程可登录
(1)修改远程可登录
修改文件：vi /home/data/postgresql/data/postgresql.conf
修改内容：
listen_addresses = ‘*’
解析：把#listen_addresses = ‘localhost’，修改为匹配所有IP地址。
(2)修改登录需要密码
修改文件：vi /home/data/postgresql/data/pg_hba.conf
修改内容：
local allall passwordhostallall127.0.0.1/32 passwordhostallall192.168.19.1/32passwordhostallall::1/128password解析：192.168.19.1/32，部署PostgreSQL主机是192.168.19.166。
(3)可以使用navicat等客户端登录
11.常用命令行
常用命令行实际上就是使用psql登录数据库后，支持的命令行和SQL语句。
注意：
PostgreSQL的普通命令行使用反斜杆\开头，结尾无标识符。例如：
命令：\du
PostgreSQL的SQL语句命令行以分号结尾。例如：
命令：create databasehzdb;
官网地址：https://www.postgresql.org/docs/current/app-psql.html
(1)登录数据库
登录：psql
解析：默认登录超级用户。
登录：psql -U hangzhou -d hzdb
解析：psql，使用命令行登录；-U，指定用户；-d，指定数据库。
(2)查看数据库用户列表
命令：\du
(3)查看数据库列表
命令：\l
(4)切换数据库
命令格式：\c 数据库名称
命令：\c hzdb
解析：切换到hzdb数据库。
(5)列举数据库中表清单
命令：\dt
(6)查看表结构
命令：\d 表名称
命令：\d t_city
解析：\d查看表结构，包括表字段名称、类型、主键、索引等。
(7)修改密码
命令：\password
解析：修改当前用户登录密码。
(8)退出登录
命令：\q
解析：退出psql程序，也就是退出登录。
(9)查看命令帮助文档
命令：\h
解析：显示数据库支持的全部SQL语句的命令，比如SELECT、CREATE TABLE等。
命令：\h CREATE TABLE
解析：查看CREATE TABLE语句的使用方法。
(10)列出转换类型
命令：\dC
12.创建和删除数据库用户
(1)创建数据库用户
命令：create user hangzhou with password ‘demo123456’;
(2)删除数据库用户
命令：drop user hangzhou;
(3)查看数据库用户
命令：\du
解析：查看数据库中用户角色，以及角色拥有的权限
(4)使用普通用户登录。
主机名：192.168.19.166
端口：5432
数据库：hzdb
用户名：hangzhou
密码：demo@123456
13.创建和删除数据库
(1)创建数据库
命令：create databasehzdb;
命令：create databasehzdb owner hangzhou;
(2)删除数据库
命令：dropdatabasehzdb;
(3)查看数据库列表
命令：\l
解析：查看数据库列表，数据库隶属用户，数据库编码，数据库字符集。
14.创建表(CREATE TABLE)
(1)建表
建表语句：
CREATE TABLE t_city (ID BIGINT NOT NULL,CITY_NAME VARCHAR (64),LAND_AREA FLOAT8 DEFAULT NULL,POPULATION BIGINT DEFAULT NULL,GROSS FLOAT8 DEFAULT NULL,CITY_DESCRIBE VARCHAR (512) DEFAULT NULL,DATA_YEAR VARCHAR (16) DEFAULT NULL,UPDATE_TIME TIMESTAMP DEFAULT NULL);(2)添加表名称和字段注释
-- 表名称注释COMMENT ON TABLE t_city IS '城市信息表';-- 字段注释COMMENT ON COLUMN t_city.ID IS '主键';COMMENT ON COLUMN t_city.CITY_NAME IS '城市名';COMMENT ON COLUMN t_city.LAND_AREA IS '城市面积';COMMENT ON COLUMN t_city.POPULATION IS '城市人口';COMMENT ON COLUMN t_city.GROSS IS '生产总值';COMMENT ON COLUMN t_city.CITY_DESCRIBE IS '城市描述';COMMENT ON COLUMN t_city.DATA_YEAR IS '数据年份';COMMENT ON COLUMN t_city.UPDATE_TIME IS '更新时间';(3)创建主键
-- 主键约束ALTER TABLE t_city ADD CONSTRAINT t_city_id_pk PRIMARY KEY (ID);(4)创建索引
-- 索引CREATE INDEX idx_city_name ON t_city (CITY_NAME);15.表应用
15.1增(INSERT INTO)
INSERT INTO t_city (ID,CITY_NAME,LAND_AREA,POPULATION,GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIME)VALUES('1','杭州','16850','1200','1.61','杭州是一个好城市','2020','2022-12-10 15:39:32'),('2','杭州','16850','1200','1.81','杭州是一个好城市','2021','2022-12-10 15:39:33');15.2查(SELECT)
SELECTID,CITY_NAME,LAND_AREA,POPULATION,GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIMEFROM t_city;15.3改(UPDATE)
UPDATEt_citySETCITY_DESCRIBE = '杭州是互联网城市'WHERE CITY_NAME = '杭州'AND ID = '1';15.4删(DELETE)
DELETE FROM t_city WHERE CITY_NAME = '杭州' AND ID = '1';16.删除表(DROP TABLE)
SQL语句：drop table t_city;
17.超级用户postgres的表
(1)查看系统表清单(表: pg_tables)
SQL: select * from pg_tables;
(2)查看数据库用户(表: pg_authid)
SQL: select * from pg_authid;
(3)查看已建数据库(表: pg_database)
SQL: select * from pg_database;
(4)查看分区表(表: pg_partitioned_table)
SQL: select * from pg_partitioned_table;
(5)查看序列(表: pg_sequence)
SQL: select * from pg_sequence;
18.导出建表语句
(1)登录部署数据库主机
命令：su postgres
(2)导出指定数据库
命令：pg_dump -U hangzhou -d hzdb -s > allTable.sql
导出的表结构：
---- Name: t_city; Type: TABLE; Schema: public; Owner: hangzhou--CREATE TABLE public.t_city (id bigint NOT NULL,city_name character varying(64),land_area double precision,population bigint,gross double precision,city_describe character varying(512) DEFAULT NULL::character varying,data_year character varying(16) DEFAULT NULL::character varying,update_time timestamp without time zone);ALTER TABLE public.t_city OWNER TO hangzhou;---- Name: TABLE t_city; Type: COMMENT; Schema: public; Owner: hangzhou--COMMENT ON TABLE public.t_city IS '城市信息表';---- Name: COLUMN t_city.id; Type: COMMENT; Schema: public; Owner: hangzhou--COMMENT ON COLUMN public.t_city.id IS '主键';---- Name: COLUMN t_city.city_name; Type: COMMENT; Schema: public; Owner: hangzhou--COMMENT ON COLUMN public.t_city.city_name IS '城市名';---- Name: COLUMN t_city.land_area; Type: COMMENT; Schema: public; Owner: hangzhou--COMMENT ON COLUMN public.t_city.land_area IS '城市面积';---- Name: COLUMN t_city.population; Type: COMMENT; Schema: public; Owner: hangzhou--COMMENT ON COLUMN public.t_city.population IS '城市人口';---- Name: COLUMN t_city.gross; Type: COMMENT; Schema: public; Owner: hangzhou--COMMENT ON COLUMN public.t_city.gross IS '生产总值';---- Name: COLUMN t_city.city_describe; Type: COMMENT; Schema: public; Owner: hangzhou--COMMENT ON COLUMN public.t_city.city_describe IS '城市描述';---- Name: COLUMN t_city.data_year; Type: COMMENT; Schema: public; Owner: hangzhou--COMMENT ON COLUMN public.t_city.data_year IS '数据年份';---- Name: COLUMN t_city.update_time; Type: COMMENT; Schema: public; Owner: hangzhou--COMMENT ON COLUMN public.t_city.update_time IS '更新时间';---- Name: t_city t_city_id_pk; Type: CONSTRAINT; Schema: public; Owner: hangzhou--ALTER TABLE ONLY public.t_cityADD CONSTRAINT t_city_id_pk PRIMARY KEY (id);---- Name: idx_city_name; Type: INDEX; Schema: public; Owner: hangzhou--CREATE INDEX idx_city_name ON public.t_city USING btree (city_name);以上，感谢。
2022年12月10日