6.2. Creating and Dropping Tables
With MySQL or mSQL successfully installed, you should now be ready to
create your first table. The table, a structured
container of data, is the basic concept in a relational database.
Before you can begin adding data to a table, you must define the
table's structure. Consider the following layout:
+---------------------------------+
| people |
+-------------+-------------------+
| name | char(10) not null |
| address | text(100) |
| id | int |
+-------------+-------------------+
Not only does the table contain the names of the columns, but it also
contains the types of each field as well as any additional
information the fields may have. A field's datatype specified
what kind of data the field can hold. SQL datatypes are similar to
datatypes in other programming languages. The full SQL standard
allows for a large range of datatypes. MySQL implements most of them,
while mSQL contains only a few of the most useful types.
The general syntax for table creation is:
CREATE TABLE table_name (column_name1 type [modifiers]
[, column_name2 type [modifiers]]
)
NOTE
What constitutes a valid identifier -- a name for a table or
column -- varies from DBMS to DBMS. mSQL provides close to the
bare minimum support for names. It accepts any sequence of
International Standards Organization (ISO) 8859-1 (Latin 1) letters,
numbers, or `_' up to 20 characters as a valid
identifier. An identifier must begin with a letter. Good database
design only encounters problems with the ISO 8859-1 restriction. In
other words, for good portable SQL, you do not want to have names
that start with anything other than a valid letter. MySQL lets you go
further. It allows up to 64 characters in an identifier, supports the
character `$' in identifiers, and lets identifiers start
with a valid number. More important, however, MySQL considers any
valid letter for your local character set to be a valid letter for
identifiers.
A column is the individual unit of data within a
table. A table may have any number of columns, but large tables may
be inefficient. This is where good database design, discussed in
Chapter 2, "Database Design", becomes an important skill. By creating
properly normalized tables, you can "join" tables to
perform a single search from data housed in more than one table. We
discuss the mechanics of a join later in the chapter.
Like most things in life, destruction is much easier than creation.
The command to drop a table from the database
is:
DROP TABLE table_name
This command will completely remove all traces of that table from the
database. MySQL will remove all data within the destroyed table from
existence. If you have no backups of the table, you absolutely cannot
recover from this action. The moral of this story is to always keep
backups and be very careful about dropping tables. You will thank
yourself for it some day.
With MySQL, you can specify more than one table to delete by
separating the table names with commas. For example, DROP
TABLE people,
animals,
plants would delete the three
named tables. You can also use the IF EXISTS
modifier under MySQL to avoid an error should the table not exist
when you try to drop it. This modifier is useful for huge scripts
designed to create a database and all its tables. Before the create,
you do a DROP TABLE
table_name IF
EXISTS.
 |  |  | 6. SQL According to MySQL and mSQL |  | 6.3. SQL Datatypes |
Copyright © 2001 O'Reilly & Associates. All rights reserved.
|