8.MySQL?数据操作?DML

数据的DML操作：添加数据，修改数据，删除数据

添加数据

格式：?insert into?表名[(字段列表)] values(值列表...);

--标准添加（指定所有字段，给定所有的值）

mysql>?insert?into?stu(id,name,age,sex,classid)?values(1,'zhangsan',20,'m','lamp138');

Query OK,?1?row affected (0.13?sec)

mysql>

--指定部分字段添加值

mysql>?insert?into?stu(name,classid) value('lisi','lamp138');

Query OK,?1?row affected (0.11?sec)

--?不指定字段添加值

mysql>?insert?into?stu value(null,'wangwu',21,'w','lamp138');

Query OK,?1?row affected (0.22?sec)

--?批量添加值

mysql>?insert?into?stu?values

-> (null,'zhaoliu',25,'w','lamp94'),

-> (null,'uu01',26,'m','lamp94'),

-> (null,'uu02',28,'w','lamp92'),

-> (null,'qq02',24,'m','lamp92'),

-> (null,'uu03',32,'m','lamp138'),

-> (null,'qq03',23,'w','lamp94'),

-> (null,'aa',19,'m','lamp138');

Query OK,?7?rows affected (0.27?sec)

Records:?7?Duplicates:?0?Warnings:?0

修改数据

格式：update?表名?set?字段1=值1,字段2=值2,字段n=值n... where?条件

--?将id为11的age改为35，sex改为m值

mysql>?update?stu?set?age=35,sex='m'?where?id=11;

Query OK,?1?row affected (0.16?sec)

Rows matched:?1?Changed:?1?Warnings:?0

--?将id值为12和14的数据值sex改为m，classid改为lamp92

mysql>?update?stu?set?sex='m',classid='lamp92'?where?id=12?or?id=14?--等价于下面

mysql>?update?stu?set?sex='m',classid='lamp92'?where?id?in(12,14);

Query OK,?2?rows affected (0.09?sec)

Rows matched:?2?Changed:?2?Warnings:?0

删除数据

格式：delete from?表名?[where?条件]

--?删除stu表中id值为100的数据

mysql> delete from stu where id=100;

Query OK, 0 rows affected (0.00 sec)

--?删除stu表中id值为20到30的数据

mysql> delete from stu where id>=20 and id<=30;

Query OK, 0 rows affected (0.00 sec)

--?删除stu表中id值为20到30的数据（等级于上面写法）

mysql> delete from stu where id between 20 and 30;

Query OK, 0 rows affected (0.00 sec)

--?删除stu表中id值大于200的数据

mysql> delete from stu where id>200;

Query OK, 0 rows affected (0.00 sec)

cs