SQL statements and tutorials frequently used in ASP

1. SELECT statement
In the world of SQL, the most basic operation is the SELECT statement. When using SQL directly using database tools, many people will be familiar with the following operations:
　　

Copy codeThe Code is as follows: SELECT what FROM whichTable WHERE criteria

Execute the preceding statement to create a query that stores the result.
On the ASP page file, you can also use the above general syntax, but the situation is slightly different. during ASP programming, the content of the ELECT statement should be assigned to a variable as a string:
　　Copy codeThe Code is as follows: SQL = "SELECT what FROM whichTable WHERE criteria"

Now, I understand the way SQL is "spoken" in ASP. The next step is the method. As long as you meet your needs, the traditional SQL query mode and conditional query can be used.
For example, assume that your database has a data table named Products. Now you want to retrieve all the records in this table. Then you write the following code:Copy codeThe Code is as follows: SQL = "SELECT * FROM Products"

The preceding code -- SQL statement is used to retrieve all the data in the table. After execution, all records in the data table are selected. However, if you only want to retrieve a specific column from the table, such as p_name. Then you cannot use the * wildcard. enter the name of a specific column. The Code is as follows:
　　Copy codeThe Code is as follows: SQL = "SELECT p_name FROM Products"

After the above query is executed, all content in the p_name column in the Products table will be selected.
2. Set query conditions in the WHERE clause
For example, if you only want to retrieve the p_name record, and the names of these records must start with a letter w, you need to use the following WHERE clause:
　Copy codeThe Code is as follows: SQL = "SELECT p_name FROM Products WHERE p_name LIKE 'W % '"

The WHERE keyword is followed by the conditions used to filter data. With the help of these conditions, only data that meets certain criteria can be queried. In the preceding example, only the p_name record with the name of w is obtained.
In the preceding example, the percent sign (%) indicates that the query returns all records with w letters headers and any data or even no data. Therefore, when executing the preceding query, west and willow will be selected from the Products table and stored in the query.
As you can see, by carefully designing SELECT statements, you can limit the amount of information returned in recordset.
These are just the first steps to grasp the use of SQL. To help you gradually understand the usage of complex SELECT statements, let's take a look at the key standard terms: Comparison operators, these things are often used when you build your own SELECT string to obtain specific data.
WHERE clause Basics
When creating a WHERE clause, the simplest method is to use standard comparison symbols, which are <, <=,>, >=, <>, and =. Obviously, you will soon be able to understand the meaning and specific running results of the following code:
　　Copy codeThe Code is as follows: SELECT * FROM Products WHERE p_price> = 199.95
SELECT * FROM Products WHERE p_price <> 19.95
SELECT * FROM Products WHERE p_version = '4'

Note: here you will notice that the number 4 in the last example is enclosed in single quotes. The reason is that "4" in this example is the text type rather than the number type.
3. Comparison operators: LIKE, not like, and
The comparison operator specifies the content range of the data retrieved from the table. You can use them to create filters to narrow the scope of the recordset, so that they only store the information you are concerned about under a given task.
You have seen the LIKE usage in the example of getting the w-header record above. LIKE is a very useful symbol. However, in many cases, using it may bring you too much data, so before using it, you 'd better start thinking about what data you want. Assume that you want to retrieve the SKU number with five digits and start with "1" and end with "5", you can use the underscore (_) to replace the "%" symbol:
　　Copy codeThe Code is as follows: SQL = "SELECT * FROM Products WHERE p_sku LIKE '1 ___ 5 '"

The delimiter represents any character. Therefore, when you enter "110000_5", your search will be limited to a 5-digit range that meets the specific pattern.
If you want to do the opposite, you need to find all SKU entries that do not match the "12.16_5" mode. Then you only need to add NOT before LIKE in the preceding statement example.
BETWEEN
If you want to retrieve data within a certain range, and you know the start and end of the range in advance, you may wish to use BETWEEN to determine the word. Now let's assume that you want to select a record with a range of 1 and 10 in a given table. You can use BETWEEN as follows:
... Where id between 1 AND 10
Or you can use the familiar mathematical judgment words:
... Where id> = 1 and id> = 10
4. Union statement
The SQL statements we have mentioned so far are relatively simple. If we can use standard recordset for loop query, these statements can also meet more complex requirements. However, why do we have to stick to the basic level of simplicity? You can add other symbols, such as AND, OR, and not, to complete more powerful functions.
The following SQL statement is used as an example:Copy codeThe Code is as follows: SQL = "SELECT c_firstname, c_lastname, c_email FROM customers WHERE c_email
Is not null and c_purchase = '1' OR c_purchase = '2' AND c_lastname LIKE 'a % '"

The preceding SQL statements do not clearly show how condition statements are attached to a single SQL statement.
Multi-line statements
When SQL statements are hard to understand, you may want to break down the entire statement into multiple lines of code, and then gradually add the components of the query statement based on the existing variables and store them in the same variable:Copy codeThe Code is as follows: SQL = "SELECT c_firstname, c_lastname, c_emailaddress, c_phone"
SQL = SQL & "FROM MERs"
SQL = SQL & "WHERE c_firstname LIKE 'a % 'and c_emailaddress NOT NULL"
SQL = SQL & "ORDER BY c_lastname, c_firstname"

In the last sentence, the SQL variable contains the following complete SELECT statement:Copy codeThe Code is as follows: "SELECT c_firstname, c_lastname, c_emailaddress, c_phone FROM MERs
WHERE c_firstname LIKE 'a % 'and c_emailaddress NO NULL ORDER
C_lastname, c_firstname"

