SQL의 속성, 옵션, 기본키, 외래키, 명령어, 트랜잭션 개념

강의 내용 복습 : 코리아IT 신촌점 강의 (2024-05-10,16 강의)

• Tool :
  

🔔 기초 개념

• SQL 또는 데이터베이스 자체에 대한 개념은 링크된 글에 잘 작성되어 있습니다.
• Spring MVC에서 Model과 JPA 및 MyBatis 관련 개념은 링크된 글에 잘 작성되어 있습니다.

🔔 Attribute (속성)

• 데이터베이스에서의 속성이란 Java의 데이터 타입과 비슷한 개념입니다.
• 테이블을 구성하는 각각의 컬럼에는 각각의 속성이 지정됩니다.
• 컬럼의 속성은 저장되는 데이터의 타입과 크기를 정의합니다.
• 아래는 각각의 속성에 대한 설명입니다.

📌 정수

• int : 가장 기본적인 정수 타입이며 레코드 당 4 byte의 메모리 공간을 차지합니다.
• tinyInt : -128 ~ 127 사이의 정수를 저장할 때 사용되며 크기는 1 byte입니다.
• bigInt : 사용자의 고유 ID 등을 저장할 때 사용되며 크기는 8 byte입니다.

📌 소수

• double : 소수가 저장되며 8 byte를 차지합니다. 참고로 정밀하지는 않습니다.
• decimal : 소수가 저장되며 소수 자릿수 등의 정밀도를 직접적으로 지정할 수 있습니다.
• decimal은 decimal(a, b) 형식으로 사용됩니다.
• decimal(a, b)에서 a는 숫자의 전체 개수, b는 소수점 최대 자릿수를 의미합니다.

📌 문자열

• varchar : 자동으로 변경되는 문자열이 저장되며 저장할 글자의 최대 개수를 지정할 수 있습니다.
• char : 고정되는 문자열이 저장되며 초기 지정한 글자 수대로 용량을 차지합니다.
• text : 책 한 권, 블로그 글 등의 긴 문자열을 저장하는 데 사용됩니다.
• 참고로 DBMS마다 varchar를 다르게 입력해야될 수 있습니다.

📌 날짜 : datetime

• 날짜(연-월-일) 및 시간(시-분-초) 등을 저장할 수 있는 속성입니다.
• 참고로 밀리세컨드 또는 마이크로세컨드까지의 정밀도를 제공하는 DBMS도 있습니다.
• datetime 속성은 콘텐츠의 생성, 수정 및 삭제, 로그인 시간 등을 저장할 때 사용됩니다.

🔔 Option

• 테이블에서 칼럼을 정의할 때 프로젝트의 목적에 따라 속성의 옵션을 지정할 수 있습니다.
• 아래는 각각의 옵션과 작동 방식에 대한 설명입니다.

📌 not null

• not null은 해당되는 컬럼에 null 값을 허용하지 않도록 설정하는 옵션입니다.
• not null은 필수 값으로 처리되는 필드에 사용되며 데이터 무결성을 유지시킬 수 있습니다.
• 예를들어 블로그 게시글의 제목, 내용, 작성자 등을 not null로 지정할 수 있습니다.

📌 null

• not null 지정이 안된 컬럼은 기본적으로 null 값을 허용합니다.
• 즉, null 옵션은 필드가 값을 선택적으로 할당받을 수 있음을 명시하는 것입니다.
• 예를들어 필수적이지 않은 사용자의 중간 이름을 저장하는 컬럼에 null을 지정할 수 있습니다.

📌 default

• default 옵션은 레코드에 값이 명시적으로 제공되지 않을 경우에 사용될 기본값을 설정합니다.
• default 옵션은 일관된 데이터 관리를 할 수 있는 옵션입니다.
• default 옵션을 사용하는 상세한 방법은 아래와 같습니다.

/* default를 이용하여 기본값을 공란으로 설정하는 방법 */
mem_addr_detail varchar(100) default ''     comment '상세주소',

/* default를 이용하여 기본값을 현재의 날짜 및 시간으로 설정하는 방법 */
create_date     datetime     default now()  comment '등록일',

/* default를 이용하여 기본값을 null로 설정하는 방법 */
mem_phone_add   varchar(30)  default null   comment '추가 연락처'

