데이터베이스 개론과 실습 (MySQL로 배우는) | Chapter 03: SQL 기초

Jul, 2023

01. SQL 기초

SQL(Structured Query Language)이란?

• 1970s 후반에 IBM이 개발한 관계형 데이터베이스 언어
• 완전한 프로그래밍 언어(e.g., C, JAVA)는 아니고, 데이터 부속어라고 부름
• : 데이터베이스의 데이터와 메타 데이터(데이터 구조에 관한 데이터)를 생성하고 처리하는 문법만 있음
• DBMS에 직접 입력, 클라이언트/서버 응용 프로그램에 삽입, 웹 페이지 문서에 삽입 등 다양한 방법을 통해 사용할 수 있음

SQL의 기능에 따른 분류

• 데이터 정의어(DDL): 테이블이나 관계의 구조를 생성 - CREATE, ALTER, DROP 문
• 데이터 조작어(DML): 테이블에 데이터를 검색, 삽입, 수정, 삭제 - SELECT(→질의어), INSERT, DELETE, UPDATE
• 데이터 제어어(DCL): 데이터의 사용 권한을 관리 - GRANT, REVOKE 문

SQL 기본 문법

• SQL 문은 세미콜론(;)으로 끝낸다(생략은 가능하다)
• 대소문자의 구분은 없다
• SQL 예약어는 대문자, 테이블이나 속성 이름은 소문자로 적어주는 것이 좋다
• 열의 순서는 결과 테이블의 열 순서를 결정한다
• *(asterisk): 모든 열을 나타낸다
• 기본적으로 중복을 제거하지 않는다
• → DISTINCT 로 중복을 제거한다
• 숫자를 작성할 때 콤마(,)를 쓰지 않는다
• ‘[]’: 선택을 의미
• ‘|’: 둘 중 하나를 선택하라는 의미
• 열 이름이 어느 테이블과 연관되었는지 표기하는 방법: ‘테이블 이름.열 이름’
• 투플 변수: 테이블에 별칭을 붙인 것으로, FROM 절의 테이블 이름 뒤에 표기한다

02. 데이터 조작어 - 검색

SELECT

• 질의어(query)라고 특별하게 부름
• 검색한 결과를 테이블 형태로 출력
• 기본 문법

SELECT [ALL | DISTINCT] 속성이름(들)
FROM 테이블이름(들)
[WHERE 검색조건(들)]
[GROUP BY 속성이름]
[HAVING 검색조건(들)]
[ORDER BY 속성이름[ASC | DESC]]

# []: 대괄호 안의 SQL 예약어들은 선택적으로 사용한다.
# | : 선택 가능한 문법들 중 한 개를 사용할 수 있다.

WHERE 조건

• 비교 : =, <>, <, <=, >, ≥
• 범위 : BETWEEN
• 집합: IN, NOT IN
• 패턴: LIKE
  • 찾는 속성이 텍스트 혹은 날짜 데이터를 포함하면 비교 값을 영문 작은 따옴표(’ ‘)로 둘러싸야 함
  • 와일드 문자를 함께 사용할 수 있음

    와일드 문자	의미
    +	문자열을 연결
    %	0개 이상의 문자열과 일치
    []	1개의 문자와 일치
    [^]	1개의 문자와 불일치
    _	특정 위치의 1개의 문자와 일치

• NULL: IS NULL, IS NOT NULL
• 복합조건: AND, OR, NOT

ORDER BY

• SQL 문의 실행 결과를 특정 순서대로 출력할 때 사용
• 열을 여러 번 정렬하는 것도 가능하다
• ORDER BY price, bookname; # 가격순으로 먼저 정렬한 후, 가격이 같은 도서에 대해 이름순으로 정렬

ORDER BY price, bookname; # 가격순으로 먼저 정렬한 후, 가격이 같은 도서에 대해 이름순으로 정렬

• 기본적으로는 오름차순으로 정렬해주고, 내림차순으로 정렬하기 위해서는 DESC를 추가함

ORDER BY price DESC, publisher ASC; # 내림차순 후 오름차순으로 정렬하는 방법

집계 함수(aggregate function)

• 테이블의 각 열에 대해 계산하는 함수
• 종류: SUM, AVG, MIN, MAX, COUNT(행의 개수를 셈)
• 집계 함수의 결과에 AS 키워드를 사용해서 의미 있는 열 이름을 부여할 수 있다

SELECT SUM(saleprice) AS 총 매출

GROUP BY

• 속성이 같은 값끼리 그룹을 만들 때 사용
• HAVING을 추가하면, GROUP BY 절의 결과 나타나는 그룹을 제한하는 역할을 한다

# 가격이 8,000원 이상인 도서를 구매한 고객에 대해, 고객별 주문 도서의 총 수량을 구하시오.
# 단, 두 권 이상 구매한 고객만 구하시오
SELECT custid, COUNT(*) AS 도서수량
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING count(*) >= 2;

두 개 이상 테이블에서 SQL 질의 → 조인(join)

• 한 테이블의 행을 다른 테이블의 행에 연결하여 두 개 이상의 테이블을 결합하는 연산
• 두 테이블을 아무런 조건 없이 SELECT 시키면 카티전 프로덕트 연산이 된다

