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 that is used to express SQL queries A statement is the most powerful and complex SQL statement that retrieves data from a database and provides the results of the query 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

In this table, there are six columns, six fields: 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 sale results 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 gender = "Male" results 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 Sale > Sales target and gender = "male" ORDER by sales target results 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 functions: 1, Selec The T clause lists all the data items that are required to be retrieved by the SELECT statement. 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 Left-to-right order, the query results for one column produced by each selection 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. Please 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.create OBject ("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 database%> < table border=1>< tr> <% fo R i=0 to Howmanyfields%> < td>< b><%=rstemp (i) .name%></b></td> <% next%&gt 　　; </tr> <% do rstemp.eof%> < tr> <% for i = 0 to Howmanyfields thisvalue=rste 　　MP (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%& 　　Gt </tr> <%rstemp.movenext loop%> </table> <% Rstemp.close Set rstemp=nothing conntemp The. Close set Conntemp=nothingend sub%> completes the definition of the sub, and in the following ASP programs we simply add the SQL query statement that we want to use and call the process to get the results of the query very conveniently. 　　Save the following four snippets of code as asp11a.asp, asp11b.asp, asp11c.asp, asp11d.asp Four. asp files. < head>< title>asp11a.asp</title>