欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-21 08:42:03
transact-sql transact-sql(又称 t-sql),是在 microsoft sql server 和 sybase sql server 上的 ansi 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脚本(推荐)

总结

以上所述是小编给大家介绍的sql server数据库常用transact-sql脚本,希望对大家有所帮助

相关标签: Transact-SQL 脚本