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
