TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

MySQL Insert Statement

MySQL Insert Statement for beginners and professionals with examples on CRUD, insert statement, select statement, update statement, delete statement, use database, keys, joins etc.

<< Back to MYSQL

MySQL INSERT Statement

MySQL INSERT statement is used to store or add data in MySQL table within the database. We can perform insertion of records in two ways using a single query in MySQL:

  1. Insert record in a single row
  2. Insert record in multiple rows

Syntax:

The below is generic syntax of SQL INSERT INTO command to insert a single record in MySQL table:

INSERT INTO table_name ( field1, field2,...fieldN )  
VALUES  
( value1, value2,...valueN );  

In the above syntax, we first have to specify the table name and list of comma-separated columns. Second, we provide the list of values corresponding to columns name after the VALUES clause.

NOTE: Field name is optional. If we want to specify partial values, the field name is mandatory. It also ensures that the column name and values should be the same. Also, the position of columns and corresponding values must be the same.

If we want to insert multiple records within a single command, use the following statement:

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

In the above syntax, all rows should be separated by commas in the value fields.

MySQL INSERT Example

Let us understand how INSERT statements work in MySQL with the help of multiple examples. First, create a table "People" in the database using the following command:

CREATE TABLE People(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(45) NOT NULL,
	occupation varchar(35) NOT NULL,
	age int,
	PRIMARY KEY (id)
);

1. If we want to store single records for all fields, use the syntax as follows:

INSERT INTO People (id, name, occupation, age) 
VALUES (101, 'Peter', 'Engineer', 32);

2. If we want to store multiple records, use the following statements where we can either specify all field names or don't specify any field.

INSERT INTO People VALUES
(102, 'Joseph', 'Developer', 30),
(103, 'Mike', 'Leader', 28),
(104, 'Stephen', 'Scientist', 45);

3. If we want to store records without giving all fields, we use the following partial field statements. In such case, it is mandatory to specify field names.

INSERT INTO People (name, occupation) 
VALUES ('Stephen', 'Scientist'), ('Bob', 'Actor');

In the below output, we can see that all INSERT statements have successfully executed and stored the value in a table correctly.

MySQL INSERT Statement

We can use the below syntax to show the records of the People table:

mysql> SELECT * FROM People;

We will get the output as follows:

MySQL INSERT Statement

Inserting Date in MySQL Table:

We can also use the INSERT STATEMENT to add the date in MySQL table. MySQL provides several data types for storing dates such as DATE, TIMESTAMP, DATETIME, and YEAR. The default format of the date in MySQL is YYYY-MM-DD.

This format has the below descriptions:

  • YYYY: It represents the four-digit year, like 2020.
  • MM: It represents the two-digit month, like 01, 02, 03, and 12.
  • DD: It represents the two-digit day, like 01, 02, 03, and 31.

Following is the basic syntax to insert date in MySQL table:

INSERT INTO table_name (column_name, column_date) VALUES ('DATE: Manual Date', '2008-7-04'); 

If we want to insert a date in the mm/dd/yyyy format, it is required to use the below statement:

INSERT INTO table_name VALUES (STR_TO_DATE(date_value, format_specifier));

Next TopicMySQL Update Query




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf