Sad Puppy 3 'DB/SQL(MySQL)' 카테고리의 글 목록 :: 개발자 아지트

 ✏️

  • MySql 동작방식
  • MySql 파싱의 목적이 뭔가요?
  • DML이 뭔가요?
  • DML과 DDL의 차이는 뭔가요?
  • Join이 뭔가요? </aside>

[들어가기전에 …]

🍏 RDBMS의 특징

  • 테이블이라는 최소 단위로 구성 됨
  • 테이블은 열과 행으로 이루어짐
  • 테이블간 FK(Foreign Key)를 통해 다른 데이터를 조합해서 함께 볼 수 있음

🍏 RDBMS의 종류

  • 유료인 Oracle
  • MySQL (Spring과 궁합이 좋다함 근데 왜..?)
  • PostgreSQL
  • 등등 …

🍏 SQL; Structed Query Language

(* 아래의 내용은 MySQL 8.0 기준으로 작성됨)

  1. MySQL Connectors에 요청이 도착한다
  2. 사용자 스레드가 할당되고 요청이 MySQL엔진에 전달된다.
  3. 토큰 파서가 SQL을 MySQL이 이해 가능한 최소 단위로 잘라내고, 문법 유효성을 검증한다.
  4. 전처리기가 컬럼명, 테이블명 등이 존재하는지 확인하고, 접근 권한이 있는지 검증한다.

<aside> 💡

(3-4) 과정은 SQL 파싱(Parsing)이라고 한다.

  • MySQL Server의 ‘SQL 파서’ 모듈로 처리한다.
  • 해당 단계에서 SQL 파스 트리가 만들어진다.
  • MySQL Server가 실제로 쿼리를 실행할 때는 SQL 문장이 아니라 SQL 파스 트리를 사용해서 쿼리를 실행한다. </aside>


5. 옵티마이저가 사용자가 전달한 SQL문을 어떻게 실행해야 효율적일지 결정한다. (최적화 및 실행 계획 수립 단계)

<aside> 💡

*옵티마이저(Optimizer)란?

:쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지를 참조하고, 데이터를 기반으로 최적의 실행 계획을 수립해주는 것. DBMS의 두뇌

[상세]

  • 파스트리를 참조해서 다음의 내용을 처리한다.
  • 불필요한 조건 제거 및 복잡한 연산 단순화
  • 여러 테이블의 조인이 있는 경우, 어떤 순서로 테이블을 읽을지 결정
  • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
  • 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
  • 등등

[옵티마이저가 실행 계획을 수립하는 2가지 방법]

  • 규칙기반 최적화 방법: 옵티마이저에 내장된 우선순위에 따라서 실행 계획을 수립하는 방법이다.
  • 비용 기반 최적화: SQL을 처리하는 다양한 방법을 마련해두고, 각 방법의 비용과 테이블 통계 정보를 토대로 실행 계획을 수립하는 방법이다. </aside>

6. 수립된 계획대로 스토리지 엔진에서 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.MySQL 엔진

  • 클라이언트 접속과 SQL 요청을 처리한다. 이는 쿼리 파서, 전처리기, 옵티마이저, 실행 엔진 등으로 이루어져 있다. 여기서 중요한 옵티마이저는 요청 SQL문을 최적화해서 실행시키기 위해 실행 계획을 짠다.

MySQL스토리지 엔진

  • 데이터를 실제로 디스크에 저장하거나, 디스크에 저장된 데이터를 가져오는 역할을 한다. 즉 MySQL 엔진의 옵티마이저가 짠 SQL 실행 계획에 따라 스토리지 엔진을 적절히 호출해서 쿼리를 실행한다. 여기서 스토리지 엔진을 호출할 때 사용하는 것을 Handler API라고 하고, 실제로 이 Handler API를 구현해서 플러그인으로 스토리지 엔진을 커스텀하고 추가할 수 있다. </aside>

7. 결과를 반환한다.

8. 백그라운드 스레드에서 커밋되었으나 디스크에 반영되지 않은 내용을 디스크에 접근하여 일괄 처리한다.

MySql 파싱의 목적이 뭔가요?

→ 클라이언트로부터 전달된 SQL문을 기계가 이해할 수 있는 내부 구조로 변환하고, 그 과정에서 문법적 의미적 오류를 가려내기 위함


