기초적인 SQL 코드 및 정리 2

Recent Posts

Recent Comments

Link

Tags more

Archives

Today

Total

관리 메뉴

Kwon's 데이터분석기

기초적인 SQL 코드 및 정리 2 본문

카테고리 없음

기초적인 SQL 코드 및 정리 2

DataKwon 2024. 1. 15. 22:19

저번 글인 SQL코드 및 정리의 마지막 설명이었던 '함수 활용하기'에 이어서 작성하도록 하겠다.

● 데이터의 그룹화, 필터링

1. 데이터의 그룹화

데이터를 그룹화 하는 GROUP BY를 사용하면 집단 간 차이를 좀 더 자세히 확인할 수 있다.

그룹화 하는 방법은 열 이름으로 그룹화 하는 방법과 열 위치로 그룹화 하는 방법 2개가 있다.

아래는 GROUP BY 구문 그룹화에 대한 코드 예시이다.

# 열 이름으로 그룹화
SELECT cust_id, seg, AVG(annl_rev) AS annl_rev
FROM PPC_201312
GROUP BY seg;

# 열 위치로 그룹화
SELECT cust_id, seg, AVG(annl_rev) AS annl_rev
FROM PPC_201312
GROUP BY 2;

# 심화
SELECT card_flg, loan_flg, fund_fnc, COUNT(*) AS cnt
FROM PPC_201312
GROUP BY 1, 2;  # 또는 GROUP BY card_flg, loan_flg; 사용

※ 유용한 지식

- 원하는 열을 group by 절에 포함 할 수 있다. GROUP BY절은 하나의 기준으로만 그룹화가 가능한 것이 아닌, 여러 열을 지정할 수 있다.

- GROUP BY절은 WHERE 조건절 뒤에 위치하고 ORDER BY절 앞에 위치한다. ORDER BY절은 항상 마지막 문장에 위치하기 때문에 GROUP BY절이 ORDER BY절 뒤에 가면 오류가 발생한다.

- 그룹화될 열에 NULL값 포함할 경우 NULL값도 그룹화가 된다.

2. 그룹화 된 데이터 필터링

그룹화 된 데이터 필터링을 하기 위해선 HAVING 절을 사용하여 조건을 준다.

아래는 그룹화 데이터 필터링 코드 구조이다.

SELECT 그룹화할 열 이름, 집계함수
FROM 테이블명
WHERE 조건절
GROUP BY 열 이름 or 열 위치
HAVING 집계 함수 조건;

위 데이터 필터링 구조를 통해 특정한 조건을 만족하는 데이터 추출 후 특정한 조건을 만족한 그룹화 된 특정 열 및 집계 함수를 나타내는 것이다.

아래는 그룹화 데이터 필터링 코드 예시이다.

# GROUP BY ~~ HAVING 조건절; 예시 코드
SELECT cust_nm, SUM(sales_amt) AS sales_tot
FROM PROD_SALES
WHERE sales_amt >= 50000
GROUP BY cust_nm  #또는 1
HAVING SUM(sales_amt) >= 100000;

위 코드를 보면 cust_nm과 sales_amt를 모두 더한 값을 sales_tot로 두개의 열을 들고오는데, sales_amt가 50000이상인 값만 들고오고, GROUP BY cust_nm을 통해 cust_nm을 기준으로 그룹화를 시켜준다. 이렇게 나온 데이터 행 중 sales_amt가 100000 이상인 값만 불러오는 것이다.

※ 유용한 지식

- WHERE 조건절의 조건은 데이터 그룹화 되기 전 필터링 하고, HAVING절의 조건은 데이터가 그룹화된 후 필터링을 수행한다.

● 테이블 합치기 - 열(Column) 합치기

1. 내부 조인

내부 조인은 두 테이블에 공통으로 존재하는 키값이 되는 모든 행을 나타낸다. 간단하게 얘기하면 교집합이라고 생각하면 된다. 내부 조인에는 크게 2가지 방법이 있다. 아래는 내부 조인의 문법이다.

