1. SQL

SQL(Structured Query Language)의 의미

• 관계 데이터베이스를 위한 표준 질의어
• SQL은 비절차적인 언어로, 데이터를 조회하거나 조작하는데 필요한 조건을 기술하지만, 어떻게 데이터를 찾고 처리하는지와 같은 실행 절차를 직접 명시하지 않음
  • SELECT 문의 문장 프레임워크

    SELECT: 질의 결과 추출되는 속성 리스트를 열거한다
    FROM: 질의에 어느 테이블이 사용되는지 열거한다
    WHERE: 질의의 조건을 작성한다

  • 예) SQL과 일반 프로그래밍 언어의 차이점

    구분	SQL	일반 프로그래밍 언어
    용도	데이터베이스에서 데이터를 추출하여 문제 해결	모든 문제 해결
    입출력	입력과 출력 모두 테이블	모든 형태의 입출력 가능
    번역	DBMS	컴파일러
    문법	SELECT * FROM Book;	int main() {…}

발전 역사

• SEQUEL(Structured English QUEry Language)에서 유래
  • SEQUEL: 연구용 관계 데이터베이스 관리 시스템인 SYSTEM R을 위한 언어
• 미국 표준 연구소인 ANSI와 국제 표준화 기구인 ISO에서 표준화 작업을 진행
  • 계속 수정 및 보완되고 있음

사용 방식

• 대화식 SQL
  • 데이터베이스 관리 시스템에 직접 접근해 질의를 작성하고 실행
  • 예) 터미널 창에 SELECT * FROM 고객; 이라고 직접 타이핑
• 삽입 SQL
  • 프로그래밍 언어로 작성한 응용 프로그램에 삽입
  • 예) Java로 짠 소스 코드 안에 SQL 문장 삽입

SQL의 분류

• 데이터 정의어(DDL)
  • 테이블을 생성하고 변경, 삭제하는 기능을 제공
  • CREATE, ALTER, DROP 등
• 데이터 조작어(DML)
  • 테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정, 삭제, 검색하는 기능을 제공
  • SELECT, INSERT, UPDATE, DELETE 등
• 데이터 제어어(DCL)
  • 보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능을 제공
  • GRANT, REVOKE 등

2. 데이터 조작어 - 검색

2-1. SELECT 문

SELECT 문

• 데이터를 검색하는 기본 문장으로, 검색한 결과를 테이블 형태로 출력함
• SELECT문 문법 기본형

  SELECT [ALL | DISTINCT] 속성이름(들)   -- 5
  	FROM 테이블이름(들)                -- 1
  	[WHERE 검색조건(들)]               -- 2
  	[GROUP BY 속성이름]                -- 3
  	[HAVING 검색조건(들)]              -- 4
  	[ORDER BY 속성이름 [ASC | DESC]]   -- 6

  • 주의: 절들의 작성 순서가 바뀌면 안 됨
• 질의1) 도서 테이블에 있는 모든 출판사를 검색하세요.

  SELECT DISTINCT publisher
  	FROM Book;

  • ALL(기본값): 결과 테이블이 튜플의 중복을 허용하도록 지정
  • DISTINCT: 결과 테이블이 튜플의 중복을 허용하지 않도록 지정

WHERE 조건

술어	연산자	사용 예
비교	=, <>, <, <=, >, >=	price < 20000
범위	BETWEEN	price BETWEEN 10000 AND 20000
집합	IN, NOT IN	price IN (10000, 20000, 30000)
패턴	LIKE	bookname LIKE ‘축구의 역사’
NULL	IS NULL, IS NOT NULL	price IS NULL
복합조건	AND, OR, NOT	(price < 20000) AND (bookname LIKE ‘축구의 역사’)

• 비교/범위
  • 질의2) 가격이 10,000원 이상 20,000원 이하인 도서를 검색하세요.

    SELECT *
    	FROM Book
    	WHERE price BETWEEN 10000 AND 20000;
    
    -- 혹은
    
    SELECT *
    	FROM Book
    	WHERE price >= 10000 AND price <= 20000;

