데이터베이스와 JDBC

데이터베이스의 개요

 

데이터베이스란?

데이터를 체계적으로 관리할 수 있도록 해주는 소프트웨어로, 대부분의 프로그램은 어떠한 형태로든 데이터베이스를 사용하고 있다고 볼 수 있다.

 

■ 데이터베이스

데이터베이스는 사전적으로 여러 사람이 공유하여 사용할 목적으로 체계화하여 통합, 관리하는 데이터 집합을 의미한다.

즉 데이터베이스는 방대한 데이터를 쉽게 검색하거나 찾을 수 있도록 체계적으로 분류하고 정리해둔 정보의 집합이라고 할 수 있으며 디지털화된 정보만을 의미하는 것은 아니다.

 

■ DBMS

현실적으로 데이터베이스는 컴퓨터 도움 없이는 운영이 어렵기 때문에 DBMS(DataBase Management System)라고 불리는 소프트웨어 시스템을 사용하게 된다.

널리 알려진 Oracle, MySQL, IBMDB2, MS SQL 등은 모두 DBMS 이다.

DBMS는 효과적인 데이터 파일 관리와 운영을 위한 구조와 함께 인덱싱, 캐싱, 네트워크 서버, 사용자 및 권한 관리, 백업/복원, 클러스터링 등 다양한 기능을 제공한다.

데이터베이스와 데이터베이스 관리 시스템인 DBMS는 다른 의미이지만 보통 데이터베이스라고 하면 DBMS를 포함한 개념을 말한다.

 

데이터베이스의 일반적인 특징은 다음과 같다.

• 데이터 중복을 최소화할 수 있다.
• 데이터를 쉽게 공유할 수 있다.
• 일관성, 무결성, 보안성이 유지된다.
• 최신 데이터를 유지할 수 있다.
• 데이터의 표준화가 가능하다.
• 데이터의 논리적, 물리적 독립성이 유지된다.
• 데이터 접근이 용이하다.
• 데이터 저장 공간을 절약할 수 있다.

 

데이터베이스의 종류란?

가장 대표적인 것으로 관계형 데이터베이스이며 그 외에도 계층형, 네트워크형, 객체형, NoSQL 형 등이 있다.

현재 대부분의 데이터베이스는 관계형 데이터베이스인데 최근 들어 NoSQL 형태의 데이터베이스 활용이 늘어나는 추세이다.

 

※ 관계형 데이터베이스

RDBMS(Relational DataBase Management System)는 전통적이고 가장 보편적 형태의 데이터베이스로 데이터 구조를 테이블이라고 하는 칼럼과 로우 구조로 정의하고 관리하며 SQL을 사용해 데이터를 관리한다.

특히 테이블과 테이블의 관계 지정을 통해 연관성 있는 데이터를 체계적으로 구조화할 수 있다.

 

 

관계형 데이터베이스의 장점은 다음과 같다.

• 다양항 용도로의 사용이 가능하며 높은 성능을 보여준다.
• 데이터의 일관성을 보여준다.
• 정규화를 통해 갱신 비용을 최소화할 수 있다.

 

관계형 데이터베이스의 단점은 다음과 같다.

• 데이터 구조의 변경(칼럼의 수정, 확장)이 어렵다.
• 빠른 속도를 요구하는 단순한 처리에 대응하기 어렵다.
• 데이터 관계는 유용하지만 그로 인한 처리 속도가 저하가 발생할 수 있다.

 

관계형 데이터베이스는 구조화되어 있고 동일한 구조를 가지는 데이터를 다룰 때 유리하다.

각각 독립된 정보를 가지면서 필요할 때 관련 정보를 연계하는 구조의 경우에 관계형 데이터베이스가 적합하다.

 

 

※ NoSQL 데이터베이스

말 그대로 SQL을 '사용하지 않는다(No)'는 의미로, 'SQL을 사용하는 전통적인 RDBMS가 아니다'라는 의미로 사용되는 데이터베이스를 말한다.

SQL과 다를 수 있지만 그럼에도 데이터 관리를 위해 별도의 쿼리 언어아 구조는 여전히 존재한다.

