[SQLD 요약 정리] 2과목 1장 SQL 기본

박선영·2023년 11월 6일

SQLD 자격증

[SQLD 이론 정리]

목록 보기

3/4

👋에필로그

책 [SQLD 자격검정 실전문제]과 한종구 강사님의 시험대비 유튜브 강의를 참고하여 공부하며 정리한 내용입니다.

1장 SQL 기본

1절 관계형 데이터베이스 개요

01_관계형 데이터베이스

💡관계형 데이터베이스

관계에 데이터를 저장하고 관리하며 집합 연산 및 관계 연산을 할 수 있는 데이터베이스이다.

Oracle, MSSQL, MySQL 등이 있다.

데이터베이스(DB) vs 데이터베이스 관리시스템(DBMS)

DB는 어떤 자료구조를 사용하느냐에 따라 달라진다.

DB에는 계층형, 네트워크형, 관계형 데이터베이스가 있다.

계층형 DB
: 트리 자료구조에 데이터를 저장하고 관리하며 1:N 관계를 표현한다.

네트워크형 DB
: 오너와 멘버 형태로 데이터를 저장하며 N:M 관계 표현이 가능하다.

DBMS는 데이터베이스를 관리하기 위한 소프트웨어를 의미한다.

02_연산

집합연산

합집합: 두 관계의 합

교집합: 두 관계에 함께 존재하는 것

차집합: 한 관계에만 존재하며 다른 관계에는 존재하지 않는 것

곱집합: 각 관계에 존재하는 모든 데이터의 조합

관계연산

선택연산: 관계에서 특정 조건에 해당하는 행만 조회

투영연산: 관계에서 특정 조건에 맞는 속성만 조회

결합연산: 여러 관계에서 공통된 속성을 통해 새로운 관계 생성

나누기연산: 기준 관계에서 나누는 관계가 가지는 속성과 동일한 행만 추출하여 중복된 행 제거

03_SQL

SQL 문장 종류

🔶 데이터 정의어 DDL
: 테이블이나 컬럼 같이 데이터 구조를 정의하기 위한 명령어
- CREATE, DROP, RENAME, ALTER, TRUNCATE

🔶 데이터 조작어 DML
: 데이터를 조회 및 검색하기 위한 명령어
- SELECT, INSERT, UPDATE, DELETE

🔶 데이터 제어어 DCL
: 데이터베이스 접근 및 사용 권한을 부여하거나 회수하기 위한 명령어
- REVOKE, GRANT

🔶 트랜잭션 제어어 TCL
: 트랜잭션 별로 제어하기 위한 명령어로 데이터 제어어라고도 함
- SAVEPOINT, COMMIT, ROLLBACK

트랜잭션의 특징

🔶 원자성 Atomicity
: 데이터베이스 내 연산의 전부가 실행되거나 실행되지 않아야 한다.
트랜잭션이 완전히 끝나지 않았다면 실행되기 전의 상태와 동일하다.

🔶 일관성 Consistency
: 트랜잭션 결과로 데이터베이스 상태가 모순되지 않고 일관성을 유지해야 한다.

🔶 고립성 Isolation
: 트랜잭션 실행 중의 중간결과는 다른 트랜잭션이 접근할 수 없다.

🔶 영속성 Durability
: 트랜잭션이 완료되면 그 결과는 영구적으로 보장되어야 한다.

SQL 실행 순서

파싱 Parsing
: SQL 문법을 확인하고 구문을 분석하고, Library Cache에 저장한다.

실행 Execution
: 옵티마이저가 생성한 실행 계획에 따라 SQL문을 실행한다.

인출 Fetch
: 데이터를 읽어 전송한다.

2절 SELECT문

01_SELECT

💡SELECT문

테이블에 저장된 데이터를 조회하기 위한 명령어로, 특정 행 또는 컬럼을 조회할 수 있다.
SELECT 컬럼명
FROM 테이블명;

DISTINCT

SELECT절의 컬럼에 위치해 중복을 제거한 결과를 출력한다.

Alias

테이블 또는 컬럼 명이 길거나 함수를 적용하였을 때 이름을 변경한다.
SELECT 컬럼명 AS COL
FROM 테이블명 AS TAB;

02_ORDER BY

ORDER BY

