티스토리 뷰

CS/인터넷DB응용

[인터넷DB응용] SQL 활용 - 3

JeongeunChoi 2023. 4. 14. 02:04

트랜잭션

자료 항목을 읽고 또한 때로는 값을 고치기도 하는 프로그램 처리(수행) 단위이다.
트랜잭션 시작.

select ...
select ...
insert ...
delete ...
select ...
delete ...

트랜잭션 끝

 

 

트랜잭션의 예

  • 은행
    • 돈을 찾는다, 돈을 넣는다, 돈을 옮긴다, 새로 계좌를 만든다, 있던 계좌를 없앤다.
  • 비행기
    • 좌석을 예약한다, 취소한다, 날짜를 바꾼다, 편 번호(flight number)를 바꾼다.
  • 기차
    • 좌석을 예약한다, 취소한다, 열차 번호를 바꾼다.
  • 주식
    • 주식을 사는 일, 파는 일, 사거나 파는 일 취소

✅ 데이터베이스에서는 일관성이 굉장히 중요하다.

 

트랜잭션의 네 가지 특성: ACID

  • 원자성(atomicity)
    • All or Nothing
    • 한 트랜잭션 내에 있는 sql은 전부 실행되거나 전부 실행되지 않아야 한다.
  • 일관성(consistency)
    • 트랜잭션이 성공적으로 완료되면 일관적인 DB 상태를 유지
    • 트랜잭션 완료 후에는 데이터의 일관성이 계속해서 유지되어야 한다.
  • 독립성(isolation)
    • 다른 트랜잭션의 작업이 끼어들지 못하도록 보장
    • 하나의 트랜잭션이 다른 트랜잭션에 영향을 미쳐서는 안된다.
  • 내구성(durability)
    • 성공적으로 수행된 트랜잭션은 영원히 반영
    • 커밋하면 데이터가 업데이트 되어야 함.

 

트랜잭션의 처음을 나타내는 SQL 명령은 없다.

  • 보기를 들어, begin-transaction 또는 start-transaction과 같은 SQL 명령이 없다는 뜻
  • 프로그램의 처음에 트랜잭션이 묵시적으로 시작하고, 프로그램의 마지막에 묵시적으로 트랜잭션이 commit 된다.
  • 상호 작용적인 방식(interactive mode)에서는 SQL 해석기에 들어갔을 때 시작
  • 접속 후 sql로 데이터를 건드리기 시작하면 새로운 트랜잭션이 시작되는 것

 

SQL에서 트랜잭션을 나타내기

  • 트랜잭션의 끝을 의미하는 SQL문
  • 트랜잭션 완료 명령어: 커밋, 롤백
  • commit
    • 현재의 트랜잭션을 데이터베이스에 반영시킨 뒤, 새 트랜잭션을 시작한다.
    • 현재 변경된 데이터를 실제 반영해라.
  • rollback
    • 현재의 트랜잭션을 취소한 뒤, 새 트랜잭션을 시작한다.

 

 

뷰(View, Virtual Relation)

  • 사용자 레벨에서 보는 테이블로 논리 레벨에서 저장되지 않는 것
  • 바탕 테이블(Base relation)들로부터 연산의 결과 테이블이 View로 지정되는 경우가 많음
  • 흔히 바탕 테이블은 관계형 데이터베이스의 설계 규칙에 따라(정규형에 맞게) 설계하고 응용 프로그램에서 필요한 테이블은 질의어를 이용하여 View의 형태로 만들어서 사용자에게 제공한다.
  • 실제 테이블은 아니지만 테이블처럼 보이고 테이블처럼 사용할 수 있다.
  • 데이터베이스를 세 개의 계층으로 나누어 설명할 때
    • 물리레벨: 저장장치에 저장되는 형태 ex) 파일
    • 논리레벨: 테이블단계
  • 테이블에 저장된 데이터를 가져와 사용자에게 유용한 형태로 제공되는 것
    • 쉽게 말해, 데이터베이스와 연결된 프로그램들은 대부분 뷰

 

✅ Relation: 테이블, Virtual Relation: 가상 테이블 = View

 

