Using MySQL - Manipulating Data
Workshop Requirements
You should have completed Parts One and Two of this series.
You should also have access to the MySQL command line client software.
You should also have full permissions on a database.
Introduction
In the previous MySQL Virtual Workshops we have looked at logging into MySQL, creating tables, adding and selecting data using SQL statements. In this Workshop we will continue to look at other SQL commands, specifically updating and deleting, and revisit the SELECT command to examine different operators and options.
UPDATE-ing Records
The UPDATE SQL statement is similar to the SELECT statement as there must be a WHERE condition to specify which record(s) to change.
UPDATE <table_name> SET <column_name> = 'new_value' WHERE (<column_name> = 'some_value');
So to change the 'title' in the first row of our 'cds' table from:
| 1 | jamiroquai | A Funk Odyssey | 2001 | Sony Soho2 | 2001-09-01 |
to:
| 1 | jamiroquai | Wrong Title | 2001 | Sony Soho2 | 2001-09-01 |
We would use the following statement:
mysql> UPDATE cds
-> SET cds.title = 'Wrong Title'
-> WHERE (cds.cdID = '1');
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
The value '1' in the 'cdID' column is the unique identifier that allows us to change values. You can issue a SELECT statement if you want to check the changes.
mysql> SELECT * FROM cds; +------+------------+----------------+------+------------+------------+ | cdID | artist | title | year | label | bought | +------+------------+----------------+------+------------+------------+ | 1 | jamiroquai | Wrong Title | 2001 | Sony Soho2 | 2001-09-01 | | 2 | jamiroquai | A Funk Odyssey | 2001 | Sony Soho2 | 2001-09-01 |
We could also change multiple columns using one statement
mysql> UPDATE cds SET cds.title = 'Other Title',
-> cds.artist = 'artist'
-> WHERE (cds.cdID = '1');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
The DELETE statement
By using the UPDATE statement we have altered the 'cds' table so that there are no longer duplicate records in rows 1 and 2. However we do have an incorrect record in row 1 and need to DELETE it. The syntax for deleting rows is:
DELETE FROM <table_name> WHERE (<column_name> = 'some_value');
Thus to remove our incorrect record
mysql> DELETE FROM cds
-> WHERE (cds.cdID = '1');
Query OK, 1 row affected (0.00 sec)
NOTE: When a record is deleted all the fields are deleted INCLUDING the unique identifier, and the database does not renumber this auto_increment COLUMN. So it is important to remember that the ID is not the same as the row number, e.g. in our example row 2 has a cdID of 3, row 3 - cdID of 4 etc.
SELECT Revisited
In the first MySQL Virtual Workshop we looked at using the SELECT SQL statement to retrieve data from a table. This was fairly limited in scope as we could only retrieve data which matched a specific text string. In this section we will look at retrieving data based on other criteria.
The first of these we will look at is Selecting a Number Range. We have created two numeric COLUMNs in our 'cds' TABLE: the 'cdID' and 'Year' COLUMNs. As each 'cdID' entry is unique, we will use the 'Year' COLUMN in the following examples. It is also important to note that while we may recognise an entry in the 'Year' COLUMN as an actual year MySQL only recognises it as a number.
Simple stuff first to select all from one year
mysql> SELECT * FROM cds
-> WHERE (cds.year = 2001);
Note that as the 'year' COLUMN is numeric rather than a text string we do not need to enclose our search term in apostrophes (although if we did it would treat the number '2001' as a text string and find it anyway). If we wanted to expand our search term to include other years (as well as 2001) we need to use numeric operators.
Numeric Operators
Numeric operators are used to compare two numbers under several conditions.
| Operator |
Condition |
| = |
Equal to |
| <> |
Not equal to |
| > |
Greater than |
| < |
Less than |
| >= |
Greater than or equal to |
| <= |
Less than or equal to |
Thus to display all cds that were released before 2001:
mysql> SELECT * FROM cds
-> WHERE (cds.year < 2001);
Or all cds in / after 1990:
mysql> SELECT * FROM cds
-> WHERE (cds.year >= 1990);
So we are able to select records by a column's numeric content.
Other Operators
There are two other operators so far unmentioned, the LIKE and BETWEEN operators.
The LIKE operator is used when we want to match part of the data in a field by using the '%' wildcard. So if we wanted to search the 'title' field for all albums beginning with 'A' we would use the following statement.
mysql> SELECT * FROM cds
-> WHERE (cds.title LIKE 'A%');
The 'A' in the search string is followed by our wildcard character. To match the word 'the' in any title we would put a wildcard character before and afterwards in the SQL statement.
mysql> SELECT * FROM cds
-> WHERE (cds.title LIKE '%the%');
The BETWEEN statement is used to add more functionality to a condition, allowing us to select a range of values from a column.
SELECT <column_name> FROM <table_name> WHERE (<column_name> BETWEEN value1 AND value2)
Thus we could select a numeric range
mysql> SELECT * FROM cds
-> WHERE (cds.year BETWEEN 1995 AND 1998);
Or we could select an alphanumeric range. To select all artists alphabetically between 'Elvis' and 'Michael Jackson'
mysql> SELECT * FROM cds
-> WHERE (cds.artist BETWEEN 'Elvis' AND 'Michael Jackson');
Which might return 'Frank Sinatra' or 'Led Zeppelin'.
Working With Dates
A typical entry for a date might look something like this.
mysql> SELECT cds.bought FROM cds; +------------+ | bought | +------------+ | 2001-09-01 | +------------+ 1 row in set (0.01 sec)
You could imagine trying to SELECT dates using 'Less Than' or 'More Than' would be difficult as a date is not a sequential decimal number. However that is why we specify the 'DATE' datatype when creating the field in the table, as this allows comparisons of this type. What actually happens is that the MySQL DBMS converts any date into the number of days since year '0' before doing any comparison.
Try SELECTing cds that you bought prior to the year 2000
mysql> SELECT * FROM cds
-> WHERE (cds.bought < '2001-01-01');
And cds that were bought this century.
mysql> SELECT * FROM cds
-> WHERE (cds.bought >= '2001-01-01');
Other Tricks When Displaying Data
Before leaving the SELECT command alone again for a while, we should touch upon a few popular ways to control output.
DISTINCT
The DISTINCT qualifier is used to return unique results and is included as part of the SELECT statement (i.e. before any conditions are applied). Let us image that our 'cds' table contained over 2000 rows. If we wanted to look at all the artists whose name started with 'E' we might construct a statement like so.
mysql> SELECT artist FROM cds
-> WHERE cds.artist LIKE 'E%';
This would return each row that had an artist whose name started with 'E'. If that artist has 10 cds in the database his name will be returned 10 times (once for each cd). We could avoid this by using the DISTINCT qualifier, which would ensure that each artist was returned only once.
mysql> SELECT DISTINCT cds.artist FROM cds
-> WHERE cds.artist LIKE 'E%';
Hierarchy of Conditions
The conditions that control the filtering of the query exist in a rough (if not quite exact) hierarchal order:
WHERE
|
GROUP BY
| |
HAVING ORDER BY
| |
LIMIT
If more than one of these conditions are to used in a query then it is recommended that this order be used, so that GROUP BY cannot go before the WHERE condition and LIMIT cannot go before HAVING etc. Notice that HAVING and ORDER BY are at the same level - this is because either one can go before the other. Breaking this hierarchy will generally result in errors.
We have already looked at the WHERE condition extensively, so lets have a closer look at these conditions.
GROUP BY
When we issue a SELECT command we are shown the results in the order that the records were entered. We can change this by using the 'GROUP BY' directive, allowing us to display the data grouped by field. To return the results ordered by artist:
mysql> SELECT * FROM cds
-> GROUP BY cds.artist;
ORDER BY
Similar to GROUP BY, this condition further allows control over the result set. ORDER BY controls the sequence of results. For example to display all the data from the 'cds' table ordered alphabetically by artist we would issue this command.
mysql> SELECT * FROM cds ORDER BY cds.artist;
You can also choose to sort the results in reverse by appending DESC to the condition
mysql> SELECT * FROM cds ORDER BY cds.artist DESC;
We can also use two or more ORDER BY conditions, for example if we wanted to list by artist then the date bought in descending order we could enter.
mysql> SELECT * FROM cds ORDER BY cds.artist, cds.bought DESC;
HAVING
The HAVING condition is really just another WHERE condition that acts as a 'secondary constraint' on the result set. This works best when you are trying to apply a restrictive condition after a grouping has taken place. So for example:
mysql> SELECT * FROM cds
-> GROUP BY cds.artist
-> HAVING cds.title LIKE 'A%';
You should try if at possible to write queries using the WHERE condition rather than the HAVING condition as HAVING is un-optimised. Most people use HAVING to work out summaries of data returned - such as the number or rows etc.
LIMIT
There will be times that you create a query that produces a large result set that you don't want to view or handle all at once. In these situations it is useful to use the LIMIT condition to restrict the number or records returned. For example to display the first 5 records that meet a query:
mysql> SELECT * FROM cds LIMIT 5;
To retrieve the rest of the records in batches we can also specify a starting point before the number or rows to return.
SELECT <fields> FROM <TABLE> LIMIT <starting_point>, <number_of_rows>
So to return rows 6-10 we would specify the start point of 5 and then ask to return 5 records.
mysql> SELECT * FROM cds LIMIT 5, 5
Or to start at rows 3-7:
mysql> SELECT * FROM cds LIMIT 2, 4
Mini Exercise
If you have any artists with more that one cd in your 'cds' table try using the DISTINCT qualifier or using the Group By condition.