Basic MySQL

MySQL

기본 MySQL 강좌 

시작 날짜 : 2021.12.13

목표 종료 날짜 : 20201.12.13

 

Section 1. SELECT 기초 - 원하는 정보 가져오기

1-1 . SELECT

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

 

1-2. 연산자

1. 사칙연산

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

SELECT '1' + '002' * 3;

-- 숫자로 구성된 문자열은 숫자로 자동인식

2. 불리언

IS	양쪽이 모두 TRUE 또는 FALSE
IS NOT	한쪽은 TRUE, 한쪽은 FALSE

SELECT TRUE IS TRUE;

SELECT TRUE IS NOT FALSE;

AND, &&	양쪽이 모두 TRUE일 때만 TRUE
OR, ||	한쪽은 TRUE면 TRUE

SELECT * FROM OrderDetails
WHERE
  ProductId = 20
  AND (OrderId = 10514 OR Quantity = 50);

 

=	양쪽 값이 같음
!=, <>	양쪽 값이 다름
>, <	(왼쪽, 오른쪽) 값이 더 큼
>=, <=	(왼쪽, 오른쪽) 값이 같거나 더 큼

SELECT 1 = 1, !(1 <> 1), NOT (1 < 2), 1 > 0 IS NOT FALSE;

 

cf. 컬럼이 아닌 값으로 선택하기 => price가 20 초과인 row의 경우 EXPENSIVE 컬럼이 1, 그 외에는 0

SELECT
  ProductName, Price,
  Price > 20 AS EXPENSIVE 
FROM Products;

BETWEEN {MIN} AND {MAX}	두 값 사이에 있음
NOT BETWEEN {MIN} AND {MAX}	두 값 사이가 아닌 곳에 있음

SELECT 5 BETWEEN 1 AND 10;

IN (...)	괄호 안의 값들 가운데 있음
NOT IN (...)	괄호 안의 값들 가운데 없음

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

LIKE '... % ...'	0~N개 문자를 가진 패턴
LIKE '... _ ...'	_ 갯수만큼의 문자를 가진 패턴

SELECT * FROM Employees
WHERE Notes LIKE '%economics%'

SELECT * FROM OrderDetails
WHERE OrderID LIKE '1025_'

 

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

ROUND	반올림
CEIL	올림
FLOOR	내림

SELECT 
  ROUND(0.5),
  CEIL(0.4),
  FLOOR(0.6);

ABS

절대값

SELECT * FROM OrderDetails
WHERE ABS(Quantity - 10) < 5;

GREATEST	(괄호 안에서) 가장 큰 값
LEAST	(괄호 안에서) 가장 작은 값

SELECT
  OrderDetailID, ProductID, Quantity,
  GREATEST(OrderDetailID, ProductID, Quantity),
  LEAST(OrderDetailID, ProductID, Quantity)
FROM OrderDetails;

MAX	가장 큰 값
MIN	가장 작은 값
COUNT	갯수 (NULL값 제외)
SUM	총합
AVG	평균 값

SELECT
  MAX(Quantity),
  MIN(Quantity),
  COUNT(Quantity),
  SUM(Quantity),
  AVG(Quantity)
FROM OrderDetails
WHERE OrderDetailID BETWEEN 20 AND 30;

POW(A, B), POWER(A, B)	A를 B만큼 제곱
SQRT	제곱근

SELECT
  POW(2, 3),
  POWER(5, 2),
  SQRT(16);

 

TRUNCATE(N, n)

N을 소숫점 n자리까지 선택

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

UCASE, UPPER	모두 대문자로
LCASE, LOWER	모두 소문자로

SELECT
  UCASE(CustomerName),
  LCASE(ContactName)
FROM Customers;

CONCAT(...)	괄호 안의 내용 이어붙임
CONCAT_WS(S, ...)	괄호 안의 내용 S로 이어붙임

SELECT CONCAT_WS('-', 2021, 8, 15, 'AM')

SELECT CONCAT('O-ID: ', OrderID) FROM Orders;

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;

LENGTH	문자열의 바이트 길이
CHAR_LENGTH, CHARACTER_LEGNTH	문자열의 문자 길이

SELECT
  LENGTH('ABCDE'),
  CHAR_LENGTH('ABCDE'),
  CHARACTER_LENGTH('ABCDE');

