[SQL] MySQL yalco 강의 정리 (1) SELECT 기능(연산자/함수) 총 정리

안녕하세요 진자이입니다 :) 이번 포스팅에서는 MySQL 강의로, yalco님의 강의를 실습하면서 정리한 내용, 그 중에서도 SELECT 내용에 대한 기초 내용을 전반적으로 기록하려고 합니다. 좋은 강의 및 실습 자료를 오픈해주신 yalco님께 감사드립니다 :) https://www.yalco.kr/lectures/sql/ 실습 코드도 yalco님 사이트에서 제공하는 실습 코드 작성 프로그램 내 제공되는 샘플 데이터베이스로 진행하게 됩니다.

MySQL

어려운 프로그래밍 개념들을 쉽게 설명해주는 유튜브 채널 '얄팍한 코딩사전'. 영상에서 다 알려주지 못한 정보들이나 자주 묻는 질문들의 답변들, 예제 코드들을 얄코에서 확인하세요!

www.yalco.kr

시작 전

SELECT, FROM 등의 구문은 소문자/대문자 모두 허용되지만, 가독성을 위해 대문자를 쓰는 것이 좋다.
구문별로 구분하기 위해 ENTER를 사용해도 되지만, 가독성을 항상 고려하자.

1. SELECT 전반 기능 훑어보기

-- 전반 기능 전체 활용 코드

SELECT
  CustomerID AS '아이디',
  CustomerName AS '고객명',
  City AS '도시',
  Country AS '국가'
FROM Customers
WHERE
  City = 'London' OR Country = 'Mexico'
ORDER BY CustomerName
LIMIT 0, 5;

테이블의 모든 내용 보기

SELECT * FROM Customers
-- 해당 방식으로 주석을 달 수 있음

MySQL에서 Select는 ‘가져온다’ 라고 생각하면 쉬움
주석은 대시(-)를 두 개 작성한 뒤 텍스트를 입력하면 된다.

원하는 column만 골라 보기

-- Customers 테이블에서 CustomerName 컬럼만 가져오기
SELECT CustomerName FROM Customers;

-- Customers 테이블에서 여러 개의 칼럼을 가져오기
SELECT CustomerName, ContactName, Country
FROM Customers;

컬럼이 아닌 값도 선택할 수 있다. → 1, ‘Hello’, NULL 값으로 채워진 테이블을 확인하자
‘Hello’와 같이 문자열 데이터를 입력할 땐 따옴표(’) 안에 넣어주자.

SELECT SELECT
  CustomerName, 1, 'Hello', NULL
FROM Customers;

원하는 조건의 row(행)만 걸러서 보기 - WHERE

-- Orders 테이블에서 EmployeeID가 3인 모든 데이터를 불러오기
SELECT * FROM Orders WHERE EmployeeID = 3;

-- OrderDetails 테이블에서 Quantity가 5 미만인 모든 데이터를 불러오기
SELECT * FROM OrderDetails WHERE Quantity < 5;

원하는 순서로 데이터 가져오기 - ORDER BY

-- ORDER BY는 ASC(오름차순)이 Default고, DESC(내림차순)으로 바꿀 수 있다.

-- ContactName 이라는 컬럼을 기준으로 Customers 데이터를 정렬하자
SELECT * FROM Customers ORDER BY ContactName;

-- ProductID는 오름차순으로, Quantity는 내림차순으로 정렬해보자.
SELECT * FROM OrderDetails ORDER BY ProductID ASC, Quantity DESC;

원하는 만큼만 데이터 가져오기

LIMIT [가져올 개수]
LIMIT [시작 위치], [가져올 개수]

-- 위에서부터 10개 들고오기
SELECT * FROM Customers LIMIT 10;
SELECT * FROM Customers LIMIT 0, 10;

-- 30번째부터 10개 들고오기
SELECT * FROM Customers LIMIT 29, 10;

