PGSQL常見命令行與函數(shù)實(shí)例詳解

更新時(shí)間：2025年11月03日 12:06:40 作者：問道飛魚

本文詳細(xì)介紹了PostgreSQL常用命令行工具psql和內(nèi)置函數(shù)的操作方法,文章提供了大量實(shí)用的命令模板和函數(shù)示例,是PostgreSQL數(shù)據(jù)庫管理和開發(fā)的實(shí)用參考手冊(cè),適合數(shù)據(jù)庫管理員和開發(fā)人員快速查閱常用操作,感興趣的朋友跟隨小編一起看看吧

PostgreSQL 常用命令行與內(nèi)置函數(shù)詳解

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

1. 數(shù)據(jù)庫連接與基本操作

（1）連接數(shù)據(jù)庫

# 基本連接（使用當(dāng)前系統(tǒng)用戶）
psql
# 連接指定數(shù)據(jù)庫
psql -d mydatabase
# 連接指定用戶和數(shù)據(jù)庫
psql -U username -d mydatabase
# 連接遠(yuǎn)程數(shù)據(jù)庫
psql -h hostname -p 5432 -U username -d mydatabase
# 連接并執(zhí)行SQL文件
psql -d mydatabase -f script.sql
# 連接并執(zhí)行單條命令
psql -d mydatabase -c "SELECT version();"

（2）連接參數(shù)說明

參數(shù)	說明	示例
`-d`	數(shù)據(jù)庫名	`-d postgres`
`-U`	用戶名	`-U postgres`
`-h`	主機(jī)地址	`-h localhost`
`-p`	端口號(hào)	`-p 5432`
`-W`	強(qiáng)制密碼提示	`-W`
`-f`	執(zhí)行SQL文件	`-f init.sql`
`-c`	執(zhí)行命令	`-c "SELECT 1"`

2. psql 內(nèi)部元命令（反斜杠命令）

（1）數(shù)據(jù)庫操作

-- 列出所有數(shù)據(jù)庫
\l
\l+
-- 連接/切換數(shù)據(jù)庫
\c database_name
\connect database_name
-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE newdb;
-- 刪除數(shù)據(jù)庫
DROP DATABASE olddb;

（2）表操作

-- 列出當(dāng)前數(shù)據(jù)庫所有表
\dt
\dt+
-- 描述表結(jié)構(gòu)
\d table_name
\d+ table_name
-- 列出所有索引
\di
\di+
-- 列出所有序列
\ds
\ds+

（3）模式操作

-- 列出所有模式
\dn
\dn+
-- 設(shè)置搜索路徑
SET search_path TO schema1, schema2;
-- 查看當(dāng)前搜索路徑
SHOW search_path;

（4）用戶和權(quán)限

-- 列出所有用戶/角色
\du
\du+
-- 查看當(dāng)前用戶
SELECT current_user;
-- 查看用戶權(quán)限
\dp table_name
\z table_name

（5）信息查詢

-- 查看服務(wù)器版本
SELECT version();
-- 查看連接信息
\conninfo
-- 查看當(dāng)前數(shù)據(jù)庫
SELECT current_database();
-- 查看當(dāng)前模式
SELECT current_schema();

（6）輸出和格式控制

-- 擴(kuò)展顯示（行轉(zhuǎn)列）
\x on
\x auto
\x off
-- 設(shè)置輸出格式
\a (對(duì)齊切換)
\f (字段分隔符)
-- 計(jì)時(shí)開關(guān)
\timing on
\timing off
-- 輸出到文件
\o filename.txt
\o

3. 實(shí)用元命令大全

命令	功能	示例
`\?`	查看所有元命令幫助	`\?`
`\h`	SQL命令幫助	`\h CREATE TABLE`
`\q`	退出psql	`\q`
`\!`	執(zhí)行系統(tǒng)命令	`\! ls -l`
`\g`	再次執(zhí)行上次查詢	`\g`
`\s`	查看命令歷史	`\s`
`\e`	使用編輯器編輯查詢	`\e`
`\i`	執(zhí)行外部SQL文件	`\i /path/to/file.sql`
`\copy`	導(dǎo)入導(dǎo)出數(shù)據(jù)	`\copy table TO 'file.csv' CSV`

