BEYOND SW 캠프 21기 3회차 회고

DML에서 정보를 UPDATE 하는 것

DML은 업무의 흐름(Business Logic)에 따라 사용 순서나 방법등이 달라진 다는 것을 배웠다.

DML을 완료 후 Database에 전달하는 것이 commit 이고

DML 작성 후 전달하지 않고 되돌리는 것이 Rollback이다.

→ AUTOCOMMIT을 아래의 명령어로 키고 끌 수 있었다. 설정으로 들어가면 복잡한데, 코드는 간단했다.

-- AUTOCOMMIT 활성화
SET autocommit = 1;
SET autocommit = ON;

-- AUTOCOMMIT 비활성화
SET autocommit = 0;
SET autocommit = OFF;

• Commit과 Rollback의 역할은 다음 그림과 같이 설명될 수 있다.
  

  빨간 선이 데이터의 흐름이고, 특정 시점Transaction에서 DML 명령어 기입 후, 어떤 상황에 의해 Rollback하게 된다면, 해당 시점으로 복귀하게 된다.

  Commit을 하게 되면 실제 물리 데이터베이스에 영향을 미치게 된다.
  

Rollback, Rollback To

데이터베이스에 Commit을 바로 하지 않고 Transaction을 걸어주어 Rollback을 가능하게 해주는 명령어를 배웠다.
트랜잭션을 수동적으로 걸어주어 특정 구간 동안 ROLLBACK이 가능하도록 해준다.

START TRANSACTION;

  START TRANSACTION;

INSERT INTO tbl_menu
VALUES (null, '딸기맛감자탕', 9500, 4, 'Y');

INSERT INTO tbl_menu(menu_code, menu_name, menu_price, category_code, orderable_status)
VALUES (null, '생딸기한우국밥', 15000, 4, 'Y');

INSERT INTO tbl_menu(menu_name, menu_price, category_code, orderable_status)
VALUES ('민트초코맛파전', 10000, 4, 'Y');

ROLLBACK;

SAVEPOINT "특정 시점 명칭";

START TRANSACTION;

데이터 UPDATE ...

SAVEPOINT strawberry;

DELETE FROM tbl_menu WHERE menu_name = '민트초코맛파전';

ROLLBACK TO strawberry;

DATATYPE BLOB

문자 데이터 형식 중 BLOB 에 대한 부분이 새로웠다. 해당 내용을 사용할 때는 무조건 큰 데이터가 좋은 것이 아니라, 사용할 때 범위가 넓어지는 경우, 늘려줄 때에 유용하게 사용된다.

형변환

명시적: 뭘로 바꿀지 직접 표기해준다.

암시적: 표기하지 않아도 내부적에서 자동으로 변환을 해준다.

타입에 관하여 사용할 때, 어떤 값을 통해 계산을 하거나 값끼리 상호 작용하도록 하려면(날짜25-09-29 + 30일 ⇒ 25-10-28) 형변환을 하여 맞추어 주고 진행해주어야 한다.

1. 명시적 형변환

ANSI표준 의 CAST / CONVERT : 변경할 타입을 명시적으로 기입하여 형변환을 해주는 “함수”이다.

SELECT CAST(AVG(menu_price) AS SIGNED INTEGER) 
	AS '평균 메뉴 가격' FROM tbl_menu;
SELECT CONVERT(AVG(menu_price), SIGNED INTEGER) 
	AS '평균 메뉴 가격' FROM tbl_menu;

• INTEGER 뿐 아니라 DATE, CHAR 등 다른 타입으로도 변경 가능하다.

Concatenate 함수

CONCAT("VALUE1" + "VALUE1") => "VALUE1VALUE2"

• 단일행 함수이다. 단일행 함수는 받는 행만큼 똑같이 출력한다.

2. 암시적/묵시적 형변환

SELECT '1' + '2';    -- 각 문자가 정수로 변환됨
-- 특이사항 --------------------------------------
SELECT '1a' + '2'; => 의 결과는 3이 나온다.
-- 숫자가 먼저 나오면 숫자로 쳐 주겠다는 것이다.

SELECT 'a1' + '2'; => 의 결과는 2가 나온다.
-- 문자가 먼저 나오면 'a1'을 문자로 보게 되고, 2만 출력하는 것이다.
-- ------------------------------------------------------------------------
SELECT CONCAT(menu_price, '원') FROM tbl_menu;    
-- > menu_price가 문자로 변환됨

SELECT 3 > 'MAY';    -- 문자는 0으로 변환된다.

SQL 문에서 함수를 사용할 때, 단일행과 그룹을 구분해서 사용해야 한다.

• 그룹 → COUNT, SUM, AVG, MIN, MAX : 그룹을 넣어서 하나만 나온다.

  GROUP BY 는 이 그룹 함수들의 그룹을 나누어 주는 역할을 한다.

• 단일행 → CAST, CONCAT, …엄청많음: 각 행마다 하나씩 출력한다.

그래서 이 함수들을 사용할 때 그룹을 외워두고 구분하여 사용하는 것이 편하다.

  CREATE 등의 DDL 의 명령문을 DML(INSERT, UPDATE, DELETE)을 사용하다가 갑자기 사용하면, 그 전에 쓰던 내용들이 전부 COMMIT 될 수도 있다.

