SQL | MySQL | 데이터 수정, 삭제 - Update, Delete

 

 

 

01. UPDATE - 주어진 조건의 행 수정 

 

MySQL에서는 UPDATE 문을 사용하여 행의 내용을 수정할 수 있음

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
         SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
         [WHERE where_condition]
         [ORDER BY ...]
         [LIMIT row_count]

 

1)  예제 데이터 전처리 및 테이블 생성 

 

· 소상공인시장진흥공단의 상가(상권) 정보 데이터 사용

 

https://www.data.go.kr/data/15083033/fileData.do

소상공인시장진흥공단_상가(상권)정보_20221231

 

     - 대전광역시 csv 파일 사용 

     - 대전광역시 5개의 자치구 중 서구만 추출

     - 상권업종대분류코드가 Q(음식)인 경우만 추출

     - 필요 없는 컬럼 삭제 

 

### 대전광역시 서구, 상권업종대분류코드가 Q(음식) 인 경우만 추출 
library(dplyr)
data <- read.csv(file.choose(), fileEncoding = 'utf-8')
data %>% head()
data.s <- data %>% filter(시군구명 =='서구' & 상권업종대분류코드 =='Q')
data.s %>% dim()

### 필요한 컬럼만 추출
data.s %>% names()
data.food <- data.s %>% 
                select(-지점명, -지번코드, -대지구분코드, -대지구분명, -지번본번지,
            -지번부번지, -건물본번지, -건물부번지, -건물관리번호, -동정보, -층정보, -호정보)
data.food %>% dim()
data.food %>% summary()
data.food %>% head()
data.food %>% names()

table(data.food$구우편번호)

### csv 파일로 내보내기 
write.csv(data.food, file = "C:/소상공인시장진흥공단_상가(상권)정보_20221231/상권정보_대전_음식.csv")

 

 

 

- DB에 테이블 생성 

/* 상권(상가)정보 테이블 생성 */
CREATE TABLE store (
      상가업소번호 int PRIMARY KEY,
      상호명 text,
      상권업종대분류코드 char(2),
      상권업종대분류명 char(3),
      상권업종중분류코드 varchar(5),
      상권업종중분류명 varchar(10),
      상권업종소분류코드 char(7),
      상권업종소분류명 varchar(25),
      표준산업분류코드 char(6),
      표준산업분류명 varchar(50),
      시도코드 char(2),
      시도명 char(5),
      시군구코드 char(5),
      시군구명 char(2),
      행정동코드 char(10),
      행정동명 varchar(10),
      법정동코드 char(10),
      법정동명 varchar(4),
      지번주소  VARCHAR(50),
      도로명코드 char(12), 
      도로명 VARCHAR(50),
      건물명 VARCHAR(50), 
      도로명주소 VARCHAR(50), 
      구우편번호 char(6),
      신우편번호 char(5),
      경도 FLOAT,
      위도 FLOAT
);

 

 

 

· Kaggle의 와인품질 데이터 사용 

https://www.kaggle.com/datasets/ghassenkhaled/wine-quality-data?resource=download 

Wine_Quality_Data

 

- DB에 테이블 생성 

CREATE TABLE wine ( 
     fixed_acidity float, 
     volatile_acidity float, 
     citric_acid float, 
     residual_sugar float, 
     chlorides float,
     free_sulfur_dioxide float, 
     total_sulfur_dioxide float, 
     density float, 
     pH float, 
     sulphates float, 
     alcohol float, 
     quality char(1),
     color varchar(10)
    )

 

 

 

2)  where 문을 추가하여 주어진 조건의 행 수정 

 

· 상권업종소분류명이 '라면김밥분식'인 데이터를 '라면/김밥/분식'으로 변경 

SELECT 상권업종소분류명
FROM STORE 
WHERE 상권업종소분류명 = '라면김밥분식';

 

 

변경 전

 

 UPDATE store 
 SET 상권업종소분류명  = '라면/김밥/분식'
 WHERE 상권업종소분류명 = '라면김밥분식'
 ;

 

변경 후

 

3) 여러 컬럼 수정하기 

 

· 와인 품질 데이터 셋에서 chlorides, density 컬럼 값 반올림 

 

- 변경 전 

SELECT chlorides, density 
FROM wine 
ORDER BY 1,2;

 

 

- 변경 후 