[들어가기전에 …]

🍉 DDL; Data Definition Language

: 테이블이나 관계의 구조를 생성하는데 사용함

  • CREATE: 새로운 데이터베이스 및 테이블 생성
  • create database 데이터베이스 이름; create table 테이블이름 ( 필드이름1 필드타입1, 필드이름2 필드타입2, ... );
  • ALTER: 데이터베이스와 테이블의 내용 수정
  • alter table 테이블이름 add 필드이름 필드 타입; alter table 테이블이름 drop 필드이름; alter table 테이블이름 modify column 필드이름 필드타입;
  • DROP: 데이터베이스와 테이블 삭제. 데이터 및 테이블 전체를 삭제함
  • drop database 데이터베이스이름; drop table 테이블이름;
  • TRUNCATE: 데이터베이스와 테이블 삭제. 생성 초기 상태 처럼 컬럼값만 남김
  • truncate database 데이터베이스이름; truncate table 테이블이름;

🍑 DCL; Data Control Language

: 데이터의 사용 권한 관리

  • GRANT: 사용자 또는 ROLE에 대해 권한을 부여할 수 있음
  • grant [객체권한명] (컬럼) on [객체명] to { 유저명 | 롤명 | public } [with grant option]; //ex) grant select, insert on mp to scott with grant option;
  • REVOKE: 사용자 또는 ROLE에 부여한 권한을 회수할 수 있음
  • revoke {권한명 [, 권한명...] all} on [객체명] from { 유저명 [, 유저명...] | 롤명 | public } [cascade constraints]; //ex) revoke select, insert on emp from scott [cascade constraints];

DML이 뭔가요?

🌽 DML; Data Manipulation Language

: 테이블에 데이터를 검색, 삽입, 수정, 삭제하는데 사용함

  • INSERT: 테이블에 새로운 row를 추가할 수 있음
  • insert into 테이블이름(필드이름1, 필드이름2, ...) values(데이터값1, 데이터값2, ...); insert into 테이블이름 values(데이터값1, 데이터값2, ...);

SELECT: 테이블의 row를 선택할 수 있음

  • UPDATE: 테이블의 row의 내용을 수정할 수 있음
  • update 테이블이름 set 필드이름1=데이터값1, 필드이름2=데이터값2, ... where 필드이름=데이터값;
  • DELETE: 테이블의 row를 삭제할 수 있음
  • delete from 테이블이름 where 필드이름=데이터값;

DML과 DDL의 차이는 뭔가요?

→DDL은 테이블을 관리하는 명령어

→DML은 테이블의 데이터를 관리하는 명령어

Join이 뭔가요?

  • 두 테이블을 하나로 합치기 위해 데이터베이스가 제공하는 기능
  • JOIN은 ON키워드를 통해 기준이 되는 컬럼을 선택해 2개의 테이블을 합쳐줌
  • JOIN을 할 때는, 적어도 하나의 컬럼을 서로 공유하고 있어야함
    • 테이블에 외래 키가 설정돼 있으면 해당 컬럼을 통해 JOIN가능

❗ 다만 JOIN을 하기위해 외래키를 설정하는게 항상 좋은 선택이 아닐 수 있음

  • 외래키 설정시, 데이터 무결성을 확인하는 추가 연산이 발생함
  • 무결성을 지켜야 하기 때문에, 상황에 따라 개발하는데 불편할 수 도 있음

⇒ 항상 테이블에 모든 제약조건을 걸어야 하는건 아니고, 프로젝트 상황에 따라 효율적인 제약조건 적용필요

Reference


'DB > SQL(MySQL)' 카테고리의 다른 글

MySQL 기본 문법 정리  (0) 2024.06.18

 

SELECT문의 기본 형식

 

