Transact SQL syntax basis of Sqlserver2019 database and summary of common SQL functions

Posted by misterm on Mon, 24 Jan 2022 23:32:34 +0100

Pre school essential knowledge

1, Fundamentals of transact SQL syntax

1.1 constants

The amount of data stored in memory that is always constant is called a constant. Constants, also known as literal or scalar values, are symbols that represent a specific data value. The format of a constant depends on the data type of the value it represents.

1. Numeric constant. Numeric constants include integer constants, decimal constants, and floating-point constants. For example:

13
-56
1314.520
--Floating point constants use symbols e To specify, for example:
select 3.4e3  
select -3.14e1
select 2.5e7  -- e The following numbers indicate"To the power of 10"
0xB0C5

2. String constant. String constants are defined within single quotes. String constants contain alphanumeric characters (A~Z, A~Z and 0 ~ 9) and special characters (such as number font size #, exclamation point!, at character @). For example, the following are string constants:

'Amo'
'Life is short, i need Python. '
--'Equivalent to the escape function, the middle'Became ordinary'Output as is
select 'Oh''MyGod'

3. Date and time constants. The constant values of SQL specified date, time and time interval are specified as date and time constants, for example:

'2021-06-23'
'06/21/2021'

4. Symbolic constant. In addition to user provided constants, SQL contains several unique symbolic constants that represent different common data values. For example:

select CURRENT_TIMESTAMP; --2021-06-23 10:55:11.030
select CURRENT_USER; --dbo

1.2 variables

The amount of data stored in memory that can be changed is called a variable. In order to store information in memory, the user must specify the unit for storing information and name the storage unit to facilitate access to information. This is the function of variables. Transact SQL can use two variables, one is a local variable; The other is global variables. The main difference between local variables and global variables is that the scope of stored data is different.

1. Local variables. Local variables are user-defined variables, and their scope of action is only within the program. The local variable name is user-defined. The named local variable name must comply with the SQL Server identifier naming rules, and the local variable name must start with @.

DECLARE
{
@varaible_name datatype [ ,... n  ]
}
/*
1.@varaible_name:The variable name of a local variable must start with @ and the form of the variable name must conform to the naming method of the SQL Server identifier.
Supplement: naming method of SQL Server identifier.
1, Initial with letter, underline@ The identifier at the beginning ## of symbol (representing local variable or parameter) and # symbol (representing temporary table or procedure) represents global temporary variable!
2, The subsequent components of the identifier can be letters, numbers, @, $, #_
3, Identifiers are not allowed to be T-SQL keywords.
4, Embedding spaces or other special characters is not allowed.

2.The data types used by local variables can be all system data types and user-defined data types except text, ntext or image types.
*/

For example, declare the local variable @ name, and the SQL statement is as follows:

DECLARE @name VARCHAR(10);

There are generally two ways to assign values to variables, one is to use the SELECT statement, and the other is to use the SET statement. The syntax for assigning values to variables using the SET statement is as follows:

{SET @varible_name = ecpression}[ ,... n]

The following is a simple assignment statement:

DECLARE @name VARCHAR(10);
SET @name = 'Amo';

Multiple variable values can also be assigned together. The corresponding SQL statements are as follows:

DECLARE @name VARCHAR(10),@age INT, @sex CHAR(2);
SELECT @name='Amo', @age=18, @sex='male';

The syntax for assigning values to variables using the SELECT statement is as follows:

SELECT @varible_name = expression 
[FROM table_name [ ,... n ]
WHERE clause] 
/*The above SELECT statement is used to assign values to variables, not to query data from tables.
In addition, it is not necessary to use the FROM keyword and WHERE clause in the process of assignment using the SELECT statement.*/

example:

USE student;
DECLARE @courseName NVARCHAR(20);
SELECT @courseName=Course Number  FROM course WHERE Course Number ='K06';
PRINT @courseName;

DECLARE @a INT;
SELECT @a=1;
PRINT @a;

2. Global variable

Global variables are pre-defined variables within the SQL Server system and do not need to be defined by users. For users, their scope of action is not limited to a program, but any program can be called at any time. Global variables are usually used to store some SQL Server configuration settings and performance statistics. SQL Server provides more than 30 global variables. This section only introduces the functions and usage of some common variables. Global variable names start with @ @.

global variable	effect
@@CONNECTIONS	Record the number of all connections made to this server since the last server startup, including unsuccessful attempts to connect. Using @ @ CONNECTIONS allows system administrators to easily get the number of CONNECTIONS trying to connect to this server today.
@@CUP_BUSY	Record the number of connections attempted since the last startup. Whether the connection is successful or failed, the CPU working time in ms.
@@CURSOR_ROWS	Returns the number of data rows retrieved by opening the cursor in this server connection.
@@DBTS	Returns the current value of the timestamp data type in the current database.
@@ERROR	Returns the error code returned by executing the previous transact SQL statement.
@@FETCH_STATUS	Returns the status value returned by the last cursor FETCH operation, and the return value is an integer. 0: FETCH statement succeeded − 1: the FETCH statement fails or the row is not in the result set. − 2: the extracted row does not exist
@@IDENTITY	Returns the value of the last inserted identity column. The return value is numeric.
@@IDLE	Returns the time in ms that the SQL Server server has been in a standstill state since it was last started.
@@IO_BUSY	Returns the time, in ms, that the SQL Server server has spent on input and output since it was last started.
@@LOCK_TIMEOUT	Returns the current timeout setting for data locking.
@@PACK_RECEIVED	Returns the total number of data packets received by the SQL Server server from the network since it was last started.
@@PACK_SENT	Returns the total number of data packets sent by the SQL Server server to the network since it was last started.
@@PACK_SENT	Returns the total number of data packets sent by the SQL Server server to the network since it was last started.
@@PROCID	Returns the ID ID of the current stored procedure.
@@REMSERVER	Returns the name of the remote SQL Server server recorded in the login record.
@@ROWCOUNT	Returns the number of data rows affected by the previous SQL statement. For all SQL statements that do not affect database data, the result returned by this global variable is 0. During database programming, it is often necessary to check the return value of @ @ ROWCOUNT to determine whether the operation has reached the goal.
@@SPID	Returns the ID ID of the current server process.
@@TOTAL_ERRORS	Returns the total number of read-write errors encountered since the SQL Server server was started.
@@TOTAL_READ	Returns the number of disk reads since the SQL Server server was started.
@@TOTAL_WRITE	Returns the number of times the disk was written since the SQL Server server was started.
@@TRANCOUNT	Returns the number of active transactions in the current connection.
@@VERSION	Returns the current SQL Server server installation date, version, and processor type.

1.3 annotators, operators and wildcards

1. Comment. Comment statements are not executable statements and do not participate in the compilation of the program. They are usually some explanatory words to give a brief explanation and prompt on the function of the code or the implementation of the code.

--DECLARE @a INT; Single-Line Comments  
PRINT @@ERROR;

/*DECLARE @a INT;
PRINT @@ERROR;*/ multiline comment 

Note shortcuts: Ctrl + K Then press and hold Ctrl Key, press C
 Uncomment shortcut: Ctrl + K Then press and hold Ctrl Key, press U

2. Operator.

1.Arithmetic operators include:+(plus),-(reduce),*(ride),/(except),%(Surplus). 

2.Assignment operator, i.e. equal sign (=)

3.Comparison operator:>(greater than),<(less than),=(be equal to),>=(Greater than or equal to),<=(Less than or equal to),!=(Not equal to_,!>(Not greater than),!<(Not less than)
And others SQL Server Due to different data types, Boolean data type cannot be specified as the data type of table column or variable, and Boolean data type cannot be returned in result set.

4.Logical operators:
ALL: If a comparison is concentrated, all are TRUE，Then the value is TRUE
AND: If both Boolean expressions are TRUE，Then the value is TRUE
ANY: If any one in a comparison set is TRUE，Then the value is TRUE
BETWEEN: If the operand is in a range, the value is TRUE
EXISTS: If the subquery contains any rows, the value is TRUE
IN: If the operand is equal to one in an expression list, the value is TRUE
LIKE: If the operand matches a pattern, the value is TRUE
NOT: Negates the value of any other Boolean operator
OR: If any Boolean expression is TRUE，Then the value is TRUE
SOME: If some in a comparison set are TRUE If yes, the value is TRUE

5.Bitwise operator.
&: Bitwise AND
|: Bitwise OR
^: Bitwise mutual exclusion OR
～: Bitwise NOT

Operator priorities are as follows:

+(positive) − (negative) ~ (negative)
*(multiply) / (divide)% (take remainder)
+(plus) + (string concatenation operator) - (minus)
=,>,<,>=,<=,<>,!=,!>,!< (comparison operator)
^(bitwise XOR) & (bitwise AND) | (bitwise OR)
NOT
AND
ALL ANY BETWEEN IN LIKE OR SOME
=(assignment)

If the expression contains operators with the same priority, they are processed from left to right. You can also use parentheses to increase the priority of operations, and the expression in parentheses has the highest priority. If the expression has nested parentheses, the innermost expression is evaluated first.

3. Wildcards. In SQL, LIKE keyword and wildcard are commonly used to realize pattern query. The wildcards supported by SQL are shown in the following table:

Wildcard	Description	Example
%	Any character that contains zero or more characters	"Amo%" can mean: "amo", "Amo Cool", "amo?"
_	Any single character	"Amo_" It can be expressed as "AmoS". Only one more character can be followed
[ ]	Specify any single character in the range ([a-f]) or set ([abcdef])	[1-9] 2 represents a string that starts with any character between 1-9 and ends with '2'
[^]	Any single character that does not belong to the specified range ([a-f]) or set ([abcdef])	[^ 0-5] 123 indicates a character that does not start with any character between 0 and 5, but ends with '123'

1.4 process control

Process control statement is a statement used to control the process of program execution. Using process control statements can improve the processing power of programming language. The process control statements provided by transact SQL language are as follows:

BEGIN...END
WHILE	: 
IF...ELSE
BREAK
CONTINUE
RETURN
GOTO
WAITFOR

1,BEGIN…END. Used to combine multiple transact SQL statements into a logical block. When the process control statement must execute a statement block containing two or more T-SQL statements, use the begin... End statement. The syntax format is as follows:

BEGIN
{sql_statement...}
END

[exercise 1] exchange the values of two variables in the BEGIN... END statement block. The operation results are shown in the figure below:

This example does not use BEGIN... END statement, and the result is exactly the same, but BEGIN... END works when combined with some process control statements. In BEGIN... END, another BEGIN... END can be nested to define another program block.

2,IF. The syntax of if structure is as follows:

IF<Conditional expression>
    {command line|Program block}

IF<Conditional expression>
    {Command line 1|Block 1}
[ELSE
    {Command line 2|Block 2}]

[example 1] judge whether a number is a positive number. The SQL statement is as follows:

declare @x int
set @x=3
if @x>0
	print '@x Is a positive number'
print'end'

[exercise 2] judge the parity of a number. The operation results are shown in the figure below:

[example 2] judge the size of two numbers.

declare  @x int,@y int
set @x=8
set @y=3
if @x>@y
	print '@x greater than@y'
else
	print'@x Less than or equal to@y'

The operation results are shown in the figure below:

[exercise 3] enter a coordinate value and judge which quadrant it is in.

3,CASE. The syntax of case structure is as follows:

CASE input_expression
  WHEN when_expression THEN result_expression
     [ ...n ]
  [
     ELSE else_result_expression]
 END

CASE
  WHEN Boolean_expression THEN result_expression
      [ ...n ]
  [
      ELSE else_result_expression]
  END

[exercise 4] query the grades of each student in the grade table in the student database. If the score is greater than or equal to 90, the score is excellent; If the score is less than 90 and greater than or equal to 80, the result is good. If the score is less than 80 and greater than or equal to 70, it shows that the score is passed. Otherwise, a failure will be displayed. The operation results are shown in the figure below:

Slightly more complex SQL statements:

USE pubs
SELECT   kind =
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(30)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type 

--CASE The second form of:
USE pubs
SELECT    'Price Kind' =
      CASE
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 20 THEN 'Very Reasonable Title'
         WHEN price >= 20 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,  
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price

4,WHILE. The while clause is a loop structure supported by T-SQL statements. When the condition is true, the while clause can execute a subsequent T-SQL command cyclically. If you want to loop through a set of commands, you need to use the BEGIN... END clause:

WHILE<Conditional expression>
BEGIN
    <command line|Program block>
END

[exercise 5] find the sum of integers from 1 to 10. The operation results are shown in the figure below:

5,WHILE…CONTINUE…BREAK. The while clause of the loop structure can also control the execution of statements in the while loop with the continue and break commands. The syntax format is as follows:

WHILE<Conditional expression>
BEGIN
    <command line|Program block>
    [BREAK]
    [CONTINUE]
    [command line|Program block]
END

[exercise 6] find the sum of even numbers between 1 and 10, and use CONTINUE to control the output of the statement.

6,RETURN. The return statement is used to unconditionally exit from the query process. The return statement can be used to exit from a procedure, batch, or statement block at any time. Statements after return are not executed. The syntax format is as follows:

RETURN[Integer value]

You can specify a return value within parentheses. If no return value is specified, the SQL Server system will return an internal fixed value according to the result of program execution, as shown in the following table:

Return value	meaning
0	Program execution succeeded
−1	Object not found
−2	Wrong data type
−3	deadlock
−4	Violation of authority principle
−5	syntax error
−6	General errors caused by users
−7	Resource errors, such as insufficient disk space
−8	Non fatal internal error
−9	The limit of the system has been reached
− 10 or − 11	Fatal internal inconsistency error
−12	Table or pointer corruption
−13	Database corruption
−14	Hardware error

The sample SQL statement is as follows:

declare @x int  
set @x=3
if @x>0
	print'encounter return before' 
return
print'encounter return after'

7,GOTO. Used to change the process of program execution, so that the program jumps to the program line specified by the identifier and then continues to execute. The syntax format is as follows:

--Syntax format: GOTO identifier 
--Example:
declare @X int
select @X=1
loving:
    print @X
    select @X=@X+1
WHILE @X<=3 goto loving

8,WAITFOR. Specify the time, interval, or event at which a trigger, stored procedure, or transaction executes; It can also be used to temporarily stop the execution of the program until the set waiting time has passed. The syntax format is as follows:

-- Syntax format: WAITFOR{DELAY<'time'>|TIME<'time'>
-- 1.DELAY: Used to set the waiting time, up to 24 hours.
-- 2.TIME: Used to set the time point for waiting to end.

WAITFOR DELAY'00:00:03'
PRINT'Congcong is sleeping!'

WAITFOR TIME'15:00:00'
PRINT'Favorite song: Dance'

2, SQL function

(1) Sum function: returns the sum of all values in an expression or the sum of non repeating values.
(2) AVG (average value) function: returns the average value of each value in the group, ignoring null values.
(3) Min function: used to return the minimum value in the expression.
(4) Max function: used to return the maximum value in an expression.
(5) Count function: used to return the number of items in a group.
(6) Distinct function: evaluate the specified set, delete the duplicate tuples in the set, and then return the result set.
(7) ABS (absolute value) function: used to return the absolute value of a numeric expression.

SELECT ABS(1.5),ABS(-2.5);

(8) PI (PI) function: used to return the constant value of PI.

SELECT PI();

(9) Power function: used to return the result of a power operation on a numeric expression.

SELECT POWER(2,2),POWER(3,3),POWER(4,4);

(10) Rand (random floating point number) function: used to return random float values of 0 ~ 1.

DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter < 4
	begin 
		--SELECT RAND(10) Random_Number
		SELECT RAND() Random_Number
		SET @counter = @counter + 1
	end;

(11) Round function: used to return a value rounded to a specified length or precision.

SELECT ROUND(123.9994,3),ROUND(123.9995,3);

(12) Square function and sqrt function.

SELECT SQUARE(4)  --16

declare @mysqrt float
set @mysqrt = 1.00
while @mysqrt < 10.00
	begin
		select SQRT(@mysqrt)
		select @mysqrt = @mysqrt + 1
	end

(13) The string functions are as follows:

Function name	purpose
ASCII	Returns the ASCII code value of the leftmost character of a character expression
REVERSE	Returns the inversion of a character expression
CHARINDEX	Returns the starting position of the specified expression in a string
RIGHT	Starting from the right, get the specified number of characters on the right side of the string
LEFT	Starting from the left, get the specified number of characters on the left of the string
STR	Returns character data converted from numeric data
LEN	Returns the number of characters (not bytes) of the specified string
SUBSTRING	Returns the specified number of characters
REPLACE	Replaces the specified string with another specified string

[exercise 7] use the ASCII function to return the ASCII code value of AMOXiang.
[exercise 8] use the RIGHT function to query the last two digits of the grade table student number in the student database.

(14) The date and time functions are as follows:

Function name	purpose
DATEADD	Returns a new datetime value after adding a period of time to the specified date
DATEDIFF	Returns the number of date and time boundaries that span two specified dates
GETDATE	Returns the current system date and time
DAY	Returns an integer of days in a specified date
MONTH	Returns an integer of the month in the specified date
YEAR	Returns an integer of the year in the specified date

(15) The cast function and CONVERT function are as follows:

Function name	purpose
CAST	Used to convert an expression display of one data type to another.
CONVERT	Similar to the function of CAST function, this function is not an ANSI standard SQL function, which converts data into another data type according to the specified format.

Topics: SQL

Programmer Think