Working with data: View data

  1. Import data
  2. Working with data
  3. Backup Data

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.

image 9

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

image 10

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.

image 11

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

image 12

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
  1. Import data
  2. Working with data
  3. Backup Data