添加约束及打印五角星及查询

最新推荐文章于 2024-09-06 22:33:47 发布

转载最新推荐文章于 2024-09-06 22:33:47 发布 · 61 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/WuXuanKun/p/5361223.html

本文详细介绍了SQL中主键、唯一、默认、检查、外键约束的应用，并通过实例展示了如何利用SQL进行批量更新操作，针对特定条件进行成绩提升。

  1 USE MySchool
  2 --添加主键约束
  3 alter table Student 
  4 add constraint PK_StudentNo primary key (StudentNo)
  5 
  6 
  7 --添加唯一约束
  8 alter table Student
  9 add constraint UQ_IdentityCard unique (IdentityCard)
 10 
 11 
 12 --添加默认约束
 13 alter table Student
 14 add constraint DF_Address default('地址不详') for Address
 15 
 16 --添加检查约束
 17 alter table Student
 18 add constraint CK_BornDate check (BornDate>='1980-01-01')
 19 
 20 
 21 --添加外键约束
 22 alter table Student
 23 add constraint FK_StudentNO foreign key(StudentNo) references Student(StudentNo)
 24 
 25 declare @x int ,@a int,@z varchar(100),@y nvarchar
 26 set @x =0
 27 set @a=5
 28 set @y='★'
 29 set @z=''
 30 while @x<5
 31 begin
 32 set @x=@x+1
 33 set @a=@a-1
 34 set @z=REPLICATE (@y,@x)+REPLICATE('',@a)
 35 print @z
 36 end
 37 
 38 
 39 --求符合条件的人数
 40 --if me ,I'll do this 
 41 --01.定义一个int类型的变量，保存课程名称为"oop"对应的课程编号
 42 --declare @subid int
 43 --select @subid=subjectid
 44 --from Subject
 45 --where SubjectName='oop'
 46 --print @subid
 47 
 48 
 49 
 50 declare @subid int 
 51 select @subid =subjectid
 52 from Subject
 53 where SubjectName='oop'
 54 declare @Maxdate datetime
 55 select @Maxdate=MAX(examdate)
 56 from Result
 57 where SubjectId=@subid
 58 declare @totaCount int
 59 select @totaCount=COUNT (*)
 60 from Result
 61 where SubjectId=@subid
 62 and ExamDate =@Maxdate
 63 and StudentResult<80
 64 
 65 
 66 
 67 select @totalCount=COUNT(*) 
 68 from Result
 69 where SubjectId=@subid
 70 and ExamDate=@Maxdate
 71 and StudentResult<80
 72 
 73 
 74 
 75 
 76 
 77 
 78 --02.第二个限定条件   最近一次  携带  课程编号
 79 --max() min() sum() count() avg()
 80 --定义一个Datetime类型的变量，保存最近一次考试时间
 81 declare @Maxdate datetime
 82 select @Maxdate=MAX(examdate) 
 83 from Result
 84 where SubjectId=@subid
 85 
 86 --select * from Result
 87 
 88 --easy  总人数
 89 --03.定义一个保存总人数的变量
 90 declare @totalCount int
 91 
 92 
 93 
 94 select @totalCount=COUNT(*) 
 95 from Result
 96 where SubjectId=@subid
 97 and ExamDate=@Maxdate
 98 and StudentResult<80
 99 
100 --56   
101 --判定人数>0
102 
103 --循环
104 while(@totalCount>0)
105 begin
106    --有不及格的，提分+2  高于95，不提
107    update Result set StudentResult=StudentResult+2
108    where  SubjectId=@subid
109     and ExamDate=@Maxdate
110     and StudentResult<95
111     
112     select @totalCount=COUNT(*) 
113 from Result
114 where SubjectId=@subid
115 and ExamDate=@Maxdate
116 and StudentResult<80
117     
118     
119 
120 end
121 
122 
123 
124 
125 
126 
127 
128 
129 
130 
131 
132 --代码
133 
134 declare @subid int
135 select @subid =subjectID from Subject
136 where SubjectName='oop'
137 declare @examDate datetime
138 select @examDate=MAX(examdate)from
139 Result where SubjectId=@subid
140 
141 declare @num int
142 select @num=COUNT(*)from Result
143 where SubjectId =@subid 
144 and ExamDate=@examDate
145 and StudentResult<60
146 while (@num>0)
147 begin
148 update Result
149 set StudentResult+=2
150 where SubjectId=@subid and ExamDate=@examDate
151 and StudentResult<95
152 select @num=count(*)from Result
153 where subjectid =@subid 
154 and ExamDate=@examDate 
155 and studentResult<60
156 end
157 
158 
159 --查询学生姓名和成绩
160 declare @subid int
161 select @subid=subjectid
162 from Subject
163 where SubjectName='oop'
164 declare @Maxdate datetime
165 select @Maxdate=MAX(examdate) 
166 from Result
167 where SubjectId=@subid
168 case
169 where SubjectResult>=90 then  'A'
170 where SubjectResult>=80 then  'B'
171 where SubjectResult>=70 then  'C'
172 where SubjectResult>=60 then  'D'
173 else 'E'
174 
175 
176 end
177 from Result ,subject
178 where result.subjectId=subject.subjectId
179 and subjectName
180

转载于:https://www.cnblogs.com/WuXuanKun/p/5361223.html