Oracle數(shù)據(jù)庫(kù)學(xué)習(xí)三

5.DML

DML 語(yǔ)句 語(yǔ)句操作語(yǔ)言

INSERT

UPDATE

DELETE

MERGE

INSERT 方法:

按順序列插入

按指定列插入

default值插入

子查詢(代替values)結(jié)果插入

替代變量插入

DML 語(yǔ)句 語(yǔ)句操作語(yǔ)言

INSERT

UPDATE

DELETE

MERGE

INSERT 方法:

按順序列插入

按指定列插入

default值插入

子查詢(代替values)結(jié)果插入

替代變量插入

SQL> create table t1(id number,name varchar2(20) default 'dxw');

表已創(chuàng)建拆魏。

SQL> select *from t1;

未選定行

SQL> insert into t1(id) values(1);

已創(chuàng)建 1 行跟匆。

SQL> select *from t1;

ID NAME

---------- --------------------------------------------------

1 dxw

SQL> insert into t1(id,name) values(1,'daivd');

已創(chuàng)建 1 行。

SQL> select *from t1;

ID NAME

---------- --------------------------------------------------

1 dxw

1 daivd

SQL> insert into t1 values(1,'xyz');

已創(chuàng)建 1 行湾碎。

SQL> select *from t1;

ID NAME

---------- --------------------------------------------------

1 dxw

1 daivd

1 xyz

SQL>

SQL> insert into t1 select empno,ename from emp;

已創(chuàng)建14行厕宗。

SQL> select *from t1;

ID NAME

---------- --------------------------------------------------

1 dxw

1 daivd

1 xyz

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

7698 BLAKE

7782 CLARK

7788 SCOTT

7839 KING

7844 TURNER

7876 ADAMS

7900 JAMES

7902 FORD

7934 MILLER

已選擇17行画舌。

SQL>

SQL> insert into t1(id,name) values(&a,&b);

輸入 a 的值:? 1000

輸入 b 的值:? qwer

原值? ? 1: insert into t1(id,name) values(&a,&b)

新值? ? 1: insert into t1(id,name) values(1000,qwer)

insert into t1(id,name) values(1000,qwer)

*

第 1 行出現(xiàn)錯(cuò)誤:

ORA-00984: 列在此處不允許

SQL> insert into t1(id,name) values(&a,&b);

輸入 a 的值:? 1000

輸入 b 的值:? 'qwer'

原值? ? 1: insert into t1(id,name) values(&a,&b)

新值? ? 1: insert into t1(id,name) values(1000,'qwer')

已創(chuàng)建 1 行。

SQL> select *from t1;

ID NAME

---------- --------------------------------------------------

1 dxw

1 daivd

1 xyz

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

已慢。曲聂。。蛇受。句葵。厕鹃。兢仰。乍丈。

update 更新表中數(shù)據(jù)

一定要加where子句 否則會(huì)應(yīng)用到所有行

加了where子句時(shí)只更新where條件受影響的行

多列同時(shí)更新列與列之間用逗號(hào)分隔

更新日期列時(shí)要避免隱式轉(zhuǎn)換 使用to_date()函數(shù)

使用defulat值更新依舊使用default關(guān)鍵字

子查詢更新保證等號(hào)兩邊列的數(shù)量一致

SQL> create table t1 as select *from emp;

Table created.

SQL> select *from t1;

EMPNO ENAME? ? ? JOB ? ? ? MGR HIREDATE ? ? SAL? ? ? COMM? ? DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH? ? ? CLERK ? ? ? 7902 17-DEC-80 ? ? 800 ? 20

7499 ALLEN? ? ? SALESMAN ? ? ? 7698 20-FEB-81 ? 1600? ? ? ? 300 ? 30

7521 WARD? ? ? SALESMAN ? ? ? 7698 22-FEB-81 ? 1250? ? ? ? 500 ? 30

7566 JONES? ? ? MANAGER ? ? ? 7839 02-APR-81 ? 2975 ? 20

7654 MARTIN? ? SALESMAN ? ? ? 7698 28-SEP-81 ? 1250? ? ? 1400 ? 30

7698 BLAKE? ? ? MANAGER ? ? ? 7839 01-MAY-81 ? 2850 ? 30

7782 CLARK? ? ? MANAGER ? ? ? 7839 09-JUN-81 ? 2450 ? 10

7788 SCOTT? ? ? ANALYST ? ? ? 7566 19-APR-87 ? 3000 ? 20

7839 KING? ? ? PRESIDENT ? 17-NOV-81 ? 5000 ? 10

7844 TURNER? ? SALESMAN ? ? ? 7698 08-SEP-81 ? 1500 0 ? 30

7876 ADAMS? ? ? CLERK ? ? ? 7788 23-MAY-87 ? 1100 ? 20

7900 JAMES? ? ? CLERK ? ? ? 7698 03-DEC-81 ? ? 950 ? 30

