You can use Sequel Pro’s pull-down menu to look at the data, but this is a lot like the simple view in Dreamweaver. If you need to do anything special, you will need to understand the select command.
Let’s look for all albums that were released in 1979. Go to Custom Query and type:
select * from albums where year=1979
You should get a list of 20 rows.
Usually, we’ll want our records to be sorted a specific way. We want them in a specific order.
select * from albums where year=1979 order by rating
If we want to order by more than one column, we list all columns in the order we want them to matter.
select * from albums where year=1979 order by rating, artist, purchasedate
Finally, we also will usually only want some of the columns, not all of them. The asterisk means “give me all columns”. But usually we don’t want all columns, and in many applications, such as web pages, we could easily be receiving several requests every second. By reducing the number of columns we ask for, we can speed up whatever web pages or other applications are using our data.
select album, artist, purchasedate
from albums where year=1979 order by purchasedate, artist, album
SQL statements can often look a lot like English. Don’t let this fool you: SQL statements have a very strict syntax. The spelling, choice of words, and order of parts cannot usually be modified. For example, “where” always comes before “order by”.
Text like this
Sometimes you want to find records where a value contains some piece of text. For example, we might want to look for albums whose name begins with the word “the”.
select album, artist from albums where album like "the %" order by album
The keyword “like” is pretty much just like the equals sign for strings, except that it tells MySQL to search inside the search string for percent symbols. Wherever it finds a percent symbol, any text can be present in the actual records. In this case, we’ve told it to look for values of “album” that start with “the ” and then have any text.
We can have more than one percent in the search string. We might want to look for all of our “best of” albums, but we don’t know where “best of” appears in the album name.
select album, artist from albums where album like "%best of %"
View data: Distinct
Suppose we want a list of all of the artists whose albums we own and who came out with one of those albums in the seventies.
select artist from albums where year between 1970 and 1979 order by artist
You’ll see a lot of duplicate data. If we were displaying this on a web page, or importing it into some other document, we probably would not want the same artist listed multiple times. This is what the distinct keyword is for.
select distinct artist from albums where year between 1970 and 1979 order by artist
“Distinct” applies to all of the fields selected. It throws out any records that are exactly like a previous record. If we choose both artist and year, we will start seeing duplicate artists again, because the row is not duplicated when the same artist has albums in different years.
select distinct artist, year
from albums where year between 1970 and 1979 order by artist, year
It will, however, remove duplicate rows, where the artist came out with more than one album in a single year. If you remove distinct from the statement, you’ll see two entries for Alice Cooper in 1971. With distinct, there will be only one.
Concatenating items
You can combine multiple fields, or combine a field with some static text, using the concat function.
select concat(album, " by ", artist) from albums order by album
The concat() function takes a comma-delimited list of items and glues them together. Here, we glued together the album name, a comma and a space, and the artist name.
Now, the title of the column is not very useful. When using concat or other functions, it is often useful to specify a title for a column when it is displayed.
select concat(album, " by ", artist) as Attribution from albums order by album
The column will now be titled “Attribution”. This will make it easier to use that data in a program like Dreamweaver or a programming language such as PHP.
Formatted dates and times
The dates that MySQL gives you are formatted to be easy to enter and easy to store. They are not, however, the best format for displaying them to humans. We can format dates with the date_format() function, and times with the time_format() function. While we do not have any times in this table, the time_format() function works exactly like the date_format() function.
select album, artist, date_format(purchasedate, "%W, %M %D, %Y") as date
from albums order by artist, album, purchasedate
The _format() function use a special string of text to format the date. Any letter preceded by a percent sign will be replaced with some value based on the date. In this example, %W becomes the day of the week, %M becomes the full month name, %D becomes the day of the month with suffix, and %Y is the four-digit year. There are many such codes, and any good book on MySQL will give you the full table. Here are a few samples:
| Code | Replacement |
|---|---|
| %S | second, two digits |
| %i | minute, two digits |
| %l | hour |
| %p | AM or PM |
| %W | weekday name |
| %a | weekday name, abbreviated |
| %e | day of the month |
| %D | day of the month, with English suffix |
| %M | month name |
| %b | month name, abbreviated |
| %c | month number |
| %Y | year |
| %% | a percent sign |
