<데브코스> 26-02-03 TIL MySQL Workbench 사용, DB 연동, 단축평가

MySQL Workbench 사용해보기

 

1. 스키마 만들기

1) 왼쪽 SCHEMAS창에서 마우스 오른쪽 -> Create Schema

2) "우리가 이렇게 입력해줘도 되는지 확인해줘"! 라는 창 확인

 

2. 테이블 만들기

- users 테이블

트랜드에 맞게 id --> email로 바꾸고 해보자.

채널(분리)

채널 번호(PK)	채널명	구독자수	영상 수	user_id
1	달려라 가영	1	5	1
2	날아라 가영	20	50	2
3	걸어라 가영	400	200	3
4	집에가고싶은채널	1000	600	2
5	침착맨	10000	900	4

 

사용자

회원 id (PK)	이메일	이름	비밀번호	연락처
1	kim@mail.com	김가영	1111	010-1234-1234
2	lee@mail.com	이가영	2222	010-2345-2345
3	park@gmail.com	박가영	3333	010-3456-3456
4	chim@gmail.com	김침착	5555	010-5678-5678

 

 

- channels 테이블

Foregin Key 연결해주기 

CONSTARINT 제약조건 이름 user_id (그냥 별명)
FOREIGN KEY user_id 로 연결

 

3. 테이블 삽입

 

💡 AUTO INGREMENT 쓸 때는 직접 입력하면 안됨. -> 만약 데이터가 삭제 될 경우, 이후에 번호가 이를 채워주지 못함.

❗️user_id에 없는 값 넣어주면 - Foreign키 제약조건 만족하지 못해서 오류 발생 

❗️sub_num, video_count 비우면 - default로 넣어준 0으로 입력됨.

 

4. 코드 연동 

https://www.npmjs.com/package/mysql2

First Query

// Get the client
const mysql = require('mysql2');

// Create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'Youtube'
});

// A simple SELECT query
connection.query(
  'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45',
  function (err, results, fields) {
    console.log(results); // results contains rows returned by server
    console.log(fields); // fields contains extra meta data about results, if available
  }
);

// Using placeholders
connection.query(
  'SELECT * FROM `users`',
  ['Page', 45],
  function (err, results, fields) {
    console.log(results); //결과 행 추천
    console.log(fields); // 메타데이터 
  }
);

---

⏰ Timezone 설정

--> 엥 그래도 안돼있는데?

global에서는 설정이 되었지만, session에서는 아직 SYSTEM 시간으로 설정돼있음

 

다시 이 session에서 설정해주면 설정됨!!

dataStrings : true

원래에는 시간 자체(표준시간으로 세팅해주고, 원하는 타입으로 뿌려줌)를 가져오기 때문에 소수점까지 더러운 형태로 가져와짐 

=> dataStrings로 원하는 형식으로 가져와줌

전 -> 후

---

DB 모듈화 시키기 

mariadb.js

// Get the client
const mysql = require('mysql2');

// Create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'Youtube',
  dateStrings : true
});

// A simple SELECT query
connection.query(
  'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45',
  function (err, results, fields) {
    console.log(results); // results contains rows returned by server
    console.log(fields); // fields contains extra meta data about results, if available
  }
);

module.exports = connection

users.js

const conn = require('../mariadb');

// Using placeholders
conn.query(
    'SELECT * FROM `users`',
    ['Page', 45],
    function (err, results) {
            var {id, email, name, created_at} = results[0];
            console.log(id);
            console.log(email);
            console.log(name);
            console.log(created_at);
    }
);

---

✔️ SELECT

1) 로그인 : POST /login

- req : body(email, password)

- res : `${name}님 환영합니다` // -> 메인 페이지

✔️ INSERT

2) 회원 가입 : POST /join

- req : body (email, name, password, contact)

- res : `${name}님 환영합니다` // -> 로그인 페이지

✔️ SELECT

3) 회원 개별 조회 : GET /users

- req : body(email) 

- res : 회원 객체를 통으로 전달

✔️ DELETE

4) 회원 개별 탈퇴 : DELETE /users

- req : body(email)

- res : `${name}님 다음에 또 뵙겠습니다.` or 메인페이지

---

1. 회원 개별 조회 

우선 PK를 이메일로 바꾸자

router
    .route('/users')
    .get((req, res) => {
        let {email} = req.body;

        // Using placeholders
        conn.query(
            `SELECT * FROM users WHERE email = ?`, email,
            function (err, results, fields) {
                    res.status(200).json(results)
            }
        );  
    }) // 회원 개별 조회

 

---

2. 회원 가입 

//회원가입
router.post('/join', (req, res) => {
    console.log(req.body)

    if(req.body == {}){
        res.status(400).json({
            message : `입력 값을 다시 확인해주세요.`
        })
    }
    else {
        const {email, name, password, contact} = req.body
        conn.query(
            `INSERT INTO users (email, name, password, contact) 
                VALUES(?, ?, ?, ?)`, [email, name, password, contact],
            function(err, results, fields){
                res.status(201).json(results)
            }
        )
    }
})

