MySQL多表连接查询以及information_schema

1. 作用

业务需要的数据来自多张表时。

2. 多表连接的基本语法

内链接 *****
外链接 ***
全链接 *
笛卡尔 *

3. 内链接的类型

传统连接 where
自连接
join uning
join on

4. join on 的语法

select xxx
from A
join B
on A.xxx=B.xxx
where A.name=‘zs’

多表连接的套路：

• 根据需求找到关联表
• 找到表与表的关联列
• 列名调用时，需要添加表前缀，例如 a.id，b.name

5. 多表连接案例

表内容说明

新新

>>5.1 查询人口数量少于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学习了几门课程 
SELECT student.sname,COUNT(sc.cno)
FROM student
JOIN sc
ON student.`sno`=sc.`sno`
WHERE student.sname='zhang3'
GROUP BY student.`sno`;

>>5.3 统计zhang3学的课程的名称
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老师教了学生的个数
SELECT teacher.`tname`,COUNT(sc.`sno`)
FROM teacher
JOIN course
ON teacher.`tno`=course.`tno`
JOIN sc
ON course.`cno`=sc.`cno`
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.`tname`
ORDER BY AVG(sc.`score`) DESC

>>5.6 查询oldguo所教的不及格的学生姓名
---方法1：
SELECT teacher.`tname`,GROUP_CONCAT(student.`sname`)
FROM student
JOIN sc
ON student.`sno`=sc.`sno`
JOIN course
ON course.`cno`=sc.`cno`
JOIN teacher
ON teacher.`tno`=course.`tno`
WHERE teacher.`tname`='oldguo' AND sc.`score`<60;
---方法2：
SELECT teacher.`tname`,GROUP_CONCAT(CONCAT(student.`sname`,':',sc.`score`))
FROM student
JOIN sc
ON student.`sno`=sc.`sno`
JOIN course
ON course.`cno`=sc.`cno`
JOIN teacher
ON teacher.`tno`=course.`tno`
WHERE teacher.`tname`='oldguo' AND sc.`score`<60;


>>5.7 查询所有老师所教学生不及格的信息
SELECT teacher.`tname`,student.`sname`,course.`cname`,sc.`score`
FROM student
JOIN sc
ON student.`sno`=sc.`sno`
JOIN course
ON course.`cno`=sc.`cno`
JOIN teacher
ON teacher.`tno`=course.`tno`
WHERE sc.`score`<60;

6. 别名的使用

6.1 表别名

>>例子:
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;

说明：表别名一般是在FROM后的表别名，或者join后的表的别名
在where，group，select后的列，having，order by都可以使用

6.2 列别名

>>例子:
SELECT a.tname AS 讲师,AVG(c.score) AS 平均分
FROM teacher AS a 
JOIN course  AS b
ON a.tno=b.tno
JOIN sc AS c 
ON b.cno=c.cno
GROUP BY a.tno
ORDER BY 平均分 ;

说明：列别名一般是在select后的列，定义的别名
作用：

• 结果集显示会以别名形式显示
• 在having和order by(排序)中可以调用列别名

新新

7. 外连接***

左外链接：left join
右外链接：right join

• 多表连接中，小表驱动大表
• 通过left join强制选定驱动表

>>还需额外学习:
    1. 内置函数
    2. 存储过程
    3. 函数
    4. 触发器
    5. 事件
    6. 视图
    7. json语法

8. 元数据获取

8.0 元数据获取

"基表" ------> 数据字典信息(列结构frm)，系统状态，对象状态
相当于Linux 中的 Inode
DDL DCL会导致基表改变
元数据查询：
information_schema 库
show 语句

8.1 show语句(独家)

8.2 information_schema虚拟库

information_schema ----> VIEWS 视图

CREATE VIEW test AS SELECT    
country.name AS co_name,country.SurfaceArea,city.name AS ci_name,city.Population
FROM city   JOIN country
ON city.CountryCode=country.code
WHERE city.Population<100;

SELECT * FROM test;

8.2.1 TABLES的作用和结构
作用：存储整个数据库中，所有表的元数据的查询方式。

use information_schema;
desc tables; 

>>例子：
---1. 查询world数据库下所有表信息
SHOW TABLES FROM world;

---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 -p123 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/bak1.sql';

---7. 模仿模板语句，批量生成对world数据库下的表进行批量操作
---alter table world.city discard tablespace;

---8.模仿模板语句,批量生成对world数据库下的表操作的语句 
---atler table world.city engine=innodb;

补充

新新

2.2 多表之间的关系是怎么样的？

• ER图：Poerdesigner(找开发)
• 挨个表进行对比关联关系(和业务语句查询需求对比。)

3. information_schema
3.1 定义view视图
create view test as select * from t1 where user_id='oldguo'; dsadsadasdasdad
select * from test; (类似以别名)
3.2 tables
数据库中所有表的元数据的查询方法