数据库编程

数据库编程

T-SQL编程基础

T-SQL即Transact-SQL，是SQL在Microsoft SQL Sever上增强版

• SQL SERVER 专用标准结构化查询语言增强版
• T-SQL提供了标准SQL的DDL和DML功能，嘉盛延伸函数、系统预存程序以及程序设计结构（例如IF和WHILE）让程序设计更有弹性

标识符分类

• 常规标识符（严格遵循标识符格式规则）
• 界定标识符（引号“” 或方括号【】）

标识符格式规则

• 字母或 、@ # 开头的字母数字或 、@ ￥ 序列
• 不与保留字相同
• 长度小于128
• 不符合规则的标识符必须加以界定（算信号“” 或方括号【】）

数据类型

• 整数属于、字符数据、货币数据、日期和时间数据、二进制字符串等

变量

局部变量

• 局部变量是用户定义，必须以@开头，在程序内声明，并只能在该程序内使用

局部变量的声明

• declare @<局部变量名> <数据类型>

局部变量的赋值

• set/select @<局部变量名>=<表达式>

SET \SELECT 区别

• set只能给单一变量赋值
• select 可以给多个变量赋值
  

-- 创建一个@myvar变量，然后将一个字符串放在变量中，最后输出@myvar变量的值

declare @myvar char(20);
select @myvar='Hello world~';
select @myvar;
set @myvar='你好呀，世界';
select @myvar;

--用set语句和select语句为局部变量赋值
declare @var1 datetime,@var2 char(10);
set @var1=getdate();
select @var2=convert(char(10),@var1,102);
select @var2;

全局变量

• 全局变量是SQL SEVER 系统内部使用的变量，以@@开头
• 全局变量不是由用户的程序定义的，它是程序的服务器定义的
• 用户只能使用预先定义的全局变量

-- 用全局变量查看SQL Server的版本、当前所使用的的SQL Sever服务器
--的名称以及所使用的的服务名称等信息
print '目前所使用的SQL Sever的版本信息如下：'
print @@VERSION;
print '目前sql Server服务器名称为：'+@@servername;
print '目前所用的服务器为：'+@@servicename;

选择结构（if…else…）

• if…else 语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。其中，else子句是可选的。

--查找有没有学号为201215120、201216521的学生，有的话显示血红色呢个信息，没有显示没找到

if exists(select * from student where sno='201215120')
begin
	select * from student
	where sno='201215120'
end
else
	print '没有找到'

CASE语句

-- 从学生表student中，选取sno,ssex,如果ssex为'男'，则输出‘m’
-- 如果为‘女’，则输出‘F’
select sno,ssex=
		case ssex
		when '男' then 'M'
		when '女' then 'F'
		end
from student;

搜索式case语句

--从sc表中查询所有同学选课成绩情况、凡是成绩为空者
--输出‘未考’、小于60输出为‘不及格’、小于70输出‘及格’
--小于90输出‘良好’、大于等于90输出为‘优秀’
select sno,cno,grade=
	case
		when grade is null then '未考'
		when grade < 60 then '不及格'
		when grade>=60 and grade<70 then '及格'
		when grade>=70 and grade<90 then '良好'
		when grade>90 then '优秀'
	end
from sc;

case 语句可以计算多条件是，并将其中一个符合条件的结果表达式返回

循环结构

	while 逻辑表达式
	begin
		T-SQL语句组
		[break]  /*终止整个语句的执行*/
		[countinue]/*结束一次循环体的执行*/

--求1~10的和
declare @x int,@sum int;
set @x=0;
set @sum=0;
while @x<10
	begin
		set @x=@x+1;
		set @sum+=@x;
		print 'sum='+convert(char(2),@sum)
	end

等待语句

• 等待语句挂起一个程序中语句的执行，直到指定的某一时间点到来或在一定的时间间断之后才继续执行

--设置等待三秒后执行查询
begin 
	waitfor delay '0:00:03'
	select * from student
end

-- 设置到10点整执行查询
begin 
	waitfor time '10:00:00'
	select * from student
end

返回语句

• return 语句用于无条件地终止一个查询、存储过程或者批处理，此时位于return语句之后的程序将不会被执行

系统函数

日期和时间函数

字符串操作函数

• quotename() 函数返回被特定字符括起来的字符串
• 

数据类型转换

• cast函数允许把一个数据类型强制转换为另一种数据类型
• • cast(expression AS date_type)
• convert函数允许用户把表达式从一种数据类型转换为另一种数据类型；允许把日期转换成不同的样式
• • convert(data_type[(length)],expression[,style])

--查询学生基本信息student中的学号、姓名、年龄，并将这三个字运算符用“+”
--连接显示在查询结果中
select sno+sname+cast(sage as char(2))
from student;

游标

游标是一种能从包括多条数据记录的结果集中提取一条记录的机制

游标的使用

