tsql

In this article, we’ll take a look at a brief TSQL history and a few examples of loops, conditionals, and stored procedures

在本文中，我们将看一下简短的TSQL历史记录以及一些循环，条件和存储过程的示例

介绍 (Introduction)

TSQL also named T-SQL or Transact-SQL is an SQL extension used in SQL Server created and used by Sybase to write queries. In this article, we will talk about the Transact-SQL history including some SQL history to understand the evolution.

TSQL也称为T-SQL或Transact-SQL，是Sybase创建并用于Sybase编写查询SQL Server中SQL扩展。 在本文中，我们将讨论Transact-SQL历史记录，包括一些SQL历史记录以了解其演变。

要求 (Requirements)

1. and SSMS to run queries and code 和SSMS以运行查询和代码
2. database installed 数据库

SQL历史 (SQL history)

To understand T-SQL, you need to study SQL first. The SQL which was initially SEQUEL (Structured English Query Language). This language was initially developed by IBM in San Jose, California. The name SEQUEL was changed to SQL later due to some trademark problems with a UK company, but now SEQUEL is the official pronunciation of SQL

要了解T-SQL，您需要首先学习SQL。 该SQL最初是SEQUEL（结构化英语查询语言）。 该语言最初由IBM在加利福尼亚州圣何塞开发。 后来由于一家英国公司的商标问题，名称SEQUEL更改为SQL，但现在SEQUEL是SQL的正式发音

SQL now stands for Structured Query Language and it is a standard used by the databases to query Structured Languages. Even several NoSQL databases use similar languages.

SQL现在代表结构化查询语言，它是数据库查询结构化语言所使用的标准。 甚至几个NoSQL数据库都使用类似的语言。

There were several revisions of the SQL. The first revision was on 1986 (SQL-86) the second one on 1989 (SQL-89) and the main revision was . After that major revision, there were other minor revisions:

SQL有多个修订版本。 第一个版本是1986年（SQL-86），第二个版本是1989年（SQL-89），主要版本是 。 在该主要修订之后，还有其他次要修订：

• (included some OLAP concepts, Boolean data types, role-based access) （包括一些OLAP概念，布尔数据类型，基于角色的访问）
• SQL 2003 (included some XML features, windows functions, more OLAP features, and other features)
  SQL 2003（包括一些XML功能，Windows功能，更多的OLAP功能和其他功能）
• SQL 2006 (includes more features related to Storing XML, XQuery, etc.)
  SQL 2006（包括与存储XML，XQuery等有关的更多功能）
• SQL 2008 (includes definitions for TRUNCATE statements, FETCH Clauses, INSTEAD OF)
  SQL 2008（包括TRUNCATE语句，FETCH子句，INSTEAD OF的定义）