二、PostgreSQL 內(nèi)置函數(shù)詳解

1. 數(shù)學(xué)函數(shù)

（1）基本數(shù)學(xué)運(yùn)算

-- 絕對(duì)值
SELECT abs(-15); -- 15
-- 四舍五入
SELECT round(3.14159, 2); -- 3.14
SELECT round(123.456); -- 123
-- 取整函數(shù)
SELECT ceil(3.14); -- 4 (向上取整)
SELECT floor(3.14); -- 3 (向下取整)
SELECT trunc(3.14159, 2); -- 3.14 (截?cái)?
-- 冪運(yùn)算
SELECT power(2, 3); -- 8
SELECT sqrt(25); -- 5 (平方根)
SELECT cbrt(27); -- 3 (立方根)
-- 對(duì)數(shù)函數(shù)
SELECT ln(10); -- 自然對(duì)數(shù)
SELECT log(100); -- 以10為底對(duì)數(shù)

（2）三角函數(shù)

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

（3）隨機(jī)數(shù)生成

SELECT random(); -- 0-1之間的隨機(jī)數(shù)
SELECT setseed(0.5); -- 設(shè)置隨機(jī)種子
-- 生成指定范圍隨機(jī)整數(shù)
SELECT floor(random() * 100)::int; -- 0-99隨機(jī)整數(shù)
SELECT (random() * (max-min) + min)::int; -- min-max隨機(jī)整數(shù)

2. 字符串函數(shù)

（1）字符串操作

-- 長度和位置
SELECT length('Hello'); -- 5
SELECT position('l' in 'Hello'); -- 3
SELECT strpos('Hello', 'l'); -- 3
-- 大小寫轉(zhuǎn)換
SELECT upper('hello'); -- HELLO
SELECT lower('HELLO'); -- hello
SELECT initcap('hello world'); -- Hello World
-- 修剪函數(shù)
SELECT trim('  hello  '); -- 'hello'
SELECT ltrim('  hello'); -- 'hello'
SELECT rtrim('hello  '); -- 'hello'
SELECT trim(BOTH 'x' FROM 'xxhelloxx'); -- 'hello'
-- 填充函數(shù)
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）格式化函數(shù)

-- 字符串連接
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'
-- 重復(fù)字符串
SELECT repeat('*', 5); -- '*****'

3. 日期時(shí)間函數(shù)

（1）當(dāng)前時(shí)間獲取

-- 獲取當(dāng)前時(shí)間
SELECT current_date; -- 當(dāng)前日期
SELECT current_time; -- 當(dāng)前時(shí)間
SELECT current_timestamp; -- 當(dāng)前時(shí)間戳
SELECT now(); -- 當(dāng)前時(shí)間戳（同current_timestamp）
SELECT localtimestamp; -- 本地時(shí)間戳
-- 帶精度的當(dāng)前時(shí)間
SELECT current_time(2); -- 當(dāng)前時(shí)間（2位小數(shù)秒）
SELECT current_timestamp(3); -- 當(dāng)前時(shí)間戳（3位小數(shù)秒）

（2）日期時(shí)間提取

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

（3）日期時(shí)間運(yùn)算

-- 日期加減
SELECT current_date + INTERVAL '1 day'; -- 明天
SELECT current_date - INTERVAL '1 week'; -- 一周前
SELECT current_timestamp + INTERVAL '2 hours 30 minutes'; -- 2小時(shí)30分鐘后
-- 年齡計(jì)算
SELECT age('2000-01-01'::date); -- 從2000-01-01到現(xiàn)在的年齡
SELECT age('2000-01-01'::date, '2020-01-01'::date); -- 兩個(gè)日期之間的年齡
-- 日期截?cái)?
SELECT date_trunc('hour', current_timestamp); -- 截?cái)嗟叫r(shí)
SELECT date_trunc('day', current_timestamp); -- 截?cái)嗟教?
SELECT date_trunc('month', current_timestamp); -- 截?cái)嗟皆?/pre>

（4）日期時(shí)間格式化

-- 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轉(zhuǎn)換
SELECT to_date('20230101', 'YYYYMMDD'); -- 2023-01-01
SELECT to_timestamp('20230101 143000', 'YYYYMMDD HH24MISS'); -- 2023-01-01 14:30:00

