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) }}} {{{ create table student ( id number(4) name char(10) constraint stu_nu }}} == read only table == {{{ ALTER TABLE emp READ ONLY; -- perform table maintenance and then -- return table back to read/write mode ALTER TABLE emp READ WRITE; }}} === rename table === {{{ rename student to stu }}} * dba_tables {{{ 1 select table_name,OWNER from dba_tables 2* fetch next 5 rows only SQL> / TABLE_NAME OWNER ------------------------------ ------------------------------ ICOL$ SYS COL$ SYS IND$ SYS TAB$ SYS CLU$ SYS }}} == View == 1 create view v1 2 as 3 select empno,ename,sal,comm 4* from emp SQL> / View created. SQL> select * from v1; EMPNO ENAME SAL COMM ---------- -------------------- ---------- ---------- 7369 SMITH 5000 7499 ALLEN 1700 300 7521 WARD 1350 500 7566 JONES 3375 7654 MARTIN 1350 1400 7698 BLAKE 3250 7782 CLARK 2850 7788 SCOTT 3400 7839 KING 5400 7844 TURNER 1600 0 7876 ADAMS 1200 EMPNO ENAME SAL COMM ---------- -------------------- ---------- ---------- 7900 JAMES 1050 7902 FORD 3400 7934 MILLER 1400 14 rows selected. SQL> desc user_views; Name Null? Type ----------------------------------------- -------- ---------------------------- VIEW_NAME NOT NULL VARCHAR2(128) TEXT_LENGTH NUMBER TEXT LONG TEXT_VC VARCHAR2(4000) TYPE_TEXT_LENGTH NUMBER TYPE_TEXT VARCHAR2(4000) OID_TEXT_LENGTH NUMBER OID_TEXT VARCHAR2(4000) VIEW_TYPE_OWNER VARCHAR2(128) VIEW_TYPE VARCHAR2(128) SUPERVIEW_NAME VARCHAR2(128) EDITIONING_VIEW VARCHAR2(1) READ_ONLY VARCHAR2(1) CONTAINER_DATA VARCHAR2(1) BEQUEATH VARCHAR2(12) ORIGIN_CON_ID NUMBER DEFAULT_COLLATION VARCHAR2(100) CONTAINERS_DEFAULT VARCHAR2(3) CONTAINER_MAP VARCHAR2(3) EXTENDED_DATA_LINK VARCHAR2(3) EXTENDED_DATA_LINK_MAP VARCHAR2(3) HAS_SENSITIVE_COLUMN VARCHAR2(3) SQL> SELECT VIEW_NAME,TEXT from USER_VIEWS; VIEW_NAME -------------------------------------------------------------------------------- TEXT -------------------------------------------------------------------------------- V1 select empno,ename,sal,comm from emp SQL> create view v2 2 as 3 select ename,sal,job,dname,loc 4 from emp,dept 4 where emp.deptno=dept.deptno; 5 / SQL> create view v3 2 as 3 select deptno,avg(sal) as avg_sal,max(sal) max_sal, min(sal) min_sal,count(sal) count_sal,sum(sal) as sum_sal 4 from emp 5 group by deptno; SQL> select * from v3; DEPTNO AVG_SAL MAX_SAL MIN_SAL COUNT_SAL SUM_SAL ---------- ---------- ---------- ---------- ---------- ---------- 30 1550 2250 1050 6 9300 10 2550 4400 1400 3 7650 20 2475 4000 1200 5 12375 1 create view v4 2 as 3* select ename,sal from emp where sal>2000 SQL> / View created. SQL> select * from v4; ENAME SAL -------------------- ---------- SMITH 5000 JONES 3375 BLAKE 3250 CLARK 2850 SCOTT 3400 KING 5400 FORD 3400 7 rows selected. SQL> update v4 set sal=sal-1000; 7 rows updated. SQL> select * from v4; ENAME SAL -------------------- ---------- SMITH 4000 JONES 2375 BLAKE 2250 SCOTT 2400 KING 4400 FORD 2400 6 rows selected. SQL> create or replace view v4 2 as 3 select ename,sal,comm 4 from emp 5 where sal>2000 6 with check option 7 / View created. SQL> select * from v4; ENAME SAL COMM -------------------- ---------- ---------- SMITH 4000 JONES 2375 BLAKE 2250 SCOTT 2400 KING 4400 FORD 2400 6 rows selected. SQL> update v4 set sal=sal-1000; update v4 set sal=sal-1000 * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation }}} == sequence == {{{ SQL> create sequence s1; Sequence created. SQL> select user from dual; USER ------------- SCOTT SQL> select s1.currval from dual; CURRVAL ---------- 1 SQL> select s1.nextval from dual; NEXTVAL ---------- 2 SQL> / NEXTVAL ---------- 3 SQL> / NEXTVAL ---------- 4 1* insert into dept values(s1.nextval,'xxxx','xxxx') SQL> / 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 BEIJIN 1 xxxx xxxx SQL> create sequence s2 2 increment by 5 3 start with 1000 4 maxvalue 10000 5 cycle 6 cache 20 7 / Sequence created. SQL> select s2.nextval from dual; NEXTVAL ---------- 1000 SQL> / NEXTVAL ---------- 1005 SQL> / NEXTVAL ---------- 1010 SQL> alter sequence s2 increment by 1; Sequence altered. SQL> select s2.nextval from dual; NEXTVAL ---------- 1011 SQL> / NEXTVAL ---------- 1012 SQL> / NEXTVAL ---------- 1013 }}} == index == {{{ SQL> create index idx1 on emp(ename); Index created. SQL> column index_name format a10 SQL> column table_name format a20 SQL> select index_name,table_name,UNIQUENESS FROM user_indexes; INDEX_NAME TABLE_NAME UNIQUENESS ---------- -------------------- ------------------ PK_DEPT DEPT UNIQUE PK_EMP EMP UNIQUE IDX1 EMP NONUNIQUE SQL> select index_name,table_name,UNIQUENESS FROM user_indexes where INDEX_NAME='IDX1'; INDEX_NAME TABLE_NAME UNIQUENESS ---------- -------------------- ------------------ IDX1 EMP NONUNIQUE SQL> create index idx4 on emp(ename,job); Index created. SQL> create index idx2 on emp(sal); Index created. SQL> select index_name,table_name,UNIQUENESS FROM user_indexes; INDEX_NAME TABLE_NAME UNIQUENESS ---------- -------------------- ------------------ PK_DEPT DEPT UNIQUE PK_EMP EMP UNIQUE IDX1 EMP NONUNIQUE IDX4 EMP NONUNIQUE IDX2 EMP NONUNIQUE }}}