학원에서 학습한 내용을 개인정리한 글입니다.

---

수업

inline view에 사용한 컬럼을 Main select문에서 사용

• () 안에 사용된 year_sal이 select문에서 사용이 되는 것을 확인할 수 있다

SELECT EMP_NAME, PHONE, YEAR_SAL
FROM(SELECT EMP_NAME, EMAIL, PHONE, SALARY*12 AS YEAR_SAL FROM EMPLOYEE);

집합 연산을 Inline view로 이용

SELECT *
FROM (
    SELECT EMP_ID AS ID, EMP_NAME AS NAME FROM EMPLOYEE
    UNION
    SELECT DEPT_ID, DEPT_TITLE FROM DEPARTMENT
    UNION
    SELECT JOB_CODE, JOB_NAME FROM JOB)
WHERE NAME LIKE '%장%';

다수의 Inline view

• 중첩에서 inline view 생성가능

--중첩에서 inline view 생성가능- level
SELECT A.*
FROM(SELECT T.*,
        (SELECT AVG(SALARY) FROM EMPLOYEE WHERE JOB_CODE = JOB_JOB_CODE) AS JOB_AVG_SAL
    FROM (SELECT SALARY * 12 AS YEAL_SAL, E.*, D.*, J.JOB_CODE AS JOB_JOB_CODE
            FROM EMPLOYEE E 
            JOIN DEPARTMENT D ON DEPT_CODE = DEPT_ID
            JOIN JOB J ON E.JOB_CODE = J.JOB_CODE)T
    )A;

RowNum 가상컬럼

SELECT E.*, ROWNUM
FROM(SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) E
WHERE ROWNUM BETWEEN 1 AND 5;

rank() over() dense_rank over()

• Rank() over(정렬 - order by 컬럼):동일값이 있으면 번호를 생략
  • -> 누락되는 번호가 있음

SELECT RANK() OVER(ORDER BY SALARY), 
    E.SALARY, 
    E.EMP_NAME,
    DENSE_RANK() OVER(ORDER BY SALARY) AS DENSE
FROM EMPLOYEE E;

• Dense_rank() over(order by 컬럼):동일값이 있어도 번호를 생략하지않음
  • -> 누락되는 번호가 없음

SELECT *
FROM (SELECT DEPT_CODE, AVG(SALARY), RANK() OVER(ORDER BY AVG(SALARY) DESC) AS AVG_ORDER
    FROM EMPLOYEE
    WHERE DEPT_CODE IS NOT NULL
    GROUP BY DEPT_CODE)
WHERE AVG_ORDER <= 3;

with 구문

• select 문 안에서 사용할 Select 문을 저장하여 재사용하는 구문

WITH
    TEST_WITH AS (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC),
    TEST_WITH2 AS (SELECT * FROM EMPLOYEE),
    TEST_WITH3 AS (SELECT * FROM DEPARTMENT)
SELECT *
FROM TEST_WITH
MINUS
SELECT EMP_NAME, SALARY
FROM TEST_WITH2
WHERE DEPT_CODE = 'D5'
UNION
SELECT DEPT_ID, 0
FROM TEST_WITH3;

DB 설계에 대해 생각해보자

DDL(Data Definition Language)

• 데이터 정의 언어
• 객체 object를 만들고 create 수정하고 alter 삭제 drop 하는 구문

오라클 데이터형

1. CHAR(크기) 고정길이 문자 데이터
2. VARCHAR2(크기) 가변길이 문자 데이터(최대 4,000 Byte)
3. NUMBER 숫자 데이터(최대 40자리)
4. NUMBER(길이) 숫자 데이터로, 길이 지정 가능 (최대 38자리)
5. DATE 날짜 데이터(BC 4712년 1월 1일 ~ AD 4712년 12월 31일)
6. LONG 가변 길이 문자형 데이터(최대 2GB)
7. LOB 2GB까지의 가변길이 바이너리 데이터 저장 가능
(이미지, 실행파일 등 저장 가능)
8. ROWID DB에 저장되지 않는 행을 식별할 수 있는 고유 값
9. BFILE 대용량의 바이너리 데이터 저장 가능(최대 4GB)
10. TIMESTAMP DATE형의 확장된 형태
11. INTERVAL YEAR TO MONTH 년과 월을 이용하여 기간 저장
12. INTERVAL DAY TO SECOND 일, 시, 분, 초를 이용하여 기간 저장

CREATE

• 테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문

--테이블은 create table 명령어를 이용해서 생성
--데이터를 저장하는 저장소
--데이터는 컬럼에 저장 -> 컬럼 값에 맞는 타입을 설정

