mysql 기초 함수들/출처 코드잇

1. 단순 CASE 함수

CASE 컬럼 이름 WHEN 값 THEN 값  WHEN 값 THEN 값 WHEN 값 THEN 값 ELSE 값 END

이런 식으로 작성된 CASE 함수를 단순 CASE 함수라고 하는데요. 바로 예시를 보여드릴게요. 

지금 age 컬럼의 값이 

29면 ‘스물 아홉 살’, 30이면 ‘서른 살’ 이라고 표현되도록 했습니다. 결과를 보니 잘 작동하죠?

그리고 CASE 함수 중에서 ELSE age는 나머지 경우에는 모두 age 컬럼에 있던 값을 그대로 보여달라는 뜻입니다. 

이렇게 CASE 문 바로 뒤에 컬럼 이름을 쓰고, 그 컬럼의 값과 어떤 값이 같은지(=)를 비교하는 CASE 함수를 단순 CASE 함수라고 합니다.

2. 검색 CASE 함수

위 그림처럼 우리가 이전 영상에서 배운 것이 바로 검색 CASE 함수입니다. 지금 CASE 함수의 형식을 보면 다음과 같죠?CASE  WHEN 조건1 THEN 값 WHEN 조건2 THEN 값  WHEN 조건3 THEN 값  ELSE 값 END 

이전에 설명한 대로 이런 CASE 함수에서는 일단 TRUE인 조건을 만나게되면 거기에 있는 THEN 뒤의 값을 돌려주고, CASE 함수는 종료됩니다. 

 

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

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

 

 

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

NULL을 다른 값으로 변환하는 방법들, 한번 정리해볼게요.

1. COALESCE 함수 

우리가 배웠던 COALESCE 함수입니다. 

이전에 배웠던 것처럼 COALESCE 함수는 괄호 속 인자 중에서 가장 첫 번째로 NULL이 아닌 값을 반환합니다. 지금 저는 height 컬럼의 NULL들을 ‘N/A’라는 문자열로 교체했는데요. N/A는 Not Available, Not Applicable의 줄임말로 테이블에서 어떤 값이 없거나 표현할 수 없는 값일 때를 사용되는 단어입니다. 엑셀에서도 자주 등장하는 단어라 아무래도 NULL보다는 사람들이 좀더 많이 아는 단어일 겁니다. 

그런데 COALESCE를 이렇게도 써볼 수 있습니다. 

이번엔 COALESCE 함수 안에 weight * 2.3이라는 식이 추가되었습니다. 지금 저는 사람의 키가 보통 몸무게에 2.3 을 곱한 값이라고 가정한 건데요. 만약 height 컬럼이 NULL이면, 해당 row의 weight 컬럼의 값을 갖고 키를 추론해본 겁니다. height 컬럼도 NULL이고, weight 컬럼도 NULL인 row라면 ‘N/A’가 출력됐을 겁니다. 결과를 보니까 다행히도 지금 height 컬럼과 weight 컬럼이 모두 NULL인 row는 없는 것 같네요. 

만약 데이터 분석을 할 때 단 하나의 NULL도 허용할 수 없는 상황이라면, 이렇게 나름의 가정을 하고 NULL을 적절한 값으로 변환하는 것도 좋은 방법 중 하나입니다. 

2. IFNULL 함수

IFNULL 함수는 첫 번째 인자가 NULL인 경우에는, 두 번째 인자를 표시하고 NULL이 아니면 해당 값을 그대로 표현합니다. 아래 그림을 보시면 바로 이해되실 겁니다. 

그러니까 height 컬럼이 NULL이면 'N/A'를 출력하고, NULL이 아니면 height 컬럼의 값을 그대로 출력하죠.

3. IF 함수

IF 함수는 가장 첫 번째 인자로 어떤 조건식이 옵니다. 만약 그 조건식의 결과가 True라면 두 번째 인자를 리턴하고, False라면 세 번째 인자를 리턴합니다. 아래 그림을 보면 바로 이해되실 텐데요.

지금 height IS NOT NULL이 True인 경우, 그러니까 height 컬럼에 값이 있는 경우에는 그 값이 그대로 출력되고, False인 경우 그러니까 height 컬럼이 NULL인 경우는 'N/A'이 출력되는 겁니다.

4. CASE 함수 

CASE 함수는 이전에 배웠는데요. 아래 SQL 문처럼 CASE 함수로도 NULL을 적절한 값으로 변환해서 나타낼 수 있습니다. 아래 그림은 따로 설명하지 않아도 되겠죠?

