SQL Server Functions

SQL Server Functions

-- Aggregate functions
Use pubs
Go
Select AVG (distinct price) -- calculate the average
From titles
Where type = 'business'

Use pubs
Go
Select max (ytd_sales) -- maximum number
From titles

Use pubs
Go
Select min (ytd_sales) -- Minimum Number
From titles

Use pubs
Go
Select Type, sum (price), sum (advance) -- sum
From titles
Group by type order by type

Use pubs
Go
Select count (distinct city) -- calculate the number
From authors

Use pubs
Go
Select STDev (royalty) -- returns the statistical standard deviation of all values in a given expression
From titles

Use pubs
Go
Select stdevp (royalty) -- return the standard deviation of filling Statistics for ownership in the expression
From titles

Use pubs
Go
Select VAR (royalty) -- returns the statistical variance of all values
From titles

Use pubs
Go
Select varp (royalty) -- returns the statistical variance for filling all values
From titles

-- Mathematical functions

Select sin (23.45), atan (1.234), Rand (), Pi (), sign (-2.34) -- where Rand is used to obtain a random number

-- Configure Functions
Select @ version -- get the current database version
Select @ language -- Current Language

-- Time Functions
Select getdate () as 'wawa _ getdate' -- current time
Select getutcdate () as 'wawa _ getutcdate '-- get UTC time
Select Day (getdate () as 'wawa _ day' -- retrieve the day
Select month (getdate () as 'wawa _ month' -- retrieve month
Select Year (getdate () as 'wawa _ year' -- retrieve year
Select dateadd (D, 3, getdate () as wawa_dateadd -- add three days. Note that 'D' indicates day, 'M' indicates month, and 'yy' indicates year.
Select datediff (D, '2014-07-01 ', '2014-07-15') as wawa_datediff -- calculate the difference between the two time ranges.
Select datename (D, '2014-07-15 ') as wawa_datename -- get a part of the time
Select datepart (D, getdate () as wawa_datepart -- get a part of the time, which is similar to the one above

-- String functions
Select ASCII (123) as '200', ASCII ('100') as '"123"', ASCII ('abc') as '"ABC"' -- convert to ASCII code
Select char (123), char (321), char (-123) -- convert from ASCII to character
Select lower ('abc'), lower ('abc'), upper ('abc'), upper ('abc') -- convert case sensitivity
Select STR (123.45, 123.45), STR (,) -- converts a value to a string.
Select ltrim ('"No space on the left"') -- remove space
Select rtrim ('"No space on the right"') -- remove space
Select ltrim (rtrim ('"no space left or right"') -- remove space
Select left ('SQL Server', 3), Right (' SQL Server', 6) -- Take the left or right

Use pubs
Select au_lname, substring (au_fname,) -- Obtain the substring
From authors
Order by au_lname

Select charindex ('20140901', 'abc123def ', 2) -- returns the starting position of the specified expression in the string.
Select patindex ('20140901', 'abc123def '), patindex (' % 100', 'abc123def ') -- returns the starting position of the first occurrence of a pattern in the expression.
Select quotename ('abc', '{'), quotename ('abc') -- returns a string expanded by a specified character
Select reverse ('abc'), reverse ('shanghai') -- reverse string order
Select Replace ('abcdefghicde', 'cde', 'xxxx') -- returns the string that replaces the specified substring.
Select space (5), Space (-2)

-- System functions
Select host_name () as 'host _ name', host_id () as 'host _ id', user_name () as 'user _ name', user_id () as 'user _ id', db_name () as 'db _ name'

-- Variable definition and usage
-- Declare local variables
Declare @ mycounter int
Declare @ last_name varchar (30), @ fname varchar (20), @ State varchar (2)

-- Declare multiple variables
-- Assign values to variables
Use northwind
Go
Declare @ firstnamevariable varchar (20 ),
@ Regionvariable varchar (30)
Set @ firstnamevariable = 'Anne '-- you can use set or select to assign values to variables. Microsoft recommends set, but select is useful when selecting a value for direct assignment.
Set @ regionvariable = 'wa'

Select lastname, firstname, title -- construct a SELECT statement and query it using the declared and assigned Variables
From employees
Where firstname = @ firstnamevariable or region = @ regionvariable
Go
-- Global variables
Select @ version -- returns the database version
Select @ error -- returns the last Script Error
Select @ identity -- returns the ID of the last auto-increment column.

-- While, break, and continue usage
-- Calculate the average price. If the price is lower than 30, the cycle will double all prices,
-- There is another if in it to determine if the maximum unit price is greater than 50, then exit the cycle, otherwise continue the cycle, know that the maximum unit price is greater than 50 then break out of the cycle, haha,
-- I should have analyzed it, right.
Use pubs
Go
While (select AVG (price) from titles) <$30
Begin
Update titles
Set price = price * 2
Select max (price) from titles
If (select max (price) from titles)> $50
Break
Else
Continue
End
Print 'too much for the marker to bear'

-- Typical transaction programming example
-- Begin transaction is the start transaction, commit transaction is the commit transaction, and rollback transaction is the rollback transaction
-- In this example, insert a record first. If an error occurs, roll back the transaction, that is, cancel the transaction, and directly return (return). If yes, commit commits the transaction.
-- The Return above can return an integer. If the return value is 0, there is no error during execution. If the return value is a negative number,
-- This return can also be used in the stored procedure. You can use exec @ return_status = pro_name to obtain this value.
Use pubs
Go
Begin Tran mytran
Insert into stores (stor_id, stor_name)
Values ('20140901', 'My books ')
Go
Insert into discounts (discounttype, stor_id, discount)
Values ('clearance sale ', '123', 9999)
If @ error <> 0
Begin
Rollback Tran mytran
Print 'insert discount record error'
Return
End
Commit Tran mytran

-- Example of saving points for Transaction Processing
-- After a transaction is saved, the transaction can be rolled back to the specified storage point, so that no operation can be used.
Use pubs
Go
Select * from stores
Begin transaction testsavetran
Insert into stores (stor_id, stor_name)
Values ('000000', 'W. z.d Book ')
Save transaction before_insert_data2
Go
Insert into stores (stor_id, stor_name)
Values ('20140901', 'foreat books ')
Go
Rollback transaction before_insert_data2
Select * from stores

-- Stored Procedure
Use pubs
If exists (Select name from sysobjects where name = 'proc _ calculate_taxes 'and type = 'P ')
Drop procedure proc_calculate_taxes
Go
Create procedure proc_calculate_taxes (@ P1 smallint = 42, @ P2 char (1), @ P3 varchar (8) = 'Char ')
As
Select *
From titles
-- Execution Process
Execute proc_calculate_taxes @ P2 = 'A'

-- Aggregate functions
Use pubs
Go
Select AVG (distinct price) -- calculate the average
From titles
Where type = 'business'

Use pubs
Go
Select max (ytd_sales) -- maximum number
From titles

Use pubs
Go
Select min (ytd_sales) -- Minimum Number
From titles

Use pubs
Go
Select Type, sum (price), sum (advance) -- sum
From titles
Group by type order by type

Use pubs
Go
Select count (distinct city) -- calculate the number
From authors

Use pubs
Go
Select STDev (royalty) -- returns the statistical standard deviation of all values in a given expression
From titles

Use pubs
Go
Select stdevp (royalty) -- return the standard deviation of filling Statistics for ownership in the expression
From titles

Use pubs
Go
Select VAR (royalty) -- returns the statistical variance of all values
From titles

Use pubs
Go
Select varp (royalty) -- returns the statistical variance for filling all values
From titles

-- Mathematical functions

Select sin (23.45), atan (1.234), Rand (), Pi (), sign (-2.34) -- where Rand is used to obtain a random number

-- Configure Functions
Select @ version -- get the current database version
Select @ language -- Current Language

-- Time Functions
Select getdate () as 'wawa _ getdate' -- current time
Select getutcdate () as 'wawa _ getutcdate '-- get UTC time
Select Day (getdate () as 'wawa _ day' -- retrieve the day
Select month (getdate () as 'wawa _ month' -- retrieve month
Select Year (getdate () as 'wawa _ year' -- retrieve year
Select dateadd (D, 3, getdate () as wawa_dateadd -- add three days. Note that 'D' indicates day, 'M' indicates month, and 'yy' indicates year.
Select datediff (D, '2014-07-01 ', '2014-07-15') as wawa_datediff -- calculate the difference between the two time ranges.
Select datename (D, '2014-07-15 ') as wawa_datename -- get a part of the time
Select datepart (D, getdate () as wawa_datepart -- get a part of the time, which is similar to the one above

-- String functions
Select ASCII (123) as '200', ASCII ('100') as '"123"', ASCII ('abc') as '"ABC"' -- convert to ASCII code
Select char (123), char (321), char (-123) -- convert from ASCII to character
Select lower ('abc'), lower ('abc'), upper ('abc'), upper ('abc') -- convert case sensitivity
Select STR (123.45, 123.45), STR (,) -- converts a value to a string.
Select ltrim ('"No space on the left"') -- remove space
Select rtrim ('"No space on the right"') -- remove space
Select ltrim (rtrim ('"no space left or right"') -- remove space
Select left ('SQL Server', 3), Right (' SQL Server', 6) -- Take the left or right

Use pubs
Select au_lname, substring (au_fname,) -- Obtain the substring
From authors
Order by au_lname

Select charindex ('20140901', 'abc123def ', 2) -- returns the starting position of the specified expression in the string.
Select patindex ('20140901', 'abc123def '), patindex (' % 100', 'abc123def ') -- returns the starting position of the first occurrence of a pattern in the expression.
Select quotename ('abc', '{'), quotename ('abc') -- returns a string expanded by a specified character
Select reverse ('abc'), reverse ('shanghai') -- reverse string order
Select Replace ('abcdefghicde', 'cde', 'xxxx') -- returns the string that replaces the specified substring.
Select space (5), Space (-2)

-- System functions
Select host_name () as 'host _ name', host_id () as 'host _ id', user_name () as 'user _ name', user_id () as 'user _ id', db_name () as 'db _ name'

-- Variable definition and usage
-- Declare local variables
Declare @ mycounter int
Declare @ last_name varchar (30), @ fname varchar (20), @ State varchar (2)

-- Declare multiple variables
-- Assign values to variables
Use northwind
Go
Declare @ firstnamevariable varchar (20 ),
@ Regionvariable varchar (30)
Set @ firstnamevariable = 'Anne '-- you can use set or select to assign values to variables. Microsoft recommends set, but select is useful when selecting a value for direct assignment.
Set @ regionvariable = 'wa'

Select lastname, firstname, title -- construct a SELECT statement and query it using the declared and assigned Variables
From employees
Where firstname = @ firstnamevariable or region = @ regionvariable
Go
-- Global variables
Select @ version -- returns the database version
Select @ error -- returns the last Script Error
Select @ identity -- returns the ID of the last auto-increment column.

-- While, break, and continue usage
-- Calculate the average price. If the price is lower than 30, the cycle will double all prices,
-- There is another if in it to determine if the maximum unit price is greater than 50, then exit the cycle, otherwise continue the cycle, know that the maximum unit price is greater than 50 then break out of the cycle, haha,
-- I should have analyzed it, right.
Use pubs
Go
While (select AVG (price) from titles) <$30
Begin
Update titles
Set price = price * 2
Select max (price) from titles
If (select max (price) from titles)> $50
Break
Else
Continue
End
Print 'too much for the marker to bear'

-- Typical transaction programming example
-- Begin transaction is the start transaction, commit transaction is the commit transaction, and rollback transaction is the rollback transaction
-- In this example, insert a record first. If an error occurs, roll back the transaction, that is, cancel the transaction, and directly return (return). If yes, commit commits the transaction.
-- The Return above can return an integer. If the return value is 0, there is no error during execution. If the return value is a negative number,
-- This return can also be used in the stored procedure. You can use exec @ return_status = pro_name to obtain this value.
Use pubs
Go
Begin Tran mytran
Insert into stores (stor_id, stor_name)
Values ('20140901', 'My books ')
Go
Insert into discounts (discounttype, stor_id, discount)
Values ('clearance sale ', '123', 9999)
If @ error <> 0
Begin
Rollback Tran mytran
Print 'insert discount record error'
Return
End
Commit Tran mytran

-- Example of saving points for Transaction Processing
-- After a transaction is saved, the transaction can be rolled back to the specified storage point, so that no operation can be used.
Use pubs
Go
Select * from stores
Begin transaction testsavetran
Insert into stores (stor_id, stor_name)
Values ('000000', 'W. z.d Book ')
Save transaction before_insert_data2
Go
Insert into stores (stor_id, stor_name)
Values ('20140901', 'foreat books ')
Go
Rollback transaction before_insert_data2
Select * from stores

-- Stored Procedure
Use pubs
If exists (Select name from sysobjects where name = 'proc _ calculate_taxes 'and type = 'P ')
Drop procedure proc_calculate_taxes
Go
Create procedure proc_calculate_taxes (@ P1 smallint = 42, @ P2 char (1), @ P3 varchar (8) = 'Char ')
As
Select *
From titles
-- Execution Process
Execute proc_calculate_taxes @ P2 = 'A'