In the previous article, we have prelimated NULL this incredible little thing. Today, I will introduce it to it as much as possible. In accordance with the practice, this is a discussion as much as possible, but not rigorous, and even possibly the content is not so serious. My purpose is to help readers work more easily and interested in learning further learning. On the other hand, I believe that there is a mistake in my argument, and there will be other people who are also committed, so I will find that the improper friend must speak. This will help me with my readers and friends. I sincerely thank each friend who proposed criticism and correction, especially those who openly propose criticism and correction. Special thanks to Sunshine19, the special trip is sent to e-mail, pointing out the shortcomings of "SQL Story" and puts forward valuable comments. I expect him to continue to care about this topic and expect him to bring our excellent works.
Unavoidable
Example: Double room.
Suppose a school, its single staff apartment, each room has two beds. There is a lot of benefits, such as safety, is a half-large young man, no matter whether the boy girl, there is a companionship, people are always rest assured, there is a photo of each other; if anyone is married, because each room live two Individual, you can also find one more, first give them a home, slowly wait for the room. These things do not have to worry about our programmers (unless we live inside). What is going to care now is that some people have made an apartment management database. The room management table is designed like this:
CREATE TABLE ROOMS
Roomid char (5),
Masterid char (10),
Secondid char (10),
Constraint PK_ROOM PRIMARY Key (roomid),
ConsTRAINT FK_MASTER Foreign Key (MasterID) References Livers (ID),
Constraint fk_second Foreign Key (Secondid) References Livers (ID)
)
Simply introduce, the administrator requires every room for each living room to have a homeowner in order to facilitate management (such as what to collect water and electricity bills; two more detailed information is saved in the Livers table, so we have two Foreign key constraints. Of course, this design is not impeccable, but it also has its reason, here we don't discuss it first.
What I have to pay attention to is that the tenants are less than two people, what should we fill in the empty place? One way is to fill in an empty string, so that we are equal to our default, there is a household for an id number as an empty string. Because the empty string is also a string. Two foreign key constraints also illustrate this. In fact, in this design, I have seen some examples, and their authors make a non-existent information in order to avoid untropical situations, as part of the database structure. Specific to this example, some people may really use an empty string or write a "none", write in the livers table, indicating no one live. Usually this will cause a lot of problems, such as we may have to add a constraint, making a person can't appear twice in the rooms table. But now this virtual comes out, how do you do anyone's tenant? There are a lot of positions, such as if there is an audio, there are two "nones" on this record! In the case of more bizarre (but not impossible in some applications), there is a tenant, his ID is "none", how? ? And if the tenant is fully, what should I do? If you want "delet from liver", "none" can be deleted. When you work in such a database, you have a time to remind yourself not to be guilty of guys who don't exist. To remember, he has no gender, so he must live in the room of the male and female employees; someone can only sleep a bed, but he can be all idle; others have moved out, his old man accounts for all. The room, our legal regulations have no meaning to him. This special value gives us the biggest problem, that is, the information and database structure are stirred together. That is, the pattern of relationships and relationships is mixed. If you have a few such a table in a database, everyone will be crazy. This is the meaning of null. When you define a data as NULL, it is equal to the system that does not exist, or unknown. Don't take it, its content is meaningless (of course, this is also a meaning). There is a null in the room, only no one, but will not be considered a person called "no one" living inside. There will never have a villager who occupies all rooms. To empty the Livers, then clear, as long as you set the cascade constraint, there will be no bizarre. The only consequence is just an unmanned apartment. Is this not what we want?
NULL is true, although we cannot explain its existence from a sense. It is a black hole that is related to the world. Maybe some people will be uncomfortable, but avoiding it will only bring us more trouble.
I can't see it.
In the SQL-3 Reference Daquan, the author explains the meaning of NULL: Unknown or undefined. For NULL, there are many interesting features.
NULL is a data value, and it belongs to a domain (?). Yes, such as a string field, which can only be a string. Although its content is not defined, or unknown, it is a string, which is unquestionable.
NULL is not illegal data, this SQL-3 standard is also very clear. We have no way to save zero, but you can save null values. Although it is null, it is not defined, unknown, but it is indeed a legal information.
Black hole: For NULL, the general operation will return null. For example, the addition and subtraction, this is simply a black hole. There will never have any data equal to NULL. Of course, 1 is not equal to NULL, 2 is the same. However, NULL is not equal to NULL. It is wrong to say that a null is equal to NULL. So we can only compare it "yes" or "no". In order to avoid confusion, SQL-3 standard has some conventions. For example, expressions x = null, the result should be unkown. The expression "x is null" is to see the situation. If x is null or false, return TURE (?); X is non-null, return false. Is it a bit strange to, it is not equal to NULL based on NULL. This rule is indeed supported by SQL standards, and there is such a database system, but it is not surprised. Three-value logic: "Deer Ding Ji" in Wei Xiaobao, one of the top secrets is "I asked you, just nod, I don't shake your head, I don't allow you to be!" Indeed, usually our logical perspective, always based on this A two-value logic system, right is correct, wrong is wrong. It is not as simple as in the relationship theory. There is a case where there is no absolutely non-right: null. This is the three-value logic of relationship theory.
There are also some common situations related to NULL. For example, the statistical function (also known as the aggregation function, the set function) usually ignores NULL. However, suppose to have such a table T:
C
-----
1
2
NULL
NULL
We perform two queries separately: select count (*) from T and Select Count (c) from T, guessing is there? At first, I thought two results should be the same. As a result, the previous one is 4, the next one is 2. The former one is equal to 4, obviously NULL is also data facts; and then a result is 2, when statistics C columns, count ignores NULL.
In the SQL-3 standard, the search conditions (where and having) only accept true, but the constraint only refuses FALSE, the two are opposite to NULL. So the ROOMS table you see in front can write an empty value to the person field. And when you sort, there is no law. The SQL standard only makes a supplementary definition, so the method of processing the order by each DBMS is not the same, and of course NULL is not above all values.
Introducing more detailed content about NULL in "SQL-3 Reference Daquan". In addition, the book also introduces two database experts C.j.date and E.f.codd About NULL's different arguments (E.CODD even wants to have four-value logic) Interested friends can find one read. In practice, we will also encounter some interesting things, especially in the joint query, NULL makes our life "colorful".
Due to various reasons, I may slow down in recent days, but this doesn't mean that I will give up. After the rest and charging, I will return to the actual combat field I excellent. Thanks again to every reader who cares about "SQL Story".
