Mysql 简明教程
MySQL - NULL Values
The MySQL NULL Values
MySQL 使用术语“NULL”来表示数据库中不存在的数据值。这些值不同于空字符串或零，并且不占用数据库中的任何存储空间。它们用于指示数据字段中不存在的值或未知值。
MySQL uses the term "NULL" to represent a non-existent data value in the database. These values are different from an empty string or zero and do not occupy any storage space in the database. They are used to indicate the absence of a value or an unknown value in a data field.
一个值可能为 NULL 有一些常见的原因 −
There are some common reasons why a value may be NULL −
-
The value may not be provided during data entry.
-
The value is not yet known.
Creating a Table without NULL Values
为了创建一个没有 NULL 值的表，你可以在定义列时使用“NOT NULL”关键字。如果某个列被指定为“NOT NULL”，则当尝试将 NULL 值插入该特定列时，将发生错误。
To create a table without NULL values, you can use the "NOT NULL" keyword while defining the columns. If a column is specified as "NOT NULL," an error will occur when attempting to insert NULL values into that specific column.
Syntax
使用“NOT NULL”列创建表的语法如下 −
The basic syntax for creating a table with "NOT NULL" columns is as follows −
CREATE TABLE table_name (
column1 datatype NOT NULL,
column2 datatype NOT NULL,
...
columnN datatype
);其中，“NOT NULL”表示一列必须始终包含已定义数据类型的特定值。标记为“NOT NULL”的列不能接受 NULL 值。另一方面，可以将 NULL 值插入到没有“NOT NULL”约束的列中。
Where, "NOT NULL" indicates that a column must always contain a specific value of the defined data type. Columns marked as "NOT NULL" cannot accept NULL values. On the other hand, you can insert NULL values into the columns without the "NOT NULL" constraint.
Example
让我们使用以下查询创建一个名为“CUSTOMERS”的表 −
Let us create a table named "CUSTOMERS" using the following query −
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，让我们将一些记录插入到上面创建的表中 −
Now, let us insert some records into the above-created table −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', NULL),
(7, 'Muffy', 24, 'Indore', NULL);获得的 CUSTOMERS 表如下：−
The CUSTOMERS table obtained is as follows −
现在，要检索不为 NULL 的记录，可以使用“IS NOT NULL”运算符，如下所示−
Now, to retrieve records that are not NULL, you can use the "IS NOT NULL" operator as shown below−
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;以下是上面代码的输出： -
Following is the output of the above code −
要检索为 NULL 的记录，可以使用“IS NULL”运算符，如下所示 −
To retrieve records that are NULL, you can use the "IS NULL" operator as shown below −
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;输出结果如下：
The output produced is as follows −
Updating NULL Values in a Table
要在表中更新 NULL 值，可以使用带有“IS NULL”运算符的“UPDATE”语句。此语句将筛选包含 NULL 值的行，并使用“SET”关键字设置新值。
To update NULL values in a table, you can use the "UPDATE" statement with the "IS NULL" operator. This filter the rows containing NULL values and set new values using the "SET" keyword.
Example
此处，我们正在更新 CUSTOMERS 表中 SALARY 列的 NULL 值，如下所示 −
Here, we are updating the NULL values in the SALARY column of the CUSTOMERS table as shown below −
UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL;Deleting Records with NULL Values
要从表中删除具有 NULL 值的记录，可以使用“DELETE FROM”语句以及“WHERE”子句中的“IS NULL”运算符。
To delete records with NULL values from a table, you can use the "DELETE FROM" statement with the "IS NULL" operator in the "WHERE" clause.
Example
现在，我们正在删除 SALARY 列中具有 NULL 值的记录，如下所示 −
Now, we are deleting records with NULL values in the SALARY column as shown below −
DELETE FROM CUSTOMERS WHERE SALARY IS NULL;