mysql 기초 - 분석법

0818부터.

 

> 날짜, 시간

--------------------------------------

관련 데이터 타입 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html

 관련 함수 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

EX.

SELECT email, sign_up_day, FROM_UNIXTIME(UNIX_TIMESTAMP(sign_up_day)) FROM member;

 

 

 

>대문자만 조회하기

--------------------------------------

EX.

SELECT * FROM copang_main.test WHERE sentece LIKE BINARY "%G%"

 

 

>내림차순/오른차순.

--------------------------------------

EX.

ORDER BY height DESC;
ORDER BY height ASC;

 

INT 타입의 값은 숫자의 대소(크고 작음)를 기준으로 정렬이 수행되지만,

TEXT 타입의 값은 숫자의 대소가 아니라 한 문자, 한 문자씩 그 문자 순서를 비교해서 정렬이 수행됩니다. 

 

 

 

>CAST

--------------------------------------

data 컬럼의 타입이 INT였을 때와 같은 결과가 나옵니다. 참고로 만약 문자열 타입으로 저장된 숫자값에 소수점이 포함되어 있다면 signed 대신 decimal(소수점이 있는 수를 나타내는 타입)을 적어주고 사용하면 됩니다.

EX.

    ORDER BY CAST(data AS signed) ASC; 

 

EX. sales 테이블에서 TEXT 타입의 컬럼인 registration_num(등록번호)에 있는 숫자값들을 기준으로 정렬을 하려고 합니다. 이때 숫자가 작을수록 해당 row들이 먼저 출력되도록 하고 싶어요.

SELECT * FROM sales ORDER BY CAST(registration_num AS signed);

 

 

 

>다양한 산술 함수들이 궁금하신 분들은 이 링크

MySQL :: MySQL 8.0 Reference Manual :: 12.6.2 Mathematical Functions

 

 

 

>LIMIT

--------------------------------------

(1) LIMIT (row의 개수)

(2) LIMIT (첫 번째 row를 기준으로 한 시작 Offset, row의 개수)

이 두 가지 사용 방식이 있습니다. 이때 (2)에서 첫 번째 인자는, 가장 첫 번째 row가 0, 두 번째 row가 1, 세 번째 row가 2 ... 이라는 사실을 잘 기억하셔야 합니다.

 

EX. 5개 뽑기 LIMIT 5 또는 LIMIT 0, 5

 

EX. menu 테이블의 row들을 price(가격) 컬럼을 기준으로 내림차순으로 정렬하고, 가격이 비싼 기준으로 6위~8위에 해당하는 row들만 추리려고 합니다. 다음 빈 칸에 들어갈 수를 직접 적어보세요.

SELECT * FROM menu ORDER BY price DESC LIMIT 5, 3;

 

 

>NULL

--------------------------------------

-NULL에는 어떤 연산을 해도 결국 NULL이다.

 

-칸 채우기

EX.

SELECT 
    COALESCE(height, '####')
    COALESCE(wieght, '---')
    COALESCE(address, '@@@')
FROM member;
  

 

 

> SELECT 문 내의 각 절들 간의 작성 순서

 

 FROM - WHERE - ORDER BY - LIMIT 순

 정확히 알고 싶은 분은 이 링크

MySQL :: MySQL 8.0 Reference Manual :: 13.2.10 SELECT Statement

 

 

 

>처리순서

--------------------------------------

5. SELECT   /WHAT정의 how - *, 컬럼, 수식, SQ(saclar sq) 등

1. FROM   /재료집합 정의 -T, V , SQ, MV, JOIN

2. WHERE   /Filtering Rows - 조건식(PREDICATE): T일때만 통과

3. GROUP BY   /무리짓기 - 값(컬럼, 수식): 같은 값을 갖는 행을 묶음

4. HAVING    /Filtering Groups - 조건식(Predicate): T일때만 통과

6. ORDER BY   /결과집합 정렬 - 값(컬럼, 수식, 컬럼별칭), 오름/내림차순, 널처리

 

 

 

>PAGINATION

--------------------------------------

EX.

1페이지 : SELECT * FROM db.search_result ~ ORDER BY registration_date DESC LIMIT 0, 10

