1. SQL 학습을 위한 준비

  • MySQL 설치 및 설정
  • MySQL Workbench 설치
  • DBeaver 설치
  • IntelliJ IDE와 연동

2. SQL 개요

  • SQL은 1970년대 IBM에서 개발한 관계형 데이터베이스 언어이다.
  • SQL은 완전한 프로그래밍 언어가 아닌 데이터 부속어(data sublanguage)에 해당한다.
  • SQL은 Java나 C등의 클라이언트, 서버 응용 프로그램이나 HTML등 여러 프로그램과 문서들에 삽입되어 사용될 수 있다.

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

구분 SQL 일반 프로그래밍 언어
용도 데이터베이스에서 데이터를 추출 용도가 정해져있지 않음
입출력 입력과 출력 모두 테이블 형태 입력과 출력 형태가 자유로움
번역 DBMS 컴파일러, 인터프리터

SQL 문법 분류

SQL은 기능에 따라 DLL(Data Definition Language, 데이터 정의어), DML(Data Manipulation Language, 데이터 조작어) 와 DCL(Data Control Language, 데이터 제어어) 로 분류된다.

  • DLL(데이터 정의어) : 테이블이나 관계의 구조를 생성하는데 사용, CREATE, ALTER, DROP 문 등이 있다.
  • DML(데이터 조작어) : 테이블에 데이터를 검색, 삽입, 수정하는데 사용, SELECT, ALTER, DROP 문 등이 있다. SELECT 문은 특별히 질의어라고 부른다.
  • DCL(데이터 제어어) : 데이터의 사용 권한을 관리하는데 사용, GRANT, REVOKE 문 등이 있다.

3. 데이터 조작어 - 검색

SELECT 문

  • SELECT 문은 데이터를 검색하는 명령어로, 질의어(query)라고도 부른다.
  • SELECT 문은 검색 결과를 테이블 형태로 출력한다.
/* Book 테이블에서 price 값이 10000 이상인 bookname, price를 가져오는 쿼리문 */
SELECT bookname, publisher
FROM Book
WHERE price >= 10000;
  • SQL문은 세미콜론(;)으로 끝마친다.
  • 세미콜론은 생략 가능하지만 작성해주는 것이 좋다.
  • 주석은 /* 여러줄 주석 */, -- 한 줄 주석 으로 작성한다.
  • SELECT 문에서 열 순서(bookname, publisher)를 바꾸면 출력되는 결과 테이블의 순서도 바뀐다.
/* Book 테이블의 모든 열을 가져오는 쿼리문 */
SELECT *
FROM Book;
  • *를 사용하면 모든 열을 나타낼 수 있다.
/* Book 테이블의 publisher을 중복을 제거하여 가져오는 쿼리문 */
SELECT DISTINCT publisher
FROM Book;
  • DISTINCT 키워드를 사용하여 중복을 제거할 수 있다.

WHERE 문

  • WHERE 문은 검색 조건을 지정할 때 사용한다.
  • 조건에 사용할 수 있는 술어(predicate)는 아래와 같다.
술어(predicate) 연산자
비교 =, <>, <, <=, >, >=
범위 BETWEEN
집합 IN, NOT IN
패턴 LIKE
NULL IS NULL, IS NOT NULL
복합조건 AND, OR, NOT

비교

/* 가격이 20000원 미만인 도서 검색 */
SELECT *
FROM Book
WHERE price < 20000;

범위

/* 가격이 10000원 이상 20000원 이하인 도서 검색 1 */
SELECT *
FROM Book
WHERE price BETWEEN 10000 AND 20000;

/* 가격이 10000원 이상 20000원 이하인 도서 검색 2 */
SELECT *
FROM Book
WHERE price >= 10000 AND price <= 20000;

집합

  • IN, NOT IN 연산자를 사용
/* 출판사가 '굿스포츠' 혹은 '대한미디어'인 도서 검색 */
SELECT *
FROM Book
WHERE publisher IN ('굿스포츠', '대한미디어');

/* 출판사가 '굿스포츠' 혹은 '대한미디어'가 아닌 도서 검색 */
SELECT *
FROM Book
WHERE publisher NOT IN ('굿스포츠', '대한미디어');

패턴

  • LIKE 연산자를 사용
/* '축구의 역사'를 출간한 출판사를 검색 */
SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '축구의 역사';

/* 도서이름에 '축구'가 포함된 출판사를 검색 */
SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '%축구%';
  • 와일드 문자 %는 임의의 문자열을 대신하는 기호이다.
/* 도서이름의 왼쪽 두 번째 위치에 '구'라는 문자열을 갖는 도서를 검색 */
SELECT *
FROM Book
WHERE bookname LIKE '_구%';
  • 와일드 문자 %는 임의의 문자열을 대신하는 기호이다.

복합조건

/* 축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색 */
SELECT *
FROM Book
WHERE bookname LIKE '%축구%' AND price >= 20000;
  • AND, OR, NOT 논리 연산자를 사용한다.

