1. 함수(function)
1) 역할: 데이터 가공처리
2) 종류:
가. 단일행 함수 ( single row function )
- 테이블의 행(레코드) 단위로 적용되는 함수 의미.
- 데이터별 종류
a. 문자데이터
- 길이,부분열,대문자,소문자,공백제거,...
b. 수치데이터
- 반올림, 절삭, 부호식별,절대값,...
c. 날짜 ( Oracle 함수와 완전히 다르다.)
- 현재날짜,시간, 연산, 특정날짜추출(년도,월,시간,...)
d. 변환함수
- 숫자==> 문자, 문자==> 숫자
날짜==>문자 , 문자==>날짜
숫자 <----> 문자 <----->날짜
e. etc
- ifnull, case, if
나. 그룹 함수 ( group function)
- 테이블의 그룹 단위로 적용되는 함수 의미
예> 부서별, 성별
- 총합,평균,최대,최소,갯수
sum(), avg(), max(), min(), count()
2. 단일행 함수: 문자데이터
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/string-functions.html
- upper()
- lower()
- length()
- substr(), substring(), left(), right()
- lpad(), rpad()
- ltrim(), rtrim(), trim()
- concat(n,n2,..)
- concat_ws(구분자,n,n2,.)
- format()
- instr()
- replace()
- space(n)
- repeat()
- reverse()
3. 단일행 함수: 수치데이터
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/numeric-functions.html
- round
- truncate
- ceil
- floor
- sign
- mod
- abs
4. 단일행 함수: 날짜데이터
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/date-and-time-functions.html
- curdate() : 'yyyy-MM-DD'
- curtime() : 'hh:mm:ss'
- now(), sysdate()
- adddate()
- date_date()
date_sub()
- datediff()
- TIMESTAMPDIFF()
- last_day()
- extract()
- date_format() : 날짜를 문자열로 변환
- str_to_date(): 문자를 날짜로 변환
5. 단일행 함수: 조건처리
- case ~ end ( ANSI )
- if()
6. 단일행 함수: 형변환 함수
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/cast-functions.html
- cast()
- convert()
##########################################################################
7. 그룹함수
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/aggregate-functions.html
==> 그룹단위로 적용됨.
만일 명시적으로 그룹으로 묶는 작업을 안하면 자동으로 전체 레코드를 하나의 그룹으로 묶음.
==> 기본적으로 그룹함수는 null 제외하고 처리됨.
==> group by로 묶지 않는 컬럼은 select 절에 그룹함수와 같이 사용 불가.
그룹함수와 같이 사용할려면 반드시 group by로 그룹핑해야 된다.
예> select deptno, sum(sal) // 에러
from emp;
해결:
select deptno, sum(sal)
from emp
group by deptno;
예> select deptno, hiredate, sum(sal)
from emp
group by deptno, hiredate;
==> where 절에는 그룹함수 사용 불가.
having 절, order y 절에도 사용 가능
==> group by 표현식
mysql: 표현식에 컬럼명|별칭|순서 모두 가능
oracle: 별칭|순서 사용 불가
1) 종류
sum(컬럼)
avg(컬럼)
max(컬럼)
min(컬럼)
count(컬럼|*) : 레코드 갯수
: count(*) 는 null 여부와 무관하게 전체 레코드 갯수를 반환함.
2) 명시적으로 그룹으로 묶는 방법
문법:
group by 표현식, 표현식 // 다중그룹
mysql: 표현식에 컬럼명|별칭|순서 모두 가능
oracle: 별칭|순서 사용 불가
select sum(sal), 그룹함수 // 4번
from // 1번
where // 2번
group by deptno // 3번 부서별
order by ; // 5번
3) 그룹에 대한 필터링
select sum(sal), 그룹함수 // 5번
from // 1번
where 조건식 // 2번: 1번에 대한 필터링
group by deptno // 3번 부서별
having 조건식 // 4번: 3번에 대한 필터링
order by ; // 6번
use testdb;
##################################################################
## 문자열
-- 11. 정렬
SELECT empno, ename, hiredate
FROM emp
ORDER BY hiredate; -- 오름차순정렬
SELECT empno, ename, hiredate
FROM emp
ORDER BY hiredate DESC; -- 내림차순정렬
--
SELECT empno, ename, sal * 12 AS Annual
FROM emp
ORDER BY Annual; -- 별칭을 기준으로 정렬도 가능하다.
SELECT empno, ename, sal * 12
FROM emp
ORDER BY sal * 12; -- 별칭을 안써도 똑같은 값이 나온다.
--
SELECT empno, ename, sal * 12 AS Annual
FROM emp
ORDER BY 1; -- 컬렴명위치로 사용 가능하다.( 1: empno, 2: ename, 3: Annual )
-- 다중정렬
SELECT empno, ename, sal * 12 AS Annual
FROM emp
ORDER BY 3, 1 DESC;
-- Annual 로 먼저 정렬하고 empno 로 정렬
-- 같은 Annual 값이 있을때는 empno 가 큰순으로 먼저 출력
SELECT empno, ename, sal * 12 AS Annual
FROM emp
ORDER BY 3, 1 ASC;
-- 같은값이 있을때는 empno 가 작은순으로 출력alter
-- 2일차 함수 -----------------------------------------------
-- 1. 단일행함수 - 문자데이터
SELECT empno, lower(ename), LOWER('HeLLO')
FROM emp; -- 소문자
SELECT empno, UPPER(ename), UPPER('HeLLO')
FROM emp; -- 대문자
--
SELECT empno, CONCAT(ename, ' ', sal) as "ename_sal"
FROM emp; -- ename 과 sal 컬럼을 연결해서 하나의 컬럼으로 나오게했음.
SELECT empno, CONCAT_WS('/', ename, sal)
FROM emp; -- ename 과 sal 컬럼을 연결할때 '/' 로 구분지어서 연결.
--
SELECT empno, LPAD(ename,10,'*'), LPAD(sal, 10,'*')
FROM emp; -- 왼쪽부터 ( enam,sal 에 왼쪽부터 * 을 채워 10개의 문자로 만들기 )
SELECT empno, RPAD(ename,10,'*'), RPAD(sal, 10,'*')
FROM emp; -- 오른쪽부터 ( enam,sal 에 오른쪽부터 * 을 채워 10개의 문자로 만들기 )
--
SELECT empno, ename, SUBSTR(ename,1,2), SUBSTR(ename,3),SUBSTR(ename, -1)
FROM emp; -- SUBSTR(1,2) : 1부터 2까지 문자만 출력 / 양수: 순방향, 음수: 역방향
SELECT empno, ename, SUBSTRING(ename,1,2), SUBSTRING(ename,3),SUBSTRING(ename, -1)
FROM emp;
--
SELECT empno, ename, LENGTH(ename)
FROM emp; -- 길이반환
--
SELECT empno, ename, sal, REPLACE(sal, '0','o')
FROM emp; -- 치환; 0 -> o 로 치환
--
SELECT INSTR('foobarbar', 'bar'), INSTR('foobarbar', 'xbar');
-- 문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환 한다.
-- 지정한 문자열이 발견되지 않으면 0이 반환 됩니다.
-- 자바의 indexOf 역할
-- 차이점은 자바는 없으면 -1 을 반환하고 MySQL 은 없으면 0 반환.
--
-- 문자열의 첫 문자부터 확인해서 공백 문자가 나타나는 동안 해당 문자를 제거.
SELECT LTRIM(' bar '), LENGTH(LTRIM(' bar '));
-- 문자열의 끝 문자부터 확인해서 공백 문자가 나타나는 동안 해당 문자를 제거.
SELECT RTRIM(' bar '), LENGTH(RTRIM(' bar '));
-- 공백삭제
SELECT TRIM(' bar '),
TRIM(BOTH FROM ' bar '),
TRIM(LEADING FROM ' bar '),
TRIM(TRAILING FROM ' bar ');
-- 특정문자삭제
SELECT TRIM('xxxbarxxx'),
TRIM(BOTH 'x' FROM 'xxxbarxxx'),
TRIM(LEADING 'x' FROM 'xxxbarxxx'),
TRIM(TRAILING 'x' FROM 'xxxbarxxx');
--
SELECT ename, REVERSE(ename)
FROM emp; -- 문자열 값을 거꾸로 반환한다.
--
SELECT FORMAT(9876543.2145, 2);
-- 9,876,543.21 ( 문자열 )
-- 세자리마다 , 를 찍고 소수점 두번째자리까지 출력
-- 지정된 숫자값을 ‘#,###,###.##’ 같은 포맷을 적용하여 문자열로 반환.
SELECT FORMAT(9876543.2145, 2,'en_US'),FORMAT(9876543.2145, 2,'ko_KR');
--
-- 삽입 : INSERT(str, pos, len, newstr )
-- ==> 지정된 위치의 len 만큼 newstr 로 치환
SELECT INSERT('abcdefg', 1, 4, '####');
-- 인덱스 1번~4번 까지 # 으로 변경
--
-- 왼쪽부분열: LEFT(str,len)
SELECT LEFT('foobarbar',5); -- fooba
-- 오른쪽부분열 : RIGHT(str,len)
SELECT RIGHT('foobarbar',5); -- arbar
--
-- 문자열 반복 : REPEAT(str,count)
SELECT REPEAT('MySQL',3);
#########################################################
## 숫자
-- 2. 단일행함수 - 숫자데이터
-- ROUND()
SELECT ROUND(45.678), ROUND(45.678, 2) , ROUND(45.678, -1);
SELECT ROUND(45.678), ROUND(45.678, 2) , ROUND(45.678, -1) FROM DUAL;
-- 숫자를 소수점 n자리까지 반올림해서 출력한다. 생략하면 기본값은 0.
-- -n이 양수이면 소수 자리를, 음수이면 정수 자리를 반올림한다.
-- 문법: ROUND( 정수 [, n])
--
-- TRUNCATE()
SELECT TRUNCATE(45.678, 0), TRUNCATE(45.678, 2) ,
TRUNCATE(45.678, -1)
FROM DUAL;
-- oracle : trunc(값 [,자릿수])
-- 숫자를 소수 n 자리까지 절삭해서 출력한다. 생략불가
-- - n이 양수이면 소수 자리를, 음수이면 정수 자리를 절삭
-- 문법: TRUNCATE( 정수 , n)
--
-- CEIL(CEILING) 함수 (올림값)
-- - 주어진 숫자보다 크거나 같은 최소 정수 리턴
-- 문법: CEIL( 실수값)
SELECT CEIL(45.178), CEIL(-45.178); -- 46 / -45
-- FLOOR 함수 (버림값)
-- - 주어진 숫자보다 작거나 같은 최대 정수 리턴
-- 문법: FLOOR( 실수값)
SELECT FLOOR(45.178), FLOOR(-45.178); -- 45 / -46
--
-- MOD 함수
-- oracle : % 지원안됨. mod() 만 지원
-- - 숫자의 나머지를 구하는 함수로서 % 및 MOD 연산자와 동일
-- - n으로 나누어 남은 값을 반환한다. n이 0일 경우 그 자체를 반환.
-- 문법: MOD(m, n)
SELECT MOD(10, 3), 10%3 , 10 MOD 3 FROM DUAL; -- 1 / 1 / 1
--
-- SIGN 함수
-- - 숫자의 부호를 식별하는 함수로서 음수(-1), 양수(1), 0(0) 값 반환
-- 문법: SIGN(값)
SELECT SIGN(-1.200), SIGN(34.3), SIGN(0) FROM DUAL;
#########################################################
## 날짜
-- 3. 단일행함수 - 날짜 함수
-- 1) 현재 날짜 출력, 포맷 : 'YYYY-MM-DD’
-- CURDATE 함수
-- - 데이터베이스 서버에 설정되어 있는 날짜를 리턴.
-- 출력 포맷은 'YYYY-MM-DD’ 형식의 문자열 포맷이다.
-- - current_date() 와 current_date 는 curdate()의 synonym이다.
-- 문법: CURDATE()
SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE FROM DUAL;
-- 2) 현재 시간 출력, 포맷: 'hh:mm:ss'
-- CURTIME 함수
-- - 데이터베이스 서버에 설정되어 있는 시간을 리턴.
-- 출력 포맷은 'hh:mm:ss’ 형식의 문자열 포맷이다.
-- - current_time() 와 current_time 는 curtime()의 synonym이다.
-- 문법: CURTIME()
SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME FROM DUAL;
-- 3) 현재날짜와 시간 출력, 포맷: ＇YYYY-MM-DD hh:mm:ss＇
-- SYSDATE 및 NOW 함수
-- oracle : sysdate 소괄호 없음
-- - 데이터베이스 서버에 설정되어 있는 날짜 및 시간을 리턴.
-- 출력 포맷은 ＇YYYY-MM-DD hh:mm:ss＇ 이다.
-- 문법: SYSDATE() 및 NOW()
SELECT SYSDATE(), NOW() FROM DUAL;
-- SYSDATE() vs NOW()
-- SYSDATE() : 함수가 실행되는 시간을 반환
-- NOW() : sql 명령문이 실행할때의 시간을 반환
SELECT NOW(), sleep(2), NOW() FROM DUAL;
-- sleep 2초후 NOW 는 값 변화가 없음
SELECT SYSDATE(), sleep(2), SYSDATE() FROM DUAL;
-- SYSDATE() 는 뒤의 SYSDATE() 에 찍힌 시간이 2초늦음.
--
-- 4) 날짜에 일(day) 더하거나 빼기
-- ADDDATE 함수
-- 날짜에 일(day)을 더하거나 뺀다.
-- 문법: ADDDATE(date, days)
SELECT ADDDATE('2008-01-02', 30), ADDDATE('2008-01-02', -30);
SELECT ADDDATE(now(), 30), ADDDATE(now(),-30);
-- 4) ADDDATE(date, interval값 unit)
SELECT ADDDATE(now(), INTERVAL 1 DAY),
ADDDATE(now(), INTERVAL 1 MONTH),
ADDDATE(now(), INTERVAL 1 YEAR),
now(),
ADDDATE(now(), INTERVAL 2 HOUR),
ADDDATE(now(), INTERVAL 3 MINUTE)
FROM dual;
-- DATE_ADD 함수
-- -날짜에 입력된 기간(interval)만큼 더하거나 뺀다.
-- 문법: DATE_ADD(date, INTERVAL expr unit)
SELECT DATE_ADD('2008-01-02', INTERVAL 1 DAY) as A1,
DATE_ADD('2008-01-02', INTERVAL 1 MONTH) as A2,
DATE_ADD('2008-01-02', INTERVAL 1 YEAR) as A3,
NOW() as A4,
DATE_ADD(NOW(), INTERVAL 10 MINUTE) as A5,
DATE_ADD(NOW(), INTERVAL 2 HOUR) as A6;
-- DATE_SUB 함수
-- -날짜에 입력된 기간(interval)만큼 빼거나 더한다.
-- 문법: DATE_SUB(date, INTERVAL expr unit)
SELECT DATE_SUB('2008-01-02', INTERVAL 1 DAY) as B1,
DATE_SUB('2008-01-02', INTERVAL 1 MONTH) as B2,
DATE_SUB('2008-01-02', INTERVAL 1 YEAR) as B3,
NOW() as B4,
DATE_SUB(NOW(), INTERVAL 10 MINUTE) as B5,
DATE_SUB(NOW(), INTERVAL 2 HOUR) as B6;
--
-- 5) 두 날짜간의 일수 계산
-- DATEDIFF함수
-- -날짜간 일(day) 차이를 반환한다.
-- 문법: DATEDIFF(date1, date2)
SELECT DATEDIFF('2023-01-04', '2022-01-04');
--
-- 6) 날짜에 지정된 단위(unit)만큼 차이를 반환한다.
-- TIMESTAMPDIFF함수
-- -날짜에 지정된 단위(unit)만큼 차이를 반환한다.
-- 문법: TIMESTAMPDIFF( unit, date1, date2)
SELECT TIMESTAMPDIFF(HOUR, '2020-3-1', '2020-3-3') AS C1,
TIMESTAMPDIFF(DAY, '2020-3-1', '2020-3-3') AS C2,
TIMESTAMPDIFF(MONTH, '2020-2-1', '2020-6-3') AS C3,
TIMESTAMPDIFF(YEAR, '2020-3-1', '2022-3-3') AS C4;
--
-- 7) 월의 마지막 날짜 반환
-- LAST_DAY 함수
-- oracle 지원
-- - 월의 마지막 날짜를 계산
-- - 윤년, 평년은 자동 계산
SELECT LAST_DAY('2003-02-05'), LAST_DAY(NOW());
--
-- 8) 날짜에서 특정 unit 에 해당하는 정보 반환
-- EXTRACT 함수
-- oracle 지원
-- - 날짜에서 지정된 단위(unit)에 해당하는 날짜 정보 추출.
-- 문법: EXTRACT( unit FROM date)
SELECT NOW(),
EXTRACT(SECOND FROM NOW()),
EXTRACT(MINUTE FROM NOW()),
EXTRACT(HOUR FROM NOW()),
EXTRACT(DAY FROM NOW()),
EXTRACT(MONTH FROM NOW()),
EXTRACT(YEAR FROM NOW()),
EXTRACT(YEAR_MONTH FROM NOW());
--
-- 9) 날짜데이터를 문자열로 특정 포맷지정해서 반환 : DATE_FORMAT(date,format)
-- DATE_FORMAT 함수
-- - 지정된 날짜를 format 적용하여 문자열로 반환.
-- oracle : TO_CHAR(datetime,format)
SELECT NOW(),
DATE_FORMAT(NOW(),'%Y%m%d'),
DATE_FORMAT(NOW(),'%Y/%m/%d'),
DATE_FORMAT(NOW(),'%Y년%m월 %d일'),
DATE_FORMAT(NOW(),'%H:%i:%S'),
DATE_FORMAT(NOW(),'%Y');
--
-- 10) 문자데이터를 날짜로 반환 : STR_TO_DATE(str,format)
-- STR_TO_DATE 함수
-- - 지정된 문자열을 format 적용하여 날짜로 반환.
-- 문법: STR_DATE_FORMAT(str, format)
-- oracle: TO_DATE(str,format)
SELECT STR_TO_DATE('2020-03-04','%Y-%m-%d'),
STR_TO_DATE('01,5,2013','%d,%m,%Y'),
STR_TO_DATE('2020년03월05일','%Y년%m월%d일'),
STR_TO_DATE('2020-03-04 12:23:32','%Y-%m-%d %H:%i:%S');
--
-- 11) 조건문 : case문
-- ANSI
-- 가. 동등비교하는 case 문
-- 문법 1: 동등비교
-- CASE value WHEN compare_value THEN result
-- [WHEN compare_value THEN result ...]
-- [ELSE result] END
SELECT empno, ename, sal, job,
CASE job WHEN 'ANALYST' THEN sal * 1.1
WHEN 'CLERK' THEN sal * 1.2
WHEN 'MANAGER' THEN sal * 1.3
WHEN 'PRESIDENT' THEN sal * 1.4
WHEN 'SALESMAN' THEN sal * 1.5
ELSE sal
END AS 실수령
FROM emp;
-- 나. 부등비교하는 case 문
-- 문법2: 부등비교
-- CASE WHEN condition THEN result
-- [WHEN condition THEN result ...]
-- [ELSE result] END
SELECT empno, ename, sal,
CASE WHEN sal > 3500 THEN '이사급'
WHEN sal > 2500 THEN '과장급'
ELSE '사원급'
END AS 직급
FROM emp;
SELECT empno, ename, sal,
CASE WHEN sal > 3500 and sal < 8000 THEN '이사급'
WHEN sal > 2500 and sal in(300,400,500) THEN '과장급'
ELSE '사원급'
END AS 직급
FROM emp;
-- IF 함수
-- 문법: IF (조건식, 참, 거짓 )
SELECT sal, IF(sal > 2500, '과장이상급','사원급')
FROM emp;
--
-- 12) 형변환 함수
SELECT '10',
cast('10' as SIGNED),
cast('10' as SIGNED INTEGER),
cast(100 as char),
cast(100 as float),
cast('24-03-29' as DATE),
cast('240329' as DATE),
cast('24-03-29 15:23:34' as DATETIME)
from dual;
SELECT '10',
convert('10' , SIGNED),
convert(100 , char),
convert('24-03-29' , DATE)
from dual;
#################################################################
-- 4. 그룹함수
SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal), COUNT(sal)
FROM emp
WHERE job = 'SALESMAN';
SELECT COUNT(*),
COUNT(comm),
AVG(comm),
AVG(IFNULL(comm,0)),
AVG(sal),
AVG(DISTINCT sal),
COUNT(deptno),
COUNT(DISTINCT deptno)
FROM emp;
--
-- GROUP BY
-- -전체 테이블이 아닌 특정 그룹으로 묶을 때 사용. ( 예: 부서별, 직급별 )
-- SELECT [ column ,] 그룹함수( column), …
-- [FROM table]
-- [WHERE 조건식]
-- [GROUP BY column|별칭|컬럼순서]
-- [ORDER BY column|별칭|컬럼순서];
SELECT deptno, AVG(sal), MAX(sal), MIN(sal), SUM(sal), COUNT(sal)
FROM emp GROUP BY deptno;
SELECT deptno, hiredate, AVG(sal), MAX(sal), MIN(sal), SUM(sal), COUNT(sal)
FROM emp GROUP BY deptno, hiredate;
--
SELECT deptno, sal, AVG(sal), MAX(sal), MIN(sal), SUM(sal), COUNT(*)
FROM emp
GROUP BY deptno, sal
ORDER BY 1,2;
--
-- HAVING 절
-- : GROUP BY에 의해 분류된 그룹들을 제한하기 위한 방법.
-- 5. SELECT [ column ,] group_function( column), …
-- 1. FROM table
-- 2. [WHERE 조건식]
-- 3. [GROUP BY column]
-- 4. [HAVING group_조건식
-- 6. [ORDER BY column];
SELECT deptno, sal, AVG(sal), MAX(sal), MIN(sal), SUM(sal), COUNT(*)
FROM emp
WHERE sal > 1500
GROUP BY deptno, sal
having avg(sal) > 2000
ORDER BY 1,2;
SELECT deptno, sal, AVG(sal), MAX(sal), MIN(sal), SUM(sal), COUNT(*)
FROM emp
WHERE sal > 1500
GROUP BY deptno, sal
having avg(sal) > 2000
ORDER BY AVG(sal),2;'Programming > MySQL' 카테고리의 다른 글
| MySQL 4일차 (0) | 2024.04.02 |
|---|---|
| MySQL 3일차정리 (0) | 2024.04.02 |
| MySQL 3일차 (0) | 2024.04.01 |
| MySQL 1일차 수정 (0) | 2024.03.29 |
| 1일차 수업 (0) | 2024.03.28 |