Addressbook in MySQL

AddressBook MySQL Data Entry Options on a Linux Server


Visit http://www.mysql.com and check out the documentation section for the online manual. Go directly to the manual here: http://www.mysql.com/doc/D/a/Database_use.html


1. ENTERING BULK DATA INTO A DATABASE:

Create a spreadsheet with the fields in the exact order of the database.
fname lname phone age gender email profile



You could create a text file `addressbook.txt' containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown age), you can use NULL values. To represent these in your text file, use \N.

mysql> LOAD DATA LOCAL INFILE "addressbook.txt" INTO TABLE addressbook;




2. ENTERING RECORDS ONE AT A TIME:

When you want to add new records one at a time, the INSERT statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the CREATE TABLE statement. Suppose Diane gets a new hamster named Puffball. You could add a new record using an INSERT statement like this:

mysql> INSERT INTO addressbook (fname,lname,phone,age,gender,email,profile)
-> VALUES ('Scott','LeDuc','753-8892','32','M','sleduc@osd.wednet.edu',NULL);


Note that string and date values are specified as quoted strings here. Also, with INSERT, you can insert NULL directly to represent a missing value. You do not use \N like you do with LOAD DATA.

From this example, you should be able to see that there would be a lot more typing involved to load your records initially using several INSERT statements rather than a single LOAD DATA statement.




3. DELETING CONTENT OF A TABLE:

If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form:

mysql> DELETE FROM table_name WHERE 1>0;

Note that this is MUCH slower than DELETE FROM table_name with no WHERE clause, because it deletes rows one at a time.

The following will delete all of the rows at once:
mysql> DELETE FROM table_name;