JOIN 활용 실제 예제1
sakila 데이터베이스에서 실습¶
In [97]:
db.close() # 이전에 연결된 데이터베이스 끊어주세요 (에러나면 이미 끊어진 것임)
In [98]:
host_name = "0.0.0.0"
username = "root"
password = "korea123"
database_name = "sakila" # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
In [99]:
db = pymysql.connect(
host=host_name, # DATABASE_HOST
port=3306,
user=username, # DATABASE_USERNAME
passwd=password, # DATABASE_PASSWORD
db=database_name, # DATABASE_NAME
charset='utf8'
)
- "Comedy", "Sports", "Family" 각 카테고리 ID만 출력해보세요
In [100]:
category_df = pd.read_sql("SELECT * FROM category;", db)
In [101]:
category_df
Out[101]:
In [106]:
SQL = """
SELECT name, category_id FROM category
WHERE name = 'Comedy' OR name = 'Sports' OR name = 'Family'
"""
category_df = pd.read_sql(SQL, db)
In [107]:
category_df
Out[107]:
- 영화 아이디가 2 인 영화의 카테고리 ID는?
In [108]:
film_category_df = pd.read_sql("SELECT * FROM film_category;", db)
In [109]:
film_category_df.head()
Out[109]:
In [110]:
SQL = """
SELECT category_id FROM film_category WHERE film_id = 2;
"""
film_category_df = pd.read_sql(SQL, db)
In [111]:
film_category_df
Out[111]:
- 카테고리 ID별 영화 수는?
In [112]:
SQL_QUERY = """
SELECT COUNT(*) FROM film_category GROUP BY category_id;
"""
In [113]:
category_film_count_df = pd.read_sql(SQL_QUERY, db)
In [114]:
category_film_count_df
Out[114]:
- category 테이블에는 카테고리 이름과 category_id, film_category 테이블에는 category_id와 각 영화 id가 있음
JOIN 시작: 카테고리가 Comedy인 영화를 모두 찾아보자.¶
In [115]:
SQL_QUERY = """
SELECT *
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy'
"""
In [ ]:
pd.read_sql(SQL_QUERY, db)
- 카테고리가 Comedy인 영화 수를 알아보자.
In [117]:
SQL_QUERY = """
SELECT COUNT(*)
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy'
"""
In [118]:
pd.read_sql(SQL_QUERY, db)
Out[118]:
- 카테고리가 Comedy, Sports, Family인 영화 수를 각각 알아보자.
In [119]:
SQL_QUERY = """
SELECT category.name, COUNT(*)
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy' OR category.name = 'Sports' OR category.name = 'Family'
GROUP BY category.category_id
"""
In [120]:
pd.read_sql(SQL_QUERY, db)
Out[120]:
- 카테고리가 Comedy, Sports, Family인 영화 수와 카테고리명을 각각 출력해보자.
In [121]:
SQL_QUERY = """
SELECT category.name, COUNT(*)
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy' OR category.name = 'Sports' OR category.name = 'Family'
GROUP BY category.category_id
"""
In [122]:
pd.read_sql(SQL_QUERY, db)
Out[122]:
- 카테고리가 Comedy, Sports, Family인 영화 수와 카테고리명을 각각 출력해보자(카테고리명을 이해하기 쉽게 category_film_count로 필드명을 출력하자)
In [123]:
SQL_QUERY = """
SELECT category.name, COUNT(*) AS category_film_count
FROM film_category
JOIN category
ON category.category_id = film_category.category_id
WHERE category.name = 'Comedy' OR category.name = 'Sports' OR category.name = 'Family'
GROUP BY category.category_id
"""
In [124]:
pd.read_sql(SQL_QUERY, db)
Out[124]: