Where tablename is the name of the table you want to delete. For example, to delete the Customers
table from the Meet_A_Geek database, you would type the following command:
DROP Customers;This will delete the entire table and all the data inside the table. Use caution when executing this
command. Remember there are no warnings from the monitor. After you drop something, the only way
to get it back is through a backup log.If you need to delete a column from a table, enter the following command:
ALTER TABLE tablename DROP columnname;
Where tablename is the table that holds the column you want to delete, and columnname is the
column you want to delete.
If you wanted to delete the Last_Name column of the Customers table, you would enter the following
statement:
ALTER TABLE Customers DROP Last_Name;This will delete the column and all the information that the column stored. Again, exercise caution when
using this command.Adding Columns
We have already covered adding tables to a database. You can only create and drop a table. To add a
column, you have to use a variation of the ALTER TABLE command. For example, to add a column to an
existing schema, execute the following statement:
ALTER TABLE tablename ADD columnname data type;
Where tablename is the table you need to add the column to, and columnname is the name of the
column to be added. If you wanted to add the Last_Name column back to the Customer table, you
would issue the following statement:
ALTER TABLE Customer ADD Last_Name VARCHAR(30);
This will add a column to your table. An important point to remember is that the column you add must
have a default value. It cannot be a NOT NULL column. It must contain NULL or some other default
value. The reason for this is fairly simple. If you add a column that is NOT NULL, how will MySQL know
what value to store? It won't, so you must tell it what to store.Using Indexes
An index is a structured file that facilitates data access.
What this means to you as the database designer is this: An index on the correct column will increase a
query's speed considerably. An index works much like alphabetic separator folders in a file cabinet. It
allows you to skip to the part of the alphabet you're looking for. For example, suppose you needed Glen
Brazil's record. You could go directly to the B section without going through every single record before
you get to Mr. Brazil's. This makes your searches much easier to accomplish, and you're don't waste
time looking at records that are not even close to what you need.Indexes are wonderful things, but they do have some drawbacks. Too many indexes can have an
adverse effect. In the example, you went directly to the B section. What if instead of just having letter
separators you separated every name. There would be a ton of separators—almost as many as the
number of people you were tracking. This would slow things down instead of speeding them up. So it is
best not to have too many indexes.Another adverse effect is that adding a row to an indexed table can be a little slower than adding it to a
non-indexed table. Using the example, it takes a little time to put a record in the correct place. You have
to go through the separators and then place it in the right order within the file drawer. This is much
slower than throwing the record anywhere in the drawer. Retrieval from indexed columns is much
quicker. It is up to you to decide if the good outweighs the bad.
Note Indexes speed up data access for SELECT queries, but they slow it down for
INSERT, UPDATE, and DELETE queries.