Database - # 07_02

7.3 SQL을 이용한 데이터 조작

SQL의 데이터 조작 기능

 

고객 테이블 - 예제에 사용할 판매 DB

제품 테이블 - 예제에 사용할 판매 DB

주문 테이블 - 질의에 사용할 판매 DB

데이터 검색: SELECT 문

기본 검색

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

- ALL * 결과 테이블이 튜플의 중복을 허용하도록 지정(생략 가능)

- DISTINCT * 결과 테이블이 튜플의 중복을 허용하지 않도록 지정

 

예제 7-10

고객 (Customer) 테이블에서 고객아이디(C_ID), 고객이름(C_Name), 등급(C_Grade) 속성을 검색해보자.

 

예제 7-11

고객 (Customer) 테이블에서 존재하는 모든 속성을 검색해보자

모든 속성 검색 시 모든 속성 이름 나열하지 않고 * 사용 가능

 

예제 7-13

제품 (Product) 테이블에서 제조업체(P_Company) 를 검색해보자.

결과 테이블에서 제조업체가 중복

 

예제 7-14

제품(Product) 테이블에서 제조업체(P_Company)를 검색하되, ALL 키워드를 사용해보자.

결과 테이블에서 제조업체 중복

 

예제 7-15

제품(Product) 테이블에서 제조업체(P_Company) 속성을 중복 없이 검색해보자.

결과 테이블에서 제조업체 한 번씩만 나타남

 

 

기본 검색

• AS 키워드를 이용 -> 결과 테이블에서 속성 이름 바꾸어 출력 가능
• 새로운 이름에 공백이 포함되어 있으면 큰따옴표(MySQL) or 작은따옴표로 묶어주어야 함
• AS 키워드는 생략 가능 

SELECT   P_Name, P_Danga (AS Price)

FROM   Product;

 

예제 7-16

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

 

 

산술식을 이용한 검색

• SELECT 키워드와 함께 산술식 제시
• 산술식 - 속성 이름 + 산술 연산자와 상수
• 속성의 값이 실제로 변경 X, 결과 테이블에서만 계산된 결과 값 출력

SELECT   P_Name, P_Danga+500 AS "Controlled Danga"

FROM   Product;

 

예제 7-17

제품(Product) 테이블에서 제품명(P_Name)과 단가(P_Danga) 속성을 검색하되, 
단가(P_Danga)에 500원을 더해 ‘조정 단가’(Controlled Danga)라는 새 이름으로 출력.

 

 

조건 검색

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

WHERE 키워드와 함께 비교 연산자와 논리 연산자를 이용한 검색 조건 제시

• 숫자뿐 아니라 문자, 날씨 값 비교도 가능
• 조건에서 문자, 날짜 값은 작은따옴표로 묶어서 표현
• 논리 연산자는 조건을 여러 개 결합 or 조건을 만족하지 않는 데이터 검색 시 이용

 

예제 7-18

제품(Product) 테이블에서 한빛제과가 제조한 제품의
제품명(P_Name),재고량(P_Jaego),단가(P_Danga)를 검색해보자.

 

예제 7-19

주문(Order1) 테이블에서 apple 고객이 15개 이상 주문한
주문제품(O_Product), 수량(O_Quantity), 주문일자(O_Date)를 검색해보자.

 

예제 7-20

주문(Order1) 테이블에서 apple 고객이 주문했거나
15개 이상 주문한 주문제품(O_Product), 수량(O_Quantity), 
주문일자(O_Date), 주문고객(O_Name)을 검색해보자.

 

예제 7-21

