국비 TIL(Today I Learned) 20220714 오라클 TEST 계정 생성, 테이블 생성, OT계정 문제

새로운 계정 다시 생성 만들기 TEST

 

 

OT 계정 만들었을 때와 똑같음

 

 

 

데이터베이스 기본타입

숫자: NUMBER

문자: VARCHAR ( 단, ORACLE은 VARCHAR2 ) - VARCHAR , VARCHAR2 보관방식의 차이

날짜 : DATE

 

VARCHAR에서 제공되는 크기 4000BYTE 

일반적으로 숫자, 공백 1개당 1BYTE, 영어 2BYTE, 한글 4BYTE

 

VARCHAR 크기를 넘어갔을때 사용하는것

BLOB(보통적으로 쓰임), CLOB

 

 

PK (PRIMARY KEY) -> 주키, 데이터를 구분지음  ==> UNIQUE, INDEX(특정 인덱스로 정렬되, 검색 빠르게 함)\

FK(FOREIGN KEY) -> 외래키, 다른 데이터와 관계를 형성. PK와 관계

UK(UNIQUE KEY) -> 고유키, 데이터의 고유성을 보장

 

 

 

테이블 만들때는 단독으로 있는 애 부터 만드는게 쉬움

이런 테이블 만들거임

SYSDATE 오늘날짜 넣어줌

 

학생테이블

BIRTH 에 기본값으로 SYSDATE 만 더 넣어주고,  NAME크기를 100으로 변경함

 

학부테이블

 

학과 테이블

FK는 테이블 생성 후 추가적으로 해주는게 좋아

외래키(FK)걸때

FK 설정시 삭제 조건

작업 없음(RESTRICT) : FK쪽에 자식 데이터가 존재하는 한 부모는 삭제 안됨. 기본설정

종속 삭제(CASCADE) : PK쪽에 데이터가 삭제될경우 FK쪽 관련 자식 데이터도 삭제함

널 없음(SET NULL): FK가 NULL이 허용되는 경우 PK쪽 데이터가 삭제될 경우 FK쪽 해당 내역들을 NULL로 변경

 

FK 걸어진거 확인가능

 

 

학생_학과 테이블

STUNO -> STU_NO로 변경함

 

 

INSERT문

INSERT INTO 테이블명(컬럼명, 컬럼명..) VALUES (값1, 값2)

테이블명 뒤의 컬럼 지정은 생략이 가능하나 생략시 실 테이블상 컬럼순서를 지켜야 함. 단, 대부분 생략하지 않고 사용.

 

지정 컬럼과 값의 개수와 타입이 동일해야 함

 

TRANSACTION : 데이터 변경 기록 관리, 안전장치

COMMIT: 현재 변경사항을 적용한다.

ROLLBACK: 마지막 커밋시점까지 돌아간다.

 

 

TOOL마다 COMMIT에 대한 설정이 다름

AUTOCOMMIT이 되어있는 툴도 있음, INSERT되면 바로 COMMIT됨, AUTOCOMMIT 끄고 사용하래

우리(sqldeveloper) AUTOCOMMIT 설정 안되어있음

 

 

SEQUENCE: 숫자 증가용 공간, JAVA로 따지면 변수역활, TRANSACTION의 영향을 받지 않는다, 뒤로 갈려면 지우고 다시 만들어야함(편집에서 다음값 1로 해주고 확인하면 아래 경고창 뜸, 예 하면 기존꺼 지우고 다시 만듬)

시퀀스.NEXTVAL : 다음값으로 증가한 후 불러온다. 최초 호출시 시작값, 쿼리 한번당 증감 한번

시퀀스.CURRVAL: 현재값을 불러온다. 사용하려면 최초 1회는 시퀀스.NEXTVAL을 호출 해줘야함

 

 

캐시: 처리속도 높이기 위해서 일종의 임시적인 저장공간, 대신 크기가 작고 임시기 때문에 휘발성,

여기서 캐시는 내가 지정한 숫자만큼 미리 캐시를 확보해놔, 1-10까지라고 하면 10까지 미리 만들고 (대기표 뽑아논겨) 그거를 던져줘, 빠름, 근디 휘발성, 데이터베이스가 꺼지면은 다시 뽑음, 중간에 숫자가 빌수도 있음

 

캐시 안 쓰면 정렬도 의미없음

 

최대값 9 * 28개까지 가능

 

 

 

 

중복된 데이터 들어가면 아래처럼 오류

SELECT * FROM STU;

