MySQL Function - 17

ANSI SQL 표준이란?

💡ANSI SQL 표준 ANSI(미국국가표준협회, American National Standards Institute) 표준은 미국에서 광범위한 산업과 기술 분야에 대해 자발적인 표준을 설정하는 기관입니다. 특히 컴퓨터와 데이터베이스 분야에서 ANSI는 데이터베이스 관리 시스템(DBMS)의 호환성, 일관성, 이식성을 보장하기 위한 표준 SQL(Structured Query Language)을 정의하고 유지 관리합니다. ANSI SQL 표준의 주요 목표 호환성: 다양한 데이터베이스 시스템 간의 호환성을 보장하여 사용자가 한 시스템에서 다른 시스템으로 쉽게 전환할 수 있도록 합니다. 일관성: SQL 쿼리 언어의 문법과 기능에 대해 일관된 표준을 제공함으로써, 개발자와 사용자가 다양한 시스템에서 일관된 경험을 할 수 있도록 합니다. 이식성: SQL 코드가 다양한 시스템과 환경에서 이식 가능하도록 함으로써, 소프트웨어와 데이터베이스의 이식성을 향상시킵니다.

 

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(): 하나의 데이터 타입을 다른 데이터 타입으로 변환합니다. 예를 들어, 문자열을 숫자로, 또는 날짜를 문자열로 변환할 때 사용됩니다.

시나리오 1

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

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자리는 소수부 이다. 
    즉 위 데이터 타입은(5,2), -999.99에서 999.99 까지의 범위를 가진다. 

*/

 

---

-- 복습 !!!
-- 팁~ 다른 DB에 있는 테이블에 스키마와 데이터를 복사해보자
use mydb2;
create table new_employees
as select * from employees.employees 
where emp_no < 10005;
select * from new_employees;

 

 

---

시나리오 2

-- 집계 함수 문제 
-- 문제 1 : 모든 직원에 평균 급여를 계산하세요.
select AVG(salary) from employee;

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

문제 1

주의

---

-- 문제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-1 : 직원 이름에서 첫 2글자만 추출하세요
select substring(name, 1, 2) as '성, 이름'
from employee;

-- 문제 4-2 직원 이름 끝에서 2글자 추출
select substring(name, 2, 2) as '이름'
from employee;

-- 문제 4-3 이것의 경우 name이 다 다른 경우 쓴데 지금은 3글자라 다보일 뿐이다.
select right(upper(name), 3) as '끝 3글자'
from employee
group by name;

 

4-1, 4-2, 4-3

---

-- 논리 및 조건 함수 문제 
-- 문제 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;
select '안녕 반가워' as a ;

---

-- 문제 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 : 각 직원의 급여를 문자열 형태로 표시 하시오
-- 출력 자체는 같으나 컴퓨터는 char 형식으로 바라본다.
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;

 

 

 

쿼리 문제 만들기 employees data

-- 2024 06 07 일일 일쿼리
-- 문제 : 직원의 이름, 근무연수, 부서를 출력하시오.
-- 단 , 직원의 이름은 퍼스트네임, 라스트네임을 합쳐쳐서 표에 나타내고 근무연수는 35년 이상 직원의 직원들만 포함시키시오.
select concat(e.first_name, ' ', e.last_name) as '이름', (year(current_date) - year(hire_date)) as '근속연차', dm.dept_name as '부서' 
from employees as e
left join dept_emp as d
on e.emp_no = d.emp_no
left join departments as dm
on d.dept_no = dm.dept_no
where (year(current_date) - year(hire_date) >= 35);