221114 TIL - 정리중

// 정리해서 다시 올릴 것

정미나 유튜브

CREATE USER TEST IDENTIFIED BY 1234;

GRANT CREATE SESSION TO TAMI;

GRANT CREATE TABLE, RESOURCE TO TAMI;

CREATE TABLE SUPERBAND_MEMBER (

SEQ NUMBER(3);

NAME VARCHAR(30); -- 한글 한글자 3바이트

POSITION VARCHAR(100);

FINAL_YN VARCHAR(1);

);

INSERT INTO superband_member VALUES ('1', '아일', 'VOCAL, PIANO', 'Y');

SELECT * FROM superband_member;

SELECT DISTINCT COMPANY , COUNT() FROM IDOL_GROUP GROUP BY COMPANY HAVING COUNT() = 2

SELECT * FROM IDOL_GROUP WHERE DEBUT_YEAR < '2015' AND GENDER = 'GIRL' ORDER BY DEBUT_YEAR DESC

INNER JOIN

A, B = alias(별명)

SELECT * FROM idol_group A, idol_member B WHERE a.group_name = b.group_name

• • a와 b의 그룹네임이 같은 멤버들이 나온다. 양쪽 칼럼 전부 출력한다.
• • 교집합 느낌

SELECT a.company, a.group_name, b.member_name, b.real_nameFROM idol_group A, idol_member B WHERE a.group_name = b.group_name

SELECT a.company, a.group_name, COUNT(b.member_name) COUNTFROM idol_group A, idol_member B WHERE a.group_name = b.group_name GROUP BY a.company, a.group_name

OUTER JOIN

• - SELECT * FROM idol_group A, idol_member B WHERE a.group_name = b.group_name;- 여기까지가 INNER JOIN
• -- A를 기준으로 B 를 OUTER JOIN 하면- 양쪽 테이블에서 그룹네임 겹치는거 나오고, 추가로 A테이블 나머지가 마저 나온다.
• • INNER JOIN + 기준테이블 나머지SELECT  FROM idol_group A, idol_member B WHERE a.group_name = b.group_name(+);- = A를 기준으로 B를 LEFT OUTER JOIN 하세요SELECT  FROM idol_group A LEFT OUTER JOIN idol_member B ON a.group_name = b.group_name;

UPDATE idol_member SET real_name = '조미연' WHERE member_name = '미연';UPDATE idol_member SET real_name = '예슈화', sns_info = 'V LIVE, 인스타그램' WHERE member_name = '수화';

COMMIT; -- GIT이랑 비슷한 느낌인듯?ROLLBACK; -- 방금 한거 되돌리기

• - VALUES 이전에 딱히 칼럼 지정을 하지 않았다면 전체 칼럼을 순서대로 넣어준다INSERT INTO idol_group VALUES ('JYP 엔터테인먼트', 'ITZY', '2019', 'ITz DIFFERENT', 'girl');- TABLE DISCRIPTION 에서 칼럼이 NULLABLE이 NO면 값이 필수로 있어야 한다.INSERT INTO idol_group (COMPANY, GROUP_NAME) VALUES ('테스트 소속사', '예비 아이돌 그룹');

CREATE TABLE idol_group_copy AS SELECT * FROM idol_group;

• • 테이블 복사(기본키, 외래키, 인덱스와 같은 테이블의 속성값들은 복사가 되지 않고 컬럼들과 데이터들만 복사가 됨)
• • WHERE 절 써서 일부만 복사 할 수 있음
• • WHERE 1=2; 하면 항상 거짓이 되니 데이터들은 복사되지 않고 껍데기 컬럼들만 복사해서 가져올 수 있다.

DELETE FROM idol_group_copy -- 여기까지만 쓰고 WHERE절 안적으면 싹다 삭제되니 주의!!

WHERE group_name = 'Wanna One' OR (company = 'SM 엔터테인먼트' AND GENDER = 'boy');

ROLLBACK; -- 되돌리기

TRUNCATE TABLE IDOL_GROUP_COPY;

• • DELETE는 log기록을 남기지만, TRUNCATE는 기록을 남기지 않는다. 그래서 속도는 빠르지만, ROLLBACK이 불가능
• • 절대 되살릴 일 없는 대량의 데이터만 TRUNCATE 한다
• • 날짜별 그룹바이SELECT ORDER_DT, COUNT(*) FROM starbucks_order GROUP BY ROLLUP(order_dt) ORDER BY order_dt;

