MySQL Insert Data
In the previous article, you have learnt how to create a table and define column names and in this article, you will learn to insert data in a table.
The INSERT keyword is used to insert values in an existing table.
Syntax of MySQL insert query
INSERT INTO Tablename (field1, field2, filed3) VALUES ('value1', 'value2', 'value3');
In insert query, fields name and their values are separated by comma.
The given query insert data in the 'students' table.
mysql->INSERT INTO students(id, first_name, last_name, age, class) VALUES ('', 'John', 'Brown', '10', '4A');
We have made 'id' field value blank because we have set it AUTO_INCREMENT during table creation.
You can insert multiple values in a single insert query. All the values of each row set should be comma separated, like -
mysql->INSERT INTO student(id, first_name, last_name, age, class) ->VALUES ('', 'John', 'Brown', '10', '5A'), ->VALUES ('', 'Ray', 'Bryan', '11', '6B'),;
If the length of values exceed the defined size of fields then the characters will be truncated to the column's maximum length.
MySQL Load Data
We can also populate records in a created table using text file. For this, create a text file and write data to insert. The one record data should be in one line and separated by tabs. You can write \N in place of NULL values or missing values.
Suppose, there is a 'students.txt' text file in the given format.
\N Sonia Roy 9 4A \N John Smith 10 5B \N Ria Gaga 11 6C
The given command populates the text file data in students table.
mysql->LOAD DATA LOCAL INFILE 'C:/db/students.txt' INTO TABLE students ->LINES TERMINATED BY '\r\n';