postgresql的理论知识

一、Linux下centos7版本安装pgsql14

1.安装rpm文件

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2.安装PostgreSQL

sudo yum -y install postgresql14-server

3.初始化数据库

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

4.启动和开机启动设置

sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

5.设置数据库密码（用来远程连接）

yum安装pgsql数据库会自动生成一个为postgres用户此用户为超级管理员。

使用postgres用户登录

su - postgres

使用用户登录两种方式

指定用户登录 #psql -U postgres
默认用户登录 #psql  默认用户是 postgres

设置已有用户的密码（生产环境下需要设置复杂）

alter user postgres with password '123456' 

退出登录

\q 退出主数据库 #或者 快捷键ctrl+d
exit #退出pg账号也是可以用以上的快捷键

7.开启远程登录

对所有ip开放

打开postgresql.conf文件
#vim /var/lib/pgsql/14/data/postgersql.conf
修改listen_address配置对所有IP开放访问
listen_address = '*'#（此配置可以设置想开放的IP段如192.168.0.0/0）

虽然此配置已经开放了所有IP的访问权限但是需要配置信任连接才可以连接数据库。

增加信任连接

打开pg_hba.conf文件
vim /var/lib/pgsql/14/data/pg_hba.conf

在# IPv4 local connections: #此数据行下添加
此配置项 #host    all             all             0.0.0.0/0               trust
其中（trust）此项配置是免除网段内远程连接使用密码
配置完成后重启服务
systemctl restart postgresql-14

8.添加防火墙放行端口

firewall-cmd --zone=public --add-port=5432/tcp --permanent
重启防火墙
firewall-cmd --reload

以上是postgreSQL的yum安装和放行远程连接的过程。

二，postgreSQL基本语句使用及解释

SELECT查询、检索语句

此语句可从单个或多个表中检索数据，经常使用

单个表检索数据：

语法：

第一种

SELECT
 user_id
FROM
 table_name 
 [other_clauses];

第二种

SELECT user_id FROM table_name [other_clauses];

以上两种并没有区别，但是本人觉得第一种SQL语句更清晰一点，但是本人习惯使用第二种。

此语法中，SELECT 和 FROM 是关键字，意思是选择和从。

其中， user_id 表明要查找那个字段，而 table_name 是从那个数据表中查找。

字段可以自己定义查找的范围，还有全表扫描**（但是不建议使用）** 如下：

SELECT user_id,name,age FROM table_name [other_clauses];
以下全表扫描
SELECT * FROM table_name [other_clauses];

而 [other_clauses] 是表示SELECT子句的占位符，子句相当于附加条件。

以下是子句的解释：

DISTINCT 运算符用于从结果集中选择不重复的行。

ORDER BY 子句用于对查询结果集中的行进行排序。

WHERE 子句用于根据指定的条件过滤行。

LIMIT和FETCH 子句用于从查询结果中选择一定数量（或百分比）的行比如前几行后几行。

GROUP BY 可以根据一个或多个列的值将查询结果划分为多个组。

HAVING 用于在GROUP BY子句之后对分组进行过滤。就是把过滤出的分组按要求在进行过滤。

使用诸如 INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN 之类的与其他表连接。

使用 UNION， INTERSECT 和 EXCEPT执行集合运算。其中的大致意思是

UNION 操作会合并两个集合，并去除重复的元素，返回一个包含两个集合所有不重复元素的新集合。

INTERSECT 操作会返回两个集合中共有的元素，即返回一个包含两个集合共有元素的新集合。

EXCEPT 操作会从第一个集合中移除与第二个集合中相同的元素，返回一个包含第一个集合中不属于第二个集合的元素的新集合。

详细的子句使用方法将在之后提及。

2.1pgsql SELECT语句实际使用

单列查询

SELECT user_id FROM table_name;

 user_id 
---------
       1
       2
       3
       4
       5
       6
(6 行记录)

多列查询

 SELECT user_id,name,age,locked,  created_at  FROM table_name;
  user_id | name  | age | locked |         created_at         
---------+-------+-----+--------+----------------------------
       1 | huaji |  18 | t      | 2023-07-14 14:28:41.628464
       2 | huaj  |  18 | t      | 2023-07-14 14:29:04.38336
       3 | huaj2 |  18 | t      | 2023-07-14 14:29:23.121366
       4 | huaj2 |  18 | t      | 2023-07-14 14:29:42.075722
       5 | huaj3 |  18 | t      | 2023-07-14 14:29:54.351195
       6 | huaj6 |  18 | f      | 2023-07-14 14:35:31.500747
(6 行记录)