(3) 4장. 데이터의 추가, 삭제, 갱신

(3) 4장. 데이터의 추가, 삭제, 갱신

16~19강 INSERT, DELETE, UPDATE

---

16강. 행 추가하기 - INSERT

• INSERT 명령을 사용해 테이블의 행 단위로 데이터를 추가
• INSERT INTO 테이블명(컬럼명1, 컬럼명2, ...) VALUES(값1, 값2, ...);
• [신규 등록] 또는 [추가] 버튼 클릭 시 처리되는 데이터 추가 기능
• NOT NULL 제약이 걸려있는 열은 NULL 값을 허용하지 않음

• default설정이 있는 경우; default로 값을 설정하거나(↖명시적 방법) 열을 지정하지 않으면(암묵적 방법↗) default 값으로 행이 추가됨

• AUTO_INCREMENT 제약의 INSERT 특성

  

  • → NOT NULL 제약이 있더라도 별도의 값이 지정되지 않아도 됨 (AUTO_INCREMENT)
  • → AUTO_INCREMENT 열에 강제로 값(n)을 입력했을 때
    n < AUTO_INCREMENT; AUTO_INCREMENT은 변하지 않음
    n > AUTO_INCREMENT; AUTO_INCREMENT의 현재 값이 얼마였든지 관계없이 강제로 n+1값이 AUTO_INCREMENT의 다음 값으로 지정됨
  • → 0 또는 NULL값 입력 시 AUTO_INCREMENT됨→ AUTO_INCREMENT에 0값을 insert하려면 sql_mode 시스템 변수에 "NO_AUTO_VALUE_ON_ZERO" 추가하면 됨
    (외에도 많은 옵션이 있음..! 필요시 찾아보기)

• MySQL 중복 키 관리 방법

  • INSERT IGNORE; 로그나 이력 성격의 프라이머리 키나 유니크 키가 중복되는 레코드는 그냥 버려도 무방할 때 신규로 입력되는 레코드는 무시하는 방법
    (AUTO_INCREMENT는 새롭게 발급 됨!)

    → 값이 변형되어 저장되는 경우

    • 컬럼에 입력해야 할 값이 컬럼의 타입과 다르다 > 최대한 비슷한 타입으로 변환하여 저장
    • NOT NULL 컬럼에 NULL을 저장하려고 한다 > NULL 대신 빈 문자열 또는 0을 저장
      ※ 위와 같은 경우 별도의 에러 발생 없이 레코드가 테이블에 저장되므로 IGNORE 사용 시 저장하려는 값이 의도와 달리 변형되는지 혹은 저장되는 건수가 얼마나 되는지 확인 필요..!

  • REPLACE INTO; insert into 구문에서 insert를 replace로 바꾼 구문으로 중복 키 오류 발생 시 '2 rows affected'가 출력 되며
    기존 레코드를 삭제하고 새로운 레코드를 입력한다 ∴ 1건은 delete 다른 1건은 insert이므로 AUTO_INCREMENT는 새롭게 발급 됨!

    → REPLACE 문장에서 중복된 레코드에 대한 판정 기준

    • 테이블의 모든 유니크 키에서 동일 값이 존재하는지를 비교 ∴테이블에 프라이머리 키와 별도의 유니크 키가 있다면 프라이머리 키와 유니크 키 모두에 중복된 값이 있는지 체크함

      

    • REPLACE 쿼리는 중복된 레코드가 있을 때 기존 레코드를 사제하고 새로이 레코드를 INSERT하므로 이미 존재하는 중복 레코드의 컬럼 값을 참조할 수 없음 ← 이 단점을 해결하려면 INSERT INTO ... ON DUPLICATE KEY UPDATE 문장을 쓰면 됨

  • ON DUPLICATE UPDATE; 중복 키 오류 발생 시 사용자가 원하는 값을 직접 설정할 수 있음 (중복된 레코드를 DELETE하지 않고 기존의 컬럼 값을 참조해서 UPDATE한다는 것이 replace와 차이), 중복된 레코드에 대한 판정 기준은 replace와 동일

    

    • AUTO_INCREMENT가 어떻게 변화하는지 확인

      

---

17강. 삭제하기 - DELETE

• DELETE명령을 사용해 테이블의 행 단위로 데이터 삭제
• DELETE FROM 테이블명 WHERE 조건식;
• [삭제] 또는 [취소] 버튼 클릭 시 처리되는 데이터 삭제 기능
• WHERE 구에 조건을 지정하여 해당 조건에 일치하는 '모든 행'을 삭제할 수 있음
  • → SELECT 명령에서는 조건에 맞는 행의 결과값이 클라이언트로 반환되지만, DELETE 명령에서는 조건에 맞는 행이 삭제된다는 점이 다름
  • → ORDER BY 절과 LIMIT 절을 동시에 사용해 특정 값으로 정렬해서 그 중 상위 몇 건만 삭제 하는 것도 가능
    이때 마스터와 슬레이브에서 중복된 값의 정렬 순서가 달라질 수 있기 때문에 프라이머리 키로 정렬하는 것이 아니라면 주의 필요
