MySQL 요약 정리

MySQL 공부한 것들을 간단 요약 정리하고자 한다.

추후 업데이트를 하며 살을 붙여 갈 예정이다.

기본 관행은 아니지만,

• 예약어(USE,SELECT 등)는 대문자로, 사용자 정의터(테이블 이름, 열 이름)등은 대,소문자 섞어서 영문으로 사용한다.
• 주석처리

-- 주로 한줄 주석처리시 사용(‘ — ’바로 뒤 스페이스바 한번 눌러야한다.)

/* 한줄이상 주석처리 필요시 활용

두줄

세줄 */

• 별칭Alias 지정방법: Column명 (AS) 별칭

ex) SELECT studentId AS 학번, gender 성별, admission_date 입학일

1. SQL기분 구문

1–1. DML(Data Manipulation Language) ‘데이터 조작 언어’ 로 선택, 삽입, 수정, 삭제하는데에 사용되는 언어다. DML구문이 사용되는 타겟은 테이블의 ‘행’이다.

SQL구문 중에 SELECT / INSERT / UPDATE / DELETE 구문이 해당된다. 트랜잭션이 발생하는 SQL도 DML이다.

• 트랜잭션이란? 테이블의 데이터를 변경(입력/수정/삭제)할 때 실제 테이블에 완전히 적용하지 않고 임시로 적용시키는 것을 말한다.

1–2. DDL(Data Definition Language) ‘데이터 정의 언어‘로 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할을 한다. 주로 사용하는 DDL은 CREATE / DROP / ALTER 등이다. DDL은 트랜잭션을 발생시키지 않으며, 실행 즉시 MySQL에 적용된다.

1–3. DCL(Data Control Language) ’데이터 제어 언어‘로 사용자에게 어떤 권한을 부여하거나 빼앗을 때 사용하용 구문으로 GRANT / REVOKE / DENY가 해당된다.

2. 데이터 조회 구문

• SELECT문: 원하는 데이터를 가져올 수 있는 기본적인 구조

<SELECT *

______FROM 테이블명>

• 특정 조건의 데이터만 조회

<SELECT 컬럼명

_______FROM 테이블명

_______ WHERE 조건절;>

=>관계연산자의 사용: AND / OR / BETWEEN…AND / IN() / LIKE / ANY / ALL / SOME / 서브쿼리(SubQuery, 하위쿼리)

• 조회된 값 정렬: ORDER BY
• 중복제거: DISTINCT
• 출력값 갯수제한: LIMIT
• 테이블복사: CREATE TABLE … SELECT (복사 시 PK나 FK 등 제약조건은 복사 안됨 주의)

＊GROUP BY절: 집계함수와 함께 사용

• 집계함수란? 데이터를 그릅화 해주는 기능들

* HAVING절: GROUP BY의 조건절로 생각하면 된다.

• ROLLUP: 총합 혹은 중간합계가 필요하다면 GROUP BY절과 함께 WITH ROLLUP구문을 사용하면 된다.

3. 데이터 변경구문

• INSERT문: 데이터 추가

< INSERT INTO 테이블명(…)

______VALUES (값…..); >

• UPDATE문: 데이터 수정

<UPDATE 테이블이름

______SET 열1=값1 , 열2=값2, …

______WHERE 조건; >

• DELETE문: 데이터 삭제

< DELETE FROM 테이블명

______WHERE 조건; >

* 데이터 삭제의 또 다른 방법

— DROP: 테이블 자체 삭제

— TRUNCATE: 테이블의 구조만 남겨두고 삭제

4. 인덱스 => 성능부분에 중요함 (Full Table Scan)～많은 시간 & 시스템 과부하초래 , JOIN쿼리에서 많이 활용

5. 뷰 => 진짜 테이블에 링크개념으로, view의 실체는 SELECT구문을 통하여 발현됨

6. 스토어드 프로지서(함수,호출) => 동시작업 & 반복작업에 많이 활용.

DELIMETER//

CREATE PROCEDURE myProc()

BEGIN

_______SELECT * FROM membertbl WHERE memberName=’ALICE’;

_______SELECT * FROM producttbl WHERE productName=’냉장고’;

END//

DELIMETER;

7. 트리거 => INSERT / UPDATE / DELETE작업이 발생하면 실행되는 코드 (ex.원본데이터 삭제 전 복사작업)

DELIMETER//

CREATE TRIGGER trg_deleteedMembertbl

_______AFTER DELETE

_______ON membertbl

_______FOR EACH ROW

BEGIN

_______INSERT INTO deletedMembertbl

______________VALUES (OLD.memberID, OLD.memberName,,,,);

END//

DELIMETER;

8.데이터베이스의 백업 및 관리

백업: 현재의 데이터베이스를 다른 매체에 보관하는 작업

복원: 데이터베이스에 문제가 발생 시 다른 매체에 백업된 데이터를 이용해서 원상태로 돌려놓는 작업

9. 데이터베이스 모델링

10. SQL의 Data Type(데이터형)

- 숫자 데이터 형식

