database 关联操作

1.SQL join
a:从一个和多个表中，根据指定的字段的关联关系，查询出数据
b:join 分为
   inner join: 返回在两个表中至少一个匹配的行
  left join:返回左表中的所有行，即使右表没有匹配的记录
  right join:返回右表中的所欲行，即使左表没有匹配的记录
  full join: 返回那些在其中一个表中匹配的行

  C：例子




2.行转列的例子

2.1 成绩表 将其 通过行转列转化如下结果
create table score(username varchar(100),
                    subject varchar(100),
                    score float);
初始化数据：
insert into score(username,subject,score)values('aa','chinese',100.0);
insert into score(username,subject,score)values('aa','math',90.0);
insert into score(username,subject,score)values('aa','eng',10.0);
insert into score(username,subject,score)values('ab','chinese',30);
insert into score(username,subject,score)values('ab','math',12);

要求输出

username chinese  math, eng

        aa        100.0   90.0 10.0

        ab        30      12    0

这样的结果。

mysql> select username,
      (case subject when 'chinese' then score else 0 end) as 'Chinese' , (case subject when 'math' then score else 0 end) as 'Math',
  (case subject when 'eng' then score else 0 end) as 'Eng'
  from score;
+----------+---------+------+------+
| username | Chinese | Math | Eng  |
+----------+---------+------+------+
| aa       |     100 |    0 |    0 |
| aa       |       0 |   90 |    0 |
| aa       |       0 |    0 |   10 |
| ab       |      30 |    0 |    0 |
| ab       |       0 |   12 |    0 |
+----------+---------+------+------+

select a.username,
       ifnull(sum(a.Chinese),0) as 'Chinese',
       ifnull(sum(a.Math),0) as 'Math',
       ifnull(sum(a.Eng),0) as 'Eng'

from(
select username,
    (case subject when 'chinese' then score else 0 end) as 'Chinese' ,
     (case subject when 'math' then score else 0 end) as 'Math',
    (case subject when 'eng' then score else 0 end) as 'Eng'
   from score 
) a
group by a.username

或者修改为：

select username,
   max(case subject when 'chinese' then score else 0 end) as 'Chinese' , max(case subject when 'math' then score else 0 end) as 'Math',
    max(case subject when 'eng' then score else 0 end) as 'Eng'
   from score group by username;

3.列转行的例子 通过union all
select ProgrectName,'Oversea'as supply,OverseaSupply as total from  ProgrectDetail
union all
select ProgrectName,'Native' as supply ,NativeSupply as total from  ProgrectDetail
union all
select ProgrectName,'South' as supply ,SouthSupply as total from  ProgrectDetail
union all
select ProgrectName,'North'as supply,NorthSupply as total from  ProgrectDetail;



4.删除重复行的例子
  a:先创造一个空的备份表
   create table ProgrectDetail_bak where 1=0;
  b:将源表中的数据group by 出来 insert 新表中

 insert into ProgrectDetail_bak
select a.ProgrectName ,a.OverseaSupply,a.NativeSupply,a.SouthSupply,a.NorthSupply
from(
select   ProgrectName ,OverseaSupply,NativeSupply,SouthSupply,NorthSupply,count(*) as cn
from ProgrectDetail
group by ProgrectName ,OverseaSupply,NativeSupply,SouthSupply,NorthSupply
)a

  c: 删除源表
  drop table ProgrectDetail
  d:将备份表重命名为源表
  alter table ProgrectDetail_bak rename to ProgrectDetail;
a: 首先为原来的表添加伪列


5.构造伪列
SELECT @rownum:=@rownum+1 AS rownum, ProgrectDetail.*
   FROM (SELECT @rownum:=0) r, ProgrectDetail