DB - MySQL

 

 

1) MySQL 집계 함수 : SUM, AVG, MIN, MAX, COUNT, COUNT DISTINCT

함수명	함수 표기	설명
SUM	SUM()	합계를 구한다.
AVG	AVG()	평균을 구한다.
MIN	MIN()	최솟값을 구한다.
MAX	MAX()	최댓값을 구한다.
COUNT	COUNT()	행의 갯수를 센다
COUNT DISTINCT	COUNT(DISTINCT)	행의 갯수를 센다. (중복은 1개만 인정)

 

SELECT 
   MAX(o.order_cnt) AS '최대구매량',
   MIN(o.order_cnt) AS '최소구매량',
   SUM(o.order_cnt) AS '총구매량',
   AVG(o.order_cnt) AS '평균구매량1',
   ROUND(AVG(o.order_cnt),1) AS '평균구매량2',
   TRUNCATE(AVG(o.order_cnt),0) AS '평균구매량3'
FROM 
   orderitems o

SELECT
	o.order_no,
	o.order_prod_cd,
	SUM(o.order_cnt),
	AVG(o.order_cnt),
	COUNT(o.order_no), -- null 포함 x
	COUNT(*), 			 -- null 포함 o
	COUNT(1), 			 -- null 포함 o
	COUNT(DISTINCT o.order_prod_cd),	-- 행의 갯수 / 중복은 1개만 인정
	MAX(o.order_cnt),
	MIN(o.order_cnt),
	TRUNCATE(AVG(o.order_cnt),0),
	ROUND(AVG(o.order_cnt),0)
FROM 
	orderitems o;

 

 

2) 데이터 그룹핑 : GROUP BY, HAVING

-- 데이터를 그룹하여 조회 
SELECT
    p.vend_cd,
    count(p.prod_cd) AS '상품등록수'
FROM 
    products p
GROUP BY p.vend_cd;

-- 데이터를 그룹 필터링하여 조회 
SELECT
    p.vend_cd ,
    count(p.prod_cd) AS '상품등록수'
FROM 
    products p
GROUP BY p.vend_cd
HAVING count(p.prod_cd) > 20;

 

 

3) SQL 윈도우 함수 : 행과 행간을 비교, 연산, 정의하기 위한 함수

SELECT
   WINDOW_FUNCTION(ARGUMENTS) OVER (PARTITION BY 컬럼 ORDER BY 컬럼 오름차순|내림차순)
FROM 
   테이블명;

- ARGUMENTS : 윈도우 함수에 따라 0~N개의 인수를 설정

- PATITION BY : 전체 집합을 기준에 의해 소그룹 나눔

- ORDER BY : 컬럼 정렬

WINDOW_FUNCTION(ARGUMENTS) OVER (PARTITION BY 컬럼 ORDER BY 컬럼 오름차순|내림차순)
순위: RANK, DENSE_RANK, ROW_NUMBER

SELECT
	-- 중복 값들에 대해서 동일 순위로 표시하고,
	-- 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력
	RANK() OVER(ORDER BY p.prod_untprc DESC) AS '순위',
    -- 중복 값들에 대해서 동일 순위로 표시하고,
	-- 중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력
	DENSE_RANK() OVER(ORDER BY p.prod_untprc DESC) AS '순위',
    -- 중복 값들에 대해서도 순차적인 순위를 표시하도록 출력
	ROW_NUMBER() OVER(ORDER BY p.prod_untprc DESC) AS '순위',
	p.prod_cd AS '상품코드',
	p.vend_cd AS '판매처',
	p.prod_name AS '상품이름',
	p.prod_untprc AS '상품단가',
	SUM(p.prod_untprc) OVER (PARTITION BY p.vend_cd ORDER BY p.prod_cd DESC),
	COUNT(p.prod_cd) OVER (PARTITION BY p.vend_cd)
FROM
	products p
ORDER BY 순위
LIMIT 10,10;

 

 

 

4) MySQL 내장함수 : 행과 행간을 비교, 연산, 정의하기 위한 함수

- 데이터 변환함수 : CAST, CONVERT

