create table books(
id int notnull auto_increment,
namevarchar(50),
typeint,
primarykey(id)
)
当name不为admin时候才插入
name字段设置为唯一
alter table books add unique key(name);
当unique 字段name 已存在admin 的时候不插入
insert ignore into books(name,type)values ('admin','1')
取消唯一字段name
alter table books drop index name;
当数据不存在的时候插入,存在的时候进行更新(name 必须设为unigue)
insert into books(name,type)values('admin','2') on duplicate keyupdate name='admin',type=2
查找表中多余的重复记录,重复记录是根据单个字段(name)来判断
select * from books
where name in (select name from books group by name havingcount(name) > 1)
删除表中多余的重复记录(多个字段),只留有id最小的记录
delete from books where id not in(select id from (select min(id)id from books where name=name and type=type group by name,type) e);
)
当name不为admin时候才插入
insert into books(name,type) select 'admin','1'from
dual where
not exists(
select name from books wherename='admin')
name字段设置为唯一
alter table books add unique key(name);
当unique 字段name 已存在admin 的时候不插入
insert ignore into books(name,type)values ('admin','1')
取消唯一字段name
alter table books drop index name;
当数据不存在的时候插入,存在的时候进行更新(name 必须设为unigue)
insert into books(name,type)values('admin','2') on duplicate keyupdate name='admin',type=2
查找表中多余的重复记录,重复记录是根据单个字段(name)来判断
select * from books
where name in (select name from books group by name havingcount(name) > 1)
删除表中多余的重复记录(多个字段),只留有id最小的记录
delete from books where id not in(select id from (select min(id)id from books where name=name and type=type group by name,type) e);