SQL Data Manipulation: Select Insert Delete and Modify SQL data

This tutoriald focuses on how to add, modify, and remove data from tables in the database using SQL 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 tutorial. 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;

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.

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);

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)

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.

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 tutotial, 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;

Note

Notice how the datecreated field reflects the time and date when we added the row into the table. This is because we specified NULL when we added the row, and doing this will make a TIMESTAMP field grab the current date and time from the system by default.

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@chopsewage.com', NULL),      
-> ('jim', 'letmein', 23, 'jim@email.net', NULL),      
-> ('wes', 'opensesame', 31, 'wes@email.net', NULL);

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.

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';

Caution

The UPDATE command (as well as all of the SQL commands) is quite powerful. With reckless use you can destroy a lot of data with a simple mistake. Almost every query should have at least one where condition.

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;

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.

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.

Tip

A useful idea is to update a timestamp field with NULL. This will retrieve the latest time from the system that the database is running on (i.e., a practical use would be to note the last time a player logged in).

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).

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.

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;

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);

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;

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;

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.

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.

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.

Note

When we specify specific fields, as in this example, we are not limited to the fields that we are selecting for use in the WHERE clause.

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.

Note

Duplicates can be removed from the result by using the DISTINCT option. For example: SELECT DISTINCT username FROM sampletable.

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%';

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".