Introduction to ASP Basics: 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 page access counters? A friend of mine asked me why when he changed the counter start variable

NumVisits

Does not have any 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")

%> Guest!

In this program, if you try to change the variable numvisits

Value to change the result of the register, it is absolutely impossible. Because the value of the variable is used to change

Application

The value is not possible, and the two are irrelevant. Therefore, the definition and assignment of variables are superfluous. So how exactly are we supposed to give

Application ("NumVisits")

Define an initial value? 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")

%> Guest!

Here's 999

is the initial value of the counter you want to set, so the problem is 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.

Sql

The language can be divided into two parts: data definition language and data manipulation language, and then we learned the language of data manipulation

After the Select statement, today's author will continue to give you a brief description of the remaining SQL statements.

Sql

is a kind of complete data processing language, not only for database query, but also for data modification and update in database, and support

The complexity of the Select statement of the SQL query compares the SQL of the database content changes

Statement is exceptionally simple. However, for a DBMS

, the risk of data updates is far beyond the data query. The database management system must protect the consistency of the stored data during the change period, ensure the effective data enters the database, and the database must be consistent.

Dbms

You must also 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 name of the column is separated by commas in parentheses, followed by the Value

For each column of data that is also separated by commas in the phrase and parentheses, it should be noted that the order of the data and column names is the same, and if the string is separated by single quotes. Conceptually speaking,

An Insert statement establishes a row of data that is consistent with the table column structure, taken from Values

clause, and then add the new row to 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 multiple rows of data to the target table, in this form

Insert

statement, 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, do you want to

Order number, date, and number of orders generated before December 30, 1998

Table to another table named Oldorder, multiple rows 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 that receives the new row

Oldorder and receiving data columns, exactly like a single row Insert

Statement. The remainder of the statement is a query that retrieves the data in the order table. Sql

Execute a query on the order table, and then insert the query results line by row into the Oldorder

Table.

Let's look at the use of update, update

Statement 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.

The SET clause specifies which columns to update and calculates their values. The Update statement always contains

Where statement, and the Update statement is more dangerous, you must explicitly recognize where

Statement, where statements are 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 will table customers the customer name ASP

The customer's credit value is updated to 100,000 and his ID is changed 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 find that in fact SQL processing Update

The process of a statement is to iterate through the specified table, to update the rows whose search criteria result is true, and to skip 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, so the consequences are serious, although the

The WHERE clause is optional, but it almost always exists, if the WHERE clause is 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 that is used to create and modify the structure of the 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 Create Table

It's more difficult to understand than the previous statement, but it's still intuitive. It will bills

Assigns 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 use the Drop table

Statement deletes the table and all the data it contains from the database.

Standard syntax:

DROP TABLE table_name

Now that we've learned all the common SQL

Statements, do not underestimate these simple words that seem to be English, they are very powerful, when we write

Asp

Programs must use them to manipulate the database.