当前位置：Gxlcms > 数据库问题 > SQL Server2012 T-SQL基础教程--读书笔记（8 - 10章）

SQL Server2012 T-SQL基础教程--读书笔记（8 - 10章）

时间：2021-07-01 10:21:17 帮助过：6人阅读

INTO table_name (col1,col2) VALUES (val1,val2), (val1,val2), (val1,val2)

该语句被作为一个原子性操作。对于增强VALUES子句，还可以将其作为表值构造函数以标准方式构建一个派生表。

  1. SELECT * 
  2. FROM (VALUES (1,2),(3,4),(5,6)) num(odd,even) 

8.1.2 INSERT SELECT 语句

  1. --1.一般形式 
  2. INSERT INTO table_name (col1,col2) 
  3. SELECT col1,col2 FROM table_name1 
  4. --2.增强VALUES子句，此处SELECT没有FROM子句，不是标准语法，建议使用VALUES子句的表值构造函数来构造结果值 
  5. INSERT INTO table_name (col1,col2) 
  6. SELECT val1,val2 UNION ALL 
  7. SELECT val3,val4 UNION ALL 
  8. SELECT val5,val6 UNION ALL 
  9. SELECT val7,val8  

INSERT SELECT也是作为原子性操作执行的。

8.1.3 INSERT EXEC 语句

使用INSERT EXEC语句可以将存储过程或动态SQL批处理返回的结果集插入到目标表中。INSERT EXEC非常类似INSERT SELECT的语法和概念，只是将SELECT语句替代为EXEC语句。

--创建一个过程，创建过程和执行动态语句将在CHPATER 10中介绍
CREATE PROC Sales.usp_getorders
  @country AS NVARCHAR(40)
AS
BEGIN
    SELECT orderid, orderdate, empid, custid
    FROM Sales.Orders 
    WHERE shipcountry = @country
END
--------------------------
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
  EXEC Sales.usp_getorders @country = ‘France‘;

8.1.4 SELECT INTO 语句

SELECT INTO是一个非标准的T-SQL语句，它使用查询的结果创建并填充目标表。“非标准”即不是ISO和ANSI SQL标准的一部分，不能使用此语句将数据插入到现有表中。

--比如备份Sales.Orders表中 France 的记录
SELECT orderid, orderdate, empid, custid
INTO Sales.FanceOrders
FROM Sales.Orders 
WHERE shipcountry = ‘France‘

SELECT INTO好处之一就是只要数据库的“恢复模式”属性未设置成完整，SELECT INTO就会以最小日志记录模式执行，意味着相对于完整日志记录这是一个非常快速的操作。更多详细住处请点击这里

8.1.5 BULK INSERT 语句

可以使用BULK INSERT语句将来自文件的数据插入到一个现在表中。在语句中指定目标表、源文件和选项。可以指定多个选项，包括数据文件类型（如：CHAR和NATIVE）、字段终止符、行终止符和其他所有文件选项。

--建表
IF OBJECT_ID(‘dbo.Orders‘, ‘U‘) IS NOT NULL DROP TABLE dbo.Orders;

CREATE TABLE dbo.Orders
(
  orderid   INT         NOT NULL
    CONSTRAINT PK_Orders PRIMARY KEY,
  orderdate DATE        NOT NULL
    CONSTRAINT DFT_orderdate DEFAULT(SYSDATETIME()),
  empid     INT         NOT NULL,
  custid    VARCHAR(10) NOT NULL
);
-------------------------------
BULK INSERT dbo.Orders FROM ‘d:\orders.txt‘
  WITH 
    (
       DATAFILETYPE    = ‘char‘,
       FIELDTERMINATOR = ‘,‘,
       ROWTERMINATOR   = ‘\n‘
    );
