Introduction to MySQL Queries #moodle

One of the aspects of my workshop on Monday will be to quickly share how to do MySQL queries. This is something that took me awhile to learn--after all, it's not like a school district would pay to send one off to learn this stuff--because it was critical for the services we provided to students and staff.

Here's just a quickly written tutorial on some of my favorite MySQL commands. There's a whole world in what I didn't mention so if you have something you'd like to share, feel free to speak up!



Intro to MySQL Queries



GETTING SETUP WITH MYSQL/PHP
Implementing free, open-source software solutions is cost-saving, but there is some up-front time you will need to spend. In implementing these solutions--such as Moodle, Wordpress, and a million others--you will need to setup a computer as a server. This can be a relatively inexpensive investment--such as your own desktop computer or laptop for home practice, so to speak--or more expensive such as a rack-mounted server for about $6,000-$8,000. Once you have a server, you need to set it up as an Apache/MySQL/


Note that these 3 components are often referred to as "AMP" with one of the following letters in front of it to represent an operating system. For example, you'll see the term "WAMP" as representative of "Windows Apache MySQL PHP." Macintosh users might refer to it as "MAMP" and GNU/Linux users as "LAMP."


While this is not as difficult as you might think, it does involve a commitment to install PHP/MySQL on your web server or other appropriate machine. You can find more information on how to setup PHP/MySQL online in free web-based tutorials.


Once you setup PHP/MySQL on your server, you’ll be set and ready to go with various solutions. However, getting to that point might require some technical expertise. You can see some examples (screenshots) of walled garden applications.


