1.密码强度设置:
进入mysql执行如下的sql语句:
密码的最小长度要求,设置密码长度大于等于8位;
set global validate_password_length=8;
密码策略为中等或更强,设置小写或大写字母的最小数量;
set global validate_password_mixed_case_count=1;
密码必须包含的数字个数;
set global validate_password_number_count=1;
密码必须包含的特殊字符个数;
set global validate_password_special_char_count=1;
密码有效期,天;
set global default_password_lifetime=90;
设置密码强度:
set global validate_password_policy=1;
0) LOW:只验证长度;
1) MEDIUM:验证长度、数字、大小写、特殊字符;
2) STRONG:验证长度、数字、大小写、特殊字符、字典文件;
查看密码策略:SHOW VARIABLES LIKE 'validate_password%';
用于重新加载授权表。当创建、修改、删除用户或者授权时,该命令会使更改生效:
FLUSH PRIVILEGES;
全局设置密码过期:(等于0时表示永不过期)
set global default_password_lifetime=90;
直接让用户密码过期:(mysql.user字段:password_expired变成了Y);
ALTER USER 'root'@'localhost' PASSWORD EXPIRE;
指定过期天数:(mysql.user字段:password_lifetime值变成了30)
ALTER USER ‘root’@‘localhost' PASSWORD EXPIRE INTERVAL 30 DAY;
设置密码永不过期:(mysql.user字段:password_lifetime值变成了0)
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
查看密码到期时间限制:
show variables like 'default_password_lifetime';
当password_lifetime(mysql.user表字段)的标记为null时,使用默认有效期,为default_password_lifetime参数控制
2.新建用户,授权
新建用户:CREATE USER 'lhb3'@'localhost'IDENTIFIED BY 'xxxx';
授权:GRANT ALL PRIVILEGES ON * . * TO ‘lhb’@’localhost’;
开放远程登录权限:
grant all privileges on hn_dev.* to lhb2@'%' identified by "Hn199325$" with grant option;
MySQL的各种语句:
查询更新时间大于今日零点【13位时间戳】的记录。
select * from xx【表名】 where updated_at > (SELECT CONCAT(UNIX_TIMESTAMP(CAST(SYSDATE() AS DATE)), '000'));
字段值的截取加替换【截掉最后一位字符,并将@替换为,】
select REPLACE(SUBSTR(tb_user.email,1,LENGTH(tb_user.email)-1), '@' , ',' ) declaration_unit_code from tb_user;
当查询的值【如decimal类型】为空时:补默认值0
select id,IFNULL(tb_address.level,0) `level` FROM tb_address;
关联查询相关信息时,有多条,取最后一条进行关联:
select
hn_ach_main.ach_name,
hac.change_status
from hn_ach_main
left join
(select ach_sid,change_status from hn_ach_change where (id,ach_sid) in
(select max(hac1.id),hac1.ach_sid from hn_ach_change hac1 group by hac1.ach_sid)) hac
on hac.ach_sid = hn_ach_main.sid
查询某个字段是否有重复记录
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;
mysql 13位时间戳转yyyy-MM-dd HH:mm:ss
select DATE_FORMAT(FROM_UNIXTIME(updated_at/1000),'%Y-%m-%d %H:%i:%s') FROM hn_ach_main;
select DATE_FORMAT(FROM_UNIXTIME(1723706132790/1000),'%Y-%m-%d %H:%i:%s') FROM dual;