수업일지 24일차 (23/10/24) - MySQL

---

 

수업내용 정리 (MySQL)

 

1. 복습문제

-- 급여가 1000~2000 사이가 아닌 사원
select*from emp where !(sal between 1000 and 2000);

-- 직급이 clerk, manager, analyst인 사원 검색
select*from emp where job in('clerk', 'manager', 'analyst');

-- 직급이 clerk, manager, analyst이 아닌 사원 검색
select*from emp where job not in('clerk', 'manager', 'analyst');

-- 급여가 2000이상이고 입사일이 1981-01-01 이후인 사원 검색
select*from emp where sal>=2000 and hiredate>='1981-01-01';

• 직급은 [select distinct job from emp;] 코드를 통해 이름을 복사해서 작성했다.

 

 

2. Like 연산자

• 와일드 문자
• %: 0~n개의 문자를 가진 패턴
• _: 1개의 문자를 가진 패턴 / 글자의 위치와 글자가 같아야 한다.

 

select 'HELLO' like 'hel%';	-- 참
select 'HELLO' like '__LLO';	-- 참
select 'HELLO' like 'HELLO_';	-- 거짓
select 'HELLO' like 'HELL_';	-- 참

• 'HELLO'
• 문자열의 앞에 'hel'이란 문자가 들어있는가: MySQL은 대소문자를 구분하지 않고, 대문자 'HEL'이 첫부분에 들어있기 때문에 반환 값은 1
• 문자열의 세번째부터 마지막 글자까지 'LLO'인가: 위치와 문자가 동일하기 때문에 반환 값은 1
• 문자열의 첫번째부터 다섯번째까지 'HELLO'이고, 마지막에 한 글자가 있는가(총 6글자인식): 'HELLO'는 들어있지만 마지막 문자가 없기 때문에 반환 값은 0
• 문자열의 첫번째부터 네번째까지 'HELL'이고, 마지막 한 글자가 있는가(총 5글자로 인식): 'HELL'이 위치와 문자가 동일하고 마지막 문자가 있기 때문에 반환 값은 1

 

 예제)

-- 사원 이름이 'J'로 시작하는 사원 검색
select*from emp where ename like 'j%';
-- 사원 이름이 'R'로 끝나는 사원 검색
select*from emp where ename like '%r';
-- 사원 이름이 'R'을 포함하는 사원 검색
select*from emp where ename like '%r%';
-- 사원 이름 중 세번째 글자가 'A'인 사원 검색
select*from emp where ename like '__a%';

select*from emp where empno like '_7%';
select*from emp where hiredate like '1981%';
select*from emp where hiredate not like '1981%';

 

 문제) 1981년도에 입사한 사원 중에서 부서가 10번 또는 20번인 사원 검색

select*from emp where hiredate like '1981%' and (deptno=10 or deptno=20);
select*from emp where hiredate like '1981%' and deptno in(10,20);

• 두 코드는 동일하지만 처음에 위에 버전으로 풀었다가 어제 배웠던 in이 생각나서 뒤늦게 추가했다.
• in으로 코드를 짜는 것이 보기에도 좋고 타이핑하기도 편한 것 같다.

 

 

3. 숫자와 문자열 함수

 3-1) round / ceil / floor / truncate ㄱ 

• round: 반올림
• ceil: 올림
• floor: 내림
• truncate(n, m): n을 소수점 m자리까지 선택하고, 반올림은 안 한다.

 

select round(0.5), ceil(0.4), floor(0.6);
select round(3/2) as r_test;
select abs(1), abs(-1),abs(2-10);

• round: 1 / ceil: 1 / floor: 0
• as r_test는 as 뒤 문자로 칼럼명을 변경한 것이다. 작은 따옴표''를 사용하는 것이 원래 작성법이지만 안 사용해도 되는 경우가 있는데, 문자만 있거나 특수기호 언더바_ 하나를 사용하는 것 정도다. (공백은 안 된다.)

 

select truncate(12345.6789,-2);	-- 출력: 12300

• -2를 넣었으므로 소수점이 아닌 양수에서 숫자를 떨궈, 12300이라는 값이 나온다.

 

 3-2) 최댓값, 최솟값 ㄱ 

