Databases Reference
In-Depth Information
CHAPTER 6
Working with Database Structures
This chapter shows you how to create your own databases, add and remove structures
such as tables and indexes, and make choices about column types in your tables. It
focuses on the syntax and features of SQL, and not the semantics of conceiving, spec-
ifying, and refining a database design; you'll find an introductory description of data-
base design techniques in Chapter 4. To work through this chapter, you need to un-
derstand how to work with an existing database and its tables, as discussed in Chap-
ter 5.
This chapter lists the structures in the sample music database used in this topic; detail
on how to load the database is presented in Chapter 2. If you've followed those in-
structions, you'll already have the database available and know how to restore the
database after you've modified its structures.
When you finish this chapter, you'll have all the basics required to create, modify, and
delete database structures. Together with the techniques you learned in Chapter 5,
you'll have the skills to carry out a wide range of basic operations. Chapters 7, 8, and
9 cover skills that allow you to do more advanced operations with MySQL.
Creating and Using Databases
When you've finished designing a database, the first practical step to take with MySQL
is to create it. You do this with the
CREATE DATABASE
statement. Suppose you want to
create a database with the name
lucy
. Here's the statement you'd type in the monitor:
mysql>
CREATE DATABASE lucy;
Query OK, 1 row affected (0.10 sec)
We assume here that you know how to connect to and use the monitor, as described
in Chapter 3. We also assume that you're able to connect as the
root
user or as another
user who can create, delete, and modify structures (you'll find a detailed discussion on
user privileges in Chapter 9). Note that when you create the database, MySQL says that
one row was affected. This isn't in fact a normal row in any specific database—but a
new entry added to the list that you see with
SHOW DATABASES
.
