SQL
SQL 초급 - DB 개념 및 SELECT 전반 기능 훑기 02 (각종 함수/그룹 묶기)
H-V
2021. 9. 30. 17:10
인프런 '갖고노는 MySQL 데이터베이스 by 얄코' 참조
01 숫자 함수
01) ROUND(반올림)/CEIL(올림)/FLOOR(내림)
SELECT Price, ROUND(Price), CEIL(Price), FLOOR(Price) FROM Products;
02) ABS 절대값
SELECT * FROM OrderDetails WHERE ABS(Quantity - 10) < 5;
03) GREATEST (괄호 안에서) 가장 큰 값 / LEAST (괄호 안에서) 가장 작은 값
SELECT OrderDetailID, ProductID, Quantity,
GREATEST(OrderDetailID, ProductID, Quantity),
LEAST(OrderDetailID, ProductID, Quantity)
FROM OrderDetails;
04) MAX/MIN/COUNT(개수)/SUM/AVG
SELECT * FROM Employees WHERE BirthDate = (SELECT MAX(BirthDate) FROM Employees)
OR BirthDate = (SELECT MIN(BirthDate) FROM Employees) ;
05) 그 외 연산자
- POW(A,B) - A를 B만큼 제곱근
- SQRT - 제곱근
- TRUNCATE - N을 소숫점 N자리까지 선택
02 문자열 함수
01) UCASE/UPPER → 모두 대문자로 ▶ LCASE/LOWER → 모두 소문자로
02) CONCAT(...)/CONCAT_WS(S, ...) → 괄호 안의 내용 이어 붙임 / 괄호 안의 내용 S로 이어 붙임
SELECT CONCAT_WS(' ', FirstName, LastName) AS FullName FROM Employees;
SELECT CONCAT('E-ID: ', EmployeeID) FROM Employees;
03) SUBSTR/SUBSTRING → 주어진 값에 따라 문자열 자름
LEFT → 왼쪽부터 N 글자 , RIGHT → 오른쪽부터 N 글자
SELECT OrderDate, LEFT(OrderDate, 4) AS Year,
SUBSTR(OrderDate, 6, 2) AS Month,
RIGHT(OrderDate, 2) AS Day FROM Orders;
04) 문자열 길이/공백제거/추가/변환/위치/자료형 변환
- LENGTH - 문자열의 바이트 길이 / CHAR_LENGTH - 문자열의 문자 길이
- TRIM - 양쪽 / LTRIM - 왼쪽 / RTRIM - 오른쪽 공백
- LPAD - 왼쪽 / RPAD - 오른쪽 문자 추가
- REPLACE(S,A,B) - S중 A를 B로 변경
- INSTR(S, S) - S중 S의 첫 위치 반환, 없을 시 0
- CAST(A,T) - A를 T자료형으로 변환
03 시간/날짜 관련 및 기타 함수
- CURDATE() - 현재 날짜 반환 / CURTIME() - 현재 시간 반환 / NOW() - 현재 시간과 날짜 반환
- DATE() - 문자열에 따라 날짜 생성 / TIME() - 문자열에 따라 시간 생성
SELECT * FROM Orders WHERE OrderDate BETWEEN DATE('1997-1-1') AND DATE('1997-1-31');
- 각종 YEAR/MONTHNAME/MONTH/WEEKDAY/DAYNAME/DAYOFMONTH/DAY
SELECT OrderDate, CONCAT(CONCAT_WS('/', YEAR(OrderDatE), MONTH(OrderDate), DAY(OrderDate)), ' ', UPPER(LEFT(DAYNAME(OrderDate), 3))) FROM Orders;
- HOUR/MINUTE/SECOND 반환
- ADDDATE/SUBDATE - 시간/날짜 더하기, 빼기
- DATE_DIFF/TIME_DOFF - 두 시간/날짜 간 일수차, 시간차
SELECT * FROM Orders WHERE ABS(DATEDIFF(OrderDate, '1996-10-10')) < 5;
- LAST_DAY - 해당 달의 마지막 날
- DATE_FORMAT - 시간/날짜를 지정한 형식으로 변환
04 기타 함수
- IF(조건, T, F) - 조건이 참이면 T, 거짓이면 F 반환 → 좀더 복잡한 조건은 CASE 활용
SELECT Price, IF(Price > 30, 'Expensive', 'Cheap'),
CASE
WHEN Price < 20 THEN 'Very Cheap'
WHEN Price BETWEEN 20 AND 30 THEN 'Regular'
ELSE 'Very Expensive' END FROM Products;
- IFNULL(A, B) - 만약 A가 NULL이면 B 출력 → 나중의 강의해서 다룰 예정
05 조건에 따라 그룹 묶기
- MAX/MIN/COUNT/SUM/AVG는 집계의 역할이 있음
- 위의 함수들은 GROUP BY와 같이 집계를 낼때 사용 됨 → 즉 집계를 내면서 중복된 값을 제거 할 수 있음
- 예로 한 테이블에 '성별'이라는 항목이 있으면 거기에는 당연히 M/F 기준으로 많은 M,F,M,F....식으로 되어있을 것이다. 이때 그룹핑(GROUP BY)을 쓰면 성별의 많은 개수들이 단 2개 M/F으로 그룹화 시킬 수 있는 것.
- 예제는 아래와 같다
- 이런 테이블에서 커피이름으로 중복값이 있는데 그룹화 시켜서 각 커피의 판매량을 보면 아래와 같아진다.
- GROUP BY란?
데이터베이스에서 유형별로 개수를 가져오고 싶은데, 단순히 COUNT는 전체 개수만 가져올 뿐.
이때 유형별로 개수를 알고싶을때 컬럼에 데이터를 그룹화 할 수 있는데 이것이 'GROUP BY' - GROUP BY에는 두가지 형태가있다
특정 컬럼을 그룹화 하는 'GROUP BY'
특정 컬럼을 그룹화한 결과에 조건을 거는 'HAVING'
WHERE과 HAVING을 헷갈리는 경우가 많은데 WHERE은 그룹화 하기 전, HAVING은 그룹화 이후
SELECT Country, City, CONCAT_WS(', ', City, Country) as 'the City in the Country'
FROM Customers GROUP BY Country, City;
SELECT COUNT(*), City FROM Customers GROUP BY City;
SELECT ProductID, SUM(Quantity) AS QantitySum
FROM OrderDetails GROUP BY ProductID ORDER BY QantitySum DESC;
SELECT CategoryID, MAX(Price) AS MaxPrice, MIN(Price) AS MinPrice
FROM Products GROUP BY CategoryID;
SELECT CONCAT_WS(', ', City, Country) AS Location,
COUNT(*)
FROM Customers GROUP BY Country, City;
- WITH ROLLUP; - 구한값을 다 더해 결과값을 줌
SELECT CONCAT_WS(', ', City, Country) AS Location,
COUNT(*)
FROM Customers GROUP BY Country, City WITH ROLLUP;
05-2 그룹화 한 후 필터를 거쳐 데이터 걸려내기 (HAVING)
SELECT Country, COUNT(*) AS Count
FROM Suppliers GROUP BY Country HAVING Count >= 1;
SELECT EmployeeID, COUNT(*) AS OrderCount
FROM Orders GROUP BY EmployeeID HAVING OrderCount > 100;
- DISTINCT - 중복된 값을 없애주나 GROUP BY처럼 연산이 되는것이 아닌 중복된 값을 다 없애고 하나만 들고 옴
SELECT Country, COUNT(DISTINCT City)
FROM Customers GROUP BY Country;
- DISTINCT - 중복된 값들 중 중복값은 다 지우고 한개의 값만 띄움 (즉 1이 10개면 9개를 지우고 1개만 있다고 보여줌)
SELECT Country, COUNT(DISTINCT City)
FROM Customers GROUP BY Country;