GO
-------------------------------
/*
orders.txt 数据
10001,2009-02-12,3,1
10002,2009-02-12,5,2
10003,2009-02-13,4,2
10004,2009-02-14,1,1
10005,2009-02-13,1,3
10006,2009-02-15,3,3
10007,2009-02-15,2,2
10008,2009-02-15,1,3
10009,2009-02-16,2,3
10010,2009-02-16,3,1
*/

8.1.6 标识列属性和序列对象

SQL SERVER 支持两种自动生成键的内置解决方案：标识列属性和序列对象（SQL SERVER 2012）。

8.1.6.1 标识列属性

SQL SERVER 允许无小数的任意数值类型的列定义了IDENTITY的属性，此属性根据提供的种子值（第一参数）和增量值（步长值）自动生成值。通常情况下是使用此属性来生成SURROGATE键（系统生成）。

CREATE  table dbo.T1 
(
    keycol INT IDENTITY(1,1)    CONSTRAINT PK_T1 PRIMARY KEY,
    datacol VARCHAR(20) NOT NULL CONSTRAINT CHK_T1_datacol CHECK(datacol like ‘[A-Za-z]%‘)
)
--插入数据
INSERT INTO dbo.T1 VALUES (‘AAA‘),(‘BBB‘),(‘ccc‘)
-- 使用$identity 结果是1，2，3
SELECT $IDENTITY FROM dbo.t1

-- 显式值插入到dbo.T1里
SET IDENTITY_INSERT dbo.T1 ON
INSERT INTO dbo.T1(keycol,datacol) VALUES(4,‘dddd‘)
SET IDENTITY_INSERT dbo.T1 OFF

--结果是4，返回当前值，那么下一个值就是5
SELECT IDENT_CURRENT(‘dbo.T1‘)

 

技术分享
1460043669645.jpg
查询表时，除可以使用列来引用IDENTITY的值，还可以使用SQL SERVER提供的标识符$identity。
标识列的一个重要属性就是用户不能将其添加到现有列或从现有列删除它。如果要手动插入数据到现有列，只需对表设置IDENTITY_INSERT的会话选项即可。

 

8.1.6.2 序列对象

序列对象是作为一种替代标识列的键生成机制添加到SQL SERVER 2012 的，这在其他数据库中已经实现的标准功能。
与标识列不同，序列对象的优点之一是它不是绑定到特定表中的特定列，而是作为数据库中的一个独立对象。当需要生成新值时，调用一个针对对象的函数，然后可以在任何地方使用返回值。
与标识列属性不同点：

  • 序列对象可以指定任意整数数值类型（默认为BIGINT）

  • 序列对象支持指定数据类型内的最小值和最大值，如果不指定则为数据类型的最大或最小值。

  • 序列对象可以循环（默认为不循环）。

/*
创建一个订单ID的序列对象，数据类型为INT，最小值为1，最大值为INT最大值，从1开始，步长为1.
*/
--创建
CREATE SEQUENCE dbo.SeqOrderIDs AS INT MINVALUE 1 CYCLE
--修改
ALTER SEQUENCE dbo.SeqOrderIDs NO CYCLE
--查询
SELECT (NEXT VALUE FOR dbo.SeqOrderIDs)

IF OBJECT_ID(‘dbo.T1‘, ‘U‘) IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
  keycol  INT         NOT NULL
    CONSTRAINT PK_T1 PRIMARY KEY,
  datacol VARCHAR(10) NOT NULL
);
--插入到T1
INSERT INTO dbo.T1 VALUES(NEXT VALUE FOR dbo.SeqOrderIDs, ‘b‘)

--获取序列对象的相关住处，可以查询sys.sequences的视图
SELECT current_value FROM sys.sequences WHERE name = ‘SeqOrderIDs‘

SQL SERVER扩展了对序列选项的支持，一个是类似于开窗函数的OVER子句，在多行插入时来控制分配序列值的顺序。

INSERT INTO dbo.T1(keycol, datacol)
SELECT 
    NEXT VALUE FOR dbo.SeqOrderIds OVER (ORDER BY hiredate)
    ,LEFT(firstname,1) + left(lastname,1)