데이터 조회 시 오름차순 또는 내림차순으로 정렬할 수 있다.

정렬은 가장 마지막에 수행되는 명령이다.

정렬은 메모리를 많이 사용하기 때문에 SQL이 느려질 수 있다.
SELECT 컬럼명
FROM 테이블명
ORDER BY 컬럼명 ASC/DESC;

03_WHERE절

WHERE절

원하는 행 또는 컬럼만 조회하기 위해 조건을 줄 때 사용한다.
SELECT 컬럼명
FROM 테이블명
WHERE 조건;
연산자

연산자 설명
= ~와 같다
< ~보다 작다
> ~보다 크다
!= / <> / NOT ~가 아니다
LIKE %문자% '문자'가 들어간 경우
BETWEEN A AND B A와 B 사이
IN LIST LIST 안에 있는 경우
IS NULL NULL인 경우

연산자	설명
=	~와 같다
<	~보다 작다
>	~보다 크다
!= / <> / NOT	~가 아니다
LIKE %문자%	'문자'가 들어간 경우
BETWEEN A AND B	A와 B 사이
IN LIST	LIST 안에 있는 경우
IS NULL	NULL인 경우

3절 함수

01_NULL

💡NULL

데이터 정의가 없는 알수 없는 값을 의미한다.
NULL의 연산 결과는 NULL 값이다.

NULL 조회

NOT을 사용하면 NULL이 아닌 값을 조회할 수 있다.
SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 IS (NOT) NULL;

02_문자 함수

내장 함수 중 문자를 다루는 함수

LOWER/UPPER

영문의 소문자화 또는 대문자화 하는 함수
SELECT LOWER/UPPER(컬럼명) AS 소문자/대문자
FROM 테이블명;

LENGTH

문자열의 길이를 반환하는 함수
SELECT LENGTH(컬럼명) AS 길이
FROM 테이블명;

SUBSTR

전체 문자열 중 START부터 END까지의 일부 문자열을 반환하는 함수
SELECT SUBSTR(컬럼명, START, END) AS 일부
FROM 테이블명;

INSTR

전체 문자열 중 특정 문자 'X'의 위치를 반환하는 함수
SELECT INSTR(컬럼명, 'X') AS 위치
FROM 테이블명;

LPAD/RPAD

문자열을 L길이가 되도록 왼쪽/오른쪽에 특정 문자 'X'를 채운 문자열을 반환하는 함수
SELECT LPAD/RPAD(컬럼명, L, 'X') AS 문자열채움
FROM 테이블명;

TRIM/LTRIM/RTRIM

문자열에서 공백을 제거한 문자열을 반환하는 함수
SELECT TRIM/LTRIM/RTRIM(컬럼명) AS 공백제거
FROM 테이블명;

REPLACE

문자열에서 'OLD'문자를 'NEW' 문자로 변환한 문자열을 반환하는 함수
SELECT REPLACE(컬럼명, OLD, NEW) AS 문자변환
FROM 테이블명;

03_숫자 함수

ROUND

숫자를 N번째 소수점 자리에서 반올림한 숫자를 반환하는 함수
SELECT ROUND(컬럼명, N) AS 반올림
FROM 테이블명;

TRUNCATE

숫자를 N번째 소수점 자리에서 버린 숫자를 반환하는 함수
SELECT TRUNCATE(컬럼명, N) AS 버림
FROM 테이블명;

CEIL/FLOOR

숫자를 N번째 소수점 자리에서 올림/내림한 숫자를 반환하는 함수
SELECT CEIL(컬럼명, N) AS 올림 FLOOR(컬럼명, N) AS 내림
FROM 테이블명;

POWER

숫자를 N번 제곱한 숫자를 반환하는 함수
SELECT POWER(컬럼명, N) AS 제곱수
FROM 테이블명;

04_날짜 함수

SYSDATE / SYSTIMESTAMP

현재 시스템 날짜 및 시간을 반환해주는 함수
SELECT SYSDATE, SYSTIMESTAMP
FROM 테이블명;

ADD_MONTHS / NEXT_DAY / LAST_DAY

특정 일/월 만큼 더한 날짜를 반환해주는 함수
SELECT ADD_MONTHS(SYSDATE, 6) AS 6개월후, 
	   NEXT_DAY(SYSDATE, '월요일') AS 다음 월요일
       LAST_DAY(SYSDATE) AS 어제날짜
