同乐学堂第十一章 组合表达式
1.获取那些来自Inglewood和来自Plymouth的每个球员的号码和城市。
select playerno,town from players
where town = 'Inglewood'
union
select playerno,town from players
where town = 'Plymouth';
2.获取出现在players表和penalties表中的所有日期的列表。
select birth_date as date from players
union
select payment_date as date from penalties;
3.获取那些至少引起一次罚款或者担任队长，或者两个条件都符合的每个球员号码。
select playerno from penalties
union
select playerno from teams;
4.对于那些至少引起一次罚款，担任队长，居住在Stratford或者满足上述两个或3个条件的每个球员，获取球员号码。
select playerno from penalties
union
select playerno from teams
union
select playerno from players where town = 'Stratford';
5.对于球队编号和球员号码的每个组合，获取所有赢得局数和输掉局数的和，并且对每个球队
给出一个子和和最终的总和。
select cast(teamno as char(4)) as TEAMNO,
cast(playerno as char(4)) as PLAYERNO,
sum(won+lost) as total
from matches
group by teamno,playerno
union
select cast(teamno as char(4)),'subtotal',sum(won+lost)
from matches
group by teamno
union
select 'total','total',sum(won+lost)
from matches
order by 1,2;
6.把penalties表中的球员号码的集合和来自teams表中的球员号码组合起来，不要移除重复的行。
select playerno from penalties
union all
select playerno from teams;
第十二章 用户变量和SET语句
1.设定用户变量PI，并用值3.141592654来初始化它。
set @PI=141592654;
2.获取用户变量PI的值。
select @PI as PI;
3.给出号码比刚刚创建用户变量PI的值小的所有球员的姓氏，居住地和邮编。
select name,town,postcode
from players
where playerno<@PI;
注意：@与PI之间不能有空格。
4.定义三个新的用户变量。
set @abc = 5, @def = 'Inglewood', @ghi = date('2001-09-08');
5.用一个公式来定义用户变量PI。
set @PI=cast(22 as BINARY)/7.0;
6.设定一个球员用户变量，它的值是一号球队队长的号码。
set @ANR = (select playerno from teams where teamno = 1);
7.创建用户变量playerno并且用5来初始化它。
select @playerno := 7;
8.定义用户变量name，town和postcode，并且赋值。
select @name:='Johnson',@town:='Stratford',@postcode:='529020';
9.定义变量name，town和postcode并用2号球员的值赋给他。
select @name:=name,@town:=town,@postcode:=postcode
from players
where playerno = 2;
10.定义用户变量penaltiestotal和numberpenalties，并把2号球员的值赋给他们。
select @penaltiestotal:=sum(amount),@numberpenalties:=count(*)
from penalties;
11.定义用户变量playerno并把players中最大号码的球员号赋给它。
select @playerno:=playerno
from players
order by playerno desc;
select @playerno;
12.获取1号球队的队长名字。
set @CNO = (select playerno from teams where teamno = 1);
select name from players where playerno = @CNO;
13.给出支付号码小于表达式(((3/7)*100)/124)+3的结果而球员的号码大于同一个表达式的结果罚款的所有数据。
set @var=(((3/7)*100)/124)+3;
select * from penalties
where paymentno<@var and playerno>@var;
14.创建两个可以在未来会话中使用的用户变量。
create table varibles(
varname char(30) not null primary key,
varvalue char(30) not null);
set @var1 = 100, @var2 = 'John';
insert into varibles values ('var1',@var1);
insert into varibles values ('var2',@var2);
退出会话后重新登录
select @var1 :=varvalue
from varibles
where varname='var1';
select @var2 :=varvalue
from varibles
where varname='var2';
select @var1, @var2;
15.为当前日期增加两年。
do current_date + interval 2 year;
第十四章 更新表
1.网球俱乐部里有了一支新的球队，第三个球队队长是第100号球员，该球队在third级中进行比赛。
insert into teams values(3,100,'third');
2.添加一个新的球员。
insert into players values(611,'Jones','GG',null,'M',1977,'Green Way',null,null,'Stratford',null,null);
如果只添加指定的列，则需要在表后面指明要添加的列，如：
insert into players(playerno,name,initials,sex,joined,street,town)
values (611,'Jones','GG' ,'M',1977,'Green Way' ,'Stratford');
未指明的列默认为null。
3.添加四个新的球队。
insert into teams values
(6,100,'third'),
(7,27,'fourth'),
(8,39,'fourth'),
(9,112,'sixth');
4.创建一个新表，其中存储了球员号码和所有罚款的总额。
create table totals(
NumberPlayers integer not null,
SumPenalties decimal(9,2) not null);
insert into totals values(
(select count(*) from players),
(select sum(amount) from penalties));
5.向teams表中再次添加1号球队。
insert ignore into teams values (1,39,'second');
6.向teams表中添加一支球队，如果一号球队已经存在，输入39号球员作为其队长以及second作为其分级。
insert into teams values(1,39,'second')
on duplicate key update playerno = 39,division = 'second';
7.创建一个单独的表，其中存储了记录中的非参赛球员号码、名字、城市和电话号码。
create table recr_players(
playerno smallint not null,
name char(15) not null,
town char(20) not null,
phoneno char(13),
primary key (playerno));
8.复制recr_players表中行的数目。
insert into recr_players(playerno,name,town,phoneno)
select playerno+1000,name,town,phoneno from recr_players;
9.把那些罚款额大于平均额的所有罚款添加到penalties表中。
insert into penalties
select paymentno+100,playerno,payment_date,amount
from penalties
where amount>(select avg(amount) from penalties);
10.把95号球员的联盟会员改为2000。
update players
set leagueno = 2000
where playerno = 95;
11.把所有的罚款额减少5%。
update penalties
set amount=amount*0.95 ;
12.把所有居住在Stratford的参赛球员获胜的局数设置为0.
update matches
set won=0
where playerno in
(select playerno from players where town = 'Stratford');
13.Parmenter家已经搬到了Inglewood的83号Palmer Street，邮编变成了1234UU，电话号码为Unknown。
update players
set street='Palmer Srteet',houseno='83',town='Inglewood',postcode='1234UU',phoneno=NULL
where name='Parmenter';
14.创建一个新表来存储每个球员号码，他所参加的比赛以及他所引起的所有罚款。
create table players_data(
playerno integer not null primary key,
number_mat integer,
sum_penalties decimal(7,2));
insert into players_data(playerno)
select playerno from players;
update players_data
set number_mat=(
select count(*) from matches as m where m.playerno=players_data.playerno),
sum_penalties=(
select sum(amount) from penalties as pen where pen.playerno=players_data.playerno);
15.从每个罚款额中减去平均额。
set @average_amount = (select avg(amount) from penalties);
update penalties
set amount=amount-@average_amount;
16.把所有罚款额降低5%并且最高罚款额排在最前面。
update penalties
set amount=amount*0.95
order by amount desc;
17.把所有支付编号增加1.
update penalties
set paymentno=paymentno+1
order by paymentno desc;
18.把4个最高罚款额增加5%。
update penalties
set amount=amount*1.05
order by amount desc,playerno asc
limit 4;
19.对于4号比赛，把编号增加1并使得赢得的局数为2而输掉局数为3.
update ignore matches
set matchno=matchno+1 ,
won=2,
lost = 3
where matchno=4;
20.把所有为first分级的球队所参加的所有比赛的赢得的局数设置为0.
update matches as m,teams as t
set won = 0
where t.teamno=m.teamno and t.division = 'first';
21.把一个处于first分级的球队所参加的所有比赛获胜局数设置为0，并且把那些first分级球队队长的号码设置为112号。
update matches as m,teams as t
set m.won=0,
t.playerno=112
where t.teamno=m.teamno and t.division='first';
22.如果2号球员出现在示例数据库的所有5个列表中，号码必须在5个表中都改为1.
update players as p,
teams as t,
matches as m,
penalties as pen,
committee_members as c
set p.playerno = 1,
t.playerno=1,
m.playerno=1,
pen.playerno=1,
c.playerno=1
where p.playerno=t.playerno and t.playerno=m.playerno and m.playerno=pen.playerno and pen.playerno=c.playerno
and c.playerno=2;
23.添加一个新球员，如果主键已经存在，旧的值必须被覆盖。
replace into players(playerno,name,initials,sex,joined,street,town)
values(611,'Johns','GG','M',1977,'Green Way','Stratford');
24.添加4个新的球队，如果主键已经存在，旧的值必须被覆盖。
replace into teams(teamno,playerno,division)
values(6,100,'third'),
(7,27,'fourth'),
(8,39,'fourth'),
(9,112,'sixth');
25.删除44号球员所引发的所有罚款。
delete
from penalties where playerno=44;
27.把那些加入俱乐部的年份比来自Stratford的球员加入俱乐部的平均年份要晚的所有球员删除。
delete
from players
where joined> (SELECT joined FROM (select avg(joined) from players AS p where town='Stratford') AS p )
28.删除4个最高的罚款额。
delete from penalties
order by amount desc, playerno asc
limit 4;
29.删除所有球员并且如果在处理过程中出现错误的话不返回出错信息。
delete ignore from players;
30.删除所有居住在Inglewood的球员比赛。
delete matches from matches,players
where matches.playerno=players.playerno and players.town='Inglewood';
31.从teams表和matches表中删除所有有关1号球对的数据。
delete teams,matches
from teams,matches where teams.teamno=matches.teamno and teams.teamno=1;
32、之前有公司同事问，如何合并一个表中相同ID对应的某列进行合并成一行，并只保留ID值最小的一个！在Access中因不支持group_concat函数，只能写脚本来实现。
用一些编程语言，或者BI工具根据逻辑很容易办到!。（合并、更新、去重）
但是纯用SQL，不借助脚本，也能轻松完成！
GROUP_CONCA里面的字段必须为字符串varchar类型！
下面以员工表为例进行演示：
UPDATE
employee
INNER JOIN (SELECT name, GROUP_CONCAT(salary) AS label FROM employee GROUP BY name) as ele
ON ele .name = employee .name
SET employee.salary = ele .label;
delete from employee where id not in (select minid from (select min(id) as minid from employee group by name) b);
补充
数据库中存在重复记录，删除保留其中一条（是否重复判断基准为多个字段）
DELETE consum_record
ROM
consum_record,
(
SELECT
min(id) id,
user_id,
monetary,
consume_time
FROM
consum_record
GROUP BY
user_id,
monetary,
consume_time
HAVING
count(*) > 1
) t2
WHERE
consum_record.user_id = t2.user_id
and consum_record.monetary = t2.monetary
and consum_record.consume_time = t2.consume_time
AND consum_record.id > t2.id;
consum_record,
(
SELECT
min(id) id,
user_id,
monetary,
consume_time
FROM
consum_record
GROUP BY
user_id,
monetary,
consume_time
HAVING
count(*) > 1
) t2
WHERE
consum_record.user_id = t2.user_id
and consum_record.monetary = t2.monetary
and consum_record.consume_time = t2.consume_time
AND consum_record.id > t2.id;
微信扫一扫，打赏作者吧～
