The original plan will introduce the ASP built-in ActiveX component from this article, but considering that we will come into contact with a large number of database queries in the future learning, the author temporarily decides to spend one to two paragraphs Everyone briefly introduces the basic knowledge of some database query languages, which is actually a knowledge that I must master the ASP. Whether it is possible to flexibly use a database query language, it will be directly related to the execution efficiency of the ASP program, so you must pay attention to it. I believe many friends have heard of SQL this name. If you are a computer expert, SQL's big name must be like a thunder. So what is SQL exactly? The word SQL is actually an abbreviation for the "Structure Query Language" structural query language, is a tool for organizing, managing, and retrieving data stored in a computer database; it is a specific type of database-relational database . Computer programs that control this database are the DBMS-database management system that we often say. Such as: SQL Server, Oracle, Sybase, DB2, etc. When the user wants to retrieve data in the database, the request is issued via the SQL language, then DBMS processes the SQL request and retrieves the required data, and finally returns it to the user, this process is called a database query, this is also It 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 condition testing, nor does it use the GOTO statement for program branches and cyclic statements for or do. Specifically, SQL is a database sub-language, and the SQL statement can be embedded in another language, thereby having data inventory. SQL is also non-stringent structural language, its syntax is closer to the English statement, so it is easy to understand, most SQL statements are interested in it, read as a natural language. SQL is also an interactive query language that allows users to directly query storage data, using this interaction characteristic, users can answer fairly complex issues in a very short time, and the same problem If the programmer will write the corresponding report programs may be It takes a few weeks or even longer. In most ASP applications, we will come into contact with the database, and we used the standard syntax used to perform database operations when writing ASP applications, so the importance of SQL syntax is self-evident. Below, we will start from the most common SQL statement SELECT, and learn SQL step by step. The query is the core of the SQL language, and the SELECT statement used to express the SQL query is the strongest SQL statement, which retrieves data from the database and supplies the query results to the user. In this article we will establish a simple database called Tianjiao, which stores a sales record table called Sales, as shown below:
Name Gender Salary Sales Target Sale Area Subject Male 250080009000 Shanghai Thunder Men 2000800010000 Sichuan Snow Children 250050006000 Guangzhou Gu Yi 260090009800 Dalian Dalian Dalian 200040004000 Tianjin 天 男 40002000020000
In this table, there are six columns: name, gender, salary, sales target, sales, region, first we use the SELECT statement to list name, sales goals and sales: SELECT name, sales target, sales from Sales results are as follows:
Name sales target sales book students 80009000 Wu Champions 100009999 Thunder 800010000 Xueer 50006000 Gu Yixia 90009800 Android 40004000 天 2000020000
Then we list all the names, sales objectives and sales: SELECT name, sales target, sales from sales where gender = "Male" results: name sales target sales books 80009000 Wu Champions 10000999 Thunder 800010000 Gu Yi 90009800 天 2000020000
Next, we have a relatively complex query, list all the sales targets, sales targets and sales of sales greater than the sales target, and sort by sales target. SELECT Name, Sales Target, Sales Form Sales Where Sales> Sales Targets and Gender = "Men" ORDER BY Sales Target Results As follows:
Name sales target sales books 80009000 Thunder 800010000 Gu Ye 90009800 天 2000020000
12
Everyone can see that for simple queries, SQL SELECT statements and English syntax are very similar, let's analyze the complete format of the SELECT statement, which includes six clauses, where SELECT and FROM clauses are necessary, other clauses can be The function of each clause is as follows:
1. SELECT clause lists all data items that require SELECT statements. It placed at the beginning of the SELECT statement, specifying the data item to be retrieved. These data items typically use the selection table, ie, a set of selection options. According to the order from left to right, a column generated by each selection item, a selection may be the following items:
(1), the column name: Identify the "Column in the table in the FROM clause. If the column name is used as the selection, SQL removes the value of the column directly from each row in the database table, and then places it in the corresponding line of the query result.
(2), constant: Specifies that this value is placed in each row of query results.
(3), SQL expression: Description Must be calculated according to the specified value in the query result.
2, the FROM clause lists the tables containing the data you want to query, which consists of a set of comma-separated table names by keyword from. Each indicates that it represents a table that includes the query to retrieve data. These tables are the table sources of this SQL statement because the query results are from them.
3, the WHERE clause tells SQL only to query the data in some rows, these rows search criteria descriptions.
4, the Group By clause specifies the summary query, ie not generating a query result for each row, but a similar row is packet, and a summary result is generated for each group.
5, the Having clause tells SQL only generates the result of some groups obtained by Group By, as the WHERE clause, the group you need is also specified in a search condition.
6. The ORDER BY clause sorted the query results in one or more columns. If this clause is omitted, the query result will be disorderly.
The following will provide a simple but practical ASP program to use the SQL statement query for your reference.
In order to make everyone more clearly understand the application of SQL syntax in the ASP, we first write all the cores of the query into query2Table, then call the SUB using the ASP server-side inclusion function. Please scrapped the following statement to the Notepad, saved as subdbtable.inc file and placed in a 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
%>
| <% = RSTEMP (i) .Name%>
<% next%> <% Do While Not Rstemp.eof %> |
| <% = thisvalue%>
<% next%> <% RSTEMP.MOVENEXT LOOP%> <% Rstemp.close Set Rstemp = Nothing Conntemp.close SET CONNTEMP = Nothingend Sub%> Complete the Sub's definition process, in the following ASP programs, we can easily get the query results for the SQL query statement you want to use and call the process. Save the following four codes to Asp11A.asp, asp11b.asp, asp11c.asp, asp11d.asp, four .asp files. |
