12. Database Query Language 1

Starting from this article, we will introduce ASP's built-in ActiveX components, but considering that we will be exposed to a large number of database queries in the future, therefore, the author temporarily decided to spend one or two articles to brief you on the basic knowledge of some database query languages, which is actually a required knowledge for learning ASP. Whether the database query language can be used flexibly is directly related to a series of problems such as the execution efficiency of ASP programs. Therefore, please pay attention to it.

I believe that many of my friends have heard of the SQL name. If you are a computer expert, the name of SQL must be like a storm. So what is SQL? The term "Structured Query Language" is an abbreviation of the structured query language. It is a tool used to organize, manage, and retrieve data stored in computer databases; is a specific type of database-relational database. The computer program that controls such databases is what we often call DBMS-database management systems. For example, SQL Server, Oracle, Sybase, DB2, and so on. When a user wants to retrieve data in the database, the user sends a request through the SQL language. Then, the DBMS processes the SQL request and retrieves the required data, and finally returns it to the user, this process is called database query, which is the name of the database query language.

SQL is not a complete computer language like C, COBOL, or FORTRAN. SQL does not have the if statement used for conditional testing, nor does it have the GOTO statement used for the program Branch and the loop statement for or do. Specifically, SQL is a database sub-language. SQL statements can be embedded into another language to enable database access. SQL is not a strictly structured language, and its syntax is more similar to that of an English statement. Therefore, it is easy to understand. Most SQL statements are just like natural languages. SQL is also an interactive query language that allows users to directly query stored data. With this interactive feature, users can answer quite complex questions in a short period of time, however, it may take weeks or even longer for programmers to write corresponding report programs.

In most ASP applications, we are exposed to databases, and the standard syntax we use to write ASP applications for database operations is exactly SQL. Therefore, the importance of SQL syntax is self-evident. Next, let's start with select, the most common SQL statement, and learn SQL step by step.

Query is the core of the SQL language, while the SELECT statement used to express SQL queries is the most powerful and complex SQL statement. It retrieves data from the database, and provide the query result to the user. In this article, we will create a simple database named Tianjiao, which stores a sales record table named sales, as shown below:

Name	Gender	Salary	Sales Target	Sales	Region
Scholar	Male	2500	8000	9000	Shanghai
Champion Wu	Male	3000	10000	9999	Beijing
Lei Ming	Male	2000	8000	10000	Sichuan
Cher	Female	2500	5000	6000	Guangzhou
Gu Yi	Male	2600	9000	9800	Dalian
Azhuo	Female	2000	4000	4000	Tianjin
Daily	Male	4000	20000	20000	Nationwide

There are six columns in the Table: name, gender, salary, sales target, sales volume, and region. First, we use the SELECT statement to list the name, sales target, and sales volume:

Select name, sales target, sales from sales

The result is as follows:

Name	Sales Target	Sales
Scholar	8000	9000
Champion Wu	10000	9999
Lei Ming	8000	10000
Cher	5000	6000
Gu Yi	9000	9800
Azhuo	4000	4000
Daily	20000	20000

Then, we will list the names, sales targets, and sales of all male:

Select name, sales target, sales from sales where gender = "male"

The result is as follows:

Name	Sales Target	Sales
Scholar	8000	9000
Champion Wu	10000	9999
Lei Ming	8000	10000
Gu Yi	9000	9800
Daily	20000	20000

Next, we will make a relatively complex query to list the names, sales targets, and sales of all male whose sales volume is greater than the sales target, and sort them by sales target.
Select name, sales target, sales
Form Sales
Where sales> sales target
And Gender = "male"
Order by sales target

The result is as follows:

Name	Sales Target	Sales
Scholar	8000	9000
Lei Ming	8000	10000
Gu Yi	9000	9800
Daily	20000	20000

12
As you can see, for simple queries, the SQL SELECT statement is very similar to the English syntax. Let's analyze the complete format of the SELECT statement, which includes six clauses, the Select and from clauses are mandatory, and other clauses can be selected. The functions of each clause are as follows:

1. The select clause lists all data items that require the SELECT statement to retrieve. It is placed at the beginning of the SELECT statement and specifies the data item to be retrieved for this query. These data items are usually represented by a selection table, that is, a group of selection items separated by commas. In the left-to-right order, the query results of one column are generated for each selected item. A selected item may be of the following items:

