6장 : 데이터 삽입,수정,삭제와 WITH 절

section 1 : 데이터 삽입,수정,삭제

1. sql문 종류

• DML : 데이터를 검색 및 삽입,수정 삭제하는데 사용하는 언어, 반드시 테이블 정의되어 있어야 함, 트랜잭션이 발생하는 sql문도 DML에 해당
  ( INSERT, UPDATE, DELETE )
• DDL : 데이터베이스,테이블,뷰,인덱스 등의 데이터베이스 개체를 생성,삭제,변경하는 데 사용하는 언어
  ( CREATE,DROP,ALTER,TRUNCATE )
• DCL : 사용자에게 어떤 권한 부여하거나 빼앗을 때 사용
  ( GRANT, REVOKE, DENY )

2. insert문

# 예시 1 
INSERT [INTO] 테이블이름[(열1,열2,,,)] VALUES (값1,값2,,,)

# 예시 2
USE cookDB;
CREATE TABLE testTBL1(id int, userName char(3), age int);
INSERT INTO testTBL1(1,'뽀로로',16);

# 예시 3
INSERT INTO testTBL1(id,userName) VALUES(2,'크롱');

# 예시 4
INSERT INTO testTBL1(userName, age, id) VALUES('루피',14,3);

2.1.AUTO_INCREMENT 키워드

• 테이블 생성할 때 특정 열의 속성이 AUTO_INCREMENT로 지정돼 있으면 INSERT문에서는 해당 열이 없다고 생각하고 입력하면 됨
• 특정 열을 AUTO_INCREMENT로 지정할때는 반드시 PRIMARY KEY 또는 UNIQUE(유일한 값)으로 설정해야 함
• 데이터 형식이 숫자인 열에만 사용 가능

CREATE TABLE testTBL2(
id int AUTO_INCREMENT PRIMARY KEY,
userName char(3),
age int
);
INSERT INTO testTBL2 VALUES (NULL,'에디',15);
INSERT INTO testTBL2 VALUES (NULL,'포비',12);

이런 경우도 가능해요.

• AUTO_INCREMENT 입력값을 100부터 시작하도록 변경

ALTER TABLE testTBL2 AUTO_INCREMENT=100;
INSERT INTO testTBL2 VALUES(NULL,'에디',15);
INSERT INTO testTBL2 VALUES(NULL,'통통이',12);
SELECT * FROM testTBL2;

id	userName	age
100	에디	15
101	통통이	12

• AUTO_INCREMENT로 증가되는 값 지정

CREATE TABLE testTBL2(
id int AUTO_INCREMENT PRIMARY KEY,
userName char(3),
age int
);
ALTER TABLE testTBL2 AUTO_INCREMENT=100;
SET @auto_increment_increment=3;
INSERT INTO testTBL2 VALUES (NULL,'에디',15);
INSERT INTO testTBL2 VALUES (NULL,'통통이',12);
INSERT INTO testTBL2 VALUES (NULL,'크롱',12);
SELECT * FROM testTBL2;

id	userName	age
100	에디	15
103	통통이	12
106	크롱	12

여기서 잠깐! 🖐
꿀팁🍯 : 데이터 삽입 시 코드를 줄이려면 여러 행 한꺼번에 입력 가능

INSERT INTO testTBL2 VALUES (NULL,'에디',15), (NULL,'통통이',12), (NULL,'크롱',12);
SELECT * FROM testTBL2;

• mysql은 auto commit(모든구문끝날때마다자동커밋)이므로 rollback은 안되는 경우가 기본
• 설정에 따라 commit 범위가 다 다름

2.2.대량 데이터 삽입

INSERT INTO ,,, SELECT 문 사용하기

# employees 테이블의 데이터를 가져와 testTBL3 테이블에 입력
USE cookDB;
CREATE TABLE testTBL3(id int,Fname varchar(50),Lname varchar(50));
INSERT INTO testTBL3
	SELECT emp_no,first_name,last_name FROM employees.employees;
    
