mysql问题总结

1.navicate 连接mysql 

遇到Authentication plugin 'caching_sha2_password' cannot be loaded

解决办法：管理员权限运行命令提示符

1.1登陆MySQL（记得添加环境变量）

mysql -u root -p
password

1.2.修改账户密码加密规则并更新用户密码   

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;   #修改加密规则 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用户的密码

1.3.刷新权限

   FLUSH PRIVILEGES;   #刷新权限

1.4.单独重置密码命令：

alter user 'root'@'localhost' identified by '111111';

2.mysql使用总结

SELECT fullName,addedTime FROM t_user WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( addedTime, '%Y%m' ) ) =1;
 
-- 查询本季度数据
 
select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(now());
 
-- 查询上季度数据
 
select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
 
-- 查询本年数据
 
select fullName,addedTime FROM t_user where YEAR(addedTime)=YEAR(NOW());
 
-- 查询上年数据
 
select fullName,addedTime FROM t_user where year(addedTime)=year(date_sub(now(),interval 1 year));
 
-- 查询距离当前现在6个月的数据
 
select fullName,addedTime FROM t_user where addedTime between date_sub(now(),interval 6 month) and now();
 
-- 查询当前这周的数据
 
SELECT fullName,addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now());
 
-- 查询上周的数据
 
SELECT fullName,addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now())-1;
 
-- 查询上个月的数据
 
select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');
 
-- 查询当前月份的数据
 
select fullName,addedTime FROM t_user where DATE_FORMAT(addedTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
 
select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(now(),'%Y-%m');
 
-- 查询指定时间段的数据
 
select fullName,addedTime FROM t_user where addedTime between '2017-1-1 00:00:00' and '2018-1-1 00:00:00';
 
select fullName,addedTime FROM t_user where addedTime >='2017-1-1 00:00:00' and addedTime < '2018-1-1 00:00:00';

-- 今天
	 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
	 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';
 
-- 昨天
	 SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
	SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';
 
-- 上周
	 SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';
	SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';
 
-- 本周
	 SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS '本周一';
	 SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS '本周末';
-- 上面的本周算法会有问题,因为mysql是按照周日为一周第一天,如果当前是周日的话,会把时间定为到下一周.
	 SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';
	 SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';
 
-- 上月
	 SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初';
	 SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';
 
-- 本月
	 SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';
	 SELECT DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';