Lesson 12th: Database Query Language (1)

The original plan is to introduce the ActiveX components built in ASP from the beginning of this article, however, considering that we will be exposed to a large number of database queries in the future, so the author temporarily decided to spend one or two pages to brief you on some of the basic knowledge of database query language, which is actually learning ASP A knowledge that must be mastered. The flexibility of the use of database query language, will directly affect the implementation of ASP procedures, such as the efficiency of a series of issues, so please be sure to attach importance to.

I believe many friends have heard of the name of SQL, if you are a computer expert, the name of SQL must be thunderclap piercing. So what exactly is SQL? The word SQL is actually an abbreviation for the "Structured query Language" Structured Query Language, a tool for organizing, managing, and retrieving data stored in a computer database--a relational database of a particular type of database. and the computer program that controls this kind of database is we often say dbms--database management system. For example: SQL Server, Oracle, Sybase, DB2, and so on. When a user wants to retrieve data from a database, the request is made in the SQL language, and the DBMS processes the SQL request and retrieves the requested data and returns it to the user, which is called a database query, which is the origin of the name of the database query language.

SQL is not a complete computer language like C, COBOL, and Fortran languages. SQL does not have an IF statement for a conditional test, a Goto statement for a program branch, and a loop statement for or do. Specifically, SQL is a database child language, and SQL statements can be embedded in another language, enabling them to have database access capabilities. SQL is also not a strict structured language, its syntax is closer to English sentences, so easy to understand, most of the SQL statements are straight to the meaning, read as the natural language as clear. SQL is also an interactive query language that allows users to directly query for stored data, and with this interaction, users can answer fairly complex questions in a short period of time, and the same problem can take weeks or longer if the programmer writes the appropriate reporting program.

In most ASP applications we have access to the database, and the standard syntax for database operations when we write an ASP application is SQL, so the importance of SQL syntax is self-evident. Next, we start with the most commonly used SQL statement SELECT to learn SQL Step-by-step.

Queries are the core of the SQL language, and the SELECT statement for expressing SQL queries is the most powerful and complex SQL statement that retrieves data from the database and provides the results to the user. In this article, we'll build a simple database called Tianjiao, which holds a sales record table named Sale, as follows:

Name	Gender	Wages	Sales target	Sales	Area
Scholar	Man	2500	8000	9000	Shanghai
Wu Guanjun	Man	3000	10000	9999	Beijing
Thunder	Man	2000	8000	10000	Sichuan
Snow son	Woman	2500	5000	6000	Guangzhou
Gu Yi	Man	2600	9000	9800	Dalian
Charles	Woman	2000	4000	4000	Tianjin
Yi Tian	Man	4000	20000	20000	National

There are six columns or six fields in the table: name, sex, salary, sales target, sales, region, first we use the Select statement to List names, sales targets, and sales:

Select name, sales target, sales from sales

The results are as follows:

Name	Sales target	Sales
Scholar	8000	9000
Wu Guanjun	10000	9999
Thunder	8000	10000
Snow son	5000	6000
Gu Yi	9000	9800
Charles	4000	4000
Yi Tian	20000	20000

Then, we list all men's names, sales targets, and sales:

Select name, sales target, sales from sales Where sex = "male"

The results are as follows:

Name	Sales target	Sales
Scholar	8000	9000
Wu Guanjun	10000	9999
Thunder	8000	10000
Gu Yi	9000	9800
Yi Tian	20000	20000

　

Next, we make a relatively complex query that lists the names, sales targets, and sales of all men whose sales are larger than the sales target, sorted by sales target.
Select name, sales target, sales
Form Sales
Where Sales > Sales targets
and gender = "male"
Order BY sales target

The results are as follows:

Name	Sales target	Sales
Scholar	8000	9000
Thunder	8000	10000
Gu Yi	9000	9800
Yi Tian	20000	20000

As you can see, for a simple query, the SQL Select statement and the English grammar are very much alike, so let's analyze the full format of the SELECT statement, which includes six clauses, where the SELECT and from clauses are required, the other clauses can be optional, and each clause has the following function:

1. The SELECT clause lists all the data items that require a SELECT statement to retrieve. It is placed at the beginning of the SELECT statement, specifying the data items to retrieve for this query. These data items are usually represented by a select table, which is a set of selections separated by ",". In order from left to right, the query results for one column produced by each selection item may be the following items:

