Database - # 07_03

7.3 SQL을 이용한 데이터 조작

부속 질의문을 이용한 검색

SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의

상위 질의문(주 질의문, main query): 다른 SELECT 문을 포함하는 SELECT 문

부속 질의문(서브 질의문, sub query): 다른 SELECT 문 안에 들어 있는 SELECT 문

• 괄호로 묶어서 작성, ORDER BY 절을 사용 x, 상위 질의문보다 먼저 수행됨
• 단일 행 부속 질의문: 하나의 행을 결과로 반환
• 다중 행 부속 질의문: 하나 이상의 행을 결과로 반환

부속 질의문을 먼저 수행 -> 그 결과로 상위 질의문 수행 -> 최종 결과 테이블 반환

부속 질의문, 상의 질의문을 연결하는 연산자 필요

• 단일 행 부속 질의문은 비교 연산자(=, <>, >, >=, <, <=) 사용 가능
• 다중 행 부속 질의문은 비교 연산자 사용 불가

예제 7-40

판매(Panmae) 데이터베이스에서 달콤비스킷을 생산한 제조업체가 만든
제품들의 제품명(P_Name)과 단가(P_Danga)를 검색해보자.

'달콤 비스켓'의 제조업체는 '한빛제과' 밖에 없으므로 단일 행 부속 질의문(비교 연산자 '='을 이용)

 

예제 7-41

판매(Panmae) 데이터베이스에서 적립금(C_Reserve)이 가장 많은
고객의 고객이름(C_Name)과 적립금(C_Reserve)을 검색해보자.

최대 적립금은 단일 값으므로 단일 행 부속 질의문(비교 연산자 '='을 이용)

다중 행 부속 질의문에 사용 가능한 연산자

연산자	설명
IN	부속 질의문과 결과 값 중 일치하는 것 O -> 검색 조건 참
NOT IN	부속 질의문의 결과 값 중 일치하는 것 X -> 검색 조건 참
EXISTS	부속 질의문의 결과 값이 하나라도 존재 O -> 검색 조건 참
NOT EXISTS	부속 질의문과 결과 값이 하나도 존재 X -> 검색 조건 참
ALL	부속 질의문의 결과 값 모두와 비교한 결과 참 -> 검색 조건 만족 (비교 연산자와 함께 사용)
ANY or SOME	부속 질의문과 결과 값 중 하나라도 비교한 결과 참 -> 검색 조건 만족 (비교 연산자와 함께 사용)

 

예제 7-42

판매(Panmae) 데이터베이스에서 banana 고객이 주문한 제품의
제품명(P_Name)과 제조업체(P_Company)를 검색해보자.

'banana' 고객이 주문한 제품은 여러 개 -> 다중 행 부속 질의문 ('IN' 연산자 이용)

예제 7-43

판매(Panmae) 데이터베이스에서 banana 고객이 주문하지 않은 제품의
제품명(P_Name)과 제조업체(P_Company)를 검색해보자.

 

예제 7-44

판매(Panmae) 데이터베이스에서 대한식품이 제조한 모든 제품의 단가보다 비싼
제품의 제품명(P_Name), 단가(P_Danga), 제조업체(P_Company)를 검색해보자.

 

예제 7-45

판매(Panmae) 데이터베이스에서 2019년 3월 15일에 제품을 주문한 고객의 고객이름(C_Name)을 검색해보자.

 

예제 7-46

판매(Panmae) 데이터베이스에서 2019년 3월 15일에 제품을 주문하지 않은
고객의 고객이름(C_Name)을 검색해보자.

 

데이터 검색: SELECT문 - 질의 내용은 다양하게 표현 가능하므로 사용자가 선택

예시 7-42를 두 가지 방식으로 표현

7-42 1) 조인 질의를 이용한 SELECT 문

7-42 2) EXISTS 연산자를 이용한 SELECT 문

 

데이터 삽입: INSERT문

데이터 직접 삽입

INSERT INTO 테이블_이름[(속성_리스트)] VALUES(속성값_리스트);

 

• INTO 키워드와 함께 튜플을 삽입할 테이블의 이름과 속성의 이름을 나열                                                                        - 속성 리스트를 생략하면 테이블을 정의할 때 지정한 속성의 순서대로 값이 삽입
• VALUES 키워드와 함께 삽입할 속성 값들을 나열
• INTO 절의 속성 이름과 VALUES 절의 속성 값은 순서대로 일대일 대응되어야 함

