作业：
--------------------------------------------------------------------------关键字 " in " 返回多条记录select StudentName from student where LoginPwd in(select LoginPwd from student where LoginPwd='111111')--关键字 " = " 返回单条记录select StudentName from student where LoginPwd =(select LoginPwd from student where LoginPwd='123456')-------------------------------------------------------------------------------查询 最近一次 参加‘opp’ 考试的 最高分 and 最低分select max([StudentResult])as 最高分,min([StudentResult]) AS 最低分from result where examdate=( select max(examdate) from result where Subjectid=( select SubjectId from subject where Subjectname='oop'))and Subjectid=( select SubjectId from subject where Subjectname='oop') ------------------------------------------------------------------- --查询参加 ‘oop’课程 最近一次 考试的在读 学生名单（ 姓名 学号） select studentName ,studentNo from student where studentno in ( select StudentNo from result where SubjectId= ( select SubjectId from subject where SubjectName='oop' ) and ExamDate= ( select max(examdate) from result where SubjectId= ( select SubjectId from subject where SubjectName='oop' ) ) )-----------------------------------------------------------------------------------案例：检查“oop”课程最近一次考试。--如果有分以上的成绩，则每人提分；--否则，每人提分。最终的成绩不得大于95分if exists( select studentresult from Result where SubjectId= (select SubjectId from Subjectwhere SubjectName='oop' ) and ExamDate= (select MAX(ExamDate) from Resultwhere SubjectId=( select SubjectId from Subject where SubjectName='oop') ) and StudentResult>80)begin --有，每人提分 99 update Result set StudentResult=100 where SubjectId= (select SubjectId from Subjectwhere SubjectName='oop' ) and ExamDate= (select MAX(ExamDate) from Resultwhere SubjectId=( select SubjectId from Subject where SubjectName='oop') ) and StudentResult>98 update Result set StudentResult+=2 where SubjectId= (select SubjectId from Subjectwhere SubjectName='oop' ) and ExamDate= (select MAX(ExamDate) from Resultwhere SubjectId=( select SubjectId from Subject where SubjectName='oop') ) and StudentResult<=98endelsebegin --没有，整体+5 update Result set StudentResult+=5 where SubjectId= (select SubjectId from Subjectwhere SubjectName='oop' ) and ExamDate= (select MAX(ExamDate) from Resultwhere SubjectId=( select SubjectId from Subject where SubjectName='oop') ) end------------------------------------------------------------------------------------------------分页：双Top 双Order by 每页显示3条记录,我想要第二页数据 4-6条select top 3 * from Studentwhere StudentNo not in( select top 0 StudentNo from Student)---------------------------------------------------------------------------------------------- 上机练习：--------------------------------------上机练习一：--查询 最近一次 参加‘opp’ 考试的 最高分 and 最低分select max([StudentResult])as 最高分,min([StudentResult]) AS 最低分from result where examdate=( select max(examdate) from result where Subjectid=( select SubjectId from subject where Subjectname='oop'))and Subjectid=( select SubjectId from subject where Subjectname='oop')-------------------------------------------------------------------------上机练习二：--使用 in 关键字 查询S1 学期开设的课程select SubjectName from subject where gradeid in( select gradeid from grade where Gradeid='1')--------------------------------------------------------------------------上机练习三：--查询某课程 最近一次考试 缺考的 学生名单select StudentNo,studentname from student where StudentNo not in( select StudentNo from result where [SubjectId]= ( select [SubjectId] from subject where SubjectName='oop' ) and ExamDate= ( select max(ExamDate) from result where SubjectId=( select [SubjectId] from subject where SubjectName='oop') )) ---------------------------------------------------------------------------上机练习四：--如果有 S1 的学生 就将 年级更新为 S2 if exists (select * from studentwhere gradeid in ( select gradeid from student where gradeid='1' ) ) begin update student set Gradeid='2' where Gradeid in ( select gradeid from student where gradeid='1' ) end Go-----------------------------------------------------------------------------
DECLARE @subjectName varchar(50)DECLARE @date datetime --最近考试时间DECLARE @subjectNo int --科目编号SET @subjectName='java logic'SELECT @date=max(ExamDate) FROM Result INNER JOIN SubjectON Result.SubjectNo=Subject.SubjectNoWHERE SubjectName= @subjectNameSELECT @subjectNo=subjectNo FROM Subject WHERE SubjectName= @subjectName/*--------------统计考试缺考情况--------------*/SELECT 应到人数=( SELECT COUNT(*) FROM Student INNER JOIN Subject ON Subject.GradeId=Student.GradeId WHERE SubjectName= @subjectName ) , 实到人数=( SELECT COUNT(*) FROM Result WHERE ExamDate=@date AND SubjectNo=@subjectNo ), 缺考人数=( SELECT COUNT(*) FROM Student INNER JOIN Subject ON Subject.GradeId=Student.GradeId WHERE SubjectName= @subjectName ) - ( SELECT COUNT(*) FROM Result WHERE ExamDate=@date AND SubjectNo=@subjectNo ) /*---------统计考试通过情况，并将统计结果存放在新表TempResult中---------*/IF EXISTS(SELECT * FROM sysobjects WHERE name='TempResult') DROP TABLE TempResultSELECT Student.StudentName,Student.StudentNo,StudentResult, IsPass=CASE WHEN StudentResult>=60 THEN 1 ELSE 0 END INTO TempResult FROM Student LEFT JOIN ( SELECT * FROM Result WHERE ExamDate=@date AND SubjectNo=@subjectNo ) RON Student.StudentNo=R.StudentNoWHERE GradeId=(SELECT GradeId FROM Subject WHERE SubjectName= @subjectName) --SELECT * FROM TempResult --查看统计结果，可用于调试/*-------酌情加分-------*/DECLARE @avg numeric(4,1) --定义变量存放平均分SELECT @avg=AVG(StudentResult) FROM TempResult WHERE StudentResult IS NOT NULLIF (@avg<60) --判断平均分是否低于60分。如果低于60分，设置平均分为60分 SET @avg=60WHILE (1=1) --循环加分，最高分不能超过97分BEGIN IF(NOT Exists(SELECT * FROM TempResult WHERE StudentResult<@avg)) BREAK ELSE UPDATE TempResult SET StudentResult=StudentResult+1 WHERE StudentResult<@avg AND StudentResult<97END --因为提分，所以需要更新IsPass（是否通过）列的数据UPDATE TempResult SET IsPass=CASE WHEN StudentResult>=60 THEN 1 ELSE 0 END--SELECT * FROM newTable--查看更新IsPass列后的成绩和通过情况，可用于调试/*--------------显示考试最终通过情况--------------*/SELECT 姓名=StudentName,学号=StudentNo, 成绩=CASE WHEN StudentResult IS NULL THEN '缺考' ELSE CONVERT(varchar(5),StudentResult) END, 是否通过=CASE WHEN isPass=1 THEN '是' ELSE '否' END FROM TempResult /*--显示通过率及通过人数--*/ SELECT 总人数=COUNT(*) ,通过人数=SUM(IsPass), 通过率=(CONVERT(varchar(5),AVG(IsPass*100))+'%') FROM TempResult GO
------------------------------------------------------------------------------------------关键字 " in " 返回多条记录select StudentName from student where LoginPwd in(select LoginPwd from student where LoginPwd='111111')--关键字 " = " 返回单条记录select StudentName from student where LoginPwd =(select LoginPwd from student where LoginPwd='123456')-------------------------------------------------------------------------------查询 最近一次 参加‘opp’ 考试的 最高分 and 最低分select max([StudentResult])as 最高分,min([StudentResult]) AS 最低分from result where examdate=( select max(examdate) from result where Subjectid=( select SubjectId from subject where Subjectname='oop'))and Subjectid=( select SubjectId from subject where Subjectname='oop') ------------------------------------------------------------------- --查询参加 ‘oop’课程 最近一次 考试的在读 学生名单（ 姓名 学号） select studentName ,studentNo from student where studentno in ( select StudentNo from result where SubjectId= ( select SubjectId from subject where SubjectName='oop' ) and ExamDate= ( select max(examdate) from result where SubjectId= ( select SubjectId from subject where SubjectName='oop' ) ) )-----------------------------------------------------------------------------------案例：检查“oop”课程最近一次考试。--如果有分以上的成绩，则每人提分；--否则，每人提分。最终的成绩不得大于分if exists( select studentresult from Result where SubjectId= (select SubjectId from Subjectwhere SubjectName='oop' ) and ExamDate= (select MAX(ExamDate) from Resultwhere SubjectId=( select SubjectId from Subject where SubjectName='oop') ) and StudentResult>80)begin --有，每人提分 99 update Result set StudentResult=100 where SubjectId= (select SubjectId from Subjectwhere SubjectName='oop' ) and ExamDate= (select MAX(ExamDate) from Resultwhere SubjectId=( select SubjectId from Subject where SubjectName='oop') ) and StudentResult>98 update Result set StudentResult+=2 where SubjectId= (select SubjectId from Subjectwhere SubjectName='oop' ) and ExamDate= (select MAX(ExamDate) from Resultwhere SubjectId=( select SubjectId from Subject where SubjectName='oop') ) and StudentResult<=98endelsebegin --没有，整体+5 update Result set StudentResult+=5 where SubjectId= (select SubjectId from Subjectwhere SubjectName='oop' ) and ExamDate= (select MAX(ExamDate) from Resultwhere SubjectId=( select SubjectId from Subject where SubjectName='oop') ) end------------------------------------------------------------------------------------------------分页：双Top 双Order by 每页显示3条记录,我想要第二页数据 4-6条select top 3 * from Student where StudentNo not in( select top 3 StudentNo from Student)--01.分页的第二种方式： row_number() over(order by xxx) 分页select * from ( select *,row_number() over(order by studentno) as myid from Student)as tempwhere myid between 4 and 6--------------------------------------------------------------------------------------------- --02.go关键字2select * from student gocreate table s2226goodperson( sid int identity(1,1) primary key not null, sname nvarchar(32))go--DDL(Data Definition Language) 数据定义语言 建库 ，建表，建约束 go必须另起一行--DML(Data Manipulation Language) 数据操作语言 Update，insert 。delete--DQL(Data query language)数据查询语言 select -----------------------------------------------------------------------------------------------03.第三范式--如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF） -----------------------------------------------------------------------------------------------04.convert(参数1，参数2，参数3) 针对日期类型设定格式--SQL 获取系统时间select GETDATE()select CONVERT(nvarchar(32),GETDATE(),21)
0 0
