13. Database Query Language 2

At the beginning of this article, I would like to thank some of my friends for writing to me and pointing out the mistakes in the previous articles. Do you remember that in article 8, I gave you a simple page access counter created using application? A friend wrote to me, why does it have no effect on the final count result when he changes the value of the counter's starting variable numvisits? At first, I was puzzled. Let's recall this source code, as shown below:
<%
Dim numvisits
Numvisits = 0
Application. Lock
Application ("numvisits") = Application ("numvisits") + 1
Application. Unlock
%>
Welcome to this page. You are the <% = Application ("numvisits") %> guest on this page! In this program, if you try to change the result of the counter by changing the value of the variable numvisits, it is absolutely impossible. Because it is not possible to change the application value by using the value of the variable, the two are irrelevant. So the definition and value assignment of variables are redundant here. So how should we define an initial value for application ("numvisits? See the following corrected 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 <% = Application ("numvisits") %> guest on this page!

The 999 value here is the initial counter value you want to set, so that the problem can be solved. I am very grateful to this friend surnamed Kang for pointing out this error. Although this is only a small vulnerability, however, we need such a rigorous and meticulous style in the process of learning program writing. I hope that in the future, once my friends find any mistakes in the text, they will immediately send a letter to us, so that I can correct them in time. Thank you.

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

SQL is a complete data processing language. It is used not only for database queries, but also for data modification and update in databases. Compared with the complexity of select statements that support SQL queries, changing the SQL statement of the database content is extremely simple. However, for a DBMS, the risk of data update is much higher than that of data query. The database management system must protect the consistency of the stored data during the change period to ensure effective data access to the database. The database must be consistent, and the DBMS must coordinate parallel updates by multiple users, to ensure that users and their changes do not affect other users' jobs.
The following SQL statements are used to modify the database content:

1. Insert: Add new data rows to a table

2. Delete: delete data rows from a table

3. Update: change existing data in the database

First, let's look at the insert usage:

Standard Syntax:
Insert into table_name
(Col1, col2 ...)
Values (value1, value2 ...)

The next example is to add the scholar as a new salesman to the salesreps table.
Insert
Salesreps (name, num, sales, hire_date, income)
Values ('shusheng', 9, 2000, '23-Feb-99)

In this statement, the column names are separated by commas (,) in parentheses, followed by data in each column separated by commas (,) in the value phrase and parentheses, it should be noted that the order of data and column names is the same, and if it is a string type, it is separated by single quotes. In terms of concept, the insert statement creates a data row consistent with the table column structure, fills it with data from the values clause, and then adds the new row to the table, the rows in the table are not sorted, so there is no such concept as inserting the row into the table's header, tail, or between two rows. After the insert statement ends, the new row is a part of the table.

The insert statement can also add multiple rows of data to the target table. In this form of insert statements, the data values of new rows are not explicitly specified in the statement body, it is a database query specified in the statement. The added value comes from the row of the database. It seems strange at first glance, but it is very useful in some specific States. For example, you want to copy the order number, date, and number generated before January 1, December 30, 1998 from the order table to another table named oldorder, multi-row insert statements provide a compact and efficient method for copying data, as follows:
Insert into oldorder (Num, date, amount)
Select num, date, amount
From Order
Where date <'30-12-98'

This statement looks a little complicated. In fact, it is very simple. The statement identifies the oldorder table that receives new rows and the columns that receive data. It is similar to a single row insert statement. The rest of the statement is a query that retrieves data in the order table. The SQL statement first queries the order table, and then inserts the query results row by row into the oldorder table.

Next let's take a look at the update usage. The update statement is used to update the values of one or more columns of the selected rows in a single table. The target table to be updated is defined in the statement. The set clause specifies the columns to be updated and calculates their values. The update statement always contains the where statement, and the update statement is dangerous. Therefore, you must be clear about the importance of the where statement. The where statement is used to specify the row 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 MERs
Set credit = 100000.00, id = 99
Where name = 'asp'

In this example, we update the credit value of the client named ASP in the customers table to 0.1 million and change its ID to 99. Let's look at the following example:
Update MERs
Set credit = 200000.00, state = 021
Where ID in (80, 90, 100,120)

We can find that, in fact, the SQL statement processing the update statement is to traverse the specified table row by row and update the row whose search condition result is "true, the row whose search condition result is "false" or "null" is skipped.

Finally, let's look at the delete statement.

Standard Syntax:
Delete from tablename
Where condition

Because it is too simple, the consequences are serious. Although the WHERE clause is optional, it almost always exists, if the WHERE clause is omitted from the delete statement, all rows in the target table will be deleted. See the following example:
Delete from order where id = 99

At the end of the article, I will give you a brief introduction to the Data Definition Language. It is a statement used to create and modify the database structure, including the 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]...)

For example:
Create Table bills
(Name char (30 ),
Amount number,
Account_id number)

Although the create table statement is more difficult to understand than the preceding statement, 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. After the table is created, we can add data. For example:

Insert into bills (name, amout, account_id) values ('gates)

If you no longer need to save product information, you can use the drop TABLE statement to delete the table and all its data 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 look like English. They are very powerful, when writing ASP programs, you must use them to operate databases. Starting from the next article, the author will introduce ASP's built-in ActiveX components.