# FROM/WHERE을 사용해 내부 조인하는 방법 & 별칭 X
SELECT 테이블명1.열이름1, 테이블명2.열이름2
FROM 테이블명1, 테이블명2
WHERE 테이블명1.KEY = 테이블명2.KEY;
## FROM/WHERE을 사용해 내부 조인하는 방법 & 별칭 O: AS 생략 가능하다.
SELECT 별칭1.열이름1, 별칭2.열이름2
FROM 테이블명1 (AS) 별칭1, 테이블명2 (AS) 별칭2
WHERE 별칭1.KEY = 별칭2.KEY;

# INNER JOIN 사용 & 별칭 X
SELECT 테이블명1.열이름1, 테이블명2.열이름2
FROM 테이블명1 INNER JOIN 테이블명2
ON 테이블명1.KEY = 테이블명2.KEY;
# INNER JOIN 사용 & 별칭 O: AS 생략 가능
SELECT 별칭1.열이름1, 별칭2.열이름2
FROM 테이블명1 (AS) 별칭1 INNER JOIN 테이블명2 (AS) 별칭2
ON 별칭1.KEY = 별칭2.KEY;

위 코드를 보면 FROM/WHERE을 사용해 내부 조인을 하는 방법도 있지만, 새로 알아볼 방법으로 INNER JOIN에 대해서 알아볼 것이다. 그리고 조인을 할 때 별칭을 지정하는 AS는 생략이 가능하며, 조인을 사용할 때에는 ON 구문을 통해서 키 값을 매칭시켜 연결시켜준다.

아래에 내부 조인인 INNER JOIN에 대한 예시 코드로 알아보도록 한다.

# INNER JOIN: CUSTOMER, ORDERS, EMPLOYEE 테이블 이용해 주문이력 있는 cust_id, cust_nm, order_id,
# emp_id, nm을 나타내본다.
SELECT TMP1.cust_id, TMP1.cust_nm, TMP2.order_id, TMP2.emp_id, TMP3.nm
FROM CUSTOMER TMP1
	INNER JOIN ORDERS TMP2 ON TMP1.cust_id = TMP2.cust_id
    INNER JOIN EMPLOYEE TMP3 ON TMP2.emp_id = TMP3.emp_id;

위 코드를 보면 CUSTOMER 테이블을 TMP1, ORDERS 테이블을 TMP2, EMPLOYEE 테이블을 TMP3로 지정하면서 TMP1.cust_id와 TMP2.cust_id를 내부 조인을 해주고, 이어서 TMP2.emp_id와 TMP3.emp_id를 내부조인 시켜주는 것을 확인할 수 있었다. 이렇게 여러개의 테이블에 대한 열을 합쳐서 데이터를 들고올 수 있다.

2. 외부 조인

외부 조인은 내부 조인처럼 여러 테이블 간 일치하는 행을 결합하는 것은 같다. 하지만 조인 조건에 맞지 않는 행을 제외하는 내부 조인과 다르게 외부 조인은 조인 조건에 맞지 않는 행도 결과에 NULL값으로 출력되서 나온다.

외부 조인은 3가지의 종류가 존재한다. 아래는 외부 조인의 3가지 방법에 대한 코드 구문이다.

# LEFT OUTER JOIN: OUTER는 생략이 가능
SELECT 별칭1.열이름1, 별칭2.열이름2
FROM 테이블명 (AS) 별칭1 LEFT (OUTER) JOIN 테이블명2 (AS) 별칭2
ON 별칭1.KEY = 별칭2.KEY;

# RIGHT OUTER JOIN: OUTER는 생략 가능
SELECT 별칭1.열이름1, 별칭2.열이름2
FROM 테이블명 (AS) 별칭1 RIGHT (OUTER) JOIN 테이블명2 (AS) 별칭2
ON 별칭1.KEY = 별칭2.KEY;

# FULL OUTER JOIN: OUTER는 생략 가능
SELECT 별칭1.열이름1, 별칭2.열이름2
FROM 테이블명 (AS) 별칭1 FULL (OUTER) JOIN 테이블명2 (AS) 별칭2
ON 별칭1.KEY = 별칭2.KEY;

간단한 예시 테이블을 통해 외부 조인을 설명하겠다.

2-1. LEFT OUTER JOIN은 왼쪽 테이블을 기준으로 조인하는 방법이다. 만약 왼쪽 테이블에 존재하지만 오른쪽 테이블에 존재하지 않는 키값이 존재한다면 NULL값이 반환된다. 아래는 간단한 LEFT OUTER JOIN의 예시코드와 결과이다.

