SQL advanced syntax

Cube rollup and compute keywords
The cube keyword is used to calculate the sum of data in each grouping field to be queried.
The compute keyword is used to group for Subtotal, that is, to group fields and generate one or more sum averages for each group.

Example
Select cust_id, sum (pay1) as pay11 from pay_list group by pay1 with cube
Go
AboveCodeError message caused by the use of pay1 and cube keywords specified by the sum aggregate function
Modify
Select cust_id, sum (pay1) as pay11 from pay_list group by cust_id with cube
Go

Rollup keywords
Sum the name of the first field added after group by, and other fields are not
The difference between rollup and cube keywords is that rollup calculates the sum of the first field names after group by, while cube calculates the sum of each field.

Example
Select vendor ID, product name, sum (Quantity) as total quantity from proced group by vendor ID, product name with Rollup
Go

Compute keywords
Used to group A group subtotal, that is, to group A field, and generate one or more sum averages for each group.
Example
Select vendor ID, product name, quantity from proced compute sum (Quantity)
Go

Time Based on vendor ID
Select vendor No., product name, quantity from proced order by vendor No. compute sum (Quantity) by vendor No.
Go

7.5 Having statement

Having statements are used to specify search conditions for a group or a summary. A having statement can only be used with a select statement, and is usually used in a group by clause.
.

Example
Calculate the number and product name of the vendor whose total quantity is greater than 200.
Select vendor ID, product name, sum (Quantity) as quantity from proced group by vendor ID, product name having sum (Quantity)> 100
Go

7.6 distinct and all keywords

Example
Select distinct from proced
Go

7.7 escape keywords
If you want to regard a wildcard as a wildcard rather than a wildcard, you can use the escape keyword and add a wildcard.

Example
In the Customs table, find all records that meet the 50% discount
Use misdb
Go
Select * from Customs where discl like '000000' escape '%'
Go

7.8 Annotation
Method 1: Start With/* and end */
Method 2: -- annotate a row
Example:
Use "/*" and "*/" to write comments correctly "*/"
/* Compile the annotation usage */
/* Use pubs
Go */
Select * from authors
Go

7.9 region variables and global variables

In SQL Server, variables can be divided into two types: regional variables and global variables.
In SQL Server, Region variables are generally defined by the user. The expression starts with "@" as the variable name. The global variable is provided by the system, and its representation is "@" as the variable name.
The lifecycle of region variables starts with the declare statement until the execution of this batch ends.

Declare @ test int -- region variable @ Test start of lifecycle
@ Test = 5
Select * from authors where au_id = @ Test
Go -- the execution of the batch processing is completed, and the life cycle of the @ test variable ends.
Select * from authors
Go

Region variable
To use the region variable, first declare the region variable. After the region variable is declared, the system sets the initial value of the region variable to null, then, use the set or select statement to set the variable value. When declaring variables, you must use the declare statement.
Declare @ test int -- declare the variable test and its data type
Set @ test = 5 -- set the value of the test variable to 5.

Declare multiple variable names separated by commas.
Declare @ A1 char (10), @ A2 int, @ A3 char (10)

You can use the SELECT statement to set variables in either of the following ways:
1. directly set the variable value
Declare @ test int
Select @ test = 5
2. Specify the query result to the variable. However, this method requires that the query result be a single value.
Declare @ test int
Select @ test = quantity from customer where cust_id = 'a001'

When you use the SELECT statement to specify variable values, if you want to specify multiple variable values for multiple variables, each variable can be separated by a comma.

7.10 Process Control

Betin... end
Used to enclose a string of T-SQL statements using the keywords of begin... end so that the T-SQL statements that are included together can be executed.

If the T-SQL statement to be executed has only one line, you do not need to use the begin... end statement for execution, because only one line of T-SQL statements does not need to be included for execution.

Syntax
Begin {
SQL _statement | statement_block
}
End

The in... end statement group can be nested.

Example
-- Examples of begin... End statements
Use pubs
Go
Begin
Select * from authors where au_id like '2 *'
End

Begin
Select cust_id, cust_name from custom order by cust_id
End

If... else statement

Syntax
If boolean_expression
{SQL _statement | statement_block}
Else
{SQL _statement | statement_block}
If you want to execute more than one row of T-SQL statements, you must use the begin... end statement Group

Example
-- Example of IF... else statement
If (select AVG (total score) from student score <60)
Print 'your total score is failed'
Else
Print 'the total score is a few. Congratulations! '

-- Example of IF... else statement
If (select sum (order quantity) from order table)> 1000
Print 'they are the best customer'
Else
If (select sum (order quantity) from order table)> 500
Print 'keep in touch with them'
Else
Print 'try again !! '

While loop break and continue statement

Syntax Declaration
While boolean_expression
{SQL _statement | statement_block}
[Break] -- exit the innermost WHILE LOOP
{SQL _statement | statement_block}
[Continue] -- Re-execute the while loop to ignore any T-SQL statement after the continue
If two or more while loops form an embedded while loop, the break statement in the inner layer exits to the next outer loop. First, the T-SQL statement after the end of the lower-layer loop is executed, and then the next outer loop starts.

