3장

데이터베이스 3장

SELECT 문 : 데이터를 검색하는 기본 문장
열 순서는 결과 테이블의 열 순서를 결정

모든 도서의이름과 가격을 검색

SELECT bookname, price
FROM Book;

SELECT *
FROM Book;

Book 테이블의 모든 열을 보여줌

중복을 제거하고 싶으면? DISTINCT

SELECT DISTINCT publisher
FROM Book;

WHERE 조건
조건에 맞는 검색을 할 때 사용
비교, 범위 (BETWEEN),
속성 IN, NOT IN ~~~
LIKE '%축구%'
IS NULL, IS NOT NULL
AND, OR, NOT

가격이 10000원 이상 20000 이하

SELECT *
FROM Book
WHERE price BETWEEN 10000 AND 20000;

WHERE price < 20000;

IN 연산자는 집합의 원소인지 판단하는 연산자

WHERE publisher IN ('굿스포츠', '대한미디어')

출판사가 굿스포츠 혹인 대한미디어인 도서를 검색

LIKE 연산자 -> 문자열의 패턴 비교
'축구의 역사'를 출간한 출판사를 검색하시오

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '축구의 역사';

도서 이름에 '축구'가 포함된 출판사를 검색하시오

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '%축구%';

도서 이름의 왼쪽 두 번째 위치에 '구'라는 문자열을 갖는 도서
SELECT *
FROM Book
WHERE bookname LIKE '_구%';

축구에 관한 도서 중 가격이 20000원 이상인 도서
SELECT *
FROM Book
WHERE bookname LIKE '%축구%' AND price >= 20000;

출판사가 '굿스포츠' 혹인 '대한미디어'인 도서
SELECT *
FROM Book
WHERE publisher = '굿스포츠' OR '대한미디어';

오름차순, 내림차순 정렬 -> ORDER BY
이거 때문에 orders를 쓰는거지

도서를 이름순으로 검색
SELECT *
FROM Book
ORDER BY bookname;

도서를 가격순으로 검색하고 가격이 같으면 이름순으로 검색
SELECT *
FROM Book
ORDER BY price, bookname;

정렬의 기본은 오름차순!
내림차순은 DESC 키워드를 사용
오름차순은 ASC 키워드를 사용

도서를 가격의 내림차순 검색, 가격이 같다면 출판사의 오름차순 검색
SELECT *
FROM Book
ORDER BY price DESC, publisher ASC;
LIMIT 1

그냥 제일 위에거 나온다

집계 함수 -> 하나만 나온다
SUM, AVG, MIN, MAX, COUNT 5개 있다.
AS 키워드와 함께 쓰자

고객이 주문한 도서의 총 판매액을 구하시오.

SELECT SUM(salesprice) AS 총매출
FROM Orders;

2번 김연아 고객이 주문한 도서의 총 판매액

SELECT SUM(salesprice) AS 총매출
FROM Orders
WHERE custid = 2;

마당서점의 도서 판매 건수를 구하시오

SELECT COUNT(*)
FROM Orders;

GROUP BY -> ~별로, ~에 대하여
"속성이 같은 값끼리 그룹을 만들 수 있다"
이 ~별로, ~ 대하여 앞에 조건이 붙는다?
그럼 WHERE, HAVING이랑 같이 쓰면 됨.
GROUP BY에 두 개가 들어갈 수도 있다

고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오

SELECT custid, COUNT(*) AS 도서수량, SUM(salesperson) AS 총액
FROM Orders
GROUP BY custid;

테이블에 custid와 총 수량 총 판매액이 나오게 된다.

가격이 8000원 이상인 도서를 구매한 고객에 대하여
고객별 주문 도서의 총 수량을 구하시오
단, 두 권 이상 구매한 고객만 구하시오

SELECT custid, COUNT(*) AS 도서수량
FROM Orders
WHERE salesprice >= 8000
GROUP BY custid
HAVING count(*) >= 2

HAVING 절은
1. 반드시 GROUP BY 절과 같이 작성
2. WHERE 절보다 뒤에 나옴
3. 검색조건에는 집계함수가 옴

WHERE -> GROUP BY -> HAVING

