24 insert 语句插入数据
24 insert 语句插入数据
语法格式：
insert into 表名（字段名1,字段名2,字段名3,...） values(值1,值2,值3,...)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban','1996-01-22');
insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban','1996-01-22',2);
select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1996-01-22 |
| 2 | lisi | 1 | gaosan1ban | 1996-01-22 |
+------+----------+------+------------+------------+
insert into t_student(name) values('wangwu');// 除name字段以外，剩下的所有字段自动插入null。
select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1996-01-22 |
| 2 | lisi | 1 | gaosan1ban | 1996-01-22 |
| NULL | wangwu | NULL | NULL | NULL |
+------+----------+------+------------+------------+
drop table if exists t_student;// 当这个表存在的话删除。
create table t_student(
no bigint,
name varchar(255),
sex char(1) default 1,
classno varchar(255),
birth char(10)
);
insert into t_student(name) values('zhangsan');// 默认sex是1
+------+----------+------+---------+-------+
| no | name | sex | classno | birth |
+------+----------+------+---------+-------+
| NULL | zhangsan | 1 | NULL | NULL |
+------+----------+------+---------+-------+
需要注意的地方：当一条insert语句执行成功之后，表格当中那必然会多一行记录。
即使多的这一行记录当中某些字段是null，后期也没有办法在执行insert语句插入数据了，只能使用update进行更新。
// 字段可以省略不写，但是后面的value对数量和顺序都有要求。
insert into t_student values(1,'lisi','0','gaosan2ban','1986-11-12');
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 1 | lisi | 0 | gaosan2ban | 1986-11-12 |
+------+----------+------+------------+------------+
// 一次插入多行数据
insert into
t_student(no,name,sex,classno,birth)
values
(2,'wangwu','1','gaosan3ban','1996-12-31'),
(3,'zhaoliu','0','gaosan1ban','1998-02-06');
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 1 | lisi | 0 | gaosan2ban | 1986-11-12 |
| 2 | wangwu | 1 | gaosan3ban | 1996-12-31 |
| 3 | zhaoliu | 0 | gaosan1ban | 1998-02-06 |
+------+----------+------+------------+------------+