INSERT INTO STU(STU_NO, NAME, BIRTH, GEN) VALUES (1, '홍길동', '1860-01-01', 0);

-- TRANSACTION 
ROLLBACK; -- 넣기 전으로 돌아감
COMMIT; -- COMMIT 후 ROLLBACK하면 안 돌아감

-- SEQUENCE
SELECT STU_SEQ.NEXTVAL
FROM DUAL;

SELECT STU_SEQ.CURRVAL
FROM DUAL;

-- SEQUENCE 활용
INSERT INTO STU(STU_NO, NAME, GEN) VALUES (STU_SEQ.NEXTVAL, 'TEXT' || STU_SEQ.CURRVAL, 0);

 

DIV_SEQ도 만들어서 테이블에 데이터 추가

 

 

UPDATE문 : 데이터 수정

UPDATE 테이블명 SET 컬럼 = 값, ...

WHERE 조건...

 

UPDATE에서 조건은 선택적 영역, 기본적으로 UPDATE는 모든 ROW를 대상으로 함

SET의 값 부분에 컬럼이 올 경우 현재 값을 넣어줌

 

 

DELETE문 : 데이터 제거

DELETE FROM 테이블명

WHERE 조건

 

WHERE는 선택적 영억

DELETE는 ROW를 제거하기 때문에 컬럼지정이 없음

 

SELECT * FROM STU;

INSERT INTO STU(STU_NO, NAME, BIRTH, GEN) VALUES (1, '홍길동', '1860-01-01', 0);

-- TRANSACTION 
ROLLBACK; -- 넣기 전으로 돌아감
COMMIT; -- COMMIT 후 ROLLBACK하면 안 돌아감

-- SEQUENCE
SELECT STU_SEQ.NEXTVAL
FROM DUAL;

SELECT STU_SEQ.CURRVAL
FROM DUAL;

-- SEQUENCE 활용
INSERT INTO STU(STU_NO, NAME, GEN) VALUES (STU_SEQ.NEXTVAL, 'TEXT' || STU_SEQ.CURRVAL, 0);



-- 학부테이블에 데이터 5개 추가, 시퀀스 생성해야함.
INSERT INTO DIV(DIV_NO, NAME) VALUES (DIV_SEQ.NEXTVAL, 'TEXT' || DIV_SEQ.CURRVAL);

SELECT * FROM DIV;


-- 학과테이블
INSERT INTO DEPT(DEPT_NO, DIV_NO, NAME) VALUES(DEPT_SEQ.NEXTVAL, 5 ,'DEPT' || DEPT_SEQ.CURRVAL);

SELECT * FROM DEPT;

-- UPDATE
UPDATE STU SET NAME = 'TEST1' WHERE STU_NO = 1;
UPDATE STU SET NAME = NAME || 'TEST1' WHERE STU_NO = 1;  --NAME || 'TEST1' 기존값에 누적, 조회수 할때 사용

SELECT * FROM STU;

--DELETE
DELETE FROM STU;
SELECT * FROM STU;
DELETE FROM STU WHERE STU_NO =1;
COMMIT;

 

OT계정 문제

-- 카테고리별 제품 종류의 개수를 구하시오
SELECT PC.CATEGORY_NAME, COUNT(*) AS CNT
FROM PRODUCT_CATEGORIES PC INNER JOIN PRODUCTS P ON PC.CATEGORY_ID = P.CATEGORY_ID
GROUP BY PC.CATEGORY_NAME;

-- 창고별, 제품별 보유 수량을 구하시오
SELECT DISTINCT W.WAREHOUSE_NAME, P.PRODUCT_NAME, P.DESCRIPTION, SUM(I.QUANTITY) AS QUANTITY
FROM WAREHOUSES W INNER JOIN INVENTORIES I
                ON W.WAREHOUSE_ID = I.WAREHOUSE_ID
                  INNER JOIN PRODUCTS P
                ON I.PRODUCT_ID = P.PRODUCT_ID
GROUP BY W.WAREHOUSE_NAME, P.PRODUCT_NAME, P.DESCRIPTION
ORDER BY W.WAREHOUSE_NAME ASC, P.PRODUCT_NAME ASC
;

-- 17년에 주문을 취소한 고객의 명단을 구하시오
SELECT DISTINCT C.NAME
FROM CUSTOMERS C INNER JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
AND O.STATUS ='Canceled' AND TO_CHAR(O.ORDER_DATE,'YYYY') = 2017
;