# Building a Mario Database
List of relations
+--------+-----------------------------+----------+--------------+
| Schema | Name | Type | Owner |
+--------+-----------------------------+----------+--------------+
| public | actions | table | freecodecamp |
| public | actions_action_id_seq | sequence | freecodecamp |
| public | character_actions | table | freecodecamp |
| public | characters | table | freecodecamp |
| public | characters_character_id_seq | sequence | freecodecamp |
| public | more_info | table | freecodecamp |
| public | more_info_more_info_id_seq | sequence | freecodecamp |
| public | sounds | table | freecodecamp |
| public | sounds_sound_id_seq | sequence | freecodecamp |
+--------
# Create
# 基本信息表
- 创建表
-- 语法
create table <table_name>;
-- 示例
create table characters();
- 添加列
-- 语法
alter table <table_name> add column <column_name> datatype constraint;
-- 示例
alter table characters add column character_id INT NOT NULL;
alter table characters add column name varchar(30) NOT NULL;
alter table characters add column homeland varchar(60);
alter table characters add column favorite_color varchar(30);
- 设置主键
-- 语法
alter table <table_name> add primary key (<column_name>)
-- 示例
alter table characters add primary key (character_id)
\d characters;
- 查看
+----------------+-----------------------+-----------+----------+--------------------------------------------------+
| Column | Type | Collation | Nullable | Default |
+----------------+-----------------------+-----------+----------+--------------------------------------------------+
| character_id | integer | | not null | nextval('characters_character_id_seq'::regclass) |
| name | character varying(30) | | not null | |
| homeland | character varying(60) | | | |
| favorite_color | character varying(30) | | | |
+----------------+-----------------------+-----------+----------+--------------------------------------------------+
Indexes:
"characters_pkey" PRIMARY KEY, btree (character_id)
主键和索引
其实主键和索引都是键，不过主键是逻辑键，索引是物理键，意思就是主键不实际存在，而索引实际存在在数据库中，主键一般都要建，主要是用来避免一张表中有相同的记录，索引一般可以不建，但如果需要对该表进行查询操作，则最好建，这样可以加快检索的速度。
# 更多信息表
create table more_info();
alter table more_info add column more_info_id INT NOT NULL;
alter table more_info add column birthday DATE;
alter table more_info add column height INT;
alter table more_info add column weight NUMERIC(4,1);
alter table more_info rename height to height_in_cm;
alter table more_info rename weight to weight_in_kg;
-- 主键
alter table more_info add primary key (more_info_id);
-- 外键
alter table more_info add foreign key (character_id) references characters (character_id);
\d more_info;
+--------------+--------------+-----------+----------+-------------------------------------------------+
| Column | Type | Collation | Nullable | Default |
+--------------+--------------+-----------+----------+-------------------------------------------------+
| more_info_id | integer | | not null | nextval('more_info_more_info_id_seq'::regclass) |
| birthday | date | | | |
| height_in_cm | integer | | | |
| weight_in_kg | numeric(4,1) | | | |
| character_id | integer | | not null | |
+--------------+--------------+-----------+----------+-------------------------------------------------+
Indexes:
"more_info_pkey" PRIMARY KEY, btree (more_info_id)
"more_info_character_id_key" UNIQUE CONSTRAINT, btree (character_id)
# INSERT
# 添加单行
-- 添加单行，单个记录
insert into characters
(name, homeland, favorite_color)
values
('Mario', 'Mushroom Kingdom', 'Red');
# 添加多行
-- 添加多行，多个记录
insert into characters
(name, homeland, favorite_color)
values
('Luigi', 'Mushroom Kingdom', 'Green'),
('Peach', 'Mushroom Kingdom', 'Pink'),
('Toad', 'Mushroom Kingdom', 'Blue'),
('Bower', 'Koopa Kingdom', 'Yellow'),
('Daisy', 'Sarasaland', 'Orange'),
('Yoshi', 'Dinosaur Land', 'Red');
# SELECT
select *
FROM characters
ORDER BY character_id asc;
+--------------+--------+------------------+----------------+
| character_id | name | homeland | favorite_color |
+--------------+--------+------------------+----------------+
| 1 | Mario | Mushroom Kingdom | Red |
| 2 | Luigi | Mushroom Kingdom | Green |
| 3 | Peach | Mushroom Kingdom | Pink |
| 4 | Toad | Mushroom Kingdom | Blue |
| 5 | Bowser | Koopa Kingdom | Yellow |
| 6 | Daisy | Sarasaland | Orange |
| 7 | Yoshi | Dinosaur Land | Green |
+--------------+--------+------------------+----------------+
(7 rows)
SELECT *
FROM more_info
order by more_info_id asc;
+--------------+------------+--------------+--------------+--------------+
| more_info_id | birthday | height_in_cm | weight_in_kg | character_id |
+--------------+------------+--------------+--------------+--------------+
| 1 | 1981-07-09 | 155 | 64.5 | 1 |
| 2 | 1983-07-14 | 175 | 48.8 | 2 |
| 3 | 1985-10-18 | 173 | 52.2 | 3 |
| 4 | 1950-01-10 | 66 | 35.6 | 4 |
| 5 | 1990-10-29 | 258 | 300.0 | 5 |
| 6 | 1989-07-31 | | | 6 |
| 7 | 1990-04-13 | 162 | 59.1 | 7 |
+--------------+------------+--------------+--------------+--------------+
(7 rows)
# JOIN
# FULL JOIN
SELECT characters.character_id, name, homeland, favorite_color, birthday, height_in_cm, weight_in_kg
FROM characters
FULL JOIN more_info
ON characters.character_id = more_info.character_id
ORDER BY characters.character_id;
+--------------+--------+------------------+----------------+------------+--------------+--------------+
| character_id | name | homeland | favorite_color | birthday | height_in_cm | weight_in_kg |
+--------------+--------+------------------+----------------+------------+--------------+--------------+
| 1 | Mario | Mushroom Kingdom | Red | 1981-07-09 | 155 | 64.5 |
| 2 | Luigi | Mushroom Kingdom | Green | 1983-07-14 | 175 | 48.8 |
| 3 | Peach | Mushroom Kingdom | Pink | 1985-10-18 | 173 | 52.2 |
| 4 | Toad | Mushroom Kingdom | Blue | 1950-01-10 | 66 | 35.6 |
| 5 | Bowser | Koopa Kingdom | Yellow | 1990-10-29 | 258 | 300.0 |
| 6 | Daisy | Sarasaland | Orange | 1989-07-31 | | |
| 7 | Yoshi | Dinosaur Land | Green | 1990-04-13 | 162 | 59.1 |
+--------------+--------+------------------+----------------+------------+--------------+--------------+
(7 rows)
# FULL JOIN2
SELECT * FROM sounds;
+----------+--------------+--------------+
| sound_id | filename | character_id |
+----------+--------------+--------------+
| 1 | its-a-me.wav | 1 |
| 2 | yippee.wav | 1 |
| 3 | ha-ha.wav | 2 |
| 4 | oh-yeah.wav | 2 |
| 5 | yay.wav | 3 |
| 6 | woo-hoo.wav | 3 |
| 7 | mm-hmm.wav | 3 |
| 8 | yahoo.wav | 1 |
+----------+--------------+--------------+
(8 rows)
SELECT characters.character_id, name, homeland, favorite_color, filename
FROM characters
FULL JOIN sounds
ON characters.character_id = sounds.character_id
ORDER BY characters.character_id;
+--------------+--------+------------------+----------------+--------------+
| character_id | name | homeland | favorite_color | filename |
+--------------+--------+------------------+----------------+--------------+
| 1 | Mario | Mushroom Kingdom | Red | its-a-me.wav |
| 1 | Mario | Mushroom Kingdom | Red | yahoo.wav |
| 1 | Mario | Mushroom Kingdom | Red | yippee.wav |
| 2 | Luigi | Mushroom Kingdom | Green | ha-ha.wav |
| 2 | Luigi | Mushroom Kingdom | Green | oh-yeah.wav |
| 3 | Peach | Mushroom Kingdom | Pink | mm-hmm.wav |
| 3 | Peach | Mushroom Kingdom | Pink | yay.wav |
| 3 | Peach | Mushroom Kingdom | Pink | woo-hoo.wav |
| 4 | Toad | Mushroom Kingdom | Blue | |
| 5 | Bowser | Koopa Kingdom | Yellow | |
| 6 | Daisy | Sarasaland | Orange | |
| 7 | Yoshi | Dinosaur Land | Green | |
+--------------+--------+------------------+----------------+--------------+
(12 rows)
# 连续 JOIN
SELECT * FROM actions;
+-----------+--------+
| action_id | action |
+-----------+--------+
| 1 | run |
| 2 | jump |
| 3 | duck |
+-----------+--------+
(3 rows)
SELECT *
FROM character_actions
ORDER BY
character_id ASC,
action_id ASC;
+--------------+-----------+
| character_id | action_id |
+--------------+-----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
| 5 | 1 |
| 5 | 2 |
| 5 | 3 |
| 6 | 1 |
| 6 | 2 |
| 6 | 3 |
| 7 | 1 |
| 7 | 2 |
| 7 | 3 |
+--------------+-----------+
(21 rows)
Use the character_id column to join character_actions and characters
Use the action_id column to join character_actions and actions
Without the keywords, it looks like this: character_actions characters character_actions.character_id = characters.character_id actions character_actions.action_id = actions.action_id;
SELECT characters.character_id, name, homeland, favorite_color, action
FROM characters
FULL JOIN character_actions
ON characters.character_id = character_actions.character_id
FULL JOIN actions
ON actions.action_id = character_actions.action_id
ORDER BY
characters.character_id ASC,
actions.action ASC;
+--------------+--------+------------------+----------------+--------+
| character_id | name | homeland | favorite_color | action |
+--------------+--------+------------------+----------------+--------+
| 1 | Mario | Mushroom Kingdom | Red | duck |
| 1 | Mario | Mushroom Kingdom | Red | jump |
| 1 | Mario | Mushroom Kingdom | Red | run |
| 2 | Luigi | Mushroom Kingdom | Green | duck |
| 2 | Luigi | Mushroom Kingdom | Green | jump |
| 2 | Luigi | Mushroom Kingdom | Green | run |
| 3 | Peach | Mushroom Kingdom | Pink | duck |
| 3 | Peach | Mushroom Kingdom | Pink | jump |
| 3 | Peach | Mushroom Kingdom | Pink | run |
| 4 | Toad | Mushroom Kingdom | Blue | duck |
| 4 | Toad | Mushroom Kingdom | Blue | jump |
| 4 | Toad | Mushroom Kingdom | Blue | run |
| 5 | Bowser | Koopa Kingdom | Yellow | duck |
| 5 | Bowser | Koopa Kingdom | Yellow | jump |
| 5 | Bowser | Koopa Kingdom | Yellow | run |
| 6 | Daisy | Sarasaland | Orange | duck |
| 6 | Daisy | Sarasaland | Orange | jump |
| 6 | Daisy | Sarasaland | Orange | run |
| 7 | Yoshi | Dinosaur Land | Green | duck |
| 7 | Yoshi | Dinosaur Land | Green | jump |
| 7 | Yoshi | Dinosaur Land | Green | run |
+--------------+--------+------------------+----------------+--------+
(21 rows)