NOSQL은 RDBMS의 가장 큰 특징인 테이블 형태의 데이터 구조를 사용하지 않기 때문에 형태가 고정되지 않은 비정형 데이터 처리에 유용하다.

데이터 구조는 JSON 구조의 Document, Map 구조의 Key-Value, Big Table 등을 사용하며 NoSQL 데이터베이스의 특징이 되기도 한다.

대표적으로 MongoDB, Redis, Casandra, Hbase, CouchDB 등이 있다.

 

 

관계형 데이터베이스와 달리 NoSQL은 모든 데이터가 동일한 구조를 가지고 있지 않은 경우 유리하다.

또한 빠른 처리 속도를 위해 필요한 데이터를 다른 테이블 등에서 참조하지 않고 데이터 자체에 포함하는 구조에 적합하다.

NoSQL은 일반적으로 데이터 구조화나 관리보다는 다양한 구조의 데이터를 빠른 속도로 처리하는 용도로 사용된다.

특히 데이터 발생량이 많고 과거보다는 현재의 데이터가 더 중요한 SNS나 모바일 앱 혹은 데이터 분석을 위해 일회성으로 대용량 데이터를 사용하는 경우가 적합하다.

 

 

관계형 데이터베이스

 

※ 테이블

테이블은 관계형 데이터베이스에서 데이터 관리의 기본 구조다.

데이터가 가지는 공통적 속성을 모아 정의한 칼럼(필드)으로 구성되며 칼럼에 저장되는 데이터는 숫자형, 문자형, 날짜형, 불형 등으로 구분된다.

 

예를 들어서 이름, 대학, 생년월일, 이메일로 공통된 정보를 가진 학생정보 테이블을 보도록 한다.

이름	대학	생년월일	이메일
김길동	AA 대학교	1999-10-21	kim@aa.com
박사랑	BB 대학교	2000-01-21	park@bb.com
나최고	CC 대학교	1998-07-11	na@cc.com
김길동	BB 대학교	1999-03-10	kim@bb.com
홍길동	AA 대학교	1999-12-10	hong@aa.com

 

■ 칼럼 

테이블을 구성하는 기본 속성으로 필드, 어트리뷰트라고도 불린다.

 

■ 로우

하나의 데이터셋을 의미하며 레코드 혹은 튜플이라고 한다.

 

■ 자료형

칼럼에 들어갈 수 있는 데이터 유형으로 일반적으로 숫자형, 문자형, 날짜형, 불형 등으로 구분된다.

 

 

※ 제약조건

칼럼에 부여되는 일종의 속성으로 저장될 데이터에 대한 요구사항이라고 할 수 있다.

제약 조건은 데이터베이스 자체적으로 저장될 데이터에 대한 요구 조건을 설정하는 것으로 제약 조건을 벗어나는 데이터는 원천적으로 차단된다.

이는 데이터베이스의 무결성(integrity)을 지키기 위한 방법이 된다.

 

데이터베이스 제약 조건으로 다음과 같다.

종류	설명	특징
NOT NULL	칼럼에 데이터를 비워둘 수 없다.	기본은 NULL 가능으로 설정되어 있다.
UNIQUE	기본키는 아니지만 중복값을 허용하지 않는다.	기본은 중복 가능으로 설정되어 있다.
Primary Key	데이터의 유일성을 보장하는 칼럼이다.	NOT NULL + UNIQUE, 테이블에 하나만 설정 가능하다.
Foreign Key	두 테이블 간의 참조 무결성을 보장한다.	외래키로 지정된 칼럼값은 참조 테이블에 존재하는 값만 사용 가능하다.
CHECK	저장할 값의 범위 등을 지정한다.	숫자 범위, 다른 칼럼값 비교 결과, 특정 문자 중 하나 등이다.
IDENTITY	유일한 값을 가지는 id 칼럼이다.	PK, Not NULL, auto_increment가 자동 적용된다.

 

※ 키

키는 관계형 데이터베이스의 제약 조건 중 하나로, 데이터의 유일성 및 관계 설정을 위해 사용된다.

적절한 키의 사용은 데이터베이스 설계에서 매우 중요한 요소이다.

 

기본키란?

기본키(Primary Key, PK)는 주키, 프라이머리키라고도 불린다.

