[RDBMS / JDBC를 이용해 데이터 처리하기] SQL 기초

Hyunjun Kim·2025년 4월 24일
0

Data_Engineering

목록 보기
52/153

2 SQL 기초

SQL은 RDBMS 종류의 데이터베이스 뿐만 아니라, NoSQL, 빅데이터용 도구들에서도 지원하기 때문에 기본적인 사용법은 알고 있어야한다.

💡 SQL 문법의 표준
ANSI SQL을 표준 SQL이라고 한다. DB의 종류마다 ANSI의 규칙의 일부를 지키지 않는 것도 있지만, 사실상 ANSI SQL에 있는 것은 최소한을 지키고 구현해야하는 것으로 자리잡았다.

2.1 SQL 기초 - DML

SQL 중 데이터를 조작하는 동작을 표현하는 언어를 DML이라고 한다. 익숙히 알고 사용하는 CRUD에 해당하는 database query 명령어가 이에 해당한다.

2.1.1 Create (INSERT)

새로운 row를 테이블에 추가할 때 사용하는 명령어다.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
  • 컬럼 순서를 명시하지 않으면 스키마 변경 시 오류 가능성이 있으므로, 항상 컬럼명을 명시하는 것이 좋다.
  • 휴먼 에러를 줄이기 위해서라도 명시적으로 작성하는 습관이 중요하다.

2.1.2 Read (SELECT)

테이블에서 원하는 데이터를 조회할 때 사용하는 명령어이다.

SELECT column1, column2 FROM table_name WHERE 조건 LIMIT n;
  • SELECT *은 개발 환경에서는 허용되나, 운영 환경에서는 반드시 WHERE 또는 LIMIT를 사용할 것이 권장된다.
  • 대용량 테이블에서는 조건 없이 조회하면 DB에 심각한 부하를 줄 수 있다.

2.1.3 Update

조건에 맞는 row를 찾아 특정 컬럼 값을 변경할 때 사용하는 명령어이다.

UPDATE table_name
SET column1 = value1
WHERE 조건;
  • WHERE 조건 없이 실행하면 모든 row가 변경되므로 주의해야 한다.
  • Workbench 등에서는 안전 설정 때문에 WHERE 없이 실행 시 막히며, 설정 해제는 아래와 같다:
SET SQL_SAFE_UPDATES = 0;
  • 항상 unique한 key를 조건절에 포함하는 습관이 중요하다.

2.1.4 Delete

조건에 맞는 row를 삭제할 때 사용하는 명령어이다.

DELETE FROM table_name WHERE 조건;
  • WHERE 절이 없으면 전체 데이터가 삭제되므로 반드시 조건을 명확히 작성해야 한다.
  • 삭제 전에는 SELECT로 먼저 조건에 맞는 데이터가 맞는지 검토하는 것이 좋다.

2.2 SQL 기초 - DDL

2.2.1 Create Table

새로운 테이블을 생성할 때 사용하는 명령어이다. 테이블 이름은 같은 데이터베이스 내에서 유일해야 하며, 컬럼 타입과 제약조건을 함께 지정한다.

CREATE TABLE table_name (
  column1 datatype [NOT NULL],
  column2 datatype,
  ...
  PRIMARY KEY (column1),
  FOREIGN KEY (columnX) REFERENCES other_table(other_id)
);

주요 제약조건 예시

  • PRIMARY KEY (col1) : 단일 기본키
  • PRIMARY KEY (col1, col2) : 복합 기본키
  • FOREIGN KEY (colX) REFERENCES other_table(other_id) : 외래키

주의사항

  • NOT NULL은 필수값 지정용.
  • 외래 키 설정 시 참조 대상 테이블이 먼저 존재해야 함.
  • 외래키 제약조건은 데이터 무결성 유지에 중요.

예시 테이블을 전부 나열하는 대신, 주요 패턴 한 줄로 표현하고 실제 적용은 필요할 때 확장하는 방식이 유지 관리에 유리하다.

2.2.2 Alter Table

