MySQL元数据获取基础笔记day05

多表连接查询

1、作用 业务需要的数据来自多张表时

2、多表连接查询基本语法

??内连接
? 外链接
全链接
笛卡尔

3、内连接的类型

传统连接 where
自连接
join ming
join on ? ? ? ? ?

4、join on的语法

select xxx
from A
join B
on A.xxx=B.yyy
where
group
having
order by
limit

把原来的两张表按照一定的逻辑条件并成了一张表。

select a.name,b.addr from a join b on a.id=b.id where a.name='zhongsan';
如果是3张表 A B C
那么先A join C
on A.xx=C.yy
join B
on C.aa=B.bb

5、多表连接的套路：

1.根据需求找到关联表。
2.找到表与表的关联列。
3.列名调用时，需要添加表前缀，例如a.id ,b.name之类

多表连接案例 world表为例。
查询人口数量少于100人的城市所在的国家名、国土面积、城市名和人口名。

SELECT country.name,country.surfacearea,city.name,city.population
from city
join country
on city.countrycode=country.code
WHERE city.population<100;

5.2 查询zhang3学习了几门课程

涉及到的表 student
sc

select student.sname,count(sc.cno) from student join sc
on student.sno=sc.sno
where student.sname='zhang3';

5.3 统计zhang3学习了课程 名称
student
sc
course

select student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';

5.4 oldguo老师教了学生的个数
teacher

select teacher.tname,count(student.sno)
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';

5.5 每位老师所教课程的平均分，并按平均分排序

select teacher.tname,avg(sc.score)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
group by teacher.tno,teacher.tname
order by avg(sc.score);

5.6 查询oldguo所教的不及格的学生姓名

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;

5.7 查询所有老师所教学生不及格的信息

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 sc.score<60
group by teacher.tname;

6.别名的使用。
6.1 表别名

select a.tname, group_concat(d.sname)
from teacher as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
join student as d
on c.sno=d.sno
where c.score<60
group by a.tname;

说明：表别名一般是在from的表的表名，或者join后的表的别名。
在where, group by ,select后的列，having, order by

6.2 列别名

select a.tname as 讲师,group_concat(d.sname) as 学生
from teacher as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
join student as d
on c.sno=d.sno
where c.score<60
group by a.tname;

select 列
from 表 表别名
where 表别名.列
group b考别名. 列
having 列别名
order by 列别名

说明：列别名一般是在select后的列，定义的别名
作用：1.结果集显示会以别名形式展示。
2.在having和order by 中可以调用列别名。

7.外链接简介

left join
a left join b
on a.x=b.y
right join

8.元数据获取

基表----数据字典信息（列结构frm），系统状态，对象状态。
相当于linux inode

DDL DCL

information_schema 虚拟库 视图

8.1 show 语句 （mysql独家）

show databases;
show tables;
show create databases xxx;
show create table xxx;
show grant for xxx(用户);
show charset;（支持字符集情况;
show collation;校对
show variables like ''%trx%;
show engines; (存储引擎)
show process list; （进程）
show index from t1; (查看索引)
show status;（看数据库状态）
show engine innodb status\G;（看存储引擎状态）
show binlog events in'';(二进制)
show binary logs;
show master status;
show slave status\G;
show relaylog events in ''；
show table status;
help show;

8.2 information_schema虚拟库
information_schema --->values 视图

8.2.1 TABLES作用和结构

视图中保存中mysql所有的表的元数据信息。
作用：存储整个数据库中，所有表的元数据查询方式。

desc tables;
TABLE_SCHEMA 表所在的库
TABLE_NAME 表名
ENGINE 表的引擎
TABLE_ROWS 表的行数
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度

例子：
1、查询world数据库下所有的表名。

show tables from world;

use information_schema;
2、查询整个数据库下所有的表名。
select table_name from information_schema.tables;
3.查询所有innodb引擎的表
select * from information_schema.tables where engine='innodb';

4.统计每张表的实际空间占用大小情况(avg_row_length*table_rows+index_length)一张表的空间占用大小情况

select table_name , avg_row_length*table_rows+index_length
from information_schema.tables;

5.统计每个库的空间使用情况大小

select table_schema,
sum(avg_row_length*table_rows+index_length)/1024/1024 as total_mb
from information_schema.tables
group by table_schema ;

6.对mysql的数据库进行分库分表备份

mysqldump -uroot -p world(库名) city（表名） >/backup/world_city.sql

SELECT CONCAT("mysqldump -uroot -p123456 ",table_schema ," ",table_name ," >/backup/",table_schema,
"_",table_name,".sql")
FROM information_schema.tables INTO OUTFILE '/tmp/bak.sql';

会报错，这是5.7以后的一个约束，需要把参数加入配置文件中。然后保存退出，重启数据库，每次导出名不能重复。

secure-file-priv=/tmp

然后进入相应目录 sh 执行脚本即可。

7.模仿模版语句，对world数据库下的表进行批量操作。
alter table world.city discard table space;

select concat("alter table ",table_schema,".",table_name," discard table space;") from information_schema.tables where table_schema='world'
into outfield '/tmp/discard.sql'
;

8. 索引及执行计划管理
   1.索引的作用
   类似于一本书的目录，起到优化查询的功能。
   2.索引类型
   Btree索引 ⭐️⭐️⭐️⭐️⭐️
   RTREE索引
   HASH索引
   FUllText索引 （全文索引）

3. BTREE的细分类(算法)
   B-Tree 从根遍历然后遍历枝最后找到叶子。
   B+Tree 在下面叶节点双向指针。
   B*Tree 在相邻枝节点加入了枝节点的双向指针。

   
   
   

   b+tree.JPG

B-Tree先排序 然后分成几份 有个范围。

4.Btree索引的功能 （innode默认Btree*）
聚集索引（集群索引）cluster index
辅助索引 （二级索引）secondary index

5.B-tree是如何构建的
辅助索引： alter table t1 add index idx_name(name);
创建了一个B-tree的结构。

1.将name列的所有值取出来，进行自动排序。
2.将排完序的值均匀的落到16KB叶子节点数据页中，并将索引键值所对应的数据行的页码记录。
3.向上生成枝节点和根节点。

聚集索引（IOT）

1.默认是按照主键生成聚集索引，没有主键，存储引擎会使用唯一键，如果都没有会自动生成一个隐藏的聚集索引。
2.数据在存储时，就会按照聚集索引的顺序存储到磁盘的数据页。
3.由于本身数据就是有序的，所以聚集索引构建时，不需要排序。
4.聚集索引直接将磁盘的数据页作为叶子节点。
5.枝节点和根节点只会调取下层节点主键的最小值。

聚集索引和辅助索引的区别？

辅助索引的叶子节点是手工创建的
聚集索引是自动跟主键生成的
聚集索引只有一个
辅助索引可以有多个

索引可以用来提高查询速度。