Coding 공부/DBMS

[DBMS] SQL 명령어 복습, 트렌젝션, prepared statement

CBJH 2024. 3. 21. 12:02
728x90
반응형

1.  SQL 명령어 예제

 

  1.1 student 테이블 생성하기 >> 유형 변경하기

  • ALTER TABLE 테이블명  MODIFY();로 테이블의 유형을 변경할 수 있다.
  • name행의 유형을 VARCHAR(10)에서 VARCHAR(20)으로 변경했다.

 

 

  1.2 student 테이블 행 추가

  • INSERT INTO 테이블명( 첫번째 열 이름 , 두번째 열 이름 ) VALUES(추가할 행 첫번째 열 내용, '추가할 행 두번째 열 내용');
  • INSERT INTO 테이블명 VALUES(추가할 행 첫번째 열 내용 , '추가할 행 두번째 열 내용 ');
  • INSERT INTO 테이블명( 첫번째 열 이름 ) VALUES(추가할 행 첫번째 열 내용);
  • 여러가지 방법의 SQL 명령어로 행을 추가할 수 있다. PK값은 NULL값이 아니므로 꼭 추가해줘야하고 NULL값을 넣어도 되는 유형에는 값을 넣지 않아도 행을 추가할 수 있다.
  • SELECT * FROM 테이블명;을 입력하면 테이블에 작성된 행들을 모두 보여준다. 

 

  1.3 student 테이블 행 수정

UPDATE 테이블명 SET 행 열 이름  = '바꿀 행 내용' WHERE 행 열 이름 = 행  내용;

SQL 명령어로 행의 내용을 수정 할 수 있다.

WHERE을 제대로 적지 않으면 모든 자료가 한 번에 수정 될 수 있으므로 주의하자.

 

1.4 student 테이블 행 삭제

  • SQL문의 DELETE 명령어로 행의 PK값이나 유형을 입력해 삭제할 수 있다.
  • SQL문 한 줄로 여러 행들을 삭제할 수 있다.
  • WHERE을 제대로 적지 않으면 모든 자료가 한 번에 삭제 될 수 있으므로 주의하자.

 

1.5 student 테이블 불러오기

  • SQL문의 SELECT 명령어로 테이블 내용을 불러올 수 있다.
  • ORDER BY 테이블 이름 DESC으로 정렬을 할 수 있다.
  • DESC는 내림차순으로, 반면에 ORDER BY만 사용되면 기본적으로 오름차순으로 행을 정렬해 보여준다.
 
 
1.6 student 테이블 삭제하기

 

  • DROP TABLE 테이블명;             >>테이블을 삭제한다.
  • DESC 테이블명;
  • DESCRIBE 테이블명;
  • SQL, MYSQL(DBMS)에서 테이블의 구조를 보기 위해 사용되는 단축 명령어

 

 

 

2. 트렌젝션

 

SQL에서 트랜잭션(Transaction)은 데이터베이스의 상태를 변화시키기 위해 수행하는 하나의 작업 단위 또는 연산의 집합을 의미합니다. 트랜잭션은 여러 개의 SQL 명령문들을 묶어서 하나의 작업으로 처리할 때 사용되며, 이러한 트랜잭션은 다음 네 가지 기본 속성(ACID 속성)을 만족해야 합니다:

  1. 원자성(Atomicity): 트랜잭션 내의 모든 명령들은 모두 실행되거나 하나도 실행되지 않아야 합니다. 즉, 트랜잭션의 연산은 데이터베이스에 대해 원자적으로 처리되어야 합니다.
  2. 일관성(Consistency): 트랜잭션이 실행되기 전과 후의 데이터베이스 상태는 일관된 상태를 유지해야 합니다. 이는 트랜잭션이 실행되는 동안 데이터베이스의 모든 무결성 제약 조건이 만족되어야 함을 의미합니다.
  3. 독립성(Isolation): 동시에 여러 트랜잭션이 실행되고 있을 때, 각 트랜잭션은 서로의 연산에 영향을 받지 않고 독립적으로 실행되어야 합니다. 독립성은 다른 트랜잭션의 중간 상태를 볼 수 없게 하며, 결과적으로는 마치 트랜잭션이 순차적으로 실행된 것처럼 보이게 만듭니다.
  4. 지속성(Durability): 트랜잭션이 성공적으로 완료되면, 그 결과는 시스템에 영구적으로 반영되어야 합니다. 시스템에 장애가 발생하더라도, 완료된 트랜잭션의 결과는 손실되지 않아야 합니다.

트랜잭션을 사용하는 주된 목적은 데이터의 정합성과 안정성을 보장하는 것입니다. 예를 들어, 은행 시스템에서 A 계좌에서 B 계좌로 돈을 이체하는 경우, 돈을 출금하는 작업과 입금하는 작업은 하나의 트랜잭션으로 묶여야 합니다. 이렇게 함으로써 이체 과정 중에 시스템이 실패하더라도 돈이 출금만 되고 입금되지 않는 등의 일관성 없는 상태를 방지할 수 있습니다.

