[MySQL] MySQL 정리

MySQL

RDBMS & SQL

RDBMS

• Relational DataBase Management System
• 테이블 기반 (Table Based)의 DBMS
  • 데이터를 테이블 단위로 관리
    • 하나의 테이블은 여러 개의 Column으로 구성
  • 중복 데이터를 최소화 시킴
    • 같은 데이터가 여러 컬럼 또는 테이블에 존재했을 경우 데이터를 수정 시 문제 발생 가능
      • 정규화를 통해서 해결
  • 여러 테이블에 분산되어 있는 데이터를 검색 시 Join을 이용하여 필요한 데이터를 검색

SQL

• Database에 있는 정보를 사용할 수 있도록 지원하는 언어
• 모든 DBMS에서 사용 가능
• Query의 대소문자는 구문하지 않지만 데이터의 대소문자는 구분
  • MySQL은 대소문자를 구분하지 않기 때문에 대소문자를 구분할 일이 생겼다면?
  • binary(first_name) = "Steven" 과 같이 column 값에 binary()를 붙여 대소문자를 구분할 수 있음

---

DDL (Data Definition Language)

• 데이터 정의어
• 데이터베이스 객체의 구조를 정의
• 테이블 생성, 컬럼 추가, 타입 변경, 제약조건 지정, 수정 등

SQL 설명

create	데이터베이스 객체를 생성
drop	데이터베이스 객체를 삭제
alter	기존에 존재하는 데이터베이스 객체를 수정

데이터베이스 생성

create database 데이터베이스명;

create database 데이터베이스명
## Character set은 각 문자가 컴퓨터에 저장될 때 어떠한 코드로 저장될지에 대한 규칙의 집합을 의미
default character set 값
## Collation은 특정 문자 셋에 의해 데이터베이스에 저장된 값들을 비교 검색하거나 정렬 등의 작업을 위해
## 문자들을 서로 비교할 때 사용하는 규칙들의 집합을 의미
collate 값;

데이터베이스 변경

alter database 데이터베이스명
default character set 값 collate 값;

데이터베이스 삭제

drop database 데이터베이스명;

데이터베이스 사용

use database 데이터베이스명;

---

DML (Data Manipulation Language)

• 데이터 조작어
• 데이터 조작 기능
• 테이블의 레코드를 CRUD (Create, Retrieve, Update, Delete)

SQL 설명

insert (C)	데이터베이스 객체에 데이터를 입력
select (R)	데이터베이스 객체에서 데이터를 조회
update (U)	데이터베이스 객체에 데이터를 수정
delete (D)	데이터베이스 객체에 데이터를 삭제

SELECT

• SELECT clause와 FROM clause는 필수
• SELECT

SELECT *|{[ALL|DISTINCT]column|expression[alias], ...}
FROM table_name;

SELECT clause	description
*	FROM 절에 나열된 테이블에서 모든 열을 선택
ALL	선택된 모든 행을 반환 (default)
DISTINCT	선택된 모든 행 중에서 중복 행 제거
column	FROM 절에 나열된 테이블에서 지정된 열을 선택
expression	표현식은 값으로 인식되는 하나 이상의 값, 연산자 및 SQL 함수의 조합을 뜻함
alias	별칭

• CASE 구문

CASE exp1 WHEN exp2 THEN exp3
				 [WHEN exp4 THEN exp5
					...
					ELSE exp6]
END

select employee_id, first_name, salary
			 case when salary > 1500 then '고액연봉'
					  when salary > 8000 then '평균연봉'
		 			  else '저액연봉'
			 end "연봉등급"

• WHERE 구문
  • WHERE clause : 조건에 만족하는 행을 검색

  SELECT *|{[ALL|DISTINCT]column|expression[alias], ...}
  FROM table_name
  WHERE conditions;
  

  • Condition 절에 들어갈 수 있는 연산자
    • AND, OR, NOT

    ## 부서번호가 50인 사원 중 급여가 7000 이상인 사원의 사번, 이름, 급여, 부서번호 검색
    SELECT employee_id, first_name, salary, department_id
    FROM employees
    WHERE department_id = 50
    AND salary >= 7000;
    

    • IN

    ## 근무 부서번호가 50, 60, 70에 근무하는 사원의 사번, 이름, 부서번호 검색
    SELECT employee_id, first_nae, department_id
    FROM employees
    WHERE department_id in (50, 60, 70);
    

    • BETWEEN

    ## 급여가 6000이상 10000이하인 사원의 사번, 이름, 급여 검색
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE salary between 6000 and 10000;
    

    • NULL 비교 : IS NULL, IS NOT NULL

    ## 근무 부서가 지정되지 않은 사원의 사번, 이름, 부서번호 검색 잘못된 예시
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE **department_id = null**;
    
    ## 올바른 예시
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id is null;
    

• LIKE 구문
  • wild card : %, _

  -- 이름에 'x'가 들어간 사원의 사번, 이름 검색
  SELECT employee_id, first_name
  FROM employees
  WHERE first_name LIKE '%x%';
  
  -- 이름의 끝에서 3번째 자리에 'x'가 들어간 사원의 사번, 이름 검색
  SELECT employee_id, first_name
  FROM employees
  WHERE first_name LIKE '%x__';
  

 

• ORDER BY 구문 (default : ASC)

SELECT *|{[ALL|DISTINCT]column|expression[alias], ...}
FROM table_name
WHERE conditions
ORDER BY col_name1 [ASC|DESC], ...;

• GROUP BY 구문
  • SELECT 문에서 GROUP BY 절을 사용하는 경우 Database는 쿼리 된 테이블의 행을 그룹으로 묶음
  • Database는 선택 목록의 집계 함수를 각 행 그룹에 적용하고 각 그룹에 대해 단일 결과 행을 반환
  • GROUP BY 절을 생략하면 Database는 선택 목록의 집계 함수를 쿼리 된 테이블의 모든 행에 적용
  • SELECT절의 모든 요소는 GROUP BY 절의 표현식, 집계 함수를 포함하는 표현식 또는 상수만 가능

  ##                                 실행순서
  SELECT      columns                   5
  FROM        tablename                 1
  WHERE       conditions                2
  GROUP BY    grouping_column           3
  HAVING      grouping_conditions       4
  ORDER BY    col [ASC | DESC]          6
  

INSERT

INSERT INTO table_name
VALUES (col_val1, col_val2, col_val3, ...);

INSERT INTO table_name (col_name1, col_name2, col_name3, ...)
VALUES (col_val1, col_val2, col_val3, ...);

INSERT INTO table_name (col_name1, col_name2, col_name3, ...)
VALUES (col_val1, col_val2, col_val3, ...),
			 (col_val1, col_val2, col_val3, ...);

Update

UPDATE table_name
SET col_name1 = col_val1, [col_name2 = col_val2, ...]
WHERE contitions;

• WHERE 절의 condition에 만족하는 레코드의 값을 변경
• WHERE 절을 생략하면 모든 데이터가 바뀜

DELETE

DELETE FROM table_name
WHERE conditions;

• WHERE 절의 condition(조건)에 만족하는 레코드의 값을 삭제
• WHERE 절을 생략하면 모든 데이터가 삭제 됨

---

SET Operation

• 모든 집합 연산자는 동일한 우선 순위를 갖는다
• SELECT 절에 있는 column의 개수와 type이 일치해야 한다.

Operator	설명	SET
UNION	두 쿼리에서 선택된 모든 행 반환 (중복 제거)	합집합
UNION ALL	두 쿼리에서 선택된 모든 행 반환 (중복 포함)	합집합
INTERSECT	두 쿼리에서 선택된 모든 중복 행 반환	교집합
MINUS	첫번째 쿼리에서 선택한 행 반환 (중복 행 제거)	차집합

• MySQL에서 INTERSECT와 MINUS는 지원하지 않음
  • INTERSECT ⇒ INNER JOIN으로 해결

  ## 다른 RDBMS
  SELECT employee_id
  FROM employees
  INTERSECT
  SELECT DISTINCT employee_id
  FROM job_history;
  
  ## MySQL
  SELECT DISTINCT employee_id
  FROM employees e INNER JOIN job_history j
  ON e.employee_id = j.employee_id;
  

  • MINUS ⇒ NOT IN, NOT EXISTS, LEFT OUTER JOIN 으로 해결

  ## 다른 RDBMS
  SELECT employee_id
  FROM employees
  MINUS
  SELECT DISTINCT employee_id
  FROM job_history;
  
  ## MySQL 해결1 (NOT IN)
  SELECT DISTINCT employee_id
  FROM employees
  WHERE NOT IN (
  								SELECT DISTINCT employee_id
  								FROM job_history
  							);
  
  ## MySQL 해결2 (NOT EXISTS)
  SELECT DISTINCT e.employee_id
  FROM employees e
  WHERE NOT EXISTS (
  									SELECT j.employee_id
  									FROM job_history j
  									WHERE e.employee_id = j.employee_id
  								 );
  
  ## MySQL 해결3 (LEFT OUTER JOIN)
  SELECT DISTINCT e.employee_id
  FROM employees e
  LEFT JOIN job_history j
  ON e.employee_id = j.employee_id
  WHERE j.employee_id IS NULL;
  

---

DCL (Data Control Language)

• 데이터 제어어
• DB, Table의 접근 권한이나 CRUD 권한을 정의
• 특정 사용자에게 테이블의 검색권한을 부여 / 금지 등

SQL	설명
grant	데이터베이스 객체에 권한을 부여
revoke	데이터베이스 객체 권한 취소

---

TCL (Transaaction Control Language)

• 트랜잭션 제어어
• transaction
  • 데이터베이스의 논리적 연산 단위
  • 데이터베이스의 상태를 변화시키는 일종의 작업 단위를 의미

SQL	설명
start transaction	commit, rollback이 나올 때까지 실행되는 모든 SQL
commit	실행한 Query를 최종적으로 적용
rollback	실행한 Query를 마지막 commit 전으로 취소시켜 데이터를 복구

• MySQL의 경우 default가 autocommit으로 설정되어 있음