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