MySQL insert syntax Summary

Mysql> Create Table addressbook (fname varchar (255) null, lname varchar (255) null,

Phone varchar (255) null, fax varchar (255) null, email varchar (255) null );

Query OK, 0 rows affected (0.11 Sec)

 

Mysql> DESC addressbook;

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

| FIELD | type | null | key | default | extra |

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

| Fname | varchar (255) | Yes | null |

| Lname | varchar (255) | Yes | null |

| Phone | varchar (255) | Yes | null |

| Fax | varchar (255) | Yes | null |

| Email | varchar (255) | Yes | null |

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

5 rows in SET (0.06 Sec)

 

When inserting string values (and some data values) into the table ). Quotation marks are required. Otherwise, MySQL regards them as field names:

Mysql> insert into addressbook (fname, lname, phone, fax, email)

-> Values ('rob', Rabbit, '1970 1234568', 'rob @ some. Domain ');

Error 1054 (42s22): Unknown column 'rabbit 'in 'field list'

 

A backslash must be added before the value that contains the reference mark, but no quotation marks are required for the value:

Mysql> insert into addressbook (fname, lname, phone, fax, email)

-> Values ('France ', 'd \ 'souza', '1970 123', '000 123', 'fdz @ some. Domain ');

Query OK, 1 row affected (0.05 Sec)

 

Mysql> select * From addressbook;

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

| Fname | lname | phone | Fax | email |

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

| France | D' Souza | 123 4567 | 000 7574 | fdz@some.domain |

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

1 row in SET (0.00 Sec)

 

Using multiple values () clauses in a separate insert statement can insert multiple records:

Mysql> insert stocks (symbol, price, quantity) values ('abcd ),

-> ('Hydh ',), ('ugts );

 

You can also choose not to use insert... The values format uses a similar update statement, which uses the set statement to set values for each column. Therefore, do not perform the following operations:

Mysql> insert into stocks (symbol, price, quantity) values ('hydh );

Instead, do the following:

Mysql> insert into stocks set symbol = 'hydh ', price = 2000, quantity = 29;

 

 

Use default value

Mysql> Create Table forums (name varchar (150) not null, category varchar (50) defau

Lt 'unix 'not null, postsperpage smallint default 15 not null, highlightcolor varc

Har (10) default 'red' not null );

Query OK, 0 rows affected (0.11 Sec)

 

Mysql> insert into forums (name) values ('apache ');

Query OK, 1 row affected (0.05 Sec)

 

Mysql> insert into forums (name, highlightcolor) values ('sendmail', 'green ');

Query OK, 1 row affected (0.06 Sec)

 

Mysql> select * from forums;

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

| Name | category | postsperpage | highlightcolor |

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

| Apache | UNIX | 15 | red |

| Sendmail | UNIX | 15 | green |

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

2 rows in SET (0.00 Sec)

 

Mysql4.0.3And later versions support the default keyword. MySQL uses this keyword to set the column default value for the record.

Mysql> insert into forums (name, category, postsperpage, highlightcolor)

-> Values ('mysql', default );

Query OK, 1 row affected (0.06 Sec)

 

Use the autoincrement Field

When the autoincrement column is used to create a sequence, MySQL automatically generates the next sequence number if no field name is used in the insert statement. This sequence number is used as the primary key of the table.

Mysql> insert into users (uname, upass) values ('Jim ', 'secret ');

Query OK, 1 row affected (0.05 Sec)

 

Mysql> insert into users (uname, upass) values ('sara', 'opense ');

Query OK, 1 row affected (0.06 Sec)

 

Mysql> insert into users (uname, upass) values ('Tim', 'whiteboard ');

Query OK, 1 row affected (0.05 Sec)

 

Mysql> select * from users;

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

| Uid | uname | upass |

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

| 1 | Jim | secret |

| 2 | Sarah | opense |

| 3 | tim | whiteboard |

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

3 rows in SET (0.00 Sec)

Use null

We can use the null keyword in the insert statement to input a null value to a field (as long as it is not null. The following example describes how to add a record to the addressbook table that contains null values by email and fax fields:

Mysql> insert into addressbook (fname, lname, phone, fax, email)

-> Values ('polil', 'Parrot', '1970, 100', null, null );

Query OK, 1 row affected (0.05 Sec)

 

Mysql> select * From addressbook where email is null or fax is null;

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

| Fname | lname | phone | Fax | email |

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

| Polly | parrot | 239 1828 | null |

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

1 row in SET (0.02 Sec)