chapter 6: SQL 기본

song4

chapter 6의 핵심 개념

MySQL이 사용하는 SQL은 대부분의 DBMS에 공통적으로 적용되는 ANSI-92/99 SQL의 내용을 포함하면서 MySQL의 특징을 반영하는 내용이 포함된, 확장된 SQL이다.

SELECT문의 기본 구조는 'SELECT 열 이름 FROM 테이블이름 WHERE 조건' 이다.
책 전체 실습을 위해 쇼핑몰을 간략화한 sqlDB를 생성하고 사용한다.
WHERE절은 조회하는 결과에 특정한 조건을 줘서, 원하는 데이터만 보고 싶을 때 사용한다.
CREATE TABLE ··· SELECT 구문은 테이블을 복사해서 사용할 경우에 주로 사용된다.
GROUP BY절은 지정된 열을 그룹으로 묶어주는 역할을 하며, 주로 집계 함수와 함께 사용된다.
SQL문은 크게 DML, DDL, DCL로 분류한다.
INSERT/UPDATE/DELETE문은 데이터의 입력/수정/삭제의 기능을 한다.

chapter 06의 학습 흐름

SELECT문의 형식과 사용법
책 전체에서 사용할 sqlDB 생성
특정 조건을 조회하는 WHERE절
ORDER BY절 및 LIMIT절
GROUP BY 및 HAVING 그리고 집계 함수
INSERT/UPDATE/DELETE문의 형식

SELECT문

원하는 데이터를 가져와 주는 기본적인 <SELECT … FROM>

SELECT문은 가장 많이 사용하는 구문이다. 처음에는 쉬운 듯 별 것 아닌 것처럼 보이지만, 갈수록 어렵게 느껴진다. SELECT는 한마디로 데이터베이스 내의 테이블에서 원하는 정보를 추출하는 명령이다.

SELECT의 구문 형식
SELECT문은 다양한 옵션으로 인해서 전체 구문 형식은 복잡해 보이지만, 실제적으로 많이 사용되는 형태로 요약한 구조는 다음과 같다. 여기서 대괄호([])의 내용은 생략할 수 있다.
```
SELECT select_expr
[FROM table_references]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}]
```
USE 구문
현재 사용하는 데이터베이스를 지정 또는 변경하는 구문이다.
SELECT와 FROM
employees 데이터베이스를 선택한 후에 ··· 간단한 SQL문을 수행하자.
```
**SELECT * FROM titles;**
```
이번에는 해당 테이블에서 전체 열이 아닌 필요로 하는 열만 가져오자. 다음과 같이 사원 테이블의 이름만 가져와 보자.
```
SELECT first_name FROM employees;
```
여러 개의 열을 가져오고 싶으면 콤마 (,)로 구분하면 된다. 또한, 열 이름의 순서는 사용자 마음대로 바꿔도 된다.
```
SELECT first_name, last_name, gender FROM employees;
```

특정한 조건의 데이터만 조회하는 <SELECT … FROM … WHERE>

기본적인 WHERE절
WHERE절은 조회하는 결과에 특정한 조건을 줘서 원하는 데이터만 보고 싶을 때 사용하는데, 다음과 같은 형식을 갖는다.
```
SELECT 필드이름 FROM 테이블이름 WHERE 조건식;
```
관계 연산자의 사용
1970년 이후에 출생하고 신장이 182 이상인 사람의 아이디와 이름을 조회해 보자.
```
SELECT userID, userName FROM userTBL WHERE birthYear >= 1970 AND height >= 182;
```
이번에는 1970년 이후에 출생했거나, 신장이 182 이상인 사람의 아이디와 이름을 조회해 보자.
```
SELECT userID, userName FROM userTBL WHERE birthYear >= 1970 OR height >= 182;
```

BETWEEN··· AND와 IN() 그리고 LIKE
이번에는 키가 180 ~ 183인 사람을 조회해 보자.
```
SELECT userID, userName FROM userTBL where height >= 180 and height <= 183;
```
동일한 방식으로 BETWEEN··· AND를 사용할 수 있다.
```
SELECT userName, height FROM userTBL WHERE height BETWEEN 180 AND 183;
```
키의 경우에는 숫자로 구성되어 있어서 연속적인 값을 가지므로 BETWEEN··· AND를 사용했지만 지역이 '경남'이거나 '전남'이거나 '경북'인 사람을 찾을 경우에 연속된 값이 아니기 때문에 BETWEEN··· AND를 사용할 수 없다.
이번에는 지역이 '경남', '전남', '경북'인 사람의 정보를 확인해 보자.
```
SELECT userName, addr FROM userTBL WHERE addr = '경남' OR addr = '전남' OR addr = '경북';
```
이와 동일하게 연속적인 값이 아닌 이산적인 값을 위해 IN()을 사용할 수 있다.
```
SELECT userName, addr FROM userTBL WHERE addr IN ('경남', '전남', '경북');
```
문자열의 내용을 검색하기 위해서는 LIKE 연산자를 사용할 수 있다.
```
SELECT userName, height FROM userTBL WHERE userName like '김%';
```
위 조건은 성이 '김'씨이고 그 뒤는 무엇이든(%) 허용한다는 의미다. 즉, '김'이 제일 앞 글자인 것들을 추출한다. 그리고 한 글자와 매치하기 위해서는 '_'를 사용한다. 아래는 맨 앞 글자가 한 글자이고, 그 다음이 '종신'인 사람을 조회해 준다.
```
SELECT userName, height FROM userTBL WHERE userName like '_종신';
```
이 외에도 '%'와 '_'를 조합해서 사용할 수 있다. 조건에 '_용%'라고 사용하면 앞에 아무거나 한 글자가 오고 두 번째는 '용', 그리고 세 번째 이후에는 몇 글자든 아무거나 오는 값을 추출해 준다.