View를 지원하는 방법

  1. View의 정의만 저장하는 방법
    • select 문을 저장
    • 뷰를 불러오면 정의된 select 문이 실행됨.
    • 장점: 데이터가 변화하는 것을 그때그때 적용 가능.
    • 단점: 매번 계산해야 함.
  2. View를 미리 계산하여 결과를 저장해 두는 방법
    • 장단점: 1번 방식과 반대

1번 방식을 많이 사용하지만 2번 방식도 사용함.

 

  • View update
    • View에 있는 레코드를 수정하거나 새로운 레코드를 삽입하거나 레코드를 삭제하면 바탕 테이블에 반영되어야 한다.
  • View Update Anomaly
    • 뷰 갱신 이상 현상. 뷰를 고칠 때 바탕 테이블에 있는 레코드에 이상을 일으키는 경우
  • 특히 둘 이상의 테이블 연산 결과를 바탕으로 한 뷰의 데이터 변동이 있을 때 충돌을 일으키는 경우가 많다. 그래서 DBMS들은 보통 둘 이상의 테이블의 연산으로 만들어진 뷰의 갱신 자체를 허락하지 않는 경우가 많다.

 

뷰의 정의와 삭제

뷰 정의하기
Create view 뷰_이름 as select ~;

뷰 삭제하기
Drop view 뷰_이름;

 

View를 이용한 문제 해결

  • 복잡한 SQL문을 쉽게 만들 때 사용하는 방법: 일부 조건을 이용하여 View를 만든 다음, 나머지 조건을 View에 적용한다.
-- 수강 신청 과목이 두 과목 이상인 학생의 학번과 이름과 과목이름을 보여주는 SQL문을 만드시오.
-- 학번만 보여주는 건 Having 절을 이용하여 쉽게 풀 수 있다.
select 학번 from 수강 group by 학번
having count(과목번호)>=2;
-- 학번과 학생의 이름만 보여주는 건 중첩 SQL문을 이용하여 풀 수 있다.
select 학번, 이름 from 학생
where 학번 in (select 학번 from 수강
group by 학번
having count(과목번호)>=2);
  • 하지만 학생의 이름과 과목이름까지 보여주려면 학생 테이블, 수강테이블, 과목 테이블을 자연 조인한 후, group by와 Having까지 이용하고 여기에 중첩SQL문까지 써야 한다.
    • 이렇게 되면 SQL문을 만드는 것이 굉장히 복잡해진다.
    • 그래서 먼저 자연 조인 결과 뷰를 만든 다음, 조건을 적용하는 것이 비교적 쉽다.
create View 수강신청뷰 as
select 학생.*, 과목.* from 학생, 수강, 과목
where 학생.학번 = 수강.학번
and 과목.과목번호 = 수강.과목번호;
select 학번, 이름, 과목이름 from 수강신청뷰
where 학번 in (select 학번 from 수강 group by 학번
having count(과목번호)>=2);

 

with문을 이용한 inline view

  • 위와 같은 방법으로 풀면 View 객체가 하나 생성되는데 때로는 이를 원치 않을 때도 있다.
  • 이 때 With문을 이용하여 inline view를 만들면 별도의 View가 생성되지 않는다.
  • 별도의 뷰 객체를 만들지 않고 sql 문 실행 후 없어짐.
with
수강신청뷰 as (select 학생.*, 과목.*
				from 학생, 수강, 과목
                where 학생.학번 = 수강.학번
                and 과목.과목번호 = 수강.과목번호)
select 학번, 이름, 과목이름 from 수강신청뷰
where 학번 in (select 학번 from 수강 group by 학번
			   having count(과목번호)>=2);

 

문제

-- 상품, 회원, 주문, 주문상세내역 테이블에서 회원번호가 'T001'인 회원이 주문한 내역의 회원 이름, 주문일, 상품명, 주문액(수량*단가)을 구해 보자.
create view 주문내역뷰 as
select 회원.회원번호, 회원.성명, 상품.상품번호, 상품.상품명, 상품.단가, 주문.주문일, 주문상세내역.수량
from 상품, 회원, 주문, 주문상세내역
where 회원.회원번호=주문.회원번호
and 주문상세내역.주문번호=주문.주문번호
and 주문상세내역.상품번호=상품.상품번호;

select 성명, 주문일, 상품명, 수량*단가 as 주문액
from 주문내역뷰
where 회원번호='T001';

