Discussion on Transact-SQL and transactsql

Discussion on Transact-SQL and transactsql

Transact-SQL (also known as T-SQL), is in Microsoft SQL Server and Sybase SQL Server ansi SQL implementation, and Oracle PL/SQL properties are similar (not only to achieve ansi SQL, it also provides implementation support for the features of its own database system). Currently, it is still used as the core Query Language in Microsoft SQL Server and Sybase Adaptive Server.

Transact-SQL is a set of SQL commands with batch and block features. database developers can use it to write Data-based Business Logic ), to forcibly limit the data control capability of front-end applications. It is also the main development language for database objects.

Language Structure

Transact-SQL is mainly composed of ansi SQL. Currently, Microsoft-implemented Transact-SQL supports ANSI SQL-92 standards.

Ansi SQL basic syntax support

DDL

Primary entry: Data Definition Language

DDL (Data Definition Language) is a control syntax for database objects. It is added to database objects (such as Data tables, pre-stored programs, functions, or custom types, this syntax is used for both modification and deletion.

CREATE (CREATE database object) ALTER (modify Database Object) DROP (delete database object)

DML

Primary entry: data manipulation language

DML (Data Manipulation Language) is commonly known as the CRUD (Create/Retrieve/Update/Delete) function by developers. It refers to four new functions of Data, including adding, intercepting, modifying, and deleting Data.

SELECT (R) INSERT (C) UPDATE (U) DELETE (D)

DCL

Master entry: Data Control Language

DCL (Data Control Language) is a security function provided by the database. The access principles and permissions to databases and database objects are defined by DCL.

GRANT (GRANT permissions) REVOKE (REVOKE permissions) Batch

You can use semicolons (;) to partition different SQL commands. For example:

Insert into myTable (myText) VALUES (@ myText); SELECT @ IDENTITY

Control Flow syntax

Transact-SQL supports the following control flow Syntax ):

BEGIN... END indicates the SQL instruction block. commands packaged using BEGIN... END are considered as the same instruction block. IF... ELSE conditional and can support nested IF statement. IF the IF or ELSE instruction contains more than two, you must use BEGIN... END to indicate the block. Otherwise, a syntax check error occurs. WHILE loop, which is the only loop supported by Transact-SQL. the commands in the loop should be wrapped in BEGIN... END. RETURN: The block operation can be forcibly terminated. WAITFOR, you can force the statement to continue running after a specified time. GOTO: directs the running command to the specified location. Custom Variables

In Transact-SQL, you can use DECLARE to DECLARE the variable, SET the variable value, and SELECT @ var = column, get the variable value from a declarative return value.

DECLARE @ v INT -- declare a variableSET @ v = 50 -- set variable directly. SELECT @ v = SUM (Qty) FROM SaleItemRecords WHERE SaleID = 53928 -- set variable from a result of statement

Error Handling

The following methods can be used to process or cause errors in a block:

RAISERROR. TRY... CATCH, which uses a structured method to handle errors (only supported by Microsoft SQL Server's Transact-SQL ). PRINT to PRINT the variable value.

Microsoft Transact-SQL introduction address: http://msdn.microsoft.com/zh-cn/library/bb510741.aspx

Transact-SQL statements

The T-SQL can be understood as the enhanced and extended version of SQL, feel more like a programming language, not just the query language. Added variables to indicate that data definition, data control, process control, and other functions also support embedded functions.
The specific words are unclear.

What is the SQL statement pair?

The SQL in the database generally refers to the standard SQL, that is, the ANSI SQL-92. T-SQL is the language engine of SQL Server, while Oracle's language engine is PLSQL. Both query languages have extended the ANSI SQL-92 standard to provide additional support.