[MySQL] 데이터베이스 프로그래밍



21. 데이터베이스 프로그래밍



21.1 데이터베이스 프로그래밍의 일반적인 순서


img


21.2 데이터베이스 & DBMS


21.2.1 데이터베이스(database)

빠른 탐색과 검색을 위해 조직된 데이터의 집합체

'파일에 충분히 저장을 하고 저장된 내용을 불러올 수 있는데 왜 db를 배워야 하나? 파일은 구조적으로 정리된 건 아니다. 실제로 우리가 사용하는 데이터들은 훨씬 더 복잡하거나 정형화되지 않았다. 이런 파일들을 분석해서 데이터 처리를 하려면 한계가 있다! 그리고 파일은 개인들이 다 가지고 있어서 공유가 잘 되지 않는 문제가 있다. 그래서 유지 보수나 데이터 공유 측면에서 파일의 한계가 있다!


2) DBMS(DataBase Management Systme)


가) 데이터베이스를 관리하기 위한 시스템

나) 주요 기능

데이터의 추가/조회/변경/삭제

데이터의 무결성(integrity) 유지

트랜잭션 관리

데이터의 백업 및 복원

cf. 무결성? 결함이 없는 것

cf. 트랜젝션? 거래 처리 단위

다) 주요 DBMS

오라클(Oracle),

My-SQL, MariaDB(My-SQL 개발자들이 나와서 따로 만든 것, 차이점은 무료)

마이크로소프트(MS-SQL),

사이베이스(Sybase)

인포믹스(Infomix) 등



21.3 테이블 & 레코드


1) 테이블 - 데이터가 저장되는 가상의 장소


2) 테이블은 1개 이상의 컬럼으로 구성

각 컬럼은 자료형을 가지며, 제약조건(값의 길이, 범위, 조건 등)을 갖는다.

이런 테이블의 구성을 스키마(schema)라고 함


3) 컬럼의 모음을 레코드(record)라고 표현

(21.21;12)

하나의 테이블은 여러 개의 레코드로 구성


4) 기본키와 인덱스

가) 기본키(Primary Key)

각각의 레코드를 구별하기 위한 키값(중복될 수 없음)

'ex. 어떤 쇼핑몰에 10만명의 고객 정보가 들어있는데, 식별을 어떻게 해야 할까?


나) 인덱스

빠른 검색을 위한 색인(ex. 서적의 목차, 찾아보기 기능)

기본키는 인덱스가 자동으로 생성됨



21.4 SQL(Structured Query Language)


1) SQL이란

데이터 조회, 삭제 등의 데이터베이스 작업을 수행할 때 사용되는 언어


2) SQL 명령어의 종류

가) 데이터 정의어(Data Definition Language, DDL)

데이터베이스 관리자나 응용 프로그래머가 데이터베이스의 논리적 구조를 정의하기 위한 언어로서 데이터 사전(Data Dictionary)에 저장

나) 데이터 조작어(Data Manipulation Language, DML)

데이터베이스에 저장된 데이터를 조작하기 위해 사용하는 언어로서 데이터 검색(Retrieval), 추가(Insert), 삭제(Delete), 갱신(Update) 작업 수행

다) 데이터 제어어(Data Control Language, DCL)

데이터에 대한 접근 권한 부여 등의 데이터베이스 시스템의 트랜젝션을 관리하기 위한 목적으로 사용되는 언어


3) SQL 주요 타입

 SQL 타입

 설명 

 CHAR

 고정 길이의 문자열을 저장

 VARCHAR2

 가변 길이의 문자열을 저장

 NUMBER

 숫자를 저장

 TIMESTAMP (시간도장)

 날짜 및 시간을 저장

 TIME

 시간을 저장

 DATETIME

 날짜를 저장

 CLOB

 대량의 문자열 데이터를 저장

 BLOB

 대량의 바이너리 데이터를 저장


cf. char(10) 고정길이 문자열; ex. kim

 k

 i

 m

 

 

 

 

 

 

 


