mysql中各种复杂的增删改查

1.mysql查出数据表中连续出现三次或三次以上的数据

建一张表如下：表名为 number

1.1 要求找出num列连续出现三次或三次以上的数据：

select * from number where id in (
    select distinct n1.id from number n1,number n2,number n3
    where (n1.num = n2.num and n2.num = n3.num and ( 
         (n1.id + 1= n2.id  and n2.id +1 = n3.id)or
         (n3.id + 1= n2.id  and n2.id +1 = n1.id)or
         (n3.id + 1= n1.id  and n1.id +1 = n2.id)
      )
    ) 
order by n1.id )

运行结果：

1.2找出上表（运行结果表）中重复数据中id最小的数据。

思想：将上表中的数据分组取最小。

select min(id) as minid,id,num
from ((select * from number where id in (
    select distinct n1.id from number n1,number n2,number n3
    where (n1.num = n2.num and n2.num = n3.num and ( 
         (n1.id + 1= n2.id  and n2.id +1 = n3.id)or
         (n3.id + 1= n2.id  and n2.id +1 = n1.id)or
         (n3.id + 1= n1.id  and n1.id +1 = n2.id)
      )
    ) 
order by n1.id ))as t) group by num ;

运行结果：

2.删除表中的重复数据，保留id最小的数据

原表中的数据如下：

/*
    方法一：
        1.创建临时表
        2.将需要的数据保存到临时表
        3.清空原表，将临时表中的数据保存到原表中
        4.删除临时表
*/
    create temporary table temp

    select min(id),email from test group  by email;

    truncate table test;

    insert into test select * from temp;

    select * from test;

    drop table temp;

/*
  方法二：
        1.按照邮件分组，把每一组中的最小的id取出放在临时表中
        
        2.删除原表中除临时表中id以外的其他id 对应的数据
*/

    create temporary table temp

    select min(id) as minid from test group by email;  

    delete from test where id not in (select minid from temp);

    drop table temp;

/*
  方法三：
       在原表上操作
*/
   delete from test where id not in (
       select minid from (
          select min(id) minid from test group by email
         )b
     );
     select * from test;

运行结果：

3.mysql中的各种连接

创建两张表，代表两个店铺，每个店铺有不同的，每个柜台卖不同的商品。

/*
   两个商店，每个店有不同的柜台，每个柜台上卖有不同的商品
*/
create table tableA(
    id int(10) not null primary key auto_increment,
        monorail varchar(20),
        variety varchar(50)
);
insert into tableA 
values (1,'a1','苹果'),(2,'a1','梨'),(3,'a3','香蕉'),(4,'a4','苹果'),(5,'a5','西瓜'),
(6,'a6','苹果'),(7,'a7','葡萄'),(8,'a8','桃子');
create table tableB(
    id int(10) not null primary key auto_increment,
        monorail varchar(20),
        variety varchar(50)
);
insert into tableB 
values (1,'b1','虾'),(2,'b2','猪肉'),(3,'b3','苹果'),(4,'b4','西瓜'),(5,'b5','梨'),
(6,'b6','香蕉');

两个表数据如下：

’

3.1.内连接

/*
  内连接，查处两个商店共有的商品
*/
select * from tablea A INNER  JOIN tableb B
on A.variety = B.variety ;

 运行结果

3.2.左连接

/*
  左连接，以A表为左表
*/
select * from tablea A LEFT  JOIN tableb B
on A.variety = B.variety ;

运行结果

3.3.右连接

/*
  右连接，以A表为左表
*/
select * from tablea A RIGHT  JOIN tableb B
on A.variety = B.variety ;

运行结果

 

3.4.左外连接

/*
  左外连接，以A表为左表
*/
select * from tablea A LEFT  JOIN tableb B
on A.variety = B.variety Where B.variety is null;

运行结果

3.5.右外连接

/*
  右外连接，以A表为左表
*/
select * from tablea A RIGHT  JOIN tableb B
on A.variety = B.variety Where A.variety is null;

运行结果

3.6.全连接  

mysql不支持full  (outer) join 故使用union代替

/*
  全连接
*/
select * from tablea A LEFT  JOIN tableb B
on A.variety = B.variety ;
union
select * from tablea A RIGHT  JOIN tableb B
on A.variety = B.variety ;

运行结果

3.7.全外连接

select * from tablea A LEFT  JOIN tableb B
on A.variety = B.variety Where B.variety is  null 
union
select * from tablea A RIGHT  JOIN tableb B
on A.variety = B.variety Where A.variety is  null

运行结果