higher's note

CREATE TABLE DEPTX(
DEPTNO                                    NUMBER(2),                                     =>NUMBER(2) PRIMARY KEY 하면 맨 밑에 거 안 써도 돼.
DNAME                                              VARCHAR2(14),                               :COLUMN LEVER CONSTRATINT  (이름이 'SYS_Cn' 이런 식으로 잡혀.) 
LOC                                                VARCHAR2(13),
CONSTRAINT DEPTX_DEPTNO_PK PRIMARY KEY(DEPTNO)
);

 

CREATE TABLE EMPX(
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),
CONSTRAINT EMPX_EMPNO_PK PRIMARY KEY(EMPNO),
CONSTRAINT EMPX_DEPTNO_FK FOREIGN KEY(DEPTNO)

REFERENCE DEPTX(DEPTNO)

);

 

COLUMN LEVEL  : IN-LINE 방식

EX, DEPTNO NUMBER(2) PRIMARY KEY

TABLE LEVEL : OUT-LINE방식

 

-NN(NOT NULL)

-UX(UNIQUE)

-PK(PRIMARY KEY)

-FK(FOREIGN KEY)

-CK(CHECK)

 

 SELECT INSTR('ABCDE','@') FROM DUAL;

              :몇번째에 있나?

 

CREATE TABLE TA1(ID NUMBER(7), NAME VARCHAR2(20), EMAIL VARCHAR2(30),
   CONSTRAINT TAL_EMAIN_CK CHECK(INSTR('ABDCD', '@') >0 ) );

 

 SELECT 'DROP TABLE"'||TNAME||'"CASCADE CONSTRAINT;' FROM TAB
 WHERE TNAME NOT IN('BONUS','DEPTX','EMPX','SALGRADE','DEPT2');

->복사해서 실행시켜 지워

->PURGE RECYCLEBIN;  휴지통 비워

->SELECT * FROM TAB; 확인하면 안 지울 거 빼고 다 지워져.

 

chap10

 

SQL> CREATE TABLE T1(ID NUMBER(6), NAME VARCHAR2(20),
  2             CONSTRAINT T1_ID_PK PRIMARY KEY(ID) );

테이블이 생성되었습니다.

SQL> CREATE SEQUENCE T1_S;

시퀀스가 생성되었습니다.

SQL> INSERT INTO T1 VALUES(T1_S.NEXTVAL, '이은미');

1 개의 행이 만들어졌습니다.

SQL> INSERT INTO T1 VALUES(T1_S.NEXTVAL, '이태환');

1 개의 행이 만들어졌습니다.

SQL> SELECT * FROM T1;

        ID NAME
---------- --------------------
         1 이은미
         2 이태환

 

SELECT T1_S.CURRVAL FROM DUAL; 현재 일련번호 묻는 질의

 

*INDEX

(기본예) CREATE INDEX EMP_SAL_IX ON EMP(SAL);

 

SELECT ENAME, SAL FROM EMP
WHERE SAL = 3000;

 빨라...빨라!

 

CREATE VIEW EMPPUB AS SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP;

 

create view empa as select empno, sal * 12 "annual sal" from emp;

10-7

OR REPLACE : 있으면 지우고 다시 생성

FORCE :

create force view xxx as select * from xx;

 

VIEW의 범위를 벗어나는 입력 안 받아.

 

WITH CHECK OPTION

 

 CREATE VIEW EMPK2(ENAME, ANNSAL) AS SELECT ENAME, SAL*12 FROM EMP;

 

 SELECT  'DROP TABLE "'||TNAME||'" CASCADE CONSTRAINT;' FROM TAB

WHERE TNAME NOT IN

('DEPT', 'EMP', 'BONUS', 'SALGRADE');

 

나오는 거 실행시켜.

 

CURRVAL : SESSION(접속) 단위로 작동

NEXTVAL : INSTANCE(DB) 단위로 작동

 

 CREATE SEQUENCE S INCREMENT BY 2; <-2씩 늘어나.

 

CREATE SEQUENCE S2
INCREMENT BY 2
START WITH 3
MAXVALUE 8
MINVALUE 2
NOCACHE

 

10-29

ALTER SEQUENCE     

 

10-30

SEQUENSE 찾는 법

SELECT SEQUENCE_NAME FROM USER_SEQUENCES;

 

SELECT INDEX_NAME FROM USER_INDEXES;

->PK, UNIQUE 자동으로 인덱스 만들어

 

SELECT COMM, ROWID FROM EMP WHERE COMM IS NOT NULL

ORDER BY COMM;

->생성단가가 적어서 공간 조금만 들어가.

 

CHAP11

USER / ALL / DBA - 객체(E)S

소유한  접근 가능한 

 

SELECT VIEW_NAME FROM DBA_VIEWS
WHERE VIEW_NAME LIKE '%CONS%';

                                            '&DATAFILE%';

DESC DBA_CONSTRAINTS;

 

 SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM DBA_CONSTRAINTS
WHERE OWNER='HR';

 

 11-20

SELECT TABLE_NAME, COMMENTS FROM DICTIONARY

WHERE TABLE_NAME LIKE 'DBA%USER%'

아래거랑 같아

SELECT VIEW_NAME FROM DBA_VIEWS

WHERE VIEW_NAME LIKE '%DATAFILE%';