系列文章目錄
【SQL開發(fā)實戰(zhàn)技巧】系列（一）:關(guān)于SQL不得不說的那些事
【SQL開發(fā)實戰(zhàn)技巧】系列（二）：簡單單表查詢
【SQL開發(fā)實戰(zhàn)技巧】系列（三）：SQL排序的那些事
【SQL開發(fā)實戰(zhàn)技巧】系列（四）：從執(zhí)行計劃討論UNION ALL與空字符串&UNION與OR的使用注意事項
【SQL開發(fā)實戰(zhàn)技巧】系列（五）：從執(zhí)行計劃看IN、EXISTS 和 INNER JOIN效率，我們要分場景不要死記網(wǎng)上結(jié)論
【SQL開發(fā)實戰(zhàn)技巧】系列（六）：從執(zhí)行計劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，記住內(nèi)外關(guān)聯(lián)條件不要亂放
【SQL開發(fā)實戰(zhàn)技巧】系列（七）：從有重復(fù)數(shù)據(jù)前提下如何比較出兩個表中的差異數(shù)據(jù)及對應(yīng)條數(shù)聊起
【SQL開發(fā)實戰(zhàn)技巧】系列（八）：聊聊如何插入數(shù)據(jù)時比約束更靈活的限制數(shù)據(jù)插入以及怎么一個insert語句同時插入多張表
【SQL開發(fā)實戰(zhàn)技巧】系列（九）：一個update誤把其他列數(shù)據(jù)更新成空了？Merge改寫update！給你五種刪除重復(fù)數(shù)據(jù)的寫法！
【SQL開發(fā)實戰(zhàn)技巧】系列（十）：從拆分字符串、替換字符串以及統(tǒng)計字符串出現(xiàn)次數(shù)說起
【SQL開發(fā)實戰(zhàn)技巧】系列（十一）：拿幾個案例講講translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函數(shù)
【SQL開發(fā)實戰(zhàn)技巧】系列（十二）：三問(如何對字符串字母去重后按字母順序排列字符串？如何識別哪些字符串中包含數(shù)字？如何將分隔數(shù)據(jù)轉(zhuǎn)換為多值IN列表?)
【SQL開發(fā)實戰(zhàn)技巧】系列（十三）：討論一下常用聚集函數(shù)&通過執(zhí)行計劃看sum()over()對員工工資進行累加
【SQL開發(fā)實戰(zhàn)技巧】系列（十四）：計算消費后的余額&計算銀行流水累計和&計算各部門工資排名前三位的員工
【SQL開發(fā)實戰(zhàn)技巧】系列（十五）：查找最值所在行數(shù)據(jù)信息及快速計算總和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL開發(fā)實戰(zhàn)技巧】系列（十六）：數(shù)據(jù)倉庫中時間類型操作（初級）日、月、年、時、分、秒之差及時間間隔計算
【SQL開發(fā)實戰(zhàn)技巧】系列（十七）：數(shù)據(jù)倉庫中時間類型操作（初級）確定兩個日期之間的工作天數(shù)、計算—年中周內(nèi)各日期出現(xiàn)次數(shù)、確定當前記錄和下一條記錄之間相差的天數(shù)
【SQL開發(fā)實戰(zhàn)技巧】系列（十八）：數(shù)據(jù)倉庫中時間類型操作（進階）INTERVAL、EXTRACT以及如何確定一年是否為閏年及周的計算
【SQL開發(fā)實戰(zhàn)技巧】系列（十九）：數(shù)據(jù)倉庫中時間類型操作（進階）如何一個SQL打印當月或一年的日歷？如何確定某月內(nèi)第一個和最后—個周內(nèi)某天的日期？
【SQL開發(fā)實戰(zhàn)技巧】系列（二十）：數(shù)據(jù)倉庫中時間類型操作（進階）獲取季度開始結(jié)束時間以及如何統(tǒng)計非連續(xù)性時間的數(shù)據(jù)
【SQL開發(fā)實戰(zhàn)技巧】系列（二十一）：數(shù)據(jù)倉庫中時間類型操作（進階）識別重疊的日期范圍，按指定10分鐘時間間隔匯總數(shù)據(jù)
【SQL開發(fā)實戰(zhàn)技巧】系列（二十二）：數(shù)倉報表場景? 從分析函數(shù)效率一定快嗎聊一聊結(jié)果集分頁和隔行抽樣實現(xiàn)方式
【SQL開發(fā)實戰(zhàn)技巧】系列（二十三）：數(shù)倉報表場景? 如何對數(shù)據(jù)排列組合去重以及通過如何找到包含最大值和最小值的記錄這個問題再次用執(zhí)行計劃給你證明分析函數(shù)性能不一定高
【SQL開發(fā)實戰(zhàn)技巧】系列（二十四）：數(shù)倉報表場景?通過案例執(zhí)行計劃詳解”行轉(zhuǎn)列”,”列轉(zhuǎn)行”是如何實現(xiàn)的
【SQL開發(fā)實戰(zhàn)技巧】系列（二十五）：數(shù)倉報表場景?結(jié)果集中的重復(fù)數(shù)據(jù)只顯示一次以及計算部門薪資差異高效的寫法以及如何對數(shù)據(jù)進行快速分組
【SQL開發(fā)實戰(zhàn)技巧】系列（二十六）：數(shù)倉報表場景?聊聊ROLLUP、UNION ALL是如何分別做分組合計的以及如何識別哪些行是做匯總的結(jié)果行
前言
本篇文章講解的主要內(nèi)容是：插入數(shù)據(jù)、阻止對某幾列插入的實現(xiàn)、復(fù)制表的定義和數(shù)據(jù)以及注意事項、比創(chuàng)建約束功能更強大！用 WITH CHECK OPTION限制數(shù)據(jù)錄入、如何一個insert將數(shù)據(jù)同時插入多個表
【SQL開發(fā)實戰(zhàn)技巧】這一系列博主當作復(fù)習舊知識來進行寫作，畢竟SQL開發(fā)在數(shù)據(jù)分析場景非常重要且基礎(chǔ)，面試也會經(jīng)常問SQL開發(fā)和調(diào)優(yōu)經(jīng)驗，相信當我寫完這一系列文章，也能再有所收獲，未來面對SQL面試也能游刃有余~。
一、插入數(shù)據(jù)
先創(chuàng)建一個測試表，然后插入一條數(shù)據(jù)
create table test(
aa varchar2(20) default 'aa',
bb varchar2(20) default 'bb',
cc varchar2(20) default 'cc',
dd date default sysdate
);
insert into test(aa,bb,cc) values(default,null,'zyd');
SQL> select * from test;
AA BB CC DD
-------------------- -------------------- -------------------- -----------
aa zyd 2023-2-7 16
注意以下幾點：
- 如果INSERT語句中沒有含默認值的列，則會添加默認值，如dd列。
- 如果包含有默認值的列，需要用DEFAULT關(guān)鍵字，才會添加默認值，如aa列。
- 如果已顯示設(shè)定了NULL或其他值，則不會再生成默認值，如bb列。
- 建立表時，有時明明設(shè)定了默認值，可生成的數(shù)據(jù)還是NULL,原因在于我們在代碼中不知不覺地加入了NULL。
二、阻止對某幾列插入
這種需求我們可以創(chuàng)建一個視圖，視圖中暴露出來可以變更的列，然后DML操作均基于此視圖來做，看下面這個案例：
SQL> insert into vtest values('a','b',sysdate);
1 row inserted
SQL> select * from vtest;
AA BB DD
-------------------- -------------------- -----------
aa 2023-2-7 16
a b 2023-2-7 16
SQL> insert into vtest values('c',default,sysdate);
insert into vtest values('c',default,sysdate)
ORA-32575: 對于正在修改的視圖, 不支持顯式列默認設(shè)置
ok，看上面的操作我們可以通過視圖實現(xiàn)需求，但是視圖這種方式有個弊端就是通過VIEW新增數(shù)據(jù)，不能再使用關(guān)鍵字DEFAULT
三、復(fù)制表的定義和數(shù)據(jù)以及注意事項
我們可以用以下語句復(fù)制表 TEST:
create table test2 as select * from test;
也可以先復(fù)制表的定義，再新增數(shù)據(jù)：
create table test3 as select * from test where 1=2;
insert into test3 select * from test;
但是！看下面這個操作：
SQL> create table test4 as
2 select '1' as aa, null bb, '' cc, sysdate dd from test;
create table test4 as
select '1' as aa, null bb, '' cc, sysdate dd from test
ORA-01723: 不允許長度為 0 的列
當我們CTAS操作的時候，如果你的字段中包含null時候，會創(chuàng)建失敗，因為你沒有指定這個列的數(shù)據(jù)類型， oracle咋個推斷你的數(shù)據(jù)類型呢？
四、比創(chuàng)建約束功能更強大！用 WITH CHECK OPTION限制數(shù)據(jù)錄入
當約束條件比較簡單時，可以直接加在表中，如工資必須>0:
SQL> alter table emp add constraints ck_sal check(sal > 0);
Table altered
但有些復(fù)雜或特殊的約束條件是不能這樣放在表里的，如雇傭日期大于當前日期
SQL> alter table emp add constraints ck_hiredate check(hiredate>sysdate);
ORA-02436: 日期或系統(tǒng)變量在 CHECK 約束條件中指定錯誤
這時我們可以使用加了WITH CHECK OPTION關(guān)鍵字的VIEW來達到目的。下面的示例中，我們限制了不符合內(nèi)聯(lián)視圖條件的數(shù)據(jù)(SYSDATE+1)：
SQL> insert into (
2 select * from test where dd<=sysdate with check option
3 )
4 select 'a1','b1','c1',sysdate
5 from dual;
1 row inserted
SQL> select * from test;
AA BB CC DD
-------------------- -------------------- -------------------- -----------
aa zyd 2023-2-7 16
a b cc 2023-2-7 16
a1 b1 c1 2023-2-7 17
SQL> rollback;
Rollback complete
SQL> select * from test;
AA BB CC DD
-------------------- -------------------- -------------------- -----------
aa zyd 2023-2-7 16
a b cc 2023-2-7 16
SQL>
SQL> insert into (
2 select * from test where dd<=sysdate with check option
3 )
4 select 'a1','b1','c1',sysdate+1
5 from dual;
insert into (
select * from test where dd<=sysdate with check option
)
select 'a1','b1','c1',sysdate+1
from dual
ORA-01402: 視圖 WITH CHECK OPTION where 子句違規(guī)
因為里面有關(guān)鍵字WITH CHECK OPTION,所以INSERT的數(shù)據(jù)不符合其中的條件(hiredate<=SYSDATE)時，就不允許利用INSERT。
當規(guī)則較復(fù)雜，無法用約束實現(xiàn)時，這種限制方式就比較有用。
五、如何一個insert將數(shù)據(jù)同時插入多個表
多表插入語句分為以下四種：
- 無條件INSERT
- 有條件INSERT ALL
- 轉(zhuǎn)置INSERT
- 有條件INSERT FIRST
接下來先創(chuàng)建兩個臨時表
create table empa as select empno,ename,job from emp where 1=2;
create table empb as select empno,ename,deptno from emp where 1=2;
- 無條件INSERT
INSERT ALL
into empa(empno,ename,job)VALUES(empno,ename,job)
into empb(empno,ename,deptno)VALUES(empno,ename,deptno)
SELECT empno,ename,job,deptno FROM emp WHERE deptno in (10,20);
16 rows inserted
SQL> select * from empa;
EMPNO ENAME JOB
----- ---------- ---------
7369 SMITH CLERK
7566 JONES MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7876 ADAMS CLERK
7902 FORD ANALYST
7934 MILLER CLERK
8 rows selected
SQL> select * from empb;
EMPNO ENAME DEPTNO
----- ---------- ------
7369 SMITH 20
7566 JONES 20
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7876 ADAMS 20
7902 FORD 20
7934 MILLER 10
8 rows selected
SQL> rollback;
Rollback complete
因為沒有加條件，所以會同時向兩個表中插入數(shù)據(jù)，且兩個表中插入的條數(shù)一樣。
- 有條件 INSERT ALL
INSERT ALL
when job in('SALESMAN','MANAGER') then
into empa(empno,ename,job)VALUES(empno,ename,job)
when deptno in('20','30')then
into empb(empno,ename,deptno)VALUES(empno,ename,deptno)
SELECT empno,ename,job,deptno FROM emp WHERE deptno in (10,20);
7 rows inserted
SQL> select * from empa;
EMPNO ENAME JOB
----- ---------- ---------
7566 JONES MANAGER
7782 CLARK MANAGER
SQL> select * from empb;
EMPNO ENAME DEPTNO
----- ---------- ------
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20
SQL> rollback;
Rollback complete
當增加條件后,就會按條件插入。如EMPNO=7654等數(shù)據(jù)在兩個表中都有。
- INSERT FIRST就不一樣
INSERT FIRST
when job in('SALESMAN','MANAGER') then
into empa(empno,ename,job)VALUES(empno,ename,job)
when deptno in('20','30')then
into empb(empno,ename,deptno)VALUES(empno,ename,deptno)
SELECT empno,ename,job,deptno FROM emp WHERE deptno in (10,20);
6 rows inserted
SQL> select * from empa;
EMPNO ENAME JOB
----- ---------- ---------
7566 JONES MANAGER
7782 CLARK MANAGER
SQL> select * from empb;
EMPNO ENAME DEPTNO
----- ---------- ------
7369 SMITH 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20
SQL> rollback;
INSERT FIRST語句中,當?shù)谝粋€表符合條件后，第二個表將不再插入對應(yīng)的行，表empb中不再有與表empa相同的數(shù)據(jù)EMPN0=7654,這就是INSERT FIRST與INSERT ALL的不同之處。
- 轉(zhuǎn)置INSERT與其說是一個分類，不如算作"INSERT ALL"的一個用法。
創(chuàng)建一個臨時表
create table deptb(
dname varchar2(200),
remark varchar2(200)
);
insert all
into deptb values(aa,'aa')
into deptb values(bb,'bb')
into deptb values(cc,'cc')
select * from (select 'zhaoyd' aa,'zhaoyandong' bb ,'趙延東'cc from dual
);
SQL> select * from deptb;
DNAME REMARK
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
zhaoyd aa
zhaoyandong bb
趙延東 cc
可以看到，轉(zhuǎn)置INSERT的實質(zhì)就是把不同列的數(shù)據(jù)插入到同一表的不同行中。文章來源：http://www.zghlxwxcb.cn/news/detail-791612.html
總結(jié)
本章主要是介紹關(guān)于insert的一系列常用操作。文章來源地址http://www.zghlxwxcb.cn/news/detail-791612.html
到了這里，關(guān)于【SQL開發(fā)實戰(zhàn)技巧】系列（八）：聊聊如何插入數(shù)據(jù)時比約束更靈活的限制數(shù)據(jù)插入以及怎么一個insert語句同時插入多張表的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