%나 _가 검색할 문자열의 제일 앞에 들어가는 것은 MySQL 성능에 나쁜 영향을 끼칠 수 있다. 예로 name열을 '%용'이나 '_용필' 등으로 검색하면, name열에 인덱스가 있더라도 인덱스를 사용하지 않고 전체 데이터를 검색하게 된다. 지금은 데이터 양이 얼마 되지 않으므로 그 차이를 느낄 수 없겠으나 대용량 데이터를 사용할 경우에는 아주 비효율적인 결과를 낳게 된다.
ANY/ALL/SOME 그리고 서브쿼리(SubQuery, 하위쿼리)
서브쿼리란 간단히 얘기하면 쿼리문 안에 또 쿼리문이 들어 있는 것을 얘기한다. 예로 김경호보다 키가 크거나 같은 사람의 이름과 키를 출력하려면 WHERE 조건에 김경호의 키를 직접 써줘야 한다.
```
SELECT userName, height FROM userTBL WHERE height > 177;
```
그런데 이 177이라는 키를 직접 써주는 것이 아니라 이것도 쿼리를 통해서 사용하려는 것이다.
```
SELECT userName, height FROM userTBL 
WHERE height > (SELECT height FROM userTBL WHERE userNAme = '김경호');
```
후반부의 (SELECT height FROM userTBL WHERE userName = '김경호')는 177이라는 값을 돌려주므로, 결국 177이라는 값과 동일한 값이 되어서 위 두 쿼리는 동일한 결과를 내주는 것이다.
이번에는 지역이 '경남' 사람의 키보다 키가 크거나 같은 사람을 추출해 보자.
```
SELECT userName, height FROM userTBL 
WHERE height >= (SELECT height FROM userTBL WHERE addr = '경남');
```
하위쿼리가 둘 이상의 값을 반환하기 때문이다. 그래서 필요한 구문이 ANY 구문이다.
```
SELECT name, height FROM userTBL 
WHERE height >= ANY
(SELECT height FROM userTBL WHERE addr = '경남');
```
후반부의 (SELECT height FROM userTBL WHERE addr = '경남';)의 결과값 중 최소값인 170보다 크거나 같은 사람이 해당된다.
이번에는 ANY를 ALL로 바꿔서 실행해 보자.
```
SELECT name, height FROM userTBL 
WHERE height >= ALL
(SELECT height FROM userTBL WHERE addr = '경남');
```
ALL을 사용하면 후반부의 (SELECT height FROM userTBL WHERE addr = '경남';)의 결과값 중 전체를 비교해서 true인 값만 반환한다.
결론적으로 ANY는 서브쿼리의 여러 개의 결과 중 한 가지만 만족해도 되며, ALL은 서브쿼리의 여러 개의 결과를 모두 만족시켜야 한다. 참고로, SOME은 ANY와 동일한 의미로 사용된다.
이번에는 '>=ANY' 대신에 '=ANY'를 사용해보자
```
SELECT name, height FROM userTBL 
WHERE height = ANY
(SELECT height FROM userTBL WHERE addr = '경남');
```
이는 다음과 동일한 구문이다.
```
SELECT name, height FROM userTBL
WHERE height IN(
SELECT height FROM userTBL 
WHERE addr = '경남');
```

원하는 순서대로 정렬하여 출력: ORDER BY
ORDER BY절은 결과물에 대해 영향을 미치지는 않지만, 결과가 출력되는 순서를 조절하는 구문이다. 먼저 가입한 순서로 회원들을 출력해 보자.
```
SELECT name, height FROM userTBL ORDER BY mDate;
```
기본적으로 오름차순(ASCENDING)으로 정렬된다. 내림차순(DESCENDING)으로 정렬하기 위해서는 열 이름 뒤에 DESC라고 적어주면 된다.
```
SELECT name, mDate FROM userTBL ORDER BY mDate DESC;
```
이번에는 여러 개로 정렬해 보자. 키가 큰 순서로 정렬하되 만약 키가 같을 경우에 이름 순으로 정렬 하려면 다음과 같이 사용하면 된다.
```
SELECT name, height FROM userTBL ORDER BY height DESC, name ASC;
```
ORDER BY절은 SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY 중에서 제일 뒤에 와야 한다는 것을 잊지 말자.

ORDER BY절은 MySQL의 성능을 상당히 떨어뜨릴 소지가 있다. 꼭 필요한 경우가 아니라면 되도록 사용하지 않는 것이 좋다.