select greatest(1,2,3);

• 최댓값
• 비교하고싶은 숫자를 greatest 괄호 안에 적으면 최댓값을 구해서 반환해준다.

 

select least(1,2,3,4,5);

• 최솟값
• 비교하고싶은 숫자를 least 괄호 안에 적으면 최솟값을 구해서 반환해준다.

 

 3-3) 제곱, 제곱근 함수 ㄱ 

• pow(), power(): 구분없이 똑같은 함수

 

select pow(2,3), power(2,3), power(16,1/2), sqrt(16);

• 순서대로 결과값이 8, 8, 4, 4가 나온다.
• sqrt는 루트다.

 

 예제) 

-- 급여를 제곱하고 반올림
select sal, round(pow(sal,1/2)), round(sqrt(sal)) from emp;

 

 3-4) 문자열 연결 함수 ㄱ 

• Oracle에서는 ||을 이용하여 문자를 연결할 수 있지만(ex: 'Hello'||2021||'bye'), MySQL에서는 안 된다.
• concat: 괄호 안의 문자(숫자)들을 모두 더한다.
• concat_ws: 괄호의 첫부분에 들어가는 문자를 괄호 안 문자(숫자)들을 더할 때 사이에 껴서 더해진다.

 

select concat('HELLO', ', ', 2021, ', ', 'BYE');
select concat_ws(' - ', 2020, 8, 4, 'AM');
select concat('E-NO: ', empno) empno, concat(ename, '님') ename, concat(sal,'원') sal from emp;

• HELLO, 2021, BYE
• 2020 - 8 - 4 -  AM
• 칼럼에 따라 E-NO: (empno), (ename)님, (sal)원이 출력된다.

 

 문제) 칼럼 이름은 ename_job으로 하고, 출력은 (ename)_(job)으로 출력

select concat(ename," _ ", job) ENAME_JOB from emp;
select concat_ws(' _ ',ename, job) ENAME_JOB from emp;

• concat, concat_ws 두 가지 방법 모두 사용하여 풀었다.
• 칼럼 이름을 붙일 때에는 as (칼럼 명)으로 해야하지만 생략해도 된다. (초반엔 헷갈리니 추천하진 않음)

 

 3-5) 문자열 부분 추출 ㄱ 

• substr: 전체 문자열에서 부분 문자열을 추출한다.
• 자바에서 String.subString()과 동일하게 작동한다.
• (문자, n): n번째부터의 문자 출력
• (문자, n, m): n번째 문자부터 m번째 문자까지 출력

 

select substr('ABCDEFG',3), substr('ABCDEFG',3,2);
select substr('ABCDEFG',-4);

• 순서대로 'CDEFG', 'CD', 'DEFG'가 출력된다.

 

 문제) 입사일이 2월인 사원 검색

-- 입사일이 2월
select hiredate, ename from emp where substr(hiredate,-5,2)=2;

• 조건: (hiredate)의 -5번째 글자부터 -6까지의 글자가 02

 

 문제2) 입사일이 20일인 사원 검색

-- 입사일이 20일	/ like가 더 좋음
select hiredate, ename from emp where substr(hiredate,-2)=20;
select hiredate, ename from emp where hiredate like '%20';

• 해당 상황에서는 like절을 쓰는 것이 편하고 좋으나 substr을 연습하는 겸 subser로 해보라고 하셔서 두 방법 모두 풀었다.
• substr 조건: (hiredate)의 -2번째 글자부터 마지막 글자가 20
• like 조건: 마지막 글자가 20

 

 3-6) left / right ㄱ 

select hiredate,
	left(hiredate, 4) as year,
	substr(hiredate,-5,2) as month,
	right(hiredate,2) as day
from emp;

• left: 왼쪽부터 n번째 문자까지 (예문에서는 4번째)
• right: 오른쪽부터 n번째 문자까지 (예문에서는 2번째)

 

 3-7) length ㄱ 

• length: 바이트(byte)의 길이
• char_lenght / charactor_length: 문자의 길이

 

select length('abcdef'), char_length('abcdef'), character_length('abcdef');
select length('ㄱㄴㄷㄹ'), char_length('ㄱㄴㄷㄹ'), character_length('ㄱㄴㄷㄹ');