제품(Product) 테이블에서 단가(P_Danga)가 2,000원 이상이면서
3,000원 이하인 제품의
제품명(P_Name),단가(P_Danga),제조업체(P_Company)를 검색해보자`````````````````````````````````````

 

 

LIKE 를 이용한 검색

LIKE 키워드를 이용해 부분적으로 일치하는 데이터 검색

문자열을 이용하는 조건에만 LIKE 키워드 사용 가능

 

예제 7-22

고객(Customer) 테이블에서 성이 김 씨인 고객의 고객이름(C_Name), 나이(C_Age), 등급(C_Grade), 적립금(C_Reserve)을 검색해보자

 

예제 7-23

고객(Customer) 테이블에서 고객아이디(C_ID)가 5자인 고객의 고객아이디(C_ID), 고객이름(C_Name), 등급(C_Grade)을 검색해보자

 

LIKE 를 이용한 검색

IS NULL 키워드 이용 -> 특정 속성 값 널 값인지 비교

IS NOT NULL 키워드 이용 -> 특정 속성 값 널 값이 아닌지 비교

 

예제 7-24

고객(Customer) 테이블에서 나이(C_Age)가 아직 입력되지 않은 고객의 고객이름(C_Name)을 검색해보자

 

예제 7-25

고객(Customer) 테이블에서 나이(C_Age)가 이미 입력된 고객의 고객이름(C_Name)을 검색해보자

 

정렬 검색

ORDER BY 키워드를 이용 -> 결과 테이블 내용을 사용자가 원하는 순서로 출력

ORDER BY 키워드와 함께 정렬 기준이 되는 속성과 정렬 방식을 지정

• 오름차순(기본): ASC(ascending order) / 내림차순: DESC(descending order)
• 널 값은 오름차순에서는 맨 마지막에 출력되고,내림차순에서는 맨 먼저 출력됨
• 여러 기준에 따라 정렬하려면 정렬 기준이 되는 속성을 차례대로 제시

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

 

예제 7-26

고객(Customer) 테이블에서 고객이름(C_Name), 등급(C_Grade), 나이(C_Age)를 검색하되,

나이(C_Age)를 기준으로 내림차순 정렬해보자.

 

예제 7-27

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

 

 

집계 함수를 이용한 검색

특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수 이용
 집계 함수(aggregate function)

• 열 함수(column function)라고도 함
• 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공

집계 함수 사용 시 주의 사항
 집계 함수는 널인 속성 값은 제외하고 계산함
 집계 함수는 WHERE 절에서는 사용할 수 없고, SELECT 절이나 HAVING 절에서만 사용 가능

 

예제 7-28

제품(Product) 테이블에서 모든 제품의 단가(P_Danga) 평균을 검색해보자.

 

예제 7-29

한빛제과에서 제조한 제품의 재고량(P_Jaego) 합계를 제품(Product) 테이블에서 검색해보자.

 

예제 7-30

고객(Customer) 테이블에 고객이 몇 명 등록되어 있는지 검색해보자

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

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

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

 

정확한 개수를 계산하기 위해 보통 기본키 속성 or *을 주로 이용

 

예제 7-31

DISTINCT 키워드를 이용 -> 중복 없애고 서로 다른 제조업체의 개수만 계산

 

그룹별 검색

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

GROUP BY 키워드를 이용 -> 특정 속성 값이 같은 튜플 모아 그룹 만들고, 그룹별로 검색

• GROUP BY 키워드와 함께 그룹을 나누는 기준이 되는 속성을 지정
• GROUP BY 키워드가 없는 SELECT 문은 테이블 전체를 하나의 그룹으로 보고 검색하는 것임

HAVING 키워드를 이용해 그룹에 대한 조건을 작성

그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋음

 

예제 7-32

주문(Order1) 테이블에서 주문제품(O_Product)별 수량의 합계를 검색해보자.

 

예제 7-33

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

 

예제 7-34

제품(Product) 테이블에서 제품을 3개 이상 제조한 제조업체(P_Company)별로 제품의 개수와,

제품 중 가장 비싼 단가(P_Danga)를 검색해보자.

 

예제 7-35

고객(Customer) 테이블에서 적립금(C_Reserve) 평균이 1,000원 이상인등급(C_Grade)에 대해 등급(C_Grade)별 고객수와 적립금(C_Reserve) 평균을 검색해보자.

 

예제 7-36

주문(Order1) 테이블에서 각 주문고객(O_Name)이 주문한 제품(O_Product)의 총주문수량을 주문제품(O_Product)별로 검색해보자.

 

#그룹별 검색

SELECT O_Product, O_Name, SUM(O_Quantity) AS 총주문수량 FROM Order1 GROUP BY O_Product;

--> 집계 함수나 GROUP BY 절에 명시된 속성 외의 속성은 SELECT 절에 작성 불가하므로 오류 발생!!

 

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

조인 검색: 여러 개의 테이블을 연결 -> 데이터 검색

조인 속성: 조인 검색을 위해 테이블을 연결

• 연결하려는 테이블 간 조인 속성 이름은 달라도 되지만, 도메인은 같아야 함
• 일반적으로 외래키를 조인 속성으로 이용

조인 검색을 위한 SQL문

• FROM 절에 검색에 필요한 모든 테이블 나열
• WHERE 절에 조인 속성의 값이 같아야 함을 의미하는 조인 조건 제시
• 같은 이름의 속성이 서로 다른 테이블에 존재 가능 -> 속성 이름 앞에 해당 속성이 소속된 테이블 이름 표시

banana 고객이 주문한 제품의 제품명을 검색하려면 제품, 주문 테이블이 필요

 

예제 7-37

판매(Panmae) 데이터베이스에서 banana 고객이 주문한
제품의 이름(P_Name)을 검색해보자.

 

예제 7-38

판매(Panmae) 데이터베이스에서 나이가 30세 이상인 고객이 주문한
제품의 주문제품(O_Prudoct)과 주문일자(O_Date)를 검색해보자.

-> 이름이 같은 속성이 없다면, 테이블 이름 없이 속성 이름으로만 작성 가능

-> 테이블의 이름을 대신하는 단순한 별명 제시 -> 질의문 작성 가능

 

예제 7-39

판매(Panmae) 데이터베이스에서 고명석 고객이 주문한 제품의
제품명(P_Name)을 검색해보자.