TT380 : Week 4 - Summary Notes

Okay it's now week 4 of the course and it's time to read hours 9 to 14 of the MySQL course book. So what are these 6 hours about?

• Hour 9 - Populating your database tables.
• Hour 10 - Selecting data from your tables.
• Hour 11 - Advanced usage of SELECT statements.
• Hour 12 - Modifying and deleting data.
• Hour 13 - More about DELETE.
• Hour 14 - Modifying table structure.

Hour 9 Populating your Database Tables

In this hour we will learn how to insert data into out database tables using a variety of methods.

The INSERT command
This MySQL command is the first that we will use to insert data into our database tables. To use this command we need to be in MySQL monitor. So open up a connection to your database and login as normal.

A detailed account has already been written for using this command, and can be accessed via the following link, click here.

It should be noted that although you do not need to specify the names of the column within your table when using the INSERT command. However if you don't then you must ensure that each field within your table has an entry in your INSERT statement even if its empty or is going to use the default value. For example surpose you have a table called employees and you want to add a new record. The employees table has the following fields;

employee_id, employee_startdate, employee_moddate, firstname, lastname, nickname

You are wanting to add the following data into a new record in this table:

James Hummersknott, start date: today. James does not have a nickname. The moddate is obviously also going to be today as thats when you lasted modified the record and the employee_id is your primary key and therefore auto increments.

The command we would use to insert this data is as follows:

INSERT INTO employees
(employee_id, employee_startdate, employee_moddate,
firstname, lastname, nickname)
VALUES
('0', now(), now(), 'James', 'Hummersknott', '')
;

Alternatively if we wanted to simplifiy this and reduce the amount of typing we could use:

INSERT INTO employees
VALUES
('0', now(), now(), 'James', 'Hummersknott', '')
;


The LOAD DATA INFILE method

This is the second method of importing data into your database and again this has been documenetd previously, click here to view the detail blog entry about how to use the LOAD DATA method.

Using mysqlimport

This is our 3rd method of importing data into our database. the mysqlimport must be run from a command prompt and uses a plain text file that contains the data that you want inserting. This command has various switches that you can alter to suit you own personal needs. The following is the basic summary of the syntax that is used for this method of data insertion:

mysqlimport [options] databasename textfile

Okay let's look at that command, the first bit is calling the mysqlimport function. Then we have the chance to enter any options that we wish to use, common options here are:

-L to use a local file
--fields-enclosed-by
--fields-escaped-by
--fields-terminated-by

I have only used 3 of these along with a couple of other ones when I perform my own data insertion using this method. This is the complete command syntax that I used to successfull insert some data from a text file:

mysqlimport -L -v -u jg3723 -pmypassword
--fields-terminated-by=::: --fields-enclosed-by=\"
jg3723 master_name.txt

This successfully entered the contents of master_name.txt into my database called jg3723. It should be noted that this method of data insertion will import the data into the table of the same name as the text file, so in the case of the above example it inserted the data into a table called master_name.

My master_name.txt file consisted of the following data:

"0":::now():::now():::"Jayson":::"Doe"
"0":::now():::now():::"Donna":::"Smith"
"0":::now():::now():::"Tabitha":::"Carr"
"0":::now():::now():::"Naomi":::"Bell"

Hour 10 - Selecting data from your tables

Still to be documeneted - sorry!

Hour 11 - Advanced usage of SELECT statements

Still to be documeneted - sorry!

Hour 12 - Modifying and deleting data

Still to be documeneted - sorry!

Hour 13 - More about DELETE

Still to be documeneted - sorry!

Hour 14 - Modifying table structure

Still to be documeneted - sorry!

My Conclusion

Well that's the end of this particular blog entry and I hope that some of the information is of help to you. Please feel free to comment, all questions will be answered to the best of my ability.

All the best Jayson