[KB IT's Your Life] 1주차 데이터베이스 활용 & 모델링 💡

💡 데이터베이스 개요

✔ 데이터베이스란?

: Data를 영구적으로 저장하는 구조화된 저장소

: 여러 사람이 공유하여 사용할 목적으로 체계화해 통합, 관리하는 데이터의 집합

: 데이터베이스 관리 시스템(DBMS)의 통합된 정보들을 저장하여 운영할 수 있는 공용 데이터의 묶음

 

✔ Database 특징

1. 실시간 접근성 : 사용자가 데이터 요청 시 실시간으로 결과 서비스
2. 계속적인 변화 : 데이터 값은 삽입, 삭제, 수정을 통해 변화를 가짐
3. 동시 공유 : 서로 다른 업무 또는 여러 사용자에게 동시 공유됨
4. 내용에 따른 참조 : 데이터의 물리적 위치가 아닌 데이터 값에 따라 참조
   ex) 메모리 : 0x1000 주소에 사용자 정보를 저장 DB : user1004인 사람의 이름은 홍길동

 

 

---

 

 

 

자료(Data)와 정보(Information)차이

✔ 자료(Data)란?

: 현실에서 수집된 결과로 나타난 정략적 혹은 정성적인 실제 값(수치,텍스트)

✔ 정보(Information)란?

: 데이터를 기반으로 사용자에게 유용하게 활용 되도록 목적에 따라 가공한 결과물

 

 

 

---

 

 

 

DBMS(DataBase Management System)

데이터를 만들고 저장하는 관리 프로그램(=시스템)
다수의 사용자들이 DB내의 데이터를 접근할 수 있도록 해주는 소프트웨어 도구의 집합

 

 

✔ DBMS 주요 기능

데이터 조작 (Manipulation, DML)	사용자가 요구하는 데이터를 조회(Select)하거나 데이터의 삽입, 수정, 삭제 등의 연산을 처리하는 기능, 실제 사용자와 DB간의 데이터 연동 수단	SELECT, INSERT, UPDATE, DELETE
데이터 정의 (Definition, DDL)	데이터의 구조를 정의하고 데이터 구조에 대한 삭제 및 변경 기능 수행	CREATE, ALTER, DROP, TRUNCATE
데이터 제어 (Control, DCL)	데이터베이스 사용자를 생성하고 모니터링하며 접근 제어(권한) 및 백업	GRANT, REVOKE

 

 

 

---

 

 

관계형데이터베이스와 비관계형 데이터베이스

✔ 관계형 데이터베이스(RDB)(=SQL 데이터베이스)

키와 값들의 간단한 관계를 테이블(행렬구조, 2차원 배열)화 시킨 매우 간단한 원칙의 DB

• 모든 데이터를 2차원 테이블 형태로 표현
• 데이터 중복을 최소화할 수 있다 !

→ 가장 많이 활용하는 DB 고전 구조

 

 

✔ 비관계형 데이터베이스(=NoSQL 데이터베이스)

전통적인 RDBS와 다른 DBMS를 지칭하기 위한 용어로, 데이터에 액세스하고 관리하기 위해 다양한 데이터 모델을 사용하는 DB

• SQL과 같은 구조적 언어를 지원하지 않고, 언어의 Library를 통해 DB 접근 기능 지원
• 데이터 저장에 고정 Table-Schema가 필요하지 않음
• Join 연산을 사용할 수 없음
• 수평적으로 확장 가능한 DBMS

→ 성능 및 단순한 구조적 이점을 가질 수 있으며, 최근 성능이 중요한 시스템과 데이터 분석 분야에서 활용

 

 

 

---

 

 

💡 MySQL 시작하기

SQL ( Structured Query Language )

관계형 데이터 베이스에서 데이터를 조회하거나 조작하기 위해 사용하는 표준 검색 언어 원하는 데이터를 찾는 방법이나 절차를 기술하는 게 아닌 조건을 기술하여 작성

 

 

 

데이터 딕셔너리 (Data Dictionary)

데이터 사전이란 의미로 DB의 스키마 정보, 테이블 구조, 유저 목록, 권한 정보 등 DB 구성에 필요한 메타데이터를 저장하고 있는 DB
→ DB 구성과 성능에 영향을 미치는 구조로 굉장히 중요한 영역 !
MySQL 8.0버전부터는 InnoDB를 통해 관리한다.

✔ 딕셔너리 주요 구문

