MySQL 기초(2) - RDBMS 및 SQL 정리

1) MySQL 기초

   1-1) 데이터베이스 / DBMS

   1-2) RDBMS

   1-3) SQL

   1-4) 코드 실습 - list 구현하기

 

 

 

 

1) MySQL 기초

1-1) 데이터베이스 / DBMS

데이터베이스(Database) : 단순하게 데이터들을 저장하는 공간

DBMS(DataBase Management System)

DBMS 정의 : 다수의 사용자들이 데이터베이스 내의 데이터를 접근할 수 있도록 해주는 소프트웨어 도구의 집합
DBMS는 사용자 또는 다른 프로그램의 요구를 처리하고 적절히 응답하여 데이터를 사용할 수 있도록 해준다!

SQL(Structured Query Language)

SQL 정의 : 데이터들을 저장하는 기능, 권한을 부여하는 기능 등 조작을 할 수 있도록 하는 명령어들 혹은 언어를 의미함
DBMS에서 구현된 기능을 실행시키기 위해 특정한 언어가 바로 "SQL"이다!!
데이터를 보관 혹은 저장하거나 삭제하거나, 수정할 시 사용한다!

NoSQL(SQL 문법을 사용하지 않음) vs SQL
SQL 문법으로 DBMS가 동작하는 것을 "RDBMS(관계형 데이터베이스)"라 한다!

 

 

1-2) RDBMS(Relational DataBase Management System)
- 관계형 데이터베이스의 대표적인 플랫폼(프로그램)

• Oracle : 유료이기에 일반적으로 대기업에서만 사용함!(엄청난 퍼포먼스를 요구하는 경우 혹은 기업에서만 사용함)
• MySQL : Oracle에서 MySQL을 인수했지만 인수 후 더 이상의 프로그램 개발이 이루어지지 않아 MySQL 관계자들이 Oracle에서 나와서 만든 것이 바로 "MariaDB"이다!
• MariaDB : MySQL과 호환이 된다!(즉, MySQL을 사용할 줄 알면 MariaDB를 쓸 줄 안다는 뜻이다!)
• PostgreSQL : 무료이며, 퍼포먼스가 좋다!
• MSSQL : 마이크로소프트(MS) 사가 만든 DBMS이다!

- 비관계형 데이터베이스의 대표적인 플랫폼(프로그램)

MongoDB : 데이터를 저장함에 있어 혹은 사용함에 있어 SQL 구문을 쓰지 않고 사용한다!(비관계형 데이터베이스)

 

 

1-3) SQL

SQL 개요

$ mysql -uroot -p
$ # 비밀번호 입력
$ mysql >  # <-- 이는 MySQL 프로세스에 접속했다는 의미이므로 여기서부터는 SQL 문법을 사용함!!

 

SQL 분류

• 데이터 정의어(DDL)
• 데이터 조작어(DML)
• 데이터 제어어(DCL)

 

(1) 데이터 정의어(DDL)

• CREATE
• SHOW
• DROP
• ALTER

데이터베이스(DB)의 경우, 모든 데이터는 텍스트로 저장되는데 이때 저장되는 형태는 테이블이다!

$ > SHOW DATABASES;

$ > CREATE DATABASE sangbeom;

$ > DROP DATABASE sangbeom;

$ > CREATE DATABASE sangbeom DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

$ > USE sangbeom;

$ > SHOW tables;

 

 

 

 

 

 

 

 

$ > CREATE TABLE store(
    category VARCHAR(50),
    store_name VARCHAR(50),
    menu_name VARCHAR(50),
    price VARCHAR(50),
    tel VARCHAR(50),
    address VARCHAR(50)
);

$ > ALTER TABLE store RENAME TO store2;

$ > SHOW TABLES;

$ > ALTER TABLE store2 RENAME TO store;

$ > DESC store;

 

 

 

 

 

Field Type : 숫자형, 문자형, 날짜형, 이진 데이터 타입
(1) 숫자형은 INT(4byte로 -21억 ~ 21억 까지 표현 가능!!)를 많이 사용함!!
(2) 문자형 : CHAR, VARCHAR, TEXT

• CHAR : 255 byte 저장가능, 고정 데이터일 때 사용함
• VARCHAR : 255 byte 저장가능, 가변 데이터일 때 사용함
• TEXT : 65535 byte 저장가능

(3) 날짜형

• DATE : 년, 월, 일 저장
• TIME : 시간 저장
• DATETIME : 년, 월, 일, 시간 저장(yyyy-mm-dd hh:MM:ss) --> 8byte
• TIMESTAMP : 년, 월, 일, 시간(integer) --> 4byte
• YEAR : 연도

(4) 이진 데이터 타입

• BLOB : 이미지

참고로 데이터베이스의 경우, 이미지를 저장하는 경우는 거의 없다!!

 

Key : 중복값을 넣지 않을 때 사용함!

• PRIMARY KEY : 중복값 입력 불가(null 불가), 테이블 당 하나만 넣을 수 있음!
• UNIQUE : 중복값 입력 불가(null 가능), 여러 개 넣을 수 있음!
• +) 자동생성 --> AUTO_INCREMENT : 숫자를 자동으로 하나씩 증가시킴(1씩 자동생성)!

 

$ > CREATE TABLE user(
    user_id VARCHAR(50) PRIMARY KEY,
    user_pw VARCHAR(50) NOT NULL,
    user_name VARCHAR(50) NOT NULL,
    gender CHAR(4) DEFAULT '남자',
    register_date DATETIME DEFAULT now()
);

 

 

