• 在对应的字段上拼接 \t 即可

select id,mobile,`name`,concat("\t",idcard) as idcard from users

• 如果用户无授权, 给的是拼接小星星的字符串

select id
,IF(mobile IS NULL,'',CONCAT(LEFT(mobile,3),'******',RIGHT(mobile,2))) AS mobile
,IF(name IS NULL,'',CONCAT(LEFT(name,1),'**')) AS name
,IF(idcard IS NULL,'',CONCAT(LEFT(idcard,3),'******',RIGHT(idcard,2))) AS idcard 
from users

• 将身份证除了前面6位和最后4位显示, 其余替换为*

SELECT INSERT('1234567890abcdefg',7,LENGTH('1234567890abcdefg')-10,REPEAT('*',LENGTH('1234567890abcdefg')-10))

时间范围

• （当数据库字段中存储的是yyyy-MM-dd格式，即date类型；用between and查询参数yyyy-MM-dd格式时，包含头尾，相当于x>=y && x<=z.
• 当是yyyy-MM-dd HH:mm:ss格式，即datetime类型；用between and查询参数yyyy-MM-dd  HH:mm:ss格式时，包含头尾，x>=y && x<=z。参数yyyy-MM-dd格式时，只包含头，相当于x>=y && x<z。）

查询参数格式与数据库类型相对应时，between and包含头尾，否则依情况

case when 自定义排序时的使用

CASE 
    WHEN t2.status = 4
    AND t2.expire_time>UNIX_TIMESTAMP()
    AND t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 DAY)) THEN 4
        WHEN `status` = 2  THEN 3
        WHEN `status` = 3 THEN 2
        WHEN t2.status = 4
        AND t2.expire_time>UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 DAY))
        AND t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 1 YEAR)) THEN 1
    ELSE 0
    END sort     
$query->orderBy('sort desc ,t2.expire_time desc,t2.created_at desc');

当colume 与condition 条件相等时结果为result

case colume 
    when condition then result
    when condition then result
    when condition then result
else result
end

当满足某一条件时，执行某一result

case  
    when condition then result
    when condition then result
    when condition then result
else result
end

• 转换为字符串

CAST(1234 AS CHAR)

• 转换为decimal

CAST('123.45678' as DECIMAL(10,2))

• 转换为整型

CAST('123' as INT)

• 登录

mysql -uroot -proot

• 命令窗口添加账户

create user 'dev'@'%' identified by '654321';

参考文档: https://www.cnblogs.com/dreamofprovence/articles/11672172.html