PostgreSQL 基础与实践

--- 在创建表时对字段进行唯一性的约束
CREATE TABLE person (
    id INT NOT NULL auto_increment PRIMARY KEY,
    name VARCHAR(30),
    id_number VARCHAR(18) UNIQUE
);

# postgres
export PATH=${PATH}:/Applications/Postgres.app/Contents/Versions/14/bin

psql --help

# 连接数据库
psql -h <host> -p <port> -U <username> <database-name>

# 查看帮助
help

# 查看 psql 命令详情
\?

# 查看数据库（全部）
\l

# 查看数据库（指定）
\l <database-name>

# 连接数据库
\c <database-name>

# 查看方法
\df

# 查看表（全部）
\d

# 查看表（只看表）
\dt

# 查看表（指定）
\d <table-name>

# 执行 sql 命令
\i <filepath>/<filename>

# 打开拓展视图
\x

# 导出至 CSV
\copy (SELECT * FROM person LEFT JOIN car ON person.car_id = car.id) TO 'path/to/output.csv' DELIMITER ',' CSV HEADER;

# 退出
\q

--- 创建数据库
CREATE DATABASE <database-name>;

--- 删除数据库
DROP DATABASE <database-name>;

# 进入某个数据库
\c <database-name>;

--- 创建表（添加约束）
CREATE TABLE person (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    date_of_birth DATE NOT NULL,
    country_of_birth VARCHAR(50),
    email VARCHAR(150)
);

--- 修改表
ALTER TABLE person ADD PRIMARY KEY(id);

--- 删除字段
ALTER TABLE person DROP column email;

--- 删除全表
DROP TABLE person;

--- 插入数据
INSERT INTO person (
    first_name,
    last_name,
    gender,
    date_of_birth
) VALUES ('Yu', 'ZHANG', 'MALE', DATE '1997-06-06');

--- 修改数据内容
UPDATE person SET email = '[email protected]' WHERE id = 20;

--- 删除数据内容
DELETE FROM person WHERE id = 1;

--- 当发生冲突时不进行操作
INSERT INTO person (
    first_name,
    last_name,
    gender,
    date_of_birth
) VALUES ('Yu', 'ZHANG', 'MALE', DATE '1997-06-06') ON CONFLICT (id) DO NOTHING;

--- 当发生冲突时更新指定字段
INSERT INTO person (
    first_name,
    last_name,
    gender,
    date_of_birth
) VALUES ('Yu', 'ZHANG', 'MALE', DATE '1997-06-06') ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

--- 查看表的全部数据
SELECT * FROM person;

--- 查询数据（特定字段）
SELECT first_name, last_name FROM person;

--- 查询数据（条件筛查，WHERE | AND | OR | 比较 > | >= | < | <= | = | <>）
SELECT * FROM person WHERE gender = 'MALE' AND (country_of_birth = 'Poland' OR country_of_birth = 'China');

--- 查询数据（使用 IN 关键词查询）
SELECT * FROM person WHERE country_of_birth IN ('China', 'Brazil', 'France');

--- 查询数据（使用 BETWEEN 关键词查询）
SELECT * FROM person WHERE date_of_birth BETWEEN DATE '2021-10-10' AND '2022-08-31';

--- 查询数据（使用 LIKE/ILIKE 关键词查询，_ | %）
SELECT * FROM person WHERE email LIKE '%@bloomberg.%';
SELECT * FROM person WHERE email LIKE '________@google.%';
SELECT * FROM person WHERE country_of_birth ILIKE 'p%';

--- 查询数据（使用 GROUP BY 关键词分组查询，使用 HAVING 关键词添加条件，使用 AS 对结果别名）
SELECT country_of_birth, COUNT(*) AS Amount FROM person GROUP BY country_of_birth HAVING Amount > 5 ORDER BY country_of_birth;

--- 查询数据（去重）
SELECT DISTINCT country_of_birth FROM person;

--- 查询数据（排序 ASC | DESC）
SELECT * FROM person ORDER BY id, country_of_birth;

--- 查询数据（指定数量与偏移量）
SELECT * FROM person OFFSET 5 LIMIT 10;
SELECT * FROM person OFFSET 5 FETCH FIRST 5 ROW ONLY;

--- 添加主键
CREATE TABLE person (
    id BIGSERIAL NOT NULL PRIMARY KEY
);

--- 修改主键
ALTER TABLE person ADD PRIMARY KEY(id);

CREATE TABLE person (
    id UUID NOT NULL PRIMARY KEY
);

--- 添加外键
CREATE TABLE person (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    car_id BIGINT REFERENCES car (id),
    UNIQUE(car_id)
);

--- 修改外键
CREATE TABLE car (
    id BIGSERIAL NOT NULL PRIMARY KEY
)

--- JOIN 联表查询
SELECT * FROM person
JOIN car ON person.car_id = car.id

--- LEFT JOIN 左联表查询
SELECT * FROM person
LEFT JOIN car ON person.car_id = car.id

SELECT * FROM person
LEFT JOIN car USING (car_id);

ALTER TABLE person ADD CONSTRAINT gender_constraint CHECK (gender = 'Female' OR gender = 'Male');

CREATE TABLE person (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    email VARCHAR(150) UNIQUE
);

ALTER TABLE person ADD CONTRAINT unique_email_address UNIQUE (email);

--- 使用 COALESCE 填充默认值
SELECT COALESCE(email, 'Email Not Provided') FROM person;

SELECT COALESCE(10 / NULLIF(0, 0), 0);

SELECT NOW();
SELECT NOW()::DATE;
SELECT NOW()::TIME;

SELECT NOW() - INTERVAL '1 YEAR';
SELECT NOW() + INTERVAL '10 MONTHS';
SELECT (NOW() - INTERVAL '3 DAYS')::DATE;

SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(DOW FROM NOW());
SELECT EXTRACT(CENTURY FROM NOW());

SELECT first_name, last_name, AGE(NOW(), date_of_birth) AS age FROM person;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

df

SELECT uuid_generate_v4();