중복된 것은 하나만 남기는 DISTINCT
회원 테이블에서 회원들의 거주지역이 몇 군데인지 출력해 보자.
```
SELECT addr FROM userTBL;
```
10개 행밖에 안 되는 데도 중복된 것을 세는 것이 어렵다. 조금 전에 배운 ORDER BY를 사용해 보자.
```
SELECT addr FROM userTBL ORDER BY addr;
```
아까보다는 쉽지만 그래도 중복된 것을 골라서 세기가 좀 귀찮다. 또, 몇 만 건이라면 정렬이 되었어도 세는 것을 포기해야 할 것이다. 이때 사용하는 구문이 DISTINCT다.
```
SELECT DISTINCT addr FROM userTBL;
```
중복된 것은 1개씩만 보여주면서 출력되었다.

출력하는 개수를 제한하는 LIMIT
이번에는 employees DB를 잠깐 사용해 보자. hire_date(회사 입사일)열이 있는데, 입사일이 오래된 직원 5명의 emp_no(사원번호)를 알고 싶다면 어떻게 해야 할까? 조금 전에 배운 ORDER BY절을 사용하면 된다.
```
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC;
```
제일 앞의 5건만 사용하면 된다. 그런데, 5건을 보기 위해서 필요 없는 약 30만 건을 더 출력하였다. 어쩌면 이런 것이 별것 아니라고 생각할 수도 있겠지만, 이러한 조회가 자주 일어난다면 필요 없는 부담을 MySQL에게 많이 주는 것이다. 그래서 상위의 N개만 출력하는 'LIMIT N'구문을 사용하면 된다.
```
SELECT emp_no, hire_date
FROM employees ORDER BY hire_date ASC LIMIT 5;
```
딱 원하는 개수만큼 출력되었다. 이는 개수의 문제보다는 MySQL의 부담을 많이 줄여주는 방법이기도 하다.
LIMIT절은 'LIMIT 시작, 개수' 또는 'LIMIT 개수 OFFSET 시작' 형식으로소 사용할 수 있다. 시작은 0부터 시작한다. 그러므로 5개를 출력하기 위해서는 다음과 같이 사용해도 된다.
```
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC
LIMIT 0, 5; -- LIMIT 5 OFFSET 0과 동일

SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC
LIMIT 5 OFFSET 0;
```
악성 쿼리문이란 서버의 처리량을 많이 사용해서 결국 서버의 전반적인 성능을 나쁘게 하는 쿼리문을 뜻한다. 비유를 하자면 많은 사람(쿼리문)이 표를 끊기 위해서(처리되기 위해) 줄 서 있는데, 어떤 사람(악성 쿼리문)이 계속 판매원에게 필요치 않은 질문을 던져서 뒤에 서 있는 다른 많은 사람이 표를 끊는데 시간이 오래 걸리는 것과 같은 이치다. 지금은 SQL문의 문법을 배우는 과정이므로 결과만 나온다면 잘 된 것처럼 느껴지겠지만, 실무에서는 얼만큼 효과적으로 결과를 얻느냐가 더욱 중요한 이슈가 된다. 잘못된 악성쿼리를 자꾸 만들지 않도록 더욱 신경을 써서 SQL문을 만들 필요가 있다.

테이블을 복사하는 CREATE TABLE ··· SELECT
CREATE TABLE ··· SELECT 구문은 테이블을 복사해서 사용할 경우에 주로 사용된다.
```
형식:
CREATE TABLE 새로운테이블 (SELECT 복사할 열 FROM 기졸테이블)

CREATE TABLE buyTBL2 (SELECT * FROM buyTBL);
```
필요하다면 지정한 일부 열만 복사할 수도 있다.
```
CREATE TABLE buyTBL3 
(SELECT userID, prodNAME FROM buyTBL);
```
제약조건은 카피되지 않는다.

GROUP BY 및 HAVING 그리고 집계 함수

GROUP BY절
이 절이 말 그대로 그룹으로 묶어주는 역할을 한다. 구매 테이블(buyTBL)에서 사용자(userID)가 구매한 물품의 개수(amount)를 보려면 다음과 같이 하면 된다.
```
SELECT userID, amount FROM buyTBL ORDER BY userID;
```
결과를 보면 사용자별로 여러 번의 물건 구매가 이루어져서 각각의 행이 별도로 출력된다. BBK 사용자의 경우에는 5+10+2+2 = 19개의 구매를 했다. 합계를 낼 때 이렇게 손이나 전자계산기를 두드려서 계산한다면 MySQL을 사용할 이유가 없을 것이다.
이럴 때는 집계 함수를 사용하면 된다. 집계 함수(Aggregate Function)는 주로 GROUP BY절과 함께 쓰이며 데이터를 그룹화(Grouping) 해주는 기능을 한다.
각 사용자별로 구매한 개수를 합쳐서 출력하는 것이다.
```
SELECT userID, sum(amount) FROM buyTBL GROUP BY userID;
```
그런데, SUM(amount)의 결과 열에는 제목이 함수 이름 그대로 나왔다. 전에 배운 별칭을 사용해서 결과를 보기 편하게 만들자.
```
SELECT userID `사용자 아이디`, sum(amount) `총 구매 개수` FROM buyTBL GROUP BY userID ;
```
이번에는 구매액의 총합을 출력하자. 구매액은 가격 * 수량이므로, 총합은 SUM()을 사용하면 된다.
```
SELECT userID `사용자 아이디`, sum(amount * price) `총 구매액` FROM buyTBL GROUP BY userID ;
```

