Getting Started with MySQL - SQL Querys

[sivaji]

Getting Started with MySQL

In Chapter Installation, we installed and setup two software programs: PHP and MySQL.In this chapter, we'll learn how to work with MySQL databases using Structured Query Language (SQL).

An Introduction to Databases

As I've already explained, PHP is a server-side scripting language that lets you insert into your Web pages instructions that your Web server software (be it Apache, IIS, or whatever) will execute before it sends those pages to browsers that request them. In a brief example, I showed how it was possible to insert the current date into a Web page every time it was requested.

Now, that's all well and good, but things really get interesting when a database is added to the mix. A database server (in our case, MySQL) is a program that can store large amounts of information in an organized format that's easily accessible through scripting languages like PHP. For example, you could tell PHP to look in the database for a list of jokes that you'd like to appear on your Website.

In this example, the jokes would be stored entirely in the database. The advantages of this approach would be twofold. First, instead of having to write an HTML file for each of your jokes, you could write a single PHP file that was designed to fetch any joke from the database and display it. Second, adding a joke to your Website would be a simple matter of inserting the joke into the database. The PHP code would take care of the rest, automatically displaying the new joke along with the others when it fetched the list from the database.

Let's run with this example as we look at how data is stored in a database. A database is composed of one or more tables, each of which contains a list of things. For our joke database, we'd probably start with a table called joke that would contain a list of jokes. Each table in a database has one or more columns, or fields. Each column holds a certain piece of information about each item in the table. In our example, our joke table might have one column for the text of the jokes, and another for the dates on which the jokes were added to the database.Each joke stored in this way would then be said to be a row in the table.These rows and columns form a table.

Notice that, in addition to columns for the joke text (joketext) and the date of the joke (jokedate), I included a column named id. As a matter of good design, a database table should always provide a means by which we can identify each of its rows uniquely. Since it's possible that a single joke could be entered more than once on the same date, the joketext and jokedate columns can't be relied upon to tell all the jokes apart. The function of the id column, therefore, is to assign a unique number to each joke so that we have an easy way to refer to them and to keep track of which joke is which. Such database design issues will be covered in greater depth in Chapter 5.

So, to review, the above is a three-column table with two rows, or entries. Each row in the table contains three fields, one for each column in the table: the joke's ID, its text, and the date of the joke. With this basic terminology under our belts, we're ready to get started with MySQL.

So, What's SQL?

The set of commands we'll use to direct MySQL throughout the rest of this book is part of a standard called Structured Query Language, or SQL (pronounced either "sequel" or "ess-cue-ell"—take your pick). Commands in SQL are also referred to as queries (I'll use these two terms interchangeably).

SQL is the standard language for interacting with most databases, so, even if you move from MySQL to a database like Microsoft SQL Server in the future, you'll find that most of the commands are identical. It's important that you understand the distinction between SQL and MySQL. MySQL is the database server software that you're using. SQL is the language that you use to interact with that database.

Creating a Database

Those who are working on their Web host's MySQL server are likely already to have been assigned a database with which to work. Sit tight; we'll get back to you in a moment. If you're running a MySQL server that you installed yourself, however, you'll need to create your own database. It's just as easy to create a database as it is to delete one:

mysql>CREATE DATABASE ijdb;

I chose to name the database ijdb, for Internet Joke Database, because that fits with the example we're using. Feel free to give the database any name you like, though. Those of you working on your Web host's MySQL server will probably have no choice in what to name your database, as it will probably already have been created for you.

Now that we have a database, we need to tell MySQL that we want to use it. Again, the command isn't difficult to remember:

mysql>USE ijdb;

You're now ready to use your database. Since a database is empty until you add some tables to it, our first order of business will be to create a table that will hold our jokes.

Creating a Table

The SQL commands we've encountered so far have been reasonably simple, but as tables are so flexible, it takes a more complicated command to create them. The basic form of the command is as follows:

mysql>CREATE TABLE table_name (
-> column_1_name column_1_type column_1_details,
-> column_2_name column_2_type column_2_details,
-> ...
->);

Let's return to our example joke table. Recall that it had three columns: id (a number), joketext (the text of the joke), and jokedate (the date on which the joke was entered). The command to create this table is as follows:

mysql>CREATE TABLE joke (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> joketext TEXT,
-> jokedate DATE NOT NULL
->);

