SQL

SELECT * FROM customers;

SELECT CustomerName, ContactName FROM Customers;

SELECT CustomerName AS 고객, Address AS 주소, PostalCode AS 우편번호 FROM Customers;

DROP TABLE IF EXISTS mst_users; CREATE TABLE mst_users( user_id varchar(255) , sex varchar(255) , birth_date varchar(255) , register_date varchar(255) , register_device varchar(255) , withdraw_date varchar(255) ); INSERT INTO mst_users VALUES ('U001', 'M', '1977-06-17', '2020-10-01', 'pc' , NULL ) , ('U002', 'F', '1953-06-12', '2020-10-01', 'sp' , '2020-10-10') , ('U003', 'M', '1965-01-06', '2020-10-01', 'pc' , NULL ) , ('U004', 'F', '1954-05-21', '2020-10-05', 'pc' , NULL ) , ('U005', 'M', '1987-11-23', '2020-10-05', 'sp' , NULL ) , ('U006', 'F', '1950-01-21', '2020-10-10', 'pc' , '2020-10-10') , ('U007', 'F', '1950-07-18', '2020-10-10', 'app', NULL ) , ('U008', 'F', '2006-12-09', '2020-10-10', 'sp' , NULL ) , ('U009', 'M', '2004-10-23', '2020-10-15', 'pc' , NULL ) , ('U010', 'F', '1987-03-18', '2020-10-16', 'pc' , NULL ) ; DROP TABLE IF EXISTS action_log; CREATE TABLE action_log( session varchar(255) , user_id varchar(255) , action varchar(255) , category varchar(255) , products varchar(255) , amount integer , stamp varchar(255) ); INSERT INTO action_log VALUES ('989004ea', 'U001', 'purchase', 'drama' , 'D001,D002', 2000, '2020-11-03 18:10:00') , ('989004ea', 'U001', 'view' , NULL , NULL , NULL, '2020-11-03 18:00:00') , ('989004ea', 'U001', 'favorite', 'drama' , 'D001' , NULL, '2020-11-03 18:00:00') , ('989004ea', 'U001', 'review' , 'drama' , 'D001' , NULL, '2020-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2020-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2020-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2020-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2020-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001' , NULL, '2020-11-03 18:00:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D002' , NULL, '2020-11-03 18:01:00') , ('989004ea', 'U001', 'add_cart', 'drama' , 'D001,D002', NULL, '2020-11-03 18:02:00') , ('989004ea', 'U001', 'purchase', 'drama' , 'D001,D002', 2000, '2020-11-03 18:10:00') , ('47db0370', 'U002', 'add_cart', 'drama' , 'D001' , NULL, '2020-11-03 19:00:00') , ('47db0370', 'U002', 'purchase', 'drama' , 'D001' , 1000, '2020-11-03 20:00:00') , ('47db0370', 'U002', 'add_cart', 'drama' , 'D002' , NULL, '2020-11-03 20:30:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A004' , NULL, '2020-11-04 12:00:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A005' , NULL, '2020-11-04 12:00:00') , ('87b5725f', 'U001', 'add_cart', 'action', 'A006' , NULL, '2020-11-04 12:00:00') , ('9afaf87c', 'U002', 'purchase', 'drama' , 'D002' , 1000, '2020-11-04 13:00:00') , ('9afaf87c', 'U001', 'purchase', 'action', 'A005,A006', 1000, '2020-11-04 15:00:00') ; DROP TABLE IF EXISTS purchase_log; CREATE TABLE purchase_log( dt varchar(255) , order_id integer , user_id varchar(255) , purchase_amount integer ); INSERT INTO purchase_log VALUES ('2019-01-01', 1, 'rhwpvvitou', 13900) , ('2019-01-01', 2, 'hqnwoamzic', 10616) , ('2019-01-02', 3, 'tzlmqryunr', 21156) , ('2019-01-02', 4, 'wkmqqwbyai', 14893) , ('2019-01-03', 5, 'ciecbedwbq', 13054) , ('2019-01-03', 6, 'svgnbqsagx', 24384) , ('2019-01-03', 7, 'dfgqftdocu', 15591) , ('2019-01-04', 8, 'sbgqlzkvyn', 3025) , ('2019-01-04', 9, 'lbedmngbol', 24215) , ('2019-01-04', 10, 'itlvssbsgx', 2059) , ('2019-01-05', 11, 'jqcmmguhik', 4235) , ('2019-01-05', 12, 'jgotcrfeyn', 28013) , ('2019-01-05', 13, 'pgeojzoshx', 16008) , ('2019-01-06', 14, 'msjberhxnx', 1980) , ('2019-01-06', 15, 'tlhbolohte', 23494) , ('2019-01-06', 16, 'gbchhkcotf', 3966) , ('2019-01-07', 17, 'zfmbpvpzvu', 28159) , ('2019-01-07', 18, 'yauwzpaxtx', 8715) , ('2019-01-07', 19, 'uyqboqfgex', 10805) , ('2019-01-08', 20, 'hiqdkrzcpq', 3462) , ('2019-01-08', 21, 'zosbvlylpv', 13999) , ('2019-01-08', 22, 'bwfbchzgnl', 2299) , ('2019-01-09', 23, 'zzgauelgrt', 16475) , ('2019-01-09', 24, 'qrzfcwecge', 6469) , ('2019-01-10', 25, 'njbpsrvvcq', 16584) , ('2019-01-10', 26, 'cyxfgumkst', 11339) ; DROP TABLE IF EXISTS purchase_detail_log; CREATE TABLE purchase_detail_log( dt varchar(255) , order_id integer , user_id varchar(255) , item_id varchar(255) , price integer , category varchar(255) , sub_category varchar(255) ); INSERT INTO purchase_detail_log VALUES ('2019-12-01', 1, 'U001', 'D001', 2000, 'ladys_fashion', 'bag' ) , ('2019-12-08', 95, 'U002', 'D002', 3000, 'dvd' , 'documentary') , ('2019-12-09', 168, 'U003', 'D003', 5000, 'game' , 'accessories') , ('2019-12-11', 250, 'U004', 'D004', 8000, 'ladys_fashion', 'jacket' ) , ('2019-12-11', 325, 'U005', 'D005', 2000, 'mens_fashion' , 'jacket' ) , ('2019-12-12', 400, 'U006', 'D006', 4000, 'cd' , 'classic' ) , ('2019-12-11', 475, 'U007', 'D007', 4000, 'book' , 'business' ) , ('2019-12-10', 550, 'U008', 'D008', 6000, 'food' , 'meats' ) , ('2019-12-10', 625, 'U009', 'D009', 6000, 'food' , 'fish' ) , ('2019-12-11', 700, 'U010', 'D010', 2000, 'supplement' , 'protain' ) ;