FROM HR.Employees

 

技术分享
1460043700745.jpg
另一个是允许在默认约束中使用NEXT VALUE FOR函数。这是超越了标识列属性的一个显著优势。

 

ALTER TABLE dbo.T1 ADD CONSTRAINT DF_T1_keycol 
  DEFAULT(NEXT VALUE FOR dbo.SeqOrderIDs)
  FOR keycol

INSERT INTO dbo.T1(datacol) VALUES (‘TEST‘)

最后一个扩展是允许使用一个名为sp_sequence_get_range的存储过程一次分配整个序列值范围。即如果你需要某个范围的序列值，最简单的方式就是仅更新序列一次，按照范围的大小递增。调用此过程时，设置相应的参数即可。

DECLARE @first SQL_VARIANT;
EXEC sys.sp_sequence_get_range 
  @sequence_name = ‘dbo.SeqOrderIDs‘, 
  @range_size = 1000, 
  @range_first_value = @firsts

8.2 删除数据

T-SQL提供个删除表中的行的语句：DELETE和TRUNCATE。
准备表：

CREATE TABLE dbo.Customers
(
  custid       INT          NOT NULL,
  companyname  NVARCHAR(40) NOT NULL,
  contactname  NVARCHAR(30) NOT NULL,
  contacttitle NVARCHAR(30) NOT NULL,
  address      NVARCHAR(60) NOT NULL,
  city         NVARCHAR(15) NOT NULL,
  region       NVARCHAR(15) NULL,
  postalcode   NVARCHAR(10) NULL,
  country      NVARCHAR(15) NOT NULL,
  phone        NVARCHAR(24) NOT NULL,
  fax          NVARCHAR(24) NULL,
  CONSTRAINT PK_Customers PRIMARY KEY(custid)
);

CREATE TABLE dbo.Orders
(
  orderid        INT          NOT NULL,
  custid         INT          NULL,
  empid          INT          NOT NULL,
  orderdate      DATETIME     NOT NULL,
  requireddate   DATETIME     NOT NULL,
  shippeddate    DATETIME     NULL,
  shipperid      INT          NOT NULL,
  freight        MONEY        NOT NULL
    CONSTRAINT DFT_Orders_freight DEFAULT(0),
  shipname       NVARCHAR(40) NOT NULL,
  shipaddress    NVARCHAR(60) NOT NULL,
  shipcity       NVARCHAR(15) NOT NULL,
  shipregion     NVARCHAR(15) NULL,
  shippostalcode NVARCHAR(10) NULL,
  shipcountry    NVARCHAR(15) NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid),
  CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid)
    REFERENCES dbo.Customers(custid)
);

8.2.1 DELETE 语句

DELETE是一个标准语句，只包含有FROM和WHERE两个子句。
DELETE语句是完全日志记录的，所以，当删除大量的行时，运行时间可以会比较长一点。

8.2.2 TRUNCATE 语句

TRUNCATE 语句删除表中的所有行，与DELETE 不同，TRUNCATE 不进行筛选的。TRUNCATE 是最小日志方式，所以性能较DELETE 要好得多。TRUNCATE 完全是事务性的（常见谅解），并且在ROLLBACK时，SQL SERVER 可以撤销删除操作。
当表中含有标识列时，TRUNCATE 会重置标识值到原始的种子值，而DELETE 不会。
当目标表被外键约束引用时，即使父表为空，甚至外键是禁用的，都不允许使用TRUNCATE 语句。唯一解决方法就是删除该表的所有外键。
TRUNCATE 和 DROP 语句的执行速度都是非常快，为防止生产环境上发生误操作，可以创建一个虚拟表，带有指向生产表的外键，这样就可以防止用户从被引用表中删除数据或是删除表实体。

8.2.3 基于联接的DELETE

T-SQL 支持一个联接的非标准的DELETE 语法，联接本身是有筛选作用的。

DELETE FROM o
FROM dbo.Orders o INNER JOIN dbo.Customers c ON o.custid =c.custid 
WHERE c.country = ‘USA‘

其实这个语句和SELECT语句样的。你把DELETE替换成SELECT *毫无专程感。
如果想要使用标准语句来实现这个功能，可以这样

  1. DELETE FROM dbo.Orders  
  2. WHERE EXISTS ( 
  3. SELECT * FROM Sales.Customers c 
  4. WHERE c.custid = dbo.Orders.custid AND c.country = ‘USA‘ 
  5. ) 

SQL SERVER 很有可能以相同的方式处理这两个查询，因此，不用考虑两者之间的性能差异。但是，建议尽可能的坚持标准。

8.3 更新数据

8.3.1 UPDATE 语句

8.3.2 基于联接的 UPDATE

T-SQL 也支持基于联接的UPDATE 非标准语句。
与DELETE 和 SELECT 差别不大

UPDATE od SET od.discount += 0.05
FROM Sales.OrderDetails od
    INNER JOIN dbo.Orders o ON o.orderid = od.orderid
WHERE o.custid = 1

当然也也可以使用标准SQL，通过子查询来进行UPDATE。
在某些情况下，联接版本比子查询版本会有性能优势。除了筛选之外，联接也为用户提供了访问其他表属性的权限，用户可以在SET子句的列赋值中使用这些属性。
下面是非标准UPDATE语句：

UPDATE T1
  SET col1 = T2.col1,
      col2 = T2.col2,
      col3 = T2.col3
FROM dbo.T1 JOIN dbo.T2
  ON T2.keycol = T1.keycol
WHERE T2.col4 = ‘ABC‘;

需要UPDATE的T1表可以直接引用联接查询的T2表的列。如果我们使用标准SQL，可能需要使用的子查询就会比较多，造成SQL比较繁杂：

UPDATE dbo.T1
  SET col1 = (SELECT col1
              FROM dbo.T2
              WHERE T2.keycol = T1.keycol),
      col2 = (SELECT col2
              FROM dbo.T2
              WHERE T2.keycol = T1.keycol),
      col3 = (SELECT col3
              FROM dbo.T2
              WHERE T2.keycol = T1.keycol)
WHERE EXISTS
  (SELECT *
   FROM dbo.T2
   WHERE T2.keycol = T1.keycol
     AND T2.col4 = ‘ABC‘);

标准SQL支持行构造函数（也称为矢量表达式），在SQL SERVER 2012中只实现了部分功能，行构造函数的许多方面SQL SERVER还未实现。包括下面的UPDATE中的SET子句：

UPDATE dbo.T1
  SET (col1, col2, col3) =
      (SELECT col1, col2, col3
       FROM dbo.T2
       WHERE T2.keycol = T1.keycol)
WHERE EXISTS
  (SELECT *
   FROM dbo.T2
   WHERE T2.keycol = T1.keycol
     AND T2.col4 = ‘ABC‘);

PS: 虽然本书作者说要使用标准SQL来进行UPDATE或DELETE，但是实际使用中笔者认为若是对生产环境中的表操作时还是使用T-SQL的联接查询好一点。
理由如下：

DELETE FROM Sales.Customers  
SELECT * 
FROM Sales.Customers 
WHERE custid = 28

上述语句本来想删除custid = 28的这个用户，但是删除前一般都会确认条件是否正确，所以可能会使用SELECT进行查询，但是有时手滑或者不注意直接运行上述语句，Sales.Customers的数据会被全部删掉。如果使用T-SQL的联接来进行删除可以这样

DELETE FROM c  
SELECT * 
FROM Sales.Customers 
WHERE custid = 28

 

技术分享
1460043754972.jpg
PS:
在删除前对要删除的表不要起别名，即使上述SQL全部运行也不会进行任何删除，通过SELECT查询后再对Sales.Customers起别名c，再注释掉SELECT，就不会有误操作了。
UPDATE同理。

 

8.3.3 赋<

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！