4. DML ( Data Manipulation Language )
01. INSERT
일반적인 데이터 삽입
SELECT * FROM EMP;
INSERT INTO EMP VALUES (5, '강순자', 700, '1002', sysdate);
SELECT * FROM EMP;
테이블에 행(튜플)을 삽입하기 위해서는 INSERT INTO ~ VALUES (~) 구문을 사용하면 된다.
INSERT INTO EMP(EMPNO, ENAME, DEPTNO_ID) VALUES (6, '곽민재', '1001');
위와 같이 일부 열(칼럼)만 선택해서 데이터를 삽입할 수 있다. 다만 빠진 열에 not null 제약조건이 있을 경우 오류가 발생한다.
❗ INSERT문을 실행했다고 데이터 파일에 저장되는 것은 아니며, 최종적으로 데이터를 저장하려면 TCL문인 COMMIT을 실행해야 한다. 만약 Auto commit(SET AUTO COMMIT ON)을 설정했을 경우는 생략해도 된다.
SELECT문을 이용한 데이터 삽입
- SELECT문을 사용해 데이터를 조회하고 테이블에 바로 삽입할 수 있다
- 입력되는 테이블은 사전에 생성되어 있어야 하며 칼럼의 수와 데이터 타입이 같아야 한다
- INSERT INTO ~ SELECT ~문을 사용한다
CREATE TABLE COPY_TEST
(
AA NUMBER(4) PRIMARY KEY,
BB VARCHAR2(20)
);
INSERT INTO COPY_TEST (AA, BB)
SELECT EMPNO, ENAME FROM EMP;
SELECT * FROM COPY_TEST;
NUMBER타입을 가진 AA 칼럼, VARCHAR2타입을 가진 BB 칼럼으로 이루어진 COPY_TEST 테이블을 만들고 EMP테이블로부터 EMPNO 칼럼의 데이터를 SELECT문으로 조회한 뒤 바로 삽입한다
다음과 같이 INSERT INTO COPY_TEST SELECT ~를 이용해 AA, BB칼럼에 각각 EMPNO, ENAME 데이터를 조회한 뒤 복사해 넣었다.
Nologging
ALTER TABLE EMP NOLOGGING;
- 데이터베이스에 데이터를 입력하면 로그파일에 그 정보를 기록
- Check point라는 이벤트가 발생하면 로그파일의 데이터를 데이터파일에 저장함
- Nologging 옵션은 로그 파일의 기록을 최소화 시켜 입력 시 성늘을 향상시키는 방법임
- Nologging 옵션은 Buffer Cache라는 메모리 영역을 생략하고 기록해 성능이 향상
02. UPDATE
UPDATE EMP
SET ENAME = '김수환'
WHERE EMPNO = 3;
SELECT * FROM EMP;
특정 튜플의 값을 변경하고자 할 때는 UPDATE ~ SET ~ WHERE ~ 문을 사용할 수 있다.
다만 조건을 제약하는 WHERE문을 사용하지 않는다면 해당 열 전체가 수정되므로 주의해야 한다. 또한 데이터를 수정할 때 조건절에 검색되는 행 만큼 수정되므로 주의해야 한다.
03. DELETE
DELETE FROM EMP
WHERE DEPTNO_ID = '1002';
SELECT * FROM EMP;
조건에 해당하는 행을 삭제하고자 할 때는 DELETE FROM ~ WHERE ~ 문을 사용할 수 있다. 조건문을 입력하지 않으면 모든 데이터가 삭제된다. 하지만 DELETE문으로 데이터를 삭제한다고 해서 테이블의 용량이 초기화 되는 것은 아니며 별도의 삭제 표시로 삭제 여부를 구분하게 된다. 따라서 실제 테이블의 용량을 초기화시키고 싶다면 TRUNCATE TABLE 문을 사용해야 한다.
04. SELECT
SELECT EMPNO, ENAME || '님' as honorifics , DEPTNO_ID FROM EMP
ORDER BY ENAME DESC;
특정 테이블의 데이터를 조회하고자 할 때는 SELECT문을 사용한다. 위 코드에서 ||를 이용해 특정 문자를 결합해 출력할 수 있으며 as alias를 이용해 별칭을 지어줄 수 있다. 또한 ORDER BY를 이용해 ASC(오름차순), DESC(내림차순)으로 정렬할 수 있다. 차순을 지정해주지 않으면 기본적으로 ASC가 설정 돼있다.
ORDER BY는 정렬을 수행하므로 데이터베이스 메모리를 많이 사용한다. 따라서 대량의 데이터를 정렬하게 되면 정렬로 인한 성능 저하가 발생한다.
기본적으로 시스템 전체 레벨에서 정렬을 위한 메모리 크기인 sort_area_size가 설정되어 있다
SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME = 'sort_area_size';
이 sort_area_size가 너무 작아 정렬할 데이터가 이 값을 초과하게 되면 임시 테이블 스페이스를 이용해 디스크I/O 작업이 일어나므로 성능 저하가 발생한다. 따라서 정렬을 회피하기 위해 인덱스를 생성할 때 사용자가 원하는 형태로 오름차순 혹은 내림차순으로 생성하기를 권장한다.
INDEX를 이용한 정렬 회피
정렬은 오라클 데이터베이스에 부하를 주므로 인덱스를 사용해 ORDER BY를 회피할 수 있다.
SELECT * FROM EMP;
SELECT /*+ INDEX_DESC(A) */ * FROM EMP A;
이렇게 /*+ INDEX */ 를 이용해 ORDER BY를 쓰지 않고 인덱스만으로 내림차순으로 읽도록 할 수 있다.
순서
- EMPNO 인덱스를 내림차순으로 읽는다
- 인덱스를 스캔한 후에 해당 EMPNO 값을 가지고 테이블의 데이터를 읽는다
- 테이블에서 해당 행을 찾으면 인출해 화면에 조회된다
DISTINCT
SELECT DISTINCT DEPTNO_ID FROM EMP;
칼럼명 앞에 다음과 같이 DISTINCT문을 쓰면 중복된 데이터를 한 번만 조회하도록 한다
Alias
SELECT EMPNO, ENAME AS "회원이름", SAL, DEPTNO_ID "부서번호" FROM EMP A
WHERE A.ENAME = '김철수';
이런 식으로 테이블명이나 칼럼명이 길 경우 간략하게 Alias를 지정할 수 있다. FROM절에서 A라는 alias를 설정했을 경우 읽는 순서에 따라 SELECT절이 FROM절보다 뒤 이므로 A라는 alias를 이용할 수는 없다.
5. WHERE
01. 연산자
- 비교 연산자
| 비교 연산자 | 설명 |
| = | - 같은 것을 조회 |
| < | - 작은 것을 조회 |
| <= | - 작거나 같은 것을 조회 |
| > | - 큰 것을 조회 |
| >= | - 크거나 같은 것을 조회 |
- 부정 비교 연산자
| 부정 비교 연산자 | 설명 |
| != | - 같지 않은 것을 조회 |
| ^= | - 같지 않은 것을 조회 |
| <> | - 같지 않은 것을 조회 |
| NOT 칼럼명 = | - 같지 않은 것을 조회 |
| NOT 칼럼명 > | - 크지 않은 것을 조회 |
- SQL 연산자
| SQL 연산자 | 설명 |
| LIKE '_비교 문자열%' | - 비교 문자열을 조회. '%'는 모든 값을 의미. '_'는 한 개의 단일 문자를 의미 |
| BETWEEN A AND B | - A와 B를 포함한 사이의 값을 조회 |
| IN (LIST) | - OR을 의미하며 LIST 안의 값 중에서 하나만 일치해도 조회 |
| IS NULL | - NULL값을 조회 |
🔍NULL의 특징
- NULL은 모르는 값을 의미
- NULL은 값의 부재를 의미
- NULL과 숫자 혹은 어떤 문자를 더하면 NULL이 됨
- NULL과 어떤 값을 비교할 때, '알 수 없음'이 반환
- NULL = NULL은 NULL을 반환, NULL != NULL도 NULL을 반환, 5= NULL은 NULL을 반환
- 칼럼에서 어떤 값이 NULL일 때 True를 반환하고 싶다면 COL1 = NULL처럼 직접 비교하는 것이 아니라 COL1 IS NULL처럼 비교해야 함
- 부정 SQL 연산자
| 부정 SQL 연산자 | 설명 |
| NOT BETWEEN A AND B | - A와 B를 포함한 사이의 값에 해당되지 않는 값을 조회 |
| NOT IN (LIST) | - LIST와 불일치하는 값을 조회 |
| IS NOT NULL | - NULL이 아닌 값을 조회 |
🔍 NOT IN (서브쿼리) 시 주의점
- NOT IN (서브쿼리)에서 서브쿼리에 NULL이 포함되는 경우 데이터가 출력되지 않는다
- 이유는 서브쿼리에 NULL값이 포함되어 있을 경우 앞에 비교할 값이 서브쿼리의 결과에 포함되어 있는지 아닌지 알 수 없기 때문이다.
- 즉, NULL연산이 수행되므로 NOT IN의 전체 결과가 Unknown으로 반환됨
- 목표하는 연산을 수행하고자 한다면 서브쿼리에 NULL이 포함되지 않도록 서브쿼리의 WHERE절에 IS NOT NULL을 추가해야 함
02. NULL 관련 함수
| NULL 함수 | 설명 |
| NVL(COLUMN, 대체값) | - COLUMN의 값이 NULL일 경우 대체값으로 바꾼다 - NVL(MGR, 0)일 때 MGR칼럼이 NULL일 경우 0으로 대체한다 |
| NVL2(COLUMN, NOT NULL 대체값, NULL 대체값) | - NVL함수 + DECODE함수 - NVL2(MGR, 1, 0)일 때 MGR칼럼이 NULL이 아닐 경우 1, NULL일 경우 0으로 대체한다 |
| NULLIF(EXP1, EXP2) | - 두 개의 값이 같으면 NULL, 아니면 첫번째 EXP1 값을 반환 - NULLIF(EXP1, EXP2)일 경우 EXP1, EXP2가 같으면 NULL, 아니면 EXP1값을 반환한다 |
| COALESCE(EXP1, EXP2, EXP3, ...) | - NULL이 아닌 최초의 인자 값을 반환 - COALESCE(EXP1, EXP2, EXP3, ...)에서 EXP1가 NULL, EXP2가 NULL이 아닐 경우 EXP2의 값을 반환한다 |
6. GROUP BY
01. GROUP BY
- 테이블에서 소규모 행을 그룹화해 합계, 평균, 최댓값, 최솟값 등을 계산할 수 있도록 함
- HAVING구에 조건문을 사용
- Grouping된 결과에 대한 조건문을 사용하는 것
- ORDER BY를 사용해 정렬 가능
SELECT * FROM CUSTOMER;
SELECT SIDO, SUM(NVL2(SGG, 0, 1)) FROM CUSTOMER
GROUP BY SIDO;
SELECT SIDO, SUM(CASE WHEN SGG IS NULL THEN 1 ELSE 0 END) FROM CUSTOMER
GROUP BY SIDO;
이렇게 GROUP BY를 이용해 SIDO별 집계가 가능하다
02. HAVING절
SELECT SIDO, SUM(NVL2(SGG, 0, 1)) FROM CUSTOMER
GROUP BY SIDO
HAVING SUM(NVL2(SGG, 0, 1)) >= 5;
GROUP BY된 결과에 대해 조건을 지정하고자 할 때는 HAVING절을 사용할 수 있다. SELECT문보다 HAVING절이 먼저 읽히므로 해당 컬럼에 Alias를 지정해서 HAVING절에 사용할 수는 없다
03. 집계 함수
| 집계 함수 | 설명 |
| COUNT() | - 행 수를 조회 |
| SUM() | - 값의 합계를 계산 |
| AVG() | - 평균을 계산 |
| MAX(), MIN() | - 최댓값과 최솟값을 계산 |
| STDDEV() | - 표준편차를 계산 |
| VARIANCE() | - 분산을 계산 |
❗ COUNT(*)과 COUNT(칼럼명)의 차이
COUNT(*)은 NULL값을 포함한 행의 개수를 계산
COUNT(칼럼명)은 NULL값을 제외한 행의 개수를 계산
7. SELECT문 실행 순서⭐
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
8. 명시적(Explicit) 형변환 과 암시적(Implicit) 형변환
- 형변환은 두 개의 데이터의 데이터 타입이 일치하도록 변환하는 것
- 숫자와 문자열, 문자열과 날짜형 등과 같이 데이터 타입이 불일치 할 때 발생
- 명시적 형변환은 형변환 함수를 이용해 데이터 타입을 일치시키는 것, 암시적 형변환은 개발자가 형변환 함수를 명시하지 않은 경우 DBMS가 자동으로 형변환하는 것
01. 형변환 함수
| 형변환 함수 | 설명 |
| TO_NUMBER(문자열) | - 문자열 -> 숫자 |
| TO_CHAR(숫자OR날짜, [FORMAT]) | - 숫자 혹은 날짜를 FORMAT의 문자로 |
| TO_DATE(문자열, FORMAT) | - 문자열을 FORMAT의 날짜형으로 |
02. 형변환과 인덱스
- 인덱스는 데이터를 빠르게 조회하기 위해 인덱스 키를 기준으로 정렬해 놓은 데이터임
- 그런데 인덱스는 형변환 등의 변형이 발생하면 더이상 사용할 수 있음 (예외도 있음)
'프로그래밍 > DataBase' 카테고리의 다른 글
| [SQLD] SQL 기본 및 활용 Section 02. SQL 활용 (1) 조인부터 서브쿼리 (0) | 2023.08.28 |
|---|---|
| [SQLD] SQL 기본 및 활용 Section 01. SQL 기본 (3) 내장형 함수부터 끝까지 (0) | 2023.08.27 |
| [SQLD] SQL 기본 및 활용 Section 01. SQL 기본 (1) SQL기초와 DDL까지 (0) | 2023.08.26 |
| [SQLD] 데이터 모델링의 이해 Section 02. Data Model and Performance (0) | 2023.08.23 |
| [SQLD] 데이터 모델링의 이해 Section 01. Data Modeling (0) | 2023.08.23 |