원하는 별명(ALIAS)으로 데이터 가져오기

-- ALIAS는 한글로 지정하지 않는 것이 좋다!
SELECT
  CustomerId AS ID,
  CustomerName AS NAME,
  Address AS ADDR
FROM Customers;

2. 연산자들

사칙 연산자

+,-,*,/,% → 더하기, 빼기, 곱하기, 나누기, 나머지
MOD : 나머지

SELECT 1 + 2;
SELECT 10 % 3;

-- 2.5를 DIFFERENCE 컬럼에 담기
SELECT 5 - 2.5 AS DIFFERENCE;

-- HELLO는 다른 컬럼으로 만들어진다
SELECT 3 * (2 + 4) / 2, 'Hello';

-- 문자열에 사칙연산을 가하면 0으로 인식하기 때문에 조심하자!
SELECT 'ABC' + 3;
SELECT 'ABC' * 3;

-- 숫자로 구성된 문자열은 숫자로 자동인식 (결과 1+2*3 = 7)
SELECT '1' + '002' * 3;

-- 숫자로 구성된 컬럼들을 합친 필드만 출력됨 -> OrderID와 ProductID를 같이 출력하는 게 좋겠지?
SELECT
  OrderID + ProductID
FROM OrderDetails;

SELECT
  ProductName,
  Price / 2 AS HalfPrice
FROM Products;

참/거짓 연산자

조건을 구할 때 사용하므로 굉장히 중요!
MySQL에서는 TRUE는 1, FALSE는 0으로 자동 저장
반대의 값을 표현하고 싶을 땐 !와 NOT을 활용하자
다른 언어에서 ‘==’는 =(↔ !=,<>), IS(↔ IS NOT)로 표현할 수 있다
AND(=&&)는 양쪽이 모두 TRUE일 때 TRUE를 반환한다
OR(=||)는 한쪽만 TRUE여도 TRUE가 된다.

SELECT TRUE, FALSE;

-- 반대값 나타내기
SELECT !TRUE, NOT 1, !FALSE, NOT FALSE;

-- 등호
SELECT 0 = TRUE, 1 = TRUE, 0 = FALSE, 1 = FALSE;
SELECT TRUE IS TRUE;
SELECT TRUE IS NOT FALSE;

-- AND, OR
SELECT TRUE AND FALSE, TRUE OR FALSE;

-- 두 조건을 모두 충족하는 경우를 보여준다.
-- 하나만 충족하고 싶을 땐 OR를 쓰자
SELECT * FROM Orders
WHERE
  CustomerId = 15 AND EmployeeId = 4;

-- 괄호를 이용해서 복합적으로 표현할 수도 있다.
SELECT * FROM OrderDetails
WHERE
  ProductId = 20
  AND (OrderId = 10514 OR Quantity = 50);

-- 문자열도 <, >를 통해 비교할 수 있다.
SELECT 'A' = 'A', 'A' != 'B', 'A' < 'B', 'A' > 'B';
SELECT 'Apple' > 'Banana' OR 1 < 2 IS TRUE;

-- 기본 사칙연사자는 대소문자 구분을 하지 않는다 (결과 TRUE)
SELECT 'A' = 'a';

-- 테이블의 컬럼 값이 20이하인 것들만 골라보기
SELECT
  ProductName, Price,
  NOT Price > 20 AS CHEAP 
FROM Products;

BETWEEN, IN, NOT IN, LIKE 연산자

(NOT) BETWEEN [MIN] AND [MAX]
- MIN, MAX 값을 포함한다
LIKE %~ : %뒤에 0~N개의 문자를 가진 패턴
LIKE _~ : 개수만큼의 문자를 가진 패턴

SELECT 'banana' BETWEEN 'Apple' AND 'camera';

-- 1, 4 값을 포함한다.
SELECT * FROM OrderDetails
WHERE ProductID BETWEEN 1 AND 4;

