SQL Data Operations Basics (intermediate) 8

Data Update record

To modify one or more records that already exist in a table, use the SQL UPDATE statement. As with the DELETE statement, the UPDATE statement can use the WHERE clause to choose to update a particular record. Take a look at this example:

UPDATE mytable SET first_column= ' updated! ' WHERE second_column= ' Update me! '

This UPDATE statement updates the value of all Second_column fields to ' Update me! ' 's record. For all selected records, the value of the field First_column is set to ' updated! '.

The following is the complete syntax for the UPDATE statement:

UPDATE {table_name|view_name} SET [{table_name|view_name}]

{Column_list|variable_list|variable_and_column_list}

[, {Column_list2|variable_list2|variable_and_column_list2} ...

[, {COLUMN_LISTN|VARIABLE_LISTN|VARIABLE_AND_COLUMN_LISTN}]]

[WHERE clause]

Attention:

You can use the UPDATE statement on text fields. However, if you need to update a very long string, you should use the UPDATETEXT statement. This part is too advanced for this book, so it is not discussed. To learn more, refer to the Microsoft SQL Sever documentation.

If you do not provide a WHERE clause, all records in the table are updated. Sometimes this is useful. For example, if you want to double the price of all the books in the table titles, you can use the following UPDATE statement:

You can also update multiple fields at the same time. For example, the following UPDATE statement updates both First_column,second_column, and third_column three fields:

UPDATE mytable SET first_column= ' updated! '

Second_column= ' updated! '

Third_column= ' updated! '

WHERE first_column= ' Update Me1 '

Skills:

SQL ignores extra spaces in the statement. You can write the SQL statement in any format that is easiest for you to read.

　

Create records and tables with select

You may have noticed that the INSERT statement is a little different from the DELETE statement and the UPDATE statement, and it only operates one record at a time. However, there is a way to make the INSERT statement add more than one record at a time. To do this, you need to combine the INSERT statement with the SELECT statement, like this:

INSERT MyTable (First_column,second_column)

SELECT Another_first,another_second

From anothertable

WHERE another_first= ' Copy me! '

This statement is recorded from the anothertable copy to the mytable. Only the value of field another_first in table anothertable is ' copy me! ' Records before being copied.

This form of INSERT statement is useful when a backup is established for a record in a table. Before you delete records from a table, you can copy them to another table in this way.

If you need to copy the entire table, you can use the SELECT INTO statement. For example, the following statement creates a new table named NewTable that contains all the data for the table mytable:

SELECT * into newtable from mytable

You can also specify that only specific fields are used to create this new table. To do this, simply specify the field you want to copy in the field list. Alternatively, you can use the WHERE clause to limit the records copied to the new table. The following example only copies the value of the field second_columnd equal to ' copy me! ' The First_column field of the record.

SELECT First_column into newtable

From MyTable

WHERE second_column= ' Copy me! '

It is difficult to modify an already established table using SQL. For example, if you add a field to a table, there is no easy way to get rid of it. In addition, if you accidentally give a field a data type wrong, you will have no way to change it. However, using the SQL statements that are described in this section, you can bypass these two issues.

For example, suppose you want to delete a field from a table. Using the SELECT INTO statement, you can create a copy of the table, but it does not contain the field you want to delete. This allows you to delete both the field and the data you don't want to delete.

If you want to change the data type of a field, you can create a new table that contains the correct data type fields. Once you've created the table, you can use both the UPDATE statement and the SELECT statement to copy all the data from the original table to the new table. In this way, you can modify the structure of the table and save the original data.

Aggregate functions

So far, you've only learned how to take one or more records from a table based on specific conditions. However, if you want to make statistics on the records in a table. For example, if you want to count the poll results of a poll stored in the table. Or you want to know how much time a visitor spends on your site on average. To count any type of data in a table, you need to use aggregate functions.

Microsoft SQL supports five kinds of aggregate functions. You can count the number, average, minimum, maximum, or sum of records. When you use an aggregate function, it returns only a number that represents one of these statistical values.

Attention:

To use the return value of the aggregate function on your ASP page, you need to give the value a name. To do this, you can in the SELECT statement, followed by a field name immediately after the aggregate function, as shown in the following example:

SELECT AVG (vote) ' the_average ' from opinion

In this example, the average value of the vote is named The_average. Now you can use this name in the database method of your ASP page.

Count the number of field values

The function count () is perhaps the most useful aggregate function. You can use this function to count the number of records in a table. Here's an example:

SELECT COUNT (au_lname) from authors

This example calculates the number of names (last name) in table authors. If the same name appears more than once, the name will be counted multiple times. If you want to know how many authors are named for a particular value, you can use the WHERE clause as shown in the following example:

SELECT COUNT (au_lname) from authors WHERE au_lname= ' Ringer '

