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 ;

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

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 tables where table_schema not in ('sys','mysql','');,'information_schema','performance_schema');

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

select table_schema,group_concat(table_name) from tables where table_schema not in ('sys','mysql','information_schema','performance_schema') group by table_schema;

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

-- 4. 统计world下的city表占用空间大小 ****
-- 表的数据量=平均行长度*行数+索引长度
-- AVG_ROW_LENGTH*TABLE_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/",table_schema,"_",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% 删除次数)