| 패딩 |
SELECT
LPAD('ABC', 5, '-'),
RPAD('ABC', 5, '-'); |
| Products 테이블에서 SupplierID와 Price의 값이 다음과 같은 단위로 출력되도록 하세요 |
| SELECT LPAD(SupplierID,3,'0') as SupplierID, Price from Products; |
| 치환 |
| SELECT REPLACE('맥도날드에서 맥도날드 햄버거를 먹었다.', '맥도날드', '버거킹'); |
SELECT
REPLACE(Description, ', ', ' and ')
FROM Categories; |
콤마를 and로 변경하려고 했는데, and and가 되었다. 이를 해결하시오
SELECT
REPLACE(REPLACE(Description,', and',','),',',' and ')
FROM Categories;
SELECT
REPLACE(REPLACE(Description, ', ', ' and '),'and and',' and ')
FROM Categories;
|
| 위치검색 |
| INSTR(S, s) S중 s의 첫 위치 반환, 없을 시 0 |
SELECT
INSTR('ABCDE', 'ABC'),
INSTR('ABCDE', 'BCDE'),
INSTR('ABCDE', 'C'),
INSTR('ABCDE', 'DE'),
INSTR('ABCDE', 'F'); |
Customers 테이블에서 CustomerName의 first name이 5글자 이하인 사람의 이름을 출력하시오
hint. first name은 ' ' full name의 첫 스페이스 앞에 있다
SELECT CustomerName FROM Customers where INSTR(CustomerName,' ') between 1 and 6; |
| 자료형 변환 |
| CAST(A AS T) A를 T 자료형으로 변환 CONVERT(A, T) A를 T 자료형으로 변환 |
SELECT
'01' = '1',
CAST('01' AS DECIMAL) = CAST('1' AS DECIMAL); |
SELECT
'01' = '1',
CONVERT('01', DECIMAL) = CONVERT('1', DECIMAL); |
| 시간 관련 함수 |
CURRENT_DATE, CURDATE 현재 날짜 반환
CURRENT_TIME, CURTIME 현재 시간 반환
CURRENT_TIMESTAMP, NOW 현재 시간과 날짜 반환 |
| SELECT CURDATE(), CURTIME(), NOW(); |
| 문자열에 따른 시간 생성 함수 |
DATE 문자열에 따라 날짜 생성
TIME 문자열에 따라 시간 생성 |
SELECT
'2021-6-1' = '2021-06-01',
DATE('2021-6-1') = DATE('2021-06-01'),
'1:2:3' = '01:02:03',
TIME('1:2:3') = TIME('01:02:03'); |
SELECT
'2021-6-1 1:2:3' = '2021-06-01 01:02:03',
DATE('2021-6-1 1:2:3') = DATE('2021-06-01 01:02:03'),
TIME('2021-6-1 1:2:3') = TIME('2021-06-01 01:02:03'),
DATE('2021-6-1 1:2:3') = TIME('2021-06-01 01:02:03'),
DATE('2021-6-1') = DATE('2021-06-01 01:02:03'),
TIME('2021-6-1 1:2:3') = TIME('01:02:03'); |
Orders 테이블에서 OrderDate가 1997-1-1에서 1997-1-31 사이의 데이터를 가져오시오
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, YEAR(OrderDate) AS YEAR, MONTHNAME(OrderDate) AS MONTHNAME, MONTH(OrderDate) AS MONTH, WEEKDAY(OrderDate) AS WEEKDAY, DAYNAME(OrderDate) AS DAYNAME, DAY(OrderDate) AS DAY FROM Orders; |
SELECT OrderDate, CONCAT(CONCAT_WS('/', YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)),
' ', UPPER(LEFT(DAYNAME(OrderDate), 3))) FROM Orders; |
Orders 테이블에서 월요일만 추출해보세요 [hint OrderDate가 0 이면 월요일]
SELECT * FROM Orders where WEEKDAY(OrderDate)=0; |
| 시분초 반환 함수 |
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); |
Orders 테이블의 OrderDate에 있는 날짜를 위에서 제시된 조건에 맞춰 출력하시오
SELECT
ADDDATE(OrderDate, INTERVAL 1 YEAR),
ADDDATE(OrderDate, INTERVAL -2 MONTH),
ADDDATE(OrderDate, INTERVAL 3 WEEK),
ADDDATE(OrderDate, INTERVAL -4 DAY),
ADDDATE(OrderDate, INTERVAL -5 MINUTE),
ADDDATE(OrderDate, INTERVAL 6 SECOND)
FROM Orders; |
| 시간차 계산 함수 |
DATEDIFF 두 시간/날짜 간 일수차
TIMEDIFF 두 시간/날짜 간 시간차 |
SELECT
OrderDate,
NOW(),
DATEDIFF(OrderDate, NOW())
FROM Orders; |
SELECT
TIMEDIFF('2021-06-21 15:20:35', '2021-06-21 16:34:41'); |
Orders 테이블에서 OrderDate가 1996년10월10일을 기준으로 5일 미만 차이가 나는 날짜를 출력하시오
SELECT OrderDate FROM Orders where ABS(DATEDIFF('1996-10-10',OrderDate))<5; |
| 마지막 날짜 계산 함수 |
| LAST_DAY 해당 달의 마지막 날짜 |
SELECT
OrderDate,
LAST_DAY(OrderDate),
DAY(LAST_DAY(OrderDate)),
DATEDIFF(LAST_DAY(OrderDate), OrderDate)
FROM Orders; |
| 시간 포맷 함수 |
| DATE_FORMAT 시간/날짜를 지정한 형식으로 반환 |
%Y 년도 4자리
%y 년도 2자리 -> 끝의 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초'); |
현재 시간을 Y년 m월 d일 AM/PM h시 i분 s초로 출력하시오
위에서 출력한 날짜에서 AM은 오전으로 PM은 오후로 자동 변경되어 출력되게 하시오 [hint replace함수]
SELECT DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초');
SELECT REPLACE(REPLACE(DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초'),'AM','오전'),'PM','오후'); |
| 진위함수 |
| IF(조건, T, F) 조건이 참이라면 T, 거짓이면 F 반환 |
| SELECT IF (1 > 2, '1는 2보다 크다.', '1은 2보다 작다.'); |
SELECT
CASE
WHEN -1 > 0 THEN '-1은 양수다.'
WHEN -1 = 0 THEN '-1은 0이다.'
ELSE '-1은 음수다.'
END; |
Products 테이블에서 Price가 30보다 크면 Expensive 나머지는 Cheap으로 출력하는 열을 따로 만들어서
Price가 20 미만이면 저가, 20과 30 사이이면 일반, 그 외는 고가로 출력하시오
SELECT
Price,
IF (Price>30, 'Expensive', 'Cheap') as '비교',
CASE
WHEN Price < 20 THEN '저가'
WHEN Price between 20 and 30 THEN '일반'
ELSE '고가'
END AS '분류'
FROM Products; |
| NULL 관련 함수 |
| IFNULL(A, B) A가 NULL일 시 B 출력 |
SELECT
IFNULL('A', 'B'),
IFNULL(NULL, 'B'); |
집계함수: null값은 집계하지 않는다
MAX 가장 큰 값
MIN 가장 작은 값
COUNT 갯수 (NULL값 제외)
SUM 총합
AVG 평균 값 |
| select Country from Customers group by Country; |
| SELECT CategoryID FROM Products GROUP BY CategoryID; |
| SELECT Country, City, CONCAT_WS(', ', City, Country) FROM Customers GROUP BY Country, City; |
Orders 테이블에서 group by로 OrderDate를 묶은 다음, 동일 OrderDate의 갯수를 출력하시오.
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; |
Customers 테이블에서 Country와 City를 콤마(,)로 묶고 CustomersID 수를 적으세요
hint] count 함수
SELECT CONCAT_WS(',',Country,City) as Location,COUNT(CustomerID) as Numbers FROM Customers group by Location; |
| ROLLUP |
| SELECT Country, COUNT(*) FROM Suppliers GROUP BY Country WITH ROLLUP; |
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; |
위에서 출력했던 쿼리문 맨 뒤에
with rollup; 을 추가하면
각 항목의 총합을 알 수 있다 |
| HAVING |
| HAVING - 그룹화된 데이터 걸러내기 |
| SELECT Country, COUNT(*) AS Count FROM Suppliers GROUP BY Country HAVING Count >= 3; |
SELECT
COUNT(*) AS Count, OrderDate
FROM Orders
WHERE OrderDate > DATE('1996-12-31')
GROUP BY OrderDate
HAVING Count > 2; |
WHERE는 그룹하기 전 데이터, HAVING은 그룹 후 집계에 사용한다. |
| 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 CategoryID in (4,5,8); |
Products 테이블에서 CategoryID를 가져오는데, MaxPrice이름으로 Price 최대값과 MinPrice이름으로 Price최소값을 가져오고, MedianPrice이름으로 중간값을 계산해서 가져오고, AveragePrice이름으로 평균값 계산하기
이때, CategoryID는 2보다 큰 값이고, AveragePrice 값은 20과 30사이인 것을 출력
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 CategoryID>2 and AveragePrice between 20 and 30; |
| DISTINCT |
| DISTINCT - 중복된 값들을 제거합니다. select 문에서 사용한다. |
SELECT DISTINCT CategoryID
FROM Products; |
| SELECT DISTINCT Country FROM Customers ORDER BY Country; |
| SELECT DISTINCT Country, City FROM Customers ORDER BY Country; |
Customers 테이블에서 Country를 가져오는데, 각 County마다 겹치지 않는 도시는 몇 개인지 출력하기
출력 시 Country기준으로 알파벳 정렬
SELECT Country,COUNT(DISTINCT City) as CityNum FROM Customers GROUP BY Country order by Country; |
| 서브쿼리 |
SELECT
CategoryID, CategoryName, Description,
(SELECT ProductName FROM Products WHERE ProductID = 1)
FROM Categories; |
SELECT * FROM Products
WHERE Price < (SELECT AVG(Price) FROM Products); |
Categories 테이블에서 CategoryID, CategoryName, Description을 가져온다
이때 (가져올때 조건, 서브쿼리 내용은)
Products 테이블에서 Price가 50 초과인 CategoryID중에서 CategoryID를 가져온다
[CategoryID는 Price가 50 초과인 것들 중에서 가져온다. hint : IN 함수 ~중에서 가져오기]
SELECT CategoryID,CategoryName,Description FROM Categories WHERE CategoryID in (select CategoryID from Products where Price>50); |
| 서브쿼리 조건 |
~ ALL 서브쿼리의 모든 결과에 대해 ~하다
~ ANY 서브쿼리의 하나 이상의 결과에 대해 ~하다 |
| SELECT * FROM Products WHERE Price > ALL (SELECT Price FROM Products WHERE CategoryID = 2); |
Categories 테이블에서 CategoryID, CategoryName, Description을 가져오는데
서브쿼리 내용 : CategoryID가 Products 테이블에서 Price가 50 초과인 CategorytID에 해당되는
[hint : any 하나 이상에 해당된다 = 어떤 값에 해당된다 = ..중에 하나에 해당된다]
여기에서는 IN을 사용해도 결과는 동일하다
SELECT CategoryID,CategoryName,Description FROM Categories where CategoryID=any(select CategoryID from Products where Price>50); |
| 상관 서브쿼리 |
| SELECT ProductID, ProductName, (SELECT CategoryName FROM Categories C WHERE C.CategoryID = P.CategoryID) AS CategoryName FROM Products P; |
SELECT SupplierName, Country, City,
(SELECT COUNT(*) FROM Customers C WHERE C.Country = S.Country)
AS CustomersInTheCountry,
(SELECT COUNT(*) FROM Customers C WHERE C.Country = S.Country AND C.City = S.City)
AS CustomersInTheCity
FROM Suppliers S; |
Categories 테이블에서 CategoryID와 CategoryName을 가져온다
서브쿼리1. Products 테이블의 CategoryID와 Categories 테이블의 CategoryID가 같은 Produects 테이블에서의 최대(MAX) Price를 MaximumPrice 라는 이름으로 출력
서브쿼리2. Products 테이블의 CategoryID와 Categories 테이블의 CategoryID가 같은 Produects 테이블에서의 평균(AVG) Price를 AveragePrice 라는 이름으로 출력
SELECT CategoryID,CategoryName,
(select MAX(Price) from Products P where P.CategoryID=C.CategoryID) as MaximumPrice,
(select AVG(Price) from Products P where P.CategoryID=C.CategoryID) as AveragePrice
FROM Categories C; |
| 조인 |
| JOIN(INNER JOIN) - 내부 조인 |
관계형 데이터베이스는 데이터의 중복을 피하기 위해서 테이블을 나눈다.
join은 나눠진 테이블들에서 정보를 가져오는 작업 |
- 조인은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다.
- INNER JOIN(내부 조인)은 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.
- OUTER JOIN(외부 조인)은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.
- CROSS JOIN(상호 조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.
- SELF JOIN(자체 조인)은 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다. |
| SELECT * FROM Categories C JOIN Products P ON C.CategoryID = P.CategoryID; |
| 그냥 join이라고 쓰면 내부조인인 된다 |
SELECT C.CategoryID, C.CategoryName, P.ProductName FROM Categories C
JOIN Products P ON C.CategoryID = P.CategoryID; |
Products 테이블과 Suppliers 테이블을 JOIN,
Products테이블의 SupplierID와 Suppliers테이블의 SupplierID가 같고
Products테이블에서 ProductName을 가져오고, Suppliers테이블에서 SupplierName을 가져와서 ‘by'로 연결하고
컬럼이름은 Product로 한다.
Suppliers테이블에서 Phone을 가져오고, Products테이블에서 Price를 가져온다
Price는 50초과를 가져오고, ProductName으로 order by 한다 [order by default값은 asc 오름차순]
SELECT CONCAT(P.ProductName,'by',S.SupplierName) as Product, S.Phone, P.Price FROM Products P Join Suppliers S on P.SupplierID=S.SupplierID where P.Price>50 order by P.ProductName; |
| 2개가 아닌 여러 테이블 join하기 |
SELECT C.CategoryID, C.CategoryName, P.ProductName, O.OrderDate, D.Quantity
FROM Categories C
JOIN Products P ON C.CategoryID = P.CategoryID
JOIN OrderDetails D ON P.ProductID = D.ProductID
JOIN Orders O ON O.OrderID = D.OrderID; |
| 조인 후 그룹핑하기 |
SELECT C.CategoryID, C.CategoryName, MIN(O.OrderDate) AS FirstOrder, MAX(O.OrderDate) AS LastOrder, SUM(D.Quantity) AS TotalQuantity FROM Categories C
JOIN Products P ON C.CategoryID = P.CategoryID
JOIN OrderDetails D ON P.ProductID = D.ProductID
JOIN Orders O ON O.OrderID = D.OrderID
GROUP BY C.CategoryID; |
SELECT C.CategoryID, P.ProductID, C.CategoryName, MIN(O.OrderDate) AS FirstOrder, MAX(O.OrderDate) AS LastOrder, SUM(D.Quantity) AS TotalQuantity FROM Categories C
JOIN Products P ON C.CategoryID = P.CategoryID
JOIN OrderDetails D ON P.ProductID = D.ProductID
JOIN Orders O ON O.OrderID = D.OrderID
GROUP BY C.CategoryID, P.ProductID; |
| self 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 JOIN Employees E2 ON E1.EmployeeID + 1 = E2.EmployeeID; |
| LEFT/RIGHT OUTER JOIN - 외부 조인 [둘 중에 한쪽에만 있어도 가져오는 조인] |
SELECT
E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
ifnull(E2.EmployeeID,'N'), 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; |
Customers 테이블에 Suppliers 테이블을 조인하라
조인조건: Customers.City=Suppliers.City and Customers.Country=Suppliers.Country
출력되는 컬럼명
Customers테이블의 CustomerName, City, Country
Suppliers 테이블의 SupplierName
Customers 테이블에는 있는데 Suppliers 테이블에는 없어도 출력시켜라 -> left or right join
SELECT C.CustomerName,S.SupplierName,C.City,C.Country FROM Customers C Left join Suppliers S on C.City=S.city and C.Country=S.Country; |
반대로 Suppliers 테이블에는 있는데 Customers테이블에는 없어도 출력시켜라 -> left or right join
SELECT C.CustomerName,S.SupplierName,C.City,C.Country FROM Customers C Right join Suppliers S on C.City=S.city and C.Country=S.Country; |
위의 쿼리문에서 left join이나 right join을 할 때
CustomerName이 없는 경우에는 No Customer 라고 출력되고,
SupplierName이 없는 경우에는 No Supplier 가 출력되도록 하라
SELECT ifnull(C.CustomerName,'--NO CUSTOMER--') as CustomerName,S.SupplierName,C.City,C.Country FROM Customers C Right join Suppliers S on C.City=S.city and C.Country=S.Country;
SELECT C.CustomerName,ifnull(S.SupplierName,'--NO SUPPLIER--') as SupplierName,C.City,C.Country FROM Customers C Left join Suppliers S on C.City=S.city and C.Country=S.Country; |
CROSS JOIN - 교차 조인 : 조건 없이 모든 조합 반환(A * B)
join을 할 때 on으로 조건을 붙이지 않고, 모든 조합을 하는 경우. A테이블의 행수*B테이블의 행수 |
SELECT E1.LastName, E2.FirstName FROM Employees E1
CROSS JOIN Employees E2 ORDER BY E1.EmployeeID; |
Orders 테이블과 OrderDetails 테이블을 CROSS JOIN하라
Orders 테이블의 CustomerID와 OrderDetails 테이블의 ProductID를 가져와라
Orders 테이블의 OrderID로 order by 하라
SELECT O.CustomerID,D.ProductID FROM Orders O CROSS JOIN OrderDetails D order by O.OrderID; |
UNION 중복을 제거한 집합
UNION ALL 중복을 제거하지 않은 집합 |
SELECT CustomerName AS Name, 'CUSTOMER' FROM Customers
UNION
SELECT SupplierName AS Name, 'SUPPLIER' FROM Suppliers
ORDER BY Name; |
SELECT CustomerName AS NAME,City,Country,'CUSTOMER' FROM Customers
UNION
SELECT SupplierName AS NAME,City,Country,'SUPPLIER' FROM Suppliers
ORDER BY NAME; |
