上節(jié)重點(diǎn)難點(diǎn)回顧：
1. 數(shù)據(jù)類型
2. 列屬性
primary? key
unique
not null
default
auto_increment
unsigned
comment
3. 各種SQL語句使用場景
DDL: create database? create table drop database drop table alter database alter table truncate
DML : insert? update? delete
insert into? oldguo(name,age,gender)
values
('zhangsan',18,'m'),
('zhs',18,'m');
update? where
delete? where
4. 命令記不住
命令太長
collation 校對(duì)規(guī)則
大小寫是否敏感
=============================我是分割線===============================
1. select
1.1 作用
獲取MySQL中的數(shù)據(jù)行
1.2 單獨(dú)使用select
1.2.1 select @@xxxx;獲取參數(shù)信息鲫惶。
mysql> select @@port;
mysql> show variables like '%innodb%';
1.2.2 select 函數(shù)();
mysql> select database();
mysql> select now();
mysql> select version();
1.3 SQL92標(biāo)準(zhǔn)的使用語法
1.3.1 select語法執(zhí)行順序（單表）
select開始 ---->
from子句 --->
where子句--->
group by子句--->
select后執(zhí)行條件--->
having子句 ---->
order by ---->
limit
--- 1.3.2 FROM
--- 例子：查詢city表中的所有數(shù)據(jù)
USE world;
SELECT * FROM city;? --->適合表數(shù)據(jù)行較少暑椰，生產(chǎn)中使用較少。
SELECT * FROM world.city;
--- 例子： 查詢name和population的所有值
SELECT NAME , population FROM city;
SELECT NAME , population FROM world.city;
單表查詢練習(xí)環(huán)境：world數(shù)據(jù)庫下表介紹
SHOW TABLES FROM world;
city(城市):
DESC city;
id: 自增的無關(guān)列政溃，數(shù)據(jù)行的需要
NAME: 城市名字
countrycode：城市所在的國家代號(hào)杈湾，CHN,USA,JPN。。析恢。。
district : 城市的所在的區(qū)域秧饮，中國是省的意思映挂，美國是洲的意思
population: 城市的人口數(shù)量
說明: 此表是歷史數(shù)據(jù)，僅供學(xué)習(xí)交流使用盗尸。
熟悉業(yè)務(wù)：
剛?cè)肼殨r(shí)柑船，DBA的任務(wù)
1. 搞清楚架構(gòu)
通過公司架構(gòu)圖，搞清楚數(shù)據(jù)庫的物理架構(gòu)
1-2天
邏輯結(jié)構(gòu)：
（1）生產(chǎn)庫的信息（容易達(dá)到）
（2）庫下表的信息（非常復(fù)雜）
? ? 1. 開發(fā)和業(yè)務(wù)人員泼各，搞好關(guān)系
? ? 2. 搞到ER圖（PD）
? ? 3. 啥都沒有怎么怎么辦鞍时？
? ? ? （1） 找到建表語句，如果有注釋，讀懂注釋逆巍。如果沒有注釋及塘，只能根據(jù)列名翻譯
? ? ? （2） 找到表中部分?jǐn)?shù)據(jù) ，分析數(shù)據(jù)特點(diǎn)锐极，達(dá)到了解列功能的目錄
1.3.3 where
--- 1.3.3 WHERE
--- 例子：
--- WHERE 配合 等值查詢(=)
--- 查詢中國的城市信息
SELECT *?
FROM? world.city?
WHERE? countrycode='CHN';
--- 查詢美國的城市信息
SELECT *?
FROM? world.city?
WHERE? countrycode='USA';
--- WHERE 配合 不等值(> < >= <=? <>)
--- 查詢一下世界上人口小于100人的城市
SELECT *?
FROM? world.city?
WHERE population<100;
--- 查詢世界上人口大于10000000的城市
略笙僚。
--- WHERE 配合 模糊(LIKE)
--- 查詢國家代號(hào)是C開頭的城市
SELECT *?
FROM? world.city?
WHERE countrycode
LIKE 'C%';
--- 注意：like 語句在MySQL中，不要出現(xiàn)%在前面的情況灵再。因?yàn)樾屎艿臀犊龋蛔咚饕?/p>
--- 錯(cuò)誤的里
SELECT *?
FROM? world.city?
WHERE countrycode
LIKE '%C%';
--- WHERE 配合 邏輯連接符(AND OR)
--- 查詢城市人口在1w到2w之間的城市
SELECT *
FROM city
WHERE population >= 10000
AND Population <= 20000;
SELECT *
FROM city
WHERE population
BETWEEN 10000 AND 20000;
--- 查詢一下中國或美國的城市信息
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';
--- 1.3.4 GROUP BY 配合聚合函數(shù)應(yīng)用
常用聚合函數(shù)：
AVG()
COUNT()
SUM()
MAX()
MIN()
GROUP_CONCAT()
--- 統(tǒng)計(jì)每個(gè)國家的總?cè)丝?/p>
SELECT? countrycode,SUM(population) FROM city GROUP BY countrycode ;
--- 統(tǒng)計(jì)每個(gè)國家的城市個(gè)數(shù)
1.拿什么站隊(duì)
GROUP BY? countrycode
2. 拿什么統(tǒng)計(jì)
城市id檬嘀，name
3. 統(tǒng)計(jì)的是什么槽驶？
COUNT(id)
SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;
--- 統(tǒng)計(jì)并顯示 每個(gè)國家的省名字列表
SELECT countrycode,GROUP_CONCAT(district)? FROM city GROUP BY countrycode;
--- 統(tǒng)計(jì)中國每個(gè)省的城市名列表
SELECT? District,GROUP_CONCAT(NAME)
FROM city
WHERE countrycode='CHN'
GROUP BY district;
--- 統(tǒng)計(jì)一下中國,每個(gè)省的總?cè)丝跀?shù)
SELECT? district ,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
--- 統(tǒng)計(jì)一下中國,每個(gè)省的平均人口
SELECT? district ,AVG(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
--- 1.3.5 HAVING
--- 統(tǒng)計(jì)中國，每個(gè)省的總?cè)丝诖笥?000w的省及人口數(shù)
SELECT? district ,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING? SUM(population)>10000000
說明: having后的條件是不走索引的鸳兽，可以進(jìn)行一些優(yōu)化手段處理掂铐。
--- 1.3.6 ORDER BY
SELECT? district ,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC? ;
--- 例子：查詢中國所有的城市，并以人口數(shù)降序輸出
SELECT * FROM city WHERE countrycode='CHN' ORDER BY? population DESC;
--- 1.3.7 LIMIT
SELECT *
FROM city
WHERE countrycode='CHN'
ORDER BY? population DESC
LIMIT 5;
SELECT *
FROM city
WHERE countrycode='CHN'
ORDER BY? population DESC
LIMIT 10;
SELECT *
FROM city
WHERE countrycode='CHN'
ORDER BY? population DESC
LIMIT 5,3;
SELECT *
FROM city
WHERE countrycode='CHN'
ORDER BY? population DESC
LIMIT 3 OFFSET 5;
LIMIT M,N? ? 跳過M行揍异，顯示N行
LIMIT X OFFSET Y 跳過Y行全陨，顯示X行
1.4 多表連接查詢
1.4.1 介紹4張測試表的關(guān)系
略。
1.4.2 什么時(shí)候用衷掷？
需要查詢的數(shù)據(jù)是來自于多張表時(shí)辱姨。
1.4.3 怎么去多表連接查詢
（1）傳統(tǒng)的連接：基于where條件
1. 找表之間的關(guān)系列
2. 排列查詢條件
select name,countrycode from city whrere population<100;
PCN
select name,surfacearea from country where code='PCN'
--- 人口數(shù)量小于100人的城市,所在國家的國土面積(城市名，國家名戚嗅，國土面積)
select city.name,country.name ,country.surfacearea
from city,country
where city.countrycode = country.code
and city.population<100
（2）內(nèi)連接 *****
A? B
A.x? B.y
1. 找表之間的關(guān)系列
2. 將兩表放在join左右
3. 將關(guān)聯(lián)條件了放在on后面
4. 將所有的查詢條件進(jìn)行羅列
select A.m,B.n
from?
A? join? B
on A.x=B.y
where
group by
order by
limit
--- 例子：
--- 1. 查詢?nèi)丝跀?shù)量小于100人的國家名雨涛，城市名，國土面積
SELECT country.name,city.name,country.surfacearea
FROM
city JOIN country
ON city.countrycode=country.code
WHERE city.population<100;
--- 2. 查詢oldguo老師和他教課程名稱
SELECT teacher.tname ,course.cname
FROM teacher
JOIN course
ON teacher.tno=course.tno
WHERE teacher.tname='oldguo';
SELECT teacher.`tname` ,course.`cname`
FROM teacher
JOIN course
ON teacher.`tno`=course.`tno`
WHERE teacher.`tname`='oldboy';
--- 3. 統(tǒng)計(jì)一下每門課程的總成績
SELECT course.cname,SUM(sc.score)
FROM course?
JOIN sc
ON course.cno = sc.cno
GROUP BY course.cname;
-- 5.7 版本會(huì)報(bào)錯(cuò)的情況,在sqlyog中以下操作沒問題
-- 但是在命令行上是會(huì)報(bào)錯(cuò)
SELECT course.cno,course.cname,SUM(sc.score)
FROM course?
JOIN sc
ON course.cno = sc.cno
GROUP BY course.cname;
mysql> SELECT course.cno,course.cname,SUM(sc.score)? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? -> FROM course?
? ? -> JOIN sc
? ? -> ON course.cno = sc.cno
? ? -> GROUP BY course.cname;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.course.cno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
1. 在select后面出現(xiàn)的列懦胞，不是分組條件替久，并且沒有在函數(shù)中出現(xiàn)。
2. 如果group by 后是主鍵列或者是唯一條件列躏尉，不會(huì)報(bào)出錯(cuò)誤蚯根。如下：
SELECT
course.cno,course.cname,SUM(sc.score)? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? FROM course?
JOIN sc
ON course.cno = sc.cno
GROUP BY course.cno;
（3）外鏈接 ****
自連接（自己了解）
--- 4. 查詢oldguo老師教的學(xué)生姓名列表
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE teacher.tname='oldguo'
GROUP BY teacher.tname;
--- 5. 查詢所有老師教的學(xué)生姓名列表
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
GROUP BY teacher.tno;
--- 6. 查詢oldboy老師教的不及格學(xué)生的姓名
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE teacher.tname='oldboy' AND sc.score<60
GROUP BY teacher.tno;
--- 7. 統(tǒng)計(jì)zhang3,學(xué)習(xí)了幾門課
SELECT student.`sname` ,COUNT(sc.`cno`)
FROM student
JOIN sc
ON student.`sno`=sc.`sno`
WHERE student.sname='zhang3';
--- 8. 查詢zhang3,學(xué)習(xí)的課程名稱有哪些?
SELECT student.sname,GROUP_CONCAT(course.`cname`)
FROM student
JOIN sc
ON student.`sno`=sc.`sno`
JOIN course
ON sc.`cno`=course.`cno`
WHERE student.`sname`='zhang3';
--- 9. 查詢oldguo老師教的學(xué)生名.
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE teacher.tname='oldguo'
GROUP BY teacher.tname;
--- 10.查詢oldguo所教課程的平均分?jǐn)?shù)
SELECT teacher.tname ,course.`cname`,AVG(sc.`score`)
FROM teacher
JOIN course
ON teacher.`tno`=course.`tno`
JOIN sc
ON course.`cno`=sc.`cno`
WHERE teacher.tname='oldguo';
--- 11.每位老師所教課程的平均分,并按平均分排序
SELECT teacher.tname ,course.`cname`,AVG(sc.`score`)
FROM teacher
JOIN course
ON teacher.`tno`=course.`tno`
JOIN sc
ON course.`cno`=sc.`cno`
ORDER BY AVG(sc.`score`);
--- 12.查詢oldguo所教的不及格的學(xué)生姓名
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE teacher.tname='oldguo' AND sc.score<60
GROUP BY teacher.tno;
--- 13.查詢所有老師所教學(xué)生不及格的信息
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE sc.score<60;
