Example HTML page

MySQL Insert statement

We have seen how to use database and also how to create a table in that database. If you want to recall this you can refer in the following link.

Creating table

Now we can insert values into that players table. Let’s see how!!!. Before this you just make sure that schema of the table players as shown in below:

mysql> desc players;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(10) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sport   | varchar(10) | YES  |     | NULL    |       |
| country | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.09 sec)

To insert the values to the players table there is a command called INSERT INTO and the syntax for the same is as follows:

INSERT INTO table_name VALUES  ( value1, value2,…valueN );

Ex

mysql> insert into players values('Dhoni',37,'Cricket','India');
Query OK, 1 row affected (0.14 sec)

Similarly we can insert the remaining values. To display all the values from the table we use select command and the syntax for select is:

select * from table_name;

Now the players table would be like:

mysql> select * from players;
+---------+------+----------+-----------+
| name    | age  | sport    | country   |
+---------+------+----------+-----------+
| Dhoni   |   37 | Cricket  | India     |
| Nadal   |   32 | Tennis   | Spain     |
| L Messi |   31 | Football | Barcelona |
+---------+------+----------+-----------+
3 rows in set (0.02 sec)

If we specify NULL value in the ‘name’ field it displays null value instead the respective value.

mysql> insert into players values(null,37,'Cricket','India');
Query OK, 1 row affected (0.05 sec)

Now the table will be as follows:

mysql> select * from players;
+---------+------+----------+-----------+
| name    | age  | sport    | country   |
+---------+------+----------+-----------+
| Dhoni   |   37 | Cricket  | India     |
| Nadal   |   32 | Tennis   | Spain     |
| L Messi |   31 | Football | Barcelona |
| NULL    |   37 | Cricket  | India     |
+---------+------+----------+-----------+
4 rows in set (0.00 sec)

If you want to insert only two columns in the players table then use the following syntax;

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

Ex

mysql> insert into players(name,sport) values('Dhoni','Cricket');
Query OK, 1 row affected (0.06 sec)

Now using select command we can display the ‘players’ schema and it is as follows;

mysql> select * from players;
+---------+------+----------+-----------+
| name    | age  | sport    | country   |
+---------+------+----------+-----------+
| Dhoni   |   37 | Cricket  | India     |
| Nadal   |   32 | Tennis   | Spain     |
| L Messi |   31 | Football | Barcelona |
| Dhoni   | NULL | Cricket  | NULL      |
+---------+------+----------+-----------+
4 rows in set (0.00 sec)

Here we are not specifying any NULL values, so MySQL server takes NULL value as default.

If you want to insert multiple values at a time:

mysql> insert into players values('Mike',24,'Baseball','USA'),('Sharapova',31,'T
ennis','Russia'),('C Marin',25,'Badminton','Spain');
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

Now, display the players schema:

mysql> select * from players;
+-----------+------+-----------+-----------+
| name      | age  | sport     | country   |
+-----------+------+-----------+-----------+
| Dhoni     |   37 | Cricket   | India     |
| Nadal     |   32 | Tennis    | Spain     |
| L Messi   |   31 | Football  | Barcelona |
| Dhoni     | NULL | Cricket   | NULL      |
| Mike      |   24 | Baseball  | USA       |
| Sharapova |   31 | Tennis    | Russia    |
| C Marin   |   25 | Badminton | Spain     |
+-----------+------+-----------+-----------+
7 rows in set (0.00 sec)

Click here

To watch this tutorial post on YouTube Channel.

Example HTML page

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest