MySQL--DQL語句使用--Day4

上節(jié)重點回顧：

1监署、數(shù)據(jù)類型
2晓避、列屬性
3、各種SQL語句使用場景
4惕艳、命令筆記記不住

一、DQL語句使用

1辉巡、select語句

1.1、作用
獲取MySQL中的數(shù)據(jù)行

1.2、 單獨使用select
1.2.1 select @@xxxx；
mysql > select @@port;                                      #查mysql數(shù)據(jù)的端口
mysql > show  variables like '%innodb%';           
1.2.2 select 函數(shù)()褒翰；
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

二荷愕、select語句應(yīng)用

from字句

1.3.2璧亚、from
-- 例子：查詢city表中的所有數(shù)據(jù)
use school;
select  * from score; --->適合表數(shù)據(jù)較少浴井，生產(chǎn)中使用較小锦溪。
select  * from school.score;  --->使用絕對路徑

-- 例子：查詢sno和score的所有值
select  sno , score from score;
select  sno , score from school.score; --->使用絕對路徑

--- 單表查詢練習(xí)環(huán)境：world數(shù)據(jù)庫下表介紹
show databases ;
show tables  from  world;
use world;
select * from world.city;

city(城市)
desc city;
id:自增的無關(guān)列，數(shù)據(jù)行的需要
NAME：城市名字
countrycode：城市所在的國家代號，CHN，USA岔乔，JPN酥筝；
district：中國省的意思，美國是洲
population：城市的人口數(shù)量

country（國家）
countrylanguage（國家語言）

入職DBA技巧

熟悉業(yè)務(wù)：
剛?cè)肼殨r雏门，DBA的任務(wù)
1嘿歌、通過公司架構(gòu)圖，搞清楚數(shù)據(jù)庫的物理結(jié)構(gòu)
1-2天
邏輯結(jié)構(gòu)：
（1）生產(chǎn)庫的信息（容易達到）
（2）庫下表的信息（非常復(fù)雜）
1茁影、開發(fā)和業(yè)務(wù)人員宙帝，搞好關(guān)系
2、搞到ER圖（PD）
3募闲、啥都沒有怎么辦步脓？
（1）找到建表語句，如果有注釋浩螺。如果沒有注釋靴患，只能根據(jù)列名翻譯
（2）找到表中部分?jǐn)?shù)據(jù),分析數(shù)據(jù)特點，達到了解列功能的目錄

where字句

1.3.3要出、where字句
-- 例子
-- where 配合 等值查詢
-- 查詢 city 表中鸳君，中國的城市信息
select * from  world.city where CountryCode='chn';
-- 查詢 city 表中，查詢美國的城市信息
select * from  world.city where CountryCode='usa';

-- where 配合 不等于
-- 查詢一下世界人口小于100人的城市
select * from  world.city where population<100;
-- 查詢世界人口大于10000000的城市
select * from  world.city where population>10000000;

-- where 配合 模糊（like）
-- 查詢國家代號是c開頭的
select  * from world.city where CountryCode like 'c%';

-- 注意：like 語句在MySQL中患蹂，不要出現(xiàn)%在前面的情況或颊，因為效率低，不走索引传于。
-- 錯誤的例子
select  * from world.city where CountryCode like '%c%';

-- where 配合 邏輯連接符（AND OR）
-- 查詢城市人口1w到2w之間的城市
select  * from world.city where  Population > 10000 AND  Population < 20000;
select  * from world.city where  Population between 10000 and 20000;

-- 查詢中國或美國的城市信息
select * from  world.city where  CountryCode='chn' or CountryCode='usa';
select * from  world.city where  CountryCode in ('cha','usa');
建議改寫成饭宾，一下語句
select *
from  world.city
where CountryCode='chn';
UNION ALL
select *
from  world.city
where CountryCode='usa';

group by字句

1.3.4 group by 配合聚合函數(shù)引應(yīng)用
常用聚合函數(shù)：
AVG()  平均
COUNT() 計數(shù)
SUM()   總數(shù)
MAX()   最大
MIN()   最小
GROUP_CONCAT() 列轉(zhuǎn)行

-- 統(tǒng)計每個國家的總?cè)丝?select  countrycode,sum(Population) from  world.city group by CountryCode;

-- 統(tǒng)計每個國家的城市個數(shù)
1、拿什么站隊
group by CountryCode
2格了、拿什么統(tǒng)計
城市id看铆，name
3、統(tǒng)計的是什么盛末？
count(id)
select  CountryCode,count(Name)from  world.city group by CountryCode;

-- 統(tǒng)計并顯示弹惦，每個國家的省的省名字列表
select  CountryCode,group_concat(District)from  world.city group by CountryCode;

-- 統(tǒng)計中國省的城市列表
select  District,group_concat(name)from world.city where CountryCode='CHN'group by District;

-- 統(tǒng)計一下中國每個省的總?cè)丝跀?shù)
select  District,group_concat(population)
from world.city
where CountryCode='CHN'
group by District;

-- 統(tǒng)計一下中國，每個省的國家的平均數(shù)
select  District,avg(population)
from world.city
where CountryCode='CHN'
group by District;

HAVING字句

1.3.5 HAVING

-- 統(tǒng)計中國悄但，每個省的總?cè)丝诖笥?000w的省及人口數(shù)
select  District,group_concat(population)
from world.city
where CountryCode='CHN'
group by District
having sum(Population)>10000000;
說明: having后的條件是不走索引的棠隐，可以進行一些優(yōu)化手段處理。

