Creating Databases

Once you've run through all of the steps for designing and finalizing a database (a surprising amount of work), it's time to actually create the database within MySQL. To create both the database and the tables, you'll use the mysql client and some basic SQL.

In Chapter 2, "Running MySQL," I quickly defined two databases to help demonstrate how to add users. As you may recall, the syntax for creating a new database is CREATE DATABASE dbname

To specify a character set or collation for the entire database, add another clause to the end of the CREATE query:

CREATE DATABASE dbname CHARACTER SET setname COLLATE collationname

To demonstrate the CREATE statement, I'll establish the accounting database.

To create a database:

1.	Log in to the `mysql` client. If you don't know how to do this yet, you'll need to see the instructions in Chapter 2. You will also need to be logged in as a user with permission to create new databases.
2.	Create the new database (Figure 4.5). `CREATE DATABASE accounting;` This first line, as you've already seen, creates the database (assuming that you are logged in to `mysql` as a user with permission to create new databases). Also, although SQL is case insensitive, I will make it a habit to capitalize the SQL words, helping to separate them from the database, table, and column names. If you would rather not capitalize these terms, you have that option. Figure 4.5. Creating the new database.

3.	Confirm the existence of the database (Figure 4.6). `SHOW DATABASES;` The `SHOW` command, which does not need to be discussed in detail, reveals the list of databases that the logged-in user can access. Figure 4.6. The newly created accounting database appears in the full list.

Tips

You can also create databases, but not tables, using the mysqladmin application.
```
mysqladmin u root p create databasename
```
You can always see how an existing database was created by using a SHOW CREATE command (Figure 4.7):
```
SHOW CREATE DATABASE dbname
```
Figure 4.7. A SHOW CREATE query indicates how a database or table was made. Notice in this example that there's also a comment for version 4.01.00 (and greater) regarding the default character set.

Figure 4.5. Creating the new database.

Figure 4.6. The newly created accounting database appears in the full list.

Figure 4.7. A SHOW CREATE query indicates how a database or table was made. Notice in this example that there's also a comment for version 4.01.00 (and greater) regarding the default character set.