1) 데이터 조작어 - 검색
① SELECT 문
· SELECT 문의 구성 요소
SELECT bookname, publisher
FROM Book
WHERE price >= 10000;
☞ 키워드 : SELECT, FROM, WHERE
☞ 속성 이름 : bookname, publisher
☞ 테이블 이름 : Book
☞ 검색 조건 : price >= 10000
· SELECT 문의 기본 문법
SELECT [ALL or DISTINCT] 속성이름(들)
FROM 테이블이름(들)
▷ WHERE 검색조건(들)
▷ GROUP BY 속성이름
▷ HAVING 검색조건(들)
▷ ORDER BY 속성이름 [ASC(오름차순) or DESC(내림차순)]
▶ 모든 도서의 이름과 가격을 검색하시오
SELECT bookname, price
FROM Book;
▶ 모든 도서의 가격과 이름을 검색하시오
SELECT price, bookname
FROM Book;
▶ 모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오
SELECT bookid, bookname, publisher, price
FROM Book;
SELECT *
FROM Book;
▶ 도서 테이블에 있는 모든 출판사를 검색하시오
SELECT publisher
FROM Book;
※ 중복을 제거하고 싶으면 DISTINCT라는 키워드를 사용한다
SELECT DISTINCT publisher
FROM Book;
② WHERE 조건
· 비교 : =, <>, <, <=, >, >= (문자나 날짜 비교시는 작은 따옴표를 쓴다)
· 범위 : BETWEEN
· 집합 : IN, NOT IN
· 패턴 : LIKE
· NULL : IS NULL, IS NOT NULL
· 복합조건 : AND, OR, NOT
▶ (비교) 가격이 20,000원 미만인 도서를 검색하시오
SELECT *
FROM Book
WHERE price < 20000;
▶ (범위) 가격이 10,000원 이상 20,000원 이하인 도서를 검색하시오
SELECT *
FROM Book
WHERE price BETWEEN 10000 AND 20000;
※ BETWEEN은 논리 연산자인 AND로 대신 사용할 수 있다
SELECT *
FROM Book
WHERE price >= 10000 AND price <= 20000;
★ BETWEEN은 항상 작은 값부터 먼저 쓴다. 그리고 양쪽의 값이 항상 다 포함된다
▶ (집합) 출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오
SELECT *
FROM Book
WHERE publisher IN ('굿스포츠', '대한미디어');
※ 출판사가 '굿스포츠' 혹은 '대한미디어'가 아닌 도서를 검색하시오
SELECT *
FROM Book
WHERE publisher NOT IN ('굿스포츠', '대한미디어');
▶ (패턴) '축구의 역사'를 출간한 출판사를 검색하시오
SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '축구의 역사';
▶ (패턴) 도서이름에 '축구'가 포함된 출판사를 검색하시오
SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '%축구%';
★ % : 아무 문자열이나 대신하는 기호
▶ (패턴) 도서이름의 왼쪽 두 번째 위치에 '구'라는 문자열을 갖는 도서를 검색하시오
SELECT *
FROM Book
WHERE bookname LIKE '_구%';
★ _ : 특정 위치의 한 문자
★★ 와일드 문자의 종류
① + : 문자열을 연결
ex) '골프 '+'바이블' = '골프 바이블'
② % : 0개 이상의 문자열과 일치
ex) '%축구%' : 축구를 포함하는 문자열
③ [] : 1개의 문자와 일치
ex) '[0~5]%' : 0~5 사이 숫자로 시작하는 문자열
④ [^] : 1개의 문자와 불일치
ex) '[^0~5]%' : 0~5 사이 숫자로 시작하지 않는 문자열
⑤ _ : 특정 위치의 1개의 문자와 일치
ex) '_구%' : 두 번째 위치에 '구'가 들어가는 문자열
▶ (복합조건) 축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하시오
SELECT *
FROM Book
WHERE bookname LIKE '%축구%' AND price >= 20000;
▶ (복합조건) 출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오
SELECT *
FROM Book
WHERE bookname LIKE '%축구%' AND price >= 20000;
▶ (복합조건) 출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오
SELECT *
FROM Book
WEHRE publisher = '굿스포츠' OR publisher = '대한미디어';
↓ 같은 속성에서 비교하는 경우는 IN이 좋다
SELECT *
FROM Book
WHERE publisher IN('굿스포츠', '대한미디어');
③ ORDER BY
▶ 도서를 이름순으로 검색하시오
SELECT *
FROM Book
ORDER BY bookname;
▶ 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오
SELECT *
FROM Book
ORDER BY price, bookname;
▶ 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 검색한다
SELECT *
FROM Book
ORDER BY price DESC, publisher ASC;
2) 집계 함수와 GRUOP BY
▶ (운영자 관점) 고객이 주문한 도서의 총 판매액을 구하시오
SELECT SUM(saleprice)
FROM Orders;
※ 의미 있는 열 이름을 출력하고 싶으면 속성이름의 별칭을 지칭하는 AS 키워드를 사용하여 열 이름을 부여한다
SELECT SUM(saleprice) AS "총 매 출" ☞ 별칭에 공백이 있으면 반드시 쌍따옴표를 쓴다
FROM Orders;
▶ 2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오
SELECT SUM(saleprice) AS 총매출
FROM Orders
WHERE custid = 2;
▶ 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오
SELECT SUM(saleprice) AS Total, AVG(saleprice) AS Average, MIN(saleprice) AS Minimum, MAX(saleprice) AS Maxinum
FROM Orders;
▶ 마당서점의 도서 판매 건수를 구하시오
SELECT COUNT(*)
FROM Orders;
★★ 집계함수의 종류
① SUM : SUM([ALL or DISTINCT] 속성이름)
② AVG : AVG([ALL or DISTINCT] 속성이름)
③ COUNT : COUNT(([ALL or DISTINCT] 속성이름 or *))
④ MAX : MAX([ALL or DISTINCT] 속성이름)
⑤ MIN : MIN([ALL or DISTINCT] 속성이름)
▶ 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오
SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액
FROM Orders
GROUP BY custid
ORDER BY custid;
▶ 가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구하시오. 단 두권 이상 구매한 고객만 구한다
SELECT custid, COUNT(*) AS 도서수량
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING count(*) >= 2;
★ custid로 그룹화 했을 때 행의 개수가 2개 이상인 조건
3) 두 개 이상 테이블에서 SQL 질의
① 조인 : Customer 테이블을 Orders 테이블과 조건 없이 연결할 때 2개의 테이블을 합체하는 것
▶ 고객과 고객의 주문에 관한 데이터를 모두 보이시오
SELECT *
FROM Customer, Orders
WHERE Customer.custid = Orders.custid;
★ Custid 또는 Custid_1로 출력됨
▶ 고객과 고객의 주문에 관한 데이터를 고객번호 순으로 정렬하여 보이시오
SELECT *
FROM Customer, Orders
WHERE Cusromer.custid = Orders.custid
ORDER BY Customer.custid;
▶ 고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오
SELECT name, saleprice
FROM Customer, Orders
WHERE Customer.custid = Orders.custid;
▶ 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오
SELECT name, SUM(saleprice)
FROM Customer, Orders
WHERE Customer.custid = Orders.custid
GROUP BY Customer.name;
ORDER BY Customer.name;
▶ (외부조인) 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오
SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders ON Customer.custid = Orders.custid;
★ 자연조인 시 조인에 실패한 투플을 모두 보여주되 값이 없는 대응 속성에는 NULL 값을 채워서 반환(왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인)
※ LEFT(+), RIGHT, FULL
② 부속질의 : SQL 문 내의 또 다른 SQL 문을 작성해보자
▶ 가장 비싼 도서의 이름을 보이시오
SELECT bookname
FROM Book
WHERE price = SELECT MAX(price) FROM Book);
▶ 도서를 구매한 적이 있는 고객의 이름을 검색하시오
SELECT name
FROM Customer
WHERE custid IN SELECT custid FROM Orders;
▶ 대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오
SELECT name
FROM Customer
WHERE custid IN
(SELECT custid FROM Orders WHERE bookid IN
(SELECT bookid FROM Book WHERE publisher = '대한미디어'));
※ 상관 부속질의(correlated subquery)는 상위 부속질의의 투플을 이용하여 하위 부속질의를 계산함. 즉 상위 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련을 맺고 있음
▶ 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오
SELECT b1.bookname
FROM Book.b1
WHERE b1.price >
(SELECT avg(b2.price) FROM Book.b2 WHERE b2.publisher = b1.publisher);
③ 집합연산 : 합집합 UNION, 차집합 MINUS, 교집합 INTERSECT
▶ 도서를 주문하지 않은 고객의 이름을 보이시오
SELECT name
FORM Customer
MINUS
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);
④ EXISTS : 원래 단어에서 의미하는 것과 같이 조건에 맞는 튜플이 존재하면 결과에 포함시킴. 즉 부속질의문의 어떤행이 조건에 만족하면 참임. 반면 NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참임
▶ 주문이 있는 고객의 이름과 주소를 보이시오
SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT * FROM Orders od WHERE cs.custid = od.custid);
