PostgreSQL

PostgreSQL相关知识

PostgreSQL 语法 | 菜鸟教程

PostgreSQL与MySQL的比较

PostgresQL相对于MySQL的优势
1)在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨。
2)对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强。
3)PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
4)PG的主备复制属于物理复制，相对于MvSQlL基于binloq的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小,5)PostgreSQL支持JSON和其他NOSQL功能，如本机XL支持和使用HSTORE的键值对。它还支持索引SON数据以加快访问速度，特别是10版本JSONB更是强大
6)PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。相反，MySQL现在主要是被Oracle公司控制。
总结：
从应用场景来说，PG更加适合严格的企业应用场景(比如金融、电信、ERP、CRM)，但不仅仅限制于此，PostgreSQl的ison，isonb，hstore等数据格式，特别适用于一些大数据格式的分析;而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景(比如google、facebook、albaba)，当然现在MySQL的在innodb引擎的大力发展，功能表现良好。

l 熟悉PG库sql编写

创建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL
);

插入数据

INSERT INTO users (username, email, password) VALUES ('user1', 'user1@example.com', 'password1');
INSERT INTO users (username, email, password) VALUES ('user2', 'user2@example.com', 'password2');

查询数据

SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;

更新数据

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

删除数据

DELETE FROM users WHERE id = 1;

条件查询

SELECT * FROM users WHERE username LIKE 'user%';

创建索引

CREATE INDEX idx_username ON users(username);

删除表

DROP TABLE users;

l 知晓联表查询（左联接、右联接、内联接）

左连接
左联接会返回左边表的所有行，以及右边表中与左表匹配的行，如果右边表中没有匹配的行，则返回 NULL 值。

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

右连接
右联接会返回右边表的所有行，以及左边表中与右表匹配的行，如果左边表中没有匹配的行，则返回 NULL 值。

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

内连接
内联接会返回两个表中匹配的行，如果没有匹配的行，则不返回任何结果。

SELECT *
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

内联接会返回两个表中匹配的行，如果没有匹配的行，则不返回任何结果。

l 了解Sql优化思路

优化 PostgreSQL SQL 查询的思路可以从多个方面入手，主要包括以下几点：

1. 索引优化：
   • 确保表中经常用于查询的列都建立了索引，特别是在 WHERE、JOIN 和 ORDER BY 子句中经常用到的列。
   • 避免在索引列上进行函数操作，这会导致索引失效。
   • 定期清理无效或者不再使用的索引，避免索引过多造成性能下降。
2. 查询优化：
   • 优化查询语句的结构，避免使用过多的子查询、联合查询等复杂操作。
   • 在 WHERE 子句中使用合适的条件，避免不必要的全表扫描。
   • 使用** EXPLAIN ANALYZE 命令来分析查询执行计划**，找出性能瓶颈，并根据结果进行调优。
3. 表设计优化：
   • 合理设计表的结构，避免冗余字段和不必要的表关联。
   • 选择合适的数据类型和字段长度，避免过度使用字符类型或者过长的字段。
4. 统计信息维护：
   • 定期更新表的统计信息，以便 PostgreSQL 查询优化器能够更准确地选择执行计划。
   • 使用 VACUUM 和 ANALYZE 命令来管理表空间和维护统计信息。
5. 连接优化：
   • 使用合适的连接方式，避免笛卡尔积和过多的连接操作。
   • 对于大表进行分区或者分片，减少查询范围，提高查询效率。
6. 资源优化：
   • 合理配置 PostgreSQL 的内存、CPU 和磁盘资源，确保足够的资源用于查询和操作。
   • 使用连接池和缓存机制减少连接和查询的开销。
7. 应用层优化：
   • 在应用层进行数据过滤和分页，避免将大量数据传输到客户端进行处理。
   • 使用批量操作来减少数据库交互次数，提高效率。

l 了解外键机制（物理外键及逻辑外键）

外键（Foreign Key）是关系型数据库中用于建立表与表之间关联关系的一种机制，它定义了一个表中的字段（子表）与另一个表中的主键字段（父表）之间的关系。外键可以分为物理外键和逻辑外键两种类型。

物理外键（Physical Foreign Key）

物理外键是通过在数据库中定义外键约束来实现的，它要求子表中的外键字段的值必须存在于父表的主键字段中。物理外键会在数据库层面上对数据进行引用完整性的检查，确保子表中的数据与父表中的数据保持一致性。
物理外键的特点包括：

• 使用数据库的外键约束来维护数据的引用完整性。
• 可以自动执行级联操作，例如级联删除或者级联更新。
• 在数据库层面上进行数据验证，可以防止无效的引用关系。

逻辑外键（Logical Foreign Key）

逻辑外键是通过应用程序的逻辑来实现的，它通常不依赖于数据库的外键约束，而是在应用程序中进行逻辑判断和处理。逻辑外键通过应用程序来维护表与表之间的关联关系，而不是通过数据库的外键约束。
逻辑外键的特点包括：

• 不依赖于数据库的外键约束，而是通过应用程序中的逻辑来维护关联关系。
• 可以更灵活地处理数据关联关系，例如可以根据业务逻辑来定义引用关系。
• 缺乏数据库层面上的数据完整性检查，可能会导致数据不一致性。

如何选择

在使用外键时，需要根据具体的业务需求和数据管理策略来选择使用物理外键还是逻辑外键：

• 如果要求数据引用关系必须在数据库层面上得到强制执行和验证，可以选择使用物理外键。
• 如果需要更灵活地处理数据关联关系，并且可以通过应用程序来保证数据的一致性，可以选择使用逻辑外键。

总的来说，外键是用于建立表与表之间关联关系的重要机制，在使用时需要根据实际情况进行选择，并结合数据库的外键约束和应用程序的逻辑来实现数据管理和完整性保证。

l 了解主键策略有哪些，他们的适用场景是什么

在数据库设计中，主键是用于唯一标识表中每一行数据的一列或一组列。主键策略用于确定如何为表中的每一行数据生成唯一的主键值。
常见的主键策略包括以下几种：
自增主键（Auto Increment）：
使用一个递增的整数作为主键值，每次插入新记录时自动增加。
适用场景：适用于大多数数据库系统，特别是在需要处理大量数据时效率较高。
GUID/UUID 主键：
使用全局唯一标识符（Globally Unique Identifier）或通用唯一标识符（Universally Unique Identifier）作为主键值。
适用场景：当需要在分布式系统中生成唯一标识符时，或者需要在多个数据库实例中保持唯一性时使用。
序列主键（Sequence）：
使用数据库中的序列（Sequence）来生成主键值，每次插入新记录时从序列中获取下一个值。
适用场景：适用于需要手动控制主键值生成的情况，例如需要在多个表之间维护一致的主键值时使用。
组合主键（Composite Key）：
使用多个列的组合作为主键，这些列的值的组合必须是唯一的。
适用场景：适用于表中的每一行需要用多个属性来唯一标识的情况，例如联合主键、复合主键等。
自然主键（Natural Key）：
使用表中的现有数据作为主键，例如身份证号、手机号码等。
适用场景：适用于数据中已经存在的属性具有唯一性且不会发生变化的情况。