PGSQL常见命令行与函数实例详解

目录

• PostgreSQL 常用命令行与内置函数详解
• 一、PostgreSQL 命令行工具（psql）详解
• 1. 数据库连接与基本操作
• 2. psql 内部元命令（反斜杠命令）
• 3. 实用元命令大全
• 二、PostgreSQL 内置函数详解
• 1. 数学函数
• 2. 字符串函数
• 3. 日期时间函数
• 4. 条件函数
• 5. 聚合函数
• 6. 窗口函数
• 7. jsON 函数（PostgreSQL 9.3+）
• 三、实用SQL示例
• 1. 数据备份和恢复
• 2. 性能分析
• 3. 实用管理查询
• 四、总结
• 相关文献

PostgreSQL 常用命令行与内置函数详解

一、PostgreSQL 命令行工具（psql）详解

1. 数据库连接与基本操作

（1）连接数据库

# 基本连接（使用当前系统用户）
psql
# 连接指定数据库
psql -d mydatabase
# 连接指定用户和数据库
psql -U username -d mydatabase
# 连接远程数据库
psql -h hostname -p 5432 -U username -d mydatabase
# 连接并执行SQL文件
psql -d mydatabase -f script.sql
# 连接并执行单条命令
psql -d mydatabase -c "SELECT version();"

（2）连接参数说明

2. psql 内部元命令（反斜杠命令）

（1）数据库操作

-- 列出所有数据库
\l
\l+
-- 连接/切换数据库
\c database_name
\connect database_name
-- 创建数据库
CREATE DATABASE newdb;
-- 删除数据库
DROP DATABASE olddb;

（2）表操作

-- 列出当前数据库所有表
\dt
\dt+
-- 描述表结构
\d table_name
\d+ table_name
-- 列出所有索引
\di
\di+
-- 列出所有序列
\ds
\ds+

（3）模式操作

-- 列出所有模式
\dn
\dn+
-- 设置搜索路径
SET search_path TO schema1, schema2;
-- 查看当前搜索路径
SHOW search_path;

（4）用户和权限

-- 列出所有用户/角色
\du
\du+
-- 查看当前用户
SELECT current_user;
-- 查看用户权限
\dp table_name
\z table_name

（5）信息查询

-- 查看服务器版本
SELECT version();
-- 查看连接信息
\conninfo
-- 查看当前数据库
SELECT current_database();
-- 查看当前模式
SELECT current_schema();

（6）输出和格式控制

-- 扩展显示（行转列）
\x on
\x auto
\x off
-- 设置输出格式
\a (对齐切换)
\f (字段分隔符)
-- 计时开关
\timing on
\timing off
-- 输出到文件
\o filename.txt
\o

3. 实用元命令大全

二、PostgreSQL 内置函数详解

1. 数学函数

（1）基本数学运算

-- 绝对值
SELECT abs(-15); -- 15
-- 四舍五入
SELECT round(3.14159, 2); -- 3.14
SELECT round(123.456); -- 123
-- 取整函数
SELECT ceil(3.14); -- 4 (向上取整)
SELECT floor(3.14); -- 3 (向下取整)
SELECT trunc(3.14159, 2); -- 3.14 (截断)
-- 幂运算
SELECT power(2, 3); -- 8
SELECT sqrt(25); -- 5 (平方根)
SELECT cbrt(27); -- 3 (立方根)
-- 对数函数
SELECT ln(10); -- 自然对数
SELECT log(100); -- 以10为底对数

（2）三角函数

SELECT pi(); -- 3.14159265358979
SELECT sin(pi()/2); -- 1
SELECT cos(0); -- 1
SELECT tan(pi()/4); -- 1
SELECT degrees(pi()); -- 180 (弧度转角度)
SELECT radians(180); -- 3.14159 (角度转弧度)

（3）随机数生成

SELECT random(); -- 0-1之间的随机数
SELECT setseed(0.5); -- 设置随机种子
-- 生成指定范围随机整数
SELECT floor(random() * 100)::int; -- 0-99随机整数
SELECT (random() * (max-min) + min)::int; -- min-max随机整数

2. 字符串函数

（1）字符串操作

-- 长度和位置
SELECT length('Hello'); -- 5
SELECT position('l' in 'Hello'); -- 3
SELECT strpos('Hello', 'l'); -- 3
-- 大小写转换
SELECT upper('hello'); -- HELLO
SELECT lower('HELLO'); -- hello
SELECT initcap('hello world'); -- Hello World
-- 修剪函数
SELECT trim('  hello  '); -- 'hello'
SELECT ltrim('  hello'); -- 'hello'
SELECT rtrim('hello  '); -- 'hello'
SELECT trim(BOTH 'x' FROM 'xxhelloxx'); -- 'hello'
-- 填充函数
SELECT lpad('hi', 5, '*'); -- '***hi'
SELECT rpad('hi', 5, '*'); -- 'hi***'

