DECLARE @s int,@i int SET @i=1 SET @s=0 WHILE @i<=100 BEGIN
SET @s=@s+@i SET @i=@i+1 END PRINT @s
【T6-12】输出九九乘法表。
DECLARE @i smallint,@j smallint,@str varchar(100) PRINT ' 九九乘法表' set @i=1 while @i<=9 begin
select @j=1,@str='' while @j<=@i
select @str=@str+cast(@j as char(1))+'*'+cast(@i as char(1))+'='+cast(@i*@j as char(2))+space(2),@j=@j+1 print @str set @i=@i+1 end
或
DECLARE @i smallint,@j smallint,@str varchar(100) PRINT ' 九九乘法表' set @i=1 while @i<=9 begin SET @j=1 SET @str='' while @j<=@i BEGIN
SET @str=@str+cast(@j as char(1))+'*'+cast(@i as char(1))+'='+cast(@i*@j as char(2))+space(2) SET @j=@j+1 END print @str set @i=@i+1 end
三、自定义函数的使用
【T6-13】创建一个内联表值函数fun1，返回某个系学生的信息。
CREATE FUNCTION fun1(@xih CHAR(4)) RETURNS TABLE AS
22
RETURN
(SELECT * FROM Student WHERE Dno=@xih)
【T6-14】调用内联表函数fun1，查询“计算机系”（系编号DX01）学生的信息。
SELECT * FROM fun1('DX01') 【注】 --T6-13
CREATE FUNCTION fun1(@xb CHAR(20)) RETURNS TABLE AS RETURN
( SELECT * FROM Student WHERE Dno IN
( SELECT Dno FROM Department Where Dname=@xb ) ) --T6-14
SELECT * FROM fun1('计算机系')
【T6-15】定义一个函数fun2，当给定一门课程名称时，计算选修该门课程的学生人数。
CREATE FUNCTION fun2(@Cname varchar(20)) RETURNS int AS BEGIN
DECLARE @counts int SELECT @Counts=
(SELECT Count(*) FROM SC WHERE
Cno=(SELECT Cno FROM Course WHERE Cname=@Cname)) RETURN @CountS END
【T6-16】编写一端T-SQL脚本程序调用上述函数。当向SC表插入一条记录时，首先调用函数fun2，检索选修该门课程的学生人数，若学生人数小于50人，则该记录插入到SC表，允许学生选修该门课程，否则不执行插入动作。
DECLARE @kch CHAR(3),@kcm VARCHAR(20) DECLARE @rs int
SET @kcm='信息系统'
SELECT @kch=Cno FROM Course WHERE Cname=@kcm SELECT @rs=dbo.fun2(@kcm) IF @rs<50
INSERT into SC Values('2012394101',@kch,85) Go
23
实验七 视图与索引
一、实验目的和要求
1．掌握SQL视图建立、修改和删除。 2．掌握SQL视图查询。 3．掌握索引的建立和删除方法。
二、实验内容 1 建立视图
【SY7-01】建立计算机系（系号都下DX01）学生的视图ST1。
CREATE VIEW ST1 AS SELECT * FROM Student WHERE Dno='DX01'
【SY7-02】建立计算机系（系号DX01）学生的视图ST2，并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生。
CREATE VIEW ST2 AS SELECT * FROM Student WHERE Dno='DX01' WITH CHECK OPTION
【题SY7-03】建立计算机系选修了1号课程的学生的视图ST3。
CREATE VIEW ST3(Sno,Sname,Grade) AS
SELECT Student.Sno,Sname,Grade FROM Student,SC
WHERE Dno='DX01' AND Student.Sno=SC.Sno AND SC.Cno='1'
【题SY-04】将学生的学号及他的平均成绩定义为一个视图ST4。
CREATE VIEW ST4(Sno,Gavg) AS
SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno
【SY7-05】定义一个反映学生年龄的视图ST5。
CREATE VIEW ST5(Sno,Sname,Sage) AS
SELECT Sno,Sname,YEAR(GETDATE())-YEAR(Sbirth) FROM Student
【SY7-06】建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图ST6。
CREATE VIEW ST6 AS
SELECT Student.Sno,Sname,Cname,Grade
24
FROM Student,SC,Course
WHERE Student.Sno= SC.Sno AND SC.Cno=Course.Cno
【SY7-07】建立学生的学号(Sno)、姓名(Sname)、系名（Dname）、选修课程名(Cname)及成绩(Grade)的视图ST7。
CREATE VIEW ST7 AS
SELECT Student.Sno,Sname,Dname,Cname,Grade FROM Student,Department,SC,Course
WHERE Student.Dno=Department.Dno AND Student.Sno= SC.Sno AND SC.Cno=Course.Cno
2 删除视图
【SY7-08】删除视图ST4。 DROP VIEW ST4
3查询视图
【SY7-09】在计算机系的学生视图ST1中找出男学生的信息。
SELECT * FROM ST1
WHERE Ssex='男'
说明：本例转换后的查询语句为：
SELECT * FROM Student
WHERE Dno='DX01' AND Ssex='男'
【SY7-10】查询计算机系选修了1号课程的学生。
SELECT * FROM ST1,SC
WHERE ST1.Sno=SC.Sno AND SC.Cno='1' 或
SELECT *
FROM Student,SC
WHERE Student.Dno='DX01' AND Student.Sno=SC.Sno AND SC.Cno='1'
【SY7-11】在ST6视图中查询成绩在85分以上的学生信息。
SELECT * FROM ST6 WHERE Grade>85
4更新视图
【SY7-12】将计算机系(DX01)学生视图ST1中学号为2012374120的学生姓名改为“Alice”。
UPDATE ST1 SET Sname='Alice' WHERE Sno='2012374120'
【SY7-13】向计算机系（DX01）学生视图ST1中插入一个新的学生记录，其中学号为“2012314301”，
25
姓名为“Tom”。
INSERT
INTO ST1(Sno,Sname,Dno)
VALUES('2012314301','Tom','DX01')
【SY7-14】删除计算机系学生视图ST1中学号为“2012314301”的记录。
DELETE
FROM ST1
WHERE Sno='2012314301'
5索引
【SY7-15】为Student表创建一个名为SY1的索引，以所在系号排序。
【SY7-16】为Department表创建一个名为SY2的聚集索引，以Dname降序排序。 【SY7-17】查看Student表的索引。 【SY7-18】删除SY1索引。
26