Project 2-2 Insert Data in Tables


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.

Step by Step

  1. 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 
  2. 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.

  3. 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.

  4. Verify the entry by using the SELECT statement on the duck_cust table:

     SELECT * FROM duck_cust; 
  5. 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'); 
  6. Once you have all the data inserted, use the wildcard SELECT command to view the entire table.

     SELECT * FROM duck_cust; 
  7. 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; 

Project Summary

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.

Table 2-13:

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.


Figure 2-1: Allowing MySQL to fill some column data

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.




MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net