SELECT [열1, 열2, ...] FROM [테이블명] WHERE [조건식]

1 < 2 < 10 < 100 '1988-11-30' < '2020-01-01' < '2020-03-02' '우아한테크코스' < '우아한형제들' < '포비'

SELECT * FROM Customers WHERE Country = 'Germany';

SELECT * FROM Orders WHERE ShipperID <> 2;

SELECT * FROM OrderDetails WHERE Quantity > 100;

SELECT * FROM Employees WHERE FirstName >= 'O';

SELECT * FROM Employees WHERE BirthDate <= '1950-01-01';

# text : 'SQL 강의 - 레벨2' SELECT * FROM [테이블명] WHERE text LIKE 'SQL%'; # text : '우아한 테크코스에서 진행하는 입문용 SQL' SELECT * FROM [테이블명] WHERE text LIKE '%SQL'; # text : 'LIKE 문은 SQL에서 사용하는 ...' SELECT * FROM [테이블명] WHERE text LIKE '%SQL%';

# 두 쿼리의 결과는 다르다. 위의 경우, WHERE 2는 항상 TRUE 조건이다. SELECT * FROM [테이블명] WHERE no = 1 OR 2; SELECT * FROM [테이블명] WHERE no = 1 OR no = 2;

# 이 경우, a = 2 AND b = 1 가 먼저 계산 SELECT * FROM [테이블명] WHERE a = 1 OR a = 2 AND b = 1 OR b = 2; # 따라서 아래와 같이 작성해야 한다. SELECT * FROM [테이블명] WHERE (a = 1 OR a = 2) AND (b = 1 OR b = 2);

SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-05';

