SQLD 벼락치기 기억이 새록새록..^^.. 게시판 베스트 게시글 조건 변경해본다고 Raw data 백엔드에서 받아서 MySQL로 점수 계산해보던 기억도 새록새록..^^.. 서브쿼리 문제 풀 때 도대체 먼소리? 했었는데 과정 보니까 복잡할 수밖에.. 낼 바로 시험인디 언제 다시 보지 ㅎ
데이터 유형(추가)
날짜(Date)
- 데이터에 시간을 설정할 때 사용하는 형태
- 연월일시분초.0까지 저장 가능
- 더 자세한 형태로는 TIMESTAMP가 있음
- 문자열과 상호 변환이 가능
- TO_CHAR(), TO_DATE() 함수
- 현재 시각을 자동 계산해주는 객체가 존재함
- SYSDATE, SYSTIMESTAMP
- DATE는 계산이 가능함
- 1일 뒤: +1
- 5분 뒤: +5/24/60
- 5초 뒤: +5/24/60/60
CREATE TABLE TIME_TEST(
NO NUMBER UNIQUE NOT NULL,
WHEN DATE NOT NULL
);
-- '시간 표시 방식이 맞으면' 문자열을 바로 추가할 수 있지만 비추천
INSERT INTO TIME_TEST(NO, WHEN) VALUES(1, '2022-08-23');
-- TO_DATE('문자열', '형식정보')를 통해 문자열을 날짜 데이터로 변환 가능
INSERT INTO TIME_TEST(NO, WHEN) VALUES(2, TO_DATE('2022-08-23', 'YYYY-MM-DD'));
-- TO_DATE('문자열', '형식정보')를 통해 문자열을 날짜 데이터로 변환 가능
INSERT INTO TIME_TEST(NO, WHEN) VALUES(2, TO_DATE('2022-08-23', 'YYYY-MM-DD'));
INSERT INTO TIME_TEST(NO, WHEN) VALUES(3, TO_DATE('2022-08-23', 'yyyy-mm-dd'));
-- 현재 시각은 SYSDATE로 확인
INSERT INTO TIME_TEST(NO, WHEN) VALUES(4, SYSDATE);
INSERT INTO TIME_TEST(NO, WHEN) VALUES(5, SYSDATE + 365); -- 1년 뒤
SELECT * FROM TIME_TEST;
SELECT NO, TO_CHAR(WHEN, 'YYYY-MM-DD HH24:MI:SS') FROM TIME_TEST;
-- 오라클 시간(분): MI
-- SYSDATE로 삽입한 데이터만 시/분/초 정보가 출력됨(기본값: 00:00:00)테이블 제약 조건(추가)
PRIMARY KEY
- 테이블을 대표하는 컬럼(
NOT NULL + UNIQUE포함) - 최소 용량, 최대 사용 컬럼을 설정하면 됨
- 조건
- 테이블에 저장된 행을 식별할 수 있는 유일한 값이어야 함(
외래키관련 개념) - 값의 중복이 없어야 함
- NULL 값을 가질 수 없음
- 테이블에 저장된 행을 식별할 수 있는 유일한 값이어야 함(
- 기본키가 될 수 있는
후보키중 택1 → 나머지 후보키는보조키(대체키)가 됨
COMPOSITE KEY
- 복합키는 기본키가 되지 못하는 컬럼들을 서로 묶어서 기본키처럼 사용하는 것
CREATE TABLE PLAYER(
PLAYER_ID VARCHAR2(30) PRIMARY KEY,
PLAYER_JOB VARCHAR2(12) NOT NULL,
PLAYER_LEVEL NUMBER DEFAULT 1 NOT NULL
);
CREATE TABLE EXAM(
YEAR NUMBER,
ROOM NUMBER,
NO NUMBER,
NAME VARCHAR2(21),
-- YEAR+ROOM+NO로 PK 설정(복합키)
PRIMARY KEY(YEAR, ROOM, NO)
);SELECT
- 저장된 데이터를 가져오도록 지시하는 명령
- 전체 조회: 와일드카드(*) 사용
- 테이블명.* 또는 전체 항목 나열 등 ‘전체’의 기준을 설정해 조회를 하는 것 권장
SELECT 컬럼1, 컬럼2,... FROM 테이블 [WHERE 조건] -- 일부 항목 조회
SELECT * FROM 테이블 -- 전체 조회WHERE
- 데이터 필터링 조건 설정
- 실제 테이블에서 조건에 맞는 데이터만 복사한
결과 집합(RESULT SET)을 출력하는 것 - [주의] 비교연산자 중 ‘같음’은
=등호 하나만 사용
범위 필터링
-- Q. 2000원 이하의 상품만 조회
SELECT * FROM PRODUCT WHERE PRICE <= 2000;
-- Q. 1000원 이상 2000원 이하의 상품만 조회
SELECT * FROM PRODUCT WHERE PRICE BETWEEN 1000 AND 2000;
-- Q. 1000원인 상품만 조회
SELECT * FROM PRODUCT WHERE PRICE = 1000;
-- Q. 1000원이 아닌 상품만 조회
SELECT * FROM PRODUCT WHERE PRICE != 1000;
SELECT * FROM PRODUCT WHERE PRICE <> 1000;동등 비교 필터링
-- Q. 과자만 조회
SELECT * FROM PRODUCT WHERE TYPE = '과자';
-- Q. 아이스크림과 과자만 조회
SELECT * FROM PRODUCT WHERE TYPE = '과자' OR TYPE = '아이스크림';
SELECT * FROM PRODUCT WHERE TYPE IN ('과자', '아이스크림');유사검색
-
문자열 유사검색 시, 시작 검사는 LIKE / 나머지는 INSTR() 사용 권장
-
LIKE: %를 '있어도 되고 없어도 되는 값'으로 인식
-- Q. '바'로 시작하는 상품 조회
SELECT * FROM PRODUCT WHERE NAME LIKE '바%';
-- Q. '바'가 포함된 상품 조회
SELECT * FROM PRODUCT WHERE NAME LIKE '%바%';
-- Q. '바'로 끝나는 상품 조회
SELECT * FROM PRODUCT WHERE NAME LIKE '%바';INSTR: 지정한 글자가 항목의 몇 번째에 위치하는지 반환(1부터 시작)
-- Q. '바'로 시작하는 상품 조회
SELECT * FROM PRODUCT WHERE INSTR(NAME, '바') = 1;
-- Q. '바'가 포함된 상품 조회
SELECT * FROM PRODUCT WHERE INSTR(NAME, '바') > 0;
-- Q. '바'로 끝나는 상품 조회
SELECT * FROM PRODUCT WHERE INSTR(NAME, '바') = LENGTH(NAME);날짜 필터링
- 문자열처럼 사용 / 계산 / 범위 표현 가능
EXTRACT: 날짜에서만 사용 가능
-- Q. 제조년도가 2020년이 상품 조회
SELECT * FROM PRODUCT WHERE EXTRACT(YEAR FROM MADE) = 2020;
-- Q. 여름에 생산한 제품 조회
-- [방법1] EXTRACT를 한 번만 사용하는 첫 번째 방법이 성능이 좋음
SELECT * FROM PRODUCT WHERE EXTRACT(MONTH FROM MADE) IN (6, 7, 8);
-- [방법2] OR
SELECT * FROM PRODUCT
WHERE EXTRACT(MONTH FROM MADE) = 6
OR EXTRACT(MONTH FROM MADE) = 7
OR EXTRACT(MONTH FROM MADE) = 8;
-- [방법3] BETWEEN
SELECT * FROM PRODUCT
WHERE EXTRACT(MONTH FROM MADE) BETWEEN 6 AND 8;
-- [방법4] 지정한 날짜 형식에 따라 다양하게 활용 가능
SELECT * FROM PRODUCT
WHERE TO_CHAR(MADE, 'MM') IN ('06', '07', '08');
-- [방법5] LIKE% 성능 저하 이슈
SELECT * FROM PRODUCT
WHERE MADE LIKE '%/06/%'
OR MADE LIKE '%/07/%'
OR MADE LIKE '%/08/%';
-- [방법6] REGEX 성능 저하 이슈
SELECT * FROM PRODUCT
WHERE REGEXP_LIKE(TO_CHAR(MADE, 'MM'), '(06|07|08');- 날짜 범위 표현
- [주의] 시간을 입력하지 않으면 00:00:00으로 설정됨. 끝범위 날짜 하루를 온전히 포함하려면 시간 설정 필요
-- 2019년 6월 1일부터 2019년 8월 31일까지 조회
-- [방법1] 비교연산자
SELECT * FROM PRODUCT
WHERE MADE >= TO_DATE('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:DD')
AND MADE <= TO_DATE('2019-08-31 23:59:59', 'YYYY-MM-DD HH24:MI:DD');
-- [방법2] BETWEEN
SELECT * FROM PRODUCT
WHERE MADE BETWEEN TO_DATE('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:DD')
AND TO_DATE('2019-08-31 23:59:59', 'YYYY-MM-DD HH24:MI:DD');ORDER BY
-
정렬: 원하는 기준에 맞게 재배치 하는 것
- 데이터 조회 결과가 여러 개인 경우 반드시 정렬을 해야 함
-
오름차순(ASCENDING, ASC) / 내림차순(DESCENDING, DSC)
-
키워드로 정렬 수행
-
정렬 기준 여러 개 설정 가능
- PK를 기준으로 설정 시, 성능 향상에 도움됨
- PK를 마지막 조건으로 설정하면 순위가 같은 값을 처리할 수 있음
ORDER BY 컬럼명(OR 별칭) ASC; -- 오름차순 ORDER BY 컬럼명(OR 별칭) DESC; -- 내림차순SELECT * FROM PRODUCT ORDER BY NO ASC; -- Q. 가격이 저렴한 상품 순으로 출력 SELECT * FROM PRODUCT ORDER BY PRICE ASC; -- Q. 가격이 비싼 상품 순으로 출력 SELECT * FROM PRODUCT ORDER BY PRICE DESC, NO ASC; -- 같은 가격이면 번호순(PK를 마지막에)
ALIAS
- 컬럼 별칭(ALIAS) 설정 가능
- 대소문자, 공백, 한글, 특수문자 등으로 표현 할 수 있음
- 띄어쓰기를 하거나, 특수문자가 맨 앞에 들어갈 경우 인용부호
" "로 묶어줘야 함
SELECT 테이블.*, 컬럼 별칭 FROM 테이블
-- Q. 유통기한이 짧은 순으로 출력
SELECT PRODUCT.*, EXPIRE-MADE "유통기한" FROM PRODUCT
ORDER BY "유통기한" ASC;주의사항
- 항상 조회 구문
가장 마지막에 위치해야 함- 데이터가 정해져야 할 수 있는 작업이기 때문
FUNCTION
- 자바 메소드처럼 input/output이 존재하는 도구
- 함수 처리 방식에 따라 여러 가지로 구분됨
단일 행 함수
- 행별로 작업을 처리하는 함수
- 테이블 조회 시 결과를 새 컬럼으로 추가해 사용 가능
듀얼 테이블
- 임시 계산 결과를 보관/출력할 수 있도록 구성된 내장 테이블
SELECT 1234+5678 FROM DUAL;단일 행 함수 예시
-- ASCII 코드를 문자로 변환
SELECT CHR(65) FROM DUAL;
-- ASCII 코드를 숫자로 변환
SELECT ASCII('A') FROM DUAL;
-- 문자열 소문자화
SELECT LOWER('HELLO') "결과" FROM DUAL;
SELECT PRODUCT.*, LOWER(NAME) "소문자" FROM PRODUCT;
-- 문자열 인덱스 기준으로 자르기
SELECT PRODUCT.*, SUBSTR(NAME, 1, 1) "첫글자" FROM PRODUCT;집계 함수
- 여러 데이터를 종합해 하나의 결과를 만들어 내는 함수
- [대표] 합계, 평균, 최대, 최소, 개수
- SELECT 절에 컬럼/단일행 함수와 같이 사용 불가
-- SELECT PRODUCT.*, SUM(PRICE) FROM PRODUCT;
SELECT SUM(PRICE) "합계" FROM PRODUCT;
SELECT AVG(PRICE) "평균" FROM PRODUCT;
SELECT MAX(PRICE) "최대" FROM PRODUCT;
SELECT MIN(PRICE) "최소" FROM PRODUCT;
SELECT COUNT(PRICE) "개수" FROM PRODUCT;SUB QUERY
- 구문 여러 개를 순차적으로 실행하도록 구성한 것
-- Q. 가장 비싼 상품의 이름을 출력
-- SELECT NAME FROM PRODUCT WHERE PRICE = MAX(PRICE);
SELECT MAX(PRICE) FROM PRODUCT; -- 3000
SELECT NAME FROM PRODUCT WHERE PRICE = 3000; -- 초코파이
-- 서브쿼리로 구문 통합
SELECT NAME FROM PRODUCT WHERE PRICE = (
SELECT MAX(PRICE) FROM PRODUCT
);SUB QUERY 활용
TOP N QUERY
- 데이터를 원하는 개수만큼 끊어서 조회하는 기법
- 페이지네이션에 사용
-- [공식]
SELECT * FROM (
SELECT TMP.*, ROWNUM RN FROM(
원하는 데이터 조회, 필터 및 정렬 구문
)TMP
)WHERE RN BETWEEN 시작행번호 AND 종료행번호;ROWNUM
-
[문제점1] SQL문 실행 순서에 따라 조회 결과에서 ROWNUM이 1부터 시작하지 않을 수 있음
- 실행 순서: FROM - WHERE - SELECT - ORDER BY
- 원인: ROWNUM이 SELECT절에서 먼저 생성된 후에 정렬이 됨
- 해결: SELECT절을 ORDER BY절보다 나중에 실행하기 위해 구문을 의도적으로 분해-- 문제1 SELECT PRODUCT.*, ROWNUM FROM PRODUCT WHERE ROWNUM <= 3 ORDER BY PRICE DESC; -- 해결1 SELECT TMP.*, ROWNUM FROM( SELECT * FROM PRODUCT ORDER BY PRICE DESC )TMP WHERE ROWNUM <= 3;
-
[문제점2] ROWNUM 조건에 1이 포함되어 있지 않으면 데이터 조회 결과가 없음
-
원인: ROWNUM은 반드시 1부터 부여하도록 되어 있음. 번호를 부여하면서 조건을 처리하고 있기 때문에 조건에 해당하지 않는 결과는 바로바로 제거됨
- 가격 순으로 정렬하고 맨 위부터 1을 부여 → 1은 3~5사이가 아님 → 결과에서 제거 → 반복 → 결과가 하나도 안나옴
-
해결: 번호 부여를 먼저하고 조건을 나중에 처리
-- 문제2 SELECT TMP.*, ROWNUM FROM( SELECT * FROM PRODUCT ORDER BY PRICE DESC )TMP WHERE ROWNUM BETWEEN 3 AND 5; -- 해결 -- SELECT * FROM (1차 완성 구문) WHERE ROWNUM BETWEEN 3 AND 5; SELECT * FROM ( SELECT TMP.*, ROWNUM FROM( SELECT * FROM PRODUCT ORDER BY PRICE DESC )TMP )WHERE ROWNUM BETWEEN 3 AND 5;
-
- [문제점3] ROWNUM의 대상이 불명확함
-
원인: ROWNUM은 SELECT절 개수만큼 생성됨
-
해결: 어떤 ROWNUM에 대한 조건인지 지정해줘야 함 → 별칭 설정
SELECT * FROM ( SELECT TMP.*, ROWNUM RN FROM( SELECT * FROM PRODUCT ORDER BY PRICE DESC )TMP )WHERE RN BETWEEN 3 AND 5;
-
최고👍