Relationships - MySQL

You should note at this point that mySQL is a "relational database management system", or RDBMS. A relational database is typically composed of multiple tables, which contain inter-related pieces of information. SQL allows you to combine the data from these tables in different ways, thereby allowing you to create and analyze new relationships between your data.

What we have in the example above is a single table. While this is fine by itself, it's when you add more tables, and relate the information between them, that you truly start to see the power inherent in this system. Consider the following example, which adds two more tables - the second contains a list of movies available for rental, while the third links the movies with the members via their primary keys.

---

+-----------+-------+---------+---------+--------------------------+
| member_id | fname | lname   | tel     | email                    |
+-----------+-------+---------+---------+--------------------------+
|         1 | John  | Doe     | 1234567 | jdoe@somewhere.com       |
|         2 | Jane  | Doe     | 8373728 | jane@site.com            |
|         3 | Steve | Klingon | 7449373 | steve@alien-race.com     |
|         4 | Santa | Claus   | 9999999 | santa@the-north-pole.com |
+-----------+-------+---------+---------+--------------------------+

---

---

+----------+-------------------------------+------------------+
| video_id | title                         | director         |
+----------+-------------------------------+------------------+
|        1 | Star Wars: The Phantom Menace | George Lucas     |
|        2 | ET                            | Steven Spielberg |
|        3 | Charlie's Angels              | McG              |
|        4 | Any Given Sunday              | Oliver Stone     |
|        5 | Hollow Man                    | Paul Verhoeven   |
|        6 | Woman On Top                  | Fina Torres      |
+----------+-------------------------------+------------------+

---

---

+-----------+----------+
| member_id | video_id |
+-----------+----------+
|         2 |        6 |
|         4 |        2 |
|         1 |        1 |
|         1 |        2 |
|         1 |        3 |
+-----------+----------+

---

If you take a close look at the third table, you'll see that it links each member with the video(s) he/she has rented. Thus we see that Jane Doe (member #2) has rented "Woman On Top" (video #6), while John Doe (member #1) has decided to spend the week-end on the couch with "Star Wars" (video #1), "ET" (video #2) and "Charlie's Angels" (video #3).

In other words, the third table has set up a relationship between the first and second table - this is the fundamental concept behind a relational database system. Once one or more relationships are set up, it is possible to extract a subset of the data (a "data slice") in order to answer specific questions.{mospagebreak title=Building Blocks} If you've understood the concept, it's now time to get down to brass tacks.

Start up your mySQL client

---

$ mysql

---

and (assuming everything is set up properly), you should see a prompt which looks something like this.

---

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 80 to server version: 3.23.24-beta-debug
Type 'help;' or '\h' for help.
mysql>

---

This is the mySQL command prompt - you'll be using this to enter all your SQL statements. Note that all mySQL commands end with a semi-colon, and can be entered in either upper-case or lower-case.

Since all tables are stored in a database, the first command you need to know is the CREATE DATABASE command, which looks like this:

CREATE DATABASE 

---

Go on - try it out by creating a database called "library"

---

mysql> CREATE DATABASE library;
Query OK, 1 row affected (0.05 sec)

---

You can view all available databases with the SHOW DATABASES command.

---

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| library  |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

---

Once you have a list, you can select the database you wish to use with the USE command, which looks like this:

USE <database_name>

---

For the moment, we'll restrict our activities to the database you just created.

---

mysql> USE library;
Database changed

---

Once you've selected a database, you can view available tables in it with the SHOW TABLES command.

---

mysql> SHOW TABLES;
Empty set (0.00 sec)

---

Since this is a new database, there are no tables in it yet. Let's fix that.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus