Chapter fourth T-SQL programming

1. Preface
-"This T-SQL programming is based on the SQL Server development environment
-"keywords: t-SQL programming; cursors; views and indexes

2. T-SQL Programming basics
-"identifier: Regular identifiers must be in kanji, letters, underscores _, @, or #开头, cannot use SQL Server keyword
-"Variable
• Declared format: DECLARE @ variable name data type
Note: When a local variable is declared, the variable is initialized to null
Example: Declare @CurrentDateTime char (30)
• Set (SET statement) format: set|select @ variable name = value
Note: The value assigned must match the declared value
Example: Select @CurrentDateTime = Getdate ()
• Output format: print|select @ variable Name
• Global variable: @@ 变量 name
Note: System-provided and pre-stated
Example: @ @version--database version
@ @identify-called after inserting, returns the nearest identifier
@ @servername-server name
@ @erro--Returns the error number of the previous execute T-SQL
@ @rowcount--Returns the number of rows affected by the previous execution statement

3. Operators
-"arithmetic operator: + 、-、 *,/,%
-"Assignment Operator: =
-"Bitwise operators:
• Bitwise AND (&): All 1 take 1, otherwise take 0;
• Bitwise OR (|): one for 1 fetch 1, otherwise take 0;
• Bitwise XOR (^): different take 1, otherwise take 0.
-"comparison operator: used to determine the logical value of the result bit: Ture, false, and unknown
equals (=), greater than (>), less than (<)
Not equal to (<> or! =), not less than (!<), not greater than (!>)
Greater than or equal (>=), less than or equal to (<=)
-"logical operators: all, and or &&, any or some, between, exists, in, like, not or!, or or | |
-"String connection operator: @ variable name = ' + ' + + '
-"Unary operator
-Precedence of the operator

4. Process Control Statements
-"Begin End statement: Combines multiple statements into a single block of statements and treats them as a unit processing, equivalent to (...)
• Format: Begin
Statement 1
···
End
-"If Else statement:
• Format: If condition expression
Statement 1
Else
Statement 2
-"Case statement
• Format one: case input expression
When expression 1 then result expression/* If the input expression = expression 1 is ture, the result expression is valid */
···
[Else expression]
End
• Format II: case
When boolean-expression then-expression
···
[Else expression]
End

-"while statement
• Format: While conditional expression
begin
Statement 1 [break]/* Jump out of loop */
[statement 2] [Continue]/* Skip continue after statement */
[statement 3]
End
-Goto statement:
• Format: Tag name:
Statement 1
...
If condition expression
Goto label name
-Exception handling statement
• Format: Begin try
...
End Try
Begin catch
...
End Catch

5, Function
-system built-in functions
• Math functions
Floor (number) to find integers less than real numbers
round (number, 0) rounding
Power (x, y) for X's y-square
• DateTime function
getdate () Returns the current date and time of the System
Day (date_expression) returns the date in the expression of the days
Month (date_expression) returns the month in the date expression
Year (date_expression) Returns the year of the date expression
• Aggregate function
in the database query section
• String function
ASCII (char_expression) returns the ASCII value of the leftmost character in the expression
char (integer_ expression) returns the ASCII code value corresponding to the character
Lower (char_expression) uppercase to lowercase character upper (char_expression) lower case to uppercase
-"user-defined function
· Scalar function: Returns a scalar value of type
--format: Create function function name (formal parameter)/* function name and formal parameter definition section, formal parameter format: @ variable name data type */
Returns data type/* return type of parameter */
[as] Begin
Function Body
return value
End
• Inline Value table function: Return value as Table Form
--format: Create function function name (formal parameter)
returns table
[as] return SELECT statement
• Multi-statement table-valued Function: can be viewed as a combination of the above two functions
-format: Create function function name (parameter) returns @ return variable name table
[as] begin
Function Body
Return
End

6, cursor
-concept
• Mechanism for extracting one record at a time from a result set that includes multiple data records
• Action on each row in the result set returned by the query statement instead of the entire result set.
-"Three types: T-SQL cursors, API cursors, client cursors
-" declaration Cursors
· SQL-92 syntax format:
DECLARE cursor name [insensitive] [SCROLL] cursor
for sql-statement
[for {Read Only | update [of column name [, ··· N]]}]
• Parameter description: Insensitive indicates that the record selected by the cursor definition is stored in a temporary table
Scroll indicates that all read operations (Next | prior | first | last | absolute) are available without having to close the cursor and re-open it
Read only indicates that data is not allowed to be updated
• Example: Declare mycursor cursor
for Select * from Tbmydata
-"Open cursor
• Syntax format: open [Glob AL] Cursor name/*global defines cursor as global cursor */
• Example: Open MyCursor
Go
-read cursor
• syntax format: fetch [Next | prior | first | last | absolut e{n | @nvar} | Relative{n | @var}]
from cursor name
• Parameter description: Next: Returns the next row of the current row in the result set and increments the current number of rows to the number of rows returned;
• Example: Fetch NEXT from MyCursor
-"Close cursor" br> Syntax Format: Open [global] cursor name
Go
• Parameter description:
• Example: Close MyCursor
Go
-"release cursor: When the cursor is no longer needed
• Syntax format: deallocate [glob AL] Cursor name
Go
• Parameter description:
• Example: deallocate mycursor
Go

7. View (also called virtual table)
-"CREATE VIEW"
• Syntax format: CREATE view name [column name (, ... N)]
With View properties
As SQL statement block
Go
-"Modify View"
• Syntax format: ALTER VIEW name [column name (, ... N)]
With View properties
As SQL statement block
Go
-"Use view:
Query
Example: SELECT * from view name
Modify
Example: INSERT into view name values (' 0110301 ', ' Zhang San ', ' Male ', 20)
-Delete Views: Drop View Name

8. Index
-"Purpose: Improve system performance, speed up data query and reduce system response time
-"Index Type"
• Clustered index: Physically sort tables and views (tables and views up to 1 indexes)
• Nonclustered indexes: Directories are purely directories, and the body is purely a sort of body
-"CREATE INDEX"
• Syntax format: create [unique][clustered | nonclustered] Index name
On table name or view name (column name [ASC | desc][, N])
-"Indirect creation of indexes: defining primary KEY constraints and uniqueness constraints when creating tables
-"Create indexed view"
• Example: Create a "schoolgirl" view and create a unique clustered index in ascending order for the View by "student number"
--Create a view: Use teaching Library
Go
CREATE VIEW female Student
With schemabinding
As
Select school number, name, gender, professional from dbo. Student table
where sex = ' woman '
--Create an index:
Create unique clustered index schoolgirl on schoolgirl (student number)
-"View index information:
EXEC sp_helpindex table name
or EXEC sp_help table name
-"Delete index:
Drop Index Table name. Index Name

Chapter fourth T-SQL programming