[혼공JS] SQL 간단 예제 및 CRUD 예제

백엔드 프로젝트를 위해 mysql을 공부한 TIL

 

데이터베이스

데이터베이스는 엑셀과 같이 데이터를 저장하고 관리하기 쉽고 사용하기 쉽게 만들기 위해 만들어진 시스템이다.

엑셀보다 좋은 이유 - 작은 용량의 데이터라면 엑셀도 가능하지만 수십 수만 TB의 데이터들을 입출력하기 위해선 데이터 베이스가 필요하다.

 

기본 사용 예제

테이블 생성 명령어 Create Table [테이블 이름] ( 열 - 엑셀 시트

테이블 생성

CREATE TABLE Users (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    id VARCHAR(100) UNIQUE NOT NULL,
    pass VARCHAR(15)
);

이 쿼리는 "ID", "이름", "이메일" 및 "패스워드" 열이 있는 "사용자"라는 테이블을 만듭니다. "ID" 열은 기본 키이며 각각의 새 행에 대해 자동 증가시킵니다.

기본키란? 테이블에서 유일성과 최소성을 가지고 데이터를 구분할 수 있게 만들어주는 반드시 필요한 키

데이터 삽입

INSERT INTO Users (Name, id, pass) VALUES ('John Doe', 'john.doe@example.com', '1234');

이 쿼리는 "이름", "id" 및 "pass"에 대해 지정된 값을 사용하여 "사용자" 테이블에 새 행을 삽입합니다.

회원가입 구현시 삽입하여 데이터를 저장함

데이터 검색

SELECT * FROM Users WHERE id=? AND pass=? ;

이 쿼리는 "Users" 테이블에서 모든 열(*)을 검색하여 id와 pass 가 같은 열을 조회합니다.
로그인 구현시 Users 테이블에서 id와 pass가 같은 사람을 조회하여 있다면 로그인 성공, 없다면 로그인 실패 

 

데이터 수정

UPDATE Users SET pass='123456' WHERE id='john.doe@example.com';

이 쿼리는 "id" 열이 'john.doe@example.com'과 일치하는 "Users" 테이블의 행에 대한 "pass" 열을 업데이트합니다.
회원 정보 수정시 id가 같은 사람을 조회한 후 그 위치(행)의 데이터를 교체한다.

데이터 삭제

DELETE FROM Users WHERE Email='john.doe@example.com';

회원 탈퇴시 "id" 열이 'john.doe@example.com'과 일치하는 "Users" 테이블의 행을 삭제한다.

 

---

express 연동 및 로그인 구현

1. mysql 모듈 설치

$pnpm i mysql

2. mysql 테스트 데이터 생성

#데이터 베이스 my_db 생성
CREATE DATABASE IF NOT EXISTS my_db;

#my_db 사용
USE my_db;

#Users 테이블 생성 -- | id | password |
CREATE TABLE IF NOT EXISTS Users (
  id VARCHAR(45) NOT NULL,
  password VARCHAR(45) NOT NULL,
  PRIMARY KEY (id));

#Users 데이터 삽입
INSERT INTO Users (id, password) VALUES ('냐옹', '1234');

#유저 테이블로 부터 id가 냐옹인 사람을 찾아서 password를 꺼내옴
SELECT password FROM Users WHERE id='냐옹';

3.express-mysql 연동

const mysql = require("mysql");
const connection = mysql.createConnection({
  host: "localhost",
  user: "계정 정보",
  password: "패스 워드",
  database: "my_db",
});

connection.connect();

connection.query("SELECT * from Users", (error, rows, fields) => {
	if (error) throw error;
	console.log("User info is: ", rows);
});

module.exports = router;

 

콘솔창에 mysql 데이터가 나온다면 성공!

4. 라우터로 연결해보기

router.get("/", function (req, res, next) {
  connection.query("SELECT * from Users", (error, rows, fields) => {
    if (error) throw error;
    res.send(rows);
  });
});

 

SQL 연동 및 데이터 입출력 하기 완료

 

---

트러블 슈팅

1.SQL 

code: 'ER_NOT_SUPPORTED_AUTH_MODE',
errno: 1251,
sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client',

비밀번호 플러그인에 문제가 있어서 동작하지 않을 수도 있다.

SELECT Host,User,plugin,authentication_string FROM mysql.user;

보다싶이 root이용자의 경우 auth_socket으로 동작하여 비밀번호가 먹히지 않았던것

따라서 sql 플러그인을 예전 네이티브 플러그인으로 변경하고 시도해봅시다.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '< MySQL password >';

이후 다시 재시작하면 잘 동작하는것을 볼 수 있습니다.

 

단, 공부할때만 이런식으로 다운그레이드해서 사용하고 실무환경이나 보안이 중요하다고 생각된다면 다운그레이드가 아닌 caching_sha2_password를 사용하는 mysql2모듈을 사용하자.

(공부하면서 저도 바꿧습니다.)

 

2.PROTOCOL_ENQUEUE_HANDSHAKE_TWICE'

단순히 핸드쉐이크 두번 - 즉 mysql 커넥션이 두번 이루어져서 발생하는 오류이며

이는 상단의 createConnection 과 connection.connect()이 중복 되면서 생기는 오류로 connection.connect()를 지우면 정상 작동한다.