一.常用语句
1.修改为postgresql用户: su - postgres
2.登录数据库: psql -p port -U username -d dbname
3.退出: \q
4.查看正在执行的语句:
select * from pg_stat_activity;
5.杀死某个pid:
SELECT pg_terminate_backend(pid);
6.批量杀死pid:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state='idle';
7.B表数据导入A表,A表存在:
INSERT INTO table_a (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM table_b;
8.B表数据导入A表,A表不存在:
select * into table_a from table_b;
9. 多表关联更新数据:
更新table_a中的code字段,table_a关联table_b,code字段出自table_c表
update table_a as a set code = tmp.code
from (
select b.id, c.code
from table_b as b
inner join table_c as c on b.id = c.id
) as tmp
where a.id = tmp.id
10.存在则更新,不存在则插入:
INSERT INTO test_postgre(id,name,,age,InputTime)
VALUES('1','postgre','24','2018-01-10 22:00:00')
ON conflict(id)
DO UPDATE SET name = 'postgreOk', InputTime ='2018-02-22 12:00:00'
注:id必须是唯一约束;
二.修改表常用语句
1.修改表名:
ALTER TABLE table_name RENAME TO new_name;
2.修改列名:
ALTER TABLE table_name RENAME column_name to new_column_name;
3.增加列:
ALTER TABLE table_name ADD column_name datatype;
4.删除列:
ALTER TABLE table_name DROP column_name;
5.修改列数据类型:
ALTER TABLE table_name ALTER column_name TYPE datatype;
6. 添加唯一约束:
alter table table_a add constraint unique_table_a_id unique(id);
注:必须保证id不重复
7.添加主键:
alter table table_a add primary key(id);
三.常用字符串函数
1.position(substring in string):判断string是否包含substring,包含返回大于0的位置,不包含返回0。
2.replace(string1,string2,string3):替换字符串,将string1中的string2替换成string3。
3.btrim(string string1 [, characters string2]):删除string1两侧的string2。
4.rtrim(string string1 [, characters string2]):删除string1右侧的string2。
5.ltrim(string string1 [, characters string2]):删除string1左侧的string2。
6.concat(str string1 [, str string2 [, ...] ]):拼接参数,个数不限,类型不限。
7.concat_ws(sep sep1, str string1 [,str string2 [, ...] ]):以sep1分隔符,拼接参数。
8.left(str string1, int n):返回string1前n个字符。
9.right(str string1, int n):返回string1后n个字符。
10.substr(string string1, from [, count]):从from开始截取string1字符串共截取count长度。
四.常用数值函数
1.ceil(num):取整数,取大值,例:ceil(35.7)=36。
2.floor(num):取整数,取小值,例:floor(35.7)=35。
3.round(numeric,int):四舍五入.后面的值为精度,如果后面的精度没有则取整,例:round(36.567,2)=36.57。
4.trunc(numeric,int):截断,后面的为精度,如果不设置精度则为取整,类似于floor,例:trunc(32.567,2)=32.56。
5.random():0.0到1.0之间的随机数。
五.常用日期函数
1.age(timestamp1,timestamp2):返回timestamp1减去timestamp2时间差,timestamp1为空则为当前时间。
2.now():等同于current_timestamp,当前事务的开启时间戳.在一个事务内时间保持不变。
3.date_trunc(text,timestamp):截断指定精度,例:
select date_trunc('day',current_timestamp),date_trunc('hour',current_timestamp);
date_trunc | date_trunc
------------------------+------------------------
2018-09-16 00:00:00+08 | 2018-09-16 11:00:00+08
4.date_part(text,timestamp):获取时间子域,例:
select date_part('hour',now()),date_part('minute',now()),date_part('month',now());
date_part | date_part | date_part
-----------+-----------+-----------
11 | 10 | 9
注:date_part函数常用参数:
- year:年
- quarter:第几季度
- month:月
- week:第几个星期
- dow:星期几,0是星期天
- day:本月第几天
- doy:本年第几天
- hour:得到时间中的小时
- minute:得到时间中的分钟
- second:得到时间中的秒
- epoch:相对于1970-01-01 00:00:00以来的时间