Sql Server数据库常用Transact-SQL脚本

 

数据库

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  

 

数据类型