同乐学堂第五章 SELECT语句：WHERE子句
1.获取居住在Stratford的球员号码。
select playerno
from players
where town='Stratford';
2.获取那些在出生了17年或加入俱乐部的每个球员的号码。出生日期和加入俱乐部的年份。
select playerno,birth_date,joined
from players
where year(birth_date)+17=joined;
3.获取联盟会员号码为7060的球员号码。
select playerno
from players
where leagueno=7060;
4.获取那些真正拥有一个联盟会员号码的球员号码和联盟会员号码。
select playerno,leagueno
from players
where leagueno=leagueno;
注意：如果where子句写成where true，那么就会列出leagueno为空值的情况。
5.获取没有联盟会员号的球员号码。
select playerno,leagueno
from players
where leagueno<=>NULL;
注意：<=>符号的意思是：当值彼此相等或者都为空的时候，他的值为true，若其中一个为空或者是非空值但不相等的时候，结果为false，这个条件的结果不会是unknown。
6.获取那些获胜局数等于2并且输掉的局数等于3的比赛编号。
select matchno
from matches
where won=2 and lost=3;
或者：
select matchno
from matches
where (won,lost)=(2,3);
注意：我们可以只用另一个比较运算符，而不是用等于运算符。然而我们必须注意一个比较是如何处理的。例如：
(2,4)>(1,3)并不等价于(2>1)and(4>3)，而是等价于：(2>1)or(2=1 and 4>3)
下面的表达式是将谓词表达式与标量表达式的抓换，可以看一下。
(E1,E2)=(E3,E4) à(E1=E2)and(E3=E4)
(E1,E2)<>(E3,E4) à(E1<>E3)or(E2<>E4)
(E1,E2)?(E3,E4) à(E1?E3)or(E1=E3 and E2?E4)
(E1,E2,E3)?(E4,E5,E6) à(E1?E4)or(E1=E4 and E2?E5)or(E1=E4 and E2=E5 and E3?E6)
7.(子查询中的比较运算符)获取1号球队队长的号码和名字。
select players.playerno,players.name
from players,teams
where players.playerno=teams.playerno and teams.teamno=1;
第二种写法：
select playerno,name
from players
where playerno=
(select playerno from teams where teamno=1);
8.找出那些比联盟会员号码为8467的球员年龄更大的球员号码、名字和首字母。
select playerno,name,initials
from players
where birth_date<
(select birth_date from players where leagueno=8467);
9.获取队长为27号球员的球队所参加的比赛。
select matchno
from matches
where teamno=
(select teamno from teams where playerno=27);
10.获取和7号球员具有相同联盟号的球员号码，若7号球员没有联盟号，就显示出所有没有联盟号的球员。
select playerno
from players
where leagueno<=>(select leagueno from players where playerno=7);
11.找出和7号球员居住在同一城市并且和2号球员性别相同的每个球员的号码、居住城市和性别。
select playerno,town,sex
from players
where (town,sex)=(
(select town from players where playerno=7),
(select sex from players where playerno=2));
12.6号球员与1900年1月1日成为网球俱乐部委员会的秘书。找出从那一天开始在委员会任职且和6号球员同一天卸任的球员号码。
select distinct playerno
from committee_members
where (begin_date,end_date)=
(select begin_date,end_date from committee_members
where playerno=6
and position='Secretary'
and begin_date='1990-01-01');
13.获取那些名字和首字母的组合在字母顺序中位于6号球员之前的所有球员号码，名字和首字母。
select playerno,name,initials
from players
where (name,initials)<(select name,initials from players where playerno=6);
14.获取在1号比赛之后所进行的比赛编号。（进阶1-41 ）
select matchno
from matches_special
where (start_date,start_time)>(select start_date,start_time from matches_special where matchno=1);
15.获取居住在Inglewood的球员参加的比赛的编号。
select matchno
from matches,players where players.playerno=matches.playerno and players.town='Inglewood';
16.对于球队的队长参加的每场比赛，获取比赛编号，球员的号码和球队的号码。
select matchno,playerno,teamno
from matches
where playerno=(select playerno from teams where teams.playerno=matches.playerno);
17.名字的第三个字母等于他所效力的球队的分级的第三个字母的球员所参加的比赛号码。
select matchno
from matches
where substr((select division from teams where teams.teamno=matches.teamno),3,1)
=
substr((select name from players where players.playerno=matches.playerno),3,1);
18.获取那些拥有一个联盟会员号码的球员的号码和联盟会员号码。
select playerno,leagueno
from players
where leagueno;
19.获取号码不是1的球队号码。
select teamno
from teams
where teamno<>1;
20.获取出生于1970年之后的每个男性球员的号码，名字，性别和出生日期。
select playerno,name,sex,birth_date
from players
where year(birth_date)>'1970-12-31' and sex='M';
21.获取居住在Plymouth或Eltham的所有球员号码名字以及居住城市。
select playerno,town
from players
where town='Plymouth' or town='Eltham';
22.获取那些不是居住在Stratford的球员号码，名字和居住城市。
select playerno,name,town
from players
where town<>'Stratford';
或者：…where not town=’Stratford’;
23.获取那些拥有一个联盟会员号码和一个电话号码的所有球员号码，联盟会员号码和电话号码。
select playerno,leagueno,phoneno
from players
where leagueno and phoneno;
24.获取那些居住在Stratford或出生于1963年的每个球员号码，居住城市和出生日期，但不包含那些居住在Stratford并且出生于1963年的球员。
select playerno,town,birth_date
from players
where (town='Stratford' or year(birth_date)='1963') and not (year(birth_date)='1963' and town='Stratford');
25.(使用表达式列表的in运算符)找出居住在Inglewood、Plymouth、Midhurst或Douglas的每个球员的号码，名字和城市。
select playerno,name,town
from players
where town='Inglewood' or town='Plymouth' or town='Midhurst' or town='Douglas';
可以这样简化：
select playerno,name,town
from players
where town in('Inglewood',' Plymouth',' Midhurst',' Douglas');
26.获取那些出生于1962、1963或1970年的号码，名字和出生年份。
select playerno,name,birth_date
from players
where year(birth_date) in ('1962','1963','1970');
27．对于那些有两局获胜或两局输掉的比赛，获取比赛号码，获胜的局数和输掉的局数。
select matchno,won,lost
from matches
where 2 in (won,lost);
28.对于那些号码等于100或者号码等于支付号码为1的罚款的球员号码，或者号码等于2号球员的队长的号码的球员，获取其球员号码。
select playerno
from players
where playerno in (100,
(select playerno from penalties where paymentno=1),
(select playerno from teams where teamno=2));
29.对于那些获胜局数等于比赛编号除以2，或者等于输掉的局数，或者等于1号比赛中输掉的局数的所有比赛，获取比赛编号，获胜的局数和输掉的局数。
select matchno,won,lost from matches
where won in (truncate(matchno/2,0),lost,
(select lost from matches where matchno=1));
30.获取名字以大写字母B、C或E开头的球员所参加比赛的编号。
select matchno from matches
where (select substr(name,1,1) from players
where players.playerno=matches.playerno) in ('B','C','E');
31.对于那些最终比分为3:1和3:2的所有比赛，获取比赛编号，获胜局数和输掉的局数。
select matchno,won,lost
from matches
where won/lost in (3,1.5);
或者…where (won,lost) in ((3,1),(3,2));
32.对于名字和首字母等于6号球员或27号球员的所有球员，获取编号和名字首字母。
select playerno,name,initials
from players
where (name,initials) in (
(select name,initials from players where playerno=6),
(select name,initials from players where playerno=27));
33.至少参加了一场比赛的每个球员的球员号码、姓名和首字母。
select playerno,name,initials
from players
where playerno in (select playerno from matches);
34.获取那些至少为1号球队打过一场比赛的球员的号码和名字。
select playerno,name
from players
where playerno in (select playerno from matches where teamno=1);
35.获取那些至少为队长不是6号球员的球队打过球的每个球员的号码和名字。
select playerno,name
from players
where playerno in
(select playerno from matches where teamno not in
(select teamno from teams where playerno=6));
36.从committee_members表中获取任职日期和卸任日期和那些具有Secretary职位的一行相同的所有行的内容。
select * from committee_members
where (begin_date,end_date) in
(select begin_date,end_date from committee_members where position='Secretary');
37．获取那些至少引起一次罚款的球员名字，首字母和居住城市。
select name,initials,town from players
where playerno in
(select playerno from penalties);
38.(between运算符)找出出生于1962年到1964年之间的每个球员的号码，名字和出生日期。
select playerno,name,birth_date
from players
where year(birth_date) between '1962' and '1964';
39.获取赢得局数和输掉局数之和为2，3或4的比赛。
select matchno,won+lost
from matches
where won+lost between 2 and 4;
40.获取出生日期在B.Newcastle和P.Miller的出生日期之间的每个球员的号码，名字，出生日期和首字母。
select playerno,name,birth_date,initials
from players
where birth_date between (select birth_date from players where name='Newcastle' and initials='B')
and
(select birth_date from players where name='Miller' and initials='P');
41.(like运算符)找出名字以大写字母B开头的每个球员的名字和号码。
select name,playerno
from players
where name like 'B%';
如果不这样写，可以将where子句写成：
…where substr(name,1,1)=’B’;
42.获取名字以小写字母r结尾的每个球员的名字和号码。
select name,playerno
from players
where name like '%r';
43.获取名字以字母%e作为名字的倒数第二个字母的每个球员的号码。
select name,playerno
from players
where name like '%e_';
44.获取那些名字以一个字母结尾的每个球员的名字，居住城市和号码，而这个字母等于他所居住城市的第三个字母。
select name,town,playerno
from players
where name like concat('%',substr(town,3,1));
45.找出名字中包含一个下划线的球员的名字和号码。
select name,playerno
from players
where name like '%#_%' escape '#';
46.(regexp运算符)获取那些名字中含有小写字母e的球员名字和号码。
select name,playerno from players where name regexp 'e';
47.获取那些名字以字母组合ba开头的球员名字和号码。
select name,playerno from players
where name regexp '^ba';
48.对于名字以自己所居住的街道名字的第一个字母作为结尾的球员，获取其名字，所居住的街道和号码。
select name,street,playerno
from players
where name regexp concat(substr(street,1,1),'$');
49.获取包含了字母a，b或c的每个球员的名字和号码。
select name,playerno from players
where name regexp '[abc]';
注意：’[abc]’也可以写为’[a-c]’，意思是a-c的任意一个字母。
50.对于名字由模式m.n组成的每一个玩家，获取其名字和号码，这个点可以是任意随机字符。
select name,playerno from players
where name regexp 'm.n';
51.对于名字中有m,e,n中的一个且这种情况连续出现了两次，获取球员的名字和号码。
select name,playerno from players
where name regexp '[men][men]';
52.获取邮政编码中以3作为第三个字母的球员的号码和邮政编码。
select playerno,postcode from players
where postcode regexp '^[0-9][0-9]3';
或… where postcode like '__3%';
53.获取居住在街道名以St开头并且以Road结尾的每个球员所居住的街道和号码。
select street,playerno
from players
where street regexp '^St.*Road$';
54.找出邮政编码包含一个或多个数字后面跟着一个或多个字母的每个球员的号码和邮政编码。
select playerno,postcode from players
where postcode regexp '[0-9]+[a-z]+';
注意：*表示0个，1个或多个字符，？表示0个或一个字符，+表示一个或多个。
55.获取名字不以A到M之间的大写字母开头的每个球员的名字和号码。
select name,playerno from players
where name regexp '^[^A-M]';
或
select name,playerno from players
where substr(name,1,1) not in ('A','B','C','D','E','F','G','H','I','J','K','L','M');
56.获取名字由7个字母或更多字母组成的每个球员的号码和姓名。
select playerno,name from players
where name regexp '^[a-z]{7}';
说明：前8个位置浩瀚一个逗号或一个空白的名字不会出现在结果中。
57.获取名字至少含有6个字母并且最多含有7个字母的每个球员的号码和名字。
select playerno,name from players
where name regexp '^[a-z]{6,7}$';
58.获取邮政编码中含有4个4的球员号码和邮编。
select playerno,postcode from players
where postcode regexp '4{4}';
59.获取居住在街道名包含了字符串Street或Square的每个球员的号码和居住的街道。
select playerno,street from players
where street regexp 'Street|Square';
60.获取名字中包含一个空格的每个球员的号码和名字。
select playerno,name from players
where name regexp '[[.space.]]';
61.获取居住的街道名称中包含Street的球员号码和居住的街道名。
select playerno,street from players
where street regexp '[[:<:]]Street[[:>:]]';
或
select playerno,street from players
where street like '%street%';
注意：[:x:]使你能够查询字符的特定的组，即所谓的字符类。
62-64
同乐学堂（MySQL全文索引Match Against与Like比较，此篇为转载
（http://blog.csdn.net/zyz511919766/article/details/12780173）
65.(is null运算符)获取拥有一个联盟会员号码的每个球员的号码和联盟号码。
select playerno,leagueno
from players
where leagueno is not null;
66.获取联盟会员号码不等于8467的每个球员的名字、号码和联盟会员号码。
select name,playerno,leagueno from players
where leagueno <> '8467' or leagueno is null;
67.(exists运算符)获取那些至少支付了一次罚款的球员名字和首字母。
select name,initials from players
where playerno in(select playerno from penalties);
或：
select name,initials from players
where exists
(select * from penalties where playerno=players.playerno);
68.获取那些不是队长的球员的名字和首字母。
select name,initials from players
where not exists
(select * from teams where playerno=players.playerno);
可以将*替换成’nothing’，结果一样。
69.(all、any运算符)获取那些最老的球员的号码名字和生日。最老的球员是出生日期数值小于或等于所有其他球员的球员。
select playerno,name,birth_date from players
where birth_date<=all
(select birth_date from players);
70.获取那些比曾经为2号球队效力过的所有球员都老的球员的号码和出生日期。
select playerno,birth_date
from players
where birth_date<all
(select birth_date from players as p inner join matches as m
on p.playerno=m.playerno where m.teamno=2);
71.对于每个球队，找出球队中获胜局数最少的球员所在的球队号码和他的号码。
select teamno,playerno from matches as m1
where won<=all
(select won from matches as m2 where m1.teamno=m2.teamno);
72.找出最大的联盟会员以及相应的球员编号。
select leagueno,playerno from players
where leagueno>=all
(select leagueno from players);
73．对于那些居住在同一城市的球员获取联盟会员号码最小的每个球员的号码、所居住的城市以及联盟会员号码。
select playerno,town,leagueno from players as p1
where leagueno <= all
(select p2.leagueno from players as p2 where p1.town=p2.town
and leagueno is not null);
74.除了最老的球员获取所有球员的号码，名字和出生日期。
select playerno,name,birth_date
from players
where birth_date>any
(select birth_date from players);
75.获取那些至少引发一次罚款额高于27号球员所支付的罚款额罚款的球员，27号球员不出现在结果中。
select distinct playerno from penalties
where playerno<>27
and
amount>any
(select amount from penalties where playerno=27);
76.获取那些至少比同一城市的另一个球员年轻的所有球员的号码、日期和他居住的城市。
select playerno,birth_date,town
from players as p1
where birth_date>any
(select birth_date from players as p2 where p1.town=p2.town);
77.(子查询中列的作用域)对于那些至少引发一次罚款的每个球员，获取其号码和名字。
select playerno,name
from players
where playerno in
(select playerno from penalties);
或：
select playerno,name from players as p
where exists
(select * from penalties as pen
where p.playerno=pen.playerno);
78.(使用关联性子查询的更多例子) 获取44号球员曾经效力过的每个球队的球队编号和分级。
select teamno,division from teams
where exists
(select * from matches where playerno=44 and teamno=teams.teamno);
79.获取那些曾经引起多于1次罚款的球员号码。
select distinct playerno from penalties as pen
where playerno in
(select playerno from penalties where paymentno<>pen.paymentno);
80.获取那些没有为1号球队打过比赛的球员号码和名字。
select playerno,name from players
where 1 <> all
(select teamno from matches where playerno=players.playerno);
81.获取那些57号球员没有效力过的球队编号。
select teamno from teams
where not exists
(select * from matches where playerno=27 and teamno=teams.teamno);
82.哪个球员曾经为teams表中的所有球队打过比赛？
select playerno from players as p
where not exists
(select * from teams as t
where not exists
(select * from matches as m
where t.teamno=m.teamno and p.playerno=m.playerno));
83.获取那些至少为57号球员曾经效力过的所有球队都打过一场比赛的球员的号码。
select playerno from players
where not exists
(select * from matches as m1 where playerno=57 and not exists
(select * from matches as m2 where m1.teamno=m2.teamno and players.playerno=m2.playerno));
84.获取那些只为57号球员曾经效力过的球队打过球的每个球员的号码。
select playerno from matches
where teamno in
(select teamno from teams where teamno not in
(select teamno from matches where matchno=57));
85.获取那些居住在Stratford的每个球员的号码。
select playerno from players
where town='Stratford';
86.获取那些引起一次25美元罚款的每个球员号码。
select playerno from penalties where amount=25;
微信扫一扫，打赏作者吧～
