PostgreSQL 增删改和内置语句¶
一. 进入数据库¶
与MySQL不同，pgsql中没有默认的root用户。进入pgsql数据库需要用到postgres用户权限
第一次连接数据库可以使用命令sudo -u postgres psql，或者在容器中时使用命令docker exec -it my-pg18-learn psql -U postgres
二. 增删改查¶
1. 总览¶
我们在介绍MySQL时候也介绍过增删改查命令，PostgreSQL对于数据库的增删改查命令几乎是一模一样的，在对库，表的操作语法也非常相近。
-- 查询 (两者相同)
SELECT * FROM users WHERE age > 25;
-- 插入 (两者相同)
INSERT INTO users (name,email) VALUES ('张三','zhangsan@example.com');
-- 更新 (两者相同)
UPADTE users SET age = 30 WHERE name = '张三';
-- 删除 (两者相同)
DELETE FROM users WHERE id = 1;
2. 数据库操作(Database CRUD)¶
C(Create): 创建数据库¶
-- 基本创建，这个和mysql是一样的(IF NOT EXISTS在pgsql中不支持)
CREATE DATABASE abcd;
-- 创建并指定字符集和排序规则
CREATE DATABASE efg
ENCODING 'UTF8' -- 字符编码 (对应MySQL的CHARSET)
LC_COLLATE 'zh_CN.UTF-8' -- 排序规则 (对应MySQL的COLLATE)
LC_CTYPE 'zh_CN.UTF-8' -- 字符分类 (影响字符串处理，MySQL无此参数)
TEMPLATE template0; -- 关键！指定用“白纸”模板
-- PostgreSQL建库时是“复制”一个已有的库作为“模具”。默认用的是 template1，
-- 但 template1 的编码和排序规则是固定的（不能在建库时覆盖）。
-- 要自定义字符集/排序规则，必须用 template0（一个纯净的、允许覆盖设置的模板）。
-- 注：template0 是系统自带的“恢复出厂设置”模板，始终是只读的。
CREATE DATABASE hig
ENCODING 'UTF8'
LC_COLLATE 'ja_JP.UTF-8'
LC_CTYPE 'ja_JP.UTF-8'
TEMPLATE template0;
CREATE DATABASE klm
ENCODING 'UTF8'
LC_COLLATE 'C' -- 速度极快，不依赖系统语言包
LC_CTYPE 'C' -- 字符分类也按二进制处理
TEMPLATE template0;
R(Read): 查询数据库¶
-- 此命令pgsql就不同了
-- \l 等于MySQL中的 SHOW DATABASES; 列出当前 PostgreSQL 集群中所有的数据库（Database）清单。
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
abcd | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
efg | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | |
hig | postgres | UTF8 | libc | ja_JP.UTF-8 | ja_JP.UTF-8 | | |
klm | postgres | UTF8 | libc | C | C | | |
postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(7 rows)
-- 创建数据库时默认使用template1来建库，如果要更改字符集排序规则等就使用`TEMPLATE template0;`命令指定template0
-- postgres是“管理员入口”，可以删除不会导致数据库崩溃，但不建议删除可能会导致工具脚本等报错。
postgres=# SELECT datname FROM pg_database;
datname
-----------
postgres
abcd
template1
template0
efg
hig
klm
(7 rows)
D(Delete): 删除数据库¶
-- 删除abcd数据库(危险命令!谨慎操作)，这个和MySQL是一致的
postgres=# DROP DATABASE abcd;
DROP DATABASE
postgres=# SELECT COUNT(*) FROM pg_database;
count
-------
6
(1 row)
选择数据库(Use)¶
-- 在MySQL中:
USE efg;
--在pgsql中
postgres=# \c hig;
You are now connected to database "hig" as user "postgres".
hig-# \c klm
You are now connected to database "klm" as user "postgres".
klm=#
3. 表操作(Table CRUD)¶
C(Create): 创建表¶
-- 创建用户表(基本结构)
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT, -- MySQL自增语法
name VARCHAR(50) NOT NULL,
age INT,
CREATE_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- PostgreSQL中自增语法不同(用SERIAL或identity)
CREATE TABLE users(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
-- 或使用id SERIAL PRIMARY KEY, 这种写法时数据类型就是INTEGER，等于`id INTEGER NOT NULL DEFAULT nextval('users_id_seq')`。 在此`GENERATED ALWAYS AS IDENTITY`就是自增
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-- 加上时区信息，`TIMESTAMP`意义为“无时区信息的时间”，它返回的是当前会话时区下的时间。建议改成`TIMESTAMPTZ`（带时区的时间戳）。`CURRENT_TIMESTAMP`意为此时此刻。
-- created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
R(Read): 查询表¶
Ⅰ. 查询所有表
在PostgreSQL中
-- 方法1: 使用pgsql客户端特有的命令(更简洁且常用)
efg=# \dt;
List of tables
Schema | Name | Type | Owner
--------+-------+-------+----------
public | users | table | postgres
(1 row)
-- 方法2: 查询系统表
efg=# SELECT tablename FROM pg_tables WHERE schemaname = 'public';
tablename
-----------
users
(1 row)
-- 方法3: 使用通用命令
efg=# SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
table_name
------------
users
(1 row)
Ⅱ. 查询表结构
-- mysql中查看表结构
desc TABLES;
--pgsql中查看表结构
efg=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+------------------------------
id | integer | | not null | generated always as identity
name | character varying(50) | | not null |
age | integer | | not null |
created_at | timestamp without time zone | | | CURRENT_TIMESTAMP
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
-- Collation（排序规则）决定了字符串（VARCHAR、TEXT类型）在数据库里如何比较和排序。
U(Update): 修改表¶
这是DDL(数据定义语言)中非常重要的部分，用于调整表结构
-- ============== 添加到 ==============
-- PostgreSQL
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
--MySQL
ALTER TABLE users ADD phone VARCHAR(20);
-- ============== 修改列的数据类型 ==============
--PostgreSQL
ALTER TABLE users ALTER COLUMN age type INT;
--MySQL
ALTER TABLE users MODIFY age INT;
-- ============== 重命名列 ==============
-- PostgreSQL
ALTER TABLE users RENAME COLUMN pohone TO phone_number;
-- MySQL(两种方法)
ALTER TABLE users CHANGE phone phone_number VARCHAR(20);
-- MySQL8.0+
ALTER TABLE users RENAME COLUMN phone TO phone_number;
-- ============== 删除列 ==============
--PostgreSQL
ALTER TABLE users DROP COLUMN phone_number;
--MySQL
ALTER TABLE users DROP phone_number;
-- ============== 添加主键 ==============
-- PostgreSQL
ALTER TABLE users ADD PRIMARY KEY (id);
-- MySQL
ALTER TABLE users ADD PRIMARY KEY (id);
-- ============== 添加外键 ==============
-- PostgreSQL(必须命名约束)
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
--MySQL(可以自动命名)
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
-- ============== 添加索引 ==============
-- PostgreSQL
CREATE INDEX idx_users_email ON users (email);
-- MySQL(两种方法)
CREATE INDEX idx_users_email ON users (email);
-- 或
ALTER TABLE users ADD INDEX idx_users_email (email);
-- ============== 设置NOT NULL约束 ==============
-- PostgreSQL
ALTER TABLE users ALTER COLUM email SET NOT NULL;
--MySQL
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;
D(Delete): 删除表¶
警告，此操作非常危险，会直接删除表及所有数据，务必谨慎！