MySQL Tutorial/Insert Update Delete/Insert

Do calculation in the insert statement referencing the column name

mysql>
mysql> CREATE TABLE myTable(
    ->    ID TINYINT UNSIGNED NOT NULL DEFAULT 1,
    ->    Copyright YEAR,
    ->    Expire YEAR,
    ->    Name VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Copyright, Expire, Name)VALUES (1,1994,Copyright + Copyright,"A");
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
mysql> select * from myTable;
+----+-----------+--------+------+
| ID | Copyright | Expire | Name |
+----+-----------+--------+------+
|  1 |      1994 |   0000 | A    |
+----+-----------+--------+------+
1 row in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

INSERTing data

The basic syntax for the INSERT statement is as follows.

INSERT INTO <table_name>
VALUES (
value1,
value2,
etc......
);

Insert more than one rows in a single insert statement

mysql>
mysql>
mysql> CREATE TABLE employee (
    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->      name CHAR(30) NOT NULL,
    ->      PRIMARY KEY (id)
    ->  );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO employee (name) VALUES ("A"),
    ->                                    ("B"),
    ->                                    ("C"),
    ->                                    ("D"),
    ->                                    ("E"),
    ->                                    ("F");
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql>
mysql> SELECT * FROM employee;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | D    |
|  5 | E    |
|  6 | F    |
+----+------+
6 rows in set (0.00 sec)
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

Insert NULL to a NOT NULL column

mysql>
mysql> CREATE TABLE myTable(
    ->    ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Name)VALUES (NULL, "Ain\"t ");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from myTable;
+----+--------+
| ID | Name   |
+----+--------+
|  1 | Ain"t  |
+----+--------+
1 row in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

Insert quotation marks

mysql>
mysql> CREATE TABLE myTable(
    ->    ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Name)VALUES (1, "Ain\"t ");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from myTable;
+----+--------+
| ID | Name   |
+----+--------+
|  1 | Ain"t  |
+----+--------+
1 row in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)

Insert two rows with one insert statement

mysql>
mysql>
mysql> CREATE TABLE myTable(
    ->    ID TINYINT UNSIGNED NOT NULL DEFAULT 1,
    ->    Name VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Name)VALUES (1,"A"),(2,"B");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql>
mysql> select * from myTable;
+----+------+
| ID | Name |
+----+------+
|  1 | A    |
|  2 | B    |
+----+------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.02 sec)
mysql>

Insert with set statement

mysql>
mysql> CREATE TABLE myTable(
    ->    ID TINYINT UNSIGNED NOT NULL DEFAULT 1,
    ->    Name VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT DELAYED INTO myTable SET ID=1, Name="C";
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from myTable;
Empty set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.01 sec)

Use the DEFAULT in the insert statement

mysql>
mysql> CREATE TABLE myTable(
    ->    ID TINYINT UNSIGNED NOT NULL DEFAULT 1,
    ->    Name VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Name)VALUES (1,"A"),(DEFAULT,"B");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql>
mysql> select * from myTable;
+----+------+
| ID | Name |
+----+------+
|  1 | A    |
|  1 | B    |
+----+------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.01 sec)
mysql>

Using encode function with insert statement

mysql>
mysql>
mysql> CREATE TABLE UserAccounts
    -> (
    ->    UserID SMALLINT NOT NULL PRIMARY KEY,
    ->    Password VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> INSERT INTO UserAccounts VALUES (101, ENCODE("pw101", "key101"));
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from UserAccounts;
+--------+----------+
| UserID | Password |
+--------+----------+
|    101 | ?*       |
+--------+----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop table UserAccounts;
Query OK, 0 rows affected (0.00 sec)
mysql>