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