8장 - 데이터베이스 언어 SQL (3)

출처 : http://www.kocw.net/home/cview.do?lid=16c382347fbd35d1

데이터베이스의 원리와 응용

 

여러 테이블에 대한 조인 검색

 

• 조인 검색 : 여러 개의 테이블을 연결하여 데이터를 검색하는 것
• 조인 속성 : 조인 검색을 위해 테이블을 연결해주는 속성
  • 연결하려는 테이블 간에 조인 속성의 이름은 달라도 되지만 도메인은 같아야 한다.
  • 일반적으로 외래키가 조인 속성으로 사용된다.
• FROM 절에 검색에 필요한 모든 테이블을 나열
• WHERE 절에 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시
• 같은 이름의 속성이 서로 다른 테이블에 존재할 수 있기 때문에 속성 이름 앞에 해당 속성이 소속된 테이블의 이름을 표시
  • ex) 주문.주문고객

• 그림 7-11 에서 banana라는 고객이 구매한 제품명을 알고싶다고 가정해보자. 주문 테이블이나 제품 테이블 하나 만으로는 원하는 결과를 알 수 없다. 이때, JOIN 을 사용한다.

 

예제 7-36 판매 데이터베이스에서 banana 고객이 주문한 제품의 이름을 검색해보자.

SELECT 제품.제품명
FROM 주문, 제품
WHERE 주문.주문고객 = 'banana' AND 주문.주문제품 = 제품.제품번호;

 

 

예제 7-37 판매 데이터베이스에서 나이가 30세 이상의 고객이 주문한 제품의 주문제품과 주문일자를 검색해보자.

SELECT 주문.주문제품, 주문.주문일자
FROM 고객, 주문
WHERE 고객.나이 >= 30; AND 고객.고객아이디 = 주문.주문고객;

 

 

예제 7-38 판매 데이터베이스에서 고명석 고객이 주문한 제품의 제품명을 검색해보자.

SELECT 제품.제품명
FROM 고객, 제품, 주문
WHERE 고객.고객이름 = '고명석' AND 고객.고객아이디 = 주문.주문고객 AND 주문.주문제품 = 제품.제품번호;

 

 

여러 테이블에 대한 조인 검색을 할 때에는 INNER JOIN .. ON 문법을 사용해도 된다.

 

SELECT *
FROM 고객, 주문
WHERE 고객.고객아이디 = 주문.주문고객

SELECT *
FROM 고객 INNER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객

 

위 두 SQL 문은 동일한 결과를 출력한다.

 

 

부속 질의문을 이용한 검색

 

• SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의
  • 상위 질의문(주 질의문) : 다른 SELECT 문을 포함하는 SELECT 문
  • 부속 질의문(서브 질의문) : 다른 SELECT 문 안에 내포된 SELECT 문
    • 괄호로 묶어서 작성, ORDER BY 절을 사용할 수 없음
    • 단일 행 부속 질의문 : 하나의 행을 결과로 반환
    • 다중 행 부속 질의문 : 하나 이상의 행을 결과로 반환
• 부속 질의문을 먼저 수행하고, 그 결과를 이용해 상위 질의문을 수행한다.
• 부속 질의문과 상위 질의문을 연결하는 연산자가 필요하다.
  • 단일 행 부속 질의문은 비교 연산자(=, <>, <, >, <=, >=) 사용 가능
  • 다중 행 부속 질의문은 비교 연산자 사용 불가

 

예제 7-39 판매 데이터베이스에서 달콤비스켓과 같은 제조업체에서 제조한 제품의 제품명과 단가를 검색해보자.

 

 

 

SELECT 제품명, 단가
FROM 제품
WHERE 제조업체 = (SELECT 제조업체, FROM 제품, WHERE 제품명 = '달콤비스켓');

 

'달콤비스켓'의 제조업체는 '한빛제과' 밖에 없기 때문에 단일 행 부속 질의문이므로 비교 연산자 =를 이용할 수 있다.

 

 

예제 7-40 판매 데이터베이스에서 적립금이 가장 많은 고객의 고객이름과 적립금을 검색해보자.

SELECT 고객이름, 적립금
FROM 고객
WHERE 적립금 = (SELECT MAX(적립금) FROM 고객);

 

이 문제도 최대 적립금은 단일 값이므로 단일 행 부속 질의문이기 때문에 비교 연산자 = 를 이용할 수 있다.

 

 

예제 7-41 판매 데이터베이스에서 banana 고객이 주문한 제품의 제품명과 제조업체를 검색해보자.

SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN (SELECT 주문제품 FROM 주문 WHERE 주문고객 = 'banana');

 

이 문제에 경우 banana 고개이 주문한 제품은 단일 값이 아니므로 다중 행 부속 질의문이기 때문에 비교 연산자를 사용할 수 없다. 그러므로 위에 표 7-7 에 있는 연산자 중 IN을 사용한다.

 

 

예제 7-42 판매 데이터베이스에서 banana 고객이 주문하지 않은 제품의 제품명과 제조업체를 검색해보자.

SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 NOT IN (SELECT 주문제품 FROM 주문 WHERE 주문고객 = 'banana');

 

 

예제 7-43 판매 데이터베이스에서 대한식품이 제조한 모든 제품의 단가보다 비싼 제품의 제품명, 단가, 제조업체를 검색해보자.

SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 > ALL (SELECT 단가 FROM 제품 WHERE 제조업체 = '대한식품');

 

 

데이터 삽입 : INSERT 문

 

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

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

 

예제 7-46 판매 데이터베이스의 고객 테이블에 고객아이디가 strawberry, 고객이름이 최유경, 나이가 30세, 등급이 vip, 직업이 공무원, 적립금이 100원인 새로운 고객의 정보를 삽입해보자. 그런 다음 고객 테이블에 있는 모든 내용을 검색하여 삽입된 새로운 튜플을 확인해보자.

INSERT
INTO 고객(고객아이디, 고객이름, 나이, 등급, 직업, 적립금)
VALUES ('strawberry', '최유경', 30, 'vip', '공무원', 100);
SELECT *
FROM 고객;

 

 

예제 7-47 판매 데이터베이스의 고객 테이블에 고객아이디가 tomato, 고객이름이 정은심, 나이가 36세, 등급이 gold, 적립금이 4000원, 직업은 아직 모르는 새로운 고객의 정보를 삽입해보자. 그런 다음 고객 테이블에 있는 모든 내용을 검색하여 삽입된 정은심 고객의 직업 NULL 값인지 확인해보자.

INSERT
INTO 고객(고객아이디, 고객이름, 나이, 등급, 적립금)
VALUES ('tomato', '정은심', 36, 'gold', 4000);
SELECT *
FROM 고객;

 

INTO와 VALUES 값을 지정해주지 않아도 자동으로 NULL 값이 들어가게 된다.

 

 

혹은 위와 같이 INTO에 고객만 작성하고, VALUES에서 해당 값을 NULL로 입력해줘도 결과는 동일하다. 단, 값을 아예 비워두면 1:1 매칭이 이루어지지 않아 오류가 발생하게 된다.

 

 

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

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

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

 

 

데이터 수정 : UPDATE 문

 

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

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

 

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

 

예제 7-48 제품 테이블에서 제품번호가 p30인 제품의 제품명을 통큰파이로 수정해보자. 그런 다음 제품 테이블의 모든 내용을 검색하여 수정 내용을 확인해보자.

UPDATE 제품
SET 제품명 = '통큰파이'
WHERE 제품번호 = 'p30';
SELECT *
FROM 제품;

 

 

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

UPDATE 제품
SET 단가 = 단가 * 1.1;
SELECT *
FROM 제품;

 

 

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

UPDATE 주문
SET 수량 = 5
WHERE 주문고객 = (
    SELECT 고객아이디
    FROM 고객
    WHERE 고객이름 = '정소화'
);
SELECT *
FROM 주문;

 

주문 테이블에 고객의 아이디만 있고 이름은 없기 때문에 부속 질의문을 사용해서 고객 테이블의 정소화의 고객아이디를 가져온다.

 

 

데이터 삭제 : DELETE 문

 

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

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

• WHERE 절에 제시한 조건을 만족하는 튜플만 삭제
  • WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 삭제해서 빈 테이블이 된다. 즉, 테이블은 삭제가 되지 않지만 테이블 안의 데이터가 모두 삭제된다.

 

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

DELETE
FROM 주문;
SELECT *
FROM 주문;

 

예제 7-53 판매 데이터베이스에서 정소화 고객이 주문한 내역을 주문 테이블에서 삭제해보자. 그런 다음 주문 테이블의 모든 내용을 검색하여 삭제 여부를 확인해보자.

DELETE
FROM 주문
WHERE 주문고객 = (
    SELECT 고객아이디
    FROM 고객
    WHERE 고객이름 = '정소화'
);
SELECT *
FROM 주문;