--테이블을 생성하는 명령어
--create table 테이블명(
--컬럼명 타입명 [(길이)][제약조건]
--컬럼명2 타입명2[(길이)][제약조건]
--[제약조건]
--)

데이터 추가해보기

• 문자 데이터
  • 데이터 생성 후 확인시 Char은 남은 공간이 공백으로 메꿔진 것을 볼 수 있다
  • 문자열을 쓸 때 완전히 고정된 값이 아니라면 되도록 varchar을 추천
  • 한글은 3byte로 처리 참고..
  • varchar의 경우는 공간이 6이라고 했을때, 한글을 쓰면 최대 두글자까지만 가능하다.
  • nvarchar의 경우 글자당으로 처리하기때문에 한글이어도 여섯글자가 가능하다

CREATE TABLE TBL_STR(
    A CHAR(6),
    B VARCHAR(6),
    C NVARCHAR2(6)
);
DESC TBL_STR;

INSERT INTO TBL_STR VALUES('ABC','ABC','ABC');
SELECT *
FROM TBL_STR;

숫자 자료형

--number: 정수, 실수 모두 저장하는 타입
--number[(저장 가능한 자리수 소수점 자리수)]
--지정가능한 자리수 1~38
--소수점 -84~ 127 가능

CREATE TABLE TBL_NUM(
    A NUMBER, --정수, 실수
    B NUMBER(3),--정수만
    C NUMBER(3, 1), -- 정수, 실수(한자리만)
    D NUMBER(3, -1), -- 정수만, 일의 자리를 0
    E NUMBER(32)-- 정수 자리수 32
);

날짜 지정해서 저장

--Date 년 월 일
--TimeStamp 년월일시분초 저장

CREATE TABLE TBL_DATE(
    BIRTH_DAT DATE,
    RENT_START TIMESTAMP
);

DESC TBL_DATE;

INSERT INTO TBL_DATE VALUES(SYSDATE, SYSTIMESTAMP);
SELECT * FROM TBL_DATE;

--날짜 지정해서 저장하기
--1. 문자열로 저장 -> 특정패턴으로 맞춰서 저장
--yy/mm/dd YY/MM/DD HH:MI:SS
--2. to_date / to_timestamp 함수
--문자 숫자 날짜로 변환해서 사용

INSERT INTO TBL_DATE VALUES('00/09/03', '00/11/04 10:30:22');
SELECT * FROM TBL_DATE;
INSERT INTO TBL_DATE VALUES(TO_DATE('99-06-17', 'RR-MM-DD'), TO_TIMESTAMP('99-04-27 03:33:33', 'RR-MM-DD HH:MI:SS'));

많은 문자열 저장할때

--많은 문자열 저장할 때
--varchar2: 최대 40000바이트까지 저장이 가능
--clob(2gb)를 이용해서 저장 

CREATE TABLE TBL_STR3(    
    TEST_STR VARCHAR(40000),
    TEST_CLOB CLOB
);

테이블 생성시 컬럼에 제약조건 설정하기

--Not null: 컬럼에 null값을 허용하지 않을때 * 기본적으로 컬럼은 Nullable상태로 생성이됨
--Unique: 컬럼에 중복값을 허용하지 않을때 
--Primary key: 컬럼에 중복값이 없고, null값이 없는 컬럼 중 하나는 선택해서 설정 * Row를 구분하는 기준으로 사용
--Foreign key: 다른 테이블의 컬럼에 있는 값만 저장이 가능하게 설정 * 데이터의 일관성, 참조 무결성을 해결하기 위해 설정
--check: 지정된 값만 저장될 수 있게 하는 설정
--default: 저장시 컬럼에 값을 설정하지 않았을 때 저장되는 기본값

--제약조건을 설정하는 방식
--1. 컬럼레벨에서 설정: 단일컬럼에 제약조건을 설정할때 컬럼선언부 옆에 설정
--2. 테이블레벨에서 설정: 
--create table table명 (
--컬럼명 타입 제약조건 설정(컬럼레벨)
--제약조건 설정
--)

nullable

CREATE TABLE CONS_MEMBER(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(50),
    MEMBER_PWD VARCHAR2(50)
);

SELECT * FROM CONS_MEMBER;
INSERT INTO CONS_MEMBER VALUES(1,'ADMIN','1234');
INSERT INTO CONS_MEMBER VALUES(NULL, NULL, NULL);

• nullable 에 yes 라고 되어있다. 이걸 not null로 하는 방법은?