집계 함수
SUM() 외의 GROUP BY와 함께 자주 사용되는 집계 함수는 아래와 같다.
전체 구매자가 구매한 물품의 개수의 평균을 구해보자.
```
SELECT AVG(amount) `평균 구매 개수` FROM buyTBL ;
```
이번에는 각 사용자 별로 한 번 구매 시 물건을 평균 몇 개 구매했는지 평균을 내보자.
```
SELECT userID `구매자`, AVG(amount) `평균 구매 개수` FROM buyTBL GROUP BY userID;
```
다른 예를 살펴보자. 가장 큰 키와 가장 작은 키의 회원 이름과 키를 출력하는 쿼리를 만들어서 직접 실행해 보자.
```
SELECT name, max(height), min(height) FROM userTBL;
```
책에서는 결과가 나오지만 현재 only_full_group_by 모드를 사용하고 있어서 GROUP BY 절을 사용하지 않는 집계 쿼리에서 비집계 열을 사용하는 것을 금지합니다. 즉, SELECT 절에서 집계 함수를 사용하는 경우, 집계되지 않은 열을 SELECT 절에 포함시키지 않아야 합니다.
그래서 name을 제거하던지, group by로 묶어주던지, 모드를 비활성화 시키는 방법이 있습니다. 저는 모드를 비활성화 해본적이 없으므로 한번 비활성화 해보겠습니다.
```
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
```
비활성화를 했는데도 똑같은 오류가 났습니다. 그 이유는 GLOBAL설정을 했기 때문에 MySQL서버를 재시작하는 등 번거로운 작업을 해야 합니다. SESSION을 사용하면 바로 적용이 되기 때문에 때문에 GLOBAL을 사용하지 않고 SESSION을 사용해서 바로 적용을 시켜보겠습니다.
```
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
```
결과는 책과 동일하게 나왔으며 다시 모드를 돌려놓겠습니다.
```
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
```

가장 큰 키와 가장 작은 키는 나왔으나, 이름은 하나뿐이라서 어떤 것에 해당하는지 알 수 없다.

SELECT name, max(height), min(height) FROM userTBL GROUP BY name;

역시 원하는 결과가 아니다. 이런 경우에는 앞에서 배운 서브쿼리와 조합을 하는 것이 제일 수월하다.

SELECT name, height 
    FROM userTBL 
	WHERE height = (SELECT MAX(height) FROM userTBL) 
    	OR height = (SELECT MIN(height) FROM userTBL) ;

이번에는 휴대폰이 있는 사용자의 수를 카운트하자.

SELECT count(mobile1) FROM userTBL ;

Having절
앞에서 했던 SUM()을 다시 사용해서 사용자별 총 구매액을 구해보자.
```
SELECT userID, SUM(price * amount) 
    FROM buyTBL 
	GROUP BY userID ;
```
그런데, 이 중에서 총 구매액이 1,000 이상인 사용자에게만 사은품을 증정하고 싶다면 앞에서 배운 조건을 포함하는 WHERE 구문을 생각했을 것이다.
```
SELECT userID, SUM(price * amount) 
    FROM buyTBL  
	WHERE sum(price * amount) > 1000
    GROUP BY userID;
```
오류 메시지를 보면 집계 함수는 WHERE절에 나타날 수 없다는 얘기다. 이렇 때 사용되는 것이 HAVING절이다. HAVING은 WHERE와 비슷한 개념으로 조건을 제한하는 것이지만, 집계 함수에 대해서 조건을 제한하는 것이라고 생각하면 된다. 그리고, HAVING절은 꼭 GROUP BY절 다음에 나와야 한다. 순서가 바뀌면 안 된다.
```
SELECT userID, SUM(price * amount) 
	FROM buyTBL  
    GROUP BY userID
	HAVING sum(price * amount) > 1000;
```
추가로 총 구매액이 적은 사용자부터 나타내려면 ORDER BY를 사용하면 된다.
```
SELECT userID, SUM(price * amount)
    FROM buyTBL 
	GROUP BY userID 
    HAVING SUM(price * amount) > 1000
	ORDER BY SUM(price * amount) ;
```

ROLLUP
총합 또는 중간 합계가 필요하다면 GROUP BY절과 함께 WITH ROLLUP문을 사용하면 된다. 만약 분류별로 합계 및 그 총합을 구하고 싶다면 다음의 구문을 사용하자.
```
SELECT num, groupName, SUM(price * amount) 
    FROM buyTBL 
    GROUP BY groupName, num
    WITH ROLLUP ;
```
중간에 num열이 NULL로 되어 있는 추가된 행이 각 그룹의 소합계를 의미한다. 또 마지막 행은 각 소합계의 합계, 즉 총합계의 결과가 나왔다.
위 구문에서 num은 Primary Key이며, 각 항목이 보이는 효과를 위해서 넣어 준 것이다. 만약 소합계 및 총합계만 필요하다면 다음과 같이 num을 빼면 된다.
```
SELECT groupName, SUM(price * amount) `비용`
    FROM buyTBL 
	GROUP BY groupName
    WITH ROLLUP ;
```

