RADIANT GARDEN

최근 게시글

02_02 DDL·DML 기초 실습

8 min read

개념

하위 태스크 1

doitsql 스키마 구조 확인

SHOW DATABASES , SHOW TABLES , SHOW COLUMNS 로 기본 구조 파악

DBeaver에서 다음 SQL 스크립트를 실행하여 doitsql 데이터베이스와 doit_dml, doit_notnull 테이블을 생성한다.

CREATE DATABASE doitsql;
USE doitsql;
 
CREATE TABLE doit_dml (
col_1 INT,
col_2 VARCHAR(50),
col_3 DATETIME
);
CREATE TABLE doit_notnull (
col_1 INT,
col_2 VARCHAR(50) NOT NULL
);

SHOW 문을 사용하여 생성된 테이블과 테이블의 열에 대한 정보를 확인한다.

생성된 모든 테이블 조회:

SHOW TABLES;

doit_dml 테이블의 열 조회:

SHOW COLUMNS FROM doit_dml;

doit_notnull 테이블의 열 조회:

SHOW COLUMNS FROM doit_notnull;

현재 데이터베이스 스키마 구조를 그림으로 나타내면 다음과 같다.

하위 태스크 2 ~ 3

DML 예제 실행 및 추적

03-1.sql 의 INSERT·UPDATE·DELETE 를 실행하고 각 단계별 결과 확인

제약조건 위반 오류 실험

NOT NULL, 타입 불일치 등 오류 예제를 실행해 에러 메시지 분석

doit_dml 테이블

다음은 doit_dml 테이블에 대한 DML 문장의 목록이다.

-- (1)
INSERT INTO doit_dml (col_1, col_2, col_3) 
VALUES (1, 'DoItSQL', '2023-01-01');
 
-- (2)
INSERT INTO doit_dml (col_1) 
VALUES ('문자 입력');
 
-- (3)
INSERT INTO doit_dml 
VALUES (2, '열 이름 생략', '2023-01-02');
 
-- (4)
INSERT INTO doit_dml 
VALUES (3, 'col_3 값 생략');
 
-- (5)
INSERT INTO doit_dml (col_1, col_2) 
VALUES (3, 'col_3 값 생략');
 
-- (6)
INSERT INTO doit_dml (col_1, col_3, col_2) 
VALUES (4, '2023-01-03', '열순서 변경');
 
-- (7)
INSERT INTO doit_dml (col_1, col_2, col_3) 
VALUES 
    (5, '데이터 입력5', '2023-01-03'),
    (6, '데이터 입력6', '2023-01-03'),
    (7, '데이터 입력7', '2023-01-03');
 
-- (8)
UPDATE doit_dml SET col_2 = '데이터 수정'
WHERE col_1 = 4;
 
-- (9)
UPDATE doit_dml SET col_1 = col_1 + 10;
 
-- (10)
DELETE FROM doit_dml WHERE col_1 = 14;
 
-- (11)
DELETE FROM doit_dml;

목록에 나열된 모든 DML 문장을 하나씩 실행한 뒤 SELECT * FROM doit_dml으로 변경 내용을 확인한 결과는 다음과 같다.

문장 (1) 실행 결과:

문장 (2) 실행 결과:

오류

col_1의 데이터 형식은 INT로 설정되어 있다. 문장 (2)는 col_1 열의 위치에 문자열을 전달하였기 때문에 데이터 형식이 일치하지 않아 오류가 발생한다.

문장 (3) 실행 결과:

문장 (4) 실행 결과:

오류

INSERT 문 사용 시, 열의 이름을 생략하려면 모든 열에 대응하는 값을 VALUES 절에 전달해야 한다. 문장 (4)는 3개의 열 중 2개 열에 대한 값만을 전달하기 때문에 오류가 발생한다.

문장 (5) 실행 결과:

문장 (6) 실행 결과:

문장 (7) 실행 결과:

문장 (8) 실행 결과:

문장 (9) 실행 결과:

문장 (10) 실행 결과:

문장 (11) 실행 결과:

doit_notnull 테이블

다음은 doit_notnull 테이블에 대한 DML 문장의 목록이다.

-- (1)
INSERT INTO doit_notnull (col_1) VALUES (1);