results 출력되지 않음 -> results 가지지 않는구나!

---

3. 회원 개별 탈퇴

    .delete((req, res) => {
        let {email} = req.body;

        conn.query(
            `DELETE FROM users WHERE email = ?`, email,
            function (err, results, fields){
                res.status(200).json(results)
            }
        ) 
    })

{
    "fieldCount": 0,
    "affectedRows": 1,
    "insertId": 0,
    "info": "",
    "serverStatus": 2,
    "warningStatus": 0,
    "changedRows": 0
}

get의 result row를 기대했는데 이상한 값이 출력됨

-> 데이터가 아니라 “실행 결과 요약 정보”를 반환!

-> "affectdRows"을 가지고 프론트엔드가 처리해주기도 함

---

4. 로그인

//로그인
router.post('/login', (req, res) => {
    console.log(req.body) //userId, pwd

    //email이 디비에 저장된 회원이지 확인
    const {email, password} = req.body

    conn.query(
        `SELECT * FROM users WHERE email = ?`, [email],
        function(err, results, fields){
            var loginUser = result[0]; //result 없어도 오류 안남!
            if(loginUser && loginUser.password == password){
                res.status(200).json({
                    message : `${loginUser.name}님 로그인 되었습니다.`  
                })
            }
            else {
                res.status(404).json({
                    message : "이메일 또는 비밀번호가 틀렸습니다."
                })   
            }
        }
    )

var loginUser = result[0] // result 없어도 오류나지않음

 

---

리팩토링

const express = require('express');
//라우터가 확인할 수 있게만 하면됨
const router = express.Router()

const conn = require('../mariadb');


router.use(express.json()) //http 외 모듈 'json'


let db = new Map();
var id = 1; // 하나의 객체를 유니크하게 쿠별하기 위함. PK

function isExist(obj) {
    if(Object.keys(obj).length) {
        return true
    }
    else {
        return false
    }
}

function findByUserId(db, userId){
    let foundUser = {};
    db.forEach(function(user){
        if(user.userId === userId){
            foundUser = user;
        }
    });
    return foundUser;
}

function isPwdCorrect(loginUser, password){
    return loginUser.password === password;
}

//로그인
router.post('/login', (req, res) => {
    console.log(req.body) //userId, pwd

    //email이 디비에 저장된 회원이지 확인
    const {email, password} = req.body

    conn.query(
        `SELECT * FROM users WHERE email = ?`, [email],
        function(err, results, fields){
            var loginUser = results[0]; //results 없어도 오류 안남!
            if(loginUser && loginUser.password == password){
                res.status(200).json({
                    message : `${loginUser.name}님 로그인 되었습니다.`  
                })
            }
            else {
                res.status(404).json({
                    message : "이메일 또는 비밀번호가 틀렸습니다."
                })   
            }
        }
    )

    // if (isExist(loginUser)){
    //     //pwd 같은지 메소드 빼보자. 

    //     if(isPwdCorrect(loginUser, password)){
    //         res.status(200).json({
    //             message : `${loginUser.name}님 로그인 되었습니다.`
    //         })
    //     } else {
    //         res.status(400).json({
    //             message : "비밀번호가 틀렸습니다."
    //         })        
    //     }
    // } else {
    //         res.status(400).json({
    //             message : "회원 정보가 없습니다."
    //         })    
    // }
})

//회원가입
router.post('/join', (req, res) => {
    console.log(req.body)

    if(req.body == {}){
        res.status(400).json({
            message : `입력 값을 다시 확인해주세요.`
        })
    }
    else {
        const {email, name, password, contact} = req.body
        conn.query(
            `INSERT INTO users (email, name, password, contact) 
                VALUES(?, ?, ?, ?)`, [email, name, password, contact],
            function(err, results, fields){
                res.status(201).json(results)
            }
        )
    }
})

router
    .route('/users')
    .get((req, res) => {
        let {email} = req.body;

        // Using placeholders
        conn.query(
            `SELECT * FROM users WHERE email = ?`, email,
            function (err, results, fields) {
                    res.status(200).json(results)
            }
        );  
    }) // 회원 개별 조회
    .delete((req, res) => {
        let {email} = req.body;

        conn.query(
            `DELETE FROM users WHERE email = ?`, email,
            function (err, results, fields){
                res.status(200).json(results)
            }
        ) 
    })


module.exports = router

1️⃣ 안쓰는 코드 삭제

- function(err, results) : err쓰지 않더라도 날리면 안됨! 매개변수 순서 중요

2️⃣ 주석 

- 코드로 설명이 되지않은 경우에만 주석을 쓰자!

3️⃣ 문자열 

- 중요한 내용이기 때문에 정리 중요!! 변수로 빼서 깔끔하게 매개변수로 넣어주자

const express = require('express')
const router = express.Router()
const conn = require('../mariadb')


router.use(express.json())

router.post('/login', (req, res) => {
    const {email, password} = req.body

    let sql = `SELECT * FROM users WHERE email = ?`
    conn.query(sql, email,
        function(err, result){
            var loginUser = result[0]; //result 없어도 오류 안남!
                
            if(loginUser && loginUser.password == password){
                res.status(200).json({
                    message : `${loginUser.name}님 로그인 되었습니다.`  
                })
            }
            else {
                res.status(404).json({
                    message : "이메일 또는 비밀번호가 틀렸습니다."
                })   
            }
        }
    )
})

router.post('/join', (req, res) => {
    if(req.body == {}){
        res.status(400).json({
            message : `입력 값을 다시 확인해주세요.`
        })
    }
    else {
        const {email, name, password, contact} = req.body
        let sql = `INSERT INTO users (email, name, password, contact) VALUES(?, ?, ?, ?)`
        let values = [email, name, password, contact]
        conn.query(sql,values,
            function(err, results){
                res.status(201).json(results)
            }
        )
    }
})

router
    .route('/users')
    .get((req, res) => {
        let {email} = req.body;
        
        let sql = `SELECT * FROM users WHERE email = ?`
        conn.query(sql, email,
            function (err, results, fields) {
                    res.status(200).json(results)
            }
        );  
    }) 
    .delete((req, res) => {
        let {email} = req.body;

        let sql = `DELETE FROM users WHERE email = ?`
        conn.query(sql, email,
            function (err, results, fields){
                res.status(200).json(results)
            }
        ) 
    }) 


module.exports = router

---

1) INSERT ✔️ 채널 "생성" : POST /channels

- req : body(name, user_Id) (cf. 원래 userId는 body X header에 숨겨서 Token) 

- res 201 : `${name}님 채널을 응원합니다.`

 

2) UPDATE ✔️ 채널 개별 "수정" : PUT /channels/:id