📌 auto_increment

• 새로운 레코드가 추가될 때마다 숫자를 자동으로 1씩 증가시키는 기능입니다.
• 즉, 각 레코드에 대한 고유한 식별자를 자동 생성하는 기능입니다.
• auto_increment는 primary key로 지정될 사용자 ID 등에 사용됩니다.

📌 comment

• comment는 테이블의 각 컬러멩 대한 설명을 명확하게 기록할 수 있는 기능입니다.
• comment는 하나의 메타 데이터로, 개발자들끼리의 소통을 위한 수단입니다.
• comment에는 테이블의 컬럼 설명, 스키마의 변경 사항, 코드 리뷰 등에 사용됩니다.

🔔 primary key (기본키)

📌 primary key 개념

• 기본키는 테이블에 저장된 각각의 레코드를 식별하는 데 사용되는 기능입니다.
• 기본키로 설정된 필드의 값은 테이블 내에서 중복될 수 없습니다.
• 따라서 기본키를 이용하면 각각의 레코드를 유일하게 관리할 수 있습니다.
• 또한 기본키로 설정된 값은 변경할 수 없고 null 값을 가질 수도 없습니다.

📌 primary key 사용 예시

• 아래는 고객의 ID를 기본키로 설정한 예시입니다.

create table members (
    mem_id   varchar(100)   not null   comment '아이디',
    mem_pw   varchar(100)   not null   comment '비밀번호',
    primary key(mem_id)
)

🔔 foreign key (외래키)

• 외래키를 이용하면 데이터베이스에서 두 테이블 간의 관계를 정의할 수 있습니다.
• 외래키를 사용하려면 테이블을 주 테이블과 참조 테이블로 구분해야 되기 때문입니다.
• 참조 테이블(sub table)의 열은 주 테이블(main table)의 기본키(primary key)를 참조합니다.
• 아래는 외래키 사용의 장점과 단점을 설명하였습니다.

📌 외래키 사용의 장점

🚩 데이터의 무결성

• 참조 테이블에 데이터를 삽입할 때 해당 데이터가 주 테이블에 존재해야만 됩니다.
• 즉, 데이터베이스 내에 존재하지 않는 데이터에 대한 참조를 방지합니다.

🚩 삭제의 안전성

• 외래키 사용으로 주 테이블의 데이터가 참조되고 있을 때에는 해당 데이터를 삭제할 수 없습니다.
• DBMS에서 해당 데이터를 삭제하려고 하면 관련 에러가 발생됩니다.
• 왜냐하면 주 테이블의 데이터가 다른 테이블에 영향을 미치고 있기 때문입니다.

🚩 데이터의 연쇄적인 삭제

• 다른 경우로, 주 테이블의 데이터를 삭제할 때 관련 데이터를 일괄적으로 삭제할 수 있습니다.
• 예를들면 사용자가 ‘회원 탈퇴’를 할 경우 관련 활동, 정보 등도 함께 삭제되어야 됩니다.
• 연쇄적인 삭제를 할 수 있는 기능인 CASCADE DELETE는 이때 사용됩니다.
• 만일 CASCADE DELETE 기능이 없다면 참조되지 않는 불필요한 데이터가 남을 수 있습니다.

📌 외래키 사용의 난도

🚩 시스템의 성능 저하

• 외래키를 이용하여 데이터의 무결성을 유지하는 것은 서버 과부하의 원인이 될 수 있습니다.
• 예를들어 ‘할인 행사’ 이벤트 기간 동안 대량의 트래픽이 발생될 수 있습니다.
• 왜냐하면 사용자는 장바구니를 활용하여 상품을 등록, 수정, 삭제할 수 있기 때문입니다.
• 데이터의 빈번한 변경이 있을 경우 서버의 비즈니스 처리 속도가 저하될 수 있습니다.

🚩 복잡한 데이터 관리

• 외래키를 많이 사용하는 데이터베이스는 유지/보수가 복잡할 수밖에 없습니다.
• 또한 공동 개발중일 경우 테이블 및 데이터 간의 종속 관계를 공유하기 곤란할 수 있습니다.

🚩 데이터의 연쇄적 삭제의 리스크

