SQL联接

如何在两个数据库表之间执行简单的联接

联接是一个非常强大的工具。还记得数据库介绍模块中的关系代数吗?

联接是应用关系代数

假设您有2张桌子,peoplecars

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE cars ( brand CHAR(20) NOT NULL, model CHAR(20) NOT NULL, owner CHAR(20) NOT NULL PRIMARY KEY );

我们添加一些数据:

INSERT INTO people VALUES (37, 'Flavio');
INSERT INTO people VALUES (8, 'Roger');
INSERT INTO cars VALUES ('Ford', 'Fiesta', 'Flavio');
INSERT INTO cars VALUES ('Ford', 'Mustang', 'Roger');

现在,我们要关联这两个表,因为警察阻止了Roger的驾驶,看起来很年轻,并且想从他们的数据库中知道他的年龄。

罗杰是我的狗,但让我们假设狗可以开车。

我们可以创建一个加入使用以下语法:

SELECT age FROM people JOIN cars ON people.name = cars.owner WHERE cars.model='Mustang';

我们将获得以下结果:

 age 
-----
   8

What is happening? We are joining the two tables cars on two specific columns: name from the people table, and owner from the cars table.

Joins are a topic that can grow in complexity because there are many different kind of joins that you can use to do fancier things with multiple tables, but here is the most basic example.


More database tutorials: