• 初识Transact-SQL

    文章目录

    一、引言

    前面学习了SQL Server代理工具，使用SQL Server 代理时，尤其在作业步骤编辑中，是需要写脚本的（俗称写作业），而微软首推的肯定是加了自己特色的脚本语言，也就是标题中的Transact-SQL（简称T-SQL或TSQL）。

    T-SQL的特色从它的命名上也可以看出一二，在SQL的基础上加了一些东西。
    Transact有处理的意思，而SQL是结构化查询语言，所以T-SQL是往往用于处理SQL相关事务。比如备份数据库。

    如果你用过标准的SQL就会发现它们很相似，但也有不同。这些不同不仅表现在语法方面，还有T-SQL是一种脚本语言，它很有可能去处理数据，它需要做的事情按理说也比SQL语句多。因此，微软在SQL的基础上，拓展了它，加了许多函数以及其他功能。

    从使用场景来讲，SQL通常处理简单的增删改查就够了（不排除有些场景需要用一些相对复杂的操作，表连接、分组等）。而T-SQL的场景，一般比增删改查复杂，往往要生成新表、合并数据等。

    因此有必要系统学习T-SQL。接下来，从编写基础语句中学习T-SQL。

    二、编写T-SQL语句

    编写前准备，
    要进行接下来的步骤，你并不一定要熟悉SQL语言，但是你应该对数据库的基本概念有个认识，例如表、字段这种基本概念。

    系统必须安装以下软件：

    • 任意版本的SQL Server。
    • SQL Server Management Studio（简称SSMS或MS）。

    接下来教程会展示如何创建一个数据库，在数据库中创建一个表，向表中插入数据、更新数据、读取数据、删除数据，然后删除表。并且会创建视图和存储过程，为数据库和数据配置用户。

    你可以通过以下方式编写T-SQL语句并提交给数据库引擎：

    • SSMS（本教程默认使用该方式）。
    • sqlcmd。
    • 通过创建的应用程序进行连接。

    无论用哪种方式提交代码语句，代码都以相同的方式和权限在数据库引擎上执行。

    要在SSMS中运行T-SQL语句，需要打开SSMS并连接到SQL Server数据库引擎的实例。

    0. 语法规则

    有些语法元素在T-SQL编写过程中会经常遇到，虽然不知道也不影响使用，但这边还是列出来，供加深学习。

    语法用途
    大写字母表示T-SQL关键字
    斜体用户提供的参数
    |(竖杠)选择一个语法项
    [ ]可选项
    { }必须项，不用输入大括号
    [,…n]表示上一项重复n次，事件之间用逗号分隔
    <标签>一个语法块名称。使用此语法对可以在语句中多个位置使用的长语法或语法单元进行分组和标记

    1. 创建&查询数据库对象

    1.1. 创建一个数据库

    和许多T-SQL语句一样，CREATE DATABASE语句有一个必需的参数：数据库名称。CREATE DATABASE也有许多可选的参数，例如放置数据库文件的磁盘位置。在不使用可选参数的情况下执行CREATE DATABASE时，SQL Server会对这些参数使用默认值（这里可以看出，T-SQL语句解析时和代码里调用函数差不多，也有函数参数，没带参数时还会用默认参数）。

    1. 在查询编辑器中，添加以下代码：

      CREATE DATABASE TestData  
GO

      • 1
      • 2

      查询编辑器，右键数据库>新建查询就出来了。

    2. 鼠标指针选中代码中的单词 CREATE DATABASE，然后按下F1。会在SQL Server在线文档中打开CREATE DATABASE主题。你可以使用这种方式来查找CREATE DATABASE和其他语句的完整语法。

    3. 在查询编辑器中，按F5执行语句会创建一个名为TestData的数据库。

    在创建数据库时，SQL Server会生成模型数据库的副本，并将副本重命名为数据库名称。这个操作应该只需要几秒钟，除非将数据库的初始大小指定为可选参数。

    ❗注意
    在单个批处理中提交多个语句时，关键字GO可以将语句分隔开。
    当批处理只包含一条语句时，GO是可选的。

    1.2. 创建一张表

    要创建表，必须提供表名，以及表中各列的名称和数据类型。指示每列中是否允许空值是一种很好的实践（这句话是说每列的值是否可以为空是可以指定的，是否能为空会对代码产生一定影响，而这种影响有时候是正面的有时候是负面的，需要实践中体会）。为了创建表，你还必须在包含该表的模式上有CREATE TABLE和ALTER SCHEMA权限。db_ddladmin固定数据库角色具有这些权限。

    大部分表都有一个主键，由表的一个或多个列组成。主键始终是唯一的。数据库引擎会强制，让表中无法重复主键值。

    ❗注意
    数据库引擎可以按区分大小写（case-sensitive）或不区分大小写的方式安装。
    若数据库引擎是区分大小写的，则对象名必须始终具有相同的大小写。例如，名为OrderData的表与名为ORDERDATA的表是不同的表。
    若数据库引擎不区分大小写，则上面两个表会被认为是同一个表，且该名称只能使用一次。

    1.2.1. 切换查询编辑器连接到TestData数据库

    在查询编辑器中，添加并执行下列代码来改变连接至TestData数据库。

    USE TestData