# LEFT OUTER JOIN 예시 코드
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT OUTER JOIN departments ON employees.department_id = departments.department_id;

위 코드의 LEFT OUTER JOIN 결과를 보면 아래와 같은 결과가 출력된다.

2-2. RIGHT OUTER JOIN은 오른쪽 테이블을 기준으로 조인하는 방법으로 LEFT OUTER JOIN과 반대로 오른쪽 테이블에는 존재하지만 왼쪽 테이블에는 존재하지 않는 키값이 있다면 NULL값이 반환된다. 아래는 간단한 LEFT OUTER JOIN 예시코드와 결과이다.

# RIGHT OUTER JOIN 예시 코드
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT OUTER JOIN departments ON employees.department_id = departments.department_id;

위 RIGHT OUTER JOIN 예시 코드의 결과를 보면 아래와 같은 결과가 출력이 된다.

위 LEFT OUTER JOIN과 RIGHT OUTER JOIN의 결과가 다르게 나온 것을 알 수 있다. 이는 조인되는 기준이 왼쪽 테이블인지, 오른쪽 테이블인지에 따라 사용되는 구문이기 때문이다.

2-3. FULL OUTER JOIN은 왼쪽(LEFT), 오른쪽(RIGHT)로 나누는 것이 아닌 왼쪽, 오른쪽의 모든 행이 반환되는 것이다. 즉, LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐놓은 형태이다. 아래는 간단한 FULL OUTER JOIN 예시코드와 결과이다.

# FULL OUTER JOIN 예시 코드
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

위 FULL OUTER JOIN 예시 코드 결과를 보면, 왜 LEFT와 RIGHT OUTER JOIN을 합친 것인지 이해할 수 있다.

단 FULL OUTER JOIN의 문제점으로는 ACCESS, MYSQL 등 몇몇 DBMS에서는 FULL OUTER JOIN 키워드가 지원이 되지 않기 때문에 자신이 사용하는 DBMS가 무엇인지 확인해보고 사용을 해야한다.

● 테이블 합치기 - 행(Row) 합치기

1. UNION 연산자

두개 이상의 테이블의 행을 합칠 때 UNION연산자를 사용한다. 두 개 이상의 SELECT문의 결과값을 합치는 것이다.

UNION 연산자 사용시 주의점

- UNION 연산자로 합쳐지는 SELECT문의 열의 숫자는 반드시 동일해야 한다.

- SELECT문의 각 데이터 타입은 일치해야 한다.

위 두가지 주의점을 명심하면서 UNION 연산자를 사용해야 한다. 또한 UNION연산자는 중복되는 값이 존재하면 한가지만 표시하는 특징이 있다. 만약 중복되는 값 또한 모두 보여주고 싶다면, 나중에 설명할 UNION ALL을 사용하면 된다.

아래는 UNION연산자 코드 구문과 예시 코드이다.

# UNION연산자 구문
SELECT 열이름1, 열이름2 FROM 테이블명1 WHERE 조건절
UNION
SELECT 열이름1, 열이름2 FROM 테이블명2 WHERE 조건절
ORDER BY 1;

# UNION연산자 예시 코드
SELECT cust_nm FROM CUSTOMERS
UNION
SELECT nm FROM EMPLOYEE
ORDER BY 1;

위 예시코드를 살펴보면 CUSTOMERS 테이블의 cust_nm과 EMPLOYEE테이블의 nm을 UNION연산자를 통해 합치는 작업을 수행한다. 이때 cust_nm과 nm은 테이블에 저장된 이름만 다를 뿐이지 데이터 유형은 같기 때문에 결합 할 수 있다. 즉, 열 이름이 다르더라도 UNION을 사용할 수 있는 경우는 제한적이고, 데이터 유형과 순서가 일치할 때 주로 사용한다고 보면 된다.

2. UNION ALL 연산자

UNION ALL연산자 또한 UNION과 마찬가지로 두개 이상의 테이블의 행을 합칠 때 사용한다. UNION과 UNION ALL 연산자의 차이는 UNION 연산자는 중복을 허용하지 않지만, UNION ALL 연산자는 중복까지 허용해서 출력을 해준다는 차이점이 존재한다. 아래는 UNION ALL 연산자 코드 구문과 예시 코드이다.

