AWS 클라우드환경 네이티브 수업 72일차
진행
1. 데이터베이스(Database) 기초
2. 테이블의 병합 (Join)
요약
1. 데이터베이스(Database) 기초
(Length 함수, Locate() 함수 (문자열 시작위치 찾기) , LEFT / RIGHT 함수 (문자열 추출) , Lower / Upper 함수 (대소문자 변경) , Replace() 함수 (문자열을 바꿈) , Trim() 함수 (특정 문자 제거) , Format() 함수 (3자리마다 쉼표) , Floor() / Ceil() 함수 (가까운 정수 반환) , Round() 함수 (반올림) , Sqrt() / Pow() / Exp() / Log() 함수, SIN / COS / TAN() 함수, ABS / RAND() 함수, Now() / Curdate() / Curtime() 함수, Date / Month / Day / Hour / Minute / Second() 함수, Monthname() / Dayname() 함수, DayofWeek / DayofMonth / DayofYear 함수, Count() 함수, Min / Max / Sum / Avg 함수, Group by 절 (추가 그룹 나누기) , Date_Format() 함수 (날짜 반환) , If문, 묶어서 하나의 테이블로 생각하기, Having 절)
2. 테이블의 병합 (Join)
(Cartesian Product, Join의 문법)
데이터베이스(Database) 기초
Length 함수
문자열의 길이를 구한다.
아래의 예시1을 보자.
SELECT LENGTH('456');
예시2.
Locate() 함수
구조는 아래와 같다.
B에서 A가 시작되는 위치를 반환한다.
locate('A', 'B', 찾기 시작하는 위치);
아래의 예를 보자.
select locate('abc', 'ababcaabbc'), locate('abc', 'ababcaababcbc', 4);
LEFT / RIGHT 함수
문자열 추출 한다.
LEFT() : 문자열의 왼쪽부터 기술한 갯수만큼의 문자를 반환.
RIGHT() : 문자열의 오른쪽부터 기술한 갯수만큼의 문자를 반환.
아래의 예를 보면서 이해해보자.
select left('MySQL HTML JAVA SPRING', 5),
Right('MySQL HTML JAVA SPRING', 5);
Lower / Upper 함수
문자열을 대소문자로 변경하는 함수
아래의 예를 보자.
select lower('MySQL HTML JAVA SPRING'),
upper('ababcaabbc');
Replace() 함수
문자열을 바꾼다.
구조는 아래와 같다.
select replace('원하는 문자열', '바꿀 문자열', '대입할 문자열');
아래의 예를 보자.
select replace('MySQL', 'My', 'Me');
Trim() 함수
문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자를 제거.
Both : 문자열의 양쪽 끝에 존재하는 특정 문자를 제거
Leading : 문자열의 앞에 존재하는 특정 문자를 제거
Trailing : 문자열의 뒤에 존재하는 특정 문자를 제거
구조는 아래와 같다.
select trim(조건문 '없애고자하는 문자' from '변경시킬 문자열');
아래의 예를 보자.
select trim(' !!!MySQL HTML Java!!! '),
trim(leading '!' from '!!!MySQL HTML Java!!! '),
trim(trailing ' ' from ' !!!MySQL HTML Java!!! ');
Format() 함수
숫자를 세자리마다 쉼표를 사용하는 형식으로 변환시킨다.
구조는 아래와 같다.
select format(원하는 숫자, 표현하고자하는 소숫점 이하 자릿수);
아래의 예를 보자.
select format(123456789.1234567, 4);
수학 관련 함수
Floor() / Ceil() 함수
Floor : 데이터 값과 같거나 작은 수 중에서 가장 큰 정수를 반환.
Ceil : 데이터 값과 같거나 큰 수 중에서 가장 작은 정수를 반환.
아래의 예를 보면서 이해해보자.
select floor(10.88), floor(11.01), floor(-10.88), floor(-11.01);
select ceil(10.88), ceil(11.01), ceil(-10.88), ceil(-11.01);
Round() 함수
반올림을 하는 함수이다.
구조는 아래와 같다.
select round( 숫자, 최종적으로 남기려는 소숫점 자릿수);
아래의 예를 보자.
select round(10.4511355, 5), round(11.58456, 2), round(-10.888, 2), round(-11.01, 1);
Sqrt() / Pow() / Exp() / Log() 함수
Sqrt : 제곱근 중 양수의 값을 반환.
Pow : 거듭제곱을 반환, 첫번째 인수로는 밑을 반환하고 두번째 인수로는 지수를 반환.
Exp : 인수로 지수를 전달받아서, e의 거듭제곱을 계산하여 반환.
Log : 자연로그 값을 계산하여 반환
select sqrt(4), pow(2, 3), exp(2), log(2.7);
SIN / COS / TAN() 함수
삼각함수다.
-- pi는 파이다.
select sin(pi()/2), cos(pi()), tan(pi()/4);
ABS / RAND() 함수
ABS : 절대값을 반환
RAND : 0~0.999... 사이의 실수를 임의로 생성
select ABS(-3), rand()*100, round(rand()*100+1, 0);
날짜와 시간 관련 함수
Now() / Curdate() / Curtime() 함수
현재 시간들을 보여주는 함수이다.
select now(), curdate(), curtime();
Date / Month / Day / Hour / Minute / Second() 함수
각각 이름에 맞는 정보를 반환하고 값도 예상하는 그 값까지만 갖는다.
(예 : Month 는 0~12 / Minute 은 0~59 )
select Date('2018-3-29 12:35:48'),
Month('2018-3-29 12:35:48'),
Day('2018-3-29 12:35:48'),
Hour('2018-3-29 12:35:48'),
Minute('2018-3-29 12:35:48'),
Second('2018-3-29 12:35:48');
Monthname() / Dayname() 함수
Monthname : 월에 해당하는 이름을 반환
Dayname : 요일에 해당하는 이름을 반환
select Monthname('2022-08-30'), Dayname('2022-08-30');
DayofWeek / DayofMonth / DayofYear 함수
DayofWeek : 해당 일이 주에서 몇번째 날인지 반환 (일 = 1 , 토 = 7)
DayofMonth : 해당 일이 월에서 몇번째 날인지 반환 (0 ~ 31)
DayofYear : 해당 일이 연도에서 몇번째 날인지 반환 (1 ~ 366)
select DayofWeek('2022-08-30'), DayofMonth('2022-08-30'), DayofYear('2022-08-30');
그룹 함수
Count() 함수
특정 조건을 만족하는 레코드의 총 건수를 반환.
그냥 사용하면 Null 값을 빼고 반환한다.
아래의 예를 보자.
comm 의 값이 14개인데
Null 값을 무시하고 결과를 내는 것을 알 수 있다.
이런 경우를 조심해야 한다.
아예 *(전체)로 불러오면 Null이 포함 된다.
Min / Max / Sum / Avg 함수
최소 / 최대 / 합 / 평균
이다.
예를 보며 이해해보자.
Select min(sal) from emp;
예제. 직책이 Sales로 시작하는 사원들이 받는 월급의 min max sum avg를 구해라
답.
평균의 경우, null 값이 있으면 해당 개체를 나누는 것에 포함을 안한다.
아래의 예를 보자.
Null을 0으로 치환해서 계산한 값과 값이 다름을 볼 수 있다.
Group by 절
테이블 내에서 보다 작은 그룹으로 나눌 수 있다.
Group by절에 포함되지 않은 Select절의 모든 column은
반드시 group by 절에 명시되어야 한다.
예시를 보면서 이해해보자.
아래처럼 group by 에 적힌 부서 별 평균 월급을 구한 것이다.
그런데 select에 ename을 추가하고 싶다면 아래처럼 쓰면 이상한 결과값이 나온다.
ename을 group by에도 추가를 해야한다라는 말.
반대로 group by 추가되어 있는 것이 select에 추가될 필요는 없다.
예제. 사원 테이블에서 각 부서 별(deptno) / 업무 별(job) 급여의 합을 구하시오.
답.
Date_Format() 함수
-- 날짜를 지정한 형식으로 출력
DATE_FORMAT(날짜 , 형식)
형식의 종류
| 구분기호 | 역할 | 구분기호 | 역할 |
| %Y | 4자리 년도 | %m | 숫자 월 ( 두자리 ) |
| %y | 2자리 년도 | %c | 숫자 월(한자리는 한자리) |
| %M | 긴 월(영문) | %d | 일자 (두자리) |
| %b | 짧은 월(영문) | %e | 일자(한자리는 한자리) |
| %W | 긴 요일 이름(영문) | %I | 시간 (12시간) |
| %a | 짧은 요일 이름(영문) | %H | 시간(24시간) |
| %i | 분 | %r | hh:mm:ss AM,PM |
| %T | hh:mm:SS | %S | 초 |
아래의 예를 보자.
If 문
구조는 아래와 같다.
IF(조건문, 참일 때의 결과값, 거짓일 때의 결과값)
아래의 예를 보자.
각 해당 월에 입사한 사람의 수를 뽑아낸 구문이다.
select
if(date_format(hiredate, '%m') = '01', count(*), 0) '1월',
if(date_format(hiredate, '%m') = '02', count(*), 0) '2월',
if(date_format(hiredate, '%m') = '03', count(*), 0) '3월',
if(date_format(hiredate, '%m') = '04', count(*), 0) '4월',
if(date_format(hiredate, '%m') = '05', count(*), 0) '5월',
if(date_format(hiredate, '%m') = '06', count(*), 0) '6월',
if(date_format(hiredate, '%m') = '07', count(*), 0) '7월',
if(date_format(hiredate, '%m') = '08', count(*), 0) '8월',
if(date_format(hiredate, '%m') = '09', count(*), 0) '9월',
if(date_format(hiredate, '%m') = '10', count(*), 0) '10월',
if(date_format(hiredate, '%m') = '11', count(*), 0) '11월',
if(date_format(hiredate, '%m') = '12', count(*), 0) '12월'
from emp
group by date_format(hiredate, '%m')
order by date_format(hiredate, '%m') ;
한번 뽑아낸 데이터는 아예 한 덩이로 묶기가 가능하다.
아래의 예를 보자.
// 한번 제조한 테이블
select avg(sal) av
from emp
group by deptno;
// 제조한 테이블을 하나의 테이블로 사용하는 구문
select max(a.av)
from (
select avg(sal) av
from emp
group by deptno) a;
결과값은 아래와 같다.
첫 테이블은 각 부서별로 평균 연봉을 뽑아낸 테이블이고
두번째 테이블은 평균 연봉에서 제일 높은 값을 찾는 테이블이다.
이 것을 이용해서 IF 문 첫 예제의 결과를 간략히 만들어보자.
각 월 별로 입사한 사람의 총 합을 구해보자.
select
sum(1월) '1월', sum(2월) '2월', sum(3월) '3월',
sum(4월) '4월', sum(5월) '5월', sum(6월) '6월',
sum(7월) '7월', sum(8월) '8월', sum(9월) '9월',
sum(10월) '10월', sum(11월) '11월', sum(12월) '12월'
from( select
if(date_format(hiredate, '%m') = '01', count(*), 0) '1월',
if(date_format(hiredate, '%m') = '02', count(*), 0) '2월',
if(date_format(hiredate, '%m') = '03', count(*), 0) '3월',
if(date_format(hiredate, '%m') = '04', count(*), 0) '4월',
if(date_format(hiredate, '%m') = '05', count(*), 0) '5월',
if(date_format(hiredate, '%m') = '06', count(*), 0) '6월',
if(date_format(hiredate, '%m') = '07', count(*), 0) '7월',
if(date_format(hiredate, '%m') = '08', count(*), 0) '8월',
if(date_format(hiredate, '%m') = '09', count(*), 0) '9월',
if(date_format(hiredate, '%m') = '10', count(*), 0) '10월',
if(date_format(hiredate, '%m') = '11', count(*), 0) '11월',
if(date_format(hiredate, '%m') = '12', count(*), 0) '12월'
from emp
group by date_format(hiredate, '%m')
order by date_format(hiredate, '%m') ) a;
Having 절
Where 절에 그룹 함수를 사용할 수 없으므로
Having 절을 사용하여 리턴되는 행을 제한한다.
Having 절의 조건과 그룹은 일치해야 한다.
아래의 예를 보면서 이해해보자.
예1.
최대 월급이 2900 이상인 사원이 속한 부서별 최대 월급을 구하라.
select deptno, max(sal)
from emp
group by deptno
having max(sal)>2900;
예2.
직책이 sales로 시작하지 않는 직책들 중에서
월급의 합계가 5000이 넘어가는 직책과 월급의 합계를 구하라.
select job, sum(sal) sumsal
from emp
where job not like 'sales%'
group by job
having sumsal > 5000
order by job ;
테이블의 병합 (Join)
테이블과 테이블의 병합을 Join이라고 한다.
테이블과 테이블을 합치고 싶을 때,
아래처럼 무작정 합치게 되면 각 항목에 대해 모든 데이터를 만들어내게 된다.
emp의 데이터는 14개
dept의 데이터는 4개
두 개의 테이블을 조건없이 무작정 합치면
데이터 간의 만들어 질 수 있는 모든 조합으로 14 * 4 = 56 건이 생기게 된다.
이렇게 Where에 조건을 주지 않은,
Join이 아닌 Join을 Cartesian Product 이라고 한다.
이를 동일 데이터인 deptno 만 같은 데이터끼리만 병합해서 도출을 하고 싶다라고 하면
아래처럼 where 조건을 걸면 딱 14건의 데이터만 나오게 된다.
이를 확인 하기 위해 emp 쪽과 dept 쪽 no를 매칭 시키면 아래와 같다.
이 데이터의 온전한 데이터를 불러오면 아래와 같은 14건이 나오게 된다.
Join의 문법
문법에는 크게 명시적 문법(Explicit Notation)과 묵시적 문법(Implicit Notation)이 있다.
많이 쓰는 건 묵시적 문법이라 앞으로도 웬만하면 이걸 사용할 듯.
외부 조인에서는 제외.
SELECT *
FROM emp
INNER JOIN dept ON emp.deptno = dept.deptno;
묵시적 문법(Implicit Notation)
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno;
'코딩수업 > AWS 클라우드환경 네이티브' 카테고리의 다른 글
| 9/1 데이터베이스(DB) Exists 함수, 내부 조인 / 아우터 조인, 동등 / 비동등 조인, Self Join (0) | 2022.09.01 |
|---|---|
| 8/31 데이터베이스(DB) 연습 문제 (2) | 2022.08.31 |
| 8/29 데이터베이스(DB) MySQL 다운 및 설치, workbench / cmd 설정, 전반적인 기초 (0) | 2022.08.29 |
| (미완) 8/26 자바(Java) 자바 입력의 전체적 개요, 채팅 프로그램 만들기 (0) | 2022.08.26 |
| 8/25 자바(Java) IP Address, URL, InetAddress 클래스, URL 클래스, Socket programming, TCP/UDP (0) | 2022.08.25 |
댓글