cf. SQL도 표준 SQL이 있고 비표준 SQL이 있다. 처음에 DB 시장을 오라클이 선두하여 점유를 많이 했는데 후발 주자가 춘추전국시대처럼 많이 등장하다 보니 명령어 체계가 달라졌다. 그래서 이거 표준화를 만들자 해서 나온 것이 표준 SQL - ANSI SQL!




21.8 MySQL 설치


1) mysql 공식 사이트

http://mysql.com


2) mysql community edition 8.0.11 zip 버전 다운로드

가) mysql community edition - 개인 개발용 무료 버전

나) GPL(General Public License, 일반 공중 사용 허가서)

1. 컴퓨터 프로그램을 어떠한 목적으로든지 사용할 수 있다. 다만 법으로 제한하는 행위는 할 수 없다.

2. 컴퓨터 프로그램의 실행 복사본은 언제나 프로그램의 소스 코드와 함께 판매하거나 소스코드를 무료로 배포해야 한다.

3. 컴퓨터 프로그램의 소스 코드를 용도에 따라 변경할 수 있다.

4. 변경된 컴퓨터 프로그램 역시 프로그램의 소스 코드를 반드시 공개 배포해야 한다.

5. 변경된 컴퓨터 프로그램 역시 반드시 똑같은 라이선스를 취해야 한다. 즉, GPL 라이선스를 적용해야 한다.


3) c:\mysql 디렉토리에 압축 해제


4) 환경 변수 설정

MYSQL_HOME : c:\mysql

path에 c:\mysql\bin 추가


5) mysql 설치(cmd에서 실행, 관리자 권한으로 실행해야 함)

mysqld --install


"msvcp120.dll이 없어 프로그램을 시작할 수 없습니다." 라는 에러가 발생할 경우 Visual Studio 2013용 Visual C++ 재배포 가능 패키지 프로그램을 설치해야 함


다운로드 주소 : https://www.microsoft.com/ko-KR/download/details.aspx?id=40784


32bit 컴퓨터의 경우 vcredist_x86.exe

64bit 컴퓨터의 경우 vcredist_x64.exe


6) mysql 초기화 작업(cmd에서 실행, 관리자 권한으로 실행해야 함, 시간이 많이 걸림)

mysqld --initialize


7) c:\mysql\data\사용자계정.err 파일을 열어서 임시 비밀번호를 확인

A temporary password is generated for root@localhost: !;+bs;TBt2Pt


8) mysql 서비스 등록 (cmd에서 실행)

net start mysql


가) 서비스 프로그램 : 운영체제가 부팅된 이후에 로그온 되지 않은 상태에서도 자동으로 구동되며 화면 없이 백그라운드에서 실행되는 프로그램

나) 서비스 등록 : net start 서비스이름

다) 서비스 해제 : net stop 서비스이름


9) mysql에 접속(cmd에서 실행)

가) mysql -u아이디 -p 비밀번호

mysql -uroot -p

임시비밀번호

나) mysql -u아이디 -p


10) 비밀번호 변경(mysql 터미널에서 실행)

ALTER USER '아이디'@'호스트' IDENTIFIED BY '비밀번호';


mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';



11) HeidiSQL 설치

http://heidisql.com


12) Heidi SQL에서 사용자 계정 생성

도구 - 사용자 관리자 메뉴에서 사용자 추가

아이디 : java

비번 : java1234

호스트 : localhost

전체 권한에 체크


...


16) mysql ssl 접속 방법

가) SSL(Secure Socket Layer) : 암호화 통신 방식

SSL을 적용하지 않으면 서버에 패킷들이 평문으로 전송되어 보안성이 없음, 특히 로그인과 같은 경우 아이디, 암호와 같은 민감한 정보들이 그대로 노출되는 문제점이 있음.

TCP 패킷 캡처 프로그램으로 보면 sql 문장이 그대로 노출됨

서버와 클라이언트간의 패킷을 좀더 안전하게 보호할 수 있음


나) SmartSniff (패킷 캡처 프로그램) 다운로드

32bit 프로그램 http://www.nirsoft.net/utils/smsniff.zip

64bit 프로그램 http://www.nirsoft.net/utils/smsniff-x64.zip