# 아예 테이블 정의까지 생략
CREATE TABLE testTBL3
	(SELECT emp_no,first_name,last_name FROM employees.employees);
SELECT * FROM testTBL3 LIMIT 3;

# 열이름 바꾸어 테이블 생성
CREATE TABLE testTBL3
	(SELECT emp_no AS id,first_name AS Fname,last_name AS Lname FROM employees.employees);
SELECT * FROM testTBL3 LIMIT 3;

3. update문

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

WHERE절을 생략할 순 있지만, 그렇게 되면 테이블 전체의 행이 수정됨

# Fname이 Kyoichi인 사람의 Lname을 없음으로 변경
USE cookDB;
UPDATE testTBL4
SET Lname='없음'
WHERE Fname='Kyoichi';

# 구매 테품 가경ㄱ 모두 현재 단가보다 1.5배 인상
UPDATE buyTBL
SET price=price*1.5;

4. delete문

DELETE FROM 테이블이름 WHERE 조건;

# Fname이 Aamer인 상위 5건 삭제
DELTE FROM testTBL4 WHERE Fname='Aamer' LIMIT 5;

예시를 통해 대용량 테이블 삭제

CREATE TABLE bigTBL1(SELECT * FROM employees.employees)
DELETE FORM bitTBL1;
DROP TABLE bitTBL2;
TRUNCATE TABLE bitBL3;

결과를 보면 DELETE문만 실행시간이 오래 걸린다.

WHY??

• DML인 DELETE문은 트랜잭션 로그를 기록하는 작업을 하기 때문에 삭제하는 데 시간이 오래 걸림
• DDL인 DROP문은 테이블 자체를 삭제하고 트랜잭션 로그를 기록하지 않음
• DDL인 TRUNCATE문은 DELETE문과 결과 동일하지만 트랜잭션 로그를 기록하지 않음
  

• 대용량 테이블 삭제할 때, 큰 일을 작게 분할해서 사용하는 게 많음.
• delete 뒤에 limit 를 주면 주기적으로 많은 양을 삭제 가능
• 많은 양의 데이터를 삭제하는 것보다 신규테이블생성하고 과거테이블과 바꿔치기(우회,,해서 과거테이블 drop) 하는게 더 이득
• 갑자기 많은 것을 삭제하는것(한방쿼리)는 분할해서 작업하는 것이 더 현명
• drop은 되게 위험, truncate나 delete가 유용

5. 조건부 데이터 삽입과 수정

"오류가 발생해도 계속 삽입되도록 설정"하는 실습을 통해 알아봅시다

1. 새 멤버 테이블 생성
2. 멤버 테이블에 데이터 3건 추가
3. 데이터 3건 중 1건에서 기본키를 중복입력하는 실수
   INSERT INTO memberTBL VALUES ('KHD', '강후덜','미국');
4. SELECT * FROM memeberTBL; 로 조회하면 데이터 그대로 3건. 왜냐하면 1건의 오류 때문에 나머지 2건도 삽입되지 않은 것.
5. INSERT IGNORE INTO문으로 수정하면 중복오류가 난 1건은 삽입되지 않지만 나머지 2건은 데이터 삽입됨.
   INSERT IGNORE INTO memberTBL VALUES ('KHD', '강후덜','미국');
6. 기본키가 중복되면 새로 삽입한 내용으로 수정. 기본키가 중복되지 않으면 일반 INSERT문처럼 동작하고 기본키가 중복되면 그 뒤의 UPDATE문을 수행
   INSERT INTO memberTBL VALUES ('KHD','강후덜','미국') ON DUPLICATE KEY UPDATE userName='강후덜', addr="미국";

section 2 : 윈도우 함수와 피벗

1. 윈도우 함수의 개념

• 테이블의 행과 행 사이 관계를 쉽게 정의하기 위해 MySQL에서 제공하는 함수
• OVER절이 들어간 함수
• 집계함수, 비집계함수 등과 함께 사용됨

