[DataBase] 06_함수

함수

#01. SQL 함수란?

• SQL 함수
  • 저장되어 있는 데이터를 집계하거나 조회, 저장, 수정하는 과정에서 값을 가공하기 위하여 제고오디는 모듈화 된 기능
  • 각 DBMS에 따라 차이를 보이지만, 기본적으로 많이 사용되는 함수들은 공통으로 포함하고 있다.
• 함수의 사용 방법
  • 데이터 조회 시 -> 조회하고자 하는 컬럼의 값을 함수로 가공하거나 검색 조건의 값을 지정할 때 사용한다.

select 함수이름(컬럼이름) from <테이블이름>
[where  함수가 적용된 검색조건]

#02. 문자열 관련 함수

• 문자열 관련 함수의 종류
  • 아래의 표에서 값은 컬럼 이름으로도 지정 가능하다.

함수 이름	설명
left(값, 길이)	주어진 값을 길이의 글자 수 만큼 왼쪽에서 잘라낸다.
right(값, 길이)	주어진 값을 길이의 글자 수 만큼 오른쪽에서 잘라낸다.
substring(값, 시작위치, 길이)	주어진 값을 시작위치부터 길이만큼 잘라낸다. 만약 길이가 주어지지 않은 경우 시작위치부터 끝까지 잘라낸다.
replace(값, A, B)	주어진 값에서 A를 찾아 B로 바꾼다.
concat(값1, 값2, ..., 값n )	주어진 값들을 하나의 문자열로 연결한다.
trim(값)	주어진 값의 앞뒤 공백을 제거한다.
ltrim(값)	주어진 값의 왼쪽 공백을 제거한다.
rtrim(값)	주어진 값의 오른쪽 공백을 제거한다.
password(값)	주어진 값을 암호화 한다.
char_length(값)	주어진 값의 글자수를 리턴한다.
instr(값, 찾을 내용)	주어진 값에서 찾을 내용이 시작되는 위치를 리턴한다. 찾지 못할 경우 0을 리턴한다.
upper(값)	주어진 값을 대문자로 변경한다.
lower(값)	주어진 값을 소문자로 변경한다.

ex) 예제1

• 학생 테이블에서 학생의 이름과 성(이름의 첫 글자)을 조회하시오.

mysql> select name, left(name, 1) from student;
+--------+---------------+
| name   | left(name, 1) |
+--------+---------------+
| 전인하 | 전            |
| 박미경 | 박            |
| 김영균 | 김            |
| 지은경 | 지            |
| 임유진 | 임            |
| 서재진 | 서            |
| 이광훈 | 이            |
| 류민정 | 류            |
| 김진영 | 김            |
| 오유석 | 오            |
| 하나리 | 하            |
| 윤진욱 | 윤            |
| 이동훈 | 이            |
| 박동진 | 박            |
| 김진경 | 김            |
| 조명훈 | 조            |
+--------+---------------+

ex) 예제2

• 학생 테이블에서 학생의 이름과 이름의 마지막 글자를 조회하시오.

mysql> select name, right(name, 1)
    -> from student;
+--------+----------------+
| name   | right(name, 1) |
+--------+----------------+
| 전인하 | 하             |
| 박미경 | 경             |
| 김영균 | 균             |
| 지은경 | 경             |
| 임유진 | 진             |
| 서재진 | 진             |
| 이광훈 | 훈             |
| 류민정 | 정             |
| 김진영 | 영             |
| 오유석 | 석             |
| 하나리 | 리             |
| 윤진욱 | 욱             |
| 이동훈 | 훈             |
| 박동진 | 진             |
| 김진경 | 경             |
| 조명훈 | 훈             |
+--------+----------------+

ex) 예제3

• 학생 테이블에서 이름과 이름의 두 번째 글자를 조회하시오.

mysql> select name, substring(name,2,1) from student;
+--------+---------------------+
| name   | substring(name,2,1) |
+--------+---------------------+
| 전인하 | 인                  |
| 박미경 | 미                  |
| 김영균 | 영                  |
| 지은경 | 은                  |
| 임유진 | 유                  |
| 서재진 | 재                  |
| 이광훈 | 광                  |
| 류민정 | 민                  |
| 김진영 | 진                  |
| 오유석 | 유                  |
| 하나리 | 나                  |
| 윤진욱 | 진                  |
| 이동훈 | 동                  |
| 박동진 | 동                  |
| 김진경 | 진                  |
| 조명훈 | 명                  |
+--------+---------------------+

