백업 및 복원

DML 구문을 다루기에 앞서, DDL로 정의하고 DML로 조작될 데이터베이스의 데이터들을 백업하고 복원하는 방법을 알아보겠습니다.

데이터는 데이터베이스와 릴레이션 단위로 백업 및 복원할 수 있습니다.
MySQL에 접속한 상태가 아닌, 접속하기 전 프롬프트 창에서 실행해야합니다.

데이터베이스 백업
mysqldump -u 계정명 -p 백업할_데이터베이스명 > 백업_파일명.sql

데이터베이스 복원
mysql -u 계정명 -p 복원할_데이터베이스명 < 백업_파일명.sql

릴레이션 백업
mysqldump -u 계정명 -p 백업할_데이터베이스명 백업할_릴레이션명 > 백업_파일명.sql

릴레이션 복원
mysql -u 계정명 -p 복원할_데이터베이스명 < 백업_파일명.sql

INSERT 문

릴레이션(테이블)에 투플을 삽입합니다.
릴레이션 생성시 기술한 컬럼 순서에 맞게 값을 입력해주어야하고, NOT NULL을 사용한 컬럼에는 반드시 값을 입력해주어야합니다.
컬럼을 문자나 날짜 형식으로 지정한 경우 작은 따옴표를 사용하여 '값' 의 형태로 값을 입력합니다.

INSERT INTO 릴레이션명 VALUES (값1, 값2, ..., 값n);

INSERT INTO 릴레이션명 (컬럼1, 컬럼2, ..., 컬럼m) VALUES (값1, 값2, ..., 값m);

모든 컬럼에 값을 입력하는 경우 위의 형식을, 특정 컬럼에만 값을 입력하려는 경우 아래의 형식을 사용하면됩니다.

DELETE 문

릴레이션에서 투플을 삭제합니다. 컬럼 단위가 아닌 투플 단위의 삭제만 가능합니다.
다른 릴레이션에서 참조하고있는 투플을 삭제하려고 시도하면 미리 정해진 조건에따라 거절될 수 있습니다.

DELETE FROM 릴레이션명 [WHERE 조건];

만약 WHERE 절을 사용하지않으면 릴레이션의 모든 투플이 삭제되고 복구할 수 없으므로 WHERE 절과 적절한 조건을 사용하여 원하는 투플만 삭제되도록 해야합니다.

INSERT 문을 사용하여 투플을 삽입한 후, DELETE 문에 WHERE 절에서 id 컬럼에 대한 조건을 기술하여 투플을 삭제하였습니다.

WHERE 절에 기술하는 조건에따라 원치않는 투플도 삭제될 수 있으므로 조건을 적절하게 기술해야합니다.

INSERT 문에서 원하는 컬럼만을 지정하여 값을 입력하면, 입력하지않은 컬럼에대해서는 NULL 값이 삽입되지만, 릴레이션 생성시 DEFAULT 제약조건을 설정하면 기본값이 입력되는 것을 확인할 수 있습니다.

릴레이션 생성시 지정한 AUTO_INCREMENT 제약조건에 따라 값을 따로 입력하지않아도 자동으로 상승하며 저장되는 것을 확인할 수 있습니다.
투플을 삭제해도 값은 이어서 상승합니다.

릴레이션에 위처럼 투플을 삽입하였습니다.

UPDATE 문

INSERT 문으로 삽입한 투플의 컬럼값을 변경합니다.

UPDATE 릴레이션명
	SET 컬럼1=값1[, 컬럼2=값2, ..., 컬럼n=값n]
    [WHERE 조건];

DELETE 문과 마찬가지로 WHERE 절을 기술하지않으면 모든 투플에대해서 수정이 이루어지므로 WHERE 절에 적절한 조건을 기술하여 사용해야합니다.

다른 투플의 값도 수정해주었습니다.

SELECT 문

