同乐学堂前言
每多学一点知识，就少写一行代码.
第二章 查询和更新数据
1.为一个特定的表添加一个不正确的日期并显示结果。
create table incorrect_date(column1 date);
insert into incorrect_date values ('2009-13-12');
select column1 from incorrect_date;
可以sql-mode 等于 ALLOW_INVALID_DATES 可以存不正确的日期，而不变零日期。具体可参考如下文章
https://www.ztloo.com/2017/04/22/day1-mysql编程学习笔记/
2.对于赢得的局数等于输掉的局数加2的每场比赛，获得比赛号码以及赢得的局数和输掉的局数之间的差值。
select matchno,won-lost as difference
from matches
where won-lost=2;
3.对于每个球队得到编号和分级。
select teamno,division
from teams;
4.对于每个球队，获得编号和分级，并使用全名。
select teamno as team_number,division as division_of_team
from teams;
5.对于每次罚款，获取支付编号和以分为单位的罚款数额。
select paymentno,amount*100 as cents
from penalties;
6.从matches表中获得一些数据。
select matchno as primaryKey,80 as eighty,
won-lost as difference,time('23:59:59') as 'Almost_midNight','Text' as Text
from matches
where matchno<=4;
7.把所有罚款按照罚款金额以美分为单位分组，并且按照美分的数目来排序。
select amount*100 as cents
from penalties
group by cents
order by cents;
8.获取球员编号大于6的编号，名字和初始字母，使用完整的列指定。
select players.playerno,players.name,players.initials
from players
where players.playerno>6;
9.创建用户变量playerno，并用7来初始化它。
set @playerno=7;
10.获取球员号码小于已经创建的用户变量playerno的值的所有球员的姓，居住城市也邮政编码。
select players.name,town,postcode
from players
where playerno<@playerno;
11.查询playerno变量的值。
select @playerno;
12.打开SQL_WARNINGS变量。
set @@SQL_WARNINGS=TRUE;
13.对于当前会话，把系统变量SQL_SELECT_LIMIT的值设置为10.这个变量决定了一条select语句结果中最大的行数。
set @@session.SQL_SELECT_LIMIT=10;
//注意：session放在系统变量之前，说明仅对当前会话有用，全局变量仍然是原来的值。获得系统变量sql_select_limit的方式为：
select @@global.sql_select_limit;
14.获取当前系统的日期，时间，时间+日期以及当前用户。
select current_date;
select current_time;
select current_timestamp;
select current_user;
15.case表达式。获取1980年后加入俱乐部的每个球员号码，性别，名字。性别必须显示为’Female’或’Male’
select playerno,name,
case sex
when 'F' then 'Female'
when 'M' then 'Male'
end as SEX
from players
where joined>1980;
16.在一条select语句中嵌套使用case。
select playerno,town,birth_date,
case town
when 'Stratford' then 'ST'
when 'Plymouth' then 'PM'
when 'Inglewood' then 'IW'
else 'OT'
end as Position,
case town
when 'Stratford' then
case birth_date
when '1948-09-01' then 'Old Stratford'
else 'Young Stratford'
end
when 'Inglewood' then
case birth_date
when '1962-07-08' then 'Old Ingelwood'
else 'Young Inglewood'
end
else 'Rest'
end as type
from players;
17.对每个球员找到球员号码，他加入俱乐部的年份和球员的年龄组。
select playerno,joined,
case
when joined<1980 then 'senior'
when joined<1983 then 'jubior'
else 'children'
end as age_group
from players
order by joined;
from players;
18.对于每个球员，找出球员号码，加入俱乐部的年份，他居住的城市以及分级。
select playerno,joined,town,
case
when joined>=1980 and joined<1983 then 'senior'
when town='Eltham' then 'Elthammers'
when playerno<10 then 'first_members'
else 'Rest'
end as level
from players;
19.获取1980年以后每笔罚款的支付号码以及年份。
select paymentno,year(payment_date)
from penalties
where year(payment_date)>1980;
20.对于姓氏以大写字母B开头的每个球员，获取其号码以及名字的首字母，随后跟一个小数点和姓。
select playerno,concat(left(initials,1),'.',name) as full_name
from players
where left(name,1)='B';
21.对于居住在Stratford的每个球员，获取其名字，姓氏和联盟会员号码，如果联盟会员号码为空，将其显示为1。
select initials,name,coalesce(leagueno,'1') as league
from players
where town='Stratford';
注意：coalesce(leagueno,’1’)的意思是：如果leagueno非空，就取当前leagueno，否则取1.
22.对于所有号码小于10的球员，获取球员号码，他们出生的那天是星期几，他们出生所在的月份以及他们出生那天是该年的多少天。
select playerno,dayname(birth_date),monthname(birth_date),dayofyear(birth_date)
from players
where playerno<10;
23.对于出生在Saturday的每一个球员，获取号码，出生日期和出生后7天的日期。
select playerno,birth_date,adddate(birth_date,interval 7 day) as add7days
from players where dayname(birth_date)='Saturday';
24.哪个球员在俱乐部某个职位超过500天？
select playerno,begin_date,end_date,datediff(end_date,begin_date)
from committee_members
where datediff(end_date,begin_date)>500
or(end_date is null
and datediff(current_date,begin_date)>500)
order by playerno;
或者这样写：
select playerno,begin_date,end_date,
datediff(coalesce(end_date,current_date),begin_date) as diff
from committee_members
where datediff(coalesce(end_date,current_date),begin_date)>500
order by playerno;
25.对于每一笔大于50的罚款，获取支付编号。
select paymentno
from penalties
where amount>50;
26.对于居住在Inglewood的每个球员，获取他们的名字和生日作为一个完整的字符值。
select concat(rtrim(name),cast(birth_date as char(10)))
from players
where town='Inglewood';
27.把编号为2的球员的联盟会员号码改为空值。
update players
set leagueno=NULL
where playerno=2;
28.对于每个球队，获取球队号码，后面跟着一个空值。
select teamno,cast(NULL as char)
from teams;
29.对于胜出局数大于或等于输掉的局数乘以2的每场比赛，获得比赛号码，胜出局数和输掉局数。
select matchno,won,lost
from matches
where won>=lost*2;
30.把50左移2位。
select 50<<2;
31.把二进制的11向左移动三位。
select B'11'<<3;
32.获得值6和10的二进制表示。
select conv(6,10,2),conv(10,10,2),bin(6),bin(10);
33.获取属于二进制表示1001和111的十进制值。
select conv(1001,2,10),conv(111,2,10);
34.从players表中获得球员号码为奇数的号码。
select playerno
from players
where playerno&1;
35.从players表中获得球员号码为偶数的号码和名字。
select playerno,name
from players
where playerno=(playerno>>1)<<1;
36.对matches表的列应用几个位运算符。
select matchno,teamno,matchno|teamno,matchno&teamno,matchno^teamno
from matches;
37.获取居住在Stratford的每个球员的球员号码和地址。
在使用||之前，先声明：set @@sql_mode='pipes_as_concat';
select playerno,town||' '||street||' '||houseno as Address
from players
where town='Stratford';
或者直接这样写：
select playerno,concat(town,' ',street,' ',houseno)
from players
where town='Stratford';
38.对于编号大于5的每次罚款，获得支付编号，罚款支付日期以及支付后7天的日期。
select paymentno,payment_date,payment_date+interval 7 day
from penalties
where paymentno>5;
或者这样写：
select paymentno,payment_date,
adddate(payment_date,interval 7 day) as add7days
from penalties
where paymentno>5;
39.获取在1982年圣诞节（12月25日）以及新年前夕支付的罚款。
select paymentno,payment_date
from penalties
where payment_date>='1982-12-25' and payment_date<adddate('1982-12-25',interval 7 day);
40.在日期直接量’2004-13-12’上增加一天，接下来显示错误信息。
select adddate('2004-13-12',interval 1 day);
show warnings;
41.创建一个matches表的特殊形式matches_special,其中包含比赛进行的日期，开始时间和结束时间。
create table matches_special(
matchno integer not null,
teamno integer not null,
playerno integer not null,
won smallint not null,
lost smallint not null,
start_date date not null,
start_time time not null,
end_date date not null,
end_time time not null,
primary key(matchno))
插入两组数据：
insert into matches_special values(1,1,6,3,1,'2004-10-25','14:10:12','2004-10-25','16:50:09');
insert into matches_special values(2,1,44,3,2,'2004-10-29','08:30:00','2004-10-25','10:00:00');
42.对于每场比赛，获取其开始时间，并获取开始时间加上8小时候的时间。
select matchno,start_time,addtime(start_time,'08:00:00')
from matches_special;
43.获得至少在午夜前6.5小时结束的比赛。
select matchno,end_time
from match_special
where addtime(end_time,'06:30:30')<='24:00:00';
44.(复合时间说和日期表达式)创建一个表，可以存储时间戳。
create table tstamp (col1 timestamp);
set @time=timestamp('1980-12-08 23:59:59.59');
insert into tstamp values(@time+interval 3 microsecond);
select col1,col1+interval 3 microsecond from tstamp;
45.(复合布尔表达式)获取每个球队的编号。
select teamno from teams where true or false;
等同于：select teamno from teams;因为true or false 永远为真。
46.指明那些罚款的编号大于4。
select paymentno,paymentno>4
from penalties;
47.找出下面两个条件都满足或都不满足的球员，球员的号码小于15，加入俱乐部的年份晚于1979年。
select playerno,joined,playerno<15,joined>1979
from players
where (playerno<15)=(joined>1979);
48.(行表达式)为committee_members表添加一个新行。
insert into committee_members values(7+15,current_date,current_date+interval 17 day,'Member');
49.获取居住在Stratford的Haseltine Lane的球员号码和名字。
select playerno
from players
where (town,street)=('Stratford','Haseltine Lane');
50.找到那些居住在Stratford的Haseltine Lane的所有球员号码和名字。
select playerno,name
from players
where (town,street)=(select 'Stratford','Haseltine Lane');
答案通49题。
第三章 SELECT语句、表表达式和子查询
1.找到至少引发两次多余25美元的罚款的每个球员号码，根据球员号码对结果排序。
select playerno
from penalties
where amount>25
group by playerno
having count(*)>1
order by playerno;
2.获得居住在Stratford的每个球员的号码和联盟会员号码，根据联盟号码排序。
select playerno,leagueno
from players
where town='Stratford'
order by leagueno;
3.获取球队队长的号码以及引发罚款的球员号码。
select playerno
from teams
union
select playerno
from penalties;
4.获得编号小于10的男性球员号码。
select playerno
from(select playerno,sex
from players
where playerno<10) as player10
where sex='M';
5.获取那些球员号码大于10 而小于100，并且加入俱乐部的年份大于1980年的男性。
select playerno
from ( select playerno,sex
from ( select playerno,sex,joined
from ( select playerno,sex,joined
from players
where playerno>10) as greater10
where playerno<100) as lessthan100
where joined>1980) as joined1980
where sex='M';
以上语句同：
select playerno
from players
where playerno<100 and playerno>10 and joined>1980 and sex='M';
6.对于号码小于60的每个球员，获取他们加入俱乐部的年份和100号球员加入俱乐部年份之间的差值。
select playerno,joined-
(select joined
from players
where playerno=100) as diff_100
from players
where playerno<60;
7.获取和27号球员出生于同一年的球员号码。
select playerno
from players
where year(birth_date)=(select year(birth_date)
from players
where playerno=27);
8.获取27号球员、44号球员和100号球员的生日作为一行。
select
(select birth_date from players where playerno=27),
(select birth_date from players where playerno=44),
(select birth_date from players where playerno=100);
9.获取与100号球员性别相同且居住在同一城市的球员号码。
select playerno
from players
where (sex,town)=(select sex,town
from players
where playerno=100);
第四章 SELECT子句：FROM子句
1.创建一个数据库extra，并创建一个cities表，插入两行数据。
create database extra;
use extra;
create table cities(cityno integer not null,
cityname char(20) not null,
primary key(cityno));
insert into cities values(1,'Stratford');
insert into cities values(2,'Inglewood');
2.在当前数据库为tennis的前提下，访问extra数据库的cities列。
select * from extra.cities;
同理，在当前数据库为extra的情况下，也可以范围tennis数据库里的所有内容。
3.获取球队编号和每个球队队长的名字。
select teamno,name
from teams,players
where teams.playerno=players.playerno;
4.对每一笔罚款，找出支付编号，罚款数额以及引起罚款的球员号码，名字和首字母。
select players.playerno,players.name,players.initials,
penalties.paymentno,penalties.amount
from players,penalties
where players.playerno=penalties.playerno;
5.（使用假名）对于每一笔罚款，获得支付编号，罚款数额，引发罚款球员的号码，名字和首字母。
select pen.paymentno,pen.amount,p.playerno,p.name,p.initials
from players as p,penalties as pen
where p.playerno=pen.playerno;
6.获取至少引起一次罚款的队长号码。
select t.playerno
from teams as t,penalties as pen
where t.playerno=pen.playerno;
7.获取至少引起一次罚款的队长号码，删除重复的号码。
select distinct t.playerno
from teams as t,penalties as pen
where t.playerno=pen.playerno;
8.获取至少参加了一场比赛的球员名字和首字母，注意，一个球员不一定必须出现在matches表中（他可能整个赛季都因伤缺席）
select distinct p.name,p.initials
from players as p,matches as m
where p.playerno=m.playerno;
9.对于每一场比赛，获取比赛编号，球员代号，球队编号，球员名字以及参加的球队分级。
select m.matchno,p.playerno,m.teamno,p.name,t.division
from matches as m,players as p,teams as t
where p.playerno=m.playerno and t.teamno=m.teamno;
10.对于球员加入俱乐部当年所引起的每一笔罚款，获得支付编号，球员号码以及支付日期。
select pen.paymentno,p.playerno,pen.payment_date
from players as p,penalties as pen
where pen.playerno=p.playerno and year(pen.payment_date)=p.joined;
11.（必须使用假名的情况）获取比R.Parmenter年龄大的每个球员的号码，在这个例子中，假设名字和首字母的组合是唯一的。
select p.playerno
from players as p,players as par
where par.name='Parmenter' and par.initials='R' and p.birth_date<par.birth_date;
12.（不同数据库的表）把tennis数据库中的players表和extra数据库中的cities表连接起来。
select p.playerno
from players as p,extra.cities
where p.town=cities.cityname;
13.(from子句中的显示连接)对于出生于1920年6月后的每个球员，找出球员号码，名字以及引起罚款的数额。
隐式表示为：
select p.playerno,name,pen.amount
from players as p,penalties as pen
where p.playerno=pen.playerno and p.birth_date>'1920-06-30';
显示表示为：
select p.playerno,name,pen.amount
from players as p inner join penalties as pen on(p.playerno=pen.playerno)
where p.birth_date>'1920-06-30';
14.对于每个球队，找出球队的号码和队长的名字，分别用隐式表示和显示表示。
隐式：
select t.teamno,p.name
from teams as t,players as p
where t.playerno=p.playerno;
显示：
select t.teamno,p.name
from teams as t inner join players as p on(t.playerno=p.playerno);
15.（左外连接）对于所有球员，获得球员号码，名字和他所引起的罚款，结果按照球员号码排序。
按照一般的写法是这样的：
select p.playerno,name,pen.amount
from players as p,penalties as pen
where p.playerno=pen.playerno
order by p.playerno;
但是这样的写法是不完整的，为了强调所有球员，应当使用左外连接：
select p.playerno,name,pen.amount
from players as p left outer join penalties as pen
on p.playerno=pen.playerno
order by p.playerno;
对于所有外连接，outer关键字可以省略，最终结果不会受任何影响。
左外连接是否必要，取决于问题和连接之间的关系，在p.playerno和pen.playerno之间，存在一个子集关系，因此，左外连接是有用的。
16.对于每一笔罚款，获取支付编号和球员名字。
select pen.paymentno,p.name
from penalties as pen left outer join players as p
on pen.playerno=p.playerno
order by pen.paymentno;
在此例中，penalties在一个左表，由于没有那一笔罚款不是助于一个球员的，因此没有一笔罚款是漏掉的，左外连接不起作用，使用内连接也会得到相同的结果。
17.对于每个球员，获得球员编号和名字以及他当队长的球队的编号和分级，按照球员编号排序。
select p.playerno,name,t.teamno,t.division
from players as p left outer join teams as t
on p.playerno=t.playerno
order by p.playerno;
18.对于居住在Inglewood的每个球员，获取球员编号，名字，罚款列表并且列出他曾经效力过的球队。
select p.playerno,p.name,pen.amount,m.teamno
from players as p left outer join penalties as pen
on p.playerno=pen.playerno left outer join matches as m
on p.playerno=m.playerno
where p.town='Inglewood';
19.（右外连接）对于所有球员，获取球员号码，名字和他们当队长的球队编号。
select p.playerno,p.name,t.teamno
from teams as t right outer join players as p
on t.playerno=p.playerno;
20.（自然连接）对于出生于1920-06-30后且至少一次罚款的每个球员，获得球员号码名字和所有罚款。
一般情况下我们会这样写：
select p.playerno,p.name,pen.amount
from players as p,penalties as pen
where p.playerno=pen.playerno and p.birth_date>'1020-06-30';
使用自然连接后，语句就缩短了：
select p.playerno,p.name,pen.amount
from players as p natural join penalties as pen
where p.birth_date>'1920-06-30';
21.（连接条件中的附加条件）下一条select语句包含了一个左外连接加上where子句的一个附加条件。
select teams.playerno,teams.teamno,penalties.paymentno
from teams left outer join penalties
on teams.playerno=penalties.playerno
where division= 'second';
22.（使用using替换连接条件）
如：from teams inner join players
on teams.playerno=players.playerno
和下面的语句是相等的：
from teams inner join players
on using (playerno)
在penalties表和teams表之间做一个左外连接。
select * from penalties left join teams
using (playerno);
23.获取居住在Stratford的球员号码。
select p.playerno
from players as p
where p.town='Stratford';
或者：
select playerno
from (select * from players
where town='Stratford') as stratforders;
24.获取在first级别的球队中担任队长的每个球员编号。
select small_teams.playerno
from(select playerno,division
from teams) as small_teams
where small_teams.division='first';
25.对于赢得的局数和输掉的局数之间的差值大于2的每一场比赛，获取比赛编号和比赛之间的差值。
select matchno,difference
from(select matchno,abs(won-lost) as difference
from matches) as m
where difference>2;
26.创建一个名为towns的虚拟表。
select *
from (select 'Stratford' as town,4 as number
union
select 'Plymouth',6
union
select 'Inglewood',1
union
select 'Douglas',2) as towns
order by town;
27.对于每一个球员，获得球员编号，姓名，城市以及他居住城市的居民数目。
select playerno,name,players.town,number
from players LEFT JOIN
(select 'Stratford' as town,4 as number
union
select 'Plymouth',6
union
select 'Inglewood',1
union
select 'Douglas',2) as towns
on players.TOWN = towns.town
order by playerno;
28.获取所居住的城市人口指数大于2的球员号码。
select playerno
from players left outer join
(select 'Stratford' as town,4 as number
union
select 'Plymouth',6
union
select 'Inglewood',1
union
select 'Douglas',2) as towns
on players.town=towns.town
where towns.number>2;
29.获取名字为John，Mark和Arnold以及姓为Berg、Johnson和William的所有可能的组合。
select *
from(select 'John' as first_name
union
select 'Mark'
union
select 'Arnold') as first_names,
(select 'Berg' as last_name
union
select 'Johnson'
union
select 'William') as last_names;
30.对于数字10-19，一次获取3次方的值，如果值大于4000，就不要包含在结果中。
select number,power(number,3)
from (select 10 as number
union
select 11
union
select 12
union
select 13
union
select 14
union
select 15
union
select 16
union
select 17
union
select 18
union
select 19) as numbers
where power(number,3)<=4000;
31.生成0-999之间的数字，包含999.
select number
from (select cast(concat(digit1.digit,concat(digit2.digit,digit3.digit)) as unsigned integer) as number
from (select '0' as digit union select '1' union select '2' union select '3' union select '4' union select '5' union select '6' union select '7' union select '8' union select '9') as digit1,
(select '0' as digit union select '1' union select '2' union select '3' union select '4' union select '5' union select '6' union select '7' union select '8' union select '9') as digit2,
(select '0' as digit union select '1' union select '2' union select '3' union select '4' union select '5' union select '6' union select '7' union select '8' union select '9') as digit3)
as numbers
order by number;
32.找出0-999之间是整数的平方的数。
select number as square,round(sqrt(number)) as basis
from (select cast(concat(digit1.digit,concat(digit2.digit,digit3.digit)) as unsigned integer) as number
from (select '0' as digit union select '1' union select '2' union select '3' union select '4' union select '5' union select '6' union select '7' union select '8' union select '9') as digit1,
(select '0' as digit union select '1' union select '2' union select '3' union select '4' union select '5' union select '6' union select '7' union select '8' union select '9') as digit2,
(select '0' as digit union select '1' union select '2' union select '3' union select '4' union select '5' union select '6' union select '7' union select '8' union select '9') as digit3)
as numbers
where sqrt(number)=round(sqrt(number))
order by number;
微信扫一扫，打赏作者吧～
