mysql数据库部分函数使用示例

1， 环境：mysql5.7+

2，图形化界面工具：dbeaver

3，代码实例：

# ---------------------------------------------------- #
# ---------------------------------------------------- #
# -----------------MySQL数据库学习：操作表的聚合函数----------- #
# ---------------------------------------------------- #
# ---------------------------------------------------- #
select 
    avg(stu_age) as avg_age, 
    sum(stu_age) as sum_age, 
    max(stu_age) as max_age, 
    min(stu_age) as min_age, 
    count(stu_age) as numbers 
from student;


# 数据库不区分大小写匹配规则。
select * from student where stu_name like 'zhongl'; -- 不区分大小写。


# ---------------------------------------------------- #
# ---------------------------------------------------- #
# -----------------MySQL数据库学习：基本运算符--------- ------ #
# ---------------------------------------------------- #
# ---------------------------------------------------- #

# 整数的基本运算：
select 12 * 23; -- 276
select 13 - 23; -- -10
select 12 + 23; -- 35
select 50 / 2; -- 25
select 10 % 3; -- 1

# 浮点型数据的基本运算(保留6位有效数字)
select 12.5 * 2; -- 25
select 12.5 / 2; -- 6.25
select 12.5 % 2; -- 0.5
select 12.5 + 2; -- 14.5
select 12.5 - 2; -- 10.5
select 12.5 + 2.523; -- 15.023
select 12.5 - 2.523; -- 9.977
select 12.5 * 2.523; -- 31.5375
select 12.5 / 2.523; -- 4.95442
select 10.00 / 3; -- 3.333333

# ---------------------------------------------------- #
# ---------------------------------------------------- #
# -----------------MySQL数据库学习：内置函数----------------- #
# ---------------------------------------------------- #
# ---------------------------------------------------- #

# 一、数字类函数
# 1.生成随机数：rand()
select rand() * 1000; -- 0~1000之间取有效数为三位数的浮点型数据
## 举例：随机从数据表中得到任意两位数据
-- 第一种方式：
select * from student where rand() limit 2;
-- 第二种方式：
select * from student order by rand() limit 2;

# 2.四舍五入：round()
-- 取整
select round(23.454556); -- 23
select round(23.454556, 0); -- 23
-- 指定保留几位有效数字
select round(23.454556, 3); -- 23.455
select round(23.454556, 6); -- 23.454556
select round(23.4545532324342343246, 10); -- 23.4545532324
select round(23.4545532324342343246, 100); -- 23.454553232434234324600000000000 最多保留32位

# 3.向上取整或向下取整：ceil(floatNum, precision), floor(floatNum, precision)
select ceil(12.434); -- 13
select floor(12.879); -- 12

# 4.截取：truncate(数字，截取小数位数)
select truncate(12.545454, 2); -- 12.54
select truncate(12.343435454545454, 6); -- 12.343435

# 5.运算函数：mod(x,y)返回触发操作的余数
select mod(10, 3); -- 1


# 二、字符串类函数
# 1.转成大写
select ucase('hello,world!'); -- HELLO,WORLD!

# 2.转成小写
select lcase('HELLO,WORLD!'); -- hello,world!

# 3.截取字符串 ：从左往右截取left(字符串,起始位置从1开始的截取字符个数)；从右往左截取right(字符串,起始位置从1开始的截取字符个数)
select left('hello,world!', 0); -- 空字符串
select left('hello,world!', 5); -- hello
select right('hello,world!', 0); -- 空字符串
select right('hello,world!', 6); -- world!
-- 截取部分子字符串： mid(str,start[, end])
select mid('hello', 1, 2); -- he
-- instr(str,char)：char字符在str字符中出现的第一个位置，没有匹配则返回0
select instr('hello', 'he'); -- 1
select instr('hello', 'l'); -- 3
select instr('hello', 'lg'); -- 0

# 4.指定起始位置开始截取：substring(字符串，起始位置，截取长度) 注意：下标从1开始，截取长度不能为负数，否则得到一个空字符串。
select substring('hello,world!', 1, 5); -- hello
select substring('hello,world!', 1, -5); -- 空字符串
-- 如果起始位置是负数，则从右往左自动截取起始位置绝对值的个数
select substring('hello,world!', -6); -- world!

# 5.字符串的拼接:concat(params,……)函数
select concat('hello',',','world!'); -- hello,world!

# 6.空字符默认替换值函数coalesce(字段1, 替换的字符串)
# 7.字符串的长度：length(str);
select length('hello!'); -- 6


# 三、时间类函数
# 1.获取时间戳：unix_timestamp();
select unix_timestamp(); -- 例如：可以得到 1564886666
# 2.将时间戳转成datetime：from_unixtime(unix_timestamp())
select from_unixtime(1564886666); -- 2019-08-04 10:44:26
# 3.获取当前时间datetime: now()
select now(); -- 例如： 2019-08-04 10:46:13
# 4.单独从now()得到年月日时分秒；
select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now()));
# 5.得到星期名称，月份名称：
select dayname(now()),monthname(now()); -- 例如：Sunday   August
# 6.得到now()当前日期是本月的第一天，本周的第几天，本年的第几天。
select dayofmonth(now()),dayofweek(now()), dayofyear(now()); -- 例如：4   1   216
# 7.比较两个日期相差多少天
select datediff(now(),'2008-8-8'), datediff(now(),now()); -- 4013   0
# 8.DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
select date_format('2019-08-04','%y'); -- 19
select date_format('2019-08-04','%Y-%M-%D %W(%w)'); -- 2019-August-4th Sunday(0)  （注意：0表示周当中的第几天）
# 9.转换
select convert(now(), date); -- 2019-08-04 （默认格式）
select convert(now(), datetime); -- 2019-08-04 11:00:17


# 四、加密函数（不可逆）： md5()、sha()
select md5('username'); -- 14c4b06b824ec593239362517f538b29
select sha('username'); -- 249ba36000029bbe97499c03db5a9001f6b734ec
-- select encode(密码,自定义的秘钥);
-- select decode(字段,自定义的秘钥);
select password('1234567890'); -- *6B5EDDE567F4F29018862811195DBD14B8ADDD2A
-- password函数旧版16位，新版41位，可用select length(password('123456'))察看。
-- password函数加密不可逆，如果和数据库里加密后内容比较时可以采用password(pwd)==字段内容的方式；
-- md5函数加密后32位，此加密算法不可逆，其实md5算法是信息摘要算法，如果拿来做压缩也是有损压缩，
-- 理论上即使有反向算法也无法恢复信息原样。他常被用来检验下载数据的完整性。如好多软件都提供md5码，供用户下载完毕校验完整性。


# 五、判断函数
select if(12 < 20, '12小于20', '12大于20'); -- 12小于20

操作语句亲测有效。