데이터베이스(INSERT, DELETE, UPDATE)

2023.07.13 수업(script 21)

● 데이터베이스  - 데이터 변경쿼리

▶ 1. INSERT
[구문]
1) 정식형식

INSERT INTO 테이블명(컬럼리스트...)
    VALUES(값리스트...);

- 컬럼리스트와 값리스트는 1:1 대응(데이터형, 관련순서)

2) 약식형식

INSERT INTO 테이블명
    VALUES(값리스트);

-> 컬럼을 알 수 없으므로 테이블구조(컬럼들 구조)형식으로 값을 입력한다

3) 주로 백업용으로 필요한 데이터만 모으는 결과보고 데이터용

INSERT INTO 테이블명 (컬럼리스트)
    SELECT (컬럼리스트와 일치하는 검색 컬럼리스트);

※ 실제 데이터 백업용으로 사용할 때 많이 쓰는 방법은 테이블을 만들때 SELECT문장을 사용할 수 있다

- 테이블 복사 
[구문]

CREATE TABLE 테이블명
   SELECT 문장;

- 구문을 이용하면 테이블 구조를 지정해서 만들 필요없이 테이블명으로 SELECT문장으로 검색된 데이터들이 자동으로 테이블구조를 만들고 데이터를 추가할 수 있다
-> primary key는 복사되지 않는다(중복이 발생할 수 있음)

/* 1. INSERT */
-- 3) 백업용
-- subway_statistics테이블 활용하기
-- subway_statistics테이블 구조 복사(조건절에서 검색결과 없음)
CREATE TABLE SUBWAY_STATISTICS_backup AS
	SELECT * FROM SUBWAY_STATISTICS
		WHERE ROWNUM < 1;

3-1) SELECT 문장으로 검색할때 검색결과가 없도록 하면 테이블 구조만 복사해서 만들게 된다
예) CREATE TABLE member_backup AS
         SELECT * FROM member WHERE mem_id <= -1;
- 진짜 데이터가 없는 경우 오라큰 내부 행번호관리 컬럼인 ROWNUM필드(1보다 작을 수 없고 중복불가)를 이용
예) CREATE TABLE member_save AS
         SELECT * FROM member WHERE ROWNUM < 1;

3-2) SELECT 문장으로 검색한 결과 테이블구조와 같이 복사하게 하는 경우
예) CREATE TABLE member_copy AS
         SELECT * FROM member WHERE mem_name LIKE '%황';
: 이 문장을 실행하면 'member_copy'라는 테이블은 'member'테이블과 구조가 같고 '황'이라는 단어가 들어간 모든 데이터가 복사된 테이블이 만들어 진다

3-3) 이렇게 만들어진 테이블에는 primary key(기본키)제약이 없으므로 테이블의 구조에 기본키 제약을 추가해야한다
[구문]
- ALTER TABLE '테이블명' ADD '제약내용 구문';
- 컬럼이름 형식 제약조건 또는 PRIMARY KEY(기본키이름)

- 제약내용에는 테이블 만들때와 같다
예) ALTER TABLE member_save ADD PRIMARY KEY(mem_id);

-- 테이블 구조만 복사하는 경우 기본키 제약이 안걸리므로 테이블 구조 변경을 통하여 기본키 추가
ALTER TABLE SUBWAY_STATISTICS_BACKUP
	ADD PRIMARY KEY (seq_id);

---

▷ 위 테이블에 검색조건을 적용한 데이터를 추가(복사) 하기

- 잠실로 시작하는 역의 데이터 추가하기

- 잠실로 시작하지 않는 역을 카운트하여 복사가 잘 되었는지 확인하기

INSERT INTO SUBWAY_STATISTICS_BACKUP
	SELECT * FROM SUBWAY_STATISTICS
		WHERE STATION_NAME LIKE '잠실%';
-- 변경된 데이터 확인
SELECT * FROM SUBWAY_STATISTICS_BACKUP;
SELECT COUNT(*) 
	FROM SUBWAY_STATISTICS_BACKUP
	WHERE STATION_NAME NOT LIKE '잠실%'; -- 0

▷ 원본 테이블의 데이터를 전부 복사

- 잠실로 시작하지 않는 역의 데이터 추가하기

INSERT INTO SUBWAY_STATISTICS_BACKUP
	SELECT * FROM SUBWAY_STATISTICS
		WHERE STATION_NAME NOT LIKE '잠실%';
-- 결과 확인		
SELECT COUNT(*) FROM SUBWAY_STATISTICS
	WHERE STATION_NAME LIKE '잠실%';

 

▷ 기본키와 충돌하지 않게 데이터를 입력하는 다른 방법(NOT EXISTS)
- 기존에 추가된 데이터 패턴을 모두 기억하지 못하는 경우에 사용

 

* NOT EXISTS

- 값이 존재하지 않으면 연산이 종료됨

- 일치되지 않으면 메인쿼리 테이블의 모든 행을 반환

 

* 값의 존재 유무를 확인해야하는 경우에 SELECT 1 FROM 테이블명; 사용

- select문에 1을 사용하면 해당 테이블의 개수만큼 1로된 행을 출력한다(1은 true 를 의미)
- where조건문과 함께 쓰이면 해당 조건을 만족하면 1을 반환하게 됨

