数据库day05-SQL基础及元数据获取03

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

说明：表别名一般是在FROM的表的表名，或者join后的表的别名
在 WHERE,GROUP BY,select后的列，having,ORDER BY

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.外链接简介☆☆☆

left join 左链接
right join 右链接

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语法
=================================

综合练习

1. 查询平均成绩大于60分的同学的学号和平均成绩；

2. 查询所有同学的学号、姓名、选课数、总成绩；

3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 

4. 统计各位老师,所教课程的及格率

5. 查询每门课程被选修的学生数

6. 查询出只选修了一门课程的全部学生的学号和姓名

7. 查询选修课程门数超过1门的学生信息

8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

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;

SELECT CONCAT("alter table ",table_schema,".","table_name"," engine=innodb;")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/engine.sql';
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 158,736评论 4赞 362
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,167评论 1赞 291
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 108,442评论 0赞 243
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,902评论 0赞 204
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,302评论 3赞 287
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,573评论 1赞 216
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,847评论 2赞 312
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,562评论 0赞 197
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,260评论 1赞 241
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,531评论 2赞 245
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,021评论 1赞 258
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,367评论 2赞 253
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,016评论 3赞 235
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,068评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,827评论 0赞 194
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,610评论 2赞 274
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,514评论 2赞 269