多表查詢

1. 多表連接查詢 ****

1.1 介紹

內(nèi)連接  inner join
外連接  left  join right join
笛卡爾

1.2 作用

聚合多張表數(shù)據(jù),實(shí)現(xiàn)查詢需求
查詢?nèi)丝跀?shù)小于100人城市名,國家名,國土面積?

1.3 多表連接的語法

1.3.1 內(nèi)連接(交集)

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. 查詢?nèi)丝跀?shù)小于100人城市名,國家名,國土面積?
套路 :  1. 找關(guān)聯(lián)表 2. 找關(guān)系列

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. 統(tǒng)計(jì)查詢每位學(xué)員的平均分
SELECT student.xid ,
student.xname,
AVG(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname 


3. 統(tǒng)計(jì)每位學(xué)員學(xué)習(xí)了幾門課
SELECT student.xid ,
student.xname,
COUNT(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname 


4. 查詢每位老師教的課程名和價(jià)格
SELECT teacher.tname,course.`cname`,course.`cprice`
FROM teacher 
JOIN course
ON  teacher.`tid`=course.`tid`;


5. 每位老師教的學(xué)生的個(gè)數(shù)和姓名列表
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;

綜合練習(xí)題:

查詢oldguo老師教的學(xué)生名
查詢oldguo所教課程的平均分?jǐn)?shù)
每位老師所教課程的平均分,并按平均分排序
查詢白龍馬,學(xué)習(xí)的課程名稱有哪些?
統(tǒng)計(jì)每位老師賺了多少錢?
統(tǒng)計(jì)每門課程學(xué)習(xí)的人數(shù).
查詢oldboy老師教的學(xué)生不及格的學(xué)生名單
統(tǒng)計(jì)每位老師不及格學(xué)生名單
每位老師所教課程的平均分,并按平均分排序
查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績德召；

擴(kuò)展:

統(tǒng)計(jì)各位老師,所教課程的及格率
統(tǒng)計(jì)每門課程:優(yōu)秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的學(xué)生列表

1. 查詢oldguo老師教的學(xué)生名.
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教師名",
GROUP_CONCAT(student.`xname`) AS "學(xué)生列表"
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.tid,teacher.tname;


2. 查詢oldguo所教課程的平均分?jǐn)?shù).
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教師名",
course.cname AS "課程名",
AVG(score.score) AS "平均分"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
WHERE teacher.tname='oldguo'
GROUP BY teacher.tid,teacher.tname,course.cname;


3. 每位老師所教課程的平均分,并按平均分排序.
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教師名",
course.cname AS "課程名",
AVG(score.score) AS "平均分"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.cname;


4. 查詢白龍馬,學(xué)習(xí)的課程名稱有哪些.
SELECT 
CONCAT(student.xname,"_",student.`xid`) AS "學(xué)生姓名",
GROUP_CONCAT(course.`cname`) AS "課程列表"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
WHERE student.xname='白龍馬'
GROUP BY student.xid,student.xname;


5. 統(tǒng)計(jì)每位老師總收入
SELECT teacher.`tname`,COUNT(score.`xid`),SUM(course.`cprice`)
FROM teacher
JOIN course
ON teacher.`tid` = course.`tid`
JOIN score
ON course.`cid` = score.`cid`
GROUP BY teacher.`tid`,teacher.`tname`;

5.1 統(tǒng)計(jì)每位老師,每門課程,分別的收入情況
SELECT 
CONCAT(teacher.`tname`,"_",teacher.`tid`) AS "講師名",
course.`cprice`*COUNT(score.xid) AS "課程收入"
FROM teacher
JOIN course
ON teacher.`tid` = course.`tid`
JOIN score
ON course.`cid` = score.`cid`
GROUP BY teacher.`tid`,teacher.`tname` , course.`cid`;


6. 統(tǒng)計(jì)每門課程學(xué)習(xí)的人數(shù).
SELECT course.`cname`,COUNT(score.`xid`)
FROM course 
JOIN score
ON course.`cid`=score.`cid`
GROUP BY course.`cname`;


7. 查詢oldboy老師教的學(xué)生不及格的學(xué)生名單. 
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教師名"
,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.tid,teacher.tname;


