SQL Application and Development: (vi) Use of functions and expressions

Many of the table-related operations have been described in the previous posts. To demonstrate the different components of SQL, we discussed creating tables, table-based views, querying tables, modifying table data, and table relationships. The use of functions and expressions in SQL will continue to be covered in the next blog post, focusing on what SQL programmers are most used to. It should be noted that the functions and expressions described below are only part of the many functions and expressions supported by SQL.

In addition, the implementation of SQL and the supported SQL functions and expressions, in addition to the standard functions, different products have some non-standard functions. We can understand its features and features by querying the relevant product documentation.

A. Line function

1. Introduction

The row function calculates the new value on the basis of a single row in the table. This new value can be based on data in one column or in data based on several different columns. Some line function functions are used for numbers, while other line functions can be used for text or dates.

The row function calculates or constructs a new value that is not in the original table. This new value is constructed from the values of one or more cells in the original table. All of these values must be part of a separate row in the table.

For example, create a "out-of-stock detail 1" table whose data comes from the "Outbound Bills list". In the new table, a new column is created by using the row function, which combines the product of the "out-of-stock" column and the "out-of-stock" column to form another column called "Total Merchandise", and does not display the number of items in the new table:

SELECT out-of-stock order number, product number, unit price, outbound unit Price * Number of out of stock as total merchandise

into out-of-Library details 1

From out-of-stock Bill of Information

Still take this database as an example, the list of outbound orders is listed: The number of the listing, product number, the price of the outbound unit and the total merchandise. The total amount of the goods is calculated by the product of the outbound unit price and the number of outbound volumes, and the results are sorted by the total Value column, listing only the outbound information with a total value greater than 20000:

SELECT out-of-stock number, item number, Unit price, (outbound unit Price * out of stock) as total merchandise

From out of stock list details

WHERE (Outbound Unit Price * Number of out of library) >20000

ORDER by (Outbound unit Price * Number of out-of-stock)

It is worth noting that when a new column is constructed with a row function, if one of the values is null, the value of the new column is also null.

2. Classification

The following categories describe common line functions, including mathematical functions, text functions, and date functions.

2.1 Mathematical functions

Some line functions can perform arithmetic operations on numbers, while other functions can round numbers or take a bitwise operation. The line functions used for arithmetic operators can perform various operations. As in most computer languages, asterisks are used as multiplication sign; null is not equal to zero, it can represent an unknown number. Therefore, any row function that operates on NULL produces a null as the result.

The SQL symbolic flag function performs the following: If the value is positive, the result is 1, if the value is 0, the result is 0, if the value is negative, the result is-1, and if the value is null, the result is null.

For example, we want to calculate the product of 10 and 26. You can use the following statement to calculate:

SELECT 10*26 from TEMP

As another example, the remainder of the test x divided by 3, and X for all numbers from 0~9 to be stored in the N1 table, the result can be measured by executing the following statement:

SELECT n,n mod 3 as ' N mod 3 ' from NI ORDER by

2.2 Text functions

The line function can also be used for text, and most line functions generate some output text, but a few line functions return some numbers.

For example, we merge the city and address into a single column and add two space splits between cities. First select the jeweler's "City" column, then add two spaces to the right of the city, and then select the "Jeweler Address" column. The following statement lists the jeweler's information in the jeweler's information table in the Jewellery marketing system database, requiring the jeweler's number, name, telephone and detailed address to be displayed. The detailed address is a combination of the city and the limbs, separated by two spaces.

SELECT jeweler number, jeweler's name, phone, jeweler's City + ' + Jeweler's address as Beijing jeweler's address

From Jewelers information

Where jewelers ' city = ' Beijing '

Also, in the database "jeweler marketing", retrieve the data table "Customer information". Calculates the string length of the city in which the consumer is located, and obtains strings based on their length.

SELECT consumer name, consumer City + consumer address as contact address, residential City =substring (consumer city, 1,len (consumer city)-1), STR (consumer spending) as consumption amount

From Customer information

Where the consumer is located in the city = ' Nanjing '

2.3 Date function

The format of the date function differs from the date format, and the date format changes the way the date is displayed, but does not change its value, and the date function changes the date value to refer to being a new date.

Typically, a date is calculated in days rather than months or years, because the number of days in the one month or the number of days in a year can vary a lot.

If you write these dates directly to a SELECT statement, you must use a function to convert the text string within the quotation marks into a date function data type.

An example of a date function is described below. This function calculates the number of years from the date of the release to the current date, which is 1 years over 12 months. In this example, the SQL GetDate () function is used to get the current date. It completes sorting by the out-of-Library date, listing all information about 1 years (including 1 years) out of the library.

SELECT out of stock order number, customer number, inventory number, outbound date, operator number, operation date

From out of stock list information

WHERE DATEDIFF (year, outbound date, GETDATE ()) >=1

ORDER by Outbound Date

Two. Column functions

Previously, the data in the result table was directly from the original table, and the result of using the row function was a combination of a row of the original table. Next. We will describe the number of column rows, and the data in the result table using column rows can be a summary of the data for the entire column of the original table.

1. Introduction

