MySQL 한번에 끝내기

SELECT

데이터를 조회하기 위한 문법

-- 테이블에 있는 전체 데이터를 가져온다
SELECT * FROM KAU_SW

-- 테이블에서 필요로 하는 칼럼을 가져온다
SELECT NAME, AGE FROM KAU_SW

AS - 이름 변경

-- 원하는 칼럼명으로 바꿔서 출력한다
SELECT NAME AS 이름 FROM KAU_SW

DISTINCT - 중복 제거

-- 테이블에서 중복을 제외한 데이터를 조회한다
SELECT DISTINCT NAME FROM KAU_SW

WHERE

원하는 조건의 데이터를 조회하기 위한 문법

SELECT NAME, AGE
FROM KAU_SW
WHERE AGE > 25

부등호 (=, <, >, <=, >=, !=)

SELECT NAME FROM KAU_SW WHERE AGE = 25
SELECT NAME FROM KAU_SW WHERE AGE < 25
SELECT NAME FROM KAU_SW WHERE AGE > 25
SELECT NAME FROM KAU_SW WHERE AGE <= 25
SELECT NAME FROM KAU_SW WHERE AGE >= 25
SELECT NAME FROM KAU_SW WHERE AGE != 25

관계 연산자 (NOT, AND, OR)

SELECT NAME FROM KAU_SW WHERE AGE NOT IN (20, 21)
SELECT NAME FROM KAU_SW WHERE AGE = 25 AND GRADE = 4
SELECT NAME FROM KAU_SW WHERE AGE = 23 OR GRADE = 4

BETWEEN ... AND

-- 조건 사이에 있는 데이터를 가져온다
SELECT NAME FROM KAU_SW WHERE AGE BETWEEN 20 AND 24

LIKE % - 문자열 매칭

-- 부분적으로 일치하는 데이터를 가져온다
SELECT NAME FROM KAU_SW WHERE NAME LIKE = '김%'
SELECT NAME FROM KAU_SW WHERE NAME LIKE = '김__'

IN - 괄호 안에 값을 포함한 데이터

-- 괄호 안에 값을 가진 데이터를 가져온다
SELECT NAME FROM KAU_SW WHERE AGE IN (20, 21)

Sub Query - 쿼리문 안에 쿼리문

-- ANY
-- 하위 쿼리에서 나온 결괏값에 하나 이상 충족하는 데이터를 가져온다
SELECT *
FROM KAU_SW
WHERE AGE > ANY ( SELECT AGE
				  FROM KAU_SW
                  WHERE GRADE = 3 )
                  
-- SOME
-- 하위 쿼리에서 나온 결괏값에 하나 이상 충족하는 데이터를 가져온다
SELECT *
FROM KAU_SW
WHERE AGE > SOME ( SELECT AGE
				   FROM KAU_SW
                   WHERE GRADE = 3 )
                  
-- ALL
-- 하위 쿼리에서 나온 결괏값을 모두 충족하는 데이터를 가져온다
SELECT *
FROM KAU_SW
WHERE AGE > ALL ( SELECT AGE
				  FROM KAU_SW
                  WHERE GRADE = 3 )

ORDER BY

원하는 조건으로 데이터를 정렬하기 위한 문법

-- ASC
-- 오름차순 정렬
SELECT NAME FROM KAU_SW WHERE AGE = 25 ORDER BY NAME ASC

-- DESC
-- 내림차순 정렬
SELECT NAME FROM KAU_SW WHERE AGE = 25 ORDER BY NAME DESC

LIMIT - 출력 개수 제한

-- 데이터 개수를 제한해서 가져온다
SELECT NAME 
FROM KAU_SW 
ORDER BY AGE DESC
LIMIT 10

GROUP BY

그룹으로 묶어주기 위한 문법

SELECT DEPARTMENT, MAX(AGE)
FROM KAU
GROUP BY DEPARTMENT

집계 함수 (AVG, MIN, MAX, COUNT, COUNT(DISTINCT), STDEV, VARIANCE)

-- AVG 평균
SELECT DEPARTMENT, AVG(AGE)
FROM KAU
GROUP BY DEPARTMENT

-- MIN 최솟값
SELECT DEPARTMENT, MIN(AGE)
FROM KAU
GROUP BY DEPARTMENT

-- MAX 최대값
SELECT DEPARTMENT, MAX(AGE)
FROM KAU
GROUP BY DEPARTMENT

-- COUNT 행의 개수
SELECT DEPARTMENT, COUNT(MAJOR_SUBJECT)
FROM KAU
GROUP BY DEPARTMENT

-- COUNT(DISTINCT) 중복 제외된 행의 개수
SELECT DEPARTMENT, COUNT(DISTINCT MAJOR_SUBJECT)
FROM KAU
GROUP BY DEPARTMENT

-- STDEV 표준편차
SELECT DEPARTMENT, STDEV(AGE)
FROM KAU
GROUP BY DEPARTMENT

-- VARIANCE 분산
SELECT DEPARTMENT, VARIANCE(AGE)
FROM KAU
GROUP BY DEPARTMENT

HAVING - 그룹조건

-- HAVING은 GROUP BY로 그룹화 된 데이터에 조건을 줄 수 있다
-- HAVING에서 조건을 줄 데이터는 SELECT에 반드시 명시되어 있어야 한다
SELECT DEPARTMENT, MAX(AGE)
FROM KAU
GROUP BY DEPARTMENT
HAVING MAX(AGE) > 24

ROLLUP - 중간합계

-- WITH ROLLUP
SELECT DEPARTMENT, SUM(GRADE)
FROM KAU
GROUP BY DEPARTMENT, AGE WITH ROLLUP

-- ROLLUP
SELECT DEPARTMENT, SUM(GRADE)
FROM KAU
GROUP BY ROLLUP(DEPARTMENT, AGE)