not null(c)

• 제약조건 null값을 허용하지 않을 컬럼에 설정

--NOT NULL(C)  
--NOT NULL컬럼별로 설정가능
--다수로 설정할 수 없다 테이블레벨에서 설정 불가

CREATE TABLE CONS_MEMBER_NN(
    MEMBER_NO NUMBER NOT NULL,
    MEMBER_ID VARCHAR2(50) NOT NULL,
    MEMBER_PWD VARCHAR2(50)
);

INSERT INTO CONS_MEMBER_NN VALUES(1,'ADMIN', '1234');
INSERT INTO CONS_MEMBER_NN VALUES(NULL,'ADMIN', '1234');
INSERT INTO CONS_MEMBER_NN VALUES(2,'USER01', NULL);
SELECT * FROM CONS_MEMBER_NN;

unique(u)

• 컬럼에 중복데이터를 저장할 수 없게 하는 제약조건
• 중복값을 넣었을 때 오류가 뜬다

CREATE TABLE CONS_MEMBER_UQ(
    MEMBER_NO NUMBER UNIQUE,
    MEMBER_ID VARCHAR2(50) UNIQUE,
    MEMBER_PWD VARCHAR2(50) NOT NULL
);

SELECT * FROM CONS_MEMBER_UQ;

INSERT INTO CONS_MEMBER_UQ VALUES(1, 'ADMIN', '1234');
INSERT INTO CONS_MEMBER_UQ VALUES(1, 'USER01', '1234');

다수컬럼에 unique 제약조건 설정

CREATE TABLE CONS_MEMBER_UQ2(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(50),
    MEMBER_PWD VARCHAR2(50),
    UNIQUE(MEMBER_NO, MEMBER_ID) --AND 로 묶인 그룹으로 생각해야함. no 와 id 을 같이 확인
);

INSERT INTO CONS_MEMBER_UQ2 VALUES(1, 'ADMIN', '1234');
INSERT INTO CONS_MEMBER_UQ2 VALUES(1, 'ADMIN', '4444');
INSERT INTO CONS_MEMBER_UQ2 VALUES(2, 'ADMIN', '4444');

primary key(p) 설정하기

• 설정된 컬럼은 중복값, null이 저장되지 않는다
• 컬럼에 not null, unique 제약조건이 설정된 것과 동일함

--테이블에 필요한 컬럼에 설정하기 -> 회원 테이블에 Id, email
--테이블에 필요하지 않은 컬럼에 설정 -> 회원 테이블에 회원번호
--일반적으로 테이블당 한개의 pk를 설정

CREATE TABLE CONS_MEMBER_PK(
    MEMBER_NO NUMBER PRIMARY KEY,
    MEMBER_ID VARCHAR2(50),
    MEMBER_PWD VARCHAR2(50),
    MEMBER_NAME VARCHAR2(50)
);

INSERT INTO CONS_MEMBER_PK VALUES(1,'admin', '124', '관리자');
INSERT INTO CONS_MEMBER_PK VALUES(null,'admin', '124', '관리자'); --오류
INSERT INTO CONS_MEMBER_PK VALUES(1,'admin', '124', '관리자');-- 오류
INSERT INTO CONS_MEMBER_PK VALUES(2,'user1', '124', '유저1');

다수컬럼 Primary key로 설정

• 복합키

 CREATE TABLE CONS_MEMBER_PK3(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(50),
    MEMBER_PWD VARCHAR2(50) NOT NULL,
    MEMBER_NAME VARCHAR2(50) NOT NULL,
    PRIMARY KEY(MEMBER_NO, MEMBER_ID)
);

SELECT * FROM CONS_MEMBER_PK3;
INSERT INTO CONS_MEMBER_PK3 VALUES(1, 'ADMIN', '1234', '관리자');
INSERT INTO CONS_MEMBER_PK3 VALUES(1, 'ADMIN', '4444', '관리');

FOREIGN key

• 칼럼에 저장되는 데이터가 다른 테이블에 있는 값만 가능하게 설정

--참조관계
--부모테이블 <-> 자식테이블로 구분할 수있음
--값을 전달     값을 참조
--학생    <-> 수강신청 테이블 <-> 과목, 회원 <- 댓글 -> 게시글
--컬럼레벨, 테이블레벨에서 설정이 가능
--참조관계를 설정할때 주의 
--참조하는 테이블의 컬럼은 primiary key, unique 제약조건이 설정되어야한다
--참조한 컬럼에 여러개의 테이블 값을 참조할 수 없다 -> 한개만 가능

