You can see that for simple queries, SQL Select statements are very similar to English syntax. Let’s analyze the complete format of the SELECT statement. It includes six clauses, among which the SELECT and FROM clauses are necessary, and other clauses can be used. Select, the functions of each clause are as follows:
1. The Select clause lists all data items that require the SELECT statement to be retrieved. It is placed at the beginning of the SELECT statement, specifying the data item to be retrieved by this query. These data items are usually represented by selection tables, that is, a set of separated selections. In order from left to right, each selection produces a column of query results, and a selection may be the following items:
(1) Column name: Identify the column in the table specified by the FROM clause. If the column name is selected, SQL directly takes the column's value from each row in the database table and places it in the corresponding row of the query result.
(2) Constant: Specify that the value is placed in each row of the query result.
(3) SQL expression: It means that the value to be put into the query result must be calculated according to the expression's regulations.
2. The From clause lists the table containing the data to be queryed. It consists of the keyword FROM followed by a set of table names separated by commas. Each indication represents a table that includes the data to be retrieved by the query. These tables are called the table source for this SQL statement because the query results are derived from them.
3. The Where clause tells SQL to only query data in certain rows, which are described by search conditions.
4. The Group By clause specifies a summary query, that is, instead of producing a query result for each row, grouping similar rows and then producing a summary result for each group.
5. The Having clause tells SQL to produce only some groups obtained by Group By. Like the Where clause, the required groups are also specified with a search condition.
6. The Order By clause sorts the query results by data in one or more columns. If this clause is omitted, the query results will be out of order.
Below, the author will provide a simple but practical ASP program that uses SQL statement query for your reference.
In order to make everyone more clear and direct understanding of the application of SQL syntax in ASP, we first write all the core processes of the query into a SUB called query2table, and then use the server-side inclusion function of ASP to call the SUB. Please clip the following statements into the Notebook, save them as subdbtable.inc file, and place them in the virtual directory asptest:
< %
sub query2table(inputquery)
set conntemp=server.createobject(adodb.connection)
conntemp.open DSN=Student;uid=student;pwd=aspmagic
set rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1
' Statistics the number of columns in the database
%>
< table border=1>< tr>
< %
for i=0 to howmanyfields
%>
< td>< b>< %=rstemp(i).name%>< /B>< /TD>
< % next %>
< /tr>
< %
do while not rstemp.eof
%>
< tr>
< % for i = 0 to howmanyfields
thisvalue=rstemp(i)
If isnull(thisvalue) then
thisvalue=?
' If the field is empty, define the value of the variable thisvalue as a space
end if%>
< td valign=top>< %=thisvalue%>< /td>
< % next %>
< /tr>
< %rstemp.movenext
loop%>
< /table>
< %
rstemp.close
set rstemp=nothing
conntemp.close
Set c
onntemp=nothingend sub%>
After completing the SUB definition process, we can easily get the query results in the following ASP programs by adding the SQL query statement we want to use and calling the process. Save the following four codes as four .asp files: asp11a.asp, asp11b.asp, asp11c.asp, and asp11d.asp.
< HEAD>< TITLE>asp11a.asp< /TITLE>< /HEAD>
< HTML>< body bgcolor=#FFFFFF>
< %
call query2table(select * from publishers where name like 'A%%')
' Query all records with letter A in the names in the table publishers
%>
< !--#include virtual=/asptest/subdbtable.inc-->< /BODY>< /HTML>
< HEAD>< TITLE>asp11b.asp< /TITLE>< /HEAD>< HTML>< body bgcolor=#FFFFF>
< %
call query2table(select * from titles where Year_Published > = 1998)
' Query all records in the table titles whose publication year is greater than or equal to 1998
%>
< !--#include virtual=/asptest/subdbtable.inc-->< /BODY>< /HTML>
< HEAD>< TITLE>asp11c.asp< /TITLE>< /HEAD>< HTML>< body bgcolor=#FFFFF>
< %
call query2table(select * from publishers where amount>10000 and sex='male')
' Query all records in the table publishers with a number of more than 10,000 and gender of male
%>
< !--#include virtual=/asptest/subdbtable.inc-->< /BODY>< /HTML>
< HEAD>< TITLE>asp11d.asp< /TITLE>< /HEAD>< HTML>< body bgcolor=#FFFFF>
< %
call query2table(select * from publishers where state< > 'NY')
'Query all records in the table publishers that are not New York.
%>
< !--#include virtual=/asptest/subdbtable.inc-->< /BODY>< /HTML>
Using the process query2table defined in the subdbtable.inc file, you can query the database very quickly. All you have to do is to conntemp.open DSN=Student;uid=student;pwd=aspmagic database name, user Change the identity and password slightly, and enter the SQL query statement you want to use when calling query2table. Isn't it very simple? This is the charm of ASP and SQL!!!
Today, although we spent the entire article only learning one SQL instruction, please believe that what you obtained is far different from a DOS instruction. The SELECT instruction allows you to query the database very easily. Perhaps before that I still know nothing about database queries, but through this article, you can actually use ASP to conduct commonly used database queries. Are you very excited? In the next article, the author will continue to introduce it to you
Several other basic instructions for SQL. Before ending this article, the author wants to apologize to many friends who wrote to me. Because the author is busy with work recently, he really has no time to answer your questions one by one. Please forgive me. I will try to add some common and frequently occurring problems in the article. I wrote it out. As for some uncommon questions, I suggest you ask questions at the following site and you will get timely answers. www.onlinechina.net/friend/flybird/bbs/wwwboard.asp?id=1, This is the best Chinese ASP learning site the author has seen so far, hosted by Feiniao from Shanghai, so everyone must go and have a look.