It looks pretty scary, huh? Let's break it down:
   
  The first line is fairly simple; it says that we want to create a new table named joke.
   
  The second line says that we want a column called id that will contain an integer (INT), that is, a
         whole number. The rest of this line deals with special details for the column. First, this column is
         not allowed to be left blank (NOT NULL). Next, if we don't specify any value in particular when we
         add a new entry to the table, we want MySQL to pick a value that is one more than the highest
         value in the table so far (AUTO_INCREMENT). Finally, this column is to act as a unique identifier
         for the entries in the table, so all values in this column must be unique (PRIMARY KEY).
   
  The third line is super-simple; it says that we want a column called joketext, which will contain
         text (TEXT).
   
  The fourth line defines our last column, called jokedate, which will contain data of type DATE, and
         which cannot be left blank (NOT NULL).

Note that, while you're free to type your SQL commands in upper– or lowercase, a MySQL server running on a UNIX-based system will be case-sensitive when it comes to database and table names, as these correspond to directories and files in the MySQL data directory. Otherwise, MySQL is completely case-insensitive, but for one exception: table, column, and other names must be spelled exactly the same when they're used more than once in the same command.

Note also that we assigned a specific type of data to each column we created. id will contain integers, joketext will contain text, and jokedate will contain dates. MySQL requires you to specify in advance a data type for each column. Not only does this help keep your data organized, but it allows you to compare the values within a column in powerful ways, as we'll see later. For a complete list of supported
MySQL data types, see Appendix C.

Now, if you typed the above command correctly, MySQL will respond with Query OK, and your first table will be created. If you made a typing mistake, MySQL will tell you there was a problem with the query you typed, and will try to indicate where it had trouble understanding what you meant.

For such a complicated command, Query OK is a pretty boring response. Let's have a look at your new table to make sure it was created properly. Type the following command:

mysql>SHOW TABLES;

The response should look like this:
+----------------+
| Tables in ijdb     |
+----------------+
| joke                 | 
+----------------+
1 row in set

This is a list of all the tables in our database (which I named ijdb above). The list contains only one table: the joke table we just created. So far, everything seems fine. Let's take a closer look at the joke table itself:

mysql>DESCRIBE joke;
+----------+---------+------+-----+------------+----------------+
|   Field     |   Type    |   Null |  Key |     Default   |       Extra         |
+----------+---------+------+-----+------------+----------------+
| id            | int(11)  |         |  PRI |     NULL      | auto_increment |
| joketext   | text      | YES   |       |     NULL      |                       |
| jokedate  | date      |         |       | 0000-00-00 |                       |
+----------+---------+------+-----+------------+----------------+
3 rows in set

As you can see, there are three columns (or fields) in this table, which appear as the three rows in this table of results. The details are somewhat cryptic, but if you look at them closely, you should be able to figure out what they mean. Don't worry about it too much, though. We've got better things to do, like adding some jokes to our table!

We need to look at just one more thing before we get to that, though: deleting a table. This task is as frighteningly easy as deleting a database. In fact, the command is almost identical:

mysql>DROP TABLE tableName;

Inserting Data into a Table

Our database is created and our table is built; all that's left is to put some actual jokes into the database. The command that inserts data into a database is called, appropriately enough, INSERT. This command takes two basic forms:

mysql>INSERT INTO table_name SET
-> columnName1 = value1,
-> columnName2 = value2,
-> ...
->;

mysql>INSERT INTO table_name
-> (columnName1, columnName2, ...)
-> VALUES (value1, value2, ...);

So, to add a joke to our table, we can use either of these commands:

mysql>INSERT INTO joke SET
->joketext = "Why did the chicken cross the road? To get to
"> the other side!",
->jokedate = "2004-04-01";

mysql>INSERT INTO joke
->(joketext, jokedate) VALUES (
->"Why did the chicken cross the road? To get to the other
"> side!",
->"2004-04-01"
->);

Note that in the second form of the INSERT command, the order in which you list the columns must match the order in which you list the values. Otherwise, the order of the columns doesn't matter, as long as you provide values for all required fields. Now that you know how to add entries to a table, let's see how we can view those entries.

Viewing Stored Data

The command we use to view data stored in database tables, SELECT, is the most complicated command in the SQL language. The reason for this complexity is that the chief strength of a database is its flexibility in data retrieval and presentation. At this early point in our experience with databases we need only fairly simple lists of results, so we'll just consider the simpler forms of the SELECT command here. This command will list everything that's stored in the joke table:

mysql>SELECT * FROM joke;

Read aloud, this command says "select everything from joke." If you try this command, your results will resemble the following:

+----+---------------------------------------------------
------------+------------+
| id            | joketext
| jokedate   |
+----+---------------------------------------------------
------------+------------+
| 1   | Why did the chicken cross the road? To get to the
other side!   | 2004-04-01 |
+----+---------------------------------------------------
------------+------------+
1 row in set (0.05 sec)