-- 상품, 주문, 주문상세내역 테이블에서 가장 매출이 많았던 날이 언제인지 구해보자.
create view 주문상세내역뷰 as
select 상품.상품번호, 상품.상품명, 상품.단가, 주문.주문일, 주문상세내역.수량
from 상품, 주문, 주문상세내역
where 주문.주문번호=주문상세내역.주문번호
and 상품.상품번호=주문상세내역.상품번호;

create view 날짜별매출액합계뷰 as
select 주문일, sum(수량*단가) as 주문액합계
from 주문상세내역뷰 group by 주문일;

select 주문일, 주문액합계 from 날짜별매출액합계뷰
where 주문액합계 = (select max(주문액합계)
from 날짜별매출액합계뷰);

 

실습 문제

-- 지금까지 우리 쇼핑몰에 가장 많은 돈을 쓴 회원의 회원번호와 성명을 구해 보자.
create view 주문내역들의뷰 as
select 회원.회원번호, 회원.성명, 상품.상품번호, 상품.상품명, 상품.단가, 주문.주문일, 주문상세내역.수량
from 상품, 회원, 주문, 주문상세내역
where 회원.회원번호=주문.회원번호
and 주문상세내역.주문번호=주문.주문번호
and 주문상세내역.상품번호=상품.상품번호;

create view 회원별사용금액 as
select 회원번호, 성명, sum(단가*수량) as 사용금액 
from 주문내역들의뷰
group by 회원번호, 성명;

select 회원번호, 성명, 사용금액 
from 회원별사용금액
where 사용금액 = (select max(사용금액)
from 회원별사용금액);

-- 지금까지 우리 쇼핑몰에서 가장 많은 수입을 올린 상품의 분류가 무엇인지 분류코드와 분류명을 구해 보자.
create view 분류별주문내역뷰 as
select 분류.분류코드, 분류.분류명, 상품.상품번호, 상품.상품명, 상품.단가, 주문상세내역.수량
from 상품, 분류, 주문상세내역
where 상품.분류코드=분류.분류코드
and 주문상세내역.상품번호=상품.상품번호;

create view 분류별매출액뷰 as
select 분류코드, 분류명, sum(단가*수량) as 매출액
from 분류별주문내역뷰
group by 분류코드, 분류명;

select 분류코드, 분류명, 매출액
from 분류별매출액뷰
where 매출액 = (select max(매출액)
from 분류별매출액뷰);

-- 상품별로 주문된 수량의 합계를 구하여 가장 많이 팔린 상품의 상품번호와 상품명을 구해 보자.
create view 상품별주문내역뷰 as 
select 상품.상품번호, 상품.상품명, 주문상세내역.수량
from 상품, 주문상세내역
where 상품.상품번호=주문상세내역.상품번호;

create view 상품별매출액뷰 as
select 상품번호, 상품명, sum(수량) as 주문수량
from 상품별주문내역뷰
group by 상품번호, 상품명;

select 상품번호, 상품명
from 상품별매출액뷰
where 주문수량 = (select max(주문수량)
from 상품별매출액뷰);

 

치환 변수를 이용한 SQL문

치환 변수

  • 동일한 형태의 SQL이지만, SQL이 실행될 때마다 다른 값을 요구하는 것
  • 치환변수 앞에는 &를 붙인다.

 

  • 치환변수를 이용한 select문 예제
select 학번, 이름 from 학생 where 학과번호 = &검색할학과번호;

select * from 학생 where 학과번호 = &원하는학과번호;

insert into 학생 values (&입력할학생의학번, &입력할학생의이름, &입력할학생의학과번호, 0, 0);
  • 주의사항: 문자열 입력할 때 ' 기호는 반드시 붙일 것.

 

Multiple insert

여러 테이블에 한꺼번에 입력

  • 동일한 데이터를 여러 테이블에 동시에 입력할 때 사용하는 구문
  • 또 조건에 따라 다른 테이블에 입력할 때도 사용이 가능하다.
insert [all]
[when 조건절1 then
  into [table1] values (col1, col2, ,)]
[when 조건절2 then
  into [table2] values (col1, col2, ,)]
[else
  into [table2] values (co11, col2, ,)]
[Sub-Query];

 