• SQL 2011 (includes revisions for temporal data, some additional definitions for Windows Functions and also the FETCH clause
  SQL 2011（包括时态数据的修订版，Windows Functions的一些其他定义以及FETCH子句
• SQL 2016 includes features related to JSON data, polymorphic table functions, and row pattern matching
  SQL 2016包含与JSON数据，多态表函数和行模式匹配相关的功能
• SQL 2019 is related to the multidimensional arrays
  SQL 2019与多维数组有关

TSQL的历史 (TSQL history)

T-SQL is based on SQL but extended. T-SQL means Transaction SQL and it was originally used by Sybase (a database than was popular in the past) and then after a while used by Microsoft SQL Server. If you did not know, SQL Server was based in Sybase technology at the beginning. For more information about SQL Server history, refer to this article:

T-SQL基于SQL，但已扩展。 T-SQL表示事务处理SQL，它最初由Sybase（比过去更流行的数据库）使用，然后不久后由Microsoft SQL Server使用。 如果您不知道，SQL Server最初是基于Sybase技术的。 有关SQL Server历史记录的更多信息，请参考本文：

TSQL基础 (TSQL basis)

In this article, we will not teach SQL which is a database query language used by the databases. We will focus on Transact-SQL that includes some functionality not necessarily included in SQL.

在本文中，我们不会讲授SQL，这是数据库使用的数据库查询语言。 我们将重点介绍Transact-SQL，它包括一些未必包含在SQL中的功能。

You can use Transact-SQL to solve some programming problems not related to databases. What I am trying to say is that you can write code and solve problems not related to databases.

您可以使用Transact-SQL解决一些与数据库无关的编程问题。 我要说的是，您可以编写代码并解决与数据库无关的问题。

要求 (Requirements)

In order to start, you will need and (SSMS). You could use other tools to write the code, but this is the official tool.

为了开始，您将需要 和 （SSMS）。 您可以使用其他工具编写代码，但这是官方工具。

For queries, we will use the database.

对于查询，我们将使用数据库。

TSQL示例 (TSQL samples)

Let’s start with some code:

让我们从一些代码开始：

The first example is the famous Hello world. To do this we can use the following code:

第一个例子是著名的Hello world。 为此，我们可以使用以下代码：

SELECT 'hello world'

You could also use the PRINT command:

您还可以使用PRINT命令：

PRINT 'hello world'

The select prints as a column the result of Hello world, whereas the PRINT, prints as a message.

select将Hello world的结果打印为一列，而PRINT作为消息打印。

TSQL变量 (TSQL variables)

You can use variables. The following example will show how to select characters, numbers, and dates:

您可以使用变量。 以下示例将显示如何选择字符，数字和日期：

declare @integer integer=0declare @char varchar(20)='This is a value'declare @myDate date='8/27/2017' select @integer as integer, @char as character, @myDate as Date

For more information about data types, refer to this article:

有关数据类型的更多信息，请参考本文：

The next example shows how to use a variable in a query. We will use the variable in a query. We will store the value 3 in the variable departmentid and show the department id and name from the table humanresource.department where department id is equal to 3:

下一个示例显示如何在查询中使用变量。 我们将在查询中使用该变量。 我们将值3存储在变量Departmentid中，并从表humanresource.department中显示部门ID和名称，其中部门ID等于3：

DECLARE @departmentid integer=3SELECT [DepartmentID],[Name]FROM [HumanResources].[Department]WHERE [DepartmentID]=@departmentid

TSQL循环 (TSQL LOOPS)

In this programming language, you do not have a loop sentence like in C++, C#, and Java. You use the WHILE Sentence instead.

在这种编程语言中，没有像C ++，C＃和Java这样的循环语句。 您可以改用WHILE句子。

The following example will create a table named myIDs and insert 10 values using a WHILE sentence:

以下示例将创建一个名为myIDs的表，并使用WHILE语句插入10个值：

CREATE TABLE myIDs( id integer) DECLARE @counter int=0  WHILE @counter<10 BEGIN   INSERT INTO myIDs(id) values (@counter)   SELECT @counter=@counter+1 END   SELECT COUNT(id) FROM  myIDs

TSQL如果有条件 (TSQL if conditionals)

The use of conditionals uses the IF sentences. The use of IF and ELSE is similar to other programming languages, the following examples show the use of IF and ELSE.

使用条件句使用IF语句。 IF和ELSE的使用类似于其他编程语言，以下示例显示IF和ELSE的使用。

The following example detects how old is a person with the date. The function GETDATE gets the current date and the variable myBirthDay store the birthday date. The function DATEDIFF gets the number of years between the current date and the birthday. Finally, the if sentence classifies as adult or children according to the age calculated. If the age is higher than 18, the person is an adult. Otherwise, the person is a child.

以下示例检测具有日期的人的年龄。 函数GETDATE获取当前日期，变量myBirthDay存储生日。 函数DATEDIFF获取当前日期和生日之间的年数。 最后，if句子根据计算出的年龄分为成人或儿童。 如果年龄大于18岁，则该人为成年人。 否则，此人是小孩。

DECLARE @myBirthDay date= '03/19/1979'DECLARE @currentDate datetime=GETDATE()DECLARE @age integer=DATEDIFF(year,@myBirthDay,@currentDate)IF @age>18   SELECT 'Adult' as ageELSE   SELECT 'Child' as age

TSQL存储过程 (TSQL Stored Procedure)

Using Stored Procedures is a best practice and it is highly recommended in SQL Server. If possible, always use stored procedures instead of a raw T-SQL query for security and performance.

使用存储过程是一种最佳做法，强烈建议在SQL Server中使用它。 如果可能，请始终使用存储过程而不是原始T-SQL查询来提高安全性和性能。

The following example will show how to use a stored procedure with a parameter. In this stored procedure, we have a stored procedure named CountryName and we pass as a parameter the Country Region Code. We do a select of two columns of the table Person.CountryRegion from the Adventureworks database where the region code is specified as a parameter:

下面的示例将说明如何使用带有参数的存储过程。 在此存储过程中，我们有一个名为CountryName的存储过程，并将“国家（地区）代码”作为参数传递。 我们从Adventureworks数据库中选择表Person.CountryRegion的两列，其中将区域代码指定为参数：

CREATE PROCEDURE CountryName@CountryRegionCode nvarchar(2)AS SELECT   [CountryRegionCode],  [Name]FROM [Person].[CountryRegion]WHERE CountryRegionCode=@CountryRegionCode

If we want to invoke the stored procedure, the following code should be used:

如果我们要调用存储过程，则应使用以下代码：

execute CountryName @CountryRegionCode='BO'

We are calling the stored procedure CountryName and we are sending the Region Code.

我们正在调用存储过程CountryName，并且正在发送地区代码。

结论 (Conclusion)

In this article, we learned about SQL and Transact-SQL. We first learn the history first and then we jump into basic code. We learned loops, conditionals, and stored procedures.

在本文中，我们了解了SQL和Transact-SQL。 我们首先学习历史，然后跳入基本代码。 我们学习了循环，条件和存储过程。

翻译自:

tsql