Working with Database Structures - Learning MySQL - page 180

Behind the scenes, MySQL creates a new directory under the data directory for the new
database and stores the text file db.opt that lists the database options; for example, the
file might contain:
default-character-set=latin1
default-collation=latin1_swedish_ci
These particular two lines specify the default character set and collation of the new
database. We'll look at what these mean later, but you generally won't need to know
much about the db.opt file or access it directly.
Once you've created the database, the next step is to use it—that is, choose it as the
database you're working with. You do this with the MySQL command:
mysql> USE lucy;
Database changed
As discussed previously in Chapter 5, this command must be entered on one line and
need not be terminated with a semicolon, though we usually do so automatically
through habit. Once you've used the database, you can start creating tables, indexes,
and other structures using the steps discussed next in “Creating Tables.”
Before we move on to creating other structures, let's discuss a few features and limita-
tions of creating databases. First, let's see what happens if you create a database that
already exists:
mysql> CREATE DATABASE lucy;
ERROR 1007 (HY000): Can't create database 'lucy'; database exists
You can avoid this error by adding the IF NOT EXISTS keyword phrase to the statement:
mysql> CREATE DATABASE IF NOT EXISTS lucy;
Query OK, 0 rows affected (0.00 sec)
You can see that MySQL didn't complain, but it didn't do anything either: the 0 rows
affected message indicates that no data was changed. This addition is useful when
you're adding SQL statements to a script: it prevents the script from aborting on error.
Let's discuss how to choose database names and the use of character case. Database
names define physical directory (or folder) names on disk. On some operating systems,
directory names are case-sensitive; on others, case doesn't matter. For example, Unix-
like systems such as Linux and Mac OS X are typically case-sensitive, while Windows
isn't. The result is that database names have the same restrictions: when case matters
to the operating system, it matters to MySQL. For example, on a Linux machine, LUCY ,
lucy , and Lucy are different database names; on Windows, they refer to just one data-
base. Using incorrect capitalization under Linux or Mac OS X will cause MySQL to
complain:
mysql> select artIst.Artist_id from ARTist;
ERROR 1146 (42S02): Table 'music.ARTist' doesn't exist