一秩彤、知識(shí)點(diǎn)

匯總函數(shù)

分組

排序

分頁(yè)

SQL運(yùn)行順序

二锅减、練習(xí)

1伏钠、查詢(xún)課程編號(hào)為“0002”的總成績(jī)

分析思路
select 查詢(xún)結(jié)果 [總成績(jī):匯總函數(shù)sum]
from 從哪張表中查找數(shù)據(jù)[成績(jī)表score]
where 查詢(xún)條件 [課程號(hào)是0002]

select sum(成績(jī))
from score
where 課程號(hào) = '0002';

2音比、查詢(xún)選了課程的學(xué)生人數(shù)

分析思路
select 查詢(xún)結(jié)果 [不重復(fù)的學(xué)號(hào):匯總函數(shù)count ]
from 從哪張表中查找數(shù)據(jù)[成績(jī)表score];

select count(distinct 學(xué)號(hào)) as 學(xué)生人數(shù) 
from score;

3穴吹、查詢(xún)各科成績(jī)最高和最低的分

分析思路
select 查詢(xún)結(jié)果 [課程號(hào),最高分：max(成績(jī)) ,最低分：min(成績(jī))]
from 從哪張表中查找數(shù)據(jù) [成績(jī)表score]
group by 分組 [各科成績(jī)：按課程號(hào)分組];

select 課程號(hào),max(成績(jī)) as 最高分,min(成績(jī)) as 最低分
from score
group by 課程號(hào);

4、查詢(xún)每門(mén)課程被選修的學(xué)生數(shù)

分析思路
select 查詢(xún)結(jié)果 [課程號(hào)赘方，選修該課程的學(xué)生數(shù)：匯總函數(shù)count]
from 從哪張表中查找數(shù)據(jù) [成績(jī)表score]
group by 分組 [每門(mén)課程：按課程號(hào)分組];

select 課程號(hào), count(學(xué)號(hào))
from score
group by 課程號(hào);

5烧颖、查詢(xún)男生、女生人數(shù)

分析思路
select 查詢(xún)結(jié)果 [性別窄陡，對(duì)應(yīng)性別的人數(shù)：匯總函數(shù)count]
from 從哪張表中查找數(shù)據(jù) [學(xué)生表student]
group by 分組 [男生炕淮、女生人數(shù)：按性別分組];

select 性別,count(*)
from student
group by 性別;

6、查詢(xún)平均成績(jī)大于60分學(xué)生的學(xué)號(hào)和平均成績(jī)

分析思路
select 查詢(xún)結(jié)果 [學(xué)號(hào)跳夭，平均成績(jī)：匯總函數(shù)avg(成績(jī))]
from 從哪張表中查找數(shù)據(jù) [成績(jī)表score]
group by 分組 [平均成績(jī)：先按學(xué)號(hào)分組涂圆，再計(jì)算平均成績(jī)]
having 對(duì)分組結(jié)果指定條件 [平均成績(jī)大于60分]

select 學(xué)號(hào), avg(成績(jī))
from score
group by 學(xué)號(hào)
having avg(成績(jī))>60;

7、查詢(xún)至分析思路

select 查詢(xún)結(jié)果 [學(xué)號(hào),每個(gè)學(xué)生選修課程數(shù)目：匯總函數(shù)count]
from 從哪張表中查找數(shù)據(jù) [課程的學(xué)生學(xué)號(hào)：課程表score]
group by 分組 [每個(gè)學(xué)生選修課程數(shù)目：按課程號(hào)分組币叹，然后用匯總函數(shù)count計(jì)算出選修了多少門(mén)課]
having 對(duì)分組結(jié)果指定條件 [至少選修兩門(mén)課程：每個(gè)學(xué)生選修課程數(shù)目>=2]少選修兩門(mén)課程的學(xué)生學(xué)號(hào)

select 學(xué)號(hào), count(課程號(hào)) as 選修課程數(shù)目
from score
group by 學(xué)號(hào)
having count(課程號(hào))>=2;

8润歉、查詢(xún)同名同姓學(xué)生名單并統(tǒng)計(jì)同名人數(shù)

