实验五 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 游标名称 (删除游标)