MySQL I : Resources

MySQL Resources The current stable version of MySQL is 3.23.52. Version 4.x is the current development release and is not yet considered stable. Both versions can be downloaded from http://www.mysql.com/downloads MySQL Books *** Excellent ** OK * Don’t waste your money *** Beginning Databases with MySQL, by Neil Matthew and Richard Stones. WROX Press. 2002. …

MySQL I : Table Locking

1. Login to the MySQL Monitor client. 2. Use the “accounting” database: USE accounting; 3. Lock the tables you will be using: LOCK TABLES expenses READ, expense_categories READ; 4. Flush the tables (put table in its most current state): FLUSH TABLES expenses, expense_categories; 5. Backup the tables to your “c:\temp” directory: BACKUP TABLE expenses, expense_categories …

MySQL I : Transactions

1. Login to the MySQL Monitor client. 2. Use the new “movies_db” InnoDB database: USE movies_db; 3. Start a transaction: BEGIN; INSERT INTO directors (first_name, last_name) VALUES (‘Martin’, ‘Scorsese’); INSERT INTO movies (title, director_id) VALUES (‘The Age of Innocence’, LAST_INSERT_ID()); 4. Assuming you typed these correctly you should get no errors. If you get an …

MySQL I : GUI Tools

1. There are a number of excellent graphical MySQL clients that make development and administration of MySQL much easier than if using the MySQL Monitor command line client. The best so far is “MyCC”, though it is still in development. Another, equally functional, but not so well designed is “MySQLGUI”. Both are free open source …

MySQL I : Logging

1. Error logging is enabled by default. With a text editor, examine the file “c:\mysql\data\mysql.err” 2. To enable update tracking, stop the MySQL service or daemon. Windows: – Open Start > Settings > Control Panel > Administrative Tools > Services – Stop the MySQL service – Double click the MySQL service to open its properties. …

MySQL I : Grouping

4. Grouping functions allow you to group records by category and perform some minimal statistics on the category. For example: to find the maximum and minimum values of a group: SELECT MAX(invoice_amount) FROM invoices; SELECT MIN(invoice_amount) FROM invoices; 5. To determine how much has been spent under each expense category: SELECT SUM(expense_amount), expense_category FROM expenses …