上集回顧:
1.DSQL - SELECT
1.1 不用子句
select @@port;
select database();
select 1+1 from dual;
1.2 配合子句
FROM --> WHERE GROUP BY HAVING ORDER BY LIMIT
select a,count(b) from t group by a;
面試題: 執(zhí)行過(guò)程?
- 排序
- 去重復(fù)
- 函數(shù)聚合
面試題: 報(bào)錯(cuò)SQL_MODE(5.7+)=ONLY_FULL_GROUP_BY怎么解決？
mysql> select name,district,count(name) from world.city where countrycode='CHN' group by district;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
- select后的列,不在group by子句后
- 也不在聚合函數(shù)中出現(xiàn)的.
mysql> select group_concat(name),district,count(name) from world.city where countrycode='CHN' group by district;
經(jīng)典的案例(性能低下的例子)
limit 500w 100 ----> > and <
====================================
1. 多表連接查詢(xún)★★★★
1.1 介紹
內(nèi)連接 inner join
外連接 left join right join
笛卡爾 內(nèi)容自由組合 A表內(nèi)容*B表內(nèi)容
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;
綜合練習(xí)題:
- 查詢(xún)oldguo老師教的學(xué)生名
- 查詢(xún)oldguo所教課程的平均分?jǐn)?shù)
- 每位老師所教課程的平均分,并按平均分排序
- 查詢(xún)白龍馬,學(xué)習(xí)的課程名稱(chēng)有哪些?
- 統(tǒng)計(jì)每位老師賺了多少錢(qián)?
- 統(tǒng)計(jì)每門(mén)課程學(xué)習(xí)的人數(shù).
- 查詢(xún)oldboy老師教的學(xué)生不及格的學(xué)生名單
- 統(tǒng)計(jì)每位老師不及格學(xué)生名單
- 每位老師所教課程的平均分,并按平均分排序
- 查詢(xún)各科成績(jī)最高和最低的分：以如下形式顯示：課程ID护奈，最高分，最低分
- 查詢(xún)平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)竹伸；
擴(kuò)展: - 統(tǒng)計(jì)各位老師,所教課程的及格率
- 統(tǒng)計(jì)每門(mén)課程:優(yōu)秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的學(xué)生列表
-- 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.tid,teacher.tname;
-- 2. 查詢(xún)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. 查詢(xún)白龍馬,學(xué)習(xí)的課程名稱(chēng)有哪些.
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ì)每位老師,每門(mén)課程,分別的收入情況
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ì)每門(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
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. 查詢(xún)各科成績(jī)最高和最低的分：以如下形式顯示：課程ID旱易，最高分岖瑰，最低分.
SELECT course.`cid`,MAX(score.`score`),MIN(score.`score`)
FROM course
JOIN score
ON course.`cid`=score.`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 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ì)每門(mén)課程:優(yōu)秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的學(xué)生列表
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.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 課程名稱(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`;
說(shuō)明: 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 語(yǔ)句的列表介紹 ***
-- 查看所有的庫(kù)
show databases;
-- 查看當(dāng)前庫(kù)下的所有表
show tables;
show tables from world;
-- 查看當(dāng)前并發(fā)會(huì)話(huà)信息
show processlist;
show full processlist;
-- 查看數(shù)據(jù)庫(kù)支持的權(quán)限
show privileges;
-- 查看數(shù)據(jù)庫(kù)參數(shù)信息
show variables
show variables like '%trx%';
-- 查看字符集&校對(duì)規(guī)則
show charset;
show collation;
-- 查看建庫(kù)&建表語(yǔ)句
show create database world;
show create table world.city;
-- 查看用戶(hù)權(quán)限
show grants for root@'localhost';
-- 查看支持的存儲(chǔ)引擎
show engines;
-- 查詢(xún)表中索引信息
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ù),鎖,線(xiàn)程...)
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 介紹:
視圖 ?
- 安全 : 只允許查詢(xún),不知道操作的是什么對(duì)象.
- 方便 : 只需要簡(jiǎn)單的select語(yǔ)句即可使用.
3.2 作用:
- 方便做數(shù)據(jù)庫(kù)資產(chǎn)統(tǒng)計(jì)
庫(kù)\表 :
個(gè)數(shù)
數(shù)據(jù)量(大小,行數(shù))
每張表的數(shù)據(jù)字典信息 - 獲取到Server層狀態(tài)信息
- 獲取到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 : 表注釋
-- 例子:
-- 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');
