Using MySQL data in Excel

You can do more than just read (SELECT) data using Excel in conjunction with MySQL.  Here’s how.

First of course, you have to install the mysqlODBC 5.1 driver.  Then you use Control Panel | Administrative tools | Data Sources to create the data source for your data. This step is where you put in how to connect to the database and what the account (root) and password (”) should be, and if you want which database to access. It should look like this:

ODBC Data source Administrator Popup

Select ADD a source. On the new popup, scroll down to MySQL ODBC Driver 5.1. Should look like this:

Create New Data Source

Select MySQLODBC 5.1Driver, and click Finish.  You should get a new window that actually comes from your properly installed ODBC Driver itself. Note the logo!

ODBC Data source window

Use localhost for the name of the host.  You can put any user that has access to the data, but root has access to everything. When filled in it should look something like this:

Filled in Data Source Form

Now start Excel. Get a blank worksheet (not essential– you can actually load data anywhere into a sheet; Excel will ask you where to put it, and will warn you before if you accicentally are going to overwrite some other data).

Go to Data | Import External Data | Import Data.

Import Data from external source

You should get a popup window that asks you to choose a data source. Select the New Source button.

Now you will get a data connection wizard window, which allows you to create a data connection for the table you want.  Select ODBC DSN.

Select the data source you created earlier.

Select My Data Source

Select the database (and table if you wish) you want on the next popup menu.

You will now get a popup asking you which .odc file you want– pick the one you just created! Then click OPEN button.

If you have not selected a table earlier, you will get a popup to ask you to choose a table, like this one. Pick the table you want.

Now you will see the Import Data popup, which will ask you where to put the data. Choose a cell for the upper left hand corner of the data. Or choose a new sheet for putting it there.  Click OK to do this.

You should shortly see the data appear in your sheet.

Imported Student Data in Excel

Using MS Query Tutorial

Use MS Query to Treat Excel As a Relational Data Source, Page 1

Bonus Point questions again!!

Yesterday in class I announced another bonus opportunity for you in conjunction with exercises 5.1 and 5.4.  I am looking for more questions about the data in exercise 5.1 and 5.4 to give 5 bonus points for answering.  A number of these questions are quite challenging and I need more questions.

Each person who submits a business question about the database for exercise 5.1 (or 5.4) on a 3×5 card with your name, that you think will challenge the other students, hand it in by monday. I will give you 1 Bonus Point on your exam for doing so. (This is just like we did in class, where one of you thought up a business query and wrote it on a card.)

I will post the business questions and everyone can earn 5 points for submitting a SQL query that gives the correct results.

This should give us a larger pool of questions to answer for credit!! And prepare for the exam, and boost your grade on it.

Chapter Review Questions

I am going to post the chapter review questions for those of you who are having trouble opening the book. but I am afraid you will have to open the book to find the answers. Or you can try to get the answers by looking in the powerpoints.

Chapter 1, 2, 3, 5 Review Questions

Bonus Point Questions

Yesterday I mentioned that any student who writes a correct query, and demonstrates it, for the statements in exercise 5.1 and/or 5.4 will receive 5 points.  Yes, that’s right; 5 points,
for each and every one you write!!

Exercise 5.1 statements you are to write queries for are posted on WebCT.

You must use phpMyAdmin to execute the query and use the Print option at the bottom of the result to get a report on a web page that shows the query you ran and the result it got. I demonstrated it in class Monday. You can then copy and paste from the web page to a word doc, an email, or some other medium to send me your results for your points.

This should also be excellent preparation for the MIDTERM EXAM.

Transactions in MySQL

Here is a reference to MySQL support for transaction processing. In
multi-user working systems this is the way it should be done, to make
sure the DBMS can preserve the integrity of the data even if crashes
and transaction failures occur.

MySQL :: MySQL 5.0 Reference Manual :: 12.4 MySQL Transactional and Locking Statements

Note the START TRANSACTION and END TRANSACTION statements.

xampplite on a Z: drive

It is possible to install xampplite on your Z: drive. This may be the preferred way to do it if you are a student, since you can then get in on any lab machine.  Hope this makes it easier to play along in class.

Simply unzip the distribution file for xampplite into Z:

Or copy your flashdrive installation’s xampplite folder to Z:

Don’t forget to make sure the 8001 and the 4443 edits have been made.  If you extract the zip file, you will have to edit the two files apache\conf\httpd.conf and apache\conf\extra\httpd-ssl.conf to make those changes.

Then you should be able to run the Z:\xampp-control.exe on your Z:drive and start apache and mysql.  And of course you can then use the browser to get page http://localhost:8001 which is the xampp staqrt page.

If you get the firewall barking at you about apache.exe and mysqld.exe, simply close the windows– and don’t forget to NOT change the security.  It won’t hurt you since you have no outside world access to your z drive.

Fixed links

I fixed the links to the sample database files here.

I also added example data for chapter 5. You can simply import the sql file after creating a database to hold it. Remember the database names have to be all one word, no dots or other special characters, except you can use _ for a space.  In phpmyadmin or at mysql command line –
CREATE DATABASE whatever_you_want_to_name_it

Exercise 5.1

Exercise 5.4

Installing Databases – source of files

Since we have trouble getting them from webct, I have put them on our blog, on the Databases tab. You can download the sql files there.  Should work from anywhere.

Command Line and mysql (2)

Now that you know how to get to mysql thru the command line, (see this for details), what can you do when you get there?  A lot, anything that you can do at the phpmyadmin web interface.  Here are a few simple things.  What you type in is in bold, and what the computer says is not.

Show the databases.  don’t forget the semicolon.

mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| cdcol              |
| menagerie          |
| mydb               |
| mysql              |
| phpmyadmin         |
| sakila             |
| test               |
| webauth            |
| world              |
+——————–+
10 rows in set (0.08 sec)

mysql>

Choose a database. Show the tables in it.

mysql> use cdcol;
Database changed
mysql> show tables;
+—————–+
| Tables_in_cdcol |
+—————–+
| cds             |
+—————–+
1 row in set (0.01 sec)

mysql>

See what’s in the table (your first introduction to an SQL command that does something interesting).

mysql> select * from cds;
+———————————–+——————+——+—-+
| titel                             | interpret        | jahr | id |
+———————————–+——————+——+—-+
| Beauty                            | Ryuichi Sakamoto | 1990 |  1 |
| Goodbye Country (Hello Nightclub) | Groove Armada    | 2001 |  4 |
| Glee                              | Bran Van 3000    | 1997 |  5 |
+———————————–+——————+——+—-+
3 rows in set (0.05 sec)

mysql>

That’s the end of the lesson. With this equipment you will be able to add new databases easily to your mysql DBMS.