-- 대소문자 구분을 하지 않으므로 TRUE 반환
SELECT 'Hello' IN (1, TRUE, 'hello')

-- IN 활용
SELECT * FROM Customers
WHERE City IN ('Torino', 'Paris', 'Portland', 'Madrid')

-- LIKE % 결과 확인
SELECT
  'HELLO' LIKE 'hel%', --1
  'HELLO' LIKE 'H%', --1
  'HELLO' LIKE 'H%O', --1
  'HELLO' LIKE '%O', --1
  'HELLO' LIKE '%HELLO%', --1
  'HELLO' LIKE '%H', --0
  'HELLO' LIKE 'L%' --0

-- LIKE _ 결과 확인
SELECT
  'HELLO' LIKE 'HEL__', --1
  'HELLO' LIKE 'h___O', --1
  'HELLO' LIKE 'HE_LO', --1
  'HELLO' LIKE '_____', --1
  'HELLO' LIKE '_HELLO',  --0
  'HELLO' LIKE 'HEL_', --0
  'HELLO' LIKE 'H_O' --0

-- Employee 테이블에서 Notes에 economics가 포함된 데이터만 출력
SELECT * FROM Employees
WHERE Notes LIKE '%economics%'

-- OrderDetails 테이블에서 OrderID의 형태가 1025_인 데이터만 출력
SELECT * FROM OrderDetails
WHERE OrderID LIKE '1025_'

이 외 연산자는 MySQL Operators 라고 검색하면 쉽게 확인할 수 있다.

https://www.w3schools.com/mysql/mysql_operators.asp 예시

IF / CASE 함수들

IF(조건, T, F) : 조건이 참이라면 T, 거짓이면 F 반환
IFNULL(A,B) ; A가 NULL일 시 B 출력
CASE ~ WHEN ~ THEN ~ ELSE

SELECT IF (1 > 2, '1는 2보다 크다.', '1은 2보다 작다.');

SELECT
  IFNULL('A', 'B'),
  IFNULL(NULL, 'B');

SELECT
CASE
  WHEN -1 > 0 THEN '-1은 양수다.'
  WHEN -1 = 0 THEN '-1은 0이다.'
  ELSE '-1은 음수다.'
END;

3. 숫자와 문자열을 다루는 함수들

숫자 관련 함수들

ROUND : 반올림
CEIL : 올림
FLOOR : 내림
ABS: 절대값
GREATEST : 괄호 내에서 가장 큰 값
LEAST : 괄호 내에서 가장 작은 값
MAX : 최대
MIN : 최소
COUNT: 개수(NULL 값 제외)
SUM : 총합
AVG : 평균 값
POW(A,B), POWER(A,B) : A^B
SQRT : 제곱근
TRUNCATE(N,n) : N을 소숫점 n자리까지 선택(잘라내기)

-- GREATSE, LEAST 활용
SELECT
  OrderDetailID, ProductID, Quantity,
  GREATEST(OrderDetailID, ProductID, Quantity),
  LEAST(OrderDetailID, ProductID, Quantity)
FROM OrderDetails;

-- TRUNCATE 결과 확인
SELECT
  TRUNCATE(1234.5678, 1), -- 1234.5
  TRUNCATE(1234.5678, 2), -- 1234.56
  TRUNCATE(1234.5678, 3), -- 1234.567
  TRUNCATE(1234.5678, -1), -- 1230
  TRUNCATE(1234.5678, -2), -- 1200
  TRUNCATE(1234.5678, -3); -- 1000

-- TRUNCATE 활용
SELECT Price FROM Products
WHERE TRUNCATE(Price, 0) = 12;

더 많은 숫자 함수는 https://dev.mysql.com/doc/refman/8.0/en/numeric-functions.html

문자열 관련 함수들