(1), column name: Identifies the column in the table specified in the FROM clause. If the column name is a selection, SQL takes the value of the column directly from each row in the database table, and then places it in the corresponding row of the query result.

(2), constant: Specifies that the value is placed in each row of the query's results.

(3), SQL expression: Description The value that must be put into the query result is computed according to the expression's specification.

2. The FROM clause lists the table that contains the data you want to query, consisting of a comma-separated list of table names from the keyword from followed by a group. Each indication represents a table that includes the query to retrieve data. These tables are called the table sources for this SQL statement because the query results are derived from them.

3. The Where clause tells SQL to query only the data in some rows, which are described in search terms.

4. The GROUP BY clause specifies a rollup query that does not produce one query result per row, but rather groups similar rows and then produces a summary result for each group.

5. The HAVING clause tells the SQL to produce only the results of some groups obtained by the Group by, and as the Where clause, the desired group is also specified with a search condition.

6. The ORDER BY clause sorts the results of the query by one or more columns. If this clause is omitted, the query result will be unordered.

The following author will provide a simple but practical application of SQL statement query ASP program for your reference.

To get a clearer and more straightforward view of the application of SQL syntax in ASP, we first write all the core processes of the query as a sub named query2table, and then invoke the sub with ASP's server-side containment capabilities. Clip the following statement to a memo book, Save as a subdbtable.inc file, and place under 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
' The number of columns in the statistics database
%>
< table border=1>< Tr>
<%
For I=0 to Howmanyfields
%>
< td>< b><%=rstemp (i) .name%></b></td>
<% next%>
</tr>
<%
Do as not rstemp.eof
%>
< tr>
<% for i = 0 to Howmanyfields
Thisvalue=rstemp (i)
If IsNull (Thisvalue) Then
Thisvalue= "?
' If the field is empty, the value of the variable thisvalue 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%>

Complete the definition of SUB process, in the following several ASP programs, we just add the SQL query that we want to use, and call the process can be very convenient to get the results of the query. Save the following four snippets of code as asp11a.asp, asp11b.asp, asp11c.asp, asp11d.asp Four. asp files.

< head>< title>asp11a.asp</title>< html>< body bgcolor= "#FFFFFF" >
<%
Call Query2table ("select * from publishers where name like ' a%% '")
' Query the records with the letter A in all the names in the table publishers
%>
<!--#include virtual= "/asptest/subdbtable.inc"--></body>

< head>< title>asp11b.asp</title><%
Call Query2table ("SELECT * from titles where year_published > = 1998")
' Query for records that are greater than or equal to 1998 years for all published years in table titles
%>
<!--#include virtual= "/asptest/subdbtable.inc"--></body>

< head>< title>asp11c.asp</title><%
Call Query2table ("select * from Publishers where amount>10000 and sex= ' Male '")
' Query all records in the table publishers that are greater than 10000 and have sex as male
%>
<!--#include virtual= "/asptest/subdbtable.inc"--></body>

< head>< title>asp11d.asp</title><%
Call Query2table ("select * from publishers where state< > ' NY '")
' Query all the cities in the table publishers that are not New York.
%>
<!--#include virtual= "/asptest/subdbtable.inc"--></body>
Using the process defined in the Subdbtable.inc file query2table, you can query the database very quickly, all you have to do is to "Conntemp.open" dsn=student;uid=student;pwd= Aspmagic the database name, user identity, and password in "", and enter the SQL query statement you want to use when calling Query2table. Is it simple? This is the magic of ASP and SQL!!!

Today, although we have used an entire length of space to learn only one SQL instruction, but please believe that you get far different from a DOS command, the SELECT command makes it easy for you to query the database, perhaps before you know nothing about the database query, but through this study, You have actually used ASP to do commonly used database queries, is not very excited? In the next article, the author will continue to introduce several other basic SQL instructions. Before concluding this article, the author is here to apologize to many friends of letters, due to the recent work of the author busy, really no one to answer your questions, please forgive me, I will try to some common, more frequent occurrence of problems in the article written out, as for some unusual problems, I suggest you to the following site to ask questions, You will receive a timely reply. Www.onlinechina.net/friend/flybird/bbs/wwwboard.asp?id=1, this is the best Chinese-language ASP learning site that the author has seen so far, which is hosted by the birds of Shanghai, we must go to see.