7902 FORD? ? ? ANALYST ? ? ? 7566 03-DEC-81 ? 3000 ? 20

7934 MILLER? ? CLERK ? ? ? 7782 23-JAN-82 ? 1300 ? 10

14 rows selected.

SQL> update t1 set sal=1000;

14 rows updated.

SQL> select *from t1;

EMPNO ENAME? ? ? JOB ? ? ? MGR HIREDATE ? ? SAL? ? ? COMM? ? DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH? ? ? CLERK ? ? ? 7902 17-DEC-80 ? 1000 ? 20

7499 ALLEN? ? ? SALESMAN ? ? ? 7698 20-FEB-81 ? 1000? ? ? ? 300 ? 30

7521 WARD? ? ? SALESMAN ? ? ? 7698 22-FEB-81 ? 1000? ? ? ? 500 ? 30

7566 JONES? ? ? MANAGER ? ? ? 7839 02-APR-81 ? 1000 ? 20

7654 MARTIN? ? SALESMAN ? ? ? 7698 28-SEP-81 ? 1000? ? ? 1400 ? 30

7698 BLAKE? ? ? MANAGER ? ? ? 7839 01-MAY-81 ? 1000 ? 30

7782 CLARK? ? ? MANAGER ? ? ? 7839 09-JUN-81 ? 1000 ? 10

7788 SCOTT? ? ? ANALYST ? ? ? 7566 19-APR-87 ? 1000 ? 20

7839 KING? ? ? PRESIDENT ? 17-NOV-81 ? 1000 ? 10

7844 TURNER? ? SALESMAN ? ? ? 7698 08-SEP-81 ? 1000 0 ? 30

7876 ADAMS? ? ? CLERK ? ? ? 7788 23-MAY-87 ? 1000 ? 20

7900 JAMES? ? ? CLERK ? ? ? 7698 03-DEC-81 ? 1000 ? 30

7902 FORD? ? ? ANALYST ? ? ? 7566 03-DEC-81 ? 1000 ? 20

7934 MILLER? ? CLERK ? ? ? 7782 23-JAN-82 ? 1000 ? 10

14 rows selected.

SQL> update t1 set sal=2000 where deptno=20;

5 rows updated.

SQL>

SQL> select *from t1;

EMPNO ENAME? ? ? JOB ? ? ? MGR HIREDATE ? ? SAL? ? ? COMM? ? DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH? ? ? CLERK ? ? ? 7902 17-DEC-80 ? 2000 ? 20

7499 ALLEN? ? ? SALESMAN ? ? ? 7698 20-FEB-81 ? 1000? ? ? ? 300 ? 30

7521 WARD? ? ? SALESMAN ? ? ? 7698 22-FEB-81 ? 1000? ? ? ? 500 ? 30

7566 JONES? ? ? MANAGER ? ? ? 7839 02-APR-81 ? 2000 ? 20

7654 MARTIN? ? SALESMAN ? ? ? 7698 28-SEP-81 ? 1000? ? ? 1400 ? 30

7698 BLAKE? ? ? MANAGER ? ? ? 7839 01-MAY-81 ? 1000 ? 30

7782 CLARK? ? ? MANAGER ? ? ? 7839 09-JUN-81 ? 1000 ? 10

7788 SCOTT? ? ? ANALYST ? ? ? 7566 19-APR-87 ? 2000 ? 20

7839 KING? ? ? PRESIDENT ? 17-NOV-81 ? 1000 ? 10

7844 TURNER? ? SALESMAN ? ? ? 7698 08-SEP-81 ? 1000 0 ? 30

7876 ADAMS? ? ? CLERK ? ? ? 7788 23-MAY-87 ? 2000 ? 20

7900 JAMES? ? ? CLERK ? ? ? 7698 03-DEC-81 ? 1000 ? 30

7902 FORD? ? ? ANALYST ? ? ? 7566 03-DEC-81 ? 2000 ? 20

7934 MILLER? ? CLERK ? ? ? 7782 23-JAN-82 ? 1000 ? 10

14 rows selected.

SQL> update t1 set (job,sal)=(select 'worker',3000 from dual) where deptno=30;

6 rows updated.

SQL> select *from t1 order by deptno;

EMPNO ENAME? ? ? JOB ? ? ? MGR HIREDATE ? ? SAL? ? ? COMM? ? DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7782 CLARK? ? ? MANAGER ? ? ? 7839 09-JUN-81 ? 1000 ? 10

7839 KING? ? ? PRESIDENT ? 17-NOV-81 ? 1000 ? 10

7934 MILLER? ? CLERK ? ? ? 7782 23-JAN-82 ? 1000 ? 10

7566 JONES? ? ? MANAGER ? ? ? 7839 02-APR-81 ? 2000 ? 20

