1. distinct 合并函数：
对单个或多个列的去重，可单独使用
执行过程：
8.0版本以前， 对列的值先排序，再去重
例：
3306 [school]>select distinct(cno) from sc;
+------+
| cno |
+------+
| 1001 |
| 1002 |
| 1003 |
+------+
3 rows in set (0.00 sec)
union [distinct]合并
union all 不合并（性能更好，不用排序）
2. 别名（as）:简化字符串（在一个表中不能重复）（临时生效）
应用场景：
（1）表别名（不能重复）
teacher as te（推荐使用）
teacher te
例：
select teacher.tname,GROUP_CONCAT(student.sname)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='oldguo' and sc.score<60
group by teacher.tno;
设置别名的：
select te.tname,GROUP_CONCAT(st.sname)
from teacher as te
join course as co
on te.tno=co.tno
join sc
on co.cno=sc.cno
join student as st
on sc.sno=st.sno
where te.tname='oldguo' and sc.score<60
group by te.tno;
（2）列别名（给列设置啥名，就显示啥名）
3306 [school]>select count(distinct(name)) as 个数 from world.city;
+--------+
| 个数 |
+--------+
| 3998 |
+--------+
1 row in set (0.00 sec)
调用列别名的子句：
hivaing
rder by
3. 外连接 left（效果与内连接一样）
左外连接：
除了把有关联的数据拿出来外，还会把左边的数据也拿出来，右边无关的数据以NULL填充并显示。
还可以对内连接查询起到优化作用
写法:
优化内连接写法：
3306 [world]>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;
+-----------+----------+-------------+
| name | name | surfacearea |
+-----------+----------+-------------+
| Adamstown | Pitcairn | 49.00 |
+-----------+----------+-------------+
1 row in set (0.00 sec)
左外连接写法：
select a.name,b.name,b.surfacearea from city as a left join country as b on a.coun.countrycode=b.code and a.population<100 limit 10;
+----------------+------+-------------+
| name | name | surfacearea |
+----------------+------+-------------+
| Kabul | NULL | NULL |
| Qandahar | NULL | NULL |
| Herat | NULL | NULL |
| Mazar-e-Sharif | NULL | NULL |
| Amsterdam | NULL | NULL |
| Rotterdam | NULL | NULL |
| Haag | NULL | NULL |
| Utrecht | NULL | NULL |
| Eindhoven | NULL | NULL |
| Tilburg | NULL | NULL |
+----------------+------+-------------+
10 rows in set (0.00 sec)
右外连接 right：
除了把有关联的数据拿出来外，还会把右边的数据也拿出来，然后形成一个新表，左边无关联的数据以NULL的形式显示。
写法：
3306 [world]>select a.name,b.name,b.surfacearea from city as a right join country as b on a.countrycode=b.code and a.population<100 limit 10;
+------+----------------------+-------------+
| name | name | surfacearea |
+------+----------------------+-------------+
| NULL | Aruba | 193.00 |
| NULL | Afghanistan | 652090.00 |
| NULL | Angola | 1246700.00 |
| NULL | Anguilla | 96.00 |
| NULL | Albania | 28748.00 |
| NULL | Andorra | 468.00 |
| NULL | Netherlands Antilles | 800.00 |
| NULL | United Arab Emirates | 83600.00 |
| NULL | Argentina | 2780400.00 |
| NULL | Armenia | 29800.00 |
+------+----------------------+-------------+
10 rows in set (0.00 sec)
4. information_scherma（虚拟库，数据库启动时自动生成的视图）的基本使用
什么是视图？
视图也可以理解为别名
创建视图关键字：
create view as
例子：
create view t as 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;
select * from t;
4.1 information_scherma.tables
什么是元数据
元数据存放在基表中，也叫元数据表。基表无法查询和修改的。
还有一部分也保存的状态类元数据：ibdata1
可以通过DDL修改基表（元数据）。
也可以间接查看，show(片面的，只能看当前库下的表)、desc、information_schema（全局类的统计和查询）
use information_schema
desc tables;
TABLE_SCHEMA ：表所在的库
TABLE_NAME ：表名
TABLE_TYPE ：表类型
ENGINE ：表的存储引擎
TABLE_ROWS ：表行数
AVG_ROW_LENGTH ：平均行长度
INDEX_LENGTH ：索引的长度
-- 例子：information_chema
-- 要查询整个数据库下所有的库对应的表的名字，每个库显示成一行
use information_schema;
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;
+--------------------+-------------------+
| table_schema | count(table_name) |
+--------------------+-------------------+
| information_schema | 61 |
| mysql | 31 |
| oldboy | 1 |
| oldguo | 3 |
| performance_schema | 87 |
| school | 7 |
| sys | 101 |
| world | 3 |
+--------------------+-------------------+
8 rows in set (0.00 sec)
（面试题：）-- 统计一下每个库的真实数据量
每张表数据量=平均行长度x行数+索引长度
select table_schema,count(table_name),sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)
from information_schema.tables
group by table_schema;
SELECT
table_schema,
COUNT( table_name ),
SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS ToTAL_MB
FROM information_schema.TABLES
GROUP BY table_schema;
统计数据库中的总数据大小
SELECT
SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS ToTAL_MB
FROM information_schema.TABLES;
concat()拼接命令使用
3306 [information_schema]>select concat(user,"@","'",host,"'") from mysql.user;
+-------------------------------+
| concat(user,"@","'",host,"'") |
+-------------------------------+
| oldboy@'10.0.0.%' |
| oldguo@'10.0.0.%' |
| root@'10.0.0.%' |
| zhihu@'10.0.0.%' |
| oldboy@'172.16.1.%' |
| mysql.session@'localhost' |
| mysql.sys@'localhost' |
| root@'localhost' |
+-------------------------------+
8 rows in set (0.00 sec)
模仿以上命令，对整个数据库下的1000张表进行单独备份,排除sys,performance,information_schema
vim /etc/my.cnf
secure-file-priv=/tmp
systemctl restart mysqld
select concat("mysqldump -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/data_bak.sh';
[root@db01 /tmp]# head -5 data_bak.sh
mysqldump -uroot -p123 mysql columns_priv>/tmp/mysql_columns_priv.sql
mysqldump -uroot -p123 mysql db>/tmp/mysql_db.sql
mysqldump -uroot -p123 mysql engine_cost>/tmp/mysql_engine_cost.sql
mysqldump -uroot -p123 mysql event>/tmp/mysql_event.sql
mysqldump -uroot -p123 mysql func>/tmp/mysql_func.sql
模仿一下语句，批量实现world库下所有表的操作语句生成
alter table world.city discard tablespace;
select concat("alter table ",table_schema,".",table_name,"discard tablespacse;")
from information_schema.`TABLES`
where table_schema='world'
into outfile '/tmp/discard.sql';
5. show
show databases; #查看所有数据库名
show tables; #查看当前库下的表名
show tables from world; #查看world数据库下的表名
show create databales; #查看建库语句
show create tables; #查看建表语句
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 engine innodb status; 查看所有和innodb引擎状态情况
show binary logs; #查看二进制日志情况
show binglog events in; #查看二进制日志事件
show relaylog events in; #查看relay日志事件
show slave status; #查看从库状态
show master status; #查看数据看binglog位置信息
show index from; #查看表的索引情况
6. 索引（超重点）
6.0 压力测试学习环境准备
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t100w where k2='XYno'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose
6.1 什么是索引？
索引是对数据库表中一个列或多个列（例如，employee 表的姓名 (name) 列）的值进行排序的结构。
使用索引可快速访问数据库表中的特定信息。
6.2 索引的作用
提供了类似于书中目录的作用，目的是为了优化查询
6.3 为什么要用索引
例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的,并且，它还可以优化查询。
6.4 索引的种类（按算法分类）
B树索引
Hash索引（Innode不支持）
R树
Full text（全文索引）
GIS
B树 基于不同的查找算法分类介绍
普通索引B树的组成
（1）叶子节点：当我们把某一列设定成索引时，它会先把那一列的值提取出来进行排序并生成序号，然后将这些排好序的序号放进索引的存储结构中。这些存储结构就是上图中的Page（页），在MySQL中，每一个Page的大小都为16kb。假设在每一个Page中存放两个数字，从0开始，存放的顺序就是（0 1）、（2 3）、（4 5）、（6 7），这样就组成的“叶子节点”。
（2）枝节点：当叶子节点生成后，MySQL的索引节点会进行升级，并把叶子节点上每个Page中的最小值提取出来，然后组成“枝节点”。
（3）根节点：根节点的组成也跟枝节点一样，当枝节点组成后，MySQL中的索引节点会再次进行升级，并把枝节点中每个Page的最小值提取出来，组成根节点。最后，就形成了B树。
B树索引查找数据流程
假设现在需要查找的值的序号为3，它首先会遍历根节点，并发现3比0大，比4小。这个时候它就会从0这边继续往下查找，这个时候也就来到了Page4，并与Page4中的两个序号进行比较，然后发现3即大于0又大于2，所以这个时候它就从2这边继续往下查找，并来到了Page7，继续进行比较，这个时候就在Page7中找到了3这个序号，然后继续往下查找，最终找到了序号3对应的值。
总结：根据B树索引查找数据流程分析得出，不管查找的序号是多少，它首先都会先到根节点，然后到枝节点，最后到叶子节点。也就是说，查找值的步骤永远都是3步，不会多也不会少。
B+tree
B+Tree特点：
每个叶子节点都会保存相邻叶子节点的指针，减少了范围查找时遍历索引的次数及IO的消耗
BTree特点：*
在每个相邻的枝节点生成双向的指针，减少了范围查找时遍历索引的次数及IO的消耗
6.5 B树算法普及
B-tree（普通B树）
B+tree
B*tree
以上三种B树被统称为“B树”
BTREE面试题：
B-tree与B+tree之间的区别是什么？
最大的区别就在叶子节点。B+tree在叶子节点上生成了一个双向指针，减少了范围查找时遍历索引的次数及IO的消耗。
6.6 在功能上的分类（重点面试题）
6.6.1 辅助索引(s索引、二级索引)怎么构建B树的？
（1）辅助索引是基于表中的列生成的。
（2）取出索引列的所有值（取出所有键值）
（3）进行所有键值的排序
（4）将所有的键值按顺序落到Btree索引的叶子节点上
（5）进而生成枝接点和根节点
（6）叶子节点除了存储键值之外，还存储了相邻叶子节点的指针，另外还会保存指向原表数据的指针
辅助索引的缺点：
如果取出的值是没有顺序的，则会增加遍历的次数
6.6.2 聚集索引(c索引、集群索引)怎么构建B树的？
（1）前提：创建聚集索引的要求，建表时有主键列，比如ID列
（2）将来在进行表中数据存储时，会严格按照ID列数值的顺序，有序的存储一行一行的数据到数据页上（这个动作叫做索引组织表）
（3）表中的数据页，被作为聚集索引的叶子节点，此时就不用进行排序了
（4）把叶子节点的主键值（ID值）生成为上层的枝节点和根节点
聚集索引与辅助索引在叶子节点上的区别：
辅助索引是把单列的值拿出来进行排序后，进而生成叶子节点。
聚集索引是把整行的值拿出来，并且不需要进行排序，直接生成叶子节点。
6.6.3 聚集索引和辅助索引构成区别总结
（1）最大的区别就是在叶子节点上，聚集索引在叶子节点上保存的是整行的值，辅助索引保存的是一列的值。
（2）聚集索引只能有一个,非空唯一,一般为主键。
（3）如果没有主键，MySQL会自动选择一个唯一键来充当聚集索引，如果既没有主键，也没有唯一键，则MySQL会自动生成一个隐藏的主键来充当聚集索引，但是效果可能没有我们手动指定的主键效果好。
（4）辅助索引,可以有多个,是配合聚集索引使用的。
（5）聚集索引的叶子节点,就是磁盘的数据行上存储的数据页。
（6）MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据。
（7）辅助索引,只会提取索引键值（就是那一列的值）,进行自动排序生成B树结构。
6.7 辅助索引细分
其实辅助索引只是一个大的分类，它还可以详细的分为以下几类：
（1）单列的辅助索引
（2）联合多列辅助索引，又被称为覆盖索引（当我们想要获取的值能够直接通过辅助索引获取到的时候，这时候的辅助索引就被称为“覆盖索引”，不过一般情况下很难实现），当where取值为2（where name='zs' and address='bj'）的时候，就会用到覆盖索引。
（3）唯一索引（当表中没有主键是，唯一索引可能就会称为主键）
建立辅助索引的规律：
经常用Where来进行取值的列。
6.8 关于索引树的高度受什么影响？
（1）原因：数据行
解决方案：分表
（2）原因：索引列的字符长度
解决方案：前缀索引
（3）原因：char、varchar
解决方法：表设计
（4）原因：enum 优化（减少）索引数高度
解决方法：能用则用
上述这些情况只是针对大表来说的，小表无影响。
一般情况下，索引树的高度要控制在3-4层之间是比较合理的，尽量不要超过4层，为什么呢？
因为索引树的层数越多，索引对表的遍历次数就会增多，发生的IO也就增多了，这样的话就可能会对性能造成一定的影响，索引的效果就不是特别好了。
网友评论