程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle學習（8）：處理數據

Oracle學習（8）：處理數據

編輯：Oracle教程

Oracle學習（8）：處理數據

處理數據

*******************************************************************************************

一個decode語句練習

SQL> select count(*) Total,

2 sum(decode(to_char(hiredate,"yyyy'),'1980',1,0)) "1980",
3 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
4 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
5 sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
6 from emp;

TOTAL 1980 1981 1982 1987
---------- ---------- ---------- ---------- ----------
14 1 10 1 2

*******************************************************************************************

SQL語言的類型

SQL> /*
SQL> SQL語言的類型
SQL> 1. DML(Data Manipulation Language 數據操作語言):insert delete update select
SQL> 2. DDL(Data Definition Language 數據定義語言): create table,create view,create user
SQL> alter table,alter user
SQL> drop table,drop user
SQL> 3. DCL(Data Control Language 數據控制語言): commit rollback truncate
SQL> */
SQL> host cls

插入 insert語句

語法

INSERT INTO table[(column [,column...])]

VALUES (value[, value...]);

使用INSERT 語句向表中插入數據。
使用這種語法一次只能向表中插入一條數據。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - -

INSERT INTO departments(department_id, department_name,

manager_id, location_id)

VALUES (70, 'PublicRelations', 100, 1700);

為每一列添加一個新值。按列的默認順序列出各個列的值。在INSERT 子句中隨意列出列名和他們的值。字符和日期型數據應包含在單引號中。

- - - - - - - - - - - -- - - - - - - - - - - -- - - - - - - - - - - -- - - - - - - - - - - -- - - - - - - - - - - -- - - - - - - - - - - -
SQL> --插入 insert
SQL> desc emp
名稱是否為空? 類型
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
2 values (1001,'Tom','CLERK',7839,sysdate,2000,100,10);

已創建 1 行。

隱式插入空值顯式插入空值

?隱式方式:在列名表中省略該列的值。 ?顯式方式:在VALUES 子句中指定空值。

SQL> --隱式插入空值顯式插入空值

SQL> insert into emp(empno,ename,sal,deptno) values(1002,'Mary',2000,10);

SQL> insert into emp(empno,ename,sal,deptno) values(1003,'Mary',null,10);

地址符的應用

SQL> --地址符 &

l在SQL 語句中使用& 變量指定列值。 l& 變量放在VALUES子句中。

SQL> insert into emp(empno,ename,sal,deptno)
2 values(&empno,&ename,&sal,&deptno);
輸入 empno 的值: 1004
輸入 ename 的值: 'Mike'
輸入 sal 的值: 2000
輸入 deptno 的值: 10
原值 2: values(&empno,&ename,&sal,&deptno)
新值 2: values(1004,'Mike',2000,10)

已創建 1 行。

SQL> /
輸入 empno 的值: 1005
輸入 ename 的值: 'aaa'
輸入 sal 的值: 3000
輸入 deptno 的值: 20
原值 2: values(&empno,&ename,&sal,&deptno)
新值 2: values(1005,'aaa',3000,20)

已創建 1 行。

給&xxx加引號之後，在後面賦值可不需要加引號：

SQL> insert into emp(empno,ename,sal,deptno) values(&empno,'&ename',&sal,&deptno);

輸入 empno 的值: 1006
輸入 ename 的值: Tomaa
輸入 sal 的值: 1500
輸入 deptno 的值: 10
原值 1: insert into emp(empno,ename,sal,deptno) values(&empno,'&ename',&sal,&deptno)
新值 1: insert into emp(empno,ename,sal,deptno) values(1006,'Tomaa',1500,10)

已創建 1 行。

SQL> host cls

&可用於語句的任何地方：

SQL> select empno,ename,&unknown
2 from emp;
輸入 unknown 的值: sal
原值 1: select empno,ename,&unknown
新值 1: select empno,ename,sal

EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100

EMPNO ENAME SAL
---------- ---------- ----------
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
1001 Tom 2000
1002 Mary 2000
1003 Mary
1004 Mike 2000
1005 aaa 3000
1006 Tomaa 1500

已選擇20行。

SQL> /
輸入 unknown 的值: job
原值 1: select empno,ename,&unknown
新值 1: select empno,ename,job

EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK

EMPNO ENAME JOB
---------- ---------- ---------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
1001 Tom CLERK
1002 Mary
1003 Mary
1004 Mike
1005 aaa
1006 Tomaa

已選擇20行。

SQL> select *
2 from &t;
輸入 t 的值: dept
原值 2: from &t
新值 2: from dept

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> /
輸入 t 的值: salgrade
原值 2: from &t
新值 2: from salgrade

GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

SQL> host cls

從其它表中拷貝數據

·在INSERT語句中加入子查詢。

INSERT INTO sales_reps(id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

FROM employees

WHERE job_id LIKE '%REP%';

·不必書寫 VALUES 子句。 ·子查詢中的值列表應與INSERT 子句中的列名對應

SQL> --一次插入（拷貝）多條數據?
SQL> create table emp10 as select * from emp where 1=2; （因為1=2不成立，所以不會拷貝任何數據，但是會將表結構拷貝過來！）

表已創建。

SQL> desc emp10
名稱是否為空? 類型
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> insert into emp10
2 select * from emp where deptno=10;

已創建3行。

SQL> select * from emp10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10

7839 KING PRESIDENT 17-11月-81 5000
10

7934 MILLER CLERK 7782 23-1月 -82 1300
10

SQL> --在插入中使用子查詢
SQL> create table emp20 as select * from emp where deptno=20;

表已創建。

SQL> select * from emp20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20

更新數據 update

·使用UPDATE 語句更新數據。

UPDATE table

SET column = value [, column= value, ...]

[WHERE condition];

·可以一次更新多條數據。

使用 WHERE 子句指定需要更新的數據

如果省略WHERE子句，則表中的所有數據都將被更新

可在update中使用子查詢

刪除：delete

SQL> --刪除：delete
SQL> delete from emp10;

已刪除3行。

SQL> truncate table emp20;

表被截斷。

SQL> /*
SQL> 1. delete逐條刪除；truncate先摧毀，再重建
SQL> 2. delete 不會釋放空間；truncate會
SQL> 3. delete會產生碎片；truncate不會
SQL> 4. deleted可以閃回;truncate不可以閃回
SQL> */
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10

已選擇14行。

SQL> set feedback off
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
SQL> @c:\sql.sql
SQL> select count(*) from testdelete;

COUNT(*)
----------
5000
SQL> set timing on
SQL> delete from testdelete;;
delete from testdelete;
*
第 1 行出現錯誤:
ORA-00911: 無效字符

已用時間: 00: 00: 00.00
SQL> delete from testdelete;
已用時間: 00: 00: 00.04
SQL> drop table testdelete purge;
已用時間: 00: 00: 01.36
SQL> set timing on
SQL> set timing off
SQL> @c:\sql.sql
SQL> set timing on
SQL> truncate table testdelete;
已用時間: 00: 00: 03.21
SQL> set timing off
SQL> host cls

Oracle中的事務

事務知識點

數據庫事務由以下的部分組成:

一個或多個DML 語句一個DDL(Data Definition Language – 數據定義語言) 語句一個DCL(Data Control Language – 數據控制語言) 語句

1. 起始標志: DML語句-自動開啟
2. 結束標志:提交: 顯式提交 commit
隱式提交: DDL(create table,drop user,..)
回滾：顯式回滾 rollback

隱式回滾: 掉電，宕機，非正常退出

使用COMMIT 和ROLLBACK語句,我們可以:

l確保數據完整性。 l數據改變被提交之前預覽。 l將邏輯上相關的操作分組。

自動提交在以下情況中執行: ?DDL 語句。 ?DCL 語句。 ?不使用 COMMIT 或 ROLLBACK 語句提交或回滾，正常結束會話exit。會話異常結束或系統異常會導致自動回滾。

保存點

使用SAVEPOINT 語句在當前事務中創建保存點。使用ROLLBACK TOSAVEPOINT 語句回滾到創建的保存點。

SQL> create table testsavepoint

2 (tid number,tname varchar2(20));
SQL> set feedback on
SQL> insert into testsavepoint values(1,'Tom');

已創建 1 行。

SQL> insert into testsavepoint values(2,'Mary');

已創建 1 行。

SQL> savepoint a;

保存點已創建。

SQL> insert into testsavepoint values(3,'Mike');

已創建 1 行。

SQL> rollback to savepoint a;

回退已完成。

SQL> select * from testsavepoint;

TID TNAME
---------- --------------------
1 Tom
2 Mary

已選擇2行。

SQL> commit;

提交完成。

JDBC中如何使用保存點

SQL> /*
SQL> JDBC中使用存儲點
SQL> Connection conn = ...
SQL> conn.setAutoCommit(false);
SQL> Savepoint sp = conn.setSavepoint("aaa")
SQL> DML
SQL>
SQL> conn.rollback(sp);
SQL> */
SQL> spool off