릴레이션에서 필요한 데이터를 검색할 때 사용합니다. 위의 예제에서 주로 사용한 SELECT 문인
SELECT * FROM 릴레이션명;은 가장 단순한 형태로 해당 릴레이션의 모든 투플을 보여주지만, 원하는 데이터만을 선택할 때는 훨씬 복잡한 SELECT 문이 사용됩니다.

|| : OR

SELECT [DISTINCT] * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM [중첩_질의문]릴레이션1
    [CROSS JOIN || INNER JOIN || NATURAL JOIN || LEFT [OUTER] JOIN || RIGHT [OUTER] JOIN
    릴레이션2 ON 조인_조건]
    [WHERE 조건 [중첩_질의문]]
    [GROUP BY 컬럼1, 컬럼2, ..., 컬럼m [HAVING 조건]]
    [ORDER BY 컬럼 ASC || DESC];

위처럼 다양한 절을 활용하여 수많은 데이터에서 원하는 데이터만을 검색할 수 있습니다.

FROM 절

검색할 대상 릴레이션을 기술합니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명;

'DISTINCT' 키워드를 사용하면 결과에서 중복되는 값을 제거하여 하나씩만 출력합니다.

제어 흐름 함수

종류	기능
CASE	CASE 컬럼의 컬럼값을 WHEN-THEN-ELSE 구조에서 조건과 값으로 출력
IF(조건, 값1, 값2)	조건이 참이면 값1, 거짓이면 값2 출력
IFNULL(값1, 값2)	값1이 NULL이면 값2 출력, NULL이 아니면 값1 출력
NULLIF	값1과 값2가 같으면 NULL 출력, 다르면 값1 출력

CASE 컬럼
WHEN 조건1 THEN 값1
...
WHEN 조건n THEN 값n
[ELSE 조건에_맞지않는_경우_값]
END

ALIAS 절

'ALIAS(별칭)' 절은 릴레이션명이나 컬럼명을 SELECT 문 사용 중 임시로 변경할 때 사용합니다.

SELECT 컬럼1 AS 별칭1[, 컬럼2 AS 별칭2, ..., 컬럼n AS 별칭n]
FROM 릴레이션명 [as 별칭];

AS 키워드는 생략 가능합니다.

ORDER BY 절

검색 결과를 오름차순이나 내림차순으로 정렬하여 출력합니다. 생략되있을 경우 기본키 값을 기준으로 오름차순으로 정렬하여 출력합니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
ORDER BY ASC || DESC;

ASC는 오름차순(ascending), DESC는 내림차순(descending)으로 결과를 정렬합니다.

LIMIT 절

투플수를 제한하여 출력합니다.
너무많은 투플이 출력되어 일부의 결과만 필요한 경우 등에 사용할 수 있습니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
LIMIT 출력할_투플_수;

WHERE 절

릴레이션에서 특정 조건에 맞는 투플만 검색할 때 사용합니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
WHERE 조건;

IS NULL / IS NOT NULL

IS NULL : 컬럼값이 'NULL'인 투플을 검색합니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
WHERE 컬럼 IS NULL;

IS NOT NULL : 컬럼값이 'NULL'이 아닌 투플을 검색합니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
WHERE 컬럼 IS NOT NULL;

정확한 키워드가 아닌 '=NULL', '!=NULL' 등을 사용하면 원하는 결과값을 얻을 수 없습니다.

비교 연산자

종류	기능
컬럼 = 값	컬럼값이 값과 일치하는 투플 검색
컬럼 !=(<>) 값	컬럼값이 값과 일치하지않는 투플 검색
컬럼 > 값	컬럼값이 값보다 큰 투플 검색
컬럼 < 값	컬럼값이 값보다 작은 투플 검색
컬럼 >= 값	컬럼값이 값보다 크거나 같은 투플 검색
컬럼 <= 값	컬럼값이 값보다 작거나 같은 투플 검색

