Sql Server数据库常用Transact-SQL脚本(推荐)
程序员文章站
2022-11-21 11:26:22
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脚本,希望对大家有所帮助
上一篇: PHP操作Mongodb封装类完整实例
下一篇: mssql注入躲避IDS的方法
推荐阅读