---

 

 

1. LENGTH 함수

LENGTH 함수는 문자열의 길이를 구해줍니다.

2. UPPER, LOWER 함수

UPPER는 문자열을 모두 대문자로 바꿔서 보여주는 함수이고, LOWER는 문자열을 모두 소문자로 바꿔서 보여주는 함수입니다.

3. LPAD, RPAD 함수

이 두 함수는 문자열의 왼쪽 또는 오른쪽을 특정 문자열로 채워주는 함수입니다.

LPAD는 LEFT(왼쪽) + PADDING(채우기)의 줄임말, RPAD는 RIGHT(오른쪽) + PADDING(채우기)의 줄임말인데요.

예를 들어 LPAD(age, 10, ’0’)는 age 컬럼의 값을, 왼쪽에 문자 0을 붙여서 총 10자리로 만드는 함수입니다. 보통 어떤 숫자의 자릿수를 맞출 때 자주 사용하는 함수입니다. 아래 그림을 보면 무슨 뜻인지 바로 이해할 수 있습니다.

그런데 age 컬럼의 데이터 타입은 숫자를 나타내는 INT 형이었죠? 어떻게 숫자를 문자열 함수의 인자로 넣었는데 잘 작동한 걸까요? 비록 숫자이더라도 문자열 함수 안에 인자로 넣어주면 그 값이 자동으로 문자열로 형 변환이 되어 계산됩니다. 참고하세요.

RPAD 함수는 아래 그림처럼 LPAD 함수와 반대로 문자열의 오른쪽을 채워주는 함수입니다.

4. TRIM, LTRIM, RTRIM 함수

*아래의 예시는 이 trim_test.csv 파일을 다운로드받고 임포트해서 테이블로 만든 후 직접 실습해보세요.

이 함수들은 문자열에 존재하는 공백을 제거하는 함수들입니다.

지금 trim_test 라는 테이블의 word 라는 컬럼에 있는 값들을 사용해서 하나씩 보여드릴게요.

세 가지 함수를 사용한 결과를 보여드릴게요. 

(1) LTRIM : 왼쪽 공백 삭제

(2) RTRIM : 오른쪽 공백 삭제

(3) TRIM : 왼쪽, 오른쪽 양쪽 다 공백 삭제

LTRIM 함수는 왼쪽의 공백만, RTIM 함수는 오른쪽의 공백만, TRIM 함수는 왼쪽, 오른쪽 양쪽 모두의 공백을 제거해서 보여줍니다. 이때 이런 함수들이 문자열 내부에 존재하는 공백을 없애는 건 아니라는 사실에 주의하세요.

 

 

---

1. GROUP BY 뒤 기준들의 순서에 따라 WITH ROLLUP 의 결과도 달라집니다.

일단 member 테이블의 row들을 총 3가지 컬럼을 기준으로 그루핑해보겠습니다. 

1. 생일의 연도
2. 가입일자의 연도
3. 성별

아래 SQL 문을 볼까요?

날짜에서 연도를 추출하기 위해 YEAR 함수를 사용했고, 생일 연도에는 b_year, 가입 연도에는 s_year 라는 alias를 붙였네요.

지금 3가지 기준을 갖고 그루핑한 결과 중 일부를 빨간색 박스로 표시했는데요. 

잠깐 회색과 보라색 영역에 주목하세요.

이 영역들은 모두 생일 연도와 가입 연도의 조합을 기준으로 했을 때의 각 그룹 내에서, 딱히 성별은 구별하지 않은 부분 총계를 나타내고 있습니다. gender 컬럼에 NULL이 써있는 거 보이시죠? 혹시 이 말이 이해되지 않으시면 이전 영상을 다시 보고 와주세요.

그리고 마지막 연두색 부분은 생일 연도가 1992인 경우에 해당하는 모든 row의 수, 그러니까 가입 연도와 성별을 따지지 않은, 방금 전보다 조금 더 광범위한 수준의 부분 총계를 나타내는 부분입니다. 이렇게 그루핑 기준이 여러 개일 때는 WITH ROLLUP이 점차적으로 넓은 범위의 부분 총계를 보여줍니다.

여기까지는 잘 이해되시죠?

그리고 결과의 맨 아랫부분을 살펴보면 

모든 컬럼이 NULL인, 그러니까 세 가지 기준을 모두 고려하지 않은 부분 총계를 보여주고 있습니다. 그런데 이 말은 곧 전체 총계라는 뜻입니다. 그루핑 기준 중 어떤 기준도 딱히 따지지 않은 결과니까요. 빨간색 박스 안의 row는 지금 이 테이블의 총 row 수인 24를 보여주고 있습니다.

