MySQL Notes

For those of you who like having a hardcopy book in hand, here are a couple of recommendations:
Yarger, R. et al.,(1999). MySQL & mSQL, O'Reilly.
DuBois, P. (2000). MySQL, New Riders.

Accounts
If you already have a MySQL account, you can use it for the workshop and assignment. Otherwise, you need an account on Ruby, and a MySQL account. We'll hand out accounts in class (see home page).

Note that you must first connect to ruby (through SSH) in order to then connect to the MySQL server on opal.

To change your password, log into your account and issue the following command (the parenthesis and single quotes are required):
SET PASSWORD FOR YourAccountName = PASSWORD ('NewPassword');

Connecting to MySQL at SILS
There are three useful ways of connecting to MySQL.

1. Connecting for interactive work.
   shell> mysql -u YourAccountName -p -h opal.ils.unc.edu YourDatabaseName
   You will then be prompted for your password.
   
   • -u indicates that the user name follows.
   • -p prompts for your password.
   • -h specifies which host is running MySQL.
   • YourDatabaseName is the area in which all the tables you create will live - this must be the last command argument. After you've created some tables, you can enter

     show tables;

     to see a list of tables in that space.
   • You can either type SQL commands in directly, or cut and paste them in from a text file.

2. Executing a script.
   shell> mysql -u YourAccountName -p -h opal.ils.unc.edu YourDatabaseName < YourScriptFile
   You will then be prompted for your password.
   • YourScriptFile is a file containing SQL commands that will be executed by MySQL.
   • Those of you familiar with Unix will recognize the input redirection symbol "<"
   
   A script file is useful for:
   • those of us whose typing isn't perfect.
   • a file of create statements. Executing the file creates the tables, indexes, etc. in your database. As a bonus, you have a record of what you did.
   • a file of insert statements. Use your favorite text editor to write the insert statements, then execute the file to populate your database.
   • a file containing one or more queries. If you're experimenting with a new, complex query, you have a file containing what you did last time that is easy to modify. If you have queries that are run periodically, all you have to do is execute the file.

3. Executing a script and directing the output into a file.
   shell> mysql -u YourAccountName -p -h opal.ils.unc.edu YourDatabaseName < YourScriptFile >YourOutputFile
   
   • This lets you save the output of a query, rather than having it scroll by on the screen.

Demo Database
We will setup and use demonstration databases that go along with the tables for the Ramamkrishnan chapter 5 section 3 exercises, as this will help you with your assignments. You should make sure you can create tables, and populate them by scripted methods (i.e. not typing them all in by hand live!). As part of exercise 2 we will

• Create statements for building the tables.
• Insert statements for adding sample data.
• Some sample queries.

Helpful Hints
A miscellaneous collection. If you know of other useful tidbits, or discover something useful for your classmates, please let me know so I can add it!

1. Some useful commands:
   • show object
     This command shows you information about various database objects. For example, it's helpful as a quick check to see if you were successful in creating a table.
     show tables lists the tables in your database space. show columns from YourTable lists the columns in a table. And so on.
   • describe table
     This command lists the columns, data types, and other information about a table. It's helpful if you can't remember the correct name of a column. E.g. "describe teachers" lists information about the teachers table.
   • Explain select...
     This command takes a select statement, and breaks it down so you can see how it was executed, e.g., what indexes were used. This is helpful if you want to improve performance of a query.
   Consult the documentation for details on these commands.
2. Remember the semi-colon! MySQL won't execute a command unless it ends with a semi-colon (;) or \g. It will just hang around and wait for you.
3. Check out the enum data type. It lets you define a list of possible values.
4. The version of MySQL we are using (3.23.39) does not support referential integrity and foreign keys. You will have to provide your own work-arounds when necessary. 3.5.6 Using Foreign Keys of the MySQL Documentation talks about some workarounds.
   • You'll need to use explicit joins in your queries.
   • For initially populating a database, you can order your insert statements so that a base table record is added first, then the related FK records. If the PK of the base table is an autoincrement field, you can then use LAST_INSERT_ID() as the FK value.
   • To enforce integrity on deletes, you need to include specific statements for the desired action (e.g., cascade delete, update FK value, nullify). Write the initial delete statement for the base table record, then the delete (or update) statements for the related records.
5. Our version of MySQL doesn't support transaction processing, either. Again, you can read about some workarounds in the documentation. Think about the implications of this during the classes on transaction processing.
6. MySQL doesn't support nested queries. So instead of writing a nested query like so:
   SELECT teacher.fname, teacher.lname
   FROM teacher
   WHERE PID
   in
   (SELECT course.teacher
   FROM course
   WHERE course.cnum > 199);

   you write

   SELECT teacher.fname, teacher.lname
   FROM teacher, course
   WHERE teacher.PID = course.teacher AND
   course.cnum > 199;

7. For two-part queries, where you want to perform a second query on the results of the first, look at create temporary table.
8. MySQL has extended the functionality of the GROUP BY clause. You'll want to look at this in the documentation, since there is some unexpected behavior when combining this with max().