Data Manipulation Language (DML)

Now that we have covered the use of Data Definition Language, we will look at how to add, modify, and remove data from tables in the database using Data Manipulation Language. Without this knowledge, we would not really have any use for a database; it would simply be a static entity with no purpose. Let's first create a database and table to work with in this section, using the DDL we learned in the previous section. Our database will be called dmlexample, so let's create that now with the following statement:

mysql> CREATE DATABASE dmlexample;

Once created, we need to specify that we wish to use the new database by executing this statement:

mysql> USE dmlexample;

The console should now inform us that the database has changed; this can be seen in the following figure:
Screenshot-26: Creating the dmlexample database

Now that we have our database set up, let's create a table to experiment with called sampletable. This table will contain the following fields: username, password, age, e-mail, and the date the entry was created. This is accomplished using the knowledge that we gained in the section, "Data Definition Language." We can then create our table with the following DDL statement:

mysql> CREATE TABLE sampletable (
 -> username TEXT,
 -> password TEXT,
 -> age INT,
 -> email TEXT,
 -> datecreated TIMESTAMP);

Here is how this should look in the MySQL console client:
Screenshot-27: Creating our sample table

Inserting Data

Now that we have our table created, let's look at how we go about adding rows (records) of information into it. To add rows into the table, we need to use the INSERT command. Here is how we would add a single row to our sampletable.

mysql> INSERT INTO sampletable VALUES ('andrew', 'qwerty', 20, 'andrew@dreamcircle.co.uk', NULL);

Screenshot-28 shows how this looks when we enter it into the MySQL console. Notice how the feedback from the console tells us that one row has been affected. Hence, we have added one row to our sampletable table.
Screenshot-28: Inserting a single row of data

We can then use a command called SELECT to view the data in the table. We will go into more detail about this command later in this section, as it is very important, but for now we will just use it blindly. Let's see what data is in our dmlexample table:

mysql> SELECT * FROM sampletable;

When we execute this statement, the following will be visible in the MySQL console:
Screenshot-29: Viewing the new row in the table

It is also possible to add several rows of data in a single command. Let's try this now by adding another three rows to our table in a single INSERT command. This is done as follows:

mysql> INSERT INTO sampletable VALUES
 -> ('glenn', 'gimboid', 21, 'glenn@bugmenot.com', NULL),
 -> ('jim', 'letmein', 23, 'jim@email.net', NULL),
 -> ('wes', 'opensesame', 31, 'wes@email.net', NULL);

When we execute this command, the following can be seen in the MySQL console client:
Screenshot-30: Inserting multiple rows in a single statement

As you can see, this time the feedback from the console suggests that three rows have been affected; hence, we have added three rows to our table. We can verify this by again using the SELECT command:

mysql> SELECT * FROM sampletable;

When this is executed, you will now see that the table contains four rows (or records, if you like) of information. Here is a screen shot of the MySQL console after the SELECT statement has been executed:
Screenshot-31: Now we have four rows in the table.

Modifying Data

Now that we know how to add data to a table, let's look at how we go about modifying existing table data. To modify data in a table, we require the use of the UPDATE command. First let's try to change all the passwords in all the rows in the table to "changeme." This can be accomplished with the following statement:

mysql> UPDATE sampletable SET password = 'changeme';

When we execute this statement, the console will inform us that four rows have been affected, as we have changed the password for every row in that table. Now we can see the effect on the table by using the SELECT command, as follows:

myql> SELECT * FROM sampletable;

Here is a screen shot of this command being executed in the console:
Screenshot-32: The password field has been updated in all of the rows.

An obvious question now is, what if I only want to update a single row? Let's say that we wish to change Glenn's password from changeme back to gimboid. We would use the following statement to do this:

mysql> UPDATE sampletable SET password = 'gimboid' WHERE username = 'glenn';

When we execute this command in the console, it informs us that one row has been affected. This is because it will only update the password field if the username field is equal to glenn. If we use the SELECT command on the table now, we can see that only Glenn's password has changed. The following screen shot of the console reflects this:
Screenshot-33: Updating only a single row

We can also apply this technique to enable us to update only certain fields. For example, we could change all the passwords of the people who are age 30 or younger. Here is the command we would require to do this:

mysql> UPDATE sampletable SET password = 'young' WHERE age <= 30;

When we execute this command, it will inform us that three rows have been affected, as three of the four records in our table have an age equal to or less than 30. If we then use the SELECT command, we can see the following output in the console:
Screenshot-34: Conditional updates

Removing (Deleting) Data

Removing data from a table is done in a very similar way to updating data. First we will look at how to delete a single row of data. Let's now delete glenn from the database using the following statement:

mysql> DELETE FROM sampletable WHERE username = 'glenn';

When we execute this command, the MySQL console client will inform us that one row was affected (i.e., deleted). If we now use the SELECT command on the table, the following can be seen in the console:
Screenshot-35: Deleting a single row

Again, as with the UPDATE statements, we can specify conditions to allow us to delete, for example, everyone with an age less than 30. Let's do this now with the following statement:

mysql> DELETE FROM sampletable WHERE age < 30;