- INSERT 등의 구문을 작성하다가 갑자기 제약 조건을 변경하는 등 DDL 을 건드린다면 그 전 변경 사항들을 COMMIT 한다. 그래서 주의해야한다.

컬럼레벨과 테이블레벨

CREATE TABLE IF NOT EXISTS tb1 (
    pk INT PRIMARY KEY, -- **컬럼 레벨**에서  제약조건 추가
    fk INT,
    col1 VARCHAR(255),
    CHECK(col1 IN ('Y', 'N')) -- **테이블 레벨**에서 제약조건 추가
) ENGINE=INNODB;

• 컬럼레벨 : 컬럼을 선언할 때, 사이드에다가 바로 제약조건을 써넣어주는 것

  pk INT PRIMARY KEY UNIQUE, -- **컬럼 레벨**에서  제약조건 추가

  col1 INT PRIMARY KEY UNIQUE ...
  
• 테이블 레벨: 컬럼을 선언할 때, 사이드에다가 바로 제약조건을 추가해주는 게 아니라, 가장 밑에다가 어떤 제약조건에 어떤 컬럼을 포함시킬 것인지 나중에 써 넣어주는 것

   컬럼들,
   CHECK(col1 IN ('Y', 'N') -- **테이블 레벨**에서 제약조건 추가
   PRIMARY KEY(pk))

  col1 INT, PRIMARY KEY(col1)
  

→ 이처럼 테이블 레벨을 사용해주는 이유는, 컬럼 레벨만 사용 시 너무 많은 제약조건으로 가독성이 떨어지니, 테이블 레벨을 사용함으로써 가독성을 위해 사용된다.

컬럼 레벨, 테이블레벨 제약 조건 존재.

varchar(255): 가변형 char 255자를 사용할 수 있다.

IF NOT EXISTS : 해당 테이블이 있다면 추가하지 않는다.

