You should see a list of tables available in the Meet_A_Geek database. If you've been
following along in the book, there should only be the Customers table.- To see a description of the table, you can type in either of the following:
- SHOW COLUMNS FROM Customers;
or
DESCRIBE Customers;
Tip I prefer to use the second command only because there is less to type. Both of the
commands return the same information.After you have verified your data, you can continue to add tables to the database.
MySQL also enables you to create temporary tables. Temporary tables exist only for the current session
and disappear when the connection is dropped. Temporary tables can only be seen by the connection
that created them. So if I start up MySQL locally and create a temporary table, Joe, on a remote
location, will not see or interact with this table in any way. Temporary tables are useful tools for storing
data temporarily, or when you need to store the results of one query and compare them to the results of
another. To create a temporary table, issue the following command:
CREATE TEMPORARY TABLE tablename(columnsname data type);
As you can see, creating a temporary table is almost like creating a true table, the only difference being
the word TEMPORARY.
Another useful function that was recently introduced into MySQL is the ability to create a table based on
the results of a query. This is a really nice feature because it allows you to create a table without typing
in all the column data. It also allows you to easily create a copy of an existing permanent table. If you
wanted to create a temporary copy of the Customers table, you would type the following statement:
CREATE TEMPORARY TABLE SELECT * FROM Customers;
If you wanted to create permanent copy of the Customers table, you could omit the word TEMPORARY
and insert the new tablename after the word TABLE. The following is the syntax for this action:
CREATE TABLE tablename SELECT * FROM Customers;
Another feature worth mentioning is the IF NOT EXISTS parameter. This statement can be used to
check if a table exists before you actually create it. This is extremely helpful when you need to create a
table, but don't know if it exists already. The syntax would look like the following:
CREATE TABLE IF NOT EXISTS tablename (columnname data type);
Remember that the conditional will only create the table if it doesn't exist. Otherwise it will do nothing.
Note Naming conventions are a necessary evil. They can help the entire project and
bring new people up to speed faster.
A word needs to be mentioned about naming conventions. Naming conventions are a good thing. They
enable you to have a standardized way of naming objects that you or others may use. By naming things
in a certain way, new people can become familiar with a database schema quickly and easily. For
example, if you named the table that holds all your customer data Customers and the table that holds
all your product data Products, it is much easier for the new guy or girl to learn than if you named the
same tables Table_01 and Table_02. The decision is up to you, the database designer. You can
name the tables whatever you choose. I prefer to use the following conventions:
Tables are plural and field names are singular. The table that holds all my customer
data is Customers, not Customer. It just makes sense to me. My Customers table,
or any table for that matter, holds many different types of the same object. A table is
not just a repository for one of my customers but a repository for all my customers. It
just makes sense to make tables plural.
The first letter of a name is always capitalized. This just follows grammar rules. It also
looks neater, in my opinion.
Compound names are separated by an underscore, and the first letter of each name is
capitalized (for example, Meet_A_Geek). It may be a pain to type, but it makes things
easier to read. Also, spaces and dashes are not allowed in any database object name.
Use descriptive names and be consistent. When you create a whole bunch of tables,
it's nice to know that Last_Name will always be Last_Name, no matter in which table
it exists. This is especially helpful when developing programs and queries that access
a lot of tables repeatedly.