同乐学堂第六章 select语句：select子句和聚合函数
1.获取整个penalties表。
select * from penalties;
2.获取不是队长的球员所引起的所有罚款的相关信息。
select penalties.* from penalties inner join teams on
penalties.playerno=teams.playerno;
或：
select penalties.* from penalties,teams
where penalties.playerno=teams.playerno;
3.对于每场比赛，获取比赛时间，单词Tolly，列won和列lost之间的差值以及won列乘以10的值。
select matchno,'Tolly',won-lost,won*10 from matches;
4.找出players列中所有不同的城市名。
select distinct town from players;
5.获取每个已有的街道和城市的名字的组合。
select distinct street,town from players;
6.找出所有不同的联盟会员号码。
select distinct leagueno from players;
7.players表中注册了多少球员？
select count(*) as numbers from players;
8.有多少球员居住在Stratford？
select count(*) from players where town='Stratford';
9.一共有多少个联盟会员号码？
select count(all leagueno) from players;
10.在town列中，有多少不同的城市名？
select count(distinct town) from players;
11.获取从球员的姓氏的不同开头字母的个数。
select count(distinct substr(name,1,1)) from players;
12.获取出现在penalties中不同年份的数目。
select count(distinct year(payment_date)) from penalties;
13.获取不同城市名字的数目和不同性别的数目。
select count(distinct town),count(distinct sex) from players;
14.获取那些引起罚款的次数比他们参加的比赛次数还多的那些球员的号码和名字。
select playerno,name from players
as p
where (select count(*) from penalties as pen where p.playerno=pen.playerno)
>
(select count(*) from matches as m where p.playerno=m.playerno);
15.对于每个球员找出球员号码，名字以及他所引起罚款的号码，但是只是针对那些至少有两次罚款的球员。
select playerno,name,
(select count(*) from penalties where penalties.playerno=players.playerno) as numbers
from players
where (select count(*) from penalties where penalties.playerno=players.playerno)>=2;
16.获取罚款总次数，后面跟着比赛的总场次。
select
(select count(*) from penalties),
(select count(*) from matches);
17.最高的罚款额是多少？
select max(amount) from penalties;
18.居住在Stratford的一名球员所引发的最低罚款额是多少？
select min(amount) from penalties
where playerno in
(select playerno from players where town='Stratford');
或：
select min(amount) from penalties,players
where town='Stratford' and players.playerno=penalties.playerno;
19.有多少次罚款额等于罚款额最低的那次罚款？
select count(*) from penalties
where amount=(
select min(amount) from penalties);
20.最高的罚款额和最低的罚款额相差多少美分？
select (max(amount)-min(amount))*100 as diff_cents from penalties;
21.在所有球员中，获取按字母顺序排在最后的那个姓氏的第一个字母。
select substr(max(name),1,1) from players
order by name;
22.居住在Midhurst的左右球员中最大的联盟会员号码是多少？
select max(leagueno) from players
where town='Midhurst';
23.居住在Amsterdam的所有球员中最小的联盟会员号码是多少？如果不存在这样的一个球员，输出文本Unknown。
select case when min(leagueno) is null
then 'Unknown'
else min(leagueno)
end as min_leagueno_Amsterdam
from players
where town='Amsterdam';
24.对于至少引发了一次罚款的每个球员，找出球员号码，最高罚款和罚款支付日期。
select playerno,amount,payment_date
from penalties as pen1
where amount=
(select max(amount)
from penalties as pen2
where pen2.playerno=pen1.playerno);
25.对于每个球员，获取球员号码和他所支付的最高罚款和他在一场比赛中赢得的最高局数。
select playerno,
(select max(amount) from penalties where penalties.playerno=players.playerno) as highest_penalty,
(select max(won) from matches where matches.playerno=players.playerno) as highest_won
from players;
26.获取最低罚款额等于他的最高罚款额的每个球员号码。
select playerno
from players
where (select min(amount) from penalties where penalties.playerno=players.playerno)
=
(select max(amount) from penalties where penalties.playerno=players.playerno);
27.来自Inglewood的球员所引起的罚款总额是多少？
select sum(amount) from penalties
where playerno in (select playerno from players where town='Inglewood');
28.获取44号球员罚款的平均值。
select avg(amount) from penalties where playerno=44;
29.哪个球员曾经引起过一次比平均值高的罚款？
select distinct playerno from penalties
where amount>(select avg(amount) from penalties);
30.罚款额的无权重算术平均值是多少？
select avg(distinct amount) from penalties;
31.球员的名字的平均长度是多少？最长的名字长度是多少？
select avg(length(rtrim(name))),max(length(rtrim(name)))
from players;
32.对于每一笔罚款，获取支付编号，数额，罚款额和罚款额平均值之间的差值。
select paymentno,amount,abs(amount-(select avg(amount) from penalties)) as differencre
from penalties;
33.获取44号球员所引起所有罚款的方差。
select variance(amount) from penalties where playerno=44;
34.获取44号球员所引起的所有罚款的标准差。
select stddev(amount) from penalties where playerno=44;
35.获取所有罚款的样本方差和总体方差。
select var_samp(amount),variance(amount) from penalties;
36.获取所有罚款的样本标准差和总体标准差。
select stddev_samp(amount),stddev(amount) from penalties;
第七章 SELECT语句：GROUP BY 子句
1.获取players表中所有不同城市的名字，按城市名字字典顺序排列。
select town from players
group by town;
2.对于每个城市，获取球员的人数。
select town,count(*) from players
group by town;
3.对于每个球队，获取球队编号，该球队曾经参加过的比赛编号和所应得的总局数。
select teamno,count(*),sum(won)
from matches
group by teamno;
4.对于居住在 Elthan的球员担任队长的每个球队，获取球队编号和该队曾经参加过的比赛的编号。
select teamno,count(*)
from matches
where teamno in
(select teamno from teams inner join players on teams.playerno=players.playerno
where town='Eltham')
group by teamno;
5.获取每个不同的罚款额，后面跟着该罚款额出现在penalties表中的次数以及罚款额乘以次数的结果。
select amount,count(*),amount*count(*) as sum_amount
from penalties
group by amount;
6.对于matches表，获取球队编号和队员号码的不同组合。
select teamno,playerno from matches
group by teamno,playerno;
7.队员至少引起一次罚款的每个球员，获取球员号码，名字以及引起的罚款总额。
select p.playerno,name,sum(amount)
from players as p inner join penalties as pen
on p.playerno=pen.playerno
group by p.playerno,nameg
8.对于penalties表中的每一年，获取已经支付的罚款次数。
select year(payment_date),count(*)
from penalties
group by year(payment_date);
9.根据球员的号码来对球员分组，第一支应该包含号码1到24的球员，第二组应该包含25到49的球员等。对于每个组，获取球员的数目和最大的球员号码。
select truncate(playerno/25,0),count(*),max(playerno)
from players
group by truncate(playerno/25,0);
10.（对空值的分组）找出球员号码，名字和不同的联盟会员号码。
select playerno,name,leagueno from players
group by leagueno;
注意：7号，28号，39号，95号球员没有联盟会员号码，显示首行NULL。
11.对于每个球队，获取比赛编号，球队编号，并根据球队的编号按照降序排列。
select matchno,teamno,count(*) from matches
group by teamno
order by teamno desc ;
12.group by子句的几条规则：
规则1：如果一个选择语句块有一个group by子句，在select子句中指定的任何列都必须专门作为聚合函数的一个参数出现，或者位于group by子句中给出的列的列表中，或者二者兼具。
规则2：在大多数例子中，用来分组的表达式也出现在select语句中，，然而这并非必须的，出现在group by子句中的表达式也可以出现在select子句中。
规则3：用来分组的表达式也可以出现在select子句的一个复合表达式中，
规则4：如果一个表达式在group by子句中出现了多次，第二次出现的表达式被直接移除，group by子句group by town，town会自动转换成group by town。
规则5：当select子句中包含了group by子句中指定的所有列的时候，distinct是多余的。
13.（group_concat函数）对于每个球队，获取球队的号码和为该七对效力的球员列表。
select teamno,group_concat(playerno)
from matches
group by teamno;
14.对于每个球队，获取球队编号，并且对于每个球员，如果他为该球队打过比赛，获取相同的球队编号。
select teamno,group_concat(teamno)
from matches
group by teamno;
15.获取所有的支付编号（以一行显示）。
select group_concat(paymentno)
from penalties;
16.居住在Stratford和Inglewood的球员的罚款总数的平均值是多少？
select avg(total)
from (select playerno,sum(amount) as total from penalties group by playerno) as totals
where playerno in (select playerno from players where town='Stratford' or town='Inglewood');
17.对于那些引发了罚款并且是队长的每个球员，获取球员号码，名字，他所引发的罚款次数和他担任队长的球队的数目。
select players.playerno,name,number_of_penalties,number_of_teams
from players,(select playerno,count(*) as number_of_penalties from penalties group by playerno) as number_penalties
,
(select playerno,count(*) as number_of_teams from teams group by playerno) as number_teams
where players.playerno=number_penalties.playerno and players.playerno=number_teams.playerno;
18.获取参加一场比赛的每个球员的号码和罚款总次数。
select distinct m.playerno,numberp
from matches as m left outer join (select playerno,count(*) as numberp from penalties group by playerno) as np
on m.playerno=np.playerno;
19.根据支付日期对罚款分组，第一组包含1980年1月1日到1981年6月30日之间的所有罚款，第二组包含1981年7月1日到1982年12月31日之间的所有罚款，对于每一组，得到罚款的总额数。
select groups.pgroup,sum(p.amount)
from penalties as p,
(select 1 as pgroup,'1980-01-01' as start,'1981-06-30' as end
union
select 2,'1981-07-01','1982-12-31'
union
select 3,'1983-01-01','1984-12-31') as groups
where p.payment_date between start and end
group by groups.pgroup
order by groups.pgroup;
20.对于每一笔罚款，获取其罚款编号，罚款数额以及罚款编号小于该罚款编号的所有罚款的罚款额的总和（累计值）。
select p1.paymentno,p1.amount,sum(p2.amount)
from penalties as p1,penalties as p2
where p1.paymentno>=p2.paymentno
group by p1.paymentno,p1.amount
order by p1.paymentno;
21.对于每一笔罚款，获取支付编号，罚款额和该罚款额占罚款额总和的百分比。
select p1.paymentno,p1.amount,(p1.amount*100)/sum(p2.amount)
from penalties as p1,penalties as p2
group by p1.paymentno,p1.amount
order by p1.paymentno;
22.（使用rollup的分组）对于每个球员，获取他的罚款总额以及所有罚款的总和。
select playerno,sum(amount) from penalties group by playerno
union
select null,sum(amount) from penalties;
或者：
select playerno,sum(amount)
from penalties
group by playerno with rollup;
23.对于性别-城市的每个组合，获取球员的号码，每个性别的球员的总数和整个表中的球员的总数。
select sex,town,count(*)
from players
group by sex,town with rollup;
第八章 select 语句：having子句
1.获取那些引起多于1次罚款的球员号码。
select playerno from penalties
group by playerno
having count(*) > 1;
2.获取那些最后一次罚款发生在1984年的每个球员的号码。
select playerno
from penalties
group by playerno
having max(year(payment_date))=1984;
3.对于引发罚款总数超过150美元的每个球员，给出球员号码和罚款总额。
select playerno,sum(amount)
from penalties
group by playerno
having sum(amount)>150;
4.对于担任队长并引发罚款总额超过80美元的每个球员，给出球员号码和罚款总额。
select playerno,sum(amount)
from penalties
where playerno in (select playerno from teams)
group by playerno
having sum(amount)>80;
5.对于具有最高罚款总额的球员，给出球员号码和罚款总额。
select playerno,sum(amount)
from penalties
group by playerno
having sum(amount)>=all
(select sum(amount) from penalties group by playerno);
6.给出所有罚款额的总和，但是，只有在总和大于250美元的情况下才给出。
select sum(amount)
from penalties
having sum(amount)>250;
7.having子句的一半规则。
having子句中所有的列指定必须出现在一个聚合函数中，或者出现在group by子句指定的列表中
第九章 select 语句：order by子句
1.找出每笔罚款的支付号码和引起罚款的球员号码，按照球员号码来排序。
select paymentno,playerno
from penalties
order by playerno;
2.对于每一笔罚款，获取球员号码和罚款数额，按照这两列的结果来排序。
select playerno,amount
from penalties
order by playerno,amount;
3.获取所有罚款额，并且按照球员号码和罚款额来排序。
select amount
from penalties
order by playerno,amount;
4.对于所有球员，获取姓名，首字母以及球员号码，按照姓的首字母对结果排序。
select name,initials,playerno from players
order by substr(name,1,1);
5.获取球员号码以及所有罚款额，并且按照罚款额和平均罚款之间的差来对结果排序。
select playerno,amount from penalties
order by abs(amount-(select avg(amount) from penalties));
6.获取所有罚款的球员号码和罚款额，并且根据每个球员的平均罚款额来对结果排序。
select playerno,amount from penalties as p1
order by (select avg(amount) from penalties as p2
where p1.playerno=p2.playerno) ;
7.对于每个至少引起一次罚款的球员，获取罚款总额，按照这个总额对结果排序。
select playerno,sum(amount) from penalties
group by playerno
order by 2;
8.对于每个球员，获取球员号码，姓名以及罚款总额，根据这总和排序。
select playerno,name,(select sum(amount) from penalties as pen where pen.playerno=p.playerno)
from players as p
order by 3;
9.对于每一笔罚款，获取球员号码和罚款额，按照球员号码的降序以及罚款额的升序排列。
select playerno,amount from penalties
order by playerno desc,amount asc;
10.获取不同联盟会员号码并且按照降序排列结果。
select distinct leagueno from players
order by leagueno desc;
第十章 select 语句：LIMIT子句
1.获取前四个最大球员的号码和名字。
select playerno,name from players
order by playerno desc
limit 4;
2.从players表中获取5个最小的联盟会员号码对应的起源号码和名字。
select leagueno,playerno,name from players
order by leagueno asc
limit 5;
3.获取最优秀的3个球员号码，最优秀的球员就是赢得比赛数目最多的人。
select playerno,count(*) as numbers from matches
where won>lost
group by playerno
order by playerno desc
limit 3;
4.获取最好的3个球员号码，如果球员具有相同的比赛数目，只显示球员号码最大的球员。
select playerno,count(*) as numbers from matches
where won>lost
group by playerno
order by numbers desc,playerno desc
limit 3;
5.获取最好的3个球员，根据球员号码来对结果进行最终排序。
select *
from (select playerno,count(*) as numbers from matches
where won>lost
group by playerno
order by numbers desc,playerno desc
limit 3) as T;
6.4个最低的罚款额的平均值。
select avg(amount) from
(select amount from penalties order by amount limit 4) as T;
7.第3高的罚款额是多少?
select min(amount) from
(select amount from penalties
order by amount desc
limit 3) as T;
8.获取最高的罚款额，并且略去重复的罚款额。
select distinct amount from penalties
order by amount desc
limit 3;
9.在具有最大的6个联盟会员号码的一组中，获取球员号码最小的3个球员号码。
select playerno from
(select playerno from players
where leagueno is not null
order by leagueno desc limit 6) as T
order by playerno asc
limit 3;
10.获取引发最高罚款额的前3个球员号码和名字。
select playerno,name from players
where playerno in
(select playerno from
(select playerno,sum(amount) as total from penalties
group by playerno
order by total desc
limit 3) as T);
11.（带有偏移量的limit）获取那些具有最低的5个球员号码组成的球员号码和名字（从4号球员开始）。
select playerno,name from players
order by playerno asc
limit 5 offset 3;
12.给出前5个支付号码。
select sql_calc_found_rows paymentno from penalties limit 5;
微信扫一扫，打赏作者吧～