테이블에 저장된 레코드(로우)를 서로 구분할 수 있도록 특정 칼럼에 설정하는 제약 조건이다.

기본키가 존재하지 않으면 데이터가 중복되고 특정 데이터 검색에 오류가 발생하기 때문에 꼭 설정되어 있어야 한다.

예를 들어 이름은 중복이 가능하기 때문에 별도의 중복되지 않는 값이 기본키로 필요하다.

쉽게 생각할 수 있는 것은 학번, 주민등록번호 등이 고유번호다.

그러나 이러한 번호는 개인정보에 해당하므로 외부에 노출되지 않는 것이 좋기 때문에 기본키로 부적절하다.

 

보통 기본키는 시퀀스라고 불리는 단순 증가 값을 사용한다.

단순 증가 값이란 1부터 시작해서 1씩 단계적으로 증가하는 숫자로, 단순히 데이터를 서로 구분하는 용도로만 사용한다고 이해할 수 있다.

기본키가 추가된 학생정보 테이블은 다음과 같다.

번호(PK)	이름	대학	생년월일	이메일
1	김길동	AA 대학교	1999-10-21	kim@aa.com
2	박사랑	BB 대학교	2000-01-21	park@bb.com
3	나최고	CC 대학교	1998-07-11	na@cc.com
4	김길동	BB 대학교	1999-03-10	kim@bb.com
5	홍길동	AA 대학교	1999-12-10	hong@aa.com

 

외래키란?

외래키(Foreign Key, FK)는 테이블 간의 관계를 설정하기 위해 사용하며 참조 무결성을 제공하기 위한 용도로 사용된다.

예를 들어서 다음과 같이 대학정보 테이블을 추가하여 이를 참조할 수 있도록 외래키를 등록해 사용하는 학생정보 테이블은 다음과 같다.

 

대학정보 테이블

대학코드(PK)	대학명	위치
10011	AA 대학교	서울
10012	BB 대학교	인천
10013	CC 대학교	대전

 

외래키가 추가된 학생정보 테이블

번호(PK)	이름	대학코드(FK)	생년월일	이메일
1	김길동	10011	1999-10-21	kim@aa.com
2	박사랑	10012	2000-01-21	park@bb.com
3	나최고	10013	1998-07-11	na@cc.com
4	김길동	10012	1999-03-10	kim@bb.com
5	홍길동	10011	1999-12-10	hong@aa.com

 

 

※ 시퀀스, 트랜잭션

시퀀스는 기본키 칼럼을 관리하기 위해 사용하는 데이터베이스 객체로, 보통 데이터를 추가할 때 순차적으로 증가하는 값을 자동으로 생성한다.

 

트랜잭션(transaction)은 프로그램에서 어떤 이벤트가 발생했을 때 하나의 테이블에만 데이터를 변경하는 경우보다 여러 테이블의 데이터를 차례로 변경해야 하는 경우가 더 많이 발생한다.

예를 들어 은행에서 계좌이체를 한다고 생각해본다.

동일 은행의 A계좌에서 B계좌로 이체한다고 했을 때 계좌는 테이블로 볼 수 있다.

이때 A계좌에서 100만 원을 차감한 후 B계좌에 100만 원을 추가하면 계좌이체가 완료된다.

이때 A계좌에서 차감 후 B계좌에서 추가하는 과정에서 에러가 발생하면 A계좌에서 차감했던 금액은 다시 원래대로 되돌려야 한다.

 

이처럼 트랜잭션은 하나의 논리적 기능을 수행하기 위해 여러 작업을 묶어서 처리하는 것을 의미한다.

이러한 트랜잭션은 프로그램에서 단위 작업마다 성공 여부를 체크해서 진행할 수도 있으나 이 경우 개발자가 로직을 잘못 설계할 가능성도 있기 때문에 데이터베이스 혹은 미들웨어 레벨에서 처리할 수 있어야 한다.

기본적으로 데이터베이스는 다음과 같은 트랜젝션 관리를 위한 명령을 제공한다.

• commit : 모든 데이터의 변화를 실제 적용한다.
• rollback : 문제 발생 시 현재까지의 변화를 원래대로 되돌린다.

 

 

 

H2 데이터베이스

 

H2 데이터베이스란?