UCASE, UPPER : 모두 대문자로
LCASE, LOWER : 모두 소문자로
CONCAT : 괄호 안의 내용을 이어붙임
CONCAT_WS(S, …) : 괄호 안의 내용을 S로 이어붙임 → 공백으로 활용할 수 있음
SUBSTR,SUBSTRING : 주어진 값에 따라 문자열을 자름
LEFT : 왼쪽부터 N글자
RIGHT : 오른쪽부터 N글자
LENGTH : 문자열의 바이트 길이
CHAR_LENGTH, CHARACTER_LENGTH : 문자열의 문자 길이 반환
TRIM, LTRM, RTRIM : 양쪽 공백 제거 / 왼쪽 공백 제거 / 오른쪽 공백 제거
LPAD(S,N,P) / RPAD(S,N,P) : S가 N글자가 될 때 까지 P를 왼쪽/오른쪽에 붙임
REPLACE(S,A,B) : S중 A를 B로 변경.
INSTR(S,a) : S중 a의 첫 위치 반환, 없을 시 0 반환.
CAST(A AS T) : A를 T자료형으로 변환
CONVERT(A,T) : A를 T자료형으로 반환

-- CONCAT 활용
SELECT CONCAT('HELLO', ' ', 'THIS IS ', 2021)
SELECT CONCAT('O-ID: ', OrderID) FROM Orders; -- 형식 변경

-- CONCAT_WS 활용
SELECT CONCAT_WS('-', 2021, 8, 15, 'AM')
SELECT
  CONCAT_WS(' ', FirstName, LastName) AS FullName
FROM Employees;

--- SUBSTR 결과 확인
SELECT
  SUBSTR('ABCDEFG', 3), --CDEFG
  SUBSTR('ABCDEFG', 3, 2), --CD
  SUBSTR('ABCDEFG', -4), --DEFG
  SUBSTR('ABCDEFG', -4, 2); --DE

-- LEFT, RIGHT 활용
SELECT
  OrderDate,
  LEFT(OrderDate, 4) AS Year,
  SUBSTR(OrderDate, 6, 2) AS Month,
  RIGHT(OrderDate, 2) AS Day
FROM Orders;

-- 보통 LENGTH대신 CHAR_LENGTH를 쓴
SELECT
  LENGTH('안녕하세요'), -- 15
  CHAR_LENGTH('안녕하세요'), -- 5
  CHARACTER_LENGTH('안녕하세요'); -- 5

-- TRIM 활용
SELECT * FROM Categories
WHERE CategoryName = TRIM(' Beverages ')

-- LPAD, RPAD 활용
SELECT
  LPAD('ABC', 5, '-'), -- '--ABC'
  RPAD('ABC', 5, '-'); -- 'ABC--'

-- REPLACE 활용
SELECT
  REPLACE('맥도날드에서 맥도날드 햄버거를 먹었다.', '맥도날드', '버거킹');

-- 확인
SELECT
  INSTR('ABCDE', 'ABC'), -- 1
  INSTR('ABCDE', 'BCDE'), -- 2
  INSTR('ABCDE', 'C'), -- 3
  INSTR('ABCDE', 'DE'), -- 0
  INSTR('ABCDE', 'F'); -- 0

SELECT
  '01' = '1',
  CAST('01' AS DECIMAL) = CAST('1' AS DECIMAL);

SELECT
  '01' = '1',
  CONVERT('01', DECIMAL) = CONVERT('1', DECIMAL);

4. 시간/날짜 관련 및 기타 함수들

시간/날짜 관련 함수들

CURRENT_DATE(), CURDATE() : 현재 날짜 반환
CURRENT_TIME(), CURTIME() : 현재 시간 반환
CURRENT_TIMESTAMP(), NOW() : 현재 시간 & 날짜 반환
DATE(S) : 문자열에 따라 날짜 생성
TIME(S) : 문자열에 따라 시간 생성

-- DATE 활용
SELECT * FROM Orders
WHERE
  OrderDate BETWEEN DATE('1997-1-1') AND DATE('1997-1-31');