The results look a little disorganized because the text in the joketext column is so long that the table can't fit on the screen properly. For this reason, you might want to tell MySQL to leave out the joketext column. The command for doing this is as follows:

mysql>SELECT id, jokedate FROM joke;

This time, instead of telling it to "select everything," we told it precisely which columns we wanted to see. The results look like this:

+----+------------+
| id   |  jokedate    |
+----+------------+
| 1    | 2004-04-01|
+----+------------+
1 row in set (0.00 sec)

Not bad, but we'd like to see at least some of the joke text, wouldn't we? As well as being able to name specific columns that we want the SELECT command to show us, we can use functions to modify each column's display. One function, called LEFT, lets us tell MySQL to display a column's contents up to a specified maximum number of characters. For example, let's say we wanted to see only the first 20 characters of the joketext column. Here's the command we'd use:

mysql>SELECT ID, LEFT(joketext, 20), jokedate FROM joke;
+----+----------------------+------------+
| id   | LEFT(joketext, 20)      |   jokedate  |
+----+----------------------+------------+
| 1    | Why did the chicken   | 2004-04-01|
+----+----------------------+------------+
1 row in set (0.05 sec)

See how that worked? Another useful function is COUNT, which lets us count the number of results returned. If, for example, we wanted to find out how many jokes were stored in our table, we could use the following command:

mysql>SELECT COUNT(*) FROM joke;
+----------+
| COUNT(*) |
+----------+
| 1            |
+----------+
1 row in set (0.06 sec)

As you can see, we have just one joke in our table and, so far, all the examples have fetched all the entries in our table. However, we can limit our results to include only those database entries that have the specific attributes we want. We set these restrictions by adding what's called a WHERE clause to the SELECT command. Consider this example:

mysql>SELECT COUNT(*) FROM joke WHERE jokedate >= "2004-01-01";

This query will count the number of jokes that have dates greater than or equal to January 1, 2004. In the case of dates, "greater than or equal to" means "on or after." Another variation on this theme lets you search for entries that contain a certain piece of text. Check out this query:

mysql>SELECT joketext FROM joke WHERE joketext LIKE "%chicken%";

The above query displays the text of all jokes that contain the word "chicken" in their joketext column. The LIKE keyword tells MySQL that the named column must match the given pattern. In this case, the pattern we've used is "%chicken%". The % signs indicate that the word "chicken" may be preceded and/or followed by any string of text.

Additional conditions may also be combined in the WHERE clause to further restrict results. For example, to display knock-knock jokes from April 2004 only, we could use the following query:

mysql>SELECT joketext FROM joke WHERE
->joketext LIKE "%knock%" AND
->jokedate >= "2004-04-01" AND
->jokedate < "2004-05-01";

Enter a few more jokes into the table and experiment with SELECT statements. A good familiarity with the SELECT statement will come in handy later in this book.

You can do a lot with the SELECT statement. We'll look at some of its more advanced features later, when we need them.

Modifying Stored Data

Having entered your data into a database table, you might like to change it. Whether you want to correct a spelling mistake, or change the date attached to a joke, such alterations are made using the UPDATE command. This command contains elements of the INSERT command that set column values, and elements of the SELECT command that pick out entries for modification. The general form of the UPDATE command is as follows:

mysql>UPDATE table_name SET
-> col_name = new_value, ...
->WHERE conditions;

So, for example, if we wanted to change the date on the joke we entered above, we'd use the following command:

mysql>UPDATE joke SET jokedate="1994-04-01" WHERE id=1;

Here's where that id column comes in handy: it allows us to easily single out a joke for changes. The WHERE clause used here works just as it did in the SELECT command. This next command, for example, changes the date of all entries that contain the word "chicken:"

mysql>UPDATE joke SET jokedate="1994-04-01"
->WHERE joketext LIKE "%chicken%";

Deleting Stored Data

The deletion of entries in SQL is dangerously easy, which, if you haven't noticed yet, is a recurring theme. Here's the command syntax:

mysql>DELETE FROM table_name WHERE conditions;

To delete all chicken jokes from your table, you'd use the following query:

mysql>DELETE FROM joke WHERE joketext LIKE "%chicken%";

One thing to note is that the WHERE clause is actually optional. You should be very careful, however, if you leave it out, as the DELETE command will then apply to all entries in the table. This command will empty the joke table in one fell swoop:

mysql>DELETE FROM joke;

Scary, huh?