4. 條件函數(shù)

（1）條件判斷

-- CASE表達(dá)式
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（返回第一個(gè)非空值）
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. 聚合函數(shù)

（1）基本聚合函數(shù)

-- 計(jì)數(shù)
SELECT count(*) FROM table; -- 總行數(shù)
SELECT count(DISTINCT column) FROM table; -- 不重復(fù)計(jì)數(shù)
-- 求和與平均
SELECT sum(salary) FROM employees;
SELECT avg(salary) FROM employees;
-- 最大最小值
SELECT max(salary) FROM employees;
SELECT min(salary) FROM employees;
-- 統(tǒng)計(jì)信息
SELECT stddev(salary) FROM employees; -- 標(biāo)準(zhǔn)差
SELECT variance(salary) FROM employees; -- 方差

（2）高級(jí)聚合函數(shù)

-- 分組統(tǒng)計(jì)
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;
-- 數(shù)組聚合
SELECT 
    department,
    array_agg(name) as employees
FROM employees 
GROUP BY department;
-- JSON聚合
SELECT 
    department,
    json_agg(json_build_object('name', name, 'salary', salary)) as employees
FROM employees 
GROUP BY department;

6. 窗口函數(shù)

（1）排名函數(shù)

-- 行號(hào)、排名、密集排名
SELECT 
    name,
    salary,
    row_number() OVER (ORDER BY salary DESC) as row_num,
    rank() OVER (ORDER BY salary DESC) as rank,
    dense_rank() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;
-- 分區(qū)排名
SELECT 
    department,
    name,
    salary,
    rank() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

（2）前后值函數(shù)

-- 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）累計(jì)和移動(dòng)平均

-- 累計(jì)計(jì)算
SELECT 
    date,
    sales,
    sum(sales) OVER (ORDER BY date) as cumulative_sum,
    avg(sales) OVER (ORDER BY date) as cumulative_avg
FROM sales_data;
-- 移動(dòng)平均
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 函數(shù)（PostgreSQL 9.3+）

（1）JSON創(chuàng)建和解析

-- 創(chuàng)建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聚合

-- 行轉(zhuǎn)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;

三、實(shí)用SQL示例

1. 數(shù)據(jù)備份和恢復(fù)

# 備份單個(gè)數(shù)據(jù)庫
pg_dump -U username -d mydatabase -f backup.sql
# 備份所有數(shù)據(jù)庫
pg_dumpall -U username -f alldb_backup.sql
# 壓縮備份
pg_dump -U username -d mydatabase | gzip > backup.sql.gz
# 恢復(fù)數(shù)據(jù)庫
psql -U username -d mydatabase -f backup.sql
# 恢復(fù)壓縮備份
gunzip -c backup.sql.gz | psql -U username -d mydatabase

2. 性能分析

-- 查看查詢計(jì)劃
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. 實(shí)用管理查詢

-- 查看鎖信息
SELECT 
    locktype, 
    relation::regclass, 
    mode, 
    granted 
FROM pg_locks 
WHERE relation = 'mytable'::regclass;
-- 查看連接數(shù)
SELECT 
    datname, 
    count(*) as connections 
FROM pg_stat_activity 
GROUP BY datname;
-- 查看表統(tǒng)計(jì)信息
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables;

四、總結(jié)

PostgreSQL 提供了豐富的命令行工具和內(nèi)置函數(shù)，使得數(shù)據(jù)庫管理和數(shù)據(jù)操作變得非常高效。關(guān)鍵點(diǎn)包括：

psql命令行：熟練掌握連接參數(shù)和元命令，提高管理效率
數(shù)學(xué)函數(shù)：處理數(shù)值計(jì)算和統(tǒng)計(jì)分析
字符串函數(shù)：文本處理和格式化輸出
日期時(shí)間函數(shù)：時(shí)間計(jì)算和格式化
聚合函數(shù)：數(shù)據(jù)統(tǒng)計(jì)和分組分析
窗口函數(shù)：高級(jí)分析和排名計(jì)算
JSON函數(shù)：處理半結(jié)構(gòu)化數(shù)據(jù)

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线