[每周一更]-(第86期):PostgreSQL入门学习和对比MySQL

在这里插入图片描述

入门学习PostgreSQL可以遵循以下步骤：

安装 PostgreSQL：
- 首先，你需要在你的计算机上安装 PostgreSQL。你可以从 PostgreSQL 官方网站下载适合你操作系统的安装包，并按照官方文档的指导进行安装。
学习 SQL：
- PostgreSQL 是一个关系型数据库管理系统，支持 SQL 查询语言。学习 SQL 是使用 PostgreSQL 的基础。你可以通过在线教程、书籍或者免费的在线资源学习 SQL。
PostgreSQL 文档：
- 阅读 PostgreSQL 官方文档是学习 PostgreSQL 的重要途径。它提供了详细的信息和示例，帮助你了解 PostgreSQL 的各个方面。
使用 psql 命令行工具：
- PostgreSQL 提供了一个命令行工具叫做 psql，它是一个交互式的终端，允许你连接到 PostgreSQL 数据库并执行 SQL 查询。通过在终端中输入 psql，你可以进入 psql 界面，然后使用 \connect 命令连接到数据库。
创建数据库和表：
- 通过使用 SQL 语句，在 PostgreSQL 中创建数据库和表格。这将帮助你理解如何设计和管理数据库结构。
使用图形用户界面 (GUI) 工具：
- 除了命令行工具，你还可以使用图形用户界面工具来管理 PostgreSQL 数据库，例如 pgAdmin。这些工具提供了更直观的界面，使得数据库的管理更加容易。
参与社区：
- 加入 PostgreSQL 的社区，参与讨论、阅读邮件列表、参加会议等。这样你可以获取实际应用中的经验，了解最佳实践和解决问题的方法。
实践项目：
- 通过实际项目应用你的知识，这是学习任何技术最有效的方法。尝试设计和实现一个小型的数据库应用，这将帮助你巩固所学的知识。
持续学习：
- PostgreSQL 是一个强大而复杂的数据库系统，不断地学习并保持对最新版本的了解是非常重要的。参与培训、阅读博客、关注社区更新是不断提升你的技能的好途径。

总体而言，深入学习 PostgreSQL 需要时间和实践。通过不断地使用和探索，你将更熟悉这个数据库管理系统的各个方面。

使用用法

PostgreSQL 是一个功能强大且开源的关系型数据库管理系统 (RDBMS)。以下是一些基本的 PostgreSQL 用法和常见任务：

连接到 PostgreSQL 数据库：
- 命令行工具连接到 PostgreSQL 数据库。例如：
```
psql -h localhost -U username -d dbname
```
其中，localhost 是数据库服务器的地址，username 是你的数据库用户名，dbname 是你连接的数据库名称。

查看数据库列表等命令

（1）使用 \l 用于查看已经存在的数据库，如下：

mydb-# \l
                                                   List of databases
   Name    | Owner | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules | Access privileges
-----------+-------+----------+-----------------+------------+------------+------------+-----------+-------------------
 mydb      | admin | UTF8     | libc            | en_US.utf8 | en_US.utf8 |   |           |
 postgres  | admin | UTF8     | libc            | en_US.utf8 | en_US.utf8 |   |           |
 template0 | admin | UTF8     | libc            | en_US.utf8 | en_US.utf8 |   |           | =c/admin         +
           |       |          |                 |            |            |   |           | admin=CTc/admin
 template1 | admin | UTF8     | libc            | en_US.utf8 | en_US.utf8 |   |           | =c/admin         +
           |       |          |                 |            |            |   |           | admin=CTc/admin
(4 rows)

（2）使用 \c + 数据库名 来进入数据库，如下：

mydb-# \c mydb
You are now connected to database "mydb" as user "admin".

（3）查看某个数据库下所有数据表
\dt 用于列出当前连接的数据库中的所有数据表
系统自带的表，该命令查不到；自行创建的可以查阅；

创建数据库：
- 使用 SQL 语句创建数据库：
```
CREATE DATABASE yourdatabase;
```

创建表：

使用 SQL 语句在数据库中创建表格：


CREATE TABLE yourtable (
    column1 datatype1,
    column2 datatype2,
    ...
);

# 栗子
CREATE TABLE company(
   id INT PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   age            INT     NOT NULL,
   address        CHAR(50),
   salary         REAL
);

插入数据：

语句将数据插入表格中：


INSERT INTO yourtable (column1, column2, ...)
VALUES (value1, value2, ...);

