DBMS

Database Management System’

Database를 관리하고 운영하는 소프트웨어

RDBMS

Relational DBMS

관계형 데이터베이스

RDBMS는 테이블(table)이라는 최소 단위로 구성되며, 이 테이블은 열(column)과 행(row)으로 이루어져 있다.

테이블간 FK(Foreign Key)를 통해 다른 데이터를 조합해서 함께 볼수 있는 것이 장점

아이디 이름 전화번호 그룹 column 명

ka123	카즈하	010-7777-7777	르세라핌	1 row
kim123	김채원	010-6666-6666	르세라핌	2 row
sa123	사쿠라	010-8888-8888	르세라핌	3 row
heo123	허윤진	010-0000-0000	르세라핌	4 row
hong123	홍은채	010-1111-1111	르세라핌	5 row

MySQL - RDBMS의 일종

Spring과 궁합이 좋다

SQL

Structured Query Language

RDBMS에서 사용되는 언어

DDL

Data Definition Language

테이블이나 관계의 구조를 생성

CREATE : 새로운 데이터베이스 및 테이블을 생성

CREATE DATABASE 데이터베이스이름;
CREATE TABLE 테이블이름
(
		필드이름1 필드타입1,
    필드이름2 필드타입2,
    ...
);

ALTER : 데이터베이스와 테이블의 내용을 수정할 수 있습니다.

ALTER TABLE 테이블이름 ADD 필드이름 필드타입;
ALTER TABLE 테이블이름 DROP 필드이름;
ALTER TABLE 테이블이름 MODIFY COLUMN 필드이름 필드타입;

DROP : 데이터베이스와 테이블을 삭제할 수 있습니다. 데이터 및 테이블 전체를 삭제합니다.

DROP DATABASE 데이터베이스이름;
DROP TABLE 테이블이름;

TRUNCATE : 데이터베이스와 테이블을 삭제할 수 있습니다. 최초 테이블이 만들어졌던 상태 즉, 컬럼값만 남깁니다.

TRUNCATE DATABASE 데이터베이스이름;
TRUNCATE TABLE 테이블이름;

DCL

Data Control Language

GRANT : 사용자 또는 ROLE에 대해 권한을 부여할 수 있습니다.

GRANT [객체권한명] (컬럼)
ON [객체명]
TO { 유저명 | 롤명 | PUBLC} [WITH GRANT OPTION];

//ex
GRANT SELECT ,INSERT 
ON mp
TO scott WITH GRANT OPTION;

REVOKE : 사용자 또는 ROLE에 부여한 권한을 회수할 수 있습니다.

REVOKE { 권한명 [, 권한명...] ALL}
ON 객체명
FROM {유저명 [, 유저명...] | 롤명(ROLE) | PUBLIC} 
[CASCADE CONSTRAINTS];

//ex
REVOKE SELECT , INSERT
ON emp
FROM scott
[CASCADE CONSTRAINTS];

DML

Data Manipulation Language

테이블에 데이터를 검색, 삽입, 수정, 삭제

INSERT : 테이블에 새로운 row를 추가할 수 있습니다.

INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...) 
VALUES(데이터값1, 데이터값2, 데이터값3, ...);
INSERT INTO 테이블이름 
VALUES(데이터값1, 데이터값2, 데이터값3, ...);

SELECT : 테이블의 row를 선택할 수 있습니다.

SELECT 필드이름 FROM 테이블이름 [WHERE 조건];

UPDATE : 테이블의 row의 내용을 수정할 수 있습니다.

UPDATE 테이블이름 
SET 필드이름1=데이터값1, 필드이름2=데이터값2, ... 
WHERE 필드이름=데이터값;

DELETE : 테이블의 row를 삭제할 수 있습니다.

DELETE FROM 테이블이름 WHERE 필드이름=데이터값;

SQL 연습하기

SQL 에

CREATE DATABASE

CREATE DATABASE academy;

Intellij Database 연동

