数据库函数
为了简化操作，数据库提供了大量的函数给程序员使用（比如你想输入当前时间，可以调用now()函数）
函数可以出现的位置：插入语句的values()中，更新语句中，删除语句中，查询语句中。
CASE表达式
相当于java中swtich语句
语法
SELECT
CASE [字段,值]
WHEN 判断条件1
THEN 希望的到的值1
WHEN 判断条件2
THEN 希望的到的值2
ELSE 前面条件都没有满足情况下得到的值
END as 别名
FROM table_name;字符串函数
函数：concat(s1,s2...sn) -- 常用。效果同使用 || 拼接字符串
描述：字符串 s1，s2 等多个字符串合并为一个字符串
实例：select concat("abc","-","def")
函数：trim(str) | ltrim(str) | rtrim(str) -- 常用
描述：字符串去掉两侧的空格 | 字符串去掉左侧的空格 | 字符串去掉右侧的空格
实例：select trim(" 数据 ")
函数：replace(s,s1,s2) -- 常用
描述：将字符串s中的s1字符替换成s2字符
实例：select replace("abc","b","x");
函数：substr(s, start, length) -- 常用
描述：从字符串 s 的 start 位置截取长度为 length 的子字符串
注意：索引从1开始(包含头)
实例：select substr("abcdefg",2,3);
函数：ucase(s) | upper(s)
描述：将字符串转换为大写
实例：select ucase("aaa")
函数：lcase(s) | lower(s)
描述：将字符串转换为小写
实例：select lcase("aaa")
函数：char_length(str)
描述：返回字符串 str 的字符数
实例：select char_length("hello,树先生");
函数：length(str)
描述：返回字符串 s 的字节数
（一个中文字符占3个字节 中文标点占3个字节 英文字符和英文标点都占一个字节）
实例：select length("hello,树先生");
函数：locate(s1,s)
描述：从字符串 s 中获取 s1 的开始位置
注意：索引从1开始
实例：select locate("l","hello");
函数：strcmp(str1,str2)
描述：比较字符串大小，左大于右边返回1，左等于右边返回0，左小于于右边返回-1，
实例：select strcmp("a","b");数值函数
描述：四舍五入
函数：round(x) -- 常用。四舍五入成整数
函数：round(x,d) -- x指要处理的数，d是指保留几位小数
实例：round(1.5)
函数：ceil(x) | floor(x) --常用
描述：向上（下）取整
实例：ceil(1.2) floor(1.2)
函数：truncate(x,y)
描述：将X按照D长度在小数点后按照位数直接进行截取。-- MySQL数据库
实例：truncate(1.23456,2);
函数：substring(string [from int] [for int])
描述：抽取子字串。-- Postgresql数据库
实例：substring('Thomas' from 2 for 3)
函数：abs(x)
描述：返回 x 的绝对值
实例：abs(-1) -- 负变正
函数：mod(x,y)
描述：返回x mod y的结果，取余
实例：mod(10,9) -- 余数：1
描述：返回 0 到 1 的随机数
-- MySQL
函数：rand()
-- Postgresql
函数：random()
-- Oracle
函数：dbms_random.value()
函数：dbms_random.value(a,b) -- a为下限，b为上限，生成下限到上限之间的随机数，但不包含上限聚合函数
常用于group by从句的select查询中
avg(col) -- 返回指定列的平均值
count(col) -- 返回指定列中非null值的个数
min(col) -- 返回指定列的最小值
max(col) -- 返回指定列的最大值
sum(col) -- 返回指定列的所有值之和
-- 返回由属于一组的列值使用逗号进行隔开拼接组合而成的结果
group_concat(col) -- MySQL
wm_concat() -- Oracle
string_agg(字段,',') -- Postgresql高阶函数
多列记录聚合为一列记录
Oracle数据库
-- LISTAGG()函数：将多列记录聚合为一列记录，可以指定分隔符
-- 语法：
listagg(measure_expr，delimiter) within group (order by order_by_clause);
-- 解释：
measure_expr -- 可以是基于任何列的表达式
delimiter -- 分隔符，不填默认为NULL（即没有分隔符）
order_by_clause -- 决定了列值的拼接顺序
-- 示例
select listagg(name,’,’)within group(order by id) from test;
select id,listagg(name,’,’)within group(order by id) from test group by id;
-- vm_concat()函数：将多列记录聚合为一列记录，以 , 分隔
-- 示例：
select id,to_char(wm_concat(name)) as name from test group by id;
-- 注意：
-- 1.在10g以及11g中该函数可以正常使用，但是在10g和11g中需要注意的是，vm_concat返回的数据类型是不同的
-- 详见：https://blog.csdn.net/baoyuhang0/article/details/110160431
-- 2.Oracle不建议使用vm_concat函数，该函数为系统内部使用。Oracle建议使用分析函数LISTAGG来代替vm_concat函数的使用MySQL数据库
-- group_concat()函数：将多列记录聚合为一列记录，可以指定分隔符
-- 语法：
group_concat([distinct] 要连接的字段 [order by asc/desc 排序字段] [, '分隔符'])
-- 示例：以id分组，把name字段的值打印在一行，逗号分隔(默认)
select id, group_concat(name) from aa group by id;
-- 示例：以id分组，把name字段的值打印在一行，分号分隔
select id, group_concat(name separator ';') from aa group by id;
-- 示例：以id分组，把去冗余的name字段的值打印在一行
select id, group_concat(distinct name) from aa group by id;
-- 示例：以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序
select id,group_concat(name order by name desc) from aa group by id;PostgreSQL数据库
-- string_agg()函数：将多列记录聚合为一列记录，必须指定分隔符
-- 语法：
string_agg(expression, '分隔符' [order by asc/desc 排序字段])
-- 示例：
select string_agg(name,’,’) from test group by id;
select deptno, string_agg(name,’,’ order by name desc) from test group by deptno;
-- 注意：
-- 注意的是：
-- 1.order by 语句需要写在分隔符的后面，这是PostgreSql的语句；
-- 2.order by 后面的字段不受整个语句的 group by 语句后面的字段的限制，可以是ename，可以ssal等等，只是不同字段的排序ename的合并顺序会随之改变；空值的转换
Oracle数据库
-- NVL()函数：实现空值的转换
-- 语法：
NVL(string1, replace_with)
-- 解释：
-- 当第一个参数（string1）为空时，返回第二个参数（replace_with）
-- 当第一个参数（string1）不为空时，则返回第一个参数（string1）
-- 注意：NVL()函数的第一个参数和第二个参数类型必须相同，或者可以由隐式转换得到
-- 拓展：NVL2()函数
-- 语法：
NVL2(E1, E2, E3)
-- 解释：当E1为NULL时，返回E3；当E1不为NULL时，返回E2。MySQL数据库
-- IFNULL()函数
-- 语法：
IFNULL(expr1, expr2)
-- 解释：
-- 如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2
-- IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境PostgreSQL数据库
-- coalesce()函数
-- 语法：
coalesce(expr1, expr2)
-- 解释：
-- 如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2<be/>
获取两个日期的时间间隔
Oracle数据库
extract(second from date1-date2)MySQL数据库
-- datediff()函数：获取两个日期的时间间隔
-- 语法：
datediff(date1, date2)
-- 解释：
-- 返回 date1 - date2 的天数（只取日期部分计算）PostgreSQL数据库
-- age()函数：对时间进行计算，得到interval。使用extract epoch对interval 转换得到秒。
-- 使用示例：
extract(epoch from age(TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD'), TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD')))
extract(epoch from date1-date2)MySQL数据库
菜鸟教程：https://www.runoob.com/mysql/mysql-functions.html
日期函数
函数：now() | curdate() | curtime() --常用
描述：获取系统当前日期时间、日期、时间
实例：select now() select curdate() select curtime()
函数：adddate(date,n) | subdate(date,n) --常用
描述：计算起始日期 date 加（减） n 天的日期
实例：select adddate(now(),7); select subdate(now(),7)
函数：datediff(d1,d2) --常用
描述：计算日期之间相隔的天数
实例：select datediff(now(),"1989-10-02");
函数：date_format(d,f) --常用
描述：按表达式f的要求显示日期 d
实例：select date_format(now(),"%Y-%m-%d %H:%i:%s %p");
函数：year(date) | month(date) | day(date)
描述：从日期中选择出年、月、日
实例：select year(now()) select month(now()) select day(now())
函数：last_day(date)
描述：返回月份的最后一天
实例：select last_day(now())
函数：quarter(date)
描述：返回日期 date 是第几季节，返回 1 到 4
实例：select quarter(now())IF 表达式
相当于java中三元运算符
语法
SELECT IF(1 > 0,'真','假') from 表名;Oracle数据库
日期函数
-- 获取系统当前时间
sysdate
-- 实例
select sysdate from dual;
-- 获取系统当前时间并格式化为字符串
to_char(sysdate,'yyyy-MM-dd HH24:mi:ss')
-- 把字符串解析成时间
to_date('2015-08-12 15:53:37','yyyy-MM-dd HH24:mi:ss)
-- 当前时间减去7分钟
select sysdate - interval '7' minute from dual
-- 表示当前时间减去7小时
select sysdate - interval '7' hour from dual
-- 表示当前时间减去7年
select sysdate - interval '7' year from dual -- 时间转换中字符的意义
表示year的：
y 表示年的最后一位
yy 表示年的最后2位
yyy 表示年的最后3位
yyyy 用4位数表示年
表示month的：
mm 用2位数字表示月
mon 用简写形式， 比如：11月 或者 nov
month 用全称， 比如：11月 或者 november
表示day的：
dd 表示当月第几天
ddd 表示当年第几天
dy 当周第几天，简写， 比如：星期五 或者 fri
day 当周第几天，全称， 比如：星期五 或者 friday
表示hour的：
hh HH HH12 表示小时 12进制
hh24 HH24 表示小时 24小时
表示minute的：
mi 2位数表示分钟
表示second的：
ss 2位数表示秒 60进制
表示季度的：
q 1位数表示季度 (1-4)
表示周的：
w 表示当月月的第几周
ww 用来表示当年第几周 w用来表示当月第几周。decode函数
decode(条件, 值1, 返回值1, 值2, 返回值2, … 值n, 返回值n, 缺省值)
-- 含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IFMySQL和Postgresql数据库没有decode函数，可用CASE表达式替代。
Postgresql数据库
日期函数
-- 返回当前实际的时间，即使在同一个 SQL 语句中也可能返回不同的值
clock_timestamp()
-- 获取当前事务开始的完整时间，包括时区，秒也保留到了6位小数。在同一个事务期间，多次调用相同的函数将会返回相同的值
now()
current_timestamp
transaction_timestamp()
-- 实例。得到的结果：'2014-12-24 09:28:31.545145+08'
select now();
-- 只显示当前的时间，不包括日期
current_time
-- 实例
select current_time;
-- 只显示当前的日期，不包括小时等信息
current_date
-- 实例
select current_date;
-- 获取系统当前时间戳并格式化为字符串
to_char(clock_timestamp(),'yyyy-MM-dd HH24:mi:ss')
-- 把时间戳转换成字符串
to_timestamp('2014-12-24 09:28:31', 'yyyy-MM-dd HH24:mi:ss')if 表达式
-- 示例1
DO
$do$
BEGIN
IF (SELECT count(1) AS num FROM orders) = 0
THEN
DELETE FROM orders;
ELSE
INSERT INTO orders VALUES (1,2,3);
END IF;
END;
$do$;
-- 示例2
DO
$do$
BEGIN
IF EXISTS (SELECT 1 FROM orders)
THEN
DELETE FROM orders;
ELSE
INSERT INTO orders VALUES (1,2,3);
END IF;
END;
$do$;for 循环
for...in 循环
- 这种形式的 for 会创建一个在一个整数范围上迭代的循环。
- 变量 name 会自动定义为类型 integer，并且只在循环内存在（任何该变量名的现有定义在此循环内都将被忽略）。
- 给出范围上下界的两个表达式在进入循环的时候计算一次。
- 如果没有指定BY子句，迭代步长为 1，否则步长是BY中指定的值，该值也只在循环进入时计算一次。如
- 果指定了 REVERSE，那么在每次迭代后步长值会被减除而不是增加。
语法：
[ <> ] FOR target IN query LOOP
statements
END LOOP [ label ];示例：
FOR i IN 1..10 LOOP
-- 我在循环中将取值 1,2,3,4,5,6,7,8,9,10
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- 我在循环中将取值 10,9,8,7,6,5,4,3,2,1
END LOOP;
FOR i IN REVERSE 10..1 BY 2 LOOP
-- 我在循环中将取值 10,8,6,4,2
END LOOP;数组循环。示例：
create or replace function test_for_in() returns void as $$
DECLARE
tmp_arr varchar ARRAY:= ARRAY['a', 'b'];
BEGIN
FOR i IN 1...array_length(tmp_arr, 1)
LOOP
update user set age='15' where name=tmp_arr[i]
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- 测试
select test_for_in();通过一个查询的结果进行迭代并且操纵相应的数据。语法：
[ <> ] FOR target IN query LOOP
statements
END LOOP [ label ];示例：
create or replace function test_for_in() returns int as $$
DECLARE
cddm record;
BEGIN
RAISE NOTICE 'reading jcb_cddm...';
FOR cddm IN SELECT * FROM jcb_cddm limit 5
LOOP
RAISE NOTICE '场地代码为：%，场地名称为 %', cddm.dm, quote_ident(cddm.mc);
END LOOP;
return 1;
END;
$$ LANGUAGE plpgsql;数据备份与还原
mysqldump：MySQL数据库备份
mysqldump命令是MySQL数据库中备份工具，用于将MySQL服务器中的数据库以标准的sql语言的方式导出，并保存到文件中。
语法格式:
mysqldump [选项] 数据库名 [表名] > 脚本名
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
mysqldump [选项] --all-databases [选项] > 脚本名
# 常用参数：
-h, --host 服务器IP地址
-P. --port 服务器端口号
-u, --user MySQL 用户名
-p, --pasword MySQL 密码
--databases 指定要备份的数据库
--all-databases 备份mysql服务器上的所有数据库
--compact 压缩模式，产生更少的输出
--comments 添加注释信息
--complete-insert 输出完成的插入语句
--lock-tables 备份前，锁定所有数据库表
--no-create-db/--no-create-info 禁止生成创建数据库语句
--force 当出现错误时仍然继续备份操作
--default-character-set 指定默认字符集
--add-locks 备份数据库表时锁定数据库表
--ignore-table 备份数据时库排除某表参考实例：
# 导出整个数据库：
mysqldump -u linuxcool -p smgp_apps_linuxcool > linuxcool.sql
# 导出指定的数据库：
mysqldump -u linuxcool -p smgp_apps_linuxcool users > linuxcool_users.sql
# 备份指定数据库指定表(多个表以空格间隔)
mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.sql
# 导出一个数据库结构：
mysqldump -u linuxcool -p -d --add-drop-table smgp_apps_linuxcool > linuxcool_db.sql
# 备份指定数据库排除某些表
mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.sql备份还原命令：
mysqladmin -uroot -p create db_name
mysql -u root -p password db_name < /backup/mysqldump/db_name.db
# 注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入。k8s部署 定时备份mysql数据库及还原命令：
# 创建定时任务
crontab -e
# 定时任务（每天0点执行）：
0 0 * * * /usr/local/bin/kubectl exec `/usr/local/bin/kubectl get po | grep mysql | awk '{print $1}'` -- mysqldump -u user -p password db_name > /root/mysql-bak/`date + \%d`.sql 2>&1
# 注：2>&1 的意思就是将标准错误重定向到标准输出。这里标准输出已经重定向到了~.sql。那么标准错误也会输出到~.sql文件里
# 还原命令：
/usr/local/bin/kubectl exec `/usr/local/bin/kubectl get po | grep mysql | awk '{print $1}'` -- mysql -u user -p password db_name < /root/mysql-bak/`date + \%d`.sql
