本文详细介绍了MySQL中的多表连接查询，包括内连接、外连接、全连接和笛卡尔连接，以及JOIN ON的语法。通过实例展示了如何进行多表连接，如查询人口数量少于100人的城市信息、学生选课情况等。同时，文章还讲解了元数据获取，如SHOW语句和INFORMATION_SCHEMA视图的使用，以及平均行长度(AVG_ROW_LENGTH)等统计信息的获取。
多表连接查询
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'
;
索引及执行计划管理
1.索引的作用
类似于一本书的目录，起到优化查询的功能。
2.索引类型
Btree索引 ⭐️⭐️⭐️⭐️⭐️
RTREE索引
HASH索引
FUllText索引 (全文索引)
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.枝节点和根节点只会调取下层节点主键的最小值。
聚集索引和辅助索引的区别？
辅助索引的叶子节点是手工创建的
聚集索引是自动跟主键生成的
聚集索引只有一个
辅助索引可以有多个
索引可以用来提高查询速度。
扫一扫
1423
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
