SQL Story excerpt (6) ---- Impossible Error

zhaozj2021-02-11  204

I'm beginning to learn SQL, I feel like just learning to walk, the gait can be embarrassing, and the fall. I fell a lot of comiches. Take out everyone's entertainment, and you will take each other, so that we can avoid it as much as possible.

See this first:

Example 1 unreasonable comma:

Select Field1, Field2, Field3, from MyTable

One execution is a speech error, what does it mean, this is from the book, you can't be so for me ... Hehehe, in fact, the mistake is that I added a comma after the last field name. The comma is separated by the word name or the table name, what is the commaity between the field name and Form? Don't look at it, even if you have a older, it is often a mistake. It is often because of this situation: We wrote a

SELECT FIELD1,

Field2,

Field3

From myTable

......

During the debugging process, we may add some fields, especially when the FROM keyword is added to the field, often do something, forget whether it is divided by a comma and there is no extra comma, common is:

SELECT FIELD1,

Field2,

From myTable

......

or

SELECT FIELD1,

Field2,

Field3

Field4

From myTable

......

There is a comma between Field3 and Field4, if you write "SELECT ... FIELD3 AS" XXX "Field4 ..." is ok, the system will immediately find that there is a fault, do not let you check. If it is like the previous written, the system will think that this is the alias you give Field3, which will be old and actually output:

FIELD1 FIELD2 FIELD4

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

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

Majuro like me is likely to pass like "no mistake", or I am very strange to find "My Field3 there"? In fact, it is Field4 ...

SQL in the MCDBA textbook uses a way of writing, it is very strange, but it can effectively prevent such errors:

SELECT FIELD1

Field2

Field3

From myTable

......

There is a Select keyword in front of the first field, and the other fields are fully comma. This kind of writing does not meet English style, but friends can try it, when you choose a field (single line), or a few fields (multi-line), drag them, cut, paste, modify, almost no cause Comma error. Because of the habit of the average person, the most commonly changed in the debugging process is the last few fields. With this way of writing, there is always no comma between the FROM and the previous fields, and there is a comma between each segment. Of course, I have a comma between Select and the first field. I have also made (I am too too much), but my experience is that this mistake will be discovered, unlike the previous Two kinds of hidden. Of course, it really doesn't look smooth, so I will always think so now, so I still have this comma error now. The value is not worth it, please read it yourself.

Example 2 Language problem:

Look at this sentence, actually there is a fault?

SELECT FIELD1,

Field2,

Field3

From myTable

Yes, all commas are all, and this statement is so simple, there is really a place to make mistakes? In fact, is it a split? Yes, it is a Chinese comma, the database engine does not know it. In other situations, opportunities that may make this fault will be less, because we have a large amount to deal with Chinese information in the database, it will often encounter this. Not just a comma, sometimes we may write some Chinese alias, so it is used to identify the double quotation of it and there is a risk of mistaken into Chinese. In this regard, there is no special skill. If you use this machine to write code, you may wish to set the Chinese input method into English punctuation (I will never, because I want to write articles), and there must be half-angle numbers, use The full-horn digital writer is not recognized. Give everyone a more excessive Chinese error:

SELECT FIELD1,

Field2,

Field3 1,

Field4 AS "Chinese Characters"

From myTable

Look, is it terrible?

"Form" error

This is relatively simple, it is not an example, it is a spelling error. If you review the keywords that are the easiest to write in the SQL language, from the race in the list. FOR and FORM are all common words in English. I believe that I will write from Form to Form. If it is in the Query Analyzer or Interbase's ISQL in Microsoft SQL Server, it is more likely to be found because from the FROM will be bold or blue. However, we often embed SQL in the client program code (such as Delphi programming), then it is easy to make this fault. Just when writing the program today, I also wrote a "form" in the JavaScript script, this time I want to think SQL, and give it back. I really have no good way to this, be careful.

Shadow killer - Null value

There is a wage list, and the employee salary has two kinds, technical wages and business wages. Everyone sends one according to the position. Now, how much is the company in this month, maybe you will check:

SELECT SUM (Technical Wage Business Wage) from Waraction

And one time, the result is 0. The boss heard that this will be relatively happy, because he has a big spending this month, but you face the danger of being siegeed by colleagues. In fact, it is very simple, you forgot NULL value. Everyone's salary has a null, directly adding, NULL, a statistical penny is not.

This time, this time is not just a careful problem, null value is one of the most strange things in the SQL language, and even the entire relationship model. what is it? It is nothing, it represents everything unknown, unknown, undefined things. It is not 0, it is not a space or empty string. It is not information because it does not represent any information; but it is also information, it tells us there is no information. It is any type of data, because any data will have null; it is not any type of data, because it is meaningless to use any data. "SQL-3 Reference Daquan" This evaluation: "This is unpleasant and is unreasonable in mathematics, but it is what we got." This is something that will make the old hands. It reminds me of a fairy tale you read very little, saying that the fairy kingdom is swallowed by "Wushu". What is ""? It's nothing, it doesn't have a color, there is no shape, no smell, it's nothing, it can't see anything in "有", right, it doesn't have color, it does not mean it is black or transparent, "Wu is" is "Wu You", "Wu You" invades everything, and what is "有" will become "". This book also wrote some other big pile of things very philosophical. I read this book at the time, I became God, I thought about some existence every day, I was here, I was in elementary school), so that I was later accidental. Later, I met the NULL value. I understand that the author of the book must be a database programmer, not good, it is E.f.codd. NULL value is a terrible thing, what and it will become null, so don't expect anyone with it, even the null value does not, I want to know if a value is NULL can't use xxx = null, You can only use xxx is null. When doing join inquiry, especially careful NULL, join inline, with left and right outlook, have all joints, and cross join, this is this reason. Don't write some mathematical expressions, be careful. If necessary, you must use some ways to avoid NULL values. For example, when designing the database, the default value is required to set the default value, which requires the user to enter For NOT NULL. Many database systems have dedicated features to handle NULL values. In this regard, MS SQL Server is not bad, there are some functions such as ISNULL (), and the functions are very comprehensive and very convenient.

About NULL values, it is absolutely able to expand a very interesting topic, but it is indeed the most common technical mistake, so it must be mentioned here. For example, there are two ways to avoid: either set the default value of both air, or use:

SELECT SUM (Business Wage) from Waraction

Now you are safe.

Today, you will come here, there will be a chance, we will continue to discuss some more interesting, more complicated mistakes, to make them, and make it very helpful to our progress.

转载请注明原文地址:https://www.9cbs.com/read-4598.html

New Post(0)