Now that you have the beginning of your database, you can proceed to entering data. The text will supply you with the exact syntax for the first entry and a table listing the data for the rest of the entries.
You will begin, once again, with the simple duck_title table:
INSERT INTO duck_title VALUES (1, 'Mr.'), (2, 'Ms.'), (3, 'Mrs.'), (4, 'Miss'), (5, 'Sir'), (6, 'Dame'), (7, 'Dr.'), (8, 'Lady'), (9, 'None');
The command line shows the command and the return statement:
Query OK, 9 rows affected (0.02 sec) Records: 9 Duplicates: 0 Warnings: 0
To verify your data entry, use a SELECT command with the asterisk (*) wildcard symbol. Whenever an asterisk is used in a command, it can roughly be translated as all that you find or everything, so the following command is the equivalent of saying, Select everything in the table duck_title .
SELECT * FROM duck_title:
The resulting display allows you to examine the contents of the table and compare them with the command still visible above it. At this point, the table duck_title is completely loaded with the required data.
Tip | If you cannot remember the index number for any of the titles, you can use this SELECT command as a visual reminder. |
Insert the first of the customer records by typing in the following command:
INSERT INTO duck_cust VALUES (NULL, 8, 'Salisbury', 'Jenny', 0, '9 Wishing Well Court', 0, 'Meadowlark Hill', 'KS', '67048', '1234', 'Spike', '1961:03:21');
The command line will return with Query OK, 1 row affected as long as you have carefully reproduced the command.
Verify the entry by using the SELECT statement on the duck_cust table:
SELECT * FROM duck_cust;
Insert the remaining customer data into the duck_cust table:
INSERT INTO duck_cust VALUES (NULL, 1, 'Irishlord', 'Red', 'III', '1022 N.E. Sea of Rye', 'A207', 'Seacouver', 'WA', '98601', '3464', 'Netrek Rules', '1967:10:21');
INSERT INTO duck_cust VALUES (NULL, 4, 'Thegreat', 'Vicki', 0, '2004 Singleton Dr.', 0, 'Freedom', 'KS', '67209', '4321', 'Frida Kahlo de Tomayo', '1948:03:21'); INSERT INTO duck_cust VALUES (NULL, 9, 'Montgomery', 'Chantel', 0, '1567 Terra Cotta Way', 0, 'Chicago', 'IL', '89129', '4444', 'Bianca', '1971:07:29'); INSERT INTO duck_cust VALUES (NULL, 7, 'Robert', 'David', 'Sr.', '20113 Open Road Highway', '#6', 'Blacktop', 'AZ', '00606', '1952', 'Harley', '1949:08:00'); INSERT INTO duck_cust VALUES (NULL, 5, 'Kazui', 'Wonko', 'PhD', '42 Cube Farm Lane', 'Gatehouse', 'Vlimpt', 'CA', '45362', 0, 'Fitzwhistle', '1961:12:04'); INSERT INTO duck_cust VALUES (NULL, 6, 'Gashlycrumb', 'Karen', 0, '3113 Picket Fence Lane', 0, 'Fedora', 'VT', '41927', '5698', 'Tess D''urberville', '1948:08:19');
Once you have all the data inserted, use the wildcard SELECT command to view the entire table.
SELECT * FROM duck_cust;
If the display is too confusing to read quickly and clearly, you can use a variation of the SELECT command to look at only one column at a time by replacing the asterisk with the column name . The following command will list only the contents of the cust_duckname column.
SELECT cust_duckname FROM duck_cust;
In Project 2-2, after what was probably a certain amount of frustrating typos and omissions, you inserted data into both the duck_title and the duck_cust tables you created. You also used the most basic form of the SELECT FROM command to display some or all the records in a table. You now have the basic database required for the projects in the remaining modules.
In the preceding projects, you used several commands to choose and display information about your database tables. The USE command tells MySQL which database your subsequent commands refer to. The SHOW command tells MySQL to list either databases or tables. The DESC command requests a description of a specific table or a specific column within that table. The SELECT FROM command, as it was used in Project 2-2, requests the display of all of the records in a table. The basic syntax of these commands follows :
USE <database_name>; SHOW <DATABASES TABLES GRANTS PRIVILEGES>; DESC <table_name> [<column_name>]; SELECT { * <column_name> <list of column_names>} FROM <table_name>;
The SELECT command can be used for more than displaying data on the command line and will be covered in more depth in Module 4. Now that you have completed Projects 2-1 and 2-2 and have your beginning database, you should take a moment to correlate the results with the steps you took. In the project, you saw how the display wrapped around because of the number of fields in each record, but the data is readable and corresponds with the headers above it.
Note that the initial entry of NULL in the values of the INSERT INTO duck_cust command in step 3 has caused the auto-incremented cust_num column to place a 1 in that row s field. Subsequent entries caused each record in succession to have a cust_num entry one digit higher than the one before it.
An 8 was entered in the cust_title column on the first record, and if you cross- reference that to the index 8 in the duck_title table, you get the word Lady , which matches the data in Table 2-13. This customer has only one address (no apartment number or other information), and so a was entered into the cust_add2 field. Similarly, there was no suffix to the customer s name, so it also had a in its field.
Title | First Name | Last Name | Suffix | Address 1 | Address 2 | City | State | Zip Code | Plus 4 | Duck's Name | Duck's Birthday |
---|---|---|---|---|---|---|---|---|---|---|---|
Lady | Jenny | Salisbury | 9 Wishing Well Court | Meadowlark Hill | KS | 67048 | 1234 | Spike | 03/21/1961 | ||
Mr. | Red | Irishlord | III | 1022 N.E. Sea of Rye | A207 | Seacouver | WA | 98601 | 3464 | Netrek Rules | 10/21/1967 |
Miss | Vicki | Thegreat | 2004 Singleton Dr. | Freedom | KS | 67290 | 4321 | Frida Kahlo de Tomayo | 03/21/1948 | ||
Chantel | Montgomery | 1567 Terra Cotta Way | Chicago | IL | 89129 | 4444 | Bianca | 07/29/1971 | |||
Dr. | David | Robert | Sr. | 20113 Open Road Highway | #6 | Blacktop | AZ | 00606 | 1952 | Harley | 08/00/1949 |
Sir | Wonko | Kazui | PhD | 42 Cube Farm Lane | Gatehouse | Vlimpt | CA | 45362 | Fitzwhistle | 12/04/1961 | |
Dame | Karen | Gashlycrumb | 3113 Picket Fence Lane | Fedora | VT | 41927 | 5698 | Tess D'urberville | 08/19/1948 |
The last customer entered into the database provided an opportunity to deal with a special character embedded in a string. Lady Gashlycrumb s duck is named Tess D'urberville , and the embedded apostrophe in that name required that you add an extra apostrophe in front of it in the command ('Tess D''urberville'). MySQL then knows to accept the apostrophe as a text character, which should be written to the field, rather than as a special character, which indicates the beginning or ending of a string. As explained previously in this module, you alternatively could have used a backslash ('Tess D\'urberville') and achieved the same result.
You can avoid having to put the zeros in the fields with no data if they have not been designated NOT NULL , but it requires that you type the column names, in the order you are putting the data in, to indicate where each insertion goes. If you are entering only a few fields, this can be useful. The following is an example of entering the same data as step 3, using the column names method to avoid entering zeros into fields where there is no data:
INSERT INTO duck_cust (cust_title, cust_last, cust_first, cust_add1, cust_city, cust_state, cust_zip1, cust_zip2, cust_duckname, cust_duckbday) VALUES (8, 'Salisbury', 'Jenny', '9 Wishing Well Court', 'Meadowlark Hill', 'KS', '67048', '1234', 'Spike', '1961:03:21');
If you compare this INSERT command with the one you used in step 3, you can see that only fields with actual data were inserted, but each field name had to be specified before the values to insert were listed. This results in more typing than you saved by not inserting zeros into the nonrequired fields.
As you can see in Figure 2-1, if you had entered this command immediately after the command in step 3, it would have duplicated the record with some interesting differences: The first record, which inserted zeros into the two fields with no information, saved nothing into the cust_suffix field and a string with the supplied into cust_add2 field. The reason for the difference is that cust_suffix is an enumerated list and saves only those values that have been defined as acceptable. The string is not one of those values. cust_add2 is a character string field, and so it can have the string inserted into it.
In the second record, where no input was inserted into those two fields, however, the cust_suffix field and the cust_add2 field both contain the word NULL . Since the cust_suffix field was not designated NOT NULL , when the values for its enumerated list were defined, a NULL is the only thing besides the contents of that list that can be stored there. The end result, as far as output, is the same. However, the presence of the NULL indicator in a field means that the missing value may or may not have been intentionally left out. The presence of the string or a blank field means that the data was intentionally left out of the INSERT command. More about the INSERT command will be covered in Module 3, as well as adding UPDATE and DELETE to your repertoire of commands.