T_ SQL programming

Time:2020-10-28

Transact SQL language is a programming language provided by SQL server for users. It is the implementation and extension of standard SQL. It has the main characteristics of standard SQL,

At the same time, language elements such as variables, operators, functions and process control are added to make it more powerful.

 

1. Batch processing, what is batch processing

  The so-called “batch” is a complete set of data SQL instructions passed from the client to the server.

effect:

It just tells the query analyzer how many statements are included in the current batch, and the query analyzer will form a string of statements between two go and send it to the server for execution.

Errors in the current batch statement will not affect the next batch

2. Variables in SQL

  Variable declaration:

Declare variable name data type

    Note: the @ sign must be added before the variable name

Assignment of variables:

Single assignment: set variable name = value

Multiple assignments: select variable name = value [, variable name = value]

Assign a value to the query result: select variable name = column name 1 [, variable name = column name..] from table name [where…]

Common system variables (global variables)

@@Error: returns the error number returned by executing the previous T-SQL statement

@@Identity: returns the value of the most recently inserted identity column

@@Procid: returns the ID of the current stored procedure

@@Remserver: returns the name of the remote SQL server server recorded in the login record

@@SPID: returns the ID of the current server process

@@Trancount: returns the number of active transactions in the current connection

@@Version: returns the installation date, version and processor type of the current SQL server server

@@TOTAL_ Errors: returns the total number of reads and writes encountered since SQL Server was started

@@Rowcount: returns the number of data rows affected by the previous SQL statement

   Note: system variables are read-only and cannot be modified or assigned. Generally, system variables are assigned to local variables (i.e. our own variables)

3. Control flow statement

1. If… Else conditional judgment statement

    Grammar format:

The condition of if judgment

Expression | sql1 statement

      ELSE

Expression | SQL2

For example, the student number is used to determine whether the exam scores (exam) of the student database are recorded

      

Declare @stuNo varchar(30)  

      set @stuNo ='135147'

      IF (SELECT COUNT(*) FROM Exam where [email protected])=0

       begin  

          Print ('student number is' [email protected]+ 'students have no record of test results')  

       end

      Else

        begin

           Print ('student number is' [email protected]+ 'students have taken the exam and have a record of their scores')

        end

 

 

 

     **Print: displays the output information on the screen. Beggin – end is equivalent to {} in other languages (used to execute multiple expression statements)

  2. Case statement 

    (1) Simple expression

        Case expression

The value then expression of when compared with condition

          [WHEN …N…. THEN ….  ]

[expression to execute when none of else conditions holds]

        END

       eg:

      

Select stuNo student number, stuname name, stuage, age,

          case   stuSex

             Mr. then

             When 'woman' then '

             Else 'unknown'

          From persons
 

 

 

 

    (2) Select expression

        CASE

Expression of when comparison

           [WHEN ..N … THEN … ]

[expression to execute when none of else conditions holds]

        END 

 

If the compared expression is true, the expression in then is executed

        eg:

          

Select exam number, stuNo student number, score score,

            case

               when score<60  then 'D'

               when score<70 then 'C'

               when score<80  then 'B'

               when score<90 then 'A'

            End 'grade'

          from Exam

 

 

 

3. While loop statement

Grammar:

       While expression

        Begin

//T-SQL statement

           【BREAK】   

          【CONTINUE】

        End

 

    When the result of the expression is true, the server plus will repeat the T-SQL statement in the loop

The function of break is to terminate the loop unconditionally and start to execute the statement immediately following end.

The function of [continue] is to skip this cycle and start the next cycle.

 

Eg: raise the failed written test score to 2, and jump out of the loop when the minimum score is not less than 60

       

 1 declare  @score float
 2 
 3        set @score=0
 4 
 5        while exists (select * from Exam where score<60)
 6 
 7         begin
 8 
 9           update Exam set score+=2 where score<60
10 
11           select @score=MIN(score) from Exam
12 
13           if @score<60
14 
15              continue
16 
17           else
18 
19              break;
20 
21         end

 

 

 

 

4. Function

1. Mathematical function

ABS (numeric expression): find the absolute value and return the absolute value of numeric type expression. For example: select ABS (- 45.3)

ASCII (character type expression): get the ASCII code function and return the corresponding ASCII number of the character, for example: select ASCII (‘h ‘)

AVG ([all| distinct] expression): average value, e.g. select AVG (score) from exam

Count ([all|distinct] expression | *) statistics quantity, for example: select count (*) from exam

Ceiling (numeric expression): rounding up. Returns the smallest integer value greater than or equal to the given numeric expression.

    FLOOR(numeric expression)Next house. Returns the largest integer value less than or equal to the given numeric expression.

Power (numerical expression 1, numerical expression 2): the power operation function. Power (2,3) is the third power of 2.

PI (): calculate pi. No parameters are used

    SQRT(float expression): find the square root of the specified float expression and return the result of float type.

Round (numeric expression, integer): rounds a numeric expression to the specified precision of an integer.

    RAND (integer expression): generates a random number. Returns a random number between 0 and 1. 

2. String function

   —Forward to:https://www.cnblogs.com/ruhaoren/p/12758965.html

Select ASCII ('abc '); -- computes the ASCII value of the first character of a string
Select char (65); -- converts the given ASCII encoding to characters

Select lower ('abc '); -- convert all to lowercase
Select upper ('abc '); -- convert all to uppercase

Select ltrim ('aaa '); -- remove the space on the left
Select rtrim ('aaa '); -- remove the space on the right

Select len ('abc '); -- count the number of characters without the following spaces
Select left ('abcdefg ', 3); -- returns the specified number of characters from the left
Select right ('abcdefg ', 3); -- returns the specified number of characters from the right
Select substring ('abcdefg ', 3,2); -- returns 2 characters from the third character

Select CHARINDEX ('a ','ccbbaa'); -- returns the position of the first parameter string in the specified string
Select replace ('aabbcc ','a','d '); -- replaces the string specified by the second parameter in the first parameter with the third parameter

 

 

 

 

3. Date function

    GETDATE()

Returns the current system time. For example: select getdate () DATEPART(datepart,date)

Returns the specified date part of the given date type data as an integer. The value of the date part of a date type data.

    DATENAME(datepart,date)Returns the specified date part of the given date type data as a string.

   DATEADD(datepart,number,date)An integer value is added on the basis of the specified date part of the given day device variable.

   DATEDIFF(datepart,date,enddate)Returns the difference between the start date and the end date during a given date.

   DAY(date): returns the value of the day part of the specified date.

   MONTH(date): returns the value of month part of the specified date.

   YEAR(date): returns the value of the year part of the specified date.

 

 

Select getdate() -- gets the current system time

Select datename (year, getdate()) -- returns the date year section

Select DateAdd (day, 1, getdate()) -- returns the date plus one in the days part of the date

Select DateDiff (year, '2000-1-1', getdate()) -- returns the difference between two date years

Select day (getdate()) -- returns the days section

Select month (getdate()) -- returns the month

Select year (getdate()) -- returns the year

 

 

 

  

 

    

Recommended Today

Comparison and analysis of Py = > redis and python operation redis syntax

preface R: For redis cli P: Redis for Python get ready pip install redis pool = redis.ConnectionPool(host=’39.107.86.223′, port=6379, db=1) redis = redis.Redis(connection_pool=pool) Redis. All commands I have omitted all the following commands. If there are conflicts with Python built-in functions, I will add redis Global command Dbsize (number of returned keys) R: dbsize P: print(redis.dbsize()) […]