day05（information_schema、show、索引

1. distinct对某个列进行去重

select distinct(cno) from score;


mysql> select distinct(cno) from score;
+------+
| cno  |
+------+
| 1001 |
| 1002 |
| 1003 |
+------+
3 rows in set (0.00 sec)



mysql> select count(*) from world.city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.05 sec)

mysql> select count(distinct(name)) from world.city;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
|                  3998 |
+-----------------------+
1 row in set (0.01 sec)

2. 别名

表别名
SELECT stu.sname,co.cname
FROM student as stu
JOIN score as sc
ON stu.sno = sc.sno
JOIN course as co
ON sc.cno = co.cno
WHERE stu.sname = 'zhang3';



列别名
mysql> select count(distinct(name)) as 个数 from world.city;
+--------+
| 个数   |
+--------+
|   3998 |
+--------+
1 row in set (0.01 sec)

3. 外连接（优化）

左外链接（左边是小数据右边是大数据）
select a.name,b.name,b.surfacearea 
from city as a 
left join country as b 
on a.countrycode=b.code 
where a.population<100 ;

4. information_schema

1.  视图
--- 8. 查询zhang3,学习的课程名称有哪些?

create view zs_v as
SELECT stu.sname,co.cname
FROM student as stu
JOIN score as sc
ON stusno = sc.sno
JOIN course as co
ON sc.cno = co.cno
WHERE stu.sname = 'zhang3';
 
select * from zs_v;

information_schema视图库（虚拟库）

use information_schema
show tables;
表：元数据+数据行
------->元数据存储在“基表“，无法直接查询和修改
------->DDL、DCL对元数据进行修改
------->information_schema(全局类的统计和）和show的语句查询元数据

mysql> mysql> desc tables;
TABLE_SCHEMA          表所在的库
TABLE_NAME            表名
ENGINE                表的存储引擎
TABLE_ROWS            表的行数
AVG_ROW_LENGTH        平均行长度
INDEX_LENGTH          索引长度
                                                                                                    
-----查询整个数据库中所有库对应的表名
select table_schema,table_name
from information_schema.`TABLES`;

----查询world和school库对应的所有表名
select table_schema,table_name
from information_schema.`TABLES`
where table_schema='world';
union all
select table_schema,table_name
from information_schema.`TABLES`
where table_schema='school';

----统计一下每个库下的表的个数
select table_schema,count(table_name)
from information_schema.`TABLES`
group by table_schema;

--- 查询整个数据库中所有的库对应的表名，每个库显示成一行
select table_schema,group_concat(table_name)
from information_schema.`TABLES`
group by table_schema;



---统计一下每个库的真实数据量
每张表数量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
select 
table_schema,
count(table_name),
sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 as table_mb
from information_schema.`TABLES`
group by table_schema;


----模仿以上命令，对整个数据库下的1000张表进行单独备份，排除sys,performance,information_schema
select concat("mysqlump -uroot -p123 ",table_schema," ",table_name,">/tmp/",table_schema,"_",table_name,".sql")
from information_schema.tables
where table_schema not in('sys','performance','information_schema')
into outfile '/tmp/bak.sh';

5. show

show databases;                               查看所有库名
show tables;                                  查看当前库下的表名 
show tables from world;                       查看world数据库下所有表名
show create database;                         查看建库语句
show create table;                            查看建表语句
show grants for root@'localhost'              查看用户权限信息
show charset;                                 查看所有字符集
show collation                                查看校对规则
show  full processlist                        查看数据库连接情况
show status                                   查看数据库的整体状态
show status like '%lock%'                     模糊查看数据库的整体状态
show variables                                查看数据库所有变量情况
show variables like '%innodb%'                查看数据库所有变量情况
show engines;                                 查看所有支持存储引擎
show engines innodb status                    查看所有innodb存储引擎状态
show binary logs                              查看二进制日志情况
show binlog events in                         查看二进制日志事件
show relaylog events in                       查看relay日志事件
show slave status                             查看从库状态
show master status                            查看数据库binlog位置信息
show index from world.city                    查看表的索引情况

6、索引

6.0、学习环境准备

mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t100w where k2='pqde'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose

6.1、索引作用：

提供类似书中目录的作用，优化查询

6.2、索引的种类（算法）：

B树索引
hash索引
R树
Full test
GIS

6.3、B树基于不同的查找算法分类介绍

image.png image.png

取下一层的最小值组成B树结构
叶子节点---------------->枝节点------------>根节点


B+tree是优化范围查询的效率，必须是相邻节点
B-tree
B*tree(现在使用）

三者统称B树索引

6.4、在功能上的分类*****

6.4.1、辅助索引（s）怎么构建B树结构的？

（1）辅助索引基于表的列进行生成
（2）取出索引列的所有值（取出所有键值）
（3）进行所有键值的排序
（4）将所有的键值按顺序落到BTree索引的叶子节点上
（5）进而生成枝节点和根节点
（6）叶子节点除了存储键值外，还存储了相邻叶子节点的指针，另外还会保存原表数据的指针。

6.4.2、聚集索引（c）怎么构建B树结构的？

规划和存储数据
（1）建表时设置主键列（ID）
（2）表中进行数据存储，会按照ID列的顺序，有序的存储一行一行的数据到数据页上（这个动作叫做聚集索引组织表）
（3）表中的数据页被作为聚集索引的叶子节点
（4）把叶子节点的主键值生成上层的枝节点和根节点。

6.4.3、聚集索引和辅助索引构成区别总结？

（1）聚集索引只能有一个，非空唯一，一般是主键
（2）辅助索引可以有多个，是配合聚集索引使用
（3）聚集索引叶子节点就是磁盘的数据行存储的数据页（整行数据）
（4）mysql是根据聚集索引组织存储数据，存储数据时就是按照聚集索引的顺序进行存储数据
辅助索引只会提取索引键值进行自动排序生成B树结构

6.5、辅助索引细分

单列的辅助索引
联合多列辅助索引（覆盖索引）
唯一索引

6.6、关于索引树的高度受什么影响？（解决方法仅限于大表）

高度最多3层或4层
（1）数据行，分表
（2）索引列字符长度，前缀索引
（3）char varchar（char比varchar高），表设计
（4）enum大大减少将来作为索引列的高度，优化索引高度，能用则用