SELECT * FROM Products WHERE CategoryID IN (5, 6);

SELECT [열1, 열2, ...] FROM [테이블명] ORDER BY [열1, 열2, ...] { ASC | DESC }

SELECT [열1, 열2, ...] FROM [테이블명] LIMIT [행수]

SELECT * FROM Customers ORDER BY Country;

SELECT * FROM Products ORDER BY Price DESC LIMIT 5;

# 우선순위가 다른 경우의 연산 # + 보다 * 의 우선순위가 높다. 우선순위가 같다면 왼쪽에서 오른쪽으로 계산한다. SELECT 1 - 2 + 2 * 3; # 나머지 값 반환하기 SELECT MOD(10, 3) # 반올림하기 SELECT ROUND(30.60, 1)

# 문자열 합치기 SELECT CONCAT('우아한', '형제들') # 문자열 자르기 SELECT SUBSTRING('20190422', 1, 4)

# 행 개수 구하기 SELECT COUNT(*) FROM [테이블명] # 중복 제거하기 SELECT DISTINCT [열명] FROM [테이블명] # SUM, AVG, MIN, MAX SELECT SUM([열명]) FROM [테이블명]

SELECT Country, COUNT(*) AS CountByCountry FROM [Customers] GROUP BY Country

SELECT Country, COUNT(Country) AS CountByCountry FROM [Customers] WHERE COUNT(Country) = 1 GROUP BY Country

SELECT Country, COUNT(Country) AS CountByCountry FROM [Customers] GROUP BY Country HAVING COUNT(Country) = 1

5) SELECT city AS 도시, COUNT(city) AS 집계 1) FROM user 2) WHERE user.age >= 18 3) GROUP BY city 4) HAVING city >= 'b' 6) ORDER BY city 1. FROM에서 데이터 집합을 만든다. 2. WHERE는 FROM에서 만든 데이터 집합을 조건에 맞게 걸러낸다. 3. GROUP BY는 WHERE에서 필터링한 (조건에 맞는 데이터를 걸러낸) 데이터를 그룹화한다. 4. HAVING은 GROUP BY에서 집계한 데이터 집합을 다시 조건에 맞게 필터링한다. 5. SELECT는 그룹화하고 필터링한 데이터 집합을 집계한다. 6. ORDER BY는 집계한 데이터 집합을 정렬한다.

DROP TABLE IF EXISTS purchase_log; CREATE TABLE purchase_log( dt varchar(255) , order_id integer , user_id varchar(255) , purchase_amount integer ); INSERT INTO purchase_log VALUES ('2014-01-01', 1, 'rhwpvvitou', 13900) , ('2014-01-01', 2, 'hqnwoamzic', 10616) , ('2014-01-02', 3, 'tzlmqryunr', 21156) , ('2014-01-02', 4, 'wkmqqwbyai', 14893) , ('2014-01-03', 5, 'ciecbedwbq', 13054) , ('2014-01-03', 6, 'svgnbqsagx', 24384) , ('2014-01-03', 7, 'dfgqftdocu', 15591) , ('2014-01-04', 8, 'sbgqlzkvyn', 3025) , ('2014-01-04', 9, 'lbedmngbol', 24215) , ('2014-01-04', 10, 'itlvssbsgx', 2059) , ('2014-01-05', 11, 'jqcmmguhik', 4235) , ('2014-01-05', 12, 'jgotcrfeyn', 28013) , ('2014-01-05', 13, 'pgeojzoshx', 16008) , ('2014-01-06', 14, 'msjberhxnx', 1980) , ('2014-01-06', 15, 'tlhbolohte', 23494) , ('2014-01-06', 16, 'gbchhkcotf', 3966) , ('2014-01-07', 17, 'zfmbpvpzvu', 28159) , ('2014-01-07', 18, 'yauwzpaxtx', 8715) , ('2014-01-07', 19, 'uyqboqfgex', 10805) , ('2014-01-08', 20, 'hiqdkrzcpq', 3462) , ('2014-01-08', 21, 'zosbvlylpv', 13999) , ('2014-01-08', 22, 'bwfbchzgnl', 2299) , ('2014-01-09', 23, 'zzgauelgrt', 16475) , ('2014-01-09', 24, 'qrzfcwecge', 6469) , ('2014-01-10', 25, 'njbpsrvvcq', 16584) , ('2014-01-10', 26, 'cyxfgumkst', 11339) ;

