插入数据
insert into table_name (column_list) values(value_list)
插入一条记录
insert into person (id,name,age,info) values(1,'Green',21,'Lawyer');
列表名可以省略
例如:
insert into persion values(2,'Marry',24,'Musiacan');
可以插入指定列:
insert into person(name,age,info) values('Willam',20,'sports man');
同时插入多条记录
insert into persion(name,age,info) values('Evans',27,'secretary'),('Dale',22,'cok'),('Edision',28,'singer');
同时插入多条记录也可以省略字段名
将查询结果插入表中
insert into table_name (column_list) select (column_list2) from table_name2 where (condition)
需要插入的列表和查询表的类型要相同,对应的字段数据类型也要相同。
例如:
创建persion表
mysql> create table persion(
id INT unsigned not null auto_increment,
name varchar(11) not null,
position varchar(20),PRIMA
RY KEY(id));
插入数值
mysql> insert into persion(id,name,position)
values(2,'lihairu','student'),
(3,'liuboqin','teacher'),
(4,'wuguang','stude
t');
创建和表类型相同的表persion_old,并插入数值
mysql> insert into persion_old values
(8,'liuxun','student'),
(9,'wangyi','teacher'),
(10,'qixuan','moniter');
查询表persion_old的数据并插入到persion
insert into persion(id,name,position) select id ,name,position from persion_old;
结果就是将persion_old的三条记录插入到了表persion
mysql> select * from persion;
+----+-----------+------------+
| id | name | position |
+----+-----------+------------+
| 1 | 18 | musication |
| 2 | lihairu | student |
| 3 | liuboqin | teacher |
| 4 | wuguang | student |
| 6 | zhangqian | student |
| 7 | wangchong | student |
| 8 | liuxun | student |
| 9 | wangyi | teacher |
| 10 | qixuan | moniter |
+----+-----------+------------+
更新数据
update table_name set column_name1=value1,column_name2=value2,......,columnn=valuen where (condition);
例如,为persion添加age字段,并更新数据为19-24,修改id=1的name值为‘wanghai’.
用alter语句添加字段
alter table persion add age INT(4) not null after id;
更新年龄值为19-24
update persion set age=(floor(rand()*6)+19);
查看效果:
mysql> select * from persion;
+----+-----+-----------+------------+
| id | age | name | position |
+----+-----+-----------+------------+
| 1 | 19 | 18 | musication |
| 2 | 22 | lihairu | student |
| 3 | 23 | liuboqin | teacher |
| 4 | 19 | wuguang | student |
| 6 | 19 | zhangqian | student |
| 7 | 20 | wangchong | student |
| 8 | 19 | liuxun | student |
| 9 | 22 | wangyi | teacher |
| 10 | 21 | qixuan | moniter |
+----+-----+-----------+------------+
看到id=1的不协调的name字段为18,改为’wanghai’
update persion set name='wanghai' where id=1;
查看结果:
mysql> select name from persion where id=1;
+---------+
| name |
+---------+
| wanghai |
+---------+
如果不用where条件语句,数据库将默认为将该字段所有的值改为要修改的值
删除数据
delete from table_name [where <condition>];
例如:
delete from persion where id=10;
也可以用范围语句
delete from persion where age between 19 and 21;
在不添加条件时,表示清空表中内容。
删除表中所有数据还可以用truncate
truncate table_name
truncate 的原理并不是清除表的数据,而是直接删除表,然后重新创建新的。