JOIN

여러 테이블을 조합하여 하나의 테이블로 표현하는 문법

SELECT * FROM KAU
JOIN KAU_SW ON KAU.STUDUNT_ID = KAU_SW.STUDUNT_ID
JOIN KAU_MECHA ON KAU.STUDUNT_ID = KAU_MECHA.STUDUNT_ID

MySQL 내장함수

LENGTH(str) - 문자열의 길이

-- 전달받은 문자열의 길이를 반환한다
SELECT LENGTH('123456789')

CONCAT(str, str2, ...) - 문자열 결합

-- 전달받은 문자열 중 NULL 있으면 NULL을 반환한다
SELECT CONCAT('My', 'SQL', 'Course')

LOCATE(substr, str) - 문자 위치

-- 문자열 내에서 찾는 문자가 처음으로 나타나는 위치를 반환한다
-- 찾는 문자가 존재하지 않으면 0을 반환한다
-- MySQL은 인덱스가 1부터 시작한다
SELECT LOCATE('s', 'university')

LEFT(str, num), RIGHT(str, num) - 문자열 부분 가져오기

-- 문자열의 왼쪽부터 지정한 개수만큼의 문자를 반환한다
SELECT LEFT('university', 4)

-- 문자열의 오른쪽부터 지정한 개수만큼의 문자를 반환한다
SELECT RIGHT('university', 4)

LOWER(str), UPPER(str) - 문자열을 소문자로 변경

-- 문자열의 문자를 모두 소문자로 변경한다
SELECT LOWER('UNIVERSITY')

-- 문자열의 문자를 모두 대문자로 변경한다
SELECT UPPER('university')

REPLACE(str, substr, replacestr) - 문자열 부분 변경

-- 문자열에서 특정 문자열을 대체 문자열로 교체한다
SELECT REPLACE('MSSQL', 'MS', 'My')

TRIM(str) - 문자열 양쪽의 특정 문자 제거

-- 제거할 문자를 명시하지 않으면, 공백을 제거한다

-- BOTH
-- 문자열의 양쪽에 있는 특정 문자를 제거한다 (기본 설정)
SELECT TRIM(BOTH '#' FROM '##MySQL###')

-- LEADING
-- 문자열의 앞에 있는 특정 문자를 제거한다
SELECT TRIM(LEADING '#' FROM '##MySQL###')

-- TRAILING
-- 문자열의 뒤에 있는 특정 문자를 제거한다
SELECT TRIM(TRAILING '#' FROM '##MySQL###')

FORMAT(num, point) - 숫자 타입의 데이터에 쉼표 표시

-- 숫자 타입의 데이터를 세 자리마다 쉼표(,)를 사용하는 형식으로 변환한다
SELECT FORMAT(123456789.1234, 2)

FLOOR(X), CEIL(X), ROUND(X) - 소수점

-- 내림
SELECT FLOOR(10.95)

-- 올림
SELECT CEIL(10.95)

-- 반올림
SELECT ROUND(10.95)

POW(X1, X2), SQRT(X), EXP(X), LOG(X) - 제곱

-- 제곱
SELECT POW(2, 3)

-- 제곱근
SELECT SQRT(4)

-- 자연로그 제곱
SELECT EXP(3)

-- 자연로그
SELECT LOG(3)

SIN(X), COS(X), TAN(X) - 삼각함수

-- 사인 값
SELECT SIN(PI()/2)

-- 코사인 값
SELECT COS(PI())

-- 탄젠트 값
SELECT TAN(PI()/4)

ABS(X) - 절댓값

-- 절댓 값
SELECT ABS(-2)

RAND() - 랜덤값

-- 0.0보다 크거나 같고 1.0보다 작은 하나의 실수를 무작위로 생성한다
SELECT ROUND(RAND() * 100, 0)

NOW(), CURDATE(), CURTIME() - 현재 날짜와 시간

-- 현재 날짜와 시간을 반환한다
SELECT NOW()

-- 현재 날짜를 반환한다
SELECT CURDATE()

-- 현재 시간을 반환한다
SELECT CURTIME()

DATE(X), MONTH(X), DAY(X), HOUR(X), MINUTE(X), SECOND(X) - 날짜와 시간

-- 전달받은 값에 날짜 정보를 반환한다
SELECT DATE(NOW())

-- 전달받은 값에 월 정보를 반환한다
SELECT MONTH(NOW())

-- 전달받은 값에 일 정보를 반환한다
SELECT DAY(NOW())

-- 전달받은 값에 시간 정보를 반환한다
SELECT HOUR(NOW())

-- 전달받은 값에 분 정보를 반환한다
SELECT MINUTE(NOW())

-- 전달받은 값에 초 정보를 반환한다
SELECT SECOND(NOW())

MONTHNAME(X), DAYNAME(X) - 날짜와 시간 이름

-- 월에 해당하는 이름을 반환한다
SELECT MONTHNAME(NOW())

-- 요일에 해당하는 이름를 반환한다
SELECT DAYNAME(NOW())

DAYOFWEEK(X), DAYOFMONTH(X), DAYOFYEAR(X) - 몇번째 날

-- 주에서 몇번째 날인지 반환한다
-- 일요일 = 1, 토요일 = 7
SELECT DAYOFWEEK(NOW())

-- 월에서 몇번째 날인지 반환한다
-- 1부터 31사이의 값
SELECT DAYOFMONTH(NOW())

-- 년도에서 몇번째 날인지 반환한다
-- 1부터 366사이의 값
SELECT DAYOFYEAR(NOW())

DATE_FORMAT(X, format) - 날짜 형식

-- 전달받은 형식에 맞춰 날짜와 시간 정보를 문자열로 반환한다
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T')