1,select Statement
In the SQL world, the most fundamental operation is the SELECT statement. Many people will be familiar with the following when using SQL directly under Database Tools:
Copy Code code as follows:
SELECT What from whichtable WHERE criteria
Executing the above statement creates a query that holds its results.
On the ASP page file, you can also use the above general syntax, but the situation is slightly different, ASP programming, the content of the elect statement to be assigned to a variable as a string:
Copy Code code as follows:
SQL = "Select what from whichtable WHERE criteria"
OK, understand the ASP under the SQL "speak" way, then follow the same pattern, as long as you meet your needs, the traditional SQL query mode and conditional query can be useful.
For example, suppose you have a data table in your database, the name is products, and now you want to take out all the records in the list. Then you write the following code:
Copy Code code as follows:
SQL = "SELECT * FROM Products"
The function of the above code--SQL statement is to take out all the data in the table--after execution, all the records in the datasheet will be selected. However, if you only want to remove a specific column from the table, such as P_name. Then you can't use the * wildcard character, you have to type the name of a specific column, the code is as follows:
Copy Code code as follows:
SQL = "Select P_name from Products"
After executing the above query, the contents of the Products table and the P_name column will be all selected.
2,where clause set query criteria
For example, if you're only going to take out P_name records, and the names of those records must start with the letter W, you'll need to use the following WHERE clause:
Copy Code code as follows:
SQL = "Select P_name from the products WHERE p_name like ' w% '"
Where keywords are followed by the conditions used to filter the data, and with the help of these conditions, only data that satisfies a certain standard will be queried. In the above example, the result of the query will only get the P_name record with the first name in W.
In the example above, the percent sign (%) means that the query returns a record entry that starts with all the W letters and is followed by any data or even no data. Therefore, in the execution of the above query, West and Willow will be selected from the Products table and stored in the query.
As you can see, as long as you carefully design the SELECT statement, you will be able to limit the amount of information returned in the recordset, and think about how it always satisfies your requirements.
These are just the beginning of mastering the SQL use. To help you learn more about the use of complex SELECT statements, let's take a look at the key standard terms: comparison operators, which are often used when you build your own select string to get specific data.
WHERE clause basics
The simplest way to start creating a WHERE clause is to use the standard comparison notation, which is <, <=, >, >=, <>, and =. Obviously, you will soon be able to understand the meaning of the following code and the specific results of the operation:
Copy Code code as follows:
SELECT * FROM Products WHERE P_price >= 199.95
SELECT * FROM Products WHERE P_price <> 19.95
SELECT * from the products WHERE p_version = ' 4 '
Note: Here you will notice that the number 4 in the last example is surrounded by single quotes. The reason for this is that, in this example, "4" is a literal type rather than a numeric type.
3, Comparison operators: Like, don't, and BETWEEN
The comparison operator specifies the range of content from which the data is fetched. You can use them to create filters to narrow the recordset so that it saves only the information you care about under a given task.
You've seen the use of like in the example above where you've taken the W-heading record. The like decision word is a very useful symbol. However, in many cases it may give you too much data, so it's a good idea to think about what data you want to get before you use it. Suppose you want to take out a 5-digit SKU number, and it starts with 1 at the end of 5, you can use the caret (_) instead of the% symbol:
Copy Code code as follows:
SQL = "SELECT * WHERE p_sku like ' 1___5 '"
The following stroke denotes any one character. So in the case of entering "1___5", your search will be limited to a 5-digit range that satisfies a particular pattern.
If you want to do the opposite, find all SKU entries that do not match the "1___5" pattern. Then you just need to add not to the like in the example in the statement just now.
BETWEEN
If you want to take out a range of data, and you know the starting and ending points of the range, then you might as well use a between judgment word. Now let's assume that you want to select a record within a given table that ranges between 1 and 10. You can use between as follows:
... WHERE ID BETWEEN 1 and 10
Or you can use the already familiar mathematical judgment words:
... WHERE ID >= 1 and ID >= 10
4, joint statement
The SQL statements we've talked about so far are relatively simple, and if you can go through a standard recordset loop query, these statements can also meet some more complex requirements. But why do we have to stick to the basic standards of taste? You can add additional symbols, such as and, or, and not to accomplish more powerful functions.
Take the following SQL statement as an example:
Copy Code code as follows:
SQL = "Select C_firstname,c_lastname,c_email from Customers WHERE C_email
Is isn't NULL and c_purchase = ' 1 ' OR c_purchase = ' 2 ' and c_lastname like ' a% '
Your current knowledge of SQL is not difficult to explain, but the above statement does not make it clear how the conditional words are glued to a single SQL statement.
Multi-line statement
When the SQL statement is not understood, you might as well break the whole statement into multiple lines of code, and then incrementally add the parts of the query statement to the same variable based on the existing variables:
Copy Code code as follows:
SQL = "Select C_firstname,c_lastname,c_emailaddress,c_phone"
sql = SQL & "from customers"
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 Code code as follows:
"Select C_firstname, C_lastname, C_emailaddress, C_phone from Customers
WHERE c_firstname like ' a% ' and c_emailaddress NO NULL ORDER BY
C_lastname,c_firstname "
The whole sentence according to the decomposition after the obvious good read much! When you are debugging, you may be more willing to knock a few more characters to change the program to read better. Keep in mind, however, that you need to add more space before closing the quotes or after opening the quotes so that you don't get a few words together when the strings are connected.
5, start execution
After learning the structure and use of a SELECT statement, you should learn how to use it. Under the database tools you have, this may mean that you have to press a button that says "execute." On an ASP Web page, you can execute an SQL statement immediately or as a stored procedure call.
Once you have created the SQL statement, you must also try to access its query results. Obviously, the key here is the ASP recordset. To make the most of your familiar SQL skills, you need to adjust the most commonly used recordset on a regular ASP page:
Copy Code code 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 replaces the name of the data table you want to query with a variable that contains the SQL statement.
One of the advantages of this approach is that you can specify the cursor type (as shown in 1, 2 above).
Execute SQL
You can also use a compact line of code to execute SQL statements to create a recordset. Here's the syntax:
Copy Code code as follows:
Dim RS
Set rs = Conn.execute (SQL)
In the example above, the SQL you see is the variable that holds your own SQL SELECT statement. The code line "runs" the SQL statement (or queries the database), selects the data and stores the data in the recordset, in the example above, the variable Rs. The main disadvantage of this approach is that you can't choose the type of cursor you want to use. Instead, the recordset is always opened with a forward cursor.
Because of the cursor, you might want to familiarize yourself with two methods of creating a recordset. Executing a query directly saves the time it takes to type a character, but in that case you have to use the default cursor, which is likely to encounter problems that are often not functioning properly. Whichever way you use it, the biggest difference between the two is nothing more than code refinement. Without considering what fields you get, what your criteria are, and how you store the data, the SQL Recordset will be much smaller in volume than the standard recordset opened on the ASP, let alone the simplicity of the operation. After all, by filtering the data, you eliminate time-consuming if-then tests and possible loops.
6, store the query
When your query is relatively simple, it doesn't take much time to create SQL statements from scratch, but complex queries are different, and there are many development errors from scratch each time. So, once you have SQL running smoothly, you'd better save them and call them when you need them. In this way, even a simple query you can always use the stored query statement.
Let's say you have a weekly report to the team, pointing out the current business support issues that need to be selected from your database, and to select records by date, and sort by the category of support issues that your team uses. Once you have designed this query, why should you rewrite it every week? Instead of creating a query on your HTML page, you should create a query with your database tools and save it. You can then insert the query into your ASP page using the ActiveCommand attribute. The first one or two of the time you might think it's no fun, but it's just a few lines of code:
Copy Code code as follows:
Set objsq = Server.CreateObject ("Adodb.command")
Objsq.activeconnection = "DatabaseName"
Objsq.commandtext = "Storedqueryname"
Objsq.commandtype = adCmdStoredProc
Set Objrec = Objsq.execute
Note that using adCmdStoredProc means that you have included Adovbs.inc files on the page. This file defines the access constants that you can access by name rather than by number. You just need to include the file on the page, and then you can use a name like adCmdStoredProc. This will make it easier to understand what the above stored query means in the future when you see it.
7,order by
Selecting records from an Access database is one of the most frustrating things, in what order they are entered in the database. Even if you use sort by to change the view of a record in an access environment, the order of records in the datasheet does not change.
If you're using an ASP recordset to write a record on a Web page, you probably know how painful the order of jumbled is. But you may have to face the problem often, because there is no simple and easy solution. The good news is that the order by can simplify the puzzle.
To sort your results, simply add an order by to the end of the SELECT statement, and then specify the reference columns you want to sort. Therefore, if you want to sort the Customers table according to the customer's last name, you can write the following query statement:
Copy Code code as follows:
SQL = "Select C_lastname,c_firstname,c_email from Customers order by C_lastname"
So, as soon as you set up the recordset and start writing the results to the screen, you'll see that the data is sorted in alphabetical order.
8. Record Statistics
It is not difficult to determine how many records are in a database, or how many records meet certain standards. If you use the correct cursor type, you can use the RecordCount property to get the number of records, of course, with the recordset. But there's a simpler way to do this is to take count (*) in your own SELECT statement, and the code looks like this:
Copy Code code as follows:
SQL = "SELECT count (*) from Customers"
Or
Copy Code code as follows:
SQL = "SELECT count (*) from Customers WHERE c_lastname like ' a% '"
For example, the following code selects some records and the total number of these records:
Copy Code code as follows:
SQL = "Select C_firstname, C_lastname, COUNT (*) from Customers WHERE c_lastname like ' a% '"
But you can't achieve your purpose. The "Count" function here is actually an aggregate function, meaning to return only one line of information: Answer the question you asked. For the 1th SELECT statement, the question 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 queries." If you want to get other data, you need to use RecordCount.
Aggregate functions include AVG, MIN, Max, and sum, in addition to count.
9. Connection
Anyone familiar with SQL and relational databases has encountered a large number of connection types. In the simplest sense, a join (join) combines the contents of two tables into a single virtual table or recordset. If the data table is effectively regulated, you may often select specific information from one table and then select the associated information from another table. This requires a simple "equivalent connection (equijoin)".
