{ Berbagi, Menulis, Dan Mengajar } Ilmu… » 7. Operating System » String Manipulation MYSQL
String Manipulation MYSQL
Workshop Requirements
You should have completed Parts One, Two, Three, Four and Five 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
Thus far when retrieving stored data we have simply displayed the results of any query. MySQL can do more that this and has many built in functions that can transform data to meet our requirements. These include:
- Date Functions - used to manipulate the display format of a date as well as calculate time.
- String Functions – can manipulate a text string
- Numeric Functions - can manipulate figures
- Summarising Functions - output meta results from a query
There are also Control Functions that can be used to give conditionality to queries.
Date Functions
Before looking at the date functions in detail it is worth revisiting the various date datatypes to gain a better understanding of the limitations of date formatting.
Date Datatypes
There are 5 MySQL date datatypes these are:
| Datatype | Format | Info |
| DATETIME | YYYY-MM-DD HH:MM:SS | This stores both date and time. |
| DATE | YYYY-MM-DD | This only stores the date |
| TIMESTAMP(length) | Varies | See Below |
| TIME | HH:MM:SS | This stores only the time |
| YEAR | YYYY | Stores only the year |
The timestamp datatype is somewhat different as it stores the time that a row was last changed. The format also varies according to the length. For example to store the same information as DATETIME, you would specify a length of 14 whereas to store the DATE you would specify a length of 8.
| Timestamp Definition | Format |
| TIMESTAMP(2) | YY |
| TIMESTAMP(4) | YYYY |
| TIMESTAMP(6) | YYMMDD |
| TIMESTAMP(8) | YYYYMMDD |
| TIMESTAMP(10) | YYMMDDHHMM |
| TIMESTAMP(12) | YYMMDDHHMMSS |
| TIMESTAMP(14) | YYYYMMDDHHMMSS |
In the ‘cds’ table we have used the DATE for the ‘bought’ field.
mysql> SELECT cds.title, cds.bought
-> FROM cds;
+------------------------------+------------+
| title | bought |
+------------------------------+------------+
| A Funk Odyssey | 2001-10-10 |
| Now 49 | 2001-10-15 |
| Eurovision Song contest 2001 | 2000-09-08 |
| Abbas Greatest Hits | 2000-11-05 |
| Space Cowboy | 2001-10-10 |
| Sign of the times | 1987-11-07 |
| The White Album | 1994-07-20 |
| The Hits | 1993-10-07 |
| westlife | 2000-06-09 |
+------------------------------+------------+
9 rows in set (0.02 sec)
So to begin with let’s look at how we can manipulate these dates using MySQL’s date functions.
DATE_FORMAT()
This function allows the developer to format the date anyway that they wish by specifying a sequence of format strings. A string is composed of the percentage symbol ‘%‘ followed by a letter that signifies how you wish to display part of the date. These are some of the more common strings to use:
| String | Displays | Example |
| %d | The numeric day of the month | 01….10….17….24 etc |
| %D | The day of the month with a suffix | 1st, 2nd, 3rd…. etc |
| %m | The numeric month | 01….04….08….11 etc |
| %M | The Month name | January….April….August etc |
| %b | The Abbreviated Month Name | Jan….Apr….Aug….Nov etc |
| %y | Two digit year | 98, 99, 00, 01, 02, 03 etc |
| %Y | Four digit year | 1998, 2000, 2002, 2003 etc |
| %W | Weekday name | Monday…. Wednesday….Friday etc |
| %a | Abbreviated Weekday name | Mon….Wed….Fri etc |
| %H | Hour (24 hour clock) | 07….11….16….23 etc |
| %h | Hour (12 hour clock) | 07….11….04….11 etc |
| %p | AM or PM | AM….PM |
| %i | Minutes | 01….16….36….49 etc |
| %s | Seconds | 01….16….36….49 etc |
There are more, but that should be enough for now. There are a couple of things to note. Upper and Lowercase letters in the string make a difference and also that when arranging these strings into a sequence you can intersperse ‘normal’ characters. For example:
The sequence ‘%d/%m/%y‘, with forward slashes separating the strings, would be displayed as 01/06/03.
The next stage is to use the function DATE_FORMAT() to convert a stored time to a format we want.
Syntax:
DATE_FORMAT(date, sequence)
Thus to change the format of the cds.bought field to DD-MM-YYYY we specify the field as the date and the sequence as ‘%d-%m-%Y’.
DATE_FORMAT(cds.bought, '%d-%m-%Y')
This function is then incorporated into our SQL statement in place of the exiting cds.bought field.
mysql> SELECT cds.title, DATE_FORMAT(cds.bought, '%d-%m-%Y')
-> FROM cds;
+------------------------------+-------------------------------------+
| title | DATE_FORMAT(cds.bought, '%d-%m-%Y') |
+------------------------------+-------------------------------------+
| A Funk Odyssey | 10-10-2001 |
| Now 49 | 15-10-2001 |
| Eurovision Song contest 2001 | 08-09-2000 |
| Abbas Greatest Hits | 05-11-2000 |
| Space Cowboy | 10-10-2001 |
| Sign of the times | 07-11-1987 |
| The White Album | 20-07-1994 |
| The Hits | 07-10-1993 |
| westlife | 09-06-2000 |
+------------------------------+-------------------------------------+
9 rows in set (0.00 sec)
Dates can also be formatted in ‘plain english’.
mysql> SELECT cds.title, DATE_FORMAT(cds.bought, '%W the %D of %M %Y')
-> FROM cds;
+------------------------------+-----------------------------------------------+
| title | DATE_FORMAT(cds.bought, '%W the %D of %M %Y') |
+------------------------------+-----------------------------------------------+
| A Funk Odyssey | Wednesday the 10th of October 2001 |
| Now 49 | Monday the 15th of October 2001 |
| Eurovision Song contest 2001 | Friday the 8th of September 2000 |
| Abbas Greatest Hits | Sunday the 5th of November 2000 |
| Space Cowboy | Wednesday the 10th of October 2001 |
| Sign of the times | Saturday the 7th of November 1987 |
| The White Album | Wednesday the 20th of July 1994 |
| The Hits | Thursday the 7th of October 1993 |
| westlife | Friday the 9th of June 2000 |
+------------------------------+-----------------------------------------------+
9 rows in set (0.01 sec)
Note: DATE_FORMAT() only works with datatypes that include the date. This means DATE, DATETIME and TIMESTAMP. There is a similar function called TIME_FORMAT() that works with TIME as well as DATETIME and TIMESTAMP.
Extraction Functions
As well as using DATE_FORMAT() there are other functions that allow you to extract specific information about a date (year, month, day etc). These include:
| Function | Displays | Example |
| DAYOFMONTH(date) | The numeric day of the month | 01….10….17….24 etc |
| DAYNAME(date) | The Name of the day | Monday…. Wednesday….Friday etc |
| MONTH(date) | The numeric month | 01….04….08….11 etc |
| MONTHNAME(date) | The Month name | January….April….August etc |
| YEAR(date) | Four digit year | 1998, 2000, 2002, 2003 etc |
| HOUR(time) | Hour (24 hour clock) | 07….11….16….23 etc |
| MINUTE(time) | Minutes | 01….16….36….49 etc |
| SECOND(time) | Seconds | 01….16….36….49 etc |
| DAYOFYEAR(date) | Numeric day of the year | 1…..366 |
To give an example of one of these you can use DAYNAME() to work out which day you were born on. To do this you can specify the date directly to the function without referring to any tables or field. So for my birthday (20th July 1973):
mysql> SELECT DAYNAME('1973-07-20');
+-----------------------+
| DAYNAME('1973-07-20') |
+-----------------------+
| Friday |
+-----------------------+
1 row in set (0.00 sec)
Or you could even SELECT two or three date items.
mysql> SELECT DAYNAME('1973-07-20'), MONTHNAME('1973-07-20'), YEAR('1973-07-20');
+-----------------------+-------------------------+--------------------+
| DAYNAME('1973-07-20') | MONTHNAME('1973-07-20') | YEAR('1973-07-20') |
+-----------------------+-------------------------+--------------------+
| Friday | July | 1973 |
+-----------------------+-------------------------+--------------------+
1 row in set (0.02 sec)
Getting the Current Date and Time
There are three functions that you can use to get the current date and time. NOW() – which gets both date and time, CURDATE() which works with only the date and CURTIME() for the time.
mysql> SELECT NOW(), CURTIME(), CURDATE(); +---------------------+-----------+------------+ | NOW() | CURTIME() | CURDATE() | +---------------------+-----------+------------+ | 2003-06-02 19:44:51 | 19:44:51 | 2003-06-02 | +---------------------+-----------+------------+ 1 row in set (0.01 sec)
Changing Date Values
There are two functions that allow you to add and subtract time to a date. These are DATE_ADD() and DATE_SUB().
Syntax:
DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type)
The date – is a standard DATE or DATETIME value, next come the command INTERVAL followed by the time period (expr) and finally what type period it is (Month, Day, Year etc). Therefore to work out the date 60 days in the future:
mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 60 DAY); +--------------------------------------+ | DATE_ADD(CURDATE(), INTERVAL 60 DAY) | +--------------------------------------+ | 2003-08-01 | +--------------------------------------+ 1 row in set (0.00 sec)
Or 6 months in the past:
mysql> SELECT DATE_SUB(CURDATE(), INTERVAL 6 MONTH); +---------------------------------------+ | DATE_SUB(CURDATE(), INTERVAL 6 MONTH) | +---------------------------------------+ | 2002-12-02 | +---------------------------------------+ 1 row in set (0.00 sec)
We can also format this result as well using DATE_FORMAT() and using an alias to tidy up the title:
mysql> SELECT
-> DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH), '%W the %D of %M %Y')
-> AS 'Six Months Ago';
+---------------------------------+
| Six Months Ago |
+---------------------------------+
| Monday the 2nd of December 2002 |
+---------------------------------+
1 row in set (0.01 sec)
By now you should have the idea and thus I’m not going to carry on and extensively cover all the functions, the MySQL manual is probably the best place to look to see all the date functions.
String Functions
String values are can be explained as ‘bits of text’ and much like the date functions, the string functions allow us to manipulate these values before they are displayed. Although there are once more many different functions, I’m going to concentrate on the functions that fall into a few broad categories.
- Adding text to an existing value
- Changing Part of a String
- Extracting Text from a String
- Finding a piece of text in a string
Adding text to an existing value
There are two simple ways to add more text to an existing value – either at the start or end of the text. Placing the text at either end is best achieved with the CONCAT() function.
Syntax:
CONCAT(string1,string2,...)
Thus we can take an existing value (say string2) and place a new value (string1) at the beginning to get string1string2. To see this in action let’s retrieve the title of The Beatles ‘The White Album’ (which was entered in part 8).
mysql> SELECT cds.title
-> FROM cds WHERE cdID='20';
+-----------------+
| title |
+-----------------+
| The White Album |
+-----------------+
1 row in set (0.00 sec)
If we wanted to add the text “The Beatles’ ” at the beginning.
+-----------------------------------+
| CONCAT("The Beatles' ",cds.title) |
+-----------------------------------+
| The Beatles' The White Album |
+-----------------------------------+
1 row in set (0.04 sec)
Or if we wanted to say “By The Beatles” at the end.
mysql> SELECT CONCAT(cds.title," By The Beatles")
-> FROM cds WHERE cdID='20';
+-------------------------------------+
| CONCAT(cds.title," By The Beatles") |
+-------------------------------------+
| The White Album By The Beatles |
+-------------------------------------+
1 row in set (0.00 sec)
Changing Part of a String
As well as add text we can replace it or overwrite it completely. To replace an instance of text within a string we can use the REPLACE() function.
Syntax:
REPLACE(whole_string,to_be_replaced,replacement)
Therefore if we wanted to replace the word ‘White‘ with the word ‘Black‘ in the cds.title:
mysql> SELECT REPLACE(cds.title,'White','Black')
-> FROM cds WHERE cdID='20';
+------------------------------------+
| REPLACE(cds.title,'White','Black') |
+------------------------------------+
| The Black Album |
+------------------------------------+
1 row in set (0.02 sec)
Or just to be silly (and to demonstrate that each occurrence in the string is changed) let’s swap the ‘e‘ for an ‘a‘.
mysql> SELECT REPLACE(cds.title,'e','a')
-> FROM cds WHERE cdID='20';
+----------------------------+
| REPLACE(cds.title,'e','a') |
+----------------------------+
| Tha Whita Album |
+----------------------------+
1 row in set (0.00 sec)
Another Function we can use to add text is the INSERT() function that overwrites any text in the string from a start point for a certain length.
Syntax:
INSERT(string,start_position,length,newstring)
In this case the crucial bits of information are the position to start (how many characters from the begriming) and the length. So again to replace ‘White‘ (which starts at character 5 in the string) with ‘Black‘ in the title we need to start at position 5 for a length of 5.
mysql> SELECT INSERT(cds.title,5,5,'Black')
-> FROM cds WHERE cdID='20';
+-------------------------------+
| INSERT(cds.title,5,5,'Black') |
+-------------------------------+
| The Black Album |
+-------------------------------+
1 row in set (0.01 sec)
If we alter the position (say to 3) you can see that the exchange doesn’t work properly.
mysql> SELECT INSERT(cds.title,3,5,'Black')
-> FROM cds WHERE cdID='20';
+-------------------------------+
| INSERT(cds.title,3,5,'Black') |
+-------------------------------+
| ThBlackte Album |
+-------------------------------+
1 row in set (0.00 sec)
Similarly if we were to shorten the length to 3 (resetting the position to 5) not all of the word ‘White’ gets overwritten.
mysql> SELECT INSERT(cds.title,5,3,'Black')
-> FROM cds WHERE cdID='20';
+-------------------------------+
| INSERT(cds.title,5,3,'Black') |
+-------------------------------+
| The Blackte Album |
+-------------------------------+
1 row in set (0.00 sec)
Thus using this knowledge we can insert text into the middle of a string by setting the length to ‘0‘ (so it doesn’t overwrite anything). Let’s make the title ‘Black and White‘:
mysql> SELECT INSERT(cds.title,5,0,'Black and ')
-> FROM cds WHERE cdID='20';
+------------------------------------+
| INSERT(cds.title,5,0,'Black and ') |
+------------------------------------+
| The Black and White Album |
+------------------------------------+
1 row in set (0.00 sec)
Extracting Text from a String.
As well as adding text to a string we can also use functions to extract specific data from a string. To begin with lets look at three LEFT(), RIGHT() and MID().
Syntax:
LEFT(string,length) RIGHT(string,length) MID(string,start_position,length)
The first two, LEFT() and RIGHT(), are fairly straight forward. You specify the string and the length of the string to keep, relative to either the left or right depending on which function you are using. So to keep the words ‘The‘ (which occupies 3 characters on the left) and ‘Album‘ (5 characters on the right) we would specify:
mysql> SELECT LEFT(cds.title,3), RIGHT(cds.title,5)
-> FROM cds WHERE cdID='20';
+-------------------+--------------------+
| LEFT(cds.title,3) | RIGHT(cds.title,5) |
+-------------------+--------------------+
| The | Album |
+-------------------+--------------------+
1 row in set (0.00 sec)
The MID() function is only slightly complex. You still specify the length, but also a starting position. So to keep the work ‘White‘, you would start at position 5 and have a length of 5.
mysql> SELECT MID(cds.title,5,5)
-> FROM cds WHERE cdID='20';
+--------------------+
| MID(cds.title,5,5) |
+--------------------+
| White |
+--------------------+
1 row in set (0.03 sec)
There is also another extraction function that is is worth mentioning; SUBSTRING().
Syntax:
SUBSTRING(string,position)
This returns all of the string after the position. Thus to return ‘White Album‘ you would start at ‘5‘.
mysql> SELECT SUBSTRING(cds.title,5)
-> FROM cds WHERE cdID='20';
+------------------------+
| SUBSTRING(cds.title,5) |
+------------------------+
| White Album |
+------------------------+
1 row in set (0.00 sec)
Finding a piece of text in a string.
In some of the string functions we have seen so far it has been necessary to provide a starting position as part of the function This position can be found using the LOCATE() function specifying the text to find (substring) as well as the string to search in.
Syntax:
LOCATE(substring,string)
So to find the location of ‘White‘:
mysql> SELECT LOCATE('White',cds.title)
-> FROM cds WHERE cdID='20';
+---------------------------+
| LOCATE('White',cds.title) |
+---------------------------+
| 5 |
+---------------------------+
1 row in set (0.06 sec)
If a substring is not present then ‘0’ is returned.
mysql> SELECT LOCATE('Black',cds.title)
-> FROM cds WHERE cdID='20';
+---------------------------+
| LOCATE('Black',cds.title) |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.00 sec)
It is also possible to automatically calculate the length of a piece of text using LENGTH().
Syntax:
LENGTH(string)
So with the word ‘White‘.
mysql> SELECT LENGTH('White');
+-----------------+
| LENGTH('White') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.03 sec)
Therefore with these results who these can be combined with one of the other functions. For example with the MID() function.
mysql> SELECT MID(cds.title,LOCATE('White',cds.title),LENGTH('White'))
-> FROM cds WHERE cdID='20';
+----------------------------------------------------------+
| MID(cds.title,LOCATE('White',cds.title),LENGTH('White')) |
+----------------------------------------------------------+
| White |
+----------------------------------------------------------+
1 row in set (0.01 sec)
The LENGTH() of ‘White‘ is worked out, the position of ‘White‘ is worked out using LOCATE() and these values are included within the MID() function. The result is that White is returned.
Transforming Strings
The final group of string functions this workshop will look at are those that transform the string in some way. The first two change the case of the string to either uppercase – UCASE() – or to lowercase – LCASE().
Syntax:
LCASE(string) UCASE(string)
As you can imagine the usage of these are fairly straightforward.
mysql> SELECT LCASE(cds.title), UCASE(cds.title)
-> FROM cds WHERE cdID='20';
+------------------+------------------+
| LCASE(cds.title) | UCASE(cds.title) |
+------------------+------------------+
| the white album | THE WHITE ALBUM |
+------------------+------------------+
1 row in set (0.01 sec)
The last string function this workshop will examine is REVERSE().
Syntax:
REVERSE(string)
This rather obviously reverses the order of the letters. For example the alphabet.
mysql> SELECT REVERSE('abcdefghijklmnopqrstuvwxyz');
+---------------------------------------+
| REVERSE('abcdefghijklmnopqrstuvwxyz') |
+---------------------------------------+
| zyxwvutsrqponmlkjihgfedcba |
+---------------------------------------+
1 row in set (0.00 sec)
Once more there are more string functions that the MySQL Manual documents.
Numeric Functions
Before talking about the specific numeric functions, it is probably worth mentioning that MySQL can perform simple math functions using mathematical operators.
| Operator | Function |
| + | Add |
| - | Subtract |
| * | Multiply |
| / | Divide |
Examples:
mysql> SELECT 6+3; +-----+ | 6+3 | +-----+ | 9 | +-----+ 1 row in set (0.00 sec) mysql> SELECT 6-3; +-----+ | 6-3 | +-----+ | 3 | +-----+ 1 row in set (0.01 sec) mysql> SELECT 6*3; +-----+ | 6*3 | +-----+ | 18 | +-----+ 1 row in set (0.00 sec) mysql> SELECT 6/3; +------+ | 6/3 | +------+ | 2.00 | +------+ 1 row in set (0.22 sec)
There are also other functions that serve a more specific math function and we shall have a look at a few of these.
FLOOR()
This reduces any number containing decimals to the lowest whole number.
Syntax:
SELECT FLOOR(number)
Example:
mysql> SELECT FLOOR(4.84); +-------------+ | FLOOR(4.84) | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)
CEILING()
Raises a number containing decimals to the highest whole number.
Syntax:
SELECT CEILING(number)
Example:
mysql> SELECT CEILING(4.84); +---------------+ | CEILING(4.84) | +---------------+ | 5 | +---------------+ 1 row in set (0.01 sec)
ROUND()
This function, as you may have guessed, rounds the figures up or down to the nearest whole number (or to a specified number of decimal places).
Syntax:
ROUND(number,[Decimal Places])
‘Decimal Places‘ is optional and omitting it will mean that the figure is rounded to a whole number.
Examples:
mysql> SELECT ROUND(14.537); +---------------+ | ROUND(14.537) | +---------------+ | 15 | +---------------+ 1 row in set (0.01 sec) mysql> SELECT ROUND(14.537,2); +-----------------+ | ROUND(14.537,2) | +-----------------+ | 14.54 | +-----------------+ 1 row in set (0.00 sec)
TRUNCATE()
This function, rather than rounding, simply shortens the number to a required decimal place.
Syntax:
TRUNCATE(number,places)
Example:
mysql> SELECT TRUNCATE(14.537,2); +--------------------+ | TRUNCATE(14.537,2) | +--------------------+ | 14.53 | +--------------------+ 1 row in set (0.00 sec)
The interesting thing about truncate is that if you specify a negative number for the ‘places’, it replaces the existing numbers in those places with zeros. So for example 545 to ‘-2‘ becomes 500.
mysql> SELECT TRUNCATE(545,-2); +------------------+ | TRUNCATE(545,-2) | +------------------+ | 500 | +------------------+ 1 row in set (0.00 sec)
Summary Functions
The MySQL manual describes this group of functions as ‘ Functions for Use with GROUP BY Clauses‘ which I think is a little misleading as they can be used in queries where there are no GROUP BY clauses (see Part 3 for a refresher on GROUP BY). Thus is it is perhaps better (if probably not strictly correct) to think of them as functions that report information about a query (for example the number of rows), rather than simply display or manipulate directly the data retrieved.
COUNT()
This counts the number of times a row (or field) is returned.
Syntax:
COUNT(field)
The most common usage for this is just to specify an asterisks as the field to count the number of rows (or in this case cds).
mysql> SELECT COUNT(*) as 'Number of Cds'
-> FROM cds;
+---------------+
| Number of Cds |
+---------------+
| 9 |
+---------------+
1 row in set (0.00 sec)
You could also choose to count just one field:
mysql> SELECT COUNT(cds.title) as 'Number of Cds'
-> FROM cds;
+---------------+
| Number of Cds |
+---------------+
| 9 |
+---------------+
1 row in set (0.00 sec)
There may be occasions that we would want to count the DISTINCT occurrences in a field. Let’s look at the artist ID field to demonstrate.
mysql> SELECT COUNT(cds.artistID)
-> FROM cds;
+-----------------+
| COUNT(artist ID) |
+-----------------+
| 9 |
+-----------------+
1 row in set (0.00 sec)
Now my database contain two artists that have 2 cds in the database (Various and Prince) each of whom have been counted twice. Thus we could try using DISTINCT as part of the query to find out how many artists have cds in the database.
mysql> SELECT DISTINCT COUNT(cds.artistID)
-> FROM cds;
+-----------------+
| COUNT(artist ID) |
+-----------------+
| 9 |
+-----------------+
1 row in set (0.00 sec)
As we can see this doesn’t work as each row is indeed unique and thus returns the total of cds again. However if we include DISTINCT as part of the COUNT() function it does work.
mysql> SELECT COUNT(DISTINCT cds.artistID)
-> FROM cds;
+--------------------------+
| COUNT(DISTINCT artist ID) |
+--------------------------+
| 7 |
+--------------------------+
1 row in set (0.00 sec)
AVG()
The next function we are going to look at is the AVG() which unsurprisingly is the average function.
Syntax:
AVG(field)
Lets look that the tracks field and work out the average number of tracks per CD.
mysql> SELECT AVG(cds.tracks)
-> FROM cds;
+-------------+
| AVG(tracks) |
+-------------+
| 25.6667 |
+-------------+
1 row in set (0.01 sec)
As that is simply but AVG() can work out more that one value at a time when used with a GROUP BY clause.
mysql> SELECT cds.artistID,AVG(cds.tracks)
-> FROM cds
-> GROUP BY cds.artistID;
+----------+-------------+
| artist ID | AVG(tracks) |
+----------+-------------+
| 1 | 11.5000 |
| 2 | 40.0000 |
| 3 | 17.0000 |
| 4 | 23.0000 |
| 5 | 24.0000 |
| 17 | 30.0000 |
| 18 | 37.0000 |
+----------+-------------+
7 rows in set (0.01 sec)
As you can see an average is produced for each artist (not that we know who they are yet) when the artist ID is specified in the GROUP BY clause. Although AVG() is fairly straight forward, lets take this opportunity to ‘tidy things up’ and see how it can work with other functions and also with a join. So first left’s round the averages to one decimal place:
mysql> SELECT cds.artistID, ROUND(AVG(cds.tracks),1)
-> FROM cds
-> GROUP BY cds.artistID;
+----------+----------------------+
| artist ID | ROUND(AVG(tracks),1) |
+----------+----------------------+
| 1 | 11.5 |
| 2 | 40.0 |
| 3 | 17.0 |
| 4 | 23.0 |
| 5 | 24.0 |
| 17 | 30.0 |
| 18 | 37.0 |
+----------+----------------------+
7 rows in set (0.04 sec)
…then perform a join on the artists table to retrieve and display the artists’ names in place of the artist ID:
mysql> SELECT artists.Artist, ROUND(AVG(cds.tracks),1)
-> FROM cds
-> LEFT JOIN artists
-> USING (artist ID)
-> GROUP BY artists.Artist;
+-------------+--------------------------+
| Artist | ROUND(AVG(cds.tracks),1) |
+-------------+--------------------------+
| Abba | 24.0 |
| Jamiroquai | 11.5 |
| Prince | 37.0 |
| The Beatles | 30.0 |
| Various | 31.5 |
| westlife | 17.0 |
+-------------+--------------------------+
6 rows in set (0.01 sec)
MIN() and MAX()
These functions are very similar and select the lowest and highest figure respectively from a result set.
Syntax:
MIN(field) MAX(field)
So a simple example would be to display the least number and most number of tracks that any cd in the database has.
mysql> SELECT MIN(cds.tracks), MAX(cds.tracks)
-> FROM cds;
+-----------------+-----------------+
| MIN(cds.tracks) | MAX(cds.tracks) |
+-----------------+-----------------+
| 11 | 58 |
+-----------------+-----------------+
1 row in set (0.00 sec)
SUM()
The final summary function that we will look at is the SUM() function which adds rows of one field in the results set together.
Syntax:
SUM(field)
So another simple example would be to add the total number of tracks in the CD collection.
mysql> SELECT SUM(tracks)
-> FROM cds;
+-------------+
| SUM(tracks) |
+-------------+
| 231 |
+-------------+
1 row in set (0.03 sec)
Control Functions
The final set of functions that this workshop will look at are the control functions that allow us a degree of conditionality when returning result sets.
IF()
The IF() function is fairly straight forward and consists of 3 elements. A condition and values for the condition being evaluated either true or false.
Syntax:
IF(condition,true_value,false_value)
So using a simple comparison (is a number greater than 10) to return either ‘yup’ or ‘nope’.
mysql> SELECT IF(15>10,'Yup','Nope'); +------------------------+ | IF(15>10,'Yup','Nope') | +------------------------+ | Yup | +------------------------+ 1 row in set (0.03 sec) mysql> SELECT IF(5>10,'Yup','Nope'); +-----------------------+ | IF(5>10,'Yup','Nope') | +-----------------------+ | Nope | +-----------------------+ 1 row in set (0.01 sec)
As well as returned a string value, a number can also be returned. Thus if we wanted to count how many cds had more that 15 tracks we could return ‘1’ for a true match and a NULL value for a false match.
mysql> SELECT IF(cds.tracks>15,1,NULL)
-> FROM cds;
+--------------------------+
| IF(cds.tracks>15,1,NULL) |
+--------------------------+
| NULL |
| 1 |
| 1 |
| 1 |
| NULL |
| 1 |
| 1 |
| 1 |
| 1 |
+--------------------------+
9 rows in set (0.00 sec)
We can then use COUNT() to give us a total as it ignores NULL values.
mysql> SELECT COUNT(IF(cds.tracks>15,1,NULL))
-> FROM cds;
+---------------------------------+
| COUNT(IF(cds.tracks>15,1,NULL)) |
+---------------------------------+
| 7 |
+---------------------------------+
1 row in set (0.01 sec)
CASE
Slightly more advanced from IF() is the CASE function that allows for than one comparison to be made. It is slightly different as the actual value is specified first, then a series of comparisons are made for a potential match that then returns a value.
Syntax:
CASE actual_value WHEN potential_value1 THEN return_value1 WHEN potential_value2 THEN return_value2... etc END
Thus if we were to evaluate numeric values and return their string values.
mysql> SELECT CASE 2
-> WHEN 1 THEN 'One'
-> WHEN 2 THEN 'Two'
-> WHEN 3 THEN 'Three'
-> END;
+--------------------------------------------------------------------+
| CASE 2 WHEN 1 THEN 'One' WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' END |
+--------------------------------------------------------------------+
| Two |
+--------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT CASE 1
-> WHEN 1 THEN 'One'
-> WHEN 2 THEN 'Two'
-> WHEN 3 THEN 'Three'
-> END;
+--------------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'One' WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' END |
+--------------------------------------------------------------------+
| One |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE 3
-> WHEN 1 THEN 'One'
-> WHEN 2 THEN 'Two'
-> WHEN 3 THEN 'Three'
-> END;
+--------------------------------------------------------------------+
| CASE 3 WHEN 1 THEN 'One' WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' END |
+--------------------------------------------------------------------+
| Three |
+--------------------------------------------------------------------+
1 row in set (0.01 sec)
Notice that the actual_value is the only thing that changes in the statement. Again you could also return numeric values. Consider the following short example in which I have competed in various competitions around Scotland (sport undetermined ;-).
mysql> SELECT * FROM comp; +------+-----------+----------+ | id | location | position | +------+-----------+----------+ | 1 | Aberdeen | 2 | | 2 | Edinburgh | 1 | | 3 | Glasgow | 1 | | 4 | Dundee | 3 | +------+-----------+----------+ 4 rows in set (0.00 sec)
I know that 10 points are awarded for 1st, 7 points for second and 5 points for third. I can use the case statement to work out the total points I have.
mysql> SELECT CASE comp.position
-> WHEN 1 THEN 10
-> WHEN 2 THEN 7
-> WHEN 3 THEN 5
-> END
-> AS points
-> FROM comp;
+--------+
| points |
+--------+
| 7 |
| 10 |
| 10 |
| 5 |
+--------+
4 rows in set (0.00 sec)
A SUM() function can be added to this statement to give us the total.
mysql> SELECT SUM(CASE comp.position
-> WHEN 1 THEN 10
-> WHEN 2 THEN 7
-> WHEN 3 THEN 5
-> END)
-> AS points
-> FROM comp;
+--------+
| points |
+--------+
| 32 |
+--------+
1 row in set (0.00 sec)
IFNULL()
The Final function we will look at is IFNULL and unsurprisingly this is a very simple syntax that is similar to IF(). The difference is that that instead of there being TRUE and FALSE return values based on a condition, the original value is returned if it is not NULL and a different new value is returned if it is NULL.
Syntax:
IFNULL(original_value, new_value)
To quickly demonstrate test one query with a NULL value and another with a real value.
mysql> SELECT IFNULL(NULL,'The value is Null'); +----------------------------------+ | IFNULL(NULL,'The value is Null') | +----------------------------------+ | The value is Null | +----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT IFNULL(10,'The value is Null'); +--------------------------------+ | IFNULL(10,'The value is Null') | +--------------------------------+ | 10 | +--------------------------------+ 1 row in set (0.00 sec)
This is particularly useful for converting NULL values to actual numeric zeros. To illustrate let’s set the track value from ‘The White Album’ to NULL.
mysql> UPDATE cds
-> SET cds.tracks = NULL
-> WHERE (cdID = "20");
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT cds.title, cds.tracks
-> FROM cds;
+------------------------------+--------+
| title | tracks |
+------------------------------+--------+
| A Funk Odyssey | 11 |
| Now 49 | 40 |
| Eurovision Song contest 2001 | 23 |
| Abbas Greatest Hits | 24 |
| Space Cowboy | 12 |
| Sign of the times | 16 |
| The White Album | NULL |
| The Hits | 58 |
| westlife | 17 |
+------------------------------+--------+
9 rows in set (0.00 sec)
So we can use the IFNULL() function to specify a zero in place of the NULL figure.
mysql> SELECT cds.title, IFNULL(cds.tracks,0)
-> FROM cds;
+------------------------------+----------------------+
| title | IFNULL(cds.tracks,0) |
+------------------------------+----------------------+
| A Funk Odyssey | 11 |
| Now 49 | 40 |
| Eurovision Song contest 2001 | 23 |
| Abbas Greatest Hits | 24 |
| Space Cowboy | 12 |
| Sign of the times | 16 |
| The White Album | 0 |
| The Hits | 58 |
| westlife | 17 |
+------------------------------+----------------------+
9 rows in set (0.00 sec)
Conclusion
I have by no means covered all the functions that MySQL possesses and even some of those discussed above have additional syntax elements that have been excluded from this workshop for reasons of brevity and ‘keeping things simple’. That said I would wager that what has been covered would be enough 99% of the time. The final thing I would like to mention about functions is that if you want you can define your own to some degree using ‘User Defined Functions‘, but I don’t think these workshops are the correct place to discuss that either.
Filed under: 7. Operating System