use sys; -- 데이터베이스 선택
show tables; -- 테이블 목록 조회
show table status; -- 테이블 상태 정보 ( 테이블 이름, 엔진, 행 수, 데이터 크기, 인덱스 크기, 생성 시간 및 수정 시간 등 )
show privileges; -- 현재 데이터베이스에 대한 사용자 권한 목록

 

 

 

✔ 스키마 생성 및 USE 명령어

--DB 생성 (대소문자 구분)
CREATE SCHEMA db_name;

-- DB 삭제
DROP SCHEMA db_name;

-- USE 명령으로 DB이름을 생략하는 명령어
USE db_name;
SELECT * FROM table_name;

-- 만일 USE 명령어를 안쓸 경우
SELECT * FROM db_name.table_name;

 

 

 

 

---

 

 

DDL ( Data Definition Language )

데이터 정의 언어로 DB에서 사용되는 다양한 객체(Object)를 생성하고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말한다.

💡 객체 종류
-> 스키마(SCHEMA), 테이블(TABLE), 뷰(VIEW), 인덱스(INDEX), 프로시저(PROCEDUAL), 함수(FUNCTION), 트리거(TRIGGER), 사용자(USER)

 

 

✔ CREATE

테이블이나 스키마, 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문

CREATE TABLE tbl_member (
    member_no INT,
    member_id VARCHAR(20),
    member_pwd VARCHAR(20),
    member_name VARCHAR(20)
);

💡 객체나 칼럼 이름에 특수문자가 들어갈 경우 → 백틱(`)사용 !

CREATE TABLE `member;_test`(
	`member;_id` VARCHAR(20),
	`member;_pwd` VARCHAR(20),
	`member;_name` VARCHAR(20)
	);

💡 테이블이 존재하지 않을 때만 테이블 생성

CREATE TABLE IF NOT EXISTS member(
member_id VARCHAR(20),
member_pwd VARCHAR(20),
member_name VARCHAR(20)
);

 

 

 

제약 조건 (CONSTRAINTS)

DB 데이터의 무결성을 확보하기 위해 특정한 제약 조건 만들고 데이터를 입력 받을 때 데이터를 검사하여 조건에 맞는 데이터만 저장하는 기능

제약 조건	설명
NOT NULL	데이터에 NULL을 허용하지 않음
UNIQUE	중복된 값을 허용하지 않음
PRIMARY KEY (PK)	컬럼의 고유 식별자로 NULL과 중복 값 허용 안함, INDEX 자동 생성 (NOT NULL + UNIQUE)
FOREIGN KEY (FK)	참조되는 테이블의 컬럼의 값이 존재하면 허용
CHECK	저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용
DEFAULT	Insert 명령시 값이 없어도 자동으로 초기화 되는 값을 생성
AUTO_INCREMENT	고유 값을 만들어 1씩 자동으로 증가 시킴 (다른 DB Sequence 기능)

CREATE TABLE user_foreignkey(
	user_no INT PRIMARY KEY,
	user_id VARCHAR(20) UNIQUE,
	user_pwd VARCHAR(30) NOT NULL,
	user_name VARCHAR(30),
	gender VARCHAR(10),
	phone VARCHAR(30),
	email VARCHAR(50),
	grade_code INT,
	CONSTRAINT FK_grade_code -- 제약의 이름을 추가 할 때 사용
		FOREIGN KEY (grade_code) REFERENCES USER_GRADE(grade_code)
);

 

 

 

 

 

✔ ALTER

테이블에 정의된 내용을 수정할 때 사용하는 데이터 정의어

-- 컬럼 추가
ALTER TABLE dept_copy ADD (cname VARCHAR(20));

-- 제약 조건 추가 (기본키)
ALTER TABLE dept_copy ADD CONSTRAINT dcopy_id_pk PRIMARY KEY(dept_id);

-- 컬럼 수정

-- TYPE만 변경하는 경우는 MODIFY 활용
ALTER TABLE dept_copy MODIFY COLUMN dept_id VARCHAR(20) NOT NULL;
-- 이름까지 변경 할 때 CHANGE 활용
ALTER TABLE dept_copY CHANGE COLUMN lname lname2 VARCHAR(20) NOT NULL DEFAULT '미국'

-- 제약 조건 삭제 
ALTER TABLE dept_copy DROP PRIMARY KEY;

-- 테이블 이름 변경
ALTER TABLE dept_copy RENAME TO dept_test;

 

 

 

✔ DROP

모든 객체(테이블, 스키마, VIEW 등)을 삭제할 수 있는 구문 📛 Rollback 되지 않고 바로 휘발되는 것을 주의해야함 !!

DROP SCHEMA kb;
DROP TABLE kb.member;
DROP TABLE member, department, jobtable;
DROP TABLE IF EXISTS kb.member;

 

 

✔ TRUNCATE

테이블 전체 행 삭제 시 사용하며 DELETE보다 수행 속도가 빠르고 ROLLBACK을 통해 복구 불가능
테이블 구조는 남아있음

TRUNCATE TABLE emp_salary;

 

 

 

---

 

 

 

DML (Data Manipulation Languae)

데이터 조작 언어로 테이블에 값을 삽입(INSERT), 수정(UPDATE), 삭제(DELETE) 하는 구문

 

 

✔ INSERT

-- 데이터 삽입
INSERT INTO employee
VALUES(1, ‘홍길동’, ‘820114-1010101’, ‘hong_kd@muliti.com’, ‘01099998888’, ‘D5’, ‘J2’, ‘S4’, 3800000,
NULL, ‘200’, SYSDATE, NULL, DEFAULT);

 

✔ UPDATE

-- 데이터 수정
UPDATE employee
SET emp_id = 290
WHERE emp_name = ‘홍길동’;

 

✔ DELETE

• WHERE절과 ORDER BY절, LIMIT 절도 활용 가능
• 삭제 시 FOREIGN KEY 제약조건으로 컬럼 삭제가 불가능한 경우, 제약 조건을 비활성화 하고 삭제할 수 있음.

-- 데이터 삭제
DELETE FROM employee
WHERE emp_name = ‘홍길동’;

 

 

✔ SELECT

데이터를 조회하거나 조작하기 위해 사용하는 표준 검색 언어

SELECT 컬럼 명 [, 컬럼명, …] -- 컬럼명 소문자 권장
FROM 테이블 명 -- 테이블명 소문자 권장
WHERE 조건식; -- 조건식 컬럼명은 소문자 키워드는 대문자 권장

 

DISTINCT - 중복제거

-- 중복 값을 제외하고 한 번씩만 표시 (DISTINCT)
SELECT DISTINCT job_code
FROM employee;

 

WHERE - 조건

-- WHERE절
-- 부서코드가 'D6'이고 급여를 2000000 보다 많이 받는 직원의 이름, 부서코드, 급여 조회
SELECT emp_name, dept_code, salary
FROM employee
WHERE dept_code = ‘D6’ AND salary > 2000000;

 

ORDER BY - 정렬

SELECT * FROM employee ORDER BY emp_id ASC; -- emp_id 기준 오름차순 정렬
SELECT * FROM employee ORDER BY emp_id DESC; -- emp_id 기준 내림차순 정렬

 

LIMIT - 행 수 제어

SELECT * FROM employee LIMIT 5;

 

LIKE

-- ‘전’씨 성을 가진 직원 이름과 급여 조회
SELECT emp_name, salary
FROM employee
WHERE emp_name LIKE ‘전%’;

-- 핸드폰의 앞 네 자리 중 첫 번호가 7인 직원 이름과 전화번호 조회
SELECT emp_name, phone
FROM employee
WHERE phone LIKE ‘_ _ _7%’;

 

 

 

---

 

 

💡JDBC

: 자바에서 데이터베이스(DB)에 접속할 수 있도록 하는 자바 API (java.sql 패키지)

 

 

• JDBC Driver : JDBC 인터페이스를 구현한 것, 실제로 DB와 작업 (.jar 파일)

 

 

 

 

 

✔JDBC Coding 절차

1. Driver 등록 (DriverManger)

Class.forName("com.mysql.cj.jdbc.Driver");
// 다른 DB설정
// 만약 oracle이면?
// Class.forName("oracle.jdbc.driver.OracleDriver");

💡 주의! 반드시 ClassNotFoundException 처리를 해야 함
그 외에도 SQLException처리 함께 필요 → try/catch문 사용

 

 

2. DBMS 연결 (Connection)

Connection conn = DriverManger.getConnection(“jdbc:mysql://localhost:3306/jdbc”,"user",1234);

 

 

3. SQL 생성

String sql = "SELECT ID, LAST_NAME FROM EMP";

 

 

4. SQL 전송 (Statement)

// SELECT
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(query);

// INSERT, UPDATE, DELETE
Statement stmt = conn.createStatement();
int result = stmt.executeUpdate(query);

 

 

5. 결과 받기 (ResultSet)(int)

ResultSet rset = stmt.executeQuery(query);
while(rset.next()){
System.out.println(rset.getString(“ID”) + “\\t” +
rset.getString(2));
}

int result = stmt.executeUpdate(query);

 

 

6. 닫기(객체 반환)

rset.close(); //ResultSet 사용한 경우 반납처리
stmt.close();
conn.close();

 

 

---

 

 

✔ MVC 패턴이란?

: SW 공학, 아키텍처 디자인 패턴 중 하나로 Model - View - Controller의 약자

: MVC의 핵심은 화면(View), 제어(Controller), 데이터 연동(Model)을 구성하는 로직(코드)를 분리하여 서로 간의 영향도를 줄이고 코드를 간결하게 관리하여 코드 설계 품질을 향상시킬 수 있는 기법

 

 

View

: User Interface (UI) 를 담당하는 코드

: 사용자로부터 입력 받고, 결과를 화면에 보여주기 위한 역할

ex) 고객과 소통하는 역할 = 프론트 직원

 

 

Controller

: View와 Model의 중간다리 역할

: View로 부터 전달 받은 사용자 입력(요청)을 분석하고, 이에 필요한 데이터를 Model로 부터 전달 받아 올바른 결과의 View를 구성하도록 전달

ex) 일은 하지 않고 시키기만 하는 역할 = 관리직

 

 

Model

: 데이터를 처리하기 위한 로직을 구성

: DBMS와 상호작용 하거나 DATA를 가공하는 역할

: 비즈니스 로직이라고도 부르며, 요청 받고 실제 필요한 데이터를 가공하는 역할

ex) 노동자

 

 

✔MVC Class의 구조

VO(Value Object)

: 데이터베이스의 각 컬럼 개체(entity) 저장용 클래스가 있는 패키지

: DB의 도메인 정보를 Client에게 전달할 때 그 도메인 단위 정보를 VO에 구현

 

 

DTO(Data Transfer Object)

: 데이터베이스의 각 컬럼 개체(entity) 저장용 클래스가 있는 패키지

: DB의 도메인과 일치하지 않으며 어떤 로직에 사용되는 영속적이지 않은 정보를 DTO에 구현

 

 

DAO(Data Access Object)

: DB에 접근하여 DB 관련 처리를 담당하는 클래스

• 기능 별로 메서드를 구현함

ex) member테이블

• 회원가입하는 메서드 insert()
• 회원탈퇴하는 메서드 delete()
• 회원수정하는 메서드 update()
• 회원정보검색하는 메서드 one(),list()

 

 

 

💭DTO와 VO는 무엇이 다른걸까 ?

DTO(Data Transfer Object)

: 순수하게 데이터를 담아 계층 간으로  전달 하는 객체

• getter, setter 메소드만 가진다.
  • 추가적으로 toString(), equals()과 같은 Object 클래스 메소드 사용가능

public class MemberDTO {
    
    private String name;
    private String password;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String id) {
        this.password = password;
    }
    
}

→ 데이터 전송이 목적

 

 

 

VO(Value Object)

: 값 그 자체를 나타내는 객체

• 불변성 보장
• 주로 비즈니스 로직에 사용

public class UserService {
    public void createUser(UserDTO userDTO) {
        // UserDTO를 사용하여 사용자 생성
    }

    public Money calculateTotalAmount(List amounts) {
        BigDecimal total = BigDecimal.ZERO;
        for (Money amount : amounts) {
            total = total.add(amount.getAmount());
        }
        return new Money(total, Currency.getInstance("USD"));
    }
}

→ 비즈니스 로직이 목적

 

 

 

---

 

✔ 최종 JDBC 코드 구현

import java.util.Date;

public class MemberVO {
    private int mno;            // int
    private String id;         // varchar(20)
    private String password;   // varchar(20)
    private String name;       // varchar(20)
    private String gender;     // varchar(1)
    private int age;            // int
    private String email;      // varchar(30)
    private String phone;      // char(13)
    private String address;    // varchar(200)
    private String hobby;      // varchar(100)
    private Date birthday;     // date
    private Date enrolldate;   // datetime

    public MemberVO() {
    }

    public MemberVO(int mno, String id, String password, String name, String gender, int age, String email, String phone, String address, String hobby, Date birthday, Date enrolldate) {
        this.mno = mno;
        this.id = id;
        this.password = password;
        this.name = name;
        this.gender = gender;
        this.age = age;
        this.email = email;
        this.phone = phone;
        this.address = address;
        this.hobby = hobby;
        this.birthday = birthday;
        this.enrolldate = enrolldate;
    }

    public int getMno() {
        return mno;
    }

    public void setMno(int mno) {
        this.mno = mno;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getHobby() {
        return hobby;
    }

    public void setHobby(String hobby) {
        this.hobby = hobby;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Date getEnrolldate() {
        return enrolldate;
    }

    public void setEnrolldate(Date enrolldate) {
        this.enrolldate = enrolldate;
    }

    @Override
    public String toString() {
        final StringBuilder sb = new StringBuilder("Member{");
        sb.append("mno=").append(mno);
        sb.append(", id='").append(id).append('\'');
        sb.append(", password='").append(password).append('\'');
        sb.append(", name='").append(name).append('\'');
        sb.append(", gender='").append(gender).append('\'');
        sb.append(", age=").append(age);
        sb.append(", email='").append(email).append('\'');
        sb.append(", phone='").append(phone).append('\'');
        sb.append(", address='").append(address).append('\'');
        sb.append(", hobby='").append(hobby).append('\'');
        sb.append(", birthday=").append(birthday);
        sb.append(", enrolldate=").append(enrolldate);
        sb.append('}');
        return sb.toString();
    }
}

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class MemberDAO {
    public static String driverClass = "com.mysql.cj.jdbc.Driver";
    public static String url = "jdbc:mysql://localhost:3306/jdbc";
    public static String user = "user";
    public static String password = "1234";
    private Connection conn = null;

    public MemberDAO() {
        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

	
    // 단일 행 조회
    public Member selectOne(int no) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select * from member where mno = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, no);
            rs = ps.executeQuery();
            if (rs.next()) {
                int mno = rs.getInt("mno");
                String id = rs.getString("id");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                int age = rs.getInt("age");
                String email = rs.getString("email");
                String phone = rs.getString("phone");
                String address = rs.getString("address");
                String hobby = rs.getString("hobby");
                Date birthday = rs.getDate("birthday");
                Date enrolldate = rs.getDate("enrolldate");
                return new Member(mno, id, password, name, gender, age, email, phone, address, hobby, birthday, enrolldate);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return null;
    }
    
	// 다중 행 조회
    public List<Member> selectAll(String memberId, String memberName) {
        List<Member> list = new ArrayList<Member>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select * from member where id like ? and name like ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + memberId + "%"); // test
            ps.setString(2, "%"+memberName+"%");
            rs = ps.executeQuery();
            while (rs.next()) {
                int mno = rs.getInt("mno");
                String id = rs.getString("id");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                int age = rs.getInt("age");
                String email = rs.getString("email");
                String phone = rs.getString("phone");
                String address = rs.getString("address");
                String hobby = rs.getString("hobby");
                Date birthday = rs.getDate("birthday");
                Date enrolldate = rs.getDate("enrolldate");
                Member member = new Member(mno, id, password, name, gender, age, email, phone, address, hobby, birthday, enrolldate);
                list.add(member);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return list;
    }

	// 데이터 삽입 코드
    public int insert(Member m) {
        PreparedStatement pstmt = null;
        try {
            String sql = "INSERT INTO MEMBER VALUES(default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, default)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, m.getId());
            pstmt.setString(2, m.getPassword());
            pstmt.setString(3, m.getName());
            pstmt.setString(4, m.getGender());
            pstmt.setInt(5, m.getAge());
            pstmt.setString(6, m.getEmail());
            pstmt.setString(7, m.getPhone());
            pstmt.setString(8, m.getAddress());
            pstmt.setString(9, m.getHobby());
            pstmt.setDate(10, new java.sql.Date(m.getBirthday().getTime()));
            return pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if(pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

 

import java.util.List;

public class MemberUI {

    public static void main(String[] args) {
        MemberDAO dao = new MemberDAO();
        MemberVO member = dao.selectOne(1);
        System.out.println(member);
        System.out.println("-------------------------------------------");
        List<MemberVO> list = dao.selectAll("test", "길동");
        list.forEach(System.out::println);
        System.out.println("-------------------------------------------");

        member.setName("홍박이길동");
        member.setId("test12879110");
        int result = dao.insert(member);
        if(result > 0){
            System.out.println("성공");
        }else {
            System.out.println("실패");
        }
        
    }
}