# UNION ALL 코드 구문
SELECT 열이름1, 열이름2 FROM 테이블명1 WHERE 조건절
UNION ALL  # UNION ALL은 중복 값까지 다 나옴
SELECT 열이름1, 열이름2 FROM 테이블명2 WHERE 조건절
ORDER BY 1;  # 또는 ORDER BY 열이름1

# UNION ALL 예시 코드
SELECT cust_id FROM CUSTOMERS
UNION ALL
SELECT cust_id FROM ORDERS
ORDER BY 1; # 또는 ORDER BY cust_id

위 UNION ALL 연산자 코드는 UNION 연산자와 큰 차이가 존재하지 않지만, 중복값이 출력된다는 차이가 존재한다.

● 하위 쿼리

하위 쿼리란 하나의 SQL 문장에 속하는 또 다른 SQL문장으로, 두 번 이상의 질의를 통해 얻을 수 있는 결과를 한 번의 질의로 해결할 수 있다. 하위 쿼리를 잘 사용하면 복잡한 SQL 문장도 간단하게 만들 수 있고 DBMS 데이터 처리 속도를 빠르게 향상 시킬 수 있다.

1. FROM절의 하위 쿼리

- 조건에 맞는 대상자 선정 후 요약할 때

# 조건에 맞는 대상자 선정 후 요약할 경우 SQL문법 구조
SELECT 열이름1, 열이름2
FROM (SELECT *
	FROM 테이블명
    WHERE 조건절) (AS) 별칭  # AS 생략가능 
WHERE 조건절;

조건에 맞는 대상자 선정 후 요약할 때의 FROM절 하위 쿼리는 하위 쿼리를 작성한 후 테이블 별칭을 필수로 줘야한다. 만약 별칭을 주지 않았다면 에러가 발생한다. 이때 AS 키워드는 생략이 가능하다.

- 테이블 조인을 할 때

# FROM절의 하위 쿼리 - 테이블 조인을 할 때
SELECT 별칭1.열이름1, 별칭2.열이름2
FROM 테이블명1 (AS) 별칭1 LEFT OUTER JOIN
(SELECT 열이름1, 열이름2
FROM 테이블명2
WHERE 조건절) (AS) 별칭2  # AS 생략 가능
ON 별칭1.KEY = 별칭2.KEY;

FROM절의 하위 쿼리는 위 코드의 SQL문법 구조를 가지고 있다는 것을 확인하면 된다.

아래는 FROM절의 하위 쿼리에 대한 예제를 통해 간단히 알아본다.

CARD_ACCT 테이블과 CUST_PARTY라는 테이블 데이터이다. 데이터 값을 쉽게 확인하기 위해 엑셀로 불러온 것이다. 이러한 테이블 데이터를 통해서 FROM절 하위 쿼리의 예시 코드를 작성해본다.

# CUST_ACCT, CUST_PARTY 테이블 이용해 현재 살아있는 신용카드 보유 고객과 미보유 고객 수 나타냄
SELECT 
    CASE WHEN TMP2.SSN IS NOT NULL THEN 'O' ELSE 'X' END AS CC_HOLDER,
    SUM(CASE WHEN TMP2.SSN IS NOT NULL THEN 1 ELSE 0 END) AS CNT
FROM CUST_PARTY TMP1
LEFT OUTER JOIN (
    SELECT DISTINCT SSN
    FROM CARD_ACCT
    WHERE CLOSE_DT IS NULL
      AND CC_GRADE IN ('1', '2')
) TMP2 ON TMP1.SSN = TMP2.SSN
GROUP BY CC_HOLDER;

위 코드를 하나씩 살펴보겠다.

먼저 FROM절에소 CUST_ID를 TMP1으로 지정하며 LEFT OUTER JOIN 외부 조인을 수행하는데 외부 조인을 수행하는 테이블을 하위 쿼리방식으로 CARD_ACCT의 SSN을 들고오는데 CLOSE_DT가 NULL값이고, CC_GRADE가 1 또는 2인 경우를 들고온다. 그 후 TMP1의 SSN과 TMP2의 SSN을 외부 조인으로 연결시켜 준 것이다.

