Inserting data into tables
The INSERT SQL statement impregnates our table with data. Here is a general form of INSERT.
INSERT into table_name (column1, column2….)
values (value1, value2…);
where table_name is the name of the table into which we want to insert data; column1, column2 etc. are column names and value1, value2 etc. are values for the respective columns. This is quite simple, isn't it?
The following statement inserts the first record in employee_data table.
INSERT INTO employee_data
(f_name, l_name, title, age, yos, salary, perks, email)
values
("Manish", "Sharma", "CEO", 28, 4, 200000,
50000, "manish@bignet.com");
As with other MySQL statements, you can enter this command on one line or span it in multiple lines.
Some important points:
•The table name is employee_data
•The values for columns f_name, l_name, title and email are text strings and surrounded with quotes.
•Values for age, yos, salary and perks are numbers (intergers) and without quotes.
•You'll notice that we've inserted data in all columns except emp_id. This is because, we leave this job to MySQL, which will check the column for the largest value, increment it by one and insert the new value.
Once you type the above command correctly in the mysql client, it displays a success message.
mysql> INSERT INTO employee_data
-> (f_name, l_name, title, age, yos, salary, perks, email)
-> values
-> ("Manish", "Sharma", "CEO", 28, 4, 200000,
-> 50000, "manish@bignet.com");
Query OK, 1 row affected (0.00 sec)
Inserting additional records requires separate INSERT statements. In order to make life easy, I've packed all INSERT statements into a file. Click to download the file, employee.dat (3.38 kb)
Once you download the file, open it in a text editor. You'll notice that it's a plain ASCII file with an INSERT statement on each line.
Inserting data into employee_data table with employee.dat file
On Windows
1.Move the file to c:\mysql\bin.
2.Make sure MySQL is running.
3.Issue the following command
mysql employees <employee.dat
On Linux
1.Migrate to the directory that contains the downloaded file.
2.Issue the following command
mysql employees <employee.dat -u username -p
3.Enter your password.
Our table contains 21 entries (20 from employee.dat file and one from the INSERT statement we issued at the beginning). You can view the table here: db.htm (3.34 kb)