• 声明游标
• 打开游标
• 读取游标中的数据
• 关闭游标
• 释放游标

--声明游标，统计没有选修课程的学生的人数
declare num_cursor cursor
for 
	select sno
	from student
	for read only;

--打开游标
open num_cursor;

读取游标

读取游标中的数据

• 在执行fetch语句后，可通过@@fetch_status全局变量返回游标当前的状态，@@fetch_status
• 变量有三个返回值
• • 0 ： fetch语句执行成功
• • -1：fetch语句执行失败或者行数据超过游标数据结果聚集的范围
• • -2：表示提取的数据不存在

关闭游标

• close num_cursor

释放游标

• deallocate num_cursor

--根据学生成绩计算统计各个等级的人数
declare @mygrade int,@mylevel char(1)
declare @a int,@b int,@c int ,@d int ,@e int;
select @a=0,@b=0,@c=0,@d=0,@e=0;

--定义游标
declare level_cursor cursor for 
select grade
from sc;

--打开游标
open level_cursor;

fetch next from level_cursor into @mygrade
while @@fetch_status=0
	begin
		if @mygrade is null
			set @e+=1
		else if @mygrade<60
			set @e+=1
		else if @mygrade<70
			set @d+=1
		else if @mygrade<80
			set @c+=1
		else if @mygrade<90
			set @b+=1
		else if @mygrade<=100
			set @a+=1
		fetch next from level_cursor into @mygrade
	end

select @a,@b,@c,@d,@e;

close level_cursor;

deallocate level_cursor;

--根据员工工资计算其所得税，3000元为起征点，超过3000元的部分按照10%
--的比例征收个人所得税
--定义局部变量
declare @salary int,@cno int
--声明游标
declare salery_cursor cursor
for
	select 工资，员工编号
	from 员工工资表
for read only

open salery_cursor

fetch next from salery_cursor into @salary,@cno
while @@fetch_status=0
begin
	if @salary>3000
	begin
		update 员工工资表 set 个人所得税=@salary*0.1 where 员工编号=@cno
	end
end

close salery_cursor
deallocate salery_cursor

存储过程

--)查询不及格课程超过3门的学生信息
GO
create proc myproc
as
begin
select *
from student
where sno in
(
	select sno
	from sc
	where grade<60
	group by sno having count(*)>3
)
end;
GO
--执行
exec myproc;

GO
create proc proc_insert_student
@sno char(9),
@sname char(10),
@ssex char(2)='男',
@sage int,
@sdept varchar(10)
as
begin
	insert into student(sno,sname,ssex,sage,sdept)
	values(@sno,@sname,@ssex,@sage,@sdept)
end
GO

exec proc_insert_student '201315126','陈东','男',18,'IS';

--求指定学号的平均成绩
GO
create proc proc_avgegrade
@sno char(9),
@savg int output
as 
begin
	select @savg=avg(grade)
	from sc
	where sno=sno
end
GO

declare @student_avg int
set @student_avg=0
exec proc_avgegrade '201215121',@student_avg out;
select @student_avg;

自定义函数

--返回一个不带时间日期
create function dateonly(@date datetime)
returns varchar(12)
as 
begin
	return convert(varchar(12),@date,101)
end

select dbo.dateonly(getdate())

--根据输入的参数生日，来判断学生是“80后”，还是“90后”，如果是“80后”，返回80s
--如果是90后，返回90s,其他返回“too old”
GO
create function whichgeneration(@birthday datetime)
returns varchar(10)
as 
begin
	if year(@birthday)<1980
		return 'too old!'
	else if year(@birthday)<1990
		return '80s'
	else
		return '90s'
	return 0
end
GO

select dbo.whichgeneration('1979-06-01');

内嵌表值函数

多语句表值

视图、存储过程和自定义函数

触发器

触发器的作用

• 触发器可以对数据库进行级联修改
• 实现比sheck约束更为复杂的限制
• 比较数据修改数据前后差别
• 强制表的修改要合乎业务规则

DML触发器

--1）在sc表中插入记录，去检查student表中是否有相应学号
GO
create trigger tr_sc_insert
on sc
after insert
as 
begin
	declare @sno char(9)
	select @sno=sno from inserted
	if not exists(select * from student where sno=@sno)
		delete from sc where sno=@sno
end
GO

--2）检查新插入的学生记录，如果成绩不在0~100之间，则不允许插入
create trigger tr_sc_grade
on sc
after insert,update
as
begin
	declare @grade int
	select @grade=grade from inserted
	if (@grade>100 or @grade<0)
	begin
		raiserror('成绩必须在0~100之间！！！！！',16,10)
		rollback transaction/*回滚*/
	end
end

GO
create trigger tr_student_update
on student
after update
as 
begin
	if update(sno)
		begin
			raiserror('学号无法修改~~',16,10)
			rollback transaction/*回滚*/
		end
end
GO