插入默認(rèn)值

定義表的某些列的默認(rèn)值：

create table D (id integer default 0);

所有的數(shù)據(jù)庫(kù)都支持使用default關(guān)鍵字來顯式地為某一列指定默認(rèn)值：

insert into D values(default);

Oracle 8i數(shù)據(jù)庫(kù)及更早的版本不支持default關(guān)鍵字，因此沒辦法為某一列顯式地插入默認(rèn)值。

如果所有的列都預(yù)設(shè)了默認(rèn)值，MySQL允許制定一個(gè)空白的values列表為所有列創(chuàng)建預(yù)設(shè)的默認(rèn)值：

MySQL

insert into D values();

如果數(shù)據(jù)表中某些列沒有設(shè)定默認(rèn)值，而某些列設(shè)定了默認(rèn)值，那么在插入數(shù)據(jù)的時(shí)候之喲啊不把預(yù)設(shè)了默認(rèn)值的列寫入insert列表，就可以方便地為其插入默認(rèn)值。考慮如下表：

create table DD (id integer default 0, foo varchar(10));

在insert列表中只指定foo列：

insert into DD (foo) values ('Bar');

也可以使用null值覆蓋默認(rèn)值：

insert into DD (id, foo) values (null, 'Brighten');

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

insert into dept_east (deptno, dname, loc)

select deptno, dname, loc

from dept

where loc in ('NEW YORK', 'BOSTON');

注意在insert列表后沒有values關(guān)鍵字。

復(fù)制表定義

為DEPT表創(chuàng)建一個(gè)副本DEPT_2，但是只要表結(jié)構(gòu)，不復(fù)制數(shù)據(jù)：

create table dept_2

as

select * from dept

where 1=0;

多表插入

Oracle可以使insert all或者insert first語法

Oracle

insert all

when loc in ('NEW YORK', 'BOSTON') then

into dept_east (deptno, dname, loc) values (deptno, dname, loc)

when loc in ('CHICAGO') then

into dept_mid (deptno, dname, loc) values (deptno, dname, loc)

else

into dept_west (deptno, dname, loc) values (deptno, dname, loc)

select deptno, dname, loc

from dept;

insert all與insert first的區(qū)別就是：一旦WHEN-THEN-ELSE的結(jié)果為真，insert first會(huì)立即結(jié)束評(píng)估，insert all則會(huì)逐一評(píng)估所有的條件，而不論前面的測(cè)試結(jié)果是否為真，所以使用insert all可能把同一行數(shù)據(jù)插入到多個(gè)表中。

也就是說，當(dāng)使用insert first時(shí)，如果滿足某一個(gè)when或else條件，判斷過程就會(huì)立即返回，不會(huì)再繼續(xù)評(píng)估其他判斷條件是否成立，保證每次過程都只有一條記錄插入到一張表中。

禁止插入特定列

如果想要阻止用戶或者錯(cuò)誤的軟件應(yīng)用程序在某些列中插入數(shù)據(jù)，可以創(chuàng)建一個(gè)視圖，只暴露那些你希望暴露的列，然后強(qiáng)制所有的insert語句都被傳送到該視圖。向一個(gè)簡(jiǎn)單視圖插入數(shù)據(jù)，數(shù)據(jù)庫(kù)服務(wù)器會(huì)把它轉(zhuǎn)換為針對(duì)基礎(chǔ)表的插入操作。

例如，創(chuàng)建如下視圖：

create view new_emps as

select empno, ename, job

from emp;

執(zhí)行下列插入語句

insert into new_emps (empno, ename, job)

values (1, 'Jonathan', 'Editor');

會(huì)被翻譯成：

insert into emp (empno, ename, job)

values (1, 'Jonathan', 'Editor');

當(dāng)相關(guān)行存在時(shí)更新記錄

例如，如果一個(gè)員工出現(xiàn)在EMP_BONUS表中， 將他的工資(在EMP表中)上漲20%。

update emp

set sal = sal*1.2

where empno in (select empno from emp_bonus);

也可以使用exists關(guān)鍵字：

update emp

set sal = sal*1.2

where exists (select null

from emp_bonus

where emp.empno = emp_bonus.empno);

使用另一個(gè)表的數(shù)據(jù)更新記錄

MySQL & Oracle

update emp set (e.sal, e.comm) = (select ns.sal, ns.sal/2

from new_sal ns

where ns.deptno = e.deptno)

where exists (select null

from new_sal ns

where ns.deptno = e.deptno);

Oracle 更新內(nèi)嵌視圖

update (

select e.sal as emp_sal, e.comm as emp_comm,

ns.sal as ns_sal, ns.sal/2 as ns_comm

from emp e, new_sal ns

where e.deptno = ns.deptno

) set emp_sal = ns_sal, emp_comm = ns_comm;

合并記錄

如果想根據(jù)相關(guān)記錄是否已經(jīng)存在來插入、更新或刪除一個(gè)表的記錄，例如，如果記錄存在，則更新它，如果不存在，則插入一條新紀(jì)錄；如果更新之后的記錄不滿足某個(gè)條件，則刪除它。

考慮如下條件來修改emp_commission表：

1) 如果emp_commission表的員工數(shù)據(jù)在emp表里也存在相關(guān)記錄，則更新業(yè)務(wù)提成comm為1000；

2)對(duì)于所有可能會(huì)把comm列更新為1000的員工，如果他們的sal低于2000，則刪除相關(guān)記錄(他們不應(yīng)該存在于emp_commission表中；

3)否則，就要從emp表中取出相應(yīng)的empno，ename，deptno并插入到emp_commission表。

Oracle

merge into emp_commission ec

using (select * from emp) emp

on (ec.empno = emp.empno)

when matched then

update set ec.comm = 1000

delete where (sal < 2000)

when not matched then

insert (ec.empno, ec.ename, ec.deptno, ec.comm)

values (emp.empno, emp.ename, emp.deptno, emp.comm);

刪除違反參照完整性的記錄

想從表里刪除一些記錄，因?yàn)樵诹硪粋€(gè)表里不存在與這些記錄相匹配的數(shù)據(jù)。例如，一些員工所屬的部門其實(shí)并不存在，你希望刪除這些員工。

delete from emp

where not exists (

select * from dept

where dept.deptno = emp.deptno;

);

或者

delete from emp

where deptno not in (select deptno from dept);

刪除重復(fù)記錄

考慮如下表dupes數(shù)據(jù)：

對(duì)于每一組重復(fù)的名字，你希望保留任意一個(gè)ID,并刪除其余的。

delete from dupes

where id not in (

select min(id) from dupes

group by name);

《SQL經(jīng)典實(shí)例》第四章

與50位技術(shù)專家面對(duì)面20年技術(shù)見證，附贈(zèng)技術(shù)全景圖

總結(jié)

以上是生活随笔為你收集整理的mysql 更改 uf_SQL经典实例（四）插入、更新和删除的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。