Home » Learn » MySQL Tutorial » MySQL INSERT INTO

MySQL INSERT INTO

INSERT INTO adds new rows to a table.

INSERT INTO students (name, city, marks, course_id)
VALUES ('Robin', 'Delhi', 85, 1);
MySQL says

Query OK, 1 row affected (0.01 sec)

  • After the table name we list the columns.
  • VALUES has the values in the same order.
  • We did not give id, so AUTO_INCREMENT made it 1.
  • Text and dates go in single quotes, numbers do not.

Insert many rows

INSERT INTO students (name, city, marks, course_id) VALUES
  ('Maria', 'Mumbai', 92, 2),
  ('John', 'Pune', 78, 1),
  ('Asha', 'Jaipur', 88, 3),
  ('Vikram', 'Delhi', 65, NULL),
  ('Neha', 'Mumbai', 45, 2);
MySQL says

Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

NULL means “no value”. Vikram has no course yet.

The data of the table

SELECT * FROM students;
Result
id name city marks course_id
1 Robin Delhi 85 1
2 Maria Mumbai 92 2
3 John Pune 78 1
4 Asha Jaipur 88 3
5 Vikram Delhi 65 NULL
6 Neha Mumbai 45 2

Add the courses too:

INSERT INTO courses (id, course_name) VALUES
  (1, 'Python'),
  (2, 'Java'),
  (3, 'Web Design'),
  (4, 'C Programming');

Useful things

  • Get the id of the last inserted row with SELECT LAST_INSERT_ID();.
  • If you give values for all columns, in the table order, you can skip the column list. But writing the list is safer.
  • To insert a row, or update it if the key exists, look at INSERT ... ON DUPLICATE KEY UPDATE.
Note: When values come from a visitor, never join them into the SQL text. Use prepared statements, see Python or PHP.

Summary

  • INSERT INTO table (columns) VALUES (values); adds a row.
  • Add many rows in one statement by separating the groups with commas.
  • Next: MySQL SELECT.

Share this post:

Share on LinkedIn
Congratulations! Chapter Finished. Learn more about the similar topics:
Exercises & Assignments
No Content Found.
Interview Questions & Answers
No Content Found.