티스토리 뷰

프로그래밍 공부/데이터베이스 & SQL

[SQL] 마지막화 - 데이터베이스 수정(UPDATE,DELETE,DROP,ALTER), 트랜잭션

sikaro 2024. 2. 25. 17:50
반응형

SQL은 읽기, 쓰기 작업으로 사용되는 것들이 있다.

 

읽기는 SELECT FROM

 

쓰기는 INSERT INTO VALUES

 

INSERT 는 데이터를 삽입만 하기 때문에 상대적으로 안전하지만, UPDATE, DELETE, DROP, 혹은 ALTER 같은 명령들은 더 기존의 데이터를 갱신하기 때문에 훨씬 더 위험할 수 있다.

 

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT);
    
CREATE TABLE diary_logs (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    date TEXT,
    content TEXT
    );

 

다이어리 앱을 위한 데이터베이스를 만든다고 해보자.

사용자가 입력했을 떄, INSERT문이 실행된다.

/* After user submitted their new diary log */
INSERT INTO diary_logs (user_id, date, content) VALUES (1, "2015-04-01",
    "I had a horrible fight with OhNoesGuy and I buried my woes in 3 pounds of dark chocolate.");
    
INSERT INTO diary_logs (user_id, date, content) VALUES (1, "2015-04-02",
    "We made up and now we're best friends forever and we celebrated with a tub of ice cream.");

 

사용자가 읽기를 고치고 싶어 한다면?

UPDATE 문을 사용해야 한다.

 

UPDATE 테이블명 SET 컬럼명 = "내용"

그런데 이렇게 하면 행 표시가 없다.

그래서 한 줄만 수정하려면 행을 알려줘야 한다.

UPDATE diary_logs SET content = "I had a horrible fight with OhNoesGuy" WHERE user_id=1

 

이 구분을 WHERE로 한다. id=1 이런식.

만약 id를 모른다면, 각각의 일기를 구별할 수 있는 열을 사용할 수 있다.

 

예를 들어, user_id와 id를 연결한 키

 

WHERE user_id =1 AND date= ' '

하지만 user_id로 구분하는 것이 더 안전하다.

 

그리고 나서 SELECT 문을 써보면 전에 있던 내용이 사라지고, UPDATE 문의 내용으로 바뀐 것을 볼 수 있다.

 

DELETE

삭제하길 원한다면?

DELETE FROM diary_logs WHERE id=1;

 

당연하게 UPDATE나 DELETE를 사용할 떄 조심해야 한다. 

 

어떤 앱들은 아예 delete라는 개념을 쓰지 않는다.

그 값을 TRUE로 표현하여 그냥 보여주지 않는다.

 

테이블을 생성 후에 변경

테이블이 이미 출시된 상태에서는 CREATE 문을 실행할 수 없다.

이미 CREATE 문이 있는 경우에는 ALTER TABLE 문을 사용해야 한다.

 

ALTER TABLE 테이블명 ADD 추가할 컬럼명 데이터 유형

ALTER TABLE T1 ADD att TEXT

 

이렇게 만들어주면, 새로 만든 열에도 값을 넣어줘야 한다.

보통 값을 따로 넣지 않으면 NULL로 표시한다.

 

기본값을 바꾸고 싶으면 defalut문을 써주면 된다.

ALTER TABLE T1 ADD att TEXT defalut "unknown";

 

테이블을 지우려면 DROP TABLE 문을 쓰면 된다.

 

DROP TABLE T1

 

테이블을 삭제할 떄는 심사숙고해야 한다.

특히 테이블의 행이 여러개일 때는 성능 저하를 일으킬 수 있다.

 

update를 하기 전에는 다음과 같은 것으로 where이 똑같은지 확인하고 실행해본다.

UPDATE users SET deleted = true WHERE id = 1;

대신

SELECT id, deleted FROM users WHERE id = 1;

로 살펴보고 삭제하는 것이다.

 

UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;

LIMIT으로 너무 많은 행을 삭제하는 것을 방지할 수 있다.

 

트랜잭션

트랜잭션은 하나의 논리적인 일로 취급되는 명령의 순서적 집합 (은행 송금 처럼). 데이터베이스의 세계에서 신뢰성을 위해 트랜잭션은 "ACID" 원칙에 따라야 한다.

 

만약에 다른 UPDATE 명령이 수행될 때만 어떤 UPDATE 명령이 통과하도록 하려면 두 명령을 같은 트랜잭션에 넣어야 한다.

이럴 경우, 명령어를 다음과 같이 BEGIN TRANSACTION과 COMMIT으로 감쌀 수 있다. 

BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;

 

만약 데이터베이스가 두 UPDATE를 어떠한 이유로 모두 실행하지 못했다면, 트랜잭션은 롤 백 되고 데이터베이스는 기존 상태로 돌아간다.

 

트랜잭션은 모든 명령어를 실행할 때, 일련의 데이터를 똑같은 방식으로 처리하고 싶을 때 사용한다.

다시 말하자면, 트랜잭션을 실행할 때는 어떤 순서대로 명령어를 실행하는 중에 해당 데이터에 대해 다른 트랜잭션이 작업을 하지 못하게 만들고 싶을 때 쓴다.

 

백업은 무조건 만들어둬야 하고, 복제도 만들어두는 게 좋다.

또한 완전한 권한 부여는 최소화해야 한다.

GRANT FULL ON TABLE users TO super_admin;

 

SELECT 권한만 줄 수도 있다.

GRANT SELECT ON TABLE users TO analyzing_user;

 

반응형