oracle修改多个字段的值，oracle去重

电玩女神 2021-11-16 09:06 548阅读 0赞

一：创建简单的学生表：

create table STUDENT
    (
      sid       NUMBER,
      sname     VARCHAR2(40),
      sage      NUMBER,
      sbirthday DATE,
      saddress  VARCHAR2(200)
    );
    comment on column student.sid  is '学号（主键）';
    comment on column student.sname is '学生姓名';
    comment on column student.sage is '学生年龄';
    comment on column student.sbirthday is '学生出生年月日';
    comment on column student.saddress is '学生地址';

增加数据：

insert into student (sid, sname, sage, sbirthday, saddress)
    values (1, '张三', 10, to_date('1994-01-01', 'yyyy-mm-dd'), '上海市浦东新区');
    insert into student (sid, sname, sage, sbirthday, saddress)
    values (2, '张三', 10, to_date('1994-01-01', 'yyyy-mm-dd'), '上海市浦东新区');
    insert into student (sid, sname, sage, sbirthday, saddress)
    values (3, '张三', 10, to_date('1994-01-01', 'yyyy-mm-dd'), '上海市浦东新区');
    insert into student (sid, sname, sage, sbirthday, saddress)
    values (4, '张三', 10, to_date('1994-01-01', 'yyyy-mm-dd'), '上海市浦东新区');
    insert into student (sid, sname, sage, sbirthday, saddress)
    values (5, '张三', 10, to_date('1994-01-01', 'yyyy-mm-dd'), '上海市浦东新区');
    insert into student (sid, sname, sage, sbirthday, saddress)
    values (6, '张三', 10, to_date('1994-01-01', 'yyyy-mm-dd'), '上海市浦东新区');
    insert into student (sid, sname, sage, sbirthday, saddress)
    values (7, '张三', 10, to_date('1994-01-01', 'yyyy-mm-dd'), '上海市浦东新区');

删除数据：

delete student where sid=7;

修改数据：

update student set sname='赵四' where sid=1;

修改多个字段的值：

update student
      set sname = '李四',
          sage = 20,
          sbirthday = to_date('2010-01-01', 'yyyy-mm-dd'),
          saddress = '广州市越秀区'
    where sid = 2;

如果字段非常多，这样写就稍微麻烦点，因为待修改字段和待修改的数据没有分离。

还有另外一种写法（字段多的时候写的时候方便，书写效率高些；字段少的时候感觉不出来）：

update student
      set (sname, sage, sbirthday, saddress) =
      (select '王五', 21, to_date('2010-01-01', 'yyyy-mm-dd'), '北京海淀区' from dual)
    where sid = 3;

修改字段的值为空：

update student
      set (sname, sage, sbirthday, saddress) =
      (select '', '', to_date('', ''), '' from dual)
    where sid = 4;

修改字段的值为空方法二：

update student
      set (sname, sage, sbirthday, saddress) =
      (select '', '', to_date('', ''), '' from dual)
    where sid = 5;

修改多个字段时间为空：

方法一：
    update student
      set sname = '',
          sage = '',
          sbirthday = to_date('', ''),
          saddress = '',
          createdate = to_date('', '')
    where sid = 7;
    方法二 ：
    update student
      set (sname, sage, sbirthday, saddress,createdate) =
      (select '', '', to_date('', ''), '',to_date('', '') from dual)
    where sid = 7;

oracle去重查询:

查询表中多余的重复记录（多个字段） 
    select * from student a
     where (a.saddress, a.sname) in 
     (select saddress, sname from student group by saddress, sname having count(*) > 1)

select distinct sname from student;--去掉张三重复的名称查询
    select distinct t1.sname,t1.sage,t1.saddress from  student t1;--去重查询

去重留下一条需要的：
    --语法格式
    delete from student(表名) t
     where t.rowid not in
           (select max(rowid) from 表名(temp) group by temp.字段名1，字段名2);
    
    delete from student t
     where t.rowid not in 
     (select max(rowid)from student temp group by temp.sname, temp.saddress);

去重：

delete from student t
     where t.rowid not in 
     (select max(rowid)from student temp group by temp.sname, temp.saddress);

添加一列：

Alter table student add createDate DATE;--添加创建时间的字段

删除一列：

alter table student drop column createdate;--删除创建时间