• 집합
  • 질의3) 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’가 아닌 도서를 검색하세요

    SELECT *
    	FROM Book
    	WHERE publisher NOT IN ('굿스포츠', '대한미디어');

• 패턴 & 복합조건
  • 와일드 문자의 종류

    와일드 문자	의미	사용 예
    %	0개 이상의 문자열과 일치	‘%축구%’ : 축구를 포함하는 문자열
    [ ]	1개의 문자와 일치	‘[0-5]%’ : 0-5 사이 숫자로 시작하는 문자열
    [^]	1개의 문자와 불일치	‘[^0-5]%’ : 0-5 사이 숫자로 시작하지 않는 문자열
    _	특정 위치에 있는 1개의 문자와 일치	‘_구%’ : 두 번째 위치에 ‘구’가 들어가는 문자열

  • 복합조건으로 활용: LIKE + 와일드문자 사용
  • 질의4) 출판사의 이름이 ‘굿스포츠’이거나 ‘대’로 시작하는 5자리인 도서를 검색하시오.

    SELECT *
    	FROM Book
    	WHERE publisher LIKE '굿스포츠' OR publisher LIKE '대____';
    	-- 참고) WHERE publisher='굿스포츠'; (X)

ORDER BY

• 정렬 검색: SQL 문의 실행 결과를 특정 순서대로 출력
  • 오름차순(기본): ASC / 내림차순: DESC
  • NULL 값은 오름차순에서는 맨 마지막에 출력되고, 내림차순에선 맨 먼저 출력
  • 문자의 경우 숫자, 영문자, 한글 순으로 정렬
• 질의5) 도서를 가격순으로 내림차순 검색하되, 가격이 같으면 출판사를 오름차순으로 출력

  SELECT *
  	FROM Book
  	ORDER BY price DESC, publisher ASC;

2-2. 집계 함수와 GROUP BY

집계함수

• 특정 속성 값을 통계적으로 계산하는 함수
• 특징
  • 열함수라고도 하며, NULL 값은 제외하고 계산함
  • WHERE 절에서는 사용 불가, SELECT나 HAVING 절에서만 사용 가능
• 집계 함수의 종류

  집계 함수	문법	사용 예
  SUM	SUM([ALL | DISTINCT] 속성이름)	SUM(price)
  AVG	AVG([ALL | DISTINCT] 속성이름)	AVG(price)
  COUNT	COUNT({[[ALL | DISTINCT] 속성이름] | * })	COUNT(*)
  MAX	MAX([ALL | DISTINCT] 속성이름)	MAX(price)
  MIN	MIN([ALL | DISTINCT] 속성이름)	MIN(price)

• AS 키워드
  • 속성이나 테이블에 별칭 부여
  • 생략 가능하지만 가독성과 호환성을 위해 AS 명시 권장
  • 별칭에 공백이나 특수 문자가 들어가는 경우 ‘ ‘로 표기해야 함
• 질의6) 고객이 주문한 도서의 총판매액, 평균값, 최저가, 최고가를 구하세요

  -- 아래와 같이 작성 시 속성이름 변경 가능
  SELECT SUM(saleprice) AS Total,
  	   AVG(saleprice) AS Average,
  	   MIN(saleprice) AS Minimum,
  	   MAX(saleprice) AS Maximum
  FROM Orders;

  

COUNT

• 행의 개수를 셈
• COUNT()의 괄호 안에는 * 혹은 특정 속성의 이름이 사용됨
• 특징
  • COUNT(*)는 NULL 여부와 관계없이 전체 튜플의 개수를 카운트
  • COUNT(속성)은 NULL 제외하여 카운트
