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

Transact-SQL

Transact-SQL（又称 T-SQL），是在 Microsoft SQL Server 和 Sybase SQL 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 Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf', 

									 SIZE = 10, 

									 MAXSIZE = 50, 

									 FILEGROWTH = 5 ) 

									LOG ON

									( NAME = Sales_log, 

									 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf', 

									 SIZE = 5MB, 

									 MAXSIZE = 25MB, 

									 FILEGROWTH = 5MB ) ; 

									GO

2、查看数据库

1 2	`SELECT` `name, database_id, create_date` `FROM` `sys.databases ;`

3、删除数据库

1	`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、删除表

1	`DROP` `TABLE` `dbo.PurchaseOrderDetail;`

3、重命名表

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

列

1、添加列

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

2、删除列

1	`ALTER` `TABLE` `dbo.doc_exb` `DROP` `COLUMN` `column_b;`

3、重命名列

1	`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、删除视图

1	`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、删除存储过程

1	`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、重命名存储过程

1	`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