Creating Databases

Creating Databases

Now that we have thought about how we would like to lay out our data and have begun thinking about what types we might associate with the various bits of data, we need to turn our attention to creating a database with our server. The way in which this is done varies from server to server, with varying degrees of complexity. We will cover the process here for MySQL, but we will also show a number of examples for creating them for other servers in Appendix B, "Database Function Equivalents."

Talking to the Database Server

To make the database server do something, you need to connect to it and establish a communications session. Connections can come from any number of places, including specialized client programs that ship with individual server software, web server software (including PHP), other language clients (such as programs written in Microsoft Visual BASIC), or monitoring and administration tools for your server (see Figure 9-6).

We will spend most of this and the next chapter working with the simple client programs that come with the database servers. After that, we will look at how we can make these connections and execute code from within PHP scripts.

Connecting and Authenticating

To create a database in MySQL, you must connect to the running server process (which may or may not be running on the same computer as yours) and log in as a user to whom the DBMS has given permission to create databases. This is often done as the user root, but in an established system with more complete permissions set up, this might be somebody else. You can do this from the command line by running the following command (under both Windows Command Prompts and Unix-like shell prompts), provided you are in the mysql/bin directory or the command mysql is in your system PATH:

mysql -user=username -p

where username is either root or the user with permissions to create databases and the -p flag tells MySQL to be sure to ask for the user's password before attempting a login. For users who are not comfortable with cmd.exe under Windows, you can also click on "Run" from the Start menu and type in the full path of the mysql.exe program:

c:\mysql\bin\mysql.exe --user=root -p

Another program called winmysqladmin.exe, which we do not cover in this book, is a fine program for interacting with MySQL. The program prompts you for the password associated with the given username (you should have set it up so that a password is required for any account) and continues the login.

Creating the Database

Creating a new database is as simple as executing the SQL query

CREATE DATABASE DatabaseName;

where DatabaseName is the name of the database you would like to create. We use a more advanced version of this command:

CREATE DATABASE DatabaseName
   DEFAULT CHARACTER SET charset
   DEFAULT COLLATE collation;

This version of the query allows us to specify both a character set and a collation (sorting order for the strings) that is used by default for tables created in that particular database. To see a list of character sets and collations available in your MySQL system, you can enter the following commands from the MySQL client:

mysql> SHOW CHARACTER SET;
mysql> SHOW COLLATION;

The output for the first contains a number of lines like the following:

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+
| Charset  | Description                 | Default collation   |
+----------+-----------------------------+---------------------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |
| dec8     | DEC West European           | dec8_swedish_ci     |
| cp850    | DOS West European           | cp850_general_ci    |
| hp8      | HP West European            | hp8_english_ci      |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |
| latin1   | ISO 8859-1 West European    | latin1_swedish_ci   |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |
etc...

The second command lists collations available on the system along with other details about them. For both, you can use the SQL keyword LIKE and the SQL wildcard character '%' to refine the queries:

mysql> SHOW CHARACTER SET LIKE %latin%';
mysql> SHOW COLLATION LIKE '%utf8%';

The second command produces output similar to this. (We have trimmed it for space.)

mysql> SHOW COLLATION LIKE '%utf8%';

+--------------------+---------+-----+---------+----------+
| Collation          | Charset | Id  | Default | Compiled |
+--------------------+---------+-----+---------+----------+
| utf8_general_ci    | utf8    |  33 | Yes     | Yes      |
| utf8_bin           | utf8    |  83 |         | Yes      |
| utf8_unicode_ci    | utf8    | 192 |         | Yes      |
...
| utf8_estonian_ci   | utf8    | 198 |         | Yes      |
| utf8_spanish_ci    | utf8    | 199 |         | Yes      |
| utf8_swedish_ci    | utf8    | 200 |         | Yes      |
| utf8_turkish_ci    | utf8    | 201 |         | Yes      |
...
| utf8_roman_ci      | utf8    | 207 |         | Yes      |
| utf8_persian_ci    | utf8    | 208 |         | Yes      |
+--------------------+---------+-----+---------+----------+

Since most of our web applications default to English and our character set is Unicode, we typically use utf8 as the character set and utf8_general_ci as the collation for our databases. For the rest of this chapter (and in the next few chapters), we create a message board system. We first create the database for it, as follows:

CREATE DATABASE MessageBoard
   DEFAULT CHARACTER SET utf8
   DEFAULT COLLATE utf8_general_ci;

Please note that support for these character sets and collation features is new to MySQL version 4.1, so releases prior to this do not support all of the commands.

To see a list of all databases available to the currently connected database user in MySQL, execute the following query:

mysql> SHOW DATABASES;
+--------------+
| Database     |
+--------------+
| messageboard |
+--------------+
1 row in set (0.02 sec)

Other databases to which the currently connected database user does not have permission to access are not listed.