서민주[광주 2반 C209] 팀원

240416 알아 두면 쓸모 있는 데이터베이스 객체 1 (강시몬 컨설턴트)

• Relation DBMS
  • MySQL, MariaDB, Oracle(유료) PostgreSQL, Microsoft SQL Server(유료)
  • 최근에는 HeidiSQL이나 DBeaver 같은 프로그램을 많이 씀.
    • 다른 DBMS에서 쓰던 설정 그대로 가져다 쓸 수 있음
• 실습 시나리오
  • 현 상태: MySQL 기본 설치, root 사용자로 외부에서 접근 가능
  • 회원 정보는 한글사용자, 영문사용자로 나눠서 관리
  • 회원정보 변경시 LOG 테이블에 변경 내역 저장
  • 회원정보 조회시 회원이 작성한 글 수를 함께 보여줘야 함
  • 작성한 글 조회시 항상 작성자 아이디, 이름 함께 보여줘야 함
• Schema? Database?
  • 인스턴스: DBMS가 동작할 때의 단위, OS 입장에서는 ‘프로세스’
  • Database: 물리적으로 구분된 데이터베이스(스키마의 집합)
    • 다른 데이터베이스에 접근할 수 없음
  • Schema: 데이터베이스의 구조와 제약 조건을 정의한 것
    • 접근 제한 등 권한 관리가 가능
    • MySQL은 인스턴스 - 스키마 - 테이블 순으로, 오라클은 인스턴스 - 데이터베이스 - 스키마 - 테이블 순으로 구조화되어있음
• 데이터베이스 생성(MySQL)
  • CREATE SCHEMA SSAFY DEFAULT CHARACTER SET UTF8; 혹은 CREATE DATABASE FREE DEFAULT CHARACTER SET UTF8;
• 테이블
  • 데이터를 담고 있는 객체
  • 행(Row)과 열(Column)로 구성된 2차원 행태(표)의 객체
  • DBMS의 모든 데이터는 테이블로 관리
  • 'USER_EMAIL' VARCHAR(200) DEFAULT NULL COMMENT '사용자 이메일' 처럼 COMMENT를 꼼꼼하게 달라고 추천함.
    • erd를 굳이 열어보지 않게끔 할 수 있어서 추천 :slightly_smiling_face:
  • 비밀번호는 VARCHAR(64)로 설정했는데 해싱의 편의를 위함.
• Constraint
  • 제약조건
  • 데이터의 무결성을 지키기 위해 제한된 조건(성능과는 상관이 없음)
  • NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT 등
  • CREATE TABLE 테이블이름 ( 필드이름 필드타입, ..., [CONSTRAINT 제약조건이름] PRIMARY KEY ... ) 이런 식으로 작업하는데 대괄호는 생략 가능
• Sequence
  • 시퀀스는 자동 순번을 반환하는 데이터베이스 객체
  • MySQL에서는 지원하지 않음(Oracle, Postgresql, MS-SQL 등에서 지원)
  • SEQ_USER.nextval, SEQ_USER.currval, SEQ_USER.setval 등으로 사용
  • INCREMENT BY 증감숫자, START WITH 시작숫자, NOMINVALUE | MINVALUE ⇒ 최소값, NOMAXVALUE | MAXVALUE ⇒ 최대값, NOCYCLE | CYCLE, NOCACHE | CACHE
