하루 10분 SQL (위키북스, 한상일 지음)에서 독학하며 모르거나 중요한 부분을 기록한 것 입니다.
https://book.naver.com/bookdb/book_detail.nhn?bid=16395921
하루 10분 SQL
《하루 10분 SQL》은 데이터베이스를 처음 접하는 사용자와 초급 개발자를 대상으로 SQL을 쉽게 익힐 수 있도록 구성한 입문서이다. 먼저 실습에 필요한 이론을 배운 후 데이터베이스 시스템 개발
book.naver.com
1. JOIN (테이블의 연관 정보 검색하기)
<구문 #1>
SELECT 검색필드명
FROM 테이블명1
JOIN 테이블명2
ON 테이블명1(별명1).필드명1 = 테이블명2(별명2).필드명1
AND 테이블명1(별명1).필드명2 = 테이블명2(별명2).필드명2
WHERE 조건식;
<구문 #2>
SELECT 검색필드명
FROM 테이블명1 (별명1), 테이블명2 (별명2)
WHERE 테이블명1(별명1).필드명1 = 테이블명2(별명2).필드명1(+); -- 대상 테이블에 + 붙이기
- JOIN : 기존 테이블과 대상 테이블에 매칭하는 필드값이 있는 경우에 검색 (공통만)
- LEFT JOIN : 기준 테이블의 모든 필드값을 보이고 대상 테이블에 매칭하는 필드값이 있는 경우에 검색, 그렇지 않으면 공백 (기준 테이블 모든 필드 보이기)
- RIGHT JOIN : 대상 테이블의 모든 필드값을 보이고 기준 테이블에 매칭하는 필드값이 있는 경우에 검색, 그렇지 않으면 공백 (대상 테이블 모든 필드 보이기)
- FULL OUTER JOIN : 기준 테이블과 대상 테이블에 상호 매칭하는 필드값이 있는 경우에 검색, 그렇지 않으면 공백
예제 1) 성적 테이블의 반코드와 반정보 테이블의 반코드를 연관 검색해 반이름 구하기
SELECT B.CLASS_NM, A.STUDENT_NO, A.STUDENT_NM,
A.KOR, A.MAT, A.TOT, A.AVG
FROM TB_GRADE A, TB_CLASS_INFO B
WHERE A.CLASS_CD = B.CLASS_CD;
-- JOIN ... ON 조건 형식 사용
SELECT B.CLASS_NM, A.STUDENT_NO, A.STUDENT_NM,
A.KOR, A.ENG, A.MAT, A.TOT, A.AVG
FROM TB_GRADE A JOIN TB_CLASS_INFO B
ON A.CLASS_CD = B.CLASS_CD;
[실행결과]
예제 2) 8월 성적 테이블과 9월 성적 테이블을 연관 검색해 두 테이블에 공통적인 시험코드를 보이기 -> JOIN 명령
SELECT A.TEST_CD AS "08_TEST_CD", A.KOR AS "08_KOR", A.ENG AS "08_ENG", A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD", B.KOR AS "09_KOR", B.ENG AS "09_ENG", B.MAT AS "09_MAT"
FROM TB_GRADE_08 A
JOIN TB_GRADE_09 B
ON A.TEST_CD = B.TEST_CD;[실행결과]
예제 3) 8월 성적 테이블과 9월 성적 테이블을 연관 검색해 8월 성적 테이블의 데이터는 모두 보이고, 9월 성적 테이블의 데이터는 8월 성적의 시험코드와 같으면 보이기 -> LEFT JOIN 명령 (기준 테이블 8월 성적 테이블을 기준으로 검색)
-- 방법 1 : LEFT JOIN 명령어 사용
SELECT A.TEST_CD AS "08_TEST_CD", A.KOR AS "08_KOR", A.ENG AS "08_ENG", A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD", B.KOR AS "09_KOR", B.ENG AS "09_ENG", B.MAT AS "09_MAT"
FROM TB_GRADE_08 A LEFT JOIN TB_GRADE_09 B
ON A.TEST_CD = B.TEST_CD;
-- 방법 2 : "(+)"로 변경한 SQL
SELECT A.TEST_CD AS "08_TEST_CD", A.KOR AS "08_KOR", A.ENG AS "08_ENG", A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD", B.KOR AS "09_KOR", B.ENG AS "09_ENG", B.MAT AS "09_MAT"
FROM TB_GRADE_08 A, TB_GRADE_09 B
WHERE A.TEST_CD = B.TEST_CD(+);[실행결과]
예제 4) 8월 성적 테이블과 9월 성적 테이블을 연관 검색해 9월 성적 테이블의 데이터는 모두 보이고, 8월 성적 테이블의 데이터는 9월 성적의 시험코드와 같으면 보이기 -> RIGHT JOIN 명령 (대상 테이블 9월 성적 테이블을 기준으로 검색)
-- 방법 1 : RIGHT JOIN 명령어 사용
SELECT A.TEST_CD AS "08_TEST_CD", A.KOR AS "08_KOR", A.ENG AS "08_ENG", A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD", B.KOR AS "09_KOR", B.ENG AS "09_ENG", B.MAT AS "09_MAT"
FROM TB_GRADE_08 A RIGHT JOIN TB_GRADE_09 B
ON A.TEST_CD = B.TEST_CD;
-- 방법 2 : "(+)"로 변경한 SQL
SELECT A.TEST_CD AS "08_TEST_CD", A.KOR AS "08_KOR", A.ENG AS "08_ENG", A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD", B.KOR AS "09_KOR", B.ENG AS "09_ENG", B.MAT AS "09_MAT"
FROM TB_GRADE_08 A, TB_GRADE_09 B
WHERE A.TEST_CD(+) = B.TEST_CD;[실행결과]
예제 5) 8월 성적 테이블과 9월 성적 테이블을 연관 검색해 두 테이블의 데이터를 모두 보이고, 두 테이블에 상호 연관되지 않는 데이터는 공백으로 보이기 -> FULL OUTER JOIN 명령 (기준 테이블과 대상 테이블을 상호 검색)
SELECT A.TEST_CD AS "08_TEST_CD", A.KOR AS "08_KOR", A.ENG AS "08_ENG", A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD", B.KOR AS "09_KOR", B.ENG AS "09_ENG", B.MAT AS "09_MAT"
FROM TB_GRADE_08 A FULL OUTER JOIN TB_GRADE_09 B
ON A.TEST_CD = B.TEST_CD;[실행결과]
2. CASE (조건에 따른 처리와 값 설정하기)
<구문 #1>
CASE 조건대상 WHEN 비굣값1 THEN 결과1
WHEN 비굣값2 THEN 결과2
....
WHEN 비굣값n THEN 결과n
ELSE 결과가 없을 떄
END
<구문 #2>
CASE WHEN 조건식1 THEN 결과1
WHEN 조건식2 THEN 결과2
....
WHEN 조건식n THEN 결과n
ELSE 결과가 없을 때
END
예제 1) 성적 테이블의 국어, 영어, 수학 점수가 91에서 100이면 '수', 80에서 89이면 '우', 70에서 79이면 '미', 60에서 69이면 '양', 59 이하는 '가'로 바꾸기
- 비교 연산자로 국어 점수의 범위 비교 -> "KOR >= 90 AND KOR <= 100"
- BETWEEN .. END로 영어 점수의 범위 비교 -> "BETWEEN 90 AND 100"
- IN(필드1, 필드2, ...)으로 수학 점수의 범위 비교 -> "IN (90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100)"
SELECT CLASS_CD, STUDENT_NO, STUDENT_NM,
CASE WHEN KOR >= 90 AND KOR <= 100 THEN '수'
WHEN KOR >= 80 AND KOR <= 89 THEN '우'
WHEN KOR >= 70 AND KOR <= 79 THEN '미'
WHEN KOR >= 60 AND KOR <= 69 THEN '양'
WHEN KOR < 60 THEN '가'
END AS KOR,
CASE WHEN ENG BETWEEN 90 AND 100 THEN '수'
WHEN ENG BETWEEN 80 AND 89 THEN '우'
WHEN ENG BETWEEN 70 AND 79 THEN '미'
WHEN ENG BETWEEN 60 AND 69 THEN '양'
ELSE '가'
END AS ENG,
CASE WHEN MAT IN (90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100) THEN '수'
WHEN MAT IN (80, 81, 82, 83, 84, 85, 86, 87, 88, 89) THEN '우'
WHEN MAT IN (70, 71, 72, 73, 74, 75, 76, 77, 78, 79) THEN '미'
WHEN MAT IN (60, 61, 62, 63, 64, 65, 66, 67, 68, 969) THEN '양'
ELSE '가'
END AS MAT, TOT, AVG
FROM TB_GRADE;[실행결과]
예제 2) 성적 테이블의 반코드가 'A'이면 '종합입시반', 'B'이면 '단과반', 'C'이면 '대학편입반', 'D'이면 '일반일반'으로 바꾸기
SELECT CASE CLASS_CD WHEN 'A' THEN '종합입시반'
WHEN 'B' THEN '단과반'
WHEN 'C' THEN '대학편입반'
WHEN 'D' THEN '일반인반'
END AS CLASS_NM, STUDENT_NO, STUDENT_NM
FROM TB_GRADE;[실행결과]
3. ROWNUM (행 번호 구하기)
예제 1) 고객 테이블의 전체 행 번호를 검색하기
SELECT ROWNUM, CUSTOMER_CD, CUSTOMER_NM, MW_FLG, BIRTH_DAY, PHONE_NUMBER
FROM TB_CUSTOMER;[실행결과]
cf) 행 번호와 전체 레코드를 함께 출력 -> 기존 테이블에 별명을 지정하고 "별명.*" 형식과 ROWNUM을 사용
SELECT ROWNUM, A.*
FROM TB_CUSTOMER A;[실행결과]
예제 2) 고객 테이블에서 남성인 고객을 행 번호와 함께 검색해 고객명으로 정렬하기
-> ROWNUM, WHERE, ORDER BY 사용
SELECT ROWNUM, CUSTOMER_CD, CUSTOMER_NM, MW_FLG, BIRTH_DAY, PHONE_NUMBER
FROM TB_CUSTOMER
WHERE MW_FLG = 'M' -- 남성인 고객
ORDER BY CUSTOMER_NM; -- 고객명으로 정렬[실행결과]
4. NULL (값이 없는 상태)
SELECT 검색필드명
FROM 테이블명
WHERE 필드명 IS [NOT] NULL;
예제 1) 고객 테이블에서 전화번호가 없거나 이메일이 없는 고객을 검색하기 -> IS NULL, OR 사용
SELECT *
FROM TB_CUSTOMER
WHERE PHONE_NUMBER IS NULL OR EMAIL IS NULL;[실행결과]
예제 2) 고객 테이블에서 남성 중 이메일이 없거나, 여성 중 이메일이 있는 고객을 검색해 여성, 남성순으로 보이기
-> AND, OR, IS NULL, IS NOT NULL, ORDER BY ... DESC 사용
SELECT *
FROM TB_CUSTOMER
WHERE (MW_FLG = 'M' AND EMAIL IS NULL) -- 남성 중 이메일이 없거나
OR (MW_FLG = 'W' AND EMAIL IS NOT NULL) -- 여성 중 이메일이 있는 고객
ORDER BY MW_FLG DESC; -- 여성, 남성 순[실행결과]
예제 3) 고객 테이블에서 고객명이 '강원진', '박승대', '이혜옥', '남궁소망', '한찬희'인 고객 중 전화번호가 있는 고객을 거색하기 -> IN, AND, IS NOT NULL 사용
SELECT *
FROM TB_CUSTOMER
WHERE CUSTOMER_NM IN ('강원진', '박승대', '이혜옥', '남궁소망', '한찬희')
AND PHONE_NUMBER IS NOT NULL; -- 전화번호가 있는 고객[실행결과]
5. DUAL (연산 값 출력용 1행 테이블)
- 오라클에서 기본적으로 설치되는 SYSTEM 권한의 1행으로 구성된 테이블
- 값이 'X'인 'DUMMY' 필드를 가지고 있음
- 테이블 검색보다는 일반적으로 수치 계산 값과 함수 실행 결과, 문자 출력값을 확인하는데 사용됨
<구문 #1>
SELECT 연산, 함수실행 결과, 문자 출력값 등
FROM DUAL;
<구문 #2>
SELECT 연산, 함수실행 결과, 문자 출력값 등
FROM DUAL CONNECT BY LEVEL <= n; -- n행까지의 행 번호를 보임
예제 1) DUAL 테이블의 내용을 검색하기
SELECT *
FROM DUAL;[실행결과]
예제 2) DUAL 테이블로 다음 연산의 값과 함수의 결과를 확인하기
- SYSDATE : 현재 일시를 구하는 명령
- SUBSTR() : 문자열 중 일부를 구하는 기능
SELECT 123 * 45, SYSDATE + 30, '1Day SQL' || '실습', SUBSTR('20190820',1,4)
FROM DUAL;[실행결과]
예제 3) DUAL 테이블로 '2019-1'에서 '2019-5'까지 문자열 행을 만들기
- || : 문자열을 연결하는 기능
SELECT '2019-' || ROWNUM
FROM DUAL
CONNECT BY LEVEL <= 5;[실행결과]
6. UPDATE (테이블 필드의 값 바꾸기)
UPDATE 테이블명
SET 필드명1=값1, 필드명2=값2, ... , 필드명n=값n
WHERE 조건식;
예제 1) 성적 테이블에서 A반, B반인 학생을 대상으로 국어, 영어, 수학 점수의 합계와 평균을 계산해서 바꾸기
-> UPDATE, WHERE, IN 사용
UPDATE TB_GRADE
SET TOT = KOR + ENG + MAT, -- 합계
AVG = ROUND((KOR + ENG + MAT) / 3, 1) -- 평균
WHERE CLASS_CD IN ('A', 'B'); -- A, B반 대상[실행결과]
예제 2) 성적 테이블에서 모든 학생을 대상으로 국어, 영어, 수학 점수의 합계와 평균을 계산해서 바꾸기
-> UPDATE 사용 (모든 학생을 대상으로 적용하므로 WHERE 필요 없음)
UPDATE TB_GRADE
SET TOT = KOR + ENG + MAT,
AVG = ROUND((KOR + ENG + MAT) / 3, 1);[실행결과]
예제 3) 성적 테이블에서 UPDATE 명령으로 계산한 모든 학생의 합계와 평균을 데이터베이스에 반영하기
COMMIT;[실행결과]
7. INSERT (테이블에 데이터 입력하기)
<구문 #1>
INSERT INTO 테이블명 VALUES (값1, 값2, ... , 값n);
-> 테이블의 모든 필드와 일치하는 데이터를 입력 (VALUES에 지정한 값의 개수가 필드와 같아야 한다는 의미)
<구문 #2>
INSERT INTO 테이블명 (필드명1, 필드명2, ... , 필드명n) VALUES (값1, 값2, ..., 값n);
-> 필드에 해당하는 데이터만 입력 (지정하지 않은 필드는 NULL 값을 허용해야 함)
<구문 #3>
INSERT INTO 테이블명
SELECT 필드명1, 필드명2, ... , 필드명n
FROM 테이블명
WHERE 조건식;
-> SELECT 명령으로 검색한 필드를 지정한 테이블에 바로 입력 (이때 SELECT에서 검색하는 필드가 INSERT 명령에 지정한 테이블의 필드 개수와 형식이 같아야함)
<구문 #4>
INSERT [ALL|FIRST] WHEN 조건식 THEN INTO 테이블명
-> SELECT 명령으로 검색한 필드를 조건에 따라 테이블을 달리해 입력
(ALL : 조건에 따라 데이터를 입력, FIRST : 첫 번째 조건에 만족하면 해당 테이블에 입력하고 뒤의 조건은 처리하지 않음)
예제 1) 성적 테이블에서 A반 3번 '독고승재', B반 4번 '이도희' 학생을 입력하기 -> INSERT INTO .. 이용
INSERT INTO TB_GRADE(CLASS_CD, STUDENT_NO, STUDENT_NM, KOR, ENG, MAT)
VALUES ('A', 3, '독고승재', 97, 88, 95);
INSERT INTO TB_GRADE VALUES ('B', 4, '이도희', 86, 92, 90, 0, 0);
-- 입력한 내용을 확인
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO; -- 반코드와 학생번호로 정렬
-- 데이터베이스에 반영
COMMIT;[실행결과]
예제 2) 정보를 입력할 연도별 포인트 테이블 검색하기
SELECT * FROM TB_POINT_2017;
SELECT * FROM TB_POINT_2018;
SELECT * FROM TB_POINT_2019;[실행결과]
예제 3) 포인트 테이블의 등록일시 앞 4자리 연도가 '2017'이면 2017년 포인트 테이블에 직접 내용을 입력하고, '2018'이면 2018년 포인트 테이블에 적립 내용을 입력하고, '2019'이면 2019년 포인트 테이블에 적립 내용을 입력하기, 조건에 해당하지 않으면 2019년 포인트 테이블에 입력하기
* 'YYYYMMDDHHMMSS' 형식의 등록일시에서 앞 4자리인 'YYYY'는 연도를 나타냄 -> SUBSTR() 함수로 앞 4자리만 구해서 비교
-> INSERT ALL, SUBSTR(), WHERE 이용
INSERT ALL -- 조건에 맞는 전체 데이터를 입력
WHEN SUBSTR(REG_DTTM, 1, 4) = '2017' THEN INTO TB_POINT_2017
WHEN SUBSTR(REG_DTTM, 1, 4) = '2018' THEN INTO TB_POINT_2018
WHEN SUBSTR(REG_DTTM, 1, 4) = '2019' THEN INTO TB_POINT_2019
ELSE INTO TB_POINT_2019 -- 앞의 조건에 해당 X
SELECT CUSTOMER_CD, SEQ_NO, POINT_MEMO, POINT, REG_DTTM
FROM TB_POINT
WHERE REG_DTTM >= '20170101000000'; -- 등록일시가 '201701010000' 이상인 적립 내역을 대상으로 함[실행결과]
SELECT * FROM TB_POINT_2017;
SELECT * FROM TB_POINT_2018;
SELECT * FROM TB_POINT_2019;
COMMIT;[실행결과]
8. MERGE (테이블에 데이터를 입력하거나 바꾸기)
MERGE INTO 입력/수정할 테이블명
USING (테이블명 | 뷰 | 서브쿼리)
ON 조건식
WHEN MATCHED THEN
UPDATE SET 필드명1=값1, 필드명2=값2, ... , 필드명n=값n
WHEN NOT MATCHED THEN
INSERT .. VALUES 구문;
: 조건식에 따라 데이터가 있다면 UPDATE 구문을 처리, 데이터가 없다면 INSERT 구문을 처리
- WHEN MATCHED THEN 이후 -> UPDATE 명령으로 레코드 변경
- WHEN NOT MATCHED THEN 이후 -> INSERT 명령으로 입력
-> 고객 테이블(TB_CUSTOMER)에서 '박승대' 고객의 전화번호는 공백이며 '전미래' 고객은 아직 미등록 상태
-> '박승대' 고객은 고객 테이블(TB_CUSTOMER)에 기존 고객명, 성별, 생년원일, 전화번호 값을 바꾸고, '전미래' 고객은 고객 테이블에 새롭게 입력할 예정
예제 1) 추가고객 테이블의 고객코드와 같은 고객코드가 고객 테이블에 있다면 추가고객 테이블의 데이터로 업데이트하고, 같은 고객코드가 없다면 추가고객 테이블의 데이터로 입력하기
-> MERGE로 데이터 존재하면 UPDATE, 존재하지 않으면 INSERT .. INTO 적용
MERGE INTO TB_CUSTOMER CU
USING TB_ADD_CUSTOMER NC
ON (CU.CUSTOMER_CD = NC.CUSTOMER_CD)
WHEN MATCHED THEN -- 추가고객 테이블의 고객코드와 같은 고객코드가 고객 테이블에 있다면 -> 추가고객 테이블의 데이터로 업데이트
UPDATE SET CU.CUSTOMER_NM = NC.CUSTOMER_NM,
CU.MW_FLG = NC.MW_FLG,
CU.BIRTH_DAY = NC.BIRTH_DAY,
CU.PHONE_NUMBER = NC.PHONE_NUMBER
WHEN NOT MATCHED THEN -- 같은 고객코드가 없다면 추가고객 테이블의 데이터로 입력
INSERT (CU.CUSTOMER_CD, CU.CUSTOMER_NM, CU.MW_FLG,
CU.BIRTH_DAY, CU.PHONE_NUMBER, CU.EMAIL, CU.TOTAL_POINT, CU.REG_DTTM)
VALUES (NC.CUSTOMER_CD, NC.CUSTOMER_NM, NC.MW_FLG,
NC.BIRTH_DAY, NC.PHONE_NUMBER, '', 0, '20191015082030');
SELECT * FROM TB_CUSTOMER; -- 추가고객 테이블의 내용을 반영했는지 확인
COMMIT;[실행결과]
9. DELETE (테이블에 데이터 삭제하기)
DELETE
FROM 테이블명
WHERE 조건식;
예제 1) 성적 테이블에서 '나경숙', '박승대', '이아름' 학생을 삭제하기 -> DELETE, WHERE, IN 사용
DELETE
FROM TB_GRADE
WHERE STUDENT_NM IN ('나경숙', '박승대', '이아름');
-- 성적 테이블을 검색해 삭제한 학생을 확인
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
COMMIT;[실행결과]
10. COMMIT (데이터베이스에 바뀐 내용 반영하기)
: 메모리에 임시 저장된 내용을 실제 데이터베이스에 반영하는 것
-> '독고승재', '이도희' 학생의 합계(TOT)와 평균(AVG)은 아직 계산 전으로 공백이나 '0'으로 보임
예제 1) 성적 테이블에서 '독고승재', '이도희' 학생의 국어, 영어, 수학 점수의 합계와 평균을 계산해 바꾸기
-> UPDATE, WHERE, IN 사용
UPDATE TB_GRADE
SET TOT = KOR + ENG + MAT,
AVG = ROUND((KOR + ENG + MAT) / 3, 1)
WHERE STUDENT_NM IN ('독고승재', '이도희');
-- 바뀐 합계와 평균 확인
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
COMMIT; -- 바뀐 합계와 평균을 데이터베이스에 반영[실행결과]
11. ROLLBACK (데이터베이스에서 바뀐 내용 취소하기)
: 데이터베이스에서 바뀐 내용을 취소하고 최종 커밋(COMMIT) 상태로 돌아가기
예제 1) 성적 테이블에서 B반, D반인 학생을 삭제 후 확인 -> DELETE, WHERE, IN 사용
DELETE
FROM TB_GRADE
WHERE CLASS_CD IN ('B', 'D');
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;[실행결과]
예제 2) 성적 테이블에서 삭제한 내용을 취소하고 이전 상태로 복구하고 B반, D반 학생이 다시 보이는지 확인하기
ROLLBACK; -- 삭제한 내용을 취소하고 이전상태로 복구
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;[실행결과]
'Data analysis > 개념(SQL)' 카테고리의 다른 글
| SQL 수치 관련 함수 (0) | 2022.04.08 |
|---|---|
| SQL 날짜 관련 함수 (0) | 2022.04.02 |
| SQL 집합 명령어 (0) | 2022.04.02 |
| SQL 명령어와 함수 (3) (0) | 2022.04.01 |
| SQL 명령어와 함수 (1) (0) | 2022.03.24 |
