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

앞으로 이론을 배운 후 w3schools에서 제공하는 샘플 데이터를 통해 실습을 해보겠습니다.

https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_select_all 

MySQL Tryit Editor v1.0

 

* SQL명령어를 적을 때 대소문자, 줄 바꿈은 자유입니다.

 

 

Lesson 01. SELECT 전반적인 기능 

1. 테이블의 모든 내용 보기

    SELECT * FROM 테이블명;

SELECT * FROM Customers;

/*
     SELECT : 가져오겠다 
     * :   테이블의 모든 컬럼을
*/

 

 

주석

-- 한 줄 주석을 적습니다.

/*
    여러 줄 주석을 적습니다.
*/

 

2. 원하는 column(컬럼, 열)만 골라서 보기

SELECT 컬럼명 FROM 테이블명;

-- 컬럼 하나 가져오기
SELECT CustomerName FROM Customers;

-- 컬럼 여러개 가져오기
SELECT CustomerName, ContactName, Country
FROM Customers;

컬럼 하나

컬럼 여러 개

📢 테이블의 컬럼이 아닌 값도 가져올 수 있습니다.

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

 

3. 조건 WHERE 사용해서 원하는 row(행)만 보기

SELECT * FROM Orders
WHERE EmployeeID = 2;

4. ORDER BY 사용해서 특정 컬럼을 기준으로 데이터 정렬하기

구문	기준	비고
ASC	오름차순	기본값
DESC	내림차순

 

SELECT * FROM Customers
ORDER BY City;

두 가지 기준으로 가지고 오고 싶을 경우, 앞쪽에 배치된 것이 우선순위를 가집니다. 

SELECT * FROM OrderDetails
ORDER BY ProductID ASC, Quantity DESC;

 

5. LIMIT 사용해서 원하는 개수만큼 데이터 가져오기

페이징 구현할 때 사용합니다.

LIMIT 가져올 개수
또는
LIMIT  건너뛸 개수, 가져올 개수

-- 두 구문이 가져오는 데이터의 갯수는 같습니다.
SELECT * FROM Customers
LIMIT 5;

SELECT * FROM Customers
LIMIT 0, 5;

-- 31 ~ 35까지 데이터를 가져옵니다.
SELECT * FROM Customers
LIMIT 30, 5;

 

6. AS 사용해서 기존 컬럼명이 아닌 내가 원하는 컬럼명으로 가져오기

SELECT
  CustomerId AS ID,
  CustomerName AS NAME,
  Address AS ADDR
FROM Customers;

한글 문자열로도 가져올 수 있습니다.

SELECT
  CustomerId AS '아이디',
  CustomerName AS '고객명',
  Address AS '주소'
FROM Customers;

 

Lesson 01 배운 내용을 다 넣어서 데이터 가져오기

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

 

 

 

Lesson 02. 각종 연산자들

1. 사칙연산

연산자	의미
+, -, *, /	더하기, 빼기, 곱하기, 나누기
%, MOD	나머지

 

SELECT 1 + 2; 	-- 3
SELECT 1 - 2;   -- -1
SELECT 1 * 2;	-- 2
SELECT 1 / 2;	-- 0.5000

SELECT 10 % 3;	-- 1

기존 컬럼명이 아닌 AS 컬럼명으로 가져오고 싶을 경우 

SELECT 5 - 2.5 AS DIFFERENCE;

쉼표로 두 개 가져올 수 있음

SELECT 3 * (2 + 4) / 2, 'Coding';

 

💡 문자열 사칙연산
    문자열에 사칙연산을 가하면 문자열을 0으로 인식합니다. 
    단, 숫자로 구성된 문자열은 숫자로 자동으로 인식하여 계산합니다.

SELECT 'ABC' + 100;	-- 100
SELECT 'ABC' * 100;	-- 0

-- 숫자로 구성된 문자열은 숫자로 자동인식
SELECT '1' + '002' * 3;	--7

 

테이블 활용 - 세일을 했을 때 한눈에 원가와 비교 가능

SELECT
  ProductName, Price,
  Price / 2 AS HalfPrice
FROM Products;

 

2. [중요] 참 / 거짓 연산자 + WHERE 사용

💡 MySQL에서 TRUE는 1, FALSE는 0으로 저장됩니다. 

 

! 과 NOT

-- !는 반대를 뜻하며 NOT을 사용할 때는 한 칸 띄고 데이터를 입력합니다.
SELECT !TRUE, NOT 1, !FALSE, NOT FALSE;

 

WHERE를 통해 가져올 시

-- Customers테이블 전체 다 가져옵니다.
SELECT * FROM Customers WHERE TRUE;

 

 

연산자	의미
IS	양쪽이 모두 TRUE 또는 FALSE
IS NOT	한쪽은 TRUE, 한쪽은 FALSE

-- 괄호 먼저 인식합니다.
SELECT (TRUE IS FALSE) IS NOT TRUE;	-- 1

 

 

