create database AA go use AA go create table Student ( sid int primary key, sname nvarchar(20, sex nvarchar(20, birthday datetime, class nvarchar(10, pwd nvarchar(10 go create table Course ( cid int identity(1,1 primary key, cname nvarchar(20 go create table Score ( sid int, cid int, score int, primary key(sid,cid go insert into Course values('C#' insert into Course values('English' insert into Student values(1001,'张三','男', '1990-1-12','一班','123' insert into Student values(1002,'李四','女', '1990-4-20','一班','456' insert into Student values(1003,'王五','男', '1991-10-11','二班','789' insert into Studentvalues(1004,'赵六','男', '1992-8-5','二班','101' insert into Student values(1005,'天齐','男', '1992-5-5','三班','120' insert into Score values(1001,1,65 insert into Scorevalues(1001,2,60 insert into Score values(1002,1,50 insert into Score values(1002,2,40 insert into Score values(1003,1,75 insert into Score values(1003,2,60 insert into Score values(1004,1,72 insert into Score values(1004,2,45 select * from student select sname from student select sid,sname from student select * from student order by sid desc --desc 降序 asc 升序 select * from student order by sex,class asc select * from student order by birthday desc select * from student where sid=1001 select * from student where sex='男' and birthday>'1991-1-1' select * from student where birthday between '1990-1-1' and'1991-1-1' select * from student where sname like '%五%' --%%是通配符 select distinct(class from student --去除重复项 select count(sid from student select count(sid from student where sid=1001 and pwd='123' select count(sid,class from student group by class select count(sid,sex from student group by sex select count(sid,sex,class from student group by sex,class select count(sid,class from student where sex='男' group by class select sum(score from score select avg(score from score where sid=1001 select max(score,cid from score group by cid select avg(score,cid from score group by cid having avg(score>60 select avg(score,sid,cid from score group by sid,cid havingavg(score<60 select avg(score,sid from score group by sid having avg(score>59 select * from score where score = (select max(score from score select * from student where birthday = (select min(birthday from student select * from student select * from course select * from score select count(sid as Y_N from student where sid=1001 and pwd='123'--select avg(score as avg from score where sid=1001 --select count(sid,class from student group by class --select count(sid,sex from student group by sex --selectcount(sid,class,sex from student group by class,sex --select count(sid,class from student where sex='男' group by class --内连接 select student.sid,student.sname,student.class,score.score from student inner join score on student.sid = score.sid --三表链接 selectstudent.sid,student.sname,ame,score.score from score inner join student on student.sid=score.sid inner join course on score.cid=course.cid where student.sname = '张三' --用右外表查询（以右表为基准） selectstudent.sid,student.sname,student.class,score.score from student right outer join score on student.sid=score.sid select student.sid,student.sname,student.class,score.score from student left outer join score on student.sid=score.sid where score.score is null --交叉链接(笛卡尔集合(少用 select student.sid,student.sname,score.score from student cross join score --嵌套查询 --查询最高分的学生 select sid,sname from student where sid in (select sid from score where score = (select max(score from score --总分最高的学生学号 select sid from( select top 1 sum(score as s,sid from score group by sid order by s desc a --统计每门课程最高分的学生的学号 select score.sid,score.cid,score.score from score, (select max(score as m,cid from score group by cid b where score.cid=b.cid andscore.score= b.m --查班级平均分 select avg(c.score,c.class from (select a.class,b.score from student a,score b where a.sid=b.sid c group by class --个人平均成绩 selectavg(c.score,c.sid from (select a.sid,b.score from student a,score b where a.sid=b.sid c group by sid --单科最高分的同学学号和姓名和班级 selectdistinct(d.sid,d.sname,d.class from ( select c.sid,c.sname,c.class from student c,( select sid,score from score,( select max(score as s,cid from score group by cid a where a.s = score.score b where c.sid=b.sid d declare @i int --int i =0; declare @s nvarchar(10 --string s; set @i = 1; --i = 10; --set @s = 'AAAAAAA' --s = 'AAAAAAA' while @i<10 --while 循环 begin print @i set @i = @i + 1 --i++ end select sid,score,case when cid=1 then 'C#' when cid=2 then 'English' end from score select sid,cid,score, case whenscore>59 then '及格' else '不及格' end from score --print @i --本地测试用print --print @s -- --if @i=0 --if条件语句 --begin --print 'BBBBBBBBBBBBBBBBBBBBBBBB' --end --else --begin --print 'SSSSSSSSSSSSS' --end --使用存储过程判断用户登录信息alter proc sp_login @sid int, --输入参数 @pwd nvarchar(20, --输入参数 @snvarchar(20 output --输出参数 as declare @i int set @i = (select count(1 from student where sid=@sid and pwd=@pwd --return @i if @i = 1 set @s = '合法用户' else set @s = '非法用户' declare @s nvarchar(20 exec sp_login 1001,'123',@s output print @s if(select min(score from score where sid=1001>90 print '学生1001成绩全部优秀' else if(select min(score from score where sid = 1001>59 print '学生1001成绩全部及格' else print '学生1001有成绩不及格' --为表创建具有不同字段名的视图 create view v_stu(sid,sname,sex as select sid,sname,sex from student --查看视图数据 select * from v_stu --使用存储过程实现从表及联删除 alter proc sp_delete @sid int as delete from score where sid = @sid delete from student where sid = @sid exec sp_delete 1001 --使用存储过程添加学生信息 -------------------添加---------------------------------------开始------------------------------------------------------------------------- alter proc sp_add @sid int, @sname nvarchar(20, @sex nvarchar(10, @birthday datetime, @class nvarchar(10, @pwd nvarchar(10, @i int, @r nvarchar(10 output --一个过程可以有多个输出参数但只有一个返回值输出参数是任何类型 as if @i = 0 begin if not exists (select sid from student where sid=@sid begin insert into studentvalues(@sid,@sname,@sex,@birthday,@class,@pwd set @r= '添加成功' end else set@r= '重复添加' end else begin update student set sname =@sname,sex=@sex,birthday=@birthday,class=@class,pwd=@pwd where sid=@sid if @@rowcount >0 set @r = '修改成功' else set @r = '修改无效' end declare @r int exec @r = sp_add 1011,'李四','男' ,'1990-1-12','一班','123' print @r ------------------------------------查找所有学生分数--------------------一个------------------------------------------------------------------- alter proc sp_selectAllStudentScore as selectstudent.sid,student.sname,ame,score.score from score right join student on student.sid=score.sid left join course on score.cid=course.cid ------------------------------------------------ exec sp_selectAllStudentScore select * from student delete from student where sid= 0 -----------------------------------条件查询学生分数----------------------一个----------------------------------------------------------- create proc sp_selectstudentscore @sid int, @sname nvarchar(10 as if @sid>0 begin selectstudent.sid,student.sname,ame,score.score from score inner join student on student.sid=score.sid inner join course on score.cid=course.cid where student.sid=@sid end else begin select student.sid,student.sname,ame,score.score from score inner join student on student.sid=score.sid inner join course on score.cid=course.cid where student.sname like '%'+@sname+'%' end --------------------------------------------- exec sp_selectstudentscore 1001,'' exec sp_selectstudentscore '','三' --------------------------查找与删除学生----------------------------------一个----------------------------------------------------------- alter proc sp_operstudent @sid int, @i int as if @i = 0 select * from student where sid = @sid else delete from student where sid = @sid ----------------------登录-------------------------------------一个------------------------------------------------------------------- alter procsp_login @sid int, @pwd nvarchar(20 as declare @i int set @i = (select count(1 from student where sid = @sid and pwd = @pwd return @i --------------------------查看学生分数信息---------------------------------------------------------------------------------------------------- alter proc sp_viewstudent @sid int, @sum int output, @avg int output, @pid int output as declare cur cursor for select avg(score p, sum(score s,sid from score group by sid order by s desc open cur declare @p int, @s int, @sid2 int, @i int set @i = 1 fetch from cur into @p, @s,@sid2 while @@fetch_status = 0 begin if @sid2 = @sid begin set @pid = @i set @sum = @s set @avg = @p end set @i = @i + 1 fetch from cur into @p,@s,@sid2 end close cur deallocate cur select cname,score from score a,course b where a.cid = b.cid and a.sid = @sid declare @avg int,@pid int,@sum int exec sp_viewstudent 1003 ,@pid output,@sum output,@avg output print @sum print @avg print @pid -------------------------------------------------------------结束------------------------------------------------------ --使用存储过程添加新课程信息，并输入新课程的ID alter proc sp_addCourse@cname nvarchar(10 as insert into course values(@cname return @@identity declare @i int exec @i = sp_addCourse 'PHP' print @i select * from course --实现数据表分页查询--页码 = 总行数 / 每页行数 page --每页行数 size --总行数 --paixu linshibiao zhuanyonghanshu select top 5 * from (select top (9-(2-1*5 * from employees order by employeeid desc a order by a.employeeid alter proc sp_page @page int, --页码 @table varchar(10, --表名称 @orderby varchar(10, --排序字段名 @size int as declare @sql varchar(500 --放sql语句字符串 set @sql = 'declare @count int ' set @sql = @sql +' set @count =(select count(1 from ' + @table + '' set @sql = @sql + ' select top ' + str(@size + ' * from (select top (@count - (' + convert(varchar(10,@page + '-1*' +str(@size+' * from ' + @table + ' order by ' + @orderby + ' desc a ' + 'order by a.'+ @orderby exec (@sql print @sql exec sp_page 1,'employees','employeeid',3 --函数 declare @str varchar(10 set @str = '1234' declare @i int set @i = 1234 --print len(@str printlen(ltrim(str(@i print substring(@str,1,2 print floor(rand(*1000 -- print getdate(--Datetime.Now print dateadd(day,2,getdate( print dateadd(day,2,'2011-10-20'--往后添加时间 print datediff(day,'2011-10-25','2012-12-10'--时间差 TimeSpan.days print datepart(year,'2011-10-25'--获取时间格式中的一部分（即年，月或日） print datepart(month,'2011-10-25' print datepart(day,'2011-10-25' printconvert(nvarchar(10,datepart(year,'2011-10-25'+ '/' + convert(nvarchar(10,datepart(month,'2011-10-25' + '/' +convert(nvarchar(10,datepart(day,'2011-10-25' --按出生年份统计学生人数 --sid counts years select count(sid,datepart(year,birthday from student group by datepart(year,birthday select * from @table declare @table nvarchar(10 set @table = 'student' exec('select * from ' + @table create proc sp_AA @table varchar(10 as exec ('select * from ' + @table exec sp_AA 'student' --编写通用版的分页存储过程 --自定义函数 create functionfun(@i int returns nvarchar(10 as begin return convert(nvarchar(10,@i end printdbo.fun(10--dbo 当前数据库里有效 --定义函数输入姓名后返回学号 create function fun2(@sname nvarchar(10 returns int as begin declare @i int set @i = (select sid from student where sname=@sname return @i end print dbo.fun2('张三' --查询学号，课程号，分数同时在分数栏将<60的成绩直接输出为不及格 --学号课程分数 --1001 1 95 --1002 1 不及格 alter function fun3(@score int returns nvarchar(10 as begin declare @r nvarchar(10 if @score<60 set @r = '不及格' else set @r =convert(nvarchar(10,@score return @r end select sid,cid,dbo.fun3(score from score --在同一列，输出 Employees 表中的lastname 和 firstname 字段 create functionfun4(@lastname varchar(10,@firstname varchar(10 returns varchar(20 as begin return@lastname +'-'+@firstname end select employeeid,dbo.fun4(lastname,firstname,title from employees --计算 order details 表每条订单的总价 select * from [order details] create function fun5(@unitprice money,@quantity smallint,@discount real returns smallint as begin return @unitprice * @quantity * (1 - @discount end select*,dbo.fun5(unitprice,quantity,discount from [order details] --游标不占用物理内存，全部是临时文件 declare cur1 cursor for select sid,sname,class from student open cur1 declare @sid int, @sname nvarchar(10,@class nvarchar(10,@i int set @i = 1 fetch from cur1 into @sid,@sname,@class --fetch 取游标所在的行的值 while @@fetch_status = 0 begin print convert(nvarchar(10,@i+'. '+convert(nvarchar(10,@sid+','+@sname+','+@class set @i =@i + 1 fetch from cur1 into @sid,@sname,@class end close cur1 deallocate cur1 --给所有分数＜60的人加送10分declare cur2 cursor for select * from score open cur2 declare @sid int,@cid int,@score int fetch from cur2 into @sid,@cid,@score while @@fetch_status = 0 --0语句成功 -1语句失败或行不在结果集中 -2提取的行不存在 begin if @score < 60 update score set score = @score + 10 where sid = @sid and cid = @cid fetch from cur2 into@sid,@cid,@score end close cur2 deallocate cur2 --清空资源 --找出重名的人，并使用合适的方式返回结果数据。