2. 순위 함수

• 결과에 순번 또는 순위를 매기는 함수
• 비집계 함수 중 RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER() 등이 해당
• 장점 : 구문이 단순=>코드명확=>수정 쉽고 효율성 뛰어남

<순위함수이름>() OVER(
[PARTITION BY <partition_by_list>]
ORDER BY <order_by_list>)

"순위 함수 사용하기"예제를 통해 알아봅시다
1. cookDB초기화
2. 키가 큰 순, 키가 동일 시 이름 가나다순,으로 정렬

USE cookDB;
SELECT ROW_NUMBER() OVER(ORDER BY height DESC, userName ASC) "키큰순위", userName,addr,height FROM userTBL;

3. 각 지역별로 순위 매기기

SELECT addr, ROW_NUMBER() OVER(PARTITION BY addr ORDER BY height DESC, userName ASC) "지역별 키큰순위", userName, height FROM userTBL;

4. 키 동일 시 동일한 등수 처리

SELECT DENSE_RANK() OVER(ORDER BY height DESC) "키큰순위", addr, height FROM userTBL;

5. 동일한 등수 시 그 다음 등수는 갯수대로 등수 매기기

SELECT RANK() OVER(ORDER BY height DESC) "키큰순위", userName, height FROM userTBL;

6. 10명 사용자를 키 큰 순으로 정렬하고 2개 반으로 나누는 경우

SELECT NTILE(2) OVER(ORDER BY height DESC) "반번호", userName, addr, height FROM userTBL;

3. 분석 함수

• 비집계 함수 중에서 CUME_DIST(), LEAD(), FIRST_VALUE(), LAG(), LAST_VALUE() PERCENT_RANK()

1. 특정 데이터와의 차이 값 구하기
2. 누적 백분율 구하기

4. 피벗

• 한 열에 포함된 여러 값을 여러 열로 변환해 출력하고 필요하면 집계까지 수행
• 수행 결과 피벗 테이블 생성

p 207 그림 6-23 참고
1. 샘플 테이블 만들기
2. 피벗 테이블 만들기
계절별로 판매 수량이 합산되게 함

SELECT uName,
	SUM(CASE WHEN season='봄' THEN amout END) AS '봄'
    SUM(CASE WHEN season='여름' THEN amout END) AS '여름'
    SUM(CASE WHEN season='가을' THEN amout END) AS '가을'
    SUM(CASE WHEN season='겨울' THEN amout END) AS '겨울'
FROM pivotTest
GROUP BY uName;

section3 : WITH절과 CTE

1. WITH절과 CTE의 개요

• with절 : 기존의 뷰, 파생 테이블, 임시 테이블 등을 더 간결하게 표현하는 cte를 포함한 구문
• cte : 비재귀적 cte/재귀적 cte

2. 비재귀적 CTE

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

• CTE는 바로 위의 WITH절에서 정의한 CTE_테이블이름 을 사용.
• 즉, WITH CTE_테이블이름(열이름) AS...형식의 테이블이 하나 더 있다고 보는 것

1. 구매 테이블에서 총구매액을 구하는 쿼리문 예제를 통해 알아봅시다
2. 회원 테이블에서 각 지역별로 가장 키가 큰 사람을 1명씩 뽑아 키의 평균을 구하는 예제를 통해 알아봅시다.

• CTE의 용도는 뷰와 비슷하지만 개선된 부분이 많음
• 뷰는 계속 존재하기에 다른 구문에서도 사용가능하지만 CTE와 파생 테이블은 구문이 끝나면 소멸되므로 다른 구문에서 사용 불가능
• 또한, 아직 정의 되지 않은 CTE를 참조하는 것은 불가능하다

# AAA나 BBB 쿼리문에서는 CCC를 참조할 수 없음
AAA(칼럼들)
AS (AAA의 쿼리문),
	BBB(칼럼들)
    AS (BBB의 쿼리문),
    	CCC(칼럼들)
        AS (CCC의 쿼리문)