ex) 예제4

• 학생 이름과 이름에서 '이'를 'lee'로 변경한 값을 출력하시오.

mysql> select name, replace(name, '이', 'lee') from student;\
+--------+----------------------------+
| name   | replace(name, '이', 'lee') |
+--------+----------------------------+
| 전인하 | 전인하                     |
| 박미경 | 박미경                     |
| 김영균 | 김영균                     |
| 지은경 | 지은경                     |
| 임유진 | 임유진                     |
| 서재진 | 서재진                     |
| 이광훈 | lee광훈                    |
| 류민정 | 류민정                     |
| 김진영 | 김진영                     |
| 오유석 | 오유석                     |
| 하나리 | 하나리                     |
| 윤진욱 | 윤진욱                     |
| 이동훈 | lee동훈                    |
| 박동진 | 박동진                     |
| 김진경 | 김진경                     |
| 조명훈 | 조명훈                     |
+--------+----------------------------+

ex) 예제5

• 학생 이름과 학년을 하나의 문장으로 합쳐 출력하시오.

mysql> select concat(name, grade) from student;
+---------------------+
| concat(name, grade) |
+---------------------+
| 전인하4             |
| 박미경1             |
| 김영균3             |
| 지은경2             |
| 임유진2             |
| 서재진1             |
| 이광훈4             |
| 류민정2             |
| 김진영2             |
| 오유석4             |
| 하나리1             |
| 윤진욱3             |
| 이동훈1             |
| 박동진1             |
| 김진경2             |
| 조명훈1             |
+---------------------+

ex) 예제6

• 학생의 이름과 학년을 '전인하 4학년'의 형식으로 출력하시오.

mysql> select concat(name, ' ', grade, '학년') from student;
+----------------------------------+
| concat(name, ' ', grade, '학년') |
+----------------------------------+
| 전인하 4학년                     |
| 박미경 1학년                     |
| 김영균 3학년                     |
| 지은경 2학년                     |
| 임유진 2학년                     |
| 서재진 1학년                     |
| 이광훈 4학년                     |
| 류민정 2학년                     |
| 김진영 2학년                     |
| 오유석 4학년                     |
| 하나리 1학년                     |
| 윤진욱 3학년                     |
| 이동훈 1학년                     |
| 박동진 1학년                     |
| 김진경 2학년                     |
| 조명훈 1학년                     |
+----------------------------------+

ex) 예제7

• 학생의 이름에서 앞뒤 공백을 제거한 값을 출력하시오.

mysql> select trim(name) from student;
+------------+
| trim(name) |
+------------+
| 전인하     |
| 박미경     |
| 김영균     |
| 지은경     |
| 임유진     |
| 서재진     |
| 이광훈     |
| 류민정     |
| 김진영     |
| 오유석     |
| 하나리     |
| 윤진욱     |
| 이동훈     |
| 박동진     |
| 김진경     |
| 조명훈     |
+------------+

ex) 예제8

• 학생 이름에서 왼쪽 공백을 제거한 값을 출력하시오.

mysql> select ltrim(name) from student;
+-------------+
| ltrim(name) |
+-------------+
| 전인하      |
| 박미경      |
| 김영균      |
| 지은경      |
| 임유진      |
| 서재진      |
| 이광훈      |
| 류민정      |
| 김진영      |
| 오유석      |
| 하나리      |
| 윤진욱      |
| 이동훈      |
| 박동진      |
| 김진경      |
| 조명훈      |
+-------------+

ex) 예제9

• 학생의 이름에서 오른쪽 공백을 제거한 값을 출력하시오.

mysql> select rtrim(name) from student;
+-------------+
| rtrim(name) |
+-------------+
| 전인하      |
| 박미경      |
| 김영균      |
| 지은경      |
| 임유진      |
| 서재진      |
| 이광훈      |
| 류민정      |
| 김진영      |
| 오유석      |
| 하나리      |
| 윤진욱      |
| 이동훈      |
| 박동진      |
| 김진경      |
| 조명훈      |
+-------------+s

ex) 예제10

