MySQL Insert Command


To insert data into a MySQL table, you would need to use the SQL INSERT INTO command. You can insert data into the MySQL table by using the mysql> prompt or by using any script like PHP.
Syntax
Here is a generic SQL syntax of INSERT INTO command to insert data into the MySQL table −

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

To insert string data types, it is required to keep all the values into double or single quotes. For example “value”. The same is true if you want to insert date type values.

Inserting Data from the Command Prompt

To insert data from the command prompt, we will use SQL INSERT INTO command to insert data into MySQL table student.
Example

root@host# mysql -u root -p password;
Enter password:*******
mysql> use SCHOOL;
Database changed

mysql> INSERT INTO student 
   ->(admno, name, father_name,address)
   ->VALUES
   ->(101, "rakesh kumar",'Jagdisha Prasad','b-100 surya nagar, Ghaziabad');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student 
              -> (admno, name, father_name,address) 
              ->VALUES ->(102, "mannat bhatia','Mr Anurag Bhatia','C-2100 surya nagar, Ghaziabad');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student 
         ->(admno, name, father_name,address) 
         ->VALUES ->(103, "Pushkar Rustagi",'Devendra Rustagi','E-40 Brij vihar, Ghaziabad');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student 
       ->VALUES ->(104, "Arushi Aggarwal",'Surendra Aggarwal','B245/-2 Brij vihar, Ghaziabad');
mysql>

NOTE − Please note that all the arrow signs (->) are not a part of the SQL command. They are indicating a new line and they are created automatically by the MySQL prompt while pressing the enter key without giving a semicolon at the end of each line of the command.

Also, note that the last example does not use attributes to insert values. It is only recommended when you are going to feed all the columns using MySQL insert Command.

Insert multiple rows using Insert command

multiple rows can be inserted in a table using single insert command. The syntax of for inserting multiple rows in a using single insert command is as follows

mysql>insert into  
        ( column-name, column-name......) 
        values 
         ( value1, value2....), 
         ( value3, value4....), 
         (value5, value 6) ;

Example command

insert into student (admno,name,father_name,address) 
       values 
        ( 104,'rakesh','jagdish','b-100 surya nagar ghaziabad'),
        (105,'atul','suresh','c-100 brij vihar');

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Python Program to insert Values using Insert Command
Example
Please try the following code at your system

import mysql.connector
conn = mysql.connector.connect(host='localhost',user='root',password='',database="school")
cursor = conn.cursor()
cursor.execute("insert into student values (106,'Nikunj Jain','Sandeep jain','C-100 surya nagar ghaziabad');")
conn.close()
print('Record inserted successfully')

The output of the above program is
Record inserted successfully
Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.