그 후 SELECT 구문에 있는 것을 보면 CASE WHEN 구문으로 TMP2.SSN이 NULL값이 아니면 O, NULL값이면 X를 줘 CC_HOLDER에 넣고, TMP2.SSN이 NULL값이 아니면 1, NULL값이면 0을 넣고 SUM함수로 더해 CNT로 지정하면서 신용카드 보유 고객과 미보유 고객 수를 나타내주었다. 결과값은 이렇게 나올 것이다.

이때 하위 쿼리에서 DISTINCT를 사용했는데, 그 이유로는 CARD_ACCT의 SSN에는 중복값이 존재하기 때문에 제거를 하지 않으면 중복 값이 이중으로 붙기 때문에 제거를 수행한 것이다.

2. WHERE 조건절의 하위 쿼리

WHERE 조건절에서 하위 쿼리는 IN 연산자와 함께 쓰인다. 단일 결과값인 경우는 '='를 사용할 수 있지만 결과값이 단일이 아니면 사용할 수 없으므로, IN 연산자를 통해 다중 결과값을 가지게 한다.

아래는 IN을 사용한 WHERE 조건절의 하위 쿼리 코드 문법이다.

# IN을 사용한 WHERE 조건절의 하위 쿼리 문법 구조
SELECT 열이름1, 열이름2
FROM 테이블명1
WHERE 열이름 IN (SELECT 열이름 FROM 테이블명2 WHERE 조건절);

이러한 WHERE 조건절의 하위 쿼리는 특정 세그먼트만 추출할 때 유용하게 사용된다.

FROM 하위 쿼리 예시에 사용한 테이블 데이터를 그대로 사용해 WHERE 조건절의 하위 쿼리에 대해서 예시문제를 풀어본다.

# CUST_ACCT. CUST_PARTU 테이블 이용해 현재 살아있는 신용카드 보유 고객의
# 주민등록번호, 이름, 아이디, 자택번호 및 휴대폰 번호 출력
SELECT *
FROM CUST_PARTY
WHERE SSN IN (SELECT DISTINCT SSN
			FROM CARD_ACCT
            WHERE CLOSE_DT IS NULL
            	AND CC_GRADE IN ('1', '2'));

위 코드를 자세히 살펴보면 CUST_PARTY의 모든 열을 들고오고 조건으로 SSN IN (~~)를 통해 조건을 하위 쿼리를 활용해 조건을 주었다. 하위 쿼리의 조건을 보면 CARD_ACCT에서 중복을 제거한 SSN을 들고오는데, CLOSE_DT가 NULL값인 것과 CC_GRADE가 1 또는 2인 것을 들고오라는 것이다. 이러한 하위 쿼리 조건을 CUST_PARTY에 적용을 한 것이다. 결과는 아래와 같다.

만약 이를 하위 쿼리 없이 수행을 해야한다면 두 단계로 나눠진다. 아래는 하위 쿼리 없이 출력하는 코드이다.

# 1단계
SELECT DISTINCT SSN
FROM CARD_ACCT
WHERE (CLOSE_DT IS NULL
	AND CC_GRADE IN ('1', '2'));
    
# 2단계
SELECT * FROM CUST_PARTY
WHERE SSN IN ('5705121111000', '8204073333111');

이렇게 두 단계를 나눠서 해야할 일을 IN을 사용한 WHERE 조건절의 하위 쿼리를 통해서 한번에 처리를 할 수 있다.

만약 단일 행을 구하는 것이면 IN연산자 대신 '='를 사용할 수 있다. 아래는 예시 코드이다.

# WHERE 하위 쿼리 : 단일 행(1개의 값만 찾는 것)인 경우
SELECT *
FROM CUST_PARTY
WHERE SSN = (SELECT DISTINCT SSN
		FROM CARD_ACCT
        WHERE SSN = '6508112222333');

SSN이 2개 이상이 아닌 딱 하나의 값을 기준으로 찾는 것이기 때문에 부등호 '=' 사용이 가능한 것이지, 2개 이상의 값을 찾을려면 IN연산자로 무조건 사용해야한다.

● 데이터 조작(DML: Data Manipulation Language)

데이터는 하위 쿼리에 사용했던 데이터인 CUST_PARTY 테이블 데이터를 사용한다.

1. 데이터 삽입