• CASCADE DELETE 기능은 분명 유용하지만 예상하지 못한 데이터 손실을 야기할 수 있습니다.
• 예를들어 클라이언트 및 개발자 간의 소통 오류로 인한 문제가 발생될 수 있습니다.
• 데이터베이스 설계에 있어 외래키 사용은 효율적일 수 있으나 이러한 단점을 고려해야 됩니다.

🔔 SQL 명령어

📌 CREATE

• CREATE 명령어는 주로 데이터베이스 또는 테이블 생성을 위해 사용됩니다.
• DBMS에 create database 또는 create table을 입력하여 사용합니다.
• 사용 예시는 아래와 같습니다.

create table js_member (
    /* Column               Attribute       Option      Comment */
    member_id               varchar(50)     not null    comment '아이디',
    member_password         varchar(50)     not null    comment '비밀번호',
    member_name             varchar(50)     not null    comment '이름',
    member_email            varchar(50)     not null    comment '이메일',
    member_phone            varchar(20)     default ''  comment '연락처',
    member_gender           varchar(20)     default ''  comment '성별',
    member_birth            int             default ''  comment '생년월일(yymmdd)',
    member_address          varchar(200)    default ''  comment '기본 주소',
    member_address_detail   varchar(100)    default ''  comment '상세 주소',
    
    primary key(member_id)
);

📌 INSERT

• INSERT 명령어는 주로 테이블의 레코드에 데이터를 저장시키기 위해 사용됩니다.
• 데이터 저장을 희망하는 테이블, 컬럼명, 값을 입력하여 사용합니다.
• 사용 예시는 아래와 같습니다.

insert into js_member (member_id, member_password, member_name, member_email,
                       member_phone, member_gender, member_birth,
                       member_address, member_address_detail)
            values ('admin', '0000', '김소스', 'ceobaek153@gmail.com',
                    '01012341234', '남자', '950525',
                    '서울시 송파구 신천동', '29-5');

📌 SELECT

• SELECT 명령어는 데이터베이스에 저장된 데이터를 조회하기 위해 사용됩니다.
• 데이터 조회를 희망하는 쿼리문을 작성하여 FROM과 함께 사용합니다.
• 사용 예시는 아래와 같습니다.

select * from js_member;

📌 WHERE

• 특정 조건에 맞는 데이터를 조회하거나 관리할 때 사용됩니다.
• 사용 예시는 아래와 같습니다.

select member_id, member_name, member_gender
from js_member
where member_gender = '남자';

📌 ORDER BY

• 데이터 조회 결과를 오름차순 또는 내림차순으로 표시할 때 사용됩니다.
• 사용 예시는 아래와 같습니다.

select *
from js_member
order by member_name asc;

📌 UPDATE

• UPDATE 명령어는 레코드 규모의 수정 작업을 수행할 때 사용됩니다.
• 사용 예시는 아래와 같습니다.

update js_member
set salary =  salary * 1.05
where member_name = '김소스';

📌 ALTER

• ALTER 명령어는 컬럼 규모 이상의 수정 작업을 수행할 때 사용됩니다.
• 사용 예시는 아래와 같습니다.

alter table js_member
add column member_join int;

📌 DELETE

• DELETE 명령어는 레코드 규모 이상의 삭제 작업을 수행할 때 사용됩니다.
• 사용 예시는 아래와 같습니다.

delete from js_member
where member_name = '김소스';

📌 DROP

• DROP 명령어는 테이블 규모 이상의 삭제 작업을 수행할 때 사용됩니다.
• 개발 초기에는 데이터베이스 또는 테이블 설정을 초기화하기 위해 사용될 수 있습니다.
• 사용 예시는 아래와 같습니다.

drop table if exists js_member;

🔔 JOIN

• JOIN 명령어는 2개 이상의 테이블을 연결하여 조회할 수 있는 기능을 제공합니다.
• JOIN 명령어를 목적에 맞게 활용하는 방법은 다양하면서도 난이도가 있습니다.
• 아래는 여러 JOIN 기능에 대해 알아보겠습니다.

📌 INNER JOIN