연산자	의미
AND, &&	양쪽이 모두 TRUE일때만 TRUE
OR, ||	한쪽이 TRUE면 TRUE

 

SELECT 2 + 3 = 6 OR 2 * 3 = 6;	-- 1

SELECT * FROM Products 
WHERE
  ProductName = 'Tofu' OR CategoryId = 8;

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

 

 

연산자	의미
=	양쪽 값이 같음
!=, <>	양쪽 값이 다름
>, <	왼쪽 / 오른쪽 값이 더 큼
>=, <=	왼쪽 / 오른쪽 값이 같거나 더 큼

 

알파벳순으로 크기를 따집니다.

SELECT 'Apple' > 'Banana' AND 1 < 2 IS TRUE; -- 0

 

💡 테이블의 기존 컬럼이 아닌 AS 이용한 컬럼으로 가져오기

SELECT
  ProductName, Price,
  NOT Price > 25 AS CHEAP 
FROM Products;

 

 

연산자	의미
BETWEEN {min} AND {max}	두 값 사이에 있음
NOT BETWEEN {min} AND {max}	두 값 사이가 아닌 곳에 있음

 

SELECT 10 BETWEEN 1 AND 15;	-- 1

-- 사전상 알파벳순으로 비교합니다.
SELECT 'banana' NOT BETWEEN 'Apple' AND 'camera';	-- 0

-- 숫자
SELECT * FROM OrderDetails
WHERE ProductID BETWEEN 1 AND 4;

-- 문자열
SELECT * FROM Customers
WHERE CustomerName BETWEEN 'b' AND 'c';

 

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

SELECT 10 + 2 IN (12, 13, 4) 		-- 1

SELECT 'Lea' IN (1, TRUE, 'LEA') 	-- 1

SELECT * FROM Customers
WHERE City IN ('Berlin', 'Seattle', 'London', 'Madrid');

💡 [중요] LIKE 연산자

연산자	의미
LIKE '...%...'	0 ~ N개 문자를 가진 패턴
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

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

SELECT * FROM Employees
WHERE Notes LIKE '%university%';

 

 

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

1. 숫자 관련 함수들

함수	설명
ROUND	반올림
CEIL	올림
FLOOR	내림

 

SELECT 
  ROUND(0.5),		-- 1
  CEIL(0.4),		-- 1
  FLOOR(0.6);		-- 0

SELECT 
  Price,
  ROUND(price),
  CEIL(price),
  FLOOR(price)
FROM Products;

 

 

함수	설명
ABS	절대값

SELECT ABS(1), ABS(-1), ABS(3 - 10); 	-- 1 1 7

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

 

함수	설명
GREATEST	(괄호 안에서) 가장 큰 값
LEAST	(괄호 안에서) 가장 작은 값

SELECT 
  GREATEST(1, 20, 45, 100, 12),		-- 100
  LEAST(-12, 23, 55, 40, 120);		-- -12

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

 

💡 그룹 함수 = 집계 함수

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

* [헷갈림 주의] GREATEST, LEAST는 괄호 안에서 가장 큰 값 / 작은 값이고

                    MAX, MIN는 주어진 컬럼내에서 가장 큰 값 / 작은 값입니다.

 

SELECT
  MAX(Quantity),
  MIN(Quantity),
  COUNT(Quantity),
  SUM(Quantity),
  AVG(Quantity)
FROM OrderDetails;

 

 

함수	설명
POW(A, B) 또는 POWER(A, B)	A를 B만큼 제곱
SQRT	제곱근

 

SELECT
  POW(10, 3),		-- 1000
  POWER(4, 2),		-- 16
  SQRT(9);		-- 3
  POW(16, 1/2);		-- 4 제곱근을 POW를 이용할 수 도 있습니다.

SELECT Price, POW(Price, 1/2)
FROM Products
WHERE SQRT(Price) < 4;

 

함수	설명
TRUNCATE(N, n)	n이 양수 : N을 소수점 n자리까지 자르고 반환              (올림, 내림 안 함) n이 음수 : 정수N의  n자리수까지 0처리 후 반환

SELECT
  TRUNCATE(1234.5678, 0),	-- 1234
  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

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

 

여기까지 주요 숫자 함수를 배워보았습니다, 만약 더 많은 숫자 함수를 공부하고 싶으시다면

MySQL 공식문서를 확인하시길 바랍니다.

 

 

2. 문자열 관련 함수들

함수	설명
UCASE 또는 UPPER	모두 대문자로
LCASE 또는 LOWET	모두 소문자로

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

 

함수	설명
CONCAT(...)	괄호 안의 내용을 이어붙임
CONCAT(S, ...)	괄호 안의 내용을 S로 이어붙임

JAVA를 배울 때 String 클래스에서 제공하는 기본 메서드로 concat이 있습니다. 