(2) 데이터 조작어(DML)

• SELECT
• INSERT
• UPDATE
• DELETE

문법 : INSERT INTO [테이블명](필드1, 필드2, ...) VALUES();

$ > INSERT INTO user(user_id, user_pw, user_name, gender) VALUES('web7722', '1234', 'sangbeom', '남자');

$ > SELECT user_id, user_pw, user_name, gender FROM user;

$ > DESC user;

$ > SELECT user_id, user_pw, user_name, gender, register_date FROM user;

$ > INSERT INTO user(user_id, user_name, gender) VALUES('web7722', 'sangbeom', '남자');   # --> 에러 발생함!

$ > SELECT * FROM user;

$ > INSERT INTO user(user_id, user_pw, user_name, gender) VALUES('admin', '1234', '관리자', '남자');

$ > SELECT * FROM user;

 

 

 

 

 

 

 

WHERE 절(조건절)

$ > SELECT * FROM user WHERE user_id = 'admin';

 

 

UPDATE

$ > UPDATE user SET gender = '여자' WHERE user_id = 'admin';

$ > SELECT * FROM user WHERE user_id = 'admin';

$ > UPDATE user SET user_pw = '0000', user_name = '슈퍼 관리자', gender = '남자' WHERE user_id = 'admin';

 

 

 

 

DELETE

$ > DELETE FROM user WHERE user_id = 'admin';
$ > SELECT * FROM user;

 

 

 

 

연습 - 게시판 테이블 만들기

$ > CREATE TABLE board(
    idx INT(11) AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(150) NOT NULL,
    content TEXT NULL,
    writer VARCHAR(50) NOT NULL,
    register_date DATETIME NOT NULL DEFAULT now(),
    hit INT(11) DEFAULT 0
);

 

row 5개 만들기

$ > INSERT INTO board(subject, content, writer) VALUES('게시글 1', '내용 1 ~~~', '황상범');
$ > INSERT INTO board(subject, content, writer) VALUES('게시글 2', '내용 2 !!!', '이세욱');
$ > INSERT INTO board(subject, content, writer) VALUES('게시글 3', '내용 3 ###', '박종환');
$ > INSERT INTO board(subject, content, writer) VALUES('게시글 4', '내용 4 &&&', '김주형');
$ > INSERT INTO board(subject, content, writer) VALUES('게시글 5', '내용 5 ---', '이민수');

$ > SELECT * FROM board;

 

 

$ mysql > exit
$ mysqldump -uroot -p sangbeom[데이터베이스 명] > backup.sql
$ 비밀번호 입력
$ # --> 그럼 지정한 경로에 "backup.sql" 파일이 생성됨!!

 

 

 

 

 

 

 

 

(3) 데이터 제어어(DCL)

• GRANT
• REVOKE

 

 

 

1-4) 코드 실습 - list 구현하기

$ npm init -y

$ npm install express nunjucks mysql2

※ pool : 연결을 해놓은 객체를 모아둔 공간

 

 

구현할 directory 구조

• repository
• services
• controllers
• routes
• public
• views
• server.js

 

server.js

const express = require("express");
const nunjucks = require("nunjucks");
const app = express();
const router = require("./routes");

app.set("view engine", "html");
nunjucks.configure("views", {
  express: app,
});

app.use(express.static("public"));
app.use(express.urlencoded({ extended: false }));
app.use(router);

app.listen(3000, () => {
  console.log("server start");
});

 

 

views/board

list.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Document</title>
  </head>
  <body>
    <table>
      <tr>
        <th>번호</th>
        <th>제목</th>
        <th>작성자</th>
        <th>작성일</th>
        <th>조회수</th>
      </tr>
      {% for item in list %}
      <tr>
        <td>{{item.idx}}</td>
        <td>{{item.subject}}</td>
        <td>{{item.writer}}</td>
        <td>{{item.register_date}}</td>
        <td>{{item.hit}}</td>
      </tr>
      {% endfor %}
    </table>
  </body>
</html>

 

 

public/css

list.css

* {
  margin: 0;
  padding: 0;
}

body {
  background: #beb;
}

 

public/js

list.js

alert("Welcome to my board list~~!!");

 

 

routes

index.js

const express = require("express");
const router = express.Router();
const board = require("./board.route");

router.use("/board", board);

module.exports = router;

 

board.route.js

const express = require("express");
const router = express.Router();
const controller = require("../controllers/board.controller");

router.get("/list", controller.list);
// router.get("/list", (req, res) => {
//   res.send("router 확인");
// });

module.exports = router;

 

 

controllers

board.controller.js

const service = require("../services/board.service");

exports.list = async (req, res) => {
  const list = await service.getList(); // [{}, {}, {}]
  res.render("board/list.html", { list });
};

 

 

services

board.service.js

const board = require("../repository/board.repositoty");

exports.getList = async () => {
  const result = await board.findAll(); // [{}, {}, {}]
  return result;
};

 

 

repository

board.repository.js

const pool = require("./db");

exports.findAll = async () => {
  const [result] = await pool.query("SELECT * FROM board"); // [[], []]
  // const [result] = await pool.query("SELECT * FROM board order by idx desc"); // <-- idx 기준으로 내림차순 정렬하는 조건이 적용된 코드
  return result;
};

 

db.js

const mysql = require("mysql2");

const pool = mysql
  .createPool({
    host: "127.0.0.1",
    port: "3306",
    user: "root",
    password: "[본인 mysql 비밀번호]",
    database: "sangbeom",
    connectionLimit: 5,
  })
  .promise();

module.exports = pool;