• 학생의 이름을 암호화 한 결과를 출력하시오.

mysql> select md5(name) from student;
+----------------------------------+
| md5(name)                        |
+----------------------------------+
| 502a9ea0d0b99944ff2e01e1eb509f3f |
| 167c1446b6031e7e9ea19b2e61071718 |
| a965195d8ff7f2578ded7023158551d6 |
| fa4cf978744d94bb0f671a072c2164f9 |
| e3e7062eec14e85b3c599f509de7ede7 |
| 718752ea6162894807162c0fcad90279 |
| 37d2073e95928ba8bb6ceec546e3feff |
| 0ec9c231ae3c73a9d45cffdb81094c79 |
| c444ea4d425b22e4398b226be9d6b185 |
| aa4a066ee8459d526b5fb608998e412d |
| 9a9ddb33cc169386c0dc17af7afd0127 |
| 2607dca37796e50ee192cf075f41fe02 |
| da78898e4f770ca67155f0f0c569b471 |
| 9a3ec7fa19a04a6dc3036509b1779a21 |
| ee84f899be798f93181c6db5916c6fbd |
| 82b343678cdec365475d3083c1eee774 |
+----------------------------------+

ex) 예제11

• 학생 이름의 글자수를 조회하시오.

mysql> select char_length(name) from student;
+-------------------+
| char_length(name) |
+-------------------+
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
+-------------------+

ex) 예제12

• 학생 이름에서 '이'라는 글자가 나타나는 위치와 이름을 조회하시오.

mysql> select instr(name, '이'), name from student;
+-------------------+--------+
| instr(name, '이') | name   |
+-------------------+--------+
|                 0 | 전인하 |
|                 0 | 박미경 |
|                 0 | 김영균 |
|                 0 | 지은경 |
|                 0 | 임유진 |
|                 0 | 서재진 |
|                 1 | 이광훈 |
|                 0 | 류민정 |
|                 0 | 김진영 |
|                 0 | 오유석 |
|                 0 | 하나리 |
|                 0 | 윤진욱 |
|                 1 | 이동훈 |
|                 0 | 박동진 |
|                 0 | 김진경 |
|                 0 | 조명훈 |
+-------------------+--------+

ex) 예제13

• 학생의 아이디를 대문자로 변경하여 조회하시오.

mysql> select upper(userid) from professor;
+---------------+
| upper(userid) |
+---------------+
| CAPOOL        |
| SWEAT413      |
| PASCAL        |
| BLUE77        |
| REFRESH       |
| POCARI        |
| TOTORO        |
| BIRD13        |
+---------------+

ex) 예제14

• 학생의 아이디를 소문자로 변경하여 조회하시오.

mysql> select lower(userid) from professor;
+---------------+
| lower(userid) |
+---------------+
| capool        |
| sweat413      |
| pascal        |
| blue77        |
| refresh       |
| pocari        |
| totoro        |
| bird13        |
+---------------+

#03. 날짜 관련 함수

• 날짜 관련 함수의 종류

함수 이름	설명
now()	시스템의 현재 시각을 리턴한다.
date-add(시각, INTERVAL 값 단위)	주어진 시각을 기준으로 날짜를 연산하여 리턴한다.
date_format(시각, 형식)	주어진 시각을 형식에 맞춰 변경한 결과를 리턴한다.

- date_add 함수에서 사용 가능한 단위
    - YEAR, MONTH, DAY,HOUR, MINUTE, SECOND
    - 사용 예
        - 1년 후 -> date_add(now(), INTERVAL 1YEAR)
        - 3개월 전 -> date_add(now(), INTERVAL -3 MONTH)

기능	키워드	기능	키워드
달 이름	%M	MM형식의 월	%m
요일 이름	%W	HH형식의 시간(24시간제)	%H
YYYY형식의 년도	%Y	H형식의 시간(24시간제)	%k
YY형식의 년도	%y	HH형식의 시간(12시간제)	%h
요일 이름의 약자	%a	MM형식의 분	%i
DD형식의 날짜	%d	SS형식의 초	%s
D형식의 날짜	%e	AM/PM	%p

• 사용 예
  • yy/mm/dd hh:mi/ss 형식의 날짜 얻기
    • -> select date_format(now(), '%y/%m/%d %H:%i%s');
    • 형식을 일정하게 맞춰주기 위해 데이터 포맷 형식을 쓴다.

