辅助索引(S)怎么构建B树结构的?
(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
id  name  age  gender
select  *  from  t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.

DESC information_schema.TABLES
TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）

查询整个数据库中所有库和所对应的表信息
SELECT table_schema,GROUP_CONCAT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

统计所有库下的表个数
SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema

查询所有innodb引擎的表及所在的库
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';
统计world数据库下每张表的磁盘空间占用
SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")  AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA='world';

统计所有数据库的总的磁盘空间占用
SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

生成整个数据库下的所有表的单独备份语句
模板语句：
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;

CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

107张表，都需要执行以下2条语句
ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';

执行顺序：select 列 from 表 where 条件 group by 条件 having 条件 order by 条件 limit 条件;
分析：
select 需要显示的列 from 表名 where 条件 group by 将where后条件进行分组的条件 having 对having之前的结果按条件做过滤 order by 对以上处理完的结构再进行条件过滤 limit 显示顺序;

1>group by + 常用聚合函数（有统计类需求一般使用group by）
group by：将某列中有共同条件的数据行，分成一组，然后再进行聚合函数操作。group by 还有去重复的功能，将共同点合并，只显示一次。
group by执行顺序：先排序，再去重。

根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列
**max()**      ：最大值
**min()**      ：最小值
**avg()**      ：平均值
**sum()**      ：总和
**count()**    ：计数

group_concat() ：列转行
例子：
mysql> grant all on *.* to test@'192.168.3.%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| test          | %           |
| test          | 192.168.3.% |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
+---------------+-------------+
5 rows in set (0.00 sec)

mysql> select user,group_concat(host) from mysql.user group by user;
+---------------+--------------------+
| user          | group_concat(host) |
+---------------+--------------------+
| mysql.session | localhost          |
| mysql.sys     | localhost          |
| root          | localhost          |
| test          | %,192.168.3.%      |
+---------------+--------------------+
4 rows in set (0.00 sec)
#group_concat实现一对多显示

例子：
例子1：统计世界上每个国家的总人口数.

USE world
SELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;
例子2： 统计中国各个省的总人口数量(练习)

SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;
例子3：统计世界上每个国家的城市数量(练习)

SELECT countrycode,COUNT(id)  FROM city GROUP BY countrycode;

2>SELECT + having 应用
语句顺序：where|group|having
having属于后过滤
例子4：统计中国每个省的总人口数，只打印总人口数小于100

SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(Population) < 1000000 ;

concat拼接
例：
mysql> select concat(user,"@",host) from mysql.user;
+------------------------+
| concat(user,"",host)   |
+------------------------+
| test%                  |
| test192.168.3.%        |
| mysql.sessionlocalhost |
| mysql.syslocalhost     |
| rootlocalhost          |
+------------------------+
5 rows in set (0.00 sec)

3>order by + limit
作用
实现先排序，by后添加条件列
 
district 去重复，只显示一次。

应用案例
查看中国所有的城市，并按人口数进行排序(从大到小)
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
统计中国各个省的总人口数量，按照总人口从大到小排序
SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;
统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名
SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3 ;

LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5

LIMIT N OFFSET M--->跳过M,显示一共N行

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

4>distinct：去重复

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city  ;

-- select @@xxx 查看系统参数

SELECT @@port	#查看当前数据库端口
SELECT @@basedir;	#查看安装目录位置
SELECT @@datadir;	#查看数据目录位置
SELECT @@socket;	#查看socket文件位置
SELECT @@server_id;	#查看serverID

-- select 函数();

SELECT NOW();	#查看当前时间
SELECT DATABASE();	#查看当前库
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;

use mysql
select * from user limit 5;	#查看user表前5行顺序