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

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、查看数据库

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脚本，希望对大家有所帮助