day 05 多表连接查询

1.select 多表

1.1 介绍
内连接  inner join
外连接  left  join right join
笛卡尔  
1.2 作用
聚合多张表数据,实现查询需求
查询人口数小于100人城市名,国家名,国土面积?

1.3 多表连接的语法
1.3.1 内连接(交集)
FROM A 
INNER JOIN B 
ON  A.x=B.y

1.3.2 外连接

FROM A 
LEFT JOIN B 
ON  A.x=B.y

FROM A 
RIGHT JOIN B 
ON  A.x=B.y

1.3.3 笛卡尔乘积
FROM A 
JOIN B 


1.4 多表连接例子 
-- 1. 查询人口数小于100人城市名,国家名,国土面积?
-- 套路 :  1. 找关联表 2. 找关系列

USE world
SHOW TABLES;
DESC city;
DESC country;

SELECT 
city.name,
country.name, 
country.SurfaceArea,
city.`Population`
FROM city
JOIN country
ON city.`CountryCode`=country.`Code`
WHERE city.`Population`<100


-- 2. 统计查询每位学员的平均分

SELECT student.xid ,
student.xname,
AVG(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname 


-- 3. 统计每位学员学习了几门课
SELECT student.xid ,
student.xname,
COUNT(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname 

-- 4. 查询每位老师教的课程名和价格
SELECT teacher.tname,course.`cname`,course.`cprice`
FROM teacher 
JOIN course
ON  teacher.`tid`=course.`tid`;

-- 5. 每位老师教的学生的个数和姓名列表
SELECT 
teacher.tname,
COUNT(student.xid),
GROUP_CONCAT(student.xname)
FROM student 
JOIN score 
ON student.xid=score.xid 
JOIN course
ON score.cid=course.cid
JOIN teacher 
ON course.tid=teacher.tid
GROUP BY teacher.tid,teacher.tname;


实例：

1. 查询oldguo老师教的学生名
SELECT CONCAT(teacher.`tname`,"_",teacher.`tid`) AS "教师名",
GROUP_CONCAT(student.`xname`) AS "学生名"
FROM teacher JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`
JOIN student
ON score.`xid`=student.`xid`
WHERE  teacher.`tname`='oldguo'
GROUP BY teacher.`tname`,teacher.`tid`
2.查询oldguo所教课程的平均分数.
SELECT CONCAT(teacher.`tname`,"_",teacher.`tid`) AS "教师名",
course.`cname`,
AVG(score.`score`)
FROM teacher JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`
WHERE teacher.`tname`='oldguo'
GROUP BY teacher.`tname`,teacher.`tid`,course.`cname`
3.每位老师所教课程的平均分,并按平均分排序.
SELECT CONCAT(teacher.`tname`,"_",teacher.`tid`),
course.`cname`,AVG(score.`score`)
FROM teacher JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`
GROUP BY teacher.`tname`,teacher.`tid`,course.`cname`
ORDER BY score.`score` DESC

4.查询白龙马,学习的课程名称有哪些.
SELECT CONCAT(student.`xname`,"_",student.`xid`),
GROUP_CONCAT(course.`cname`)
FROM student JOIN score
ON student.`xid`=score.`xid`
JOIN course
ON score.`cid`=course.`cid`
WHERE  student.`xname`='白龙马'
GROUP BY student.`xname`,student.`xid`
5. 统计每位老师,每门课程,分别的收入情况
SELECT teacher.`tname`,course.`cname`,course.`cprice`*COUNT(score.`xid`)
FROM teacher JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`
GROUP BY teacher.`tname`,teacher.`tid`,course.`cid`

6.统计每门课程学习的人数.
SELECT course.`cname`,COUNT(score.`xid`)
FROM course JOIN score
ON course.`cid`=score.`cid`
GROUP BY course.`cname`

7.查询oldboy老师教的学生不及格的学生名单. 
SELECT teacher.`tname`,GROUP_CONCAT(CONCAT(student.`xname`,":",score.`score`))
FROM teacher JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`
JOIN student
ON score.`xid`=student.`xid`
WHERE teacher.`tname`='oldboy' AND score.`score`<60
GROUP BY teacher.`tname`,teacher.`tid`

8.统计每位老师不及格学生名单. 
SELECT teacher.`tname`,GROUP_CONCAT(CONCAT(student.`xname`,":",score.`score`))
FROM teacher JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`
JOIN student
ON score.`xid`=student.`xid`
WHERE score.`score`<60
GROUP BY teacher.`tname`,teacher.`tid`
9.每位老师所教课程的平均分,并按平均分排序.
SELECT teacher.`tname`,course.`cname`,
AVG(score.`score`)
FROM teacher JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`
GROUP BY teacher.`tname`,teacher.`tid`,course.`cid`
ORDER BY AVG(score.`score`) DESC


10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分.
SELECT course.`cid`,MAX(score.`score`),MIN(score.`score`)
FROM score JOIN course
ON score.`cid`=course.`cid`
GROUP BY course.`cid`

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

SELECT CONCAT(student.`xname`,"_",student.`xid`),AVG(score.`score`)
FROM score
JOIN student
ON score.`xid`=student.`xid`
GROUP BY student.`xname`,student.`xid`
HAVING  AVG(score.`score`)>60
或
SELECT 
CONCAT(student.`xname`,"_",student.`xid`),
AVG(score.`score`)
FROM student
JOIN score
ON student.`xid`=score.`xid`
GROUP BY student.`xname`,student.`xid`
HAVING AVG(score.`score`)>60
ORDER BY AVG(score.`score`) DESC ;

12.统计各位老师,所教课程的及格率
SELECT teacher.`tname`,course.`cname`,
CONCAT(COUNT(CASE WHEN score.`score`>60 THEN 1 END )/COUNT(score.`xid`)*100,"%")
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY  teacher.`tid`,teacher.`tname`,course.`cid`


13.统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
SELECT course.`cname`,
GROUP_CONCAT(CASE WHEN score.`score`>=85 THEN student.`xname` END ) AS '优秀',
GROUP_CONCAT(CASE WHEN score.`score`>=70 AND score.`score`<85 THEN student.`xname` END ) AS '良好',
GROUP_CONCAT(CASE WHEN score.`score`>=60 AND score.`score`<70 THEN student.`xname` END ) AS '一般',
GROUP_CONCAT(CASE WHEN score.`score`<60 THEN student.`xname` END ) AS '不及格'
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
GROUP BY course.`c

1.5 left/right join   外连接应用
  inner join 内连接应用
  一般应用在强制驱动表时，强制小结果集驱动大表
  ---生产总可以使用left join 强制驱动表 ，尽量减少next loop 的出现
  ---为啥要强制？
    inner join 优化器  自动选择，按照索引选择的几率较大，导致效率比较低。通过大量的读写磁盘，导致磁盘的损坏。

1.6.1 列别名

SELECT 
course.`cname` AS 课程名称,
GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "优秀",
GROUP_CONCAT(CASE WHEN score.`score` >=70 AND score.`score` < 85  THEN student.xname  END) AS "良好",
GROUP_CONCAT(CASE WHEN score.`score` >=60  AND score.`score` <70 THEN  student.xname  END  )AS "一般",
GROUP_CONCAT(CASE WHEN score.`score` <60  THEN  student.xname END  ) AS "不及格"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
GROUP BY course.`cid`;


1.6.2 表别名
SELECT 
CONCAT(te.tname,"_",te.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(st.xname,":",sc.score))
FROM teacher AS te 
JOIN course AS  co 
ON te.`tid`=co.`tid`
JOIN score AS sc 
ON co.`cid`=sc.`cid`  
JOIN student  AS st
ON sc.`xid`=st.`xid`
WHERE sc.`score`<60
GROUP BY te.tid,te.tname;

2.show 语句的列表介绍

----查看所有的库
show databases;
--查看当前库下的所有表
show tables;
show tables from world;
--查看当前并发会话信息
show processlist;
--查看数据库支持的权限
show  privileges;

--查看数据库参数信息
show variables;
show variables like '%trx%';

--查看字符集&效对规则
show charset;  #查看字符集
show collation;   #查看效对信息
---查看建库&建表信息
mysql> show create database  world;
mysql> show create table world.city;

-- 查看用户权限 
mysql> show grants for oldguo@'10.0.0.%';
-- 查看支持的存储引擎
mysql> show engines;
-- 查询表中索引信息
mysql> show index from world.city;
-- 查看数据库当前状态信息
show status;
show status like '%lock%';
-- 查看InnoDB引擎相关的状态信息(内存,事务,锁,线程...)
show engine innodb status\G

-- 查看二进制日志相关信息
show binary logs ;
show master status; 
show binlog events in 'xxxx';
-- 查看主从复制相关信息
show relaylog events in 'xxxx';
show slave status \G

mysql> help show ;

3. information_schema 统计信息库

3.1 介绍

视图 ?

安全 : 只允许查询,不知道操作的是什么对象.

方便 : 只需要简单的select语句即可使用

create view a
as
SELECT 
CONCAT(te.tname,"_",te.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(st.xname,":",sc.score))
FROM teacher AS te 
JOIN course AS  co 
ON te.`tid`=co.`tid`
JOIN score AS sc 
ON co.`cid`=sc.`cid`  
JOIN student  AS st
ON sc.`xid`=st.`xid`
WHERE sc.`score`<60
GROUP BY te.tid,te.tname;

select * from a;

3.2 作用：

1.方便做数据库资产统计

资产就包括：库表

库\表 :
个数
数据量(大小,行数)
每张表的数据字典信息

2.获取到server 层状态信息

3.获取到innodb 引擎层的状态信息

3.3 应用举例

tables

table_schema: 表所在的库

table_name: 表名

engine : 表的引擎

table_rows: 表的行数

AVG_ROW_LENGTH: 平均行长度（字节）

INDEX_LENGTH: 索引占用的长度（字节）

TABLE_COMMENT:表注释

---统计信息库information_schema

例：
 1. 简单查询体验TABLES信息

SELECT * FROM TABLES;
2. 所有业务库和表的名字.
SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_schema NOT IN('sys','information_schema','performance_schema','mysql');

3 .统计每个业务库,表的个数和列表
SELECT TABLE_SCHEMA,COUNT(TABLE_NAME),GROUP_CONCAT(TABLE_NAME)
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY TABLE_SCHEMA

4. 统计业务数据库的总数据量
SELECT SUM(TABLE_rows * AVG_ROW_LENGTH+INDEX_LENGTH)/1024 AS total_kb
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','performance_schema','mysql');

5.每个业务库分别统计数据量
SELECT TABLE_SCHEMA,SUM(TABLE_ROWS * AVG_ROW_LENGTH+INDEX_LENGTH)/1024 AS TOTAL_kb
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY TABLE_SCHEMA
ORDER BY  total_kb  DESC;

6.top 3 数据量大的表
SELECT TABLE_SCHEMA,TABLE_NAME,(TABLE_ROWS * AVG_ROW_LENGTH+INDEX_LENGTH)/1024 AS table_kb
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','performance_schema','mysql')
ORDER BY table_kb DESC
LIMIT 3
7.查询所有非INNODB的表
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','performance_schema','mysql') 
AND ENGINE <> 'innodb'

8.查询所有非INNODB的表 , 并且提出修改建议

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CONCAT("alter table",table_schema,".",table_name,"engine=innodb;") AS "修改建议"
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','performance_schema','mysql') 
AND ENGINE <> 'innodb'

9.所有业务库和表的名字,并且生成备份语句
SELECT TABLE_SCHEMA,TABLE_NAME,
CONCAT("mysqldump", TABLE_SCHEMA,".",table_name, "> /bak/",table_schema,"_",table_name,".sql") AS "备份"
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','performance_schema','mysql')

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

day 05 多表连接查询

day 05 多表连接查询

1.select 多表

2.show 语句的列表介绍

3. information_schema 统计信息库

推荐阅读更多精彩内容