5. SELECT custid, COUNT(*) AS 도서수량
1. FROM Orders
2. WHERE saleprice >= 8000
3. GROUP BY custid
4. HAVING count(*) > 1
6. ORDER BY custid;

두 개 이상의 테이블에서 SQL 질의
조인(join), 부속질의(subquery)
조인 -> 한 테이블의 행을 다른 테이블의 행에 연결

아무론 조건을 주지 않고 SELECT 시키면
관계대수의 카티전 프로덕트 연산이 됨

SELECT *
FROM Customer, Orders;

<동등 조인>
테이블 이름 . 열 이름

고객과 고객의 주문에 대한 데이터를 모두 보이시오
select *
from Customer, Orders
where Customer.custid = Orders.custid;

고객과 고객의 주문에 관한 데이터를 정렬하여 보이시오
select *
from Customer, Orders
where Customer.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

3개 이상의 테이블을 조인

고객의 이름과 고객이 주문한 도서의 이름을 구하시오

SELECT Customer.name, Book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid = Orders.custid AND
Orders.bookid = Book.bookid

가격이 20000원인 도서를 주문한 고객의 이름과 도서의 이름
SELECT Customer.name, Book.bookanme
FROM Customer, Orders, Book
WHERE Customer.custid = Orders.custid AND
Orders.bookid = Book.bookid AND Book.price = 20000;
3개 조인하고 AND로 조건까지

<외부조인> -> FROM 문에 씀
도서를 구매하지 않은 고객을 포함하여 고객의 이름과
고객이 주문한 도서의 판매가격을 구하라

SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders
ON Customer.custid = Orders.custid

FROM 테이블 1 { LEFT, RIGHT, FULL } OUTER JOIN 테이블 2 ~~ ON 조인 조건

셀프 조인 : 하나의 테이블을 대상으로 조인하는 것
alias를 써야한다. EMP staff, EMP manager

부속 질의 -> SQL 문 내에 또 다른 SQL 문을 작성
괄호 필수겠지?

가장 비싼 도서의 이름을 보이시오
SELECT bookname
FROM Book
WHERE price = (SELECT MAX(price) FROM BOOK);

SELECT 문의 WHERE 절에 다시 SELECT문을 괄호로 묶음

부속질의도 SQL 문이다
SQL 문의 결과는 테이블
단일 행 x 단일 열 -> 스칼라 부속질의
,다중 행 x 단일 열, 단일행 x 다중 열,
다중 행 x 다중 열

도서를 구매한 적이 있는 고객의 이름 검색
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 Orders
WHERE publisher ='대한미디어'));

하위 부속질의를 먼저 실행하고 그 결과를 이용하여
상위 부속질의를 실행한다.

상관 부속질의 (correlated subquery)는
상위 부속질의를 투플을 이용하여 하위 부속질의를 계산
상위 부속질의와 하위 부속질의가 서로 관련을 맺음

출판사 별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오

출판사의 평균 도서 가격을 구하는 부속질의를 만들고
WHERE 절에 b2.publisher = b1.publisher

SELECT b1.bookname
FROM Book b1
WHERE b1.price > (SELECT avg(b2.price)
FROM Book b2
WHERE b2.publisher = b1.publisher)

투플 변수
테이블 이름이 길거나 한 개의 테이블이 SQL 문에 두번 사용될 때
혼란을 피하기 위해 쓰는 테이블의 별칭
-> FROM 절의 테이블 이름 뒤에 표기

한 개의 테이블에서만 결과를 얻는 여러 테이블의 질의는
조인보다 부속질의로 작성하는 것이 편함
아니면 셀프 조인!!

낌새를 어떻게 느껴야하는가?
뭔가 "하나의 테이블에서 처리하는 정보가 여러개"이다.
하면 투플 변수 써서 셀프 조인 해버리자. (매니저)
집계 함수를 쓴다? 부속질의를 하자.

집합 연산 -> UNION

대한민국에서 거주하는 고객의 이름과
도서를 주문한 고객의 이름을 보이시오

SELECT name
FROM Customer
WHERE address LIKE '대한민국%'
UNION
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);

이 UNION에 UNION ALL을 쓰면 중복을 포함하여
모든 결과를 구한다.

Minus 연산 -> NOT IN 연산자
대한민국에서 거주하는 고객의 이름에서
도서를 주문한 고객의 이름을 빼고 보이시오

SELECT name
FROM Customer
WHERE address like '대한민국%' AND
name NOT IN (SELECT name
FROM Customer
WHERE custid IN
(SELECT custid FROM Orders)));

INTERSECT 연산

대한민국에서 거주하는 고객 '중'
도서를 주문한 고객의 이름을 보이시오
SELECT name
FROM Customer
WHERE address LIKE '대한민국%' AND
name IN (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);

데이터를 저장하려면 테이블의 구조를 만들어야함

CREATE 문 : 테이블을 구성

정수형은 INTEGER, 문자형은 VARCHAR
VARCHAR -> 문자열 가변길이 저장 CHAR -> 문자열 고정 길이
DEFAULT, UNIQUE, NOT NULL, CHECK( ), PRIMARY KEY( )
FOREIGN KEY( ) REFERENCES 테이블 (기본키) ON DELETE
3개 중 택1 CASCADE, SET NULL, NO ACTION

다음과 같은 속성을 가진 NewBook 테이블을 생성하시오.
bookid, bookname, publisher, price
제약 조건 넣기 -> 등호는 안쓴다!

CREATE TABLE NewBook (
bookid INTEGER,
bookname VARCHAR(20) NOT NULL,
publisher VARCHAR(20) UNIQUE,
price INTEGER DEFAULT 10000 CHECK(price >= 1000),
PRIMARY KEY (bookname, publisher));

bookname은 NULL 값을 가질 수 없고
publisher는 같은 값이 있으면 안된다 -> UNIQUE
price에 값이 입력되지 않은 경우 기본값 10000 지정
가격은 최소 1000원 이상
InTEGER DEFAULT 10000 CHECK(price >= 1000)

NewOrders 테이블 생성
orderid - INTEGER, 기본키
custid - INTEGER, NOT NULL 제약조건,
외래키(NEWCustomer.cusid, 연쇄 삭제)
bookid - INTEGER, NOT NULL 제약조건
saleprice - INTEGER
orderdate - DATE

CREATE TABLE NewOrders(
orderid INTEGER,
custid INTEGER NOT NULL,
bookid INTEGER NOT NULL,
salesprice INTEGER,
orderdate DATE,
PRIMARY KEY(orderid),
FOREIGN KEY(custid) REFERENCES NewCustomer(custid)
ON DELETE CASCADE);
외래키 : 다른 릴레이션의 기본키를 참조하는 속성
릴레이션 간의 관계를 표현함

참조 무결성 제약조건 (외래키 제약)
자식 릴레이션의 외래키는 부모 릴레이션의 기본키와
도메인이 동일해야 하며, 자식 릴레이션의 값이 변경될 때
부모 릴레이션의 제약을 받음

삭제할 때
RESTRICTED : 자식 릴레이션에서 참조하고 있을 경우
부모 릴레이션의 삭제 작업을 거부
CASCADE : 자식 릴레이션의 관련 투플을 같이 삭제
DEFAULT : 자식 릴레이션의 관련 투플을 미리 설정한 값으로 변경
NULL : 자식 릴레이션의 관련 투플을 NULL 값으로 설정

외래키 제약조건 주의할 점
1. 반드시 참조되는 테이블(부모 릴레이션)이 존재해야 함
참조되는 테이블의 기본키여야 함.
2. 외래키 지정 시 ON DELETE 옵션은 참조되는 테이블의
투플이 삭제될 떄 취할 수 있는 동작을 지정
CASCADE, SET NULL, NO ACTION 3가지 있음

CASCADE는 위의 예시로 보면
NewCustomer의 투플이 삭제되면 참조하는
NewOrders 테이블의 해당 투플이 연쇄 삭제된다.

SET NULL 은 NULL 값으로 바꾼다.
NO ACTION은 아무 행동을 하지 않음

FOREIGN KEY(외래키) REFERENCES 테이블(기본키)
ON DELETE
CASCADE, NO ACTION, SET NULL

