上節(jié)回顧:
mysql停止的流程浪腐，多實例在不知root密碼情況下通過命令行是否能停止
sql語句在查詢不到數(shù)據(jù)時的執(zhí)行流程如何手動停止手動開啟的數(shù)據(jù)庫议街；如何從8.0降級到5.7
mysqladmin -uroot -p -S /tmp/xxx shutdown
3.配合多子句
select from where group by
where
=
< >= <=
like 'aaa%'
and or in
between and
group by + 聚合函數(shù)
一張大表中,需要對具有共同條件的數(shù)據(jù),進行分組統(tǒng)計工作.
max()
min()
count()
sum()
avg()
group_concat
顯示表數(shù)據(jù)時,不能1對多的顯示,5.7中通過sql_mode=ONLY_fULL_GROUP_BY
====================================
from where group by having order by limit
- having 后過濾
--- 統(tǒng)計中國每個省的總?cè)丝跀?shù),只顯示總?cè)丝诖笥?00w
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000;
- order by
--- 查詢中國所有的城市信息,并按照人口數(shù)排序輸出結(jié)果
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY population DESC ;
--- 統(tǒng)計中國每個省的總?cè)丝跀?shù),只顯示總?cè)丝诖笥?00w,并按照總?cè)丝诮敌蜉敵?br> SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC ;
- LIMIT
--- 統(tǒng)計中國每個省的總?cè)丝跀?shù),只顯示總?cè)丝诖笥?00w,并按照總?cè)丝诮敌蜉敵銮?名
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 10;
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 2,3;
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 4 OFFSET 2;
distinct 去重復
mysql> select distinct countrycode from city ;union && union all
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';
union : 去重復
union all : 不去重復
======================
- 多表連接查詢
7.0 多表連接準備
按需求創(chuàng)建一下表結(jié)構(gòu):
use school
student ：學生表
sno： 學號
sname：學生姓名
sage： 學生年齡
ssex： 學生性別
teacher ：教師表
tno： 教師編號
tname：教師名字
course ：課程表
cno： 課程編號
cname：課程名字
tno： 教師編號
score ：成績表
sno： 學號
cno： 課程編號
score：成績
============================================
CREATE DATABASE school CHARSET utf8;
USE school
CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學號',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED NOT NULL COMMENT '年齡',
ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性別'
)ENGINE=INNODB CHARSET=utf8;
CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '課程編號',
cname VARCHAR(20) NOT NULL COMMENT '課程名字',
tno INT NOT NULL COMMENT '教師編號'
)ENGINE=INNODB CHARSET utf8;
CREATE TABLE sc (
sno INT NOT NULL COMMENT '學號',
cno INT NOT NULL COMMENT '課程編號',
score INT NOT NULL DEFAULT 0 COMMENT '成績'
)ENGINE=INNODB CHARSET=utf8;
CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教師編號',
tname VARCHAR(20) NOT NULL COMMENT '教師名字'
)ENGINE=INNODB CHARSET utf8;
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(1,'zhang3',18,'m'),
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f'),
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');
(8,'oldboy',20,'m'),
(9,'oldgirl',20,'f'),
(10,'oldp',25,'m');
INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
===============
7.1 多表連接應用
-- 查詢?nèi)丝跀?shù)量少于100人的城市信息
SELECT * FROM world.city WHERE population<100;
-- 延伸一下: 查詢?nèi)丝跀?shù)量少于100人的城市名,人口數(shù),國家名,國土面積
SELECT
city.name,
city.population,
country.name,
country.surfacearea
FROM city
JOIN country
ON city.countrycode=country.code
WHERE city.population<100;
說明:
1. 通過查詢需求,找到所需表
2. 找到所有表之間的關(guān)聯(lián)關(guān)系
語法:
兩表關(guān)聯(lián):
from a join b on a.x=b.y
多表關(guān)聯(lián):
from a
join c on a.x=c.y
join b on c.z=b.z
例子:
-- 1. 每位老師講的課程名稱
SELECT teacher.tno,teacher.tname,GROUP_CONCAT(course.cname)
FROM teacher
JOIN course
ON teacher.tno=course.tno
GROUP BY teacher.tno;
思考: 如果老師名重名,或者老師講多門課,怎么辦?
-- 2. 每位學員學習了幾門課?
SELECT student.sno,student.sname,COUNT(sc.cno)
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno;
-- 3. 每位學員學習了幾門課及課程名稱列表?
SELECT
student.sno,
student.sname,
COUNT(sc.cno),
GROUP_CONCAT(course.cname)
FROM student
JOIN sc
ON student.sno=sc.sno
JOIN course
ON sc.cno=course.cno
GROUP BY student.sno;
張啟
-- 5. 查詢每位老師所教學生的人數(shù)及姓名
select teacher.tname,group_concat(student.sname),count(student.sname)
from teacher
join course
on teacher.tno = course.tno
join sc
on sc.cno = course.cno
join student
on student.sno = sc.sno
group by teacher.tno;
-- 6. 統(tǒng)計每位老師所教課程的平均分
select teacher.tname,group_concat(course.cname),avg(sc.score)
from teacher
join course
on teacher.tno = course.tno
join sc
on sc.cno = course.cno
group by teacher.tno;
王凱鵬:
-- 7. 統(tǒng)計每位老師所教課程不及格的學生名
select teacher.tname,group_concat(student.sname)
from teacher
join course
on teacher.tno = course.tno
join sc
on sc.cno = course.cno
join student
on student.sno = sc.sno
where sc.score < 60
group by teacher.tno;
-- 8. 查詢平均成績大于60分的同學的學號和平均成績；
select student.sno, avg(sc.score)
from student
join sc
on student.sno = sc.sno
group by student.sno
having avg(sc.score) > 60;
陳嬌娜 1
-- 9. 查詢所有同學的學號丁鹉、姓名悴能、選課數(shù)搜骡、總成績；
select student.sno,student.sname,count(course.cno),sum(sc.score)
from student join sc on student.sno=sc.sno
join course on sc.cno=course.cno
group by student.sno;
-- 10. 查詢各科成績最高和最低的分：以如下形式顯示：課程ID谈竿，最高分空凸，最低分
select course.cno,max(sc.score),min(sc.score)
from course join sc on course.cno=sc.cno
group by course.cno;
楊騰
-- 11. 查詢每門課程被選修的學生數(shù)
select course.cname,count(student.sno)
from sc
on
jion couse
on
join student
group by course.cno
-- 12. 查詢出只選修了一門課程的全部學生的學號和姓名
select student.sno,student.sname
from student
sc
group by sutdent.sno
having count(sc.cno)=1;
荊俊瑋
-- 13. 查詢選修課程門數(shù)超過1門的學生信息
select student.sno,student.sname,****
from student
sc
group by sutdent.sno
having count(sc.cno)>1
-- 15. 查詢平均成績大于85的所有學生的學號呀洲、姓名和平均成績
select student.sno,student.sname,avg(sc.score)
from student
sc
group by sutdent.sno
having avg(sc.score)>85
擴展:
-- 16. -- 50
統(tǒng)計每門課程:
優(yōu)秀(85分以上),
良好(70-85),
一般(60-70),
不及格(小于60)的學生列表
select 課程名 , 優(yōu)秀 , 良好 , 一般 , 不及格
-- 17. -- 50 張現(xiàn)偉
統(tǒng)計各位老師,所教課程的及格率
select teacher.tno,course.cname,concat(count(case when sc.score>60 then 1 end)/count(sc.score)*100,"%") as "啊啊啊" from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by teacher.tno,course.cno;
考試題:
http://www.reibang.com/p/c99e2d3d295b
==============================================
寫多表連接技巧
- 相關(guān)表
- 找相關(guān)表關(guān)聯(lián)條件
E-R 圖 (自己擴展) ----> 開發(fā)DBA要做的事情
外連接
A left join B
A 表所有的數(shù)據(jù)+B表滿足關(guān)聯(lián)條件的數(shù)據(jù)
A right join B
B表所有的數(shù)據(jù)+A 滿足關(guān)聯(lián)條件的數(shù)據(jù)
實現(xiàn)外連接原生功能,需要把where的條件改為and/
mysql> select city.name,city.population ,country.name from city left join country on city.countrycode=country.code and city.population<100 order by population desc ;
結(jié)論: left join 應用場景,強制驅(qū)動表(關(guān)聯(lián)查詢中結(jié)果集小的)
- where 條件
- 原始表大小
- 別名的應用
表別名: 給表設計的別名,在任何子句中調(diào)用
列別名: 給select 后的列設定別名,在having 和 order by子句中調(diào)用
表別名:
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 c.cno = b.cno
join student AS d
on d.sno = c.sno
where c.score < 60
group by a.tno;
列別名:
select
student.sno AS 學生學號 ,
student.sname AS 學生姓名,
avg(sc.score) AS 平均成績
from student
join sc
on student.sno=sc.sno
group by sutdent.sno
having 平均成績 >85
==================
子查詢 : (自己了解)
高級SQL編程: 內(nèi)置函數(shù),存儲過程,函數(shù),視圖,事件,觸發(fā)器,游標,Json開發(fā) (自己了解)
==================
- show 語句
show databases ; 查看所有庫名
show tables; 查看當前庫下的所有表名.
show tables from world; 查看world庫下的所有表
show create database world; 查看建庫語句
show create tables city; 查看建表語句
show [global] variables like '%trx%'; 查看參數(shù)信息
show grants for root@'localhost'; 查看用戶權(quán)限信息
show [full] processlist; 查看會話連接情況
show engines; 查看當前數(shù)據(jù)庫支持的引擎.
show charset; 查看當前數(shù)據(jù)庫支持的字符集.
show collation; 查看當前數(shù)據(jù)庫支持的排序規(guī)則.
show [global] status; 查看當前數(shù)據(jù)庫的狀態(tài)信息.
show status like '%lock%'\G 模糊查詢數(shù)據(jù)庫狀態(tài).
show master status; 查看當前使用的二進制日志信息.
show binary logs; 查看所有二進制日志信息.
show binlog evnets in 'xxxx' 查看二進制日志事件信息.
show relaylog events in 'xxx' 查看中繼日志事件信息.
show slave status \G 查看從庫復制狀態(tài)信息.
show engine innodb status \G 查看InnoDB引擎相關(guān)狀態(tài)信息.
======================
- 元數(shù)據(jù)獲取
10.1 什么是元數(shù)據(jù)?
庫,表 : 屬性(字符集,校對規(guī)則,數(shù)據(jù)類型,存儲引擎,約束,其他數(shù)據(jù))
權(quán)限 :
狀態(tài)信息:
等.
10.2 元數(shù)據(jù)獲取方法
show語句 : 封裝好的基礎功能,可以實現(xiàn)大部分的元數(shù)據(jù)查詢需求.
information_schema<視圖>庫: mysql 給我們定義好的元數(shù)據(jù)查詢的方法.
10.3 information_schema<視圖>庫
應用場景: 做數(shù)據(jù)庫資產(chǎn)統(tǒng)計.
tables :
TABLE_SCHEMA : 表所在的庫
TABLE_NAME : 表名
ENGINE : 引擎
TABLE_ROWS : 表的行數(shù)
AVG_ROW_LENGTH: 平均行長度
INDEX_LENGTH : 索引長度
TABLE_COMMENT : 表的注釋
例子:
-- 1. 統(tǒng)計所有庫下的表的個數(shù)
select table_schema,count(table_name) from information_schema.tables group by table_schema;
-- 2. 統(tǒng)計不同存儲引擎的表名
select engine,group_concat(table_name) from information_schema.tables group by engine;
-- 3. 統(tǒng)計所有非系統(tǒng)表,非InnoDB的表
mysql
information_schema
performace_schema
sys
SELECT table_schema,table_name ,ENGINE
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
HAVING ENGINE != 'innodb';
