Working with data

If you are using CocoaMySQL, go to the “Query” tab. If you are using the command line, make sure you are in the mysql command line, and remember to end your statements with semicolons.

From this point on, I’m assuming that you have access to a MySQL database and that your username and password have been set up to let you work on that database.

You will also need the Songs and Albums source files, available at http://www.hoboes.com/NetLife/MySQL/sources.tar.gz.

Connect

If you are not connected to your MySQL server, connect to your server the same way you did earlier.

Change password

It is a good idea to change your password regularly. Usually, your initial password is sent to you by way of e-mail. E-mail is notoriously insecure, so it is always a good idea to change your password as soon as you connect the first time.

set password = password(‘newpassword’)

Choose database

In CocoaMySQL, you’ll choose your database from the pull-down menu in the upper left. On the command line, you’ll choose your database by typing “use databasename;” and pressing the return key on your keyboard.

Choose the “music” database.

About Tables and Columns

All of your data in MySQL is stored in tables. When you view tables, they look a lot like spreadsheets. Each table consists of rows and columns. A row is one record; it is one set of data. A column is a field: it is one piece of data that each record has.

For example, if you are keeping a database of your record albums, you will have a row for Alice Cooper’s “Brutal Planet”. You will have a column for the artist’s name (“Alice Cooper”) and the album name (“Brutal Planet”).

Basic field types

In MySQL, you must specify what kind of data will go into columns. The two field types you’ll use most often are probably varchar and int. These are strings or text, and numbers. The title of an album and the artist is an example of text. If you also keep track of the year that the album came out, this is a number.

With varchar, you need to specify the maximum size of the text. If you specify a size that is too low, extra text will be truncated. You can always go back and change your mind, however.

Dates

Dates are another common field type. You will enter dates as Year-Month-Day. For example, you might keep track of the day you purchased each album. If you bought Brutal Planet on January 3, 2005, you would enter this into your MySQL date column as 2005-1-3.

Create a table

We’re going to keep track of albums and tracks. We’ll create the albums table first. Before creating a table, it is a good idea to think of what you will need in that table. For our albums table, we will want:

In the “tables” area of CocoaMySQL, create a new table called “albums”.

In the “structure” tab of CocoaMySQL, add new columns one by one. We’re going to specify “NOT NULL” for the “album” column. This means that the “album” column can never be empty. Every album must have a name.

If you are using the command-line version, use the following commands:

create table albums (album varchar(80) not null);

alter table albums add column artist varchar(60);

alter table albums add column year int unsigned;

alter table albums add column rating tinyint unsigned;

alter table albums add column purchasedate date;

A “tinyint” is a number from 0 to 255. A “smallint” is a n umber from 0 to 65,535. “Unsigned” means that the numbers cannot be negative. If you let the numbers be negative, then the maximum has to drop to make room for the negatives. A “signed” tinyint goes from -127 to +127, for example.

Record Information

It is always a good idea to have an auto incrementing ID field. This ID field will ensure that if we choose to access this data for other users, we have a unique field with which to identify each record. The unique ID will never change; even if the title, artist, or year purchased gets updated, the unique ID will remain the same. This makes it possible for us to add data in other tables, and link those other tables to this one.

CocoaMySQL made an ID field for us, but it does not automatically increment by default.

For our ID field, we must tell MySQL to “auto increment” the column. When we do this, CocoaMySQL will tell us that auto increment fields need to be indexed; do we want to make this our primary key? Click the okay button and CocoaMySQL will make an index for us. We’ll talk about indexes later.

If you are using the command-line, use the following commands:

alter table albums add column id int unsigned not null auto_increment primary key;

Import data

We have two sets of sample data. The one we’re looking at right now is the albums.txt file. This file is a tab-delimited list of albums, artists, years, and date purchased.

In CocoaMySQL, pull down the “File” menu, go to the “Import” submenu, and choose “CSV file...”. Because different computers have different line endings, and because different export options will use different things to separate fields, you need to tell CocoaMySQL that fields are terminated by tabs (“\t”), that the fields are not enclosed by anything, and that the first line does not contain field names.

Next, you’ll need to choose which items in the file go to which field. One of the first things you’ll notice is that we have an extra field. This data contains what looks to be the format of the album: if you scroll through the data you can see values of CD, Vinyl, or Cassette. We aren’t keeping track of that data, so choose “Do not import”.

Because CocoaMySQL does not let you import the same data into two different fields, you will need to also use “Do not import” temporarily for one or two of the others.

You will notice that the date is in MySQL format. Often when transferring data in bulk you will find it easier to convert date and time while exporting, into a format that the destination understands.

Click the “refresh” button in CocoaMySQL to see your changes.

On the command line, you’ll need to make sure that you are in the same directory as the albums.txt file (or that it is in the same directory as you) and import it using the MySQL statement:

load data local infile "albums.txt " into table albums (album, artist, rating, year, purchasedate);

You need to list the columns in the order that they appear in the import file.

View data

You can use CocoaMySQL’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 %"

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() functions use a special string of text to format the date or time. 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:

Backup Data

You must backup your data regularly. You should perform a backup as often as you change data. In CocoaMySQL, this is performed from the Export menu item under the File menu. On the command line, you can do the same with:

/usr/local/mysql/bin/mysqldump --lock-tables=FALSE -h localhost -u Username -p music > music.bak_date

The -lock-tables=FALSE turns off lock-tables. If your username has permission to lock tables, you don’t need that.

You can also place that command line in a crontab file if you are familiar with cron. This will back up your data on a regular basis. Make sure you are not overwriting previous information, or you are likely to overwrite good information with bad information at precisely the time you needed your backup. Always archive your backups.