Using Functions phần 1

Using Functions SQL Server provides a number of functions you can use to get values from the database.
Nội dung trích xuất từ tài liệu:
Using Functions phần 1Using FunctionsSQL Server provides a number of functions you can use to get values from the database.For example, you can use the COUNT() function to get the number of rows in a table.The various functions are split into the categories shown in Table 4.2. Table 4.2: FUNCTIONSFUNCTION DESCRIPTIONCATEGORYAggregate Return information based on one or more rows in a table.Mathematical Perform calculations.String Perform string manipulations.Date and time Work with dates and times.System Return information on SQL Server.Configuration Return information on the configuration of the server.Cursor Return information on cursors.Metadata Return information on the database and the various database items, such as tables.Security Return information on the database users and roles.System statistical Return statistical information on SQL Server.Text and image Perform text and image manipulations.Youll learn about the first five functions in the following sections. The other categoriesof functions are beyond the scope of this book, as they are of primary interest to databaseadministrators. You can learn about those functions in the SQL Server Online Booksdocumentation.Using Aggregate FunctionsEarlier, you saw the use of the COUNT() aggregate function to get the number of rows.COUNT() and some other aggregate functions you can use with SQL Server are listed inTable 4.3. The expression you may pass to the aggregate functions is typically a singlecolumn, but it can also be a calculated field. ALL means that the function is applied to allthe column values, while DISTINCT means that the function is applied only to uniquevalues. ALL is the default. Table 4.3: AGGREGATE FUNCTIONSFUNCTION DESCRIPTION Table 4.3: AGGREGATE FUNCTIONSFUNCTION DESCRIPTIONAVG([ ALL | DISTINCT ] Returns the average of the values in a group.expression)COUNT([ ALL | DISTINCT ] Returns the number of rows in a group. COUNT()expression ] | *) returns an int data type value.COUNT_BIG([ ALL | Returns the number of values in aDISTINCT ] expression} | *) group.COUNT_BIG()returns a bigint data type valueMAX([ ALL | DISTINCT ] Returns the highest value.expression)MIN([ ALL | DISTINCT ] Returns the lowest value.expression)SUM([ ALL | DISTINCT ] Returns the sum of any non-null values. SUM() canexpression) be used only with numeric expressions.STDEV(expression) Returns the standard deviation for all the values.STDEVP(expression) Returns the standard deviation for the population of all the values.VAR(expression) Returns the variance for all the values.VARP(expression) Returns the variance for the population of all the values.Lets consider examples that use some of the aggregate functions.You use the AVG() function to get the average value. For example, the followingstatement gets the average of the UnitPrice column of the Products table using the AVG()function:SELECT AVG(UnitPrice)FROM Products;This example returns 28.8663. Since ALL is the default used with functions, this exampleuses every row in the Products table when performing the calculation. If you wanted tojust use unique values in the calculation, then you use the DISTINCT option, as shown inthe following example:SELECT AVG(DISTINCT UnitPrice)FROM Products;This example returns 31.4162, slightly higher than the previous result because onlyunique values are used this time.In addition to passing a column to a function, you can also pass a calculated field. Forexample, the following statement passes the calculated field UnitPrice * 1.20 to theAVG() function:SELECT AVG(UnitPrice * 1.20)FROM Products;This example returns 34.639636; the average after the UnitPrice values have beenincreased 20 percent.You can limit the rows passed to a function using a WHERE clause. For example, thefollowing SELECT statement calculates the average UnitPrice value for the rows with aCategoryID of 1:SELECT AVG(UnitPrice)FROM ProductsWHERE CategoryID = 1;This example returns 37.9791.You can combine a function with a GROUP BY clause to perform a calculation on eachgroup of rows. For example, the following SELECT statement calculates the averageUnitPrice value for each block of rows grouped by CategoryID:SELECT AVG(UnitPrice)FROM ProductsGROUP BY CategoryID;Figure 4.2 shows the results of this SELECT statement.Figure 4.2: Using the AVG() function to compute the average value of the UnitPricecolumnYou can also supply a HAVING clause to eliminate groups used in a SELECT statement.For example, the following statement adds a HAVING clause to the previous example toeliminate the groups that have an average value greater than 50:SELECT AVG(UnitPrice)FROM ProductsGROUP BY CategoryIDHAVING AVG(UnitPrice) > 50;This example returns 54.0066.Lets take a look at some of the other aggregate functions. You get the total number ofrows using the COUNT() function. For example, the following statement gets the totalnumber of rows in the Products table using the COUNT() function:SELECT COUNT(*)FROM Products;This example returns 77.You use the MAX() and MIN() functions to get the maximum and minimum values. Forexample, the following statement uses these functions to get the maximum and minimumUnitPrice:SELECT MAX(UnitPrice), MIN(UnitPrice)FROM Products;This example returns 263.5000 and 2.5000 for the respect ...