在 MySQL 中如何替换特定表的特定字段中的所有 NULL 值?
mysqlmysqli database
要替换特定表的特定字段中的所有 NULL 值，请使用带有 IS NULL 属性的 UPDATE 命令。语法如下:
UPDATE yourTableName SET yourColumnName=”yourValue’ WHERE yourColumnName IS NULL;
为了理解上述语法，让我们创建一个表。创建表的查询如下:
mysql> create table Employee_Information_Table -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> Salary int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.59 sec)
使用 insert 命令在表中插入一些记录。插入记录的查询如下:
mysql> insert into Employee_Information_Table(Name,Salary) values('John',NULL);
Query OK, 1 row affected (0.13 sec)
mysql> insert into Employee_Information_Table(Name,Salary) values('Carol',NULL);
Query OK, 1 row affected (0.17 sec)
mysql> insert into Employee_Information_Table(Name,Salary) values('Bob',NULL);
Query OK, 1 row affected (0.10 sec)
mysql> insert into Employee_Information_Table(Name,Salary) values('David',NULL);
Query OK, 1 row affected (0.17 sec)
mysql> insert into Employee_Information_Table(Name,Salary) values('Robert',NULL);
Query OK, 1 row affected (0.12 sec)
mysql> insert into Employee_Information_Table(Name,Salary) values('Mike',NULL);
Query OK, 1 row affected (0.24 sec)
mysql> insert into Employee_Information_Table(Name,Salary) values('Sam',NULL);
Query OK, 1 row affected (0.17 sec)
使用 select 语句显示表中的所有记录。查询如下:
mysql> select *from Employee_Information_Table;
输出结果如下:
+----+--------+--------+ | Id | Name | Salary | +----+--------+--------+ | 1 | John | NULL | | 2 | Carol | NULL | | 3 | Bob | NULL | | 4 | David | NULL | | 5 | Robert | NULL | | 6 | Mike | NULL | | 7 | Sam | NULL | +----+--------+--------+ 7 rows in set (0.00 sec)
以下是将所有 NULL 值替换为特定表的特定字段的查询。查询如下:
mysql> update Employee_Information_Table -> set Salary=45500 where Salary IS NULL; Query OK, 7 rows affected (0.23 sec) Rows matched: 7 Changed: 7 Warnings: 0
现在再次检查表记录。所有 NULL 值都已更新为某个值。以下是使用 select 语句列出表中的所有记录的查询:
mysql> select *from Employee_Information_Table;
输出结果如下:
+----+--------+--------+ | Id | Name | Salary | +----+--------+--------+ | 1 | John | 45500 | | 2 | Carol | 45500 | | 3 | Bob | 45500 | | 4 | David | 45500 | | 5 | Robert | 45500 | | 6 | Mike | 45500 | | 7 | Sam | 45500 | +----+--------+--------+ 7 rows in set (0.00 sec)