(1) column name: identifies columns in the table specified by the from clause. If the column name is selected, SQL directly extracts the value of this column from each row in the database table and places it in the corresponding row of the query result.
(2) constant: Specify to put this value in each row of the query result.

(3) SQL expressions: the values to be placed in the query results must be calculated according to the expressions.

2. The from clause lists the tables that contain the data to be queried. It consists of the keywords from followed by a group of table names separated by commas. Each table indicates a table that includes the data to be retrieved from the query. These tables are called the table sources of this SQL statement, because the query results all come from them.
3. The WHERE clause tells SQL to query only data in some rows. These rows are described using search conditions.

4. The group by clause specifies a summary query, that is, grouping similar rows instead of generating a query result for each row, and then generating a summary result for each group.

5. The having clause tells SQL to generate only the results of some groups obtained by group by. Like the WHERE clause, the desired group is also specified by 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 are unordered.

The authors below will provide a simple but practical ASP program to query using SQL statements for your reference.

To help you better understand the application of SQL syntax in ASP, we first write all the core processes to a sub named query2table, then, the sub is called using the server-side inclusion function of ASP. Paste the following statement to the workbook, save it as the subdbtable. inc file, and place it under the virtual directory asptest:
<%
Sub query2table (inputquery)
Set conntemp = server. Createobject ("ADODB. Connection ")
Conntemp. Open "DSN = student; uid = student; Pwd = aspmagic"
Set rstemp1_conntemp.exe cute (inputquery)
Howmanyfields = rstemp. Fields. Count-1
'Count 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 blank, the value of the thisvalue variable is defined as a space.
End if %>
<TD valign = top> <% = thisvalue %> </TD>
<% Next %>
</Tr>
<% Rstemp. movenext
Loop %>
</Table>
<%
Rstemp. Close
Set rstemp = nothing
Conntemp. Close
Set conntemp = nothingend sub %>

The sub definition process is completed. In the following ASP programs, you only need to add the SQL query statements you want to use and call this process to conveniently obtain the query results. Save the following four sections of code as four. asp files: asp11a. asp, asp11b. asp, asp11c. asp, and asp11d. asp.

<Head> <title> asp11a. asp </title> <HTML> <body bgcolor = "# ffffff">
<%
Call query2table ("select * from publishers where name like 'a % '")
'Query all records with letters A in the publishers table.
%>
<! -- # Include virtual = "/asptest/subdbtable. Inc" --> </body>

<Head> <title> asp11b. asp </title> <%
Call query2table ("select * from titles where year_published> = 1998 ")
'Query all records in table titles with the year of publication greater than or equal to 1998.
%>
<! -- # Include virtual = "/asptest/subdbtable. Inc" --> </body>

<Head> <title> asp11c. asp </title> <%
Call query2table ("select * from publishers where amount> 10000 and sex = 'male '")
'Query all the male records in the publishers table with a quantity greater than 10000
%>
<! -- # Include virtual = "/asptest/subdbtable. Inc" --> </body>

<Head> <title> asp11d. asp </title> <%
Call query2table ("select * from publishers where State <> 'ny '")
'Query the records of all cities in the publishers table that are not in New York.
%>
<! -- # Include virtual = "/asptest/subdbtable. Inc" --> </body>

Use subdbtable. in the query2table process defined in the inc file, you can query the database very quickly. All you have to do is set "conntemp. in open "DSN = student; uid = student; Pwd = aspmagic", the database name, user identity, and password are slightly changed. When query2table is called, enter the SQL query statement you want to use. Is it easy? This is the charm of ASP and SQL !!!

Today, although we only learned one SQL command in a whole article, please believe that what you got is far different from one DOS command, the SELECT command makes it very easy for you to query the database. Before that, you may know nothing about the database query, but through the study in this article, you have already used ASP for common database queries. Are you very excited? In the next article, I will continue to introduce several other basic SQL commands. Before the end of this article, I would like to apologize to many friends who have sent emails. Due to the busy work recently, I have no time to answer your questions one by one. Sorry, I will try to write out some common and frequently-occurring problems in the article. As for Some uncommon problems, I suggest you go to the following site to ask questions, you will receive a prompt response. Www.onlinechina.net/friend/flybird/bbs/wwwboard.asp? Id = 1. This is the best Chinese ASP learning site ever seen by the author. It is hosted by birds of Shanghai. You must check it out.