여기서 추가적으로 알아야할 중요한 사실은 바로,

WITH ROLLUP이 GROUP BY 뒤에 나오는 그루핑 기준의 등장 순서에 맞춰서 계층적인 부분 총계를 보여준다는 점입니다.

이 말은 GROUP BY 뒤에 나오는 그루핑 기준의 등장 순서에 따라 WITH ROLLUP이 출력하는 결과가 달라진다는 뜻인데요. 

그럼 한번 GROUP BY 뒤에서 생일 연도(b_year)와 가입 연도(s_year)의 순서를 바꾸고, SELECT 뒤에서도 순서를 바꿔보겠습니다.

그리고 정렬 순서도 가입 연도를 기준으로 내림차순하는 것으로 변경할게요. 그리고 실행하면,

아까와 또다른 형식의 부분 총계들을 볼 수 있습니다.

지금 연두색 부분을 보면 이번에는 생일 연도가 아닌 가입 연도를 기준으로 한 중간 규모의 부분 총계가 보입니다.

WITH ROLLUP이 GROUP BY 뒤의 그루핑 기준들의 등장 순서에 맞춰서 부분 총계를 보여준다는 말, 무슨 뜻인지 아시겠죠?

2. NULL임을 나타내기 위해 쓰인 NULL vs. 부분 총계을 나타내기 위해 쓰인 NULL

방금 전 SQL 문을 수정해볼게요. 그루핑 기준으로 생일 연도(b_year) 대신 회원이 사는 주요 지역명을 넣어볼게요. 실행 결과를 보면,

우리의 예상대로 부분 총계들이 잘 보입니다.

그런데 지금 자세히 보면 그림에 보이는 주황색 NULL은 부분 총계를 나타내는 row가 아닙니다. 그냥 애초에 region 컬럼에 NULL이 들어있던 row들의 그룹을 나타내고 있는 것 뿐인데요.

부분 총계는(가입 연도가 2019년이고 남성 회원이며, 주요 지역을 따지지 않은 부분 총계)를 나타내는 것은 하늘색 NULL입니다.

자, 여기서 한 가지 문제가 있다는 걸 눈치채셨을 겁니다.

우리가 NULL을 보았을 때

(1) 이게 원래 있는 NULL을 나타내는 건지,

(2) 부분 총계임을 나타내기 위해 쓰인 NULL인 건지

구분할 수가 없다는 거죠. 그런데 이 둘을 구분할 수 있게 해주는 함수가 있는데요. 바로 GROUPING이라는 함수입니다.

SQL 문을 아래와 같이 수정해볼게요. 각 그루핑 기준을 GROUPING이라는 함수의 인자로 넣은 3개의 컬럼들을 추가했습니다. 

GROUPING 함수는 그 인자를 그루핑 기준에서 고려하지 않은 부분 총계인 경우에 1을 리턴하고 그렇지 않은 경우 0을 리턴합니다. 현재 보이는 노란색 박스와 연두색 박스들을 보면 어떤 말인지 이해하실 수 있을 겁니다.

그리고 방금 전 문제도 해결된 것이 보입니다. 지금 같은 NULL이더라도 원래 NULL이 있던 곳은 0이 출력되었고(분홍색 둥근 사각형), 부분 총계를 나타내기 위해 NULL이 쓰인 곳은 1이 출력되었습니다.(연두색 둥근 사각형) 

정리하면, GROUPING 함수는,

(1) 실제로 NULL을 나타내기 위해 쓰인 NULL인 경우에는 0,

(2) 부분 총계를 나타내기 위해 표시된 NULL은 1

을 리턴해서 둘을 구분하게 해주는 함수입니다.

현재 결과의 마지막 줄을 보면 

이렇게 전체 총계를 나타내는 row에서는 모든 GROUPING 함수가 1을 리턴했다는 것을 알 수 있습니다. 전체 총계는 모든 그루핑 기준들을 무시한 채 계산된 값이기 때문에 당연한 결과죠?

만약 WITH ROLLUP을 썼을 때, 이 NULL이

(1) 실제로 NULL을 나타내기 위해서 쓰인 건지,

(2) 부분 총계를 나타내기 위해 쓰인 건지

구분하고 싶다면 GROUPING 함수를 사용해보세요.

---

1. 컬럼 가장 앞으로 당기기

