SQLD 공부 1일차

SQL 문장의 종류

SQL 문장은 역할에 따라 크게 DML, DDL, DCL, TCL로 구분된다.

DML (Data Manipulation Language)

데이터를 조회하거나 조작할 때 사용하는 명령어이다.

명령어설명

SELECT	데이터 조회
INSERT	데이터 삽입
UPDATE	데이터 수정
DELETE	데이터 삭제

DDL (Data Definition Language)

테이블과 같은 데이터 구조를 생성·변경·삭제할 때 사용하는 명령어이다.

명령어설명

CREATE	테이블 생성
ALTER	테이블 구조 수정
DROP	테이블 삭제
RENAME	테이블 이름 변경

DCL (Data Control Language)

데이터베이스 접근 권한을 제어하는 명령어이다.

명령어설명

GRANT	권한 부여
REVOKE	권한 회수

TCL (Transaction Control Language)

트랜잭션 단위로 작업을 제어하는 명령어이다.

명령어설명

COMMIT	변경 사항 저장
ROLLBACK	변경 사항 취소

SQL 명령어 관련 정리

1. 호스트 프로그램 내부에서 사용하는 DML 명령어를 데이터 부속어(DSL: Data Sub Language)라고 한다.
2. 비절차적 언어는 “무엇을 원하는지”만 작성하고, 절차적 언어는 “어떻게 처리할지”까지 작성한다.

• 비절차적 언어: SQL
• 절차적 언어: PL/SQL(Oracle), T-SQL(SQL Server)

PRIMARY KEY 생성 방법

PRIMARY KEY는 테이블의 각 행을 고유하게 식별하는 제약조건이다.

명시적 방식

CREATE TABLE 테이블명 (
    컬럼명1 데이터타입 NOT NULL,
    컬럼명2 데이터타입 NOT NULL,
    컬럼명3 데이터타입 NULL,

    CONSTRAINT 테이블명_PK PRIMARY KEY (컬럼명1)
);

암시적 방식

CREATE TABLE 테이블명 (
    컬럼명1 데이터타입 PRIMARY KEY,
    컬럼명2 데이터타입 NOT NULL,
    컬럼명3 데이터타입 NULL
);

예시 1

CREATE TABLE EMPLOYEES (
    EMP_ID      INT          NOT NULL,
    NAME        VARCHAR(100) NOT NULL,
    DEPT_ID     INT          NOT NULL,
    HIRE_DATE   DATE         NOT NULL,

    CONSTRAINT EMPLOYEES_PK PRIMARY KEY (EMP_ID)
);

예시 2

CREATE TABLE EMPLOYEES (
    EMP_ID     INT PRIMARY KEY,
    NAME       VARCHAR(100) NOT NULL,
    DEPT_ID    INT NOT NULL,
    HIRE_DATE  DATE NOT NULL
);

테이블 컬럼 정의 변경

DBMS마다 컬럼 수정 문법이 다르다.

Oracle

ALTER TABLE 테이블명
MODIFY (
    컬럼명1 데이터타입 DEFAULT 값 NOT NULL,
    컬럼명2 데이터타입 DEFAULT 값 NOT NULL
);

SQL Server

ALTER TABLE 테이블명
ALTER COLUMN 컬럼명1 데이터타입 [NULL | NOT NULL];

ALTER TABLE 테이블명
ALTER COLUMN 컬럼명2 데이터타입 [NULL | NOT NULL];

차이점 정리

• Oracle은 MODIFY 사용
• SQL Server는 ALTER COLUMN 사용
• Oracle은 여러 컬럼 동시 수정 가능
• SQL Server는 한 번에 하나씩만 수정 가능
• SQL Server는 괄호를 사용하지 않음

NULL

• NULL은 공백('')이나 숫자 0과는 전혀 다른 값이다.
• 값이 존재하지 않거나 아직 정의되지 않은 상태를 의미한다.
• 비교 연산 시 일반 값처럼 동작하지 않는다.

제약조건(Constraint)

데이터 무결성을 유지하기 위해 사용하는 규칙이다.

PRIMARY KEY

• 각 행을 고유하게 식별
• NULL 허용 불가
• 테이블당 하나만 존재 가능
• 자동으로 고유 인덱스 생성

UNIQUE

• 중복 값 허용 불가
• NULL은 허용 가능
• 여러 개 생성 가능

예시 1

CREATE TABLE USERS (
    USER_ID   INT PRIMARY KEY,
    EMAIL     VARCHAR(100) UNIQUE,
    USERNAME  VARCHAR(50) NOT NULL
);

예시 2

CREATE TABLE ORDERS (
    ORDER_ID    INT PRIMARY KEY,
    CUSTOMER_ID INT NOT NULL,
    PRODUCT_ID  INT NOT NULL,
    ORDER_DATE  DATE NOT NULL,

    CONSTRAINT UQ_ORDERS_CUSTOMER_PRODUCT
    UNIQUE (CUSTOMER_ID, PRODUCT_ID)
);

예시 3 (SQL Server)

ALTER TABLE USERS
ADD CONSTRAINT UQ_USERS_PHONE
UNIQUE (PHONE_NUMBER);

NOT NULL

• 반드시 값이 입력되어야 함
• NULL 저장 불가

CHECK

• 조건식을 만족하는 값만 저장 가능

예시 1

CREATE TABLE EMPLOYEES (
    EMP_ID INT PRIMARY KEY,
    AGE INT CHECK (AGE >= 18 AND AGE <= 65)
);

예시 2

CREATE TABLE USERS (
    USER_ID INT PRIMARY KEY,
    GENDER CHAR(1)
    CHECK (GENDER IN ('M', 'F'))
);

예시 3

CREATE TABLE PROJECTS (
    PROJECT_ID INT PRIMARY KEY,
    START_DATE DATE NOT NULL,
    END_DATE DATE CHECK (END_DATE > START_DATE)
);

예시 4 (SQL Server)

CREATE TABLE PRODUCTS (
    CODE VARCHAR(10)
    CHECK (LEN(CODE) = 10)
);

FOREIGN KEY

• 다른 테이블의 PRIMARY KEY를 참조하는 제약조건
• 참조 무결성을 유지하기 위해 사용
• 부모 테이블에 없는 값은 저장할 수 없음

테이블 생성 시 주의사항

1. 테이블명은 의미를 명확하게 표현하는 것이 좋다.
2. 같은 DB 내에서 동일한 테이블명을 사용할 수 없다.
3. 하나의 테이블 안에서 동일한 컬럼명을 중복 사용할 수 없다.
4. 컬럼 정의는 쉼표(,)로 구분한다.
5. SQL 문장은 세미콜론(;)으로 종료한다.
6. 컬럼명은 일관성 있게 작성하는 것이 좋다.
7. 컬럼 생성 시 데이터 타입을 반드시 지정해야 한다.
8. 테이블명과 컬럼명은 문자로 시작해야 한다.
9. 예약어(SELECT, TABLE 등)는 이름으로 사용하지 않는 것이 좋다.
10. 일반적으로 영문, 숫자, _, $, # 사용 가능하다.

CASCADE / SET NULL / SET DEFAULT / RESTRICT

외래키(FK) 제약조건에서 부모 데이터 변경 시 자식 데이터 처리 방식을 지정한다.

옵션설명

CASCADE	부모 값 변경/삭제 시 자식 데이터도 함께 변경/삭제
SET NULL	자식 FK 값을 NULL로 변경
SET DEFAULT	자식 FK 값을 DEFAULT 값으로 변경
RESTRICT / NO ACTION	참조 중이면 수정/삭제 불가

DELETE 시

• RESTRICT: 자식 테이블에서 참조 중이면 부모 데이터 삭제 불가

INSERT 시

• DEPENDENT: 부모 테이블에 PK가 존재할 때만 자식 데이터 입력 가능

컬럼 삭제

ALTER TABLE 테이블명
DROP COLUMN 컬럼명;

참조 무결성

외래키(Foreign Key)를 통해 존재하지 않는 데이터를 참조하지 못하도록 보장하는 규칙이다.

데이터 삽입

컬럼명을 생략하면 모든 컬럼 값을 순서대로 입력해야 한다.

컬럼명 지정

INSERT INTO 테이블명 (컬럼1, 컬럼2)
VALUES (값1, 값2);

전체 컬럼 입력

INSERT INTO 테이블명
VALUES (값1, 값2, 값3);

예시

INSERT INTO TBL
VALUES (5, 500, NULL);

데이터 수정

UPDATE 테이블명
SET 컬럼명 = 새로운값;

SELECT [ALL / DISTINCT]

ALL

• 기본 옵션
• 중복 데이터까지 모두 조회

DISTINCT

• 중복 데이터를 제거하고 조회

SELECT DISTINCT 컬럼명
FROM 테이블명;

TRUNCATE TABLE

• 테이블 구조는 유지하고 데이터만 제거
• 저장 공간을 재사용 가능 상태로 변경
• DROP TABLE은 테이블 자체를 삭제

TRUNCATE TABLE 테이블명;

참고

DELETE FROM 테이블명;

은 가능하지만,

DELETE * FROM 테이블명;

은 문법 오류이다.

트랜잭션(Transaction)

하나의 논리적인 작업 단위를 의미하며, 여러 SQL 작업을 하나로 묶는다.

트랜잭션의 특징 (ACID)

원자성(Atomicity)

• 작업 전체가 모두 수행되거나 전혀 수행되지 않아야 한다.
• 일부만 수행된 상태는 허용되지 않는다.

일관성(Consistency)

• 트랜잭션 수행 전후 데이터 상태가 항상 정상이어야 한다.
• 제약조건과 무결성이 유지되어야 한다.

고립성(Isolation)

• 동시에 여러 트랜잭션이 실행되어도 서로 간섭하지 않아야 한다.

지속성(Durability)

• COMMIT된 결과는 시스템 오류가 발생해도 유지되어야 한다.

COMMIT / ROLLBACK과 DDL 차이

다음 상황을 가정한다.

1. UPDATE 수행
2. CREATE TABLE 수행
3. ROLLBACK 수행

Oracle과 SQL Server는 결과가 다르다.

항목OracleSQL Server

DDL 실행 시 자동 COMMIT	O	X
DDL이 트랜잭션에 포함되는가	X	O
ROLLBACK으로 DDL 취소 가능 여부	X	O
ROLLBACK 후 CREATE TABLE 결과	테이블 생성 유지	테이블 생성 취소