ORDER BY字句

1.3.6 ORDER BY
select  District,sum(population)
from world.city
where CountryCode='CHN'
group by District
ORDER BY sum(Population) desc ;

說明：desc 從大到小

--- 例子：查詢中國所有的城市檐嚣，并以人口數(shù)降序輸出
select *
from world.city
where countryCode='chn'
order by  Population desc;

LIMIT字句

1.3.7 LIMIT

--- 例子：查詢中國所有的城市助泽，并以人口數(shù)降序輸出啰扛，并顯示前10行
select *
from world.city
where countryCode='chn'
order by  Population desc
limit 10;

select *
from world.city
where countryCode='chn'
order by  Population desc
limit 5,5;

select *
from world.city
where countryCode='chn'
order by  Population desc
limit 5 offset 5;

1.4 多表連接查詢

語法

image

1.4.1 介紹4張測試表的關(guān)系

image

1.4.2 什么時候用？
需要查詢的數(shù)據(jù)來自于多張表時嗡贺。

1.4.3 怎么多表連接查詢

傳統(tǒng)的連接：基于where條件
1隐解、找表之間的關(guān)系列
2、排列查詢條件
selcet name,countrycode from city whree population<100;
pcn
selcet name,surfacearea from country whree code<'pcn';

-- 人口數(shù)量小于100人的城市诫睬，所在國家的國土面積(城市名煞茫，國家名，國土面積)

**(2)摄凡、內(nèi)連接*******

A   B

A.x   B.y 
1\. 找表之間的關(guān)系列 
2\. 將兩表放在join左右
3\. 將關(guān)聯(lián)條件了放在on后面
4\. 將所有的查詢條件進行羅列

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人國家名，城市名亲澡，國土面積
use world;
select country.name,city.name,country.surfacearea
from
city join country
on city.countrycode=country.code
where city.population<100;

-- 2钦扭、查詢oldguo老師和他教的課程名稱
use school;
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';

show  tables;
select * from course;  課程表
select * from score;   成績表
select * from student; 學(xué)生表
select * from teacher; 教師表

--- 3\. 統(tǒng)計一下每門課程的總成績
select course.cname,sum(score.score)
from course
join score
on course.cno = score.cno
group by course.cno,course.cname;

5.7、版本會報錯情況
mysql> select course.cno,course.cname,sum(score.score)
    -> from course
    -> join score
    -> on score.cno=course.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 后面主鍵或者是唯一條列裹匙，如下
select course.cno,course.cname,sum(score.score)
from course
join score
on score.cno=course.cno
group by course.cno;

select @@sql_mode;       #查看sql_mode的

--- 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 student
JOIN score
ON student.sno=score.sno
JOIN course
ON score.cno=course.cno
JOIN teacher ON
course.tno=teacher.tno
WHERE teacher.tname='oldboy' AND score.score<60
GROUP BY teacher.tno;

SELECT
    course.cname,
    teacher.tname,
    student.sname
FROM
    teacher
    JOIN course ON teacher.tno = course.tno
    JOIN score ON course.cno = score.cno
    JOIN student ON score.sno = student.sno
WHERE
    teacher.tname = 'oldboy'
AND
    score.score < 60;

--- 7\. 統(tǒng)計zhang3,學(xué)習(xí)了幾門課
SELECT student.sname,GROUP_CONCAT(course.cname)
FROM teacher
  JOIN course
    ON teacher.tno = course.tno
  JOIN score
    ON course.cno = score.cno
  JOIN student
    ON score.sno = student.sno
WHERE student.sname = 'zhang3'
GROUP BY student.sname;

--- 8\. 查詢zhang3,學(xué)習(xí)的課程名稱有哪些?
select student.sname,group_concat(course.cname)
from student
  join score
    on student.sno=score.sno
  join course
    on score.cno=course.cno
where student.sname='zhang3'
group by student.sno;

--- 9\. 查詢oldguo老師教的學(xué)生名.
select teacher.tname,group_concat(student.sname)
from teacher
  join course
    on teacher.tno=course.tno
  join score
    on course.cno=score.cno
    join student
      on score.sno=student.sno
where teacher.tname='oldguo'
group by teacher.tno;

--- 10.查詢oldguo所教課程的平均分?jǐn)?shù)
select teacher.tname,AVG(score.score)
from course
  join teacher
    on course.tno=teacher.tno
  join score
    on course.cno=score.cno
where teacher.tname='oldguo'
group by teacher.tno or  course.cno;

--- 11.每位老師所教課程的平均分,并按平均分排序
select teacher.tname,course.cname,AVG(score.score)
from course
  join teacher
    on course.tno=teacher.tno
  join score
    on course.cno=score.cno
group by course.cno
ORDER BY AVG(score.score);

--- 12.查詢oldguo所教的不及格的學(xué)生姓名
select teacher.tname,student.sname,course.cname,score.score
from course
  join teacher
    on course.tno=teacher.tno
  join score
    on course.cno=score.cno
    join student
      on score.sno=student.sno
where teacher.tname='oldguo' and score.score<60;
group by teacher.tno

--- 13.查詢所有老師所教學(xué)生不及格的信息
select teacher.tname,student.sname
from student
  join score
    on student.sno=score.sno
  join course
    on course.cno=score.cno
  JOIN teacher
    on teacher.tno=course.tno
WHERE score.score<60
group by teacher.tname,student.sname;