FROM 테이블명;

TO_CHAR

특정 날짜 및 시간 후의 날짜 및 시간을 반환해주는 함수

SELECT TO_CHAR(SYSDATE + 1/24/60/60, YYYY/MM/DD/HH24:MI:SS) AS 1초뒤,
	   TO_CHAR(SYSDATE + 1/24/60, YYYY/MM/DD/HH24:MI:SS) AS 1분뒤,
       TO_CHAR(SYSDATE + 1/24, YYYY/MM/DD/HH24:MI:SS) AS 1시간뒤,
       TO_CHAR(SYSDATE + 1, YYYY/MM/DD/HH24:MI:SS) AS 1일뒤,
FROM 테이블명;

4절 GROUP BY

01_GROUP BY

GROUP BY

집합 내에서 하나 혹은 여러 행을 그룹화한 후 집계 함수를 이용하여 계산하는 명령어이다.
SELECT GROUP_COL, 집계함수(COL1) AS 집계
FROM 테이블명
GROUP BY GROUP_COL;

02_HAVING

HAVING

HAVING절을 이용하여 GROUP BY가 적용된 상태에 조건을 추가할 수 있다.

WHERE절은 GROUP BY 연산 전에, HAVING절은 GROUP BY 연산 이후에 적용되는 조건절이다.
SELECT GROUP_COL, SUM(COL1) AS 합계, AVG(COL2) AS 평균
FROM 테이블명
GROUP BY GROUP_COL
HAVING AVG(COL2) > 10000;

03_집계 함수

집계 함수는 NULL을 제외하고 연산을 한다.

COUNT

특정 컬럼의 행의 개수를 반환하는 함수로,
컬럼명 대신 *을 사용할 경우 NULL을 포함한 행의 수를 반환한다.
SELECT GROUP_COL, COUNT(COL1) AS NULL제외행, COUNT(*) AS 전체행
FROM 테이블명
GROUP BY GROUP_COL;

SUM / AVG

특정 컬럼의 합계와 평균을 반환하는 함수
SELECT GROUP_COL, SUM(COL1) AS 합계, AVG(COL2) AS 평균
FROM 테이블명
GROUP BY GROUP_COL;

MIN / MAX

특정 컬럼의 최소와 최대를 반환하는 함수
SELECT GROUP_COL, MIN(COL1) AS 최소, MAX(COL2) AS 최대
FROM 테이블명
GROUP BY GROUP_COL;

5절 분기문

01_NVL

NVL

NULL인 값을 제거하기 위한 일반 함수로 NULL 대신 특정 값(VALUE)으로 변환할 수 있다.
SELECT NVL(COL, VALUE) AS NULL변환
FROM 테이블명;

02_NVL2

NVL2

NVL의 개선된 버전의 함수로, NULL이 아닐 경우 VALUE1으로 NULL일 경우 VALUE2로 변환할 수 있다.
SELECT NVL2(COL, VALUE1, VALUE2) AS NULL변환
FROM 테이블명;

03_DECODE

DECODE

한 컬럼에 여러 조건을 적용하여 변환하는 함수
조건 1에 해당하는 경우 값1로 조건 2에 해당하는 경우 값2로 변환된다.
SELECT DECODE(COL, CONDI1, VALUE1, CONDI2, VALUE2, ...) AS DECODE변환
FROM 테이블명;

04_CASE WHEN

CASE WHEN

여러 컬럼 및 조건으로 변환하는 경우 사용하는 함수로,
하나의 컬럼 값만 기준이 되는 경우와 여러 컬럼이 기준이 되는 경우 문법을 달리해서 사용할 수 있다.

CASE WHEN은 나열된 순서대로 조건을 비교한다.
SELECT CASE COL1 WHEN A THEN 1 WHEN B THEN 2 AS 기준하나,
	   CASE WHEN COL1='A' THEN 1 WHEN COL1='B' THEN 2 AS 기준여러개
FROM 테이블명;

6절 ORACLE 행 설정

ROWNUM

ORACLE에서 출력 행을 설정하는 함수
MSSQL에서는 TOP으로 대체하여 사용할 수 있다.
SELECT *
FROM 테이블명
WHERE ROWNUM < 3;

