插入

• 插入默認(rèn)值

-- 數(shù)據(jù)準(zhǔn)備
create table table4(
  id int4,
  name text default 'no one'
);
-- 使用 default
insert into table4 values (1001, default);
insert into table4(id) values (1002);

• 多表插入

-- 數(shù)據(jù)準(zhǔn)備
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);
-- 創(chuàng)建相似表結(jié)構(gòu)，無法使用 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;

復(fù)制數(shù)據(jù)

• 復(fù)制數(shù)據(jù)到另一個表

insert into table6 select * from table5;

• 復(fù)制表結(jié)構(gòu)

-- 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);

• 使用另一表的數(shù)據(jù)更新

-- 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;

刪除

• 刪除記錄

-- 刪除全部數(shù)據(jù)
delete from table2;
-- 刪除指定數(shù)據(jù)
delete from table1 where id = 1001;

• 刪除違反參照物的數(shù)據(jù)

-- 使用 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);

• 刪除重復(fù)數(shù)據(jù)

-- 數(shù)據(jù)準(zhǔn)備
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');
-- 刪除數(shù)據(jù)
delete from table3 where id not in (select min(id) from table3 group by name);