Example
-- Example of using the while continue break statement
While (select AVG (unit price) from order table) <60
Begin
Set Unit Price = unit price * 4
If (select max (unit price) from order table)> 80
Break
Else
Continue
End

GOTO statement
The GOTO statement can be used to change the execution process to a tag name.

Syntax Declaration
When defining a tag name
Label:
To change the execution
Goto label

There are no special restrictions on label names before or after the GOTO statement.
Note that the GOTO statement can only Jump outside the while or if... else statement to the label name, but cannot jump into the IF... else or while statement from outside.

Example
-- Example of using a GOTO statement
If (select max (unit price) from order table)> 100
Goto max_label
Else
Goto min_label
Max_label:
Print 'too expensive'
Min_label:
Print 'reasonable price'

Return Statement
Exit the batch T-SQL statement that is currently being executedProgramOr the statement is interrupted, and a value is returned or no value is returned.
When a return statement is used together with a stored procedure, its return does not return a null value. However, when a program tries to return a null value, a warning message is generated and a value of 0 is returned.

Syntax Declaration
Return [integer_expression]
Integer_expression indicates the integer to be returned.

Example
-- Return example
Create procedurecheck_maxdata
As
If (select state from authors where au_id = '2017-34-45 ')
Return 1
Else
Return 2

Waitfor statement
A waitfor statement is used to specify the time, interval, or event for triggering the statement group, stored procedure, or transaction execution. After the waitfor statement is executed, you must wait until the specified time or event occurs before using the SQL server connection.
If you want to know who is currently using or waiting for it, you can use the system stored procedure sp_who.

Declaration syntax
Waitfor {delay 'time' | time 'time '}
Parameter description
Delay is used to set the SQL server connection to be used only when the specified number of hours has expired.
Time is used to specify the time to wait
You can only specify a time, but not a date. The waiting time can be set to a maximum of 24 hours.

Time indicates that the SQL Server waits for the specified time.

Example
-- This example will execute the select_data stored procedure.
Begin
Waitfor time '18: 00'
Exec select_data
End

Begin
Waitfor time '15: 20'
Print 'ninhao'
End

Example
-- Waitfor delay example
Use pubs
Go
-- Set SQL Server to wait one minute before executing the following statements
Waitfor delay '00: 01: 00'
Select * from authors
Go

7.11 case, raiserror statement

Case
The expression is executed after the condition list. The case can be divided into two formats:
One format is a simple case format, which compares expressions with a group of simple expressions to determine the results.
2. Another format is the case format of the search. This format evaluates a group of boolean expressions to determine their results.

Simple case statement
Case input_expression
When when_expression then result_expression
[. N]
[
Else else_result_expression
End

If you want to use the search case format, you can declare it according to the following syntax:
Case
When boolean_expression then result_expression
[. N]
[
Else else_result_expression
End

Case if there is no else and there is no comparison operation, and the result is true, the case statement returns a null value.

Example
-- This example describes how to use a simple case format.
Use
Pubs
Go
Select Product Category =
Case type1
When 'Go' then sum1 = sum1 + 1
When 'hardware' then sum2 = sum2 + 1
When 'pharmaceuticals 'then sum3 = sum3 + 1
Else sum0 = sum0 + 1
End
Go

Example
-- This example describes how to use the search case format

Use MIS
Go
Select language score =
Case
When scorel <59 then 'failed'
When scorel> 60 and scorel <= 70 then 'medium score'
When scorel> = 91 and scorel <+ 100 then "excellent performance'
End
Go

Raiserror statement
The raiserror statement returns the custom error message and sets the system flag to record the errors that have occurred.
When you use raiserror to specify custom error messages, use the error message code greater than 50,000 and the Importance level from 0 to 18.

Use the system stored procedure sp_addmessage to create custom error messages

Syntax Declaration of The raiserror statement

When you use the raiserror statement to create and return custom error messages, use sp_addmessage to add custom error messages and use sp_dropmessage to delete custom error messages.
When an error occurs, the error code is placed in the @ error function, which stores the recently generated error code.

Example
-- Example of raiserror
-- Use sp_addmessage to create a custom error message. 76548 indicates the error code, 9 indicates the severity, and "error message test" indicates the message content.
Exec sp_addmessage 76548, 'test the error message'
Go
Raiserror (76548, 9, 1)

7.12 exec statement

A statement that is used to execute a stored procedure or a T-SQL statement.

Example
-- Execute the system stored procedure sp_who
Exec sp_who
Go

Example
-- Execute the program test_pro and pass a parameter

Exec test_pro case_id -- test_pro is the program name, And case_id is the parameter.
Go

Example
-- Use multiple parameters and one output parameter
-- A01 and B01 indicate the parameters to be used for the test_pro program.
-- @ A1 indicates the name of the parameter to be output. We use @ test to accept the value returned by @ A1.
Exec test_pro 'a01', 'b01', @ a1 = @ test output

Example
-- Execute the remote program test1 and pass a parameter
-- Where mis_server.mis.dbo.order1 refers to the execution of mis_server on the remote server. In MIS @ T1, it refers to the status value of the Order1 program after self-execution is accepted.
Exec @ T1 = mis_server.mis.dbo.order1 '2017-098'
Go

Example
-- String
Exec ('false' + 'a098. 18364.47 ')
Go