YEAR(DATETIME) : 주어진 값의 년도 반환
MONTHNAME(DATETIME) : 주어진 값의 월(영문) 반환
MONTH(DATETIME) : 주어진 값의 월 반환
WEEKDAY(DATETIME) : 주어진 값의 요일값 반환(월 0 ~ 일 6)
DAYNAME(DATETIME) : 주어진 값의 요일명 반환
DAYOFMONTH, DAY : 주어진 값의 날짜(일) 반

-- CONCAT, CONCAT_WS
SELECT
  OrderDate,
  CONCAT(
    CONCAT_WS(
      '/',
      YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)
    ),
    ' ',
    UPPER(LEFT(DAYNAME(OrderDate), 3))
  )
FROM Orders;

-- 일요일 데이터만 확인
SELECT * FROM Orders
WHERE WEEKDAY(OrderDate) = 0;

HOUR(DATETIME) : 주어진 값의 시 반환
MINUTE(DATETIME) : 주어진 값의 분 반환
SECOND(DATETIME) : 주어진 값의 초 반환
ADDDATE, DATE_ADD : 시간/날짜 더하기
SUBDATE, DATE_SUB : 시간/날짜 빼기

SELECT 
  ADDDATE('2021-06-20', INTERVAL 1 YEAR),
  ADDDATE('2021-06-20', INTERVAL -2 MONTH),
  ADDDATE('2021-06-20', INTERVAL 3 WEEK),
  ADDDATE('2021-06-20', INTERVAL -4 DAY),
  ADDDATE('2021-06-20', INTERVAL -5 MINUTE),
  ADDDATE('2021-06-20 13:01:12', INTERVAL 6 SECOND);

DATE_DIFF : 두 시간/날짜 간 일수 차
TIME_DFF : 두 시간/날짜 간 시간 차
LAST_DAY : 해당 달의 마지막 날짜

-- 앞에 오는 인자 - 뒤에 오는 인자
SELECT
  OrderDate,
  NOW(),
  DATEDIFF(OrderDate, NOW())
FROM Orders;

-- 절대값 활용
SELECT * FROM Orders
WHERE
  ABS(DATEDIFF(OrderDate, '1996-10-10')) < 5;

DATE_FORMAT : 시간/날짜를 지정한 형식으로 반환
STR_TO_DATE(S,F) : S를 F형식으로 해석하여 시간/날짜 생성
- %Y : 년도 4자리
- %y : 년도 2자리
- %M : 월 영문
- %m : 월 숫자
- %D : 일 영문(1st, 2nd, 3rd …)
- %d, %e : 일 숫자(01 ~ 31)
- %T : hh:mm:ss
- %r : hh:mm:ss AM/PM
- %H, %k : 시(0 ~ 23)
- %h, %l : 시(1 ~ 12)
- %i : 분
- %S, %s : 초
- %p : AM/PM

-- 형식 변경
SELECT
  DATE_FORMAT(NOW(), '%M %D, %Y %T'),
  DATE_FORMAT(NOW(), '%y-%m-%d %h:%i:%s %p'),
  DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초');

-- STR_TO_DATE
SELECT
  DATEDIFF(
    STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
    STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
  ),
  TIMEDIFF(
    STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
    STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
  );

5. 조건에 따라 그룹으로 묶기

조건에 따라 집계된 값 가져오기 - GROUP BY

집계함수 쿼리 끝에 WITH ROLLUP을 추가하면 전체 집계값을 추가할 수 있다
단, ORDER BY와 함께 사용할 수 없다.

SELECT Country FROM Customers
GROUP BY Country;

SELECT 
  Country, City,
  CONCAT_WS(', ', City, Country)
FROM Customers
GROUP BY Country, City;

-- WITH ROLLUP
SELECT
  Country, COUNT(*)
FROM Suppliers
GROUP BY Country
WITH ROLLUP;

