Speaking SQL (part 1) - Relationships
(Page 3 of 7 )
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.
Next: Not Your Type? >>
More MySQL Articles
More By icarus, (c) Melonfire
/ 6 
