if exists(select name from sysobjects where name = 'stuInfo')
drop table stuInfo
go
create table stuInfo
(
stuName varchar(20) not null, --学员名称
stuNo char(6) not null, --学号
stuSex char(2) not null, --性别
stuAge int not null, --年龄
stuID numeric(18,0) not null, --身份证号，numeric（18，0）表示18位数字，小数位数为0
stuSeat smallint identity(1,1), --座位号：自动编号（标识列），从1开始自增
stuAddr text --住址
)
go

alter table stuInfo add constraint PK_stuID primary key(stuID);
alter table stuInfo add constraint UQ_stuSeat unique(stuSeat);
alter table stuInfo add constraint CK_stuSex check(stuSex in('男','女'));
---------------------------------------------------------------------------------------------------------
if exists(select name from sysobjects where name='stuMarks')
drop table stuMarks
go
create table stuMarks
(
examNo char(7) not null, --考号
stuNo char(6) not null, --学号
writtenExam int not null, --笔试成绩
labExam int not null --机试成绩
)
go

alter table stuMarks add constraint PK_examNo primary key(examNo);
alter table stuMarks add constraint UQ_stuNo unique(stuNo);
---------------------------------------------------------------------------------------------------------
select * from stuInfo;
insert into stuInfo values('张秋丽','s25301','女',18,412724198603151234,'北京海淀');
insert into stuInfo values('李斯文','s25303','男',22,412724198603161234,'河南洛阳');
insert into stuInfo values('李文才','s25302','男',31,412724198603171234,'陕西西安');
insert into stuInfo values('欧阳俊','s25304','男',28,412724198603181234,'*喀什');
insert into stuInfo values('欧阳丹','s25305','女',23,412724198603191234,'*库车');

select * from stuMarks;
insert into stuMarks values('s27811','s25303',80,58);
insert into stuMarks values('s27813','s25302',50,90);
insert into stuMarks values('s27815','s25301',65,0);
insert into stuMarks values('s27816','s25304',77,82);
insert into stuMarks values('s27817','s25305',70,92);

---------------------------------------------------------------------------------------------------------
--局部变量声明/赋值/查询
declare @stuName varchar(20) --声明
set @stuName = '李文才' --赋值@stuName
select * from stuInfo where stuName=@stuName; --查询
declare @stuSeat int
select @stuSeat = stuSeat from stuInfo where stuName=@stuName --赋值@stuSeat
select * from stuInfo where (stuSeat = @stuSeat + 1) or (stuSeat = @stuSeat - 1) --查询
go
---------------------------------------------------------------------------------------------------------
--IF···ELSE···
--案例1：如果笔试成绩平均分大于70优秀，否则较差
declare @avgWrt float
select @avgWrt=AVG(writtenExam) from stuMarks
if(@avgWrt > 70)
begin
print '成绩优秀,平均成绩：' + convert(varchar, @avgWrt)
select top 3 * from stuMarks order by writtenExam desc
end
else
begin
print '成绩较差，平均成绩：' + convert(varchar, @avgWrt)
select top 3 * from stuMarks order by writtenExam asc
end
---------------------------------------------------------------------------------------------------------
--案例2：如果机试成绩平均分大于40优秀，否则较差
declare @avgLab float
select @avgLab=AVG(labExam) from stuMarks
if(@avgLab > 40)
begin
print '成绩优秀，平均成绩：' + convert(varchar, @avgLab)
select max(labExam) from stuMarks
end
else
begin
print '成绩较差，平均成绩：' + convert(varchar, @avgLab)
select min(labExam) from stuMarks
end
go
---------------------------------------------------------------------------------------------------------
--while循环语句，可以使用 break 退出所在层的循环，continue 退出当次循环
select * from stuMarks where labExam<60
--案例1：labExam低于60分的，循环加到60分，每次加2分
declare @n int
while(1 = 1)
begin
select @n=count(*) from stuMarks where labExam < 60
if(@n > 0)
begin
update stuMarks set labExam = labExam + 2 where labExam < 60
end
else
begin
break
end
end
select * from stuMarks

--update stuMarks set labExam=50 where labExam=60
---------------------------------------------------------------------------------------------------------
select * from stuMarks where writtenExam<60
--案例2：writtenExam低于60分的，循环加到60分，每次加2分
declare @m int
while(1=1)
begin
select @m=count(*) from stuMarks where writtenExam < 60
if(@m > 0)
begin
update stuMarks set writtenExam = writtenExam + 2 where writtenExam < 60
end
else
break;
end
select * from stuMarks

---------------------------------------------------------------------------------------------------------
--case多分支语句
--select col1,col2 =
-- case
-- when···col2 = condition1···then···
-- when···col2 = condition2···then···
-- else···
-- end
--from tabName
select stuNo,writtenExam =
case
when writtenExam >= 90 then 'A'
when writtenExam between 80 and 89 then 'B'
when writtenExam between 70 and 79 then 'C'
when writtenExam between 60 and 69 then 'D'
else 'E'
end
from stuMarks
select * from stuMarks
---------------------------------------------------------------------------------------------------------
select stuName,stuAge =
case
when stuAge >= 30 then 'old'
when stuAge between 20 and 29 then 'strong'
when stuAge between 10 and 19 then 'young'
else 'baby'
end
from stuInfo
select * from stuInfo