예제 7-47

판매 데이터베이스의 고객 테이블에 고객아이디가 strawberry, 고객이름이 최유경, 나이가 30세, 등급이 vip, 직업이 공무원, 적립금이 100원인 새로운 고객의 정보를 삽입해보자. 

 

예제 7-48

판매 데이터베이스의 고객 테이블에 고객아이디가 tomato, 고객이름이 정은심, 나이가 36세, 등급이 gold, 적립금은 4,000원, 직업은 아직 모르는 새로운 고객의 정보를 삽입해보자. 

그런 다음 고객 테이블에 있는 모든 내용을 검색하여, 삽입된 정은심 고객의 직업 속성이 널 값인지 확인해보자.

 

부속 질의문을 이용한 데이터 삽입

 

SELECT 문을 이용해 다른 테이블에서 검색한 데이터를 삽입

INSERT INTO 테이블_이름[(속성_리스트)] SELECT 문;

 

 

데이터 수정: UPDATE문

테이블에 저장된 튜플에서 특정 속성 값 수정

UPDATE 테이블_이름 SET 속성_이름1=값1, 속성_이름2=값2,… [WHERE 조건];

SET 키워드 다음에 속성 값을 어떻게 수정할 것인지를 지정
WHERE 절에 제시된 조건을 만족하는 튜플에 대해서만 속성 값을 수정
 - WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 대상으로 수정

 

예제 7-49

제품(Product) 테이블에서 제품번호(P_ID)가 p03인 제품의 제품명(P_Name)을 통큰파이로 수정해보자.

 

예제 7-50

제품(Product) 테이블에 있는 모든 제품의 단가(P_Danga)를 10% 인상해보자. 
그런 다음 제품 테이블의 모든 내용을 검색하여 인상 내용을 확인해보자.

 

예제 7-51

판매 데이터베이스에서 정소화 고객이 주문한 제품(O_Product)의 주문수량(O_Quantity)을 5개로 수정해보자. 
그런 다음 주문(Order) 테이블의 모든 내용을 검색하여 수정 내용을 확인해보자.

 

데이터 삭제: DELETE문

테이블에 저장된 데이터를 삭제

DELETE FROM 테이블_이름 [WHERE 조건];

WHERE 절에 제시한 조건을 만족하는 튜플만 삭제
WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 삭제해 빈 테이블이 됨

 

예제 7-52

주문(Order) 테이블에서 주문일자(O_Date)가 2019년 5월 22일인 주문내역을 삭제해보자. 
그런 다음 주문 테이블의 모든 내용을 검색하여 삭제 여부를 확인해보자.

 

예제 7-53

판매 데이터베이스에서 정소화 고객이 주문한 내역을 주문(Order) 테이블에서 삭제해보자.

그런 다음 주문 테이블의 모든 내용을 검색하여 삭제 여부를 확인해보자.

 

예제 7-54

판매 데이터베이스의 주문(Order) 테이블에 존재하는 모든 튜플을 삭제해보자. 
그런 다음 주문 테이블의 모든 내용을 검색하여 삭제 여부를 확인해보자.

빈 테이블이 남음 / DROP TABLE과는 다름

 

7.4 뷰

뷰(VIew)

Primary Key의 유무

다른 테이블을 기반으로 만들어진 가상 테이블

데이터 실제 저장 x / 논리적으로만 존재하는 테이블이지만, 일반 테이블과 동일한 방법으로 사용

다른 뷰 -> 새로운 뷰 생성

기본 테이블의 내용을 쉽게 검색 가능 / 기본 테이블(Create Table)의 내용 변화 작업은 제한적

CREATE VIEW 뷰_이름[(속성_리스트)] AS SELECT 문 [WITH CHECK OPTION];

CREATE VIEW 키워드와 함께 생성할 뷰의 이름과 뷰를 구성하는 속성의 이름을 나열

 - 속성 리스트를 생략하면 SELECT 절에 나열된 속성의 이름을 그대로 사용

AS 키워드와 함께 기본 테이블에 대한 SELECT 문 작성

 - SELECT 문은 생성하려는 뷰의 정의를 표현하며 ORDER BY는 사용 불가