• JOIN DELETE; 두 개 이상의 테이블을 조인할 때, 특정(하나 또는 다수의) 테이블의 레코드 삭제 시 사용
• select와 마찬가지로 index 유무에 따라 처리 방식이 다를 수 있음
• drop / truncate / delete 차이
  • DELETE FROM (테이블명); 데이터를 순차적으로 삭제
  • DROP TABLE (테이블명); 테이블 자체를 제거
  • TRUNCATE TABLE (테이블명); 테이블 자체를 삭제하고 같은 이름의 테이블을 새로 생성 → 테이블 DROP 후 CREATE하는 명령어 (속도가 빠르다) (auto_increment가 초기화 된다) ∴모든 데이터를 삭제할 때 복구가 가능한 DELETE 명령과 달리 TRUNCATE 명령은 데이터 복구가 불가능

---

18강. 데이터 갱신하기 - UPDATE

• UPDATE명령을 사용해 테이블의 셀에 저장되어 있는 값 갱신

• UPDATE 테이블명 SET 열1=값1, 열2=값2, ... WHERE 조건식;

• [등록] 또는 [갱신] 버튼 클릭 시 처리되는 데이터 갱신 기능

• DELETE와 달리 UPDATE는 셀 단위로 데이터를 갱신할 수 있음

• WHERE 구에 조건을 지정하면 그에 일치하는 행을 갱신할 수 있으며, 생략된 경우 테이블의 모든 행이 갱신됨

• ORDER BY 절과 LIMIT 절을 동시에 사용해 특정 값으로 정렬해서 그 중 상위 몇 건만 업데이트 하는 것도 가능

  • 이때 마스터와 슬레이브에서 중복된 값의 정렬 순서가 달라질 수 있기 때문에 프라이머리 키로 정렬하는 것이 아니라면 주의 필요
    → 마스터와 슬레이브가 동기화되었다고 판단하는 기준; PK값이 같고 개수가 같다 ∴update/delete _ order by _ limit n과 같은 쿼리는 지양할 필요가 있다

    

    → ranking 열에 연봉 순위를 매기는 쿼리 > 게임의 랭킹이나 순서를 부여하는 기능으로 활용 가능

• JOIN UPDATE; 두 개 이상의 테이블을 조인할 때, 특정 테이블의 칼럼 값을 이용해 다른 테이블의 컬럼에 업데이트 해야할 때 사용

• SET의 '=' 연산은 비교 연산자가 아닌 값을 대입하는 대입 연산자

• '값'은 상수로 표기하며 INSERT 명령과 마찬가지로 자료형에 맞는 값(리터럴)을 지정해야 함

  

• 갱신할 값을 열이 포함된 식(y+1)으로도 표기할 수 있음

• 여러개의 열을 한번에 갱신할 경우 처리 순서 주의

  • ORACLE에서는 SET 구에 기술한 식의 순서가 처리에 영향을 주지 않음

인덱스 사용시 delete, update는 처리 시간↓ but, insert는 인덱스 유무에 영향X

---

19강 물리삭제와 논리삭제

• 물리삭제; SQL의 DELETE 명령을 사용해 직접 데이터를 삭제하자는 사고 방식
  → 삭제 대상 데이터는 필요없는 데이터이므로 DELETE 명령을 실행해서 테이블에서 삭제해 버리자! 라는 자연스러운 발상 > 하드웨어의 제한이 있다면 선택
  ex) SNS 서비스처럼 사용자의 개인정보를 다루는 시스템에서는 사용자가 탈퇴하는 경우 데이터를 삭제함 → 개인정보 유출 방지를 위해 물리삭제!

• 논리삭제; 테이블에 '삭제플래그'와 같은 열을 미리 준비하여 테이블에서 실제로 행을 삭제하는 대신, UPDATE 명령을 이용해 '삭제플래그'의 값을 유효하게 갱신해두자는 발상

  → 실제 테이블 안에 데이터는 남아있지만, 참조할 때에는 '삭제플래그'가 삭제로 설정된 행을 제외하는 SELECT 명령을 실행 > 결과적으로 해당 행이 삭제된 것 처럼 보임 > 롤백이 가능

  • but 삭제해도 DB 공간 그대로 검색속도↓

  • 삭제임에도 불구하고 UPDATE 명령을 실행하므로 혼란을 야기

    ex) 쇼핑 사이트의 경우 사용자가 주문을 취소할 경우 논리삭제
    ex) 탈퇴시, 우편함 ... 정책 상 근거 자료를 가지고 있어야 하는 data의 경우 적재를 하는 편

---