PostgreSQL 常用操作

 1-- 插入数据
 2INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
 3
 4-- 查询记录
 5SELECT * FROM user_tbl;
 6
 7-- 更新数据
 8UPDATE user_tbl set name = '李四' WHERE name = '张三';
 9
10-- 删除记录
11DELETE FROM user_tbl WHERE name = '李四' ;

 1-- 批量更新一个字段
 2UPDATE mytable
 3SET myfield = CASE id
 4    WHEN 1 THEN 'value'
 5    WHEN 2 THEN 'value'
 6    WHEN 3 THEN 'value'
 7END
 8WHERE id IN (1,2,3)
 9
10-- 批量更新多个字段
11UPDATE categories
12SET display_order = CASE id
13    WHEN 1 THEN 3
14    WHEN 2 THEN 4
15    WHEN 3 THEN 5
16END,
17title = CASE id
18    WHEN 1 THEN 'New Title 1'
19    WHEN 2 THEN 'New Title 2'
20    WHEN 3 THEN 'New Title 3'
21END
22WHERE id IN (1,2,3)

1-- 查看当前序列的值
2SELECT currval('user_id_seq');
3SELECT last_value FROM user_id_seq;
4
5-- 设置序列的初始值为 100
6SELECT setval('user_id_seq',100);
7
8-- 递增序列的值并返回
9SELECT nextval('user_id_seq');

 1-- 转换时间戳为格式化时间函数 TO_TIMESTAMP()
 2SELECT TO_TIMESTAMP(created_at) FROM user;
 3
 4-- 转换时间戳为格式化时间函数 TO_TIMESTAMP() ，显示为中国时区
 5-- 使用 'CST' 时区不一定显示为中国本地时间，建议使用 'Asia/Shanghai' 时区
 6SELECT TO_TIMESTAMP(created_at) AT TIME ZONE 'CST' FROM user;
 7SELECT TO_TIMESTAMP(created_at) AT TIME ZONE 'Asia/Shanghai' FROM user;
 8SELECT TO_CHAR(TO_TIMESTAMP(created_at) AT TIME ZONE 'UTC-8', 'YYYY-MM-DD HH24:MI:SS') FROM user;
 9
10-- 返回字段中的条件判断
11SELECT name, CASE sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '保密' END AS sex, age FROM user;
12
13-- 拼接字符串
14SELECT CONCAT('姓名:', name) AS name, age FROM user;
15
16-- 数值计算
17SELECT id, amount/100 FROM order;
18
19-- 保留 2 位小数精度
20SELECT id, round(100/3, 2) FROM order;
21
22-- 解析数组格式数据，获取数据时先将数组转为 JSON，再用编程语言函数将 JSON 转为可识别格式，例如 PHP 中的 json_decode()
23SELECT array_to_json(ids) FROM user;

 1-- 创建新表
 2CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
 3
 4-- 添加字段
 5ALTER TABLE user_tbl ADD email VARCHAR(40);
 6ALTER TABLE user_tbl ADD COLUMN images jsonb DEFAULT '{}';
 7
 8-- 更改字段类型
 9ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
10
11-- 更改字段类型长度
12ALTER TABLE user_tbl ALTER COLUMN password TYPE varchar(32);
13
14-- 为字段添加索引
15CREATE INDEX index_name ON user_tbl (name);
16
17-- 设置字段默认值（注意字符串使用单引号）
18ALTER TABLE user_tbl ALTER COLUMN email SET DEFAULT '[email protected]';
19
20-- 设置字段默认值为指定序列（通常用于主键字段）
21ALTER TABLE user_tbl ALTER COLUMN id SET DEFAULT nextval('user_id_seq');
22
23-- 去除字段默认值
24ALTER TABLE user_tbl ALTER email DROP DEFAULT;
25
26-- 去除字段不为空限制
27ALTER TABLE user_tbl ALTER email DROP NOT NULL;
28
29-- 重命名字段
30ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
31
32-- 删除字段
33ALTER TABLE user_tbl DROP COLUMN email;
34
35-- 表重命名
36ALTER TABLE user_tbl RENAME TO backup_tbl;
37
38-- 删除表
39DROP TABLE IF EXISTS backup_tbl;
40
41-- 删除库
42DROP DATABASE IF EXISTS hello;

 1CREATE TABLE lm_user(
 2    id SERIAL PRIMARY KEY NOT NULL, -- 'ID'
 3    openid VARCHAR(20) DEFAULT NULL UNIQUE, -- 'OPENID'
 4    email VARCHAR(50) NOT NULL UNIQUE, -- 'email'
 5    mobilephone CHAR(15) DEFAULT NULL UNIQUE, -- '手机号码'
 6    password VARCHAR(60) NOT NULL, -- '登录密码'
 7    name VARCHAR(20) DEFAULT NULL, -- '姓名'
 8    num INT NOT NULL DEFAULT '0', -- ''
 9    status SMALLINT NOT NULL DEFAULT '1' -- '状态'
10);