MySQL查询将特定记录的值减小到零？
使用SET减小值，在WHERE处将特定记录的条件设置为0。让我们首先创建一个表-
mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Number int );
使用插入命令＆minus在表中插入一些记录
mysql> insert into DemoTable(Number) values(10); mysql> insert into DemoTable(Number) values(20); mysql> insert into DemoTable(Number) values(1); mysql> insert into DemoTable(Number) values(0); mysql> insert into DemoTable(Number) values(-1);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+----+--------+ | Id | Number | +----+--------+ | 1 | 10 | | 2 | 20 | | 3 | 1 | | 4 | 0 | | 5 | -1 | +----+--------+ 5 rows in set (0.00 sec)
以下是将值减小到0的查询-
mysql> update DemoTable set Number = Number-1 where Id = 3 AND Number > 0; Rows matched: 1 Changed: 1 Warnings: 0
让我们再次显示表记录-
mysql> select *from DemoTable;
这将产生以下输出。现在，Id3的记录为0，因为我们将其减1-
+----+--------+ | Id | Number | +----+--------+ | 1 | 10 | | 2 | 20 | | 3 | 0 | | 4 | 0 | | 5 | -1 | +----+--------+ 5 rows in set (0.00 sec)