ASP Tutorial: 13th database Query Language (2)

At the beginning of this article, I would like to thank some friends for their letters to me pointing out the mistakes in the previous articles. I do not know if you remember in the eighth article I gave a simple use of application produced by the page access counters? A friend of mine asked me why, when he changed the value of the counter starting variable numvisits, did it have no effect on the final count result? At first I was puzzled, let's recall the source program as follows:
<%
Dim NumVisits
Numvisits=0
Application.Lock
Application ("NumVisits") = Application ("NumVisits") + 1
Application.UnLock
%>
Welcome to this page, you are the first <%= application ("NumVisits")%> visitors! In this program, if you are trying to change the value of the variable numvisits the result of the register, it is absolutely impossible. Because it is not possible to change the value of the application with the value of the 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")? Please see the following revised procedure:
<%
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 first <%= application ("NumVisits")%> visitors!

Here's 999 is the counter you want to set the initial value, so the problem will be solved. I am very grateful to the friend surnamed Kang for pointing out this mistake to me, although this is only a very small loophole, but we in the process of common program to write procedures, we really need this kind of rigorous and meticulous style, hope that in the future when friends find the text of the error in the letter immediately informed, I can also timely correction, thank you.

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

SQL is a complete data processing language, not only for database query, but also for data modification and update in the database, compared with the complexity of the SELECT statement that supports SQL query, it is very simple to change the SQL statement of database content. However, for a DBMS, the risk of data update is far beyond the data query. The database management system must protect the consistency of the stored data during the change period, ensure that valid data is entered into the database, the database must be consistent, and the DBMS must coordinate concurrent updates for multiple users to ensure that users and their changes do not affect the jobs of other users.
The following three SQL statements are used to modify the contents of a database:

1, insert, add new data rows to a table

2, delete, remove data rows from a table

3, Update, change the data already existing in the database

First let's look at the use of Insert:

Standard syntax:
INSERT into table_name
(col1, col2 ...)
VALUES (value1, value2 ...)

The following example will be the scholar as a new salesman into the table Salesreps
Insert into
Salesreps (Name,num,sales,hire_date,income)
VALUES (' Shusheng ', 9,10000, ' 23-feb-99 ', 2000)

In this statement, the column names are separated by commas in parentheses, followed by each column of data separated by commas in the Value phrase and parentheses, noting that the order of data and column names is the same, and that strings are separated by single quotes. Conceptually, an Insert statement establishes a row of data that is consistent with the table-column structure, fills it with data taken from the Values clause, and then joins the new row in the table, and the rows in the table are not sorted, so there is no such notion of inserting the row between the head or tail of the table or the two rows. After the Insert statement is finished, the new row is part of the table.

The INSERT statement can also add multiline data to the destination table, in which the data value of the new row is not specified explicitly in the body of the statement, but is a database query specified in the statement. It may seem strange to add values from the rows of the database itself, but this is useful in some specific state. For example, if you want to copy the order number, date, and number of orders that were generated before December 30, 1998 from the ordering table to another table named Oldorder, a multiline Insert statement provides a compact and efficient way to copy 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 that receives the new row and the column that receives 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 into the Oldorder table line by row.

Let's look at the usage of update, which is used to update the values of one or more columns of a 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 calculates their values. The UPDATE statement always contains the 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. Then look at the following example:
Update Customers
Set credit=200000.00,state=021
Where ID in (80,90,100,120)

We can see that the process of SQL processing update statements is to iterate through the specified table, 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 serious, and although the WHERE clause is optional, it almost always exists, and if the WHERE clause is omitted from the DELETE statement, all rows of the target table are 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 statement described earlier, 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 established, we can add the data. Such as:

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

If you feel that you no longer need to save product information, you can delete the table and all the data it contains from the database by using the Drop table statement.

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 programs must use them to operate the database. From the beginning of the next, the author will give you an introduction to ASP's built-in ActiveX components, please pay attention.