SQL story Excerpt (vi) ———— an impossible error

The day of the wrong beginner SQL, feel like just learning to walk, gait can be, stumbling. I fell a lot of ridiculous somersault. Take out everyone to entertain, also mention each other wake up, such a mistake we can try to avoid.
Look at this first:

Example 1 an unreasonable comma:

Select Field1, Field2, Field3, from MyTable

One implementation is a grammatical error, what does it mean, this is copied from the book Ah, you can not do this to me ... Oh, well, the mistake is that I added a comma after the last field name. Comma is a separate field name or table name, the field name and form a comma between what is the matter? Do not underestimate it, even veteran, also often out of this fault, often because of this situation: we wrote a

Select Field1,

Field2,

Field3

From MyTable

......

Then in the debugging process, we may have to delete some of the fields, especially before the FROM keyword additions and deletions to the field, often trouble, forget to see whether the fields are separated by commas and there is no extra comma, common is:

Select Field1,

Field2,

From MyTable

......

Or

Select Field1,

Field2,

Field3

Field4

From MyTable

......

The comma between Field3 and Field4 is missing, if you write "SELECT ... Field3 as "XXX" Field4 ... "Fortunately, the system will immediately find that there is a mistake, do not let you pass the grammar check." If it's as bad as the previous one, the system will assume that this is the alias you gave Field3, which will honestly output:

Field1 Field2 Field4

------------------------

..............................

Big sloppy like me is likely to pass "without fault", or to find "my Field3" in a strange way. In fact, the missing is Field4 ...

The SQL in the MCDBA textbook uses a method of writing that is strange at first, but can effectively prevent such errors:

Select Field1

, Field2

, Field3

From MyTable

......

The first field is preceded by the Select keyword, and the other fields are preceded by commas. This type of writing does not conform to the English style, but friends can try, when you select a field (single line), or a few fields (multiple lines), drag them, cut, paste, modify, almost does not cause a comma error. Because of the common People's habit, the debugging process most often changes is the last few fields. This notation always guarantees that there is no comma between the From and the fields in front of it, and that there is a comma between each segment. Of course there's a comma between the Select and the first field. I've done it too (I've made too many mistakes), but my experience is that this kind of error can easily be found, not as concealed as the two mentioned earlier. Of course it really looked bad, so I still can not remember to write so, so I will sometimes make this comma error. If the value is worth it, please let the readers choose for themselves.

Example 2 language problem:

Look at this sentence, incredibly also wrong?

Select Field1,

Field2,

Field3

From MyTable

Yes, all the commas are in place, and the statement is so simple that there is nothing wrong with it? In fact...... is the comma behind the Field2 a little weird? Yes, it's a Chinese comma, and the database engine doesn't know it. On other occasions, there may be less chance of making such a mistake, because we often encounter this kind of thing in the database because we have to deal with the information in Chinese. Not only the comma, sometimes we may write some Chinese aliases, so the double quotes that are used to identify it are also in danger of being written in Chinese. In this regard, no special skills, if you only use this machine to write code, you can set the Chinese input method into English punctuation (I will never, because to write the article), there is the need to use half-width digits, with a full angle of the number to write the formula, the system may not recognize.

To give you a more excessive Chinese error:

Select Field1,

Field2,

Field3+1,

Field4 as "Chinese text segment name"

From MyTable

Look, Chinese and English mixed row is not quite scary?

"Form" error

This is relatively simple, not an example, is a spelling error. If you comment on the most error-prone keywords in the SQL language, from must be the top of the list. For and form are common words in English. I believe that as soon as I wrote it down, I would have to write from the form. In the case of Microsoft SQL Server Query Analyzer or InterBase's isql, it is easier to find because from will be bold or blue. However, we often have to embed SQL in the client program code (such as Delphi programming), then it is easier to make this mistake. When I wrote the program today, I also wrote a "Form" in a JavaScript script that was written "from," which was written back to SQL. There is really no good way to do this, be careful.

Shadow Killer--null value

There is a payroll, there are two types of employee pay, technical wages and business wages, each person according to post, send one of them. Now statistics this month, the company issued a total amount of money, you may be so check:

SELECT SUM (Technical salary + business wage) from payroll

And one time, the result is 0. The boss is happy to hear about this, because he's lost a lot of money this month, but you're in danger of being besieged by colleagues. It's really simple, you forget the null value. Each person's salary has a null, directly a plus is null, a statistic does not have a penny.

This time it's not just a matter of care, the null value is one of the weirdest things in the SQL language, and even the entire relational model. What is it? It is nothing, it stands for a unknowable, unknown, undefined thing. It is not a 0, not a space or an empty string. It is not information because it does not represent any information, but it is also information that tells us there is no information here. It is any type of data, because any data will have null; it is not any type of data, because it doesn't make sense to use any data to compare it. "It is not pleasant and mathematically unreasonable, but it is what we get," SQL-3 in the book of references. "It's a thing that makes old hands go crazy," he said. It reminds me of a fairy tale I read when I was very young, saying that the Kingdom of fairy tales was swallowed up by nothingness. What is "nothingness"? It is nothing, it has no color, no shape, no smell, it is nothing, in "nothing" can not see, yes, it has no color, it does not mean that it is black or transparent, "Nothing" Is "Utopia", "Nothing" embezzle everything, What Encounter "Utopia" will become "Utopia". The book also wrote a lot of other things that are very philosophical in the stream of consciousness. I was reading this book. The whole people become god-like, every day to think of some existence ah, self I what (at that time I was in primary school), so that later accidentally became a programmer. Later saw the null value I just understand, the original book of the author must be a database programmer, not good is e.f.codd himself.

Null value is a very scary thing, what and its operation will be null, so do not expect who can and it is not the same size, even null value itself, want to know whether a value is null can not use Xxx=null, only with XXX is null. Do join query, especially to be careful of NULL, joins have inline, there are left and right outside, there is a full join, there is cross join, is this reason. Not to mention a number of mathematical expressions, be sure to be careful of it, if necessary, be sure to use some methods to avoid NULL values, such as when designing a database, the default value of the field to set the default value, the requirement that the user must input is set to NOT NULL. Many database systems have dedicated functions to handle null values, in this regard, MS SQL Server does a good job, there are some such as isnull () functions such as, the function is very comprehensive and easy to use.

On the null value, you can definitely expand a very interesting topic, but it is indeed the most common technical errors, so here must be mentioned. For example, the above error, there are two ways to avoid: either the default value of the two empty funds are set to 0, or use:

SELECT SUM (Technical salary) + (business wage) from payroll

Now, you're safe.

Come here today, we'll have a chance, we will continue to discuss some more interesting, but also more complex mistakes, to understand them, for our progress is also very helpful.