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]] )
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.
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.
Copyright © 2001 O'Reilly & Associates. All rights reserved.