Database Reference
In-Depth Information
Creating a Database
Creating a database is simple, mostly because there's nothing much to it. Use the SQL
statement CREATE DATABASE . You will have to provide a name for the database with
this SQL statement. You could call it something bland like db1 . However, let's do
something more realistic and interesting. I'm a fan of birds, so I've used a database of a fic-
titious bird-watching website for the examples in this topic. Some birds live in groups, or a
colony called a rookery . To start, let's create a database that will contain information about
birds and call it rookery . To do this, enter the following from within the mysql client:
CREATE DATABASE rookery;
As previously mentioned, this very minimal, first SQL statement will create a subdirectory
called rookery on the filesystem in the data directory for MySQL. It won't create any
data. It will just set up a place to add tables, which will in turn hold data. Incidentally, if
you don't like the keyword DATABASE , you canuse SCHEMA instead: CREATE SCHEMA
database_name . The results are the same.
You can, though, do a bit more than the SQL statement shown here for creating a database.
You can add a couple of options in which you can set the default types of characters that
will be used in the database and how data will be sorted or collated.So, let's drop the
rookery database and create it again like so:
DROP DATABASE rookery ;
CREATE DATABASE rookery
CHARACTER SET latin1
COLLATE latin1_bin ;
The first line in this SQL statement is the same as the earlier one — remember, all of this is
one SQL statement spread over two lines, ending with the semicolon. The second line,
which is new, tells MySQL that the default characters that will be used in tables in the data-
base are Latin letters and other characters. The third line tells MySQL that the default
method of sorting data in tables is based on binary Latin characters. We'll discuss binary
characters and binary sorting in a later chapter, but it's not necessary to understand that at
this point. In fact, for most purposes, the minimal method of creating a database without
options, as shown earlier, is fine. You can always change these two options later if neces-
sary. I'm only mentioning the options here so that you know they exist if you need to set
them one day.
Now that we've created a database, let's confirm that it's there, on the MySQL server. To
get a list of databases,enter the following SQL statement:
Search WWH ::




Custom Search