데이터베이스 연결, 변경
-- 유저 확인
select user();
-- 데이터베이스 확인
select database();
-- 데이터베이스 변경 : use 데이터베이스 이름;
use autoever;Function
Function이란 입력 데이터를 이용해서 연산을 수행한 후 출력 값을 만들어내는 개체이다. 입력데이터를 Argument(Parameter, 매개변수, 인수, 인자 등)라 하고, 출력 데이터를 Return Value 라고 한다.
프로그래밍언어의 함수는 리턴 값이 없는 경우도 있지만 데이터베이스 함수는 반드시 리턴을 하고 원본을 변경하지 않는다.
함수는 데이터를 수정하거나 연산을 수행해서 지원하는 일을 한다. 데이터베이스 함수는 데이터베이스 자체를 수정할 수 없다.
1. 함수의 종류
scala Function : 데이터 단위로 연산을 수행하는 함수
- 데이터 1개를 주면 1개가 리턴되고 하나의 열을 대입하면 열에 존재하는데이터만큼 리턴
grouping Function : 여러개의 데이터를 묶어서 연산을 수행하는 함수
system Function : NULL 관련 처리나 타입 변환 등을 수행하는 함수
Scala Function
1. 수치함수
1) ROUND
ROUND(컬럼이름이나 표현식, 반올림할 자릿수)양수를 기재하면 그 소수점 자리에서 반올림을 하고, 음수를 기재하면 1의 자리부터 위로 올라간다.
select sal, round(sal, -2)
from emp;
5가 반올림이 안되는 경우
int(data + 0.5) : int가 소수점을 버림
select sal, round(sal, -2), FLOOR(sal/100 + 0.5) * 100
from emp;
2) Mode
MOD(컬럼이름이나 표현식, 나눌수)뒤의 수로 나누어서 나머지를 리턴해준다
예시) emp 테이블에는 EMPNO라는 숫자로된 사원번호 컬럼이 존재한다. 사원번호가 홀수인 사원의 모든 컬럼 조회
select *
from emp
where mod(empno,2) = 1;
2. 문자열 관련 함수
1) CHAR(숫자)
숫자를 문자로 변환
2) BIT_LENGTH, CHAR_LENGTH, LENGTH
비트크기, 글자길이, 메모리크기(Byte)
select ename, BIT_LENGTH(ENAME), CHAR_LENGTH(ENAME), LENGTH(ENAME)
from EMP;
한글을 사용할 때는 한글 1글자가 몇 바이트를 차지하는지 알아볼 필요가 있다.
한글 1개 = 3byte
select name, BIT_LENGTH(NAME), CHAR_LENGTH(NAME), LENGTH(NAME)
from tstaff;
3) LEFT, RIGHT
길이만큼 왼쪽이나 오른쪽에서 추출
4) UPPER, LOWER
대문자, 소문자
예시) EMP 테이블에서 ENAME이 scott인 사원을 조회
select *
from EMP
where lower(ENAME) = 'scott';오라클은 기본적으로 대소문자를 구분하지만 MySQL이나 MariaDB는 기본적으로 대소문자 구분을 하지 않는다.(설정 파일에 lower_case_table의 값을 1로 설정하면 테이블 이름을 모두 소문자로 변경해서 사용한다)
5) LTRIM, RTRIM, TRIM
공백제거
6) substring
substring(컬럼이름이나 연산식, 시작위치, 가져올 개수)부분문자열 리턴
예시) emp 테이블에서 hiredate는 입사일을 의미한다. 형식은 yyyy-mm-dd 형식이다. 02월에 입사한 사원의 ename과 hiredate를 조회
-- substirng 이용
select ename, hiredate
from emp
where substring(hiredate,6,2)='02';
-- like 이용
select ename, hiredate
from emp
where hiredate like '_____02%';
3. 날짜 함수
데이터베이스는 날짜를 숫자와 문자 모두로 표현이 가능한데 산술 연산도 가능
1) 현재날짜 및 시간을 위한 함수
CURRENT_DATE(), CURDATE() -> 현재 날짜
CURRENT_TIME(), CURTIME() -> 현재 시간
NOW(), LOCALTIME(), LOCALTIMESTAMP()
CURRENT_TIMESTAMP() -> 현재 날짜 및 시간
2) 년월일시분초마이크로초 반환 함수
YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시 간), MICROSECOND(시간)
예시) 02월에 입사한 사원의 ename과 hiredate를 조회
select ename, hiredate
from emp
where month(hiredate) = 2 ;3) 문자열을 이용해서 날짜 생성
MariaDB, MySQL 은 일반적인 날짜 형식의 문자열도 날짜로 인식한다.
직접 변환하고자 하는 경우 : STR_TO_DATE(날짜 문자열, 서식문자열)
4) 날짜와 INTERVAL 을 이용해서 기간형 데이터와 연산 가능
날짜 + INTERVAL 숫자 기간(DAY 등)
select CURRENT_DATE() + INTERVAL 2 DAY;
5) 날짜 간의 뺄셈
DATEDIFF(날짜 데이터, 날짜 데이터)
TIMEDIFF(날짜 또는 시간 데이터, 날짜 또는 시간 데이터)
select datediff(CURRENT_DATE(), STR_TO_DATE('1987-05-05','%Y-%m-%d'));
4. 시스템정보함수
USER(),DATABASE()FOUND_ROWS()ROW_COUNT()VERSION()SLEEP(초)
5. 타입 변환 연산자
1) Data Type
DATETIME: 'YYYY-MM-DD HH:MM:SS'DATE: 'YYYY-MM-DD'TIME: 'HH:MM:SS'CHAR: StringSIGNED: Integer(64bit), 부호 사용 가능UNSIGNED: Integer(64bit), 부호 사용 불가BINARY: binary String
2) 숫자를 문자로 변환
SELECT CAST(숫자 AS CHAR(길이))SELECT CAST(1 AS CHAR(10)) AS testChar3) 문자를 숫자로 변환
SELECT CAST('1' AS SINGNED) AS testInt6. NULL 관련 함수
1) IFNULL
IFNULL(데이터1,데이터2) : 데이터 1이 NULL이면 데이터2 반환
NULL + 100은 연산불가하기 때문에 NULL을 0으로 간주하고 싶을 때
IFNULL(NULL, 0) + 100 사용한다
SELECT COMM, IFNULL(COMM,0)+100
FROM emp;
2) NULLIF
NULLIF(데이터1,데이터2) : 데이터1과 데이터2가 같으면 NULL을 리턴, 아니면 데이터1을 리턴
3) COALESCE
COALESCE(데이터 나열) : 데이터 중 NULL이 아닌 첫번째 데이터 리턴
7. IF 함수
IF(연산식, 참일 때 내용, 거짓일 때 내용)
8. CASE ~ WHEN ~ ELSE ~ END
CASE 데이터
WHEN 값 THEN 데이터가 값일 때 내용
..
ELSE 일치하는 값이 없을 때 내용
END