-- CAST(데이터 AS 변환할 데이터 타입)
SELECT CAST('2024-10-19 12:35:29.123' AS DATE) AS 'DATE' ;
SELECT CAST('2024-10-19 12:35:29.123' AS TIME) AS 'TIME' ;
SELECT CAST('2024-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME' ;

-- CONVERT(데이터, 변환할 데이터 타입)
SELECT
   CONVERT (AVG(o.order_cnt), SIGNED INTEGER) AS '평균 주문 개수'
FROM
   orderitems o;
   
   
 -- SIGNED INTEGER : 실수형 -> 정수형 반올림해서 반환 (5.5 -> 6)

 

 

- 문자열 함수 : CONCAT, SUBSTRING, SUBSTRING_INDEX,GROUP_CONCAT

-- CONCAT(데이터, 데이터, 데이터...)
SELECT 
   CONCAT(m.mbr_id, ' : ', m.mbr_name) AS '아이디 : 이름'
FROM 
   members m
ORDER BY m.mbr_id;


-- CONCAT _WS(구분자, 데이터, 데이터...)
SELECT 
   CONCAT_WS(' : ', m.mbr_id, m.mbr_name, m.mbr_email) 
FROM 
   members m;
   
   
SELECT CONCAT('데이터1', 100, CAST('2024-07-30' AS DATE)); -- 결과 : 데이터11002024-07-30
SELECT '데이터1'+'2'; -- 문자열 '데이터' -> 0으로 인식 -> 결과 : 2
SELECT '1데이터'+'2'; -- 1 인식 -> 결과 : 3

SELECT
	CONCAT(m.mbr_id, ':', m.mbr_email, ':', m.mbr_telno) -- 결과 : ksmartid1:ksmartid1@ksmart.or.kr:063-717-1008
FROM
	members m;
    
SELECT
	CONCAT_WS(' : ', m.mbr_id, m.mbr_email, m.mbr_telno)
FROM
	members m; -- 결과 : ksmartid1:ksmartid1@ksmart.or.kr:063-717-1008 위랑 결과 동일
    
 -- SUBSTRING(데이터, 추출할 인덱스), SUBSTRING(데이터, 추출할 인덱스, 추출할 문자 개수)
SELECT 
   SUBSTRING(m.mbr_id, 2),
   SUBSTRING(m.mbr_id, 2,5) 
FROM 
   members m;


-- SUBSTRING_INDEX(데이터, 구분자, 양수|음수)
SELECT 
   CAST(SUBSTRING_INDEX( m.mbr_id, 'ksmartid', -1) AS UNSIGNED)
FROM 
   members m;
   

SELECT SUBSTRING('가나다라', 4); -- 시작점부터 문자열 출력 1 : 가나다라 / 2 : 나다라 / 3 : 다라 / 4 : 라
SELECT SUBSTRING('가나다라', 2,2); -- 결과 : 나다



-- members 테이블 회원아이디를 조회 시 숫자만 추출하시오
-- 회원아이디, 추출한 번호
SELECT m.mbr_id, cast(SUBSTRING(m.mbr_id, 9) AS UNSIGNED) FROM members m;

https://www.mysqltutorial.org/mysql-string-functions/mysql-substring/

 

 

- 문자열 함수 : ASCII, CHAR, TRIM, REPEAT, REPLACE, REVERSE, SPACE

-- ASCII 코드변환
SELECT ASCII('A'), CHAR(65);

-- TRIM 앞뒤 공백제거
SELECT TRIM(' KSMART52 ') AS `result`;

-- REPEAT 데이터 반복 출력
SELECT REPEAT('KSMART', 3) AS `result`;

-- REPLACE 데이터 변경
SELECT REPLACE ('KSMART52기', 'KSMART' , '한국스마트정보교육원') AS `result`;

-- REVERSE 문자데이터 반전
SELECT REVERSE ('KSMART52') AS `result`;

-- SPACE(크기) 크기만큼 공백문자 반환
SELECT CONCAT('KSMART', SPACE(15), '52기') AS `result`;

 

 

5) MySQL 묵시적 형변환

- 묵시적 형변환 주의: 연산시 필히 동일한 데이터 타입으로 변환 후 연산작업

-- 문자와 문자를 더함 (정수로 변환되서 연산됨)
SELECT '100' + '200' ;

-- 문자와 문자를 연결 (문자로 처리)
SELECT CONCAT('100', '200'); 

-- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)
SELECT CONCAT(100, '200');

-- 정수인 2로 변환되어서 비교 
SELECT 1 > '2mega';

-- 정수인 2로 변환되어서 비교 
SELECT 3 > '2MEGA'; 

-- 문자는 0으로 변환됨
SELECT 0 = 'mega2';

SELECT '100길'+'200번지'; -- 결과 : 300
SELECT CONCAT('100','200'); -- 결과 : 100200

 

 