SELECT TOP(2) *
FROM 테이블명;

ROWID

ORACLE에서 행이 생성될 때 부여되는 행 고유 ID 함수
SELECT ROWID, *
FROM 테이블명;

WITH

일종의 임시적인 뷰 테이블로, 데이터가 저장되지 않은 테이블이다.

특정 SQL문을 재사용하기 위해 사용한다.

SQL 실행 속도에 유리하게 작용할 수 있다.

하나의 SQL문에 여러 개의 WITH문을 사용할 수 있다.
WITH SQL1 AS (SELECT * 
			  FROM 테이블명
              WHERE 조건),
	 SQL2 AS (SELECT * 
			  FROM 테이블명
              WHERE 조건)
SELECT * FROM SQL1
UNION ALL
SELECT * FROM SQL2

7절 DDL

01_DDL

DDL

테이블 또는 데이터베이스 내 객체 등의 구조를 정의하는 명령어

구조를 생성하거나 변경 또는 삭제할 때 사용한다.

명령어 설명
CREATE 데이터베이스의 객체 생성
ALTER 생성된 객체의 구조 변경
DROP 생성된 객체 제거
TRUNCATE 테이블의 모든 데이터 삭제 및 초기화하여 저장공간 반납

명령어	설명
CREATE	데이터베이스의 객체 생성
ALTER	생성된 객체의 구조 변경
DROP	생성된 객체 제거
TRUNCATE	테이블의 모든 데이터 삭제 및 초기화하여 저장공간 반납

CREATE TABLE

테이블 생성하는 DDL문

PK, FK CHECK 등의 제약조건, 컬럼 및 데이터 타입도 정의할 수 있다.
CREATE TABLE 테이블명
(
	ID VARCHAR2(10) NOT NULL PRIMARY KEY,
    EMPLOYEE_ID VARCHAR2(10) NOT NULL REFERENCES EMPROYEES (ID), #참조
    SALARY NUMBER,
    REG_DATE DATE
);

+ 데이터 타입

🔘 CHAR(s)
: 고정 길이 문자열로, 최대 길이 s만큼 공간을 채운다. ('AA_' = 'AA')

🔘 VARCHAR(s)
: 가변 길이 문자열로, 할당된 변수 값의 바이트만 적용한다. ('AA_' != 'AA')

🔘 NUMBER
: 정수, 실수 등의 숫자

🔘 DATE
: 날짜와 시각

ALTER

테이블이나 컬럼 구조를 변경하는 DDL문

제약조건을 추가하거나 삭제할 수 있다.

# 컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN COL TO NEW_COL;

# 컬럼 데이터 타입변경
ALTER TABLE 테이블명 MODIFY COL VARCHR2(5) NOT NULL;

# 제약조건 삭제
ALTER 테이블명 DROP PRIMARY KEY;

# 제약조건 추가 - PRIMARY KEY 조건 추가
ALTER 테이블명 ADD CONSTRAINT 테이블명_컬럼명_PK PRIMARY KEY(컬럼명);

DROP

테이블이나 특정 객체를 삭제하는 DDL문

테이블 내 데이터와 구조를 삭제할 수 있으며, CASCADE CONSTRAINT 옵션은 종속된 제약조건도 모두 삭제한다.
DROP TABLE 테이블명 (CASCADE CONSTRAINT);

TRUNCATE

테이블 구조는 남긴 채 내부 데이터 및 행만 삭제하는 DDL문

테이블 삭제 시 테이블이 차지하던 저장공간을 반납한다.
TRUNCATE TABLE 테이블명;

VIEW

실제 데이터를 가지지 않고 SQL문 저장

참조된 테이블이 변경되면 뷰의 결과도 변경된다.
CREATE VIEW 뷰명 AS
	SELECT * FROM 테이블명;
장점

보안에 적합하다.

단순하게 불러올 수 있다.

하나의 테이블로 여러 결과를 가진 뷰를 생성할 수 있다.

단점

삽입, 갱신 등 연산이 제한적이다.

데이터 구조를 쉽게 변경할 수 없다.

인덱싱하기 어렵다.

8절 DML

01_DML

DML

데이터베이스 내 데이터를 조회, 입력, 수정, 삭제하는 명령어

