數(shù)據(jù)庫 4

上節(jié)回顧:

  1. mysql停止的流程浪腐,多實例在不知root密碼情況下通過命令行是否能停止
    sql語句在查詢不到數(shù)據(jù)時的執(zhí)行流程

  2. 如何手動停止手動開啟的數(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

  1. having 后過濾
    --- 統(tǒng)計中國每個省的總?cè)丝跀?shù),只顯示總?cè)丝诖笥?00w
    SELECT district , SUM(population)
    FROM city
    WHERE countrycode='CHN'
    GROUP BY district
    HAVING SUM(population)>5000000;
  1. 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 ;
  1. 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;

  1. distinct 去重復
    mysql> select distinct countrycode from city ;

  2. 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 : 不去重復

======================

  1. 多表連接查詢

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
==============================================
寫多表連接技巧

  1. 相關(guān)表
  2. 找相關(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é)果集小的)

  1. where 條件
  2. 原始表大小
  1. 別名的應用
    表別名: 給表設計的別名,在任何子句中調(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ā) (自己了解)
==================

  1. 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)信息.

======================

  1. 元數(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';

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市巩那,隨后出現(xiàn)的幾起案子即横,更是在濱河造成了極大的恐慌,老刑警劉巖跺嗽,帶你破解...
    沈念sama閱讀 222,946評論 6 518
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件抛蚁,死亡現(xiàn)場離奇詭異瞧甩,居然都是意外死亡弥鹦,警方通過查閱死者的電腦和手機彬坏,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,336評論 3 399
  • 文/潘曉璐 我一進店門栓始,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人禀忆,你說我怎么就攤上這事±胙” “怎么了戴涝?”我有些...
    開封第一講書人閱讀 169,716評論 0 364
  • 文/不壞的土叔 我叫張陵啥刻,是天一觀的道長可帽。 經(jīng)常有香客問我,道長钝满,這世上最難降的妖魔是什么申窘? 我笑而不...
    開封第一講書人閱讀 60,222評論 1 300
  • 正文 為了忘掉前任剃法,我火速辦了婚禮贷洲,結(jié)果婚禮上收厨,老公的妹妹穿的比我還像新娘。我一直安慰自己优构,他們只是感情好诵叁,可當我...
    茶點故事閱讀 69,223評論 6 398
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著钦椭,像睡著了一般拧额。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上彪腔,一...
    開封第一講書人閱讀 52,807評論 1 314
  • 那天侥锦,我揣著相機與錄音,去河邊找鬼德挣。 笑死,一個胖子當著我的面吹牛,可吹牛的內(nèi)容都是我干的番挺。 我是一名探鬼主播唠帝,決...
    沈念sama閱讀 41,235評論 3 424
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼建芙!你這毒婦竟也來了没隘?” 一聲冷哼從身側(cè)響起懂扼,我...
    開封第一講書人閱讀 40,189評論 0 277
  • 序言:老撾萬榮一對情侶失蹤禁荸,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后阀湿,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體赶熟,經(jīng)...
    沈念sama閱讀 46,712評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,775評論 3 343
  • 正文 我和宋清朗相戀三年陷嘴,在試婚紗的時候發(fā)現(xiàn)自己被綠了映砖。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 40,926評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡灾挨,死狀恐怖邑退,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情劳澄,我是刑警寧澤地技,帶...
    沈念sama閱讀 36,580評論 5 351
  • 正文 年R本政府宣布,位于F島的核電站秒拔,受9級特大地震影響莫矗,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜砂缩,卻給世界環(huán)境...
    茶點故事閱讀 42,259評論 3 336
  • 文/蒙蒙 一作谚、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧庵芭,春花似錦妹懒、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,750評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至伊诵,卻和暖如春单绑,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背曹宴。 一陣腳步聲響...
    開封第一講書人閱讀 33,867評論 1 274
  • 我被黑心中介騙來泰國打工搂橙, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人。 一個月前我還...
    沈念sama閱讀 49,368評論 3 379
  • 正文 我出身青樓区转,卻偏偏與公主長得像苔巨,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子废离,可洞房花燭夜當晚...
    茶點故事閱讀 45,930評論 2 361

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

  • 作為一枚Java后端開發(fā)者侄泽,數(shù)據(jù)庫知識必不可少,對數(shù)據(jù)庫的掌握熟悉度的考察也是對這個人是否有扎實基本功的考察蜻韭。特別...
    丶Orz丶閱讀 406評論 0 0
  • 上節(jié)重點難點回顧: 1. 數(shù)據(jù)類型 2. 列屬性 primary key unique not null defa...
    張鑫澤_2109閱讀 496評論 0 0
  • 1).創(chuàng)建數(shù)據(jù)庫 create database學生選課數(shù)據(jù)庫 2).創(chuàng)建四張表 Create table Stu...
    blvftigd閱讀 1,595評論 0 0
  • 最近打算采用關(guān)系型數(shù)據(jù)庫來理一下公司的運營數(shù)據(jù)悼尾,先拿點東西練手找感覺。下面是幾個關(guān)于學生課業(yè)的表肖方,需要建立一個數(shù)據(jù)...
    九天朱雀閱讀 985評論 0 3
  • 軟件測試筆試——數(shù)據(jù)庫題型 1.一個簡單的學生成績表闺魏,表名Student,有字符類型的Name項和整型的score...
    天天向上的小M閱讀 4,716評論 0 11