SQL의 분류

DML
DML(Data Manipulation Language, 데이터 조작 언어)은 데이터를 조작(선택, 삽입, 수정, 삭제)하는데 사용되는 언어다. DML 구문이 사용되는 대상은 테이블의 행이다. 그러므로 DML을 사용하기 위해서는 꼭 그 이전에 테이블이 정의되어 있어야 한다.
SQL문 중에 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당된다. 또, 트랜잭션(Transaction)이 발생하는 SQL도 이 DML이다.
트랜잭션이란 쉽게 표현하면, 테이블의 데이터를 변경 (입력/수정/삭제)할 때 실제 테이블에 완전히 적용하지 않고, 임시로 적용시키는 것을 말한다. 그래서 만약 실수가 있었을 경우에 임시로 적용시킨 것을 취소시킬 수 있게 해준다.

SELECT도 트랜잭션을 발생시키기는 하지만, INSERT/UPDATE/DELETE와는 조금 성격을 달리하므로 별도로 생각하는 것이 좋다.

DDL
DDL(Data Definition Language, 데이터 정의 언어)은 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할을 한다. 자주 사용하는 DDL은 CREATE, DROP, ALTER 등이다. 한 가지 기억할 것은 DDL은 트랜잭션을 발생시키지 않는다는 것이다. 따라서 되돌림 (ROLLBACK)이나 완전 적용(COMMIT)을 시킬 수가 없다. 즉, DDL문은 실행 즉시 MySQL에 적용된다.

DCL
DCL(Data Control Language, 데이터 제어 언어)은 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문으로, GRANT/REVOKE/DENY 등이 이에 해당된다.

데이터의 변경을 위한 SQL문

데이터의 삽입 : INSERT

INSERT문 기본
INSERT는 테이블에 데이터를 삽입하는 명령어다. 기본적인 형식은 다음과 같다.
```
INSERT [INTO] 테이블[(열1, 열2, ···)] VALUES (값1, 값2 ···)
```
우선 테이블 이름 다음에 나오는 열은 생량이 가능하다. 하지만, 생략할 경우에 VALUES 다음에 나오는 값들의 순서 및 개수가 테이블이 정의된 열 순서 및 개수와 동일해야 한다.
```
CREATE TABLE testTbl1 (id int, userName char(3), age int);
INSERT INTO testTbl1 VALUES (1, '홍길동', 25);
```

INSERT INTO testTbl1(id, userName) VALUES (2, '설현');

열의 순서를 바꿔서 입력하고 싶을 때는 꼭 열 이름을 입력한 순서에 맞춰 나열해 줘야 한다.

INSERT INTO testTbl1(userName, age, id) VALUES ('하니', 26, 3);

자동으로 증가하는 AUTO_INCREMENT
테이블의 속성이 AUTO_INCREMENT로 지정되어 있다면, INSERT에서는 해당 열이 없다고 생각하고 입력하면 된다. AUTO_INCREMENT는 자동으로 1부터 증가하는 값을 입력해 준다. AUTO_INCREMENT로 지정할 때는 꼭 PRIMARY KEY 또는 UNIQUE로 지정해 줘야 하며 데이터 형은 숫자 형식만 사용할 수 있다. AUTO_INCREMENT로 지정된 열은 INSERT문에서 NULL 값을 지정하면 자동으로 값이 입력된다.
```
CREATE TABLE testTbl2(
	id int AUTO_INCREMENT PRIMARY KEY ,
	userName char(3),
	age int
);
INSERT INTO testTbl2 VALUES (NULL, '지민', 25);
INSERT INTO testTbl2 VALUES (NULL, '유나', 22);
INSERT INTO testTbl2 VALUES (NULL, '유경', 21);
```
계속 입력을 하다 보면 현재 어느 숫자까지 증가되었는지 확인할 필요도 있다. SELECT LAST_INSERT_ID(); 쿼리를 사용하면 마지막에 입력된 값을 보여준다. 이 경우에는 3을 보여줄 것이다.
그런데, 이후에는 AUTO_INCREMENT 입력값을 100부터 입력되도록 변경하고 싶다면 다음과 같이 수행하면 된다.
```
ALTER TABLE testTbl2 AUTO_INCREMENT = 100;
INSERT INTO testTbl2 VALUES (NULL, '찬미', 23);
```
증가값을 지정하려면 서버 변수인 @@auto_increment_increment 변수를 변경시켜야 한다.
다음 예제는 초깃값을 1000으로 하고 증가값은 3으로 변경하는 예제다.
```
ALTER TABLE testTbl3 AUTO_INCREMENT = 1000;
SET @@auto_increment_increment = 3;
INSERT INTO testTbl3 VALUES (NULL, '나연', 20);
INSERT INTO testTbl3 VALUES (NULL, '정연', 18);
INSERT INTO testTbl3 VALUES (NULL, '모모', 19);
```
한꺼번에 Insert
여러 개의 행을 한꺼번에 입력할 수도 있다. 3건을 입력하기 위해서 지금까지 다음과 같이 3개의 문장으로 입력했었다.
```
INSERT INTO 테이블이름 VALUES(값1, 값2···);
INSERT INTO 테이블이름 VALUES(값3, 값4···);
INSERT INTO 테이블이름 VALUES(값5, 값6···);
```
3건의 데이터를 한 문장에서 다음과 같이 입력할 수도 있다.
```
INSERT INTO 테이블이름 VALUES (값1, 값2···), (값3, 값4···), (값5, 값6···);
```