7장 : 데이터 형식과 내장 함수

section 1 : 데이터 형식의 종류

1. 데이터 형식의 개요

2. 데이터 형식의 종류

2.1.숫자 데이터 형식

• 정수, 실수 등의 숫자 저장
• DECIMAL : 정확한 수치 저장
• FLOAT,REAL : 근사치 저장, 상당히 큰 숫자 저장 가능
• TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• UNSIGNED : 예약어

2.2.문자 데이터 형식

• CHAR : char(100)에 'abc'저장->3자리차지하고 나머지 97자리 비워둠
  / INSERT,UPDATE문 쓸 때 이게 더 좋은 성능
• VARCHAR : 가변길이 저장하므로 varchar(100)에 'abc'저장->3자리만 차지
  / 저장공간 효율
• BINARY, VARBINARY : 바이트 단위의 이진 데이터 값 저장
• TEXT : 대용량 글자 저장, 필요한 크기에 따라 TINYTEXT, TEXT, MIDIUMTEXT, LONGTEXT 사용
• BLOB : 사진, 동영상, 문서 파일 등의 대용량 이진 데이터를 저장하는 데 사용
• ENUM : 열거형 데이터 저장할 때 사용
• SET : 데이터를 세트로 묶어서 저장할 때 사용

• 가능하면 varchar를 사용 => 성능차이 많이 없음
• char를 많이 안씀 => 성능에는 유리하지만, 예외의 상황이 생겼을 때 비교하기 힘듦

2.3.날짜와 시간 데이터 형식

<날짜와 시간 데이터 형식의 종류>

• DATE : 바이트 수 3
• TIME : 바이트 수 3
• DATETIME : 바이트 수 8
• TIMESTAMP : 바이트 수 4
• YEAR : 바이트 수 1

• CAST() : 지정된 데이터 형식으로 값 변경해줌

2.4.기타 데이터 형식

• GEOMETRY, POINT, POLYGON, JSON 등

section 2 : 변수와 형 변환

1. 변수의 선언과 활용

sql도 일반 프로그래밍 언어처럼 변수 선언하고 사용가능

SET @변수이름 = 변수값;
SELECT @변수이름;

"변수 사용하기"실습을 통해 알아보자

USE cookDB;

SET @myVar1=5;
SET @myVar2=3;
SET @myVar3=4.25;
SET @myVar4='MC 이름==> ';

SELET @myVar1;
SELET @myVar2+@myVar3;

SELET @myVar4, userName FROM userTBL WHERE height >180;

@myVar1	@myVar2+@myVar3	@myVar4	userName
5	7.2500000000000000	MC이름==>	강호동
		MC이름==>	박수홍

LIMIT절에는 원칙적으로 변수 사용할 수 없으나 PREPARE문과 EXECUTE문을 활용하면 가능

SET @myVar1=3;
# 쿼리문을 준비만하고 실행하지 X
PREPARE myQuery
	FROM 'SELECT userName, height FROM userTBL ORDER BY height LIMIT ?';
# EXECUTE쿼이름 구문을 만나는 순간 실행됨
# 'USING @ 변수' 의 변수가 'LIMIT ?' 의 물음표에 대입
EXECUTE myQuery USING @myVar1;

2. 데이터 형식과 형 변환

2.1.데이터 형식 변환 함수

• 일반적인 데이터 형식 변환 함수 CAST(), CONVERT()

CAST(expression AS 데이터형식[(길이)])
CONVERT(expression, 데이터형식[(길이)])

예시를 통해 알아봅시다.

USE cookDB:
SELECT AVG(amount) AS '평균 구매 개수' FROM buyTBL;

.	평균구매개수
.	2.9803

❗️ 구매 개수는 실수가 아닌 정수여야 함❗️

