记录下关于数据库的一些常用命令

我比较熟悉的是mysql，但最近也开始接触起了postgresql，因为一些docker应用数据库用的是posgresql，当然我不是干DBA的，最多用到的还是基础命令，这里做个记录，以防老年痴呆忘了。。。

mysql

日期格式化

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');

%Y：四位数年份

%m：两位数月份

%d：两位数日

%H：小时（24小时制）

%i：分钟

%s：秒

数字格式化

1.保留小数点

SELECT ROUND(123.4567, 2);

保留两位小数

2.百分比

concat(round(min/sum*100,2),"%")

null值做加减乘除时处理

当对 NULL 值进行相加操作时，结果将始终是 NULL
处理方法：

COALESCE(salary, 0)

子查询+多表查询

select A.class A.sum, B.sum
from
(select class, count(1) as sum from (SUB_QUERY) S group by class) A
left join
(select class, count(1) as sum from (SUB_QUERY) S where S.score >= 60 group by class) B
on A.class = B.class

备份

mysqldump -uroot -p123456  database_name > database_backup.sql
mysqldump -h ip -P3306 -uroot -p123456  database_name > database_backup.sql    //备份远程数据库

恢复

mysql -uroot -p123456 database_name < database_backup.sql
mysql -h ip -P3306 -uroot -p123456  database_name < database_backup.sql   //恢复远程数据库

postgresql

备份

pg_dump -U postgres -d database_name > database_name.sql     //备份为纯sql脚本
pg_dump -U postgres -d database_name -Fc > database_name.dump  //备份为自定义压缩格式，体积更小，推荐用这种方式
pg_dump -h ip -p 5432 -U postgres -d database_name > database_name.sql    //备份远程数据库

恢复

psql -U username -d database_name < backup.sql      //恢复纯sql脚本的备份
pg_restore -h ip -U postgres -d database_name  database_name.dump   //恢复pg_dump的备份 

连接远程的数据库

psql -h ip -p 5432 -U root -d postgres   //连接到默认的postgres数据库

查看所有数据库

\l

创建数据库

createdb -h ip -U postgres mydatabase

删除数据库

dropdb -h ip -U postgres mydatabase

重置id

重置自增计数器

ALTER SEQUENCE students_id_seq RESTART WITH 1;

将students表的id字段重置为从1开始自增

null值处理

将null值转为0

COALESCE(column2, 0)

日期格式化

TO_CHAR(riqi,'YYYY-MM')

连接多个字符串

CONCAT(Hello, World)   //结果是Hello World

数字保留小数点

ROUND(sum(money) ::numeric,2)    //保留2位小数