Python高级（MySQL联接操作实现细节）

本文概述

正确加入
左加入

我们可以通过使用join语句在两个或多个表中使用一些公共列来合并这些列。
我们的数据库中只有一个表, 让我们再创建一个表Departments, 其中两列为department_id和department_name。

create table Departments (Dept_id int(20) primary key not null, Dept_Name varchar(20) not null);

文章图片
如上图所示, 我们创建了一个新的表Departments。但是, 我们尚未在其中插入任何值。
让我们插入一些Departments ID和Departments名称, 以便我们可以将其映射到Employee表。

insert into Departments values (201, "CS"); insert into Departments values (202, "IT");

让我们看一下插入每个表中的值。考虑下图。

文章图片
【Python高级（MySQL联接操作实现细节）】现在, 让我们创建一个Python脚本, 该脚本将共同列上的两个表即dept_id连接起来。
例子

import mysql.connector#Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")#creating the cursor objectcur = myconn.cursor()try:#joining the two tables on departments_idcur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments join Employee on Departments.Dept_id = Employee.Dept_id")print("IDNameSalaryDept_IdDept_Name")for row in cur:print("%d%s%d%d%s"%(row[0], row[1], row[2], row[3], row[4]))except:myconn.rollback()myconn.close()

输出

IDNameSalaryDept_IdDept_Name101John25000201CS102John25000201CS103David25000202IT104Nick90000201CS105Mike28000202IT

正确加入右联接显示右侧表的所有列, 因为我们在数据库PythonDB中有两个表, 即Departments和Employee。表中没有任何不在任何部门工作的Employee(部门ID为null的雇员)。但是, 要了解正确连接的概念, 我们来创建一个。
在MySQL服务器上执行以下查询。

insert into Employee(name, id, salary, branch_name) values ("Alex", 108, 29900, "Mumbai");

这将插入一个不在任何部门工作的员工Alex(部门ID为null)。
现在, 我们在Employee表中有一个雇员, 而Departments表中没有该部门的ID。现在让我们在两个表上执行正确的联接。
例子

import mysql.connector#Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")#creating the cursor objectcur = myconn.cursor()try:#joining the two tables on departments_idresult = cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments right join Employee on Departments.Dept_id = Employee.Dept_id") print("IDNameSalaryDept_IdDept_Name") for row in cur:print(row[0], "", row[1], "", row[2], "", row[3], "", row[4])except:myconn.rollback()myconn.close()

输出

IDNameSalaryDept_IdDept_Name101John25000.0201CS102John25000.0201CS103David25000.0202IT104Nick90000.0201CS105Mike28000.0202IT108Alex29900.0NoneNone

左加入左联接涵盖了左侧表中的所有数据。它与正确的连接正好相反。考虑以下示例。
例子

import mysql.connector#Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")#creating the cursor objectcur = myconn.cursor()try:#joining the two tables on departments_idresult = cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments left join Employee on Departments.Dept_id = Employee.Dept_id")print("IDNameSalaryDept_IdDept_Name")for row in cur:print(row[0], "", row[1], "", row[2], "", row[3], "", row[4])except:myconn.rollback()myconn.close()

输出