비교 연산자는 WHERE 절 내에서 컬럼값과 값을 비교하여 조건에 맞는 투플을 검색할 때 사용됩니다.

BETWEEN 절

비교 연산자를 사용하는 대신 BETWEEN과 AND를 사용하여 범위에 해당하는 값을 검색할 수 있습니다. 범위는 값들을 포함하는 이상, 이하의 범위를 가집니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
WHERE 컬럼 BETWEEN 값1 AND 값2;

논리 연산자

종류	기능
NOT(!) 조건	해당 조건이 아닌 투플을 검색하여 출력
조건1 AND(&&) 조건2	조건1과 조건2를 모두 만족하는 투플을 검색하여 출력
조건1 OR(||) 조건2	조건1 이나 조건2 중 하나 이상을 만족하는 투플을 검색하여 출력

논리 연산자는 WHERE 절 내에서 부울값(참(1), 거짓(0))을 이용하여 결과를 반전시키거나 여러 조건을 이용하여 검색할 때 사용됩니다.

IN / NOT IN 절

논리 연산자를 사용하는 대신 IN이나 NOT IN을 사용하여 기술한 값들 중 일치하거나 일치하지않는 값을 검색할 수 있습니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
WHERE 컬럼 IN || NOT IN (값1[, 값2, ..., 값n]);

LIKE 절

비교 연산자(=, !=)를 사용하여 완전히 일치하는 값을 검색하는 대신, LIKE 절에 와일드 카드를 활용하여 부분적으로 일치하는 값을 검색할 수 있습니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
WHERE 컬럼 LIKE 문자와_와일드_카드_조합;

※ 와일드 카드

기호	기능
%	0개 이상의 문자 대체
_	한개의 문자 대체

함수

MySQL에서는 다양함 함수를 지원합니다.
SELECT 문과 결합하여 사용할수도있지만, SELECT 함수;의 형태로 사용하면 계산기처럼 단순 계산도 가능합니다.

숫자 관련 함수

종류	기능
ABS(A)	A의 절댓값 출력
CEILING(A)	A보다 큰 정수 중 가장 작은 정수 출력
FLOOR(A)	A보다 작은 정수 중 가장 큰 정수 출력
ROUND(A,B)	A의 소수점 B자리에서 반올림한 값 출력 (0이 소수점 첫째 자리)
TRUNCATE(A,B)	A의 소수점 B자리에서 버림한 값 출력 (0이 소수점 첫째 자리)
POWER(A,B)	A의 B 제곱값 출력
MOD(A,B)	A를 B로 나눈 나머지값 출력(모듈로 연산)

문자 관련 함수

종류	기능
ASCII(A)	문자열 A의 맨 처음 문자 ASCII 코드 값 출력
CONCAT(A,B,...)	입력한 문자열을 연결하여 출력
CHAR_LENGTH(A)	문자열 A의 길이 출력
UPPER(A)	문자열 A를 대문자로 변환하여 출력
SUBSTRING(A,N)	문자열 A의 N번째부터 문자열 출력
INSERT(A,N,M,B)	문자열 A의 N번째부터 M만큼 문자열 B로 변환하여 출력
REPLACE(A,B,C)	문자열 A의 B를 C로 변환하여 출력
STRCMP(A,B)	A > B이면 1 출력 A = B이면 0 출력 A < B이면 -1 출력

날짜 관련 함수

