Shulou(Shulou.com)06/01 Report--

This article mainly introduces the common types of functions in SQL server, which have a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to know about it.

Since it is an advanced query, it will also be necessary to use functions, and there are several common types of functions:

Examples of common advanced query syntax:

Displays the names and base salaries of all operation and maintenance engineers in the test table, requiring the following format:

The basic salary of operation and maintenance engineer XXX is: XXXXX yuan.

The syntax format is as follows:

The basic salary of select 'operation and maintenance engineer' + name +'is: + CAST (basic salary as varchar (10)) + 'yuan'

From test where Job = 'Operation and maintenance engineer'

Displays the date and time after the current 10 days:

Select DATEADD (DD, 10MagneGETDATE ())

Displays the names and ages of everyone in the test table:

Select name, DATEDIFF (YY, date of birth, GETDATE ()) age from test

Displays the names and year of birth of all post-90s employees in the test table:

Select name, DATENAME (YY, date of birth) year of birth from employee

Where date of birth between '1990-01-01' and '1999-12-31'

Aggregate functions and grouping queries:

Aggregate function can be calculated based on columns and return a single value. Grouping query refers to grouping query results according to conditions, and then using aggregate function to return summary information of each group. Grouping query is generally used to meet statistical needs.

Common aggregate functions are: SUM (), AVG (), MAX (), M I N (), COUNT ().

SUM (): used to return the sum of all the numeric values in the expression. It can only be used for columns of numeric types, not for summarizing characters, dates, and so on.

For example, query the sum of the base salary of all employees in the test table

Select SUM (basic salary) Total salary from test

AVG (): used to return the average of all values in the expression, it can only be used for columns of numeric types.

Query the average base salary of all employees in the test table

Select AVG (basic salary) average wage from test

MAX (), M I N (): the former is used to return the maximum value in the expression, and the latter is used to return the minimum value in the expression. They can all be used for numeric, character, and date / time type columns.

For example, query the maximum and minimum wages in the test table:

Select MAX (basic wage) maximum wage, MIN (basic wage) minimum wage from test

COUNT (): a technique for returning non-null values in an expression that can be used for columns of numeric and character types, or an asterisk * can be used as an expression for the COUNT () function, and an asterisk * allows all rows to be counted without specifying a specific column.

Query the number of rows in the test table:

Select COUNT (*) from test

Query all the post-90s in the test table:

Select COUNT (date of birth) 'post-90s' from test

Where birth date > = '1990-01-01' and birth date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.