QUICK SETUP PROCESS

  1. Install WAMP/MAMP/XAMPP--each of these automates the process for you of installing the Apache web server, MySQL database server, and the PHP files needed to interact with the MySQL databases--as appropriate on a computer that will work as a server. Note that you may have to “lock down” each of these—to differing degrees—for security purposes upon install. My current preference is XAMPP for Windows, Mac and GNU/Linux (although it's pretty easy to accomplish the AMP process in GNU/Linux these days).
  2. After installing the software, download your walled garden application of choice (e.g. Wordpress, Moodle) and put it in the appropriate folder (usually “htdocs” in an Apache server setting).
  3. Use a program like SQLYog (Windows), SequelPro (Mac) to create the database. The walled garden app will create all the necessary tables to house your data.
Once you are setup with MySQL/PHP on your server, you’re ready to install Moodle or whatever on your computer.

BASICS OF SQL QUERIES

MySQL queries allow you to ask questions of your stored data, as well as update, and/or delete it. There are a lot of books (I highly recommend the SQL Pocket Guide from O'Reilly Press) that purport to make your life easier, but here are the basics that I've used again and again over the years. And, of course, your best source is a targeted Google search for tutorials. For example, a quick search and review yielded these two resources:
Unfortunately, as nice as these tutorials are, they usually involve typing in MySQL queries into the command line. I have found it more convenient to use a go-between program like PHPMyAdmin (web-based), SQLYog (windows), or Navicat Lite (Mac,Win).

For the examples below, let's assume we have a simple address list with these fields (column rows on a Spreadsheet is another way to think of it) where a discrete piece of information is stored:

Address Book Field Names:
  1. FirstName
  2. LastName
  3. Email
  4. Twitter
  5. Phone
To create the database, I'd use one of the database tools available for my computer. Most make it easy to create a database and then a table with appropriate fields. Here's what the command might look like:


Now that I have a database, I could easily import that list I'd been compiling in my favorite spreadsheet program. Simply export your address list spreadsheet as a comma-delimited file (CSV) and then import using PHPMyAdmin; you can find a tutorial online here on how to accomplish that.

You can also type in the information, but this is kinda tough...better to do the import after typing them in via Excel or a similar spreadsheet/dbase tool. But for fun, this is what it would look like to insert TWO records.


INSERT INTO `addressbook`.`names` (`FirstName`, `LastName`, `Email`, `Twitter`, `Phone`, `Notes`) VALUES ('Miguel', 'Guhlin', 'mguhlin@gmail.com', 'mguhlin', '210-617-3330', '10/22/1968'), ('Monica', 'Guhlin', 'mguhlin2@gmail.com', 'na', 'na', '');



SELECT STATEMENTS - HOW TO FIND YOUR DATA
For example, if I wanted to query a list of users and get a list of people's email address and twitter account name, I would write a SQL query like the one below:

SELECT *
FROM `names`
LIMIT 0 , 30

In the table above, the SELECT statement allows me to pick what information I want to get out of the database. The asterisk "*" tells it I want EVERYTHING, all field names, to show up in the results. The FROM specifies what database table I want, and the "names" is the title of the table being queried.

The result for the SELECT statement above is shown below:


Notice that in PHPMyAdmin, you can actually edit the information by clicking on the pencil, or delete it using the red X.


ADDING WHERE TO SELECT STATEMENTS
If I wanted to be selective about which data I wanted to see, I could add a WHERE command to limit information. For example, since a person's email is usually unique, I could limit my search by saying I wanted results where the email was equal to "mguhlin@gmail.com". 

My Select statement would look like this:

SELECT *
FROM `names`
WHERE email = 'mguhlin@gmail.com'
LIMIT 0 , 30

This would yield the following results:

Now, as nice as this is, what if I only wanted certain information? In some databases, there are TONS of fields. What if I only wanted to see the person's first and last name, and their twitter address?

SELECT STATEMENTS - LIMITING FIELDS IN QUERY RESPONSE

Here's how to only get a few information from some of the fields:

SELECT FirstNameLastNameTwitter
FROM `names`
LIMIT 0 , 30

In this SELECT statement above, notice how the * is gone and has been replaced by more specific request...I only want to see the contents of those 3 fields. The results of the SELECT statement above would look like this:


USING WHERE AND LIKE TO FIND SIMILAR DATA
I could also say, show me the data in the database where information is like the search term....

SELECT *
FROM `names`
WHERE email LIKE "mg%"
LIMIT 0 , 30

Note that this SELECT statement says, "Show me everything from the table entitled 'names' where the email starts off like 'mg'." We signify that we only want to see the first part of the email by adding a % to the end. A different Select Statement focused on pulling who has Gmail accounts.

SELECT *
FROM `names`
WHERE email LIKE "%@gmail.com"
LIMIT 0 , 30

Notice that the MySQL query has changed and now the results will show me anyone with a @gmail.com account. You can also do searches where you say where something is NOT like your search term.

USING WHERE AND LIKE TO FIND DIS-SIMILAR DATA
For example, I could do a search and have it show me all email accounts that are NOT like Gmail.com accounts:

SELECT *
FROM `names`
WHERE email NOT LIKE "%@gmail.com"
LIMIT 0 , 30

Since I don't have any matching data in the database, I get a result of MySQL returned an empty result set (i.e. zero rows).

There are many other types of queries you can do--such as JOINS--that allow you to blend information from one table with another. Those are beyond the scope of this tutorial but you can get help in the SQL Pocket Guide I mentioned earlier.

UPDATING INFORMATION
Modern MySQL tools make it easy to update one or two fields in a database, but sometimes, you have to do several hundred en masse. Here's one example of that, and another, for Moodle.

When you encounter that challenge, you can use the UPDATE command. Here's what the UPDATE command looks like in light of our Addressbook example.

Problem: Need to change Monica Guhlin's Twitter account to reflect her actual Twitter account rather than "na" shown presently.

Solution: Use the UPDATE command.

UPDATE NAMES 
SET twitter = 'monicaguhlin' 
WHERE email = 'mguhlin2@gmail.com'

Note that in this SQL query, we are updating the "names" table and setting the field called "twitter" to reflect the new value of "monicaguhlin" only where the email is equal to "mguhlin2@gmail.com". The WHERE command is incredibly important in ensuring that we limit the scope of the UPDATE, or we could accidentally change EVERYONE'S twitter account name to the new value.

Usually before I run an update, I run a SELECT query to ensure that I get the results and ensure that only the record I want affected will be. For example, for the UPDATE query above, I would write the following SELECT prior to doing the update:

Select *
From NAMES
where email='mguhlin2@gmail.com"

If the record information for Monica Guhlin came up, then I would know that my UPDATE would only affect her record.

Another UPDATE example:

UPDATE NAMES SET Firstname = 'Juan',
LastName = 'Gonzalez' WHERE email = 'mguhlin@gmail.com'

In the example above, I changed "Miguel Guhlin" firstname and last name to "Juan Gonzalez" matched to my email.

You can have a lot of fun with UPDATE...it is one of my favorite commands because it makes massive changes to hundreds of records possible. Of course, it can also be a dangerous command!







Get Blog Updates via Email!
Enter your email address:


Delivered by FeedBurner


PingIt!
Delicious Bookmark this on Delicious
Subscribe to Around the Corner-MGuhlin.org



Everything posted on Miguel Guhlin's blogs/wikis are his personal opinion and do not necessarily represent the views of his employer(s) or its clients. Read Full Disclosure

Comments

Popular posts from this blog

#Chromecast Add-Ons to Play Various Video File Formats

Free Professional Learning! Education On Air #googleedu

10 Steps to a Blended Learning Classroom #MIEexpert #MIE #tceamie1