快乐学习
前程无忧、中华英才非你莫属!

Day45-MySQL之开发者-进阶4

第十一章 组合表达式
 
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;

 
打赏

未经允许不得转载:同乐学堂 » Day45-MySQL之开发者-进阶4

分享到:更多 ()

评论 1

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  1. #1

    增达网:逆向网赚,五五倍增,自动下滑,十级提成。

    终生只需99元,加入增达网VIP会员,享受以下特权:

    ①,让成千上万人每天浏览你的广告,强制浏览时间。

    ②,免费下载群发软件,无使用限制,全部是破解版。

    ③,让你什么不干躺着就赚钱,上线替下线发展会员。

    注册网址:

    http://www.lin152.zengd.com/

    增达网

    增达网8个月前 (09-04)回复

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!