SQL学习笔记（六）主键与外键

准备

为了介绍 主键（Primary Keys） 与外键 （Foreign Keys） ，我们再新建一个数据库。

sqlite3 movies.db
# SQLite version 3.43.1 2023-09-11 12:01:27
# Enter ".help" for usage hints.
# sqlite>

假如movies.db不存在，SQLite会自动创建它。

我用ChatGPT又生成了一些数据（不确定有没有错误）。

movies.csv:（两列分别代表id和title）

1,The Dark Knight
2,Inception
3,The Shawshank Redemption
4,The Godfather
5,The Godfather: Part II

......

stars.csv:（两列分别代表id和name）

1,Christian Bale
2,Heath Ledger
3,Aaron Eckhart
4,Michael Caine
5,Gary Oldman

......

movies_stars.csv:（两列分别代表movie_id和star_id）

1,1
1,2
1,3
1,4
1,5

......

下载这三个文件

创建三个相应的表格，并载入数据：

CREATE TABLE movies (
  'id' INTEGER PRIMARY KEY,
  'title' TEXT
);
CREATE TABLE stars (
  'id' INTEGER PRIMARY KEY,
  'name' TEXT
);
CREATE TABLE movies_stars (
  'movie_id' INTEGER,
  'star_id' INTEGER,
  FOREIGN KEY('movie_id') REFERENCES movies('id'),
  FOREIGN KEY('star_id') REFERENCES stars('id')
);
.mode csv
.import movies.csv movies
.import stars.csv stars
.import movies_stars.csv movies_stars
.mode box

主键和外键

我们这次的数据里，每个电影都对应了多名主演，而每个演员也可能出演多部电影，所以我们使用一张movies_stars表格来记录电影和主演之间的关系，而不是直接放在同一个表格的另一列中。

主键是一种约束，一个表只能有一列主键，每个主键唯一，且不可以为空。'id' INTEGER PRIMARY KEY会将id设置为主键。

外键可以连接其他的表的主键，设置外键的约束可以防止插入外部表中不存在的主键。按以下格式：

CREATE TABLE movies_stars (
  ......,
  FOREIGN KEY(外键) REFERENCES 外部表(外部表的主键)
);

JOIN

查看movies_stars，发现它实际上只有两列数字id，如果我们想看看这些数字id代表那哪电影或哪名演员，就要使用JOIN合并两张表。

1	SELECT 列名, ... FROM 表1 JOIN 表2 ON 条件;

按照上面的格式，有：

1	SELECT * FROM movies_stars JOIN stars ON movies_stars.star_id = stars.id LIMIT 5;

输出如下结果：

┌──────────┬─────────┬────┬────────────────┐
│ movie_id │ star_id │ id │      name      │
├──────────┼─────────┼────┼────────────────┤
│ 1        │ 1       │ 1  │ Christian Bale │
│ 1        │ 2       │ 2  │ Heath Ledger   │
│ 1        │ 3       │ 3  │ Aaron Eckhart  │
│ 1        │ 4       │ 4  │ Michael Caine  │
│ 1        │ 5       │ 5  │ Gary Oldman    │
└──────────┴─────────┴────┴────────────────┘

比如我们想知道Morgan Freeman和Tom Hardy主演了哪些电影，我们可以使用两次JOIN:

SELECT title, name FROM movies_stars
JOIN stars ON movies_stars.star_id = stars.id
JOIN movies ON movies_stars.movie_id = movies.id
WHERE name = 'Morgan Freeman'
OR name = 'Tom Hardy'
ORDER BY name;

得到以下结果：

┌──────────────────────────┬────────────────┐
│          title           │      name      │
├──────────────────────────┼────────────────┤
│ The Shawshank Redemption │ Morgan Freeman │
│ Se7en                    │ Morgan Freeman │
│ The Dark Knight Rises    │ Morgan Freeman │
│ Inception                │ Tom Hardy      │
│ The Dark Knight Rises    │ Tom Hardy      │
└──────────────────────────┴────────────────┘

还有一种写法，效果是一样的：

SELECT title, name FROM movies_stars, stars, movies
WHERE movies_stars.star_id = stars.id
AND movies_stars.movie_id = movies.id
AND name IN ('Morgan Freeman', 'Tom Hardy')
ORDER BY name;