유효성 제약CHECK(col1 IN ('Y', 'N')

: ‘Y’ or ‘N’만들어갈 수 있다. → 나중에 오류를 잡을 때 좋다고 한다. DB-JAVA-프론트 각각의 자기의 유효성 검사를 다 한다. 그래도 오류가 뚫리려면 다 뚫린다. 그러다 보면 잘못된 데이터가 들어갈 수 있다. 그런 것들로부터 예방하기 위한 유효성 제약이 존재하는 것이다.

• NOT NULL: NULL 값 허용하지 않는 제약조건
• UNIQUE : 중복값 허용하지 않는 제약조건
• PRIMARY KEY : NOT NULL+UNIQUE 제약조건
• FOREIGN KEY : 데이터 무결성을 보장하기 위한 제약 조건 Foreign Key 정리 (MariaDB 기준)
  
• CHECK :
• DEFAULT :

FOREIGN KEY 외래키

생각 외로 FK 관련한 내용이 정말 많았고, 수업 시간 내에서는 정말 순식간에 지나가서 정말 기억이 제대로 나지가 않아 복습은 필수였다.

외래키의 특성은 다음과 같다.

• 참조 무결성을 위배하지 않도록 사용된다. 두 테이블 간의 참조 관계에서 데이터의 일관성을 유지하기 위해 외래키가 참조하는 값이 항상 유효한지 보장하는 무결성이다.
• FK 제약조건에 의해 테이블 간의 관계가 형성된다.
• 제공되는 값 외에는 NULL을 사용할 수 있다.

FOREIGN KEY 제약조건 에러 발생(참조 컬럼에 없는 값 적용 시)

부모의 참조 컬럼에 없는 값을 적용하면 에러가 발생한다.

CASCADE

• 수정 및 삭제 시 함께 삭제한다.

DEFAULT 값 채워주기

컬럼을 작성할 때 혹시 포멧에 맞지 않은 정보가 들어오는 경우를 대비하여 default 값을 채워넣어주는 것이 좋다.

CURRENT_DATE / CURRENT_TIME과 DATE()/TIME()의 사용 차이는 무엇인가?

• DATE(), TIME():
  일자와 시간 추출 -> NOW() 에서 DATE를 뽑아내고, TIME을 뽑아내는 용도
  원하는 정보만 뽑아서 사용 하는 용도이다.

  ↳ currentTIME, DATE랑은 현시점이고 DATE,TIME은 원하는형태 추출이다.
  

• DATE: 연-월-일만 추출

• TIME: 시:분:초만 추출

SELECT DATE(NOW()), TIME(NOW());

VIEW를 통한 DML

생성한 VIEW에 DML 작업을 하면 데이터베이스에도 영향을 준다. VIEW를 통해

• INSERT
• UPDATE
• DELETE

가 가능하다.

하지만 VIEW DML 명령어로 조작이 불가능한 경우가 존재한다.

1. VIEW 정의에 존재하지 않은 컬럼을 조작
2. 뷰에 포함되지 않은 컬럼 중 베이스가 되는 컬럼이 NOT NULL 제약조건이 지정된 경우
3. 산술 표현식(계산식)이 정의된 경우
4. JOIN 을 사용한 경우
5. DISTINCT 를 사용한 경우
6. GROUP BY절나 그룹함수를 포함한 경우

DML 을 VIEW에서 사용할 수 없으니, 해당 경우에 포함되는지 확인하고 사용 여부를 결정해야한다.

CHECK OPTION

• 뷰를 통한 데이터 변경 시 뷰 정의 조건을 만족하지 않는 데이터는 추가/수정 불가하도록 제한하는 옵션
  1. with local check option : 뷰 자체 조건만 검사
  2. With cascaded check option: 상위 뷰의 조건까지 검사
  3. WHERE 조건문을 CHECK한다

CREATE OR REPLACE VIEW hansik_with_check AS
SELECT 
       menu_code 
     , menu_name
     , menu_price
     , category_code
     , orderable_status
  FROM tbl_menu 
 WHERE category_code = 4
  WITH CHECK OPTION;

• 위와 같이 가장 아래에 WITH CHECK OPTION을 붙인다.

인덱스가 없으면 데이터를 찾을 때 FULL TABLE SEARCH

인덱스가 있으면 정렬된 구조를 활용해서 필요한 행만 빠르게 찾는다.

인덱스를 잘 쓰는 경우 & 안 쓰는 경우

✅ 잘 쓰는 경우

• 조회가 자주 일어나는 컬럼 (검색 조건, 조인 키, 정렬 기준)
• 데이터가 많고 검색 범위가 좁을 때 (예: 고객ID, 주문ID)

❌ 안 쓰는 경우

• 데이터 양이 적은 테이블 (인덱스 이득 없음)
• 값의 종류가 적은 컬럼 (예: 성별 M/F → 인덱스 효과 없음)
• 자주 갱신되는 컬럼 (인덱스 유지 비용 ↑)

• Procedure는 SQL 문장을 저장&실행하도록 하는 기능이다. → 작업 자동화 → 네트워크 트래픽 줄임 네트워크-DB 구조  Stored Procedure는 [Localhost](http://Localhost) 전체에서 호출하는 것이 아니라 DB 서버에서 직접 실행되는 구조이다. → client—서버 간 트패릭을 줄임.

↳ 데이터 베이스 서버의 작업 부하를 줄이고 성능을 최적화

Procedure 예시

DELIMITER //  -- > 프로시저 시작

CREATE PROCEDURE procedure_name(  -- > 프로시저 이름
	IN param_01 DOUBLE,           -- > 프로시저 인자 설정
	IN param_02 DOUBLE,         -- > IN,OUT, INOUT(삽입,출력,입출력 초기화)
	OUT result DOUBLE
)
-- 본문 시작
BEGIN
		-- 예외처리 선언 시작
    DECLARE "예외처리_이름_설정" CONDITION FOR SQLSTATE '경고_번호';
    DECLARE EXIT HANDLER FOR "예외처리_이름_설정"
    BEGIN
        SIGNAL SQLSTATE '경고_번호' SET MESSAGE_TEXT = '경고_메시지';
    END;
    -- 예외처리 선언 끝
		-- 메인 쿼리
    IF param_02 = 0 THEN
        SIGNAL "예외처리_이름_설정";
    ELSE
        SET result = param_01 / param_02;
    END IF;
    -- 메인쿼리 끝
END//
-- 본문 끝

DELIMITER ; -- > 프로시저 끝

• 프로시저 호출

CALL procedure_name(10, 2, @result); -- > 인자 맞추어 호출
SELECT @result; -- > 
CALL divideNumbers(10, 0, @result); -- error 발생

트리거란, DB 테이블 상 발생하는 DML 후 자동으로 실행되는 DB 객체다.

• BEFORE 트리거: 이벤트 발생 전 트리거 실행(유효서어 검사, 변형)
• AFTER 트리거: 이벤트 발생 후 트리거(로깅, 알림 전송 등

트리거의 작성 방법은 아래와 같다.

DELIMITER //
CREATE OR REPLACE TRIGGER [트리거명]
		BEFORE|AFTER [이벤트 타입]
		ON [테이블명]
		FOR EACH ROW
BEGIN
END//
DELIMITER ;

• 주문 메뉴 테이블에 INSERT가 되고 나서 주문 테이블의 총 합계가 UPDATE될 수 있는 트리거를 생성할 것이다. 코드는 아래와 같다.

  ```sql
  DELIMITER //
  
  CREATE OR REPLACE TRIGGER after_order_menu_insert
      AFTER INSERT
      ON tbl_order_menu
      FOR EACH ROW
  BEGIN
      UPDATE tbl_order
      SET total_order_price = total_order_price + NEW.order_amount * (SELECT menu_price 
  																																    FROM tbl_menu 
  																																	  WHERE menu_code = NEW.menu_code)
      WHERE order_code = NEW.order_code;
  END//
  
  DELIMITER ;
  ```
  
  - Table_order_menu 에 주문넘버, 음식코드, 주문수량이 들어오면 Order table(주문테이블)에 총 금액이 얼마 나오는지 자동으로 업데이트 해준다.