Module 3: Manipulating the Database

This module will further your ability to interface with your newly made database. You will learn more ways to add new data to your database, as well as how to edit or add to existing data and create indexes to speed access to your data. This module will also show you how to remove either records from tables or entire tables from your database.

Critical Skill 3.1 Add Data to Your Database

After you have a basic database, you will want to insert data into existing tables and add new tables. You can add data to your database in a variety of ways, in addition to using the INSERT command covered in the previous module.

Sometimes, an existing table can have all or nearly all the data you want to use in another table. In that case, it is easier and more efficient to create your new table by copying the old one and altering that copy. You can do this with one command, which defines the new table, and then fills it with the specified data. If you want to use data from just selected parts of another table, you can do that as well.

Occasionally, you may have data in a text file where the fields are separated by a common divider like a comma. You can use that file as a source to load all its data into a table with one command.

In this section, you will learn how to add data using both techniques: by copying a table and by using a text file as a source.

Copying a Table

In order to create a copy of a table so that you can alter it or experiment with it without endangering the original data, you can use the following command:

 CREATE TABLE <  new_table_name  > SELECT * FROM <  old_table_name  >; 

Note  

Remember that commands in MySQL are not ended until the semicolon. So the preceding code, despite being displayed on two lines, is only one command.

You can give your new table any name ( new_table_name ) you like, with just a few restrictions, as discussed in Module 2. As we suggested in that module, the name should clearly but succinctly reflect the table s intended use. For example, if it is an experimental copy of the original table, naming it with the original name followed by the word copy makes its function clear; a copy of duck_cust could be named duck_cust_copy .

As you learned in the previous module, the use of the asterisk ( * ), or wildcard symbol, means everything. In this case, it is instructing MySQL to use not only the table definition data, but also all of the data the table contains. The only difference between the two tables, besides their names , is that if the old table had an index (or key), it does not transfer to the new table. If the new table needs the same or another index, you can create one for it, as explained in the Creating an Index section later in this module.

Copying Only Selected Columns from a Table

If you need a table that holds only part of the information in an existing table, you can specify the columns you want defined in the new table by using the following command:

 CREATE TABLE <  new_table_name  > SELECT <  column_name1  >, <  column_name2  > FROM <  old_table_name  >; 

This example shows only two columns chosen , but you can list any number of columns, separated by commas.

The same basic result can be approached in a different way. You can choose to create the table first, and then use an INSERT command to populate the table with data, using this command:

 CREATE TABLE <  new_table_name  > (<  column_name1  > CHAR(15) NOT NULL, <  column_name2  > CHAR(20) NOT NULL)TYPE=MyISAM; INSERT INTO <  new_table_name  > SELECT <  column_name1  >, <  column_name2  > FROM <  old_table_name  >; 

Copying to a Temporary Table

If you are creating a new table strictly to experiment with, and you won t need it after you ve learned what you need to from it, you can create the copy as a temporary table by using the following syntax:

 CREATE TEMPORARY TABLE <  new_table_name  > SELECT * FROM <  old_table_name  >; 

This copy of the old table will remain until the current session with the MySQL Client is ended. This is true whether the connection is severed on purpose or by accident , so keep that in mind if you are working with an unstable system.

Temporary tables are saved differently than permanent tables, and as a result, they do not show up in the output of the SHOW TABLES; command. If you leave your MySQL session connected and later find you have forgotten what you named the temporary table, use the arrow keys to search the command-line history for the command that created it.

Copying Only Selected Data from a Table

You can also use a more specific SELECT command to make a temporary or permanent table that holds only part of the data in the original table. Using the duck_cust table as an example, the following command will create a temporary table named temp_list , which displays only those customers who list the state KS in their customer record, and then print a display of their customer number and last name, from the temp_list table on the command line:

 CREATE TEMPORARY TABLE tempKS_list SELECT cust_num, cust_last  FROM duck_cust WHERE cust_state LIKE "KS";SELECT *  FROM tempKS_list; 

Inserting Data from a Text File

Another option is to insert data into a table by using a file, such as a log file from your server, as a source. For this to work, the file must have the proper format ”the data types and sizes, and the order of its arrangement, must match the table in order to load the entire file at one time. Also, it must be saved in a simple text file format, which usually means the file will have a .txt extension. Text files saved in more complex, word-processor formats, such as Word's .doc file format, have too many ”and often invisible ”formatting symbols that will corrupt data when it s loading. You can either design a table to hold the information that reflects the file s format, or edit the file so that its format fits an existing table in your database. This might seem like extra work, but the amount of time you will save by inserting multiple records this way usually outweighs the amount of time it takes to set up the file or a new table.

A comma-separated file is a text file in which each field of data in a record is separated from its neighboring fields with a comma (i.e. field1, field2, field3, and so on) The end of each record in the comma-separated field is indicated by an invisible Line Terminator, which you insert by pressing the ENTER key. Windows and Linux indicate the end of line differently, so you have to know your operating system and use the proper terminator in your commands. The Windows Line Terminator is " \r\n " and the Linux Line Terminator is " \n ". The syntax for a command to use a comma-separated text file, saved in a Windows format, is as follows :

 LOAD DATA INFILE '<  full_path_to_text_file  > INTO TABLE <  table_name  > FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; 

The path to the text file varies, depending on where you choose to save the file. The best place is somewhere you can easily remember, and of course, a shorter path is easier to type into the command line than a longer one.

In the FIELDS TERMINATED BY code, you can designate other separators:

• Colons, by using ':'

• Semicolons, by using ';'

• Spaces, by using ' '

Another useful option when you insert data from a file is IGNORE < numeric_value > lines . Often, data stored in a file will have headers at the top. This option allows you to tell MySQL to skip over those lines and only load actual data. If the first line of your semi- colon - separated test file ( duck.txt ) is a series of column headers, and you do not want those headers placed in your table ( quackers ) as a record, you would use the IGNORE clause, as shown in the following example, using Linux Line Termination:

 LOAD DATA INFILE '/documents/duck.txt' INTO TABLE <  quackers  > FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;