WITH CHECK OPTION

 - 뷰에 삽입이나 수정 연산을 할 때 SELECT 문의 where 조건에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하     는 제약조건을 지정

 

예제 7-55

고객(Customer) 테이블에서 등급(C_Grade)이 vip인 고객의 고객아이디(C_ID), 고객이름(C_Name), 나이(C_Age)로 구성된 뷰를 우수고객이라는 이름으로 생성해보자. 그런 다음 우수고객 뷰의 모든 내용을 검색해보자.

뷰가 생성된 후 우수고객 뷰에 vip 등급이 아닌 고객 데이터를 삽입 or 뷰의 정의 조건 위반하는 수정 및 삭제 연산 시도 시 -> 실행 거부(WITH CHECK OPTION)

 

예제 7-56

제품(Product) 테이블에서 제조업체별 제품수로 구성된 뷰를 업체별제품수라는 이름으로 생성해보자.

그런 다음 업체별제품수 뷰의 모든 내용을 검색해보자.

-> 제품수 속성은 기본 테이블인 제품 테이블에 원래 있던 속성이 아니라 집계 함수를 통해 새로 계산된 것

-> 속성 이름을 명확히 제시

 

뷰 활용: SELECT 문

• 뷰는 일반 테이블과 같은 방법으로 원하는 데이터를 검색할 수 있음                                                                                - 뷰에 대한 SELECT 문은 뷰가 가상 테이블이므로, 기본 테이블의 SELECT 문처럼 수행이 가능함.
• 검색 연산은 모든 뷰에 수행 가능

예시 7-57

우수고객 뷰에서 나이가 25세 이상인 고객에 대한 모든 내용을 검색해보자.

 

뷰 활용: INSERT, UPDATE, DELETE 문

뷰에 대한 삽입, 수정, 삭제 연산은 실제로 기본 테이블에 수행 -> 결과적으로 기본 테이블이 변경

뷰에 대한 삽입, 수정, 삭제 연산은 제한적으로 수행

 

예시 7-58

제품 번호가 p08, 재고량이 1,000, 제조업체가 신선식품인 새로운 제품의 정보를 제품1 뷰에 삽입해보자.

그런 다음 제품 1 뷰에 있는 모든 내용을 검색해보자.

INSERT  INTO 제품2  VALUES(‘시원냉면’,1000,’신선식품’);

 

제품2 뷰에 대한 삽입 연산은 실패함(오류 발생)

1. 제품2 뷰는 제품 테이블의 기본키인 제품번호 속성을 포함하고 있지 않기 때문에
2. 제품2 뷰를 통해 새로운 튜플을 삽입하려고 하면
3. 제품번호 속성이 널 값이 되어 삽입 연산에 실패하게 됨

INSERT, UPDATE, DELETE 문 모두
제품 테이블의 기본키 속성을 포함하고 있는 제품1 뷰는 삽입, 수정, 삭제 연산이 수행되지만,

제품2 뷰는 실행되지 않음

• 기본키(제품번호) 속성이 없는 제품2 뷰에서는 어떤 튜플에 대한 삽입.수정.삭제 연산인지 명확히 구분되지 않기 때문임. 즉, 기본키에 널 값이 입력되기 때문임
• 모두 뷰에 대한 삽입·수정·삭제 연산은 실제로 기본 테이블에 수행되므로 결과적으로는 기본 테이블이 변경됨

변경 불가능한 뷰의 특징

• 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
• 기본 테이블에 있던 내용이 아닌 집계 함수로 새로 계산된 내용을 포함하는 뷰
• DISTICNT 키워드를 포함하여 정의한 뷰
• GROUP BY 절을 포함하여 정의한 뷰
• 여러 개의 테이블을 조인하여 정의한 뷰는 변경이 불가능한 경우가 많음

결과적으로 뷰에 대한 삽입.수정.삭제 연산이
 기본 테이블에 대한 연산으로 변환되어 수행됨을 알 수 있음

 

뷰의 장점

질의문을 좀 더 쉽게 작성 가능

데이터 보안 유지에 도움

데이터 편리하게 관리

 

뷰 삭제: DROP VIEW 문

뷰를 삭제해도 기본 테이블은 영향 X

DROP VIEW 뷰_이름;