ALTER 문
생성된 테이블의 속성과 속성에 관한 제약을 변경
기본키, 외래키도 변경
ADD로 한다!!!
ALTER TABLE 테이블이름 {ADD, MODIFY, DROP COLUMN}
속성명

CREATE TABLE NewBook (
bookid INTEGER,
bookname VARCHAR(20),
publisher VARCHAR(20),
price INTEGER);

NewBook 테이블에 VARCHAR(13)의 자료형을 가진
isbn 속성 추가

ALTER TABLE NewBook ADD isbn VARCHAR(13);

NewBook 테이블의 isbn 속성의 데이터 타입을
INTEGER 형으로 변경
ALTER TABLE NewBook MODIFY isbn INTEGER;

NewBook 테이블의 isbn 속성을 삭제
ALTER TABLE NewBook DROP COLUMN isbn

NewBook 테이블의 bookid 속성에 NOT NULL 제약조건
ALTER TABLE NewBook MODIFY bookid INTEGER NOT NULL;

NewBook 테이블의 bookid 속성을 기본키로 변경
ALTER TABLE NewBook ADD PRIMARY KEY(bookid);
기본키는 ADD로 한다

DROP 문 -> 테이블을 삭제하는 명령
테이블의 구조와 데이터를 모두 삭제함
데이터만 삭제하려면 DELETE 문을 사용
DROP TABLE 테이블이름;

DROP TABLE NewBook;

삭제하려는 테이블의 기본키를 다른 테이블에서
참조하고 있다면 삭제가 거절됨.
삭제하려면 참조하고 있는 테이블부터 삭제해야 함.

CREATE 문은 테이블을 만드는 거라면
이제 테이블에 투플을 삽입, 삭제, 수정해보자

INSERT문
Insert into 테이블( ) values ( )

Book 테이블에 새로운 도서를 삽입
INSERT INTO Book(bookid, bookname, publisher, price)
VALUES (11, '스포츠 의학', '한솔의학서적', 90000);

결과 확인?
SELECT * FROM Book;

(bookid, bookname, publisher, price) 이거 생략 가능함
대신 데이터 입력 순서가 속성의 순서와 일치해야함
( ) 안의 순서를 바꾸면 입력 순서도 바뀔 수 있겠지!
( ) 안에 모든 속성을 안 채워도 된다.
안 채운 값은 NULL로 저장됨.

INSERT 문을 SELECT 문을 이용해 작성
여러 개의 투플을 삽입할 때 사용한다!
테이블에 저장된 데이터를 읽어서 Book 테이블에 넣을 때
이 땐 VALUES를 안 쓴다!

수입도서 목록을 Book 테이블에 모두 삽입
INSERT INTO Book(bookid, bookname, price, publisher)
SELECT bookid, bookname, price, publisher
FROM Improted_book;

UPDATE 문 -> 특정 속성 값을 수정하는 명령
UPDATE 테이블 이름
SET 속성 이름 = 값
WHERE 조건

조건이 없으면 다 바꿔버린다!

Customer 테이블에서 고객번호가 5인 고객의 주소를
'부산으로 변경

SET SQL_SAFE_UPDATES = 0
/* Safe Updates 옵션 미 해제시 실행 */

UPDATE Customer
SET address = '부산'
WHERE custid = 5;

SET SQL_SAFE_UPDATES = 0 란?
UPDATE나 DELETE 수행 시 실수를 방지하기 위해
기본키 속성을 사용해서만 가능하도록 한 안전 옵션

Book 테이블에서 14번 '스포츠 의학' 출판사를
imported_book 테이블의 21번 책의 출판사와 동일하게 변경

UPDATE Book
SET publisher = (SELECT publisher
FROM imported_book
WHERE bookid = '21')
WHERE bookid = '14';

DELETE 문 -> 테이블에 있는 기존 투플을 삭제하는 명령
DELETE FROM 테이블이름
WHERE 조건

조건이 없으면 모든 튜플을 지워버림

Book 테이블에서 도서번호가 11인 도서를 삭제하시오

DELETE FROM Book
WHERE bookid = '11';

모든 고객을 삭제하시오
DELETE FROM Customer;