지금 보면 Primary Key 역할을 하는 id 컬럼이 가장 뒤에 있어서 보기가 어색하네요. 이 컬럼을 가장 앞으로 옮기고 싶은데 어떻게 하면 좋을까요? 

이렇게 써주면 됩니다. 

우리가 배웠던 MODIFY 문이죠? id 컬럼 정보의 맨 뒤에 FIRST라고 써주면, 해당 테이블의 가장 첫 번째 컬럼이 됩니다. player_info 테이블을 다시 조회해보면, 

id 컬럼이 가장 첫번째 컬럼이 된 것을 알 수 있습니다. 이런 식으로 테이블에서는 보통 Primary Key에 해당하는 컬럼을 가장 앞에 두는 것이 일반적입니다. 

2. 컬럼 간의 순서 바꾸기

그런데 지금 보면 선수 역할을 나타내는 role 컬럼이 선수 이름을 나타내는 name 컬럼보다 이후에 나오는 것이 더 자연스러울 것 같네요. role 컬럼을 name 컬럼 이후에 위치하도록 하려면 이렇게 써주면 됩니다. 

이번에도 MODIFY를 썼는데요. 그리고 가장 마지막에 AFTER name이라고 썼습니다. 표현 그대로 name 컬럼 바로 다음으로 위치를 바꾸라는 말인데요. 이 SQL 문을 실행하고 다시 보면

컬럼의 순서가 잘 바뀐 것을 알 수 있습니다. 

3. 컬럼의 이름과 컬럼의 데이터 타입 및 속성 동시에 수정하기

우리는 이전 영상에서

컬럼의 이름을 수정할 때는 RENAME COLUMN A TO B 절을,

컬럼의 타입 및 속성을 수정할 때는 MODIFY 절을 사용한다고 배웠습니다. 

그런데 이 두 가지 성격의 작업을 한번에 수행해주는 절이 있습니다. 바로 CHANGE 인데요. 

현재 테이블에서 role이라는 컬럼을 

a. 그 이름을 position으로 바꾸고

b. 동시에 그 데이터 타입을 CHAR(5)에서 VARCHAR(2)로, 그 속성도 NULL에서 NOT NULL로 바꾸겠습니다. 

이렇게 써주면 됩니다. 

지금 맨 앞에 CHANGE라고 썼고 그 뒤에는 기존 컬럼의 이름인 role, 그 다음에는 새로운 이름(position)과 새로운 데이터 타입(VARCHAR(2)), 새로운 속성(NOT NULL)을 썼습니다. 실행하고 다시 컬럼 구조를 보면, 

role 컬럼이 새로운 데이터 타입과 속성을 가진 position이라는 컬럼으로 바뀐 것을 볼 수 있습니다. 

이렇게 컬럼의 이름과, 데이터 타입 및 속성을 동시에 바꾸고 싶을 때는 CHANGE 절을 사용하면 편리합니다. 

4. 여러 작업 동시에 수행하기

ALTER TABLE 문 뒤에는 컬럼에 관한 작업을 하는 절들을 여러 개 두는 것이 가능합니다. 

아래의 작업들을 동시에 수행해볼게요. 

a. id 컬럼의 이름을 registraion_number로 수정

b. name 컬럼의 데이터 타입을 VARCHAR(20)로, 속성을 NOT NULL로 수정

c. position 컬럼을 테이블에서 삭제

d. 새로운 컬럼 2개(height(키), weight(몸무게)) 추가

지금 4가지 작업을 동시에 수행하는 ALTER TABLE 문을 완성했는데요. 각 작업마다 굳이 매번 ALTER TABLE을 써줄 필요 없이 위 이미지처럼 여러 가지 작업을 하나의 ALTER TABLE 문 안에서 한번에 수행하는 것도 가능합니다. 

이 SQL 문을 실행하면 

이렇게 컬럼 구조가 잘 수정된 것을 확인할 수 있습니다. 

참고로, 위 SQL 문 중에서 a 작업과 b 작업을 하는 절을 CHANGE 절로 아래와 같이 쓸 수도 있습니다. 

컬럼의 이름만 바꾸거나,

컬럼의 데이터 타입 및 속성만 바꿀 때도

이런 식으로 CHANGE 절로 다 처리할 수 있습니다. 

자, 이때까지 테이블의 컬럼 구조, 컬럼의 이름/데이터 타입/속성들을 변경하는 방법에 대해서 아주 자세하게 배워보았습니다. 이정도만 알고 있어도 앞으로 여러분이 기존 테이블의 구조를 손대야할 때 아무런 어려움 없이 수정할 수 있게될 겁니다.