day 05 多表連接查詢(xún)

day 05 多表連接查詢(xún)

1.select 多表

1.1 介紹
內(nèi)連接  inner join
外連接  left  join right join
笛卡爾  
1.2 作用
聚合多張表數(shù)據(jù),實(shí)現(xiàn)查詢(xún)需求
查詢(xún)?nèi)丝跀?shù)小于100人城市名,國(guó)家名,國(guó)土面積?

1.3 多表連接的語(yǔ)法
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. 查詢(xún)?nèi)丝跀?shù)小于100人城市名,國(guó)家名,國(guó)土面積?
-- 套路 :  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ì)查詢(xún)每位學(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í)了幾門(mén)課
SELECT student.xid ,
student.xname,
COUNT(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname 

-- 4. 查詢(xún)每位老師教的課程名和價(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;


實(shí)例：

1. 查詢(xún)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.`tname`,teacher.`tid`
2.查詢(xún)oldguo所教課程的平均分?jǐn)?shù).
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.查詢(xún)白龍馬,學(xué)習(xí)的課程名稱(chēng)有哪些.
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. 統(tǒng)計(jì)每位老師,每門(mén)課程,分別的收入情況
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.統(tǒng)計(jì)每門(mén)課程學(xué)習(xí)的人數(shù).
SELECT course.`cname`,COUNT(score.`xid`)
FROM course JOIN score
ON course.`cid`=score.`cid`
GROUP BY course.`cname`

7.查詢(xún)oldboy老師教的學(xué)生不及格的學(xué)生名單. 
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.統(tǒng)計(jì)每位老師不及格學(xué)生名單. 
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.查詢(xún)各科成績(jī)最高和最低的分：以如下形式顯示：課程ID蒜鸡，最高分腰吟，最低分.
SELECT course.`cid`,MAX(score.`score`),MIN(score.`score`)
FROM score JOIN course
ON score.`cid`=course.`cid`
GROUP BY course.`cid`