# 栗子； 文本类型需要用单引号
INSERT INTO company  VALUES (1,'胡',31,'许昌市',1000);

查询数据：

语句从表格中检索数据：


SELECT column1, column2, ...
FROM yourtable
WHERE condition;

# 栗子
select * from company;
 id | name | age |                        address                        | salary
----+------+-----+-------------------------------------------------------+--------
  1 | 胡   |  31 | 许昌市                                                |   1000

更新数据：

语句更新表格中的数据：


UPDATE yourtable
SET column1 = value1, column2 = value2, ...
WHERE condition;

# 栗子
UPDATE company SET salary = 2000 WHERE id = 1;

mydb=# select * from company;
 id | name | age |                        address                        | salary
----+------+-----+-------------------------------------------------------+--------
  1 | 胡   |  31 | 许昌市                                                |   2000
(1 row)

删除数据：
- 语句删除表格中的数据：
```
DELETE FROM yourtable
WHERE condition;
```

索引：

为表格中的列创建索引，以提高检索性能。例如：


CREATE INDEX index_name ON yourtable (column1, column2, ...);

# 栗子
CREATE INDEX index_name ON company (name);
\di ： 查看数据库的索引；
\dv ： 查看视图；

备份和还原：

命令备份数据库：


pg_dump -h localhost -U username -d dbname > backup.sql

命令还原数据库：


psql -h localhost -U username -d dbname < backup.sql

数值类型

PGSQL跟MySQL数据类型还是存在差异；创建表时候要重新整理；

名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

用户管理：

创建新用户：


CREATE USER username WITH PASSWORD 'password';

赋予用户权限：


GRANT permission ON yourtable TO username;

这只是 PostgreSQL 的一些基础用法。要深入了解和学习更多高级特性，你可能需要阅读官方文档，参与社区，以及进行更复杂的实际项目。

对比MySQL

PostgreSQL 和 MySQL 是两种流行的开源关系型数据库管理系统（RDBMS），它们在一些方面有相似之处，但也有一些关键的区别。以下是它们的应用场景和一些主要区别：

PostgreSQL 的应用场景：

复杂查询和大数据量： PostgreSQL 在处理复杂查询和大数据量时表现出色，它支持更复杂的查询语句和具有较好的性能。
GIS（地理信息系统）应用： PostgreSQL 具有强大的地理和空间数据支持，适用于需要处理地理信息的应用，如地理信息系统（GIS）。
JSON 数据存储： PostgreSQL 支持原生的 JSON 数据类型和 JSONB 存储格式，使其更适用于需要处理 JSON 数据的场景。
扩展性： PostgreSQL 是一个高度可扩展的数据库系统，支持水平和垂直扩展，适用于需要处理不断增长数据量的应用。
并发控制和事务管理： PostgreSQL 提供强大的并发控制和事务管理功能，适用于需要高并发和数据完整性的应用场景。

MySQL 的应用场景：

Web 应用程序： MySQL 在处理简单的读写操作时表现良好，适用于许多 Web 应用程序和小型网站。
事务处理： MySQL 在事务处理方面表现出色，适用于需要处理大量事务的应用，如电子商务系统。
简单查询和读写性能： MySQL 在处理较为简单的查询和读写性能方面通常较为出色，适用于轻负载的应用。
存储引擎选择： MySQL 支持多种存储引擎，可以根据具体需求选择合适的引擎，例如 InnoDB 用于事务处理，MyISAM 用于读密集型应用。
社区和生态系统： MySQL 拥有庞大的用户社区和丰富的生态系统，广泛用于各种规模的项目。

主要区别：

数据类型支持： PostgreSQL 提供更丰富的数据类型，包括数组、JSON、HSTORE 等，而 MySQL 对数据类型的支持相对较少。
存储过程和触发器： PostgreSQL 的存储过程和触发器支持更丰富，而 MySQL 的支持相对较简单。
并发度和事务处理： PostgreSQL 在并发度和事务处理方面通常更强大，适用于高并发和复杂事务场景。
性能优化： MySQL 在一些简单查询和读写性能方面可能更优，但 PostgreSQL 在复杂查询和大数据量方面通常表现更好。
许可协议： PostgreSQL 使用 PostgreSQL 许可证，而 MySQL 使用 GNU 通用公共许可证（GPL），在商业使用上有一些差异。

选择使用 PostgreSQL 还是 MySQL 取决于具体的项目需求、性能要求和数据特性。在某些情况下，两者都可以胜任，而在其他情况下，一个可能更适合特定的用例。