MySQL - searching for a record

When the database has more than a handful of records, we need a way of searching for a particular record. The point of a database is to hold the information for us so we don't have to remember it all.

To make this article more practical we need some more entries in our 'pickled1' colleagues table. To save you some writing you can import this pickled1.sql file.

The sql file contain 15 basic records. To import the data:

mysql -u root -p pickled1 < pickled1.sql

Search

Now we have some more data, log into MySQL pickled1 database:

mysql -u root -p pickled1

If we display the contents we can see that with only 15 entries, it's getting difficult to navigate and get the information we want:

select * from colleagues;

So let's do a search for a name that contains 'dave':

select * from colleagues where name like '%dave%';

That will return the 4 entries that have 'dave' in the name column.

Still too many, I want to know how many people named Dave also have Dave in their email address:

select * from colleagues where name like '%dave%' and email like '%dave%';

That returned 3 records. Who's the other one? Let's have a look:

select * from colleagues where name like '%dave%' and email not like '%dave%';

Ah, the record returned good old marshy:

7 | Dave Marshal | marshy@domain.tld

Order

That's OK but we prefer a bit of 'order' to our lists. Lets display the list of Daves in alphabetical order:

select * from colleagues where name like '%dave%' order by name;

That's better.

Counting

Sometimes all you need is to count the records available.

Let's see how many records we have in the colleagues table:

select count(*) from colleagues;

Well, now we know there are 15 entries.

What about how many Daves I know?

select count(*) from colleagues where name like '%dave%';

Hmm, that's 4 Daves. I wonder if that's too many? Maybe I need to know more Sallys.

Anyway, you can get as detailed as you like with the select and count queries but I hope this clears any doubts as to how simple manipulating the data actually is.

PickledOnion.

Digg it | del.icio.us | reddit | StumbleUpon