GO

    • 1
    • 2

    ❗注意
    这个变更数据库连接很重要，如果你不先连接到数据库，你是没办法用该数据库里的东西的。
    就比如你在代码里去操作数据库，首先就要连接数据库，然后才能操作数据库里的内容。
    这边的USE大概就是这个作用。

    1.2.2. 创建表

    在查询编辑器中，添加并执行以下代码，以创建一张名为Products的表。表中的列命名为ProductID、ProductName、Price和ProductDescription。ProductID列为表的主键。int、varchar(25)、money和varchar(max)分别是它们的类型。Price和ProductionDescription列可以为空。该语句包含一个称为 schema（模式） 的可选元素（dbo.）。schema是拥有表的数据库对象。若你是管理员，dbo是默认模式，dbo代表数据库所有者。

    CREATE TABLE dbo.Products  
   (ProductID int PRIMARY KEY NOT NULL,  
   ProductName varchar(25) NOT NULL,  
   Price money NULL,  
   ProductDescription varchar(max) NULL)  
GO

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    1.3. 在表中插入并更新数据

    现在，你已经创建了Products表，接着准备使用INSERT往表中插入数据。插入数据后，使用UPDATE语句更改行内容。你将使用UPDATE语句的WHERE子句来更新单行。接下来四条语句将会产生以下数据。

    ProductIDProductNamePriceProductDescription
    1clamp12.48Workbench clamp
    50Screwdriver3.17Flat head
    75Tire BarTool for changing tires.
    30003 mm Bracket0.52
    -- 基本语法是：
INSERT 表名 列名的列表 VALUES 要插入的值的列表
-- 行前面的两个 “-” 表示该行是注释，编译器会忽略该文本

    • 1
    • 2
    • 3
    1.3.1. 插入数据到表中

    1. 执行下面语句，以在之前创建的Products表中插入一行（也可以叫插入一条记录）。

      -- Standard syntax  
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)  
	VALUES (1, 'Clamp', 12.48, 'Workbench clamp')  
GO

      • 1
      • 2
      • 3
      • 4

    ❗注意
    若插入成功，继续下一步。
    若插入失败，可能是因为Products表中已经有包含该产品ID的行了。
    想要继续，得删除表中已有行，并重复前面的步骤。
    用TRUNCATE TABLE可以删除表中的所有行。
    运行下面命令以删除表中所有记录：

    TRUNCATE TABLE TestData.dbo.Products;
GO

    • 1
    • 2

    在删除表后，重复前面的INSERT命令。

    1. 下面语句展示了，如何通过切换字段列表和值列表中的ProductID和ProductName的位置来更改提供参数的顺序（其实很简单，就是将字段列表的字段和对应的值列表的值都换个位置）。

      -- Changing the order of the columns  
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)  
    VALUES ('Screwdriver', 50, 3.17, 'Flat head')  
GO

      • 1
      • 2
      • 3
      • 4

    2. 下面语句演示了列的名称是可选的，只要值按正确顺序列出。这种语法很常见，但不推荐使用，因为可读性会下降。Price列指定为NULL，表示该产品的价格未知。

      -- Skipping the column list, but keeping the values in order  
INSERT dbo.Products  
    VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')  
GO

      • 1
      • 2
      • 3
      • 4

    3. 若访问和更改的是默认模式的表，模式名就是可选的。因为ProductDescription列允许为NULL，并且没有提供默认值，所以可以从语句中完全删除ProductDescription列名和值。

      -- Dropping the optional dbo and dropping the ProductDescription column  
INSERT Products (ProductID, ProductName, Price)  
    VALUES (3000, '3 mm Bracket', 0.52)  
GO

      • 1
      • 2
      • 3
      • 4
    1.3.2. 更新Products表

    输入并执行下面UPDATE语句，将第二个茶农的ProductName从Screwdriver改为Flat Head Screwdriver。

    UPDATE dbo.Products  
    SET ProductName = 'Flat Head Screwdriver'  
    WHERE ProductID = 50  
GO

    • 1
    • 2
    • 3
    • 4

    1.4. 从表中读取数据

    使用SELECT语句来读取表数据。SELECT语句在T-SQL中非常重要，其语法有许多变体。本节中，你将看到它的5个简单使用场景。

    1.4.1. 读取表中数据

    1. 输入并执行下面语句，以读取Products表的数据：

      -- The basic syntax for reading data from a single table  
SELECT ProductID, ProductName, Price, ProductDescription  
    FROM dbo.Products  
GO

      • 1
      • 2
      • 3
      • 4

    2. 可以使用星号（*）来选中表中所有列。即使后面表的结构发生变化，你用*还是会返回新的所有列。

      -- Returns all columns in the table  
-- Does not use the optional schema, dbo  
SELECT * FROM Products  
GO

      • 1
      • 2
      • 3
      • 4

    3. 你也可以省略不想返回的列。这些列将按照它列出的顺序返回：

      -- Returns only two of the columns from the table  
SELECT ProductName, Price  
    FROM dbo.Products  
GO

      • 1
      • 2
      • 3
      • 4

    4. 使用WHERE从句可以限制返回的行：

      -- Returns only two of the records in the table  
SELECT ProductID, ProductName, Price, ProductDescription  
    FROM dbo.Products  
    WHERE ProductID < 60  
GO

      • 1
      • 2
      • 3
      • 4
      • 5

    5. 你还可以在返回的列中处理这些值。下面示例对Price列执行数学运算。以这种方式更改的列没有名称，不过你可以使用AS关键字给它提供名称：

      -- Returns ProductName and the Price including a 7% tax  
-- Provides the name CustomerPays for the calculated column  
SELECT ProductName, Price * 1.07 AS CustomerPays  
    FROM dbo.Products  
GO

      • 1
      • 2
      • 3
      • 4
      • 5
    1.4.2. 在SELECT语句中使用函数

    有关用于处理SELECT语句中的函数的信息，可以参考下面主题：

    1.5. 创建视图和存储过程

    视图（view）是一个存储的SELECT语句，或者说它是一张虚拟表，其内容由查询来定义。
    而存储过程（stored procedure）是一个或多个作为批处理执行的T-SQL语句。

    视图像表一样被查询，不接受参数。存储过程比视图更复杂。存储过程可以同时具有输入和输出参数，并且可以包含用于控制代码流的语句，如IF和WHILE语句。对数据库中的所有重复操作使用存储过程是一种好的编程习惯。

    本例中，会使用CREATE VIEW创建一个视图，该视图选中Products表中的两列。接着，使用CREATE PROCEDURE创建一个存储过程，它只接受一个价格参数并返回那些价格低于指定参数的产品。

    1.5.1. 创建一个视图

    执行下面语句来创建一个视图，该视图会执行select语句，并向用户返回产品的名称和价格。

    CREATE VIEW vw_Names
	AS
	SELECT ProductName, Price FROM Products;
GO

    • 1
    • 2
    • 3
    • 4
    1.5.2. 测试该视图

    视图就像表一样被使用。使用SELECT语句访问视图。

    SELECT * FROM vw_Names;  
GO

    • 1
    • 2
    1.5.3. 创建一个存储过程

    下面语句会创建一个名为pr_Names的存储过程，接受一个名为 @VarPrice的money类型的输入参数。该存储过程打印语句Products less than与输入参数连接，输入参数从money数据类型变更为 varchar(10) 字符数据类型。 然后，该过程在视图上执行SELECT语句，将输入参数作为WHERE子句的一部分传递。这将返回成本低于输入参数值的所有产品。

    CREATE PROCEDURE pr_Names @VarPrice money  
   AS  
   BEGIN  
      -- The print statement returns text to the user  
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));  
      -- A second statement starts here  
      SELECT ProductName, Price FROM vw_Names  
            WHERE Price < @VarPrice;  
   END  
