[MySQL] 데이터베이스 | 관계형 데이터베이스 | MySQL Basic | MySQL과 Node.js 연결

데이터베이스

• 데이터를 저장하는 구조/자료의 모음
• 데이터의 집합소
• 데이터 베이스 이전에는?
  • 파일 시스템: DBMS 사용하기 전 데이터의 저장을 위해 이용했던 시스템
    ⇒ 단점: 1. 데이터 중복. 2. 데이터 불일치
    파일의 첫 시작부터 마지막까지 다 읽어야 원하는 값을 읽을 수 있음. 원하는 부분만 수정할 수 없음. 파일 전체를 다시 저장해야 함.

DBMS: Database management system

• 파일 시스템이 가진 문제를 해결하기 위해 만들어진 것
• 데이터베이스에 접근하고 이를 관리하기 위해 존재

관계형 데이터베이스

RDBMS: Relational DBMS: 테이블들간의 관계성이 있음 → mysql, oracle 등등

데이터 베이스 용어

• 열 Column, attribute, 속성
• 행 record, tuple, 튜플
• 테이블 table, relation
• key: 데이터베이스에서 튜플을 찾거나 순서대로 정렬할 때 구분하고 정렬의 기준이 되는 속성
• 기본키(PK, Primary Key): 메인키로 한 테이블에서 특정 튜플을 유일하게 구별할 수 있는 속성
  • Null(데이터 값이 존재하지 않음) 값 불가, 중복 값 불가
  • Primary key는 테이블당 오직 하나의 필드에만 설정 가능
• 외래키(FK, Foreign Key): 어떤 테이블의 기본키를 참조하는 속성
  • 속성 이름은 달라도 되는데, 그 안의 값은 동일해야 한다.

MySQL

• 가장 널리 사용되고 있는 RDBMS
• 오픈소스
• 윈도우, mac, 리눅스 등 다양한 운영체제에서 사용 가능
  https://dev.mysql.com/downloads/mysql/

MySQL Workbench

• GUI로 설치 시 함께 설치
• 데이터를 시각적으로 확인하기 편함.
• 콘솔(git bash, terminal 등)에서도 동일한 작업 가능
  https://dev.mysql.com/downloads/workbench

---

SQL 문

• Structured Query Language
• 구조적 질의 언어
• 데이터베이스를 제어하고 관리할 수 있는 목적의 프로그래밍 언어
  주의) 명령어 끝에는 문장이 끝났음을 알려주기 위해 세미콜론을 붙여야 한다.

DDL(Data Definition Language)

• 데이터베이스를 정의하는 언어
  • CREATE: 데이터베이스, 테이블 등을 생성
  • ALTER: 데이터를 수정하는 역할
  • DROP: 데이터베이스, 테이블을 삭제하는 역할
  • TRUNCATE: 테이블을 초기화 시키는 역할
• 데이터베이스 만들기 + 한글 인코딩

create database 이름 default character set utf8 default collate urt8_general_ci;

• 문자형 데이터 형식
  • CHAR(n)
  • VARCHAR(n): n→ 최대 글자수
  • TINYTEXT(n)
  • TEXT(n)
  • MEDIUMTEXT(n)
  • LONGTEXT(n)
• 숫자형 데이터 형식
  • INT(n): 정수형 데이터 타입
• 날짜형 데이터 형식
  • DATE: 년도, 월, 일
  • DATETIME: 날짜와 시간형태
  • TIMESTAMP: 날짜와 시간형태 - 시스템 변경 시 자동으로 그 날짜와 시간이 저장됨
• NOT NULL ⇒ 모든 값이 빈 값이 아니도록 설정

---

#데이터베이스 사용하기
USE 데이터베이스 이름;  
#테이블 목록 보기
SHOW TABLES;
#데이터베이스 목록 보기
SHOW DATABASES; 
#테이블 구조 보기
DESC 테이블이름
-------------------------------------------------
#테이블 생성하기
CREATE TABLE member (
	ID varchar(10) not null,
    name varchar(5) not null,
    birthday date
);
--------------------------------------------------
1. 컬럼 삭제
alter table 테이블명 drop column 컬럼명;
ALTER TABLE member DROP COLUMN birthday;
2. 컬럼 추가
alter table 테이블명 add column 컬럼명 타입;
ALTER TABLE member ADD COLUMN birthday date;
3. 컬럼 속성 변경
alter table 테이블명 modify 컬럼명 타입;
ALTER TABLE member MODIFY birthday date not null;
-----------------------------------------------------
DROP TABLE 테이블명;
//테이블 자체를 삭제하기. 테이블을 잘못 만들었거나 더이상 필요하지 않을 때
TRUNCATE TABLE 테이블명;
//테이블 초기화하기(테이블의 모든 행(row) 일괄 삭제). 테이블 안에 있는 데이터를 삭제.

---

DML(Data manipulation language)

