经常使用 ​

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

create user 'admin'@'%' identified by 'admin123';
grant all privileges on *.* to 'admin'@'%' identified by 'admin123';
grant all privileges on *.* to 'admin'@'%' identified by 'admin123' WITH GRANT OPTION;
FLUSH PRIVILEGES;

create user 'admin'@'%' identified by 'admin123';
grant all privileges on *.* to 'admin'@'%' identified by 'admin123';
grant all privileges on *.* to 'admin'@'%' identified by 'admin123' WITH GRANT OPTION;
FLUSH PRIVILEGES;

MySQL 5.7.5及以上功能依赖检测功能。默认情况下启用了ONLY_FULL_GROUP_BY.
ONLY_FULL_GROUP_BY的意思是：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现.
那么这个SQL是不合法的，因为查出来的列必须在GROUP BY后面出现否则就会报错。

#查看是否开启
select @@global.sql_mode;
#关闭ONLY_FULL_GROUP_BY模式
#全局
set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
#当前会话
set @@**SESSION**.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

MySQL 5.7.5及以上功能依赖检测功能。默认情况下启用了ONLY_FULL_GROUP_BY.
ONLY_FULL_GROUP_BY的意思是：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现.
那么这个SQL是不合法的，因为查出来的列必须在GROUP BY后面出现否则就会报错。

#查看是否开启
select @@global.sql_mode;
#关闭ONLY_FULL_GROUP_BY模式
#全局
set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
#当前会话
set @@**SESSION**.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

#里面有数据目录和日志目录
find / -name my.cnf
 
#查看日志
tail -f /var/log/mysqld.log

#里面有数据目录和日志目录
find / -name my.cnf
 
#查看日志
tail -f /var/log/mysqld.log

use information_schema;

select data_length,index_length  
    from tables where  
    table_schema='monitor_hl'  
    and table_name = 'monitor_alarm';
		
select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,  
    concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB  
    from tables where  
    table_schema='monitor_hl'  
    and table_name = 'monitor_alarm'; 
		
		
select
  table_schema as '数据库',
  sum(table_rows) as '记录数',
  sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
  sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
  from information_schema.tables
  where table_schema='monitor_hl';


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

use information_schema;

select data_length,index_length  
    from tables where  
    table_schema='monitor_hl'  
    and table_name = 'monitor_alarm';
		
select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,  
    concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB  
    from tables where  
    table_schema='monitor_hl'  
    and table_name = 'monitor_alarm'; 
		
		
select
  table_schema as '数据库',
  sum(table_rows) as '记录数',
  sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
  sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
  from information_schema.tables
  where table_schema='monitor_hl';


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

1.使用 OPTIMIZE TABLE 命令可以对表进行优化，这将重建表并消除碎片，从而改善数据的物理存储布局。这可以使表中的数据更加连续，从而提高查询性能。
  ·OPTIMIZE TABLE `monitor_weight_info`
2.重新构建索引： 索引碎片可能导致查询性能下降。通过删除并重新创建索引，可以重新组织索引，使其更加连续。您可以使用 ALTER TABLE 命令来重建索引。
3.定期分析表 使用 ANALYZE TABLE 命令手动分析表的统计信息 ANALYZE TABLE table_name; ANALYZE TABLE table1, table2, table3;

1.使用 OPTIMIZE TABLE 命令可以对表进行优化，这将重建表并消除碎片，从而改善数据的物理存储布局。这可以使表中的数据更加连续，从而提高查询性能。
  ·OPTIMIZE TABLE `monitor_weight_info`
2.重新构建索引： 索引碎片可能导致查询性能下降。通过删除并重新创建索引，可以重新组织索引，使其更加连续。您可以使用 ALTER TABLE 命令来重建索引。
3.定期分析表 使用 ANALYZE TABLE 命令手动分析表的统计信息 ANALYZE TABLE table_name; ANALYZE TABLE table1, table2, table3;

SELECT
	* 
FROM
	driver a 
WHERE
	( ( SELECT COUNT( * ) FROM driver WHERE phone = a.phone ) > 1 ) 
ORDER BY
phone DESC,create_time desc

SELECT
	* 
FROM
	driver a 
WHERE
	( ( SELECT COUNT( * ) FROM driver WHERE phone = a.phone ) > 1 ) 
ORDER BY
phone DESC,create_time desc