• • 주문음료별SELECT ORDER_ITEM, COUNT () FROM starbucks_order GROUP BY ROLLUP(order_item) ORDER BY COUNT() DESC;- 파트너(REG_NAME)별SELECT REG_NAME, COUNT() FROM starbucks_order GROUP BY ROLLUP(reg_name) ORDER BY COUNT() DESC;

SELECT ORDER_DT, ORDER_ITEM, COUNT(*) FROM starbucks_order GROUP BY ROLLUP(ORDER_DT, ORDER_ITEM);

SELECT ORDER_DT, ORDER_ITEM, REG_NAME, COUNT(*) FROM starbucks_order

GROUP BY ROLLUP(ORDER_DT, ORDER_ITEM, REG_NAME);-- ROLLUP(ORDER_DT, ORDER_ITEM, REG_NAME)-- ORDER_DT, ORDER_ITEM, REG_NAME 로 묶어서 해당날짜 해당메뉴 판매 총합 (REG_NAME들의 합)-- ORDER_DT, ORDER_ITEM 으로 묶어서 해당날짜 판매 총합 (ORDER_ITEM들의 합)-- ORDER_DT 로 묶어서 전체판매 총합 (ORDER_DT들의 합)

보통 테이블 - 데이터가 흩어져 저장되어있어 검색시 FULL SCAN 해야해서 오래걸림

인덱스(테이블과 매핑) - 정렬되어 저장된다, 속도가 빠름

인덱스로 데이터를 찾고 테이블로 매핑된 곳을 나머지 데이터들을 꺼내온다

WHERE절, ORDER BY절에 자주 등장하는 칼럼을 인덱스로 지정하면 효율적이다

단일인덱스, 결합인덱스(SELECT에 등장하는 컬럼들을 인덱스 구성해두면 효율적이다)

WHERE절에서 EQUAL조건으로 많이 쓰이는 컬럼이 앞으로 오는것이 효율적이다

INDEX는 OBJECT - SELECT는 빨라질 지 몰라도 INSERT나 UPDATE는 오히려 느려진다(정렬을 지켜줘야해서)

CREATE INDEX IDX_SB1 ON STARBUCKS_ORDER(REG_NAME); -- 인덱스 생성

DROP INDEX IDX_SB1; -- 인덱스 삭제

SELECT ORDER_DT, COUNT(), RANK()OVER(ORDER BY COUNT()DESC) AS RANKFROM starbucks_order GROUP BY order_dt;

SELECT ORDER_DT, COUNT(), DENSE_RANK()OVER(ORDER BY COUNT()DESC) AS RANKFROM starbucks_order GROUP BY order_dt;

SELECT ORDER_DT, COUNT(), ROW_NUMBER()OVER(ORDER BY COUNT()DESC) AS RANKFROM starbucks_order GROUP BY order_dt;

순위 함수 별 차이점 알아두자!

RANK - 동점은 같은순위로 하되 전체 등수는 유지 12225

DENSE_RANK - 동점은 같은순위로 하고 다음부터 이어서 12223 // DENSE : 밀집

ROW_NUMBER - 동점도 그냥 다 순서대로 매김 12345

SELECT ORDER_DT, ORDER_ITEM , COUNT(), RANK()OVER(PARTITION BY ORDER_DT ORDER BY COUNT() DESC)

AS RANK FROM starbucks_order GROUP BY ORDER_DT, ORDER_ITEM ORDER BY ORDER_DT;-- 날짜별로 파티션 나눠서 각 날짜마다 COUNT(*)로 순위매겨라

SELECT group_name,        CASE GENDER WHEN 'boy' THEN '남' WHEN 'girl' THEN '여' ELSE '혼성' END GRNDER_KOREANFROM idol_group;

or

SELECT group_name,        CASE WHEN GENDER = 'boy' THEN '남'             WHEN GENDER = 'girl' THEN '여'             ELSE '혼성'        END GRNDER_KOREAN2FROM idol_group;

or

SELECT group_name,        DECODE(GENDER, 'boy', '남', 'girl', '여', '혼성') GENDER_KOREAN3FROM idol_group;

