MySQL学习总结

前言

最近在学MySQL，现在写一下笔记，留作参考。

正文

常用

• 展示全部数据库
  show databases;

• 选择数据库
  use db1;

• 展示当前数据库下所有表单
  show tables;

• 展示建表指令
  SHOW CREATE TABLE t;

• 展示表单详情
  desc t;

• 日期和时间

1. DATETIME
   8字节，日期范围"1000-01-01 00：00：00"到"9999-12-31 23：59：59"

2. DATE
   3字节，日期范围"1000-01-01"到"9999-12-31"

3. TIMESTAMP
   4字节，日期范围为"1970-01-01 00:00:00"UTC到"2038-01-19 03:14"07"UTC。

4. YEAR
   1字节，对于YEAR(4),日期范围为"1901-2155";对于YEAR(2)日期范围为"1970-2070",其中"00-69"表示"2000-2069"

5. TIME
   3字节，显示的范围为"-838:59:59"～"838:59:59"

函数

• HEX(x) 将x转成十六进制
  select HEX(123) #7b
select HEX("123") #313233(0x313233)

• 获取当前时间
  NOW()和CURRTENT_TIMESTAMP()都是表示当前SQL指令执行时的时间; SYSDATE()表示系统当前时间
  select NOW(), CURRENT_TIMESTAMP(), SYSDATE();
select SLEEP(3), NOW(), CURRENT_TIMESTAMP(), SYSDATE();

• 睡眠
  select SLEEP(3); # 睡眠3s

• 时间加
  DATE_ADD(date, INTERVAL expr unit
select DATE_ADD(now(), INTERVAL 1 DAY); #明天
select DATE_ADD(now(), INTERVAL -1 DAY); #昨天
  其中，unit可为 YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/MICROSECOND

• 时间减
  select DATE_SUB(date, INTERVAL expr unit);
select DATE_SUB(now(), INTERVAL 1 DAY);

• 最大值
  找出id号最大的一行数据
  select * from tst where id=(select max(id) from tst);

• 统计匹配指定条件的行数
  select count(*) from tst; #返回表tst记录的行数
select count(1) as a from tst where aaa='123';

• like

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的
'%%'     //中文

摘自
[1]. MySql like 模糊匹配
[2]. mysql中like % %模糊查询

• substr()
  substr(string string,num start,num length);
  string为字符串；start为起始位置；length为长度。
  注意：mysql中的start是从1开始的。
  参考：[mysql substr() 函数]（https://www.w3cschool.cn/mysql/mysql-3d9x27pc.html）

• INET_ATON() 和 INET_NTOA()

-- ip字符串转整型
select INET_ATON('192.168.1.123'); --3232235899
-- 整型转ip字符串
select INET_NTOA(3232235899); --'192.168.1.123'
-- ip字符串最后一位加一
select INET_NTOA(INET_ATON('192.168.1.123')+1); -- 192.168.1.124

参考：
[1]. MySQL中INET_ATON()、INET_NTOA函数

问题总结

• 查询报错"sql_mode=ONLY_FULL_GROUP_BY"
  这个好像是mysql 5.7常出现的问题

(1). 执行
SELECT @@GLOBAL.sql_mode;

数据库会返回一个@@GLOBAL.sql_mode列表，其中会有ONLY_FULL_GROUP_BY,复制这个列表。

(我的是:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION)

(2). 修改mysql配置文件
在 /etc/my.conf 中寻找 sql_mode= 这个配置。

如果有，可以把ONLY_FULL_GROUP_BY去掉;

如果没有，在[mysqld]下添加一行,这一行是(1)中查到的字段去掉ONLY_FULL_GROUP_BY,比如：

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

(3). 重启 mysqld 服务
service mysqld restart
查看 mysqld 服务状态
service mysqld status

(4). 查看 mysql 最大连接数
show variables like "%max_connections%";
修改最大连接数：
临时： set global max_connections = 1000;
长久：修改 my.cnf: max_connections=1000
参考：mysql数据库最大链接_MySQL 查看最大连接数和修改最大连接数

(5). 修改密码
登录到mysql后，执行：
alter user 'root'@'%' identified with mysql_native_password by '密码';
如果无法更改密码使用:
flush privileges
然后再进行更改密码，修改加密规则操作
注意，不要给你的root用户设密码为123456，不然很容易遭到外人勒索，删库要求支付比特币