Introduction

Database Administration used to be the purview of computer professionals. The advent of Internet business and communication has opened the door to a variety of hobbyists and small business interests who want the informational power and convenience of computer data storage without having to commit to what amounts to career training in a particular database program in order to install and use it. MySQL has risen to the forefront as the multi-OS answer to the new database requirements of low cost and ease of operation, while at the same time remaining scalable enough for corporate use.

MySQL allows almost anyone to install and run a database, from the true beginner who wants a guestbook on their web site to the e-business needing storage for their shopping cart sales interface that will grow as their customer-base grows. It is simple and easy to use, yet allows for complex purposes. As your database needs grow, you will find MySQL has likely preceded you and the more you learn, the more versatile and powerful you will find this robust, approachable program. This book will provide you with the basic knowledge to install and set up, plan and design, and protect and administer your MySQL database. It gives you enough detail to progress without drowning you in minutia, and the hands-on projects create a quick learning curve that allows you to get your database up and running in a minimal amount of time.

Who Should Read This Book

This book is subtitled Essential Skills, and anyone whose requirements mandate a rapid entry into the MySQL world will be able to find the essential information required to get them moving forward in regards to that goal. More experienced users will be able to read between the lines in a way that the less experienced user will not, but on the whole, all effort has been expended to make the information detailed enough to provide context but, at the same time, not overwhelmingly encyclopedic. We will assume a basic familiarity with the operating system you choose to install MySQL on, and because of its nature, a slightly greater depth of knowledge is assumed for the Linux OS; however, a true database beginner can use this book as well as a more experienced database professional who is looking for a quick foothold into a new area of expertise.

Whats in This Book

MySQL: Essential Skills is divided into eight modules, each covering a specific aspect of MySQL installation, operation, or administration.

Module 1: Installing MySQL

The first part establishes a common language by defining basic database terms as they are used in this text. We then move on to a discussion of open source software and how it relates to MySQL, and then to a discussion of the pertinent criteria in choosing a development and deployment operating system for your MySQL database.

You will then learn how to install MySQL on first the Windows then the Linux operating systems. The Windows installation section also covers installing MyODBC. Once MySQL is installed and running on your computer, you will learn how to use the MySQL command-line interface. The final section of this module outlines resources that may come in handy once your learning curve has taken you beyond the boundaries of essential skills.

Module 2: Defining a Database

This section covers database creation, table definition, and data insertion. The first section explains the essentials of MySQL data types, then segues into the preplanning needed to map out your database before creating it; thus, preventing some of the common mistakes that might require a later redefinition or re-creation of your tables.

The next section explains the syntax of database and table creation, as well as gives concrete examples of commands and what they accomplish. The final section of this module covers data insertion, and at the end of the included projects, you will have created a basic database that will allow you to experiment with subsequent data manipulation.

Module 3: Manipulating the Database

This module covers adding and changing the data and/or tables within the database constructed in the previous module. The first section covers commands that allow you to add new data in various forms to the database. The second section covers commands that allow you to change the structure of an existing table and/or add an index to an existing table.

The third section of this module explains the commands that allow you to modify the data already in a table, and the fourth and final section explains the commands necessary for either removing a tables data while leaving the table intact or removing a table, data and all, or deleting an entire database.

Module 4: Basic Reporting

This module covers the various uses of the basic commands used to select data from a table and display it in informative ways. You will learn how to choose the specific data you need to view then narrow that focus and display the required data in a meaningful manner.

Module 5: Advanced Reporting

The first section of this module covers a wide variety of functions used to control the flow of data, perform math on or with numerical data, manipulate and display character data, and display and manipulate representations of time from fractions of seconds to years .

The second and third sections show you how to order and group data from a table into meaningful displays to present reports and then how to connect data from two or more tables into an understandable and easily read format.

Module 6: GUI Interfaces for Data-Handling and Administration

Up until this point in the text, all interaction between the user and the database has taken place on the command-line interface. In this module, we will cover the MySQL Control Center and the new MySQL Administrator GUI interfaces.

The first section covers the functions currently available on the MySQL Administrator, which is still in Alpha testing and so subject to change. The second section covers the MySQLCC interface, which is in release and more stable. GUI interfaces allow you to interact with the database in a point-and-click environment, even if you are running on a Linux OS, instead of the GUI-driven Windows OS.

Module 7: Interfacing with Programs

This section gives an overview of PHP and touches on how it interfaces with MySQL in a web environment. You will learn how PHP can be used to allow a user to access a web page and either display or insert data to or from a MySQL database.

The second section deals with importing an existing Excel or Access database into MySQL, and also how to export or link MySQL data into Excel and/or Access in order to modify or view it in a more familiar environment.

Module 8: Basic Administration and Backups

This module covers the basic tools and actions needed for administrating a MySQL database in order to keep it secure and available for reliable use. The first section covers security in terms of access and command privileges and the creation and safekeeping of database backups.

The second section covers the correct procedures to follow in order to recover from a disaster using backups and a variety of commands to check, repair, and /or restore the database to a functional level again.

Appendixes

Appendix A contains the Mastery Check questions from the end of each chapter along with their respective answers. Appendix B contains the alphanumeric listing of the MySQL Reserved Words, which should enable you to avoid unfortunate or confusing naming conventions for database table and column names . Appendix C provides basic syntax and an overview of PHP for those users who have little or no experience with it.

Ask the Expert

Occasionally, more detailed information on a particular subject will be offered in an Ask the Expert section, which is couched in a question and answer format.

Progress Checks

Throughout each module, there will periodically be a list of questions, immediately followed by their answers at the bottom of the page, covering the material just discussed. If the questions are difficult to answer, or confusing, this is a sign you might want to review the previous material again before proceeding.

Mastery Checks

At the end of each module there are ten or more Mastery Check questions covering the most important points that should be gleaned from each module. The answers to the Mastery Check questions are in Appendix A in the back of the book.