**RDBMS - MySQL
=> 관계형 데이터 베이스들은 SQL을 이용하여 질의를 수행
=> SQL은 관계형 데이터베이스 종류가 달라도 거의 비슷
=> MySQL과 Oracle은 자료형이 조금 다름
=> Oracle은 Sequence를 이용하여 일련번호를 생성, MySQL은 Auto_Increment를 이용
=> Oracle은 Inline View를 이용하여 Paging을 구현, MySQL은 limit를 이용
1. MySQL 작업 순서
1) 접속
=> URL: localhost(127.0.0.1 - 자기컴퓨터), ip, domain(선생님 - 192.168.0.200)
=> PORT : MySQL은 3306이 기본(Oracle은 1521)
=> MySQL은 접속할 database가 필요(mysql - 제공)
=> username : root / passward : 900826
2) 데이터베이스 선택 - 없으면 생성
=> 생성
- create database 데이터베이스명;
=> 사용
- use 데이터베이스명;
3) 데이터베이스 작업
2. DML(데이터 조작어 - insert, update, delete)
1) 테이블의 구조 확인
=> desc 테이블 명;
2) 데이터 삽입
insert into 테이블 명(컬럼명 나열)
values(값 나열);
=> 컬럼명 생략시 default값이 대입, default 비설정시 null 삽입
=> Auto_Increment 설정 값은 생략시 일련번호가 자동으로 삽입
=> 테이블 생성시 사용할 컬럼 순서대로 빠짐없이 대입시 테이블 명 뒤의 ( ) 생략 가능
ex)
insert into usertbl(userid, name, birthyear, addr, mobile, mdate)
values('jessica72', '제시카', 1973, '크라이스처치', '01031391997', '1973-04-18');3) 데이터 수정
update 테이블명
set 컬럼명 = 수정할 데이터[,...]
[where 수정할 데이터의 조건];
=> where는 생략 가능, 생략시 모든 데이터가 수정
ex) usertbl 테이블에서 userid가 jessica72인 데이터의 name을 jessica로 mdate를 1973년 5월 1일로 수정
update usertbl
set name = jessica, mdate = '1973-05-01';
where userid = jessica724) 데이터 삭제
delete from 테이블 명
[where 삭제할 조건];
=> where 생략시 테이블의 모든 데이터가 삭제
=> 데이터 삭제가 안되는 경우 다른 테이블에서 외래키로 참조 중이고, 외래키 옵션이 없는 경우
(외래키 설정 중이라도 on delete cascade나 on delete set null 설정시 삭제가 가능)
=> trincate table 테이블 명; 을 이용하면 테이블의 모든 데이터 삭제 가능
- delete는 DML이라 rollback으로 철회 가능
- truncate는 DDL 이므로 철회가 불가
ex) usertbl 테이블에서 userid가 jessica72인 데이터를 삭제
delete from usertbl
where userid = 'jessica72';
=> 삭제시 from을 생략해도 되는 데이터베이스가 있기는 한데 표준은 아님
3. Join
=> 2개의 테이블을 결합
=> 조회해야할 데이터가 2개 테이블에 나누어져 있는 경우 테이블을 조인하여 결과를 가져와야 함
=> Join은 2개 테이블의 데이터를 조합해야 하기 때문에 시간이 많이 걸림
(불필요한 경우 Join을 하지 않아야 함)
- Join으로 인한 트래픽이 관계형 데이터베이스의 최대 단점
=> 조회해야할 컬럼들이 2개 테이블에 존재시 join, 1개 테이블에 존재시 sub query로 해결 가능
1) Cross Join
=> Cartesian Product 라고도 함
=> 양쪽 테이블에 존재하는 모든 행들의 조합
=> Join 조건 없이 From절에 테이블명을 2개 기재하거나 명시적으로 Cross join이라고 기재
=> 결과 : 컬럼의 갯수 - 양쪽 테이블의 컬럼의 갯수의 합, 행의 갯수 - 양쪽 테이블 행 갯수의 곱
ex) usertbl 테이블과 buytbl 테이블의 cross join
-- usertbl : 6열 11행
-- buytbl : 6열 13행
select *
from usertbl, buytbl;
select *
from usertbl cross join buytbl;
2) Inner Join
=> 양쪽 테이블에 동일한 의미를 갖는 컬럼이 존재하는 경우 동일한 의미를 갖는 컬럼을 비교하여 조인
=> equi join : =로 비교하여 조인
=> non equi join : =이외의 연산자로 비교하여 조인(>, >=, <, <=, !=)
=> 형식
select
from 테이블명1, 테이블명2
where 테이블명1.컬럼명 = 테이블명2.컬럼명
=> 컬럼명이 다르면 테이블 생략, 동일시 테이블명 생략 불가
- 테이블명이 긴 경우 테이블 이름뒤에 별명을 기재하여 별명 사용가능
- 별명을 만들면 이후부터는 원래 테이블 명은 사용 불가
=> usertbl 테이블과, buytbl테이블에는 userid라는 동일한 의미를 갖는 컬럼이 존재
ex)
select
from 테이블1 inner join 테이블2 on 테이블1.컬럼명 = 테이블2.컬럼명;
(컬럼명이 같은 경우 inner join대신 natural join이라고 입력후 on을 생략
select
from 테이블1, natural join 테이블2;
=> usertbl 테이블과 buytbl테이블에는 userid라는 동일한 의미를 갖는 컬럼이 존재(equi join 수행)
select *
from usertbl, buytbl
where usertbl.userid = buytbl.userid;3) outer join
=> 어느 한쪽 테이블에만 존재하는 데이터도 조인에 참여하는 것
=> 형식
select
from 테이블명1, 테이블명2
on 테이블명1. 컬럼명(+) = 테이블명2.컬럼명(+);
=> where절의 컬럼명 뒤에 (+)를 추가하여 수행하는데 한쪽에만 추가가 가능
=> 반대편 컬럼에만 존재하는 데이터를 join에 참여시키는데 자신의 컬럼을 제외한 부분은 전부 null
select
from 테이블1 [left | right | full] outer join 테이블2 on 테이블1.컬럼명 = 테이블2.컬럼명;
ex) usertbl 테이블과 buytbl테이블의 데이터를 full outer join을 수행
select *
from usertbl left outer join buytbl
on usertbl.userid = buytbl.userid;
4) self join
=> 동일한 테이블을 가지고 join
=> 하나의 테이블에 동일한 의미를 갖는 컬럼이 2개이상 존재하는 경우 가능
=> SNS에서 유저와 유저 친구를 갖는 테이블, 인사관리 테이블에서 직원의 아이디와 상사의 아이디를 갖는 경우 등
=> 동일한 테이블명 2개를 사용하므로 테이블명에 반드시 별명을 사용해야 함
4. 집합연산
=> 동일한 구조를 갖는 테이블끼리의 연산
1) 합집합
=> union : 동일한 데이터는 제외하고 합집합
=> union all : 동일한 데이터를 2번 표시
=> 형식
select 구문
union
select 구문
[order by 정렬 옵션];
2) 교집합
=> 양쪽에 모두 존재하는 데이터만 추출
=> 형식
select 구문
INTERSECT
select 구문
[order by 정렬 옵션];
3) 차집합
=> 한쪽에만 존재하는 데이터만 추출
=> 형식
select 구문
EXCEPT (오라클은 MINUS)
select 구문
[order by 정렬 옵션];
5. Transaction
=> 한번에 수행되어야 하는 데이터베이스 작업의 논리적 단위
=> 애플리케이션이 수행될때 한번에 처리해야 하는 작업의 단위를 설정해서 사용해야 함
1) Tansaction의 ACID 성질
=> Atomicity(원자성) : All or Nothing - 전체가 수행되거나 하나도 수행되지 않아야 함
=> Consistency(일관성) : 트랜잭션 수행전과 수행후가 일관성이 있어야 함
=> Isolation(격리성) : 트랜잭션은 다른 트랜잭션과 격리되어야 함(타 트랜잭션의 영향을 받으면 안됨)
=> Durability(영속성) : 한번 완료된 트랜잭션은 계속되어야 함
2) 트랜잭션 사용 방법
=> Auto Commit : 하나의 SQL이 성공적으로 수행되면 자동으로 commit하는 방식
- java, DBeaver가 Auto commit
=> manual commit : 직접 commit을 호출하거나 정상적으로 종료되면 commit하는 방식
- 실제 업무에서는 거의 대부분 manual commit
3) TCL - Transaction Control Language
=> commit : 현재까지 수행한 작업을 데이터베이스 원본에 반영
=> savepoint : rollback 할 지점을 생성
=> rollback [to savepoint명] : rollback시 트랜잭션 시작지점으로, savepoint명 기재시 savepoint생성지점으로 이동
4) MySQL에서의 TCL
=> MySQL은 5.1버전까지 TCL 미지원
=> InnoDB에서는 SavePoint를 지원하지만 MyISAM에서는 지원하지 않음
5) auto commit 되는 상황
=> DDL이나 DCL을 성공적으로 수행시 자동적으로 commit
=> 접속프로그램의 정상 종료
6) 자동으로 rollback되는 상황
=> 접속 프로그램의 비정상 종료
6. Index
=> 데이터를 빠르게 조회하기 위해서 설정하는 자료구조
=> Primary key와 Unique 제약조건은 자동으로 Index 생성
1) 인덱스를 생성
=> create index 인덱스명 on 테이블명(컬럼명 나열);
2) 인덱스 삭제
=> drop index 인덱스명;
3) 조회구문이 인덱스를 사용하는지 확인
=> explain select 구문;
-> 검색에 자주 이용하는 컬럼중 Primary key나 Unique가 아닌 속성에는 Index를 설정하는 것이 좋음
-> 많은 데이터(4%이상)가 조회되거나 삽입, 삭제가 빈번한 컬럼에는 사용하지 않는 것을 권장
ex) 아래 조회 구문이 인덱스를 사용하지는 지 확인
explain select *
from usertbl
where userid = 'kty';
-- 인덱스가 설정되지 않은 경우, 데이터를 찾을 때 full table scan을 수행
explain select *
from usertbl
where addr = '서울';
7. View
=> 논리적 가상 테이블
=> 자주 사용하는 Select 구문을 하나의 이름으로 만들어 두고 사용하는 객체
=> 실제로 테이블을 만들지는 않기 때문에 논리적이라고 하고, 사용은 테이블처럼 하므로 가상테이블이라고 함
1) 생성
create [or replace] view 뷰이름
as
select 구문
[with check option]
[with read only]
2) 삭제
drop view 뷰이름;
3) 장점
=> 보안 : 유저(애플리케니션 개발자)에게 필요한 부분만 노출 할 수 있음
=> 속도 : View는 1회 실행시 주기억장치에 적재가 되어 다음부터는 주기억 장치에서 가져와 실행
4) with read only 옵션이 없으면 데이터 삽입, 삭제, 갱신이 가능하고, 원본테이블에 작업이 이루어짐
ex) buytbl 테이블에서 samount 가 5이상인 데이터를 조회할 수 있는 amount5이라는 뷰를 생성
create or replace view amount5
as
select *
from buytbl
where amount >= 5;
-- 테이블처럼 사용가능(조회)
select *
from amount5;
-- view에 데이터를 삽입하면 원본 테이블에 삽입됨
insert into amount5(num, userid, productname, groupname, price, amount)
values(15, 'kty', '게임기', '전자', 200, 1);
select *
from buytbl;8. Procedure
=> 자주사용하는 SQL구문을 하나의 이름으로 묶어두고 사용하는 객체
=> 목적은 View와 같음(보안, 속도)
9. Trigger
=> DML(Insert, Update, Delete)문장을 수행하기 전, 후에 타작업을 같이 수행하고자 할때 사용하는 객체
=> 수행하기 전에 할 일은 대부분 유효성을 검사하여 작업을 하지 않도록 하는 것
=> 수행 후에 할일은 같이 수행되어야 하는 작업이나, 작업의 로그를 기록하는 것
- 데이터베이스에서는 로그 테이블에, 프로그래밍에서는 텍스트 파일에 csv형식으로 기록
**Java와 MySQL 연동
1. 준비
=> 프로젝트에 MySQL 데이터베이스 연동 드라이버 파일을 사용할수 있도록 해주어야 함
Java Application(PC나 Embedded Device에서 실행)에서는 build path에 추가해 주어야 함
=> 접속할 데이터베이스 서버의 URL, PORT, 데이터베이스명, userid, password를 알아야 함
192.168.0.9
1521
sample
root
900826
2. 데이터베이스 작업
1) 테이블 생성
=> 테이블 명 : kovid19
=> 테이블의 컬럼
- 순번 : 정수 - pk
- 지역 : 문자열(한-10), 필수
- 국가 : 문자열(한-10), 필수
- 인구 : 정수, 필수
- 확진자 수 : 정수, 필수
- 사망자 수 : 정수, 필수
=> 테이블 명 : player
=> 테이블 컬럼
- ID: 문자열 10자(한+영 - 변하지 않음)primary key
- 이름 : 문자열(변하지 않음), not null
- 레벨 : 정수, not null
- 데미지 : 정수, not null
- 치명확률 : 실수, not null
- 경험치 : 정수, not null
- 생성일자 : 날짜
create table player(
playerID varchar(30) primary key,
playerName varchar(30) not null unique,
playerLevel int not null,
playerDemage int not null,
playerCripercent double not null,
playerEXP int not null,
playerDate date not null)engine = innodb default charset=utf8;
2) 샘플 데이터 입력
insert into player(playerID, playerName, playerLevel, playerDemage, playerCripercent, playerEXP, playerDate)
values('id01', 'player1', 1, 10, 10, 100, '2019-01-05');
insert into player(playerID, playerName, playerLevel, playerDemage, playerCripercent, playerEXP, playerDate)
values('id02', 'player2', 10, 1000, 30, 150000, '2020-04-09');
insert into player(playerID, playerName, playerLevel, playerDemage, playerCripercent, playerEXP, playerDate)
values('id02', 'player2', 4, 190, 10, 10000, '2020-05-05');
3) 데이터 확인
select * from player;
3. 프로젝트 생성
=> MySQL 드라이버를 프로젝트에서 사용할 수 있도록 설정
1) 드라이버 파일을 프로젝트에 복사
2) 복사한 드라이버 파일을 선택하고 마우스 오른쪽을 클릭한 후 [Build Path]-[add to build path]를 선택
4. 도메인 클래스 만들기
=> Domain Class : DTO(Data Tranfer Object), VO(Variable Object)라고 부름
- 애플리케이션에서 하나로 묶어서 사용해야 할 데이터들을 묶는데 사용하는 클래스
- 관계형 데이터베이스의 경우 테이블 당 1개 이상이 생성, 필요에 따라 테이블의 컬럼외 데이터를 추가하기도 함
import java.sql.Date;
public class Player {
private String playerID;
private String playerName;
private int playerLevel;
private int playerDemage;
private double playerCripercent;
private int playerEXP;
private Date playerDate;
public String getPlayerID() {
return playerID;
}
public void setPlayerID(String playerID) {
this.playerID = playerID;
}
public String getPlayerName() {
return playerName;
}
public void setPlayerName(String playerName) {
this.playerName = playerName;
}
public int getPlayerLevel() {
return playerLevel;
}
public void setPlayerLevel(int playerLevel) {
this.playerLevel = playerLevel;
}
public int getPlayerDemage() {
return playerDemage;
}
public void setPlayerDemage(int playerDemage) {
this.playerDemage = playerDemage;
}
public double getPlayerCripercent() {
return playerCripercent;
}
public void setPlayerCripercent(double playerCripercent) {
this.playerCripercent = playerCripercent;
}
public int getPlayerEXP() {
return playerEXP;
}
public void setPlayerEXP(int playerEXP) {
this.playerEXP = playerEXP;
}
public Date getPlayerDate() {
return playerDate;
}
public void setPlayerDate(Date playerDate) {
this.playerDate = playerDate;
}
@Override
public String toString() {
return "Player [playerID=" + playerID + ", playerName=" + playerName + ", playerLevel=" + playerLevel
+ ", playerDemage=" + playerDemage + ", playerCripercent=" + playerCripercent + ", playerEXP="
+ playerEXP + ", playerDate=" + playerDate + "]";
}
}5. DAO 클래스의 골격 만들기
=> DAO(Data Access Object) : 데이터베이스 작업을 처리하기 위한 클래스
- 데이터베이스와 관련된 작업만 처리
- 데이터 가공, 생성등의 작업은 하지 않음
=> 서버에서 구동되는 경우가 많아 Singleton Pattern을 적용하는 것을 권장
=> 드라이버 클래스 로드, 연결, 해제하는 코드는 모든 곳에서 사용하기 때문에 별도의 메소드, 블럭에 작성 권장
public class PlayerDAO {
//클래스가 로드될때 1번만 수행되는 코드(제일먼저 실행되는 코드)
static {
//MySQL 드라이버 클래스 로드
try {
Class.forName("com.mysql.jdbc.Driver");
}catch (Exception e) {
System.err.println("로드 실패");
System.err.println(e.getMessage());
e.printStackTrace();
}
}
//Singleton Pattern : 인스턴스를 1개만 생성할 수 있도록 하는 패턴
//Back-End Programmer를 주력으로 하고자 할 때는 객체지향 디자인 패턴을 학습
//생성자가 private이므로 외부에서 인스턴스 생성 불가
private PlayerDAO() {}
//변수를 1개만 생성 할 수 있도록 선언
private static PlayerDAO playerDAO;
//외부에서 생성된 인스턴스를 사용할 수 있도록 리턴해주는 메소드
public static PlayerDAO sharedInstance() {
//static 변수이므로 null을 대입하지 않는 이상
//맨 처음에만 null이고, 이후에는 null이 될수 없음
if(playerDAO == null) {
playerDAO = new PlayerDAO();
}
return playerDAO;
}
//Java Server 개발에는 Spring을 많이 사용하는 데 Spring이 자동으로 클래스를
//Singleton 패턴으로 디자인해줌
//여러 메소드에서 공통으로 사용할 변수
//java.sql 패키지의 클래스를 import
private Connection con;
private PreparedStatement pstmt;
//데이터베이스 접속을 위한 메소드
private void connect() {
try {
//데이터베이스 연결
// con = DriverManager.getConnection(
// "jdbc:mysql://192.168.0.200:3306/sample",
// "root", "*******");
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mysql",
"root", "900826");
//System.out.println("데이터베이스 접속 성공");
}catch (Exception e) {
System.err.println("데이터베이스 접속 실패");
System.err.println(e.getMessage());
e.printStackTrace();
}
}
//데이터 베이스 연결 객체를 정리해주는 메소드
private void close() {
try {
if(pstmt != null)
pstmt.close();
if(con != null)
con.close();
}catch (Exception e) {
System.err.println("데이터베이스 접속 실패");
System.err.println(e.getMessage());
e.printStackTrace();
}
}
}6. PlayerMain
7. 전체보기를 구현
=> 전체 데이터의 ID, name, level, demage, cripercent, exp, date 출력
1) DAO 클래스에 전체보기를 위한 메소드 구현
=> Main 클래스에서 1번을 선택했을 때 수행할 내용을 작성
2) Main 클래스에서 Switch구문 바깥에 switch구문내에서 사용할 변수를 생성
=> 변수 생성
- if와 else 또는 else if는 독립된 구문이어서 동일한 변수를 각각의 블럭에서 생성가능
- switch의 case에서는 { }를 하지 않기 때문에 case 각각에서 변수 생성을 하지 못함
- 여러개의 case에서 사용할 변수는 switch 외부에 생성해야 함
8. 상세 보기를 구현
=> 상세보기 : 목록보기 상태에서 기본키를 이용하여 하나의 데이터를 자세히 조회
=> 게시판에서 게시글 제목 클릭하여 상세보기, 회원정보확인, 수정을 위해 자세히 보는 기능들을 구현할 때 사용
=> 이 로직은 아이디 중복체크나 수정, 삭제시 아이디에 해당하는 데이터의 존재 여부를 확인하는데도 사용이 가능
1) DAO 클래스
9. 데이터 삽입
=> 기본키를 어떻게 생성할 것인지 고려
- 기본키의 값을 직접 입력하도록 : 아이디
- 기본키의 값을 자동으로 설정 : 일련번호
=> 직접 입력하도록 하는 경우 중복검사를 수행
=> 자동으로 설정하는 방법 : 오라클의 시퀀스, MySQL의 Auto_Increment를 이용, 가장 큰 번호를 찾아서 +1
- 문자가 섞인 문장 숫자만들기 -> Integer.parseInt(galaxy10.substring(6)) +1
'수업 정리' 카테고리의 다른 글
| 30일차 수업정리 (0) | 2020.05.20 |
|---|---|
| 29일차 수업 정리(MySQL 데이터베이스 연결작업 - 후-) (0) | 2020.05.19 |
| 27일차 수업 정리(MySQL) (0) | 2020.05.15 |
| 26일차 수업 정리 (0) | 2020.05.14 |
| 25일차 수업 정리 (0) | 2020.05.13 |