대량의 샘플 데이터 생성
이번에는 샘플 데이터를 입력하는 경우를 생각해보자. 지금까지 했던 방식으로 직접 키보드로 입력하려면 많은 시간이 걸릴 것이다. 이럴 때 INSERT INTO··· SELECT 구문을 사용할 수 있다. 이는 다른 테이블의 데이터를 가져와서 대량으로 입력하는 효과를 낸다.
```
형식:
INSERT INTO 테이블이름 (열 이름1, 열 이름2, ···)
	SELECT문 ;
```
물론, SELECT문의 결과 열의 개수는 INSERT를 할 테이블의 열 개수와 일치해야 한다.
```
CREATE TABLE testTbl4 (id int, Fname varchar(50), Lname varchar(50));
INSERT INTO testTbl4 (SELECT emp_no, first_name, last_name FROM employees.employees);
```
이렇듯 기존의 대량의 데이터를 샘플 데이터로 사용할 때 INSERT INTO ··· SELECT문은 아주 유용하다.
아예, 테이블 정의까지 생략하고 싶다면 앞에서 배웠던 CREATE TABLE ··· SELECT 구문을 다음과 같이 사용할 수도 있다.
```
CREATE TABLE testTbl444 (SELECT emp_no, first_name, last_name FROM employees.employees);
```

데이터의 수정 : UPDATE

기존에 입력되어 있는 값을 변경하기 위해서는 UPDATE문을 다음과 같은 형식으로 사용한다.

UPDATE 테이블이름
	SET 열1 = 값1, 열2 = 값2 ···
    WHERE 조건;

WHERE절은 생략이 가능하지만 WHERE절을 생략하면 테이블의 전체의 행이 변경된다.
다음 예는 'Kyoichi'의 Lname을 '없음'으로 변경하는 예다.

UPDATE testTbl4
	SET Lname = '없음'
    WHERE Fname = 'Kyoichi';

만약, 실수로 WHERE절을 빼먹고 UPDATE testTbl4 SET Lname = '없음'을 실행했다면 전체 행의 Lname이 모두 '없음'으로 변경된다. 실무에서도 이러한 실수가 종종 일어날 수 있으므로 주의가 필요하다. 원상태로 복구하기 위해서는 많은 복잡한 절차가 필요할 뿐만 아니라 다시 되돌릴 수 없는 경우도 있다.
가끔은 전체 테이블의 내용을 변경하고 싶을 때 WHERE를 생략할 수도 있는데, 예로 구매 테이블에서 현재의 단가가 모두 1.5배 인상되었다면 다음과 같이 사용할 수 있다.

SELECT price FROM buyTBL;

UPDATE buyTBL SET price = price * 1.5;

데이터의 삭제 : DELETE FROM

DELETE도 UPDATE와 거의 비슷한 개념이다. DELETE는 행 단위로 삭제하는데, 형식은 다음과 같다.

DELETE FROM 테이블이름 WHERE 조건;

만약, WHERE문이 생략되면 전체 데이터를 삭제한다.
testTbl4에서 'Aamer'사용자가 필요 없다면 다음과 같은 구문을 사용하면 된다.

SELECT Fname, Lname FROM testTbl4 WHERE Fname = 'Aamer';

DELETE FROM testTbl4 WHERE Fname = 'Aamer';
SELECT Fname, Lname FROM testTbl4 WHERE Fname = 'Aamer';

'Aamer'을 모두 지우는 것이 아니라 'Aamer' 중에서 상위 몇 건만 삭제하려면 LIMIT 구문과 함께 사용하면 된다.

INSERT INTO testTbl4 
(SELECT emp_no, first_name, last_name 
    FROM employees.employees 
    WHERE first_name = 'Aamer');

DELETE FROM testTbl4 WHERE Fname = 'Aamer' LIMIT 5;

이번에는 대용량 테이블의 삭제에 대해서 생각해 보자. 만약 대용량의 테이블이 더 이상 필요 없다면 어떻게 삭제하는 것이 좋을까? 실습을 통해서 효율적인 삭제가 어떤 것인지 확인하자. 또, 트랜잭션의 개념도 함께 살펴보자.

실습: 대용량의 테이블을 삭제하자.