동등조인(equi join)

• 동등 조건에 의해 테이블을 조인하는 것
• 동등조인을 사용하면 두 개 이상의 테이블에서 일부 데이터만 얻을 수 있다
• 두 가지 문법 중 하나를 사용할 수 있다

# 문법 1
SELECT <속성들>
FROM 테이블1, 테이블2
WHERE <조인조건> AND <검색조건>

# 문법 2
SELECT <속성들>
FROM 테이블1 INNER JOIN 테이블2 ON <조인조건>
WHERE <검색조건>

외부조인(outer join)

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

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

부속질의(중첩질의, nested query)

• SQL 문 내에 또 다른 SQL 문을 작성
• SELECT 문의 WHERE 절에 또 다른 테이블의 결과를 이용하기 위해 다시 SELECT 문을 괄호로 묶는 것
• 결과는 네 가지 중에 하나로 나타난다
• : 단일행-단일열(1 x 1), 다중행-단일열(n x 1), 단일행-다중열(1 x n), 다중행-다중열(n x n)
• 부속질의 간에는 상하 관계가 있고, 실행 순서는 하위 부속질의를 먼저 실행하고 그 결과를 이용해 상위 부속질의를 실행한다
• 상관 부속질의는 상위 부속질의의 투플을 이용하여 하위 부속질의를 계산한다
• 상위 부속질의와 하위 부속질의는 의존적이다
• 조인은 부속질의가 할 수 있는 모든 것을 할 수 있기 때문에, 한 개의 테이블에서만 결과를 얻는 여러 테이블 질의는 조인보다 부속질의가 편하다

집합 연산

• SQL에서 합집합을 UNION으로 나타낸다
• MySQL은 MINUS, INTERSECT 집합 연산이 없다.→ INTERSECT 연산: IN 연산자로 구현
• → MINUS 연산: NOT IN 연산자로 구현

EXISTS

• 상관 부속질의문 형식
• 조건에 맞는 투플이 존재하면 결과에 포함시킨다
• 부속질의문의 어떤 행이 조건에 만족하면 참이다

NOT EXISTS

• 부속질의문의 모든 행이 조건에 만족하지 않으면 참이다

03. 데이터 정의어

CREATE

• 테이블을 구성, 속성과 속성에 관한 제약을 정의, 기본키 및 외래키를 정의하는 명령
• 문법

CREATE TABLE 테이블이름
  ( {속성이름 데이터타입
      [NULL | NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건]
    }
      [PRIMARY KEY 속성이름(들)]
      [FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
			        [ON DELETE {CASCADE | SET NULL}]
  )

# 대문자는 키워드, {} 안의 내용은 반복 가능, []는 선택적으로 사용, |는 1개를 선택, <>는 해당되는 문법 사항이 있음
# 편의상 콤마(,) 기호를 생략함
# NOT NULL은 NULL 값을 허용하지 않은 제약
# UNIQUE는 유일한 값에 대한 제약
# DEFAULT는 기본값 설정
# CHECK는 값에 대한 조건을 부여할 때 사용
# PRIMARY KEY는 기본키를 정할 때, FOREIGN KEY는 외래키를 지정할 때, ON DELETE는 투플의 삭제 시 외래키 속성에 대한 동작
# ON DELETE의 옵션: CASCADE, SET NULL, 명시하지 않으면 RESTRICT(NO ACTION)

• 외래키 제약조건 명시할 때 주의할 점: 참조되는 테이블(부모 릴레이션)이 존재해야 하고, 참조되는 테이블의 기본키여야 한다

*** 문자형 데이터 타입: CHAR, VARCHAR

• CHAR: n바이트를 가진 문자형 타입. 저장되는 문자의 길이가 n보다 작으면 나머지는 공백으로 채워 n바이트를 만들어 저장한다
• VARCHAR: n바이트를 가진 문자형 타입. 저장되는 문자의 길이만큼만 기억장소를 차지하는 ‘가변형’이다

ALTER

• 생성된 테이블의 속성과 속성에 관한 제약을 변경, 기본키 및 외래키를 변경
• 문법

ALTER TABLE 테이블이름
			[ADD 속성이름 데이터타입]
			[DROP COLUMN 속성이름]
			[ALTER COLUMN 속성이름 데이터타입]
			[ALTER COLUMN 속성이름 [NULL | NOT NULL]
			[ADD PRIMARY KEY(속성이름)]
			[[ADD | DROP] 제약이름]

DROP

• 테이블을 삭제하는 명령(cf. 데이터만 삭제하려면 DELETE를 사용한다)
• 문법

DROP TABLE 테이블이름

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

INSERT

• 테이블에 새로운 투플을 삽입하는 명령
• 문법

INSERT INTO 테이블이름[(속성리스트)]
			 VALUES (값리스트)

UPDATE

• 특정 속성 값을 수정하는 명령
• 문법

UPDATE 테이블이름
SET 속성이름1 = 값1 [, 속성이름1 = 값2, ...]
[WHERE <검색조건>]

DELETE

• 테이블에 있는 기존 투플을 삭제하는 명령
• 문법

DELETE FROM 테이블이름
[WHERE 검색조건]
# 검색조건이 없으면 모든 투플을 삭제한다