종류	기능
NOW()	현재 날짜와 시간 출력(질의문 시작 시간 기준)
CURDATE()	현재 날짜 출력
CURTIME()	현재 시간 출력
DAYOFWEEK(기준_날짜)	기준 날짜가 무슨 요일인지 출력(1 : 일요일, 2: 월요일, ..., 7 : 토요일)
DAYOFYEAR(기준_날짜)	기준 날짜가 그 해의 몇 번째 날인지 출력
ADDDATE(기준_날짜, INTERVAL T SECOND||MINUTE||HOUR||DAY||MONTH||YEAR)	기준 날짜에 T만큼의 초||분||시||일|월||년 더한 값 출력
SUBDATE(기준_날짜, INTERVAL T SECOND||MINUTE||HOUR||DAY||MONTH||YEAR)	기준 날짜에 T만큼의 초||분||시||일|월||년 뺀 값 출력
TIMESTAMPDIFF(SECOND||MINUTE||HOUR||DAY||MONTH||YEAR, 기준_날짜1, 기준_날짜2)	기준 날짜2에서 기준 날짜1을 뺀 값을 초||분||시||일||월||년으로 출력
SLEEP(T)	T초만큼 질의문 중단 후 계속 실행

집계 관련 함수

종류	기능
COUNT(컬럼)	컬럼에 있는 값들의 갯수 출력
SUM(컬럼)	컬럼에 있는 값들의 합 출력(컬럼값이 숫자여야 함)
AVG(컬럼)	컬럼에 있는 값들의 평균 출력(컬럼값이 숫자여야 함)
MAX(컬럼)	컬럼에 있는 값들 중 최대값 출력
MIN(컬럼)	컬럼에 있는 값들 중 최소값 출력

--- 투플 추가 및 수정 ---

GROUP BY ~ HAVING 절

컬럼의 값이 같은 것끼리 GROUP BY 절로 그룹화하고 HAVING 절에서 조건을 부여합니다.

SELECT 컬럼1[, 컬럼2, ..., 컬럼n][, 함수 활용] FROM 릴레이션명
GROUP BY 컬럼
[HAVING 조건];

중첩 질의문

중첩 질의문은 SELECT 문 안에 또 다른 SELECT 문을 중첩하여 사용하는 것으로, FROM 절이나 WHERE 절에 사용된 SELECT 문을 내부 질의문, 그것을 포함하는 SELECT 문을 외부 질의문이라고합니다.
내부 질의문은 '(내부 질의문)'처럼 괄호로 묶어줍니다.

SELECT 컬럼1[, 컬럼2, ..., 컬럼n] FROM (내부 질의문)
[WHERE 조건];

SELECT 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
WHERE [조건] [연산자] (내부 질의문);

위의 질의문을 FROM 절의 내부 질의문으로 사용하였습니다.
이때 FROM 절에 위치한 내부 질의문에 속한 데이터를 대상으로하여 외부 질의문을 수행합니다.
FROM 절에 중첩 질의문 사용시, 별칭을 사용해야합니다.

위의 두 질의문을 각각 외부 질의문, 내부 질의문으로하여 WHERE 절에서 중첩 질의문을 사용하였습니다.

EXISTS / NOT EXISTS 절

'EXISTS / NOT EXISTS 절'은 IN / NOT IN 절 대신 사용할 수 있으며, 외부 질의문이 실행한 EXISTS / NOT EXISTS 절에서 내부 질의문에 해당 조건에 맞는 투플이 있는지 참/거짓 값만 반환하여 확인하므로 IN / NOT IN 절 보다 성능 측면에서 뛰어납니다.

SELECT 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명
WHERE [조건] [연산자]
EXISTS||NOT EXISTS (내부 질의문);

같은 결과를 얻기위해 IN / NOT IN 절을 사용하여 질의문을 작성할 수도 있습니다.

JOIN 문

두개 이상의 릴레이션에서 연관있는 컬럼(기본키, 외래키 등)을 기준으로 투플을 결합한 릴레이션을 출력합니다.

※ JOIN 시 SELECT 문에서 출력할 컬럼을 기술할 때 여러 컬럼을 기술하는 경우, 안전성과 가독성을 위해 별칭을 사용하여 각 릴레이션의 컬럼을 기술하는 것이 권장됩니다.

CROSS JOIN

