Creating and Destroying Tables
The previous section discusses the operations SQL can perform to manipulate data stored as rows within tables in the database. However, there is a separate set of statements that covers the manipulation of the tables (and other objects) within the database themselves. These statements are known as Data Definition Language commands, or DDLs.
The operations that can be performed on tables are fairly basic, since they are quite far-reaching in their consequences. The two simplest operations available are:
- Creating a new table
This is done via the
CREATE
TABLE
command, the syntax of which varies depending on the database platform being used. However, this statement generally specifies the name of the table to be created and the definition of all the columns of the table (both names and datatypes).For example, the SQL we used to create the
megaliths
table within our database was:CREATE TABLE megaliths ( id INTEGER NOT NULL, name VARCHAR(64), location VARCHAR(64), description VARCHAR(256), site_type_id INTEGER, mapref VARCHAR(16) )
CREATE TABLE
will create a brand-new table with the given definition, which will be completely empty until you insert rows into it.- Deleting, or dropping, an existing table
This action is as drastic as data modification can get. The actual table structure within the database is completely removed, as are any rows of data currently stored within that table. This operation cannot usually be rolled back from. Once the fatal statement ...
Get Programming the Perl DBI now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.