8. 統(tǒng)計(jì)每位老師不及格學(xué)生名單. 
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教師名"
,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.tid,teacher.tname;


9. 每位老師所教課程的平均分,并按平均分排序.
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`  
GROUP BY teacher.tid,teacher.tname,course.`cid`;


10. 查詢各科成績最高和最低的分：以如下形式顯示：課程ID师倔，最高分赏陵，最低分.
SELECT course.`cid`,MAX(score.`score`),MIN(score.`score`)
FROM course
JOIN score
ON course.`cid`=score.`cid` 
GROUP BY course.`cid`;


11. 查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績.
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 ;


擴(kuò)展:
12. 統(tǒng)計(jì)各位老師,所教課程的及格率
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教師名",
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. 統(tǒng)計(jì)每門課程:優(yōu)秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的學(xué)生列表
SELECT 
course.`cname` AS 課程名稱,
GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "優(yōu)秀",
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.5 left/right join 外連接應(yīng)用

一般應(yīng)用在強(qiáng)制驅(qū)動(dòng)表時(shí),強(qiáng)制小結(jié)果集驅(qū)動(dòng)大表
生產(chǎn)中可以使用left join 強(qiáng)制 驅(qū)動(dòng)表.盡量減少next loop的出現(xiàn).
為什么要強(qiáng)制? inner join 優(yōu)化器 自動(dòng)選擇, 按照索引選擇的幾率較大
select a.name,b.telnum from a left join b on a.id=b.id where a.age>=18

1.6 補(bǔ)充別名的應(yīng)用 .

1.6.1 列別名

SELECT 
course.`cname` AS 課程名稱,
GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "優(yōu)秀",
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. 為了顯示的好看.  2. 可以在 having 或 order by 子句中調(diào)用

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;

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; 
-- 查看當(dāng)前庫下的所有表
show tables;
show tables from world;

-- 查看當(dāng)前并發(fā)會話信息
show processlist;
show  full processlist;

-- 查看數(shù)據(jù)庫支持的權(quán)限
show privileges;

-- 查看數(shù)據(jù)庫參數(shù)信息
show variables 
show variables like '%trx%';

-- 查看字符集&校對規(guī)則 
show  charset;
show  collation;

-- 查看建庫&建表語句
show create database world;
show create table world.city;

-- 查看用戶權(quán)限 
show grants for root@'localhost';

-- 查看支持的存儲引擎
show engines;

-- 查詢表中索引信息
show index from world.city;

-- 查看數(shù)據(jù)庫當(dāng)前狀態(tài)信息
show status;
show status like '%lock%';

-- 查看InnoDB引擎相關(guān)的狀態(tài)信息(內(nèi)存,事務(wù),鎖,線程...)
show engine innodb status\G

-- 查看二進(jìn)制日志相關(guān)信息
show binary logs ;
show master status; 
show binlog events in 'xxxx';

-- 查看主從復(fù)制相關(guān)信息
show relaylog events in 'xxxx';
show slave status \G

mysql> help show ;

3. Information_schema 統(tǒng)計(jì)信息庫

3.1 介紹:

視圖 ? 
1. 安全 : 只允許查詢,不知道操作的是什么對象.
2. 方便 : 只需要簡單的select語句即可使用.

3.2 作用:

1. 方便做數(shù)據(jù)庫資產(chǎn)統(tǒng)計(jì)
庫\表 : 
    個(gè)數(shù)
    數(shù)據(jù)量(大小,行數(shù))
    每張表的數(shù)據(jù)字典信息
2. 獲取到Server層狀態(tài)信息
3. 獲取到InnoDB引擎層的狀態(tài)信息

3.3 應(yīng)用舉例:

TABLES : 
TABLE_SCHEMA  : 表所在的庫 
TABLE_NAME    : 表名
ENGINE        : 表的引擎      
TABLE_ROWS    : 表的行數(shù)
AVG_ROW_LENGTH: 平均行長度(字節(jié)) 
INDEX_LENGTH  : 索引占用長度(字節(jié)) 
TABLE_COMMENT : 表注釋 


-- 例子: 
-- 1. 簡單查詢體驗(yàn)TABLES信息
SELECT * FROM TABLES;


-- 2. 所有業(yè)務(wù)庫和表的名字.
SELECT table_schema , table_name 
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');


-- 3. 統(tǒng)計(jì)每個(gè)業(yè)務(wù)庫,表的個(gè)數(shù)和列表
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. 統(tǒng)計(jì)業(yè)務(wù)數(shù)據(jù)庫的總數(shù)據(jù)量
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. 每個(gè)業(yè)務(wù)庫分別統(tǒng)計(jì)數(shù)據(jù)量
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 數(shù)據(jù)量大的表
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. 所有業(yè)務(wù)庫和表的名字,并且生成備份語句
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');

最后編輯于：2019.12.20 20:31:19

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末送火，一起剝皮案震驚了整個(gè)濱河市联贩，隨后出現(xiàn)的幾起案子烈钞，更是在濱河造成了極大的恐慌泊碑，老刑警劉巖，帶你破解...
沈念sama閱讀 218,607評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件棵磷，死亡現(xiàn)場離奇詭異蛾狗，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)仪媒，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,239評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門沉桌，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人算吩，你說我怎么就攤上這事留凭。” “怎么了偎巢？”我有些...
開封第一講書人閱讀 164,960評論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵蔼夜，是天一觀的道長。經(jīng)常有香客問我压昼，道長求冷，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 58,750評論 1贊 294
?港島之戀（遺憾婚禮）
正文為了忘掉前任窍霞，我火速辦了婚禮匠题，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘但金。我一直安慰自己韭山，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 67,764評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著钱磅，像睡著了一般梦裂。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上盖淡，一...
開封第一講書人閱讀 51,604評論 1贊 305
城市分裂傳說
那天年柠，我揣著相機(jī)與錄音，去河邊找鬼禁舷。笑死彪杉，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的牵咙。我是一名探鬼主播派近，決...
沈念sama閱讀 40,347評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼洁桌！你這毒婦竟也來了渴丸？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 39,253評論 0贊 276
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤另凌，失蹤者是張志新（化名）和其女友劉穎谱轨，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體吠谢，經(jīng)...
沈念sama閱讀 45,702評論 1贊 315
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡土童，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,893評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了工坊。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片献汗。...
茶點(diǎn)故事閱讀 40,015評論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖王污，靈堂內(nèi)的尸體忽然破棺而出罢吃，到底是詐尸還是另有隱情，我是刑警寧澤昭齐，帶...
沈念sama閱讀 35,734評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布尿招，位于F島的核電站，受9級特大地震影響阱驾，放射性物質(zhì)發(fā)生泄漏就谜。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,352評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一里覆、第九天我趴在偏房一處隱蔽的房頂上張望丧荐。院中可真熱鬧，春花似錦租谈、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,934評論 0贊 22
一樁弒父案割去，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽窟却。三九已至，卻和暖如春呻逆，著一層夾襖步出監(jiān)牢的瞬間夸赫，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,052評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工咖城，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留茬腿，地道東北人。一個(gè)月前我還...
沈念sama閱讀 48,216評論 3贊 371
代替公主和親
正文我出身青樓宜雀，卻偏偏與公主長得像切平，于是被迫代替她去往敵國和親。傳聞我的和親對象是個(gè)殘疾皇子辐董，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,969評論 2贊 355

多表查詢

1. 多表連接查詢 ****

1.1 介紹

1.2 作用

1.3 多表連接的語法

1.3.1 內(nèi)連接(交集)

1.3.2 外連接

1.3.3 笛卡爾乘積

1.4 多表連接例子

綜合練習(xí)題:

擴(kuò)展:

1.5 left/right join 外連接應(yīng)用

1.6 補(bǔ)充 別名的應(yīng)用 .

1.6.1 列別名

1.6.2 表別名

2. show 語句的列表介紹 ***

3. Information_schema 統(tǒng)計(jì)信息庫

3.1 介紹:

3.2 作用:

3.3 應(yīng)用舉例:

推薦閱讀更多精彩內(nèi)容

1.6 補(bǔ)充別名的應(yīng)用 .