SQL 更新实例

插入

• 插入默认值

-- 数据准备
create table table4(
  id int4,
  name text default 'no one'
);
-- 使用 default
insert into table4 values (1001, default);
insert into table4(id) values (1002);

• 多表插入

-- 数据准备
create table raw_data(
  id int,
  name varchar2(32),
  dept varchar2(32),
  salary double precision
);
insert into raw_data values (1001, 'aladdin', 'bigdata', 13000);
insert into raw_data values (1002, 'bilib', 'java', 10000);
-- 创建相似表结构，无法使用 like
create table med_emp_info as select * from raw_data where 1 = 0;
create table hig_emp_info as select * from raw_data where 1 = 0;

-- Oracle 支持，MySQL 和 Postgres 不支持
-- 多表插入
insert all
  when
    salary <= 10000
  then
    into med_emp_info(id, name, dept, salary)
    values (id, name, dept, salary)
  when
    salary > 10000 and salary <= 30000
  then
    into hig_emp_info(id, name, dept, salary)
    values (id, name, dept, salary)
select id, name, dept, salary
from raw_data;

复制数据

• 复制数据到另一个表

insert into table6 select * from table5;

• 复制表结构

-- MySQL/Postgres/Oracle
create table table6 as select * from table5 where 1 = 0;
-- DB2
create table table6 like table5;

更新

• 更新记录

update table5 set name = 'chrome' where id = 1002;
update table5 set name = 'ccc' where emp in (select emp from emp_bonus);

• 使用另一表的数据更新

-- Oracle
update
  table1 t1
set 
    (id, salary) = (select id, salary from table2 t2 where t1.id = t2.id)
where exists (select null from table2 t2 where t1.id = t2.id);

-- Postgres
update
  table1 t1
set
    salary = t2.salary
from
     table2 t2
where
      t1.id = t2.id;

删除

• 删除记录

-- 删除全部数据
delete from table2;
-- 删除指定数据
delete from table1 where id = 1001;

• 删除违反参照物的数据

-- 使用 exists
delete from
            table1 t1
where not exists(select * from table2 t2 where t1.id = t2.id);
-- 使用 not in
delete from table1 where id not in (select id from table2);

• 删除重复数据

-- 数据准备
create table table3(
  id int,
  name varchar2(32)
);
insert into table3 (id, name) values (1001, 'a');
insert into table3 (id, name) values (1002, 'b');
insert into table3 (id, name) values (1003, 'c');
insert into table3 (id, name) values (1004, 'c');
insert into table3 (id, name) values (1005, 'c');
-- 删除数据
delete from table3 where id not in (select min(id) from table3 group by name);