투플을 결합할 때 특정 조건없이 모든 조합으로 결합합니다.
이는 결합 가능한 모든 조합을 보여주는 '카티션 프로덕트 연산'과 동일합니다.
따라서 의미없는 조합의 투플을 포함하여 생성됩니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n]
FROM 릴레이션명1 CROSS JOIN 릴레이션명2 [, CROSS JOIN ... 릴레이션명n]
[WHERE 조건];

INNER JOIN(EQUI JOIN)

크로스 조인에서 의미없는 조합의 투플을 제거하여, 의미있는 조합의 투플만 생성하기위해 'INNER JOIN'을 사용합니다. 이는 릴레이션 간의 '교집합 연산'과 동일하며, 이를 위해 결합하는 릴레이션 간에 공통적으로 존재하는 일치 조건을 ON 절에 기술하여 결합합니다.
INNER JOIN은 대표적인 JOIN으로 INNER JOIN 대신 INNER 키워드를 생략하고 JOIN만 기술하여도 INNER JOIN이 실행됩니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n]
FROM 릴레이션명1 [INNER] JOIN 릴레이션명2 ON 조인_조건1
[, [INNER] JOIN ... 릴레이션명n ON 조인_조건m]
[WHERE 조건];

NATURAL JOIN

INNER JOIN 시 결합하는 릴레이션 간에 같은 이름의 컬럼이있을때 'NATURAL JOIN'을 이용하면, 같은 컬럼명을 가진 컬럼의 컬럼값을 기준으로 자동으로 INNER JOIN을 실행하며, ON 절에 조인 조건을 따로 기술할 필요가 없습니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n]
FROM 릴레이션명1 NATURAL JOIN 릴레이션명2
[WHERE 조건];

ON 절에 따로 조인 조건을 기술하지않았음에도 INNER JOIN과 같은 결과가 나온 것을 확인할 수 있습니다.
(※ NATURAL JOIN을 위해 'order_list' 릴레이션의 'customer_id' 컬럼을 잠시 'id'로 수정한 후 진행하였습니다.)

OUTER JOIN

INNER JOIN과 NATURAL JOIN은 유용하게 사용할 수 있지만 조인 조건에 부합하지않는 투플은 제외하고 출력합니다.
'OUTER JOIN'은 조인 조건에 부합하지않는 투플까지도 출력하기위해 사용되며, 기준이되는 릴레이션에 따라 종류가 나뉩니다.
(각 JOIN에서 'OUTER' 키워드는 생략 가능합니다.)

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n]
FROM 릴레이션명1 LEFT||RIGHT [OUTER] JOIN 릴레이션명2 ON 조인_조건
[WHERE 조건];

LEFT [OUTER] JOIN

왼쪽에 기술된 릴레이션의 모든 투플이 반영되어 출력됩니다.
오른쪽에 기술된 릴레이션의 투플은 조건에 부합하는 투플은 정상적으로 값이 출력되고, 그렇지않은 투플은 컬럼값이 'NULL'로 대체되어 출력됩니다.

INNER JOIN으로 출력되지않던 투플도 출력된 것을 확인할 수 있습니다.

RIGHT [OUTER] JOIN

오른쪽에 기술된 릴레이션의 모든 투플이 반영되어 출력됩니다.
왼쪽에 기술된 릴레이션의 투플은 조건에 부합하는 투플은 정상적으로 값이 출력되고, 그렇지않은 투플은 컬럼값이 'NULL'로 대체되어 출력됩니다.

FULL [OUTER] JOIN

양쪽에 기술된 릴레이션의 모든 투플이 반영되어 출력됩니다.
조건에 부합하는 투플은 정상적으로 값이 출력되고, 그렇지않은 투플은 컬럼값이 'NULL'로 대체되어 출력됩니다.
MySQL에서는 FULL OUTER JOIN 연산을 지원하지않으므로, LEFT OUTER JOIN과 RIGHT OUTER JOIN에 'UNION ALL' 연산을 사용하여 같은 효과를 얻을 수 있습니다.

