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

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

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

 

SQL을 이용한 데이터 조작

 

SQL의 데이터 조작 기능

• 데이터 검색, 새로운 데이터 삽입, 데이터 수정, 데이터 삭제를 할 때 사용한다.
• 이전에 배운 데이터 정의어는 테이블 단위로 생성, 수정, 삭제를 했다. 데이터 조작어는 데이터 단위로 삽입, 수정, 삭제 할 때 사용한다.

 

 

데이터 검색 : SELECT 문

• 기본 검색
  • SELECT 키워드와 함께 검색하고 싶은 속성의 이름 나열
  • FROM 키워드와 함께 검색하고 싶은 속성이 있는 테이블의 이름 나열
  • 검색 결과는 테이블 형태로 반환된다.

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트;

• ALL
  • 결과 테이블이 튜플의 중복을 허용하도록 지정, 생략 가능
• DISTINCT
  • 결과 테이블이 튜플의 중복을 허용하지 않도록 지정

 

 

예제 7-9 SQL

SELECT 고객아이디, 고객이름, 등급 FROM 고객;

 

 

예제 7-10 SQL

SELECT 고객아이디, 고객이름, 나이, 등급, 직업, 적립금 
FROM 고객;

 

혹은

SELECT *
FROM 고객;

 

 

예제 7-12 SQL

SELECT 제조업체
FROM 제품;

 

예제 7-12의 경우 따로 옵션을 정해주지 않았기 때문에 기본적으로 ALL이 디폴트 값으로 지정되서 중복되는 값이 나오게 된다.

 

 

예제 7-14 SQL

SELECT DISTINCT 제조업체
FROM 제품;

 

• 기본 검색
  • AS 키워드를 사용해서 결과 테이블에서 속성의 이름을 바꾸어 출력할 수 있다.

 

예제 7-15

제품 테이블에서 제품명과 단가를 검색하되, 단가를 가격이라는 새 이름으로 출력해보자.

 

예제 7-15 SQL

SELECT 제품명, 단가 AS 가격
FROM 제품;

 

• 산술식을 이용한 검색
  • SELECT 키워드와 함께 산술식 제시
    • 산술식 : 속성의 이름과 +, -, *, / 등의 산술 연산자와 상수로 구성
  • 속성의 값이 실제로 변경되는 것은 아니고 결과 테이블에서만 계산된 결과 값이 출력된다.

 

예제 7-16

제품 테이블에서 제품명과 단가 속성을 검색하되, 단가에 500원을 더해 조정단가라는 새 이름으로 출력해보자.

 

예제 7-16 SQL

SELECT 제품명, 단가 + 500 AS 조정단가
FROM 제품;

 

 

• 조건 검색
  • 조건을 만족하는 데이터만 검색

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건];

• WHERE 키워드와 함께 비교 연산자와 논리 연산자를 이용한 검색 조건 제시
  • 숫자 뿐만 아니라 문자나 날짜 값을 비교하는 것도 가능
    • ex) 'A' < 'C', '2013-12-01' < '2013-12-02'
  • 조건에서 문자나 날짜 값은 작은 따옴표로 묶어서 표현

 

 

 

예제 7-17 SQL

SELECT 제품명, 재고량, 단가
FROM 제품
WHERE 제조업체 = '한빛제과';

 

 

예제 7-18 SQL

SELECT 주문제품, 수량, 주문일자
FROM 주문
WHERE 주문고객='apple' AND 수량 >= 15;

 

 

예제 7-19 SQL

SELECT 주문제품, 수량, 주문일자, 주문고객
FROM 주문
WHERE 주문고객='apple' OR 수량 >= 15;

 

 

예제 7-20 SQL

SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 >= 2000 AND 단가 <= 3000;

 

 

• LIKE를 이용한 검색
  • LIKE 키워드를 이용해 부분적으로 일치하는 데이터를 검색
  • 문자열을 이용하는 조건에만 LIKE 키워드 사용 가능
  • 표 안의 키워드 기호는 데이터베이스 제품마다 약간씩 다르다.

 

 

예제 7-21 SQL

SELECT 고객이름, 나이, 등급, 적립금
FROM 고객
WHERE 고객이름 LIKE '김*';

 

 

예제 7-22 SQL

SELECT 고객아이디, 고객이름, 등급
FROM 고객
WHERE 고객아이디 LIKE '?????';

 

 

 

• NULL을 이용한 검색
  • IS NULL 키워드를 이용해 검색 조건에서 특정 속성의 값이 NULL 값인지를 비교
  • IS NOT NULL 키워드를 이용하여 특정 속성의 값이 NULL 값이 아닌지를 비교

 

예제 7-23

SELECT 고객이름
FROM 고객
WHERE 나이 IS NULL;

 

 

예제 7-24 SQL

SELECT 고객이름
FROM 고객
WHERE 나이 IS NOT NULL;

 

 

• 정렬 검색
  • ORDER BY 키워드를 이용해 결과 테이블 내용을 사용자가 원하는 순서로 출력
  • ORDER BY 키워드와 함께 정렬 기준이 되는 속성과 정렬 방식을 지정
    • 오름차순(기본) : ASC
    • 내림차순 : DESC
    • 여러 기준에 따라 정렬하려면 정렬 기준이 되는 속성을 차례대로 제시

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[ORDER BY 속성_리스트 [ASC | DESC]];

 

예제 7-25

