728x90
반응형
<목차>
1) DML - SELECT
1-1) 집합 연산자
1-2) 함수
1-3) 조건식
1-4) CASE 문
1) DML - SELECT
1-1) 집합 연산자
-- ** 교집합
select employee_id, first_name from employees
where employee_id in (145, 147, 158)
intersect
select employee_id, first_name from employees
where first_name like 'A%'
;
-- ** 차집합
select employee_id, first_name from employees
where employee_id in (145, 147, 158)
except
select employee_id, first_name from employees
where first_name like 'A%'
;
-- 위의 집합의 순서 바꿔서 조회
select employee_id, first_name from employees
where first_name like 'A%'
except
select employee_id, first_name from employees
where employee_id in (145, 147, 158)
;
-- 합집합(중복 제거)
select employee_id, first_name from employees
where first_name like 'A%'
union
select employee_id, first_name from employees
where employee_id in (145, 147, 158)
;
-- 합집합(중복 허용)
select employee_id, first_name from employees
where first_name like 'A%'
union all
select employee_id, first_name from employees
where employee_id in (145, 147, 158)
;
-- 컬럼의 개수가 다르면 다른 집합으로 인지되므로 오류 발생
select employee_id, first_name, salary from employees
where first_name like 'A%'
union all
select employee_id, first_name from employees
where employee_id in (145, 147, 158)
;
1-2) 함수
함수(Function)
- 쿼리와 함께 사용하여 결과를 강력하게 처리할 수 있는 기능
- 함수의 종류
1) 단일행 함수 : 각각의 레코드에 기능을 적용시키는 함수
2) 그룹행 함수 : 특정 조건에 맞게 레코드를 묶어서 기능을 적용시키는 함수
1. 문자열을 처리하는 함수
-- 이름을 대, 소문자로 조회
select
lower(first_name), -- lower : 전부 소문자로 바꿔줌
upper(first_name) -- upper : 전부 대문자로 바꿔줌
from employees;
select
lower('ASIAN CUP 2024'),
upper('ASIAN cup 2024');
-- SUBSTR(문자열, 위치) : INDEX가 1부터 시작
select upper(substr('The Soap', 5));
-- 바로 위의 코드와 동일한 출력 결과를 가짐 : "from dual"은 생략 가능하기 때문이다!
select upper(substr('The Soap', 5))
from dual;
-- SUBSTR(문자열, 위치, 개수)
select upper(substr('The Soap', 5, 2));
-- LENGTH() : 길이 반환
select length('The Soap');
-- INSTR() : 위치값 찾기
select instr('The Soap', 'o');
-- INSTR() : 찾지 못할 경우, 0 반환
select instr('DB마스터', '우');
-- Ex
select employee_id, first_name
from employees e
where substr(first_name, -1, 1) = 'n';
-- 이름의 끝에서 2번째 2개가 'en'으로 끝나는 직원의 사원번호와 이름 출력
select employee_id, first_name
from employees
where substr(first_name, -2, 2) = 'en';
-- CONCAT() : 두 개의 문자열을 합치는 함수
select concat('I have a', ' dream');
select
concat(employee_id, concat(' ', first_name)),
concat(employee_id, concat(' : ', first_name))
from employees;
-- REPLACE(문자열, 찾을 문자열, 바꿀 문자열)
select first_name, replace(hire_date, '-', '/')
from employees;
select first_name, phone_number, replace(phone_number, '.', ' ')
from employees;
-- LEFT(문자열, 길이) or RIGHT(문자열, 길이)
select
left('가나다라마바사', 4), -- 왼쪽부터 4개의 문자열을 가져옴!
right('가나다라마바사', 4); -- 오른쪽부터 4개의 문자열을 가져옴!
-- LTRIM(), RTRIM(), TRIM() : 공백 제거
select
ltrim(' * abcd * '),
rtrim(' * abcd * '),
trim(' * abcd * ');
-- LPAD(문자열, 증가될 길이, 채울 문자열), RPAD()
select
lpad('abcde', 10, '0'),
rpad('abcde', 10, '0');
2. 숫자와 관련된 함수
-- ABS() : 숫자의 절대값 구하는 함수
select abs(-15.6), abs(15.6);
-- CEIL() : 올림, 가장 가까운 양의 방향 정수를 반환
select ceil(15.3), ceil(-15.3);
-- FLOOR() : 내림, 가장 가까운 음의 방향 정수를 반환
select floor(15.7), floor(-15.7);
-- ROUND(숫자, 자릿수) : 지정한 위치에서 반올림
select
round(15.195, 1),
round(15.195, 2),
round(15.195, 0),
round(15.195, -1);
-- TRUNCATE(숫자, 자릿수) : 지정한 위치에서 절삭
select
truncate(-15.195, 1),
truncate(-15.195, 2),
truncate(-15.195, 0),
truncate(-15.195, -1);
-- MOD(값1, 값2) : 첫번째 숫자를 두번째 숫자로 나눴을 때 나머지
select
mod(10, 3),
power(3, 2), -- 첫번째 숫자를 두번째 숫자만큼 곱한 값 출력
sign(10), -- 숫자가 음수면 -1, 0이면 0, 양수면 1을 반환
sign(0),
sign(-10)
;
3. 날짜 함수
-- Date(표현식) 주어진 표현식에 해당하는 날짜 정보 반환
select
date('2024-05-16'),
month('2024-05-16'), -- 월 0~12
day('2024-05-16'), -- 일 0~31
hour('2024-05-16 12:34:56'), -- 시간 0~23
minute('2024-05-16 12:34:56'), -- 분 0~59
second('2024-05-16 12:34:56'), -- 초 0~59
last_day('2024-05-01')
;
-- STR_TO_DATE(변환할 문자열, 포맷 문자열)
select
str_to_date('2024-05-16', '%Y-%m-%d');
/*
%a 요일을 세 개의 문자로 표현함. Sun부터 Sat
%b 월의 축약형을 세 개의 문자로 표현함. Jan부터 Dec
%d 날짜를 두 자리 숫자로 표현함. 00부터 31
%H 24시간 형식 시간을 두 자리 숫자로 표현함. 00부터 23
%h 12시간 형식 시간을 두 자리 숫자로 표현함. 01부터 12
%i 분을 두 자리 숫자로 표현함. 00부터 59
%M 월을 완전한 문자열로 표현함. January부터 December
%m 월을 두 자리 숫자로 표현함. 00부터 12
%p 오전과 오후의 대문자를 표현함. AM 또는 PM
%s 초를 두 자리 숫자로 표현함. 00부터 59
%T hh:mm:ss 형식으로 시간을 표현함.(24시간 형식) 23:01:01
%Y 연도를 완전한 네 자리 숫자로 표현함. 1999나 2003
%y 연도를 두 자리 숫자로 표현함. 99나 03
*/
-- date_format(date, format)
select date_format(hire_date, '%Y-%m-%d') from employees;
select
date_format(now(), '%Y-%m-%d %T'),
date_format(now(), '%Y-%m-%d %p %H:%i:%s');
-- 현재 시각을 조회하는 함수
select sysdate(), now();
select now(), sleep(5), now();
select sysdate(), sleep(5), sysdate();
-- 현재 날짜와 시간 반환
select now(), current_time(), current_timestamp();
-- 현재 날짜 반환
select curdate(), current_date();
-- 현재 시각 반환
select curtime(), current_time();
-- 특정 날짜나 시간의 차이 연산
-- DATEDIFF(date1, date2) : 두 날짜 사이의 일수를 숫자로 반환(date1 - date2)
select datediff(curdate(), '2024-01-01');
-- TIMEDIFF(time1, time2) : 두 시간의 차이를 datetime 형태로 반환(time1 - time2)
select timediff('2024-05-16 12:34:56', '2024-05-15 10:34:56');
-- MAKEDATE(연도, 정수)
select makedate(2024, 32);
※ 예제
-- 예제
/*
[문제-1] 입사일부터의 근무일수를 계산하여, 이름(FIRST_NAME),
부서번호(DEPARTMENT_ID), 근무일수(WORKING DAY)를 각각 출력
[문제-2] 80번 부서에 근무하는 직원들의 시간당 임금을 계산하시오.
(이름, 급여, 시간당 임금)
SALARY를 월급이라고 가정 --> 소수점 이하 2자리 절삭하고, 시간당 임금의 내림차순 정렬
주 5일 근무, 하루 8시간 근무 X 4주 = 160시간 한다고 가정
[문제-3] JOB_HISTORY 테이블의 정보를 이용해 102번 직원이 특정부서에서
몇 개월(30일 기준)을 근무했는지 계산하시오.
*/
-- [문제-1] 입사일부터의 근무일수를 계산하여, 이름(FIRST_NAME),
-- 부서번호(DEPARTMENT_ID), 근무일수(WORKING DAY)를 각각 출력
select
first_name, department_id, datediff(sysdate(), hire_date) as 'WORKING DAY'
from employees e;
-- [문제-2] 80번 부서에 근무하는 직원들의 시간당 임금을 계산하시오.
-- (이름, 급여, 시간당 임금)
-- SALARY를 월급이라고 가정 --> 소수점 이하 2자리 절삭하고, 시간당 임금의 내림차순 정렬
-- 주 5일 근무, 하루 8시간 근무 X 4주 = 160시간 한다고 가정
select
first_name as '이름',
salary as '급여',
truncate((salary / 160), 2) as '시간당 임금'
from employees e
where department_id = 80
order by `시간당 임금` desc; -- "order by 3 desc;"로 써도 가능함!
-- [문제-3] JOB_HISTORY 테이블의 정보를 이용해 102번 직원이 특정부서에서
-- 몇 개월(30일 기준)을 근무했는지 계산하시오.
-- select * from job_history jh; --> 해당 테이블의 컬럼 확인용
select
ceil(datediff(end_date, start_date) / 30) as '근무 개월수',
timestampdiff(month, start_date, end_date)
from job_history jh
where employee_id = 102;
4. 데이터 형 변환 함수
- CAST()
- CONVERT()
-- CAST(값 AS 데이터 형식)
-- 문자에서 숫자로 변환
select cast('1234' as unsigned);
-- 숫자에서 문자로 변환
select cast(1234 as char(4));
-- 문자 또는 숫자를 날짜 타입으로 변환
select
cast(20240516 as date),
cast('20240516' as date);
-- CONVERT(값, 데이터 형식)
-- 숫자를 문자열로 변환
select convert(202404180950, char);
-- 숫자를 날짜 타입으로 변환
select convert(20240418, date);
-- signed 타입으로 변환
select convert(now(), signed);
5. NULL 처리 함수
- IFNULL : NULL을 반환할 때, 다른 값으로 출력할 수 있도록 하는 함수
- COALESCE : IFNULL보다 여러 파라미터를 받음
-- IFNULL(컬럼명, NULL일 경우 대체 값)
select
ifnull('홍길동', '변경 안 됨'),
ifnull(null, '이순신');
-- COALESCE(값1, 값2, ...)
-- 이 함수는 값1이 NULL이 아니면 값1을 반환하고, 값1이 NULL이면 값2 반환
-- 만약 값1, 값2 모두 NULL이면, 다음 인수로 넘어가서 NULL이 아닌 값 반환
select
coalesce('abc', null, null),
coalesce(null, 'abc', null),
coalesce(null, null, 'abc');
-- Ex
select employee_id,
first_name,
manager_id,
ifnull(manager_id, '회장')
from employees;
1-3) 조건식
-- IF(조건식, 값1, 값2)
select if(10 > 3, '크다', '작다');
1-4) CASE 문
/*
CASE value
WHEN '비교할 값' THEN '반환값'
WHEN '비교할 값' THEN '반환값'
ELSE 'WHEN 조건에 해당되지 않는 값'
END
*/
select
employee_id,
department_id,
(
case department_id
when 10 then 'Administration'
when 20 then 'Marketing'
when 30 then 'Purchasing'
when 40 then 'Human Resources'
when 50 then 'Shipping'
when 60 then 'IT'
when 70 then 'Public Relations'
when 80 then 'Sales'
when 90 then 'Executive'
when 100 then 'Finance'
when 110 then 'Accounting'
else '없음'
end
) as '부서명'
from employees;
'Database(DB) > MySQL(2024 version)' 카테고리의 다른 글
| MySQL(6) - SELECT 서브쿼리 (0) | 2024.05.21 |
|---|---|
| MySQL(5) - SELECT 조인(내부 조인, 외부 조인) (0) | 2024.05.20 |
| MySQL(4) - SELECT 그룹화(집계 함수, GROUP BY, HAVING) (0) | 2024.05.17 |
| MySQL(2) - SELECT 조회, 중복 제거, 별칭, 정렬, 조건, 연산자 (0) | 2024.05.14 |
| MySQL(1) - DB(Database) 개요, DBMS의 기초(RDBMS, SQL), 예제 풀이 (0) | 2024.05.13 |
