mysql join left

mysql -u root -p

비밀번호

show databases;

 

+--------------------+
| Database           |
+--------------------+
| daily_buy_list     |
| daily_craw         |
| information_schema |
| jackbot1_imi1      |
| management         |
| min_craw           |
| mysql              |
| opentutorials      |
| performance_schema |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+

use sakila;

 

show tables;

 

+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+

select * from city limit 3;

 

+---------+--------------------+------------+---------------------+
| city_id | city               | country_id | last_update         |
+---------+--------------------+------------+---------------------+
|       1 | A Corua (La Corua) |         87 | 2006-02-15 04:45:25 |
|       2 | Abha               |         82 | 2006-02-15 04:45:25 |
|       3 | Abu Dhabi          |        101 | 2006-02-15 04:45:25 |
+---------+--------------------+------------+---------------------+

select * from country limit 3;

 

+------------+----------------+---------------------+
| country_id | country        | last_update         |
+------------+----------------+---------------------+
|          1 | Afghanistan    | 2006-02-15 04:44:00 |
|          2 | Algeria        | 2006-02-15 04:44:00 |
|          3 | American Samoa | 2006-02-15 04:44:00 |
+------------+----------------+---------------------+

select city, country_id from city limit 3;

 

+--------------------+------------+
| city               | country_id |
+--------------------+------------+
| A Corua (La Corua) |         87 |
| Abha               |         82 |
| Abu Dhabi          |        101 |
+--------------------+------------+

 

테이블 city.country_id

테이블 country.country_id 

left join

select city.city, country.country from city left join country on city.country_id = country.country_id limit 3;

 

+--------------------+----------------------+
| city               | country              |
+--------------------+----------------------+
| A Corua (La Corua) | Spain                |
| Abha               | Saudi Arabia         |
| Abu Dhabi          | United Arab Emirates |
+--------------------+----------------------+

 

select address_id,addresss,district,city_id from address limit 3;

 

+------------+--------------------+----------+---------+
| address_id | address            | district | city_id |
+------------+--------------------+----------+---------+
|          1 | 47 MySakila Drive  | Alberta  |     300 |
|          2 | 28 MySQL Boulevard | QLD      |     576 |
|          3 | 23 Workhaven Lane  | Alberta  |     300 |
+------------+--------------------+----------+---------+

SELECT 
    address.address_id,
    address.address,
    address.district,
    city.city,
    country.country
FROM 
    address
INNER JOIN 
    city ON address.city_id = city.city_id
INNER JOIN 
    country ON city.country_id = country.country_id;

 

 

mysql_sakila_002.py

import sys
from PySide6.QtCore import QSize, Qt
from PySide6.QtSql import QSqlQuery
from PySide6.QtWidgets import QApplication, QLineEdit, QMainWindow, QTableView, QVBoxLayout, QWidget
from PySide6.QtGui import QStandardItemModel, QStandardItem

import pymysql

class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        container = QWidget()
        layout = QVBoxLayout()

        self.search = QLineEdit()
        self.search.textChanged.connect(self.update_filter)
        self.table = QTableView()

        layout.addWidget(self.search)
        layout.addWidget(self.table)
        container.setLayout(layout)

        self.model = None
        self.setup_model()

        self.setMinimumSize(QSize(1024, 600))
        self.setCentralWidget(container)

    def setup_model(self):
        self.model = QStandardItemModel()
        self.table.setModel(self.model)
        self.update_filter('')

    def update_filter(self, text):
        filter_str = f'%{text}%'
        sql_query = """
            SELECT 
                address.address_id, 
                address.address, 
                address.district, 
                city.city, 
                country.country 
            FROM 
                address 
            INNER JOIN 
                city ON address.city_id = city.city_id 
            INNER JOIN 
                country ON city.country_id = country.country_id 
            WHERE 
                address.address LIKE %s
        """
        # print("SQL Query:", sql_query)  # 쿼리 확인

        # MySQL 연결 및 쿼리 실행
        db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
        cursor = db.cursor()
        cursor.execute(sql_query, (filter_str,))
        # print("Query executed successfully!")  # 쿼리 실행 확인

        # Clear previous data
        self.model.clear()

        # Set header
        column_headers = ["Address ID", "Address", "District", "City", "Country"]
        self.model.setColumnCount(len(column_headers))
        self.model.setHorizontalHeaderLabels(column_headers)

        # Add data to the model
        for row_data in cursor:
            row = [QStandardItem(str(item)) for item in row_data]
            self.model.appendRow(row)
        # print("Data added to the model successfully!")  # 모델 데이터 확인

app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())