MySQL - ANSI SQL 표준과 주요 함수

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

• 데이터베이스 관리 시스템(DBMS)의 호환성, 일관성, 이식성을 보장하기 위해 SQL 표준을 정의한다.
• ANSI SQL은 SQL:1992, SQL:1999, SQL:2011, SQL:2016 등 버전으로 발전하며, 각 버전은 윈도우 함수, JSON 지원 등 새로운 기능을 추가했다.
• 주요 목표: 다양한 DBMS 간 쿼리 호환성 보장, SQL 문법과 기능의 일관된 사용 제공, SQL 코드의 DBMS 간 이식성 향상.

 

2. 주요 ANSI SQL 함수

 

 2-1. 집계 함수

• COUNT(): 특정 조건을 만족하는 행의 수 반환.
• SUM(): 숫자 열의 합 계산.
• AVG(): 숫자 열의 평균 계산.
• MAX() / MIN(): 열의 최대/최소값 반환.

2-2. 문자열 함수

• CONCAT() 문자열 결합 .
• UPPER() / LOWER(): 문자열을 대문자/소문자로 변환.
• TRIM(): 문자열 양쪽의 특정 문자(예: 공백) 제거.
• SUBSTRING(): 문자열의 특정 부분 추출.

2-3. 논리 및 조건 함수

• CASE: 조건에 따라 다른 결과 반환 (SQL의 조건문).
• COALESCE(): 첫 번째 NULL이 아닌 값 반환.

2-4. 날짜 및 시간 함수

• CURRENT_DATE: 현재 날짜 반환.
• CURRENT_TIME: 현재 시간 반환.
• CURRENT_TIMESTAMP: 현재 날짜와 시간 반환.
• EXTRACT(): 날짜/시간에서 특정 부분(년, 월, 일 등) 추출.

2-5. 형 변환 함수

• CAST(): 데이터 타입 변환 (예: 문자열 → 숫자, 날짜 → 문자열).

---

• 테이블 생성과 데이터 삽입

-- ANSI 표준 함수 사용해보기

create database my_emp_db;
use my_emp_db;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2), -- 최대 99999999.99
    hire_date DATE
);

INSERT INTO employees (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 employees;
-- 테이블 복사
create table new_employees
as select * from employees;

select * from new_employees;

 

• ANSI SQL 함수 사용 사용해보기

select * from new_employees;
 -- 1. 집계 함수
 -- 평균 구하기
 select avg(salary) as avg_salary
 from new_employees;
 
 -- 반올림, 소수점 n 자리까지 처리
  select round (avg(salary), 2) as avg_salary
 from new_employees;
 
 -- 행의 수 반환
 select count(*) as d_count
 from new_employees;
 
-- 안되는 쿼리(논리적, 물리적)
-- select count(*) as d_count, name
-- from new_employees;


-- 2. 문자열 함수
-- 2.1 문자열에 문자열을 결합하고 대문자, 소문자로 변환
select concat('mr/ms ', name) as formal_name
from new_employees;

-- 대문자
select concat(upper('mr/ms '), name) as formal_name
from new_employees;
 
 -- 소문자
select concat(lower('mr/ms '), name) as formal_name
from new_employees;

-- 문자열 추출 함수(문자열 잘라내기)
select *, substring(name, 1, 2) as formal_name
from new_employees;

-- 잘라낸 문자열에 cncat() 활용해서 문자 붙이기
select *, concat(substring(name, 1, 2), '*') as formal_name
from new_employees;


-- 3. 논리 및 조건 함수

-- 3.1 case when then else 구문
-- 현재 데이터베이스(my_emp_db) 사용
select *,
	case when employees.gender = 'M' then '남자' else '여자' end as formal_gender
from employees.employees;
-- employees.employees

-- 4. 날짜 및 시간 함수 

-- 4.1 오늘 날짜 반환
select current_date() as today;
select current_date as today; -- () 없이도 가능

-- 4.2 날짜 차이 계산
-- datediff : 두 날짜 간의 일수 차이를 계산하여 반환 하는 함수

-- 날짜 일수 차이 반환
select *,
	datediff(current_date, hire_date) as years_working
from new_employees;

-- datediff 날짜 차이 계산 / 365.0 처리
select *,
	datediff(current_date, hire_date) / 365.0 as years_working
from new_employees;

-- floor 소수점 버림
select *,
	floor(datediff(current_date, hire_date) / 365.0)  as years_working
from new_employees;


-- 5. 형 변환 함수
-- dicimal --> 형 변환 -- 문자열 (varchar는 가변길이라서 안됨)
select *, cast(salary as varchar(20)) as salary_text
from new_employees;

-- 5.1 형 변환 함수 사용
-- dicimal --> 형 변환 -- 문자열(고정길이 char 써야함)
select *, cast(salary as char(20)) as salary_text
from new_employees;

-- 5.2 형 변환 함수 + 문자열 더하기 함수 사용
select *, concat(cast( salary as char), "원") as formal_salary
from new_employees;

-- 5.3 형 변환 함수 + 문자열 더하기 함수 사용
select *, concat("$", cast( salary as char), "원") as formal_salary
from new_employees;