기존 테이블에 컬럼을 추가하거나 제거, 변경할 때 사용한다.

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY COLUMN column_name datatype;

주의사항

  • MODIFY는 MySQL 기준이며, DBMS에 따라 다름.
  • PK/FK 변경 시 먼저 삭제하고 재정의 필요할 수 있음.

2.2.3 Drop Table

테이블을 삭제한다. 복구 불가하므로 신중히 실행한다.

DROP TABLE table_name;

주의사항

  • 외래키로 참조 중이면 삭제 불가.

2.2.4 DDL 사용시 주의점

DDL은 테이블, 데이터베이스의 주요 속성을 변경한다. 영향도를 고려하지 않고 동작시킬 경우, 시스템이나 client 에게 문제를 일으킬 수 있다. 이 때문에 DDL은 신중하게 사용하고, 가능하다면 시스템적으로 원하지 않거나, 문제를 일으킬만한 DDL이 발생하지 않도록 하는 것이 중요하다.

DB를 관리하는 인프라가 구축되어있는 기업이나, managed DB를 서비스하는 업체의 솔루션에는 이런 DDL을 기능, 권한을 제한하고, 잘못된경우 되돌릴 수 있는 기능들을 구축해서 사고를 방지한다.
아무튼 DDL은 주의해서 사용해야한다.
걱정 없이 사용할 수 있는 DDL은 Alter Table > add column 정도이다.

2.3 Foreign Key 에 대한 제약 설정

Reference: https://en.wikibooks.org/wiki/Structured_Query_Language/Foreign_Key

내가 참조하고 있는 테이블에서 해당 row 가 없어지면, 그것을 참조하고 있던 테이블의 컬럼들은 어떻게 처리해야할까? 프로그래밍 언어를 이용해서 코딩한다면, 이런 처리를 할 수 있을 것이다. 하지만 SQL만 이용한다면 어떻게 할 수 있을까? 이것을 지원하기위한 기능이 있다.

ON UPDATE/ON DELETE constraint는 Foreign Key 로 참조되고 있는 원본 테이블에서 변경(Update)/삭제(Delete)가 일어날 때, 그것을 참조하고 있는 테이블에서 어떤 동작이 일어나도록 하는 설정이다.

SQL 표준에서는 ON UPDATE/ON DELETE 동작에 대한 스펙을 명시하고 있다. 다만, 내가 사용하는 DB에서 어떤 기능들을 지원하는지, 어떤 문법으로 지원하는지는 추가로 확인해야한다. 표준 스펙과 기능이 다를 수 있다.

2.3.1 MySQL Referential Action

설명에서 부모 테이블은 참조 대상이 되는 테이블, 자식 테이블은 부모 테이블을 참조하는 테이블을 말한다.

  • ON DELETE/UPDATE CASCADE : 부모 테이블(참조대상)의 row가 지워지면, 그것을 참조하고 있는 자식 테이블의 row도 함께 지워진다/업데이트 된다.

  • ON DELETE/UPDATE SET NULL : 부모 테이블(참조대상)의 row가 지워지면/업데이트 되면, 그것을 참조하고 있는 자식 테이블의 해당 row의 참조 컬럼의 값을 null로 바꿔준다. 참조 컬럼이 nullable 이어야한다.

  • ON DELETE/UPDATE SET DEFAULT : 부모 테이블(참조대상)의 row가 지워지면/업데이트되면, 그것을 참조하고 있는 자식 테이블의 해당 row의 참조 컬럼의 값을 default 값으로 바꿔준다. 참조 컬럼에 default 값 설정이 있어야 한다. MySQL의 InnoDB, NDB에서 사용 불가능하다. 사실상 못쓴다고 봐야한다.

  • ON DELETE/UPDATE RESTRICT : 다른 테이블에 참조하고 있는 곳이 있다면 부모 테이블(참조대상)의 row를 지울 수 없다/업데이트할 수 없다. 표준에서는 commit 시점까지의 판단 지연을 명시하지만, MySQL은 해당 스펙으로는 동작하지않는다. transaction 시작 이전에 자식 테이블의 row들이 지워져있어야 한다.

  • ON DELETE/UPDATE NO ACTION (the default): 아무 설정을 하지 않으면, 이 설정으로 동작한다. MySQL 에서는 Restrict 와 같다. 표준에서는 RESTRICT와 NO ACTION은 다른 스펙으로 명시하고 있다.