• 순서대로 6, 6, 6 출력
• 순서대로 12, 4, 4 출력

 

 문제) 사원 이름(한글포함)이 4~6자인 사원 검색

select ename from emp where char_length(ename)>=4 and char_length(ename)<=6;
select ename from emp where char_length(ename) between 4 and 6;

• 두 코드 모두 동일하게 돌아간다.
• between을 사용하는 편이 간편하고 보기에도 좋다.

 

 3-8) trim ㄱ 

select concat('| ','hello',' |'),
	concat('| ',ltrim('       hello    '),' |'),
	concat('| ',rtrim('       hello    '),' |'),
	concat('| ',trim('       hello    '),' |');

• trim: 양 쪽 공백을 없앤다.
• ltrim: 왼쪽 공백을 없앤다.
• rtrim: 오른쪽 공백을 없앤다.

 

 3-9) lpad / rpad ㄱ 

• lpad(s,n,p): s가 n글자가 될 때까지 p를 왼쪽에 이어붙인다.
• rpad(s,n,p): s가 n글자가 될 때까지 p를 오른쪽에 이어붙인다.

 

select lpad('abc',10,'-'), rpad('abc',5,'*');

• 순서대로 '-------abc', 'abc**'가 출력된다.

 

 3-10) replace ㄱ 

• replace(s, a, b): s 중 a를 b로 치환(변경)
• 대소문자를 구분한다.

 

select replace('hello sql Hello','hello', 'bye');

• 대소문자를 구분하기 때문에 'bye sql Hello'가 출력된다.

 

 

4. 날짜

 4-1) curdate, curtime, now ㄱ 

• current_date / curdate: 현재 날짜 반환
• current_time / curtime: 현재 시간 반환
• current_timestamp / now: 현재 시간과 날짜 반환

 

select curdate(), curtime(), now();

출력 결과

 

 4-2) date, time, datetime ㄱ 

• date: 문자열에 따라 날짜를 생성
• time: 문자열에 따라 시간을 생성

 

select '2021-6-1' = '2021-06-01', date('2021-6-1') = date('2021-06-01');
select '1:2:3' = '01:02:03', time('1:2:3') = time('01:02:03');
select date('2021-6-1 1:2:3') = date('2021-6-1 01:02:05');

• 형변환해준다고 생각하면 편하다.
• 마지막 코드는 날짜로 변환했기 때문에 시간은 무시하고 날짜만 비교한다.

 

 예제) 

-- 1981-01-01부터 1981-12-31 사이에 입사한 사람
select*from emp where hiredate between '1981-1-1' and '1981-12-31';
select*from emp where hiredate between date('1981-1-1') and date('1981-12-31');
select*from emp where hiredate like '1981%';

 

 

• datetime
• year(datetime): 주어진 datetime 값의 년도 반환
• monthname(datetime): 주어진 datetime 값의 월(영문) 반환
• month(datetime): 주어진 datetime 값의 월 반환
• weekday(datetime): 주어진 datetime 값의 요일값(0~7) 반환 / 월요일부터 0
• dayname(datetime): 주어진 datetime 값의 요일명(영문) 반환
• dayofmonth / day(datetime): 주어진 datetime 값의 날짜(일) 반

select hiredate,
	year(hiredate) as year,
	monthname(hiredate) as monthname,
	month(hiredate) as month,
	weekday(hiredate) as weekday,
	dayname(hiredate) as dayname,
	day(hiredate) as day
from emp;

결과값

 

-- 예시) 1981-12-03 --> 1981/12/3 THU
select hiredate, concat(concat_ws('/', year(hiredate), month(hiredate), day(hiredate)),
	'  ', upper(left(dayname(hiredate),3))) as hiredate2 from emp;

• year, month, day를 통해 값을 추출하고, concat_ws를 이용해 사이마다 '/'를 넣었다.
• 요일은 upper를 이용해서 대문자로 만들고 left로 3글자까지만 출력하도록 했다.

결과값

 

 4-3) 시간 ㄱ 

select concat_ws(' : ', hour(now()), minute(now()), second(now())) as time;

