MySQL 데이터베이스 기초
대부분의 웹 어플리케이션이 정보와 데이터를 저장 및 관리하기 하기 위해서 데이터베이스를 사용하고 있습니다.
웹 프로그래밍은 데이터베이스 프로그래밍 이라고 할 정도로 모든 페이지가 데이터베이스와 통신이 이루어집니다.
자바에서는 JDBC API 를 이용하여 데이터베이스 프로그래밍을 하게 되는데 기본적인 내용부터 하나씩 알아보겠습니다.
먼저 필수적으로 알아야 할 용어와 데이터베이스 프로그래밍의 일반적인 순서를 먼저 알아보겠습니다.
- 데이터베이스
: 데이터를 필요할 때 사용할 수 있도록 저장하는 공간. 데이터를 지속적으로 관리하고 보호하는 것이 주목적입니다.
- DBMS(DataBase Management System)
: 데이터베이스를 관리하는 시스템이며 대표적인 DBMS로는 오라클, MySQL, MS-SQL 등이 있고 주요기능은 다음과 같습니다.
▶ 데이터의 추가/조회/변경/삭제
▶ 데이터의 무결성(Integrity) 유지
▶ 트랜잭션 관리
▶ 데이터의 백업 및 복원
▶ 데이터의 보안
* 데이터베이스의 종류에는 관계형 / 객체지향 / 계층형 등의 여러 형태가 존재하는데 가장 많이 사용되는 종류는 관계형 데이터베이스(RDMBS) 입니다. 오라클, MySQL, MS-SQL 등이 관계형 데이터베이스입니다.
- 테이블
: RDMBS에서 데이터가 저장되는 장소를 테이블이라고 합니다. 또한, 테이블의 구조와 관련된 정보를 스키마(Schema)라고 합니다.
테이블의 구조는 칼럼 이름 / 칼럼 타입 / 칼럼의 길이 로 구성됩니다. 아래 스키마 예제를 확인해 보겠습니다.
칼럼 이름 |
칼럼 타입 |
칼럼의 길이 |
MEMBERID |
VARCHAR |
10 |
PASSWORD |
VARCHAR |
10 |
NAME |
VARCHAR |
20 |
VARCHAR |
80 |
칼럼 이름은 저장할 데이터의 이름이고 칼럼 타입은 저장할 데이터의 타입입니다. 변수에 저장하는 값의 저장 범위가 있듯이 칼럼의 길이에도 제한이 있습니다.
- 레코드
: 테이블의 스키마에 정의된 칼럼에 해당하는 값의 모음을 레코드라고 합니다. 하나의 테이블은 여러 개의 레코드로 구성되는데 아래 표를 통해 확인해보겠습니다.
MEMBERID |
PASSWORD |
NAME |
|
freestrokes |
1234 |
가나다 |
freestrokes@freestrokes.com |
jsp |
jsp1234 |
JSP |
jsp@jsp.com |
표의 2행, 3행이 각각 하나의 레코드인 것을 확인할 수 있습니다.
- 주요키(Primary Key)
: 테이블에 저장된 레코드를 사용하기 위해 각각의 레코드를 구별하는 방법 중 하나입니다.
주요키는 하나의 테이블에 저장된 모든 레코드가 서로 다른 값을 갖는 칼럼을 의미하는데, 위의 예제에서는 MEMBERID 칼럼에 여기에 해당한다고 볼 수 있습니다.
- 인덱스(Index)
: 데이터의 순서를 미리 정렬하여 저장할 때 사용합니다. 레코드를 쉽게 찾을 수 있도록 미리 정리된 표를 만들어 두는데 이것이 인덱스입니다.
(주요키도 인덱스의 일종입니다)
* 인덱스는 중복된 값에 대한 정렬이 가능한 반면에, 주요키는 중복된 값을 가질 수 없다는 차이가 있습니다.
- 데이터베이스 프로그래밍의 일반적 순서
: 다음과 같이 네가지 단계를 거쳐서 동작합니다.
2~3번 과정은 작업을 완료할 때까지 반복하여 수행이 가능하고 JDBC 프로그래밍에서도 동일하게 적용됩니다.
- 데이터베이스 프로그래밍의 필수 요소
: 데이터베이스 프로그래밍을 하기 위해서는 다음과 같이 3가지 요소가 필요합니다.
▶ DBMS : 데이터베이스를 관리해주는 시스템 (오라클, MySQL, MS-SQL 등)
▶ 데이터베이스 : 데이터를 저장할 공간
▶ DBMS 클라이언트 : 데이터베이스를 사용하는 어플리케이션
데이터베이스 생성
예제를 실행하기 위해 데이터베이스를 생성해 보겠습니다. (DBMS는 MySQL을 사용했습니다)
(MySQL 설치 방법은 '[JSP 환경설정] - 1. eclipse와 MySQL 설치' 글을 참고해주세요)
데이터베이스 생성을 위해 명령 프롬프트(cmd)에서 다음과 같이 명령어를 입력해줍니다.
▶ [MySQL설치디렉터리]\bin>mysqladmin -u root create 데이터베이스명
이전에 MySQL을 설치하면서 root 계정에 암호를 설정했다면 다음과 같이 입력해줍니다.
▶ [MySQL설치디렉터리]\bin>mysqladmin -u root -p create 데이터베이스명
* 시스템 속성의 환경변수에 [MySQL설치경로]\bin을 PATH 환경변수에 추가해주면 디렉터리의 위치에 상관없이 실행이 가능합니다.
실행 화면은 다음과 같습니다.
cmd창에서 test라는 이름의 데이터베이스를 생성하고 DBMS에 root 계정으로 접속한 뒤 데이터베이스의 생성여부를 확인하였습니다.
* MySQL에 접속 후 쿼리를 입력시 끝에 세미콜론(;)을 써줘야 명령이 실행됩니다. (Enter키는 줄바꿈)
이제 사용자를 추가해 보겠습니다. 아래 순서대로 명령을 실행해주세요.
위의 예제의 grant 쿼리는 MySQL DBMS에 계정을 추가할 때 사용하는 명령어로서 기본 구조는 다음과 같습니다.
▶ grant [권한목록] on [데이터베이스명] to [계정]@[서버] identified by [암호]
첫번째로 쓰인 grant 쿼리는 localhost에 접속하는 dbtest 계정에 권한을 부여합니다.
dbtest 데이터베이스의 모든 것에 대해 select, insert, update, delete, create, drop 쿼리를 실행할 수 있는 권한을 주며 암호는 dbex를 사용하도록 했습니다.
두번째로 쓰인 grant 쿼리는 모든 서버에서 연결할 수 있는 권한을 주는 것을 제외하고 첫번째와 내용이 동일합니다.
정상적으로 접속이 되는 것을 확인할 수 있습니다.
SQL 기초
SQL(Structured Query Language) 은 데이터를 조회하고 삭제하는 등의 데이터베이스 작업을 수행할때 사용하는 질의 언어입니다.
- 주요 SQL 타입
: 자바에서 기본 데이터 타입에 여러 종류가 있듯이 SQL도 저장할 데이터 종류에 따라서 다양한 타입을 제공합니다.
CHAR |
확정 길이의 문자열 저장. 표준의 경우 255 글자까지만 저장 가능. |
VARCHAR |
가변 길이의 문자열 저장. 표준의 경우 255 글자까지만 저장 가능. |
LONG VARCHAR |
긴 가변 길이의 문자열 저장. |
NUMERIC |
숫자 저장. |
DECIMAL |
10진수 저장. |
INTEGER |
정수 저장. |
TIMESTAMP |
날짜 및 시간 저장 |
TIME |
시간 저장 |
DATE |
날짜 저장 |
CLOB |
대량의 문자열 데이터 저장 |
BLOB |
대량의 바이너리 데이터 저장 |
* 많은 DBMS가 표준 SQL 타입과 함께 확장 타입을 추가로 제공합니다.
이들 확장 타입은 DBMS 마다 서로 호환이 되지 않으며, 표준 SQL 타입과 다르게 사용됩니다.
따라서 데이터베이스 프로그래밍을 할 때에는 사용하는 DBMS에 맞는 SQL 타입을 사용해야 합니다.
- 테이블 생성 쿼리
: 데이터가 실제로 저장되는 공간은 테이블이기 때문에, 데이터베이스 프로그래밍을 하기 위해선 테이블을 생성해 주어야 합니다.
다음은 테이블 생성시 사용되는 SQL 구문입니다.
create table TABLENAME (
COL_NAME1 COL_TYPE1(LEN1),
COL_NAME2 COL_TYPE2(LEN2),
...,
COL_NAMEn COL_TYPEn(LENn)
)
▶ TABLENAME : 테이블을 식별할 때 사용하는 이름
▶ COL_NAME : 칼럼 이름
▶ COL_TYPE : 칼럼 타입
▶ LEN : 저장될 최대값의 길이
* 주요키 칼럼을 표시할 경우 칼럼 타입 뒤에 'PRIMARY KEY' 라는 문장을 추가하고, 필수 값에 대해서는 'NOT NULL' 을 추가해줍니다.
예제 실습을 통해 확인해보겠습니다.
MEMBERID / PASSWORD / NAME 칼럼을 필수 값으로 하기 위해 'NOT NULL' 을 추가하고, MEMBERID를 주요키로 설정한 것을 확인할 수 있습니다.
* DESC(Description) 는 테이블에 대한 설명을 알아내는 데 사용할 수 있습니다.
- 데이터 삽입 쿼리
: 데이터를 삽입할 때는 INSERT 쿼리를 사용한다. INSERT 쿼리는 테이블의 한 레코드를 삽입할 때 사용되며 기본 문법은 다음과 같습니다.
▶ insert into [테이블이름] ([칼럼1], [칼럼2], ..., [칼럼n])
values ([값1], [값2], ..., [값n]);
다음과 같이 칼럼을 빼고 줄여써도 됩니다. 이 경우엔 칼럼의 순서와 개수에 맞게 값을 입력해줘야 합니다.
▶ insert into MEMBER values ([값1], [값2], ..., [값n]);
예제를 통해 확인해보겠습니다.
* 칼럼의 값을 지정하지 않을 경우 널(null) 값이 들어가게 됩니다.
- 데이터 조회 쿼리 (조회 및 조건)
: 저장된 데이터를 조회할 때는 SELECT 쿼리를 사용하고 기본 문법은 다음과 같습니다.
▶ select * from [테이블이름];
▶ select [칼럼1], [칼럼2], ..., [칼럼n] from [테이블이름];
▶ select * from [테이블이름] where [칼럼1] = [값1] and [칼럼2] = [값2] and ... and [칼럼n] = [값n];
▶ select * from [테이블이름] where [칼럼1] = [값1] or [칼럼2] = [값2] or ... or [칼럼n] = [값n];
예제를 통해 확인해보겠습니다.
각 조건에 따라 조회 결과를 확인할 수 있습니다.
다음은 SELECT 쿼리의 추가적인 조건입니다.
'<>' 연산자
: '<>' 연산자는 같지 않음을 의미합니다. 해당 [칼럼]의 값에 [조건]이 없는 레코드를 검색해줍니다.
▶ select * from [테이블이름] where [칼럼] <> [조건];
NULL 포함여부 확인
: [칼럼]의 값이 NULL 이거나 NULL 이 아닌 경우의 레코드를 검색합니다.
▶ select * from [테이블이름] where [칼럼] is NULL;
select * from [테이블이름] where [칼럼] is not NULL;
부등호 기호
: 숫자 칼럼에서 '<', '>', '<=', '>=' 등의 연산자를 사용하여 레코드를 검색합니다.
▶ select * from [테이블이름] where [칼럼] >= [NUMBER] and/or [칼럼] <= [NUMBER];
LIKE
: 특정 [문장]을 포함하는지의 여부를 확인합니다.
▶ select * from [테이블이름] where [칼럼] like '%[문장]% / %[문장] / [문장]%'
( %는 모든 것을 의미합니다.
%[문장]% : 중간에 [문장]이 포함된 경우
%[문장] : [문장]으로 끝나는 경우
[문장]% : [문장]으로 시작하는 경우 )
* 빠른 검색 속도를 필요로 하는 곳에서는 LIKE 검색을 사용하지 않고 별도의 검색 엔진을 쓰는 것이 좋습니다.
- 데이터 조회 쿼리(정렬)
: ORDER BY 쿼리를 사용해서 데이터를 정렬하여 출력합니다.
▶ select [칼럼1], [칼럼2], ..., [칼럼n] from [데이터명] order by [칼럼1] asc, [칼럼2] desc, ..., [칼럼n] asc;
* asc는 오름차순, desc는 내림차순 정렬입니다.
예제를 통해 확인해보겠습니다.
위의 예제 중에서 마지막 쿼리를 보면 NAME 칼럼을 기준으로, 먼저 오름차순으로 정렬된 상태에서 PASSWORD 칼럼이 내림차순으로 정렬하게 됩니다. 하지만 NAME 칼럼이 먼저 정렬이 된 이후에 PASSWORD 칼럼은 정렬이 되지 않는데, 이를 통해 각 칼럼의 정렬이 다르다고 해도 레코드의 변화는 일어나지 않는다는 것을 확인할 수 있습니다.
- 데이터 조회 쿼리(집합)
: 집합에 관련된 쿼리에는 sum() / max() / min() / count() 등의 함수가 존재하고 총합, 최대, 최소, 개수 를 구하는데 사용됩니다.
▶ select max([VALUE]) / min([VALUE]) / sum([VALUE]) / count([VALUE]) from 테이블이름;
예제를 통해 확인해보겠습니다.
- 데이터 수정 쿼리
: 데이터 수정에는 UPDATE 쿼리를 사용합니다.
▶ update [테이블이름] set [칼럼1]=[값1], [칼럼2]=[값2], ... ,[칼럼n]=[값n] where [조건];
※ where를 통해 조건을 명시하지 않으면 모든 레코드의 값이 변경됩니다.
예제를 통해 확인해보겠습니다.
- 데이터 삭제 쿼리
: DELETE 쿼리를 사용하여 레코드 단위로 데이터를 삭제할 수 있습니다.
▶ delete from [테이블이름] where [조건]
※ where 를 써서 조건을 명시하지 않으면 모든 레코드를 삭제합니다.
예제를 통해 확인해보겠습니다.
- 조인
: 두 개 이상의 테이블로부터 관련 있는 데이터를 읽어올 때 사용합니다.
조인을 사용하면 관련 테이블로부터 필요한 칼럼을 모아서 한꺼번에 읽어올 수 있으며, 여러 테이블에 분산해서 저장된 정보를 읽어올 때 유용하게 사용할 수 있습니다.
▶ select A.[칼럼1], A.[칼럼2], ... , A.[칼럼n], B.[칼럼1], B.[칼럼2], ... ,B.[칼럼n]
from [테이블1] as A join [테이블2] as B
where A.[칼럼x] = B.[칼럼y];
* 조인에 대한 내용을 잘 알고 있으면 웹 어플리케이션을 구현할 때 유리한 점이 많습니다.
조인은 다양한 형태로 존재하는데, 어떻게 사용하느냐에 따라서 쿼리의 성능에 많은 영향을 미치게 됩니다.
조인에 대해 좀 더 알아보겠습니다.
다음은 조인에 대해 집합으로 정리한 다이어그램입니다.
INNER JOIN은 앞서 알아본 JOIN과 같다고 보면 되고, FULL OUTER JOIN은 합집합의 개념인데 지원하지 않는 경우도 있고 많이 사용되지는 않습니다. 나머지 LEFT OUTER JOIN 과 RIGHT OUTER JOIN 에 대해서 이어서 알아보겠습니다.
- LEFT / RIGHT OUTER JOIN
: 각각 왼쪽 테이블과 오른쪽 테이블을 기준으로 JOIN을 적용합니다.
▶ select A.[칼럼1], A.[칼럼2], ... , A.[칼럼n], B.[칼럼1], B.[칼럼2], ... ,B.[칼럼n]
from [테이블1] as A LEFT / RIGHT OUTER JOIN [테이블2] as B
on A.[칼럼x] = B.[칼럼y];
위의 예제를 보면 LEFT OUTER JOIN은 왼쪽 테이블이 기준이 되기 때문에 왼쪽 테이블의 모든 레코드가 출력되고, RIGHT OUTER JOIN의 경우 오른쪽 테이블 기준으로 오른쪽 테이블의 모든 레코드가 출력됩니다. 일치하는 값이 없을 경우 NULL을 출력해줍니다.
이상으로 MySQL 데이터베이스 기초 내용을 알아보았습니다.
