Yulong Niu

个人博客

PostgreSQL使用指南

Posted at — Jul 29, 2015

1. 查询

-- 选取特定的一列或者多列
SELECT column1, column2
FROM dataset

-- 选取全部列
SELECT *
FROM dataset

-- 去除重复,选取一列或多列中唯一元素
-- 如输入多列,则去除多列组合后的重复
SELECT DISTINCT column1, column2
FROM dataset

-- 指定输出列数
-- a是输出行数,b是输出起始行(第一行计为0)
SELECT column
FROM dataset
LIMIT a OFFSET b

2. 排序

-- 按照一列或者多列升序排序
-- 可以按照“不查询”的列排序
-- 先按照column2排,再按照column5排
SELECT column1, column2, column3
FROM dataset
ORDER BY column2, column5

-- 降序
-- DESC只作用于最靠近的唯一一列,即column2,不作用于column5
SELECT column1, column2, column3
FROM dataset
ORDER BY column2 DESC, column5

--使用查询列相对编号
SELECT column1, column2, column3
FROM dataset
ORDER BY 2, column5

3. 筛选

-- ORDER BY语句需要在WHERE之后
-- AND表示“且”, OR表示“或”,第一个条件满足即终止
-- AND和OR可有任意多个
SELECT column1, column2
FROM dataset 
WHERE (column3 = a OR column3 = b) AND column1 = c
ORDER BY column3

-- 多个OR语句使用IN代替,比如上述例子为:
SELECT column1, column2
FROM dataset 
WHERE column3 IN (a, b) AND column1 = c
ORDER BY column3

-- NOT与IN连用
SELECT column1, column2
FROM dataset 
WHERE column3 NOT IN (a, b)

-- BETWEEN a AND b语句,a必须小于等于b
-- 如a等于b,则相当于筛选与a(或者b)相等数值
SELECT column1, column2
FROM dataset
WHERE column1 BETWEEN a AND b

--筛选NULL值使用IS NULL
--筛选非NULL值使用IS NOT NULL
SELECT column1, column2
FROM dataset
WHERE column1 IS NULL

4. 模式匹配

-- LIKE和NOT LIKE支持对字符串的模式匹配
-- “_”匹配单一一个字符
-- “%”匹配0个或多个字符
SELECT column1, column2
FROM dataset
WHERE column1 LIKE 'F_y%'

-- SIMILAR TO和NOT SIMILAR TO支持正则匹配
-- 正则匹配中,仍然使用“_”和“%”
SELECT column1, column2
FROM dataset
WHERE column1 SIMILAR TO '[^JM]%'

5. 函数

--数学计算
SELECT column1, column2 * column3 AS newName
FROM dataset

-- 字符串连接
SELECT RTRIM(column1) || ' (' || RTRIM(column1) || ')' AS newName
FROM dataset
ORDER BY column1

-- 使用函数
SELECT column1, UPPER(column2) AS newName
FROM dataset

-- 筛选日期
SELECT column1, columnDate 
FROM dataset
WHERE DATE_PART('year', columnDate) = 2015

-- 汇总数据
SELECT AVG(DISTINCT column1) AS newName1,
       SUM(column2) AS newName2,
       MAX(column3) AS newName3,
       MIN(column4) AS newName4
FROM Products 
WHERE vend_id = 'DLL01'

6. 分组

-- 分组的意思是对一列或者多列数据归总
SELECT column1, column2, COUNT(*) AS newName 
FROM dataset
WHERE column4 > 3
GROUP BY column1, column2
HAVING COUNT(*) > 1
ORDER BY newName

8. PostgreSQL支持的函数

8.1 数值

函数名意义
ABS()绝对值
SQRT()平方根
ROUND(v numeric, s int)取特定小数位数
AVG()平均值,忽略NULL
MAX()最大值,忽略NULL
MIN()最小指,忽略NULL
SUM()求和,忽略NULL
COUNT(*)所有行数,包括NULL
COUNT(column1)行数,忽略NULL

详细参考:PostgreSQL支持的数值操作

8.2 字符串

函数名意义
RTRIM()删除字符串左侧空格
LTRIM()删除字符串右侧空格
TRIM()删除双侧空格
SUBSTRING(string [from int] [for int])按照索引取字符串(从1开始)
SUBSTRING(string [from int] [for int])选取符合POSIX正则匹配字符串
CHAR_LENGTH()计算字符串长度,等同于LENGTH()
UPPER()大写
LOWER()小写
LEFT(string, n int)截取左侧n个字符串(从1开始)

详细参考:PostgreSQL支持的字符串操作

8.3 日期

函数名意义
CURRENT_DATE当前日期
DATE_PART(text, timestamp){:.language-psql}选取日期中的年、月或日

详细参考:PostgreSQL支持的日期操作

使用建议

  1. 数据集的名字为一个单词,比如priceCustom而不是一个字符串 'price custom'。同样,命名别名(使用AS)也如此。

参考资料

更新记录

2015年7月29日