• 함수(Function)

  • 우리가 알고 있는 바로 그 함수

  • Return 값 필수(void가 없다!)

  • Return 값은 하나

  • CREATE FUNCTION '함수명' (파라미터) RETURNS 반환할 데이터타입 BEGIN 수행할 쿼리 RETURN 반환할 값 END

  • DELIMITER

    • 구분자
    • 정상적인 로직들을 만들기 위해 필요함.
    • Delimiter 명령어 뒤에 구문 문자로 사용하고자 하는 문자를 넣어주면 된다.

  • 기본 함수

    • sha2, min, max 등

    240417 알아 두면 쓸모 있는 데이터베이스 객체 2(오형남 컨설턴트)

    View

    • 하나 이상의 데이터로부터 쿼리한 데이터의 집합
    • 접근이 허용된 자료를 제한적으로 보여주기 위해 사용
    • 사용자 편의: 자주 사용하는 복잡한 SELECT Query문을 미리 제작(재사용)
    • 기존 테이블 구조를 건드리지 않고 필요에 맞는 모습으로 데이터 제공
    • CREATE VIEW '뷰이름' AS SELECT 'Query문';

    (Stored) Procedure

    • 하나의 요청으로 여러 SQL문을 실행(가능)
    • 네트워크 소요시간을 줄일 수 있음
    • 리턴값 없을 수도 있거나 여러 개 반환 가능
    • CREATE PROCEDURE '프로시저명' ( 파라미터 ) BEGIN 수행할 쿼리 END

    Procdeure와 Function의 차이

    	프로시저	함수
    구현목적	특정 작업을 수행	특정 계산을 수행
    반환값	반드시 필요 없고, 여러 값 반환 가능	반드시 한개 존재
    SELECT, WHERE문에서 실행 여부	사용 불가	사용 가능
    처리장소	서버에서 실행(빠른 속도)	클라이언트(화면)에서 실행(느린 속도)

    Trigger

    • 어떤 상황이 발생했을 때 미리 정해진 Action이 자동으로 실행
    • 특정 테이블에 INSERT, UPDATE, DELETE문이 수행될 때 자동으로 동작
    • 변경 전 또는 변경 후의 행을 OLD, NEW라는 가상 변수를 사용하여 읽음
    • CREATE TRIGGER '트리거명' AFTER UPDATE ON '테이블명' FOR EACH ROW BEGIN (수행쿼리) END

    USER / 권한

    • MySQL의 사용자
    • grant 명령어를 통해 권한 부여
    • revoke 명령어를 사용해 권한을 제거
    • FLUSH PRIVILEGES;를 통해 권한 반영
    • CREATE USER {username}@{ip} identified by 'password';
    • DROP USER {username}@{ip};
    • grant {권한} privileges on {스키마}.{테이블} to {username}@{ip};
    • revoke {권한} privileges on {스키마}.{테이블} from {username]@{ip};

    분기문

    • 필요에 따라 일반적인 쿼리문 내에서 분기문을 사용할 수 있음
    • SELECT절 뿐만 아니라 WHERE절에서도 분기문 사용 가능
    • CASE (A) WHEN 조건 1 THEN 결과 1 WHEN 조건 2 THEN 결과 2 WHEN 조건 3 THEN 결과 3 ELSE 그외 결과 END;
    • WHERE 조건문 사용 예시
      • WHERE CASE WHEN ACTION = “U” THEN USER_SEQ ≤ 1250 WHERE ACTION = “D” THEN (USER_SEQ > 1320 AND USER_SEQ < 15000) END;
    • NULL 관련 분기문들

    CURSOR

    • 테이블에서 한 행씩 처리하기 위한 방식
    • CURSOR, LOOP, FETCH 등의 구문을 사용
    • 성능상 좋지 못해 권장하지 않으며, 실시간 처리는 매우 위험 :slightly_frowning_face:
    • 예시

    Event

    • MySQL의 Scheduler 기능
    • 반복 일정에 따라 쿼리 실행
    • CREATE EVENT 이벤트 이름 ON SCHEDULE 수행, 반복 할 시간 [STARTS 시간 ] [ENDS 시간] [ON COMPLETION [NOT] PRESERVE] [ENABLE] [COMMENT 코멘트] DO 수행할 명령 END
    • ON SCHEDULE EVERY 반복주기: MONTH, WEEK, DAY, HOUR 등 사용 가능
    • STARTS ‘시간’ : 해당 시간부터 시작, 시작시간 설정하지 않은 경우 이벤트 생성 후 다음 주기부터 실행
    • ON COMPLETION [NOT] PRESERVE
    • ENABLE/DISABLE/DISABLE ON SLAVE

    db-engines.com