Apr 14, 2011
On our previous topics, we’ve shown you how to create MySQL database. And we also demonstrate many ways of doing it by using your good old Microsoft Explorer, mysql commandline tool and the GUI MySQL Browser. The reason why we ‘reveal’ those ‘techniques’ is for you to have a choice. A very good example is that if you want to retain the ‘case’ or ‘letter case’ of your database names because a certain ‘naming standard’ should be enforced, you can choose the first method(creating a database by creating a folder) stated in our
Part 1 of video tutorials.
The second part (Part 2) teaches us to create tables and the necessary information prior to creating them. We’ve shown you the different MySQL data types that will serve as the ‘container’ of your data. These different types of data should be your basic guiding principle in building your database.
Although the information presented is not 100% ‘complete’ this will give us the necessary information to start building databases on our own. Remember that experience is the best teacher. And as I have stated before I do not want you to be swarmed with information that you might not need as a beginner. So enough for the recap, we will now learn how to insert data or load data in the tables we have created on our previous guides.
Let’s begin. But before we ‘populate’ our tables with data, let me show you some simple trick that we might need during the course. Previously, to ‘point’ our SQL commands to a particular database, we utilize the ‘USE <database name>;’ SQL command or by double-clicking on the database name on the left of the MySQL Browser window. Alternatively to avoid issuing the ‘USE’ command everytime we want access to a table, we can ‘specify’ the name of the database directly in our SQL command. See example below.
Instead of this:
SELECT * from animals;
SELECT * from myfolder.animals;
Remember that you should always put the name of the ‘database’ before the ‘.’ and not the other way around. Of course there are disadvantages and advantages to this technique. If you’ll be running a script or two and you have already determined your target database, you can use the ‘dot’ notation of accessing a database. But if a task requires you to run several hundred of scripts on different databases, it will be time consuming for you to edit each SQL command. Even if you enlist the help of your favorite text editor, still it will require a certain amount of time. Remember that programmers time is very important. Additionally, during your ‘text replace’ routine, you might accidentally ‘replace’ all text erronously. In this kind of problem using the ‘USE <database name>;’ will come in very handy. All you need is to edit a single line of SQL command.
There are two ways(that I know) to insert data to a table. The first one is by using the ‘INSERT’ SQL command. The other one is through the ‘LOAD’ SQL command. While the ‘INSERT’ method allows you to insert data by specifying the data to be inserted, the ‘LOAD’ command allows you to insert ‘data’ coming from a text file. Let’s look into some examples.
INSERT INTO <database name>.<table name> col1, col2, col3 VALUES value1, value2, value3;
col1, col2, col3 – the ‘field’ or ‘column’ names of your table <table name>;
value1, value2, value3 – the ‘values’ or ‘data’ you want to insert to col1, col2, col3 respectively
If transalated to human language, the SQL statement means that ‘I want to insert value1 to col1, value2 to col2 and value3 to col3 of table <table name> in the database <database name>’.
Note: You can omit col1, col2, col3 in the ‘INSERT’ statement, but take note that the first value after the reserved word ‘VALUES’ will be inserted to the first ‘field’ or ‘column’ of your table(the same goes for all values you specified).
LOAD DATA INFILE ‘<file path>’ INTO TABLE <database name>.<table name> FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\r\n';
LOAD DATA INFILE – is the SQL command
INTO TABLE <table name> – is the destination table
<file path> – the complete file path to the file you want to load
– if you will use ‘\’ use ‘\\’ instead
– but if you will use ‘/’, you only need it once
FIELDS TERMINATED BY ‘,’ – means the character that separates each field values
LINES TERMINATED BY ‘\r\n’ – means the character that terminates a line of a text file
– in Windows text file it is ‘\r\n’
Imagine a file somefile.csv located at c:\ drive and it contains the following:
- – – – – – – –
– – – – – – – –
– – – – – – – -
Let’s assume that we have an existing database ‘mydatabase’ that contains a table ‘mytable’. Regardless of the field names, the only thing we need to know about is the ‘data type’ of the fields. Again let’s assume that the ‘data type’ used is ‘INT’ so that it will match to the data we have. To load the contents of ‘somefile.csv’ we will use the ‘LOAD’ SQL statement below.
LOAD DATA INFILE ‘c:/somefile.csv’ INTO TABLE mydatabase.mytable FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\r\n';
Note: You can use ‘\’ but use a double ‘\’ instead. If using ‘\’ in the example above it should be ‘c:\\IuPsref.csv’. The ‘\r\n’ corresponds to ‘CRLF’ at the end of every row of data inside a text file. It’s not visible using just your plain old text editor. Try using Notepad++ to see ‘things’ you didn’t see. Click ‘View’ -> ‘Show Symbols’ -> ‘Show All Characters’ to see the ‘CRLF’. See images below.
That’s it, now let’s put all those things we’ve learned so far in action. Let’s watch the video.
I hope that you’ve learned a lot from Part 3 of our tutorial. Thanks for the patience and reading my post. But there are more to come from our MySQL tutorials. Remember that these are just the basics that will ‘put you on the ground’. Have a nice day!