SELECT 열_이름
	FROM 테이블_이름
	WHERE 조건식 (WHERE문을 작성하지 않으면 모든 항목을 조회함)
	GROUP BY 열_이름
	ORDER BY 열_이름 (어떤 열을 기준으로 정렬할 것인지, ASC(오름차순), DESC(내림차순) 
	LIMIT 숫자 (출력 하고싶은 값의 개수를 제한하고 싶을때 사용)

 

이때 가장 중요한 것은 쿼리문을 작성할때 필요없는 쿼리문을 생략할 수는 있어도, 위의 순서대로 쿼리문을 작성해야 오류가 발생하지 않는다. 

 

만약 정렬할 값이 여러값일때, 작성한 열 이름 순서대로 우선순위를 따라 정렬하게 된다. 

또한 예를들어서 첫번째 값이 동일한 경우 두번째 값의 정렬기준에 따라 정렬하게된다. (두번째 값 마저 동일할 경우 세번째 값의 정렬기준에 따라 정렬하고, 이후의 값들이 있을경우 마찬가지로 적용됨)

 

 

WHERE 절 NOTNULL 예시

SELECT 열_이름
	FROM 테이블_이름
	WHERE 칼럼명 IS NOT NULL

 

 

 

WHERE 절 NULL 예시

SELECT 열_이름
	FROM 테이블_이름
	WHERE 칼럼명 IS NULL # 조건이 여러개인 경우, AND를 통해 조건을 추가 작성한다.

 

 

 

NULL 및 NOT NULL을 다룰 때 주의할 점 

NULL은 값이 아니라서 비교문(=, <, >, <>)의 사용이 불가하다. 

NULL은 상태이다. 

 

 

CASE문(조건문)

SELECT를 통해 출력할 값에 대해서 특정 조건을 만족할 경우, 출력 값을 변경하고싶을때 사용한다. 

 

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS
        , CASE WHEN FREEZER_YN IS NULL THEN 'N' 
        ELSE FREEZER_YN 
        END AS FREEZER_YN
        FROM FOOD_WAREHOUSE

 

CASE문 사용시 ELSE문을 생략하게되면, WHEN조건에 걸리지 않는 경우 NULL 상태로 처리한다. 

CASE문은 반드시 END로 끝내야하고, 조건문과 조건문 사이에는 콤마를 사용하지 않는다. 

결과 부분에 NULL을 사용하지 않는다. 

 

END뒤에 붙은 AS는 Alias의 약어로, 별칭을 의미한다. 해당 조건문을 통해 추려진 값들은 AS뒤에 있는 값이 컬럼명이 되어 출력된다. (별칭을 사용하지 않으면 쿼리문이 그대로 출력된다. )

 

 

조회한 후 결과에 출력된 행의 수 반환하기

count(*) 함수를 통해 해당 출력할 수 있다. 만약, 해당 값에 대한 칼럼 이름을 변경하고싶다면 뒤에 as를 붙여 적용한다. 

 

SELECT COUNT(*) AS USERS
FROM USER_INFO 
    WHERE AGE IS NULL

 

 

데이터(날짜)형식 변경

 

DATE_FORMAT함수를 통해 데이터 형식을 변경한다. 

첫번째 인자에는 변경하고 싶은 대상 데이터를 입력한다. 

두번째 인자에는 변경하려는 데이터형식을 설정해준다. 

 

변경후 해당 데이터에 대한 컬럼명을 AS를 통해 지정해줘야한다. 

 

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
    FROM DOCTOR
    WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
    ORDER BY HIRE_YMD DESC, DR_NAME

 

데이터 형식은 다음과 같다. 

(펼쳐서 확인)

 

더보기

 

format 설명
%a 축약된 요일 이름 (일요일부터 토요일까지)
%b 축약된 월 이름 (1월부터 12월까지)
%c 숫자로 표시된 월 이름 (0부터 12까지)
%D 일(day)을 숫자로 표시하고 접미사를 붙임 (1일, 2일, 3일 등)
%d 일(day)을 숫자로 표시 (01부터 31까지)
%e 일(day)을 숫자로 표시 (0부터 31까지)
%f 마이크로초 (000000부터 999999까지)
%H 시간(hour) (00부터 23까지)
%h 시간(hour) (00부터 12까지)
%I 시간(hour) (00부터 12까지)
%i 분(minutes) (00부터 59까지)
%j 연(year)의 몇 번째 일(day)인지 (001부터 366까지)
%k 시간(hour) (0부터 23까지)
%l 시간(hour) (1부터 12까지)
%M 전체 월 이름 (1월부터 12월까지)
%m 숫자로 표시된 월 이름 (00부터 12까지)
%p 오전(AM) 또는 오후(PM)
%r 12시간 형식으로 표시된 시간 (hh:mm:ss 오전/오후)
%S 초(seconds) (00부터 59까지)
%s 초(seconds) (00부터 59까지)
%T 24시간 형식으로 표시된 시간 (hh:mm:ss)
%U 일요일을 한 주의 첫 번째로 하는 주차 (00부터 53까지)
%u 월요일을 한 주의 첫 번째로 하는 주차 (00부터 53까지)
%V 일요일을 한 주의 첫 번째로 하는 주차 (01부터 53까지). %X와 함께 사용됨
%v 월요일을 한 주의 첫 번째로 하는 주차 (01부터 53까지). %x와 함께 사용됨
%W 전체 요일 이름 (일요일부터 토요일까지)
%w 일요일=0, 토요일=6인 요일
%X 일요일을 한 주의 첫 번째로 하는 주의 연도. %V와 함께 사용됨
%x 월요일을 한 주의 첫 번째로 하는 주의 연도. %v와 함께 사용됨
%Y 4자리 숫자로 표시된 연도
%y 2자리 숫자로 표시된 연도

 

 

 

 

값이 특정 문자열을 포함하는지 확인

 

와일드 카드(%)를 사용하여 특정 문자열이 포함되는지 확인한다. 

예를들어서, (미술학원, 피아노학원, 집앞학교) 에서 학원만 뽑아서 확인하고 싶다면

'%학원' 이렇게 작성하여 확인한다. 

 

(빨간집, 빨간코, 빨간뚜껑, 빨간책, 파란색연필, 파란하늘, 파란종이)에서 빨간 것들만 뽑아서 확인하고 싶다면

'빨간%' 이렇게 작성하여 확인한다. 

 

특정 문자열이 포함되며, 해당 문자열 앞뒤로는 어떤 문자가 오거나 오지않아도 상관없다면

'%특정문자%' 이렇게 작성하여 확인한다. 

 

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
    FROM FOOD_FACTORY
    WHERE ADDRESS LIKE '강원도%'
    ORDER BY FACTORY_ID

 

 

특정 칼럼의 평균 구하기

 

AVG(컬럼 명)라는 함수를 이용한다. 

 

 

소수점 아래 버리기

 

FLOOR()라는 함수를 이용한다. 

 

SELECT ROUND(AVG(DAILY_FEE), 0) AS AVERAGE_FEE
    FROM CAR_RENTAL_COMPANY_CAR
    WHERE CAR_TYPE='SUV'

 

소수점 아래 특정자리수 까지만 출력하기

 

ROUND(표현하고자하는 값, 소수점 아래 출력하고자 하는 자릿수)라는 함수를 이용한다. 

 

SELECT ROUND(AVG(CASE WHEN LENGTH IS NULL THEN 10 ELSE LENGTH END), 2) AS AVERAGE_LENGTH
    FROM FISH_INFO

 

 

두 테이블 사이에 외래키가 존재하는 경우, 두 테이블의 조건을 통해 값을 뽑고 싶은 경우

 

JOIN, ON 구문을 사용한다. 

 

SELECT FIRST_HALF.FLAVOR # 기준이 되는 테이블의 칼럼
    FROM FIRST_HALF # 첫 번째 테이블
    JOIN ICECREAM_INFO # 참고할 테이블 
    ON FIRST_HALF.FLAVOR = ICECREAM_INFO.FLAVOR # 외래키로 묶여있는 컬럼, 조인 조건
    WHERE TOTAL_ORDER > 3000 AND INGREDIENT_TYPE = 'fruit_based'
    ORDER BY TOTAL_ORDER DESC

 

 


테이블의 모든 정보를 출력하고싶은 경우

 

SELECT 문에서 *을 사용한다.  (이때 *은 모든 열을 의미한다)

 

SELECT *
    FROM ANIMAL_INS
    ORDER BY ANIMAL_ID

 

 

테이블의 특정 컬럼에서 Max값을 찾고 싶은경우

 

SELECT 문에서 MAX(대상)을 사용한다. 

 

 

출력하고자 하는 컬럼 값에 문자를 추가하고 싶은 경우

 

SELECT 문에서 CONCAT(대상, 추가할 문자열)을 사용한다. 

 

SELECT CONCAT(MAX(LENGTH), 'cm') AS MAX_LENGTH
    FROM FISH_INFO

 

 

날짜도 정렬이 가능하다

 

날짜도 계산이 가능하다

 

날짜2>=날짜1이라고 했을때, DATEDIFF(.날짜2, 날짜1) 를 하면 차이 일수가 리턴된다. 

중요한 점은 예를들어서, DATEDIFF(2,1)을 했을때 1이 리턴되지 않고, 2가 리턴된다는 점이다. 

날짜 수로 보면 1이라고 생각할 수 있지만, 여기서는 1일부터 2일까지 총 이틀 빌린 것으로 계산한다. 

 

 

비트연산도 가능하다

 

SELECT COUNT(*) AS "COUNT"
    FROM ECOLI_DATA
    WHERE ((GENOTYPE & 1) OR (GENOTYPE & 4)) AND !(GENOTYPE & 2)

 

 


SQL 구문 순서

 

SELECT 컬럼명
    FROM 테이블명
    WHERE 테이블 조건
    GROUP BY 컬럼명 
    HAVING 그룹 조건
    ORDER BY 컬럼명

 

WHERE 조건을 통해 나온 결과를 GROUP BY  문으로 묶어 그룹을 만들 수 있고,

 

해당 그룹을 HAVING을 통해 조건에 맞게끔 그룹을 다시 만든다. 

 

SELECT NAME, COUNT(*) AS COUNT 
    FROM ANIMAL_INS 
    WHERE NAME IS NOT NULL
    GROUP BY NAME # 컬럼 명 
    HAVING COUNT(*) >= 2 # 그룹 조건
    ORDER BY NAME

 

 

같은 값을 가진 행끼리 하나의 그룹으로 모우기

 

GROUP BY를 사용하여 데이터를 그룹화 한다. 

이 말은, 예를들어서 아래와 같은 표에 대해서 GROUP BY를 사용하면

과일 이름 개수
딸기  3
토마토 1
딸기 3

(사용전)

 

 

과일 이름 개수
딸기  6
토마토 1

(사용후)

 

이렇게 된다는 의미이다. 

 

 

특정 열에서 중복제거를 한 후, 행의 수를 반환하기

 

COUNT내부에서 DISTINCT를 추가한다. 

COUNT(DISTINCT 컬럼명)

 

SELECT COUNT(DISTINCT NAME) AS COUNT
    FROM ANIMAL_INS
    WHERE NAME IS NOT NULL

 

 

문자열 자르기  

 

문자열을 자르기 위해 SUBSTRING(문자열, 시작위치, 길이) 함수를 사용할 수 있다. 이 함수는 문자열 내에서의 시작위치에서부터 길이 만큼 문자열을 잘라내어 반환하는 함수이다. 


문자열을 자르기 위해 LEFT(문자열, 길이) 함수를 사용할 수 있다. 이 함수는 문자열에서 왼쪽에서부터 길이만큼 문자열을 잘라내어 반환하는 함수이다. 


문자열을 자르기 위해RIGHT(문자열, 길이) 함수를 사용할 수 있다. 이 함수는 문자열에서 오른쪽에서부터 길이만큼 문자열을 잘라내어 반환하는 함수이다. 

 

SELECT SUBSTRING(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(PRODUCT_ID) AS PRODUCTS 
    FROM PRODUCT
    
    GROUP BY SUBSTRING(PRODUCT_CODE, 1, 2)
    
    ORDER BY PRODUCT_CODE

 

 

 AS로 만든 이름의 컬럼을 기준으로 정렬하기 

 

ORDER BY에는 SELECT에서 AS로 만든 별칭의 컬럼명을 넣어 정렬할 수 있다. 

단, 컬럼명을 쌍따옴표로 묶으면 안된다. 

 

SELECT MCDP_CD AS "진료과코드" , COUNT(*) AS "5월예약건수"
    FROM APPOINTMENT
    WHERE APNT_YMD LIKE "2022-05%"
    GROUP BY MCDP_CD
    ORDER BY 5월예약건수, 진료과코드

 

 

특정 컬럼의 총합 구하기

 

SUM(컬럼명)함수를 통해 특정 컬럼의 합을 구할 수 있다. 

 

SELECT SUM(PRICE) AS "TOTAL_PRICE"
    FROM ITEM_INFO
    WHERE RARITY LIKE "LEGEND"

 

 

'DB > SQL(MySQL)' 카테고리의 다른 글

MySQL 동작 방식, SQL  (0) 2025.07.24

+ Recent posts