데이터를 SQL 내부에 새로 추가하기 위해서 사용하는 방법이다.

데이터 삽입은 완전한 행 삽입, 열 이름과 함께 완전한 행 삽입, 부분 행 삽입, SQL문장 결과를 삽입으로 4가지가 있다. 아래는 각 데이터 삽입에 대한 코드 문법이다.

# 완전한 행 삽입
INSERT INTO 테이블명
VALUES(값1, 값2, 값3);

# 열이름과 함께 완전한 행 삽입
INSERT INTO 테이블명(열이름1, 열이름2, 열이름3)
VALUES(값1, 값2, 값3);

# 부분 행 삽입
INSERT INTO 테이블명(열이름1, 열이름3)
VALUES(값1, 값3);

# SQL 문장 결과를 삽입
INSERT INTO 테이블명1(열이름1, 열이름2, 열이름3)
SELECT 열이름1, 열이름2, 열이름3 FROM 테이블명2 WHERE 조건절;

데이터를 테이블 내에 적재하기 위해 이러한 방법을 사용한다. 위 4가지 방법 중 열 이름을 지정하지 않고 삽입하는 방법은 코딩을 짧게 해도 되는 장점이 존재지만, 데이터를 삽입하는 값의 순서가 변경될 경우 데이터를 적재하는 테이블이 망가질 수 있는 위험이 존재하기 때문에 조심하거나, 열 이름을 지정해서 값을 적재하는 것이 좋다.

아래는 데이터 삽입에 대한 예시 코드이다.

# 열 이름 사용하지 않고 행 전체 입력
INSERT INTO CUST_PARTY
VALUES('5508151111222', 'MJ YOO', '8828', '02-312-1111', '010-1122-1111');

# 열 이름을 사용해 행 전체 입력하기
INSERT INTO CUST_PARTY (SSN, PARTY_NM, CUST_ID, TEL_NO, MOBILE_NO)
VALUES('5508151111222', 'MJ YOO', '8828', '02-312-1111', '010-1122-1111');

# 열 이름 사용해 행 전체 입력 & MOBILE_NO는 NULL값 허용 열(부분 행 삽입)
INSERT INTO CUST_PARTY (SSN, PARTY_NM, CUST_ID, TEL_NO)
VALUES('5508151111222', 'MJ YOO', '8828', '02-312-1111');

위 코드처럼 데이터 값을 삽입할 수 있으며, 만약 데이터 값 중 NULL값으로 넣고 싶으면 열 이름과 값을 지정하지 않으면 NULL값으로 지정이 된다.

2. 데이터 삭제

데이터가 오래되어 데이터가 필요없어질 경우 데이터를 삭제를 수행할 수도 있다. 데이터 삭제에 대한 구문은 2가지가 존재하는데 테이블의 모든 행을 삭제하는 방법과 테이블의 부분적인 행만 삭제하는 방법으로 나눠진다. 아래는 데이터 삭제에 대한 두가지 코드 구문이다.

# 테이블의 모든 행 삭제
DELETE FROM 테이블명;

# 테이블의 부분 행 삭제
DELETE FROM 테이블명
WHERE 조건절;

데이터 삭제를 위한 DELETE절을 사용할 때에는 주의를 요해야한다. 그 이유로는 SQL에는 실행한 내용을 취소할 수 있는 기능이 없기 때문에 테이블 데이터를 실수로 삭제를 했다면, 되돌릴 방법은 SQL자체를 ROLLBACK을 시켜 이전 시간으로 돌리는 것 뿐이다. 이러한 ROLLBACK은 TCL(Transaction Control Language)의 하나이지만, 개발자가 직접적으로 TCL을 사용을 잘 하지 않으므로 설명을 생락한다.

아래는 데이터 삭제에 대한 예시 코드이다.

# 테이블 전체 삭제
DELETE FROM CUST_PARTY

# 테이블의 부분 행 삭제
DELETE FROM CUST_PARTY
WHERE PARTY_NM IN ('AR KIM', 'JH KIM');

위 코드 중 테이블의 부분 행 삭제를 보면 CUST_PARTY 테이블 데이터 중 PARTY_NM이 'AR KIM' 또는 'JH KIM'인 행만 삭제한다는 것이다. 이렇게 WHERE을 통해 조건을 줘 삭제해야하는 지정해서 삭제할 수 있다.