H2 데이터베이스는 보통 임베디드 데이터베이스로 알려져 있으며 MySQL과 같은 관계형 데이터베이스 관리 시스템이다.

MySQL과 달리 복잡한 프로그램의 설치가 필요 없고 데이터베이스 파일만 있으면 언제든지 데이터베이스를 실행할 수 있어 프로그램에 포함해서 배포하는 것도 가능하다.

 

H2 설치 방법

 

1. https://www.h2database.com/ 에 접속하여 최신 버전의 H2 데이터베이스를 내려받아 설치한다.

 

 

 

2. 내려받은 설치 프로그램을 실행하면 C:\Program Files (x86)\H2 에 설치된다.

 

3. 설치가 완료되면 윈도우 시작 버튼을 누르고 <H2 console> 앱을 실행한다.

앱이 실행되면 자동으로 웹 기반의 관리 콘솔 화면이 뜬다.

이때 윈도우 트레이의 H2 아이콘에서 마우스 오른쪽 버튼을 클릭하여 'Create a new database...' 를 선택한다.

 

 

4. 데이터베이스 생성 화면에 다음 내용을 참조해 원하는 이름으로 데이터베이스를 생성한다.

이때 Database path는 실제 데이터 파일이 생성되는 경로와 파일명이므로 신중히 작성해야 한다.

작성을 완료한 후 <Create> 버튼을 눌러 DB를 생성한다.

 

 

5. 정상적으로 데이터베이스가 생성되었으면 다시 콘솔 화면으로 돌아와 연결 시험을 수행한다.

JDBC URL을 다음과 같이 설정하고 사용자명, 비밀번호를 넣은 다음 <연결 시험> 버튼을 눌렀을 때 시험 성공이 나오면 된 것이다.

 

 

 

SQL의 개요

 

SQL 이란?

SQL(Structured Query Language)은 관계형 데이터베이스에서 데이터를 관리하기 위한 쿼리 언어로 대부분의 프로그래밍 언어보다는 단순한 구조를 가지고 있다.

SQL은 단순히 데이터 관련 작업 이외에 데이터베이스 자체의 관리 기능 수행에도 사용된다.

SQL에서 할 수 있는 일은 다음과 같다.

• 새로운 테이블 생성
• 내장 프로시저 생성
• 뷰 생성
• 테이블, 프로시저, 뷰 등의 접근 궈한 부여
• 데이터베이스에 대해 쿼리 실행
• 데이터베이스로부터 데이터 조회
• 데이터베이스에 기록 삽입, 갱신, 삭제
• 새로운 데이터베이스 생성

 

※ SQL의 유형

SQL 문은 크게 DDL(Data Definition Language), DML(Data Manipulation Language)로 나눌 수 있다.

DDL은 테이블의 생성, 수정, 삭제와 같은 관리 기능을 제공하는 SQL 문이며, DML은 테이블의 데이터를 조작할 때 사용하는 SQL 문을 의미한다.

 

 

H2 데이터베이스의 자료형으로 다음과 같다.

자료형	종류	크기
INT	정수형	32 bit
DOUBLE	실수형	64 bit
DATE	날짜형	날짜와 시간을 포함
CHAR	문자열	고정길이로 INT 범위
VARCHAR	문자열	가변길이로 INT 범위
CLOB	문자열	INT 범위를 넘어서는 대용량 문자열
BLOB	바이너리형	대용량 바이너리형

 

SQL의 기본 규칙은 다음과 같다.

• 모든 SQL 구문과 식별자는 대소문자를 구분하지 않는다.
• 명령과 키워드는 대문자, 식별자는 정해진 이름 규칙에 따를 것을 권장한다.
• 식별자는 영문으로 작성하며 공백을 허용하지 않는다.
• 키워드는 식별자로 사용할 수 없다.
• 서술적인 접두어 사용은 자제한다. (ex : tbl_member, idx_, pk_)

 

※ DDL(Data Definition Language)

DDL은 테이블의 생성, 수정, 삭제와 같은 관리 기능을 제공하는 SQL 문을 의미한다.

DDL을 통해 데이터베이스 스키마, 테이블, 인덱스 등 데이터 저장 및 운영을 위한 객체의 생성과 관리가 가능하다.

 