# sample 데이터 중 a 열의 최솟값은 아래와 같이 검색할 수 있다. SELECT MIN(a) FROM sample; # 최솟값을 가지는 행은 아래와 같이 삭제할 수 있다. DELETE FROM sample WHERE a = (SELECT MIN(a) FROM sample);

SELECT (SELECT COUNT(*) FROM [Customers] WHERE Country = 'Germany') AS GermanyCount, (SELECT COUNT(*) FROM [Customers] WHERE Country = 'Mexico') AS MexicoCount

SELECT * FROM (SELECT FirstName, LastName FROM [Employees] WHERE EmployeeID < 10)

SELECT * FROM [OrderDetails] WHERE Quantity = (SELECT MAX(Quantity) FROM [OrderDetails])

DELETE FROM [Customers] WHERE EXISTS (SELECT * FROM [Orders] WHERE OrderDate >= "1996-07-08")

SELECT * FROM [Customers] WHERE Country IN ('Germany', 'UK')

SELECT Country FROM [Customers] UNION SELECT Country FROM [Suppliers] ORDER BY Country

SELECT Country FROM [Customers] UNION ALL SELECT Country FROM [Suppliers] ORDER BY Country

SELECT * FROM [Products], [Employees] WHERE [Employees].EmployeeID = [Products].SupplierID

SELECT * FROM [Products] INNER JOIN [OrderDetails] ON Products.ProductID = OrderDetails.ProductID

# 어느 한 쪽에만 존재하는 데이터행을 기준으로 검색한다. 따라서 NULL 값이 있을 수 있다. # 아래 두 쿼리의 결과는 같다. SELECT * FROM [Products] LEFT JOIN [Employees] ON [Employees].EmployeeID = [Products].SupplierID SELECT * FROM [Employees] RIGHT JOIN [Products] ON [Employees].EmployeeID = [Products].SupplierID

DROP TABLE IF EXISTS categories; CREATE TABLE categories ( category_id integer , name varchar(255) ); INSERT INTO categories VALUES (1, 'dvd' ) , (2, 'cd' ) , (3, 'book') ; DROP TABLE IF EXISTS category_sales; CREATE TABLE category_sales ( category_id integer , sales integer ); INSERT INTO category_sales VALUES (1, 850000) , (2, 500000) ; DROP TABLE IF EXISTS product_sale_ranking; CREATE TABLE product_sale_ranking ( category_id integer , rank integer , product_id varchar(255) , sales integer ); INSERT INTO product_sale_ranking VALUES (1, 1, 'D001', 50000) , (1, 2, 'D002', 20000) , (1, 3, 'D003', 10000) , (2, 1, 'C001', 30000) , (2, 2, 'C002', 20000) , (2, 3, 'C003', 10000) ;

DROP TABLE IF EXISTS products_20161201; CREATE TABLE products_20161201( product_id varchar(255) , name varchar(255) , price integer , updated_at varchar(255) ); INSERT INTO products_20161201 VALUES ('A001', 'AAA', 3000, '2016-11-03 18:00:00') , ('A002', 'AAB', 4000, '2016-11-03 19:00:00') , ('B001', 'BBB', 5000, '2016-11-03 20:00:00') , ('B002', 'BBD', 3000, '2016-11-03 21:00:00') , ('C001', 'CCA', 4000, '2016-11-04 18:00:00') , ('D001', 'DAA', 5000, '2016-11-04 19:00:00') ; DROP TABLE IF EXISTS products_20170101; CREATE TABLE products_20170101( product_id varchar(255) , name varchar(255) , price integer , updated_at varchar(255) ); INSERT INTO products_20170101 VALUES ('A001', 'AAA', 3000, '2016-11-03 18:00:00') , ('A002', 'AAB', 4000, '2016-11-03 19:00:00') , ('B002', 'BBD', 3000, '2016-11-03 21:00:00') , ('C001', 'CCA', 5000, '2016-12-04 18:00:00') , ('D001', 'DAA', 5000, '2016-11-04 19:00:00') , ('D002', 'DAD', 5000, '2016-12-04 19:00:00') ;