begin
print '平均年龄正常' +cast(@avgage as varchar )
select top 2 * from student order by stuage asc------年龄最小的两个人 end
------------统计平均分，>70,成绩优秀，显示前三名
-----------70分以下，本班成绩较差，显示后三名学生成绩 declare @avgscore decimal(10,2)
select @avgscore =AVG (score) from score if (@avgscore>70) begin
print '成绩优秀,平均成绩为：'+cast (@avgscore as varchar) select top 3 * from score order by score desc end
if (@avgscore<70) begin
print '本班成绩较差,平均成绩为：'+cast (@avgscore as varchar) select top 3 * from score order by score asc end
-----------while declare @i int =0 while (@i <5) begin
set @i=@i+1 --或者select----给变量赋值 print @i if (@i=3)
break ----退出循环
--continue -----结束本次循环继续下一次循环 end
---题目较难，提分确保都通过，提分原则，每次加两分，看是否全部通过，没有全部通过，再加两分，如此反复，知道所有人都通过 --1统计没有通过的人数 declare @num int
select @num =COUNT (snumb) from score where score <60 --2、有人没有通过，加2分 while (@num>0) begin
update score set score =score +2 ------加分 where score+2 <=100 ----限制分数在100内
select @num =COUNT (snumb) from score where score <60 end
print '加完分后的成绩：'
select * from score
----------------------------逻辑控制语句--case when then end select snumb ,成绩= case
when score between 60 and 70 then 'D' when score between 70 and 80 then 'C' when score between 80 and 90 then 'B' when score between 90 and 100 then 'A' ELSE 'E' END
from score
-----------------go 批处理语句
--------------全局变量
print 'SQL Server版本'+@@version print '服务器名称' +@@servername
--insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) -- values ('tom','s25312','男',18,8,'地址不详')
-- print '当前错误号：'+cast (@@error as varchar(5)) ----如果大于0，表示上一条语句执行有错
insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values (25,25,1222,12,7,23)
print '当前错误号：'+cast (@@error as varchar(5)) go
--系统函数调用的练习
select newid() ----全球唯一标识 select GETDATE () -----系统时间 select ABS (-90) ----绝对值
select ROUND(3.1489,2)---四舍五入 select FLOOR (3.1415)----3 select FLOOR (3.9999)----3 select FLOOR (-3.9999)--- -4 select CEILING (3.1514)----4 select CEILING (3.9999)----4 select CEILING (-3.1514)---- -3
---字符串函数
select LEN('aaaaa') - --字符串长度 select LOWER ('AvdfASGFG')---小写 select upper ('AvdfASGFG')---大写
select RTRIM ( LTRIM (' aaaa ')) ---去左右空格 select SUBSTRING ('asfa123',2,3) ------取字符串
declare @index int
set @index=CHARINDEX('@','asdfgh@sinna.com') ---------获得某个字符的索引下标
select SUBSTRING ('asdfgh@sina.com',@index+1 ,10 ) -----取子字符串
-------------------日期函数 select GETDATE ()
select DATEPART(MONTH ,GETDATE ())---取当前日期中的月 select DATEPART(HOUR ,GETDATE ()) ---取当前日期中的小时 select DATEPART(YEAR ,GETDATE ()) ---取当前日期中的年 select DATEADD (day,100,getdate()) ----从当前日期到100天后 select DATEADD (day,-100,getdate()) ----从当前日期到100天之前
select DATEDIFF(day,getdate(),'2013-12-19') ----计算日期之间的差值 select DATEDIFF(dd ,'1990-12-17',getdate()) ---我的年龄
------将成绩进行反复加分 ------平均分超过85 分为止， ------90分以上：不加分 ------80-90加一分 ------70-79加2分 ------60-69加3分 ------69以下不加分 create database students use students
create table score (
examno varchar(10), stuno varchar(10), writtenexam int, labexam int )
insert into score(examno,stuno,writtenexam,labexam) values('s271811','s25303',96,58)
insert into score(examno,stuno,writtenexam,labexam)
values('s271813','s25302',66,90)
insert into score(examno,stuno,writtenexam,labexam) values('s271816','s25301',93,82)
insert into score(examno,stuno,writtenexam,labexam) values('s271818','s25328',61,65) select * from score
declare @labavg int while 1=1 begin
update score set labexam= case
when labexam<60 then labexam+5
when labexam>=60 and labexam<70 then labexam+3 when labexam>=70 and labexam<80 then labexam+2 when labexam>=80 and labexam<90 then labexam+1 else labexam end
select @labavg=AVG(labexam) from score if @labavg>=85 break end go
select * from score go
------------------------------------------------------------事物、视图、索引 ------------------------------------------银行转账业务 create table bank (
customerName char(10), currentMoney Money ) go
alter table bank
add constraint CK_currentMoney check (currentMoney>=1) go
--添加数据
insert into bank(customerName,currentMoney) values ('张三',1000)
insert into bank(customerName,currentMoney) values ('李四',1) select * from bank
update bank set currentMoney =currentMoney -1000 where customerName='张三'
update bank set currentMoney =currentMoney +1000 where customerName='李四' go
-----------------------------------------------显示事物模式，事物语句的写法
-----1、开始事物 begin transaction -----变量：记录错误 declare @errorsum int
set @errorsum =0 ----初始为0 -----两个update
update bank set currentMoney =currentMoney -1000 where customerName='张三'
set @errorsum =@errorsum +@@ERROR -----------记录错误号 update bank set currentMoney =currentMoney +1000 where customerName='李四'
set @errorsum =@errorsum +@@ERROR -----------记录错误号 -----判断变量 >0 有错 -----事物回滚 --- =0
---事物提交 ---查看数据 if(@errorsum >0) begin
print '转账失败，回滚事物'
rollback transaction------------回滚事物 end
else if(@errorsum =0) begin
print '转账成功，提交事物'
commit transaction -------------提交事物 end
select * from bank
----------------------------------------------------------事物模式的分类 set implicit_transactions on -----设置隐性事物模式
--事物自动开始，手动结束（rollback（回滚） 或者 commit（提交）） select * from bank