[ORACLE] USSOFT ORACLE 교육 1일차

TABLE 생성

CREATE TABLE STUDENTS(
	NO_SEQ NUMBER(11),
	DS_NAME VARCHAR2(100),
	NO_SEX NUMBER(1),
	NO_AGE NUMBER(10),
	NO_GRADE NUMBER(1),
	NO_CLASS NUMBER(1),
	PRIMARY KEY(NO_SEQ)
);

SEQUENCE TABLE 생성

CREATE SEQUENCE STUDENTS_SEQ;

INSERT 구문 실행

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '이현식', 0, 15, 2, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '김영서', 0, 30, 2, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '박지수', 0, 27, 2, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '김예지', 0, 29, 1, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '이혜수', 0, 23, 3, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '채지원', 0, 24, 2, 1);

전체 조회

SELECT * FROM STUDENTS;

UNIQUE INDEX 생성

CREATE UNIQUE INDEX IDX_STUDENTS ON STUDENTS(NO_GRADE, NO_CLASS, DS_NAME, NO_SEX, NO_AGE);

📌UNIQUE INDEX란?

UNIQUE INDEX는 INDEX를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이있다.
PRIMARY KEY와 UNIQUE 제약 조건시 생성되는 인덱스는 UNIQUE INDEX이다.

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '이현식', 0, 15, 2, 3);

UNIQUE INDEX 생성 후 위와 같은 INSERT 구문을 실행 시 아래의 결과를 도출한다.

DML 뒤에 DDL 실행 시 자동 커밋 진행

insert into hs_students (no_seq, ds_name, no_sex, no_age, no_grade, no_class)
values (STUDNETS_SEQ.NEXTVAL, '박지수', 0, 15, 2, 1);

CREATE TABLE TEMP_STUDENT2 AS
SELECT * FROM students;

임시 TABLE 생성 (TABLE 복사)

CREATE TABLE TEMP_STUDENTS AS 
	SELECT * FROM STUDENTS s;

📌DDL, DML, DCL 이란?

DDL

SCHEMA, DOMAIN, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어
데이터 베이스 관리자나 데이터베이스 설계자가 사용

• CREATE
• ALTER
• DROP

DML

데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어
데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공

• SELECT
• INSERT
• UPDATE
• DELETE

DCL

데이터를 제어하는 언어
데이터의 보안, 무결성, 회복, 병행 수행제어 등을 정의하는데 사용

• GRANT
• REVOKE
• COMMIT
• ROLLBACK

ORDER BY 를 이용한 정렬

SELECT NO_SEQ, DS_NAME, NO_SEX FROM STUDENTS WHERE NO_CLASS=1 ORDER BY NO_SEQ;

같은 결과값을 가지는 쿼리, 효율성은 다르다?

1. CASE WHEN THEN ELSE 첫번째

select NO_SEQ, DS_NAME, CASE NO_SEX 
    WHEN 0 THEN '남자' 
    WHEN 1 THEN '여자'
    ELSE ''
    end ds_sex
    FROM students
    where no_class = 1
    order by no_seq;

2. CASE WHEN THEN ELSE 두번째

SELECT NO_SEQ, DS_NAME,
    CASE WHEN NO_SEX = 0 THEN '남자'
         WHEN NO_SEX = 1 THEN '여자'
         ELSE ''
         END DS_SEX
         FROM students
         where no_class= 1
         order by no_seq;

3. DECODE 사용

SELECT NO_SEQ,
       DS_NAME,
       DECODE(NO_SEX, 0, '남자', 1, '여자', '') DS_SEX
       FROM students
       where no_class = 1
       order by no_seq;

4. 5번과 같은 서브쿼리지만 더 효율적인 방법

SELECT NO_SEQ,
       DS_NAME,
       DECODE(NO_SEX, 0, '남자', 1, '여자', '') DS_SEX
       FROM students A,
       (
        SELECT 0 CD_SEX, '남자' DS_SEX FROM DUAL UNION ALL
        SELECT 1 CD_SEQ, '여자' DS_SEX FROM DUAL
        )B
        WHERE A.NO_CLASS = 1
        AND A.NO_SEX = B.CD_SEX
        ORDER BY A.NO_SEQ;

5. 서브쿼리 각 행마다 SELECT 를 입히기 때문에 효율적이지 못함

SELECT NO_SEQ,
        DS_NAME,
        (
            SELECT DS_SEX
            FROM(
            SELECT 0 CD_SEX, '남자' DS_SEX FROM DUAL UNION ALL
            SELECT 1 CD_SEQ, '여자' DS_SEX FROM DUAL
            )
            WHERE CD_SEX = STUDENTS.NO_SEX
            ) DS_SEX
            FROM students
            WHERE NO_CLASS = 1
            ORDER BY NO_SEQ;

6. DUAL을 활용한 검색

SELECT NO_SEQ,
       DS_NAME,
       DECODE(NO_SEX, 0, '남자', 1, '여자', '') DS_SEX
       FROM students A,
       (
        SELECT 0 CD_SEX, '남자' DS_SEX FROM DUAL UNION ALL
        SELECT 1 CD_SEQ, '여자' DS_SEX FROM DUAL
        )B
        WHERE A.NO_CLASS = 1
        AND A.NO_SEX = B.CD_SEX
        ORDER BY A.NO_SEQ;

7. 새로운 TABLE 생성을 통한 검색

SELECT A.NO_SEQ,
    A.DS_NAME,
    A.DS_SEX,
    FROM students A,
    codes b
    where a.no_class =1
    and a.no_sex = b.cd_sex
    order by a.no_seq;