2024.07.09 / SQL, Pandas - 기초 문법 비교 / (1)

 

 

[K-Digital Training] 한국경제신문 with toss bank

 

 

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

MY SQL 듀토리얼

https://www.mysqltutorial.org/

MySQL Tutorial - Learn MySQL Fast, Easy and Fun.

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

-SQL

USE classicmodels; -- 해당 Database를 사용하겠음
CREATE SCHEMA mydata2; -- 새로운 스키마 생성
SHOW databases; -- 모든 데이터베이스를 표시

SELECT * FROM customers; -- customers 테이블의 모든 데이터를 선택 (Ctrl + Enter)

SELECT * FROM classicmodels.customers; -- 스키마를 명시하여 customers 테이블의 모든 데이터를 선택
USE classicmodels; -- classicmodels 데이터베이스를 사용
SHOW tables; -- 현재 데이터베이스의 모든 테이블을 표시

-- 현재 사용중인 스키마를 확인
SELECT DATABASE();

USE classicmodels; -- classicmodels 데이터베이스를 사용
SELECT * FROM customers; -- customers 테이블의 모든 데이터를 선택

DESC customers; -- customers 테이블의 구조를 설명

 

- Pandas

import pandas as pd

# 샘플 데이터프레임 생성
data = {
    'customer_id': [1, 2, 3, 4, 5],  # 고객 ID
    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eva'],  # 고객 이름
    'state': ['CA', 'NV', 'CA', 'AZ', 'TX']  # 고객이 거주하는 주
}

df = pd.DataFrame(data)

# SQL: USE classicmodels;
# classicmodels 데이터베이스 사용 (Pandas에서는 데이터프레임으로 대체)

# SQL: CREATE SCHEMA mydata2;
# 새로운 스키마 생성 (Pandas에서 해당 기능은 지원하지 않음)

# SQL: SHOW databases;
# 모든 데이터베이스를 표시 (Pandas에서 해당 기능은 지원하지 않음)

# SQL: SELECT * FROM customers;
# 모든 컬럼을 선택합니다.
print(df)

# SQL: SELECT * FROM classicmodels.customers;
# 스키마를 명시하여 customers 테이블의 모든 데이터를 선택
print(df)

# SQL: USE classicmodels;
# classicmodels 데이터베이스를 사용 (Pandas에서는 데이터프레임으로 대체)

# SQL: SHOW tables;
# 현재 데이터베이스의 모든 테이블을 표시 (Pandas에서 해당 기능은 지원하지 않음)

# SQL: SELECT DATABASE();
# 현재 사용중인 스키마를 확인 (Pandas에서 해당 기능은 지원하지 않음)

# SQL: USE classicmodels;
# classicmodels 데이터베이스를 사용 (Pandas에서는 데이터프레임으로 대체)

# SQL: SELECT * FROM customers;
# 모든 컬럼을 선택합니다.
print(df)

# SQL: DESC customers;
# customers 테이블의 구조를 설명
print(df.info())

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

-SQL

-- SELECT : 선택하다 필드를
SELECT * FROM customers;
SELECT customerNumber, customerName, contactFirstName FROM customers;
SELECT 
	customerNumber
    , customerName
    , contactFirstName
FROM 
	customers
;

-- customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit

 

-Pandas

# SQL: SELECT * FROM customers;
# 모든 컬럼을 선택합니다.
print(df)

# SQL: SELECT customerNumber, customerName, contactFirstName FROM customers;
# 특정 컬럼들을 선택합니다.
print(df[['customerNumber', 'customerName', 'contactFirstName']])

# SQL: SELECT customerNumber, customerName, contactFirstName FROM customers;
# 여러 줄로 작성된 특정 컬럼들을 선택합니다.
print(df[['customerNumber', 'customerName', 'contactFirstName']])

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

-SQL

-- WHERE 조건 : 필터링
SELECT *
FROM
	customers
WHERE country = 'USA'
;

SELECT *
FROM
	customers
WHERE customerNumber = '112'  -- 잘 못 써도 MySQL은 자동 변경을 진행해 준다.
;

SELECT *
FROM
	customers
WHERE customerNumber != '112'
;

 

-Pandas

# SQL: SELECT * FROM customers WHERE country = 'USA';
# country가 'USA'인 행을 선택합니다.
print(df[df['country'] == 'USA'])

# SQL: SELECT * FROM customers WHERE customerNumber = '112';
# customerNumber가 '112'인 행을 선택합니다.
print(df[df['customerNumber'] == 112])

# SQL: SELECT * FROM customers WHERE customerNumber != '112';
# customerNumber가 '112'가 아닌 행을 선택합니다.
print(df[df['customerNumber'] != 112])

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

