1.DATE_ADD 修改时间
DATE_ADD(NOW(),INTERVAL 1 MONTH)
修改现在时间加一个月
SELECT(creattime,date_add(creatime,INTERVAL 1 DAY))as time from table1 where id=1;
修改现在时间加一天
SELECT * FROM table1 WHERE day(order_time)=day(DATE_ADD("2021-04-26",INTERVAL -1 DAY));
查询2021-04-26前一天的的订单明细
AND NOW() <![CDATA[ < ]]> DATE_ADD(vm.end_time, INTERVAL 1 DAY)
在XML中只有”<”和”&”是非法的 <![CDATA[<]]>表示文本内容“<”
2.CONCAT()追加数据
mysql向表中某字段后追加一段字符串（field为字段名）:
update table1 set field=CONCAT(field,'str',)
mysql 向表中某字段前加字符串
update table1 set field=CONCAT('str',field)
3.case 条件参数 when 参数值1 then 返回值1
1.colume等于condition1时返回result1
case colume
when condition1 then result1
when condition2 then result2
when condition3 then result3
else result
end
2.当满足某一条件时，执行某一result
case
when condition1 then result1
when condition2 then result2
when condition3 then result3
else result4
end
3.当满足某一条件时，执行某一result,把该结果赋值到new_column_name 字段中
case
when condition1 then result1
when condition2 then result2
when condition3 then result3
else result4
end new_column_name
4.FIND_IN_SET(str,strlist)
1.str 要查询的字符串 strlist 字段名 参数以”,”分隔 如 (1,2,6,8)
mysql> SELECT FIND_IN_SET('b', 'a,b,c,d'); -> 2 因为b 在strlist集合中放在2的位置 从1开始
select FIND_IN_SET('1', '1'); 返回1
select FIND_IN_SET('2', '1，2'); 返回2
select FIND_IN_SET('6', '1'); 返回0
2.所以如果list是常量，则可以直接用IN， 否则要用find_in_set()函数。
SELECT id,name,list from tb_test WHERE 'daodao' IN(list); -- 不可以
SELECT id,name,list from tb_test WHERE 'daodao' IN ('libk', 'zyfon', 'daodao'); -- 可以
SELECT id,name,list from tb_test WHERE FIND_IN_SET('daodao',list); -- 可以
3.其中limits表示用户所拥有的权限字段（以逗号分隔），现在想查询拥有权限编号为2的用户
SELECT * FROM users WHERE FIND_IN_SET(2,limits);
like是广泛的模糊匹配，字符串中没有分隔符，Find_IN_SET 是精确匹配，字段值以英文”,”分隔，Find_IN_SET查询的结果要小于like查询的结果。
5.类型转换之 MySQL 的CAST()和CONVERT()函数
相同的两种写法：
CAST(value as type)
CONVERT(value, type)
type:
BINARY 二进制，同带binary前缀的效果
CHAR() 字符型，可带参数
DATE 日期
TIME 时间
DATETIME 日期时间型
DECIMAL 浮点数
SIGNED 整数
UNSIGNED 无符号整数
例如：
CAST(`3.11` as singed)
--> 3
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(8))
--> 0x0000000100000000
CAST( SYSDATE(-1) AS DATE )))
--> 2021-05-16
1.安装与启动
#指定版本安装
brew install mysql@5.7
#配置环境变量
echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile
source ~/.bash_profile
#启动服务
mysql.server start
brew services start mysql@5.7
#初始化，设置密码
mysql_secure_installation
[root@zhangMySQL5711 bin]# mysql_secure_installation
Enter password:
Securing the MySQL server deployment.
VALIDATE PASSWORD PLUGIN can be used to test passwords //密码验证插件，为了提高安全性，需要验证密码
and improve security. It checks the strength of password // 它会检查密码的强度
and allows the users to set only those passwords which are //只允许用户设置足够安全的密码
secure enough. Would you like to setup VALIDATE PASSWORD plugin? //提示安装密码验证插件
Press y|Y for Yes, any other key for No: y
There are three levels of password validation policy: //三个等级的验证策略
LOW Length >= 8 //最小长度大于等于8个字符
MEDIUM Length >= 8, numeric, mixed case, and special characters //数字，字母，特殊字符 混合，具体的应该是至少1个数字，1个字母，1个特殊字符，长度不超过32个字符
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file // 最严格，加上了，字典文件
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 //这里我选择2 MEDIUM
Using existing password for root.
Estimated strength of the password: 50 //这里也是密码强度的评级
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
New password: //密码
Re-enter new password:
Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y //提示要使用刚刚输入的密码吗？
... Failed! Error: Your password does not satisfy the current policy requirements //插件验证不通过，不符合当前安全要求级别
New password: //密码
Re-enter new password:
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user, //默认情况下，MySQL有一个匿名用户，
allowing anyone to log into MySQL without having to have //这个匿名用户，不必有一个用户为他们创建，匿名用户允许任何人登录到MySQL，
a user account created for them. This is intended only for //这只是为了方便测试使用
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production //在正式环境使用的时候，建议你移除它
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y //提示移除匿名用户
Success.
Normally, root should only be allowed to connect from //一般情况下，root用户只允许使用"localhost"方式登录，
'localhost'. This ensures that someone cannot guess at // 以此确保，不能被某些人通过网络的方式访问
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n //
... skipping.
By default, MySQL comes with a database named 'test' that //默认情况下，MySQL数据库中有一个任何用户都可以访问的test库，
anyone can access. This is also intended only for testing, //这也仅仅是为了测试
and should be removed before moving into a production // 在正式环境下，应该移除掉
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes //刷新权限表，以确保所有的修改可以立刻生效
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
启动服务
mysql.server start
brew services start mysql@5.7
停止服务
mysql.server stop
brew services stop mysql@5.7
2.允许远程访问
pre:先修改成符合mysql规则的密码 = =！
mysql>grant all privileges on *.* to root@'%' identified by '数据库密（大小写，特殊符号，数字）';
1. 授权远程用户登录
在服务器上进入MySQL控制台
$ mysql -u root -p
授权远程用户登录 %表示任意地址，也可以指定IP
> grant all privileges on *.* to root@'%' identified by 'your_root_password';
重载授权表
> flush privileges;
> exit;
2. 配置（取消）访问IP地址限制
在 /usr/local/etc/my.cnf 里，注释 bind-address= 127.0.0.1 ，然后重启MySQL即可。