※ UNION / UNION ALL

합집합 연산의 역할을 하며, 다수의 SELECT 문을 하나로 통합합니다.
UNION은 중복을 제거하고 결과를 출력하며, UNION ALL은 중복을 허용하여 결과를 출력합니다.

SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명1
[WHERE 조건]
UNION / UNION ALL
SELECT * || 컬럼1[, 컬럼2, ..., 컬럼n] FROM 릴레이션명2
[WHERE 조건]

SELF JOIN

같은 릴레이션을 JOIN하며, 같은 릴레이션을 JOIN하기때문에 반드시 별칭을 사용하여 컬럼을 구분해주어야합니다.
주로 재귀 관계에 있는 릴레이션에 사용합니다.

SELECT 별칭1.컬럼1[, ... 별칭1.컬럼n], 별칭2.컬럼1[, ... 별칭2.컬럼m]
FROM 릴레이션명1 AS 별칭1 INNER||OUTER JOIN 릴레이션2 AS 별칭2
ON 조인_조건
[WHERE 조건];

검색 결과 파일에 저장

SELECT 문을 사용하여 검색한 값을 파일에 저장할 수 있습니다.

SELECT 문
INTO OUTFILE 파일경로
FROM 릴레이션명;

View

'View'는 데이터베이스에 존재하는 원본 릴레이션을 기반으로 생성된 '가상 릴레이션'입니다. 따라서 View는 물리적으로 저장되지않고, 물리적인 삽입, 수정, 삭제는 원본 릴레이션에서 실행됩니다.
이러한 특성때문에 View에 투플을 삽입할 때는 제약이있습니다.
JOIN을 통해 생성된 View는 두개 이상의 릴레이션에 데이터가 삽입되므로 오류가 발생하고, 'NOT NULL'같은 옵션이 사용된 컬럼이 View 정의 시 포함되지않은 경우 데이터를 삽입하려고하면 오류가 발생합니다.

View는 릴레이션에서 선택한 부분을 기반으로 생성되므로, 원본 릴레이션에는 접근하지 못하게하면서 필요한 부분은 보여줄 수 있습니다. 그리고 복잡한 질의문으로 다양한 View를 구성하고 필요시에는 간단하게 View를 통해 검색하여 사용할 수 있습니다.

CREATE VIEW

SELECT 문을 사용하여 View를 정의하고 생성합니다.

CREATE VIEW View명
AS (SELECT 문)
[WITH CHECK OPTION];

※ WITH CHECK OPTION : View 생성시 기술한 제약조건에 위배되는 투플 삽입 시도시 오류가 발생합니다.
즉, View에 삽입한 투플이 View 안에 포함되지않으면 오류가 발생합니다.

'MEMBER' 릴레이션을 기반으로 생성한 'MEMBER_U30' View는 'WITH CHECK OPTION'이 사용되었고, AGE가 30 이하인 투플만 포함합니다. 따라서 AGE가 27인 'DELTA'는 View에 정상적으로 삽입되었지만, AGE가 33인 'ECO'는 삽입이 거부되고 오류 메시지가 출력되었습니다.

View에 투플 삽입시 실제로 저장되는 곳은 View의 기반이되는 원본 릴레이션입니다.

원본 릴레이션이 삭제되면 View도 사용할 수 없습니다.

ALTER VIEW

SELECT 문을 사용하여 생성된 View를 수정합니다.

ALTER VIEW View명
AS (SELECT 문);

DROP VIEW

생성된 View를 삭제합니다.

DROP VIEW View명 [CASCADE || RESTRICT];

만약 삭제할 View를 이용해 생성된 또다른 View가 있을 때 'CASCADE'와 'RESTRICT' 키워드를 사용할 수 있습니다.
CASCADE 키워드는 다른 View를 계속 유지하며,
RESTRICT 키워드는 다른 View를 같이 삭제합니다.