7902 FORD? ? ? ANALYST ? ? ? 7566 03-DEC-81 ? 2000 ? 20

7876 ADAMS? ? ? CLERK ? ? ? 7788 23-MAY-87 ? 2000 ? 20

7369 SMITH? ? ? CLERK ? ? ? 7902 17-DEC-80 ? 2000 ? 20

7788 SCOTT? ? ? ANALYST ? ? ? 7566 19-APR-87 ? 2000 ? 20

7521 WARD? ? ? worker ? ? ? 7698 22-FEB-81 ? 3000? ? ? ? 500 ? 30

7844 TURNER? ? worker ? ? ? 7698 08-SEP-81 ? 3000 0 ? 30

7499 ALLEN? ? ? worker ? ? ? 7698 20-FEB-81 ? 3000? ? ? ? 300 ? 30

7900 JAMES? ? ? worker ? ? ? 7698 03-DEC-81 ? 3000 ? 30

7698 BLAKE? ? ? worker ? ? ? 7839 01-MAY-81 ? 3000 ? 30

7654 MARTIN? ? worker ? ? ? 7698 28-SEP-81 ? 3000? ? ? 1400 ? 30

14 rows selected.

子查詢更新，使用子查詢得到的結(jié)果對(duì)目標(biāo)表進(jìn)行更新把将，如果要更新多個(gè)字段轻专，需要把這些字段用括號(hào)

括起來(lái)，并且用逗號(hào)隔開(kāi)察蹲。

SQL> update myemp set (job,sal)=(select job,sal from emp where empno=7788);

已更新14行请垛。

SQL> select *from myemp;

EMPNO ENAME? ? ? JOB? ? ? ? ? ? ? MGR HIREDATE? ? ? ? ? ? ? ? ? SAL? ? ? COMM? ? DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7369 SMITH? ? ? ANALYST? ? ? ? 7902 1980-12-17 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 20

7499 ALLEN? ? ? ANALYST? ? ? ? 7698 1981-02-20 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 30

7521 WARD? ? ? ANALYST? ? ? ? 7698 1981-02-22 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 30

7566 JONES? ? ? ANALYST? ? ? ? 7839 1981-04-02 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 20

7654 MARTIN? ? ANALYST? ? ? ? 7698 1981-09-28 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 30

7698 BLAKE? ? ? ANALYST? ? ? ? 7839 1981-05-01 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 30

7782 CLARK? ? ? ANALYST? ? ? ? 7839 2016-05-25 00:00:00? ? ? 3500? ? ? 1000? ? ? ? 10

7788 SCOTT? ? ? ANALYST? ? ? ? 7566 1987-04-19 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 20

7839 KING? ? ? ANALYST? ? ? ? ? ? ? 2016-05-25 00:00:00? ? ? 3500? ? ? 1000? ? ? ? 10

7844 TURNER? ? ANALYST? ? ? ? 7698 1981-09-08 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 30

7876 ADAMS? ? ? ANALYST? ? ? ? 7788 1987-05-23 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 20

7900 JAMES? ? ? ANALYST? ? ? ? 7698 1981-12-03 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 30

7902 FORD? ? ? ANALYST? ? ? ? 7566 1981-12-03 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 20

7934 MILLER? ? ANALYST? ? ? ? 7782 2016-05-25 00:00:00? ? ? 3500? ? ? 1000? ? ? ? 10

已選擇14行。

關(guān)聯(lián)子查詢更新

SQL> update myemp m set (job,sal,comm)=(select job,sal,comm from emp where empno=m.empno);

已更新14行洽议。

SQL> select *from myemp;

EMPNO ENAME? ? ? JOB? ? ? ? ? ? ? MGR HIREDATE? ? ? ? ? ? ? ? ? SAL? ? ? COMM? ? DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7369 SMITH? ? ? CLERK? ? ? ? ? 7902 1980-12-17 00:00:00? ? ? ? 900? ? ? ? ? ? ? ? ? ? 20

7499 ALLEN? ? ? SALESMAN? ? ? ? 7698 1981-02-20 00:00:00? ? ? 1600? ? ? ? 300? ? ? ? 30

7521 WARD? ? ? SALESMAN? ? ? ? 7698 1981-02-22 00:00:00? ? ? 1250? ? ? ? 500? ? ? ? 30

7566 JONES? ? ? MANAGER? ? ? ? 7839 1981-04-02 00:00:00? ? ? 2975? ? ? ? ? ? ? ? ? ? 20

7654 MARTIN? ? SALESMAN? ? ? ? 7698 1981-09-28 00:00:00? ? ? 1250? ? ? 1400? ? ? ? 30

7698 BLAKE? ? ? MANAGER? ? ? ? 7839 1981-05-01 00:00:00? ? ? 2850? ? ? ? ? ? ? ? ? ? 30

