MySQL Wizardry

Conclusion

DISCLAIMER

The Wizard is a fictitious character. He does not exist in the wild, although some laboratories in Outer China are collecting evidence about the theoretical possibility that he could be real. Any resemblance with any individual in the physical world is purely coincidental. Translated into plain English: if you think that the Wizard looks like somebody you know, especially if he or she is a pain in the neck, it is because you are unlucky. Wizards are grumbling fellows who call Perl or SQL their mother tongue and feel ill at ease while speaking English (or Italian, Hungarian, Flemish or whichever language they used to speak before learning C++). They are usually kind and willing to please you, but they often manage to upset you while doing so. Wizards also enjoy gathering with others of their kind, since they find common people boring. However, if you put two wizards in the same room, you are likely to see some sparks after a while. That's why wizards are usually alone, and they like to be left as such.

ACKNOWLEDGEMENT

I want to thank many people who made this article possible. All the programmers who created GNU, Linux, MySQL, StarOffice, perl, DBI, nedit (I am not the Wizard. I can't cope with vi!), and many utilities without which I would be most likely writing about embedded databases in C. (There is nothing wrong about embedding databases, and doing that in C is quite honorable, since I have done that for a while, but MySQL is a toy on a totally different scale). In a word, I am grateful to all the open source community, which has made profitable exchanging knowledge (not to mention the fun!).

RESOURCES

You must have realized that I (or perhaps the Wizard) have taken for granted that you know the basics of MySQL, SQL, client server architecture, perl, the DBI, and maybe something else that I don't recall now. Don't be angry with me. Teaching the basics is a social duty, but sometimes it makes you feel good when you can exchange some unusual experience.

You might be interested in a few links that offer the basics that I have so happily skipped:

• http://dev.mysql.com/doc/ One of the best manuals you can find about MySQL, the one written by the developers themselves.
• In addition, http://www.devshed.com/ offers many tutorials and articles for beginners (and intermediate and expert users as well).
• DBI can be learned online at http://www.symbolstone.org/technology/perl/DBI/.

About Perl, I don't dare suggesting anything that could contravene the official policy that TIMTOWTDI (there is more than one way to do it). Larry Wall has written some thousand pages on this subject, with the conclusion that Perl's official policy is that there is no official policy. I would leave it at that.

SOURCE CODE

To let you appreciate the examples in this article, I prepared some scripts:

• The Xcompany dump file, when fed into MySQL, will re-create the database for you.
• The xtab.pl script, general purpose cross-tabulation builder, will let you feel the thrill first hand.
• Some configuration files for xtab.pl will spare you the boredom of re-typing.
  • location-gender-count.xtab is the simplest one. Counting of employees by ocations and gender.
  • age-gender-count.xtab will count the employees by age range (using MySQL "WHEN" instruction) and gender.
  • country-dept-salary-sum.xtab sums the salaries by country and department.
  • month-category-sales-sum.xtab will summarize monthly sales by category.

Now, please. Don't let me do too much. Try it on your databases, and tell me how it worked.

DevShed.com is the independent Open Source Web Development Site. Fresh tutorials, articles and discussion of MySQL, PHP, Perl, Python, Apache, JSP and administration can be found daily at http://www.DevShed.com/ This article is Copyright 2001 by Developer Shed, Inc. All rights reserved. Reproduced with permission.