-SQL

-- 문자열과 부등호 연산
SELECT *
FROM
	customers
WHERE
	STATE <> 'CA';

-- WHERE LIKE 연산자
SELECT *
FROM
	customers
WHERE customerName LIKE '%GIFT%'
;

-- 문자열 검색할 때 가장 유용한 것 : 정규표현식을 활용한 검색
-- 매우 어려움, 근데 무조건 필요하다.
SELECT *
FROM
	customers
WHERE customerName REGEXP 'La*'
;

 

-Pandas

# SQL: SELECT * FROM customers WHERE STATE <> 'CA';
# STATE가 'CA'가 아닌 행을 선택합니다.
print(df[df['state'] != 'CA'])

# SQL: SELECT * FROM customers WHERE customerName LIKE '%GIFT%';
# customerName에 'GIFT'가 포함된 행을 선택합니다.
print(df[df['customerName'].str.contains('GIFT', case=False, na=False)])

# SQL: SELECT * FROM customers WHERE customerName REGEXP 'La*';
# customerName이 정규표현식 'La*'와 일치하는 행을 선택합니다.
print(df[df['customerName'].str.contains('La*', regex=True)])

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

-SQL

-- AND
-- country가 USA이면서 city가 NYC인 고객을 조회하세요.
SELECT * 
FROM 
	customers
WHERE country = 'USA' AND city = 'NYC'
;

-- OR 조건
SELECT * 
FROM 
	customers
WHERE country = 'USA' OR contactLastName = 'Lee'
;

 

-Pandas

# SQL: SELECT * FROM customers WHERE country = 'USA' AND city = 'NYC';
# country가 'USA'이면서 city가 'NYC'인 행을 선택합니다.
print(df[(df['country'] == 'USA') & (df['city'] == 'NYC')])

# SQL: SELECT * FROM customers WHERE country = 'USA' OR contactLastName = 'Lee';
# country가 'USA'이거나 contactLastName이 'Lee'인 행을 선택합니다.
print(df[(df['country'] == 'USA') | (df['contactLastName'] == 'Lee')])

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

-SQL

-- AND
-- country가 USA이면서 city가 NYC인 고객을 조회하세요.
SELECT * 
FROM 
	customers
WHERE country = 'USA' AND city = 'NYC'
;

-- OR 조건
SELECT * 
FROM 
	customers
WHERE country = 'USA' OR contactLastName = 'Lee'
;

-- 테이블 Payments
SELECT * FROM payments;

-- BETWEEN 연산자
SELECT *
FROM
	payments
WHERE
	amount BETWEEN 10000 AND 50000
    AND paymentDate BETWEEN '2003-05-20' AND '2003-06-05'
    AND checkNumber LIKE '%JM%'
;

-- IN 연산자
SELECT *
FROM
	offices
WHERE country NOT IN ('USA', 'France', 'UK')
;

-- WHERE 조건
/*
SELECT 필드명
FROM 테이블명
WHERE 필드명에 관한 여러 조건식
*/

 

-Pandas

# SQL: SELECT * FROM customers WHERE country = 'USA' AND city = 'NYC';
# country가 'USA'이면서 city가 'NYC'인 행을 선택합니다.
print(df[(df['country'] == 'USA') & (df['city'] == 'NYC')])

# SQL: SELECT * FROM customers WHERE country = 'USA' OR contactLastName = 'Lee';
# country가 'USA'이거나 contactLastName이 'Lee'인 행을 선택합니다.
print(df[(df['country'] == 'USA') | (df['contactLastName'] == 'Lee')])

# SQL: SELECT * FROM payments;
# payments 테이블의 모든 데이터를 선택합니다.
print(payments_df)

# SQL: SELECT * FROM payments WHERE amount BETWEEN 10000 AND 50000 AND paymentDate BETWEEN '2003-05-20' AND '2003-06-05' AND checkNumber LIKE '%JM%';
# amount가 10000과 50000 사이이며, paymentDate가 '2003-05-20'와 '2003-06-05' 사이이고, checkNumber에 'JM'이 포함된 행을 선택합니다.
print(payments_df[(payments_df['amount'].between(10000, 50000)) & 
                  (payments_df['paymentDate'].between('2003-05-20', '2003-06-05')) & 
                  (payments_df['checkNumber'].str.contains('JM'))])

# SQL: SELECT * FROM offices WHERE country NOT IN ('USA', 'France', 'UK');
# country가 'USA', 'France', 'UK'가 아닌 행을 선택합니다.
print(offices_df[~offices_df['country'].isin(['USA', 'France', 'UK'])])

# SQL: SELECT 필드명 FROM 테이블명 WHERE 필드명에 관한 여러 조건식
# Pandas에서 조건에 따라 필드를 선택하는 예
# print(df[(조건식)])

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

-SQL

\-- ORDER BY 절, sort_values(), 정렬
SELECT *
FROM orders
ORDER BY orderNumber DESC -- 내림차순
;

SELECT *
FROM orders
ORDER BY orderNumber ASC -- 오름차순
;

SELECT customerNumber, orderNumber
FROM orders
ORDER BY customerNumber ASC -- 오름차순
;

SELECT customerNumber, orderNumber
FROM orders
ORDER BY 1 ASC, 2 DESC -- (첫번째 필드)오름차순 (두번째 필드)내림차순
;

/*
SELECT 필드명
FROM 테이블명
WHERE 필드명에 관한 여러 조건식
ORDER BY 필드값 기준 정렬
*/

 

-Pandas

# SQL: SELECT * FROM orders ORDER BY orderNumber DESC;
# orderNumber 기준으로 내림차순 정렬합니다.
print(orders_df.sort_values(by='orderNumber', ascending=False))

# SQL: SELECT * FROM orders ORDER BY orderNumber ASC;
# orderNumber 기준으로 오름차순 정렬합니다.
print(orders_df.sort_values(by='orderNumber', ascending=True))

# SQL: SELECT customerNumber, orderNumber FROM orders ORDER BY customerNumber ASC;
# customerNumber와 orderNumber 컬럼을 선택하고 customerNumber 기준으로 오름차순 정렬합니다.
print(orders_df[['customerNumber', 'orderNumber']].sort_values(by='customerNumber', ascending=True))

# SQL: SELECT customerNumber, orderNumber FROM orders ORDER BY 1 ASC, 2 DESC;
# customerNumber 기준으로 오름차순, orderNumber 기준으로 내림차순 정렬합니다.
print(orders_df[['customerNumber', 'orderNumber']].sort_values(by=['customerNumber', 'orderNumber'], ascending=[True, False]))

# 일반적인 예시: SELECT 필드명 FROM 테이블명 WHERE 필드명에 관한 여러 조건식 ORDER BY 필드값 기준 정렬
# 예를 들어, amount가 10000 이상인 행을 paymentDate 기준으로 오름차순 정렬합니다.
filtered_sorted_df = payments_df[payments_df['amount'] >= 10000].sort_values(by='paymentDate', ascending=True)
print(filtered_sorted_df)

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

-SQL

-- GROUP BY와 HAVING
SELECT * FROM orders;

SELECT
	DISTINCT status -- 중복값 제거
FROM orders
;

-- 집계함수
SELECT
	status
    , COUNT(*) AS "갯수"
FROM
	orders
GROUP BY
	status
HAVING COUNT(*) >= 5 -- 정석
ORDER BY 2 DESC
;

SELECT
	status
    , COUNT(*) AS "갯수"
FROM
	orders
GROUP BY
	status
HAVING 갯수 >= 5 -- DBMS마다 달라용,,, (Oracle, Ms-SQL,,,)
ORDER BY 2 DESC
;

SELECT
	country
    ,city
    , count(*)
FROM
	customers
GROUP BY country, city
;

 

-Pandas

# SQL: SELECT * FROM orders;
# orders 데이터프레임의 모든 데이터를 선택합니다.
print(orders_df)

# SQL: SELECT DISTINCT status FROM orders;
# status 컬럼의 중복값을 제거하여 선택합니다.
print(orders_df['status'].drop_duplicates())

# SQL: SELECT status, COUNT(*) AS "갯수" FROM orders GROUP BY status HAVING COUNT(*) >= 5 ORDER BY 2 DESC;
# status별로 그룹화하여 개수를 세고, 그 개수가 5 이상인 그룹을 내림차순으로 정렬합니다.
status_count = orders_df.groupby('status').size().reset_index(name='갯수')
status_count_filtered = status_count[status_count['갯수'] >= 5].sort_values(by='갯수', ascending=False)
print(status_count_filtered)

# SQL: SELECT status, COUNT(*) AS "갯수" FROM orders GROUP BY status HAVING 갯수 >= 5 ORDER BY 2 DESC;
# 위와 동일한 작업을 수행합니다.
print(status_count_filtered)

# SQL: SELECT country, city, count(*) FROM customers GROUP BY country, city;
# country와 city별로 그룹화하여 각 그룹의 개수를 셉니다.
country_city_count = customers_df.groupby(['country', 'city']).size().reset_index(name='count')
print(country_city_count)