大家好，我是讯享网，很高兴认识大家。

讯享网

1. join多表查询

1.1 作用

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

1.2 多表连接类型

内连接 ☆☆☆☆☆
外连接 ☆☆☆
全连接
笛卡尔

1.3 内连接的类型

传统连接 where 自连接 join uing ☆☆ join on ☆☆☆☆☆ 

讯享网

1.4 join on 的语法

表A和表B横向拼成 一行

讯享网查询张三的家庭住址 SELECT A.name,B.address FROM A JOIN B ON A.id=B.id WHERE A.name='zhangsan' 

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

1. 查询一下世界上人口数量小于100人的城市名和国家名

讯享网SELECT b.name ,a.name ,a.population FROM city AS a JOIN country AS b ON b.code=a.countrycode WHERE a.Population<100 

多表连接的套路：
1.根据需求找到关联表
2.找到表与表的关联列

2. 多表连接案例

#进入world库练习 use world; 

2.1查询一下世界上人口数量小于100人的城市名，国家名，国土面积和人口数

讯享网SELECT country.name,country.SurfaceArea,city.name,city.Population FROM city JOIN country ON city.CountryCode=country.code WHERE city.Population<100; 

2.2 打开school表

2.2.1 查询zhangs学习了几门课程

SELECT student.sname,COUNT(sc.cno) FROM student JOIN sc ON student.sno=sc.sno WHERE student.sname='zhang3'; 

2.2.2 统计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' GROUP BY student.sno; 

2.2.3 oldguo老师教了学生的个数

SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno JOIN teacher ON course.tno=teacher.tno WHERE teacher.tname='oldguo' GROUP BY teacher.tno; 

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

讯享网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 ORDER BY AVG(sc.score) DESC; 

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

SELECT teacher.tname,student.sname,sc.score 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; 

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

讯享网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 

3.别名的使用

SELECT 别名.列
FROM 表 表别名
WHERE 别名.列
GROUP BY 别名.列
HAVING 列别名
ORDER BY 列别名

3.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 

3.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后的列，定义的别名
– 作用：
– 1.结果集显示会以别名形式展示
– 2.在having 和order by 中可以调用列别名

-- 例子：每位老师所教课程的平均分,并按平均分排序 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 平均分; 

---

4.外链接简介☆☆☆

讯享网A left join B on A.x=B.y where A left join B on A.x=B.y and 

结论：

1. 多表连接中，小标驱动大表
2. 通过left join 强制选定驱动表

---

=================================
工作中还需要学习的内容：
1.内置函数
2.存储过程
3.函数
4.触发器
5.事件
6.视图
7.JSON语法
=================================

5.元数据获取

5.1 元数据介绍

”基表“ ：数据字典信息(列结构 frm)，系统状态，对象状态

相当于linux中的 inode

DDL DCL语句修改元数据

5.1 show语句(MySQL独家)

讯享网help show; show databases; #查看所有数据库 show tables; #查看当前库的所有表 SHOW TABLES FROM #查看某个指定库下的表 show create database world #查看建库语句 show create table world.city #查看建表语句 show grants for root@'localhost' #查看用户的权限信息 show charset； #查看字符集 show collation #查看校对规则 show processlist; #查看数据库连接情况 show index from #表的索引情况 show status #数据库状态查看 SHOW STATUS LIKE '%lock%'; #模糊查询数据库某些状态 SHOW VARIABLES #查看所有配置信息 SHOW variables LIKE '%lock%'; #查看部分配置信息 show engines #查看支持的所有的存储引擎 show engine innodb status\G #查看InnoDB引擎相关的状态信息 show binary logs #列举所有的二进制日志 show master status #查看数据库的日志位置信息 show binlog evnets in #查看二进制日志事件 show slave status \G #查看从库状态 SHOW RELAYLOG EVENTS #查看从库relaylog事件信息 desc (show colums from city) #查看表的列定义信息 

5.2 information_schema.tables 虚拟库

information_schema —> VIEWS 视图

use information_schema; show tables; 

讯享网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; 

5.2.1 TABLES 作用和结构

作用: 存储整个数据库中,所有表的元数据的查询方式.

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

例子

讯享网1.查询world 数据库下的所有表名 use world; show tables; && show tables from world; 

2.查询整个数据库下的所有表名 select table_name from information_schema.tables; 

讯享网3.查询所有InnoDB引擎的表 SELECT table_schema,table_name,ENGINE 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 FROM information_schema.tables GROUP BY table_schema; 

中小型公司：数据量大小一般在 300G 500G

中大型公司：数据量大小一般在 2T 10T

6.对MySQL的数据库进行分库分表备份 mysqldump -uroot -p123 world city >/backup/world_city.sql SELECT CONCAT("mysqldump -uroot -p123 ",table_schema ," ",table_name ," >/backup/",table_schema, "_",table_name,".sql") FROM information_schema.tables INTO OUTFILE '/tmp/bak.sql'; 

添加配置

讯享网7.模仿模板语句，批量生成对world数据库下的表操作语句 alter table world.city discard tablespace; SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace;") FROM information_schema.tables WHERE table_schema='world' INTO OUTFILE '/tmp/discard.sql'; 

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

---