第1章 Python简介我们可以使用join语句通过使用其中的一些公共列来组合两个或多个表中的列。
我们的数据库中只有一个表，让我们再创建一个包含两列department_id和department_name的表Departments。
create table Departments (Dept_id int(20) primary key not null, Dept_Name varchar(20) not null);
连接操作
正如我们创建了一个新表Departments，如上图所示。但是，我们还没有在其中插入任何值。
让我们插入一些Departments ID和部门名称，以便我们可以将其映射到Employee表。
insert into Departments values (201, "CS"); insert into Departments values (202, "IT");
让我们看一下每个表中插入的值。请考虑以下图像。
现在，让我们创建一个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 object
cur = myconn.cursor()
try:
#joining the two tables on 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 David 25000 202 IT 104 Nick 90000 201 CS 105 Mike 28000 202 IT
右连接
右连接显示右侧表的所有列，因为我们在数据库PythonDB中有两个表，即Departments和Employee。表中没有任何不在任何部门工作的员工（部门ID为null的员工）。但是，为了理解右连接的概念，让我们创建一个。
在MySQL服务器上执行以下查询。
insert into Employee(name, id, salary, branch_name) values ("Alex",108,29900,"Mumbai");这将插入一个不适用于任何部门的员工Alex（部门ID为空）。
现在，我们在Employee表中有一个员工，其部门ID不在Departments表中。现在让我们在两个表上执行正确的连接。
例
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#joining the two tables on 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 David 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
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#joining the two tables on 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 David 25000.0 202 IT 104 Nick 90000.0 201 CS 105 Mike 28000.0 202 IT