GO

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    1.5.4. 测试该存储过程

    要测试该存储过程，请输入并执行下面语句。该存储过程应该会返回Products表中输入的两个产品的名称，价格小于10.00。

    EXECUTE pr_Names 10.00;  
GO

    • 1
    • 2

    在本节开始处介绍视图和存储过程时，对它们的概念还挺模糊的。
    看了上面两个示例后，发现视图就是表的一部分。而存储过程有点类似程序中的函数，可以给它设计参数和处理过程。

    这边再补充一个小例子，在我的应用场景中，该示例特别常用：

    /****** Object:  StoredProcedure [dbo].[pr_get_banCi]    Script Date: 2022/10/20 10:11:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[pr_get_banCi]
	-- Add the parameters for the stored procedure here
	@curDateTime DateTime,
	@ret NCHAR(10) OUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT @ret = BanCi FROM dbo.ZaiGangLv_v2 
  -- CURRENT_TIMESTAMP 当前时间 --
		WHERE [JiLuShiJian] BETWEEN DATEADD(mi, -20, @curDateTime) AND DATEADD(mi, -10, @curDateTime)
END


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    上面是定义存储过程，接下来是使用，

    -- 使用的时候，若是输出参数 一定要标记OUT
EXEC  pr_cal_totalTime '2022-10-12 11:00:30.950', @zongShiJianJianGe OUT

    • 1
    • 2

    1.6. 小结

    整个第一节，主要通过编写一些简单常用的T-SQL语句来熟悉它，其中也涉及到使用过程中的一些常见概念，表啊、数据库啊、函数啊、增删改查、视图、存储过程等。

    至于更细节处，还是需要通过实践来加深。

    🔺2. 配置数据库对象的权限

    标题打🔺是因为，整节相对于上一节来讲没那么重要，甚至不需要看都能直接写脚本。

    授予用户对数据库的访问权限有三个步骤：

    1. 首先，创建一个login。login允许用户连接到SQL Server数据库引擎。
    2. 接着，将login配置为指定数据库中的用户。
    3. 最后，将用户权限授予数据库对象。

    本节主要展示这三个步骤，并向您展示如何创建视图和存储过程作为对象。

    2.1. 创建一个login

    要访问数据库引擎，用户需要登录。登录可以表示Windows账户或Windows组成员的用户身份，也可以是只存在于SQL Server中的SQL Server登录名。你应该尽可能地使用Windows身份验证。

    默认情况下，计算机上的管理员拥有对SQL Server的完全访问权。而这节中，我们会搞一个权限较低的用户；因此，会在计算机上创建一个新的本地Windows身份验证账户。要创建用户，你必须是计算机的管理员。然后授予新用户对SQL Server的访问权限。

    2.1.1. 创建一个新的Windows账户
    1. 开始>运行>打开的输入框中输入 %SystemRoot%\system32\compmgmt.msc /s，点击确定。
    2. 在系统工具下展开本地用户与组，右键单击用户，然后新建用户。
    3. 用户名栏填入Mary。
    4. 在密码和确认密码的框中输入密码，点击创建来创建一个本地Windows用户。

    ❗注意
    若在计算机管理工具下没有本地用户与组这一项，你就得用其他方式去新建一个用户。具体哪种方式请自行上网搜索。

    2.1.2. 创建一个SQL login

    在SSMS的查询编辑器中，添加并执行下面代码，将computer_name替换为计算机名称。FROM WINDOWS表示WINDOWS将对用户进行身份验证。可选的DEFAULT_DATABASE参数将Mary连接到TestData数据库。该语句将;（分号）作为T-SQL语句的可选终止符引入。

    CREATE LOGIN [computer_name\Mary]  
    FROM WINDOWS  
    WITH DEFAULT_DATABASE = [TestData];  
GO

    • 1
    • 2
    • 3
    • 4

    这将授权一个名为Mary的用户访问该SQL Server实例，该用户由计算机身份验证。若计算机上有多个SQL Server实例，则必须在Mary访问的每个实例上创建login。

    ❗注意
    因为Mary不是域账户，所以该用户名只能在这台计算机上进行身份验证。

    2.2. 授予访问数据库的权限

    Mary现在可以访问该SQL Server实例，但没有访问数据库的权限。在你将它授权为数据库用户之前，它甚至不能访问它的默认数据库TestData。

    要授予Mary访问权限，请切换到TestData数据库，然后使用CREATE USER语句将它的登录映射到一个名为Mary的用户。

    2.2.1. 在数据库中创建一个用户

    添加并执行下面语句（用你的计算机名替换computer_name），以赋予Mary访问TestData数据库的权限。

    USE [TestData];  
GO  

CREATE USER [Mary] FOR LOGIN [computer_name\Mary];  
GO

    • 1
    • 2
    • 3
    • 4
    • 5

    好了，现在SQL Server和TestData数据库，Mary都能访问了。

    2.3. 创建视图和存储过程

    作为管理员，你可以从Products表和vm_Names视图执行SELECT，并且还能执行pr_Names存储过程；不过，Mary不能。要向Mary授予必要的权限，得使用GRANT语句。

    2.3.1. 授予存储过程权限

    添加并执行以下语句，以使Mary可以执行pr_Names存储过程语句：

    GRANT EXECUTE ON pr_Names TO Mary;  
GO

    • 1
    • 2

    在该场景中，Mary只能通过使用存储过程来访问Products表。若你希望Mary能对视图执行SELECT语句，那么还必须执行 GRANT SELECT ON vw_Names TO Mary。要删除对数据库对象的访问，请使用REVOKE语句。

    ❗注意
    若表、视图和存储过程不属于同一个模式，授权就会变得很复杂。

    2.3.2. 关于授权

    必须有EXECUTE权才能执行存储过程。必须拥有SELECT、INSERT、UPDATE、和DELETE权限才能访问并变更数据。GRANT语句还可用于其他权限，例如创建表。

    🔺3. 删除数据库对象

    这节很短，就是演示如何删掉数据库的。

    在删对象前，请确保你在正确的数据库中：

    USE TestData;  
GO

    • 1
    • 2

    3.1. 撤销存储过程权限

    使用REVOKE语句来移除Mary的执行存储过程权限：

    REVOKE EXECUTE ON pr_Names FROM Mary;  
GO

    • 1
    • 2

    3.2. 移除权限

    1. 使用DROP语句移除Mary对TestData数据库的访问权限：
    DROP USER Mary;  
GO

    • 1
    • 2
    1. 使用DROP语句移除Mary对SQL Server的访问权限：
    DROP LOGIN [<computer_name>\Mary];  
GO

    • 1
    • 2
    1. 用DROP语句移除存储过程pr_Names：
    DROP PROC pr_Names;  
GO

    • 1
    • 2
    1. 用DROP语句移除视图vw_Names：
    DROP VIEW vw_Names;  
GO

    • 1
    • 2

    3.3. 删除表

    1. 用DELETE语句移除Products的所有行：
    DELETE FROM Products;  
GO

    • 1
    • 2
    1. 用DROP语句移除Products表：
    DROP TABLE Products;  
GO

    • 1
    • 2

    3.4. 删除数据库

    当你正在该数据库时，是无法删除该数据库的；因此，首先切换上下文到另一个数据库，然后使用DROP语句删除TestData数据库：

    USE MASTER;  
GO  
DROP DATABASE TestData;  
GO

    • 1
    • 2
    • 3
    • 4

    三、结语

    编写T-SQL语句的教程到此就结束了。本教程只是一个简短的概述，并没有描述使用语句的所有选项。设计和创建高效的数据库结构并配置对数据库的安全访问需要比本教程中所示的更复杂的数据库，得在实践中慢慢掌握。

  • 相关阅读:
    ubuntu tools
    java 闭包的用途是什么
    java--强制类型转换
    Shell 脚本学习 day01
    美团校招机试 - 小美的MT（20240309-T3）
    存折与信用卡（继承）Java
    Jmeter中http请求时加HTTP Cookie管理器，cookie不生效问题
    华为机试真题 C++ 实现【字符串序列判定】
    亨元模式 结构型模式之六
    Python windows安装3.10.0版本
  • 原文地址：https://blog.csdn.net/BadAyase/article/details/127316409