EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial Insert in MySQL

Insert in MySQL

By Priya PedamkarPriya Pedamkar

Insert in MySQL

Introduction to Insert in MySQL

The main goal of a database system is to store data in tables. We have different kinds of SQL commands to define the database’s functionality. In this topic, we are going to learn about Insert in MySQL.

SQL commands can be categorized into the following:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • DDL (Data definition language)
  • DML (Data manipulation language)
  • DQL (Data query language)
  • DCL (Data control language)
  • TCL (Transaction control language)

Here in this article, we are going to focus mainly on DML. As we can see, the name Data Manipulation language, so once the table/database is created, we require DML commands to manipulate something. The merits of using these commands are if any wrong changes happen, we can roll back/undo it.

The following are the commands in DML:

1. INSERT: Used to insert new rows into the table.

INSERT into employee
Values(101,'abcd');

2. DELETE: Delete a table’s single row or entire records.

DELETE TABLE employee;

3. UPDATE: Used to update existing records in a table.

UPDATE employee
SET col1 = new_col
WHERE condition;

Syntax of Insert Command in MySQL

We can write the INSERT INTO statement in the following two ways.

Way #1

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

Way #2

INSERT INTO table_name (column1, column2, …….)
VALUES (value1, value2, ….);
  • INSERT INTO table_name is the command that adds a new row into a table named `table_name` in the MySQL database.
  • (column_1,column_2,…) are the column names in which a new record will be added.
  • VALUES (value_1,value_2,…) specifies the values to be added to the new row.

While adding new records, we need to be careful about the defined data types while creating the table structure.

  • String: All string values should be enclosed with single quotes.
  • Numeric: Numeric values should not be enclosed within single or double quotes.
  • Date: Those data types should be in a single quote with the ‘YYYY-MM-DD’ format.

Let’s consider there is an ’employee’ table consisting of the following attributes:

Emp_id Emp_name Phone Email Department Manager City
1001 Vinay 9876543219 [email protected] CSR Sudhir Bangalore
1002 Raaj 9764527848 [email protected] IT Stephen Hyderabad
1003 Sakti 9567382784 [email protected] Automotive Ved Bhubaneswar

If we add values for all the table columns, we don’t need to specify the column names in the query, but we have to ensure that our new record follows the column sequence as defined in the table.

INSERT INTO employee
VALUES (1004, 'Ravi', 9856478398, '[email protected]', 'marketing', 'shiv', 'kolkata');

If we don’t have all the values of columns and we will insert some of them, then we have to specify the column names in the query.

INSERT INTO employee (emp_id, emp_name, phone, email, manager)
VALUES (1005, 'sam', 9856478398, '[email protected]', 'shivankar');

Implementation of Insert Command in MySQL

Let’s consider the following lists of new records to add to the STUDENT database table.

Roll_no First_name Last_name Standard Phone Percentage City
1 Sandeep Kumar 10 9876456726 89.33 Cuttack
2 Shyam 9 76 Bhubaneswar
3 Sakti Naik 6463765776 76
4 Sid 8 9864876986 Kolkata
5 Vinay Kumar 10 92

We will insert those rows one by one into the database.

  • Let’s start with Sandeep. Here ‘Roll_no’, ‘Standard’, ‘Phone’, and ‘Percentage’ are numeric fields, so values in this column will be inserted without quotes.
INSERT INTO student
VALUES (1, 'Sandeep', 'Kumar', 10, 9876456726, 89.33, 'Cuttack');

Note: As we have values of all the columns of the student table, we don’t need to specify the column name while using the INSERT command. But we have to ensure that our value follows the order of columns in the table.

  • In the case of the Shyam record, we can see many values are missing. So here, we need to specify the column names we want to insert the values.
INSERT INTO student (Roll_no, First_name, Standard, Percentage, City)
VALUES (2, 'Shyam', 9, 76, 'Bhubaneswar');

Note: In this record, we don’t have the values of every column. That’s why we need to specify all the column names in which we want to insert our values, and in the order of those column names, we have to mention values as well.

INSERT INTO student (Roll_no, First_name, Last_name, Phone, Percentage)
VALUES (3, 'Sakti','naik', 6463765776, 76);
INSERT INTO student (Roll_no, First_name, Standard, Phone, City)
VALUES (4, 'Sid', 8, 9864876986, 'Kolkata');
INSERT INTO student (Roll_no, First_name, Last_name,standard, Percentage)
VALUES (5, 'Vinay','Kumar', 10, 92);

Changing the order of the columns & values will not affect the INSERT query, as the right value can’t be mapped to the right column. Thus problems like inserting a numeric value into a string or vice versa may arise.

Many fields like Last_name, Phone, and City values are missing in the above queries. So in such cases, MySQL will insert, by default, NULL values in those columns which we have skipped in the queries.

Inserting into a Table from Another Table

If there are two similar tables, and we want to insert data from table_1 to table_2 directly to avoid manual work, we can also use a different type of INSERT query. In such a case, we must use a SELECT command with the INSERT command. The SELECT command comes under DQL (Data Query Language), which is used for the retrieval/fetching of data. The select command can be used with many types of clauses also.

The basic syntax for inserting records to one table from another is as follows:

INSERT INTO table_1 SELECT * FROM table_2;

Example

Let us see the example given below:

1. Order Table

Order_no Order_department Customer_name Contact_no Quantity Place
8465637 Furniture Peter 8659876766 1000 Delhi
9473636 Ornaments Alex 9863769898 800 Mumbai

2. Order_archive Table

Order_no Order_department Customer_name Contact_no Quantity Place
 
 

Here we have two tables named Order and Order_archive. If we move all records from the Order table to Order_archive, then the following code can perform the task:

INSERT INTO Order_archive SELECT * FROM Order;

If we want to move some specific columns from the Order table to Order_archive, then:

INSERT INTO Order_archive (Order_no, Order_department, Contact_no, Quantity)
SELECT Order_no, Order_department, Contact_no, Quantity FROM Order;

Conclusion

Insert command is very useful as it comes to play from the time of table creation to every single time whenever we add more records to the existing table. We can summarize the following points from this article:

  • INSERT command is used to add new data to the table.
  • The date and string value should be in a single quote.
  • The numeric value should not be in quotes.
  • While inserting records in specific columns, the column name and value should be mapped in the same order.
  • This command can also insert records from one table to another.

Recommended Articles

We hope that this EDUCBA information on “Insert in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL String functions
  2. What is MySQL?
  3. Conditional Operators in MySQL
  4. NOT in MySQL
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more