3장 SQL 기초

• SQL은 데이터베이스에서 데이터를 추출하여 문제 해결하는 것을 목표로 한다.
• 입출력은 테이블에서 이루어지며 DBMS가 SQL사용자의 입력을 번역해 준다.
• sys테이블 : 메타정보를 가지는 테이블

SQL 문을 던질 때 DB가 하는일

• SQL은 세계 표준이 정해져 있지만 DB마다 syntax가 다르게 되어 있는 부분이 있기에 잘 확인해야 한다.

1. syntax 오류가 발생했는지 확인(명령어가 잘못되었는지 아닌지 확인)
2. 메타 데이터(테이블)를 확인한다. (SELECT FROM WHERE 으로 들어온 테이블들이 실제로 존재하는지 확인한다.)
   • 메타 데이터(테이블)은 카탈로그(요약 정보)같은 느낌이라 생각하면 된다.
3. 실행계획을 만든다(execute plan) : DB의 엔진에서(사람의 두뇌와 비슷한느낌) 어떻게 실행해야 효율적으로 결과를 낼 수 있을지 계획을짠다.(disk i/o를 줄이려고 노력한다.)
   • optimizer가 실행 계획을 만든다
   1. 룰 기반으로 만들거나(정해진 원칙에 따라서)
   2. 비용기반으로 만든다.(현재 들어가 있는 데이터의 상태나 상황을 고려하여 계획 - 메타데이터에서 제공하는 정보 사용)
4. 실행 권한을 확인한 후 수행한다.
5. 이렇게 해도 결과가 빠르게 안나올 경우 SQL 튜닝이 필요하다.

SQL 기능에 따른 분류

• 데이터 정의어(DDL) : 테이블이나 관계의 구조를 생성하는 데 사용하며 CREATE, ALTER, DROP 문 등이 있음.
• 데이터 조작어(DML) : 테이블에 데이터를 검색, 삽입, 수정, 삭제하는 데 사용하며 SELECT, INSERT, DELETE, UPDATE 문 등이 있음. 여기서 SELECT 문은 특별히 질의어(query)라고 함.
• 데이터 제어어(DCL) : 데이터의 사용 권한을 관리하는 데 사용하며 GRANT, REVOKE 문 등이 있음.

데이터 정의어와 데이터 조작어의 주요 명령어

데이터 조작어(DML)  - 검색 SELECT

SELECT 문의 구성 요소

SELECT 문의 기본 문법

SELECT [ALL┃DISTINCT] 속성이름(들)

FROM 테이블이름(들)

[WHERE 검색조건(들)]

[GROUP BY 속성이름]

[HAVING 검색조건(들)]

[ORDER BY 속성이름 [ASC┃DESC]]

• 대괄호 안의 SQL 예약어들은 선택적으로 사용된다.
• | 는 선택 가능한 문법을 이야기하고, 이 중 1개만 선택하여 사용할 수 있다.
• DISTINCT 키워드는 중복을 제거하고 싶을 때 사용한다.
• HAVING 절은 GROUP BY에 적용하는 조건이 필요할 때 사용한다.
• FROM WHER은 SELECT절과 묶어서 같이 작성해 주고, HAVING은 GROUP BY절과 묶어서 같이 작성해주어야 한다.

WHERE 조건

WHERE 절에 조건으로 사용할 수 있는 술어

• BETWEEN 은 이상 이하로 같은 값도 포함이다.
• LIKE는 WHERE 문에 bookname LIKE “%축구%”;로 bookname에 축구라는 이름이 들어간 모든 값들을 검색할 수 있다.
• 이렇게 LIKE에 적용할 수 있는 % 와 같은 문자를 와일드 문자라고 한다.

와일드 문자의 종류

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

ORDER BY

EX) 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 검색한다.

위 사진처럼 내림차순(DESC) 오름차순(ASC)으로 정렬이 가능하다.

집계함수

select절에서만 사용 가능하다.

SELECT    SUM(saleprice) AS 총매출 
-- AS를 통해 열 이름 지정이 가능하다
FROM      Orders;

GROUP BY

• customerID에 대해서 Grouy by 하고 싶을 때 SELECT 안에 custoemrID를 넣어주어야 한다. 그렇지 않으면 받아온 데이터의 구분이 불가능해진다.

ex) 8000원 이상인 도서를 구매한 고객에 대해서 고객별 주문 도서의 총수량을 구하시오. 단, 두권 이상 구매한 고객만 구한다.

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

조인

• 각 테이블을 조건 없이 연결하면 cartesian product를 한 결과와 같게 된다.
• cartesian product를 하면 쓸모없고, 잘못된 값들이 만들어지기 때문에 양쪽 테이블에 공통으로 있는 key field(기본키와 외래키)가 같은 경우에 대해 cartesiana product를 적용하는 조건을 부여해 주어야 한다.

고객과 고객의 주문에 관한 데이터를 고객번호 순으로 정렬하여 보이시오.
SELECT * 
FROM customer, orders
WHERE customer.custid = orders.custid
ORDER BY customer.custid;

• 조인은 2개씩 가능하다. 3개의 table을 join 하기 위해서는 두 번의 join 연산이 필요하다.

외부 조인

• 두 테이블을 join 할 때 한 테이블에만 있는 정보도 출력하고 싶을 때 사용한다.
• FROM 절에 사용하며, customer LEFT OUTER JOIN orders를 할 경우 왼쪽 테이블(customer)에 있지만 오른쪽(orders)에 매칭되는 값이 없는 경우에도 customer를 출력해 달라는 뜻이다.

도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 
판매가격을 구하시오.
SELECT customer.name, orders.saleprice
FROM customer LEFT OUTER JOIN 
orders ON customer.custid = orders.custid

집합 연산

합집합 UNION

차집합 MINUS : 오라클은 MINUS지만 SQL 표준에서는 EXCEPT를 사용한다.

교집합 INTERSECT

EX) 도서를 주문하지 않은 고객의 이름을 보이시오

EXISTS

• 원래 단어에서 의미하는 것과 같이 조건에 맞는 튜플이 존재하면 결과에 포함시킴.
• 즉 부속질의문의 어떤 행이 조건에 만족하면 참임. 반면 NOT EXIST는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참임
• 무조건 상관 질의로 사용함
• 그냥 두 테이블을 독립적으로 두고, 있는지 없는지 비교하면서 차이두기
• 카티션프로덕트에 비해 연산이 적다.
• IN은 서브쿼리의 데이터를 모두 비교하지만, EXISTS는 하나라도 있으면 결과에 포함시키고 리턴하기 때문에 데이터가 커질수록 성능이 좋음
• 테이블을 통째로 가지고 와서, 비교하기 때문에 같다 안 같다의 조건이 필요 (WHERE 조건이 추가적으로 반드시 들어가야 한다)

EX) 주문이 있는 고객의 이름과 주소를 보이시오

데이터 조작어(DML) - 삽입, 수정, 삭제

INSERT 문

• 테이블에 새로운 튜플을 삽입하는 명령이다.
• book table의 column의 형식을 확인하고 올바른 순서대로 값을 넣어주어야 한다.

EX) Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하시오. 스포츠 의학은 한솔의학서적에서 출간했으며 가격은 90000원이다.

• 대량 삽입(bulk insert)이란 한꺼번에 여러 개의 튜플을 삽입하는 방법이다.
• 존재하는 table을 통째로 다른 table에 삽입할 수 있다.

UPDATE 문

• 특정 속성 값을 수정하는 명령이다.

EX) Customer 테이블에서 박세리 고객의 주소를 김연아 고객의 주소로 변경하시오.

DELETE 문

• 테이블에 있는 기존 튜플을 삭제하는 명령이다.
• EX) Customer 테이블에서 고객번호가 5인 고객을 삭제하시오.

데이터 정의어(DDL)

CREATE 문

• 테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본키 및 외래키를 정의하는 명령
• PRIMARY KEY는 기본키를 정할 때 사용하고 FOREIGN KEY는 외래키를 지정할 때 사용하며, ON UPDATE와 ON DELETE는 외래키 속성의 수정과 튜플 삭제 시 동작을 나타냄

EX) 다음과 같은 속성을 가진 NewOrders 테이블을 생성하시오

orderid(주문번호) - NUMBER, 기본키

custid(고객번호) - NUMBER, NOT NULL 제약조건, 외래키(NewCustomer.custid, 연쇄삭제)

bookid(도서번호) - NUMBER, NOT NULL 제약조건

saleprice(판매가격) - NUMBER

orderdate(판매일자) - DATE

• default값 지정, unique지정 등의 제약조건도 존재한다.
• 외래키 제약조건을 명시할 때는 반드시 참조되는 테이블(부모 릴레이션)이 존재해야 하며 참조되는 테이블의 기본키여야 함.
• 외래키 지정 시 ON DELETE 또는 ON UPDATE 옵션은 참조되는 테이블의 튜플이 삭제되거나 수정될 때 취할 수 있는 동작을 지정함. NO ACTION은 어떠한 동작도 취하지 않음.

속성 데이터 타입의 종류

ALTER 문

• 생성된 테이블의 속성과 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경함. ADD, DROP은 속성을 추가하거나 제거할 대 사용함.
• MODIFT는 속성의 기본값을 설정하거나 삭제할 때 사용함.
• ADD <제약이름> , DROP <제약이름>은 제약사항을 추가하거나 삭제할 떄 사용함

ALTER 문의 기본 문법

DROP 문

• DROP 문은 테이블을 삭제하는 명령
• DROP 문은 테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야 함(데이터만 삭제하려면 DELETE 문을 사용함)

DROP 문의 기본 문법