When the data in the table is summarized with the number of column rows, the number of column rows checks all the things in the column, which involves each row of the table. The summary here is done on a single line in the results table.

A column can be a row function, or it can be a data column stored on a disk. Any of the row functions we described earlier can create a new column, and then you can use the number of column rows to manipulate the column.

The rollup action can be for all rows of the entire table, with hundreds of rows, thousands of rows, or more. The data in all rows is summarized as a number, a string, or a date.

The number of column rows is also called an aggregate function or a group function, and each column function produces a different type of summary. There are only a few types of column functions compared to the row function. Of course, the column functions are extended in some SQL products, and other column functions for special purposes are defined.

2. Use of common functions

After some basic knowledge of the column functions, let's introduce some of the common functions used.

2.1 Max function and min function

Use the Max and Min functions to display their total application to 3 columns in text, number, and date data types.

The data type of a column determines the sort order that is applied to its data: The text column is sorted alphabetically, the numeric column is sorted numerically, and the date column is sorted by date attribute. These sorts have choices that may affect the maximum and minimum values.

Using the GROUP BY clause results in multiple numeric values, instead of using the GROUP BY clause, the entire table is a group. Typically, a column function is used without group by itself, so the result is only one row. In the result set, each column is evaluated separately, and the rows in the resulting table contain some columns that might not be closely related to each other.

When you use a WHERE clause in a query that contains a list of functions, the WHERE clause is processed first, and then only the column function is used for the row that satisfies the where condition, instead of the column function for all things data in the table.

For example, the jewelry marketing system database is grouped by the city where the jeweler is located, to find jewelers ' information that meets the following criteria:

Jewelers in the year 2003; the smallest percentage of all jewelers ' information, the highest jewellery price for jewelers, and the name of the jeweler by name.

SELECT MAX (jeweler name) as name,

MIN (component percent) as component percentage,

MAX (jewellery price) as jewelry price,

MAX (due date) as due date, the city where the jeweler is located

From Jewelers information

WHERE cutoff date between ' 2003-1-1 ' and ' 2003-12-30 '

GROUP by Jewelers in your city

2.2 Count function

The Count function is used to calculate the number of records in a table or the number of columns in a column, and the contents of the calculation are specified by a SELECT statement. When you use the Conut function, you must specify a column name or use an asterisk, which represents all the records in the extreme table. For example, if you want to know how many records are in the Operations people table, you can use the following SELECT statement:

SELECT COUNT (*) as operator total from operator

The argument to the Count function in this statement is an asterisk that returns all records in the operator table. The return value is listed under the Total Operators column, and the total number of operators specifies the alias of a field to store the returned query results.

There are two different ways to count the data in a column in SQL. The difference between the two methods is that they deal with NULL in different ways: the count (*) function calculates the number of rows in the table, the count (colunmn) function counts the number in a particular column, and ignores all null, which is the function of all values that are not NULL in the specified column. Returns how much data is in the column.

You can use a column name instead of an asterisk if you want to know the number of items in a particular column instead of the records in the table. For example, modify the SELECT statement in the example above to calculate the number of merit in the operator Name column:

SELECT COUNT (operator name) as operator total from operator information

If you want to consider repeating worthwhile situations where the same operator name is likely to occur, add the DISTINCT keyword to the count function:

SELECT COUNT (DISTINCT operator name) as operator total from operator information

2.3 Sum function

The function can only be applied to numeric columns and cannot be computed for columns on text and date types.

The SUM function is used to sum the values. It makes it easy to calculate the sum of grouped data. As with the other column functions, if the data does not explicitly specify a grouping, the SUM function will treat the entire table as a group, and Null will be ignored when the column is added.

In this example, the total number of merchandise out-of-stock, the total number of outbound items, and the total amount of the outbound merchandise are calculated from the list of out-of-stock details table. The total quantity is the sum of the "Outbound Quantity" column values, and the total amount is the sum of the "Outbound Commodity Amounts" column.

SELECT count (out-of-stock number) as outbound count, SUM (number of outbound) as the total number of out-of-stock,

SUM (Outbound item amount) as out of stock total amount of goods

From out of stock list details

In the SELECT statement, use the column function, count (column), to count all the rows in the table, and the sum (column) function to sum the specified columns.

2.4 Avg function

Finally introduces a column function when AVG (), which is used to calculate the arithmetic mean of a set of numeric values contained in a particular query field. As with sum, no null is included in the calculation, the function can only be applied to numeric columns, and column I on text and date types cannot be evaluated.

Also in the "Sales management system" of the "Inventory details" table as an example, using the AVG () function to calculate the average out of the stock, the average commodity amount and the average out-of-stock unit price. The statements are as follows:

SELECT count (out of stock number) as outbound count, avg (out of stock) as average out of stock,

AVG (Outbound commodity amount) as average commodity amount, AVG (outbound unit price) as average out of stock unit price

From out of stock list details

On the basis of the above statement, we will use column functions to group and summarize the results, and to qualify the scope of the query with a WHERE clause. Check the invoice summary information for the item between September 15, 2002 and September 18, 2002, and then group the results by date. These summary information: the number of invoices issued within the specified date, the largest number of consumers using the invoice, the sum of the invoices in the period of the date, the average tax on the invoice price, the sum of the invoice amount and the total profit:

SELECT count (invoice number) as invoice number, max (consumer number) as maximum consumer number,

sum (Invoice subtotal) as Invoice subtotal,

AVG (tax) as average tax, sum (total) as amount total, sum (profit) as total profit

From invoice information

WHERE invoicing date between ' 2002-09-15 ' and ' 2002-09-18 '

GROUP by Invoice Date

Three. Expressions

1. Introduction

An expression is an expression that returns a numeric value that contains a column name, a numeric value, a mathematical operator, a keyword, or a formula that consists of other elements or returns a single worthwhile expression. For example, you can combine the values of two columns to generate a new value, or to manipulate the numeric values of a column to generate a new value.

2. Numeric expressions

Numeric expressions are data that uses mathematical operators to calculate numeric types of numeric types that are stored in a table. In numeric expressions, you can add, subtract, multiply, and divide data by using an operator.

They have the same basic arithmetic rules as building numeric expressions and building mathematical formulas. For example, multiply and divide operations take precedence over addition and subtraction, and you can use parentheses to change the order of operations, that is, the part that is enclosed in parentheses is evaluated first, and then to the rest of the expression.

For a clearer understanding of numeric expressions, now look at some examples of numeric expressions. In the database "dealer", Query the table "Sales Information", and calculate the price of the product.

SELECT product number, sales quantity, amount, (Amount/Quantity sold) as sales unit price

From Sales information

A numeric expression can be used not only in a SELECT clause, but also in a WHERE clause to specify a search condition. On the basis of the above example, the following changes have been made: a request to return a product with a sales price greater than 500.

Use Sales Management system

GO

SELECT product number, sales quantity, amount, (Amount/Quantity sold) as sales unit price

From Sales information

WHERE (amount/quantity of sales) >500

3. Case expression

A case expression is a conditional expression that creates a series of modified SQL statements to return a specific value. A case expression can change the way a value is represented or generate a new value. Each value is modified according to the conditions in the case expression. The case expression is made up of the case keyword and some conditions. If none of the preceding conditions are met, the last condition is the default, and the end of the expression is the keyword end. The case expression has two forms: a simple expression and a select expression, both of which support the else default condition and can be converted to each other.

An example is described below. In the database sales management system, query the operator Information table, listing the operator name, phone information, and text to list whether the operator has outbound permissions:

Use Sales Management system

GO

SELECT operator name, contact phone,

Case out-of-Library permissions

WHERE 1 Then ' has '

When 0 Then ' does not have '

ELSE ' ERROR '

Whether END as has out-of-Library permissions

From operator information

As another example, in the database "Sales management system" in the "Revenue details" table, according to the amount of revenue to determine whether the sales task is completed. Complete the situation. Completion: More than 50000 for over-completion tasks, 50000 below 10000 for completing tasks, all less than 10,000-bit unfinished tasks:

Use Sales Management system

GO

SELECT out of stock order number, revenue amount, completion =

Case

When revenue amount >50000 then ' excess '

When revenue amount >10000 then ' complete task '

ELSE ' not completed '

END

From revenue details

Here's an example of an application of a case expression in a set clause: In the database sales management system, modify the profit Amount column for the Enter Details table. Requirements: Profit amount is greater than or equal to 3000, then adjusted to the original profit amount plus 100, the profit amount is greater than 2000 (including 2000) light rain 3000, then adjusted to the original profit amount plus 50;

Use Sales Management system

GO

UPDATE Revenue Detail Information

SET Profit amount =

Case

When profit amount >=3000 then profit amount +100

When profit amount >=2000 then profit amount +50

ELSE Profit Amount

END

4. Cast expression

The function of the cast expression is very different from the function of the case expression. When a value is obtained from the database, the cast expression can change the data type of the value. However, you cannot change the data type of the source data. When you perform operations on column values in a database, the cast expression can be used to convert the data type if the column value types that are being evaluated do not match.

With the cast expression, you must specify the CAST keyword in turn, specifying the column name, the AS keyword, and the new data type in parentheses.

For example, in the database sales management system, retrieve the data table "Revenue details" and list the amount of revenue in the text form:

Use Sales Management system

GO

SELECT out-of-stock number, revenue amount, profit amount,

CAST (revenue amount as CHAR) as revenue amount in text form

From revenue details

Four. Learning Summary

From the individual, he has organized the SQL of these knowledge, the feeling of harvest is really big, previously also learned some knowledge about these, but it is not enough to understand their previous knowledge, but after this finishing, there is a guard cloud open to see the feeling of the moon, it is cool. Oneself also will in the database, diligently tries the practice, this knowledge diligently all achieves the strict collation and the practicality.

For the use of functions and expressions, here we have a general introduction to these, I hope that these collated and more systematic knowledge can help me and everyone to better understand and learn this knowledge. In the following days, will still be writing this aspect of the finishing knowledge, look forward to it!

SQL Application and Development: (vi) Use of functions and expressions