MariaDB INSERT INTO Statement


Objectives

  • Understanding MariaDB INSERT INTO Statement

  • MariaDB INSERT INTO Statement without Column Name

  • MariaDB INSERT INTO Statement with Column Name

  • MariaDB INSERT INTO Statement Multiple Row


Create database and table if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)


Understanding MariaDB INSERT INTO Statement

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
-- OR If you are adding values for all the columns of the table with sequence
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Here

  • INSERT INTO : This is the keyword for insert data into table.

  • table_name : Table name, where you want to insert data.

  • column1 …​ columnX : Column names of the table.

  • VALUES : Keyword which specify column values

  • value1 …​ valueX : Value you want to insert to table.

Note : if not specify column1 …​ columnX then you have to maintain table column order.


MariaDB INSERT INTO Statement without Column Name

Syntax

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

Example

INSERT INTO person VALUES (NULL,'Touhid', 'Mia', 'hmtmcse.com@gmail.com', 30, 500);

Must : This statement has not any column name, so here is mandatory things is that, maintain the column order. Where you get the column order then? If you see the table create statement you will find the column order there, for make things easy you can see below

    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,

If you look at the insert SQL then can find the match of above column and the SQL value order.

Output

Query OK, 1 row affected (0.371 sec)


MariaDB INSERT INTO Statement with Column Name

Syntax

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

Example

INSERT INTO person (first_name, age, income, last_name, email) VALUES ('Touhid', 30, 500, 'Mia', 'hmtmcse.com@gmail.com');
Here

When you specify column name in insert SQL then there is no need to maintain column ordering of the table, but you must have to map specified column name and value. Check the above example and then you can see if. For easy understanding see the below codes.

(first_name, age ... ) VALUES ('Touhid', 30 ...)

Output

Query OK, 1 row affected (0.377 sec)


MariaDB INSERT INTO Statement Multiple Row

Syntax

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

Example

INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 1, 5000),
	('Tahsin', 'Mia', 'tahsin@pf.local', 1, 6000),
	('Touhid', 'Mia', 'hmtmcse.com@gmail.com', 30, 500);

Output

Query OK, 3 rows affected (0.361 sec)
Records: 3  Duplicates: 0  Warnings: 0


MariaDB INSERT INTO Table Video tutorial

Share