MySQL Primer 
by Daniel Bartholomew

Intro | Logging In | Viewing | Inserting | Modifying | Links

<-- Previous

Viewing MySQL Data

Next -->

To get data out of a MySQL database, you need to ask it a question. These questions are called SQL statements. SQL stands for Structured Query Language, which is just a fancy way of saying that the database is picky about how you ask your questions. It doesn't care what you ask, so long as you ask properly.

The first thing we need to know is what sort of information this database has. Databases store information in groups, called tables. They're a lot like fancy spreadsheets, only more powerful. You may already know what tables your database has. If you don't, type:

show tables;

The semi-colon is very important. MySQL lets you create very complex questions using multiple lines. It won't actually ask your question until you type a semi-colon and hit enter.

After typing show tables; and hitting enter, you will see a list of the tables in the database, in our sample database we see:

mysql> show tables;
+---------------------+
| Tables_in_MyLibrary |
+---------------------+
| Author              |
| Books               |
| Series              |
+---------------------+
3 rows in set (0.00 sec)

mysql> 

It looks like the sample database has three tables, and we now know their names. Using this information, we can ask it to tell us more about one of them:

describe Books;

This question asks the database to tell us more about the Books table, not the actual data in the table, but how the table is set up. Executing the above statement gives us this:

mysql> describe Books;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| Book_ID  | int(11)      |      | PRI | NULL    | auto_increment |
| Title    | varchar(100) |      |     |         |                |
| SeriesID | int(11)      | YES  |     | NULL    |                |
| AuthorID | int(11)      | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> 

The main bit of info we are interested in right now is the Field column. The other columns give us all sorts of useful information about the structure and type of data in the database but the Field column gives us names, and knowing the names of the individual columns of a database is very useful (as we will see when we enter information into the database).

Knowing how a given table is organized is all well and good, but what is actually in this table? Well, to find that out we use a select question:

select * from Books;

This question basically asks the database to show us everything in the Books table (the actual data). The wildcard (*) character is very usefull for getting everything of something. Executing the above statement on our test database gives us this:

mysql> select * from Books;
+---------+----------------------------+----------+----------+
| Book_ID | Title                      | SeriesID | AuthorID |
+---------+----------------------------+----------+----------+
|       1 | The Fellowship of the Ring |        1 |        1 |
|       2 | The Two Towers             |        1 |        1 |
|       3 | The Return of the King     |        1 |        1 |
|       4 | The Sum of All Men         |        2 |        2 |
|       5 | Brotherhood of the Wolf    |        2 |        2 |
|       6 | Wizardborn                 |        2 |        2 |
|       7 | The Hobbbit                |        0 |        1 |
+---------+----------------------------+----------+----------+
7 rows in set (0.21 sec)

mysql> 

Not surprisingly, we find that the data of the Books table is a list of...books. Of course, you already knew that because of the output of the describe question above :-).

But wait! You just found out that David Farland's latest book in the Runelords series is called Lair of Bones. We'd better add that to the database, don't you think?

To learn about inserting data into a database, go to the next page.

<-- Previous

Next -->

Creative Commons License
This work is licensed under a Creative Commons License.