2.2-pgsql 命令行

Create by fall on 25 Feb 2024
Recently revised in 17 Nov 2025

postgresql

如果你想要线上编辑和执行 sql，你可以使用：https://sqlfiddle.com/

数据库操作

# 创建数据库
createdb mydb
# 移除数据库
dropdb mydb
# 访问数据库
psql mydb

表操作

PostgreSQL 支持标准的 SQL 类型，以及其他的通用功能的类型和丰富的几何类型。

标准的 SQL 类型：int、smallint、real、double precision、char(<number>)、varchar(<number>)、date、time、timestamp和interval

varchar(80)指定了一个可以存储最长 80 个字符的任意字符串的数据类型。
int是普通的整数类型。
real是一种用于存储单精度浮点数的类型。
date 类型应该可以自解释

创建表

CREATE TABLE weather {
  -- 行注释
	city    varchar(80),
	temp_lo  int,
	temp_hi  int,
	prcp     re4al,
	date     date,
};
CREATE TABLE cities {
  name varchar(80)
  -- point 是 pg 特有数据类型 
	location point
};

# 删除表：
DROP TABLE tablename;

可以手动输入换行符号，psql 可以识别该命令直到分号才结束。

空白字符可以自由使用（不必对齐每个字符）

行注释：--

SQL 对关键字和标识符不区分大小写，标识符用双引号括起来可以保留大小写

虽然列的插入，有严格的顺序，但 SQL 并不对行在表中的顺序做任何保证

插入行

# 按照列的顺序进行插入
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27', '(-194.0, 53.0)')

# 更推荐该方法插入数据，而不是隐含依赖
# 按照字段顺序进行插入，也可以忽略一些数据的插入
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

确保所有数据类型都相当明确。那些不是简单数字值的常量，通常必需用单引号 ' 包裹。

# 使用 COPY 从文本文件中装载大量数据
COPY weather FROM '/home/user/weather.txt';

更新行

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

删除行

-- 删除 Hayward 中所有的数据
DELETE FROM weather WHERE city = 'Hayward';
-- Warn ！！如果没有限制，DELETE 将删除指定表中所有行
-- DELETE FROM tablename;

表查询

按照表头查询

# 查询所有的 weather 字段
SELECT * FROM weather;
# 对应如下字段
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
# 对查询的字段进行处理
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

条件查询

# 查找 'San Francisco' 并且为降雨天气
SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

查询排序

# 按照城市排序
SELECT * FROM weather
    ORDER BY city;
# 按照城市以及温度递减进行排序
SELECT * FROM weather
    ORDER BY city, temp_lo desc;

按照集合查询数据

# 以集合形式返回查询结果，并且根据名称排序
SELECT DISTINCT city
    FROM weather
    ORDER BY city;

虽然 SELECT * 对于查询很有用，但普遍认为是很糟糕的风格，因为给表增加一列就会改变查询结果。

join 查询

一次同时访问多张表（或者多个实例的同一张表）

SELECT * FROM weather JOIN cities ON city = name;
# 返回内容，该查询结果只会包含两个表都存在的列，且 city = name
#      city      | temp_lo | temp_hi | prcp |    date    |     name      | location
# ---------------+---------+---------+------+------------+---------------+-----------
#  San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
#  San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
# 有两个列包含城市名字，如果只想输出一个，可以使用
SELECT city, temp_lo, temp_hi, prcp, date, location
	FROM weather JOIN cities ON city = name;

如果想要扫描 weather 表，并且每一行都找出匹配的 cities 表行，如果没有找到匹配的行，使用“空值”代替 cities 表的列。这种类型的查询叫外连接（OUTER JOIN）上方使用的是（INNER JOIN）

SELECT *
    FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;
# 返回内容
#      city      | temp_lo | temp_hi | prcp |    date    |     name      | location
# ---------------+---------+---------+------+------------+---------------+-----------
#  Hayward       |      37 |      54 |      | 1994-11-29 |               |
#  San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
#  San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)

我们也可以把一个表和自己连接起来。这叫做自连接。

-- 同一个表也可以进行多次命名
SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
       w2.city, w2.temp_lo AS low, w2.temp_hi AS high
    FROM weather w1 JOIN weather w2
        ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;
#   city      | low | high |     city      | low | high
# --------------+-----+------+---------------+-----+------
# San Francisco |  43 |   57 | San Francisco |  46 |   50
# Hayward       |  37 |   54 | San Francisco |  46 |   50
# (2 rows)

sql 简化，优化

SELECT * FROM weather JOIN cities ON city = name;
-- 下方的 FROM 子句中简单地列出表，比较表达式被添加到 WHERE 子句中。下方这种旧的隐式语法和上方新的显式 JOIN/ON 语法的结果是相同的。对读者来说，显示的语法使其含义更容易理解。
SELECT *
    FROM weather, cities
    WHERE city = name;

-- 对第一个命令进行简化
SELECT *
    FROM weather w JOIN cities c ON w.city = c.name;

在一个连接查询中限定列名所在的表，是一种好的风格，即使未来添加重名列也不会导致查询失败

聚集函数

从多行中计算出一个结果，比如count（计数）、sum（和）、avg（均值）、max（最大值）和min（最小值）的函数

SELECT max(temp_lo) FROM weather;
-- 聚集 max 不能被用于 WHERE 子句中
-- SELECT city FROM weather WHERE temp_lo = max(temp_lo);     错误
-- 查询最大值，以及最大值所在的城市名称
SELECT city FROM weather
	WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

-- 获取每个城市的最低温度最大值
SELECT city, count(*), max(temp_lo)
    FROM weather
    -- WHERE city LIKE 'S%'            -- 只查询以“S”开头的城市的话
    GROUP BY city
    HAVING max(temp_lo) < 40;

分组查询

select
	HOUR(created_at) as hour,
	COUNT(*) as count,
	COALESCE(SUM(value), 0) as sum,
	COALESCE(AVG(value), 0) as average
FROM data_records
WHERE created_at >= ? AND created_at < ?
GROUP BY HOUR(created_at)
ORDER BY hour

参考文章

文章名称	链接
PostgreSQL 17.5 手册	http://www.postgres.cn/docs/17/tutorial-select.html

postgresql​

数据库操作​

表操作​

创建表​

插入行​

更新行​

删除行​

表查询​

join 查询​

聚集函数​

分组查询​

参考文章​