---------------------------------------------------------------------------------------------------------
if exists(select name from sysobjects where name = 'bankCounter')
drop table bankCounter
go
create table bankCounter
(
cstName varchar(32) not null, --顾客姓名
countNo numeric(21,0) not null, --账户
curMoney money, --账户现金余额
time_stamp datetime --时间戳
)
go
alter table bankCounter add constraint PK_countNo primary key(countNo);
alter table bankCounter add constraint CK_curMoney check(curMoney > 0) ;
go
insert into bankCounter values('张三',400006666668888889999,1000,'2012-08-09');
insert into bankCounter values('李四',400006666668888889998,1001,'2013-08-06');
go
select * from bankCounter
---------------------------------------------------------------------------------------------------------
--事务处理：一种机制，一个操作序列，它将一组数据库操作指令作为一个整体执行，并一起向系统提交或者撤销，即
-- 这组指令要不全部执行，要不全部不执行，有任何一个错误就要立刻回滚/撤销所有操作。
--begin transaction --开始事务
--commit transaction --提交事务
--rollback transaction --回滚（撤销）事务

--案例：在此例中，张三转账1000给李四，但因check约束curMoney必须大于零，报错，所以update(张三)的现金值没变，
-- 但是，李四的现金却增多了1000
use StudentDemoDB
go
update bankCounter set curMoney = curMoney - 1000 where cstName='张三';
update bankCounter set curMoney = curMoney + 1000 where cstName='李四';
go
--还原数据
select * from bankCounter
update bankCounter set curMoney = curMoney - 1000 where cstName='李四';

-------------用事务处理
begin transaction
use StudentDemoDB
go
declare @errorT int --定义局部变量记录错误次数
set @errorT = 0 --初始化
update bankCounter set curMoney = curMoney - 1000 where cstName='张三';
set @errorT = @errorT + @@error
update bankCounter set curMoney = curMoney + 1000 where cstName='李四';
set @errorT = @errorT + @@error

if(@errorT > 0)
begin
print '错误编码 #' + convert(varchar,@errorT) + '！'
rollback transaction
end
else
begin
commit transaction
end
go
select * from bankCounter
---------------------------------------------------------------------------------------------------------
begin transaction
declare @error int
set @error=0
update bankCounter set curMoney = curMoney - 800 where cstName = '张三'
set @error = @error + @@ERROR
update bankCounter set curMoney = curMoney + 800 where cstName = '李四'
set @error = @error + @@ERROR

if(@error > 0)
begin
rollback transaction
end
else
begin
commit transaction
end
go
select * from bankCounter
go

---------------------------------------------------------------------------------------------------------
--索引
--create [unique] [clustered|nonclustered] index index_name
--on table_name (column)
--[with
-- fillfactor = x
--]
--说明
--unique：执行唯一索引，可选
--clustered/nonclustered：指定聚集索引还是非聚集索引，可选
--fillfactor：表示填充因子，制定一个0-100的值，该值指示索引页填满的空间所占的百分比

--案例1：成绩表stuMark中的writtenExam列经常被查询，为了加快查询速度，现在创建索引。因为成绩可能会重复，
--因此索引只能创建 非聚集索引。
use StudentDemoDB
go
if exists(select name from sysindexes where name = 'IX_stuMarks_writtenExam')
drop index stuMarks.IX_stuMarks_writtenExam
go
create nonclustered index IX_stuMarks_writtenExam
on stuMarks(writtenExam)
with fillfactor=30
go

---------------------------------------------------------------------------------------------------------
--视图
--create view view_name
--as
--<select语句>
--案例：stuInfo/stuMarks的视图
if exists(select name from sysobjects where name='view_stuMarksInfo')
drop view view_stuMarksInfo
go
create view view_stuMarksInfo
as
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo
go

select * from view_stuMarksInfo
---------------------------------------------------------------------------------------------------------
--存储过程

--系统存储过程
exec sp_databases --列出服务器上所有的的数据库
exec sp_helpdb --列出所有数据库的信息
exec sp_helpdb StudentDemoDB --列出指定数据库的信息
exec sp_renamedb 'A','B' --将数据A的名称改为B
exec sp_tables --列出当前环境下可查询的对象的列表

