Oracle 기본

 

1. 테이블 생성 (PK, FK, CASCADE) 설정.

 

--DEPT 테이블 생성

CREATE TABLE DEPT (

DEPTNO VARCHAR2(4) PRIMARY KEY,

DEPTNAME VARCHAR2(20)

);

--DEPT 테이블에 데이터 입력

INSERT INTO DEPT VALUES ('1000','의료팀');

INSERT INTO DEPT VALUES ('1001','회계팀');

--EMP 테이블 생성 및 pk,fk,cascade 적용

CREATE TABLE EMP(

EMPNO NUMBER(10),

ENAME VARCHAR2(20),

SAL NUMBER(10,2) DEFAULT 0,

DEPTNO VARCHAR2(4) NOT NULL,

CREATEDATE DATE DEFAULT SYSDATE,

CONSTRAINT E_PK PRIMARY KEY(EMPNO),

CONSTRAINT D_FK FOREIGN KEY(DEPTNO)

REFERENCES DEPT(DEPTNO)

ON DELETE CASCADE

);

 

--EMP 테이블에 데이터 입력

INSERT INTO EMP VALUES(100,'홍길동',1000,'1000',SYSDATE);

INSERT INTO EMP VALUES(101,'이순신',2000,'1001',SYSDATE);

--DEPT에 DEPTNO - 1000을 삭제하였을때 EMP테이블에 DEPTNO - 1000짜리도 삭제 되는지 (CASCADE)적용 여부 확인.

DELETE FROM DEPT WHERE DEPTNO='1000';

 

2. 컬럼 변경 (테이블명, 컬럼명, 컬럼속성, 컬럼추가/삭제, 테이블삭제, 참조삭제)

--EMP 테이블 명을 NEW_EMP 변경

ALTER TABLE EMP RENAME TO NEW_EMP;

--AGE 컬럼 추가

ALTER TABLE EMP ADD (AGE NUMBER(2) DEFAULT 1);

--컬럼 변경 VARCHAR2(30)

ALTER TABLE EMP MODIFY (ENAME VARCHAR2(30) not null);

--AGE 컬럼 삭제

ALTER TABLE EMP DROP COLUMN AGE;

--컬럼명 변경 ENAME 를 NEW_NAME로 변경

ALTER TABLE EMP RENAME COLUMN ENAME TO NEW_NAME;

--테이블 삭제

DROP TABLE EMP;

--테이블에 참조된 제한사항까지 삭제

DROP TABLE EMP CASCADE CONSTRAINT;

 

3. VIEW 테이블 ( 보안성 향상,  데이터 관리 간단 , 데이터 구조 변경 불가)

--데이터에 관한 INSERT,UPDATE,DELETE문이 없음 // 생성 삭제만 존재함.

--생성

CREATE VIEW T_EMP AS

SELECT * FROM EMP;

--조회

SELECT * FROM T_EMP;

--삭제

DROP VIEW T_EMP;

 

4. INSERT 문 (DML문)

 

INSERT INTO EMP (EMPNO,ENAME) VALUES (1000,'임베스트');

--테이블에있는 모든 컬럼에 데이터를 삽입하는 경우 컬럼명 생략가능.

INSERT INTO EMP VALUES (1000,'임베스트');

--SELECT INSERT 문

INSERT INTO DEPT_TEST SELECT * FROM DEPT;

 

--Nologging

ALTER TABLE DEPT NOLOGGING;

 

5. UPDATE 문

 

UPDATE EMP

SET ENAME='이순신'

WHERE EMPNO=1000;

 

6. DELETE 문 

 

DELETE FROM EMP

WHERE EMPNO = 100;

 

 

SELECT TABLE_NAME, MAX_EXTENTS

FROM USER_TABLES;

--MAX_EXTENTS를 넘어서면 용량초과오류 발생 // 최대 저장할 수 있는 공간

 

--DELETE 문으로 삭제시 용량은 줄지않고 삭제여부만 표시됨.

--용량을 초기화하려면

TRUNCATE TABLE EMP;

 

7. SELECT 문 

 

SELECT * FROM EMP WHERE EMPNO=1000;

 

--아래 쿼리 조회시 모든 ENAME에 '님'이 붙어서 나온다.

SELECT ENAME||'님' FROM EMP;

 

 

8. 정렬 - SORT

-- EMPNO는 오름차순, SAL은 내림차순으로 정렬한다.

SELECT * FROM EMP ORDER BY EMPNO,SAL DESC;

 

--DEFAULT값은 오름차순(ASC/Ascending)이며 내림차순 필요시에는 DESC(Descending)을 써줘야한다.

--Index 를 사용한 정렬의 회피

CREATE TABLE EMP(

EMPNO NUMBER(100) PRIMARY KEY,

ENAME VARCHAR2(200),

SAL NUMBER(100)

);

INSERT INTO EMP VALUES(1000,'홍길동',2000);

INSERT INTO EMP VALUES(1001,'이순신',3000);

INSERT INTO EMP VALUES(1002,'임꺽정',2500);

--해당 데이터를 조회하면 EMPNO를 오름 차순으로 정렬되어서 조회한다.

SELECT * FROM EMP;

--EMPNO가 PRIMARY KEY이기 때문에 자동으로 ASC 인덱스가 생성된다.

 

--힌트를 사용하여 DESC로 조회하기

SELECT /*+ INDEX_DESC(A) */

FROM EMP A;

--ORDER BY DESC 를 사용하지않고 DESC 할 수 있다.

--힌트 추가 설명

--멀티라인 주석 ( /*+ */ )과 싱글라인 주석( --+ ) 모두 인덱스 힌트를 사용할 수 있다

--여러개의 복한 인덱스 힌트를 사용할 수 있다 ( /*+ INDEX(.....) INDEX(.....) */ )

 

9.  DISTINCT - 중복제거 / ALIAS(AS) - 별칭

--DISTINCT - 중복제거

SELECT DISTINCT(EMPNO) FROM EMP;

--ALIAS(AS) - 별칭

SELECT EMPNO AS "번호" FROM EMP;