![[MySQL] 데이터베이스 프로그래밍](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Ft1.daumcdn.net%2Fcfile%2Ftistory%2F99D95F495B31E9572B)
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. 서적의 목차, 찾아보기 기능)
기본키는 인덱스가 자동으로 생성됨
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 공식 사이트
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 설치
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