ex) 예제1

• 시스템의 현재 시각 조회하기

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-11-12 11:50:31 |
+---------------------+

ex) 예제2

• 현재 시점에서 100일 후 조회하기
• 현재 시점에서 7일 전 조회하기

mysql> select date_add(now(),interval 100 day);
+----------------------------------+
| date_add(now(),interval 100 day) |
+----------------------------------+
| 2020-02-20 11:52:06              |
+----------------------------------+

mysql> select date_add(now(), interval -7 day);
+----------------------------------+
| date_add(now(), interval -7 day) |
+----------------------------------+
| 2019-11-05 11:52:59              |
+----------------------------------+

ex) 예제3

• 현재 시각을 yyyymmddhhmiss 형식의 14자리로 표현하시오

mysql> select date_format(now(), '%Y%m%d%H%i%s');
+------------------------------------+
| date_format(now(), '%Y%m%d%H%i%s') |
+------------------------------------+
| 20191112115403                     |
+------------------------------------+

연습문제1

• 학생의 이름과 가운데 글자를 '*'로 변경한 이름을 조회하시오. 단 이름은 3글자로만 구성된다고 가정합니다.

mysql> select name, concat(left(name,1),'*',right(name,1)) from student;
+--------+----------------------------------------+
| name   | concat(left(name,1),'*',right(name,1)) |
+--------+----------------------------------------+
| 박미경 | 박*경                                  |
| 김영균 | 김*균                                  |
| 지은경 | 지*경                                  |
| 임유진 | 임*진                                  |
| 서재진 | 서*진                                  |
| 이광훈 | 이*훈                                  |
| 류민정 | 류*정                                  |
| 홍길동 | 홍*동                                  |
| 둘리   | 둘*리                                  |
| 김진영 | 김*영                                  |
| 오유석 | 오*석                                  |
| 하나리 | 하*리                                  |
| 윤진욱 | 윤*욱                                  |
| 이동훈 | 이*훈                                  |
| 박동진 | 박*진                                  |
| 조명훈 | 조*훈                                  |
+--------+----------------------------------------+

연습문제2

• 학생의 이름과 주민번호를 조회하시오. 단 주민번호의 뒤 자리 7글자는 '*'로 표시하시오.

mysql> select name, concat(left(idnum,6),'*******') from student;
+--------+---------------------------------+
| name   | concat(left(idnum,6),'*******') |
+--------+---------------------------------+
| 박미경 | 840516*******                   |
| 김영균 | 810321*******                   |
| 지은경 | 800412*******                   |
| 임유진 | 830121*******                   |
| 서재진 | 851129*******                   |
| 이광훈 | 810913*******                   |
| 류민정 | 810819*******                   |
| 홍길동 | 850101*******                   |
| 둘리   | 820202*******                   |
| 김진영 | 820606*******                   |
| 오유석 | 770912*******                   |
| 하나리 | 850109*******                   |
| 윤진욱 | 790402*******                   |
| 이동훈 | 831210*******                   |
| 박동진 | 851124*******                   |
| 조명훈 | 841214*******                   |
+--------+---------------------------------+

연습문제3

• 1980년도 이후에 출생한 학생의 이름과 생년우러일을 조회하시오.

mysql> select name, birthdate from student
    -> where date_format(birthdate,'%Y') > 1980;
+--------+---------------------+
| name   | birthdate           |
+--------+---------------------+
| 박미경 | 1984-05-16 00:00:00 |
| 김영균 | 1981-03-21 00:00:00 |
| 임유진 | 1983-01-21 00:00:00 |
| 서재진 | 1985-11-29 00:00:00 |
| 이광훈 | 1981-09-13 00:00:00 |
| 류민정 | 1981-08-19 00:00:00 |
| 홍길동 | 2013-10-01 11:42:30 |
| 둘리   | 2011-10-01 11:42:30 |
| 김진영 | 1982-06-06 00:00:00 |
| 하나리 | 1985-01-09 00:00:00 |
| 이동훈 | 2019-11-13 16:20:53 |
| 박동진 | 1985-11-24 00:00:00 |
| 조명훈 | 1984-12-14 00:00:00 |
+--------+---------------------+