Databases & SQL · Class 12 · Inserting & Changing Data

MySQLDML⏱️ 10 min read

Inserting Rows

The table exists and it is empty. Everything so far has been structure; this is the first command that puts actual data in. It is also where three small rules — about order, quotes and dates — account for most of the errors beginners meet.

1The syntax

insert into <table name> values (<v1>, <v2>, …, <vN>);

MySQL command line client

mysql> insert into students values (2035, "Ravi", 12, 83.75, "2008-08-13", "Gangtok");

Query OK, 1 row affected (0.04 sec)

1 row affected — unlike create table, this one really does affect a row, because a row is exactly what it made.

The order of the values is the order of the columns

There are no column names in that query, so MySQL matches the values to the columns by position: first value into the first column, and so on. The table was built as (id, name, grade, percent, dob, city), so the values must arrive in that order. Swap two of them and you either get an error or, worse, a row that is quietly wrong.

2Many rows in one query

Rather than repeating the command, separate the bracketed groups with commas:

MySQL command line client

mysql> insert into students values

-> (1099, "Veena", 10, 90.50, "2010-11-20", "Singtam"),

-> (3033, "Tom", 8, 64.25, "2013-02-25", "Kolkata");

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0

The extra line is worth reading. Records: 2 is how many rows you offered, Duplicates: 0 how many clashed with an existing key, and Warnings: 0 how many were accepted but altered on the way in. All three at their expected values means the insert did exactly what you asked.

3Naming the columns

Sometimes you only have some of the data. List the columns you are supplying, in brackets, before values:

MySQL command line client

mysql> insert into students (id, name, grade) values (4001, "Asha", 11);

Query OK, 1 row affected (0.04 sec)

mysql> select * from students;

+------+-------+-------+---------+------------+---------+

+------+-------+-------+---------+------------+---------+

| 1099 | Veena | 10 | 90.5 | 2010-11-20 | Singtam |

| 2035 | Ravi | 12 | 83.75 | 2008-08-13 | Gangtok |

| 3033 | Tom | 8 | 64.25 | 2013-02-25 | Kolkata |

+------+-------+-------+---------+------------+---------+

4 rows in set (0.00 sec)

Asha's row exists, and the three columns nobody supplied were filled with NULL — the missing-value marker from the concepts chapter, doing its job. Had one of those columns carried a default, the default would have been used instead.

Naming the columns is the safer habit

With the names written out, the order no longer has to match the table — (name, id, grade) works just as well, as long as the values line up with the names you gave. It is also self-documenting: a reader can see what each value is without going to look at the table.

4Quotes, and the date format

Quote these

char, varchar and date values.

"Ravi" "2008-08-13"

Do not quote these

int and float values.

2035 83.75

And the date format is fixed — year first, four digits, hyphens, in quotes. Writing it the way you would by hand is refused:

MySQL command line client

mysql> insert into students values (9001,'Bad',10,80,'25-07-2050','Singtam');

ERROR 1292 (22007): Incorrect date value: '25-07-2050' for column 'dob' at row 1

5Three refusals worth recognising

The wrong number of values. The table has six columns and only two were given:

MySQL command line client

mysql> insert into students values (5001,'X');

ERROR 1136 (21S01): Column count doesn't match value count at row 1

The same error appears if you name three columns and supply four values. MySQL is counting the two lists and finding they disagree — so either supply every column, or name the ones you are supplying.

A duplicate primary key. Student 1099 is already in the table:

MySQL command line client

mysql> insert into students values (1099,'Duplicate',10,80,'2010-01-01','Singtam');

ERROR 1062 (23000): Duplicate entry '1099' for key 'students.PRIMARY'

A NULL primary key. Every row must be identifiable:

MySQL command line client

mysql> insert into students values (NULL,'NoId',10,80,'2010-01-01','Singtam');

ERROR 1048 (23000): Column 'id' cannot be null

A refused insert changes nothing

In each case the row was not partly added. The whole statement was rejected, the table is exactly as it was, and you can fix the query and run it again. There is nothing to clean up.

6Recap

insert into t values (…);

Values in the table's column order. One row.

…values (…), (…);

Several rows in one query, groups separated by commas.

insert into t (cols) values (…);

Supply only some columns; the rest get their default or NULL.

Quotes

Text and dates yes, numbers no. Dates are 'YYYY-MM-DD'.

Quick Check

A table has 6 columns. You run insert into students values (5001,'X'); What happens?

Quick Check

How must the date 20 November 2010 be written?

Quick Check

insert into students (id, name, grade) values (4001, 'Asha', 11); — what goes into the percent column?

Quick Check

What does 'Records: 2 Duplicates: 0 Warnings: 0' tell you?

← Previous

🗑️ Dropping a Table or Database

Updating Rows 🔄