2.3.2 예제: ON DELETE NO ACTION

child가 있으므로 delete 에서 실패한다.

CREATE TABLE `review_on_no_option` (
	`id` int NOT NULL,
	`content` varchar(2048) DEFAULT NULL,
	`user_id` int DEFAULT NULL,
	`product_id` int unsigned NOT NULL,
	PRIMARY KEY (`id`),
	FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
);
insert into `review_on_no_option` (id, content, user_id, product_id) values (1, 'content1', '1', '1');
delete from product where id = '1';

2.3.3 예제: ON DELETE RESTRICT

child가 있으므로 delete 에서 실패한다.

CREATE TABLE `review_on_delete_restrict` (
  `id` int NOT NULL,
  `content` varchar(2048) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `product_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) on delete restrict
);
insert into `review_on_delete_restrict` (id, content, user_id, product_id) values (2, 'content2', '2', '2');
delete from product where id = '2';

2.3.4 예제: ON DELETE SET NULL

ON DELETE SET NULL 의 contraint 를 적용한 FK column 에 대해서 NOT NULL 로 설정하면, 문법 오류로 create table 이 되지 않는다.

CREATE TABLE `review_on_delete_set_null_to_nonnull` (
  `id` int NOT NULL,
  `content` varchar(2048) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `product_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) on delete set null
);

Parent 테이블의 값이 지워진다. Child table 을 조회하면, FK column이 null 로 바뀐 것을 확인할 수 있다.

CREATE TABLE `review_on_delete_set_null` (
  `id` int NOT NULL,
  `content` varchar(2048) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `product_id` int unsigned,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) on delete set null
);
insert into `review_on_delete_set_null` (id, content, user_id, product_id) values (3, 'review_on_delete_set_null', '3', '3');
select * from `review_on_delete_set_null` where id = '3';
delete from product where id = '3';
select * from `review_on_delete_set_null` where id = '3';

2.3.5 예제: ON DELETE CASCADE

Child 테이블의 row 를 지우지 않았는데도, 참조하고 있는 Parent 테이블의 row 가 지워지니까 함께 지워진 것을 확인할 수 있다.

CREATE TABLE `review_on_delete_cascade` (
  `id` int NOT NULL,
  `content` varchar(2048) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `product_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) on delete cascade
);
insert into `review_on_delete_cascade` (id, content, user_id, product_id) values (4, 'review_on_delete_cascade', '4', '4');
select * from `review_on_delete_cascade` where id = '4';
delete from product where id = '4';
select * from `review_on_delete_cascade` where id = '4';

2.3.6 예제: ON UPDATE CASCADE

Child 테이블의 FK Column의 값을 업데이트 하지 않았는데도, 참조하고 있는 Parent 테이블의 row의 column이 변경되니까 함께 변경된 것을 확인할 수 있다.

CREATE TABLE `review_on_update_cascade` (
  `id` int NOT NULL,
  `content` varchar(2048) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `product_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) on update cascade
);
insert into `review_on_update_cascade` (id, content, user_id, product_id) values (5, 'review_on_update_cascade', '5', '5');
select * from `review_on_update_cascade` where id = '5';
update product set id = '15' where id = '5';
select * from `review_on_update_cascade` where product_id = '5';
select * from `review_on_update_cascade` where product_id = '15';
profile
Hyunjun Kim
Data Analytics Engineer 가 되
이전 포스트

[RDBMS / JDBC를 이용해 데이터 처리하기] RDBMS란

다음 포스트

[RDBMS / JDBC를 이용해 데이터 처리하기] JDBC

0개의 댓글