//mysql脚本
create table deletetest(
id bigint primary key auto_increment,
name varchar(12) not null,
age int
);
insert into deletetest(name,age) values ('admin',20);
insert into deletetest(name,age) values ('centre',19);
insert into deletetest(name,age) select name,age from deletetest;
--------------------------------------------------------------------------------------------------------------
select distinct name,age from deletetest;//select id from deletetest group by name,age having count(name)>1;
select id from deletetest where id not in (select id from deletetest group by name,age having count(name)>1);
--------------------------------------------------------------------------------------------------------------
//delete操作
delete from deletetest where id =6;
--在mysql中不能指定一个语句来更新数据库中的数据
delete deletetest from deletetest where id in (select id from deletetest group by name,age having count(name)>1);在
mysql中是错误的
--在mysql用自连接，能够解决
delete m1 from deletetest as m1,deletetest as m2 where m1.name=m2.name and m1.age=m2.age and m1.id>m2.id;
//oracle脚本
--建表
create table deletetest(
myid number(10) primary key,
myname varchar2(12) not null,
age integer check (age between 1 and 100)
);
--建序列
-- Create sequence
create sequence deletetest_se
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 50
order;
--建自动更新的触发器
create or replace trigger delete_handle_tri
before insert on deletetest
for each row WHEN (new.myid is null)
declare
-- local variables here
nexthandle number;
begin
select deletetest_se.nextval into :new.myid from dual;
--end ;
end delete_handle_tri;
--插入数据
insert into deletetest(myname,age) values('admin','19');
insert into deletetest(myname ,age) values('centre','20');
insert into deletetest(myname ,age) select myname,age from deletetest;
select distinct myname,age from deletetest;
select myname from (select distinct myname,age from deletetest group by myname,age having count(myname)>1);
//删除重复记录（只有自增长的字段不算入）,只保留一条
begin
for r in (select myname,age,max(rowid) rowid_temp from deletetest
group by myname,age having count(myname)>1) loop
delete from deletetest where myname=r.myname
and age=r.age and rowid <> r.rowid_temp ;
commit;
end loop;
end;
end;