쿼리 작성할 때는

SELECT FROM WHERE GROUP BY HAVING ORDER BY

로 작성하지만, 실행순서는

FROM - 테이블 존재유무, 권한체크(semantic error), 구문오류(syntax error),

WHERE - 조건체크해서 가져옴

GROUP BY - 가져온 row들을 그룹화

HAVING - 그룹화 한 것들 중에서 조건체크해서 가져옴

SELECT - 가져온 row들 중에서 어떤 column들을 출력할지

ORDER BY - 출력할 때 정렬

(ORDER BY절은 SELECT절 보다 늦게 수행된다)

(SELECT절에서 alias를 지정해 놓았을 경우에 ORDER BY 절에서 사용 가능)

(GROUP BY절은 SELECT절 보다 먼저 수행된다)

(SELECT절에서 alias를 지정해 놓았을 경우에 GROUP BY 절에서 사용 불가)

FOREIGN KEY -> 실무에선 테이블들이 굉장히 많아서 복잡하기때문에 지양하는 편이라고 하지만, 그래도 중요한 개념

CREATE TABLE PARENT (    P_ID VARCHAR2(2) NOT NULL);ALTER TABLE PARENT ADD CONSTRAINT P_PK PRIMARY KEY(P_ID);-- PK명(INDEX NAME)을 P_PK로 짓고 P_ID 컬럼을 PRIMARY KEY로 설정

CREATE TABLE CHILD (    C_ID VARCHAR2(2) NOT NULL,    P_ID VARCHAR2(2) -- 부모의 P_ID를 참조해(?) FOREIGN KEY 로 지정할 예정);ALTER TABLE CHILD ADD CONSTRAINT C_PK PRIMARY KEY(C_ID);ALTER TABLE CHILD ADD CONSTRAINT C_FK FOREIGN KEY(P_ID) REFERENCES PARENT (P_ID);

INSERT INTO PARENT VALUES ('A');INSERT INTO CHILD VALUES ('a', 'A');INSERT INTO CHILD VALUES ('b', 'B');-- 부모 테이블에 B가 없으니까 자식 테이블에 B를 가진 데이터를 삽입 할 수 없다

• • 참조 무결성에 위배

DELETE FROM PARENT WHERE P_ID = 'A';-- 자식이 부모의 A를 참조하고있기 때문에 삭제할 수 없다-- 참조 무결성에 위배

ALTER TABLE CHILD ADD CONSTRAINT C_FK FOREIGN KEY(P_ID) REFERENCES PARENT(P_ID) ON DELETE CASCADE;-- ON DELETE CASCADE : 자식이 부모를 외래키로 참조할 때, 부모 데이터가 삭제되면 연결된 자식 데이터도 삭제

• • CASCADE : 부모 데이터 삭제시 자식 데이터도 같이 삭제 (위험해서 잘 안쓴다!)
• • SET NULL : 부모 데이터 삭제시 자식 데이터 해당 필드 NULL로 UPDATE
• • SET DEFEULT : 부모 데이터 삭제시 자식 데이터 해당 필드 DEFAULT 값으로 UPDATE
• • RESTRICT : 자식 테이블에 PK값이 없는 경우에만 부모 데이터 삭제
• • NO ACTION : 참조 무결성 제약조건 위배하는 액션 불가

서브쿼리 : 쿼리 안에 있는 또 다른 쿼리

메인쿼리 : 바깥에 있는 쿼리

SELECT * FROM HR.EMPLOYEES A

WHERE A.DEPARTMENT_ID = (

SELECT B.DEPARTMENT_ID FROM HR.DEPARTMENTS B WHERE B.LOCATION_ID = 1800

);

• - SELECT B.DEPARTMENT_ID FROM HR.DEPARTMENTS_B WHERE B.LOCATION_ID = 1800
• • 이거 실행 결과가 한 행이라, WHERE의 EQUAL 조건 사용 가능 (단일 행 서브쿼리)
• • 여러 행을 가져오려면

SELECT * FROM HR.EMPLOYEES A

WHERE A.DEPARTMENT_ID IN

SELECT B.DEPARTMENT_ID FROM HR.DEPARTMENTS_B WHERE B.LOCATION_ID = 1700

);

• • = 대신 IN을 쓴다
• -JOIN으로 풀어 쓰면

