PostgreSQL的基本使用

0. 参考：

PGSQL的国内社区

PGSQL的官方地址

1. 数据库操作

进入容器：部署postgres 详见

docker exec -it postgres bash

登录数据库：

# psql -h 服务器 -p 端口号 -U 用户名 -d 数据库 --password	
psql -h localhost -p 5432 -U postgres --password

psql -h localhost -p 5432 -U jinjin --password	

创建数据库：

create database mydata1;

如图：

查看数据库：

\l

切换数据库：

\c mydata1;

删除数据库：

drop database mydata1;

退出当前数据库：

\q

如图：

2. PostgreSQL数据类型

PostgreSQL 数据库支持丰富的数据类型，包括基本数据类型、复合数据类型和用户自定义数据类型等。

• 常见数值型：
  

• 字符串类型：

  • char(size)，character(size)：固定长度字符串，size规定了需存储的字符数，由右边的空格补齐；
  • varchar(size)，charactervarying(size)：可变长度字符串，size规定了需存储的字符数；
  • text：可变长度字符串。

• 日期类型：

  • 表示日期或时间的数据类型有：
  • timestamp：日期和时间；
  • date：日期，无时间；
  • time：时间；
  • 其他数据类型类型还有布尔值boolean（true或false），货币数额money和几何数据等。

汇总数据类型：

以下是 PostgreSQL 中常见的数据类型：

1. 基本数据类型：
   • 整数类型：int, smallint, bigint, serial, bigserial 等。
   • 浮点数类型：real, double precision。
   • 文本类型：char, varchar, text。
   • 日期时间类型：date, time, timestamp, interval。
   • 布尔类型：boolean。
   • 枚举类型：enum。
2. 复合数据类型：
   • 数组类型：array。
   • 范围类型：range。
   • 几何类型：point, line, lseg, box, path, polygon, circle。
3. JSON 和 JSONB 类型：
   • JSON 类型：json。
   • JSONB 类型：二进制 JSON 类型，更高效的存储和查询 JSON 数据。
4. 几何类型：
   • 几何类型：point, line, lseg, box, path, polygon, circle。
5. 网络地址类型：
   • 网络地址类型：cidr, inet, macaddr。
6. 特殊类型：
   • XML 类型：xml。
   • 位字符串类型：bit, bit varying。
7. 自定义数据类型：
   • 自定义枚举类型：CREATE TYPE。
   • 自定义复合类型：CREATE TYPE。
8. 几何数据类型：
   • 几何类型：point, line, lseg, box, path, polygon, circle。
9. 其他数据类型：
   • UUID 类型：uuid。
   • 全局标识符类型：oid。

PostgreSQL 还支持用户自定义数据类型，允许用户根据自身业务需求创建自定义的数据类型。这些数据类型的选择取决于具体的业务需求和数据特点，PostgreSQL 提供了丰富的数据类型来满足不同的需求。

3. 表操作

a. 创建表

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

b. 插入数据

INSERT INTO users (username, email, age, created_at)
VALUES ('john_doe', 'john@example.com', 30, CURRENT_TIMESTAMP),
       ('jane_doe', 'jane@example.com', 28, '2022-04-10 12:00:00');

插入部分内容：

INSERT INTO users (username)
VALUES ('jinjin'),
       ('xiaohua');

c. 查看表和表结构

• 查看数据库中所有表

\d
或
\dt

如图：

• 查看表结构

\d 表名
如：
\d users

如图：

d. 更新数据

UPDATE users
SET age = 31,
    email = 'john_updated@example.com'
WHERE username = 'jinjin';

e. 删除数据

DELETE FROM users WHERE username = 'xiaohua';

f. 查询数据

以下是 PostgreSQL 中查询数据的语句案例，使用 SELECT 语句：

SELECT * FROM users;

上述示例查询了名为 users 的表中的所有数据，并返回所有字段的值。通常情况下，SELECT 语句可以根据需要选择特定的字段进行查询：

SELECT username, email FROM users;

上述示例查询了名为 users 的表中的 username 和 email 字段的值，并返回结果集。

除了简单的查询，还可以在 SELECT 语句中使用条件、排序、聚合函数等功能进行更复杂的查询：

SELECT * FROM users
WHERE age > 30
ORDER BY created_at DESC;

上述示例查询了名为 users 的表中年龄大于 30 岁的用户数据，并按创建时间降序排序。

3. PostgreSQL 的schema的作用

a. Schema简介：

在 PostgreSQL 中，Schema（模式）是数据库对象的集合，用于组织和命名数据库对象，如表、视图、索引、函数等。Schema 可以看作是一个命名空间，用于将数据库对象划分为逻辑上的分组，方便管理和控制访问权限。

Schema 的作用包括以下几个方面：

1. 命名空间管理：Schema 提供了命名空间，可以将数据库对象按照业务逻辑或功能进行组织和分类，避免对象命名冲突，提高数据库的可维护性和可扩展性。
2. 对象隔离：每个 Schema 下的对象都具有独立的命名空间，不同 Schema 下的同名对象互不影响，可以在不同的 Schema 中定义相同名称的对象，实现对象的逻辑隔离。
3. 权限控制：可以针对 Schema 进行权限管理，控制用户对 Schema 中对象的访问权限。通过给用户赋予对特定 Schema 的访问权限，可以实现细粒度的权限控制，保护数据安全。
4. 数据分区：可以使用 Schema 对数据库对象进行逻辑分区，将不同的数据存储在不同的 Schema 中，实现数据的逻辑分离和管理，提高数据库的性能和可用性。
5. 版本控制：可以使用 Schema 对数据库对象进行版本管理，通过在不同的 Schema 中保存不同版本的对象定义，实现数据库对象的版本控制和管理。

总的来说，Schema 在 PostgreSQL 中起着组织、隔离、权限控制和管理的重要作用，是 PostgreSQL 数据库管理和设计中的重要概念。通过合理设计和使用 Schema，可以提高数据库的可管理性、安全性和性能。

b. 创建Schema

create schema mySchema;

如：

c. 基于Schema创建表

CREATE TABLE mySchema.users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

效果如图：