step 1: 대용량의 테이블을 세 개 생성하자. employees에서 약 30만 건이 있는 테이블을 복사해서 사용하겠다.
CREATE TABLE bigtbl1 (SELECT * FROM employees.employees);
CREATE TABLE bigtbl2 (SELECT * FROM employees.employees);
CREATE TABLE bigtbl3 (SELECT * FROM employees.employees);
step 2: DELETE, DROP, TRUNCATE문으로 세 테이블을 모두 삭제한다. 세 구문 모두 테이블의 행을 삭제한다. (단, DROP문은 테이블 자체를 삭제한다.)
DELETE FROM bigtbl1 ;
DROP TABLE bigtbl2 ;
TRUNCATE TABLE bigtbl3 ; 
step 3: 결과를 비교하면, DELETE만 시간이 오래 걸리고 나머지는 짧은 시간이 걸린 것을 확인할 수 있다.
DML문인 DELETE는 트랜잭션 로그를 기록하는 작업 때문에 삭제가 오래 걸린다. 수백 만 건 또는 수천 만 건의 데이터를 삭제할 경우에 한참동안 삭제를 할 수도 있다. DDL문인 DROP문은 테이블 자체를 삭제한다. 그리고 DDL은 트랜잭션을 발생시키지 않는다고 했다. 역시 DDL문인 TRUNCATE문의 효과는 DELETE와 동일하지만 트랜잭션 로그를 기록하지 않아서 속도가 무척 빠르다. 그러므로 대용량 테이블 전체 내용을 삭제할 때는 테이블 자체가 필요 없을 경우에는 DROP으로 삭제하고, 테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는 것이 효율적이다.

조건부 데이터 입력, 변경

앞에서 INSERT문이 행 데이터를 입력해 주는 것에 대해 배웠다. 그러면 기본 키가 중복된 데이터를 입력하면 어떻게 될까? 당연히 입력되지 않는다. 하지만 100건을 입력하고자 하는데 첫 번째 한 건의 오류 때문에 나머지 99건도 입력되지 않는 것도 문제가 될 수 있다. MySQL은 오류가 발생해도 계속 진행하는 방법을 제공한다. 실습을 통해서 확인해 보자.

실습: INSERT의 다양한 방식을 실습하자.

step 1: 우선 멤버테이블(members)을 정의하고, 데이터를 입력하자. 지금은 연습 중이므로 기존 userTBL에서 아이디, 이름, 주소만 가져와서 간단히 만들겠다. 앞에서 배운 CREATE TABLE··· SELECT를 활용하면 된다.
CREATE TABLE members 
(SELECT userID, name, addr FROM shopdb.userTBL LIMIT 3);
step 2: 여러 건 입력 시에 오류가 발생해도 나머지는 계속 입력되도록 하자.
step 2-1: 데이터를 추가로 3건 입력해 보자. 그런데, 첫 번째 데이터에서 PK를 중복하는 실수를 했다.
INSERT INTO members 
VALUES 
('BBK', '비비코', '미국'),
('SJH', '서장훈', '서울'),
('HJY', '현주엽', '경기');
첫 번째 오류 때문에 나머지 2건도 입력이 되지 않았다.
step 2-2: INSERT IGNORE문으로 바꿔서 다시 실행해 보자.
INSERT IGNORE INTO members 
VALUES 
('BBK', '비비코', '미국'),
('SJH', '서장훈', '서울'),
('HJY', '현주엽', '경기');
첫 번째 데이터는 비록 오류 때문에 들어가지 않았지만, 2건은 추가로 입력되었다. INSERT IGNORE는 PK 중복이더라도 오류를 발생시키지 않고 무시하고 넘어간다. ([Output] 메시지를 보면 중복 키 경고 메시지가 보일 것이다.)

step 3: 이번에는 입력 시에, 기본 키가 중복되면 데이터가 수정되도록 해보자.
SELECT * FROM members ;
INSERT INTO members 
VALUES
('BBK', '비비코', '미국') ,
('DJM', '동짜몽', '일본') 
ON DUPLICATE KEY 
UPDATE name = VALUES(name), addr = VALUES(addr);
첫 번째 행에서 BBK는 중복이 되었으므로 UPDATE문이 수행되었다. 그리고 두 번째 입력한 DJM은 없으므로 일반적인 INSERT처럼 데이터가 입력되었다. 결국 ON DUPLICATE UPDATE는 PK가 중복되지 않으면 일반 INSERT가 되는 것이고, OK가 중복되면 그 뒤의 UPDATE문이 수행된다.
*PK만 검사하는게 아니라 UNIQUE 키도 검사한다. 즉, 고유한 값을 유지해야하는 제약조건을 어겼을 경우 UPDATE를 진행한다.
확인해보기
INSERT INTO members 
VALUES ('HHH', '동짜몽', '중국');
INSERT INTO members 
VALUES
('HHH', '동짜몽', '중국')
ON DUPLICATE KEY 
UPDATE userID = VALUES(userID), addr = VALUES(addr);

WITH절과 CTE

WITH절과 CTE 개요

WITH절은 CTE(Common Table Expression)를 표현하기 위한 구문으로 MySQL 8.0 이후부터 사용할 수 있다. CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며, 더 간결한 식으로 보여지는 장점이 있다. CTE는 ANSI-SQL99 표준에서 나온 것이다. 기존의 SQL은 ANSI-SQL92를 기준으로 한다. 하지만, 최근의 DBMS는 대개 ANSI-SQL99와 호환되므로 다른 DBMS에서도 같거나 비슷한 방식으로 응용된다.
CTE는 비재귀적(Non-Recursive) CTE와 재귀적(Recursive) CTE 두 가지가 있다. 이 책에서는 주로 사용되는 비재귀적 CTE에 대해서 학습한다고 한다.

