T-sql编程

-TSQL语句库(stuDB)-信息表stuInfo-成绩表stuMarks
--曾
insert into stuInfo(Name)
values('小A')
--删
delete from stuInfo where name = '小A'--delete from 表名 where 条件
--改
update stuinfo set name = '大A' where name = '小A'--update 表名 set 字段 = 更新值 where 条件
--查
select name from stuInfo where -----select 查询字段 from 表名 where 条件

/***************************建库建表***********************************************************/
use master--设置当前是数据库为master，以便sysdatabases访问
if exists (select * from sysdatabases where name ='stuDB')--查询数据库中是否存在stuDB数据库，如果存在删除
drop database stuDB--删除数据库
go

----建库(如果要创建多个数据文件和日志文件只要在数据文件的"()"后面加“,”就可以继续创建第二个数据文件了)
exec xp_cmdshell 'mkdir D:\Myproject'--利用系统存储过程调用DOS命令创建目录
create database stuDB
on

--数据文件的具体描述
(
name = 'stuDB_data',
filename = 'D:\Myproject\stuDB_data.mdf',
size = 3mb,
maxsize = 10mb,
filegrowth = 10%
)
log on
--日志文件的具体描述
(
name = 'stuDB_log',
filename='D:\Myproject\stuDB_log.ldf',
size = 3mb,
maxsize = 5mb
)
go

 

----建表
use stuDB
go
if exists (select * from sysobjects where name = 'stuInfo')--查询-删除
drop table stuInfo
go
create table stuInfo--建表，学员信息表
(
stuName    varchar(8)   not null,
stuNo    char(6)    not null,
stuAge    int     not null,
stuID    numeric(18,0), --18位数，小数位数为0
stuSeat    int     identity(1,1),--表示列，自动增长
stuAddress   text,
)
go
----创建学员成绩表
if exists (select * from sysobjects where name = 'stuMarks')
drop table stuMarks
go
create table stuMarks
(
ExamNo    char(7)    not null,--考号
stuNO    char(6)    not null,--学号
writtenExam   int   ,--笔试
labExam    int   ,--机试
)

----建约束(主键/唯一/检查/默认/外键)
alter table stuInfo
add constraint PK_stuNo primary key(stuNo) --主键约束

alter table stuInfo
add constraint UO_stuID unique (stuID) --唯一约束,作为身份证号具有唯一性

alter table stuInfo
add constraint CK_stuAge check (stuAge between 18 and 25)--检查约束,年龄在18-25之间

alter table stuInfo
add constraint DF_stuAddress default ('地址不详') for stuAddress --默认约束

--添加外键约束stuMark的stuNo引用stuInfo的主键stuNO
alter table stuMarks
add constraint FK_stuNO foreign key(stuNo) references stuInfo(stuNo)

alter table stumarks
add constraint PK_stuNo primary key(stuNo)--主键

-----删除约束
/*
alter table stuInfo
drop constraint PK_stuNo
*/