分析思路
select 查詢(xún)結(jié)果 [姓名,人數(shù)：匯總函數(shù)count()]
from 從哪張表中查找數(shù)據(jù) [學(xué)生表student]
group by 分組 [姓名相同：按姓名分組]
having 對(duì)分組結(jié)果指定條件 [姓名相同：count()>=2];

select 姓名,count(*) as 人數(shù)
from student
group by 姓名
having count(*)>=2;

9、查詢(xún)不及格的課程并按課程號(hào)從大到小排列

分析思路
select 查詢(xún)結(jié)果 [課程號(hào)]
from 從哪張表中查找數(shù)據(jù) [成績(jī)表score]
where 查詢(xún)條件 [不及格：成績(jī) <60]
order by 對(duì)查詢(xún)結(jié)果排序[課程號(hào)從大到小排列：降序desc];

select 課程號(hào)
from score 
where 成績(jī)<60
order by 課程號(hào) desc;

10颈抚、查詢(xún)每門(mén)課程的平均成績(jī)踩衩，結(jié)果按平均成績(jī)升序排序，平均成績(jī)相同時(shí)，按課程號(hào)降序排列

分析思路
select 查詢(xún)結(jié)果 [課程號(hào),平均成績(jī)：匯總函數(shù)avg(成績(jī))]
from 從哪張表中查找數(shù)據(jù) [成績(jī)表score]
group by 分組 [每門(mén)課程：按課程號(hào)分組]
order by 對(duì)查詢(xún)結(jié)果排序[按平均成績(jī)升序排序:asc驱富，平均成績(jī)相同時(shí)锚赤，按課程號(hào)降序排列:desc];

select 課程號(hào), avg(成績(jī)) as 平均成績(jī)
from score
group by 課程號(hào)
order by 平均成績(jī) asc,課程號(hào) desc;

sqlzoo練習(xí)

練習(xí)地址

1.更改查詢(xún)語(yǔ)句，顯示1950年的諾貝爾獎(jiǎng)

select yr, subject, winner
from nobel
where yr = 1950;

2.誰(shuí)贏得了1962年的文學(xué)獎(jiǎng)（Literature）

select winner
from nobel
where yr = 1962 and subject = 'Literature';

3.“愛(ài)因斯坦”(Albert Einstein)獲獎(jiǎng)年份和獎(jiǎng)項(xiàng)

select yr, subject
from nobel
where winner = 'Albert Einstein';

4.2000年及以后的和平獎(jiǎng)(‘Peace’)得獎(jiǎng)?wù)?/h4>

select winner
from nobel
where subject = 'Peace' and yr >= 2000;

5.1980年至1989年的文學(xué)獎(jiǎng)獲獎(jiǎng)?wù)叩乃屑?xì)節(jié)（年份褐鸥，獎(jiǎng)項(xiàng)线脚，獲獎(jiǎng)?wù)撸?/h4>

select yr, subject, winner
from nobel
where yr between 1980 and 1989 and subject = 'Literature';

6.查找下面獲獎(jiǎng)?wù)叩乃性敿?xì)信息：

西奧多·羅斯福：Theodore Roosevelt
伍德羅·威爾遜：'Woodrow Wilson
吉米卡特：Jimmy Carter

select *
from nobel
where winner in ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter');

7.獲獎(jiǎng)姓名以John開(kāi)頭的獲獎(jiǎng)?wù)?/h4>

select winner
from nobel
where winner LIKE 'john%';

8.查找1980年物理學(xué)獎(jiǎng)（Physics）獲得者，以及1984年的化學(xué)獎(jiǎng)（Chemistry）獲得者

select *
from nobel
where (subject = 'physics' and yr = '1980') OR (subject = 'Chemistry' and yr = 1984);

9.查找1980年獲獎(jiǎng)?wù)叩哪攴萁虚牛?jiǎng)項(xiàng)和名稱(chēng)浑侥，不包括化學(xué)獎(jiǎng)和醫(yī)學(xué)獎(jiǎng)('Chemistry', 'Medicine')

select *
from nobel
where yr = 1980 and subject NOT in ('Chemistry', 'Medicine');

10.在早年（1910年之前，不包括1910年）獲得“醫(yī)學(xué)”獎(jiǎng)的人的年份晰绎，主題和名稱(chēng)寓落，以及晚年（2004年之后，包括2004年）的“文學(xué)”獎(jiǎng)的獲獎(jiǎng)?wù)?/h4>

