数据库原理（十）--t-sql

一、变量


定义变量

declare @变量名称 类型[=默认值];


变量赋值

1、set @变量名称=value; 

一次只能给1个变量赋予标量值


2、select @变量名称=value;

1次可以给多个变量赋值,可以通过查询赋值。



二、输出信息

1、print:1次只能输出1项信息

2、select:1次可以输出多项信息



三、控制结构

1、begin...end:表示一个语句块，类似于{}

2、if 、if...else、 if...else if...else

3、while(条件)
   begin
	...
	[break|continue]
   end

4、case [表达式]
	when ...
	else ...
   end


五、局部变量与全局变量

1、局部变量：用户自己所定义，使用一个@作为前缀。

2、全局变量:系统所提供的，使用@@作为前缀。

3、常用全局变量

@@identity:获取标识列的值
@@rowcount:获取受影响的行数
@@error:获取错误号，0表示上一行语句无错误
@@fetch_status:获取游标的状态


六、常用函数

1、日期函数

getDate():获取当前日期
year(时间):获取年数
month(时间):获取月份
day(时间）：获取今天是几号

DateAdd(datePart,number,date):在date上加上一个日期

例如：
DATEADD(day,7,regTime)>=GETDATE():表示1周以内

DATEADD(day,7,regTime)<GETDATE()：表示1周以外


DateDiff(datepart,startTime,endTime):获取2个时间的差值

DatePart(detepart,time):获取日期的某一部分


2、数学函数

abs(number):返回绝对值

floor(number):返回小于等于当前数的整数

ceiling(number):返回大于等于当前数的整数

rand():返回0-1之间的随机数



3、字符函数

charIndex(searchStr,sourceStr,[startLength]):起始为1，找不到返回0

str(number):把数字转换成字符串

convert(targetType,参数):把参数转换成目标类型

cast(expression1 as targetType):把表达式转换成目标类型

replace(sourceStr,searchStr,replaceStr):替换字符串

substring(sourceStr,startPosition,length):从statrPosition位置开始截取length个字符

left(sourceStr,length):从左边截取指定长度字符

right(sourceStr,length):从右边截取指定长度字符

datalength(sourceStr):获取字符串的长度

ltrim(sourceStr):去除左边空格

rtrim(sourceStr):去除右边空格

reverse(sourceStr):反转


4、系统函数

isDate(param):判断参数是否是日期

isNumeric(param):判断参数是否是数字

isNull(param1,newValue):判断参数是为为Null,如果为Null就把newValue赋值给参数


5、自定义函数

1)创建函数

1、标量函数（返回基本类型)

create function function_name(参数列表)
returns 返回类型
[with encryption|schemabinding]
as
begin
   statements
end


2、表值函数（返回类型为table)

2.1语法：内联表值函数

CREATE FUNCTION <函数名>(<参数>)
Returns TABLE
[WITH ENCRYPTION | SCHEMABINDING]
[AS]
RETURN   <select语句>


2.2语法：多语句表值函数

CREATE FUNCTION <函数名>(<参数>)
Returns  <表格变量名> TABLE (<表格变量定义>) 
[WITH ENCRYPTION | SCHEMABINDING]
[AS]
BEGIN
      <SQL语句>
      return
END


2)修改函数

语法与创建函数相同，只需把create换成alter


3)删除删除

drop function 函数名称



七、存储过程

由1组预编译的sql语句组成，存放在数据库中。

1）优点

1、节省网络流量
2、提高了执行速度
3、提高了安全性
4、可以进行模块化设计


2）创建存储过程

CREATE PROC <存储过程名称>
     @parameter1 <数据类型> = <默认值>,
     @parameter2 <数据类型> = <默认值> OUTPUT 
AS
BEGIN
     <代码块>
END


执行存储过程
exec 存储过程名 参数...

返回值的方式：

1、使用输出参数返回

2、使用return返回（只能返回int类型）


3）修改存储过程

把create换成alter


4)删除存储过程

drop proc 存储过程名称


八、触发器

语法：
create trigger trigger_name on table_name
[WITH ENCRYPTION]
for|instead of  [DELETE, INSERT, UPDATE]
AS 
SQL语句


九、游标:用于存储查询结果，通过指针移动读取数据。

示例：
--1、定义游标
Declare  acc_cursor  cursor  forward_only 
for select * from record 

--2、打开游标
open acc_cursor

--3、定义变量接收数据
Declare @aid varchar(6),@ty varchar(6),
@jine float ,@transt datet

--4、先读取一行
FETCH NEXT FROM acc_cursor INTO @aid,@ty,@jine,@transt  

--5、循环读取
WHILE(@@FETCH_STATUS = 0)
begin
  insert into record_back2 values(@aid,@ty,@jine,@transt)

  FETCH NEXT FROM acc_cursor INTO @aid,@ty,@jine,@transt
end

--6、关闭游标
close acc_cursor

--7、销毁游标
deallocate acc_cursor