连接表

SQL Server 入门

John MacKintosh

Instructor

关系型数据库

主键

主键：唯一标识表中的每一行

+-----------+-------------------+
| artist_id | name              | 
|-----------+-------------------|
| 1         | AC/DC             |
| 2         | Accept            |
| 3         | Aerosmith         |
| 4         | Alanis Morissette |
| 5         | Alice In Chains   |
+-----------+-------------------+

主键：artist_id

+----------+-------------------------+-----------+
| album_id | title                   | artist_id |  
|----------+-------------------------+-----------|
| 1        | For Those About To Rock | 1         |
| 2        | Balls to the Wall       | 2         |
| 3        | Restless and Wild       | 2         |
| 4        | Let There Be Rock       | 1         |
| 5        | Big Ones                | 3         |
+----------+-------------------------+-----------+

主键：album_id
那 artist_id 呢？

外键

artist 表

+-----------+-------------------+
| artist_id | name              | 
|-----------+-------------------|
| 1         | AC/DC             |
| 2         | Accept            |
| 3         | Aerosmith         |
| 4         | Alanis Morissette |
| 5         | Alice In Chains   |
+-----------+-------------------+

album 表

+----------+-------------------------+-----------+
| album_id | title                   | artist_id |  
|----------+-------------------------+-----------|
| 1        | For Those About To Rock | 1         |
| 2        | Balls to the Wall       | 2         |
| 3        | Restless and Wild       | 2         |
| 4        | Let There Be Rock       | 1         |
| 5        | Big Ones                | 3         |
+----------+-------------------------+-----------+

artist_id：指向 artist 的外键

连接 album 与 artist

artist 表

+-----------+-------------------+
| artist_id | name              | 
|-----------+-------------------|
| 1         | AC/DC             |
| 2         | Accept            |
| 3         | Aerosmith         |
| 4         | Alanis Morissette |
| 5         | Alice In Chains   |
+-----------+-------------------+

AC/DC 的 artist_id = 1

album 表

+----------+-------------------------+-----------+
| album_id | title                   | artist_id |  
|----------+-------------------------+-----------|
| 1        | For Those About To Rock | 1         |
| 2        | Balls to the Wall       | 2         |
| 3        | Restless and Wild       | 2         |
| 4        | Let There Be Rock       | 1         |
| 5        | Big Ones                | 3         |
+----------+-------------------------+-----------+

第 1 和 4 行的 artist_id = 1

连接 album 与 artist

+----------+-------------------------+-----------+-------------+
| album_id | title                   | artist_id | artist_name | 
|----------+---------------------------------------+-----------|
| 1        | For Those About To Rock | 1         | AC/DC       |
| 4        | Let There Be Rock       | 1         | AC/DC       |
+----------+-------------------------+-----------+-------------|

返回 album 表中的专辑信息
返回匹配的 artist 表中的艺人信息
通过 artist_id 列连接

INNER JOIN（内连接）

SELECT 
  album_id, 
  title, 
  album.artist_id, 
  name AS artist_name 
FROM album 
INNER JOIN artist ON artist.artist_id = album.artist_id 
WHERE album.artist_id = 1;

+----------+-------------------------+-----------+-------------+
| album_id | title                   | artist_id | artist_name | 
|----------+---------------------------------------+-----------|
| 1        | For Those About To Rock | 1         | AC/DC       |
| 4        | Let There Be Rock       | 1         | AC/DC       |
+----------+-------------------------+-----------+-------------+

INNER JOIN 语法

SELECT 
  table_A.columnX, 
  table_A.columnY, 
  table_B.columnZ 
FROM table_A 
INNER JOIN table_B ON table_A.foreign_key = table_B.primary_key;

SELECT 
  album_id, 
  title, 
  album.artist_id, 
  name AS artist_name 
FROM album 
INNER JOIN artist on artist.artist_id = album.artist_id;

+----------+---------------------------------------+-----------+
| album_id | title                   | artist_id | artist_name | 
|----------+---------------------------------------+-----------|
| 1        | For Those About To Rock | 1         | AC/DC       |
| 4        | Let There Be Rock       | 1         | AC/DC       |
| 2        | Balls To The Wall       | 2         | Accept      |
| 3        | Restless and Wild       | 2         | Accept      |
+----------+---------------------------------------+-----------+

返回 album 与 artist 之间的所有匹配组合

多个 INNER JOIN

SELECT 
  table_A.columnX, 
  table_A.columnY, 
  table_B.columnZ, table_C columnW 
FROM table_A 
INNER JOIN table_B ON table_B.foreign_key = table_A.primary_key 
INNER JOIN table_C ON table_C.foreign_key = table_B.primary_key;

让我们连接一些表吧！

SQL Server 入门