上節(jié)重點(diǎn)難點(diǎn)回顧：
1. 數(shù)據(jù)類型
2. 列屬性
primary key
unique
not null
default
auto_increment
unsigned
comment
3. 各種SQL語(yǔ)句使用場(chǎng)景
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. 命令記不住
命令太長(zhǎng)
collation 校對(duì)規(guī)則
大小寫是否敏感
- 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)的使用語(yǔ)法
1.3.1 select語(yǔ)法執(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ù)庫(kù)下表介紹
SHOW TABLES FROM world;
city(城市):
DESC city;
id: 自增的無關(guān)列君编，數(shù)據(jù)行的需要
NAME: 城市名字
countrycode：城市所在的國(guó)家代號(hào)拆火，CHN,USA,JPN歧譬。。陷猫。秫舌。
district : 城市的所在的區(qū)域，中國(guó)是省的意思烙丛，美國(guó)是洲的意思
population: 城市的人口數(shù)量
說明: 此表是歷史數(shù)據(jù)锰什，僅供學(xué)習(xí)交流使用紊搪。
熟悉業(yè)務(wù)：
剛?cè)肼殨r(shí)霹疫，DBA的任務(wù)
1. 搞清楚架構(gòu)
通過公司架構(gòu)圖舌界，搞清楚數(shù)據(jù)庫(kù)的物理架構(gòu)
1-2天
邏輯結(jié)構(gòu)：
（1）生產(chǎn)庫(kù)的信息（容易達(dá)到）
（2）庫(kù)下表的信息（非常復(fù)雜）
1. 開發(fā)和業(yè)務(wù)人員，搞好關(guān)系
2. 搞到ER圖（PD）
3. 啥都沒有怎么怎么辦忘蟹？
（1） 找到建表語(yǔ)句飒房，如果有注釋，讀懂注釋媚值。如果沒有注釋狠毯，只能根據(jù)列名翻譯
（2） 找到表中部分?jǐn)?shù)據(jù) ，分析數(shù)據(jù)特點(diǎn)褥芒，達(dá)到了解列功能的目錄
1.3.3 where
--- 1.3.3 WHERE
--- 例子：
--- WHERE 配合 等值查詢(=)
--- 查詢中國(guó)的城市信息
SELECT *
FROM world.city
WHERE countrycode='CHN';
--- 查詢美國(guó)的城市信息
SELECT *
FROM world.city
WHERE countrycode='USA';
--- WHERE 配合 不等值(> < >= <= <>)
--- 查詢一下世界上人口小于100人的城市
SELECT *
FROM world.city
WHERE population<100;
--- 查詢世界上人口大于10000000的城市
略嚼松。
--- WHERE 配合 模糊(LIKE)
--- 查詢國(guó)家代號(hào)是C開頭的城市
SELECT *
FROM world.city
WHERE countrycode
LIKE 'C%';
--- 注意：like 語(yǔ)句在MySQL中嫡良，不要出現(xiàn)%在前面的情況。因?yàn)樾屎艿拖仔铮蛔咚饕?--- 錯(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;
--- 查詢一下中國(guó)或美國(guó)的城市信息
SELECT *
FROM city
WHERE countrycode='CHN' OR countrycode='USA';
SELECT *
FROM city
WHERE countrycode IN ('CHN','USA');
建議改寫為寝受，以下語(yǔ)句：
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è)國(guó)家的總?cè)丝?
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode ;
--- 統(tǒng)計(jì)每個(gè)國(guó)家的城市個(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è)國(guó)家的省名字列表
SELECT countrycode,GROUP_CONCAT(district) FROM city GROUP BY countrycode;
--- 統(tǒng)計(jì)中國(guó)每個(gè)省的城市名列表
SELECT District,GROUP_CONCAT(NAME)
FROM city
WHERE countrycode='CHN'
GROUP BY district;
--- 統(tǒng)計(jì)一下中國(guó),每個(gè)省的總?cè)丝跀?shù)
SELECT district ,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
--- 統(tǒng)計(jì)一下中國(guó),每個(gè)省的平均人口
SELECT district ,AVG(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
--- 1.3.5 HAVING
--- 統(tǒng)計(jì)中國(guó)很澄，每個(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 ;
--- 例子：查詢中國(guó)所有的城市甩苛，并以人口數(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張測(cè)試表的關(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人的城市,所在國(guó)家的國(guó)土面積(城市名爱葵，國(guó)家名，國(guó)土面積)
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人的國(guó)家名反浓，城市名，國(guó)土面積
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ì)一下每門課程的總成績(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;
2. show
3. Information_schema