• now를 통해 현재 시간의 시, 분, 초를 출력할 수 있다.
• hour: 시간
• munute: 분
• second: 초

 

select adddate(now(), interval 1 day);	-- 날짜 더하기
select subdate(now(), interval 1 day);	-- 날짜 빼기
select hiredate, adddate(hiredate, interval 2 month) from emp;	-- 날짜 더하기
select hiredate, adddate(hiredate, interval -2 month) from emp;	-- 날짜 빼기
select hiredate, subdate(hiredate, interval 2 month) from emp;	-- 날짜 빼기

• addDate / date_add(d, n): d에 n만큼 시간/날짜 더하기
• subDate / date_sub(d, n): d에 n만큼 시간/날짜 빼기
• add를 통해 날짜를 빼는 것도 가능하다.

 

select hiredate, now(), concat(abs(datediff(hiredate, now())), '일 경과') from emp;
select hiredate, now(), concat(datediff(now(), hiredate), '일 경과') from emp;

• 위의 두 코드는 적은 날짜에서 큰 날짜를 빼도 절댓값을 구하는 공식으로 마이너스-가 안 나오게 하는 것과 그냥 큰 날짜를 앞에 두어 마이너스-가 안 나오게 하는 것의 차이일 뿐 동일한 코드다.
• dateDiff(d, n): d에서 n만큼의 일수차
• timeDiff(d, n): d에서 n만큼의 시간차

 

 예제) 

-- 입사일이 1981-12-05 기준 앞뒤로 5일 이내 사원 검색
select hiredate, ename, concat(abs(datediff(hiredate,'1981-12-05')), '일 경과') as '경과일'
		from emp where abs(datediff('1981-12-05', hiredate))<=5;

 

 4-4) 날짜/시간 형식 ㄱ 

select date_format(now(), '%y - %m - %d  %p %h시 %i분');
select date_format(now(), '%y-%m-%d %h:%i:%s');

• 대소문자에 따라 다른 형식이 되기 때문에 대소문자를 구분한다.
• %Y: 년도 4자리
• %y: 년도 2자리
• %M: 월 영문
• %m: 월 숫자
• %D: 일 영문(1st, 2nd ...)
• %d / % e: 일 숫자
• %T: 시간 (hh:mm:ss)
• %r: 시간 (hh:mm:ss AM/PM)
• %H / %k: 시 (24시)
• %h / %l: 시 (12시)
• %i: 분
• %S / %s: 초
• %p: AM/PM

 

-- str_to_date(S,F): s를 f형식으로 해석하여 시간/날짜로 변환
select str_to_date('2021-06-04 07:55:22', '%Y-%m-%d %T') as str_to_date;

 

 

5. 기타 함수

 5-1) if ㄱ 

select if(1>2, '1이 크다.', '2가 크다.');

• if(조건, t, f): 조건이 참이라면 t, 아니라면 f 반환
• 해당 코드는 참이 아니기 때문에 '2가 크다'가 출력된다.

 

 5-2) case ㄱ 

select
case
  when -1 > 0 then '-1은 양수다.'
  when -1 = 0 then '-1은 0이다.'
  else '-1은 음수다.'
end;

• case 안에 when으로 조건을 주고 조건에 충족한다면 then에 반환할 값을 준다. 모든 when 조건에 충족하지 않으면 else의 값을 반환한다.
• 해당 코드는 조건에 해당하는 것이 없기 때문에 else로 빠져서 '-1은 음수다'가 출력된다.

 

 예제) 

select
  sal,
  if (sal > 2000, 'Expensive', 'Cheap') as result1,
  case
    when sal < 1000 then '저급여'
    when sal between 1000 and 2000 then '일반급여'
    else '고급여'
  end as result2
from emp;

결과값

 

 5-3) ifnull, isnull ㄱ 

select ifnull('A', 'B'), ifnull(NULL, 'B');
select sal, comm, isnull(comm), sal*12+ifnull(comm,0) as annual from emp;

• ifNull(a, b): a가 값이 있다면 a 출력, a가 null이면 b 출력
• isNull(칼럼): 칼럼 값이 null이면 1, 아니면 0 출력

결과값_1

결과값_2

 

---

 

전체 피드백

• 또 비공개로 올리고 말았다..

 

---