空值声明
我创建了一个名为 table1 的表,它有 4 列,名为“名称”、“ID”、“描述”和“日期”。
我已经创建了它们,例如 Name varchar(50) null, ID int null,Description varchar(50) null, Date datetime null
我已将一条记录插入到具有 ID 和 Description 值的 table1 中。现在我的 table1 看起来像这样:
Name ID Description Date
Null 1 First Null
其中一个要求我修改表,使“名称”和“日期”列应具有 Null 值,而不是“Text Null”。我不知道我的意思是什么有什么区别,
谁能解释一下这些选择语句之间的区别:
SELECT * FROM TABLE1 WHERE NAME 为 NULL
SELECT * FROM TABLE1 WHERE NAME = 'NULL'
从表 1 中选择 * WHERE NAME = ' '
谁能解释一下吗?
I have created a table called table1 and it has 4 columns named Name,ID,Description and Date.
I have created them like Name varchar(50) null, ID int null,Description varchar(50) null, Date datetime null
I have inserted a record into the table1 having ID and Description values. So Now my table1 looks like this:
Name ID Description Date
Null 1 First Null
One of them asked me to modify the table such a way that The columns Name and Date should have Null values instead of Text Null. I don't know what is the difference between those
I mean can anyone explain me the difference between these select statements:
SELECT * FROM TABLE1
WHERE NAME IS NULL
SELECT * FROM TABLE1
WHERE NAME = 'NULL'
SELECT * FROM TABLE1
WHERE NAME = ' '
Can anyone explain me?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
在 CREATE TABLE 中,这里的 NULL 或 NOT NULL
varchar(50) null
是一个约束,用于确定是否允许 NULL。 NOT NULL 表示否。当您插入数据时,您运行了哪条语句?
或
当您运行 SELECT * FROM TABLE1 时,客户端工具将显示NULL。
要测试是否确实有 NULL 或字符串 NULL,请运行此命令
For the SELECTs
注意:空符号/值不是空字符串。它没有价值,也不会进行比较。甚至对自己。
对于您的 DBA,上面带有 ISNULL 的代码将决定存储什么。
编辑:如果您存储空符号/值,那么您的 DBA 应该阅读“空位图"
In a CREATE TABLE, the NULL or NOT NULL here
varchar(50) null
is a constraint that determines whether NULLs are allowed. NOT NULL means no.When you inserted data, which statement did you run?
or
When you run
SELECT * FROM TABLE1
, client tools will show NULL.To test whether you actually have NULLs or the string NULL, run this
For the SELECTs
Note: null symbol/value is not empty string. It has no value and won't compare. Even to itself.
As for your DBA, the code above with ISNULL will decide what is stored.
Edit: if you are storing null symbol/value, then your DBA should read up on "null bitmap"
数据确实代表空值。文本“Null”是显示文本的查询工具。
The data does represent nulls. The text 'Null' is your query tool displaying the text.
NULL
关键字表示不存在任何值——该值是未知的。但这不会阻止有人存储拼出“NULL”的字母,提供数据类型(INT 和 DATETIME 不会)。因此,像
IS NULL
这样的运算符不适用于拼出“NULL”的文本,反之亦然 - 使用以下方式搜索字符串:... LIKE '%NULL%' 不会返回具有
NULL
值的记录。列的数据类型对于 SQL Server 中的
NULL
很重要。在 UNION 语句中,您需要将 NULL 转换为适当的数据类型 -NULL
的默认值是 INT:根据提供的有关列和输出的信息,DBA 似乎在询问您可以更改用于连接到 SQL Server 的客户端在结果集中遇到
NULL
值时显示的文本。让我想起我的第一份工作是洗碗,并被要求拿左手抹刀......The
NULL
keyword indicates the absence of any value -- the value is unknown.But that won't stop someone from storing the letters that spell out "NULL", data type providing (which INT and DATETIME will not). Because of this, operators like
IS NULL
would not work on text that spells out "NULL" and vice versa -- searching for strings using:... LIKE '%NULL%'
will not return records withNULL
values.The data type of the column does matter with regard to
NULL
in SQL Server. In a UNION statement, you need to cast NULL to be the appropriate data type -- the default forNULL
is INT:Based on the information provided about the columns and the output, the DBA appears to be asking you to change the text the client you are using to connect to SQL Server with displays when a
NULL
value is encountered in a resultset. Reminds me of my first job dishwashing, and was asked to get the lefthanded spatula...字符串“Null”是一个字符串。
NULL 值(或者 Null 或 null,SQL 在处理这些事情时不区分大小写)用于表示未知值。如果你愿意的话,它是一组空值。
http://www.w3schools.com/sql/sql_null_values.asp
The string "Null" is a string.
The value of NULL (or Null or null, SQL is case-insensitive when it comes to these things) is used to denote an unknown value. It's the empty set of values, if you will.
http://www.w3schools.com/sql/sql_null_values.asp
NULL,在软件中,象征着没有价值。假设您使用以
null
作为值的字符串插入列,请使用 null 常量。例如,请注意,NULL 是 SQL 中的常量,而不是字符串中的单词“NULL”。
NULL, in software, is symbolic of no value. Assuming you're inserting the columns using a string with
null
as the value, use the null constant. e.g.Note that NULL is a constant in SQL, not the word "NULL" in a string.
AFAIC,NULL 之间没有区别。有不同的列类型。但只要一列是文本数据类型,并且它是 NULL,它就是文本 NULL。
有时会出现关于空字符串(“”)而不是 NULL 的问题,但您使用的描述似乎并未提及这一点。
AFAIC, there is no different between NULLs. There are different column types. But as long as a column is a text data type, and it's NULL, it's a text NULL.
Sometimes there are questions about empty strings ("") instead of NULLs, but the description you're using doesn't seem to be referring to that.
SELECT * FROM TABLE1 WHERE NAME IS NULL
返回名称为 NULL 的所有行
SELECT * FROM TABLE1 WHERE NAME = 'NULL'
返回名称等于字符串 'NULL',不返回空值
SELECT * FROM TABLE1 WHERE NAME = ' '
返回名称正好等于一个空格 ' ' 的所有行,不返回空值
如果运行此命令声明它可能有助于清除何时为空以及何时不为空
SELECT * FROM TABLE1 WHERE NAME IS NULL
Returns all rows where the Name is NULL
SELECT * FROM TABLE1 WHERE NAME = 'NULL'
Returns all rows where the Name is equal to the string 'NULL', Null values are not returned
SELECT * FROM TABLE1 WHERE NAME = ' '
Returns all rows where the Name is equal to exactly one space ' ', Null values are not returned
If you run this statement it might help clear up when its null and when its not