----------------------------------------------------------------------------------------------------------
/*添加stuInfo测试数据*/
insert into stuInfo(stuName,stuNo,stuAge,stuID)
values('张飞,'s0070',18,320381199000000000)

insert into stuInfo(stuName,stuNo,stuAge,stuID)
values('李广','s0071',18,320381199000000001)

insert into stuInfo(stuName,stuNo,stuAge,stuID,stuAddress)
values('byA','s0072',18,320381199000000002,'江苏一区')
insert into stuInfo(stuName,stuNo,stuAge,stuID,stuAddress)
values('bB','s0073',18,320381199000000003,'江苏二区')
insert into stuInfo(stuName,stuNo,stuAge,stuID,stuAddress)
values('byC','s0074',18,320381199000000004,'江苏三区')

/*添加stuMarks测试数据*/

insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by00','s0070',67,89)
insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by01','s0071',77,90)
insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by02','s0072',40,55)
insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by03','s0073',59,92)
insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by04','s0074',80,83)

 

/***************************T_SQL编程***********************************************************/

----局部变量(声明:declare @i int)(赋值:set @i = 3 OR select @i = stuSeat from stuInfo where stuname ='小A')()
declare @i int,@name varchar(6)
--set赋值
set @i = 3
set @name = '小A'
print @i
print @name
--select 赋值
select @i = stuSeat from stuInfo where stuname ='byC'
select @name = stuName from stuInfo where stuID = 320381199000000003
print @i
print @name

----全局变量
@@identity--最后插入的标识列

 

-----逻辑语句 if else

declare @avgwritten float--声明变量
select @avgwritten=avg(writtenExam) from stumarks--select赋值
print '本班笔试平均分是：   ' + convert(char(4),@avgwritten)

--逻辑语句判断大于70 优秀 小于70 一般
if(@avgwritten > 70)
begin
   print '成绩优秀'
end
else
begin
   print '成绩一般'
end

-----while 循环
--统计机试没有通过的学员，循环加分，如过有超过100的，循环结束
--(先判断有几人没通过考试---1，然后通过循环判断加分，然后在循环体中在判断)
declare @n int
select @n=count(*) from stumarks where labExam < 60---1
while(@n>0)
begin
   update stumarks set labExam = labExam +1
   select @n=count(*) from stumarks where labExam < 60
end

----第二种方法()
declare @n int
while(1=1)--条件永远成立
begin
   --update stumarks set labExam = labExam +1--循环加分
   select @n=count(*) from stumarks where labExam < 60--查询不及格人数
   if(@n<1)--如果不及格人数小于1(全部通过)则跳出循环
    begin
     break
    end
   else
    begin
     update stumarks set labExam = labExam +1--循环加分
    end
end

-------case end 多分支语句

select '笔试成绩'=writtenExam,'等级'=
case
when labexam between 90 and 100 then'A'
when labexam between 80 and 89 then'B'
when labexam between 70 and 79 then'C'
when labexam between 60 and 69 then'D'
else
'E'
end

,'机试成绩'=labExam ,'等级'=
case
when labexam between 90 and 100 then'A'
when labexam between 80 and 89 then'B'
when labexam between 70 and 79 then'C'
when labexam between 60 and 69 then'D'
else
'E'
end

from stumarks

 

/***************************子查询和多表连接***********************************************************/

------利用子查询查询出年龄比byA大的学员(首先查询出byA学员的年龄，然后根据where条件筛选比byA大的学员)
select stuName from stuInfo where stuAge >(select stuAge from stuInfo where stuname = 'byA')

----利用多表链接和子查询
--查询笔试成绩小于60分的学员姓名和成绩
select stuName from stuinfo inner join stumarks on stuinfo.stuNo=stumarks.stuno where writtenExam < 60--多表链接，内连接

--in子查询查询出笔试小于60分学员的stuNO ，然后对应stuinfo表的stuNo
select stuName from stuinfo where stuNo in (select stuNo from stumarks where writtenExam < 60)

--not in
select stuName from stuinfo where stuNo not in (select stuNo from stumarks where writtenExam < 60)

 

 

 

 

/***************************事务-索引-视图***********************************************************/

begin transaction --事务开始
rollback tran --撤销事务
commit tran --提交事务

 

begin transaction --事务开始
declare @i int--定义变量，用于累计事务执行过程中的错误
set @i = 0 --初始值为0
update bank set money = money -300 where name = '张三'
set @i = @i +@@error
update bank set money = money +300 where name = '李四'
set @i = @i +@@error

if(@i>0)--根据是否有错误，确定事务是提交还是撤销

begin
   print '转账失败'
   rollback transaction--回滚事务
end
else
begin
   print '转账成功'
   commit transaction --提交事务,事务结束

end
select * from bank

 

 

 

----非聚集创建索引示例

if exists (select * from sysindexes where name='ix_stuName')
drop index stuinfo.ix_stuName--删除索引

create nonclustered index ix_stuName
on stuInfo(stuName)
with fillfactor=75--填充因子75%
--指定索引搜索
select * from stuinfo
with(index=IX_stuName)

------创建视图
if exists(select * from sysobjects where name='view_BanZhuRen')
drop view view_stu--删除视图
go
create view view_stu
as
select stuName,writtenexam,labexam from stuInfo left outer join stumarks on stuinfo.stuno = stumarks.stuno--左外连接

select * from view_stu

 

 

 

/***************************a存储过程***********************************************************/

-----------------------创建存储过程------------------------
if exists (select * from sysobjects where name='proc_stu')
drop proc proc_stu
go
create proc proc_stu
@count int output ,--输出参数
@writtenPass int=60 ,--输入参数，笔试及格线，默认值为60分
@labPass int=60--输入参数，机试及格线，默认值为60分

as
-------------T-SQL业务语句------------------
if((@writtenPass not between 0 and 100)or(@labPass not between 0 and 100))
   begin
    raiserror('及格线要在0到100之间',16,1)
    return
   end
print '笔试及格线'+convert(char(5),@writtenPass)
print '机试及格线'+convert(char(5),@labPass)
declare @avgWritten float,@avglab float
select @avgWritten=avg(writtenExam),@avglab=avg(labExam)
from stumarks
print '笔试平均分'+convert(char(5),@avgWritten)
print '机试平均分'+convert(char(5),@avgWritten)
--优秀
if(@avgWritten>70 and @avglab>70)
   print '优秀'
else
   print '一般'

--不及格学员信息
select stuinfo.* ,writtenExam,labExam
FROM stuinfo inner join stumarks
on stuinfo.stuno=stumarks.stuno
where writtenExam<@writtenPass or labExam<@labPass

--不及格学员个数
select @count=count(*)
FROM stuinfo inner join stumarks
on stuinfo.stuno=stumarks.stuno
where writtenExam<@writtenPass or labExam<@labPass

go

--调用存储过程----------
exec proc_stu--采用默认值
exec proc_stu 50,50--输入分数线为50
exec proc_stu 50--笔试采用默认值60，机试及格线50
exec proc_stu @labPass=50--机试采用50分作为及格线，笔试默认值

--调用有返回值的存储过程
declare @sum int
exec proc_stu @sum output ,60

if(@@error=0)

print '不及格人数'+convert(char(2),@sum)

else
print '因为及格线不正确，没法确定信息，请重新输入 '

 

 

select * from stuInfo
select * from stuMarks