Introduction to MySQL

In this chapter you'll see how to define a simple table using MySQL's allowed data types and other properties. Then you'll learn how to interact with the MySQL server using two different client applications.

Because this book discusses how to integrate several technologies (primarily PHP, SQL, and MySQL), a solid understanding of each individually is important before you begin writing PHP scripts that use SQL to interact with MySQL. This chapter is a departure from its predecessors in that it temporarily leaves PHP behind to delve into MySQL.

MySQL is the world's most popular open-source database application (according to MySQL's Web site, www.mysql.com) and is commonly used with PHP. The MySQL software comes with the database server (which stores the actual data), different client applications (for interacting with the database server), and several utilities. In this chapter you'll see how to define a simple table using MySQL's allowed data types and other properties. Then you'll learn how to interact with the MySQL server using two different client applications. All of this information will be the foundation for the SQL taught in the next chapter.

Naming Database Elements

Before you start working with databases, you have to identify your needs. The purpose of the application (or Web site, in this case) dictates how the database should be designed. With that in mind, the examples in this chapter and the next will use a database that stores some user registration information.

When creating databases and tables, you should come up with names (formally called identifiers) that are clear, meaningful, and easy to type. Also, identifiers

Should only contain letters, numbers, and the underscore (no spaces)

Should not be the same as an existing keyword (like an SQL term or a function name)

Should be treated as case-sensitive

Cannot be longer than 64 characters (approximately)

Must be unique within its realm

This last rule means that a table cannot have two columns with the same name and a database cannot have two tables with the same name. You can, however, use the same column name in two different tables in the same database (in fact, you often will do this). As for the first three rules, I use the word should, as these are good policies more than exact requirements. Exceptions can be made to these rules, but the syntax for doing so can be complicated. Abiding by these suggestions is a reasonable limitation and will help avoid complications.

To name a database's elements

Determine the database's name.

This is the easiest and, arguably, least important step. Just make sure that the database name is unique for that MySQL server. If you're using a hosted server, your Web host will likely provide a database name that may or may not include your account or domain name.

For this first example, the database will be called sitename, as the information and techniques could apply to any generic site.

Determine the table names.

The table names just need to be unique within this database, which shouldn't be a problem. For this example, which stores user registration information, the only table will be called users.

Determine the column names for each table.

The users table will have columns to store a user ID, a first name, a last name, an email address, a password, and the registration date. Table 4.1 shows these columns, with sample data, using proper identifiers. As MySQL has a function called password, I've changed the name of that column to just pass. This isn't strictly necessary but is really a good idea.

To be precise, the length limit for the names of databases, tables, and columns is actually 64 bytes, not characters. While most characters in many languages require 1 byte apiece, it's possible to use a multibyte character in an identifier. But 64 bytes is still a lot of space, so this probably won't be an issue for you.

TIP

Whether or not an identifier in MySQL is case-sensitive actually depends upon many things (because each database is actually a folder on the server and each table is actually one or more files). On Windows and normally on Mac OS X, database and table names are generally case-insensitive. On Unix and some Mac OS X setups, they are case-sensitive. Column names are always case-insensitive. It's really best, in my opinion, to always use all lowercase letters and work as if case-sensitivity applied.