（2）子字符串操作

-- 子字符串
SELECT substring('Hello World' from 2 for 5); -- 'ello '
SELECT substr('Hello World', 2, 5); -- 'ello '
-- 字符串替换
SELECT replace('Hello World', 'World', 'PostgreSQL'); -- 'Hello PostgreSQL'
SELECT translate('12345', '123', 'abc'); -- 'abc45'
-- 字符串分割
SELECT split_part('a,b,c,d', ',', 2); -- 'b'
SELECT string_to_array('a,b,c', ','); -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ','); -- 'a,b,c'

（3）格式化函数

-- 字符串连接
SELECT concat('Hello', ' ', 'World'); -- 'Hello World'
SELECT 'Hello' || ' ' || 'World'; -- 'Hello World'
-- 格式化输出
SELECT format('Hello %s, your balance is %s', 'John', 100.50);
-- 'Hello John, your balance is 100.50'
-- 重复字符串
SELECT repeat('*', 5); -- '****编程*'

3. 日期时间函数

（1）当前时间获取

-- 获取当前时间
SELECT current_date; -- 当前日期
SELECT current_time; -- 当前时间
SELECT current_timestamp; -- 当前时间戳
SELECT now(); -- 当前时间戳（同current_timestamp）
SELECT localtimestamp; -- 本地时间戳
js-- 带精度的当前时间
SELECT current_time(2); -- 当前时间（2位小数秒）
SELECT current_timestamp(3); -- 当前时间戳（3位小数秒）

（2）日期时间提取

-- EXTRACT函数
SELECT extract(year FROM current_timestamp); -- 年份
SELECT extract(month FROM current_timestamp); -- 月份
SELECT extract(day FROM current_timestamp); -- 日期
SELECT extract(hour FROM current_timestamp); -- 小时
SELECT extract(minute FROM current_timestamp); -- 分钟
SELECT extract(second FROM current_timestamp); -- 秒数
SELECT extract(dow FROM current_timestamp); -- 星期几（0-6，周日=0）
SELECT extract(doy FROM current_timestamp); -- 一年中的第几天
-- DATE_PART函数（同EXTRACT）
SELECT date_part('year', current_timestamp);

（3）日期时间运算

-- 日期加减
SELECT current_date + INTERVAL '1 day'; -- 明天
SELECT current_date - INTERVAL '1 week'; -- 一周前
SELECT current_timestamp + INTERVAL '2 hours 30 minutes'; -- 2小时30分钟后
-- 年龄计算
SELECT age('2000-01-01'::date); -- 从2000-01-01到现在的年龄
SELECT age('2000-01-01'::date, '2020-01-01'::date); -- 两个日期之间的年龄
-- 日期截断
SELECT date_trunc('hour', current_timestamp); -- 截断到小时
SELECT date_trunc('day', current_timestamp); -- 截断到天
SELECT date_trunc('month', current_timestamp); -- 截断到月

（4）日期时间格式化

-- TO_CHAR格式化
SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS'); -- '2023-01-01 14:30:00'
SELECT to_char(current_timestamp, 'Day, Month DD, YYYY'); -- 'Sunday, January 01, 2023'
SELECT to_char(123.45, '999D99'); -- '123.45'
-- TO_DATE和TO_TIMESTAMP转换
SELECT to_date('20230101', 'YYYYMMDD'); -- 2023-01-01
SELECT to_timestamp('20230101 143000', 'YYYYMMDD HH24MISS'); -- 2023-01-01 14:30:00

4. 条件函数

（1）条件判断

-- CASE表达式
SELECT 
    name,
    salary,
    CASE 
        WHEN salary > 100000 THEN 'High'
        WHEN salary > 50000 THEN 'Medium'
        ELSE 'Low'
    END as salary_level
FROM employees;
-- 简单CASE
SELECT 
    product_type,
    CASE product_type
        WHEN 'A' THEN 'Type A'
        WHEN 'B' THEN 'Type B'
        ELSE 'Other'
    END as type_description
FROM products;

（2）空值处理

-- COALESCE（返回第一个非空值）
SELECT coalesce(NULL, NULL, 'default'); -- 'default'
SELECT coalesce(email, 'N/A') FROM users; -- 如果email为NULL则返回'N/A'
-- NULLIF（相等返回NULL）
SELECT nullif(5, 5); -- NULL
SELECT nullif(5, 0); -- 5
-- GREATEST和LEAST
SELECT greatest(1, 5, 3); -- 5
SELECT least(1, 5, 3); -- 1

5. 聚合函数

（1）基本聚合函数

-- 计数
SELECT count(*) FROM table; -- 总行数
SELECT count(DISTINCT column) FROM table; -- 不重复计数
-- 求和与平均
SELECT sum(salary) FROM employees;
SELECT avg(salary) FROM employees;
-- 最大最小值
SELECT max(salary) FROM employees;
SELECT min(salary) FROM employees;
-- 统计信息
SELECT stddev(salary) FROM employees; -- 标准差
SELECT variance(salary) FROM employees; -- 方差