• 데이터베이스 내부 데이터를 관리하기 위한 언어
  - SELECT: 데이터베이스에서 데이터를 검색(조회)
  - INSERT: 테이블에서 데이터를 추가
  - UPDATE: 테이블에서 데이터를 수정
  - DELETE: 테이블에서 데이터를 삭제

  SELECT

  SELECT * FROM 테이블명; // * => 모든 필드
  	SELECT ID FROM 테이블명; //ID만 가져오고 싶을 때
  SELECT * FROM 테이블명 WHERE 필드1 = 조건값1 ;
  	SELECT * FROM user WHERE name="andre";
  ORDER BY ID ASC 오름차순 / DES 내림차순 
  SELECT * FROM 테이블명 WHERE 필드1 = 조건값1 ORDER BY 필드1 ASC ;
  SELECT * FROM user ORDER BY ID ASC;
  SELECT * FROM user ORDER BY ID DESC;
  LIMIT 개수
  SELECT * FROM 테이블명 WHERE 필드1 = 조건값1 ORDER BY 필드1 ASC LIMIT 개수 ;
  SELECT * FROM user WHERE name = "andy" ORDER BY ID DESC LIMIT 2 ;
  // limit만 써도 됨. 전체 조회 순서는 insert 된 순서. 

---

INSERT

INSERT INTO 테이블명 (필드1, 필드2, 필드3 ) VALUES (값1, 값2, 값3);
INSERT INTO 테이블명 VALUES (값1, 값2, 값3);
//필드를 명시하지 않을 때는 테이블의 모든 컬럼에 값을 추가할 때만 사용할 수 있다. 
INSERT INTO user (ID, name, age) VALUES("a", "andrew", 24);
INSERT INTO user (ID, name, age) VALUES("b", "andre", 20);
INSERT INTO user values("c","andy", 19);

---

UPDATE

UPDATE 테이블명 SET 필드1 = 값1 WHERE 필드2=값2;
UPDATE user SET age = 20 WHERE ID ='a';

DELETE: 테이블 자체 삭제

TRUCANATE: 테이블 초기화

DELETE FORM 테이블명 WHERE 필드1=값1;
TRUNCATE TABLE 테이블명;

---

비교 연산자 : = , >, >=, <, <=

부정 연산자: 같지 않다

!=, ^, <> , NOT 컬럼명 =

SELECT * FROM user WHERE age  ^ 80;
SELECT * FROM user WHERE NOT age = 80;

SQL 연산자

• BETWEEN a AND b: a와 b의 값 사이에 있으면 참(a와 b의 값도 포함)
• IN (list) : 리스트에 있는 값 중에서 어느 하나라도 일치하면 참
• LIKE ‘비교 문자열’: 비교문자열과 형태가 일치하면 사용
  %: 0개 이상의 어떤 문자
  _: 1개 이상의 단일 문자. 뒤에 한글자가 와도 되는데 무엇이든 상관없음.
• IS NULL: NULL값인 경우 true, 아니면 false

  SELECT * FROM user WHERE age between 18 and 24 ;
  SELECT * FROM user WHERE name IN ('andy', 'andre');
  SELECT * FROM user WHERE name LIKE '%and%'; //and가 포함된 모든 글자 조회
  SELECT * FROM user WHERE name LIKE '%y';//마지막 글자에 y가 포함된 경우
  SELECT * FROM user WHERE name LIKE 'a_';
  SELECT * FROM user WHERE name is null;
  SELECT * FROM user WHERE not name is null; // name이 비어있지 않은 것 조회

논리연산자

• AND: 앞에 있는 조건과 뒤에 있는 조건이 참이면 결과도 참
• OR: 앞에 있는 조건과 뒤에 오는 조건 중 하나라도 참이면 결과는 참
• NOT: 뒤에 오는 조건과 반대되는 결과를 돌려준다.

---

CRUD

• 대부분의 컴퓨터 소프트웨어가 가지는 기본적인 처리 기능
  • CREATE(생성)
  • READ(읽기)
  • UPDATE(갱신)
  • DELETE(삭제)

---

mySQL과 node.js 연결하기

기본적으로 root로는 mysql에 접근하지 못하도록 막아놓았기 때문에 새로운 사용자를 만들어야 한다.

mysql 사용자 추가하기

CREATE USER 'user이름'@'%' IDENTIFIED BY '비밀번호';

DB 권한 부여 (모든 DB에 접근 가능하도록)

GRANT ALL PRIVILEGES ON . TO 'user'@'%' WITH GRANT OPTION;

현재 사용중인 MySQL의 캐시를 지우고 새로운 설정을 적용하기 위해 사용

FLUSH PRIVILEGES;select host, user, plugin, authentication_string from mysql.user;

mysql 계정 비밀번호를 바꾸고 싶을 때

ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY '비밀번호

---

const express = require("express");
const mysql = require("mysql");
const app = express();
const port = 8000;

const cnn = mysql.createConnection({
  host: "localhost",
  user: "사용자 이름", 
  password: "설정한 비밀번호",
  database: "데이터베이스 이름",
});


app.get("/", (req, res) => {
  cnn.query("select * from user", (err, result) => {
    if (err) throw err;

    console.log(result);
    res.render("index", { rows: result });
  });
});

app.listen(port, () => {
  console.log("Server is open: ", port);
});

rows를 데이터로 넘겨주기 때문에 index.ejs파일에서도 ejs문법을 통해 활용이 가능하다.

   <% for(let i = 0; i <rows.length; i++) { %>
        <div>
            <span><%= rows[i].id %></span>
            <span><%= rows[i].name %></span>
        </div>
    <% } %>

(참고하면 좋은 사이트)
https://www.mysqltutorial.org/mysql-nodejs/connect/