6) MySQL 제어 흐름 함수

- 제어 흐름 함수 : IF, IFNULL, NULLIF, CASE WHEN

-- IF(조건식, 참일경우 반환데이터, 거짓일경우 반환데이터)
SELECT IF(10>20, 'true', 'false');

-- IFNULL(데이터, 데이터가 NULL일 경우 반환되는 데이터)
SELECT IFNULL(NULL, ''), IFNULL(100, '널');

-- NULLIFF(데이터1, 데이터2) 데이터1, 데이터2 비교 연산 후 참: NULL반환, 거짓: 데이터1 반환
SELECT NULLIF(10,10), NULLIF(30,10);

-- CASE 타깃값 WHEN 값 THEN 값과 일치시 반환될 데이터 ELSE 불일치시 반환되는 데이터 
SELECT
   CASE 3
   WHEN 1 THEN '일'
   WHEN 3 THEN '삼'
   WHEN 5 THEN '오'
   ELSE NULL
   END AS 'CASE 구문';

 

 

7) MySQL 내장함수

- 날짜연산 함수 : ADDDATE, SUBDATE, ADDTIME, SUBTIME

-- ADDDATE(날짜, INTERVAL 연산데이터 DAY|MONTH|YEAR) 
-- SUBDATE(날짜, INTERVAL 연산데이터 DAY|MONTH|YEAR)
SELECT 
   ADDDATE('2024-01-18', INTERVAL 31 DAY),
   ADDDATE('2024-01-18', INTERVAL 1 MONTH);
SELECT 
   SUBDATE('2024-01-18', INTERVAL 31 DAY),
   SUBDATE('2024-01-18', INTERVAL 1 MONTH);

SELECT 
   ADDTIME('2024-01-18 23:59:59', '1:1:1'),
   ADDTIME('15:00:00', '2:10:10');
SELECT 
   SUBTIME('2024-01-18 23:59:59', '1:1:1'), 
   SUBTIME('15:00:00', '2:10:10');
   
-- ADDTIME(일시, '시:분:초') 
-- SUBTIME(일시, '시:분:초')

SELECT 
   ADDTIME('2024-01-18 23:59:59', '1:1:1'),
   ADDTIME('15:00:00', '2:10:10');
SELECT 
   SUBTIME('2024-01-18 23:59:59', '1:1:1'), 
   SUBTIME('15:00:00', '2:10:10');

 

- 날짜연산 함수 : YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND, MICROSECOND, DATE, TIME

SELECT 
   YEAR(CURDATE()),
   MONTH(CURDATE()),
   DAYOFMONTH(CURDATE());

SELECT 
   HOUR(CURTIME()),
   MINUTE(CURRENT_TIME()),
   SECOND(CURRENT_TIME()),
   MICROSECOND(CURRENT_TIME());

SELECT DATE(NOW()), TIME(NOW());

-- DATEDIFF( 날짜1, 날짜2) 날짜1 – 날짜2
-- TIMEDIFF( 시간1, 시간2) 시간1 – 시간2
SELECT 
   DATEDIFF('2023-01-06', NOW()),
   TIMEDIFF('17:30:01', CURRENT_TIME());

SELECT 
   DAYOFWEEK(CURDATE()),
   MONTHNAME(CURDATE()),
   DAYOFYEAR(CURDATE());

SELECT LAST_DAY('2024-02-18');

SELECT NOW(), CURDATE(); -- NOW() : 2024-07-19 16:25:43 / CURDATE() : 2024-07-19

 

 

8) MySQL 변수사용

- 변수 선언 키워드 : SET

연결할 때만 사용하는 키워드(연결 끊어지면 데이터 사라짐)

SET @myNumber1 = 1;
SET @myNumber2 = 2.345;
SET @myStr1 = '합계:';

SELECT
    @myNumber1 AS '변수1',
    @myNumber2 AS '변수2';
 
SELECT
    @myStr1, 
    (@myNumber1 + @myNumber2) AS '합계';
 
SELECT
    @myStr1,
    ROUND((@myNumber1 + @myNumber2), 2) AS '합계';
    
    
 -- session 변수 선언 
-- set @변수명 := 값
SET @var1 := 20;

SELECT
	o.order_item,
	o.order_cnt,
	@var1 := @var1 + o.order_cnt -- int result = result + intValue;
FROM
	orderitems o 
	JOIN
	(SELECT @var1 := 0) v;