3. 데이터 수정

데이터 수정은 테이블에 적재되어 있는 데이터 중 전화번호가 변경되었다거나, 이름이 바뀌었다는 등 정보가 변경되는 경우가 존재한다. 이때 사용하는 방법이 데이터 수정 방법이다. 데이터 수정 또한 모든 행의 데이터를 수정하는 방법과 특정한 행의 데이터를 수정하는 방법이 있다. 아래는 데이터 수정에 대한 코드 구문과 예시 코드이다.

# 모든 행의 데이터 수정
UPDATE 테이블명
SET 열이름 = 변경할 값;

# 특정한 행의 데이터 수정
UPDATE 테이블명
SET 열이름 = 변경할 값
WHERE 조건절;

# 모든 행의 데이터 수정 예시 코드
UPDATE CUST_PARTY
SET CUST_ID = '1111';

# 특정한 행의 데이터 수정 예시 코드
UPDATE CUST_PARTY
SET MOBILE_NO = '010-9988-5555'
WHERE SSN = '901103444111';

위 예시코드 중 모든 행의 데이터 수정 예시 코드를 보면 CUST_PARTY의 CUST_ID를 1111로 전체 데이터가 변경이 되는 것이다. 그 아래에 있는 특정한 행의 데이터 수정 예시 코드는 WHERE을 통해 조건을 줘 CUST_PARTY에 있는 MOBILE_NO를 010-9988-5555로 변경하는데 모두 변경하는 것이 아닌 SSN이 9011034444111인 사람만 변경하라는 코드 구문이다. 이를 통해서 만약 적재된 테이블 데이터를 바꿔야 한다면 이러한 UPDATE 구문을 사용해야한다.

● 테이블 조작(DDL: Data Definition Language)

1. 테이블 생성

테이블을 생성할 때는 CREATE TABLE 문법을 사용하며, 새로 생성할 테이블 이름, 열 이름, 데이터 형식을 지정해야한다.

아래는 테이블 생성에 대한 코드 구문이다.

# 일반적인 테이블 생성 방법
CREATE TABLE 생성할 테이블 명
(
열이름 데이터 형식(크기) NOT NULL,
열이름2 데이터 형식(크기) PRIMARY KEY,
열이름3 데이터 형식(크기) NOT NULL REFERENCES 테이블1(테이블1의 Primary key),
열이름4 데이터 형식(크기) NOT NULL DEFAULT 1,
);

# 하위 쿼리에 의해 검색된 테이블과 동일한 구조로 테이블 생성
CREATE TABLE 생성할 테이블명 AS
SELECT 열이름, 열이름2 FROM 복사할 테이블명;

일반적인 테이블 생성 방법에 나온 옵션에 대해서 살펴본다.

- NOT NULL 조건: 열 값에 NULL값을 허용하지 않는다.

- PRIMARY KEY: 열 중 고유값인 기본키로 지정한다.

- REFERENCES 조건: 뒤에 있는 테이블1(테이블1의 기본키)와 연결된 외래키로 지정

- DEFAULT 1: 만약 NULL값이 발생하면 NULL 대신 1을 집어넣는다. 뒤에 1 대신 2로 지정하면 NULL값 대신 2를 집어넣는다.

※데이터 형식

문자형

- CHARACTER(n) 또는 CHAR(n): 고정 길이 문자 데이터, 고정폭 n - 문자열로 필요한 만큼 공백으로 채워짐

- NATIONAL VARYING(n) 또는 NCHAR(n): CHAR타입과 기본적으로 같은 공간 관리 한다.

- CHARACTER VARYING(n) 또는 VARCHAR(n): n문자의 최대 크기를 가진 가변폭 문자열, 입력되는 문자 길이가 정의된 공간 길이보다 적더라도 나머지를 공간을 여백으로 채우지 않고 필요한 공간만 사용

- NVARCHAR(n): 가변폭 NCHAR(n) 문자열

숫자형

- BIT: 단일 비트값 / - NUMERIC(p, s) 또는 DECIMAPL(p, s): p는 전체 자리값, s는 소수점 이하 자릿수 의미

- FLOAT: 실수 값 / - INTEGER 또는 INT: 숫자 저장할 수 있는 4Byte 정수값

