• SQL의 데이터 조작 기능
• 데이터 검색, 새로운 데이터 삽입, 데이터 수정, 데이터 삭제
• 데이터 검색 : SELECT 문
• (1) 기본 검색
- All : 결과 테이블이 투플의 중복을 허용
- DISTINCT : 결과 테이블이 투플의 중복을 허용하지 않음
예) 고객 테이블에서 고객아이디, 고객이름, 등급 속성을 검색해보자.
SELECT 고객아이디, 고객이름, 등급
FROM 고객;
• 모든 속성을 검색할 때는 모든 속성의 이름을 나열하지 않고 * 사용 가능
• AS 키워드를 이용해 결과 테이블에서 속성의 이름을 바꾸어 출력 가능 (예. SELECT 제품명, 단가 AS 가격 FROM 제품;)
• (2) 산술식을 이용한 검색
예) 제품 테이블에서 제품명과 단가 속성을 검색하되, 단가에 500원을 더해 조정단가라는 새 이름으로 출력해보자.
SELECT 제품명, 단가 + 500 AS 조정단가 FROM 제품;
• (3) 조건 검색
예) 제품 테이블에서 한빛제과가 제조한 제품의 제품명, 재고량, 단가를 검색해보자.
SELECT 제품명, 재고량, 단가
FROM 제품
WHERE 제조업체 = '한빛제과';
예) 주문 테이블에서 apple 고객이 15개 이상 주문한 주문제품, 수량, 주문일자를 검색해보자.
SELECT 주문제품, 수량, 주문일자
FROM 주문
WHERE 주문고객 = 'apple' AND 수량 >= 15;
• (4) LIKE를 이용한 검색
- LIKE 키워드를 이용해 부분적으로 일치하는 데이터를 검색
- 문자열을 이용하는 조건에만 LIKE 키워드 사용 가능
예) 고객 테이블에서 성이 김씨인 고객의 고객이름, 나이, 등급, 적립금을 검색해보자.
SELECT 고객이름, 나이, 등급, 적립금
FROM 고객
WHERE 고객이름 like '김*'
예) 고객 테이블에서 고객아이디가 5자인 고객의 고객아이디, 고객이름, 등급을 검색해보자.
SELECT 고객아이디, 고객이름, 등급
FROM 고객
WHERE 고객아이디 like '?????'
• (5) NULL을 이용한 검색
- IS NULL 키워드를 이용해 검색 조건에서 특정 속성의 값이 널 값인지를 비교
- IS NOT NULL 키워드를 이용하면 특정 속성의 값이 널 값이 아닌지를 비교
예) 고객 테이블에서 나이가 아직 입력되지 않은 고객의 고객이름을 검색해보자.
SELECT 고객이름
FROM 고객
WHERE 나이 IS NULL;
• (6) 정렬 검색
예) 고객 테이블에서 고객이름, 등급, 나이를 검색하되, 나이를 기준으로 내림차순 정렬해보자.
SELECT 고객이름, 등급, 나이
FROM 고객
ORDER BY 나이 DESC;
예) 주문 테이블에서 수량이 10개 이상인 주문의 주문고객, 주문제품, 수량, 주문일자를 검색해보자. 단, 주문제품을 기준으로 오름차순 정렬하고, 동일 제품은 수량을 기준으로 내림차순 정렬해보자.
SELECT 주문고객, 주문제품, 수량, 주문일자
FROM 주문
WHERE 수량 >= 10
ORDER BY 주문제품 ASC, 수량 DESC;
• (7) 집계 함수를 이용한 검색
• 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수를 이용
- 집계 함수 : 개수, 합계, 평균, 최대값, 최소값의 계산 기능을 제공
• 집계 함수 사용 시 주의 사항
- 집계 함수는 널인 속성 값은 제외하고 계산함
- 집계 함수는 WHERE 절에서는 사용할 수 없고 SELECT 절이나 HAVING 절에서만 사용 가능
예) 제품 테이블에서 모든 제품의 단가 평균을 검색해보자.
SELECT AVG(단가)
FROM 제품;
예) 한빛제과에서 제조한 제품의 재고량 합계를 제품 테이블에서 검색해보자.
SELECT SUM(재고량) AS '재고량 합계'
FROM 제품
WHERE 제조업체 = '한빛제과';
정확한 개수를 계산하기 위해서는 보통 기본키 속성이나 *를 주로 이용
예) 제품 테이블에서 제조업체의 수를 검색해보자.
SELECT COUNT(DISTINCT 제조업체) AS '제조업체 수'
FROM 제품;
• (8) 그룹별 검색
예) 주문 테이블에서 주문제품별 수량의 합계를 검색해보자.
SELECT 주문제품, SUM(수량) AS 총주문수량
FROM 주문
GROUP BY 주문제품;
예) 제품 테이블에서 제조업체별로 제조한 제품의 개수와 제품 중 가장 비싼 단가를 검색하되, 제품의 개수는 제품수라는 이름으로 출력하고 가장 비싼 단가는 최고가라는 이름으로 출력해보자.
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가
FROM 제품
GROUP BY 제조업체;
예) 제품 테이블에서 제품을 3개 이상 제조한 제조업체별로 제품의 개수와, 제품 중 가장 비싼 단가를 검색해보자.
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가
FROM 제품
GROUP BY 제조업체 HAVING COUNT(*)>=3;
예) 고객 테이블에서 적립금 평균이 1000원 이상인 등급에 대해 등급별 고객 수와 적립금 평균을 검색해보자.
SELECT 등급, COUNT(*) AS 고객수, AVG(적립금) AS 평균적립금
FROM 고객
GROUP BY 등급 HAVING AVG(적립금)>=1000;
예) 주문 테이블에서 각 주문고객이 주문한 제품의 총주문수량을 주문제품별로 검색해보자.
SELECT 주문제품, 주문고객, SUM(수량) AS 총주문수량
FROM 주문
GROUP BY 주문제품, 주문고객;
• (9) 여러 테이블에 대한 조인 검색
• 조인 검색 : 여러 개의 테이블을 연결하여 데이터를 검색하는 것
• 조인 속성 : 조인 검색을 위해 테이블을 연결해주는 속성
- 연결하려는 테이블 간에 조인 속성의 이름은 달라도 되지만 도메인은 같아야 함
- 일반적으로 외래키가 조인 속성으로 이용됨
• FROM 절에 검색에 필요한 모든 테이블을 나열
• WHERE 절에 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시
• 같은 이름의 속성이 서로 다른 테이블에 존재할 수 있기 때문에 속성 이름 앞에 해당 속성이 소속된 테이블의 이름을 표시
예) 주문.주문고객
FROM 고객, 주문
WHERE 고객.고객아이디 = 주문.주문고객
=
FROM 고객 INNER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객
• (10) 부속 질의문을 이용한 검색
• SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의
- 상위 질의문 : 다른 SELECT 문을 포함하는 SELECT 문
- 부속 질의문 : 다른 SELECT 문 안에 내포된 SELECT 문
• 괄호로 묶어서 작성
• 단일 행 부속 질의문 : 하나의 행을 결과로 반환
• 다중 행 부속 질의문 : 하나 이상의 행을 결과로 반환
• 부속 질의문과 상위 질의문을 연결하는 연산자가 필요
- 단일 행 부속 질의문은 비교연산자 사용 가능
예) 판매 데이터베이스에서 달콤비스켓과 같은 제조업체에서 제조한 제품의 제품명과 단가를 검색해보자.
SELECT 제품명, 단가
FROM 제품
WHERE 제조업체 = (SELECT 제조업체
FROM 제품
WHERE 제품명 = '달콤비스켓')
예) 판매 데이터베이스에서 적립금이 가장 많은 고객의 고객이름과 적립금을 검색해보자.
SELECT 고객이름, 적립금
FROM 고객
WHERE 적립금 = (SELECT MAX(적립금) FROM 고객);
예) 판매 데이터베이스에서 banana 고객이 주문한 제품의 제품명과 제조업체를 검색해보자.
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN (SELECT 주문제품
FROM 주문
WHERE 주문고객 = 'banana');
예) 판매 데이터베이스에서 banana 고객이 주문하지 않은 제품의 제품명과 제조업체를 검색해보자.
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 NOT IN (SELECT 주문제품
FROM 주문
WHERE 주문고객 = 'banana');
예) 판매 데이터베이스에서 대한식품이 제조한 모든 제품의 단가보다 비싼 제품의 제품명, 단가, 제조업체를 검색해보자.
SELECT
제품명, 단가, 제조업체
FROM
제품
WHERE
단가 > ALL (SELECT 단가
FROM 제품
WHERE 제조업체 = '대한식품');
• 데이터 삽입 : INSERT 문
• 데이터 직접 삽입
예) 판매 데이터베이스의 고객 테이블에 고객아이디가 strawberry, 고객이름이 최유경, 나이가 30세, 등급이 vip, 직업이 공무원, 적립금이 100원인 새로운 고객의 정보를 삽입해보자. 그런 다음 고객 테이블에 있는 모든 내용을 검색하여 삽입된 새로운 투플을 확인해보자.
INSERT
INTO 고객(고객아이디, 고객이름, 나이, 등급, 직업, 적립금)
VALUES ('strawberry', '최유경', 30, 'vip', '공무원', 100);
SELECT * FROM 고객;
예) 판매 데이터베이스의 고객 테이블에 고객아이디가 tomato, 고객이름이 정은심, 나이가 36세, 등급이 gold, 적립금은 4000원, 직업은 아직 모르는 새로운 고객의 정보를 삽입해보자. 그런 다음 고객 테이블에 있는 모든 내용을 검색하여 삽입된 정은심 고객의 직업 널 값인지 확인해보자.
INSERT
INTO 고객(고객아이디, 고객이름, 나이, 등급, 적립금)
VALUES ('tomato', '정은심', 36, 'gold', 4000);
SELECT * FROM 고객;
• 부속 질의문을 이용한 데이터 삽입
예) INSERT
INTO 한빛제품(제품명, 재고량, 단가)
SELECT 제품명, 재고량, 단가
FROM 제품
WHERE 제조업체 = '한빛제과';
• 데이터 수정 : UPDATE 문
• 테이블에 저장된 투플에서 특정 속성의 값을 수정
예) 제품 테이블에서 제품번호가 p03인 제품의 제품명을 통큰파이로 수정해보자. 그런 다음 제품 테이블의 모든 내용을 검색하여 수정 내용을 확인해보자.
UPDATE 제품
SET 제품명='통큰파이'
WHERE 제품번호='p03';
SELECT * FROM 제품;
예) 제품 테이블에 있는 모든 제품의 단가를 10% 인상해보자. 그런 다음 제품 테이블의 모든 내용을 검색하여 인상 내용을 확인해보자.
UPDATE 제품
SET 단가=단가*1.1;
SELECT * FROM 제품;
예) 판매 데이터베이스에서 정소화 고객이 주문한 제품의 주문수량을 5개로 수정해보자. 그런 다음 주문 테이블의 모든 내용을 검색하여 수정 내용을 확인해보자.
UPDATE 주문
SET 수량=5
WHERE 주문고객 IN (SELECT 고객아이디
FROM 고객
WHERE 고객이름='정소화');
SELECT * FROM 주문;
• 데이터 삭제 : DELETE 문
• 테이블에 저장된 데이터를 삭제
예) 판매 데이터베이스의 주문 테이블에 존재하는 모든 투플을 삭제해보자.
DELETE
FROM 주문;
예) 판매 데이터베이스에서 정소화 고객이 주문한 내역을 주문 테이블에서 삭제해보자.
DELETE
FROM 주문
WHERE 주문고객 IN (SELECT 고객아이디
FROM 고객
WHERE 고객이름='정소화');
'데이터베이스 (한양대 백현미)' 카테고리의 다른 글
| 데이터베이스 강의 11 (0) | 2021.07.21 |
|---|---|
| 데이터베이스 강의 10 (0) | 2021.07.20 |
| 데이터베이스 강의 9 (0) | 2021.07.20 |
| 데이터베이스 강의 8 (0) | 2021.07.20 |
| 데이터베이스 강의 7 (0) | 2021.07.20 |
