MySQL - 문자열 함수와 집계함수

MySQL 문자열 함수

CONCAT

문자열 통합

CONCAT(column1, column2);
ex) SELECT CONCAT(author_fname, ' ', author_lname) FROM books;

CONCAT_WS(구분자, column1, column2);
ex) SELECT CONCAT_WS('-', author_fname, author_lname) FROM books;

SUBSTING(=SUBSTR)

문자열 자르기

SUBSTRING(column, start-point, length);
ex) SELECT SUBSTRING('Hello World', 1, 7); // Hello W

CONCAT, SUBSTR 혼합하여 사용하기

SELECT CONCAT(SUBSTR(column, start-point, length), column) FROM <table-name>;
ex) SELECT CONCAT(SUBSTR(title,1,10), '...') FROM books;

REPLACE

문자열 바꾸기

REPLACE(string, from_string, to_string);
ex) SELECT REPLACE('Hello World', 'Hello', 'Yoonstar'); // Yoonstar World

REVERSE

문자열 뒤집기

REVERSE(string);
ex) SELECT REVERSE('Hello World'); // dlroW olleH

CHAR_LENGTH, LENGTH

문자열 길이

CHAR_LENGTH(string); 문자열의 길이 반환
ex) SELECT CHAR_LENGTH('안녕하세요'); // 5

LENGTH(string); 문자열의 바이트 반환
ex) SELECT LENGTH('안녕하세요'); // 10(byte)

UPPER(=UCASE), LOWER(=LCASE)

문자열 대문자, 소문자로 변환

UPPER(string);
ex) SELECT UPPER('hello world'); // HELLO WORLD

LOWER(string);
ex) SELECT LOWER('Hello World'); // hello world

INSERT

문자열 삽입

INSERT(string, start_number, length, new-string);
ex) SELECT INSERT('Hello Bobby', 6, 0, ' There'); // Hello There Bobby

LEFT, RIGHT

문자열 왼쪽, 오른쪽에서부터 반환

LEFT(string, length);
ex) SELECT LEFT('Hello World', 3); // Hel

RIGHT(string, length);
ex) SELECT RIGHT('Hello World', 3) // rld

REPEAT

문자열 반복

REPEAT(string, count);
ex) SELECT REPEAT('MySQL', 2); // MySQLMySQL

TRIM

문자열 공백 제거

TRIM(string);
ex) SELECT TRIM('  Hello World  !   '); // Hello World  !

ex) SELECT TRIM(LEADING '.' FROM '...Hello World!..'); // Hello World!..

ex) SELECT TRIM(TRAILING '.' FROM '...Hello World!..'); // ...Hello World!

ex) SELECT TRIM(BOTH '.' FROM '...Hello World!..'); // Hello World!

CAST

데이터 유형 변환

CAST(expr AS type [ARRAY]);
ex) SELECT CAST('9:00:00' AS TIME); // 09:00:00

---

MySQL 집계함수

COUNT

행 세기

SELECT COUNT(column) FROM <table-name>;
ex) SELECT COUNT(author_fname) FROM books; // 19

ex) SELECT COUNT(DISTINCT author_fname) FROM books // 12

ex) SELECT COUNT(title) FROM books WHERE title LIKE '%the%'; // 6

GROUP BY

동일하나 데이터를 요약 or 집계해서 하나의 행으로 만듦

SELECT column FROM <table-name> GROUP BY column;
ex) SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname;

ex) SELECT author_lname, COUNT(*) AS books_written FROM books 
GROUP BY author_lname ORDER BY books_written DESC;

MIN, MAX

최소, 최댓값 반환

SELECT MIN(column) FROM <table-name>;
ex) SELECT MIN(released_year) FROM books; // 1945

SELECT MAX(column) FROM <table-name>;
ex) SELECT MAX(pages) FROM books; // 634

SELECT column FROM <table-name> WHERE column =
(SELECT MIN(column) FROM <table-name>);
ex) SELECT title, released_year FROM books WHERE released_year = 
	(SELECT MIN(released_year) FROM books); // title: Cannery Row, released_year: 1945

ex) SELECT * FROM books WHERE pages =
	(SELECT MAX(pages) FROM books);
// title: The Amazing Adventures of Kavalier & Clay, pages: 634

MIN/MAX with GROUP BY

min/max를 group by와 함께 사용하기

SELECT column MIN(column) FROM <table-name>
GROUP BY column;
ex) SELECT author_fname, author_lname, MIN(released_year) FROM books 
	GROUP BY author_fname, author_lname;

SUM

합계 구하기

SELECT SUM(column) FROM <table-name>;
ex) SELECT SUM(pages) FROM books; // 7257

ex) SELECT author_fname, author_lname, SUM(pages) FROM books 
	GROUP BY author_lname, author_fname;

AVG

평균 구하기

SELECT AVG(column) FROM <table-name>;
ex) SELECT AVG(released_year) FROM books; // 1999.7895

ex) SELECT released_year, AVG(stock_quantity) FROM books 
	GROUP BY released_year;