Postgresql数据库中的SQL语句总结

1、数据分页（limit)

select * from 表名 limit 取长度 offset 偏移量（0开始）

select * from tableName limit 10 offset 0

效果图如下：

2、单一字段分组统计（group by）

SELECT 分组值列, 统计数列 FROM 表名 t GROUP BY 字段一

SELECT  t.field1 as field1, sum(1) as len FROM tableName t GROUP BY t.field1

效果图如下：

3、 组合字段分组统计（group by 和 having组合）

SELECT 分组值列,过滤字段, 统计数列 FROM table t GROUP BY 字段一，过滤字段 HAVING 过滤字段 = ‘过滤值’

SELECT t.field1 as field1,t.field2 as field2, sum(1) as len FROM tableName t GROUP BY t.field1,t.field2 HAVING t.field2 = '过滤值' 

效果图如下：

4、 备份语句

pg_dump -h 127.0.0.1 --port 5432 --username "postgres" --role "postgres" --dbname "em"  --format custom --blobs -W --encoding UTF8 --verbose --file "/var/lib/pgsql/11/backups/em_edata.backup" --schema "edata"


// psql 登录
psql -U 用户名 -h ip -p 端口 -d 数据库名
psql -U postgres -h 127.0.0.1 -p 5432 -d em

// 删除模式(登录psql)
DROP SCHEMA 模式名 CASCADE;

// 授权
-- 授予用户user_name对模式schema_name的使用权限
GRANT USAGE ON SCHEMA schema_name TO user_name;
 
-- 授予用户user_name对模式schema_name中所有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO user_name;
 
-- 授予用户user_name对模式schema_name中所有序列的使用权限
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO user_name;
 
-- 授予用户user_name对模式schema_name中所有函数的执行权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schema_name TO user_name;
// 数据库备份命令

pg_dump  -U 用户名 -h ip -p 端口 数据库名 > 备份文件名称
pg_dump  -U postgres -h 192.168.0.1 -p 5432 em > em_backup_file_20230613

// 恢复命令
./psql  -U 用户名 -h ip -p 端口 -d pre_gstanze -f  备份文件名称
./psql  -U postgres -h 192.168.0.1 -p 5432 -d em -f  em_backup_file_20230613


--备份schema
pg_dump -U 用户名 -h ip -p 端口 -d pre_gstanze -f  -d 数据库名-n 模式名> 备份文件名称
pg_dump -U postgres -h 192.168.0.1 -p 5432 -d em -n stdb > em_stdb_backup_file_20230613

--恢复schema
./psql em_stdb_backup_file_20230613

-- 自定义导出格式
pg_dump -U 用户名 -h ip -p 端口 -n 模式名 -d 数据库名称 --format custom --blobs  --encoding UTF8 --verbose --file "备份文件路径"
pg_dump -U postgres  -h 127.0.0.1 -p 5432 -n data -d dbname --format custom --blobs  --encoding UTF8 --verbose --file "/home/data.backup"

-- 自定义格式导入
pg_restore  --dbname=postgresql://用户名:密码@ip:端口/数据名 --role "postgres"    --verbose "备份文件路径"  > 日志路径 2>&1
pg_restore  --dbname=postgresql://postgres:postgres@127.0.0.1:5432/dbname --role "postgres"    --verbose "/home/data.backup"  > /var/lib/pgsql/db.log 2>&1

// 修改数据库的账户密码
psql -U postgres
ALTER USER postgres WITH PASSWORD ‘new_password’;

5、 表及字段说明

SELECT T
	.TABLE_NAME 表名,
	T.table_schema 表说明,
	C.COLUMN_NAME 字段名,
	C.table_schema 字段说明 
FROM
	information_schema.tables T,
	INFORMATION_SCHEMA.COLUMNS C 
WHERE
	C.TABLE_NAME = T.TABLE_NAME 
	AND T.table_schema = 'edss_fangxun';
	
SELECT
	relname AS tabname,
	A.attname AS NAME,
	col_description ( A.attrelid, A.attnum ) AS COMMENT,
	format_type ( A.atttypid, A.atttypmod ) AS TYPE,
	A.attnotnull AS NOTNULL 
FROM
	pg_class AS C,
	pg_attribute AS A 
WHERE
	C.relname LIKE'bas%' 
	AND A.attrelid = C.oid 
	AND A.attnum > 0

6、

SELECT
	pac,
	CONCAT ( "level", '_24h' ) AS LEVEL,
	COUNT ( "level" ) AS counts 
FROM
	"tablename" 
WHERE
	time >= NOW( ) - INTERVAL '1 days' 
GROUP BY
	pac,
	CONCAT ( "level", '_24h' ) 
	) UNION ALL
	(
	SELECT
		pac,
		CONCAT ( "level", '_7d' ) AS LEVEL,
		COUNT ( "level" ) AS counts 
	FROM
		"tablename" 
	WHERE
		time >= NOW( ) - INTERVAL '7 days' 
	GROUP BY
		pac,
		CONCAT ( "level", '_7d' ) 
	) UNION ALL
	(
	SELECT
		pac,
		CONCAT ( "level", '_30d' ) AS LEVEL,
		COUNT ( "level" ) AS counts 
	FROM
		"tablename" 
	WHERE
		time >= NOW( ) - INTERVAL '30 days' 
	GROUP BY
		pac,
		CONCAT ( "level", '_30d' ) 
	) UNION ALL
	(
	SELECT
		pac,
		CONCAT ( "level", '_365d' ) AS LEVEL,
		COUNT ( "level" ) AS counts 
	FROM
		"tablename" 
	WHERE
		time >= NOW( ) - INTERVAL '365 days' 
	GROUP BY
	pac,
	CONCAT ( "level", '_365d' )

7、获取类型为20到24的telephone总数，并去重

SELECT
        COUNT(DISTINCT telephone) 
    FROM
        grid_person 
    WHERE
        person_type IN (
            '20','21','22','23','24'
        )