Test Connection

SQL console 열어서

테이블들 생성해줌

예전에 로그인해둔 워크벤치에서 academy는 만들었는데 난리남

인텔리제이에 연결이 안 됨

비밀번호를 모르기 때문에 뭘 조작할수도 없다

삭제하고 다시 설치해도 예전 비밀번호 치라고 해서

프로그램 파일 폴더에 있는 것도 다 삭제하고 다시 깔았다

ProgramData도 다 삭제하고 깔아보고 하여튼 별 짓 다 했는데 안 됐다

MySQL 검색해서 나오는 모든 수상한 MySQL 를 싹 다 삭제하고서야 비밀번호 새로 생성하라고 했음

드디어 된다 ...

이름도 Already in use래서 바꿈

MySQL Shell에서

\connect —mysql root@localhost:3306

로 연결함

\sql

해줘야 sql문법 쓸 수 있다고 함

그리고 여기서

create database academy;

하고 이어서 진행함

Table 만듦

MAJOR 테이블

CREATE TABLE IF NOT EXISTS MAJOR
(	
	major_code varchar(100) primary key comment '주특기코드', 
	major_name varchar(100) not null comment '주특기명',
	tutor_name varchar(100) not null comment '튜터'
);

STUDENT 테이블

CREATE TABLE IF NOT EXISTS STUDENT
(
	student_code varchar(100) primary key comment '수강생코드', 
	name varchar(100) not null comment '이름',
	birth varchar(8) null comment '생년월일',
	gender varchar(1) not null comment '성별',
	phone varchar(11) null comment '전화번호',
	major_code varchar(100) not null comment '주특기코드',
	foreign key(major_code) references major(major_code)
);

EXAM 테이블

CREATE TABLE IF NOT EXISTS EXAM
(
	student_code varchar(100) not null comment '수강생코드', 
	exam_seq int not null comment '시험주차', 
	score decimal(10,2) not null comment '시험점수',
	result varchar(1) not null comment '합불'
);

tables→diagram→ popup 하면 테이블간의 관계가 뜸

외래키를 걸어줌

ALTER TABLE EXAM ADD PRIMARY KEY(student_code, exam_seq);
ALTER TABLE EXAM ADD CONSTRAINT exam_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code);

INSERT INTO MAJOR VALUES('m1', '스프링', '남병관');
INSERT INTO MAJOR VALUES('m2', '노드', '강승현');
INSERT INTO MAJOR VALUES('m3', '플라스크', '이범규');
INSERT INTO MAJOR VALUES('m4', '루비온레일즈', '차은서');
INSERT INTO MAJOR VALUES('m5', '라라벨', '구름');
INSERT INTO MAJOR VALUES('m6', '리엑트', '임민영');
INSERT INTO MAJOR VALUES('m7', '뷰', '김서영');
INSERT INTO MAJOR VALUES('m8', '엥귤러', '한현아');

