6-18 SQL基礎(chǔ)應(yīng)用

上節(jié)重點難點回顧：

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 校對規(guī)則

大小寫是否敏感

=============================我是分割線===============================

1. select

1.1 作用

獲取MySQL中的數(shù)據(jù)行

1.2 單獨使用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：城市所在的國家代號，CHN,USA,JPN。。。。

district : 城市的所在的區(qū)域，中國是省的意思，美國是洲的意思

population: 城市的人口數(shù)量

說明: 此表是歷史數(shù)據(jù)，僅供學(xué)習(xí)交流使用。

熟悉業(yè)務(wù)：

剛?cè)肼殨r，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ù)特點，達(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)

--- 查詢國家代號是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 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)計每個國家的總?cè)丝?/p>

SELECT? countrycode,SUM(population) FROM city GROUP BY countrycode ;

--- 統(tǒng)計每個國家的城市個數(shù)

1.拿什么站隊

GROUP BY? countrycode

2. 拿什么統(tǒng)計

城市id，name

3. 統(tǒng)計的是什么？

COUNT(id)

SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;

--- 統(tǒng)計并顯示 每個國家的省名字列表

SELECT countrycode,GROUP_CONCAT(district)? FROM city GROUP BY countrycode;

--- 統(tǒng)計中國每個省的城市名列表

SELECT? District,GROUP_CONCAT(NAME)

FROM city

WHERE countrycode='CHN'

GROUP BY district;

--- 統(tǒng)計一下中國,每個省的總?cè)丝跀?shù)

SELECT? district ,SUM(population) FROM city

WHERE countrycode='CHN'

GROUP BY district

--- 統(tǒng)計一下中國,每個省的平均人口

SELECT? district ,AVG(population) FROM city

WHERE countrycode='CHN'

GROUP BY district

--- 1.3.5 HAVING

--- 統(tǒng)計中國，每個省的總?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ù)據(jù)是來自于多張表時。

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)計一下每門課程的總成績

SELECT course.cname,SUM(sc.score)

FROM course?

JOIN sc

ON course.cno = sc.cno

GROUP BY course.cname;

-- 5.7 版本會報錯的情況,在sqlyog中以下操作沒問題

-- 但是在命令行上是會報錯

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 后是主鍵列或者是唯一條件列，不會報出錯誤。如下：

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)計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;

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 50個常用的sql語句Student(S#,Sname,Sage,Ssex) 學(xué)生表Course(C#,Cname...
    哈哈海閱讀 1,334評論 0贊 7
  • 1）.創(chuàng)建數(shù)據(jù)庫 create database學(xué)生選課數(shù)據(jù)庫 2）.創(chuàng)建四張表 Create table Stu...
    blvftigd閱讀 1,684評論 0贊 0
  • 最近打算采用關(guān)系型數(shù)據(jù)庫來理一下公司的運營數(shù)據(jù)，先拿點東西練手找感覺。下面是幾個關(guān)于學(xué)生課業(yè)的表，需要建立一個數(shù)據(jù)...
    九天朱雀閱讀 1,045評論 0贊 3
  • 說明：以下五十個語句都按照測試數(shù)據(jù)進(jìn)行過測試，最好每次只單獨運行一個語句。 問題及描述： --1.學(xué)生表 Stud...
    lijun_m閱讀 1,381評論 0贊 1
  • 海洋臨河里門市單獨成團 （19+1 精品VIP小團 ） 3.30日出發(fā)-4.4日回程 老客戶尊享價格3180元/人...
    2173a787ca3b閱讀 737評論 0贊 0

友情鏈接更多精彩內(nèi)容