Asp SQL statement usage page 1/3

SQL statement tutorial in ASP
There are so many SQL products that you may have to worry about. However, if you want to use ASP and SQL at the same time, you may feel dizzy. MySQL, SQL Server, and msql are both excellent SQL tools. Unfortunately, in the ASP environment, you do not need them to create practical SQL statements. However, you can use your access knowledge and corresponding access skills, coupled with our tips and skills, I believe you will be able to successfully add SQL to your asp web page.

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:
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 SELECT statement should be assigned to a variable as a string:
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 followingCode:
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:
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. Use the WHERE clause to set query Conditions

Sometimes retrieving all the database records may meet your requirements. However, in most cases, we only need to obtain some records. In this way, how can we design a query? Of course, it will be a little more time-consuming. Besides, this article does not want you to use the recordset.

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:
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:
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. Because you will put the SELECT statement in quotation marks and assign it to the variable as a value, you can also use quotation marks in the statement.

Comparison Operators
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.

3. Like, not like, and

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:
SQL = "select * from products where p_sku like '1 ___ 5 '"

The delimiter represents any character. Therefore, when "1 _ 5" is entered, your search will be limited to a 5-digit range that meets the specific mode.

If you want to do the opposite, you need to find all SKU entries that do not match the "1 _ 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:
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 % '"

As far as your current knowledge of SQL is concerned, the above examples are not difficult to explain, but the above statements do not clearly show you how condition statements are glued into 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:
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:
"Select c_firstname, c_lastname, c_emailaddress, c_phone from MERs
Where c_firstname like 'a % 'and c_emailaddress no null order by 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 charactersProgramBetter read. 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.