无涯教程可以通过使用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脚本,该脚本将共同列上的两个表连接起来,即dept_id。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #加入departments_id 上的两个表 cur.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("ID Name Salary Dept_Id Dept_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()
输出:
ID Name Salary Dept_Id Dept_Name 101 John 25000 201 CS 102 John 25000 201 CS 103 Learnfk 25000 202 IT 104 Nick 90000 201 CS 105 Mike 28000 202 IT
右联接显示右侧表的所有列,因为在数据库PythonDB中有两个表,即Departments和Employee。表中没有任何不在任何部门工作的Employee(部门ID为null的雇员)。但是,要了解正确连接的概念,无涯教程来创建一个。
链接:https://www.learnfk.comhttps://www.learnfk.com/python3/python-mysql-join-operation.html
来源:LearnFk无涯教程网
在MySQL服务器上执行以下查询。
insert into Employee(name, id, salary, branch_name) values ("Alex",108,29900,"Mumbai");
这将插入一个不在任何部门工作的员工Alex(部门ID为null)。
现在,在Employee表中有一个雇员,其Department ID在Departments表中不存在。现在在两个表上执行正确的联接。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #加入departments_id 上的两个表 result = 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("ID Name Salary Dept_Id Dept_Name") for row in cur: print(row[0]," ", row[1]," ",row[2]," ",row[3]," ",row[4]) except: myconn.rollback() myconn.close()
输出:
ID Name Salary Dept_Id Dept_Name 101 John 25000.0 201 CS 102 John 25000.0 201 CS 103 Learnfk 25000.0 202 IT 104 Nick 90000.0 201 CS 105 Mike 28000.0 202 IT 108 Alex 29900.0 None None
左联接涵盖了左侧表中的所有数据。它与正确的连接正好相反。考虑以下示例。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #加入departments_id 上的两个表 result = 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("ID Name Salary Dept_Id Dept_Name") for row in cur: print(row[0]," ", row[1]," ",row[2]," ",row[3]," ",row[4]) except: myconn.rollback() myconn.close()
输出:
ID Name Salary Dept_Id Dept_Name 101 John 25000.0 201 CS 102 John 25000.0 201 CS 103 Learnfk 25000.0 202 IT 104 Nick 90000.0 201 CS 105 Mike 28000.0 202 IT
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)