♣ CREATE

테이블을 생성할 때 사용하는 명령어이며 각 칼럼의 자료형과 최대 크기를 명시해야 한다.

필요에 따라 칼럼에 제약 조건과 속성을 추가할 수 있다.

마지막 칼럼 설정 뒤에 ','를 넣지 않도록 주의한다.

CREATE TABLE 테이블_이름 (
    칼럼_이름 자료형(크기) 제약 조건|속성,
    칼럼_이름 자료형(크기),
    ...
)

 

♣ ALTER

테이블 구조를 수정할 떄 사용하는 명령어이다.

테이블의 수정은 가능하지만 데이터가 들어가 있는 상태에서는 구조 변경에 여러 제약이 따른다.

변경된 구조로 인해 관련 프로그램의 실행에도 문제가 발생할 수 있기 때문이다.

따라서 테이블을 수정하기 전에 신중한 테이블을 생성하는 것이 좋다.

ALTER TABLE 테이블_이름 [ADD | ALTER | DROP] 칼럼명 자료형 제약 조건

 

테이블에 데이터가 들어가 있을 때 수정 제약 사항은 다음과 같다.

• 칼럼의 자료형은 변경할 수 없다.
• 칼럼의 크기(해당 칼럼에 저장할 수 있는 데이터의 크기)를 줄일 수는 없고 늘리는 것만 가능하다.
• NOT NULL 속성을 갖는 필드는 추가할 수 있으나, NULL 속성이 있는 필드는 추가할 수 없다.

 

♣ DROP

테이블 자체를 삭제하는 명령어다.

테이블을 삭제할 때는 데이터와 함께 테이블과 연관되어 정의된 인덱스, 룰, 트리거, 제약 조건도 함께 삭제되므로 주의해야 한다.

DROP TABLE 테이블_이름 [RESTRICT | CASCADE]

• RESTRICT: 기본값으로 삭제 테이블이 다른 곳에서 참조되고 있다면 삭제를 중지한다.
• CASCADE: 삭제 테이블과 의존관계가 있는 모든 개체를 함께 삭제한다.

 

DROP TABLE student
DROP TABLE student CASCADE // student 테이블과 의존관계가 있는 모든 개체를 함께 삭제

 

♣ SHOW

테이블 정보를 조회하기 위해서는 데이터베이스마다 제공되는 별도의 명령 혹은 스키마 구조에 접근하는 쿼리를 사용해야 한다.

H2에서는 다음과 같이 SHOW 명령어를 이용하여 테이블 정보를 확인할 수 있다.

SHOW COLUMNS FROM student

 

※ DML(Data Manipulation Language)

DML은 테이블의 데이터를 조작할 때 사용하는 SQL 문으로 데이터의 입력, 수정, 삭제, 검색 등에 사용된다.

주로 프로그램 코드 안에서 사용하는 쿼리문이다.

 

♣ INSERT

테이블에 데이터를 추가하기 위한 명령어이다.

일부 칼럼의 값만 추가할 수도 있고 테이블의 모든 칼럼에 해당하는 값을 추가할 수도 있다.

전체 칼럼값을 모두 추가할 때 VALUES에 오는 값의 순서는 테이블을 생성할 때 지정한 칼럼 순서와 반드시 일치해야 한다.

부분 칼럼 데이터만 저장하는 경우 NOT NULL 칼럼은 반드시 포함되어야 하며 auto_increment 속성이 적용될 칼럼은 비워두면 된다.

//전체 칼럼값을 모두 저장하는 경우
INSERT INTO 테이블_이름 VALUES(칼럼에 넣을 데이터)
INSER INTO student VALUES('김길동', 'AA대학교', '1999-10-21', 'kim@aa.com')

// 특정 칼럼값만 저장하는 경우
INSERT INTO 테이블_이름(칼럼_이름1, 칼럼_이름2 ...) VALUES(칼럼에 넣을 데이터)
INSERT INTO student(username, email) VALUES('김길동', 'kim@aa.com')

 

♣ SELECT

테이블에서 데이터 조회를 위한 명령어이다.

전체 데이터 혹은 조건에 맞는 데이터만 조회가 가능하다.

데이터베이스에서 제일 중요한 쿼리이며, 효율적인 조회를 위해서는 다음과 같은 작업이 필요하다.

• 여러 테이블의 데이터를 조합해서 조회하기
• 외래키 칼럼의 코드 데이터를 참조 테이블의 이름 칼럼으로 대체하기
• 날짜 형식 변경하기
• 데이터 정렬 또는 집계하기

 

SELECT [ALL | DISTINCT] 칼럼명(혹은 *)..
FROM 테이블_이름
[ WHERE 조건절 ]
[ GROUP BY 칼럼명 ]
[ HAVING 검색조건 ]
[ ORDER BY 칼럼명 [ ASC | DESC ]

• 특정 칼럼을 지정하거나 전체 칼럼(*)을 조회할 수 있다.
• DISTINCT: 중복된 값은 제거하고 가지고 온다.
• WHERE: 검색 조건을 지정할 때 사용된다.
• GROUP BY: 특정 칼럼을 그룹화할 때 사용한다.
• HAVING: 특정 칼럼을 그룹화한 결과에 조건을 설정할 때 사용한다.
• ORDER BY: 특정 칼럼을 기준으로 오름차순(ASC)/내림차순(DESC) 정렬할 때 사용한다.

 

※ 데이터 조회 함수

데이터베이스에는 데이터 조회의 편의를 위해 몇몇 함수가 기본적으로 제공되며 데이터베이스마다 전용 함수가 제공되기도 한다.

 

♣ 숫자 관련 함수

숫자를 조작하기 위한 함수로 ABS, CEILING, ROUND, FLOOR, SQRT 등이 있다.

SELECT ABS(-20), CEILING(20.25), ROUND(20.25), FLOOR(20.25), SQRT(4) from dual

• 결과 : 20, 21, 20, 20, 2.0

 

♣ 문자 관련 함수

문자, 문자열을 조작하기 위한 함수로 ASCII, LENGTH, CONCAT, TRIM, LOWER, UPPER, SUBSTRING 등이 있다.

SELECT ASCII('A'), LENGTH('HELLO'), CONCAT('Hello', 'World'), TRIM('   Hello World   '), 
   LOWER('ABC'), UPPER('abc'), SUBSTRING('HelloWorld' FROM 2 FOR 5) from dual

• 결과 : 65, 5, 'HelloWorld', 'Hello World', 'abc', 'ABC', 'elloW'

 

♣ 날짜/기간 함수

NOW(현재 날짜 시간), CURRENT_TIMESTAMP(현재 날짜 시간), DAYNAME(요일), PARSEDATETIME(문자열 포맷을 날짜 시간 정보로 변환) 등이 있다.

SELECT NOW(), CURRENT_TIMESTAMP(), DAYNAME(now()), PARSEDATETIME('10-01-2020', 'MM-dd-yy', 'GMT') from dual

• 결과 : 2022-01-02 12:07:47.297208, 2022-01-02 12:07:47.297208+09, Sunday, 2020-10-01 00:00:00

 

♣ 집계 함수

COUNT(레코드 수), SUM(칼럼값 더하기), AVG(칼럼값 평균), MAX(칼럼 최댓값), MIN(칼럼 최솟값) 등이 있다.

SELECT COUNT(id), SUM(id), AVG(id), MAX(id), MIN(id) FROM student

• 결과 : 5, 15, 3, 5, 1

 

※ 조인

관계형 데이터베이스에서 2개 이상의 테이블이나 데이터베이스를 조합해 데이터를 검색하는 것을 말한다.

조회하고자 하는 칼럼이 서로 다른 테이블에 있을 경우 주로 사용하며 여러 개의 테이블을 마치 하나의 테이블인 것처럼 사용할 수 있는 방법이다.

보통 기본키(PK)와 외래키(FK)로 연결된 두 테이블의 데이터를 조합하기 위해 사용할 수 있다.

조인은 여러 유형이 있으며 조인을 통해 여러 번 쿼리를 보내거나 결과를 프로그램에서 조합할 필요 없이 한 번의 쿼리로 원하는 데이터 구조를 받아볼 수 있다.

조인 형태로는 Inner Join, Outer Join, Cross Join, Self Join 등이 있다.