Let's start with some basics

C.R.U.D.

Our Tables

CREATE TABLE books (
   id int auto_increment,
   name varchar(128) NOT NULL,
   author_id int NOT NULL,
   PRIMARY KEY (id)
);

CREATE TABLE authors (
   id int auto_increment,
   first_name varchar(128) NOT NULL,
   last_name varchar(128) NOT NULL,
   PRIMARY KEY (id)
);

Create

INSERT INTO authors (first_name, last_name) VALUES ('Stephen', 'Hawking');

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Stephen    | Hawking   |
+----+------------+-----------+

Note: The "1" came from "auto_increment" in the table definition.

INSERT INTO books (name, author_id) VALUES ('Brief History of Time', 1);

+----+-----------------------+-----------+
| id | name                  | author_id |
+----+-----------------------+-----------+
|  1 | Brief History of Time |         1 |
+----+-----------------------+-----------+

Read

Get all columns and rows of a table.

SELECT * FROM books;

+----+----------------------------+-----------+
| id | name                       | author_id |
+----+----------------------------+-----------+
|  1 | Brief History of Time      |         1 |
|  2 | Pilgrim at Tinker Creek    |         2 |
|  3 | Tickets for a Prayer Wheel |         2 |
|  4 | For the Time Being         |         2 |
+----+----------------------------+-----------+

Searching

Get only the names of books with "Time" in the name.

SELECT name FROM books WHERE name LIKE '%Time%';

+-----------------------+
| name                  |
+-----------------------+
| Brief History of Time |
| For the Time Being    |
+-----------------------+

Get a book using it's id

SELECT name FROM books WHERE id = 2;

+-------------------------+
| name                    |
+-------------------------+
| Pilgrim at Tinker Creek |
+-------------------------+

Update

mysql> select * from authors;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Stephen    | Hawking   |
|  2 | Anne       | Dillard   |
+----+------------+-----------+
2 rows in set (0.00 sec)

Fix the spelling of Dillard's first name.

UPDATE authors SET first_name = "Annie" WHERE id = 2;

mysql> select * from authors;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Stephen    | Hawking   |
|  2 | Annie      | Dillard   |
+----+------------+-----------+

Deleting

Say we no longer carry Annie Dillard's "Tickets for a Prayer Wheel".

DELETE FROM books WHERE id = 3;

+----+-------------------------+-----------+
| id | name                    | author_id |
+----+-------------------------+-----------+
|  1 | Brief History of Time   |         1 |
|  2 | Pilgrim at Tinker Creek |         2 |
|  4 | For the Time Being      |         2 |
+----+-------------------------+-----------+

Relationships

• Has Many
• Many-to-Many

Has Many

Join tables to get the author's info along with the book.

SELECT * FROM books AS b, authors AS a WHERE b.author_id = a.id;

+----+-------------------------+-----------+----+------------+-----------+
| id | name                    | author_id | id | first_name | last_name |
+----+-------------------------+-----------+----+------------+-----------+
|  1 | Brief History of Time   |         1 |  1 | Stephen    | Hawking   |
|  2 | Pilgrim at Tinker Creek |         2 |  2 | Annie      | Dillard   |
|  4 | For the Time Being      |         2 |  2 | Annie      | Dillard   |
+----+-------------------------+-----------+----+------------+-----------+

Now lets remove dupe columns and only get the author's first and last name.

SELECT b.*, a.first_name, a.last_name FROM books AS b, authors AS a
WHERE b.author_id = a.id;

+----+-------------------------+-----------+------------+-----------+
| id | name                    | author_id | first_name | last_name |
+----+-------------------------+-----------+------------+-----------+
|  1 | Brief History of Time   |         1 | Stephen    | Hawking   |
|  2 | Pilgrim at Tinker Creek |         2 | Annie      | Dillard   |
|  4 | For the Time Being      |         2 | Annie      | Dillard   |
+----+-------------------------+-----------+------------+-----------+

Has Many

But you can't say a book "has many" authors!

Right. Those were technically "Belongs To" relationships.

Get all books by Annie Dillard:

mysql> SELECT b.* FROM books AS b, authors AS a WHERE a.id = b.author_id
AND a.last_name = "Dillard";
+----+-------------------------+-----------+
| id | name                    | author_id |
+----+-------------------------+-----------+
|  2 | Pilgrim at Tinker Creek |         2 |
|  4 | For the Time Being      |         2 |
+----+-------------------------+-----------+

Many-to-Many

First let's create a genre table and populate it.

CREATE TABLE genre (
   id int auto_increment,
   name varchar(128) NOT NULL,
   PRIMARY KEY (id)
);

mysql> select * from genre;
+----+------------+
| id | name       |
+----+------------+
|  1 | Science    |
|  2 | Nonfiction |
|  3 | Fiction    |
|  4 | Poetry     |
+----+------------+
4 rows in set (0.01 sec)

Many-to-Many

How do we connect genres to books?

Answer: With another TABLE! :D

CREATE TABLE book_genre (
   book_id int NOT NULL,
   genre_id int NOT NULL,
   PRIMARY KEY (book_id, genre_id)
);

Now let's create some connections

INSERT INTO book_genre VALUES (1,1); -- Brief History of Time -> Nonfiction
INSERT INTO book_genre VALUES (1,2); -- Brief History of Time -> Science
INSERT INTO book_genre VALUES (2,2); -- Pilgrim at Tinker Creek -> Nonfiction

Many-to-Many

Now we can get all books of a genre:

mysql> SELECT b.* FROM books AS b, genre AS g, book_genre AS bg
WHERE b.id = bg.book_id AND g.id = bg.genre_id AND g.name = "Nonfiction";
+----+-------------------------+-----------+
| id | name                    | author_id |
+----+-------------------------+-----------+
|  1 | Brief History of Time   |         1 |
|  2 | Pilgrim at Tinker Creek |         2 |
+----+-------------------------+-----------+

Or get all of a book's genres:

mysql> SELECT g.name FROM books AS b, genre AS g, book_genre AS bg
WHERE b.id = bg.book_id AND g.id = bg.genre_id
AND b.name = "Brief History of Time";
+------------+
| name       |
+------------+
| Science    |
| Nonfiction |
+------------+

Modifying Tables

You know, book's don't really have names, they have titles.

ALTER TABLE books CHANGE name title varchar(128) NOT NULL;

To prove that it changed, we can use the following.

describe books;

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| title     | varchar(128) | NO   |     | NULL    |                |
| author_id | int(11)      | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

Modifying Tables

Creating a unique column

ALTER IGNORE TABLE mytbl ADD UNIQUE (columnName);

Note – The IGNORE will delete all rows with duplicate values except for the first.