How can I insert the values in columns without specifying the names of the column in MySQL INSERT INTO statement?

MySQLMySQLi Database

For inserting the values in the column without specifying the names of the columns in INSERT INTO statement, we must give the number of values that matches the number of columns in the table along with taking care about the data type of that column too.

Example

In the example below we have inserted the values without specifying the name of the column.

mysql> Insert into student values(100,'Gaurav','Ph.D');
Query OK, 1 row affected (0.08 sec)

mysql> Select * from student;
+--------+--------+--------+
| RollNO | Name   | Class  |
+--------+--------+--------+
| 100    | Gaurav | Ph.D   |
+--------+--------+--------+

1 row in set (0.00 sec)

mysql> Insert into student values(200,'Rahul','Ph.D'),(300,'Aarav','B.tech');
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> Select * from student;

+--------+--------+--------+
| RollNO | Name   | Class  |
+--------+--------+--------+
| 100    | Gaurav | Ph.D   |
| 200    | Rahul  | Ph.D   |
| 300    | Aarav  | B.tech |
+--------+--------+--------+

3 rows in set (0.00 sec)

MySQL throws error if we will not take care about the total number of columns and their data types as follows −

mysql> Insert into student values(400,'Raman',M.Tech);
ERROR 1054 (42S22): Unknown column 'M.Tech' in 'field list'

mysql> Insert into student values(400,'Raman');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
raja
Published on 19-Feb-2018 12:49:28
Advertisements