2페이지 : SELECT * FROM db.search_result ~ ORDER BY registration_date DESC LIMIT 10, 10

3페이지 : SELECT * FROM db.search_result ~ ORDER BY registration_date DESC LIMIT 20, 10

4페이지 : SELECT * FROM db.search_result ~ ORDER BY registration_date DESC LIMIT 30, 10

 

 

 

=======================================================

 

>

EX.

 id    star      comment                                        registration_date

1	2	NULL	2020-01-02
2	5	'페페로니의 향을 빠르게 맛볼 수 있었어요’	2020-03-24
3	3	‘맛은 있는데, 배달이 너무 느렸어요’	2020-04-21
4	NULL	NULL	2019-11-23
5	3	'사진이랑 조금 다르지만 먹을만 하네요’	2019-09-14
6	5	NULL	2020-05-02
7	2	‘왜 피클을 안 넣어주셨죠?’	2019-10-05

이 테이블에서

(1) 고객들이 남긴 리뷰 수와,

(2) 반올림된 별점 평균값을 구하려고 합니다. 

(3) 단, 댓글이 존재하는(comment 컬럼이 NULL이 아닌) 

 

SELECT COUNT(*), ROUND(AVG(star))

FROM review WHERE comment IS NOT NULL;

 

일단 comment 컬럼이 NULL이 아닌 row들만 조회하고 그 row들의 개수, 그리고 그 row들의 별점의 평균값을 구해야 합니다. 별점 평균값을 반올림하려면 ROUND 산술 함수를 사용하면 되겠죠?

 

 

 

>CASE함수 두가지 종류

 

CASE

WHEN 조건1 THEN 해당 조건이 TRUE일 때 보여줄 값

WHEN 조건2 THEN 해당 조건이 TRUE일 때 보여줄 값

WHEN 조건3 THEN 해당 조건이 TRUE일 때 보여줄 값

ELSE 그 밖의 모든 경우

END

--------------------------------------

 

 - (1) 단순함수

그 컬럼의 값과 어떤 값이 같은지(=)를 비교

 

 - (2) 검색함수

 TRUE인 조건을 만나게되면 거기에 있는 THEN 뒤의 값을 돌려주고, CASE 함수는 종료

 

일반적으로 단순 CASE 함수에서는 등호 연산(=) 밖에 할 수 없다는 단점이 있습니다. 하지만 검색 CASE 함수에서는 사용자가 직접 원하는 대로 조건을 설정할 수 있기 때문에 좀더 다양한 형태의 조건을 걸 수 있다는 장점이 있구요. 위 사진을 보면 BMI 값의 범위를 확인하는 조건들을 사용한 것을 볼 수 있는데요. 이건 등호 연산만 할 수 있는 단순 CASE 함수에서는 불가능한 일입니다.

대부분 검색 CASE 함수를 사용하는 경우가 많지만, 여러분이 실무에서 보게될 기존 SQL 문에 단순 CASE 함수가 쓰여있을 수도 있기 때문에 알려드렸습니다.  

 

EX.

SELECT 
    email,
    CONCAT(height, 'cm', ',' , weight, 'kg') AS '키와 몸무게',
    weight/(height/100) * (height/100) AS BMI,
    
(CASE    
    WHEN weight IS NULL OR height is NULL THEN '비만 여부 알수 없음'
    WHEN weight/((height/100)*(height/100)) >=25 THEN '과체중 또는 비만'
    WHEN weight/((height/100)*(height/100)) >=18.5
        AND weight /((height/100)*(height/100)) <25
        THEN '정상'
    ELSE '저체중'
END) AS obseity_check

FROM member
ORDER BY obseity_check;

 

 

 

--------------------------------------

>: DISTINCT

특정 컬럼에 존재하는 고유한 값들을 보려면 해당 컬럼 이름 앞에 DISTINCT를 붙여주면 됩니다.

EX.

SELECT DISTINCT(SUBSTRING(address,1,2)) AS ADDRESS FROM member;

 

 

문자열은 시작이 1

 

----------------------------------------

> TRIM

문자열의 공백을 제거하는 함수에는 LTRIM(왼쪽 공백 제거), RTRIM(오른쪽 공백 제거), TRIM(양쪽 공백 제거)