一、知识点
汇总函数
分组
排序
分页
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;
网友评论