INSERT INTO STUDENT VALUES('s1', '최원빈', '20220331', 'M', '01000000001', 'm1');
INSERT INTO STUDENT VALUES('s2', '강준규', '20220501', 'M', '01000000002', 'm1');
INSERT INTO STUDENT VALUES('s3', '김영철', '20220711', 'M', '01000000003', 'm1');
INSERT INTO STUDENT VALUES('s4', '예상기', '20220408', 'M', '01000000004', 'm6');
INSERT INTO STUDENT VALUES('s5', '안지현', '20220921', 'F', '01000000005', 'm6');
INSERT INTO STUDENT VALUES('s6', '이대호', '20221111', 'M', '01000000006', 'm7');
INSERT INTO STUDENT VALUES('s7', '정주혜', '20221117', 'F', '01000000007', 'm8');
INSERT INTO STUDENT VALUES('s8', '고미송', '20220623', 'F', '01000000008', 'm6');
INSERT INTO STUDENT VALUES('s9', '이용우', '20220511', 'M', '01000000009', 'm2');
INSERT INTO STUDENT VALUES('s10', '심선아', '20220504', 'F', '01000000010', 'm8');
INSERT INTO STUDENT VALUES('s11', '변정섭', '20220222', 'M', '01000000020', 'm2');
INSERT INTO STUDENT(student_code, name, gender, major_code) VALUES('s12', '권오빈', 'M', 'm3');
INSERT INTO STUDENT VALUES('s13', '김가은', '20220121', 'F', '01000000030', 'm1');
INSERT INTO STUDENT(student_code, name, gender, major_code) VALUES('s14', '김동현', 'M', 'm4');
INSERT INTO STUDENT VALUES('s15', '박은진', '20221101', 'F', '01000000040', 'm1');
INSERT INTO STUDENT(student_code, name, birth, gender, phone, major_code) VALUES('s16', '정영호', '20221105', 'M', '01000000050', 'm5');
INSERT INTO STUDENT(student_code, name, gender, major_code) VALUES('s17', '박가현', 'F', 'm7');
INSERT INTO STUDENT(student_code, name, birth, gender, phone, major_code) VALUES('s18', '박용태', '20220508', 'M', '01000000060', 'm6');
INSERT INTO STUDENT VALUES('s19', '김예지', '20220505', 'F', '01000000070', 'm2');
INSERT INTO STUDENT VALUES('s20', '윤지용', '20220909', 'M', '01000000080', 'm3');
INSERT INTO STUDENT VALUES('s21', '손윤주', '20220303', 'F', '01000000090', 'm6');

INSERT INTO EXAM VALUES('s1', 1, 8.5, 'P');
INSERT INTO EXAM VALUES('s1', 2, 9.5, 'P');
INSERT INTO EXAM VALUES('s1', 3, 3.5, 'F');
INSERT INTO EXAM VALUES('s2', 1, 8.2, 'P');
INSERT INTO EXAM VALUES('s2', 2, 9.5, 'P');
INSERT INTO EXAM VALUES('s2', 3, 7.5, 'P');
INSERT INTO EXAM VALUES('s3', 1, 9.3, 'P');
INSERT INTO EXAM VALUES('s3', 2, 5.3, 'F');
INSERT INTO EXAM VALUES('s3', 3, 9.9, 'P');
INSERT INTO EXAM VALUES('s4', 1, 8.4, 'P');
INSERT INTO EXAM VALUES('s5', 1, 9.5, 'P');
INSERT INTO EXAM VALUES('s5', 2, 3.5, 'F');
INSERT INTO EXAM VALUES('s6', 1, 8.3, 'P');
INSERT INTO EXAM VALUES('s7', 1, 9.2, 'P');
INSERT INTO EXAM VALUES('s7', 2, 9.9, 'P');
INSERT INTO EXAM VALUES('s7', 3, 3.6, 'F');
INSERT INTO EXAM VALUES('s8', 1, 8.4, 'P');
INSERT INTO EXAM VALUES('s9', 1, 9.7, 'P');
INSERT INTO EXAM VALUES('s10', 1, 8.4, 'P');
INSERT INTO EXAM VALUES('s10', 2, 9.8, 'P');
INSERT INTO EXAM VALUES('s10', 3, 8.4, 'P');
INSERT INTO EXAM VALUES('s11', 1, 8.6, 'P');
INSERT INTO EXAM VALUES('s12', 1, 9.2, 'P');
INSERT INTO EXAM VALUES('s13', 1, 8.1, 'P');
INSERT INTO EXAM VALUES('s13', 2, 9.5, 'P');
INSERT INTO EXAM VALUES('s13', 3, 2.1, 'F');
INSERT INTO EXAM VALUES('s14', 1, 9.2, 'P');
INSERT INTO EXAM VALUES('s15', 1, 9.7, 'P');
INSERT INTO EXAM VALUES('s15', 2, 1.7, 'F');
INSERT INTO EXAM VALUES('s16', 1, 8.4, 'P');
INSERT INTO EXAM VALUES('s17', 1, 9.3, 'P');
INSERT INTO EXAM VALUES('s17', 2, 9.9, 'P');
INSERT INTO EXAM VALUES('s17', 3, 1.3, 'F');
INSERT INTO EXAM VALUES('s18', 1, 9.9, 'P');
INSERT INTO EXAM VALUES('s19', 1, 9.4, 'P');
INSERT INTO EXAM VALUES('s19', 2, 8.9, 'P');
INSERT INTO EXAM VALUES('s19', 3, 7.4, 'F');
INSERT INTO EXAM VALUES('s20', 1, 8.1, 'P');
INSERT INTO EXAM VALUES('s20', 2, 6.4, 'F');
INSERT INTO EXAM VALUES('s21', 1, 9.5, 'P');
INSERT INTO EXAM VALUES('s21', 2, 8.8, 'P');
INSERT INTO EXAM VALUES('s21', 3, 8.2, 'P');

