改变MySQL数据表中的数据信息
改变数据表中信息
1. 修改数据信息:
1.1. 整改:
语法格式:update 表名 set 字段名1=修改后的字段信息,字段名2=修改后的字段信息,字段名3=修改后的字段信息 where 具体到要修改的行
MariaDB [dazhu]> update chengjibiao set name="六六",chengji=66 where id=4;##只要修改表中信息,那么其后就必须跟上where来具体到哪一行! Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0
1.2. 修改某项(可运算):
MariaDB [dazhu]> update chengjibiao set chengji=chengji+20 where id=4; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
1.3、同时对两个表进行更新
基本语法:
update 表1,表2 set 字段1=值1,字段2=值2,字段n=值n where 条件
实例:
MariaDB [dazhu]> update chengjibiao c,xinzibiao x set x.xinzi=c.chengji where c.id=5&&x.id=5; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: ##也可以直接修改多项且直接赋值! MariaDB [dazhu]> select * from chengjibiao ; +------+--------+---------+ | id | name | chengji | +------+--------+---------+ | 1 | 大傻 | 90 | | 2 | 小二 | 95 | | 4 | 六六 | 86 | | 5 | 赵五 | 100 | | 6 | 宇宇 | 73 | | 3 | 明明 | 80 | +------+--------+---------+ 6 rows in set (0.00 sec) MariaDB [dazhu]> select * from xinzibiao; +------+--------+-------+--------+ | id | name | xinzi | gongsi | +------+--------+-------+--------+ | 1 | 大傻 | 18000 | dsd | | 2 | 小二 | 16000 | dsd | | 4 | 六六 | 16000 | ll | | 5 | 赵五 | 100 | zq | +------+--------+-------+--------+ 4 rows in set (0.00 sec)
2. 删除数据信息
语法格式:delete from 表名 where 删除条件(具体到哪一行);
| 6 | 宇宇 | 73 | | 3 | 明明 | 80 | +------+--------+---------+ 6 rows in set (0.00 sec) MariaDB [dazhu]> delete from chengjibiao where id=3; Query OK, 1 row affected (0.02 sec) MariaDB [dazhu]> select * from chengjibiao; +------+--------+---------+ | id | name | chengji | +------+--------+---------+ | 1 | 大傻 | 90 | | 2 | 小二 | 95 | | 4 | 六六 | 86 | | 5 | 赵五 | 100 | | 6 | 宇宇 | 73 | +------+--------+---------+????? 5 rows in set (0.00 sec)
3. 清空表信息;
语法格式:truncate table 表名;
MariaDB [dazhu]> truncate table xueyuan; Query OK, 0 rows affected (0.02 sec) MariaDB [dazhu]> select * from xueyuan; Empty set (0.00 sec)
4、切记:
删除数据其后一定要根生where具体删除行,否则会清空整个表格
删除或者清空数据之前一定要数据备份备份备份!