一、知识点

汇总函数

分组

排序

分页

SQL运行顺序

二、练习

1、查询课程编号为“0002”的总成绩

分析思路
select 查询结果 [总成绩:汇总函数sum]
from 从哪张表中查找数据[成绩表score]
where 查询条件 [课程号是0002]

select sum(成绩)
from score
where 课程号 = '0002';

2、查询选了课程的学生人数

分析思路
select 查询结果 [不重复的学号:汇总函数count ]
from 从哪张表中查找数据[成绩表score];

select count(distinct 学号) as 学生人数 
from score;

3、查询各科成绩最高和最低的分

分析思路
select 查询结果 [课程号,最高分：max(成绩) ,最低分：min(成绩)]
from 从哪张表中查找数据 [成绩表score]
group by 分组 [各科成绩：按课程号分组];

select 课程号,max(成绩) as 最高分,min(成绩) as 最低分
from score
group by 课程号;

4、查询每门课程被选修的学生数

分析思路
select 查询结果 [课程号，选修该课程的学生数：汇总函数count]
from 从哪张表中查找数据 [成绩表score]
group by 分组 [每门课程：按课程号分组];

select 课程号, count(学号)
from score
group by 课程号;

5、查询男生、女生人数

分析思路
select 查询结果 [性别，对应性别的人数：汇总函数count]
from 从哪张表中查找数据 [学生表student]
group by 分组 [男生、女生人数：按性别分组];

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

6、查询平均成绩大于60分学生的学号和平均成绩

分析思路
select 查询结果 [学号，平均成绩：汇总函数avg(成绩)]
from 从哪张表中查找数据 [成绩表score]
group by 分组 [平均成绩：先按学号分组，再计算平均成绩]
having 对分组结果指定条件 [平均成绩大于60分]

select 学号, avg(成绩)
from score
group by 学号
having avg(成绩)>60;

7、查询至分析思路

select 查询结果 [学号,每个学生选修课程数目：汇总函数count]
from 从哪张表中查找数据 [课程的学生学号：课程表score]
group by 分组 [每个学生选修课程数目：按课程号分组，然后用汇总函数count计算出选修了多少门课]
having 对分组结果指定条件 [至少选修两门课程：每个学生选修课程数目>=2]少选修两门课程的学生学号

select 学号, count(课程号) as 选修课程数目
from score
group by 学号
having count(课程号)>=2;

8、查询同名同姓学生名单并统计同名人数

分析思路
select 查询结果 [姓名,人数：汇总函数count()]
from 从哪张表中查找数据 [学生表student]
group by 分组 [姓名相同：按姓名分组]
having 对分组结果指定条件 [姓名相同：count()>=2];

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

9、查询不及格的课程并按课程号从大到小排列

分析思路
select 查询结果 [课程号]
from 从哪张表中查找数据 [成绩表score]
where 查询条件 [不及格：成绩 <60]
order by 对查询结果排序[课程号从大到小排列：降序desc];

select 课程号
from score 
where 成绩<60
order by 课程号 desc;

10、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

分析思路
select 查询结果 [课程号,平均成绩：汇总函数avg(成绩)]
from 从哪张表中查找数据 [成绩表score]
group by 分组 [每门课程：按课程号分组]
order by 对查询结果排序[按平均成绩升序排序:asc，平均成绩相同时，按课程号降序排列:desc];

select 课程号, avg(成绩) as 平均成绩
from score
group by 课程号
order by 平均成绩 asc,课程号 desc;

sqlzoo练习

练习地址

1.更改查询语句，显示1950年的诺贝尔奖

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

2.谁赢得了1962年的文学奖（Literature）

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

3.“爱因斯坦”(Albert Einstein)获奖年份和奖项

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

4.2000年及以后的和平奖(‘Peace’)得奖者

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

5.1980年至1989年的文学奖获奖者的所有细节（年份，奖项，获奖者）

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

6.查找下面获奖者的所有详细信息：

西奥多·罗斯福：Theodore Roosevelt
伍德罗·威尔逊：'Woodrow Wilson
吉米卡特：Jimmy Carter

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

7.获奖姓名以John开头的获奖者

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

8.查找1980年物理学奖（Physics）获得者，以及1984年的化学奖（Chemistry）获得者

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

9.查找1980年获奖者的年份，奖项和名称，不包括化学奖和医学奖('Chemistry', 'Medicine')

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

10.在早年（1910年之前，不包括1910年）获得“医学”奖的人的年份，主题和名称，以及晚年（2004年之后，包括2004年）的“文学”奖的获奖者

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

11.查找获奖者（PETERGRÜNBERG）的获奖信息

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

12.查找获奖者（Eugene O'Neill）的获奖信息

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

如果字符串中包含单引号或双引号，该怎么处理？比如Eugene O'Neill？

使用sql的转义字符。在字符串中，有些符号有特殊含义，比如Eugene O'Neill中的单引号（'），使用两个单引号（''）表示字符串里面的单引号（'），也就是转义字符。

这个例子里，Eugene O'Neill，在sql里这么写'Eugene O''Neill'

13.列出获奖者，年份以及获奖者从Sir开始的主题。首先显示最新的，然后按名称顺序显示

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

14.显示1984年的诺贝尔获奖者及其奖项。按奖项和得奖者名字排序， 但化学奖和物理奖在最后列出。

/*如果写成 subject in ('Physics', 'Chemistry') DESC 则化学奖和物理奖在最前面列出*/
  SELECT winner,
         subject
    FROM nobel
   WHERE yr = 1984
ORDER BY subject IN ('Physics', 'Chemistry'),
-- 其他按“subject”和“winner”升序列出
         subject,
         winner;

sql解释：

subject in ('Physics','Chemistry')返回值（0或者1），
会对每一个subject做一个if的判断，有的是1，没有的是0
再用order by把这些值排序在下面

不是这两个科目('Physics','Chemistry')的就是0排在前边，
是这两个科目的返回1就排在后边了。
因为化学和物理科目题目要求在后面，所以引入此函数出现0、1，达成题目的要求

练习地址

1. 显示世界总人口

select sum(population) 
from world;

2. 列出所有洲名称，不能有重复值

select distinct continent 
from world;

3. 非洲(Africa)的GDP总和

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

4.有多少个国家具有至少百万(1000000)的面积。

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

5.('France','Germany','Spain')（“法国”，“德国”，“西班牙”）的总人口是多少？

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

6.显示每个大洲以及大洲的国家数量

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

7.显示每个洲的国家数量，并且这些国家里人口数量至少为1000万

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

8.列出每个洲名称，并且每个洲的总人口数要大于等于一亿

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