Using SELECT syntax 2 in ADO
Author：Eve Cole
Update Time：2009-05-30 19:54:35
Continuing from the previous article:
WHERE can use the following BETWEEN, LIKE, and IN operators.
Between...And
Between...And determines whether the value of the expression falls within the specified range. The syntax is:
expr Between value1 And value2
Indicates that if the value of expr is between value1 and value2, the Between...And operator will return True; otherwise it will return False.
You can also add the logical operation Not expr Between value1 And value2 to determine the opposite condition, that is, expr falls outside the range of value1 and value 2.
If expr, value1, or value2 is Null, Between...And will return Null value.
You cannot use the wildcard character * in the Between...And operator. For example, you cannot use 100* and 500* to find product code numbers between 1000 and 5000.
Like
Like to find matching keywords.
Like is followed by an expression listing the strings or string literals to be compared.
In an expression, you can use the Like operator to compare a field data with a string expression. You can use the wildcard * to find similar data, such as Like "王*", and the query will return data starting with [王] in all fields.
Symbols that can be used in the operator expression after Like:
%: Wildcard character, representing one or more characters. For example, LIKE jack% will find all string literals starting with jack, and LIKE %jack% will find string literals including jack.
_: A wildcard character of one character, for example, LIKE jack_ will find string literals such as jack1, jacka, etc., and LIKE _jack_ will find string literals such as ajackb, cjackf, etc.
[xy]: Specify the character range, for example, LIKE [ac]jack will find string text such as ajack1, bjack, cjack, etc.
[^xy]: Specify the excluded character range, for example, LIKE [^ac]jack will not find string literals such as ajack1, bjack, cjack, etc.
In the following example, you will get data that starts with the letter A and is followed by any letter between B and G and a number:
Like "A[BG]#"
IN
IN operation formula, limited range.
The syntax is:
expr [Not] In(value1, value2,…)
Among them, expr is the operation expression field. value1, value2, ... are the specified range lists.
For example, you can use the In operator to specify the city where students must live, which is Guangzhou, Shenzhen, or Shantou:
SELECT *
FROM students
WHERE CityIn ('Guangzhou City','Shenzhen City','Shantou City')
Let's look at an example of an ASP program using this SQL command.
We can use the IN clause to determine the range. For example, the ASP program rs24.asp is as follows, [SELECT name, subject, score From exam Where score In (SELECT score From exam Where score >= 60)], use IN to find out the score is greater than or equal to 60 point record:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT name, subject, score From exam Where score In (SELECT score From exam Where score >= 60)"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>In: Scores below 60 are not counted"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("Name") & " " & rs2("Subject") & " Score: " & rs2("Score")
rs2.MoveNext
Loop
rs2.Close
%>
The above ASP program rs24.asp uses a browser on the client side to browse the execution results and display records with scores greater than or equal to 60 points.
NOT IN
NOT IN expression means it does not belong to the specified range.
Let's look at an example of using this SQL command in an ASP program.
We can use the Not In clause to determine the records that should not be displayed. For example, the ASP program rs24.asp is as follows, [SELECT name, subject, score From exam Where score Not In (SELECT score From exam Where score >= 60)], use IN Find the records with a score not greater than or equal to 60 points, that is, records with a score less than 60 points:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT Name, Subject, Score From Exam Where Score Not In (SELECT Score From Exam Where Score >= 60)"
rs2.Open SqlStr,conn1,1,1
Response.Write "<p>Not In: Scores above 60 are not counted"
Do while not rs2.EOF
Response.Write "<BR>" & rs2("Name") & " " & rs2("Subject") & " Score: " & rs2("Score")
rs2.MoveNext
Loop
rs2.Close
%>
For the above ASP program rs24.asp, use the browser on the client side to browse the execution results and display records with scores not greater than or equal to 60 points.
