Entities (table names) are the nouns that are described by the columns in a table.
Attributes describe the individual aspects of a particular Entity and are usually just called columns.
Records are the actual data that are described by the data in each column. These are often called rows.
Every column has a data type defined when it's created.
Table and Column names should:
Primary keys uniquely identify each Record in a Table. By convention, the primary key of a table is named id
.
A Compound Primary Key uniquely defines a table row with a combination of two primary/foreign keys.
By convention, these tables are named by the two joining table names, separated by an underscore.
Foreign keys create a column relationship between two tables.
By convention, column names with foreign key relationships end in _id
.
When a column has a limited range of allowed values.
When a column cannot be empty.
When each value in a table column must be unique.
When a default column value is defined if one is not provided when a row is created or updated.
Entity A belongs to one Entity B but Entity B can belong to many Entity A.
Example: A country can have many cities but a city can only be in one country.
Entity A belongs to many Entity B and vice versa.
Example: A movie can have many actors and an actor can be in many movies.
One-to-one relationships are rare but are sometimes used in more advanced "super/subtype" data model architectures.
The most common SQL statements follow traditional CRUD Operations:
Create → INSERT
Retrieve → SELECT
Update → UPDATE
Delete → DELETE
INSERT INTO
Insert data into all columns of a table:
INSERT INTO
table_name
VALUES
(value1, value2, value3, ...);
Insert data into specific columns of a table:
INSERT INTO
table_name (column1, column2, column3, ...)
VALUES
(value1, value2, value3, ...);
SELECT
Select specific columns from all rows from a table:
SELECT
column1, column2, ...
FROM
table_name;
SELECT first_name, last_name FROM customer
SELECT first_name, last_name FROM customer;
SELECT all columns FROM customer with *
:
SELECT * FROM customer;
SELECT
with WHERE
Select from specific rows with WHERE
SELECT
column1, column2, ...
FROM
table_name
WHERE
condition;
Find all fields for anyone with a last name of 'Packe':
SELECT * FROM customer WHERE last_name = 'Packe';
SELECT
with ORDER BY
Order the rows returned with ORDER BY:
SELECT
column1, column2, ...
FROM
table_name
WHERE
condition;
ORDER BY
column1, column2 ASC|DESC
Find all customers by last name, starting at Z:
SELECT id, first_name, last_name FROM customer ORDER BY last_name DESC
UPDATE
Update a row using SET
and WHERE
:
UPDATE
table_name
SET
column1 = value1, column2 = value2, ...
WHERE
condition;
Update the last name of customer 4
to "Marcias":
UPDATE customer SET last_name = 'Marcias' WHERE id = '4'
DELETE
Delete rows with WHERE
(we rarely delete rows in production):
DELETE FROM
table_name
WHERE
condition;
Delete all records that total more than 1000 (then get fired):
DELETE FROM rental_invoice WHERE total_amount_payable > 1000;