Today?

Node.js 숙련주차 1주차

SQL(Structured Query Language)

• 데이터베이스에서 사용되는 생성, 삽입, 조회
  💡 SQL의 종류

• DDL (Data Definition Language)
  • CREATE, DROP, ALTER
• DML (Data Manipulation Language)
  • SELECT, INSERT, DELETE, UPDATE
• DCL (Data Control Language)
  • GRANT(권한 부여), REVOKE(권한 취소)

GRANT [권한] ON 객체명 TO 사용자;
REVOKE [권한] ON 객체명 FROM 사용자;

• TCL (Transaction Control Language)
  • COMMIT, ROLLBACK

SQL 제약조건(Constraint)

정의

각 컬럼들간의 제한사항을 관리하고, 조건을 위반하는 데이터를 방지하여 데이터베이스의 무결성(Integrity)을 보장하는 규칙

종류

• 고유(Unique) 제약 조건

  • 테이블에 소속된 특정 컬럼이 중복된 키를 가질 수 없는 조건

• NULL 제약 조건

  • 특정 컬럼이 아무런 값을 입력받지 않도록 설정하거나, 무조건 값을 입력 받도록 설정하는 조건

• 기본 키 (Primary Key) 제약 조건

  • 테이블 내에서 각 행(row)을 고유하게 식별할 수 있도록 보장하는 조건

• 외래 키 (Foreign Key) 제약 조건

  • 테이블 간의 연관 관계(Relation Ship)를 설정하는 조건

    • 1:1 - 1명의 사용자(User)는 1개의 사용자 정보(UserInfo)를 가질 수 있다.
    • 1:N - 1명의 사용자(User)는 여러개의 주문(Order)을 할 수 있다.
    • N:M - 여러명의 학생(Student)은 여러개의 학원(School)을 등록할 수 있다.

    CREATE TABLE 테이블명
      FOREIGN KEY (컬럼명) REFERENCES 참조_테이블명 (참조_컬럼명)
        ON DELETE [연계 참조 제약 조건]
        ON UPDATE [연계 참조 제약 조건]
    );
    
    CREATE TABLE Garden
    (
        gardenId INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name     VARCHAR(255) NOT NULL,
        address  VARCHAR(255) NOT NULL
    );
    
    CREATE TABLE GardenPlants
    (
        gardenPlantsId INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
        gardenId       INT(11)      NOT NULL,
        name           VARCHAR(255) NOT NULL,
        FOREIGN KEY (gardenId) REFERENCES Garden (gardenId)
    );

    

외래 키(Foreign Key) 제약 조건 심화
외래 키의 경우 다른 테이블과 관계를 맺고 있는 참조 데이터가 삭제(DELETE)또는 수정(UPDATE)될 때 어떤 행위를 해야하는지 설정할 수 있다. 이런 행위를 수행하는 조건을 연계 참조 무결성 제약 조건이라고 정의함

• CASCADE
  • 참조하고 있는 개체가 변경/삭제 될 경우 함께 변경/삭제
  • ex) 사용자가 삭제된다면, 그 사용자의 모든 주문 내역도 삭제

  FOREIGN KEY (userId) REFERENCES Users(userId)
      ON DELETE CASCADE
      ON UPDATE CASCADE;

• NO ACTION
  • 참조하고 있는 개체가 변경/삭제 될 경우 아무런 행위를 하지 않고 에러가 발생
  • ex) 주문 내역이 존재하는 사용자 정보 삭제 막기

  FOREIGN KEY (userId) REFERENCES Users(userId)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION;

• SET NULL
  • 참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 NULL로 변경
• SET DEFAULT
  • 참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 기본 값으로 변경

docker / dbeaver 세팅

아래의 블로그를 참고하여 docker를 설치하고 컨테이너를 만들었다.
https://maong.tistory.com/199
https://robomoan.medium.com/mysql-%EB%8F%84%EC%BB%A4-%EC%BB%A8%ED%85%8C%EC%9D%B4%EB%84%88-%EC%84%A4%EC%B9%98-%ED%9B%84-dbeaver-%EC%97%B0%EA%B2%B0%ED%95%98%EA%B8%B0-cf945454cf1e

// 환경 변수로 비밀번호와 데이터베이스, 컨테이너명을 설정할 수 있당
MYSQL_ROOT_PASSWORD="비밀번호"
MYSQL_DATABASE="mysql_prac"
MYSQL_CONTAINER_NAME="mysql_prac"

// docker container 생성중...
> run \
> --detach \
> --env MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
> --env MYSQL_DATABASE=${MYSQL_DATABASE} \
> --name ${MYSQL_CONTAINER_NAME} \
> --publish 3306:3306 \
> mysql

// 기타 명령어
# 현재 실행 중인 컨테이너 보기
docker ps

# 종료된 컨테이터도 함께 보기
docker ps -a

# 컨테이너 종료하기
docker stop {컨테이너 ID 또는 이름}

# 컨테이너 (재)시작하기
docker start {컨테이너 ID 또는 이름}

# 컨테이너 삭제
docker rm {컨테이너 ID}

# 컨테이너에 접속하기
docker exec -it mysql_prac bash

# MySQL 관리자로 접속하기
mysql -u root -p
# 패스워드 입력

MySQL 서버 콘솔에 접속하면 DBeaver에 접속할 사용자 계정, 사용자가 사용할 새 데이터베이스를 생성하고 사용자 계정에 데이터베이스 권한을 부여한다.

-- 데이터베이스 만들기
CREATE DATABASE {데이터베이스 이름} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 사용자 계정 만들기
CREATE USER {사용계 계정 이름} IDENTIFIED BY {사용자 계정 비밀번호};

-- 사용자 계정에 데이터베이스 권한 부여하기
GRANT ALL PRIVILEGES ON {데이터베이스 이름}.* TO {사용자 계정 이름};

-- Example
CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'test_user' IDENTIFIED BY 'userpwexample';
GRANT ALL PRIVILEGES ON test_db.* TO 'test_user';

-- 데이터베이스 목록 조회
SHOW DATABASES;

-- 사용자 목록 조회
SELECT User, Host, authentication_string FROM mysql.user;

---

오늘 배운 것

회고

사실 MySql을 사용한 적은 있지만 한 번도 이론에 대해 제대로 공부해본 적이 없어서 몹시 유익한 시간이었다... 관념적으로만 알고 있었던 용어에 대해 정확한 정의를 배울 수 있어서 좋았다. 특히 외래키는 써 본 적이 없어서 더 재밌었음 join을 쓰지 않아도 아예 테이블 설계 때부터 이렇게 정의해 사용할 수 있구나..
아직 어렵긴 한데 어떤 서비스의 테이블들을 어떻게 설계해야 할지 생각하는 과정이 좀 재미있는 듯...

---