Critical Skill 2.3 Create Your Database and Tables

So far, we ve planned only one table, which contains customer information, for our DuckWear company s database. There are times when a certain set of data may be used repeatedly in various tables throughout a database. When it is clear that this may happen, you can make a separate table, so that the actual information needs to be stored only once. Other tables in the database store an index (or key) number to access the information.

For instance, if you wanted to store the full name of a state instead of its two-letter postal designation, you could create a table of the states with an automatically incrementing index number. Storing a digit from 1 to 50 takes up much less room than storing the entire name of any state. Later, when it is time to look at a printout of the customer data, the database could access the number in the State field and use it to reference the state name in the other table, so the name would be printed in the report instead of the number.

For the duckwear database, you will make a title table that allows customers to choose the title of address they prefer for their mailing label: Mr., Mrs., Ms., and so on, or none at all. (Given that it is increasingly difficult to correctly guess gender from looking at a name, it is wise to give your customers the choice.)

So, now you are making two tables in the duckwear database: duck_cust and duck_title . The data to be entered in them will be provided as you work your way through Project 2-1. However, first we need to look at the commands you will use to make the database and the tables.

Creating a Database

The full syntax for the command to create a database is very basic:

CREATE DATABASE [IF NOT EXISTS] <db_name>

Any portion of the syntax of a command within square brackets, [ ], is optional and, in this case, is merely a safeguard. Any portion of the syntax that is in all uppercase letters is a MySQL reserved word, and as stated earlier in this module, should not be used solely as a name, although you may use it as part of a name. For instance, INDEX is a reserved word, but you can name an index something like play_index , without needing any special notation.

If you try to create a database that already exists (and don t use the IF NOT EXISTS option), you will get a warning returned on the command line. If you use IF NOT EXISTS , there is no warning, but either way, the database is not created. So, the simplest syntax with which to create our DuckWear database is as follows :

CREATE DATABASE duckwear;

As mentioned in Module 1, MySQL will not process the command until the closing semicolon has been entered.

Creating a Table

The full syntax to create a table is a little more complex than the one to create a database:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <table_name>[(create_definition,...)] [table_options] [select_statement]

However, most of the syntax (as indicated by enclosing it in square brackets) is optional. The IFNOT EXISTS option works in the same manner as it does in a CREATE DATABASE statement.

The TEMPORARY option means that the table that is created will be visible only as long as the current connection to the database is open . When the connection is closed, even if by accident , the table will be deleted. An example of when you might use a temporary setting is to make a table where the data is a partial copy of data in another table. You can view the data in the table more clearly, and then delete the table after you have finished examining it.

There are a variety of options that go inside the create_definition parentheses, but we will only go over the ones that are used most frequently:

<column_name> <data type> [NOT NULL NULL] [DEFAULT <default_value>][AUTO_INCREMENT] PRIMARY KEY (<index_column_name>,...)

The column_name is used to define the columns of the table (or the fields of each record, if you prefer to think of them like that) and must have the column name and data type, which may be followed by several options:

• The NOT NULL / NULL choice, which determines whether or not the field requires data, as explained earlier in this module

• The ability to set a DEFAULT value (which the database uses in lieu of input data)

• AUTO-INCREMENT , which when set, automatically counts up when NULL is input into the column s field

• PRIMARY KEY , which indicates which column of fields will be used to form an index for faster access to the table s records

Any of these commands, if used, are contained inside the set of parentheses that enclose the column_name definitions and are separated by commas. If this seems confusing, don t panic; after the hands-on experience of Projects 2-1 and 2-2, it will all be clear.

The last two options in the CREATE TABLE command are table_options and select_statement . The most used table option is type , which specifies the table type (also referred to as engine) and determines by which rules the table operates. If you do not set a table type, your table is made with the MySQL default, MyISAM. For clarity, it is recommended that you specify the MyISAM table in a TYPE statement when you make a table. That way, there is no question as to your intentions, even though you have chosen to use the default type.

Correcting Mistakes in Commands

If you make a mistake in the syntax of the command itself or in the names within it, the command line will return an error statement. The error statement will try to give you some idea of what the difficulty is, but sometimes, especially for beginners , it does not seem all that helpful.

If you are sure of the overall syntax of the command, the most common errors should be checked first:

• A string that should be enclosed between single quotation marks but has only a single quotation mark on one side

• A double quotation mark where a single quotation mark should be

• A database, table, or field name that has been misspelled or, on Linux servers, a letter in uppercase rather than lowercase, or vice versa

• A dash where an underscore should be

• A CREATE statement that does not have one or both of the parentheses that surround the column descriptions

Especially on the longer commands, type in the command slowly and carefully , checking each line before pressing the ENTER key to go to the next line. If a typo slips past you, you cannot back up and correct it. If you do make a mistake, entering the ending semicolon without finishing the command will allow you to stop without creating a flawed table. To be absolutely sure that a flawed command is not accepted, you can type some gibberish before the semicolon to make sure that MySQL will return an error and not run the command.

Depending on you typing skills, it may save you a lot of frustration if you take advantage of already correct lines saved in the command-line history. Using the UP ARROW key will allow you to step back through the command history. Use this to back up to the beginning of the command and press ENTER. Repeat this for the next line until you get to the line with the error in it. At that point, you can use the ARROW and BACKSPACE keys to erase the error, and then type in the correct syntax. Continue to use the UP ARROW and ENTER keys until you run out of correctly entered lines of code, and then finish the command.

Formatting Table Data

When you re entering the data for a table, the form of the supplied data may not reflect the format rules of MySQL. You may need to do a certain amount of reformatting in order to enter data properly. Remember to format dates in the correct order ( YYYY:MM:DD ), replace any missing data with a zero ( ), and list the record s fields in the order in which they were defined in the table.

The data you ll enter in Project 2-2 includes a name with an embedded apostrophe, which is the same as the single quotation that marks strings contained within the syntax. Work around this by putting two single quotation marks in the place where the apostrophe should go. For example, to insert the word don t as a string, type the string as 'don''t' , using two single quotation marks between the letters n and t. This will prevent MySQL from confusing the apostrophe for a single quotation mark whose matching quotation mark is missing. You can also place a backslash (\) in front of a single quotation to escape it, or indicate that it is not one of a pair: 'don\'t' .