MySQL Function - 18

목차

MySQL Function

1. ANSI SQL 표준이란?

ANSI(미국국가표준협회, American National Standards Institute) 표준

• 미국에서 광범위한 산업과 기술 분야에 대해 자발적인 표준을 설정하는 기관.
• 특히 컴퓨터와 데이터베이스 분야에서 ANSI는 데이터베이스 관리 시스템(DBMS)의 호환성, 일관성, 이식성을 보장하기 위한 표준 SQL(Structured Query Language)을 정의하고 유지 관리한다.

ANSI SQL 표준의 주요 목표

• 호환성 : 다양한 데이터베이스 시스템 간의 호환성을 보장하여 사용자가 한 시스템에서 다른 시스템으로 쉽게 전환할 수 있도록 한다.
• 일관성 : SQL 쿼리 언어의 문법과 기능에 대해 일관된 표준을 제공함으로써, 개발자와 사용자가 다양한 시스템에서 일관된 경험을 할 수 있도록 한다.
• 이식성 : SQL 코드가 다양한 시스템과 환경에서 이식 가능하도록 함으로써, 소프트웨어와 데이터베이스의 이식성을 향상시킨다.

2. Function

1. 집계 함수

• COUNT(): 특정 조건을 만족하는 행의 수를 반환한다. 모든 SQL 쿼리에서 중요한 정보를 제공한다.
• SUM(): 숫자로 이루어진 열의 합을 계산한다.
• AVG(): 숫자로 이루어진 열의 평균값을 계산한다.
• MAX() / MIN(): 열에서 최대 또는 최소값을 반환한다.

2. 문자열 함수

• CONCAT(): 두 개 이상의 문자열을 하나로 결합한다.
• UPPER() / LOWER(): 문자열을 모두 대문자 또는 소문자로 변환한다.
• TRIM(): 문자열 양쪽의 특정 문자를 제거한다. 예를 들어, 공백 제거에 사용된다.
• SUBSTRING(): 문자열의 특정 부분을 추출한다.

3. 논리 및 조건 함수

• CASE: 여러 조건에 따라 다른 결과를 반환한다. 이는 SQL에서의 조건문(조건 분기)에 해당한다.
• COALESCE(): 제공된 인자 목록 중에서 첫 번째 NULL이 아닌 값을 반환한다. 이는 기본값 설정에 유용하다.

4. 날짜 및 시간 함수

• CURRENT_DATE: 현재 날짜를 반환한다.
• CURRENT_TIME: 현재 시간을 반환한다.
• CURRENT_TIMESTAMP: 현재 날짜와 시간을 반환한다.
• EXTRACT(): 날짜나 시간에서 특정 부분(년, 월, 일, 시, 분, 초)을 추출한다.

5. 형 변환 함수

• CAST(): 하나의 데이터 타입을 다른 데이터 타입으로 변환한다. 예를 들어, 문자열을 숫자로, 또는 날짜를 문자열로 변환할 때 사용된다.

3. 시나리오 코드

-- 샘플 데이터
create table employee(
    id int,
    name varchar(50),
    department varchar(50),
    salary decimal(10, 2),
    hire_date date
);

-- 복습
-- 팁~ 다른 DB에 있는 테이블의 스키마와 데이터를 복사해보자
use mydb2;

create table new_employees
as select * from employees.employees
where emp_no < 10005;

select * from new_employees;

INSERT INTO employee (id, name, department, salary, hire_date) VALUES
(1, '김철수', '인사부', 3000000.00, '2024-03-01'),
(2, '박영희', '개발부', 4000000.00, '2024-06-15'),
(3, '이민준', '기획부', 3500000.00, '2023-01-10'),
(4, '최지아', '마케팅부', 3200000.00, '2024-05-21'),
(5, '한수연', '영업부', 2900000.00, '2021-12-30');

select * from employee;
desc employee;

/*
    DECIMAL란 데이터 타입입니다.
    고정 소수점 수를 저장하는데 사용하는 SQL 데이터 타입입니다.
    이 데이터 타입은 금융 계산과 같이 정확한 소수점 연산이 필수적인
    애플리케이션에서 매우 유용하며, 소수점 이하의 정확도가 중요할 때
    주로 사용합니다.
    형식은 decimal(5,2)
    위 형식은 총 5자리 숫자를 저장할 수 있으며,
    이 중 2자리는 소수부이다.
    즉 -999.99에서 999.99 까지의 범위를 가진다.
*/

 

문제

-- 집계 함수 문제
-- 문제 1 : 모든 직원의 평균 급여를 계산하세요.

-- 단 주의 avg() 평균 합계와 다른 컬럼을 적는다면 관계가 없는
-- 데이터 결과 집합이다.
select avg(salary) from employee;
select avg(salary), name, id from employee;

-- 문제 2 : 각 부서별로 직원 수를 세고, 급여의 합을 계산하세요
-- 부서별, 부서별직원수, 부서별급여의 합계 금액
-- 그룹화 시켜야 된다.
select department, count(*) as '직원수', sum(salary) as '급여'
from employee
group by department;

-- 문제 3 : 모든 직원의 이름을 대문자로 변환하고, 
-- 이름 앞에 'Mr/Ms'를 추가하세요
select concat('Mr/Ms ', upper(name)) as '이름' from employee;

-- 문제 4 : 직원 이름에서 첫 2글자만 추출 하세요
select substring(name, 1, 2) as '이름' from employee;

-- 문제 4-1 : 직원 이름에서 끝 2글자만 추출하세요
select substring(name, 2, 2) as '이름' from employee;
select right(upper(name), 3) as '이름' from employee;

-- 논리 및 조건 함수 문제
-- 문제 5 : 각 직원에 대한 급여가 3백만원 이상이면 
-- 'High', 'Low'라고 표시
select name, salary,
case
    when salary >= 3000000 then 'High' 
    else 'Low' 
end as '평가'
from employee;

-- 날짜 및 시간 함수 문제
-- 문제 6 : 오늘 날짜를 반환하세요.
select current_date as today;

-- 문제 7 : 각 직원이 고용된 날로부터 몇 년이 지났는지 계산하세요
select name, hire_date, (year(current_date) - year(hire_date)) + 1 as '근속년차'
from employee;

-- 문제 8 : 각 직원의 고용 월을 반환하세요
select name, extract(month from hire_date) as hire_month
from employee;

-- 문제 9 : 각 직원의 급여를 문자열 형태로 표시하세요.
select name, cast(salary as char) as salary_text
from employee;

-- 문제 10 : 문자열로 저장된 날짜 '2024-06-07'을 Date 타입으로 변환하세요
select cast('2024-06-07' as date) as date;

MySQL 기본 목차로 돌아가기