MorkaLork Development

Interesting stuff I've picked up over the years...

A MySql scenario

2010-02-12 07:12:56 | 484 views | SQL create insert table

What's this?


This article will show a possible scenario when creating a bunch of connected tables. We will create and alter tables, use primary and foreign keys, insert into, update, and select from tables.
The point of this article is to show a common scenario when it's good to know SQL.



Create a table


To create a table, with an SQL query, you use the CREATE TABLE command. If you want to be on the safe side you can add IF NOT EXISTS which will discard the query if a table with the same name already exists.

This is the syntax:


CREATE TABLE myTable (
col1 datatype option
);


or


CREATE TABLE IF NOT EXISTS myTable (
col1 datatype option
);



Example:



CREATE TABLE IF NOT EXISTS tbl_movie (
id int(11) NOT NULL auto_increment,
genre varchar(255) NOT NULL,
title varchar(255) NOT NULL,
director varchar(255) NOT NULL,
PRIMARY KEY (id)
)


What we do here is that we create a table called 'tbl_movie', unless there already exists a table called tbl_movie, which has 4 fields, 'id', 'genre', 'title' and 'director'. We set the 'id' field to primary key with an auto incrementor because we want every record to be unique. This table will represent a movie.
We will use this table throughout the tutorial.

In addition to this table, we'll create a table for books:



CREATE TABLE IF NOT EXISTS tbl_book (
id int(11) NOT NULL auto_increment,
genre varchar(255) NOT NULL,
title varchar(255) NOT NULL,
author varchar(250) NOT NULL,
PRIMARY KEY (`id`)
)


We'll insert some data into the movies table:



INSERT INTO tbl_movie VALUES (1, 'Action', 'Die Hard', 'John McTiernan');
INSERT INTO tbl_movie VALUES (2, 'Drama', 'Braveheart', 'Mel Gibson');
INSERT INTO tbl_movie VALUES (3, 'Comedy', 'Dogma', 'Kevin Smith');
INSERT INTO tbl_movie VALUES (4, 'Thiller', 'Pusher', 'Nicolas Winding Refn');
INSERT INTO tbl_movie VALUES (5, 'Comedy', 'Hot Shots!', 'Jim Abrahams');
INSERT INTO tbl_movie VALUES (6, 'Sci-fi', 'Event Horizon', ' Paul W.S. Anderson');


The movies table should look like this:

idgenretitledirector
1 Action Die Hard John McTiernan
2 Drama Braveheart Mel Gibson
3 Comedy Dogma Kevin Smith
4 Thiller Pusher Nicolas Winding Refn
5 Comedy Hot Shots! Jim Abrahams
6 Sci-fi Event Horizon Paul W.S. Anderson



As we now have a common field, genre, which contains the same information it would be smart to put this in a separate table. The reason for this is that whenever tables are sharing information you risk inconsistency. If we were, for example, to change the genre Sci-fi to Scifi in one table we would have different genres in the two tables as Sci-fi and Scifi are different strings. Putting the genre in a separate table we do not risk this inconsistency. More about this further down in the article.
For now, let's create a table for genres:




CREATE TABLE IF NOT EXISTS tbl_genre (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL,
PRIMARY KEY (id)
)


Let's also insert a couple of genres:



INSERT INTO tbl_genre (id, name) VALUES
(1, 'Action'),
(2, 'Drama'),
(3, 'Sci-fi'),
(4, 'Comedy'),
(5, 'Thriller'),
(6, 'Animated');


This table has only 2 fields, 'id' and 'name'. The name-field will represent the name of the genre. Now, to use this table together with the books and movies tables, we need to create a connection between them.
However, before we do that, we drop the genre fields we currently have in our book and movie tables.




Drop field


To drop a column we use ALTER TABLE. There will be more about this in the next article, but for now, the syntax is:


ALTER TABLE myTable
DROP field;


In our case, we run the following two queries:



ALTER TABLE tbl_book
DROP genre;

ALTER TABLE tbl_movie
DROP genre;


Now we'll add a new column to each table that we'll call 'genre_id' which will have a foreign key pointing to the tbl_genre 'id' field. We'll start by adding the columns:



ALTER TABLE tbl_book
ADD genre_id int(11);

ALTER TABLE tbl_movie
ADD genre_id int(11);




Add a foreign key


Now we'll add the foreign key. The syntax for adding a foreign key is:


ALTER TABLE myTable
ADD FOREIGN KEY (currentTableColumn)
REFERENCES myOtherTable(otherTableColumn);


And this is how we do it:



ALTER TABLE tbl_movie
ADD FOREIGN KEY (genre_id) REFERENCES tbl_genre(id);

ALTER TABLE tbl_book
ADD FOREIGN KEY (genre_id) REFERENCES tbl_genre(id);


Now we change all the genre_id's to the correct genre id. If Action has id 1 in tbl_genre, then add 1 to the genre_id field of the action movies in tbl_movies or the action books (are there action books? Perhaps, I own a copy of "Rambo, the book").

Here's how it could look:

tbl_genre


