Shulou(Shulou.com)05/31 Report--

How to analyze the advantages and disadvantages of sql where 1? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

First, the trouble of not using where 1 query in multi-conditional query

For example, if you make a query page, and there are multiple options that can be queried, and at the same time, let the user select and enter query keywords, then, according to the usual dynamic construction of the query statement, the code is roughly as follows:

String MySqlStr= "select * from table where"; if (Age.Text.Lenght > 0) {MySqlStr=MySqlStr+ "Age=" + "'Age.Text'";} if (Address.Text.Lenght > 0) {MySqlStr=MySqlStr+ "and Address=" + "' Address.Text'";}

① hypothesis

If the above two IF judgment statements are both True, that is, the user has entered a query word, then the final MySqlStr dynamic construction statement becomes:

The code is as follows:

MySqlStr= "select * from table where Age='18' and Address=' Xiao Bo Mo Cun'", Guangnan County, Wenshan Prefecture, Yunnan Province

As you can see, this is a complete and correct SQL query statement that can be executed correctly and return data based on whether a record exists in the database.

② hypothesis

If the above two IF judgment statements are not true, the final MySqlStr dynamic construction statement becomes:

The code is: MySqlStr= "select * from table where"

Now, let's take a look at this statement. Because a condition is needed after the where keyword, but there is no condition at all, the statement is an error statement that cannot be executed, not only an error is reported, but no data is queried at the same time. The above two hypotheses represent practical applications and show that there are problems in the construction of sentences, which are not enough to cope with flexible query conditions. Second, the advantages of using where 1: 1 if we change the above statement to:

The code is as follows:

String MySqlStr= "select * from table where 1 # 1";

If (Age.Text.Lenght > 0) {

MySqlStr=MySqlStr+ "and Age=" + "'Age.Text'";}

If (Address.Text.Lenght > 0) {

MySqlStr=MySqlStr+ "and Address=" + "'Address.Text'";}

Now, there are two assumptions.

① assumes that if both IF are true, the statement becomes:

The code is as follows:

MySqlStr= "select * from table where 1 and Age='18' and Address=' Xiao Bo Mo Cun, Guangnan County, Wenshan Prefecture, Yunnan Province'"

Obviously, this statement is the correct statement that can be executed correctly, and if there are records in the database, it will certainly be queried.

② assumes that if neither IF is true, the statement becomes:

MySqlStr= "select * from table where 1 # 1"

Now, let's look at this statement. Because where 1 is a True statement, the statement is syntactically correct and can be executed correctly, and its effect is equivalent to:

MySqlStr= "select * from table", which returns all the data in the table. The implication is: if the user does not select any fields or enter any keywords in the multi-conditional query page, all the data in the table will be returned; if the user selects some fields and enters some query keywords on the page, the query will be conducted according to the conditions set by the user. At this point, I don't know if you have understood, in fact, the application of where 1 is not an advanced application, nor is it the so-called intelligent construction, but just a way to construct a dynamic SQL statement that can run correctly to meet the uncertain factors in the multi-conditional query page. Where 1: 0; this condition is always false, the result will not return any data, only table structure, can be used to quickly build tables copy code code is as follows: "SELECT * FROM strName WHERE 1: 0"

This select statement is mainly used to read the structure of the table without considering the data in the table, which saves memory because you don't have to save the result set. The copy code is as follows: create table newtable as select * from oldtable where 1: 0; create a new table, and the structure of the new table is the same as that of the query table.

This is the answer to the question on how to analyze the advantages and disadvantages of sql where 1. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.