This example returns the number of authors whose name is ' Ringer '. If the name appears two times in the table authors, the return value of the secondary function is 2.

If you want to know the number of authors with different names. You can get that number by using the keyword DISTINCT. As shown in the following example:

SELECT COUNT (DISTINCT au_lname) from authors

If the name ' Ringer ' appears more than once, it will be counted only once. The keyword distinct determines that only different values are counted.

Typically, when you use count (), the null value in the field is ignored. In general, this is exactly what you want. However, if you just want to know the number of records in a table, you need to calculate all the records in the table-regardless of whether it contains a null value or not. Here is an example of how to do this:

SELECT COUNT (*) from authors

Note the function count () does not specify any fields. This statement calculates the number of records in the table, including records with null values. Therefore, you do not need to specify a specific field to be computed.

The function count () is useful in many different situations. For example, suppose you have a table that holds the results of a poll of the quality of your site. This table has a field named vote, the value of which is either 0 or 1. 0 expressed no vote and 1 expressed their votes in favour. To determine the number of pro votes, you can have all the following SELECT statements:

SELECT COUNT (vote) from Opinion_table WHERE vote=1

Calculate the average of a field

Using the function count (), you can count how many values are in a field. But sometimes you need to calculate the average of these values. Using the function avg (), you can return the average value of all the values in a field.

If you have a more complicated poll of your site. Visitors can vote between 1 and 10 to show how much they like your site. You keep the voting results in the Int field named vote. To calculate the average of your user votes, you need to use the function avg ():

SELECT AVG (vote) from opinion

The return value of this SELECT statement represents the average user's preference for your site. The function avg () can only be used for numeric fields. This function also ignores null values when calculating averages.

Calculates the value of a field and

Suppose your site is being used to sell cards and has been running for two months, it's time to calculate how much money to make. Suppose a table named orders is used to record order information for all visitors. To calculate the sum of all order quantities, you can use the function sum ():

SELECT SUM (purchase_amount) from Orders

The return value of the function sum () represents the average of all values in the field Purchase_amount. The data type of the field Purchase_amount may be a money type, but you can also use the function sum () for other numeric fields.

Returns the maximum or minimum value

Once again assume that you have a table to hold the results of a poll of your site. Visitors can choose from a value of 1 to 10 to indicate their evaluation of your site. If you want to know the highest ratings of your visitors to your site, you can use the following statement:

SELECT MAX (vote) from opinion

You may want someone to give a high opinion of your site. With the function max (), you can know the maximum value in all the values of a numeric field. If someone throws a number 10 on your site, the function max () returns that value.

On the other hand, if you want to know the minimum rating that a visitor has for your site, you can use the function min (), as shown in the following example:

SELECT MIN (vote) from opinion

function min () returns the minimum value in all the values of a field. If the field is empty, the function min () returns a null value.

Other common SQL expressions, functions, and procedures

This section describes some of the other SQL technologies. You'll learn how to take data out of a table, where the value of a field is in a certain range, and you'll also learn how to convert field values from one type to another, and how to manipulate string and date-time data. Finally, you will learn a simple way to send mail.

To fetch data by matching a range of values

Suppose you have a table to hold the results of a poll of your site. Now you want to send a written thank-you note to all your site evaluations from 7 to 10 visitors. To get the names of these people, you can use the following SELECT statement:

SELECT username from opinion WHERE vote>6 and vote<11

This SELECT statement will fulfill your requirements. You can also get the same result using the following SELECT statement:

SELECT username from opinion WHERE vote BETWEEN 7 and 10

This SELECT statement is equivalent to the previous statement. Which statement you use is a programming style problem, but you'll find that statements that use an expression between are easier to read.

Now suppose you just want to take out the names of 1 or 10 of visitors to your site. To remove these names from the table opinion, you can use the following SELECT statement:

SELECT username from opinion WHERE vote=1 or vote

This SELECT statement returns the correct result, and there is no reason not to use it. However, there is an equivalent approach. Use the following select to get the same result:

SELECT username from opinion WHERE vote in (1,10)

Note the use of expression in. This SELECT statement only takes a record of vote values equal to one of the values in parentheses.

You can also use in to match character data. For example, suppose you just want to take out Bill Gates or President Clinton's voting values. You can use the following SELECT statement:

SELECT vote from Opinion WHERE username in (' Bill Gates ', ' President Clinton ')

Finally, you can use the expression not at the same time that you use between or in. For example, to remove the names of people whose voting values are not between 7 and 10, you can use the following SELECT statement:

SELECT username from opinion WHERE vote not BETWEEN 7 and 10

To select records in which the values of a field are not among a list of values, you can use not and in at the same time, as shown in the following example:

SELECT vote from Opinion

WHERE username not in (' Bill Gates ', ' President Clinton ')

You don't have to use between or in in SQL statements, but these two expressions are helpful to make your query closer to the natural language.