At the beginning of this article, I would like to thank some friends for writing me to point out the errors in the previous articles. I wonder if you remember that in the eighth article I mentioned a simple page access counter made using Application? A friend wrote to me why he did not have any effect on the final count result after changing 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 on this page!
In this program, it is absolutely impossible to change the counter result by changing the value of the variable NumVisits. Because it is not possible to change the Application value with the value of a variable, the two are irrelevant. Therefore, the definition and assignment of variables are redundant here. So how should we define an initial value for Application(NumVisits)? Please see the following revised procedures:
< %
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 on this page!
The 999 here is the initial value of the counter you want to set, so the problem will be solved. I am very grateful to this friend named Kang for pointing out this mistake to me. Although this is just a small loophole, we need this rigorous and meticulous style in the process of learning and writing programs together. I hope that my friends will come in the future Once you find any errors in the article, I will immediately send a letter to inform you, and I can correct them in time. Thank you.
SQL language can be divided into two parts: data definition language and data manipulation language. After we learned the Select statement in the data manipulation language in the previous article, today the author will continue to briefly introduce the remaining SQL statements to you.
SQL is a complete data processing language, not only used for database queries, but also for data modification and update in the database. Compared with the complexity of Select statements that support SQL queries, SQL statements that change database contents are particularly simple. However, for a DBMS, the risk caused by data updates is much greater than data query. The database management system must protect the consistency of stored data during the change period to ensure that effective data enters the database, the database must remain consistent, and the DBMS must also coordinate parallel updates of multiple users to ensure that users and their changes do not affect them. Jobs for other users.
There are three main SQL statements used to modify database content:
1. Insert, add a new data row to a table
2. Delete, delete data rows from a table
3. Update, change the data that already exists in the database
First, let’s take a look at the usage of Insert:
Standard syntax:
INSERT INTO table_name
(col1, col2...)
VALUES(value1, value2...)
The following example is to add the scholar as a new salesperson to 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 brackets, followed by each column of data separated by commas in the Value phrase and brackets. It should be noted that the order of the data and column names is the same, and If it is a string type, it is separated by single quotes. Conceptually speaking, an Insert statement creates a row of data that is consistent with the table column structure, fills it with data taken from the Values clause, and then adds the new row to the table. The rows in the table are not sorted. Therefore, there is no concept of inserting that row into the head or tail of a table or between 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 of Insert statement, the data value of the new row is not explicitly specified in the statement body, but a database query specified in the statement. The added value comes from the rows of the database itself, which may seem strange at first, but in certain states, this is very useful. For example, if you want to copy the order number, date and number generated before December 30, 1998 from the order table to another table called oldorder, the multi-row Insert statement provides a compact and efficient way to copy the data. The method is as follows:
Insert into older order (Num,Date,Amount)
Select Num,Date,Amount
From order
Where Date<'30-12-98'
This statement looks a bit complicated, but it is actually very simple. The statement identifies the oldorder table that receives new rows and the column that receives data, which is completely similar to a single row Insert statement. The remainder of the statement is a query that retrieves data from the order table. SQL first executes a query to the order table, and then inserts the query results row by row into the oldorder table.
Let's take a look at the usage of Update. The 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, and the Set clause specifies which columns to update and calculates their values. Update statements always contain Where statements, and Update statements are dangerous, so you must clearly recognize the importance of Where statements, which are used to specify rows that need to be updated.
Standard syntax:
UPDATE table_name
SET columnname1 = value1
[, columnname2 = value2]...
WHERE search_condition
Here 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 client named asp in the table customers to 100,000 and change his ID to 99. Let's 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, the process of SQL processing Update statements is to traverse the specified table line by line, update the rows whose search condition results are true, and skip rows whose search condition results are false or empty.
Finally, let’s 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, it almost always exists. If the Where clause is omitted from the Delete statement, all the target table is in the All rows will be deleted. See the following example:
Delete from order Where ID=99
At the end of the article, the author is briefly introducing the data definition language to you. It is a statement used to create and modify database structures, 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 ]...)
like:
CREATE TABLE BILLS
(NAME CHAR(30),
AMOUNT NUMBER,
ACCOUNT_ID NUMBER)
Although Create Table is a bit harder to understand than the previous statement, it is still very intuitive. It assigns bills to a new table and specifies the name and data type of the three columns in the table. After the table is created, we can add data. like:
Insert into bills(name,amout,account_id) values('gates',100,1)
If you feel you no longer need to save product information, you can use the Drop table statement to delete the table and all the data it contains from the database.
Standard syntax:
DROP TABLE table_name
So far, we have learned about all the commonly used SQL statements. Don't underestimate these simple statements that seem to be in English. They are very powerful and must be used to operate the database when we write an asp program. Starting from the next article, the author will introduce to you the built-in ActiveX components of ASP, please stay tuned.