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

Before we simply understand the add, delete, change, check these kinds of T-SQL syntax to manipulate the data table, but in order to more easily and quickly complete a large number of tasks, SQL Server provides some internal functions that can be used in conjunction with SQL Server's SELECT statements for type conversion, date processing, mathematical calculation, and system functions.

Strings have a special status in information processing, and almost all information needs to be converted into strings in order to display correctly, especially when different data are spliced and displayed more widely.

Use "+" concatenation between strings

The basic salary of select 'operation and maintenance engineer' + 'name' +'is:'+ cast (basic salary as varchar (10)) + 'yuan' from * table name * # "basic salary as varchar (10)" converts the type of basic salary column to a string where job = 'operation and maintenance engineer'

Example:

Displays the date and time after the current 10 days:

Select dateadd (DD,10,getdate ()) # "getdate ()" gets the current system date, DD: unit is day

Display the names and ages of all:

Select name, datediff (yy, date of birth, getdate ()) as age from * * Table name *

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

Select name, datename (year, date of birth) as month of birth # as, you can configure aliases (newly generated column names) from * table name * where birth date between '1990-01-01' and '1999-12-31' # and: and, to

Aggregate function:

Used to perform calculations on a set of values and return a single value

Such as: summation, average, maximum or minimum, etc.

SUN: summation, AVG: average, MAX and MIN: return maximum and minimum

COUNT: returns a count of non-null values

Query the summary of the base salary of all employees in the table:

Select sum (basic salary) as Total salary from * Table name *

Average salary of all employees in the query table:

Select avg (basic salary) as average wage from * Table name *

Query the highest and lowest base salary in the table:

Select max (basic wage) as maximum wage, min (basic wage) as minimum wage from * Table name *

Query the number of post-90s employees in the table:

Select count (basic salary) number of people after as'90 'from * Table name * where date of birth > =' 1990-01-01'

Group query:

Classify and combine the data in the table through the group by clause, and then display only the groups of qualified conditions according to the statistical information you need. You need to use the HAVING clause.

Average salary for each job in the query table:

Select job, avg (base salary) as job base salary from * table name * group by job

Query jobs with an average salary of less than 10000 in the table:

Select job, avg (base salary) as average job salary from * table name * group by job having avg (base salary)

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.