데이터 삽입 / 수정 / 삭제 (INSERT / UPDATE / DELETE)

목차

INSERT / UPDATE / DELETE

• 연습용 데이터 생성

  • CREATE TABLE product (
    	id INT AUTO_INCREMENT PRIMARY KEY,
    	상품명 varchar(100),
    	가격 INT
    )

INSERT

기본 삽입

• 기본 코드

  • INSERT INTO 테이블명 (컬럼명1, 컬럼명2 ...) VALUES (값1, 값2 ...)

• 예시
  • 데이터 넣기

    • INSERT INTO product (id,상품명,가격) VALUES (1,"사과",500)

  • AUTO_INCREMENT 제약이 붙은 컬럼은 입력을 생략해도 됩니다. (알아서 채워줍니다.)

    • INSERT INTO product (상품명,가격) VALUES ("포도",1000)

       
  • 모든 컬럼에 빠짐없이 데이터를 채우고 싶은 경우에는 컬럼명을 생략할 수 있습니다.

    • INSERT INTO product VALUES (3,"수박",1000)

  • 여러 행을 동시에 넣는 것도 가능합니다.

    • INSERT INTO product VALUES (4,"자두",300),(5,"참외",700);

  • 일부 컬럼에만 데이터 저장하고 싶은 경우 NULL을 입력합니다.
    • NULL 대신에 빈문자 "" 아니면 0을 입력해 두는 사람들도 있습니다.

    • INSERT INTO product VALUES (6,"귤",NULL),(7,"오렌지",NULL);

  • 해당 컬럼에 데이터를 지정해주지 않으면 해당컬럼은 NULL이 됩니다.

    • INSERT INTO product (id,가격) VALUES (100,1000)

    • 

다른 테이블 데이터 복사해서 삽입

• SQL문 결과로 나온 테이블 결과를 INSERT 할 수도 있습니다.

  • INSERT INTO 기존테이블명 SELECT 컬럼명 FROM 테이블명

  • VALUES 대신에 SQL문입력

• 예시
  • product2 테이블을 추가하고 난 뒤 아무 데이터나 추가시켜 줍니다.

    • 

  • product2 테이블을 product1에 추가시켜 보기

    • INSERT INTO product (id, 상품명, 가격)
      SELECT id, 상품명, 가격 FROM product2

    • 

UPDATE

• 기본 코드
  • 항상 UPDATE / SET / WHERE을 입력하고 시작합니다.
    • WHERE을 입력하지 않는다면 모든 행이 수정되어 버려 대참사가 발생합니다.

  • UPDATE 테이블명 
    SET 컬럼1 = 값, 컬럼2 = 값
    WHERE 조건식

• 예시
  • product 테이블에서 수박 가격을 1500으로 변경시켜주기

    • UPDATE product
      SET 가격=1500
      WHERE 상품명='수박'

  • 1000원 이하의가격을 가진 과일 가격을 200원씩 인상하기

    • UPDATE product
      SET 가격=가격+200
      WHERE 가격<=1000

    • 이전 결과

      • 

    • 이후 결과

      • 

• JOIN한 테이블에도 수정명령 가능

  • UPDATE A INNER JOIN B 
      ON 조인조건
    
    SET 수정할내용
    WHERE 조건식

DELETE

• 기본 코드
  • 항상 DELETE / FROM / WHERE을 입력하고 시작합니다.
    • WHERE을 입력하지 않는다면 모든 행이 수정되어버려 대참사가 발생합니다.

  • DELETE FROM 테이블명 WHERE 조건식

• 다른 테이블에서 해당 데이터를 Foreign key로 사용 중이면 삭제되지 않습니다. (정확히는 Foreign key 설정값에 따라 달라집니다.)
  • 참고 (Foreign key 설정 옵션)
    • • [FOREIGN KEY (속성리스트) REFERENCES 참조할테이블명(속성리스트)] [ON DELETE 옵션] [ON UPDATE 옵션]
        • 외래키로 테이블에 여러 개 존재 가능
        • ON DELETE 옵션 (외래키가 참조하고 있는 테이블에서 (참조되고 있는 기본키를 가진) 투플을 삭제하려고 하는 경우 = 참조되는 테이블의 투플이 삭제될 때)
          • ON DELETE NO ACTION(기본값) : 투플을 삭제하지 못하게 한다.
          • ON DELETE CASCADE : 관련 투플을 함께 삭제한다.
          • ON DELETE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다.
          • ON DELETE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본값(해당 속성값의 DEFAULT값)으로 변경한다.
        • ON UPDATE 옵션 (외래키가 참조하고 있는 테이블에서 (참조되고 있는 기본키를 가진) 투플을 변경하려고 하는 경우 = 참조되는 테이블의 투플이 변경될 때)
          • ON UPDATE NO ACTION(기본값) : 투플을 변경하지 못하도록 한다.
          • ON UPDATE CASCADE : 관련 투플에서 외래키 값을 함께 변경한다.
          • ON UPDATE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다.
          • ON UPDATE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 값으로 변경한다.
• 예시
  • 거봉 데이터 삭제하기

    • DELETE FROM 테이블명 WHERE 조건식

       
  • 가격정보가 없는 데이터 삭제하기

    • DELETE FROM product
      WHERE 가격 IS NULL

• JOIN한 테이블에도 삭제명령 가능

  • DELETE A 
    FROM A INNER JOIN B 
      ON 조인조건
    
    WHERE 조건식

    • DELETE A 이러면 조건식에 맞는 행이 A 테이블에서 삭제
      • 
    • DELETE B 이러면 조건식에 맞는 행이 B 테이블에서 삭제
      • 
    • DELETE A, B 이러면 조건식에 맞는 행이 A, B 테이블에서 전부 삭제
      • 

예제

• 아래 예시 테이블을 보고 이상구씨가 수업하는 프로그램의 가격을 1000원씩 인상하기(JOIN 이용)
  • 

  • UPDATE program INNER JOIN teacher ON program.강사id = teacher.id
    SET 가격=가격+1000
    WHERE 강사="이상구"

  • JOIN에 대한 테이블을 UPDATE 하면 기존 테이블의 정보가 업데이트됩니다.
    • 
• 위 예시 테이블을 JOIN 후에 이상구 강사님을 DELETE 해보기(teacher table에서만 제거)
  

  • DELETE teacher 
    FROM program INNER JOIN teacher ON program.강사id=teacher.id
    WHERE 강사="이상구"

  • 
• 위 예시 테이블을 JOIN 후에 박덕팔 강사님을 DELETE 해보기 해당 프로그램 정보도 같이 삭제(program,teacher 모두 삭제)

  • DELETE program,teacher 
    FROM program INNER JOIN teacher ON program.강사id=teacher.id
    WHERE 강사="박덕팔"

  • 
  • 
• 아래 user_sales 테이블에서 이메일란이 공백인 곳에 일관적으로 test@test.com입력하기. 단, first_name이 Solly인 사람은 admin@test.com으로 기입해야합니다.
  • 

  • UPDATE user_sales
    SET email = IF(first_name="Solly","admin@test.com","test@test.com")
    WHERE email=""

     
• 위 user_sales 테이블에서 sales 부분이 NULL인 행을 일괄삭제해보기

  • DELETE FROM user_sales
    WHERE sales IS NULL

출처

• codding apple [한 번에 끝내는 SQL & Database]