-- 활용하기
SELECT
  COUNT(*), OrderDate
FROM Orders
GROUP BY OrderDate;

SELECT
  CategoryID,
  MAX(Price) AS MaxPrice, 
  MIN(Price) AS MinPrice,
  TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,
  TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
GROUP BY CategoryID;

그룹화된 데이터 걸러내기 - HAVING

WHERE은 그룹하기 전 데이터, HAVING은 그룹 후 집계에 사용한다!

SELECT
  Country, COUNT(*) AS Count
FROM Suppliers
GROUP BY Country
HAVING Count >= 3;

중복된 값 제거 - DISTINCT

GROUP BY와 달리 집계함수가 사용되지 않음
GROUP BY와 달리에 비해 더 빠르다.

SELECT DISTINCT CategoryID
FROM Products;

-- GROUP BY와 같이 사용할 수도 있다.
SELECT
  Country,
  COUNT(DISTINCT CITY)
FROM Customers
GROUP BY Country;

저작자표시

'DATA' 카테고리의 다른 글

[SQL] PostgreSQL 값 조작 (0)	2023.08.04
[SQL] MySQL yalco 강의 정리 (2) 서브쿼리/JOIN/UNION 총 정리 (0)	2023.07.22
[SQL] 인프런 Do it! SQL 입문 정리 (5) - 주식 분석 (0)	2023.07.19
[SQL] 인프런 Do it! SQL 입문 정리 (4) - 함수 요약 총정리 (0)	2023.07.19
[SQL] 인프런 Do it! SQL 입문 정리 (3) - JOIN & SUBQUERY (0)	2023.07.18

진자이의 DATA RECODRD

[SQL] MySQL yalco 강의 정리 (1) SELECT 기능(연산자/함수) 총 정리

1. SELECT 전반 기능 훑어보기

테이블의 모든 내용 보기

원하는 column만 골라 보기

원하는 조건의 row(행)만 걸러서 보기 - WHERE

원하는 순서로 데이터 가져오기 - ORDER BY

원하는 만큼만 데이터 가져오기

원하는 별명(ALIAS)으로 데이터 가져오기

2. 연산자들

사칙 연산자

참/거짓 연산자

BETWEEN, IN, NOT IN, LIKE 연산자

IF / CASE 함수들

3. 숫자와 문자열을 다루는 함수들

숫자 관련 함수들

문자열 관련 함수들

4. 시간/날짜 관련 및 기타 함수들

시간/날짜 관련 함수들

5. 조건에 따라 그룹으로 묶기

조건에 따라 집계된 값 가져오기 - GROUP BY

그룹화된 데이터 걸러내기 - HAVING

중복된 값 제거 - DISTINCT

'DATA' 카테고리의 다른 글

티스토리툴바

[SQL] MySQL yalco 강의 정리 (1) SELECT 기능(연산자/함수) 총 정리

1. SELECT 전반 기능 훑어보기

테이블의 모든 내용 보기

원하는 column만 골라 보기

원하는 조건의 row(행)만 걸러서 보기 - WHERE

원하는 순서로 데이터 가져오기 - ORDER BY

원하는 만큼만 데이터 가져오기

원하는 별명(ALIAS)으로 데이터 가져오기

2. 연산자들

사칙 연산자

참/거짓 연산자

BETWEEN, IN, NOT IN, LIKE 연산자

IF / CASE 함수들

3. 숫자와 문자열을 다루는 함수들

숫자 관련 함수들

문자열 관련 함수들

4. 시간/날짜 관련 및 기타 함수들

시간/날짜 관련 함수들

5. 조건에 따라 그룹으로 묶기

조건에 따라 집계된 값 가져오기 - GROUP BY

그룹화된 데이터 걸러내기 - HAVING

중복된 값 제거 - DISTINCT

'DATA' 카테고리의 다른 글

관련글

티스토리툴바