D - 44 데이터베이스 pivoting insert, 트랜잭션, 시퀀스

데이터베이스

INSERT

기본적인 구문복습을 하자면,,

INSERT INTO (테이블이름) (칼럼이름) VALUES (값들);

이때, 칼럼 개수와 값의 개수가 일치해야한다.

만약 칼럼을 입력하지 않으면 해당 테이블의 모든 칼럼에 값들이 순차적으로 입력될 것이다.

개수가 맞지 않다면 남은 칼럼들은 NULL값이 입력될것이다.

이 때, 개수가 맞는것도 중요하지만 당연하게도 타입또한 중요하다.

 

이제까지의 INSERT는 단일행삽입이였다.

데이터를 관리하는 SQL에서는 당연히 다중행 삽입도 가능하다. 삽입할 행들을 기존 다른 테이블에서 가져오는것 또한 서브쿼리를 이용해 가능하다.

이를 실험하기 위해 TBL_BOARD라는 테이블을 만들었다.(게시판)

사전 데이터가 없기 때문에 직접 5개 행을 입력했고 사전 데이터 밑의 구문으로 했을 시 게시글 번호인 BNO가 똑같이 복사되기 때문에

바로 아래에 있는 쿼리를 이용해 20000개 정도의 게시글을 만들었다.

다중행 삽입시에도 당연히 칼럼의 갯수와 타입을 신경써야한다.

게시판의 페이징은 ROWNUM을 사용하면 가능하다.

위의 쿼리는 2페이지를 확인한 것이다.

기본적으로 게시판은 최신글이 맨 위로 오기 때문에 내림차순으로 정렬하였다.

 

다중행 입력을 위한 height_info, weight_info테이블을 생성했다.

 

학년이 2학년에 해당하는 여러 학생들의 학번, 이름과 몸무게 키를 각 테이블에 입력했다.

다중행입력이라 해서 복잡한 것은 없다.

first 키워드를 사용하면 뒤의 조건이 else if처럼 동작한다.

즉 첫번째 조건에 해당하는 사람이 뒤의 조건과는 관계없이 제외된다.

all키워드일 때는 개별의 if문장이라 생각하면 이해하기 수월해진다.

 

pivoting insert

데이터를 분석용 데이터로 변환하는 경우에 유용하게 사용된다.

하나의 행을 여러 행으로 나누거나 그 반대의 경우를 말하는데, 예를 들면 5개의 칼럼으로 구성된 요일별 판매 실적 데이터를 하나의 칼럼으로 통합하거나, 그 반대의 경우를 볼 수 있다.

PIVOTING INSERT 예시 테이블

위와같이 예시 테이블을 만들어 놓고 SALES_DATA로 PIVOTING INSERT를 하면,

이런식으로 자료가 취합된다.

여러 컬럼을 하나로 합쳐 통계를 구하는데 사용된다.

반대로 하나의 컬럼을 여러개로 나누어 분석하는데 용이하다.

두 테이블을 이용해서 출력방법을 달리하는 방법도 있다.

위처럼 NULL값의 특징을 이용해 GROUP BY로 NULL값을 한방에 삭제시킬수 있다.

이점을 이용해 달력을 출력할 수도 있다.

 

데이터 수정

위의 예시처럼 서브쿼리는 다양하게 사용될 수 있다.

비교 대상 컬럼이 두개여도 괄호와 서브쿼리를 통해 충분히 가능하다.

문제만 놓고 보면 어렵다는 생각이 들 수도 있지만 막상 쿼리를 작성해보면 상당히 직관적임을 알 수 있다.

 

트랜잭션

트랜잭션이란 관계형 데이터베이스에서 실행되는 여러 개의 sql명령문을 하나의 논리적 작업 단위로 처리하는 개념을 말한다.

커밋되는 시점을 기준으로 정하며 개발자가 정하는 기준에 따라 여러 트랜잭션이 맞물릴 경우 순서와 규칙을 정할 수 있다.

 

시퀀스

기본 키 값을 자동으로 생성하기 위한 일련번호를 생성하는 객체이다.

여러 테이블에서 공유가 가능하다는 장점이 있다.

현재 시퀀스 값은 currval, 다음 시퀀스 값은 nextval이라는 키워드를 사용한다.

위처럼 하나의 쿼리 안에 만약 여러번 nextval을 사용한다 해도 시퀀스는 한번만 증가한다.

처음 생성한 시퀀스의 증가치, 최소값 최대값 등의 정의는 alter sequence를 사용해 가능하다.

다만 start with으로 정의된 시작값은 수정이 불가능하다.(이미 시퀀스가 진행되었기 때문)

 

테이블 생성시 명명규칙

문자와 숫자, 특수문자를 사용할 수 있고 대소문자 구별이 없다.

소문자로 저장하려면 ""를 사용해야한다.

다만 사용할 때에도 ""를 써야한다는 불편함이 있다.

따로 기본키를 명시하면 해당 컬럼은 기본키로써의 역할을 수행한다.

즉, null값이 들어올 수 없고 고유해야한다.

간단한 서브쿼리라도 테이블생성시에 서브쿼리를 사용한다면 as키워드가 빠지면 안된다.

반드시 거짓인 조건으로 해당 테이블의 데이터는 말고 구조만 가지고 왔다.

 

기본적으로 테이블의 칼럼은 순서를 임의대로 지정하는것이 불가능하다.

하지만 원하는 칼럼 순대로 테이블을 만들어버리고, 기존의 테이블과 테이블의 이름을 맞바꿔버린다면 순서를 임의대로 바꾼것 처럼 보이게 할 수는 있다.

 

오늘은 여기까지!!