SQL Server 高级语法

--创建学生数据库
if exists (select * from sysdatabases where name = 'student')
drop database student
create database student
on(--主数据文件name='studentmdf',--数据库文件的逻辑名filename='d:\SQL2008Workspace\student.mdf',--文件存放路径size=3mb,--文件的初始大小maxsize=5mb,--文件的最大大小filegrowth=20%--文件增长率)
log on(--日志文件 name='studentldf',--数据库日志的逻辑名filename='d:\SQL2008Workspace\student.ldf',--文件存放路径,日志文件和主文件必须在同一个文件夹下面size=2mb,--文件的初始大小maxsize=5mb,--文件的最大大小filegrowth=1mb--文件的增长速度)
go--创建学生信息表
if exists (select * from sysobjects where name='stuInfo')
drop table stuInfo
create table stuInfo
(stuNo int primary key identity (1,1),--学号stuName nvarchar(10) not null,--学生姓名stuSex nvarchar(2) not null,--学生性别stuAge int not null,--学生年龄stuSeat int not null,--学生座位stuAddress nvarchar(20) --学生住址)
go--添加约束
alter table stuInfo
add constraint Ck_stuSex check (stuSex='男' or stuSex='女')
alter table stuInfo
add constraint CK_stuAge check (stuAge between 18 and 22)
--删除约束,是约束名(在表的约束中,或键中),而不是列名(后来增加)
alter table stuInfo 
drop constraint CK_stuAge
--添加列-入学日期(后来增加,扩展语法)
alter table stuInfo
add studydate datetime--插入数据
insert into stuInfo 
values('商鞅','男',19,1,'秦国') 
insert into stuInfo 
values('韩非子','男',20,2,'韩国')
insert into stuInfo 
values('刘亦菲','女',18,3,'中国')
insert into stuInfo 
values('张仪','男',22,4,'秦国')
insert into stuInfo 
values('芈月','女',19,5,'楚国')
insert into stuInfo 
values('宋慧乔','女',21,6,'韩国')--创建学生成绩表
if exists (select * from sysobjects where name='stuScore')
drop table stuScore
create table stuScore
(examNo int primary key identity (1,1),--考试编号stuNO int,--学号writtenExam float,--笔试成绩labExam float --机试成绩)
go
--添加约束
alter table stuScore
add constraint FK_stuNo foreign key (stuNo) references stuInfo(stuNo)--插入数据
insert into stuScore
values (1,90,90)
insert into stuScore
values (2,90,80)
insert into stuScore
values (3,60,60)
insert into stuScore
values (4,80,70)
insert into stuScore
values (5,70,65)select * from stuScore
select * from stuInfo
delete  from stuScore---声明局部变量
declare @name nvarchar(10)
---给局部变量赋值,必须确保筛选出来的记录只有一条
select @name=stuName from stuInfo where stuNo=3
---输出
print '学生姓名:'+@name
go---SQL语句中的print和java中system.out.print()的功能一样
---SQL语句中存在转型问题,需要将int类型转为varchar类型才能输出
---java中会默认将int型转为String类型
declare @stuAge int
set @stuAge = 19
print '学生年龄为'+convert(nvarchar(5),@stuAge)+'岁的学生信息'
select * from stuInfo where stuAge=@stuAge 
go ---全局变量,是系统定义的,只能读取
print @@error
print @@version--编写T-SQL查找刘亦菲左右同桌
--1,查找刘亦菲的座位号,将座位号赋给变量@seat
declare @seat int
select @seat=stuSeat from stuInfo where stuName='刘亦菲'
--2,将座位号+1和-1查找左右同桌
print '刘亦菲的左右同桌是'
select * from stuInfo where stuSeat=@seat-1 or stuSeat=@seat+1
go---逻辑控制语句
---if-else语句---例题:统计并显示本班笔试平均分,平均分大于80显示'成绩优秀',并显示前三名学生信息;
---反之,'成绩较差',显示后三名学生信息
declare @avgWe float
select @avgWe=AVG(writtenExam) from stuScore
if(@avgWe>80)beginprint '成绩优秀,笔试前三名学生信息为'select top 3 * from stuScore order by writtenExam descend
elsebeginprint '成绩较差,笔试后三名学生信息为'select top 3 * from stuScore order by writtenExam ascend
go--while循环语句--例题:本次考试成绩较差，假定要提分，确保每人笔试都通过。
--提分规则很简单，先每人都加5分，看是否都通过，
--如果没有全部通过，每人再加5分，再看是否都通过，如此反复提分，直到所有人都通过为止 。--1,统计没通过人数
declare @num int
--2,人数大于零,每个人加5分
--3,循环语句
while(1=1)beginselect @num=COUNT(writtenExam) from stuScore where writtenExam<75if(@num>0)beginupdate stuScore set writtenExam=writtenExam+5 --每人加5分update stuScore set writtenExam=100 where writtenExam>100endelsebeginbreak--退出循环endend
select * from stuScore
go--case-end多分支语句
--例题:采用美国的ABCDE五级打分制来显示笔试成绩。
--A级:  90分以上
--B级： 80－89分
--C级:  70－79分
--D级： 60－69分
--E级： 60分以下print 'ABCDE五级显示成绩如下'
select stuNo,笔试成绩=casewhen writtenExam>=90 then 'A'when writtenExam>=80 and writtenExam<=89 then 'B'when writtenExam>=70 and writtenExam<=79 then 'C'when writtenExam>=60 and writtenExam<=69 then 'D'else 'E'end
from stuScore
go/*课堂练习则根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。请编写T-SQL语句实现。90分以上：   不加分80－89分：   加1分70－79分：   加2分60－69分：   加3分60分以下：   加5分
*/
select * from stuScore					
declare @avglab float
while(1=1)
beginselect @avglab=avg(labExam) from stuScoreif(@avglab<85)beginupdate stuScore set labExam=casewhen labExam>=90 then labExamwhen labExam>=80 and labExam<=89 then labExam+1when labExam>=70 and labExam<=79 then labExam+2when labExam>=60 and labExam<=69 then labExam+3when labExam<60 then labExam+5endendelsebeginbreakend
end
go
select * from stuScore/*
编写T-SQL语句,查看年龄比韩非子大的学员信息
*/
--子查询
select * from stuInfo where stuAge>(select stuAge from stuInfo where stuName='韩非子')
go
--采用T-SQL变量查询
declare @age int
select @age=stuAge from stuInfo where stuName='韩非子'
select * from stuInfo where stuAge>@age 
go
/*
1子查询在WHERE语句中的一般用法：SELECT … FROM 表1 WHERE 字段1 >(子查询)2外面的查询称为父查询，括号中嵌入的查询称为子查询3UPDATE、INSERT、DELETE一起使用，语法类似于SELECT语句4将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个 
*//*
查询笔试通过（90分）的学员。
*/
--子查询(先执行子查询,再执行父查询)
select * from stuInfo where stuNo in (select stuNo from stuScore where writtenExam>90)
--表连接查询
select * from stuInfo,stuScore where stuInfo.stuNo=stuScore.stuNO and writtenExam>90
/*
1一般来说，表连接都可以用子查询替换，但有的子查询却不能用表连接替换
2子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据
3表连接更适合于查看多表的数据
*//*
检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分；否则，每人允许提5分 
*/
--exists查询
if exists (select * from stuScore where writtenExam>80 )beginprint '本班机试成绩有人高于80分,每人机试成绩+2分'update stuScore set labExam=labExam+2 select * from stuScoreend
elsebeginupdate stuScore set labExam=labExam+5endgo/*
检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都>60分），则试题偏难，每人加3分，否则，每人只加1分 */
--not exists查询
if not exists(select * from stuScore where writtenExam>60 and labExam>60)beginprint '有人未通过考试'update stuScore set labExam=labExam+3select * from stuScoreend
elsebeginprint '全部通过考试'update stuScore set labExam=labExam+0select * from stuScoreend
go---T-SQL语句的综合运用
declare @iNo int --应到人数
declare @sNo int --实到人数
select @iNo=count(stuNo) from stuInfo
select @sNo=count(stuNo) from stuScore
select '应到人数'=@iNo,'实到人数'=@sNo,'缺考人数'=@iNo-@sNo --select后面可以不接from 表名,作为变量的输出一种语法
go --将查询结果创建新表
select stuName,stuInfo.stuNo,writtenExam,labExam,
ispass=casewhen writtenExam>=60 and labExam>=60 then 1else 0end
into newTable
from stuInfo left join stuScore on stuInfo.stuNo=stuScore.stuNo
select * from newTable
gouse student
select stuName as '姓名',stuNo as '学号',
'笔试成绩'=casewhen writtenExam is null then '缺考'else convert(varchar(4),writtenExam)--注意转型end
,'机试成绩'=casewhen labExam is null then '缺考'else convert(varchar(4),labExam)end
,'是否通过'=casewhen ispass=1 then '是'else '否'end
from newTable
goselect '总人数'=COUNT(stuNo),'通过人数'	=SUM(ispass),'通过率'=(convert(varchar(5),AVG(ispass*100))+'%')
from newTable
godeclare @avgW numeric(4,1)
declare @avgL numeric(4,1)
select @avgW=AVG(writtenExam) from stuScore where writtenExam is not null
select @avgW=AVG(labExam) from stuScore where labExam is not null
if @avgW>@avgLbeginwhile(1=1)beginupdate newTabella set labExam=labExam+1if((select max(labExam) from stuScore)>95)beginbreakendendend
elsebeginprint '笔试机试两手抓!'end
go