오라클 내장 함수

  • 기본적인 Query문을 더욱 강렬하게 해준다
  • 질의가 쉬워지고 응용프로그램의 코딩을 줄여줄 수 있다
  • 따라서 함수는 많이 알고 있으면 좋다.

 

함수의 종류

단일 행 함수

  • 하나의 행 당 하나의 결과 값을 반환하는 함수
  • 문자함수
  • 숫자함수
  • 날짜함수
  • 변환함수
  • Regular Expression 함수

복수 행 함수

  • 여러 개의 행 당 하나의 결과 값을 반환하는 함수
  • COUNT: 조건을 만족하는 모든 행의 수를 보여준다.
  • SUM: 조건을 만족하는 모든 행의 합계를 보여준다.
  • MIN/MAX: 조건을 만족하는 모든 행의 최소값/최대값을 보여준다.
  • AVG: 조건을 만족하는 모든 행의 평균을 보여준다.
  • DECODE, CASE문
  • GROUP BY: 결과값을 지정한 컬럼을 기준으로 그룹화
  • HAVING: GROUP BY에 의한 결과에 대한 조건 절
  • ORDER BY: 결과값을 분류(Sorting)할 때
  • STDENV: 조건을 만족하는 모든 행의 표준편차를 보여준다.
  • VARIANCE: 조건을 만족하는 모든 행의 분산값을 보여준다.
DECODE(검색컬럼, 조건-1, 결과값-1,
			조건-2, 결과값-2, ......, 기본값);
            
select count(decode(분류코드, 1, 1)) as 마우스,
	   count(decode(분류코드, 2, 1)) as USB,
       count(decode(분류코드, 3, 1)) as CD
from 상품;

select 상품명, 단가,
decode(분류코드, 1, 단가*1.1,
				 2, 단가*1.15,
                 3, 단가*1.2, 단가) as 바뀔단가
from 상품;

 

RollUp

  • 그룹핑 된 것을 최종 집계하기 위해 발달한 함수
  • 소그룹 간의 소계를 계산
  • 말아올리다 라는 뜻
    • 분류된걸 말아서 모두 합계를 낸다는거죠
  • group by 성별
    • 성별 기준으로 묶어서, 남자 총 몇명, 여자 총 몇명
    • rollup은 여기서 한번 더 나아가서, 그룹별 결과 뿐만 아니라 총 집계도 해줌.
  • group by a, b, c, d로 묶은 뒤 rollup을 적용시켜 주면
    • (a, b, c, d) / (a, b, c) / (a, b) / (a) / () 이런식으로 그룹을 만들어가며 집계를 냄.
select ......
from table_name
group by rollup [컬럼1], [컬럼2], ......;

create view 주문상세내역뷰 as
select 상품.*, 주문.*, 주문상세번호, 수량 from 상품, 주문상세내역, 주문
where 상품.상품번호 = 주문상세내역.상품번호 and 주문.주문번호 = 주문상세내역.주문번호;

select 회원번호, 상품번호, count(*) as 주문횟수, sum(수량*단가) as 주문액합계
from 주문상세내역뷰
group by rollup(회원번호, 상품번호);

 

Cube

  • cube 함수는 group by 항목들 간 모든 경우의 수로 그룹을 생성하여 집계를 냄.
  • cube는 rollup보다 조금 더 상세한 결과를 냄.
  • cube는 rollup과 그룹을 묶어 주는 방식이 다름.
    • (a, b, c) : (a, b, c) / (a. b) / (a, c) / (b, c) / (a) / (b) / (c) / ()
select ......
from table_name
group by cube [컬럼1], [컬럼2], ...;

select 회원번호, 상품번호, count(*) as 주문횟수, sum(수량*단가) as 주문액합계
from 주문상세내역뷰
group by cube(회원번호, 상품번호);

 

'CS > 인터넷DB응용' 카테고리의 다른 글

[인터넷DB응용] PL-SQL - 2  (0) 2023.04.17
[인터넷DB응용] PL-SQL - 1  (0) 2023.04.16
[인터넷DB응용] SQL 활용 - 2  (0) 2023.04.11
[인터넷DB응용] SQL 활용 - 1  (0) 2023.04.11
[인터넷DB응용] 데이터베이스 기본 이론  (0) 2023.04.10