INSERT INTO SUBWAY_STATISTICS_BACKUP 
	SELECT 
			-- st테이블에 추가할 ss 원본의 컬럼데이터
			SS.*
		FROM
			-- subway source(ss)
			SUBWAY_STATISTICS ss
		WHERE NOT EXISTS ( -- 조건으로 검색되지 않는 데이터
		-- 조건 : st에서 하나의 데이터를 가져와서 
		-- (SELECT FROM SUBWAY_STATISTICS_BACKUP st)
		-- WHERE 구문(검사할 조건)
		-- 원본의 SEQ_ID와 st복사본의 SEQ_ID가 동일한가
		-- 두 컬럼이 같으면 데이터가 st복사본에 들어있다는 뜻, 1(true)를 리턴
			SELECT 1
				-- subway 복사본(st)
				FROM SUBWAY_STATISTICS_BACKUP st
				WHERE 
					ss.SEQ_ID = st.SEQ_ID);

▷ 실제 where절 의미 알아보기
- where의 조건식 결과가 항상 참인경우 where 절이 필요없다
- where절에서 직접 값 사용 불가 ex) WHERE = 1
- sql에서는 boolean 데이터표현식은 인식(boolean값)을 한다
- TRUE/FALSE형식으로 값 정의 없음 (TRUE = TRUE 등 불가)

SELECT * FROM SUBWAY_STATISTICS ss;
SELECT * FROM SUBWAY_STATISTICS WHERE 1 = 1;
SELECT * FROM SUBWAY_STATISTICS WHERE 1 <> 0;
SELECT * FROM SUBWAY_STATISTICS WHERE NOT EXISTS(SELECT 1 FROM DUAL); --0

---

▶ 2. DELETE 확장사용법 

[구문]

- WHERE에 검색조건이 들어간다는 부분이 SELECT의 검색 부분과 같으므로 확장이라고 봄

DELETE FROM 테이블명 WHERE 검색조건식

-- 삭제
DELETE FROM SUBWAY_STATISTICS_BACKUP 
	WHERE STATION_NAME LIKE '잠실%';
    
-- 확인
SELECT COUNT(*) 
	FROM  SUBWAY_STATISTICS_BACKUP 
	WHERE STATION_NAME LIKE '잠실%'; -- 0

---

▶ 3. UPDATE 확장사용법

[구문]
컬럼 = 값 뒤에 이어지는 ','은 나열연산자이므로 마지막 컬럼 = 값 뒤에는 붙이지 않는다

UPDATE 테이블명 SET 컬럼1 = 값1, 컬럼2, 값2, ...
    WHERE 업데이트할 조건;

-- 먼저 업데이트를 적용할 내용을 검색(업데이트 대상 확인)
SELECT * FROM SUBWAY_STATISTICS_BACKUP
	WHERE STATION_NAME LIKE '삼성%'  
	ORDER BY SEQ_ID;
	
-- 삼성역을 검색하여 PASSENGER_NUMBER(승객수) + 100, BOARD_DATE(탑승일)은 한 달뒤로 변경
UPDATE SUBWAY_STATISTICS_BACKUP 
	SET 
		PASSENGER_NUMBER = PASSENGER_NUMBER + 100,
		BOARD_DATE = BOARD_DATE + 30
	WHERE STATION_NAME LIKE '삼성%';
    
-- 확인
SELECT * FROM SUBWAY_STATISTICS_BACKUP
	WHERE STATION_NAME LIKE '삼성%';
    
-- 1달의 일수는 30일만 존재하지 않음
-- 날짜함수중에 ADD_MONTHS(월, 구하고자하는 월 수)를 이용하면 정확한 개월수가 적용됨
UPDATE SUBWAY_STATISTICS_BACKUP
	SET 
		PASSENGER_NUMBER = PASSENGER_NUMBER + 100,
		BOARD_DATE = ADD_MONTHS(BOARD_DATE, 1)
	WHERE STATION_NAME LIKE '삼성%';

원본

30일 더하기

1달 더하기

 

---

◎ 트랜잭션

- 한 번 질의가 실행되면 질의가 모두 수행되거나 모두 수행되지 않는 작업수행의 논리적 단위

●  트랜잭션 4대 특징(ACID)
1. Atomicity(아토믹) : 원자성 
- 트랜잭션의 작업이 부분적으로 실행되거나 중단되지 않는 것을 보장하는 것
- 트랜잭션이 DB에 모두 반영되거나 전혀 반영되지 않거나
- 각 단위 컬럼을 서로 무관하다

2. Consistency(컨시스턴시) : 일관성
- 트랜잭션이 성공적으로 완료되면 일관성 있는 데이터베이스 상태로 유지하는 것
- 송금 전후 모두 금액의 데이터 타입은 정수형이여야 한다는 것
- 게시판의 게시글에 코멘트의 업데이트와 삭제 결과가 보장

3. Isolation(아이소레이션) : 독립성 = 무결성 = 격리성
- 트랜잭션 수행시 다른 트랜잭션의 작업이 끼어들지 못하도록 보장하는 것
- 이전에 실행한 결과가 다른 항목에 영향을 주면 안된다
- 서로 간섭 못함

4. Durability(듀러빌러티) : 지속성 = 내구성
- 성공적으로 수행된 트랜잭션(commit)은 영원히 반영이 되는 것
- 이미 완료된 결과는 어떤 경우에도 같은 상태를 유지한다