SqlServer数据库常用TransactSQL脚本

  • Z时代
  • 2024-01-10
  • 分类：综合

database

 

数据库

1、创建数据库

USE master ;  
GO
CREATEDATABASE Sales  
ON
( NAME = Sales_dat,  
    FILENAME ="C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAsaledat.mdf",  
    SIZE =10,  
    MAXSIZE =50,  
    FILEGROWTH =5 )  
LOGON
( 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、删除数据库

DROPDATABASE Sales;

 

表

1、创建表

CREATETABLE PurchaseOrderDetail  
(  
    ID uniqueidentifierNOTNULL
    ,LineNumber smallintNOTNULL
    ,ProductID intNULL
    ,UnitPrice moneyNULL
    ,OrderQty smallintNULL
    ,ReceivedQty floatNULL
    ,RejectedQty floatNULL
    ,DueDate datetimeNULL
);

 

2、删除表

DROPTABLE dbo.PurchaseOrderDetail;

 

3、重命名表

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

 

列

1、添加列

ALTERTABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INTNULL ;

 

2、删除列

ALTERTABLE dbo.doc_exb DROPCOLUMN column_b;

 

3、重命名列

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

 

约束

1、主键

--在现有表中创建主键
ALTERTABLE Production.TransactionHistoryArchive
ADDCONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARYKEYCLUSTERED (TransactionID);
--在新表中创建主键
CREATETABLE Production.TransactionHistoryArchive1
   (
      TransactionID intIDENTITY (1,1) NOTNULL
      , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARYKEYCLUSTERED (TransactionID)
   )
;
--查看主键  
SELECT name  
FROM sys.key_constraints  
WHERE type ="PK"ANDOBJECT_NAME(parent_object_id) = N"TransactionHistoryArchive";  
GO
--删除主键
ALTERTABLE Production.TransactionHistoryArchive  
DROPCONSTRAINT PK_TransactionHistoryArchive_TransactionID;   
GO

 

视图

 1、创建视图

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

DROPVIEW V_EmployeeHireDate;

 

存储过程

1、创建存储过程

CREATEPROCEDURE P_UspGetEmployeesTest   
@LastNamenvarchar(50),   
@FirstNamenvarchar(50)   
AS
SELECT FirstName, LastName, Department  
FROM HumanResources.vEmployeeDepartmentHistory  
WHERE FirstName =@FirstNameAND LastName =@LastName
AND EndDate ISNULL;  
GO

 

2、删除存储过程

DROPPROCEDURE 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、带有输出参数的存储过程

CREATEPROCEDURE P_UspGetEmployeeSalesYTD  
@SalesPersonnvarchar(50),  
@SalesYTDmoney OUTPUT  
AS
SELECT@SalesYTD= SalesYTD  
FROM SalesPerson AS sp  
JOIN vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
WHERE LastName =@SalesPerson;  
RETURN
GO
--调用
DECLARE@SalesYTDBySalesPersonmoney;  
EXECUTE P_UspGetEmployeeSalesYTD  
    N"Blythe", 
@SalesYTD=@SalesYTDBySalesPerson OUTPUT; 
GO

 

数据类型

 

以上是 SqlServer数据库常用TransactSQL脚本 的全部内容， 来源链接： utcz.com/z/531636.html

回到顶部