Basics of SQL Data Operations (Intermediate) 8

Update records

To modify one or more records that already exist in the table, SQL should be used UPDATE statement. Like the DELETE statement, the UPDATE statement can use the WHERE clause to select specific records to update. Please see this example:

UPDATE mytable SET first_column=’Updated!’ WHERE second_column=’Update Me!’

This UPDATE The statement updates the value of all second_column fields to 'Update Me!’ record. For all selected records, the value of the first_column field is set to 'Updated!'.

The following is the complete syntax of 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]

Note:

You can use the UPDATE statement for text fields. However, if you need to update a very long string, you should use the UPDATETEXT statement. This material is too advanced for this book and will not be discussed. For more information, please refer to Microsoft SQL Server documentation.

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

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

UPDATE mytable SET first_column=’Updated!’

Second_column=’Updated!’

Third_column=’Updated!’

WHERE first_column=’Update Me1’

Tip:

SQL ignores extra spaces in statements. You can write SQL statements in whatever format is easiest for you to read.

　

Use SELECT Create records and tables

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

INSERT mytable (first_column,second_column)

SELECT another_first,another_second

FROM anothertable

WHERE another_first=’Copy Me!’

This statement copies records from anothertable to mytable. Only the value of the field another_first in the table anothertable is ‘Copy Me! ’ records are copied.

When creating a backup of records in a table, this form of INSERT Statements are very useful. You can use this method to copy records from one table to another table before deleting them.

If you need to copy the entire table, you can use SELECT INTO statement. For example, the following statement creates a new table named newtable that contains all the data for 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 fields you want to copy in the field list. Alternatively, you can use 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!’ 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 created table using SQL. For example, if you add a field to a table, there is no easy way to remove it. Additionally, if you accidentally give a field the wrong data type, you won't be able to change it. However, using the SQL statements described in this section, you can bypass both problems.

For example, suppose you want to delete a field from a table. Use SELECT INTO statement, you can create a copy of the table without the fields you want to delete. This allows you to delete the field while retaining 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 containing fields of the correct data type. Once the table is created, you can use the UPDATE statement with SELECT statement to copy all data in the original table to the new table. Through this method, you can modify the structure of the table and save the original data.

Aggregation Functions

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

Microsoft SQL Five types of aggregate functions are supported. You can count the number of records, average, minimum, maximum, or sum. When you use an aggregate function, it simply returns a number that represents one of these statistical values.

Note:

To use the return value of a set function in your asp web page, you need to give the value a name. To do this, you can follow the aggregate function with a field name in the SELECT statement, as in the following example:

SELECT AVG(vote) ‘the_average’ FROM opinion

In this example, the average of votes is named the_average. Now you can use this name in the database method of your ASP web page.

Counts the number of field values ​​

The function COUNT() is perhaps the most useful aggregate function. You can use this function to count how many records there are in a table. Here's an example:

SELECT COUNT(au_lname) FROM authors

This example calculates the name in the table authors (last name) number. If the same name appears more than once, the name will be counted multiple times. If you want to know how many authors have a specific name, you can use the WHERE clause, as in the following example:

SELECT COUNT(au_lname) FROM authors WHERE au_lname=’Ringer’

This example returns the number of authors named ‘Ringer’. If the name appears twice in the authors table, the return value of this function is 2.

Suppose you want to know the number of authors with different names. You can get this 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 only be counted once. keywordDISTINCT It is decided that only values ​​that are different from each other are calculated.

Normally, when you use COUNT(), null values ​​in fields will be ignored. Generally speaking, this is what you want. However, if you just want to know the number of records in the table, then you need to count all records in the table - regardless of whether it contains null values. Here's an example of how to do this:

SELECT COUNT(*) FROM authors

Note that the function COUNT() does not specify any fields. This statement counts all records in the table, including records with null values. Therefore, you do not need to specify specific fields to be calculated.

The function COUNT() is useful in many different situations. For example, suppose you have a table that holds the results of a poll on the quality of your site. This table has a field called vote, the value of this field is either 0 or 1. 0 means a negative vote, and 1 means a positive vote. To determine the number of upvotes you can SELECT all the following Statement:

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 ​​there are in a field. But sometimes you need to calculate the average of these values. Using the function AVG() you can return the average of all values ​​in a field.

Suppose you conduct a more complex poll on your site. Visitors can vote from 1 to 10 to indicate how much they like your site. You save the voting results in an INT type field called 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 preference for your site. The function AVG() can only be used for numeric fields. This function also ignores null values ​​when calculating the average.

Calculate the sum of field values ​​

Assume that your site is used to sell cards and has been running for two months. It’s time to calculate how much money you made. Suppose there is a table named orders to record the order information of all visitors. To calculate the sum of all ordered quantities you can use the function SUM():

SELECT SUM(purchase_amount) FROM The return value of the orders

function SUM() represents the average of all values ​​in the field purchase_amount. The data type of the field purchase_amount may be MONEY, but you can also use the function SUM() for other numeric fields.

Return the maximum or minimum value

Once again, assume you have a table that holds the results of a poll on your site. Visitors can choose from 1 to 10 The value represents their evaluation of your site. If you want to know the highest rating that visitors have for your site, you can use the following statement:

SELECT MAX(vote) FROM opinion

You may hope that someone has given your site a high opinion. Through the function MAX(), you can know the maximum value among all values ​​of a numeric field. If someone voted the number 10 on your site, the function MAX() will return that value.

On the other hand, if you want to know the lowest rating of your site by visitors, you can use the function MIN(), as shown in the following example:

SELECT MIN(vote) FROM opinion

The function MIN() returns the minimum value among all values ​​of a field. If the field is empty, function MIN() returns a null value.

Other commonly used SQL expressions, functions, and procedures

This section will introduce some other SQL technologies. You'll learn how to retrieve data from a table whose field values ​​fall within a certain range. You'll also learn how to convert field values ​​from one type to another, and how to manipulate string and datetime data. Finally, you'll learn a simple way to send an email.

Get data out by matching a range of values

Suppose you have a table that holds the results of a poll on your site. Now you want to send a written thank you note to all visitors who rated your site between 7 and 10. 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 achieve your requirements. You can also get the same result using the following SELECT statement:

SELECT username FROM opinion WHERE vote BETWEEN 7 AND 10

thisSELECT statement is equivalent to the previous statement. Which statement to use is a matter of programming style, but you will find that using the expression BETWEEN statements are easier to read.

Now let’s say you only want to extract the names of visitors who voted 1 or 10 for your site. To retrieve these names from the opinion table, you can use the following SELECT Statement:

SELECT username FROM opinion WHERE vote=1 or vote

This SELECT statement will return the correct results, there is no reason not to use it. However, there is an equivalent way. You can get the same result using SELECT like this:

SELECT username FROM opinion WHERE vote IN (1,10)

Pay attention to the use of expression IN. This SELECT The statement only retrieves records whose vote value is equal to one of the values ​​in parentheses.

You can also use IN to match character data. For example, let's say you only want to extract the vote values ​​of Bill Gates or President Clinton. You can use SELECT as follows Statement:

SELECT vote FROM opinion WHERE username IN (‘Bill Gates’,’President Clinton’)

Finally, you can use the expression NOT along with BETWEEN or IN. For example, to retrieve the names of people whose vote value is 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 whose value in a certain field is not in 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 are not required to use BETWEEN or IN in your SQL statement, however, to make your query closer to natural language, these two expressions are helpful.

The above is the content of SQL Data Operation Basics (Intermediate) 8. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!