The entire sentence is obviously much read after being decomposed! During debugging, you may be more comfortable typing a few more characters to better read the program. However, you must remember that you need to add spaces before or after the quotation marks are closed to ensure that the strings are not joined together.
5. Start execution.
After learning the construction and usage of the SELECT statement, you should learn how to use it. Under the database tool you have mastered, this may mean that you have to press a button that says "execute. On the ASP Webpage, you can immediately execute SQL statements or call them as stored procedures.
Once an SQL statement is created, you have to try to access its query results. Obviously, the key here is ASP recordset. To make full use of the SQL skills you are more familiar with, you need to adjust the recordset most commonly used on ASP Web pages:Copy codeThe Code is as follows: Dim rs
Set rs = Server. CreateObject ("ADODB. Recordset ")
Rs. Open SQL, Conn, 1, 2

Here, Conn is the database connection declaration, and the only modification is in rs. Open, and then replace the name of the data table to be queried with the variable containing the SQL statement.
One of the advantages of this method is that you can specify the cursor type (as shown in 1 and 2 ).
Execute SQL
You can also use a compact line of code to execute SQL statements to create A recordset. Syntax:Copy codeThe Code is as follows: Dim rs
Set rs = Conn. Execute (SQL)

In the preceding example, the SQL statement you see is the variable where you store your SQL SELECT statement. The code line "runs" an SQL statement (or queries the database), selects and stores the data in the recordset. In the preceding example, the rs variable is used. The main disadvantage of this method is that you cannot select the cursor type you want. On the contrary, recordset always opens with a forward cursor.
Because of the cursor, you may be familiar with the two methods for creating recordset. Directly executing a query saves the time required for typing characters, but you have to use the default cursor. This may cause frequent failures. No matter which method you adopt, the biggest difference between the two is no more than whether the code is refined or not. Without considering what fields you obtain and what your standards are, no matter how much data you store, SQL-based recordset is much smaller in size than the standard recordset opened on ASP, let alone the simplicity of the operation. After all, by filtering data, you eliminate the time-consuming if-then test and the loops that may be used.
6. Storage Query
When your query is relatively simple, it does not take much time to create an SQL statement from the beginning. However, complicated queries are different. Every time you create an SQL statement from the beginning, many development errors will occur. Therefore, once the SQL statements run smoothly, you 'd better save them and call them as needed. In this way, even for a simple query, you can use the stored query statement at any time.
Assume that you have to submit a report to the team every week to point out the existing business support problems. The data needs to be selected from your database and recorded by date, at the same time, it is sorted by the categories of support questions adopted by your team. Once you have designed this query, why do you write it again every week? Do not create a query on your HTML page. You should use your database tool to create a query and save it. Then you can use the ActiveCommand attribute to insert queries to your ASP Webpage. The first one or two times you may feel boring, but there are just a few lines of code:Copy codeThe Code is as follows: Set objSQ = Server. CreateObject ("ADODB. Command ")
ObjSQ. ActiveConnection = "databaseName"
ObjSQ. CommandText = "storedQueryName"
ObjSQ. CommandType = adw.storedproc
Set objRec = objSQ. Execute

Note that using adCmdStoredProc indicates that you have included the adovbs. inc file on the page. This file defines the Access constant that you can Access by name rather than number. You only need to include the file on the page), and then you can use names such as adCmdStoredProc. In this way, you will be more likely to understand the meaning of the stored query in the future.
7. ORDER
Selecting records from an Access database is the most frustrating thing. They are input to the database in the order they are entered. Even if you use Sort By in the Access environment to change the record view, the record order in the data table has not changed.
If you are using ASP recordset to write records on the web page, you may know that the order of disorder is so painful. However, you may have to face this problem frequently, because there is no simple and convenient solution. Fortunately, order by can simplify this problem.
To sort your results, you only need to add order by at the end of the SELECT statement and specify the reference columns to be sorted. Therefore, if you want to sort the MERs table by the customer's last name, you can write the following query statement:
　　Copy codeThe Code is as follows: SQL = "SELECT c_lastname, c_firstname, c_email FROM Customers ORDER BY c_lastname"

In this way, as long as you create a recordset and start to write the results to the screen, you will see the data in alphabetical order.
8. Record statistics
It is not difficult to determine how many records exist in the database or determine how many records meet certain standards. If you use the correct cursor type, you can use the RecordCount attribute to obtain the number of records. Of course, you can also use recordset. However, there is a simpler way to use count (*) In your SELECT statement. The Code is as follows:Copy codeThe Code is as follows: SQL = "SELECT count (*) FROM MERs"

OrCopy codeThe Code is as follows: SQL = "SELECT count (*) FROM MERs WHERE c_lastname LIKE 'a % '"

For example, the following code selects some records and the total number of these records:Copy codeThe Code is as follows: SQL = "SELECT c_firstname, c_lastname, count (*) FROM MERs WHERE c_lastname LIKE 'a % '"

However, you cannot achieve your goals. Here, the "count" function is actually a set function, meaning that only the single row information is returned: To answer your question. The question for the 1st SELECT statements is "How many records are there in the customer table ?" The query returns a single value as a response, so it cannot be combined with your regular query. If you want to get other data, you need to use RecordCount.
In addition to "count", Aggregate functions include AVG, MIN, MAX, and SUM.
9. Connection
Anyone familiar with SQL and relational databases has met many connection types. In short, join combines the content of two tables into a virtual table or recordset. If the data table is effectively normalized, you may often select specific information from a table and then select association information from another table. To do this, you need a simple "equijoin )".