비재귀적 CTE

비재귀적 CTE는 말 그대로 재귀적이지 않은 CTE다. 단순한 형태이며, 복잡한 쿼리문장을 단순화 시키는 데에 적합하게 사용될 수 있다. 우선 비재귀적 CTE의 형식을 살펴보자.

WITH CTE_테이블이름(열 이름)
AS
(
	<쿼리문>
)
SELECT 열 이름 FROM CTE_테이블이름 ;

비재귀적 CTE에는 SELECT 필드들 FROM CTE_테이블이름 구문 외에 UPDATE 등도 가능하지만, 주로 사용되는 것은 SELECT문이다.

위의 형식이 좀 생소해 보일 수도 있지만, 위쪽을 떼버리고 그냥 SELECT 열 이름 FROM CTE테이블이름 구문만 생각해도 된다. 그런데 이 테이블은 기존에는 실제 DB에 있는 테이블을 사용하지만, CTE는 바로 위의 WITH절에서 정의한 CTE테이블이름을 사용하는 것만 다르다. 즉, WITH CTE_테이블이름(열 이름) AS··· 형식의 테이블이 하나 더 있다고 생각하면 된다.
쉽게 이해하기 위해서 앞에서 했던 buyTBL에서 총 구매액을 구하는 것을 다시 살펴보자.

SELECT userID `사용자`, SUM(price * amount) `총 구매액`
FROM buyTBL GROUP BY userID;

위의 결과를 총 구매액이 많은 사용자 순서로 정렬하고 싶다면 어떻게 해야 할까? 물론, 앞의 쿼리에 이어서 ORDER BY문을 첨가해도 된다. 하지만, 그럴 경우에는 SQL문이 더욱 복잡해 보일 수 있으므로 이렇게 생각해 보자. 위의 쿼리의 결과가 바로 abc라는 이름의 테이블이라고 생각하면 어떨까? 그렇다면, 정렬하는 쿼리는 다음과 같이 간단해진다.

SELECT * FROM abc ORDER BY 총구매액 DESC

이것이 CTE의 장점 중 하나다. 구문을 단순화시켜 준다. 지금까지 얘기한 실질적인 쿼리문은 다음과 같이 작성하면 된다.

WITH abc(userID, total)
AS
(
	SELECT userID `사용자`, SUM(price * amount) `총 구매액`
    	FROM buyTBL GROUP BY userID
)
SELECT * FROM abc ORDER BY total DESC;

제일 아래의 'FROM abc' 구문에서 abc는 실존하는 테이블이 아니라, 바로 위에 네모로 표시된 WITH 구문으로 만든 SELECT의 결과다. 단, 여기서 'AS (SELECT ···'에서 조회하는 열과 'WITH abc (···'과는 개수가 일치해야 한다.
다른 예로 하나 더 연습을 해보자. 회원 테이블(userTBL)에서 각 지역별로 가장 큰 키를 1명씩 뽑은 후에, 그 사람들 키의 평균을 내보자. 만약, 전체의 평균이라면 AVG(height)만 사용하면 되지만, 각 지역별로 가장 큰 키의 1명을 우선 뽑아야 하므로 얘기가 좀 복잡해진다. 이럴 때 CTE를 유용하게 사용할 수 있다. 한꺼번에 생각하지 말고, 하나씩 분할해서 생각해 보자.

"각 지역별로 가장 큰 키"를 뽑는 쿼리는 다음과 같다.
```
SELECT addr, MAX(height) FROM userTBL GROUP BY addr
```

위 쿼리를 WITH 구문으로 묶는다.

WITH cte_usertbl(addr, height_avg) 
AS (SELECT addr, MAX(height) FROM userTBL GROUP BY addr)

"키의 평균을 구하는 쿼리를 작성한다."
```
SELECT AVG(height_avg) FROM cte_usertbl;
```
2와 3의 쿼리를 합친다.
```
WITH cte_usertbl(addr, height_avg)
AS (SELECT addr, MAX(height) FROM userTBL GROUP BY addr)
SELECT AVG(height_avg) `각 지역별 최고키의 평균` FROM cte_usertbl;
```
CTE는 뷰와 그 용도는 비슷하지만 개선된 점이 많다. 또한, 뷰는 계속 존재해서 다른 구문에서도 사용할 수 있지만, CTE와 파생 테이블은 구문이 끝나면 같이 소멸된다. 즉, 위의 예에서 cte_usertbl은 다시 사용할 수 없다.
추가로 좀더 CTE에 대해서 얘기하면 CTE는 다음 형식과 같은 중복 CTE가 허용된다.
```
WITH
AAA (컬럼들)
	AS (AAA의 쿼리문),
BBB (컬럼들)
	AS (BBB의 쿼리문),
CCC (컬럼들)
	AS (CCC의 쿼리문)
SELECT * FROM [AAA | BBB | CCC] ;
```
주의할 점은 CCC의 쿼리문에서는 AAA나 BBB를 참조할 수 있지만, AAA의 쿼리문이나 BBB의 쿼리문에서는 CCC를 참조할 수 없다. 즉, 아직 정의되지 않은 CTE를 미리 참조할 수 없다.