명령어 설명
SELECT 주어진 조건에 맞게 데이터 조회
INSERT 데이터 입력 및 저장
UPDATE 데이터 수정
DELETE 행 삭제

명령어	설명
SELECT	주어진 조건에 맞게 데이터 조회
INSERT	데이터 입력 및 저장
UPDATE	데이터 수정
DELETE	행 삭제

SELECT

테이블 내 데이터를 주어진 조건에 맞게 조회하는 DML문
SELECT 컬럼명
FROM 테이블명;

INSERT

테이블에 데이터를 입력 및 저장하는 DML문

INSERT INTO 테이블명 VALUES (VALUE1, VALUE2, VALUE3, ...);
INSERT INTO 테이블명(COL1, COL2, ...) VALUES (VALUE1, VALUE2, ...);

UPDATE

테이블에 데이터를 수정하는 DML문
UPDATE 테이블명 SET 컬럼명 = VALUE
WHERE 조건;

9절 DCL

01_DCL

DCL

데이터베이스 내 유저에게 권한을 부여하거나 회수하는 명령어

명령어 설명
GRANT 유저에게 권한 부여
REVOKE 유저에게 권한 회수

명령어	설명
GRANT	유저에게 권한 부여
REVOKE	유저에게 권한 회수

GRANT

유저에게 각종 오브젝트 권한을 부여할 수 있는 DCL문

시스템 권한: DBA, CREATE SESSION, CREATE USER, SYSDBA

명령어 설명
ALTER 테이블 수정 권한
DELETE 삭제 권한
ALL 테이블에 대한 모든 권한
INDEX 인덱스를 생성하는 권한
INSERT 지정된 테이블에 대한 데이터 추가 권한
REFERENCED 지정된 테이블을 참조하는 제약조건을 생성할 수 있는 권한
SELECT 지정된 테이블 선택 권한
UPDATE 지정된 테이블 갱신 권한
CREATE USER MY_USER IDENTIFIED BY 1234
GRANT CREATE SESSION TO MY_USER;

명령어	설명
ALTER	테이블 수정 권한
DELETE	삭제 권한
ALL	테이블에 대한 모든 권한
INDEX	인덱스를 생성하는 권한
INSERT	지정된 테이블에 대한 데이터 추가 권한
REFERENCED	지정된 테이블을 참조하는 제약조건을 생성할 수 있는 권한
SELECT	지정된 테이블 선택 권한
UPDATE	지정된 테이블 갱신 권한

ROLE

권한과 유저사이에 존재하는 개념으로, 다양한 권한을 역할에 따라 유저에게 부여할 수 있다.
유저에게 권한이 있는 ROLE을 부여함으로써 간단하게 관리할 수 있다.
CREATE ROLE MY_ROLE
GRAND CREATE SESSION, CREATE TABLE TO MY_ROLE
GRANT MY_ROLE TO MY_USER;

02_TCL

Transcation Control Language(TCL)

트랜잭션을 관리하는 명령어

트랜잭션은 데이터베이스의 논리적인 연산 단위로, 밀접히 관련되어 분리할 수 없는 한 개 이상의 데이터베이스 조작을 의미한다.

하나의 트랜잭션은 하나 이상의 SQL문을 가지고 있다.

하나의 트랜잭션은 모두 적용되거나 적용되지 않아야 한다.

명령어 설명
COMMIT 트랜잭션 단위에서 발생한 작업 저장
SAVEPOINT 트랜잭션의 특정 지점 저장
ROLLBACK COMMIT되지 않은 수행된 작업 되돌리기

명령어	설명
COMMIT	트랜잭션 단위에서 발생한 작업 저장
SAVEPOINT	트랜잭션의 특정 지점 저장
ROLLBACK	COMMIT되지 않은 수행된 작업 되돌리기

📝정리

2과목 1장에서는 SQL의 기본에 대해 소개하였다.
SQL 문장의 종류가 중요하니까 데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL), 트랜잭션 제어어(TCL)를 구분하고 각 문장에 어떠한 함수들이 있는지 잘 알고 있는 것이 중요할 것 같다.

암기도 좋지만 실습을 통해 익히는 것이 가장 효과적이니까 직접 실습해보는 것을 추천한다.

박선영

데이터를 만지는 사람

이전 포스트

[SQLD 요약 정리] 1과목 2장 데이터 모델과 성능

다음 포스트