1. 创建数据库
/*创建数据库libraryDB*/ CREATE DATABASE libraryDB ON ( /*数据库文件的详细描述*/ NAME = 'libraryDB_mdf', --主数据库文件的逻辑名 FILENAME = 'E:\library\libraryDB_mdf.mdf', --主数据文件的物理名 SIZE = 3MB, --初始大小 FILEGROWTH = 20% --增长率 ) LOG ON ( /*日志文件的详细描述*/ NAME = 'libraryDB_ldf', --日志文件的逻辑名 FILENAME = 'E:\library\libraryDB_ldf.ldf', --日志文件的物理名 SIZE = 1MB, --初始大小 MAXSIZE = 15MB, --最大值 FILEGROWTH = 10% --增长率 ) GO |
2. 数据表(创建|修改|删除)
--判断BookType表是否存在,存在则删除 if exists (select 1 from sysobjects where [name]='BookType') begin drop table BookType end --创建图书类型表:BookType create table BookType ( TypeId int not null identity(1,1) primary key, --图书类型编号(主键、标识列、从1开始、每次增加1) TypeName varchar(50) not null, --图书类型名称 Remark varchar(100) --备注信息 ) --修改BookType表,增加备注字段 alter table BookType alter column Remark varchar(100) --删除表BookType drop table BookType |
3. 创建主键
--为表添加主键 alter table productinfo add constraint PK_ProductInfo_ProductId primary key(ProductId) --删除主键只需要将add替换为drop |
4. 创建外键
--为表添加外键 alter table productpromotion add constraint FK_Promotion_Product foreign key (ProductId) references ProductInfo(ProductId) --删除外键只需要将add替换为drop |
5. 检查约束
--创建检查约束(商品编号的长度大于2) alter table productinfo add constraint CK_Product_Number check(len(ProductNumber)>2)--删除外键只需要将add替换为drop --创建检查约束(性别为男或者女) --check(Gender in ('男', '女')) |
6. 惟一约束
--创建唯一约束(商品编号唯一) alter table productinfo add constraint UQ_Product_Number unique (ProductNumber) |
7. T-SQL编程(定义变量、为变量赋值)
--T-SQL中定义变量 declare @sum int; declare @i int; --set赋值(一次只能为一个变量赋值) set @sum = 0; --select赋值(一次可以为多个变量赋值) select @sum=0,@i=1; |
8. T-SQL编程(if-else循环)
--if实例 declare @i int; set @i=7; if(@i%2 = 0) print '偶数' else print '奇数' go |
9. T-SQL编程(while)
--请输出1-10之间的数字 declare @i int; set @i = 1; while (@i<=10) begin print @i; set @i = @i + 1; end |
10. T-SQL编程(case)
--case的第一种语法格式(使用case将0显示为:空闲 1显示为:使用中) select intComputerId, 'State'=case when intInUse=0 then '空闲' when intInUse=1 then '使用中' end, chvComputerName,chvDescription from tblcomputer; --case的第二种语法格式 select intComputerId, 'State'=case intInUse when 0 then '空闲' when 1 then '使用中' end, chvComputerName,chvDescription from tblcomputer |
11. 视图
--创建视图的语法示例 create view view_RecordDetail as select cp.chvComputerName, ci.chvUserName, ri.dtmStart, ri.dtmEnd, ri.mnyFee from TblRecordInfo as ri--as为表取别名 inner join TblCardInfo as ci on ri.intCardId=ci.intCardId--内连接用inner join,同时应该为两张表指定连接字段 inner join TblComputer as cp on ri.intComputerId=cp.intComputerId --使用视图(和表很相似) Select * from view_RecodDetail |
12. 存储过程(无参数)
--创建不带参数的存储过程 create procedure pro_ComputerUseState as begin select * from tblcomputer; end --调用不带参数的存储过程 exec pro_ComputerUseState; |
13. 存储过程(含输入参数)
--如何创建有输入参数的存储过程 create procedure pro_getComputerState @state int=0--参数默认为输入参数 as begin select intComputerId, 'intInUse'=case intInuse when 0 then '未使用' when 1 then '以使用' end, chvComputerName, chvDescription from tblcomputer where intInuse=@state end go --调用带有输入参数的存储过程 declare @state int; set @state = 1; exec pro_getComputerState @state; |
14. 存储过程(含输入参数、输出参数)
--创建带有输入参数和输出参数的存储过程,多个参数之间用逗号,隔开,最后一个参数后无需逗号 create proc pro_getComputerStateById @intComputerId int, @state int output as begin select @state = intInUse from tblcomputer where intcomputerid=@intComputerId end --调用带输出参数的存储过程,调用时一定要在输出参数后加关键字output declare @state int, @computerId int; set @computerId = 7; exec pro_getComputerStateById @computerId,@state output select @state; |
15. 触发器
--判断触发器是否存在,存在则删除触发器 if exists (select * from sys.sysobjects where name = 'tr_insertRecord') drop trigger tr_insertRecord go --如何定义|创建一个触发器 create trigger tr_insertRecord on TblRecordInfo for insert--for等价于after,表示当新增完记录之后才会执行触发器 as begin declare @cardid int, @startTime datetime; select @cardid = intcardid, @startTime = dtmStart from inserted; select '卡号:'+convert(nvarchar(5), @cardid); select '上机开始时间:'+convert(nvarchar(20), @startTime); end |
16. 事务处理
--定义变量@sumError用于记录事务过程中发生错误的次数 declare @sumError int; set @sumError = 0; begin transaction update tblaccount set mnycurrentmoney = mnycurrentmoney + 200000 where chvAccountName='宝钢集团' --通过系统变量@@error可以获取上次被执行的sql是否执行成功,如果执行成功@@error的值为0,否则为1 set @sumError = @sumError + @@error update tblaccount set mnycurrentmoney = mnycurrentmoney - 200000 where chvAccountName='安钢集团' set @sumError = @sumError + @@error --判断是否在执行过程中出现错误 if(@sumError<>0) begin print '事务执行失败,即将回滚' rollback transaction end else begin print '事务执行成功,即将提交' commit transaction end |