|
Size: 4658
Comment:
|
Size: 6888
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 278: | Line 278: |
== savepoint == {{{ SQL> rollback; Rollback complete. SQL> select * from dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NETWORK BEJING 70 6 rows selected. SQL> savepoint a; Savepoint created. SQL> insert into dept(deptno) values(73); 1 row created. SQL> select * from dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NETWORK BEJING 70 73 7 rows selected. SQL> insert into dept(deptno) values(71); 1 row created. SQL> savepoint b; Savepoint created. SQL> insert into dept(deptno) values(72); 1 row created. SQL> savepoint c; Savepoint created. SQL> select * from dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NETWORK BEJING 70 73 71 72 9 rows selected. SQL> rollback to a; Rollback complete. SQL> select * from dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NETWORK BEJING 70 6 rows selected. }}} == table == {{{ create table department ( dept_id number(2), name char(20) master char(10) constraint dept_mas_nn not null, location varchar(100) constraint dept_loc_nn not null ) create table department ( dept_id number(2), name char(20), master char(10) constraint dept_mas_nn not null, location varchar(100) constraint dept_loc_nn not null ) SQL> / Table created. SQL> select * from department; no rows selected SQL> desc department; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPT_ID NUMBER(2) NAME CHAR(20) MASTER NOT NULL CHAR(10) LOCATION NOT NULL VARCHAR2(100) }}} |
Describe 首頁/2020-11-22 here.
* OCP Day2
Operator
SQL> select distinct job from emp where deptno=20; JOB ------------------ CLERK ANALYST MANAGER 1 select distinct job from emp where deptno=20 2 UNION 3* select distinct job from emp where deptno=30 SQL> / JOB ------------------ ANALYST CLERK MANAGER SALESMAN 1 select distinct job from emp where deptno=20 2 UNION all 3* select distinct job from emp where deptno=30 SQL> / JOB ------------------ CLERK ANALYST MANAGER SALESMAN CLERK MANAGER 6 rows selected.
1 select distinct job from emp where deptno=20 2 minus 3* select distinct job from emp where deptno=30 SQL> / JOB ------------------ ANALYST
Managing Tables Using DMS Statements
DML: insert update delete
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> insert into dept values(50,'NETWORK','BEJING');
1 row created.
SQL> insert into dept values(70,null,null);
1 row created.
SQL> insert into dept values(null,null,null);
insert into dept values(null,null,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 NETWORK BEJING
70
6 rows selected.SQL> create table dept1
2 as
3 select * from dept
4 where 1=2;
Table created.
SQL> insert into dept1
2 select * from dept;
6 rows created.
SQL> select * from dept1;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 NETWORK BEJING
70
6 rows selected.
SQL> truncate table dept1;
Table truncated.
SQL> select * from dept1;
no rows selected
update
SQL> update emp set sal=sal+200 where ename='SMITH';
1 row updated.
SQL> update emp set sal=sal+300 where sal>2000;
6 rows updated.
1 select ename,sal from emp
2* fetch next 3 rows only
SQL> /
ENAME SAL
-------------------- ----------
SMITH 1100
ALLEN 1700
WARD 1350
SQL> select sal from emp where ename='KING';
SAL
----------
5000
1* select * from emp where ename='SMITH'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 5000 20
delete
DML delete by condition or line. is able to rollback before commit
SQL> delete from emp; 14 rows deleted. SQL> SQL> select * from emp; no rows selected SQL> rollback; Rollback complete.
truncate
- delete all rows
cannot easy to undone
LOCK
- Terminal 1
SQL> update dept set loc='BEIJIN' where deptno=70;
1 row updated.
1* select * from dept
SQL> /
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 NETWORK BEJING
70 BEIJIN
6 rows selected.- Terminal 2
1* select * from dept
SQL> /
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 NETWORK BEJING
70
6 rows selected.
SQL> update dept set loc='SHANGHAI' where deptno=70;
will waiting...- Terminal 3
SQL> update dept set loc='GUANGZHOU' where deptno=60;
1 row updated.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 NETWORK BEJING
70
60 GUANGZHOU
7 rows selected.DEPTNO 70(Related) not commit
savepoint
SQL> rollback;
Rollback complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 NETWORK BEJING
70
6 rows selected.
SQL> savepoint a;
Savepoint created.
SQL> insert into dept(deptno) values(73);
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 NETWORK BEJING
70
73
7 rows selected.
SQL> insert into dept(deptno) values(71);
1 row created.
SQL> savepoint b;
Savepoint created.
SQL> insert into dept(deptno) values(72);
1 row created.
SQL> savepoint c;
Savepoint created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 NETWORK BEJING
70
73
71
72
9 rows selected.
SQL> rollback to a;
Rollback complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 NETWORK BEJING
70
6 rows selected.
table
create table department ( dept_id number(2), name char(20) master char(10) constraint dept_mas_nn not null, location varchar(100) constraint dept_loc_nn not null ) create table department ( dept_id number(2), name char(20), master char(10) constraint dept_mas_nn not null, location varchar(100) constraint dept_loc_nn not null ) SQL> / Table created. SQL> select * from department; no rows selected SQL> desc department; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPT_ID NUMBER(2) NAME CHAR(20) MASTER NOT NULL CHAR(10) LOCATION NOT NULL VARCHAR2(100)