11.查詢(xún)平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī).

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.統(tǒng)計(jì)各位老師,所教課程的及格率
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.統(tǒng)計(jì)每門(mén)課程:優(yōu)秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的學(xué)生列表
SELECT course.`cname`,
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.`c

1.5 left/right join   外連接應(yīng)用
  inner join 內(nèi)連接應(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)選擇，按照索引選擇的幾率較大鳄橘，導(dǎo)致效率比較低折柠。通過(guò)大量的讀寫(xiě)磁盤(pán)，導(dǎo)致磁盤(pán)的損壞带膜。

1.6.1 列別名

SELECT 
course.`cname` AS 課程名稱(chēng),
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.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 語(yǔ)句的列表介紹

----查看所有的庫(kù)
show databases;
--查看當(dāng)前庫(kù)下的所有表
show tables;
show tables from world;
--查看當(dāng)前并發(fā)會(huì)話信息
show processlist;
--查看數(shù)據(jù)庫(kù)支持的權(quán)限
show  privileges;

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

--查看字符集&效對(duì)規(guī)則
show charset;  #查看字符集
show collation;   #查看效對(duì)信息
---查看建庫(kù)&建表信息
mysql> show create database  world;
mysql> show create table world.city;

-- 查看用戶權(quán)限 
mysql> show grants for oldguo@'10.0.0.%';
-- 查看支持的存儲(chǔ)引擎
mysql> show engines;
-- 查詢(xún)表中索引信息
mysql> show index from world.city;
-- 查看數(shù)據(jù)庫(kù)當(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ì)信息庫(kù)

3.1 介紹

視圖 ?

  1. 安全 : 只允許查詢(xún),不知道操作的是什么對(duì)象.

  2. 方便 : 只需要簡(jiǎn)單的select語(yǔ)句即可使用

    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.方便做數(shù)據(jù)庫(kù)資產(chǎn)統(tǒng)計(jì)

資產(chǎn)就包括： 庫(kù) 表

庫(kù)\表 :
個(gè)數(shù)
數(shù)據(jù)量(大小,行數(shù))
每張表的數(shù)據(jù)字典信息

2.獲取到server 層狀態(tài)信息

3.獲取到innodb 引擎層的狀態(tài)信息

3.3 應(yīng)用舉例

tables

table_schema: 表所在的庫(kù)

table_name: 表名

engine : 表的引擎

table_rows: 表的行數(shù)

AVG_ROW_LENGTH: 平均行長(zhǎng)度（字節(jié)）

INDEX_LENGTH: 索引占用的長(zhǎng)度（字節(jié)）

TABLE_COMMENT:表注釋

---統(tǒng)計(jì)信息庫(kù)information_schema

例：
 1. 簡(jiǎn)單查詢(xún)體驗(yàn)TABLES信息

SELECT * FROM TABLES;
2. 所有業(yè)務(wù)庫(kù)和表的名字.
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ù)庫(kù),表的個(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ù)庫(kù)的總數(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ù)庫(kù)分別統(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.查詢(xún)所有非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.查詢(xún)所有非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ù)庫(kù)和表的名字,并且生成備份語(yǔ)句
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')
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末吩谦，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子膝藕，更是在濱河造成了極大的恐慌式廷，老刑警劉巖，帶你破解...
    沈念sama閱讀 211,290評(píng)論 6贊 491
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件芭挽，死亡現(xiàn)場(chǎng)離奇詭異滑废，居然都是意外死亡蝗肪，警方通過(guò)查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,107評(píng)論 2贊 385
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門(mén)蠕趁，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)薛闪，“玉大人，你說(shuō)我怎么就攤上這事俺陋』硌樱” “怎么了？”我有些...
    開(kāi)封第一講書(shū)人閱讀 156,872評(píng)論 0贊 347
  • 道士緝兇錄：失蹤的賣(mài)姜人
    文/不壞的土叔 我叫張陵腊状，是天一觀的道長(zhǎng)诱咏。 經(jīng)常有香客問(wèn)我，道長(zhǎng)寿酌，這世上最難降的妖魔是什么胰苏？ 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 56,415評(píng)論 1贊 283
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮醇疼，結(jié)果婚禮上硕并，老公的妹妹穿的比我還像新娘。我一直安慰自己秧荆，他們只是感情好倔毙，可當(dāng)我...
    茶點(diǎn)故事閱讀 65,453評(píng)論 6贊 385
  • 惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
    文/花漫 我一把揭開(kāi)白布。 她就那樣靜靜地躺著乙濒，像睡著了一般陕赃。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上颁股，一...
    開(kāi)封第一講書(shū)人閱讀 49,784評(píng)論 1贊 290
  • 城市分裂傳說(shuō)
    那天么库，我揣著相機(jī)與錄音，去河邊找鬼甘有。 笑死诉儒，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的亏掀。 我是一名探鬼主播忱反，決...
    沈念sama閱讀 38,927評(píng)論 3贊 406
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開(kāi)眼，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼滤愕！你這毒婦竟也來(lái)了温算？” 一聲冷哼從身側(cè)響起，我...
    開(kāi)封第一講書(shū)人閱讀 37,691評(píng)論 0贊 266
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤间影，失蹤者是張志新（化名）和其女友劉穎注竿，沒(méi)想到半個(gè)月后，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 44,137評(píng)論 1贊 303
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡巩割，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,472評(píng)論 2贊 326
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年胰丁，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片喂分。...
    茶點(diǎn)故事閱讀 38,622評(píng)論 1贊 340
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖机蔗，靈堂內(nèi)的尸體忽然破棺而出蒲祈，到底是詐尸還是另有隱情，我是刑警寧澤萝嘁，帶...
    沈念sama閱讀 34,289評(píng)論 4贊 329
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布梆掸，位于F島的核電站，受9級(jí)特大地震影響牙言，放射性物質(zhì)發(fā)生泄漏酸钦。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,887評(píng)論 3贊 312
  • 男人毒藥：我在死后第九天來(lái)索命
    文/蒙蒙 一咱枉、第九天 我趴在偏房一處隱蔽的房頂上張望卑硫。 院中可真熱鬧，春花似錦蚕断、人聲如沸欢伏。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 30,741評(píng)論 0贊 21
  • 一樁弒父案亿乳，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)硝拧。三九已至，卻和暖如春葛假，著一層夾襖步出監(jiān)牢的瞬間障陶，已是汗流浹背。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 31,977評(píng)論 1贊 265
  • 情欲美人皮
    我被黑心中介騙來(lái)泰國(guó)打工聊训， 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留抱究，地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 46,316評(píng)論 2贊 360
  • 代替公主和親
    正文 我出身青樓魔眨，卻偏偏與公主長(zhǎng)得像媳维，于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子遏暴，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,490評(píng)論 2贊 348

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

  • Student(S#,Sname,Sage,Ssex) 學(xué)生表 Course(C#,Cname,T#) 課程表 S...
    忘了呼吸的那只貓閱讀 2,850評(píng)論 0贊 8
  • 50個(gè)常用的sql語(yǔ)句Student(S#,Sname,Sage,Ssex) 學(xué)生表Course(C#,Cname...
    哈哈海閱讀 1,226評(píng)論 0贊 7
  • /*Course（課程表） 表結(jié)構(gòu) CID(課程編號(hào)),Cname（課程名）,TID(老師編號(hào))SC(成績(jī)表) 表...
    雁_9587閱讀 271評(píng)論 0贊 0
  • 說(shuō)明：以下五十個(gè)語(yǔ)句都按照測(cè)試數(shù)據(jù)進(jìn)行過(guò)測(cè)試侄刽，最好每次只單獨(dú)運(yùn)行一個(gè)語(yǔ)句。 問(wèn)題及描述： --1.學(xué)生表 Stud...
    lijun_m閱讀 1,294評(píng)論 0贊 1
  • 多表連接查詢(xún) 1朋凉、作用 業(yè)務(wù)需要的數(shù)據(jù)來(lái)自多張表時(shí) 2州丹、多表連接查詢(xún)基本語(yǔ)法 ????內(nèi)連接?? 外鏈接全鏈...
    我要笑閱讀 505評(píng)論 0贊 2