SQL에서 트랜잭션을 다루는 기본적인 명령어로는 BEGIN TRANSACTION (또는 단순히 BEGIN)으로 트랜잭션을 시작하고, COMMIT으로 트랜잭션을 완료하여 변경 사항을 데이터베이스에 영구적으로 반영하며, ROLLBACK으로 트랜잭션을 취소하고 시작 시점으로 되돌립니다.

 

예시) 마우스를 '주문'하면 '재고'가 있는지 체크하고 '결제'후 '내역'을 사용자에게 보내준다. 

주문, 재고, 결제, 내역은 모두 트랜잭션으로 묶여서 한 번에 처리한다.

 

2.1 트렌젝션으로 커넥션 연결하기

  • 자바에서 Connection 객체 con을 setAutoComit(false)로 설정하면 Comit를 해줘야 SQL명령어로 변경한 값들을 최종적으로 저장한다. 즉 그 전까진 테이블의 자료들이 수정되지 않고 수정 대기만 하는 상태이다.
  • setAutoComit은 따로 설정하지 않으면 true값을 가져 SQL문을 보낼때마다 바로바로 comit이 된다. >> 자바에서 값을 수정한 후 SQL Developer에 있는 테이블을 확인하면 값이 바로 바뀌어있는 것을 확인할 수 있다.

 

 

2.2 트렌젝션 예제

>> 명령프롬프트로 pay, orderList 테이블 생성

  • 자바코드, 17줄에 con.setAutoCommit(false); 로 트렌젝션 설정.
  • 36줄에 con.commit();을 해줘야 변경 사항이 저장된다. 
  • 40줄에 con.rollback();을 하면 지금까지 변경했던 사항이 저장되지 않고 되돌아간다.

 

  • orderList에 orderId, productName 요소가 두 개이므로 하나만 넣고 싶으면 orderList(oderId)라고 입력한다.
  • orderList만 입력하면 아래와 같은 오류가 발생한다.

 

 

 

 

3. prepared statement

 

SQL에서 준비된 문장(prepared statement)은 사전에 컴파일되고, 실행 시에만 매개변수를 전달받아 실행되는 SQL 문장을 말합니다. 이 방식은 SQL 인젝션 공격으로부터 보호하고, 쿼리 실행 성능을 향상시키는 데 도움을 줍니다. 준비된 문장은 동일한 SQL 구조를 반복적으로 사용할 때 특히 유용합니다.

작동 원리

  1. 쿼리 준비: 클라이언트는 서버에 쿼리의 구조를 보내고, 이 때 실제 데이터는 포함되지 않습니다. 대신 데이터가 들어갈 자리를 물음표(?) 같은 플레이스홀더로 표시합니다.
  2. 쿼리 파싱 및 컴파일: 서버는 이 쿼리를 파싱하고, 실행 계획을 세우며, 컴파일합니다. 이 과정에서 SQL 인젝션의 여지가 없기 때문에 보안성이 강화됩니다.
  3. 실행: 클라이언트는 실제 데이터 값을 매개변수로 서버에 전달하고, 서버는 이 매개변수를 사용해 준비된 쿼리를 실행합니다.

장점

  • 보안 강화: 사용자 입력을 쿼리의 일부로 직접 조합하지 않고, 매개변수로 전달하기 때문에 SQL 인젝션 공격을 방지할 수 있습니다.
  • 성능 향상: 동일한 쿼리 구조를 반복해서 사용할 때, 쿼리를 매번 파싱하고 컴파일하는 대신 이미 준비된 쿼리를 재사용함으로써 처리 속도가 빨라집니다.
  • 코드 간결성: 데이터를 쿼리 문자열에 직접 결합하는 대신, 매개변수를 사용하여 보다 명확하고 관리하기 쉬운 코드를 작성할 수 있습니다.

  3.1 PreparedStatement 예제

  • 자바에서 SQL문을 executeUpdate()로 보내는 방법 중 하나이다.
  • Statement는 작은 따옴표를 표기하고 중간중간에 +와 큰따옴표로 구분해 값을 입력해야되서 가독성이 떨어지지만 PreparedStatement는 위 코드처럼 물음표(?)로 변수 위치를 지정해 코드의 사용성이 향상된다.
  • ?는 테이블의 열 이름을 말하며 setString의 인덱스는 1부터 시작한다. (1이 첫번째 열을 의미한다. 2는 두번째 열...)
  • SQL Developer의 테이블을 열어 해당 열의 유형이 VARCHAR2 타입인지 NUMBER 타입인지에 따라 set메서드가 달라지므로 잘 확인해서 PreparedStatement를 사용하자. 데이터 유형이 달라서 생기는 오류는 쉽게 찾기 힘들다.
  • Statement에서는 int count = stmt.executeUpdata(sql);이지만
  • PreparedStatement에서는 int count = pstmt.executeUpdate();이므로 구문오류가 나지 않게 하자.
  • pstmt = con.prepareStatement(sql);에서 이미 매개변수를 전달 받은 상태이기 때문이 이런 차이가 생긴다.