7782 CLARK? ? ? MANAGER? ? ? ? 7839 2016-05-25 00:00:00? ? ? 2450? ? ? ? ? ? ? ? ? ? 10

7788 SCOTT? ? ? ANALYST? ? ? ? 7566 1987-04-19 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 20

7839 KING? ? ? PRESIDENT? ? ? ? ? ? 2016-05-25 00:00:00? ? ? 5000? ? ? ? ? ? ? ? ? ? 10

7844 TURNER? ? SALESMAN? ? ? ? 7698 1981-09-08 00:00:00? ? ? 1500? ? ? ? ? 0? ? ? ? 30

7876 ADAMS? ? ? CLERK? ? ? ? ? 7788 1987-05-23 00:00:00? ? ? 1100? ? ? ? ? ? ? ? ? ? 20

7900 JAMES? ? ? CLERK? ? ? ? ? 7698 1981-12-03 00:00:00? ? ? ? 950? ? ? ? ? ? ? ? ? ? 30

7902 FORD? ? ? ANALYST? ? ? ? 7566 1981-12-03 00:00:00? ? ? 3000? ? ? ? ? ? ? ? ? ? 20

7934 MILLER? ? CLERK? ? ? ? ? 7782 2016-05-25 00:00:00? ? ? 1300? ? ? ? ? ? ? ? ? ? 10

已選擇14行宗收。

delete 刪除表數(shù)據(jù)

不加where 全部記錄刪除

加where只刪除條件滿足行

where條件中也可以使用子查詢

delete t1

delete t1 where ....

SQL> delete from myemp where deptno=(select deptno from dept where loc='NEW YORK');

已刪除3行。

SQL> SELECT *FROM MYEMP;

EMPNO ENAME? ? ? JOB? ? ? ? ? ? ? MGR HIREDATE? ? ? ? ? ? ? ? ? SAL? ? ? COMM? ? DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7369 SMITH? ? ? CLERK? ? ? ? ? 7902 1980-12-17 00:00:00? ? ? ? 900? ? ? ? ? ? ? ? ? ? 20

7499 ALLEN? ? ? SALESMAN? ? ? ? 7698 1981-02-20 00:00:00? ? ? 1600? ? ? ? 300? ? ? ? 30

7521 WARD? ? ? SALESMAN? ? ? ? 7698 1981-02-22 00:00:00? ? ? 1250? ? ? ? 500? ? ? ? 30

7566 JONES? ? ? MANAGER? ? ? ? 7839 1981-04-02 00:00:00? ? ? 2975? ? ? ? ? ? ? ? ? ? 20

7654 MARTIN? ? SALESMAN? ? ? ? 7698 1981-09-28 00:00:00? ? ? 1250? ? ? 1400? ? ? ? 30

7698 BLAKE? ? ? MANAGER? ? ? ? 7839 1981-05-01 00:00:00? ? ? 2850? ? ? ? ? ? ? ? ? ? 30

7788 SCOTT? ? ? ANALYST? ? ? ? 7566 1987-04-19 00:00:00? ? ? 3500? ? ? ? ? ? ? ? ? ? 20

7844 TURNER? ? SALESMAN? ? ? ? 7698 1981-09-08 00:00:00? ? ? 1500? ? ? ? ? 0? ? ? ? 30

7876 ADAMS? ? ? CLERK? ? ? ? ? 7788 1987-05-23 00:00:00? ? ? 1100? ? ? ? ? ? ? ? ? ? 20

7900 JAMES? ? ? CLERK? ? ? ? ? 7698 1981-12-03 00:00:00? ? ? ? 950? ? ? ? ? ? ? ? ? ? 30

7902 FORD? ? ? ANALYST? ? ? ? 7566 1981-12-03 00:00:00? ? ? 3000? ? ? ? ? ? ? ? ? ? 20

已選擇11行亚兄。

ORACLE就提供了merge來(lái)解決這種選擇性執(zhí)行DML語(yǔ)句

語(yǔ)法:

MERGE INTO table_name table_alias

USING (table|view|sub_query) alias

ON (join condition)

WHEN MATCHED THEN

UPDATE SET

COL1=COL_VAR1,

COL2=COL_VAR2

WHERE expr

DELETE WHERE expr

WEHN NOT MATCHED THEN

INSERT (col1,col2,...)

VALUES (col_var1,col_var2,...);

INTO 被插入或更新的表

USING 參照表 插入或更新的數(shù)據(jù)從這個(gè)表獲取

ON 條件

WHEN MATCHED THEN? 條件成立判斷

UPDATE SET 更新內(nèi)容(不寫表名) 而且建立關(guān)系的列不用用于更新

WEHN NOT MATCHED THEN 條件失敗判斷

INSERT VALUES 插入內(nèi)容(不寫表名)

SQL> create table myemp as select empno,ename,deptno,sal from emp where deptno=10;

表已創(chuàng)建混稽。

SQL> select *from myemp;

