在MySQL表中填充空列并设置值
为此，可以使用ISNULL属性。让我们首先创建一个表-
mysql> create table DemoTable ( ProductPrice int, ProductQuantity int, TotalAmount int );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable(ProductPrice,ProductQuantity) values(100,2); mysql> insert into DemoTable(ProductPrice,ProductQuantity) values(500,4); mysql> insert into DemoTable(ProductPrice,ProductQuantity) values(1000,10);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+--------------+-----------------+-------------+ | ProductPrice | ProductQuantity | TotalAmount | +--------------+-----------------+-------------+ | 100 | 2 | NULL | | 500 | 4 | NULL | | 1000 | 10 | NULL | +--------------+-----------------+-------------+ 3 rows in set (0.00 sec)
这是填充NULL列的查询-
mysql> update DemoTable set TotalAmount=(ProductPrice*ProductQuantity) where TotalAmount IS NULL; Rows matched: 3 Changed: 3 Warnings: 0
让我们再次检查表记录-
mysql> select *from DemoTable;
这将产生以下输出-
+--------------+-----------------+-------------+ | ProductPrice | ProductQuantity | TotalAmount | +--------------+-----------------+-------------+ | 100 | 2 | 200 | | 500 | 4 | 2000 | | 1000 | 10 | 10000 | +--------------+-----------------+-------------+ 3 rows in set (0.00 sec)