实验五 SQL函数与T-SQL编程

实验五 SQL函数与T-SQL编程

1、集合函数

--统计选修了课程的学生人数 SELECT COUNT(DISTINCT SNo) 选修了课程的学生人数 FROM dbo.SC; --用GROUP BY统计不同职称的教师人数 SELECT Prof 职称,COUNT(*) 教师人数 FROM dbo.Teacher GROUP BY Prof; --COMPUTE BY统计不同职称的教师人数 SELECT TNo,Prof FROM dbo.Teacher ORDER BY Prof COMPUTE COUNT(TNo) BY Prof;

2、数据类型转换函数

--从tblTeacher表中查询教师的电话号码中的最后8位数字能被9整除的教师信息 SELECT * FROM dbo.Teacher WHERE CONVERT(INT,RIGHT(Mobile,8)) %9=0 --查询学生的学号、姓名、出生日期。要求将出生日期转化为整数后显示 SELECT SNo,SName,CONVERT(INT,BirthDate)出生日期的内部存储形式 FROM dbo.Student SELECT SNo,SName,BirthDate FROM dbo.Student

3、日期函数

--把当前日期转换为样式 101 SELECT CONVERT(CHAR(10),GETDATE(),101) AS 当前日期 --计算所有学生的平均年龄 SELECT AVG(DATEDIFF(yy,BirthDate,GETDATE())) FROM dbo.Student; --查询最近两年选修课程的课程号 SELECT DISTINCT SNo FROM dbo.SC WHERE DATEDIFF(yy,SDate,GETDATE())>=12; --分别获取数据库当前时间的年、月、日、小时 SELECT YEAR(GETDATE()) 年,MONTH(GETDATE())月,DAY(GETDATE())日,DATEPART(hh,GETDATE())时 SELECT GETDATE()

4、数学函数

--以2为随机数种子产生一随机数 SELECT RAND (2) 随机数 --对数字1223.75进行整数的四舍五入和取整 SELECT ROUND(1223.75,0) 四舍五入,FLOOR(1223.75) 取整

5、字符串函数

--求在字符串‘They call me the hunter'中从5个字符开始，"the"出现的位置 SELECT CHARINDEX('the','They call me the hunter',5) '"the"出现的位置' --返回Teacher表中的所有老师的姓氏（不重复） SELECT DISTINCT SUBSTRING(TName,1,1) 教师姓氏 FROM dbo.Teacher ; --用 ZZZ 替换 abcdefghi 中的字符串 cde SELECT REPLACE('abcdefghi','cde','zzz') --组合字符串‘hello‘和‘join‘，中间加入一空格 SELECT 'hello'+' '+'join' go

6、系统函数

--系统函数可以在不直接访问系统表的情况下，获取SQL SERVER系统表中的信息。 --请输出系统信息，并用字符串函数将其连接起来：当前数据库ID是： 当前数据库名： 当前主机ID： 当前主机名： PRINT '当前数据库ID：'+RTRIM(CONVERT(CHAR(10) ,DB_ID()))+ CHAR(10) +'当前数据库名：'+RTRIM(CONVERT(CHAR(10), DB_NAME())) + CHAR(10)+'当前主机ID: '+RTRIM(CONVERT(CHAR(10) ,HOST_ID())) + CHAR(10)+'当前主机名：'+RTRIM(CONVERT(CHAR(10),HOST_NAME())) /* SQL的换行 制表符 CHAR(9) 换行符 CHAR(10) 回车 CHAR(13) */ PRINT 'Test'+CHAR(13)+'Name' PRINT 'Test'+CHAR(10)+'Age' PRINT 'Test'+CHAR(9)+'Tel' --以文本格式显示结果 SELECT 'AAA'+ CHAR(10)+'BBB'--AAA BBB SELECT 'AAA'+ CHAR(13)+'BBB'--AAA BBB SELECT 'AAA' + CHAR(10) + CHAR(13) + 'BBB'--AAA BBB go

7、用户自定义函数

--创建标量函数myfunction1，从SC表中查询指定学生（由输入的学号确定）的平均成绩。 --自定义函数名，参数 CREATE FUNCTION dbo.myfunction1(@SNo varchar(20)) --返回值类型 RETURNS NUMERIC(4,1) BEGIN --声明返回值 DECLARE @aveScore NUMERIC(4,1) -- 通过查询给返回值赋值 SELECT @aveScore=AVG(score) FROM dbo.SC GROUP BY SNo HAVING SNo=@SNo -- return返回值 RETURN @aveScore END go --标量值函数的调用 select dbo.myfunction1('108') as 平均分 go --创建内嵌表值函数myfunction2，从student和sc表中查询指定年份出生的学生的学号、姓名、年龄平均成绩。 CREATE FUNCTION myfunction2(@byear INT) RETURNS TABLE AS --查询返回结果集的SQL语句 RETURN( SELECT dbo.SC.SNo,SName, datediff(yy,birthdate,getdate()) age,Score FROM dbo.SC,dbo.Student WHERE dbo.SC.SNo=dbo.Student.SNo AND YEAR(BirthDate)=@byear ) go SELECT DISTINCT* FROM dbo.myfunction2('1980') go --删除函数myfunction1 DROP FUNCTION dbo.myfunction2 --用流控制语言统计tblSelCourse表中的成绩为A、B、C、D各个层次的学生数。假如规定：A--[90，100} B--[80，89] C[60，80] D[0，60] DECLARE @a INT,@b INT,@c INT ,@d INT SET @a=0 SET @b=0 SET @c=0 SET @d=0 DECLARE @score DECIMAL(4,1) --定义游标cursor1 DECLARE SC_Cursor CURSOR FOR --使用游标的对象 SELECT score FROM SC --打开游标 OPEN SC_Cursor --将将游标向下移1行,获取的数据放入之前定义的变量@score中 FETCH NEXT FROM SC_Cursor into @score --判断是否成功获取数据 WHILE @@FETCH_STATUS = 0 BEGIN IF @score<60 SET @d=@d+1 IF @score>=60 AND @score<80 SET @c=@c+1 IF @score>=80 AND @score<90 SET @b=@b+1 IF @score>=90 SET @a=@a+1 --将游标向下移1行,获取的数据放入之前定义的变量@score中 FETCH NEXT FROM SC_Cursor into @score END PRINT @a PRINT @b PRINT @c PRINT @d --关闭游标 CLOSE SC_Cursor --删除游标 DEALLOCATE SC_Cursor go --游标一般格式： --DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ... --OPEN 游标名称 --FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,... --WHILE @@FETCH_STATUS=0 -- BEGIN -- SQL语句执行过程... ... -- FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,... -- END --CLOSE 游标名称 --DEALLOCATE 游标名称 (删除游标)