- req : URL(id), body(name)

- res 200 : `채널명이 성공적으로 수정되었습니다. 기존 : ${} -> 수정 ${}`

 

3) DELETE ✔️ 채널 개별 "삭제" : DELETE /channels/:id

- req : URL(id)

- res 200 : `${name}이 정상적으로 삭제 되었습니다.`

 

4) SELECT ✔️ 회원의 채널 전체 "조회" : GET /channels

- req : body(userId)

- res 200 : 채널 전체 데이터 list, json array

 

5) SELECT ✔️ 채널 개별 "조회" : GET /channels/:id

- req : URL(id)
- res 200 : 채널 개별 데이터

 

1. 채널 개별 조회

router
    .route('/:id')
    .get((req, res)=>{
        let {id} = req.params;
        id = parseInt(id);

        let sql = `SELECT * FROM channels WHERE id = ?`
        conn.query(sql, id,
            function(err, results){
                if(results.length){
                    res.status(200).json(results)   
                }
                else {
                    notFoundChannel(res)
                }
            }
        )
    })

 

2. 채널 전체 조회

router
    .route('/')
    .get((req, res)=>{
            var {userId} = req.body

            let sql = `SELECT * FROM channels WHERE user_id = ?`
            var channels = []
            userId && conn.query(sql, userId,
                function(err, results){
                    if(results.length){
                        res.status(200).json(results)
                        res.end()
                    }
                    else {
                        notFoundChannel(res)
                    }
                }
            )
			
            res.status(404).end() //지금 코드에서는 이게 덮여써짐
    }) // 채널 전체 조회

논리연산자 단축평가 

A && B

- A가 false -> B 실행 X

- A가 true -> B 실행

 

userId && conn.query(sql. userId, ...

 

: 단축평가, userId 없는 경우에는 실행하지 않음

하지만!! 클린 코드의 목적은 가독성이기 때문에 쓰지 않는 것이 좋음

되도록 If else문을 쓰자!

router
    .route('/')
    .get((req, res)=>{
            var {userId} = req.body

            let sql = `SELECT * FROM channels WHERE user_id = ?`
            if(userId) {
                conn.query(sql, userId,
                    function(err, results){
                        if(results.length){
                            res.status(200).json(results)   
                        }
                        else {
                            notFoundChannel(res)
                        }
                    }
                )
            } 
            else {
                res.status(400).end()
            }
    }) // 채널 전체 조회

 

---

3. 개별 채널 등록

    .post((req, res)=>{
        const {name, userId} = req.body
        if(name && userId){
            let sql = `INSERT INTO channels (name,user_id) VALUES (?, ?)`
            let values = [name, userId]
            conn.query(sql,values,
                function(err, results){
                    res.status(201).json(results)
                }
            )   
        } else {
            res.status(404).json({
                message : "요청 값을 제대로 보내주세요."
            })
        }
    }) // 채널 개별 생성 = db에 저장

지금 코드에서는 name, userId만 있다면 무조건 실행되기때문에

name userId의 유효성 검사를 해야됨.

---

장정 4시간길이의 강의였습니다..

 

생각보다도 더 길게 느껴져서 쉽지않았는데

그래도 DB연결하니까 코드가 간결해지고, 정말 실무에서 쓸만한 코드라고 생각하니 오히려 재밌게 느껴지고 있는 것같습니다!!

 

얼른 프론트부분과 합치고 싶어요.... 욕심이지만은