（2）高级聚合函数

-- 分组统计
SELECT 
    department,
    count(*) as emp_count,
    avg(salary) as avg_salary,
    sum(salary) as total_salary
FROM employees 
GROUP BY department;
-- 字符串聚合
SELECT 
    department,
    string_agg(name, ', ') as employees
FROM employees 
GROUP BY department;
-- 数组聚合
SELECT 
    department,
    array_agg(name) as employees
FROM employees 
GROUP BY department;
-- JSON聚合
SELjavascriptECT 
    department,
    json_agg编程客栈(json_build_object('name', name, 'salary', salary)) as employees
FROM employees 
GROUP BY department;

6. 窗口函数

（1）排名函数

-- 行号、排名、密集排名
SELECT 
    name,
    salary,
    row_number() OVER (ORDER BY salary DESC) as row_num,
javascript    rank() OVER (ORDER BY salary DESC) as rank,
    dense_rank() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;
-- 分区排名
SELECT 
    department,
    name,
    salary,
    rank() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

（2）前后值函数

-- LAG和LEAD（访问前后行）
SELECT 
    date,
    sales,
    lag(sales) OVER (ORDER BY date) as prev_sales,
    lead(sales) OVER (ORDER BY date) as next_sales
FROM sales_data;
-- FIRST_VALUE和LAST_VALUE
SELECT 
    date,
    sales,
    first_value(sales) OVER (ORDER BY date) as first_sales,
    last_value(sales) OVER (ORDER BY date) as last_sales
FROM sales_data;

（3）累计和移动平均

-- 累计计算
SELECT 
    date,
    sales,
    sum(sales) OVER (ORDER BY date) as cumulative_sum,
    avg(sales) OVER (ORDER BY date) as cumulative_avg
FROM sales_data;
-- 移动平均
SELECT 
    date,
    sales,
    avg(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3
FROM sales_data;

7. JSON 函数（PostgreSQL 9.3+）

（1）JSON创建和解析

-- 创建JSON
SELECT json_build_object('name', 'John', 'age', 30);
SELECT json_build_array(1, 2, 3);
-- 解析JSON
SELECT json_extract_path('{"a": {"b": 1}}', 'a', 'b'); -- 1
SELECT '{"name": "John"}'::json->>'name'; -- 'John'
SELECT '{"ages": [10, 20, 30]}'::json->'ages'->>1; -- '20'

（2）JSON聚合

-- 行转JSON
SELECT row_to_json(employees) FROM employees WHERE id = 1;
-- JSON聚合
SELECT 
    department,
    json_agg(json_build_object('name', name, 'salary', salary)) as employees
FROM employees 
GROUP BY department;

三、实用SQL示例

1. 数据备份和恢复

# 备份单个数据库
pg_dump -U username -d mydatabase -f backup.sql
# 备份所有数据库
pg_dumpall -U username -f alldb_backup.sql
# 压缩备份
pg_dump -U username -d mydatabase | gzip > backup.sql.gz
# 恢复数据库
psql -U username -d mydatabase -f backup.sql
# 恢复压缩备份
gunzip -c backup.sql.gz | psql -U username -d mydatabase

2. 性能分析

-- 查看查询计划
EXPLAIN SELECT * FROM large_table WHERE id = 1000;
EXPLAIN ANALYZE SELECT * FROM large_table WHERE id = 1000;
-- 查看表大小和索引
SELECT 
    schemaname,
    tablename,
    tableowner,
    tablesize,
    indexsize
FROM pg_tables 
WHERE schemaname = 'public';
-- 查看长查询
SELECT 
    pid, 
    now() - pg_stat_activity.query_start AS duration, 
    query 
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY duration DESC;

3. 实用管理查询

-- 查看锁信息
SELECT 
    locktype, 
    relation::regclass, 
    mode, 
    granted 
FROM pg_locks 
WHERE relation = 'mytable'::regclass;
-- 查看连接数
SELECT 
    datname, 
    count(*) as connections 
FROM pg_stat_activity 
GROUP BY datname;
-- 查看表统计信息
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables;

四、总结

PostgreSQL 提供了丰富的命令行工具和内置函数，使得数据库管理和数据操作变得非常高效。关键点包括：

1. psql命令行：熟练掌握连接参数和元命令，提高管理效率
2. 数学函数：处理数值计算和统计分析
3. 字符串函数：文本处理和格式化输出
4. 日期时间函数：时间计算和格式化
5. 聚合函数：数据统计和分组分析
6. 窗口函数：高级分析和排名计算
7. JSON函数：处理半结构化数据

相关文献

【数据库知识】PostgreSQL介绍

到此这篇关于PGSQL常见命令行与函数实例详解的文章就介绍到这了,更多相关PGSQL常见命令内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！