本文概述
- 正确加入
- 左加入
我们的数据库中只有一个表, 让我们再创建一个表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()
输出
IDNameSalaryDept_IdDept_Name101John25000.0201CS102John25000.0201CS103David25000.0202IT104Nick90000.0201CS105Mike28000.0202IT
推荐阅读
- Python程序实现示例
- Python栈和队列用法图解
- Python OOPs概念介绍
- Python类和对象详细解析
- python|Python教程之粒子运动轨迹动态绘图
- python|YOLOv5添加注意力机制
- 算法技巧|python算法技巧——贪心算法练习及掌握
- python|python animation 轨迹_Python实例(自动轨迹绘制)
- 滑动轨迹|滑动轨迹 曲线 python_python曲线图