Update : m1을 m2로 바꿔봄

INSERT INTO STUDENT VALUES('s0', '수강생', '20220331', 'M', '01000000005', 'm1');
UPDATE STUDENT SET major_code= 'm2' where student_code= 's0';

UPDATE STUDENT SET major_code= 'm1' where student_code= 's0';

Delete : 수강생 삭제해봄

DELETE FROM STUDENT WHERE student_code = 's0';

s0사라짐

Select

SELECT * FROM STUDENT;
SELECT * FROM STUDENT WHERE STUDENT_CODE = 's1';
SELECT name, major_code FROM STUDENT WHERE student_code = 's1';

SELECT s.name, s.major_code, m.major_name FROM STUDENT s JOIN MAJOR m ON s.major_code = m.major_code;
SELECT s.name, s.major_code, m.major_name FROM STUDENT s, MAJOR m WHERE s.major_code = m.major_code;

JDBC

Java Database Connectivity

DB에 접근할 수 있도록 Java에서 제공하는 API

JDBC에 연결해야하는 DB의 JDBC 드라이버 → DB 연결 로직을 변경할 필요없이 DB 변경이 가능

JDBC 드라이버 :

DB 회사들은 자신들의 DB에 맞도록 JDBC 인터페이스를 구현한 후 라이브러리로 제공

JDBC의 등장 후 많이 편리해졌지만 아직도 DB에 연결하려면 여러가지 작업 로직들을 직접 작성해야 함 → JdbcTemplate 등장!

JdbcTemplate :

커넥션 연결, statement 준비 및 실행, 커넥션 종료 등의 반복적이고 중복되는 작업들을 대신 처리

src → main → resources → application.properties에 DB에 접근하기 위한 정보를 작성

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/memo
spring.datasource.username=root
spring.datasource.password=비밀번호는 비밀임
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver//빨간줄 -> 2)에서 해결

build.gradle → dependencies → JDBC 라이브러리와 MySQL을 등록

build.gradle : JDBC, MySQL 추가

// MySQL
implementation 'mysql:mysql-connector-java:8.0.28'
implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'

MySQL에 db생성

create database memo;

show databases;

인텔리제이에 연결, 테이블 생성

create table memo
(
    id       bigint       not null auto_increment,
    contents varchar(500) not null,
    username varchar(255) not null,
    primary key (id)
);

Memo Controller 만듦

package com.sparta.memo.controller;

import com.sparta.memo.dto.MemoRequestDto;
import com.sparta.memo.dto.MemoResponseDto;
import com.sparta.memo.entity.Memo;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.web.bind.annotation.*;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

@RestController
@RequestMapping("/api")
public class MemoController {

    private final JdbcTemplate jdbcTemplate;

