关于GROUP BY的SQL_MODE

  1. 在5.7版本中SQL_MODE参数中自带only_full_group_by,5.6和8.0都没有
  2. 在带有GROUP BY字句的SELECT中,SELECT后的条件列(非主键列),要么是GROUP BY后的列,要么需要在函数中包裹
  • MySQL5.6
mysql> select user,host from mysql.user group by user;
+-------+-----------+
| user  | host      |
+-------+-----------+
| root  | 127.0.0.1 |
| sunty | %         |
+-------+-----------+
2 rows in set (0.07 sec)

mysql> select user,group_concat(host) from mysql.user group by user;
+-------+---------------------+
| user  | group_concat(host)  |
+-------+---------------------+
| root  | localhost,127.0.0.1 |
| sunty | %                   |
+-------+---------------------+
2 rows in set (0.06 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.37-log |
+------------+
1 row in set (0.06 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.08 sec)
  • MySQL5.7
mysql> select user,host from mysql.user group by user;
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql.user.Host' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select user,group_concat(host) from mysql.user group by user;
+-----------+--------------------+
| user      | group_concat(host) |
+-----------+--------------------+
| mysql.sys | localhost          |
| root      | localhost,%        |
| sunty     | %                  |
+-----------+--------------------+
3 rows in set (0.08 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.7.18-20170830-log |
+---------------------+
1 row in set (0.07 sec)

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

如果MySQL5.7想要执行不带聚合函数的带有GROUP BY字句的SELECT语句,需要删除sql_mode中only_full_group_by参数

关于多表连接语法规则

  1. 首先找涉及到的所有表
  2. 找到表和表之间的关联列
  3. 关联条件写在on后面
  4. 所有需要查询的信息放在SELECT后
  5. 其他的过滤条件WHERE,GROUP BY,HAVING,ORDER BY,LIMIT
  6. 对多表连接中,驱动表选择数据行少的表.后续所有表的关联列尽量是主键或唯一键(表设计),至少建立一个索引.

元数据获取

元数据介绍

元数据是存储在"基表"中.通过专用的DDL语句,DCL语句进行修改.通过专用视图和命令进行元数据的查询.information_schema中保存了大量元数据查询的视图SHOW命令是封装好功能,提供元数据查询基础功能

information_schema的基本应用

desc tables;可以查看当前表的所有字段,下面列出部分常用的字段

  • TABLE_SCHEMA : 表所在的库名
  • TABLE_NAME : 表名
  • ENGINE : 存储引擎
  • TABLE_ROWS : 数据行
  • AVG_ROW_LENGTH : 平均行长度
  • INDEX_LENGTH : 索引长度

示例查询

  • 显示所有的库和表的信息
SELECT TABLE_SCHEMA,TABLE_NAME FROM `TABLES`;
  • 以该格式显示所有的库和表的信息

| world | city,country,countrylanguage |

SELECT TABLE_SCHEMA,GROUP_CONCAT(TABLE_NAME) FROM `TABLES` GROUP BY TABLE_SCHEMA;
  • 查询所有innodb引擎的表
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM `TABLES` WHERE `ENGINE`='innodb';
  • 统计world下的city表占用空间大小

表的数据量=平均行长度*行数+索引长度

SELECT TABLE_SCHEMA,TABLE_NAME,(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH) FROM `TABLES` WHERE TABLE_SCHEMA='world' AND TABLE_NAME='city';
  • 统计world库数据量总大小
SELECT TABLE_SCHEMA,SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH) FROM `TABLES` WHERE TABLE_SCHEMA='world';
  • 统计每个库的数据量大小,并按数据量从大到小排序
SELECT TABLE_SCHEMA,SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH) AS total_KB FROM `TABLES` GROUP BY TABLE_SCHEMA ORDER BY total_KB DESC;
  • 配合concat()函数拼接语句或命令

mysqldump -uroot -p123 world city >/bak/world_city.sql

SELECT CONCAT('mysqldump -uroot -p123 ',TABLE_SCHEMA,' ',TABLE_NAME,' >/bak/',TABLE_SCHEMA,'_',TABLE_NAME,'.sql') FROM `TABLES`;

SHOW介绍

#查看数据库名
SHOW DATABASES;
#查看表名
SHOW TABLES;
#查看建库语句
SHOW CREATE DATABASE xx;
#查看建表语句
SHOW CREATE TABLE xx;
#查看所有用户连接情况
SHOW PROCESSLIST;
#查看支持的字符集
SHOW charset;
#查看所有支持的校对规则
SHOW COLLATION;
#查看用户的权限信息
SHOW GRANTS FOR root@'localhost';
#查看参数信息
SHOW VARIABLES LIKE '%xx%';
#查看所有支持的存储引擎类型
SHOW ENGINES;
#查看表的索引信息
SHOW INDEX FROM xxx;
#查看innoDB引擎详细状态信息
SHOW ENGINE INNODB STATUS;
#查看二进制日志的列表信息
SHOW BINARY LOGS;
#查看二进制日志的事件信息
SHOW BINLOG EVENTS IN '';
#查看mysql当前使用二进制日志信息
SHOW MASTER STATUS;
#查看从库状态信息
SHOW SLAVE STATUS;
#查看中继日志的事件信息
SHOW RELAYLOG EVENTS IN '';
#查看数据库整体状态信息
SHOW STATUS LIKE '';