MySQL中的SQL基础应用扩展

1. 1.1 group by name

1.2 关于group by的sql_mode
only_full_group_by
说明：
在5.7版本中MySQL sql_mode参数中自带，5.6没有
在带有group by 字句的select中，select 后的条件列（非主键列），要么是group by后的列，要么需要在函数中包裹

实例：导入world库
use world;
select name,population from city group by district; #报错违反only_full_group_by

关闭only_full_group_by：
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
仅复制only_full_group_by后面的字段，然后修改mysql配置文件。
vim /etc/my.cnf
在[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
保存退出
systemctl restart mysqld

1.3 group_concat
列转行聚合函数
mysql> select user,group_concat(host) from mysql.user group by user;

1.4 concat
做列值拼接
mysql> select concat(user,"@",host) from mysql.user;

1.5 关于多表连接语法规则 *
首先找涉及到的所有表
找到表和表之间的关联列
关联条件写在on后面 A join B on 关联列
所有需要查询的信息放在select后
其他的过滤条件where group by having order by limit 往最后放
注意：对多表连接中，驱动表选择数据行少的表。后续所有表的关联列尽量是主键或唯一键（表设计），至少建立一个索引。

1.6 别名
表别名 from 表名 as 表别名
列别名 select 列名 as 列别名

1.7 distinct
mysql> select count(distinct countrycode) from city;

1.8 select 执行顺序
select user ,count(name) from 表 where 列 group by user having 聚合函数 order by 列 limit 3 offset 1 ;

==========================================================

1. 扩展类内容-元数据获取 **

2.0 元数据介绍及获取介绍
元数据是存储在"基表"中。
通过专用的DDL语句，DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的视图
show 命令是封装好功能，提供元数据查询基础功能

2.1 information_schema的基本应用 *
tables 视图的应用
mysql> use information_schema;
mysql> desc tables;

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

例子：
USE information_schema;
DESC TABLES;
-- 1. 显示所有的库和表的信息
SELECT table_schema,table_name FROM information_schema.tables;

-- 2. 以以下模式 显示所有的库和表的信息
-- world city,country,countrylanguage

SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;

-- 3. 查询所有innodb引擎的表
SELECT table_schema,table_name ,ENGINE
FROM information_schema.tables
WHERE ENGINE='innodb';

-- 4. 统计world下的city表占用空间大小 **
-- 表的数据量=平均行长度行数+索引长度
-- AVG_ROW_LENGTHTABLE_ROWS+INDEX_LENGTH
SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024
FROM information_schema.TABLES
WHERE table_schema='world' AND table_name='city';

-- 5. 统计world库数据量总大小 **
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024
FROM information_schema.TABLES
WHERE table_schema='world';

-- 6. 统计每个库的数据量大小，并按数据量从大到小排序 **
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY total_KB DESC ;

2.2 配合concat()函数拼接语句或命令
例子：
-- 1. 模仿以下语句,进行数据库的分库分表备份。
备份world库下city表：
mysqldump -uroot -p123 world city >/bak/world_city.sql

SELECT
CONCAT("mysqldump -uroot -p ",table_schema," ",table_name
," >/bak/",tableschema,"",table_name,".sql")
FROM information_schema.tables;

-- 2. 模仿以下语句,进行批量生成对world库下所有表进行操作
ALTER TABLE world.city DISCARD TABLESPACE;

SELECT
CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;")
FROM information_schema.tables
WHERE table_schema='world';

2.2 show介绍*
show databases; 查看数据库名
show tables; 查看表名
show create database xx; 查看建库语句
show create table xx; 查看建表语句
show processlist; 查看所有用户连接情况
show charset; 查看支持的字符集
show collation; 查看所有支持的校对规则
show grants for xx; 查看用户的权限信息
show variables like 'innodb%' 查看参数信息
show engines; 查看所有支持的存储引擎类型
show index from city; 查看表的索引信息
show engine innodb status\G 查看innoDB引擎详细状态信息
show binary logs ; 查看二进制日志的列表信息
show binlog events in '' 查看二进制日志的事件信息
show master status ; 查看mysql当前使用二进制日志信息
show slave status\G 查看从库状态信息
show relaylog events in '' 查看中继日志的事件信息
show status like '' 查看数据库整体状态信息(%innodb_rows_update%:更新次数；%innodb_rows_inserted% 插入次数； %innodb_rows_read% 读取次数；%innodb_rows_delete% 删除次数)