DML - MySQL (INSERT)

• DML means data manipulation language.
• The operations of DML includes INSERT, UPDATE, DELETE.

Insert a new record to table. (Specific Field)

Syntax

INSERT INTO <table_name> (Field1, Field2, FieldN) VALUES (Value1, Value2, ValueN);

Example

• Insert a new value to the (DATABASE: test,TABLE: student)
• The according field of the student table:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int          | YES  |     | NULL    |       |
| student_name | varchar(255) | YES  |     | NULL    |       |
| age          | int          | YES  |     | NULL    |       |
| gender       | varchar(1)   | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

Usage

mysql> INSERT INTO student (student_id, student_name, age, gender) VALUES (1, "Kai", 15, "M");

Output:

Results:

Query OK, 1 row affected (0.01 sec)

Validation:

mysql> SELECT * FROM student;
+------------+--------------+------+--------+
| student_id | student_name | age  | gender |
+------------+--------------+------+--------+
|          1 | Kai          |   15 | M      |
+------------+--------------+------+--------+
1 row in set (0.00 sec)

Insert a new record (All field)

Syntax

INSERT INTO VALUES (Value1, Value2, ValueN);

Usage

mysql> INSERT INTO student VALUES (2, "John", 15, "M");

Output:

Results:

Query OK, 1 row affected (0.01 sec)

Validation:

mysql> SELECT * FROM student;
+------------+--------------+------+--------+
| student_id | student_name | age  | gender |
+------------+--------------+------+--------+
|          1 | Kai          |   15 | M      |
|          2 | John         |   15 | M      |
+------------+--------------+------+--------+
2 rows in set (0.00 sec)

Insert Multiple record at once

Syntax

Specific Field

INSERT INTO <table_name> (Field1, Field2, FieldN) VALUES (Value1, Value2, ValueN), (Value1, Value2, ValueN);

All Field

INSERT INTO <table_name> VALUES (Value1, Value2, ValueN), (Value1, Value2, ValueN);

Usage

• Use All field as example.

mysql> INSERT INTO student VALUES (3, "Ben", 15, "M"),
    -> (4, "Jane", 15, "F");

Output:

Results:

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

Validation:

mysql> SELECT * FROM student;
+------------+--------------+------+--------+
| student_id | student_name | age  | gender |
+------------+--------------+------+--------+
|          1 | Kai          |   15 | M      |
|          2 | John         |   15 | M      |
|          3 | Ben          |   15 | M      |
|          4 | Jane         |   15 | F      |
+------------+--------------+------+--------+
4 rows in set (0.00 sec)

Conclusion

• All of the syntax od insert data is based on top of:

INSERT INTO <tableName> VALUE();

• For string and date type must with the quote ("").
• The data must follow the according type and size.