고객 테이블에서 고객이름, 등급, 나이를 검색하되, 나이를 기준으로 내림차순 정렬 해보자.

 

예제 7-25 SQL

SELECT 고객이름, 등급, 나이
FROM 고객
ORDER BY 나이 DESC;

 

예제 7-26

주문 테이블에서 수량이 10개 이상인 주문의 주문고객, 주문제품, 수량, 주문일자를 검색해보자. 단, 주문제품을 기준으로 오름차순 정렬하고, 동일 제품은 수량을 기준으로 내림차순 정렬해보자.

 

SELECT 주문고객, 주문제품, 수량, 주문일자
FROM 주문
WHERE 수량 >= 10
ORDER BY 주문제품 ASC, 수량 DESC;

 

 

• 집계 함수를 이용한 검색
  • 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수를 이용
    • 집계 함수(aggregate function) : 개수, 합계, 평균, 최대값, 최소값의 계산 기능을 제공
  • 집계 함수 사용 시 주의 사항
    • 집계 함수는 NULL인 속성 값은 제외하고 계산함
      • ex) [10, 10, 10, 10, NULL] 인 튜플의 평균 값을 계산하면 40 / 4 = 10 이 된다.
    • 집계 함수는 WHERE 절에서는 사용할 수  없고, SELECT 절이나 HAVING 절에서만 사용 가능

 

 

• COUNT, MAX, MIN 은 모든 데이터에서 사용할 수 있지만, SUM, AVG는 숫자 데이터에서만 사용할 수 있다.

 

예제 7-27 제품 테이블에서 모든 제품의 단가 평균을 검색해보자.

SELECT AVG(단가)
FROM 제품;

 

예제 7-28 한빛제과에서 제조한 제품의 재고량 합계를 제품 테이블에서 검색해보자.

SELECT SUM(재고량) AS 재고량 합계
FROM 제품
WHERE 제조업체 = "한빛제과";

 

 

예제 7-29 고객 테이블에 고객이 몇 명 등록되어 있는지 검색해보자.

 

이 문제의 경우 튜플의 값이 NULL 값을 가진 경우도 있으므로 어떤 속성을 기준으로 검색을 하느냐에 따라 결과값이 달라질 수 있다.

 

1. 고객아이디 속성을 이용해 계산하는 경우

SELECT COUNT(고객아이디) AS 고객수
FROM 고객;

 

2. 나이 속성을 이용해 계산하는 경우

SELECT COUNT(나이) AS 고객수
FROM 고객;

 

3. *를 이용해 계산하는 경우

SELECT COUNT(*) AS 고객수
FROM 고객;

 

일반적으로는 기본키(PRIMARY KEY)를 사용하거나 * 를 사용해서 모든 값을 통해 COUNT 해서 구한다. 왜냐하면 기본키는 NULL 값을 가질 수 없기 때문이다.

 

예제 7-30 제품 테이블에서 제조업체의 수를 검색해보자.

 

이 문제의 경우 DISTINCT 키워드를 사용해 중복을 없애고 서로 다른 제조업체의 개수만 계산해야 한다.

SELECT COUNT(DISTINCT 제조업체) AS '제조업체 수'
FROM 제품;

 

 

• 그룹별 검색
  • GROUP BY 키워드를 이용해 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고, 그룹별로 검색
    • GROUP BY 키워드와 함께 그룹을 나누는 기준이 되는 속성을 지정
  • HAVING 키워드를 함께 이용해 그룹에 대한 조건을 작성
  • 그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋음

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[GROUP BY 속성_리스트 [HAVING 조건]]
[ORDER BY 속성_리스트 [ASC | DESC]];

 

 

예제 7-31 주문 테이블에서 주문제품별 수량의 합계를 검색해보자.

 

GROUP BY 에 들어간 속성도 SELECT 문에 넣어줘야 식별이 가능하다.

SELECT 주문제품, SUM(수량) AS '주문제품별 수량'
FROM 주문
GROUP BY 주문제품;

 

 

예제 7-32 제품 테이블에서 제조업체별로 제조한 제품의 개수와 제품 중 가장 비싼 단가를 검색하되, 제품의 개수는 제품수라는 이름으로 출력하고 가장 비싼 단가는 최고가라는 이름으로 출력해보자.

SELECT 제조업체, COUNT(*) AS '제품수', MAX(단가) AS '최고가'
FROM 제품
GROUP BY 제조업체;

 

 

예제 7-33 제품 테이블에서 제품을 3개 이상 제조한 제조업체별로 제품의 개수와, 제품 중 가장 비싼 단가를 검색해보자.

SELECT 제조업체, COUNT(*), MAX(단가)
FROM 제품
GROUP BY 제조업체 HAVING COUNT(*) >= 3;

 

집계 함수를 이용한 조건은 WHERE 절에는 작성할 수 없고 HAVING 절에서 작성한다.

 

 

예제 7-34 고객 테이블에서 적립금 평균이 1000 이상인 등급에 대해 등급별 고객 수와 적립금 평균을 검색해보자.

SELECT 등급, COUNT(*) AS '고객수', AVG(적립금) AS '적립금평균'
FROM 고객
GROUP BY 등급 HAVING AVG(적립금) >= 1000;

 

 

예제 7-35 주문 테이블에서 각 주문고객이 주문한 제품의 총주문수량을 주문제품별로 검색해보자.

SELECT 주문제품, 주문고객, SUM(수량) AS '총주문수량'
FROM 주문
GROUP BY 주문제품, 주문고객;