SELECT * FROM HR.EMPLOYEES A, HR.DEPARTMENTS B WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID AND B.LOCATION_ID = 1700;

VIEW : 데이터베이스의 SELECT문을 저장한 OBJECT, 쿼리문에서 TABLE처럼 쓰인다

VIEW를 사용하는 이유 :

• 공통모듈처럼 사용하기 위해서

• 복잡한 쿼리를 미리 VIEW로 생성해두면 소스단의 쿼리가 간결해진다

• 보안상의 이유(외부에서 정보요청을 할때, 테이블을 통째로 보여주지 않아도 상대방이 요청한 부분만 VIEW로 뽑아서 제공하면 된다)

• 실무에서 흔하게 쓰인다고 함

CREATE OR REPLACE VIEW V1_IDOL AS     SELECT * FROM idol_group WHERE group_name = 'BTS';

SELECT * FROM V1_IDOL;

CREATE OR REPLACE VIEW V2_IDOL AS    SELECT A.COMPANY, A.GROUP_NAME, B.MEMBER_NAME, B.REAL_NAME    FROM IDOL_GROUP A,        IDOL_MEMBER B    WHERE A.GROUP_NAME = B.GROUP_NAME    AND A.GROUP_NAME = 'BTS';    SELECT * FROM V2_IDOL;

지울때는 DROP VIEW V1_IDOL; DROP VIEW V2_IDOL;

INLINE VIEW : "FROM 절에 쓰이는 서브쿼리"

VIEW가 OBJECT로 생성시킨 후 불러쓰는 구조라면 INLINE VIEW는 따로 생성하진 않고 일회성으로 불러쓰는 것이다.

SCALAR SUBQUERY- ?

계층형 쿼리

CREATE TABLE TAB1 (
    C1 VARCHAR2(1),
    C2 VARCHAR2(1),
    C3 VARCHAR2(1)
);

INSERT INTO TAB1 VALUES ('1', NULL, 'A');
INSERT INTO TAB1 VALUES ('2', '1', 'B');
INSERT INTO TAB1 VALUES ('3', '1', 'C');
INSERT INTO TAB1 VALUES ('4', '2', 'D');

SELECT * FROM TAB1;

SELECT C3 FROM TAB1
    START WITH C2 IS NULL
    CONNECT BY PRIOR C1 = C2
    ORDER SIBLINGS BY C3 DESC;

/**
	START WITH C2 IS NULL : 일단 1 NULL A 출력
    CONNECT BY PRIOR C1 = C2 : 이전 C1값이 C2와 같은것을 찾아라
    즉, C2가 (이전ROW의 C1인) 1인 ROW를 찾는다

    2 1 B
    3 1 C

    일단 이렇게 두개가 나오는데 이걸 ORDER SIBLINGS BY C3 DESC
    즉, 같은 계층에선 C3기준 내림차순 정렬

    3 1 C
    2 1 B 로 정렬된다

    다시 CONNECT BY PRIOR C1 = C2
    C2가 (이전ROW의 C1인)3인 ROW는 없으니 스킵
    C2가 (이전ROW의 C1인)2인 ROW를 찾는다

    4 2 D

    정리하면

    1 NULL A
    3  1   C
    2  1   B
    4  2   D 가 된다.

    SELECT C3 하여 출력하면 끝
*/

CREATE TABLE TAB_A (
    COL1 NUMBER,
    COL2 NUMBER,
    COL3 NUMBER
);

INSERT INTO TAB_A VALUES (30, NULL, 20);
INSERT INTO TAB_A VALUES (NULL, 10, 40);
INSERT INTO TAB_A VALUES (50, NULL, NULL);

SELECT COL1 + COL3 FROM TAB_A;

/**

	ROW 마다 COL1 + COL3 을 하므로 총 3줄짜리 결과가 나온다.
    가로로 연산시 NULL과의 연산은 NULL이다.
    크기비교도 전부 거짓으로 리턴
    즉, 결과는

    50
    NULL
    NULL

    하지만, SUM()함수는 NULL값이 있어도 무시하고 총합을 구한다. (세로연산)
    만약,
    SELECT SUM(COL1) FROM TAB_A; 를 실행한다면
    실행결과는

    80

    이다.
*/