让我们首先创建两个表,并用外键约束将它们联接起来。创建第一个表的查询如下-mysql> create table ParentTable
-> (
-> UniqueId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> EmployeeName varchar(10)
-> );
使用insert命令在第一个表中插入一些记录。查询如下-mysql> insert into ParentTable(EmployeeName) values('John');
mysql> insert into ParentTable(EmployeeName) values('Carol');
mysql> insert into ParentTable(EmployeeName) values('Sam');
mysql> insert into ParentTable(EmployeeName) values('Bob');
现在,您可以使用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)
-> );
现在,使用insert命令在第二个表中插入一些记录。查询如下-mysql> insert into ChildTable values(1,'15 West Shady Lane Starkville, MS 39759');
mysql> insert into ChildTable values(2,'72 West Rock Creek St. Oxford, MS 38655');
mysql> insert into ChildTable(UniqueId) values(3);
mysql> insert into ChildTable values(4,'119 North Sierra St. Marysville, OH 43040');
使用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)