EMPNO ENAME? ? ? ? ? DEPTNO? ? ? ? SAL

---------- ---------- ---------- ----------

7782 CLARK? ? ? ? ? ? ? 10? ? ? 2450

7839 KING? ? ? ? ? ? ? 10? ? ? 5000

7934 MILLER? ? ? ? ? ? 10? ? ? 1300

SQL> update myemp set sal=0;

已更新3行。

SQL> commit;

提交完成审胚。

SQL> select *from myemp;

EMPNO ENAME? ? ? ? ? DEPTNO? ? ? ? SAL

---------- ---------- ---------- ----------

7782 CLARK? ? ? ? ? ? ? 10? ? ? ? ? 0

7839 KING? ? ? ? ? ? ? 10? ? ? ? ? 0

7934 MILLER? ? ? ? ? ? 10? ? ? ? ? 0

merge into myemp m

using emp e

on(m.empno=e.empno)

when matched then

update set m.sal=e.sal

where m.empno=7839

when not matched then

insert values(e.empno,e.ename,e.deptno,e.sal)

where e.deptno=20

SQL> select *from myemp;

EMPNO ENAME? ? ? ? ? DEPTNO? ? ? ? SAL

---------- ---------- ---------- ----------

7782 CLARK? ? ? ? ? ? ? 10? ? ? ? ? 0

7839 KING? ? ? ? ? ? ? 10? ? ? 5000

7934 MILLER? ? ? ? ? ? 10? ? ? ? ? 0

7369 SMITH? ? ? ? ? ? ? 20? ? ? ? 900

7566 JONES? ? ? ? ? ? ? 20? ? ? 2975

7788 SCOTT? ? ? ? ? ? ? 20? ? ? 3500

7876 ADAMS? ? ? ? ? ? ? 20? ? ? 1100

7902 FORD? ? ? ? ? ? ? 20? ? ? 3000

update后可以使用delete where子句

刪除規(guī)則: 匹配delete條件并且匹配on條件匈勋，并且匹配update的where條件。

6.DCL

DCL 語(yǔ)句 數(shù)據(jù)控制語(yǔ)言

GRANT? 授予

REVOKE? 回收

DCL語(yǔ)言會(huì)在講授權(quán)限控制時(shí)接觸到更多權(quán)限和角色

權(quán)限? grant 權(quán)限? to user

revoke 權(quán)限 from user

7.TCL

TCL 事務(wù)控制 語(yǔ)句

什么是事務(wù):用于確保數(shù)據(jù)的一致性,由一組相關(guān)的DML組成, 該組DML的操作要么全確認(rèn),要么全取消

比如銀行轉(zhuǎn)賬業(yè)務(wù) 步驟一:從A賬戶減少300元,步驟二:向B賬戶增加300元.

為了確鄙胚叮總的金額不變 就要維持?jǐn)?shù)據(jù)的一致性 步驟一和步驟二兩個(gè)操作或者全確認(rèn) 或者全取消

事務(wù)的開(kāi)始語(yǔ)句:update insert delete? select ... for update 都能觸發(fā)一個(gè)事務(wù)的開(kāi)啟

事務(wù)的結(jié)束語(yǔ)句:commit提交 rollback回滾

未完成的事務(wù)可以撤銷

未完成的事務(wù),其他會(huì)話看不到結(jié)果,只能看到提交的結(jié)果

維護(hù)事務(wù)需要鎖和回退段參與

事務(wù)四個(gè)特性

事務(wù)的原子性：事務(wù)中的所有操作作為一個(gè)整體洽洁，要么全部回退，要么全部提交

事務(wù)的隔離性：事務(wù)之間互相隔離菲嘴，相互獨(dú)立存在

事務(wù)的一致性：未提交的事務(wù)不被其他會(huì)話看到

事務(wù)的持久性：事務(wù)提交后饿自，數(shù)據(jù)永久生效

事物控制語(yǔ)句

COMMIT

SAVEPOINT

savepoint t1

rollback to t1

ROLLBACK

事務(wù)的提交方法

1.手動(dòng)提交

執(zhí)行commit語(yǔ)句

2.自動(dòng)提交

ddl,dcl會(huì)自動(dòng)提交之前未結(jié)束的事務(wù)

exit｜disconnect 退出sqlplus會(huì)自動(dòng)提交

3. 設(shè)置sqlplus里: set autocommit on|off

事務(wù)的撤銷方法

1.手動(dòng)撤銷

執(zhí)行rollback

2.自動(dòng)撤銷

網(wǎng)絡(luò)或數(shù)據(jù)庫(kù)崩潰

強(qiáng)制退出sqlplus 點(diǎn)擊X按鈕

commit 執(zhí)行就是提交整個(gè)事務(wù)

rollback 用savepoint設(shè)置定點(diǎn) 可以回退到事務(wù)過(guò)程中的定點(diǎn)位置

8.數(shù)據(jù)庫(kù)其他資源

(1).用戶和權(quán)限

創(chuàng)建用戶

CREATE USER user_name IDENTIFIED BY password;

DROP USER user_name [cascade];

ALTER USER user_name ACCOUNT [UN]LOCK;

什么是權(quán)限

權(quán)限就是用來(lái)控制特定的用戶發(fā)送特定的SQL語(yǔ)句是否允許正確的執(zhí)行

在多用戶環(huán)境,DBA需要維護(hù)數(shù)據(jù)庫(kù)訪問(wèn)的安全

以確保特定用戶擁有特定的操作權(quán)限

數(shù)據(jù)庫(kù)的權(quán)限分為

系統(tǒng)權(quán)限

對(duì)象權(quán)限

系統(tǒng)權(quán)限

大部分是針對(duì)對(duì)象的創(chuàng)建,刪除,修改,審計(jì)等等的操作

查看系統(tǒng)都具備哪些權(quán)限

select DISTINCT PRIVILEGE from dba_sys_privs;

查看當(dāng)前會(huì)話都具備哪些權(quán)限

select * from session_privs;

對(duì)象權(quán)限

授予特定用戶對(duì)特定對(duì)象的權(quán)限

特定對(duì)象:表、視圖龄坪、序列昭雌、過(guò)程、函數(shù)悉默、程序包上城豁。

角色

角色就是一組權(quán)限的集合

我們也可以自己建立包含自己擬定權(quán)限的角色

查看oracle給你定制了哪些角色

SQL> SELECT *FROM DBA_ROLES;

(2).視圖

視圖 view

命名的sql語(yǔ)句,存儲(chǔ)在數(shù)據(jù)字典中

視圖本身不包含數(shù)據(jù)(物化視圖除外)，視圖的數(shù)據(jù)來(lái)源于基表或其他視圖（創(chuàng)建視圖時(shí)的查詢所關(guān)聯(lián)的表）

視圖優(yōu)缺點(diǎn)

可以限制數(shù)據(jù)的訪問(wèn)

方便管理

增加了數(shù)據(jù)庫(kù)的負(fù)擔(dān)

查看視圖的定義

user_views

視圖分類

簡(jiǎn)單視圖

只有單表

不包含去重,聚集,分組,函數(shù),偽列,計(jì)算表達(dá)式

可以在視圖上執(zhí)行DML操作抄课，這叫可更新視圖

復(fù)雜視圖

包含一個(gè)或多個(gè)表

可以包含函數(shù)和分組數(shù)據(jù)

不能進(jìn)行DML操作基表

創(chuàng)建語(yǔ)法

create view view_name as select deptno,max(sal) from emp group by deptno;

DEPTNO? MAX(SAL)

---------- ----------

30 2850

20 3000

10 5000

刪除視圖

drop view view_name;

強(qiáng)制選項(xiàng)

SQL> create or replace force view v1 as select empno,ename,sal,deptno from my_emp where deptno=10;

Warning: View created with compilation errors.

(3)序列

sequence 序列

流水號(hào)(整數(shù))遞增或遞減

存在于library cache

通常為主鍵或唯一約束提供值

減少編寫生成序列的程序代碼量

比如QQ號(hào)的產(chǎn)生

語(yǔ)法

CREATE SEQUENCE seq_name

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE N | NOMAXVALUE}]

[{MINVALUE N | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE N|NOCACHE}];

取值方法

NEXTVAL 返回下一個(gè)值

CURRVAL 返回當(dāng)前值

SQL> create sequence seq1;

Sequence created.

SQL> select *from user_sequences where sequence_name='SEQ1';

SEQUENCE_NAME MIN_VALUE? MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

------------------------------ ----------? ? ? ? ---------- ------------ - - ---------- -----------

SEQ1 1? ? ? ? ? ? ? ? ? ? 1.0000E+28 1 N N ? ? ? 20 ? 1

SQL> SELECT SEQ1.NEXTVAL FROM DUAL;

NEXTVAL

----------

1

SQL> /

NEXTVAL

----------

2

SQL> /

NEXTVAL

----------

3

SQL> /

NEXTVAL

----------

4

SQL> SELECT SEQ1.CURRVAL FROM DUAL;

CURRVAL

----------

4

想要修改序列的當(dāng)前值回溯的過(guò)去值 只能刪除重新

drop sequence seq2;

create sequence seq2 start with 10;

修改序列的屬性

alter sequence seq2 minvalue 1;

（4）約束

用來(lái)維護(hù)數(shù)據(jù)結(jié)構(gòu)完整性的一種手段

可以是表級(jí) 也可以是列級(jí)

通過(guò)查詢視圖 user_constraints? user_cons_columns

約束的五種類型

非空 not null

唯一 unique

檢測(cè) check? 自定義