CREATE TABLE CONS_BOARD(
    BOARD_NO NUMBER PRIMARY KEY,
    BOARD_TITLE VARCHAR2(100) NOT NULL,
    BOARD_CONTENT VARCHAR2(2000)
);

CREATE TABLE CONS_COMMENT(
    COMMENT_NO NUMBER PRIMARY KEY,
    COMMENT_COMMENT VARCHAR2(2000),
    BOARD_REF REFERENCES CONS_BOARD(BOARD_NO)
);

INSERT INTO CONS_BOARD VALUES(1, 'ORACLE VERY EASY', NULL);
INSERT INTO CONS_BOARD VALUES(2, 'CHEER UP SANG,', ' BLESS YOU!');
SELECT * FROM CONS_BOARD;
INSERT INTO CONS_COMMENT VALUES(1, 'EZ', 1);
INSERT INTO CONS_COMMENT VALUES(2, 'NO ITS HARD', 2);
SELECT * FROM CONS_COMMENT;
INSERT INTO CONS_COMMENT VALUES(3, 'IM NEW', 3); -- 부모 키가 없어서 오류 발생

NULL 값은 가능..이건 따로 not null로 설정해주던가 해야함

--참조컬럼에 Null값 넣기
INSERT INTO CONS_COMMENT VALUES(4, 'IM NEW !!', NULL);

앞선 DB처럼 조회

SELECT * FROM CONS_BOARD
JOIN CONS_COMMENT ON BOARD_REF = BOARD_NO;

• 참조관계가 설정된 부모 테이블의 데이터는 함부로 삭제할 수 없다

 DELETE FROM CONS_BOARD WHERE BOARD_NO = 1;

참조 제약조건을 설정할때 삭제에 대한 옵션

• on delete cascade
  • 부모 테이블의 참조 데이터가 삭제되면 같이 삭제
• on delete set null

  • 부모 테이블의 참조 데이터가 삭제되면 컬럼을 NUlL값으로 변경 컬럼에 not null 제약조건 설정하면 안됨

    CREATE TABLE CONS_COMMENT(
        COMMENT_NO NUMBER PRIMARY KEY,
        COMMENT_COMMENT VARCHAR2(2000),
        --BOARD_REF REFERENCES CONS_BOARD(BOARD_NO) ON DELETE CASCADE
        BOARD_REF REFERENCES CONS_BOARD(BOARD_NO) ON DELETE SET NULL
    );

참조 컬럼에 두개 테이블 값을 설정할 수 없다

CREATE TABLE CONS_TEST(
    TEST_NO NUMBER,
    NUMBER_REF NUMBER REFERENCES CONS_BOARD(BOARD_NO) REFERENCES CONS_MEMBER_PK(MEMBER_NO)
); -- NO

참조 대상이 되는 컬럼은 반드시 Pk, unique 제약조건 설정 필요

--참조 대상이 되는 컬럼은 반드시 Pk, unique 제약조건이 설정되어야한다 
CREATE TABLE CONS_REFTEST(
    MEMBER_NO NUMBER REFERENCES CONS_MEMBER(MEMBER_NO)
);

check(c) 제약조건

• 원하는 데이터만 저장할 수 있게 설정

--CHECK (조건문) True 데이터만 저장
CREATE TABLE CONS_PERSON_CK(
    PERSON_NAME VARCHAR2(20),
    PERSON_AGE NUMBER CHECK(PERSON_AGE > 10),
    PERSON_GENDER VARCHAR2(5) CHECK(PERSON_GENDER IN ('W', 'M'))
);

INSERT INTO CONS_PERSON_CK VALUES('K', 9, 'W'); --NO
INSERT INTO CONS_PERSON_CK VALUES('K', 11, 'W'); --YES

💡

QNA

Temi: 오라클을 깔고 로그인을 하고 데이터베이스를 수정하거나 조회하는 과정 전체에 대해서 의문 설명부탁

Teacher :

💡

QNA

Temi: DB를 이전하는 경우도 있는지

Teacher : 꽤 있다 DB가 비싸서 옮기기로했다던지, 기존 가지고있던 엑셀파일을 정리해서 새로운 DB로 올린다던지 함

---

미리 준비

• 실습과제
• 이번에야말로 진짜 백앤드배포..

---

느낀점

• 디비를 건드린다는게 대체 어떤느낌이지? 했는데 오늘 설명을 들어서 좀 좋당
• 점심은 너무 달고 건강에 안좋은걸 먹지않는게 좋겟다 졸리다.

---