if) 삭제할 뷰를 참조하는 제약조건 존재?

ex) 삭제할 뷰를 이용해 만들어진 다른 뷰가 존재할 시

 - 뷰 삭제가 수행 X

 - 관련 제약조건 먼저 삭제

 

7.5 삽입 SQL

삽입 SQL의 개념과 특징

삽입 SQL (ESQL) : 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL 문

주요 특징

• 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입 가능
• 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙임
• 프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용할 때는 이름 앞에 콜론(:)을 붙여서 구분

커서(Cursor)

• 수행 결과로 반한된 여러 행을 한 번에 하나씩 가리키는 포인터
• 여러 개의 행을 결과로 반환하는 SELECT 문을 프로그램에서 사용할 때 필요

커서가 필요 없는 삽입 SQL

• CREATE TABLE 문, INSERT 문, DELETE 문, UPDATE 문 - 결과 테이블을 반환하지 않음
• 결과로 행 하나만 반환하는 SELECT 문

입력된 제품번호에 해당되는 제품명과 단가 검색 프로그램

-> 제품 테이블에서 서로 다른 제품은 같은 제품번호를 가질 수 없으므로 

    이 select 문은 하나의 행을 결과로 반환 -> 커서 필요 X

1. 삽입 SQL 문에서 사용할 변수 선언

• 테이블 내에 대응되는 속성과 같은 타입으로 변수의 데이터 타입 선언
• C에서는 문자열의 끝을 표시하는 널 문자("\0")을 포함할 수 있도록 변수 선언 시 대응되는 속성의 문자열 길이보다 한 개 더 길게 선언

2. 검색하고자 하는 제품의 제품번호를 사용자로부터 입력받는 부분

3. 제품 테이블에서 사용자가 입력한 제품번호에 해당하는 제품명, 단가를 검색하여 대응되는 각각의 변수에 저장하는 삽      입 SQL 문

• 변수는 INTO 키워드 다음에 차례대로 나열

4. 검색된 제품명과 단가를 화면에 출력

 

커서가 필요한 삽입 SQL

• SELECT 문의 실행 결과로 여러 행이 검색되는 경우에 한 번 에 한 행씩 차례로 접근할 수 있게 해주는 커서 필요
• 커서 사용을 위한 삽입 SQL 문

 [ 커서 사용하기 전에 커서의 이름과 커서가 필요한 SELECT 문 선언 ]

EXEC SQL DECLARE 커서_이름 CURSOR FOR SELECT 문;

ex) EXEC SQL DECLARE product_cursor CURSOR FOR

                             SELECT 제품명, 단가 FROM 제품

 -> 제품 테이블에서 제품명, 단가를 모두 검색하는 SELCET 문을 위한 커서를 product_cursor라는 이름으로 선언

 

 [ 커서에 연결된 SELECT 문을 실행하는 삽입 SQL 문 ]

EXEC SQL OPEN 커서_이름;

ex) EXEC SQL OPEN product_cursor;

 -> product_cursor라는 이름의 커서에 연결된 SELECT문 실행

 

OPEN 명령어를 이용 

SELECT 문 실행 -> 검색된 행들 반환 -> 반환된 커서는 검색된 행들 중 첫 번째 행의 바로 앞에 위치

검색된 행들을 차례로 처리하기 위해 커서를 이동시키는 명령어는 FETCH

커서를 이동시키는 삽입 SQL 문

• 커서를 이동하여 처리할 다음 행을 가리키도록 하고, 커서가 가리키는 행으로부터 속성 값을 가져와 변수에 저장
• 결과 테이블에는 여러 행이 존재 -> FETCH 문 여러 번 수행해야 함 (for, while 문과 같은 반복문과 함께 사용)

EXEC SQL FETCH 커서_이름 INTO 변수_리스트;

ec) EXEC SQL FETCH product_cursor INTO:p_name, :price;

-> product_cursor 커서를 이동 -> 결과 테이블의 다음 행에 접근 ->제품명 속성의 값을 p_name 변수에 저장, 단가 속성의 값을 price 변수에 저장

 

커서의 사용을 종료하는 삽입 SQL 문

EXEC SQL CLOSE 커서_이름;

ec) EXEC SQL CLOSE product_cursor;

-> product_cursor 커서를 더는 사용하지 않음