다) 윈도우즈용 OpenSSL 설치

mysql 서버가 설치된 컴퓨터에 ssl 접속을 허용하도록 설정해야 함

인증서 설치를 위해 윈도우즈용 openssl 설치

http://slproweb.com/products/Win32OpenSSL.html


32bit : http://slproweb.com/download/Win32OpenSSL_Light-1_1_0g.exe

64bit : http://slproweb.com/download/Win64OpenSSL_Light-1_1_0g.exe


OpenSSL의 설정 파일 샘플 다운로드


http://www.tbs-certificats.com/openssl-dem-server-cert-thvs.cnf


다운로드 받은 파일의 내용을 메모장에 복사한 후 파일이름을 c:\OpenSSL-Win32 또는 c:\OpenSSL-Win64 디렉토리에 openssl.cnf로 저장


라) 개인키와 공개키 만들기(cmd를 관리자권한으로 실행)


32bit의 경우

set OPENSSL_CONF=c:\OpenSSL-Win32\openssl.cnf

cd c:\openssl-win32\bin

#개인키(복호화에 필요한 키) 만들기

openssl genrsa -out key.pem 2048

#공개키(암호화에 필요한 키) 만들기

openssl req -new -x509 -key key.pem -out cert.pem -days 365


64bit의 경우

set OPENSSL_CONF=c:\OpenSSL-Win64\openssl.cnf

cd c:\openssl-win64\bin

#개인키 만들기

openssl genrsa -out key.pem 2048

#공개키 만들기

openssl req -new -x509 -key key.pem -out cert.pem -days 365


공개키를 만들 때 국가코드는 kr을 입력하고 나머지는 기본값으로 입력


* 암호화의 예


image


BUS라는 문자열을 암호화한다면

B=110, U=115, S=108 따라서 BUS="110115108"로 암호화 할 수 있다.

반대로 "110115108"=BUF로 복호화할 수 있다.


마) my.ini 파일에 ssl 인증서 관련 내용을 추가한 후 mysql 서비스 재시작

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqld]

collation-server=utf8_unicode_ci

init-connect='SET NAMES utf8'

character-set-server=utf8

ssl-ca=C:\\openssl-win32\\bin\\cert.pem

ssl-cert=C:\\openssl-win32\\bin\\cert.pem

ssl-key=C:\\openssl-win32\\bin\\key.pem


바) HeidiSQL에서 mysql에 SSL로 접속하는 방법

세션 관리자의 "고급" 탭에서 "SSL 사용" 체크(개인키, 인증서 등의 항목은 설정하지 않아도 됨)


image

접속 후

show variables like '%ssl%';

명령어로 SSL 설정이 활성화되어 있는지 확인


아) JDBC에서 mysql에 SSL로 접속하는 방법

db.prop 파일에서 연결문자열 뒤에 옵션을 추가함

#d:\work\db.prop

url=jdbc:mysql://localhost/java?useSSL=true&verifyServerCertificate=false

driver=com.mysql.jdbc.Driver

id=java

password=java1234


useSSL=true : SSL을 사용함

verifyServerCertificate=false : 서버 인증서를 확인하지 않음


자) SmartSniff를 이용하여 패킷이 암호화되어 전송되는지 확인


21.9 실습예제


01) mysql 실습

02) ConnectDatabase.java

03) db.prop

04) SQLInsertTest.java

05) Property.java

06) member.sql

07) LoginTest.java

08) car.sql

09) CarDTO.java

10) CarDAO.java

11) CarManage.java

12) emp.sql

13) DB.java

14) EmpDTO.java

15) EmpDAO.java

16) EmpManage.java

17) score.sql

18) ScoreDTO.java

23)( 오라클 테이블스페이스 작업

24) member_oracle.sql

39) emp_dept_oracle.sql

46) TranactionExam.java

47) Batch.java

48) student.sql

49) ProfessorDTO.java

50) ProfessorDAO.java

51) DepartmentDTO.java

52) DepartmentDAO.java

53) StudentDTO.java

54) StudentDAO.java

55) StudentTab.java

댓글(0)

Designed by JB FACTORY