TRIM	양쪽 공백 제거
LTRIM	왼쪽 공백 제거
RTRIM	오른쪽 공백 제거

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

LPAD(S, N, P)	S가 N글자가 될 때까지 P를 이어붙임
RPAD(S, N, P)	S가 N글자가 될 때까지 P를 이어붙임

SELECT
  LPAD(SupplierID, 5, 0),
  RPAD(Price, 6, 0)
FROM Products;

REPLACE(S, A, B)

S중 A를 B로 변경

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

INSTR(S, s)

S중 s의 첫 위치 반환, 없을 시 0

SELECT
  INSTR('ABCDE', 'ABC'),
  INSTR('ABCDE', 'BCDE'),
  INSTR('ABCDE', 'C'),
  INSTR('ABCDE', 'DE'),
  INSTR('ABCDE', 'F');

CAST(A, T)

A를 T 자료형으로 변환

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

 

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

CURRENT_DATE, CURDATE	현재 날짜 반환
CURRENT_TIME, CURTIME	현재 시간 반환
CURRENT_TIMESTAMP, NOW	현재 시간과 날짜 반환

SELECT CURDATE(), CURTIME(), NOW();

DATE	문자열에 따라 날짜 생성
TIME	문자열에 따라 시간 생성

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

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

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

HOUR	주어진 DATETIME의 시 반환
MINUTE	주어진 DATETIME의 분 반환
SECOND	주어진 DATETIME의 초 반환

SELECT
  HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());

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_DIFF	두 시간/날짜 간 시간차

LAST_DAY

해당 달의 마지막 날짜

DATE_FORMAT

시간/날짜를 지정한 형식으로 반환

%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	시 (~23)
%h, %l	시 (~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초');

  REPLACE(
    DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %초'),
    'AM', '오전'
  ),
  'PM', '오후'
)

IF(조건, T, F)

조건이 참이라면 T, 거짓이면 F 반환

 

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

SELECT
  Price,
  IF (Price > 30, 'Expensive', 'Cheap'),
  CASE
    WHEN Price < 20 THEN '저가'
    WHEN Price BETWEEN 20 AND 30 THEN '일반'
    ELSE '고가'
  END
FROM Products;

IFNULL(A, B)

A가 NULL일 시 B 출력

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

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

1. GROUP BY - 조건에 따라 집계된 값을 가져옵니다.

- 날짜별 주문건수

SELECT
  COUNT(*), OrderDate
FROM Orders
GROUP BY OrderDate;

- 각 아이템별 오더 수 높은 순서대로 출력

SELECT
  ProductID,
  SUM(Quantity) AS QuantitySum
FROM OrderDetails
GROUP BY ProductID
ORDER BY QuantitySum DESC;

- 카테고리별 최소가격, 최대가격, 중간가격, 평균가격 출력

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;

💡 WITH ROLLUP - 전체의 집계값

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

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

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

💡 WHERE는 그룹하기 전 데이터, HAVING은 그룹 후 집계에 사용합니다

SELECT
  COUNT(*) AS Count, OrderDate
FROM Orders
WHERE OrderDate > DATE('1996-12-31')
GROUP BY OrderDate
HAVING Count > 2;

2. DISTINCT - 중복된 값들을 제거합니다.

SELECT
  Country,
  COUNT(DISTINCT CITY)
FROM Customers
GROUP BY Country;

 

Section 2. SELECT 더 깊이 파보기

2-1. 쿼리 안에 서브쿼리

1. 비상관 서브쿼리

SELECT
  CategoryID, CategoryName, Description,
  (SELECT ProductName FROM Products WHERE ProductID = 1)
FROM Categories;

2. 상관 서브쿼리

SELECT
  ProductID, ProductName,
  (
    SELECT CategoryName FROM Categories C
    WHERE C.CategoryID = P.CategoryID
  ) AS CategoryName
FROM Products P;

2-2. JOIN - 여러 테이블 조립하기

1. JOIN(INNER JOIN)

SELECT * FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID;

2. LEFT/RIGHT OUTER JOIN

SELECT
  E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
  E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1
LEFT JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID
ORDER BY E1.EmployeeID;

-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것

3. CROSS JOIN

SELECT
  E1.LastName, E2.FirstName
FROM Employees E1
CROSS JOIN Employees E2
ORDER BY E1.EmployeeID;