ASP Basic Tutorial: Database Query Language (2)

At the beginning of this article, I would like to thank some of my friends for their letters to me pointing out the mistakes in the previous articles. I wonder if you remember in the eighth article that I gave a simple page access counter made using application? A friend wrote to me, why did he not have any effect on the last count result when he changed the value of the counter starting variable NumVisits? At first I was puzzled, Let's recall this source program, as follows:

<%

Dim NumVisits

Numvisits=0

Application.Lock

Application ("NumVisits") = Application ("NumVisits") + 1

Application.UnLock

%>

Welcome to this page, you are the <%= application ("NumVisits")%> visitor of this page!

In this program, if you try to change the value of the numvisits by changing the values of the variables, it is absolutely impossible. Because it is not possible to change the value of a application with the value of a variable, the two are irrelevant. Therefore, the definition and assignment of variables are superfluous. So how exactly should we define an initial value for application ("NumVisits")? See the following revised program:

<%

If application ("NumVisits") < 999 Then

Application ("NumVisits") =999

End If

Application.Lock

Application ("NumVisits") =application ("NumVisits") +1

Application.UnLock

%>

Welcome to this page, you are the <%= application ("NumVisits")%> visitor of this page!

The 999 here is the counter initial value you want to set, so the problem is solved. I am very grateful to the surname Kang's friend pointed out this mistake, although this is only a very small loophole, but we in the process of common learning procedures to write procedures are very need of this rigorous and meticulous style, hope that friends in the future once found the error in the text immediately notify, I can also correct, thank you.

The SQL language can be divided into two parts: data definition language and data manipulation language, following the previous one we learned the data manipulation language in the Select statement, today the author will continue to give you a brief introduction of the remaining SQL statements.

SQL is a complete data processing language, not only for database queries, but also for data modifications and updates in the database, compared to the complexity of the SELECT statement that supports SQL queries, the SQL statement that changes the database content is particularly straightforward. For a DBMS, however, the risk of data updates is far greater than the data query. The database management system must protect the consistency of the stored data during the change period, ensure that valid data goes into the database, the database must be consistent, and the DBMS must coordinate concurrent updates of multiple users to ensure that users and their changes do not affect other users ' jobs. Shanghai Treatment Impotence Hospital}

The SQL statements used to modify the contents of the database have the following three main items:

1. Insert to add a new data row to a table

2. Delete to remove data rows from a table

3. Update to change data that already exists in the database

First, let's look at Insert usage:

Standard syntax:

INSERT into table_name

(col1, col2 ...)

VALUES (value1, value2 ...)

The following example joins the scholar as a new salesperson in table Salesreps

Insert into

Salesreps (Name,num,sales,hire_date,income)

VALUES (\\\ ' shusheng\\\ ', 9,10000,\\\ ' 23-feb-99\\\ ', 2000)

In this statement, the column Name column is separated by commas in parentheses, followed by a comma-separated column of data in the Value phrase and parentheses, and it should be noted that the order of the data and column names is the same, and if the string type is separated by single quotation marks. Conceptually, an INSERT statement establishes a row of data that is consistent with the structure of a table column, populates it with data from the Values clause, and then joins the new row into the table, where the rows in the table are unordered, so there is no concept of inserting the row between the head or tail or two rows of the table. When the Insert statement ends, the new row is part of the table.

The INSERT statement can also add multiple rows of data to the target table, in which case the data value of the new row is not explicitly specified in the body of the statement, but rather a database query specified in the statement. The added value comes from the rows of the database itself, which may seem strange at first glance, but it is very useful in certain States. For example, if you want to copy the order number, date, and number from the order table to another table named Oldorder that arose before December 30, 1998, the multiline Insert statement provides a compact and efficient way to copy the data, as follows:

Insert into Oldorder (Num,date,amount)

Select Num,date,amount

From order

Where date<\\\ ' 30-12-98\\\ '

This statement looks a bit complicated, but it's simple, the statement identifies the table Oldorder and the columns that receive the data, exactly like a single-line Insert statement. The remainder of the statement is a query that retrieves the data in the order table. SQL executes the query on the order table first, and then inserts the query results row by line into the Oldorder table.

Let's look at the usage of update, which updates the values of one or more columns of the selected row in a single table. The target table to be updated is defined in the statement, and the SET clause specifies which columns to update and evaluates their values. The UPDATE statement always contains a where statement, and the UPDATE statement is dangerous, so you must explicitly recognize the importance of the where statement, which is used to specify the rows that need to be updated.

Standard syntax:

UPDATE table_name

SET columnname1 = value1

[, columname2 = value2] ...

WHERE search_condition

The following is an example of a simple Update statement:

Update Customers

Set credit=100000.00,id=99

Where name=\\\ ' asp\\\ '

In this example we update the credit value of the customer named ASP in table customers to 100,000 and change his ID to 99. Look again at the following example:

Update Customers

Set credit=200000.00,state=021

Where ID in (80,90,100,120)

As we can see, the process of SQL processing the UPDATE statement is to iterate through the specified table row by line, update the rows whose search criteria result is true, and skip the rows whose search criteria result is false or empty.

Finally, take a look at the Delete statement.

Standard syntax:

DELETE from TableName

WHERE condition

Because it is too simple, the consequences are also serious, although the WHERE clause is optional, but it almost always exists, if you omit the WHERE clause from the DELETE statement, all rows of the target table will be deleted. Look at the following example:

Delete from Order Where id=99

At the end of the article, the author gives you a brief introduction to the data definition language. It is a statement used to create and modify the structure of a database, including create and DROP statements.

1. Create statement

Standard syntax:

CREATE TABLE table_name

(field1 datatype [not NULL],

Field2 datatype [not NULL],

FIELD3 datatype [not NULL] ...)

Such as:

CREATE TABLE BILLS

(NAME CHAR (30),

AMOUNT number,

ACCOUNT_ID number)

Although the Create Table is more difficult to understand than the previous statements, it is still intuitive. It assigns bills to a new table and specifies the names and data types of the three columns in the table. Once the table is set up, we can add the data. Such as:

Insert into Bills (name,amout,account_id) VALUES (\\\ ' gates\\\ ', 100, 1)

If you feel you no longer need to save the product information, you can use the DROP TABLE statement to remove the table and all of the data it contains from the database.

Standard syntax:

DROP TABLE table_name

At this point, we have learned all the common SQL statements, do not underestimate these seemingly English simple statements, they are very powerful, when we write ASP program must use them to operate the database. From the next chapter, the author will give you an introduction to ASP's built-in ActiveX components, so stay tuned.

ASP Basic Tutorial: Database Query Language (2)