• 질의7)

  -- 마당서점의 도서 판매건수를 구하세요(실행결과: 10)
  SELECT COUNT(*)
  	FROM Orders;
  
  -- 고객이 구매한 마당서점의 도서 종류 개수를 구하세요(실행결과: 8)
  SELECT COUNT(DISTINCT bookid)
  	FROM Orders;

GROUP BY

• 특정 속성의 값이 같은 튜플을 모아 그룹을 만든 후 검색
• HAVING: GROUP BY 절의 결과에 나타나는 그룹을 제한
• 질의8) 가격이 8,000원 이상인 도서를 구매한 고객에 대해서, 고객별 주문도서의 총수량을 구하세요. 단, 2권 이상 구매한 고객에 대해서만 구하고, 고객 아이디를 기준으로 오름차순 출력.

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

GROUP BY & HAVING 절 주의사항

• GROUP BY <속성>

  • GROUP BY를 사용해 튜플을 그룹으로 묶으면 SELECT 절에는 GROUP BY에서 사용한 <속성>과 집계 함수만 나올 수 있다

    -- 맞는 예
    SELECT custid, SUM(saleprice)
    	FROM Orders
    	GROUP BY custid;
    
    -- 틀린 예
    SELECT bookid, SUM(saleprice) -- SELECT 절에는 bookid 속성이 올 수 없다
    	FROM Orders
    	GROUP BY custid;

• HAVING <검색조건>

  • WHERE 절과 HAVING 절이 같이 포함된 SQL 문은 검색 조건이 모호해질 수 있다.

  • HAVING 절은

    • 반드시 GROUP BY 절과 함께 작성해야 하고
    • WHERE 절보다 뒤에 나와야 하며
    • <검색조건>에는 SUM, AVG, MAX, MIN, COUNT와 같은 집계함수가 와야 한다

    -- 맞는 예
    SELECT custid, COUNT(*) AS 도서수량
    	FROM Orders
    	WHERE saleprice >= 8000
    	GROUP BY custid
    	HAVING COUNT(*) >= 2;
    
    -- 틀린 예
    SELECT custid, COUNT(*) AS 도서수량
    	FROM Orders
    	HAVING COUNT(*) >= 2 -- 순서가 틀렸다
    	WHERE saleprice >= 8000
    	GROUP BY custid;

2-3. 두 개 이상의 테이블을 이용한 SQL 질의

조인 문법

• 일반적인 조인

  • SQL 문에서는 주로 동등조인을 사용한다

  • 다음 두 가지 문법 중 하나를 사용할 수 있다

    SELECT <속성들>
    	FROM 테이블1, 테이블2
    	WHERE <조인조건> AND <검색조건>
    
    -- 또는
    
    SELECT <속성들>
    	FROM 테이블1
        	   INNER JOIN 테이블2
            ON <조인조건>
    	WHERE <검색조건>

• 외부 조인

  • 외부조인이면 FROM 절에 조인 종류를 적고 ON을 이용하여 조인조건을 명시한다

  • FULL OUTER JOIN은 SQL 표준 문법에는 있지만 MySQL은 지원하지 않는다

    SELECT <속성들>
    	FROM 테이블1
            {LEFT | RIGHT | FULL[OUTER]} JOIN 테이블2
            ON <조인조건>
    	WHERE <검색조건>

조인

• 한 테이블의 행을 다른 테이블의 행에 연결해 두 개 이상의 테이블을 결합하는 연산
• 테이블을 조건 없이 연결하면 카티션 프로덕트(x)가 되며, 비효율적인 방식이다.
• 질의9) 세 개의 테이블을 이용해, 고객의 이름과 고객이 주문한 도서의 이름을 구하세요.

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

외부 조인

• 조인 조건을 만족하지 않는 튜플에 대해서도 검색을 수행
• LEFT | RIGHT | FULL OUTER JOIN과 ON 키워드를 이용해 작성
  • MySQL에서는 FULL OUTER JOIN을 지원하지 않기 때문에 LEFT, RIGHT JOIN을 UNION하여 사용
• 질의10) 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하세요.

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