select *
from nobel
where (subject  = 'Medicine' and yr < 1910) or (subject = 'Literature' and yr >= 2004);

11.查找獲獎(jiǎng)?wù)撸≒ETERGRüNBERG）的獲獎(jiǎng)信息

select *
from nobel
where winner='PETER GRüNBERG';

12.查找獲獎(jiǎng)?wù)撸‥ugene O'Neill）的獲獎(jiǎng)信息

select *
from nobel
where winner = 'Eugene O''Neill';

如果字符串中包含單引號(hào)或雙引號(hào)寒匙，該怎么處理零如？比如Eugene O'Neill？

使用sql的轉(zhuǎn)義字符锄弱。在字符串中考蕾，有些符號(hào)有特殊含義，比如Eugene O'Neill中的單引號(hào)（'）会宪，使用兩個(gè)單引號(hào)（''）表示字符串里面的單引號(hào)（'）肖卧，也就是轉(zhuǎn)義字符。

這個(gè)例子里掸鹅，Eugene O'Neill塞帐，在sql里這么寫(xiě)'Eugene O''Neill'

13.列出獲獎(jiǎng)?wù)撸攴菀约矮@獎(jiǎng)?wù)邚腟ir開(kāi)始的主題巍沙。首先顯示最新的葵姥，然后按名稱(chēng)順序顯示

select winner, yr, subject
from nobel
where winner LIKE 'sir%'
order by yr DESC, winner;

14.顯示1984年的諾貝爾獲獎(jiǎng)?wù)呒捌洫?jiǎng)項(xiàng)。按獎(jiǎng)項(xiàng)和得獎(jiǎng)?wù)呙峙判颍?但化學(xué)獎(jiǎng)和物理獎(jiǎng)在最后列出句携。

/*如果寫(xiě)成 subject in ('Physics', 'Chemistry') DESC 則化學(xué)獎(jiǎng)和物理獎(jiǎng)在最前面列出*/
  SELECT winner,
         subject
    FROM nobel
   WHERE yr = 1984
ORDER BY subject IN ('Physics', 'Chemistry'),
-- 其他按“subject”和“winner”升序列出
         subject,
         winner;

sql解釋?zhuān)?/h5>

subject in ('Physics','Chemistry')返回值（0或者1）榔幸，
會(huì)對(duì)每一個(gè)subject做一個(gè)if的判斷，有的是1矮嫉，沒(méi)有的是0
再用order by把這些值排序在下面

不是這兩個(gè)科目('Physics','Chemistry')的就是0排在前邊削咆，
是這兩個(gè)科目的返回1就排在后邊了。
因?yàn)榛瘜W(xué)和物理科目題目要求在后面蠢笋，所以引入此函數(shù)出現(xiàn)0拨齐、1，達(dá)成題目的要求

練習(xí)地址

1. 顯示世界總?cè)丝?/h4>

select sum(population) 
from world;

2. 列出所有洲名稱(chēng)昨寞，不能有重復(fù)值

select distinct continent 
from world;

3. 非洲(Africa)的GDP總和

select sum(gdp) 
from world
where continent='Africa';

4.有多少個(gè)國(guó)家具有至少百萬(wàn)(1000000)的面積瞻惋。

select count(name) 
from world 
where area>=1000000;

5.('France','Germany','Spain')（“法國(guó)”厦滤，“德國(guó)”，“西班牙”）的總?cè)丝谑嵌嗌伲?/h4>

select sum(population) 
from world
where name in('France','Germany','Spain');

6.顯示每個(gè)大洲以及大洲的國(guó)家數(shù)量

select continent,count(name) 
from world
group by continent;

7.顯示每個(gè)洲的國(guó)家數(shù)量熟史，并且這些國(guó)家里人口數(shù)量至少為1000萬(wàn)

select continent,count(name) 
from world
where population>=10000000
group by continent;

8.列出每個(gè)洲名稱(chēng)馁害，并且每個(gè)洲的總?cè)丝跀?shù)要大于等于一億

select continent 
from world
group by continent
having sum(population)>=100000000;