主鍵 primary key

外鍵 foreign key

非空約束

列的值不允許null值

可以在建表時(shí)指明

可以建完表后添加

可以指定名字 不指定系統(tǒng)會(huì)給定隨機(jī)名

創(chuàng)建表時(shí)在字段類型后添加 not null關(guān)鍵字

SQL> create table t1 (id number not null,name varchar2(20) constraint t1_name_notnull not null);

Table created.

SQL> desc t1

Name ? ? Null?? ? Type

-------------------------------------------------- -------- ----------------------------------

ID ? ? NOT NULL NUMBER

NAME ? ? NOT NULL VARCHAR2(20)

查看約束

SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name='T1';

TABLE_NAME ? ? ? CONSTRAINT_NAME ? ? ? C STATUS

------------------------------ ------------------------------ - --------

T1 ? ? ? SYS_C005173 ? ? ? C ENABLED

T1 ? ? ? T1_NAME_NOTNULL ? ? ? C ENABLED

SQL>

查看約束作用于t1表哪一列

SQL> col OWNER for a10

SQL> col column_name for a16

SQL> col table_name for a10

SQL> col CONSTRAINT_NAME for a20

SQL> select * from user_cons_columns where table_name='T1';

OWNER ? CONSTRAINT_NAME TABLE_NAME COLUMN_NAME ? ? ? POSITION

---------- -------------------- ---------- ---------------- ----------

SCOTT ? T1_NAME_NOTNULL T1 ? NAME

SCOTT ? SYS_C005173 T1 ? ID

SQL> insert into t1 values(1,'SEKER');

1 row created.

對(duì)已經(jīng)存在的表添加

SQL> alter table t1 add (mail varchar2(40));

Table altered.

SQL> desc t1

Name ? ? ? Null?? ? Type

-------------------------------------------- -------- ------------------------------

ID ? ? ? NOT NULL NUMBER

NAME ? ? ? NOT NULL VARCHAR2(20)

MAIL ? ? ? VARCHAR2(40)

SQL> alter table t1 modify(mail constraint ct not null);

Table altered.

SQL> desc t1

Name ? ? ? Null?? ? Type

-------------------------------------------- -------- ------------------------------

ID ? ? ? NOT NULL NUMBER

NAME ? ? ? NOT NULL VARCHAR2(20)

MAIL ? ? ? NOT NULL VARCHAR2(40)

唯一約束

列的數(shù)據(jù)只能是唯一的 不可重復(fù)

可以是單列,可以是組合列(就叫表級(jí))

所以唯一性約束它可以是列級(jí)別,可以是表級(jí)別

但unique規(guī)定的列只有一列時(shí)可以在列級(jí)別定義

如果unique規(guī)定的列包含多列時(shí)只能在表級(jí)別定義

創(chuàng)建唯一約束 即自動(dòng)創(chuàng)建索引

SQL> create table t1(id number unique,

name varchar2(10),

mail varchar2(20),

constraints my_un unique(name,mail))

;

SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name='T1';

TABLE_NAME CONSTRAINT_NAME C

---------- -------------------- -

T1 ? SYS_C005210 U

T1 ? MY_UN U

SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME from user_cons_columns where TABLE_NAME='T1';

TABLE_NAME CONSTRAINT_NAME COLUMN_NAME

---------- -------------------- ----------------

T1 ? MY_UN NAME

T1 ? MY_UN MAIL

T1 ? SYS_C005210 ID

SQL> select TABLE_NAME,INDEX_NAME,INDEX_TYPE from user_indexes where table_name='T1';

TABLE_NAME INDEX_NAME ? INDEX_TYPE

---------- ------------------------------ ---------------------------

T1 ? SYS_C005210 ? NORMAL

T1 ? MY_UN ? NORMAL

SQL>? 索引自動(dòng)創(chuàng)建 和約束同名

刪除約束語(yǔ)法

SQL> alter table t1 drop constraint SYS_C0011129;

SQL> alter table t1 disable constraint SYS_C0011129;

表已更改唱星。

主鍵約束

主鍵和唯一共同點(diǎn):

都是唯一的? 可以單列 也可以多列

都是自動(dòng)創(chuàng)建索引

主鍵和唯一不同點(diǎn):

unique? ? ? 可以為空

primary key 不可以為空

一般來(lái)說(shuō) 每個(gè)表都應(yīng)該有自己的主鍵列 而且最好是單列的，一張表只能具有一個(gè)主鍵跟磨，可以是單列也可以是多列

SQL> create table t2 (id number constraint t2_PK_id primary key,name varchar2(10));

Table created.

外鍵約束

例如 emp.deptno 被 dept.deptno值包含

如果你刪除dept中deptno=10

從數(shù)據(jù)變更角度來(lái)講沒(méi)問(wèn)題 但他的業(yè)務(wù)邏輯出現(xiàn)了錯(cuò)誤 沒(méi)有部門哪來(lái)的員工呢??