idname
1 Action
2 Drama
3 Sci-fi
4 Comedy
5 Thriller
6 Animated


tbl_movie


idtitledirectorgenre_id
1 Die Hard John McTiernan 1
2 Braveheart Mel Gibson 2
3 Dogma Kevin Smith 4
4 Pusher Nicolas Winding Refn 5
5 Hot Shots! Jim Abrahams 4
6 Event Horizon Paul W.S. Anderson 3


tbl_book


idtitleauthorgenre_id
1 Pride and Prejudice Jane Austen 2
2 the boys from brazil Ira Levin 3
3 Ghost in the shell V1 Masamune Shirow 6
4 The Firm John Grisham 5




Joining tables


Now, if we select all from table tbl_book now we will get a column called genre_id which will contain either null or a number that represents a foreign key. That key points to a value in the tbl_genre table. A user viewing the table will, however, not understand that, so we need to join the tables and get the proper genre name.

Now, to join two tables we use the JOIN keyword in combination with the ON keyword:


SELECT m1.col, m2.col
FROM myTable m1
JOIN myOtherTable m2 ON m1.foreign_key = m2.id


Example:


SELECT b.id as id,
b.title as title,
b.author as author,
g.name as genre
FROM tbl_book b
JOIN tbl_genre g ON b.genre_id = g.id
ORDER BY b.title ASC


Here we select id, title and author from table tbl_book (which we refer to as 'b') and name from table tbl_genre (which we refer to as 'g'). We join the tables based on the records where the genre_id value in the tbl_book table is equal to the id value in the tbl_genre table.
This means that any record having null as genre_id will not be displayed, this is the output:

idtitleauthorgenre
3 Ghost in the shell V1 Masamune Shirow Animated
1 Pride and Prejudice Jane Austen Drama
2 the boys from brazil Ira Levin Sci-fi
4 The Firm John Grisham Thriller


The reason we are using aliases (ie b.id as id) is because if we don't, the table headers reads 'b.id' instead of 'id'. If you try it out without the aliases you can see the difference.

Now, let's do the same with movies, but change the order a bit. In the movies table we have more than one movie under some of the categories:



SELECT g.name as genre,
m.id as id,
m.title as title,
m.director as director
FROM tbl_genre g
JOIN tbl_movie m ON m.genre_id = g.id
ORDER BY g.name ASC


genretitledirectorid
Action Die Hard John McTiernan 1
Comedy Hot Shots! Jim Abrahams 5
Comedy Dogma Kevin Smith 3
Drama Braveheart Mel Gibson 2
Sci-fi Event Horizon Paul W.S. Anderson 6
Thriller Pusher Nicolas Winding Refn 4


As we can see, we have two comedies here. But, we don't have any animated movies which is why the 'animated' genre is never shown. If we want to display all the genres with the connected movies, even the genres that don't have any movies so far, we have to use the LEFT JOIN.
The left join will iterate all records in the left table even if there are no records in the right one. The left table is the table you select from where as the 'right' table is the one you're joining with:



SELECT g.name as genre,
m.id as id,
m.title as title,
m.director as director
FROM tbl_genre g
LEFT JOIN tbl_movie m ON m.genre_id = g.id
ORDER BY g.name ASC


genretitledirectorid
Action Die Hard John McTiernan 1
Animated
Comedy Dogma Kevin Smith 3
Comedy Hot Shots! Jim Abrahams 5
Drama Braveheart Mel Gibson 2
Sci-fi Event Horizon Paul W.S. Anderson 6
Thriller Pusher Nicolas Winding Refn 4


This would be a good time to show a good use of having the genres in a separate table. What if we want to list all genres together with all books and movies in those genres. We'll join both the books and movies with the genres:



SELECT g.name as genre,
b.title as book,
m.title as movie
FROM tbl_genre g
LEFT JOIN tbl_book b ON b.genre_id = g.id
LEFT JOIN tbl_movie m ON m.genre_id = g.id
ORDER BY g.name ASC;


genreBookMovie
Action Die Hard
Animated Ghost in the shell V1
Comedy Hot Shots!
Comedy Dogma
Drama Pride and Prejudice Braveheart
Sci-fi the boys from brazil Event Horizon
Thriller The Firm Pusher



Hopefully this article has shed some light on how to use the foreign keys and joining tables. The point of this article was to show you (the reader) that is quite easy to join tables which means it's equally easy to extract fields from tables and making them in to separate tables. If we in this article had kept the genre field in both books and movies we would've had a lot more job on our hands comparing genres (which is something the tables have in common).

The article ends here, it was just a short example, but hopefully useful. Consider how this could be applied to, say, a forum database. How would you let a user create a new thread in a forum? Would you have the user table contain a field called thread1, thread2, thread3 etc, or just one field, threads, with the thread id´s/names separated by commas? Perhaps there, as well as here, it would be a good idea to have another table, tbl_forum_threads, which holds a foreign key to the user id and then join the tables?
Joining is vital when working with SQL and not learning how to use it can cause illogical table designs and very heavy querying.

Have a nice day :P


Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.