MySQL INSERT Statement

Summary: In this chapter, we will learn how to use MySQL INSERT statement to insert data into the database tables.

MySQL INSERT Statement to insert data into the database tables

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

1 2	INSERT INTO table_name (column1, column2, column3, ...)             VALUES (value1, value2, value3, ...);

In above syntax, we specify the table name and a list of comma-separated columns inside parentheses after the INSERT INTO clause. Then, we put a comma-separated values of the corresponding columns inside the parentheses followed the VALUES keyword.

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

1 2	INSERT INTO table_name             VALUES (value1, value2, value3, ...);

MySQL INSERT INTO statement examples

Let’s create a new table named customers for practicing the INSERT statement.

1 2 3 4 5 6 7 8 9 10

CREATE TABLE IF NOT EXISTS customers (     CustomerID INT(11) AUTO_INCREMENT,     CustomerName VARCHAR(45) DEFAULT NULL,     ContactName VARCHAR(45) DEFAULT NULL,     Address VARCHAR(200) DEFAULT NULL,     City VARCHAR(100) DEFAULT NULL,     PostalCode VARCHAR(45) DEFAULT NULL,     Country VARCHAR(100) DEFAULT NULL,     PRIMARY KEY (CustomerID) );

MySQL INSERT – insert single row

For example, if we want to insert a new customer details into the customer table, we use the INSERT  statement as follows:

1 2	INSERT INTO customers (CustomerName, ContactName, Address, City, PostalCode, Country)        VALUES ('Rama Krishna','RK Ayyar','Street 21','Mumbai','305408','India');

After executing the statement, MySQL returns a message to inform the number of rows affected. In this case, one row were affected.

Output:   1 row(s) affected

MySQL INSERT – insert multiple rows

In this form, the value list of each row is separated by a comma. For example, to insert multiple rows into the tasks table, you use the following statement:

1 2 3 4

INSERT INTO customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Kelvin','Samyu Lame','Ring Road','Mumbai','305422','India'),        ('John Freak','Hennery','New Town','Alaska','858586','US'),        ('Amit Rastogi','Pooja Kumar','BTM Layout','Bangalore','560013','India');

Output:   3 row(s) affected

You can check affected data using MySQL SELECT statement as follows,

1	SELECT * FROM customers;

Notice that we don’t have to specify the value for auto-increment column e.g., customerId column because MySQL generates value for the auto-increment column automatically.

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

The following MySQL statement will insert a new record, but only insert data in the “CustomerName”, “City”, and “Country” columns (rest of column will be updated automatically):

1 2	INSERT INTO Customers (CustomerName, City, Country)             VALUES ('Shaynu', 'Nara', 'Japan');

In this chapter, we have learnt how to use various forms of the MySQL INSERT statement.

---

MySQL Subquery MySQL UPDATE

---