MySQL is the most widely used database management system. This article introduces you to MySQL databases. Read it with MySQL up and running.

Introduction To MySQL Databases

Rather than present abstract, unconnected articles that describe MySQL syntax we are going to write a series of interconnected articles that illustrate MySQL syntax and features in the context of a simple case study. Samuel has been making speciality cookies for a few years. They have become so popular that he wants to use MySQL to help manage his record keeping so that he can spend more time on the cookie-making end of the business. We believe that you will get a lot more out of this article and its companions if you read it actively, in other words turn on your computer, download MySQL4, and bash in the commands. You will learn a lot more than by simply nodding your head.

As their name indicates, database management systems exist to create and process databases. This article presents what you need to know to start database processing with MySQL4. You should read this article armed with a computer running MySQL4 and try out the programming statements. As is often the case, MySQL5 retains most but not all of these statements. When you are ready for serious MySQL processing make sure that the on-line manual is available.

The SHOW DATABASES; command displays the databases for which you have some privilege. By the way, this command may be entered in lower-case letters as well. Initially when the root user enters this command only the mysql database is indicated on the screen. This particular database describes user access privileges.

This textbook gives recipes for MySQL, but no edible cookies.

The CREATE DATABASE command creates a database. For example, the CREATE DATABASE cookies; command creates the cookies database. The next step will be to create a user and add it to that database. To do so enter the statement GRANT ALL PRIVILEGES ON cookies.* TO 'samuel'@'localhost' IDENTIFIED BY 'samuel63' WITH GRANT OPTION; which creates the user named samuel on the local computer for the cookies database. Other users may be able to log on to this database anonymously without a password from the local computer. A given database is created just once. But it must always be activated prior to use.

The USE cookies; statement activates the cookies database. The mysql monitor replies with the message Database changed. Only a single database may be active at one time.

A database itself is of little interest. Databases are composed of tables that contain the actual data and control information about the data. To see the tables in a given database make sure that the database is activated and then code the SHOW TABLES; statement. For example the mysql database mentioned above contains six tables: columns_priv, db, func, host, tables_priv, and user. This particular database and tables are of interest to the system administrator. Because of the many issues involved in table creation and processing this particular article will not go into the details of MySQL tables, discussed in a companion article.

When you get to be quite familiar with MySQL you may want to use mysqldump program enables you to backup one or more database to a file or another SQL server, perhaps one that isn’t even MySQL. Needless to say this powerful program can be fairly complicated. Make sure that you test it extensively before applying it to important databases.

Unless you are just starting to learn the commands, you should seriously consider the tables that will make up a database, their interrelations, and which users should be granted which privileges prior to entering a single statement into the MySQL monitor. Yes, you can always make changes. But improper planning inevitably means extra work and a clumsier final database.

The next step will be introducing MySQL tables.