When we execute this statement, the client will inform us that two rows have been affected, or in this case, deleted. If we now use the SELECT command on our table, we will see that only one row is left in the table:
Screenshot-36: Conditional deleting

Finally, it is also possible to delete all the rows from a table in a single statement. All we need to do is not specify any condition, as we did when we updated all the password fields to changeme. Here is the statement to delete all the rows in a table (i.e., empty the table).

mysql> DELETE FROM sampletable;

After executing this, if we select all the information in the table using the SELECT command, the following will be shown in the console:
Screenshot-37: Deleting all the data from a table

As you can see, the table now contains no information.

Using SELECT Statements

Until now, we have simply used the following command to show all the data in our sampletable table:

mysql> SELECT * FROM sampletable;

This is actually fetching all the fields from the sampletable table and returning them. The * is a wildcard, which means basically it represents anything (or in this case, any field). Before we go into the SELECT statement further, let's first add some data to experiment with into our sampletable table. Use the following statement to insert some data:

mysql> INSERT INTO sampletable VALUES
 -> ('andrew', 'qwerty', 20, 'andrew@dreamcircle.co.uk', NULL),
 -> ('andrew', 'letmein', 27, 'andrew@email.net', NULL),
 -> ('george', 'paper', 19, 'george@email.net', NULL),
 -> ('jenny', 'jen999', 27, 'jen@email.net', NULL),
 -> ('sandra', 'sdra2', 27, 'sandra@email.net', NULL);

Here is a screen shot of how this should look when we enter it into the console and execute it:
Screenshot-38: Inserting our new data into the sampletable table

Now that we have added our data into the table, if we use the SELECT statement with the wildcard (*), as we were doing before, it will retrieve and display all of the information from the table into the console. Let's try this now with the following statement.

mysql> SELECT * FROM sampletable;

Here is a screen shot of the output from the console:
Screenshot-39: Using the wildcard with a SELECT statement

As you can see, the statement has retrieved all of the information from the table (i.e., all of the rows and all of the columns contained in each of the rows). Let's say that all we want to retrieve is the password field. To get all of the passwords from the sampletable table, we would use the following statement:

mysql> SELECT password FROM sampletable;

When we execute this statement, we can expect the following output from the console:
Screenshot-40: Retrieving only a single column

Notice how we simply replace the wildcard (*) with the column we wish to retrieve. We can also retrieve multiple columns by using a comma to delimit them. Let's try to select both the username column and password column only. Here is the statement we require for this:

mysql> SELECT username, password FROM sampletable;

When we execute this statement, we can see in the console that only the username and password fields have been selected from the table. Here is a screen shot of the console that shows this:
Screenshot-41: Retrieving multiple columns

Now that we know how to retrieve individual fields from the tables, how do we retrieve a single row? We can easily apply a condition to a SELECT statement, just as we did when we were updating the table and deleting from the table. Using a conditional SELECT statement, let's only display Jenny's information from the database. Here is the statement we require for this:

mysql> SELECT * FROM sampletable WHERE username = 'jenny';

When we execute this statement, only Jenny's details will be displayed in the MySQL client console. This can be seen in the following figure:
Screenshot-42: Selecting a single row

We can also incorporate the idea of selecting specified fields. A practical example of this would be to find the password that relates to a username. Here is how we would get the password that belonged to George:

mysql> SELECT password FROM sampletable WHERE username = 'george';

When we execute this statement, we can see that only a single field is displayed, which happens to be George's password. This can be seen in Screenshot-43.
Screenshot-43: Selecting a single row with specified columns

In our sample data, there are two rows with the username andrew. If we try to use a conditional statement to get the password for andrew, we will in fact get two passwords, one for each andrew entry in the database. Let's try this now just for proof. Here is the statement that we need:

mysql> SELECT password FROM sampletable WHERE username = 'andrew';

When we execute this statement, we can see that we have two passwords showing in the console. The result is shown in the following figure:
Screenshot-44: The two- password problem!

Later in this chapter, we will discuss a way around this problem with the use of relational databases and keys, but let's not go into that just yet. Instead let's have a look at how the LIKE command can help us find the information that we require. Using LIKE is ideal for finding strings in databases, especially if you only have a part of the complete string (i.e., for a search engine). For example, let's say that we wish to find someone in the database whose name starts with the letter j. To accomplish this, we would require the following statement:

mysql> SELECT * FROM sampletable WHERE username LIKE 'j%';

When we execute this statement, we can expect the following output from the MySQL console:
Screenshot-45: Using LIKE with a SELECT statement

Notice here how jenny was retrieved, as her username was the only one to start with a j. The % represents a wildcard when used with LIKE, so if we used the following statement instead:

mysql> SELECT * FROM sampletable WHERE username LIKE '%j%';

...the letter j could appear anywhere in the string. Also, note that you can have more than a single character:

mysql> SELECT * FROM sampletable WHERE username LIKE '%nny';

This would retrieve all of the people who have names that end with the text "nny." Finally, if we used the following statement:

select * from sampletable where username like '%nny%a';

...it would retrieve all rows containing "nny" in their names, but the name would have to end with "a".

• Previous
• Next