這就叫數(shù)據(jù)的完整性

這就是外鍵的作用 參考主鍵來(lái)維護(hù)數(shù)據(jù)完整性

參考主鍵的值 外鍵列中的值被主鍵中的值所包含(子集)

同一張表可以創(chuàng)建多個(gè)外鍵列间聊，例如：

SQL> create table t2(id number,name number,

foreign key(id) references emp(empno),

foreign key(name) references emp(empno));

同一個(gè)列可以創(chuàng)建多個(gè)外鍵約束，但是該列上的只能存在所有參考的主鍵列值的交集部分

外鍵約束既可以參考其他表抵拘，也可以參考本表的其他字段哎榴，例如：

SQL>alter table emp add constraint emp_mgr_fk foreign key(mgr) references emp(empno);

而且參考列一定是唯一或主鍵

否則會(huì)報(bào): ORA-02270: no matching unique or primary key for this column-list

創(chuàng)建外鍵約束

SQL> create table my_dept as select * from dept;

SQL> alter table my_dept modify(deptno number primary key);

SQL> create table my_emp (

empno number,

ename varchar2(20),

deptno number,

constraint my_emp_fk_dno foreign key(deptno) references my_dept(deptno)

);

轉(zhuǎn)至：↓

鏈接：http://www.reibang.com/p/4b715a7c965d

來(lái)源：簡(jiǎn)書

著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處尚蝌。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末迎变，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子飘言，更是在濱河造成了極大的恐慌衣形，老刑警劉巖，帶你破解...
    沈念sama閱讀 216,544評(píng)論 6贊 501
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件姿鸿，死亡現(xiàn)場(chǎng)離奇詭異谆吴，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)苛预，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,430評(píng)論 3贊 392
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門句狼，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人热某，你說(shuō)我怎么就攤上這事腻菇。” “怎么了苫拍？”我有些...
    開(kāi)封第一講書人閱讀 162,764評(píng)論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵芜繁，是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我绒极，道長(zhǎng)骏令，這世上最難降的妖魔是什么？ 我笑而不...
    開(kāi)封第一講書人閱讀 58,193評(píng)論 1贊 292
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任垄提，我火速辦了婚禮榔袋，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘铡俐。我一直安慰自己凰兑，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,216評(píng)論 6贊 388
  • 惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
    文/花漫 我一把揭開(kāi)白布审丘。 她就那樣靜靜地躺著吏够，像睡著了一般。 火紅的嫁衣襯著肌膚如雪滩报。 梳的紋絲不亂的頭發(fā)上锅知，一...
    開(kāi)封第一講書人閱讀 51,182評(píng)論 1贊 299
  • 城市分裂傳說(shuō)
    那天，我揣著相機(jī)與錄音脓钾，去河邊找鬼售睹。 笑死，一個(gè)胖子當(dāng)著我的面吹牛可训，可吹牛的內(nèi)容都是我干的昌妹。 我是一名探鬼主播捶枢，決...
    沈念sama閱讀 40,063評(píng)論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開(kāi)眼，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼飞崖！你這毒婦竟也來(lái)了烂叔？” 一聲冷哼從身側(cè)響起，我...
    開(kāi)封第一講書人閱讀 38,917評(píng)論 0贊 274
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤蚜厉，失蹤者是張志新（化名）和其女友劉穎长已，沒(méi)想到半個(gè)月后畜眨，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體昼牛，經(jīng)...
    沈念sama閱讀 45,329評(píng)論 1贊 310
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,543評(píng)論 2贊 332
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年康聂，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了贰健。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 39,722評(píng)論 1贊 348
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡恬汁，死狀恐怖伶椿，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情氓侧，我是刑警寧澤脊另，帶...
    沈念sama閱讀 35,425評(píng)論 5贊 343
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站约巷，受9級(jí)特大地震影響偎痛，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜独郎，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,019評(píng)論 3贊 326
  • 男人毒藥：我在死后第九天來(lái)索命
    文/蒙蒙 一踩麦、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧氓癌，春花似錦谓谦、人聲如沸。這莊子的主人今日做“春日...
    開(kāi)封第一講書人閱讀 31,671評(píng)論 0贊 22
  • 一樁弒父案反粥，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至疲迂，卻和暖如春才顿，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背鬼譬。 一陣腳步聲響...
    開(kāi)封第一講書人閱讀 32,825評(píng)論 1贊 269
  • 情欲美人皮
    我被黑心中介騙來(lái)泰國(guó)打工娜膘， 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人优质。 一個(gè)月前我還...
    沈念sama閱讀 47,729評(píng)論 2贊 368
  • 代替公主和親
    正文 我出身青樓竣贪，卻偏偏與公主長(zhǎng)得像军洼，于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子演怎，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,614評(píng)論 2贊 353