UPDATE wine 
SET chlorides = ROUND(chlorides, 2) , /* 소수점 둘째자리까지 반올림 */
      density = ROUND(density, 2) /* 소수점 둘째자리까지 반올림 */
 ;

 

 

 

 

4)  CASE WHEN 문 사용해서 데이터 UPDATE

 

· 와인 품질 데이터 셋에서 'quality' 변수가 3~5이면 'C', 6~7이면 'B' , 8~9이면 'A' 등급 부여 

 

- grade 컬럼 생성 

ALTER TABLE wine ADD COLUMN grade char(1)  AFTER quality;

 

- case when 문을 사용하여 등급 update

UPDATE WINE  
   SET grade = CASE WHEN (QUALITY IN ('3','4','5')) THEN 'C'
                            WHEN (QUALITY IN ('6','7')) THEN 'B'
                            WHEN (QUALITY IN ('8','9')) THEN 'A' ELSE NULL END 
                            ;

 

 

 

 

5) 서브쿼리를 사용해서 UPDATE

 

-- 방법 1
UPDATE 테이블명
SET 컬럼1 = (서브쿼리)
WHERE 조건 = 조건값

-- 방법 2 
UPDATE 테이블1
SET 컬럼 1 = (SELECT 값1 FROM 테이블2 WHERE 조건2 = 조건값2)
WHERE 조건1 = 조건값1

 

· fixed_acidity의 평균을 서브쿼리를 통해 계산한 후, fixed_acidity의 평균 이상인 값들을 평균값으로 대체

SELECT  avg(FIXED_ACIDITY) ,
        max(FIXED_ACIDITY)
FROM WINE;

- 평균값 대체 전 fixed_acidity의 평균은 7.2153070684174345 / 최대값은 15.9

 

- SELECT * FROM(SELECT round(avg(FIXED_ACIDITY),1) FROM wine → 7.2임

UPDATE WINE 
SET fixed_acidity = (SELECT * FROM(SELECT round(avg(FIXED_ACIDITY),1) FROM wine) AS x)
WHERE  fixed_acidity >= (SELECT * FROM(SELECT round(avg(FIXED_ACIDITY),1) FROM wine) AS x)
;

 

※ 오라클의 경우에는 set이나 where 구문에서 서브쿼리를 바로 사용할 수 있는데,

     MySQL은 SELECT 구문에 서브쿼리를 한 번 감싸고 alias를 지정해줘야 함.....

    그냥 MySQL은 SELECT로 열 하나 수정해서 만드는 게 편할 듯...!

 

 

---

 

02. DELETE- 주어진 조건의 행 삭제 

 

· DELETE Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
         [WHERE where_condition]
         [ORDER BY ...]
         [LIMIT row_count]

 

1) where 조건문에 해당하는 행만 삭제 

 

· grade가 'C' 등급인 행 제거 

SELECT grade, count(*)
FROM wine
GROUP BY grade;

 

C등급 제거 전

 

· where문을 사용해 C등급 행만 제거 

DELETE FROM WINE
WHERE grade = 'C';

C등급 제거

 

2)  DELETE 문으로 행 전체 삭제 

 

※ DELETE 명령어는 데이터는 지워지지만 테이블 용량은 줄어들지 않는다.

※ 원하는 데이터만 지울 수 있고(where문), 삭제 후 잘못 삭제한 것을 되돌릴 수 있다.(ROLLBACK 가능)

DELETE FROM WINE;

 

 

 

3) TRUNCATE 문으로 테이블 초기화

 

※ TRUNCATE 명령어는 용량이 줄어들고, 인덱스 등도 모두 삭제 된다. 

※ 테이블을 삭제하지는 않고, 데이터만 삭제하며, where문을 사용할 수 없다(ROLLBACK 불가능)

truncate wine;

 

 

※ 참고로 DROP 명령어는 테이블 전체를 삭제, 공간, 객체를 삭제한다. ROLLBACK 불가능

 

 

 

 

 

---

출처 및 참고 문헌(reference)

https://dev.mysql.com/doc/refman/8.0/en/preface.html

MySQL :: MySQL 8.0 Reference Manual :: Preface and Legal Notices

https://www.yalco.kr/@sql/3-4/

데이터 변경, 삭제하기

https://wikidocs.net/4021

6) DELETE, TRUNCATE, DROP 명령어의 차이점