SELECT CAST(AVG(amount) AS SIGNED INTEGER) AS '평균 구매 개수' FROM buyTBL;
또는
SELECT CONVERT(AVG(amount), SIGNED INTEGER) AS '평균 구매 개수' FROM buyTBL;

2.2.임시적인 형 변환

# 문자와 문자를 더함 => 정수로 변환후 처리
SELECT '100'+'200'; = 300
# CONCAT()은 문자열을 연결하는 함수이므로 문자열 그대로 처리
SELECT CONCAT('100','200'); = 100200
# CONCAT()함수안의 정수 100을 문자열로 변환 후 처리
SELECT CONCAT(100,'200'); = 100200
# 비교 연산으로 인해 3이 들어간 문자열이 숫자 3으로 변경돼 결국 '1>3'으로 처리 => 결과 거짓
SELECT 1 > '3mega'; = 0
# 위와 동일한 결과
SELECT 4 > '3MEGA'; = 0
# 앞에 'm'이 들어간 문자열이 숫자로 변경되며 그냥 0 이 돼서 결국 '0=0'으로 처리 => 결과 참
SELECT 0 = 'mega3'; = 1(참)

section 3 : 내장 함수

1. 내장함수 개요

• 제어흐름함수,문자열함수,수학함수,날짜/시간함수,전체텍스트 검색함수

2. 제어 흐름함수

• IF(수식,참,거짓), IFNULL(수식1,수식2), NULLIF(수식1,수식2), CASE...WHEN...ELSE...END

3. 문자열 함수

• ASCII(아스키코드), CHAR(숫자), BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열), CONCAT(문자열1,문자열2,,,), CONCAT_WS(문자열2,문자열2,,,)
• ELT(위치,문자열1,문자열2,,,),FIELD(찾을문자열, 문자열1,문자열3,,,),FIND_IN_SET(찾을문자열,문자열리스트), INSTR(기준문자열,부분문자열), LOCATE(부분문자열,기준_문자열)
• FORMAT, BIN, HEX, OCT, INSERT(기준문자열,위치,길이,삽입할문자열), LEFT, RIGTH, LOWER, UPPER, LPAD, RPAD, LTRIM, RTRIM, TRIM, TRLM, REPEAT, REPLACE, REVERSE, SPACE, SUBSTRING, SUBSTRING_INDEX

4. 수학 함수

• ABS, CELING, FLOOR, FOUND, CONV, DEGRESS, RADIANS, PI, MOD, POW, SQRT, RAND, SIGN, TRUNCATE

5. 날짜/시간 함수

• ADDDATE, SUBDATE,ADDTIME, SUBTIME, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND, DATE, TIME, DATEDIFF, TIMEDIFF, DAYOFWEEK, MONTHNAME, DAYOFYEAR, LAST_DAY, MAKEDATE, MAKETIME, PERION_ADD, PERIOD_DIFF, QUARTER, TIME_TO_SEC, CURDATE, CURTIME, NOW, SYSDATE

6. 시스템 정보 함수

• USER, DATABASE, FOUND_ROWS, ROW_COUNT, SLEEP, VERSION

section 4 : JSON 데이터와 대용량 데이터 저장

1. json 데이터

JSON데이터?

• 웹 환경이나 모바일 응용 프로그램 등에서 데이터를 교환하기 위해 만든 개방형 표준 포맷
• 속성(key)과 값(value)의 쌍
• javascript에서 파생됐지만 종속되지 않은 독립적인 데이터 포맷

예제를 통해 알아봅시다.

1. "회원 테이블에서 키가 180 이상인 사람의 이름과 키를 출력"

USE cookDB;
SELECT JSON_OBJECT('name', userName, 'height', height) AS 'JSON값'
FROM userTBL
WHERE height>=180;

.	JSON값
.	{"name":"a","height":120}
.	{"name":"b","height":180}
.	{"name":"c","height":168}
.	{"name":"d","height":190}

2. "@json변수에 JSON 데이터 대입하면서 테이블 이름 userTBL로 지정"

SET @json='{"userTBL":
 [
	{"name":"a","height":120}
	{"name":"b","height":180}
	{"name":"c","height":168}
	{"name":"d","height":190}
 ]
}';
# JSON_VALID() : 문자열이 JSON 형식을 만족하면 1, 아니면 0 반환 => 1
SELECT JSON_VALID(@json) AS JSON_VALID;
# JSON_SEARCH() : 세번째 파라미터에 주어진 문자열 위치 반환
# 'one' : 처음으로 매치되는 하나 반환 / 'all' : 매치되는 모든 것 반환
# 위치 : $.userTBL[2].name
SELECT JSON_SEARCH(@json, 'one', 'c') AS JSON_SEARCH;
# JSON_SEATCH()와 반대로 지정된 위치의 값 반환
# 값 : c
SELECT JSON_EXTRACT(@json, '$userTBL[2].name') AS JSON_EXTRACT;
# JSON_INSERT() : 새로운 값 추가
.
# JSON_REPLACE() : 값 변경
.
# JSON_REMOVE() : 지정된 항목 삭제
.

2. 대용량 데이터 저장

• MySQL은 대용량 데이터를 저장하기 위해 LONGTEXT, LONGBLOB 데이터 형식을 지원함
• 위 데이터 형식 이용하면 약 4GB 파일을 하나의 데이터로 저장 가능

영화 한편의 대본(LONGTEXT 형식), 동영상 파일(LONGBLOB 형식) 를 사용하는 예제를 통해 알아봅시다.
1. 대용량 텍스트 파일과 동영상 준비
2. 영화 데이터베이스 만들기

CREATE DATABASE moviDB;
USE movieDB;
CREATE TABLE movieTBL(
movie_id INT,
movie_title VARCHAR(30),
movie_director VARCHAR(20),
movie_star VARCHAR(20),
movie_script LONGTEXT,
movie_film LONGBLOB
) DEFAULT CHARSET=utf8mb4;

3. 대용량 데이터 입력하기

INSERT INTO movieTBL VALUES(1, '쉰들러 리스트', '스필버그', '리암 니슨',
	LOAD_FILE('C:/SQL/Movies/SCHINDLER.txt'), LOAD_FILE('C:/SQL/Movies/SCHINDLER.mp4'));

❗️ 하지만 조회해보면 영화 대본과 영화 동영상이 NULL값으로 표시됨
💡 왜냐하면 아무것도 입력되지 않았기 때문이다.
❗️ 왜 아무것도 입력되지 않았나요?
💡 1. 최대 패킷 크기(최대 파일 크기)가 설정된 시스템 변수인 max_allowed_packet 값을 수정해주어야 함
💡 2. 파일을 업로드하거나 다운로드할 폴더 경로를 별도로 허용해주어야 함


4. 최대 파일 크기와 허용된 파일의 경로 추가하기

# my.ini 파일 확인
CD %PROGRAMDATA%
CD MySQL
CD "MySQL Server 8.0"
DIR
# my.ini 열기
NOTEPAD my.ini
# ctrl+f로 max_all 검색해서 max_allowed_packet 값 변경
max_allowed_packet=1024M
# ctrl_f로 secure-file-priv 검색해서 아래에 한줄 추가하고 저장
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
secure-file-priv="C:/SQL/Movies"
# my.ini 파일 변경해주었으니 서버 재시작
NET STOP MySQL
NET START MySQL

5. 다시 대용량 데이터 입력하기
   성공!!
6. 입력된 데이터를 파일로 내려받기
   영화 아이디가 1인 쉰들러 리스트의 영화대본을 SCHINDLER_out.txt 파일로 내려받기

SELECT movie_script FROM movieTBL WHERE movie_id=1
	INTO OUTFILE 'C:/SQL/Movies/SCHINDLER_out.txt'
    	# 아래 명령어 = 줄 바꿈 문자도 그대로 저장하기 위한 옵션
	LINES TERMINATED BY '\\n';