• INNER JOIN은 2개의 테이블 모두에 존재하는 데이터를 조회할 때 사용됩니다.
• 아래는 JS와 KNU 그룹에서 동일한 이름이 있는 사람을 찾기 위한 쿼리 예시입니다.

select *
from js_member
inner join knu_member on js_member.name = knu_member.name;

📌 LEFT JOIN (Outer Join)

• 좌측 테이블은 모든 행을, 우측 테이블은 조건에 맞는 테이블을 조회할 때 사용됩니다.
• 만일 우측 테이블 행에 조건에 부합되는 데이터가 없으면 컬럼은 null을 반환합니다.
• 아래는 JS와 KNU 그룹에서 동일 인물이 아이디까지 동일한지 확인하는 쿼리 예시입니다.

select *
from js_member
left join knu_member on js_member.name = knu_member.name;

📌 RIGHT JOIN (Outer Join)

• 우측 테이블은 모든 행을, 좌측 테이블은 조건에 맞는 테이블을 조회할 때 사용됩니다.
• 만일 좌측 테이블 행에 조건에 부합되는 데이터가 없으면 컬럼은 null을 반환합니다.
• 아래는 JS와 KNU 그룹에서 동일 인물이 아이디까지 동일한지 확인하는 쿼리 예시입니다.

select *
from js_member
right join knu_member on js_member.name = knu_member.name;

📌 CROSS JOIN

• 2개 테이블의 연관된 데이터를 특정 조건을 사용하지 않고 모두 조회할 때 사용됩니다.
• 사용 예시는 아래와 같습니다.

select *
from js_member
cross join knu_member;

📌 Self Join

• Self Join은 계층 구조 등 내부적으로 연결된 데이터 처리에 유용합니다.
• 그래서 SNS 커뮤니티의 기존 대댓글 기능을 부여할 때 사용되었습니다.
• 하지만 Self Join는 복잡한 계층 구조나 재귀적인 쿼리에서 성능 저하를 야기할 수 있습니다.
• 왜냐하면 데이터가 많아지면 내부 데이터 처리 속도가 저하될 수밖에 없기 때문입니다.
• SNS가 발달하면서 이를 해결하기 위해 인스타그램에서는 @ 댓글 기능을 사용합니다.

📌 ANSI Join

• ANSI Join 방식은 join 조건을 on 키워드를 사용해 명시적으로 표현합니다.
• ANSI Join이 아닌 방식은 join 조건을 WHERE 절에 명시합니다.
• ANSI Join으로 쿼리를 작성하는 방식이 더 명확하다는 이유로 보통 ANSI Join이 사용됩니다.
• 참고롤 ANSI는 American National Standards Institute의 축약어입니다.

🔔 Transaction

📌 Transaction 소개

• Transaction은 작업이 전부 성공적으로 진행될 경우 코드를 실행시키는 기능을 제공합니다.
• 또힌 예약된 작업이 하나라도 실패할 경우 모든 작업을 취소하는 기능을 제공합니다.
• 이와 같은 특성이 있기 때문에 transaction은 입출금과 같은 금융 시스템에 사용됩니다.
• 아래는 transaction을 구현하기 위해 필요한 rollback 및 commit에 대한 설명입니다.

📌 Rollback

• Rollback은 조건에 부합되지 않는 작업을 commit 전 시점으로 회기시키는 기능을 제공합니다.
• 예를들어 송금을 원하는 계좌의 잔액이 부족하면 송금 과정을 취소시키는 기능을 구현합니다.

📌 Commit

• Commit은 조건에 충족되는 작업을 통해 변경된 사항을 DB에 반영하는 기능을 제공합니다.
• Commit의 장점은 데이터의 삽입, 수정, 삭제 등의 내용을 반영구적으로 처리한다는 것입니다.
• 아래는 transaction 사용 예시입니다.

📌 Transaction 사용 예시

• Transaction 등을 통해 간편화 된 입출금 시스템을 구현하였습니다.
• 출금 계좌에서 입금 계좌로 10,000원을 이체하였습니다.
• 이때 rollback 및 commit 관련 비즈니스 로직은 서비스 객체 등에서 처리됩니다.

begin transaction;

update account_out
set balance = balance - 10000
where account_number = '110123123456';

update account_in
set balance = balance + 10000
where account_number = '110321654321';