MySql一直是性价比最高的关系型数据库典范
MongoDB带来了关系数据库以外的NoSql体验。
让我们看一个简单的例子,我们将如何为MySQL(或任何关系数据库)和MongoDB中创建一个数据结构。
MySql设计
我们假设设计个表:
People 人物信息表 包含ID 和名字字段
passports 护照表 ,主要包含 对应的people表的外键ID ,所属国家,和护照有效期
mysql> select * from people; +----+------------+ | id | name | +----+------------+ | 1 | Stephane | | 2 | John | | 3 | Michael | | 4 | Cinderella | +----+------------+ mysql> select * from passports; +----+-----------+---------+-------------+ | id | people_id | country | valid_until | +----+-----------+---------+-------------+ | 4 | 1 | FR | 2020-01-01 | | 5 | 2 | US | 2020-01-01 | | 6 | 3 | RU | 2020-01-01 | +----+-----------+---------+-------------+
mysql> select * from people;
+----+------------+
| id | name |
+----+------------+
| 1 | Stephane |
| 2 | John |
| 3 | Michael |
| 4 | Cinderella |
+----+------------+
mysql> select * from passports;
+----+-----------+---------+-------------+
| id | people_id | country | valid_until |
+----+-----------+---------+-------------+
| 4 | 1 | FR | 2020-01-01 |
| 5 | 2 | US | 2020-01-01 |
| 6 | 3 | RU | 2020-01-01 |
+----+-----------+---------+-------------+
于是你接下来可以操作如下基本功能:
一共有多少人
SELECT count(*) FROM people
查询出 stephane 的护照有效期
SELECT valid_until from passports ps join people pl ON ps.people_id = pl.id WHERE name = 'Stephane'
有多少人木有护照
SELECT name FROM people pl LEFT JOIN passports ps ON ps.people_id = pl.id WHERE ps.id IS NULL
--转自