- 문자 데이터 형식: VARCHAR형식은 가변길이 문자형으로 VARCHAR(100)에 ’ABC’ 3글자를 저장할 경우 3자리만 사용하게 된다. 공간을 효율적으로 운영할 수 있다는 장점이 있다.

하지만, CHAR형식으로 설정하는 것이 INSERT / UPDATE시 더 좋은 성능을 발휘한다.

=> MySQL에서 기본적으로 CHAR, VARCHAR는 모두 UTF-8형태를 지니므로 입력한 글자가 영문, 한글 등에 따라서 내부적으로 크기가 달라진다. 하지만 사용자 입장에서는 CAHR(100)은 영문, 한글 구분 없이 100글자를 입력할 수 있다고 알고 내부적인 할당 크기는 신경쓰지 않아도 된다.

11. 변수의 사용

@변수명 => 전역 변수처럼 사용 (글로벌 영역)

DECLARE 변수명 => 지역 변수처럼 사용

SET @변수이름 = 변수 값; — 변수 선언 및 값 대입

SELECT @변수이름 — 변수 값 출력

12. MySQL내장함수

12–1. 제어 흐름 함수

• IF(수식, 참, 거짓) ~ 수식의 참/거짓에 따라 2중 분기

SELECT IF(100 > 200, ‘참이다‘, ’거짓이다); => 출력값: ‘거짓이다’

• IFNULL(수식1, 수식2) ~ 수식1이 NULL이 아니면 수식1반환, 수식1이 NULL이면 수식2 반환

SELECT IFNULL(NULL, ‘널이군요’) => 출력값: ‘널이군요‘

_______ IFNULL(100, ‘널이군요’); => 출력값: 100

• NULLIF(수식1, 수식2) ~수식1과 수식2가 값으면 NULL을 반환, 다르면 수식1을 반환 SELECT NULLIF(100,100) => 출력값: NULL

_______ NULLIF(200,100) => 출력값: 200

• CASE~WHEN~ELSE~END

12–2. 문자열 함수

ASCII(아스키코드), CHAR(숫자) ~ 문자의 아스키 코드 값을 돌려주거나 숫자의 아스키코드값에 해당하는 문자를 돌려줌

CONCAT(문자열1,문자열2,…), CONCAT_WS(문자열1,구분자1, 문자열2,….) ~ 문자열을 이어줌

FORMAT(숫자,소수점 자릿수)

….등 다양함

12–3. 수학함수

12–4. 날짜 및 시간 함수

12–5. 시스템 정보 함수

13. 조인(Join)

서로 다른 테이블간에 설정된 관계가 결합하여 1개 이상의 테이블에서 데이터를 조회하기 위해 사용 된다.이 때 테이블간의 상호 연결을 조인이라고 하는데요. 각각의 테이블에 분리된 연관성 있는 데이터를 연결하거나 조합해야 하는데 이러한 일련의 작업들을 조인이라고 합니다.

13–1. 조인(Join)의 종류_ 논리적 조인

INNER Join

OUTER Join

CROSS Join

FULL OUTER Join

SELF Join

조인의 처리에서 어느 테이블을 먼저 읽을지를 결정하는 것은 상당히 중요하며, 그에 따라 처리할 작업량이 상당히 달라진다. INNER JOIN은 어느 테이블을 먼저 읽어도 결과가 달라지지 않으나, OUTER JOIN은 OUTER가 되는 테이블을 먼저 잘 읽어야 한다.

13–2. 조인(Join)의 방식_ 물리적 조인

• Nested Loop Join — 중첩반복조인
• Merge Join — 정렬병합조인
• Hash Join — 해시매치조인

=> MySQL에서는 Nested Loop 조인 알고리즘만 지원함

13–3. Nested Loops JOIN

NESTED LOOPS는 말 그대로 중첩 반복을 사용하는 알고리즘이다. SQL에서 조인은 한 번에 두개의 테이블만을 결합하도록 되어있기 때문에 본질적으로는 이중 루프와 같은 의미로 볼 수 있다.

이에 대한 세부적인 처리는 다음과 같이 이루어 진다.

1) 조인 대상 테이블(이하 A)에서 레코드를 하나씩 반복해가며 스캔한다. 여기서 OUTER LOOP에 해당하는 테이블 A를 driving table 또는 outer table이라고 부른다. 다른 테이블(이하 B)는 inner table(driven table)이라 부른다.

2) driving table의 레코드 전부에 대해 하나씩 loop를 돌면서 inner table의 해당 레코드를 스캔한다. 스캔 중 조인 조건에 맞는 레코드가 발견되면 리턴한다.

• A와 B의 조인 대상 레코드를 R(A), R(B)라고 하면 접근하는 레코드 수는 R(A) x R(B)가 된다. NESTED LOOPS의 실행 시간은 이 레코드 수에 비례한다.
• HASH나 SORT MERGE에 비해 한 번의 단계에서 처리하는 레코드 수가 적어서 상대적으로 메모리 소비가 적다.
• 모든 DBMS에서 지원한다.