mysqlinsertinto语法_MySQL的INSERT语法⼩结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.0
6 sec)
当向表中插⼊字符串值(以及⼀些数据值时)。必须使⽤引号，否则MySQL认为它们是字段名：
mysql> insert into addressbook(fname,lname,phone,fax,email)
-> values('Rob',Rabbit,'674 1536','382 8364','rob@some.domain');
ERROR 1054 (42S22): Unknown column 'Rabbit' in 'field list'
包含引⽤标志的值需要在前⾯加上反斜线，不过数值不需要加引号：
mysql> insert into addressbook(fname,lname,phone,fax,email)
-> values('France','D\'Souza','123 4567','000 7574','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)
在⼀个单独的Insert语句中使⽤多个VALUES()⼦句可以插⼊多条记录：
mysql> insert stocks (symbol,price,quantity) values('ABCD',100,4500),
-> ('HYDH',2000,29),('UGTS',25,67);
也可以不使⽤INSERT。。。VALUES格式，⽽是使⽤类似的UPDATE语句，它使⽤SET语句分别为每⼀列设置值。故可以不进⾏下⾯的操作：
mysql> insert into stocks(symbol,price,quantity) values('HYDH',2000,29);
⽽是这样做：
mysql> insert into stocks set symbol='HYDH',price=2000,quantity=29;
使⽤DEFAULT值
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 |
+----------+----------+--------------+----------------+
insert语句字段顺序| Apache | UNIX | 15 | red |
| Sendmail | UNIX | 15 | green |
+----------+----------+--------------+----------------+
2 rows in set (0.00 sec)
MySQL4.0.3以及更⾼版本⽀持DEFAULT关键字，MySQL使⽤这个关键字为记录设置列的默认值。
mysql> insert into forums(name,category,postsperpage,highlightcolor)
-> values('MySQL',default,default,default);
Query OK, 1 row affected (0.06 sec)
使⽤AUTOINCREMENT字段
当使⽤AUTOINCREMENT列创建序列时，在INSERT语句中不使⽤字段名将导致MySQL⾃动产⽣序列的下⼀个序号。这个序号作为表的主键。
mysql> insert into users(uname,upass) values('jim','secret');
Query OK, 1 row affected (0.05 sec)
mysql> insert into users(uname,upass) values('sarah','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)
使⽤NULL值
我们可以在INSERT语句中使⽤NULL关键字来向⼀个字段(只要没有标记为NOT NULL)输⼊NULL值。下⾯的例⼦通过向包含NULL值的email和fax字段的addressbook表添加记录进⾏说明：
mysql> insert into addressbook(fname,lname,phone,fax,email)
-> values('Polly','Parrot','239 1828',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 | NULL |
+-------+--------+----------+------+-------+
1 row in set (0.0
2 sec)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论