목록에 나열된 모든 DML 문장을 하나씩 실행한 뒤 SELECT * FROM doit_notnull을 실행하여 변경 내용을 확인한 결과는 다음과 같다.

문장 (1) 실행 결과:

오류

doit_notnull의 col_2 열은 NOT NUll 제약 조건이 설정되어 있다. 문장 (1)은 col_2 열에 대한 값을 포함하지 않았기 때문에 실행 시 오류가 발생한다.

하위 태스크 4

도메인 테이블 설계

간단한 도메인을 정하고, PK/제약조건을 포함한 테이블을 직접 설계

TODO 목록에 관한 테이블을 설계한다. 요구 사항은 다음과 같다.

  • TODO는 식별자, 설명, 완료 열을 가진다.
  • 식별자는 숫자 자료형의 값이며 중복될 수 없고 필수적으로 지녀야 하는 열이다.
  • 설명은 20자 이내의 문자열 값을 갖는 열이다.
  • 완료는 참 또는 거짓의 진리값을 갖는 열이다.

다음 블록은 todo_db 데이터베이스에 todo_table 테이블을 생성한다.

CREATE DATABASE todo_db;
USE todo_db;
 
CREATE TABLE todo_table (
	id INT AUTO_INCREMENT PRIMARY KEY,
	description VARCHAR(20) NOT NULL,
	done BOOL DEFAULT FALSE
);

하위 태스크 5

도메인용 DML 작성

설계한 테이블에 대해 INSERT/UPDATE/DELETE 문을 직접 작성·실행

INSERT 문을 실행하여 todo_table 테이블에 데이터를 추가한다.

INSERT INTO todo_table (description) VALUES ('전자책 구매');
INSERT INTO todo_table (description) VALUES ('영화 감상');
INSERT INTO todo_table (description, done) VALUES ('보조 배터리 구매', FALSE);
INSERT INTO todo_table (description, done) VALUES ('커피 챙기기', TRUE);

SELECT * FROM todo_table을 실행한 결과는 다음과 같다.

UPDATE 문을 실행하여 등록된 데이터를 수정한다.

UPDATE todo_table SET done = 1 WHERE id = 2;
UPDATE todo_table SET description = '심청전 구매', done = 1 WHERE id = 1;

SELECT * FROM todo_table을 실행한 결과는 다음과 같다.

DELETE 문을 실행하여 등록된 데이터를 삭제한다.

DELETE FROM todo_table WHERE id = 3;

SELECT * FROM todo_table을 실행한 결과는 다음과 같다.

하위 태스크 6

초기화 스크립트 작성

스키마/테이블/데이터를 한 번에 준비하는 .sql 스크립트 작성

다음은 지금까지의 과정을 일괄적으로 수행하는 스크립트다.

02_02_init.sql:

-- 데이터베이스 생성
DROP DATABASE IF EXISTS todo_db;
CREATE DATABASE todo_db;
 
USE todo_db;
 
-- 테이블 생성
 
CREATE TABLE todo_table (
	id INT AUTO_INCREMENT PRIMARY KEY,
	description VARCHAR(20) NOT NULL,
	done BOOL DEFAULT FALSE
);
 
-- 테스트 데이터 삽입
 
INSERT INTO todo_table (description) VALUES ('전자책 구매');
INSERT INTO todo_table (description) VALUES ('영화 감상');
INSERT INTO todo_table (description, done) VALUES ('보조 배터리 구매', FALSE);
INSERT INTO todo_table (description, done) VALUES ('커피 챙기기', TRUE);
 
UPDATE todo_table SET done = 1 WHERE id = 2;
UPDATE todo_table SET description = '심청전 구매', done = 1 WHERE id = 1;
 
DELETE FROM todo_table WHERE id = 3; 
 
SELECT * FROM todo_table;

스크립트의 실행 결과는 다음과 같다.

하위 태스크 7

스크립트 재실행 검증

스크립트를 여러 번 실행했을 때도 안정적으로 동작하는지 확인

스크립트의 첫 문장 DROP DATABASE IF EXISTS todo_db은 기존 데이터베이스가 존재할 때 자동으로 해당 데이터베이스를 삭제한다. 그 결과 02_02_init.sql 스크립트를 반복하더라도 오류 없이 같은 결과를 얻을 수 있다.

그래프 뷰