이 메서드도 문자열을 합치는 데 쓰이는데 MySQL도 비슷한 함수가 있어서 익히는 데 수월했습니다.

 

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

-- 중간중간 띄어쓰기를 넣어서 가져옵니다.
SELECT
  FirstName, LastName, BirthDate, CONCAT_WS(' ', FirstName, LastName, BirthDate) AS Staff
FROM Employees;

 

함수	설명
SUBSTR(S, n, m) 또는 SUBSTRING(S, n, m)	n이 양수 : 문자열 S를 n부터 m개 자름 n이 음수 : 문자열 S를 n부터 m개 자름, 뒤에서부터 진행
LEFT(S, n)	왼쪽부터 n글자
RIGHT(S, n)	오른쪽부터 n글자

💡 1부터 센다는 것에 주의해야 합니다! JAVA 배열 인덱스는 0부터 시작하는 것과 비교해서 알아두어야 합니다. 

 

SELECT
  SUBSTR('ABCDEFG', 4),		-- DEFG
  SUBSTR('ABCDEFG', 3, 2),	-- CD
  SUBSTR('ABCDEFG', -5),	-- CDEFG
  SUBSTR('ABCDEFG', -6, 2); 	-- BC

SELECT
  LEFT('ABCDEFG', 2),	-- AB
  RIGHT('ABCDEFG', 4);  -- DEFG

 

테이블 컬럼 중에 날짜 관련 컬럼을 적절하게 나누는 데 이용할 수 있습니다.

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

 

함수	설명
LENGTH	문자열의 바이트 길이
CHAR_LENGTH 또는 CHARACTER_LENGTH	문자열의 문자 길이

우리가 원하는 문자열을 길이는 CHARACTER_LENGTH를 사용합니다. 

SELECT
  CHAR_LENGTH('Hello, My name is Lea.'),		-- 22
  CHARACTER_LENGTH('Hello, My name is Lea.');		-- 22

 

함수	설명
TRIM	양쪽 공백 제거
LTRIM	왼쪽 공백 제거
RTRIM	오른쪽 공백 제거

TRIM함수는 유용하게 쓰이는 함수입니다,

예를 들어, DB에 특정 단어를 입력할 때 어떤 개발자는 띄어쓰기를 해서 넣기도 하고 안 하고 넣기도 합니다.

추후 원하는 단어를 찾을 때 양쪽 공백을 제거한다면 손쉽게 찾아낼 수 있습니다.

 

SELECT
  CONCAT('|', ' LEA ', '|'),
  CONCAT('|', LTRIM(' LEA '), '|'),
  CONCAT('|', RTRIM(' LEA '), '|'),
  CONCAT('|', TRIM(' LEA '), '|');

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

 

함수	설명
LPAD(S, N, P)	S가 N글자가 될때까지 왼쪽에 P를 이어붙임
RPAD(S, N, P)	S가 N글자가 될때까지 오른쪽에 P를 이어붙임

SELECT
  LPAD('ABC', 6, '*'),		-- ***ABC
  RPAD('ABC', 5, '*');		-- ABC**

 

LPAD 함수를 사용하면 자리 수 확인이 편리합니다. 

특히 id나 금액의 경우 DB의 자릿수를 통일하면 1,10,100의 자리 수 확인을 한눈에 확인할 수 있습니다.

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

 

 

함수	설명
REPLACE(S, A, B)	S중 A를 B로 변경

SELECT
  REPLACE('애버랜드에 오신 것을 환영합니다.', '애버랜드', '롯데월드');
  
/*

롯데월드에 오신 것을 환영합니다.

*/

 

SELECT
  Description,
  REPLACE(Description, ', ', ' and ')
FROM Categories;

Description에 , 부분을 전부 and로 바꾸었습니다. 다만 맨 뒷부분의 경우 

수정 전에도 and로 연결되어 있어 수정 후 and and로 중복되어 출력되었습니다.

이를 해결하기 위해서 선작업으로  , and를 ,로 바꾼 후 다시 작업해주면 해결됩니다.

SELECT
  Description,
  REPLACE(Description, ', and', ','),
  REPLACE(REPLACE(Description, ', and', ','), ',', ' and')
FROM Categories;

 

함수	설명
INSTR(S, s)	S중 s의 첫 위치 반환, 없을 시 0

💡 [비교] JAVA String클래스의 indexOf메서드의 경우 문자가 해당 문자열에 없을 시 -1을 반환합니다.

 

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

 

함수	설명
CONVERT(A, T)	A를 T자료형으로 반환

 

SELECT
  '0001' = '1', -- 0
  CONVERT('0001', DECIMAL) = CONVERT('1', DECIMAL); -- 1

 

여기까지 주요 문자열 함수를 배워보았습니다, 만약 더 많은 문자열 함수를 공부하고 싶으시다면

MySQL 공식문서를 확인해보시길 바랍니다.