Basics of SQL Data Operations (Elementary) 3

Manipulating Fields

Normally, when you retrieve a field value from a table, the value is associated with the field name defined when the table was created. If you select all author names from the authors table, all values ​​will be associated with the field name au_lname. But in some cases, you need to operate on field names. In a SELECT statement, you can replace the default field name by simply following it with a new name. For example, you can use a more intuitive and readable name Author Last Name instead of field name au_lname:

SELECT au_lname "Author Last Name" FROM authors

When this SELECT statement is executed, the value from the field au_lname will be the same as "Author Last Name". The query results may be as follows:

Author Last Name

……………………………………………………………………..

White

Green

Carson

O’Leary

Straight

…

(23 row(s) affected)

Note that the field title is no longer au_lname, but Author Last Name replaced.

You can also operate on field values ​​returned from a table by performing operations. For example, if you want to double the price of all books in the titles table, you can use the following
SELECT statement:

SELECT PRice*2 FROM titles

When this query is executed, the price of each book is doubled when it is taken out of the table. However, manipulating the fields in this way will not change the book prices stored in the table. Operations on fields will only affect the output of the SELECT statement, but will not affect the data in the table. To display both the original price of a book and the new price after the price increase, you can use the following query:

SELECT price "Original price", price*2 "New price" FROM titles

When data is taken out from the table titles, the original price is displayed under the title Original price, and the doubled price is displayed under the title New price below. The result may be like this:

original price new price

……………………………………………………………….

39.98
11.95 23.90

5.98
39.98
…

(18 row(s) affected)

You can use most standard mathematical operators to manipulate field values, such as addition (+), subtraction (-), multiplication (*) and division (/). You can also perform operations on multiple fields at once, for example:

SELECT price*ytd_sales "total revenue" FROM titles

In this example, the total sales of each type of book are calculated by multiplying the price by the sales volume. The result of this SELECT statement will be like this:

total revenue

………………………………………………………

(18 row(s) affected)

Finally, you can also use the concatenation operator (it looks like a plus sign) to join two character fields:

SELECT au_fname+" "+au_lname "author name" FROM authors

In this example, you paste the fields au_fname and au_lname together, separated by a comma, and specify the title of the query results as author
name. The execution result of this statement will be like this:

author names

………………………………………………………………

Johnson White

Marjorie Green

Cheryl Carson

Michael O’Leary

Dean Straight

…

(23 row(s) affected)

As you can see, SQL provides you with a lot of control over query results. You should take full advantage of these advantages during asp programming. Using SQL to manipulate query results is almost always more efficient than using a script that does the same thing.

Sort query results

It was emphasized in the introduction of this chapter that SQL tables have no inherent order. For example, it doesn't make sense to fetch the second record from a table. From a SQL perspective, no record
precedes any other record.

However, you can manipulate the order of results of a SQL query. By default, records appear in no particular order when they are retrieved from the table. For example, when the field au_lname is taken out from the table
authors, the query results are displayed like this:

au_lname

……………………………….

White

Green

Carson

O'Leary

Straight

…

(23 row(s) affected)

It is very inconvenient to look at a column of names in no specific order. It would be much easier to read the names if they were arranged in alphabetical order. By using ORDER BY clause, you can
force a query results to be sorted in ascending order, like this:

SELECT au_lname FROM authors ORDER BY au_lname

When this SELECT statement is executed, the display of author names will be in alphabetical order. ORDER The BY clause sorts the author names in ascending order.

You can also use ORDER on multiple columns at the same time BY clause. For example, if you want to display both the field au_lname and the field au_fname in ascending order, you need to
both fieldsSort by:

SELECT au_lname,au_fname FROM authors ORDER BY au_lname ,au_fname

This query first sorts the results by the au_lname field, and then sorts by the field au_fname. Records will be retrieved in the following order:

au_lname au_fname

…………………………………………………………………………………….

Bennet Abraham

Ringer Albert

Ringer Anne

Smith Meander

…

(23 row(s) affected)

Note that there are two authors with the same name Ringer. An author named Albert Ringer appears as Anne Before the author of Ringer, this is because the surname Albert should come before the surname Anne in alphabetical order.

If you want to sort the query results in reverse order, you can use the keyword DESC. The keyword DESC sorts the query results in descending order, as shown in the following example:

SELECT au_lname,au_fname FROM authors

WHERE au_lname=”Ringer” ORDER BY au_lname ,au_fname DESC

This query retrieves all author records named Ringer from the authors table. ORDER The BY clause sorts the query results in descending order based on the author's first and last name. The result is
like this:

au_lname au_fname

………………………………………………………………………………………………………….

Ringer Anne

Ringer Albert

(2 row(s) affectec)

Note that in this table, the surname Anne appears before the surname Albert. Author names are displayed in descending order.

You can also sort a query result by numeric fields. For example, if you want to get the prices of all books in descending order, you can use the following SQL query:

SELECT price FROM titles ORDER BY price DESC

This SELECT statement retrieves the prices of all books from the table. When displaying the results, books with low prices are displayed first, and books with high prices are displayed last.

Warning:

Do not sort the query results unless it is particularly necessary, because it takes some effort for the server to complete this work. This means that with ORDER BY The SELECT statement of the clause takes longer to execute than the ordinary SELECT statement.

Retrieve different records

A table may have duplicate values ​​in the same column. For example, there are two authors named Ringer in the authors table of the database pubs. If you take all
names from this table, the name Ringer will be displayed twice.

In certain cases, you may only be interested in getting different values ​​from a table. If a field has duplicate values, you may want each value to be selected only once. You can do this using the keyword DISTINCT:

SELCET DISTINCT au_lname FROM authors WHERE au_lname="Ringer"

When this SELECT statement is executed, only one record is returned. By including the keyword DISTINCT in the SELECT statement, you can remove all duplicate values. For example, suppose there is a table about information published in a news group, and you want to retrieve the names of all people who have posted information in this news group, then you can use the keyword DISTINCT. Each user's name is only taken once - although some users post more than one message.

WARNING:

Like ORDER Like the BY clause, forcing the server to return different values ​​will also increase operational overhead. Blessings had to spend some time getting this done. Therefore, do not use the keyword DISTINCT when it is not absolutely necessary.

Create a new table

As mentioned earlier, all data in the database is stored in tables. Data tables include rows and columns. Columns determine the type of data in the table. Rows contain actual data.

For example, the table authors in the database pubs has nine fields. One of the fields is named au_lname, which is used to store the author's name information. Each time a new author is added to this table, the author's name is added to this field, producing a new record.

By defining fields, you can create a new table. Each field has a name and a specific data type (the data type is described in the "Field Type" section later). For example, the field au_lname stores character data. A field can also store other types of data.

Using SQL Sever, there are many ways to create a new table. You can execute a SQL statement or use SQL Transaction Manager (SQL Enterprise Manager) to create a new table. In the next section, you will learn how to use SQL statements to create a new table.



The above is the content of SQL Data Operation Basics (Elementary) 3. For more related articles, please pay attention to the PHP Chinese website (m.sbmmt.com)!