1. 수학 함수
1) ABS(): 절댓값 반환
SELECT ABS(-8); -- 결과는 8입니다.
2) ROUND(): 반올림된 값 반환
SELECT ROUND(3.7); -- 결과는 4입니다.
SELECT ROUND(3.14159, 2); -- 결과는 3.14입니다. 소수점 둘째 자리까지 반올림.
SELECT ROUND(7.49); -- 결과는 7입니다. 7.49를 반올림하여 7로 변환.
3) CEIL(): 올림된 값 반환
SELECT CEIL(3.2); -- 결과는 4입니다.
SELECT CEIL(-2.8); -- 결과는 -2입니다.
SELECT CEIL(9.12345); -- 결과는 10입니다. 가장 작은 정수로 올림됩니다.
4) FLOOR(): 내림된 값 반환
SELECT FLOOR(3.8); -- 결과는 3입니다.
SELECT FLOOR(-2.5); -- 결과는 -3입니다.
SELECT FLOOR(9.87654); -- 결과는 9입니다. 가장 큰 정수로 내림됩니다.
5) SQRT(): 제곱근 반환
SELECT SQRT(16); -- 결과는 4입니다. 16의 제곱근은 4.
SELECT SQRT(2.25); -- 결과는 1.5입니다. 2.25의 제곱근은 1.5.
SELECT SQRT(5); -- 결과는 약 2.23607입니다. 5의 제곱근은 무리수로 나타납니다.
SELECT SQRT(-9); -- 결과는 NULL입니다. 음수의 제곱근은 복소수로 나타날 수 없으므로 NULL 반환.
6) POWER(): 거듭제곱 계산
SELECT POWER(2, 3); -- 결과는 8입니다. 2의 3제곱은 8.
SELECT POWER(25, 0.5); -- 결과는 5입니다. 25의 제곱근은 5.
SELECT POWER(-3, 4); -- 결과는 81입니다. -3의 4제곱은 81.
SELECT POWER(0, 10); -- 결과는 0입니다. 0의 어떤 양수 거듭제곱도 0입니다.
SELECT POWER(27, 1/3); -- 결과는 3입니다. 27의 1/3 거듭제곱은 3.
7) MOD(): 나머지 계산
SELECT MOD(10, 3); -- 결과는 1입니다. 10을 3으로 나눈 나머지는 1.
SELECT MOD(-10, 3); -- 결과는 -1입니다. -10을 3으로 나눈 나머지는 -1.
SELECT MOD(7, 0); -- 결과는 NULL입니다. 0으로 나누면 나머지를 정의할 수 없으므로 NULL 반환.
2. 문자열 함수
1) CONCAT(): 문자열 연결
SELECT CONCAT('Hello', ' ', 'World'); -- 결과는 'Hello World'입니다.
SET @name = 'John';
SELECT CONCAT('My name is ', @name); -- 결과는 'My name is John'입니다.
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
SELECT CONCAT('Today is ', DATE_FORMAT(NOW(), '%Y-%m-%d')); -- 오늘의 날짜를 문자열과 결합
SELECT CONCAT('Age: ', age) FROM people; -- age가 NULL일 경우, 'Age: ' 문자열과 결합
2) LENGTH(): 문자열 길이 반환
SELECT LENGTH('Hello, World'); -- 결과는 12입니다. 공백 및 구두점을 포함한 문자열의 길이.
SET @message = 'This is a sample message.';
SELECT LENGTH(@message); -- 결과는 26입니다. 변수 문자열의 길이를 계산.
SELECT LENGTH(description) FROM products; -- 제품 설명 열의 각 행의 문자열 길이를 반환.
SELECT LENGTH(NULL); -- 결과는 NULL입니다. NULL 값은 문자열이 아니므로 길이를 계산할 수 없음.
3) UPPER(): 문자열을 대문자로 변환
SELECT UPPER('Hello, World'); -- 결과는 'HELLO, WORLD'입니다.
SET @text = 'This is a sample text.';
SELECT UPPER(@text); -- 결과는 'THIS IS A SAMPLE TEXT.'입니다.
SELECT UPPER(product_name) FROM products; -- 제품 이름 열의 각 행의 문자열을 대문자로 변환.
4) LOWER(): 문자열을 소문자로 변환
SELECT LOWER('Hello, World'); -- 결과는 'hello, world'입니다.
SET @text = 'This Is A Sample Text.';
SELECT LOWER(@text); -- 결과는 'this is a sample text.'입니다.
SELECT LOWER(product_name) FROM products; -- 제품 이름 열의 각 행의 문자열을 소문자로 변환.
5) SUBSTRING(): 부분 문자열 추출
SELECT SUBSTRING('Hello, World', 1, 5); -- 결과는 'Hello'입니다. 1번 문자부터 5번 문자까지 추출.
SET @text = 'This is a sample text.';
SELECT SUBSTRING(@text, 6, 10); -- 결과는 'is a samp'입니다. 6번 문자부터 10번 문자까지 추출.
SELECT SUBSTRING(product_name, 1, 3) FROM products; -- 각 제품 이름의 처음 3글자 추출.
SELECT SUBSTRING('Hello, World', -1); -- 결과는 'd'입니다. 마지막 글자를 추출.
6) REPLACE(): 문자열에서 특정 부분 대체
SELECT REPLACE('Hello, World', 'a', 'x'); -- 결과는 'Hello, World' 문자열에서 'a'를 'x'로 대체.
SET @text = 'I like apples and apples are delicious.';
SELECT REPLACE(@text, 'apple', 'banana'); -- 결과는 'I like bananas and bananas are delicious.'입니다.
SELECT REPLACE(description, 'old', 'new') FROM products; -- 제품 설명 열의 'old'를 'new'로 대체.
SELECT BINARY REPLACE('ABcdABCDabcd', 'A', 'B'); -- 결과는 'BBcdBBCDabcd'입니다. 대소문자 구분하여 'A'를 'B'로 대체.
3. 날짜 및 시간 함수
1) NOW(): 현재 날짜 및 시간 반환
SELECT NOW(); -- 현재 날짜와 시간을 반환합니다.
SET @current_datetime = NOW();
SELECT @current_datetime; -- 현재 날짜와 시간을 변수에 저장하여 출력.
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 현재 날짜와 시간을 'YYYY-MM-DD HH:MM:SS' 형식으로 표시.
INSERT INTO log_table (log_datetime, message) VALUES (NOW(), 'Something happened.'); -- 로그 테이블에 현재 날짜와 시간과 메시지를 저장.
2) DATE(): 날짜 부분 추출
SELECT DATE(NOW()); -- 현재 날짜와 시간에서 날짜 부분만 추출합니다.
SET @datetime = '2023-10-19 14:30:00';
SELECT DATE(@datetime); -- 변수에서 날짜 부분만 추출합니다.
SELECT DATE(order_date) FROM orders; -- 주문 날짜 열의 값에서 날짜 부분만 추출합니다.
SELECT * FROM events WHERE DATE(event_date) = '2023-10-19'; -- 날짜 부분을 비교하여 특정 날짜와 일치하는 이벤트를 선택합니다.
3) TIME(): 시간 부분 추출
4) YEAR(): 연도 추출
5) MONTH(): 월 추출
6) DAY(): 일 추출
4. 조건 함수
1) IF(): 조건에 따라 값 반환
SELECT IF(1 < 2, '참', '거짓'); -- 결과는 '참'입니다. 1은 2보다 작으므로 참인 경우 '참'을 반환.
SET @score = 85;
SELECT IF(@score >= 70, '합격', '불합격'); -- 결과는 '합격'입니다. 점수가 70 이상이므로 합격으로 평가됩니다.
SELECT IF(NULL, '참', '거짓'); -- 결과는 '거짓'입니다. 조건이 NULL이므로 거짓으로 평가됩니다.
SELECT IF(age >= 18 AND has_id = 1, '성인이면서 신분증 있음', '미성년자 또는 신분증 없음') FROM customers;
2) CASE: 조건부로 값 반환
SELECT
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM student_scores;
-- student_scores 테이블에서 학생의 점수에 따라 학점을 할당합니다.
SELECT
CASE
WHEN age < 18 AND has_permission = 0 THEN '미성년자'
WHEN age < 18 AND has_permission = 1 THEN '부모/법정 대리인 허가 필요'
WHEN age >= 18 THEN '성인'
ELSE '정보 없음'
END AS status
FROM customers;
-- customers 테이블에서 고객의 연령 및 권한에 따라 고객 상태를 할당합니다.
UPDATE employees
SET salary =
CASE
WHEN experience >= 5 THEN salary * 1.1
ELSE salary * 1.05
END;
-- employees 테이블에서 경력에 따라 직원의 급여를 업데이트합니다.
5. 집계 함수
1) SUM(): 합계 계산
SELECT SUM(sales) FROM sales_data; -- sales_data 테이블의 sales 열의 값의 합을 반환.(숫자열)
SELECT SUM(quantity * price) FROM order_items; -- 주문 항목의 수량과 가격을 곱하고 합산.(여러열)
SELECT SUM(CASE WHEN revenue > 1000 THEN 1 ELSE 0 END) AS high_revenue_count FROM transactions; -- 수익이 1000 이상인 거래 수를 계산.
SELECT department, SUM(salary) FROM employees GROUP BY department; -- 부서별로 급여의 합을 계산.
2) AVG(): 평균
SELECT AVG(score) FROM student_scores; -- student_scores 테이블의 점수 열의 평균을 반환.
SELECT AVG(price) AS average_price, AVG(quantity) AS average_quantity FROM products; -- 제품 가격과 수량의 평균을 계산.
SELECT AVG(CASE WHEN age >= 18 THEN salary ELSE 0 END) AS average_adult_salary FROM employees; -- 성인 직원의 평균 급여를 계산.
SELECT department, AVG(salary) FROM employees GROUP BY department; -- 부서별로 급여의 평균을 계산.
3) COUNT(): 행의 개수 계산
SELECT COUNT(*) FROM customers; -- customers 테이블의 레코드 수를 반환.
SELECT COUNT(*) FROM orders WHERE status = '배송중'; -- 주문 테이블에서 '배송중' 상태의 주문 수를 계산.
SELECT COUNT(DISTINCT product_category) FROM products; -- 제품 테이블에서 고유한 제품 카테고리의 수를 계산.
SELECT department, COUNT(*) FROM employees GROUP BY department; -- 부서별로 직원 수를 계산.
4) MAX(): 최댓값 찾기
SELECT MAX(salary) FROM employees; -- employees 테이블의 급여 열에서 최댓값을 반환.
SELECT MAX(price) AS max_price, MAX(quantity) AS max_quantity FROM products; -- 제품 가격 및 수량 열에서 최댓값을 찾음.
SELECT MAX(score) FROM student_scores WHERE subject = 'Math'; -- 수학 과목에서 학생 점수의 최댓값을 찾음.
SELECT department, MAX(salary) FROM employees GROUP BY department; -- 부서별로 최댓 급여를 찾음.
5) MIN(): 최솟값 찾기
6. 집합 함수
1) GROUP_CONCAT(): 그룹 내에서 값 결합
SELECT department, GROUP_CONCAT(employee_name) AS employee_list
FROM employees
GROUP BY department;
-- 그룹화된 데이터의 값 연결
-- employees 테이블을 부서별로 그룹화하고 각 부서의 직원 이름을 GROUP_CONCAT() 함수를 사용하여 연결하여 출력
SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ') AS employee_list
FROM employees
GROUP BY department;
-- 구분자를 사용한 값 연결
-- 직원 이름을 쉼표와 공백으로 구분하여 출력
SELECT department, GROUP_CONCAT(DISTINCT employee_name) AS unique_employee_list
FROM employees
GROUP BY department;
-- 중복 값을 제외한 연결
-- 각 부서의 중복되지 않는 직원 이름을 연결하여 출력
2) GROUP BY: 그룹화 작업 수행
SELECT department, year, SUM(revenue) AS total_revenue
FROM sales
GROUP BY department, year;
-- 여러 열 값을 그룹화하여 집계
-- sales 테이블을 부서 및 연도별로 그룹화하고 매출을 합산하여 출력
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE age > 30
GROUP BY department;
-- 그룹화된 데이터에서 조건을 적용
-- 30세 이상인 직원을 필터링하고 부서별로 그룹화하여 직원 수를 출력
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;
-- 그룹화된 데이터에서 정렬
-- 각 부서의 평균 급여를 계산하고 평균 급여에 따라 내림차순으로 정렬하여 출력
7. 비교 함수
1) =, <>, !=, >, <, >=, <=: 비교 연산자로 값 비교
8. 타입 변환 함수
1) CAST(): 데이터 형식 변환
SELECT CAST('42' AS SIGNED); -- 문자열 '42'를 부호 있는 정수로 변환하여 42를 반환.
SELECT CAST(3.14159 AS SIGNED); -- 실수 3.14159를 부호 있는 정수로 변환하여 3을 반환.
SELECT CAST('2023-10-19' AS DATE); -- 문자열 '2023-10-19'를 날짜로 변환하여 2023-10-19을 반환.
SELECT CAST(123 AS CHAR); -- 숫자 123을 문자열 '123'로 변환.
9. NULL 관련 함수
1) IS NULL: 값이 NULL인지 확인
SELECT * FROM customers WHERE email IS NULL; -- 이메일이 NULL인 고객을 선택.
SELECT * FROM products WHERE description IS NOT NULL; -- 설명이 NULL이 아닌 제품을 선택.
SELECT department, COUNT(*) AS null_age_count
FROM employees
WHERE age IS NULL
GROUP BY department;
-- NULL 값인 열을 필터링하여 집계
-- 부서별로 나이가 NULL인 직원 수를 계산하여 출력
2) COALESCE(): NULL이 아닌 첫 번째 값 반환
SELECT COALESCE(NULL, 'Hello', 'World'); -- 결과는 'Hello'입니다. NULL이 아닌 첫 번째 값을 반환.
SET @name = NULL;
SELECT COALESCE(@name, 'John', 'Doe'); -- 결과는 'John'입니다. 변수 @name이 NULL이므로 다음으로 NULL이 아닌 값을 반환.
SELECT COALESCE(address, alternate_address, '주소 없음') FROM customers; -- 고객 주소 또는 대체 주소 중에서 첫 번째 NULL이 아닌 값을 반환.
SELECT customer_name, COALESCE(email, phone, '연락처 없음') AS contact
FROM customers;
-- 여러 열 값 중에서 첫 번째 NULL이 아닌 값을 반환하여 새로운 열 생성
+ 추가
1) COS(): 주어진 각도(라디안 단위)에 대한 코사인 값을 반환하는 함수
2) SIN(): 사인
3) TAN(): 탄젠트
4) LOG(): 주어진 숫자의 자연로그(밑이 e인 로그) 반환
- LOG(x): 주어진 숫자 x에 대한 자연로그 계산
- LOG(x, base): x의 base를 밑으로 하는 로그 계산
5) SIGN(): 주어진 숫자의 부호 반환(양수: 1, 음수: 1, 0: 0)
6) LPAD(string, length, pad_string): 주어진 문자열을 지정된 길이로 채우는 함수, 왼쪽(문자열 시작 부분)에서부터 패딩 문자 사용해 지정된 길이에 도달하도록 문자열 채움
EX) SELECT LPAD('5', 3, '0'); --> 005
7) TRIM( [ [BOTH | LEADING | TRAILING] [remstr FROM] ] str): 문자열의 양쪽 끝에서 공백 문자(또는 다른 지정된 문자)를 제거하는 함수.
주로 데이터 정제 및 문자열 처리 작업에 사용. 문자열에서 불필요한 공백 제거, 원하는 문자 제거할 때 유
- BOTH(기본값), LEADING, TRAILING: 제거할 위치 지정
-- BOTH: 양쪽 끝에서 공백 문자 제거
-- LEADING: 문자열의 시작 부분(왼쪽)에서 공백 문자 제거
-- TRAILING: 문자열의 끝 부분(오른쪽)에서 공백 문자 제거
- remstr: 제거할 문자나 문자열 지정. 이 매개변수를 생략하면 기본적으로 공백 문자 제거
- str: 공백 문자가 제거될 대상 문자열
SELECT TRIM(' Hello '); -- 결과는 'Hello'입니다. 양쪽 공백 제거
SELECT TRIM(LEADING '0' FROM '00123'); -- 결과는 '123'입니다. 왼쪽 '0' 제거
SELECT TRIM(TRAILING 'o' FROM 'cool'); -- 결과는 'coo'입니다. 오른쪽 'o' 제거
SELECT TRIM(BOTH 'x' FROM 'xxxyzxx'); -- 결과는 'yz'입니다. 양쪽 'x' 제거
8) ASCII(string): ASCII 코드 값 반환
9) ADDDATE(date, INTERVAL value unit): 날짜에 특정 기간을 더하고 새로운 날짜를 반환하는 함수
- date: 기준이 되는 날짜
- value: 더할 값 또는 기간
- unit: value의 단위 나타내는 문자열, 주로 YEAR, MONTH, DAY, HOUR MINUTE, SECOND
SELECT ADDDATE(NOW(), INTERVAL 7 DAY); -- 현재 날짜에 7일을 더한 날짜를 반환
10) LAST_DAY(date): 주어진 날짜의 월의 마지막 날짜를 반환하는 함
SELECT LAST_DAY('2023-10-19'); -- 결과는 '2023-10-31'입니다.
11) DATE_FORMAT(date, format): 날짜 또는 시간 값을 원하는 형식으로 포맷팅하여 반환하는 함수
- date: 포맷팅하려는 날짜 또는 시간 값
- format: 원하는 날짜 및 시간 형식을 나타내는 문자열
- %Y: 연도(4자리)
- %y: 연도(2자리)
- %m: 월(01부터 12까지)
- %d: 일(01부터 31까지)
- %H: 시간(00부터 23까지, 24시간 형식)
- %h: 시간(01부터 12까지, 12시간 형식)
- %i: 분(00부터 59까지)
- %s: 초(00부터 59까지)
- %p: AM 또는 PM
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
12) STR_TO_DATE(string, format): 문자열을 날짜 또는 시간 값 변화하는 함수
- string: 날짜 또는 시간 나타내는 문자열
- format: string의 형식을 정의하는 문자
SELECT STR_TO_DATE('2023-10-19', '%Y-%m-%d'); -- '2023-10-19' 문자열을 날짜로 변환
13) IFNULL(expr1, expr2): 첫 번째 인수가 NULL인 경우 두 번째 인수를 반환, 첫 번째 인수 NULL이 아닌 경우 첫 번째 인수를 반환하는 함수
- expr1: 첫 번째 표현식 또는 값
- expr2: 두 번째 표현식 또는 값
SELECT IFNULL(age, 30) FROM users;--> 나이가 NULL인 경우 30 반환, NULL이 아닌 경우 실제 나이 반
'DATABASE(MY SQL)' 카테고리의 다른 글
| (MY SQL) JOIN (0) | 2023.10.09 |
|---|---|
| (My SQL) GROUP BY/HAVING (0) | 2023.09.26 |