如何在两个数据库表之间执行简单的联接
联接是一个非常强大的工具。还记得数据库介绍模块中的关系代数吗?
联接是应用关系代数。
假设您有2张桌子,people
和cars
:
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
-----
8What 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:
- The MongoDB basics tutorial
- How MongoDB is different from a SQL database
- Information systems, data and information
- Data models
- Introduction to the ER Data Model
- The Relational Model
- Relational Algebra
- Relational Databases
- What is a Database? And a DBMS?
- Introduction to SQL
- How to install PostgreSQL on macOS
- Do you always need a database for your app?
- How to install SQLite on macOS
- Introduction to PostgreSQL
- PostgreSQL User Permissions
- How to list all users in PostgreSQL
- How to switch database using PostgreSQL
- How to list all databases using PostgreSQL
- How to list tables in the current database using PostgreSQL
- How to install MySQL on macOS
- Creating a user on MySQL
- PostgreSQL vs MySQL, a comparison
- SQLite User Permissions
- MySQL User Permissions
- SQL, creating a table
- SQL, adding data to a table
- SQL, how to use SELECT
- SQL, Handling empty cells
- SQL, Unique and Primary keys
- SQL, how to update data
- SQL, how to update a table structure
- SQL, how to delete data and tables
- SQL Joins
- SQL Views
- How to insert multiple items at once in a MongoDB collection
- How to remove all items from a MongoDB collection