    public MemoController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @PostMapping("/memos")
    public MemoResponseDto createMemo(@RequestBody MemoRequestDto requestDto) {
        // RequestDto -> Entity
        Memo memo = new Memo(requestDto);

        // DB 저장
        KeyHolder keyHolder = new GeneratedKeyHolder(); // 기본 키를 반환받기 위한 객체

        String sql = "INSERT INTO memo (username, contents) VALUES (?, ?)";
        jdbcTemplate.update( con -> {
                    PreparedStatement preparedStatement = con.prepareStatement(sql,
                            Statement.RETURN_GENERATED_KEYS);

                    preparedStatement.setString(1, memo.getUsername());
                    preparedStatement.setString(2, memo.getContents());
                    return preparedStatement;
                },
                keyHolder);

        // DB Insert 후 받아온 기본키 확인
        Long id = keyHolder.getKey().longValue();
        memo.setId(id);

        // Entity -> ResponseDto
        MemoResponseDto memoResponseDto = new MemoResponseDto(memo);

        return memoResponseDto;
    }

    @GetMapping("/memos")
    public List<MemoResponseDto> getMemos() {
        // DB 조회
        String sql = "SELECT * FROM memo";

        return jdbcTemplate.query(sql, new RowMapper<MemoResponseDto>() {
            @Override
            public MemoResponseDto mapRow(ResultSet rs, int rowNum) throws SQLException {
                // SQL 의 결과로 받아온 Memo 데이터들을 MemoResponseDto 타입으로 변환해줄 메서드
                Long id = rs.getLong("id");
                String username = rs.getString("username");
                String contents = rs.getString("contents");
                return new MemoResponseDto(id, username, contents);
            }
        });
    }

    @PutMapping("/memos/{id}")
    public Long updateMemo(@PathVariable Long id, @RequestBody MemoRequestDto requestDto) {
        // 해당 메모가 DB에 존재하는지 확인
        Memo memo = findById(id);
        if(memo != null) {
            // memo 내용 수정
            String sql = "UPDATE memo SET username = ?, contents = ? WHERE id = ?";
            jdbcTemplate.update(sql, requestDto.getUsername(), requestDto.getContents(), id);

            return id;
        } else {
            throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
        }
    }

    @DeleteMapping("/memos/{id}")
    public Long deleteMemo(@PathVariable Long id) {
        // 해당 메모가 DB에 존재하는지 확인
        Memo memo = findById(id);
        if(memo != null) {
						// memo 삭제
            String sql = "DELETE FROM memo WHERE id = ?";
            jdbcTemplate.update(sql, id);

            return id;
        } else {
            throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
        }
    }

    private Memo findById(Long id) {
        // DB 조회
        String sql = "SELECT * FROM memo WHERE id = ?";

        return jdbcTemplate.query(sql, resultSet -> {
            if(resultSet.next()) {
                Memo memo = new Memo();
                memo.setUsername(resultSet.getString("username"));
                memo.setContents(resultSet.getString("contents"));
                return memo;
            } else {
                return null;
            }
        }, id);
    }
}

저작자표시

'TIL(Develop)' 카테고리의 다른 글

IoC와 DI (5)	2023.11.09
3 Layer Architecture 역할 분리 (1)	2023.11.09
HTTP 데이터를 객체로 처리하는 방법 (0)	2023.11.07
Path Variable과 Request Param (0)	2023.11.06
Jackson이란 무엇일까 (0)	2023.11.06

Greedeveloper

Database&SQL&JDBC

DBMS

RDBMS

SQL

DDL

DCL

DML

Update : m1을 m2로 바꿔봄

Delete : 수강생 삭제해봄

Select

JDBC

JDBC 드라이버 :

JdbcTemplate :

'TIL(Develop)' 카테고리의 다른 글

티스토리툴바

Database&SQL&JDBC

DBMS

RDBMS

SQL

DDL

DCL

DML

Update : m1을 m2로 바꿔봄

Delete : 수강생 삭제해봄

Select

JDBC

JDBC 드라이버 :

JdbcTemplate :

'TIL(Develop)' 카테고리의 다른 글

관련글

티스토리툴바