7.DML操作-数据的增删改 - 图17.DML操作-数据的增删改 - 图2

    MySQL 数据操作 DML

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

    添加数据

    格式： insert into 表名[(字段列表)] values(值列表…);
    —标准添加（指定所有字段，给定所有的值）
    mysql> insert into stu(id,name,age,sex,classid)
    values(null , ‘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 条件

    7.DML操作-数据的增删改 - 图3

    — 将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 —等价于下⾯ id in(12 ,14 );
    mysql> update stu set sex= ‘m’ ,classid= ‘lamp92’ where
    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)