MySQL 连接两个表?
mysqlmysqli database
让我们首先创建两个表，并使用外键约束将它们连接起来。创建第一个表的查询如下−
mysql> create table ParentTable -> ( -> UniqueId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EmployeeName varchar(10) -> ); Query OK, 0 rows affected (0.56 sec)
使用 insert 命令在第一个表中插入一些记录。查询如下 −
mysql> insert into ParentTable(EmployeeName) values('John');
Query OK, 1 row affected (0.15 sec)
mysql> insert into ParentTable(EmployeeName) values('Carol');
Query OK, 1 row affected (0.32 sec)
mysql> insert into ParentTable(EmployeeName) values('Sam');
Query OK, 1 row affected (0.18 sec)
mysql> insert into ParentTable(EmployeeName) values('Bob');
Query OK, 1 row affected (0.19 sec)
现在，您可以使用 select 语句显示表中的所有记录。查询如下 −
mysql> select *from ParentTable;
以下是输出 −
+----------+--------------+ | UniqueId | EmployeeName | +----------+--------------+ | 1 | John | | 2 | Carol | | 3 | Sam | | 4 | Bob | +----------+--------------+ 4 rows in set (0.00 sec)
使用外键约束创建第二个表的查询如下 −
mysql> create table ChildTable -> ( -> UniqueId int NOT NULL PRIMARY KEY, -> EmployeeAddress varchar(100), -> CONSTRAINT fk_uniqueId FOREIGN KEY(UniqueId) references ParentTable(UniqueId) -> ); Query OK, 0 rows impacted (0.54 sec)
现在使用 insert 命令在第二个表中插入一些记录。查询如下−
mysql> insert into ChildTable values(1,'15 West Shady Lane Starkville, MS 39759'); Query OK, 1 row affected (0.19 sec) mysql> insert into ChildTable values(2,'72 West Rock Creek St. Oxford, MS 38655'); Query OK, 1 row affected (0.18 sec) mysql> insert into ChildTable(UniqueId) values(3); Query OK, 1 row affected (0.41 sec) mysql> insert into ChildTable values(4,'119 North Sierra St. Marysville, OH 43040'); Query OK, 1 row affected (0.16 sec)
使用 select 语句显示表中的所有记录。查询如下 −
mysql> select *from ChildTable;
以下是输出 −
+----------+-------------------------------------------+ | UniqueId | EmployeeAddress | +----------+-------------------------------------------+ | 1 | 15 West Shady Lane Starkville, MS 39759 | | 2 | 72 West Rock Creek St. Oxford, MS 38655 | | 3 | NULL | | 4 | 119 North Sierra St. Marysville, OH 43040 | +----------+-------------------------------------------+ 4 rows in set (0.00 sec)
现在让我们使用左连接来连接表格。查询如下 −
mysql> select ParentTable.UniqueId,ParentTable.EmployeeName,ChildTable.EmployeeAddress from ParentTable left join -> ChildTable on ParentTable.UniqueId=ChildTable.UniqueId;
以下是输出 −
+----------+--------------+-------------------------------------------+ | UniqueId | EmployeeName | EmployeeAddress | +----------+--------------+-------------------------------------------+ | 1 | John | 15 West Shady Lane Starkville, MS 39759 | | 2 | Carol | 72 West Rock Creek St. Oxford, MS 38655 | | 3 | Sam | NULL | | 4 | Bob | 119 North Sierra St. Marysville, OH 43040 | +----------+--------------+-------------------------------------------+ 4 rows in set (0.00 sec)
