常用

1 查版本

select version();

查询消耗时间排序：

select statReportId,startTime,endTime, (endTime-startTime) as consumes from realtime_stat.log_stat_report_mysql where  date(startTime) ='2020-07-28' order by consumes desc;

2 备份

https://www.cnblogs.com/chenmh/p/5300370.html

1.导出所有数据库
该命令会导出包括系统数据库在内的所有数据库
mysqldump -uroot -proot --all-databases >/tmp/all.sql
2.导出db1、db2两个数据库的所有数据
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql

导出

mysqldump --u  root -h 172.16.16.212 -p'youai123'  -P3306 -d --databases report_ix >report_ix.sql

去掉锁表及语句加锁

mysqldump --skip-lock-tables --skip-add-locks -u  root -h 172.16.16.212 -p'youai123'  -P3306  --databases dolphinscheduler  >/data/backup/dolphinscheduler.sql

1. 还原备份数据

mysql -uroot -p database_name < /backup/mysqldump/db_name.sql

3 小数

# 保留两位小数向上
select ceil(2.491*100)/100;
# 保留两位小数四舍五入
select round(2.495,2);
# 保留两位小数向下
select truncate(2.495,2);
# 向上取整
select ceil(32.1);
# 向下取整
select floor(32.9);

4 修改编码

-- 查看
show variables like 'character_set_database';
-- 修改
alter database analysis character set utf8;  -- 推荐使用utf8mb4
-- 
set names utf8;

• 终端中文乱码
  set character_set_results=utf8;

5看数据库,表占用磁盘大小

• 查询所有数据库占用磁盘空间大小

select 
TABLE_SCHEMA, 
concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc;
#order by data_length desc;

• 所有库表

  select
  table_schema as '数据库',
  table_name as '表名',
  table_rows as '记录数',
  truncate(data_length/1024/1024/1024, 2) as '数据容量(GB)',
  truncate(index_length/1024/1024, 2) as '索引容量(MB)'
  from information_schema.tables
  order by data_length desc, index_length desc;

• 查询单个库中所有表磁盘占用大小

  select 
  TABLE_NAME, 
  concat(truncate(data_length/1024/1024,2),' MB') as data_size,
  concat(truncate(index_length/1024/1024,2),' MB') as index_size
  from information_schema.tables 
  where TABLE_SCHEMA = 'DB_NAME'
  group by TABLE_NAME
  order by data_length desc;

6 union all 和 union

union all 不去重，不排序； union 会去重与进行默认规则的排序。 union all 性能好，因为无需额外去重与排序

7 left join on 和 where 条件的区别

两者放置相同条件，之所以可能会导致结果集不同，就是因为优先级。on的优先级是高于where的。
inner join

8 查询重复数据

select * from tablename where id in (
select id from tablename 
group by id 
having count(id) > 1
)

根据name和code字段找到重复记录

SELECT * from (SELECT *, CONCAT(name,code) as nameAndCode from tb_table) t WHERE t.nameAndCode in 
(
 SELECT nameAndCode from (SELECT CONCAT(name,code) as nameAndCode from tb_table) tt GROUP BY nameAndCode HAVING count(nameAndCode) > 1
)

• innodb和myisam的区别
• mysql int类型 int(11) 和int(2)区别

1. mysql字段定义中INT(x)中的x仅仅指的是显示宽度。
   显示宽度小于指定的列宽度的值时从左侧用数值0填满宽度(指定ZEROFILL时)
   如果设置了ZEROFILL扩展属性，默认就有了无符号属性UNSIGNED
   显示宽度并不限制可以在列内保存的值的范围
   显示宽度不限制超过列的指定宽度的值的显示
   所以INT(x)的定义与存储空间没有任何关系，都是4个字节。
2. INT(x)的最大值和最小值与UNSIGNED有关
   有符号型范围 -2147483648 ~ 2147483647，即-2^31 ~ 2^31 - 1
   无符号型范围 0 ~ 4294967295，即2^32 - 1

int类型在内存中占4个字节，也就是用32位二进制表示，其中最高位为符号位，0表示正，1表示负。

int类型正数上限二进制表示为0111 1111 1111 1111即(2^32)-1，表示0的符号位是0(即最高位是0)：0000 0000 0000 0000
由于0已经被正数这一边表示了，所以负数的1000 0000 0000 0000就可以用来表示-2^32

• MYSQL字符型数据初始值，default 为 null 好还是空串好？

如果是允许null的列，对索引会有影响（索引不会包括有NULL值）。
影响索引的统计信息，影响优化器的判断。
可空列被索引后，每条记录都需要额外的字节。
复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
所以我们在表设计时不要让字段的默认值为NULL。

• 为什么一定要用自增ID作为主键？

  1.InnoDB按主键聚集，自增可以防止页分裂。

  2.所有索引都包含主键。

  3.方便优化分页

• 查询优化

  1.绝对禁止使用select * 和insert into 不带字段
  a) 防止业务需求需对表进行DDL时，造成字段数不匹配的故障。
  b) 使用select（col…）、分页，只获取需要的数据列和行，避免不需要的字段造成较大的网络开销

• 重置密码

  1.在my.cnf设 skip-grant-tables 
  2.重启mysql 
  3.进入客户端设置 
  update mysql.user set password=password('你要重置的密码') where user='root' and host='localhost';  
  或较新的版本
  alter user root@localhost identified by 'NewPass';
  flush privileges;

• 添加用户

  create user ‘用户名‘@‘主机名‘ identified by ‘密码‘;

• 开放访问权限
  ```

1. 允许任意主机以用户bd和密码bdpw连接到local服务器 GRANT ALL PRIVILEGES ON . TO ‘bd’@’%’ IDENTIFIED BY ‘bdpw’ WITH GRANT OPTION; flush privileges;

2. 允许特定的IP地址以用户bd和密码bdpw连接到local服务器 GRANT ALL PRIVILEGES ON . TO ‘bd’@’172.16.4.198’ DENTIFIED BY ‘bdpw’ WITH GRANT OPTION; flush privileges; ```

   注意：

   1. 在Command Line Client输入密码 或 进入Nivacat的命令行界面，
   2. 输入：_use mysql;_
   3. 查询host输入： select user,host from user;
      如果有host值为%的，则直接执行授权语句；如果没有，则执行第4步
   4. 创建host
      如果没有”%”这个host值,选择某个开放出去的用户（bd），执行
      mysql> update user set host=’%’ where user=’bd’;
      mysql> flush privileges;
   5. 授权用户*.* 表明授权所有权限

• 变量

  # 查看端口
  show variables like 'port';

配置Ubuntu的防火墙

• 打开3306端口：sudo ufw allow 3306
• 开启防火墙：sudo ufw enable
• 设置默认的防火墙防御：sudo ufw default deny
• 查看防火墙的状态：sudo ufw status