exec sp_columns stuInfo --列出指定表的的所有列的信息
exec sp_help --列出当前环境下所有的对象信息(表名/约束名/视图)
exec sp_help stuInfo --返回指定表的所有信息(表名/字段/约束/)
exec sp_helpconstraint stuInfo --返回指定表的约束信息
exec sp_helpindex stuInfo --返回指定表的索引信息
exec sp_stored_procedures --返回当前环境中的所有存储过程
exec sp_helptext 'view_stuMarksInfo' --查看视图/默认值/（未加密的|用户定义的存储过程）/触发器的语句文本
---------------------------------------------------------------------------------------------------------
--创建不带参的存储过程
--create proc[edure] procedure_name
-- [
-- { @参数1 数据类型 }[= 默认值][output], --其中参数部分可选
-- ······， --其中参数部分可选
-- { @参数n 数据类型 }[= 默认值][output] --其中参数部分可选
-- ]
-- as
-- <select语句>

--案例：查看本次考试平均分，以及未通过(writtenExam < 60 or labExam < 60)的考试学员名单
use StudentDemoDB
go
if exists(select name from sysobjects where name = 'proc_avg_nopass_nopara')
--drop proc proc_avg_nopass_nopara
drop procedure proc_avg_nopass_nopara --与上面的drop proc proc_avg_nopass等价
go
create procedure proc_avg_nopass_nopara
as
select avg(writtenExam) as '笔试平均成绩',avg(labExam) as '机试平均成绩' from stuMarks
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < 60 or b.labExam < 60)
go

exec proc_avg_nopass_nopara
go

select * from stuMarks
update stuMarks set writtenExam=55 where stuNo='s25303'

go
---------------------------------------------------------------------------------------------------------
--创建带输入参数的存储过程
--create proc[edure] procedure_name
-- [
-- { @参数1 数据类型 }[= 默认值][output], --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ······， --其中参数部分可选,有output表示输出参数,否则为输入参数
-- { @参数n 数据类型 }[= 默认值][output] --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ]
-- as
-- <select语句>

--案例：查看本次考试平均分，以及未通过(writtenExam < 自定义参数 or labExam < 自定义参数)的考试学员名单
use StudentDemoDB
go
if exists(select name from sysobjects where name = 'proc_avg_nopass_para')
drop proc proc_avg_nopass_para
go
create proc proc_avg_nopass_para
@written int,
@lab int
as
select avg(writtenExam) as '笔试平均成绩',avg(labExam) as '机试平均成绩' from stuMarks
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < @written or b.labExam < @lab)
go

exec proc_avg_nopass_para 55,65
go

---------------------------------------------------------------------------------------------------------
--默认值（条件）存储过程
--create proc[edure] procedure_name
-- [
-- { @参数1 数据类型 }[= 默认值][output], --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ······， --其中参数部分可选,有output表示输出参数,否则为输入参数
-- { @参数n 数据类型 }[= 默认值][output] --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ]
-- as
-- <select语句>

--案例：查看本次考试平均分，以及未通过(writtenExam/labExam默认<60不通过)的考试学员名单
use StudentDemoDB
go
if exists(select name from sysobjects where name = 'proc_avg_nopass_default')
drop proc proc_avg_nopass_default
go
create proc proc_avg_nopass_default
@writePass int=60, --默认值
@labPass int=60 --默认值
as
select avg(writtenExam) as '笔试平均成绩',avg(labExam) as '机试平均成绩' from stuMarks
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < @writePass or b.labExam < @labPass)
go
exec proc_avg_nopass_default
go

---------------------------------------------------------------------------------------------------------
--创建带输出参数的存储过程
--create proc[edure] procedure_name
-- [
-- { @参数1 数据类型 }[= 默认值][output], --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ······， --其中参数部分可选,有output表示输出参数,否则为输入参数
-- { @参数n 数据类型 }[= 默认值][output] --其中参数部分可选,有output表示输出参数,否则为输入参数
-- ]
-- as
-- <select语句>

--案例：查看本次考试平均分，以及未通过(writtenExam/labExam默认<60不通过)的考试学员名单，还想返回不及格人数
use StudentDemoDB
go
if exists(select name from sysobjects where name = 'proc_avg_nopass_output')
drop proc proc_avg_nopass_output
go
create proc proc_avg_nopass_output
@noPassNum int output,
@wPass int=60,
@lPass int=60
as
select avg(writtenExam) as '笔试平均成绩',avg(labExam) as '机试平均成绩' from stuMarks
select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < @wPass or b.labExam < @lPass)
select @noPassNum = count(c.stuName) from (select a.stuName,a.stuNo,b.examNo,a.stuSex,a.stuAge,b.writtenExam,b.labExam
from stuInfo a,stuMarks b where a.stuNo = b.stuNo and (b.writtenExam < @wPass or b.labExam < @lPass)) c
go

/*调用存储过程proc_avg_nopass_output*/
declare @sum int --声明一个局部变量,用来接收存储过程中输出的参数值
exec proc_avg_nopass_output @sum output --输出参数，此时@wPass,@lPass使用默认值
print convert(varchar,@sum)
go
declare @sum int
exec proc_avg_nopass_output @sum output,66 --此时@wPass=66，d@lPass使用默认值
go
declare @sum int
exec proc_avg_nopass_output @sum output,66,100 --此时@wPass=66，@lPass=100
go