날짜 및 시간

- DATE: 날짜값 / - TIME: 시간값 / - TIMESTAMP: DATE와 TIME이 하나의 변수로 결합된 형태

이렇게 테이블 생성에 대한 간단한 코드 구문을 알아봤으니, 예시를 통해 직접 생성해본다. 아래는 테이블 생성 코드 구문 예시이다.

# CUST_PARTY 테이블 생성
CREATE TABLE CUST_PARTY (
	SSN CHAR(13) PRIMARY_KEY,
	PARTY_NM VARCHAR(20) NOT NULL,
	CUST_ID CHAR(4) NOT NULL,
	TEL_NO VARCHAR(20) NULL,
	MOBILE_NO VARCHAR(20) NULL
);

CUST_PARTY 테이블을 생성한다. SSN은 문자열 길이 13에 기본키 지정하고 PARTY_NM은 가변 문자열 길이 20 지정에 NOT NULL로 결측값이 없게 만들었고, CUST_ID는 문자열 길이 4로 NOT NULL 조건을 달았다. 그 후 TEL_NO는 가변길이 문자열 20을 지정하고 NULL 결측값을 허용하고, MOBILE_NO도 TEL_NO와 마찬가지로 가변길이 문자열 20과 NULL값을 허용하게 하고 CUST_PARTY라는 테이블을 생성한 것이다.

2. 테이블 변경 및 삭제

테이블 변경 및 삭제는 테이블에 새로 열을 추가를 해야하거나 아예 테이블 자체가 필요 없어질 경우 등 테이블 자체와 테이블 내부 추가해야하는 열이 새로 존재하는 경우 사용한다. 아래는 테이블 변경 및 삭제에 대한 코드 구문이다.

# 테이블 변경(열 추가)
ALTER TABLE 수정할 테이블명
ADD (추가할 열 이름데이터 형식(크기));

# 테이블 변경(데이터 구조 변경)
ALTER TABLE 수정할 테이블명
MODIFY (변경할 열 이름 변경할 데이터 형식(크기));

# 테이블명 변경
RENAME 변경 전 테이블명 TO 변경 후 테이블명;

# 테이블 삭제
DROP TABLE 삭제할 테이블명;

테이블 변경 및 삭제에는 위 4가지 정도가 존재한다. 아래에 이어서 테이블 변경 및 삭제에 대한 예시 코드를 작성한다.

# 테이블 변경(열 추가) - CUST_PARTY에 대한 열 추가: 나이(AGE)를 추가하기로 함
ALTER TABLE CUST_PARTY
ADD (AGE CHAR(3));

# 테이블 변경(데이터 구조 변경) - CUST_PARTY의 데이터 구조 변경: CUST_ID를 VARCHAR(5)로 변경
ALTER TABLE CUST_PARTY
MODIFY (CUST_ID VARCHAR(5));

# 테이블명 변경: CUST_PARTY를 CUST_CELEBRATE로 변경
RENAME CUST_PARTY TO CUST_CELEBRATE

# 테이블 삭제: 변경한 CUST_CELEBRATE를 테이블 전체 삭제
DROP TABLE CUST_CELEBRATE

이처럼 테이블에 대해서 변경하거나 테이블 자체를 삭제하는 방법에 대해서 알아보았다.

이때 자세히 보면 DELETE와 DROP 둘다 삭제를 한다는 공통점이 존재하는데 이 둘의 차이는 DELETE문으로 테이블을 지정하면 테이블 자체가 삭제되는 것이 아닌 테이블 내부에 있는 데이터들만 전체 삭제가 되는 것이다. 하지만 DROP문을 통해 테이블을 지정하면 테이블 내부에 있는 데이터뿐만 아니라 테이블 자체도 삭제가 이루어지는 차이가 존재한다.

이렇게 SQL에 대한 기본적인 코드 정리를 해보았다. 이러한 기초를 통해 내가 원하는 데이터를 출력하고 사용하는 방법에 대해서 배우게 되었기 때문에 이를 실무에 잘 적용할 수 있도록 정진하겠다.

Kwon's 데이터분석기

기초적인 SQL 코드 및 정리 2 본문

기초적인 SQL 코드 및 정리 2

티스토리툴바