ORDER BY

/* 도서를 이름순으로 검색 */
SELECT *
FROM Book
ORDER BY bookname;
  • ORDER BY 문을 사용하여 검색 결과를 특정 순서대로 출력할 수 있다.
  • DESC 키워드로 내림차순, ASC 키워드로 오름차순으로 출력할 수 있다.

집계 함수

  • 테이블의 각 열에 대해 계산 함수
  • SUM, AVG, MIN, MAX, COUNT 5가지가 있다.
  • AS 키워드로 열 이름을 부여할 수 있다.
/* 주문된 도서의 총 판매액 */
SELECT SUM(saleprice) AS 총매출
FROM Orders;

GROUP BY

  • GROUP BY 문으로 속성 값이 같은 값끼리 그룹을 만들 수 있다.
/* 고객별로 주문한 도서의 총 수량과 총 판매액 */
SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액
FROM Orders
GROUP BY custid;
  • HAVING 문은 GROUP BY 의 결과로 나타나는 그룹을 제한하는 역할을 한다.
/* 가격이 8000원 이상인 도서를 구매한 고객에 대해 고객별 주문 도서의 총 수량을 구함. 단, 두 권 이상 구매한 고객에 대해서만 탐색 */
SELECT custid, COUNT(*) AS 도서수량
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING count(*) >= 2;

JOIN

  • JOIN 은 한 테이블의 행을 다른 테이블의 행에 연결하여 두 개 이상의 테이블을 결합하는 연산이다.

카티전 프로덕트

/* Customer와 Orders 테이블을 합치는 연산 */
SELECT *
FROM Customer, Orders;
  • 조건 없이 테이블을 결합하는 연산

동등조인

/* custid가 같은 항목끼리 연결 후 정렬 */
SELECT *
FROM Customer, Orders
WHERE Customer.custid=Orders.custid
ORDER BY Customer.custid;
  • 동등조건에 의하여 테이블을 결합하는 연산

외부조인

  • 조인하는 여러테이블에서 한 쪽에는 데이터가 있고, 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블의 내용을 모두 출력하는 연산
/* 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하는 연산 */
SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders ON Customer.custid=Orders.custid;

부속질의

  • 하나의 SQL 문 안에 또다른 SQL 문이 삽입되는 것
  • 질의가 중첩되어 있다는 의미에서 중첩질의라고도 한다.
/* 가장 높은 가격의 책의 이름을 구하는 연산 */
SELECT bookname
FROM Book
WHERE price = (SELECT MAX(price) FROM Book);

집합연산

  • 테이블은 투플의 집합이므로 테이블 간 집합 연산을 수행할 수 있다.
  • MySQL에서 합집합 연산은 UNION 을 사용하여 수행할 수 있다.
/* 대한민국에서 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 구하는 연산 */
SELECT name
FROM Customer
WHERE address LIKE '대한민국%'
UNION
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);
  • UNION 대신 UNION ALL을 사용하면 중복을 포함한 결과를 반환한다.

EXISTS

  • 조건에 맞는 투플을 결과에 포함시킨다.
  • 부속질의문 형식이다.
/* 주문이 있는 고객의 이름과 주소를 구하는 연산 */
SELECT name, address
FROM Customer cs
WHERE EXISTS(SELECT *
            FROM Orders od
            WHERE cs.custid=od.custid);

4. 데이터 정의어

CREATE 문

  • 테이블의 구조를 만드는 명령어
  • 테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본키 및 외래키를 정의하는 명령
CREATE TABLE 테이블이름
( {속성이름 데이터타입
    [NULL | NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건]
  }
    [PRIMARY KEY 속성이름(들)]
    [FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
      [ON DELETE {CASCADE | SET NULL}]
)

ALTER 문

  • 테이블의 구조를 변경하는 명령어
  • 생성된 테이블의 속성과 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경한다.
ALTER TABLE 테이블이름
	[ADD 속성이름 데이터타입]
	[DROP COLUMN 속성이름]
	[ALTER COLUMN 속성이름 데이터타입]
	[ALTER COLUMN 속성이름 [NULL | NOT NULL]
	[ADD PRIMARY KEY(속성이름)]
	[[ADD | DROP] 제약이름];

DROP 문

  • 테이블을 삭제하는 명령어
DROP TABLE 테이블명;

5. 데이터 조작어 - 삽입, 수정, 삭제

INSERT 문

  • 테이블에 새로운 투플을 삽입하는 명령어
INSERT INTO 테이블이름[(속성리스트)]
        VALUES (값리스트);

UPDATE 문

  • 특정 속성 값을 수정하는 명령어
UPDATE 테이블이름
SET 속성이름1 = 값1 [, 속성이름1 = 값2, ...]
[WHERE <검색조건>];

DELETE 문

  • 기존 투플을 삭제하는 명령어
DELETE FROM 테이블이름
[WHERE 검색조건];