Transact-

Transact-(又称 T-SQL),是在 Microsoft 和 Sybase Server 上的 ANSI SQL 实现,与 Oracle 的 PL/SQL 性质相近(不只是实现 ANSI SQL,也为自身数据库系统的特性提供实现支持),在 Microsoft SQL Server 和 Sybase Adaptive Server 中仍然被使用为核心的查询语言。

数据库

1、创建数据库

  USE master ;   GO   CREATE DATABASE Sales   ON   ( NAME = Sales_dat,    FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAsaledat.mdf',    SIZE = 10,    MAXSIZE = 50,    FILEGROWTH = 5 )   LOG ON   ( NAME = Sales_log,    FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAsalelog.ldf',    SIZE = 5MB,    MAXSIZE = 25MB,    FILEGROWTH = 5MB ) ;   GO     

2、查看数据库

  SELECT name, database_id, create_date   FROM sys.databases ; 

3、删除数据库

  DROP DATABASE Sales;

1、创建表

  CREATE TABLE PurchaseOrderDetail   (    ID uniqueidentifier NOT NULL    ,LineNumber smallint NOT NULL    ,ProductID int NULL    ,UnitPrice money NULL    ,OrderQty smallint NULL    ,ReceivedQty float NULL    ,RejectedQty float NULL    ,DueDate datetime NULL   ); 

2、删除表

  DROP TABLE dbo.PurchaseOrderDetail; 

3、重命名表

  EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr'; 

1、添加列

  ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;

2、删除列

  ALTER TABLE dbo.doc_exb DROP COLUMN column_b; 

3、重命名列

  EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN'; 

约束

1、主键

  --在现有表中创建主键  ALTER TABLE Production.TransactionHistoryArchive   ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);    --在新表中创建主键  CREATE TABLE Production.TransactionHistoryArchive1   (    TransactionID int IDENTITY (1,1) NOT NULL    , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)   )  ;    --查看主键   SELECT name   FROM sys.key_constraints   WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive';   GO   --删除主键  ALTER TABLE Production.TransactionHistoryArchive   DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID;   GO 

视图

1、创建视图

  CREATE VIEW V_EmployeeHireDate   AS   SELECT p.FirstName, p.LastName, e.HireDate   FROM HumanResources.Employee AS e JOIN Person.Person AS p   ON e.BusinessEntityID = p.BusinessEntityID ;   GO 

2、删除视图

  DROP VIEW V_EmployeeHireDate; 

存储过程

1、创建存储过程

  CREATE PROCEDURE P_UspGetEmployeesTest    @LastName nvarchar(50),    @FirstName nvarchar(50)   AS    SELECT FirstName, LastName, Department    FROM HumanResources.vEmployeeDepartmentHistory    WHERE FirstName = @FirstName AND LastName = @LastName    AND EndDate IS NULL;   GO 

2、删除存储过程

  DROP PROCEDURE P_UspGetEmployeesTest; 

3、执行存储过程

  EXEC P_UspGetEmployeesTest N'Ackerman', N'Pilar';   -- Or   EXEC P_UspGetEmployeesTest @LastName = N'Ackerman', @FirstName = N'Pilar';   GO   -- Or   EXECUTE P_UspGetEmployeesTest @FirstName = N'Pilar', @LastName = N'Ackerman';   GO 

4、重命名存储过程

  EXEC sp_rename 'P_UspGetAllEmployeesTest', 'P_UspEveryEmployeeTest2'; 

5、带有输出参数的存储过程

  CREATE PROCEDURE P_UspGetEmployeeSalesYTD   @SalesPerson nvarchar(50),   @SalesYTD money OUTPUT   AS    SELECT @SalesYTD = SalesYTD    FROM SalesPerson AS sp    JOIN vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID    WHERE LastName = @SalesPerson;   RETURN   GO    --调用  DECLARE @SalesYTDBySalesPerson money;   EXECUTE P_UspGetEmployeeSalesYTD    N'Blythe',    @SalesYTD = @SalesYTDBySalesPerson OUTPUT;   GO 

数据类型

Sql Server数据库常用Transact-SQL脚本(推荐)

总结

以上所述是小编给大家介绍的Sql Server数据库常用Transact-SQL脚本,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对沃谷博客网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注