九九乘法表：DECLARE @i intDECLARE @j intDECLARE @str varchar(110)SET @i=1WHILE @i<10 BEGIN SET @j=1 SET @str='' WHILE @j<=@i BEGIN SET @str= @str+CAST(@i AS varchar(1))+' x '+CAST(@j AS varchar(1))+'='+CAST(@i*@j AS varchar(2))+' ' SET @j=@j+1 END print @str SET @i=@i+1 END罚款记录表：Print '罚款记录表情况如下：'Select RID ,BID ,PDate,Amount, 罚款类型=case When PType=1 then '损坏' When Ptype=2 then '延期' When Ptype=3 then '丢失'End from Penalty图书归还记录：declare @count intdeclare @TDay datetimeset @TDay=DateAdd(dd,1,getDate()) --获取明天的日期select @count=count(*) from Borrow where willDate<@TDayIF(@count=0) BEGIN print '明天没有应归还的图书' EndElseIF(@count<10)BEGIN Update Borrow set WillDate=DateAdd(dd,2,WillDate) WHERE willDate<@TDayENDElse BEGIN print '还书总数量：'SELECT book.BName, reader.RName, borrow.LendDate FROM Book as book,Reader as reader,Borrow as borrow WHERE book.BID=borrow.BID and reader.RID=borrow.RID and borrow.willDate<@TDayprint @countEnd统计 册数 和 总金额：DECLARE @count int,@totalMoney moneySELECT @count=sum(BCount) FROM bookSELECT @totalMoney=sum(BCount*Price) FROM bookprint '现存数量'+convert(varchar(10),@count)print '总金额'+convert(varchar(10),@totalMoney)IF @count<10000 print '现有图书不足一万本，还需要继续购置书籍' ELSE print '现有图书在一万本以上，需要管理员加强图书管理'三角形： declare @i int declare @j int declare @str nvarchar(100) set @i=1 set @j=1 set @str='' while(@i<=5) begin while(@j<=@i) begin set @str+='*' set @j+=1 end print @str set @i+=1 end计算 姓名和年龄declare @year int --声明 年龄declare @date datetime --声明 出生日期declare @no varchar(10)--声明 学号set @no='23316' select studentname,FLOOR(DATEDIFF(DY,birthday,GETDATE())/365) from Student where studentno=@no --查询 学号为 23316 的 姓名和年龄 select @date=birthday from Student where StudentNo=@no SET @year = DATEPART(YY, @date) select * from Student where DATEPART(YY,birthday )= @year + 1 OR DATEPART(YY,birthday) = @year - 1
一，打印三角形01，一重循环 declare @i int declare @j int declare @str nvarchar(100) set @i=1 set @j=1 set @str='' while(@i<=5) begin while(@j<=@i) begin set @str+='*' set @j+=1 end print @str set @i+=1 end02，二重循环declare @strr varchar(32)set @strr ='*'declare @row intset @row=1 declare @col int set @col=1 while(@row<=5) begin while(@col<=@row)begin PRINT @strr SET @strr =@strr +'*'SET @col=@col+1end SET @row=@row+1end二。面试题作业 行转列 --创建 tmp 表 并添加数据 create table tmp(riqi varchar(32),shengfu nchar(1)) select * from tmp insert into tmp values('2015-05-09','胜') insert into tmp values('2015-05-09','胜') insert into tmp values('2015-05-09','负') insert into tmp values('2015-05-09','负') insert into tmp values('2015-05-10','胜') insert into tmp values('2015-05-10','负') insert into tmp values('2015-05-10','负') --行转列 select *from tmpselect riqi as 日期, SUM( case when shengfu='胜' then 1 else 0 end ) as 胜, SUM( case when shengfu='负' then 1 else 0 end ) as 负 from tmp group by riqi
--储存 字符串 类型declare @name nvarchar(32)set @name ='小雨吧'print @name--储存 浮点型 declare @Blance decimal(18,7)set @Blance=12.36print @Blance--储存 字符串型 declare @Brithday datetimeset @Brithday='1998-3-30'print @Brithday--最后一个T-SQL语句的 错误符号select @@ERROR --输出 计算机的名称select @@SERVERNAME--输出 自增列 最后一次插入的标示值select @@IDENTITY--cast convert 转换declare @num int set @num=5print '值是：'+cast(@num as nvarchar(32))declare @numm int set @numm=6print '值是：'+convert(nvarchar(32),@numm)--if-elese 结构declare @age intset @age=30 if(@age=30)begin print'库里'end elsebegin print '詹姆斯'end--public class student{--类中的内容都称为成员--public string name;--驼峰命名法 Camel 首字母小写，后续有含义的单词，首字母大写 成员变量。--帕斯卡命名法 Pascal 首字母大写，后续有含义的单词，首字母大写 类名--public void doHomeWork(){ --int age=10; 局部变量 --生命周期 呱呱坠地------dameover -- } --public string Play(){ --rerurn name+"科比布莱恩特"； --} --返回到了哪里？？？ --在方法体 调用的时候 被返回给了 接收的对象 --也就是说 想返回给谁 就返回给谁--}***********************************************************--练习：--统计并显示2013-08-09 的oop考试平均分--如果平均分在70分以上，显示“考试成绩优秀”，并显示前三名学生的考试信息--如果在以下，显示“考试成绩较差”，并显示后三名学生的考试信息 select * from result order by examdate --00.求出oop课程对应的课程编号 declare @subid int select @subid=SubjectId from Subject where subjectname='oop' --01.查询平均分 declare @avg int select @avg=avg(Studentresult) from result where examdate>='2013-08-09' and examdate <'2013-08-10' and subjectid=@subid if(@avg>=70) begin print '成绩优秀' -- *打印前三名的成绩 select top 3 from result where examdate>='2013-08-09' and examdate <'2013-08-10' and subjectid=@subid order by studentresult desc end else begin print '成绩较差' select from Result where examdate>='2013-08-09' and examdate <'2013-08-10' and subjectid=@subid order by studentresult end********************************************************一。case when then when thenend二。while循环 循环结构-- 一定不能出现 while(true)--100内偶数和 declare @sum int declare @num int set @sum=0 set @num=1 while(@num<=100) begin if(@num%2=0) begin set @sum=@num+@sum end set @num=@num+1 end print @sum三。GO指令四。--***********************************************************************--检查学生参加“oop”课最近一次考试是否有不及格（分及格）的学生。--如有，每人加分，高于分的学生不再加分，直至所有学生这次考试成绩均及格--code a little ,debug a little 编写一点点，调试一点点--01.找一个变量存储oop科目编号declare @subid int select @subid=subjectid from subjectwhere subjectname='oop'--02.找到一个变量，存储最近一次考试时间declare @maxdate datetime select @maxdate= max(examdate) from resultwhere subjectid=@subidselect * from resultwhere subjectid=@subid and examdate=@maxdate--03.投影出符合条件的人数declare @sum intselect @sum=COUNT(Studentno) from Resultwhere subjectid=@subid and examdate=@maxdateand studentresult<70while(@sum>0)begin --每人+2分 update result set studentresult+=2 where subjectid=@subid and examdate=@maxdate and studentresult<95 select @sum=COUNT(Studentno) from Resultwhere subjectid=@subid and examdate=@maxdateand studentresult<70end
上机练习一
0001使用变量打印直角三角形
DECLARE @tag nvarchar(1) SET @tag = '★'PRINT @tagPRINT @tag + @tagPRINT @tag + @tag + @tagPRINT @tag + @tag + @tag + @tagPRINT @tag + @tag + @tag + @tag + @tagGO
上机二DECLARE @NO int -- 学号SET @NO = 3DECLARE @date datetime -- 出生日期DECLARE @year int -- 出生年份-- 获得学号是20011的学生姓名和年龄SELECT StudentName 姓名, FLOOR(DATEDIFF(DY, BornDate, GETDATE())/365) 年龄 FROM student WHERE StudentNo=@NO-- 查询输出比学号是20011的学生大1岁和小1岁的学生信息SELECT @date=BornDate FROM Student -- 使用SELECT赋值 WHERE StudentNo=@NOSET @year = DATEPART(YY, @date) SELECT * FROM Student WHERE DATEPART(YY,BornDate) = @year + 1 OR DATEPART(YY,BornDate) = @year - 1GO
上机三
DECLARE @name nvarchar(50) --姓名DECLARE @score decimal(5,2) --分数SELECT TOP 1 @score=StudentResult,@name=stu.StudentName FROM Result r INNER JOIN Student stu ON r.StudentNo=stu.StudentNo INNER JOIN Subject sub ON r.SubjectNo=sub.SubjectNo WHERE r.StudentNo='20012' AND sub.SubjectName='Java Logic' ORDER BY ExamDate DESCPRINT '学生姓名:' + @nameIF (@score > 85) PRINT '考试等级:' + '优秀'ELSE IF (@score > 70) PRINT '考试等级:' + '良好'ELSE IF (@score > 60) PRINT '考试等级:' + '中等'ELSE PRINT '考试等级:' + '差'
上机四
DECLARE @date datetime --考试时间DECLARE @subNO int --课程编号SELECT @subNO=SubjectNo FROM SubjectWHERE SubjectName='C# OOP'SELECT @date=max(ExamDate) FROM Result WHERE SubjectNo=@subNOPRINT '加分前学生的考试成绩如下：'SELECT 学号=StudentNo,成绩等级=CASE WHEN StudentResult BETWEEN 0 AND 59 THEN '你要努力了！！！' WHEN StudentResult BETWEEN 60 AND 69 THEN '★' WHEN StudentResult BETWEEN 70 AND 79 THEN '★★' WHEN StudentResult BETWEEN 80 AND 89 THEN '★★★' ElSE '★★★★' END FROM Result WHERE SubjectNo=@subNO AND ExamDate=@date DECLARE @n intWHILE(1=1) --条件永远成立 BEGIN SELECT @n=COUNT(*) FROM Result WHERE SubjectNo=@subNO AND ExamDate=@date AND StudentResult<60 --统计不及格人数 IF (@n>0) UPDATE Result SET StudentResult=StudentResult+2 FROM Result WHERE SubjectNo=@subNO AND ExamDate=@date AND StudentResult<=98 --每人加2分 ELSE BREAK --退出循环 END
上机五USE MySchool GOIF EXISTS(SELECT * FROM sysobjects WHERE name ='Admin')DROP TABLE AdminGOCREATE TABLE Admin( --创建表[LoginId] [nvarchar](50) NOT NULL,[LoginPwd] [nvarchar](50) NOT NULL)ALTER TABLE Admin --添加主健约束ADD CONSTRAINT PK_Admin PRIMARY KEY (LoginId)GOINSERT INTO Admin([LoginId],[LoginPwd]) VALUES('TEST1','123') --插入数据INSERT INTO Admin([LoginId],[LoginPwd]) VALUES('TEST2','123456') --插入数据GOUPDATE Admin SET [LoginPwd]='1234567' WHERE [LoginId]='TEST2' --更新数据GO
0 0
