SQL server 13编程基础(2) 函数

IF EXISTS (SELECT name FROM SYS.DATABASES WHERE name='学生选课')
    DROP DATABASE 学生选课
GO
CREATE DATABASE 学生选课
GO
USE 学生选课
-- DROP TABLE SC,course,student
-- DROP TABLE course
GO

--创建教师表
CREATE TABLE Teacher(
Tno CHAR(3) NOT NULL PRIMARY KEY,
Tname VARCHAR(10) NOT NULL,
Tsex CHAR(2),
Sbirthday DATE,
Prof VARCHAR(6),
Tdept VARCHAR(10)
)
GO

--创建学生表
CREATE TABLE Student(
Sno CHAR(5) NOT NULL PRIMARY KEY,
Sname VARCHAR(10) NOT NULL,
Ssex CHAR(2),
Sage TINYINT,
Sdept VARCHAR(10)
)
GO

--创建课程表
CREATE TABLE Course(
Cno CHAR(3) NOT NULL PRIMARY KEY,
Cname VARCHAR(20) NOT NULL,
Cpno CHAR(3),
Credit TINYINT,
Semester TINYINT,
Tno CHAR(3)
)
GO

--创建选课表
CREATE TABLE SC(
Sno CHAR(5) NOT NULL,
Cno CHAR(3) NOT NULL,
Grade INT
)
GO

-- 插入教师表数据
-- SP_HELP Teacher
INSERT INTO Teacher 
VALUES ('104','李诚','男','1958/12/2','副教授','计算机系')
,('256','张旭','男','1969/3/12','讲师','数学系')
,('125','王萍','女','1972/5/5','助教','计算机系')
,('331','刘冰','女','1977/8/14','助教','信息系')
GO    
-- SELECT * FROM Teacher

-- 插入学生表数据
-- SP_HELP Student
INSERT INTO Student 
VALUES('95001','刘超华','男',22,'计算机系')
,('95002','刘晨','女',21,'信息系')
,('95003','王敏','女',20,'数学系')
,('95004','张海','男',23,'数学系')
,('95005','陈平','男',21,'数学系')
,('95006','陈斌斌','男',28,'数学系')
,('95007','刘德虎','男',24,'数学系')
,('95008','刘宝祥','男',22,'计算机系')
,('95009','吕翠花','女',26,'计算机系')
,('95010','马盛','男',23,'数学系')
,('95011','吴霞','男',22,'计算机系')
,('95012','马伟','男',22,'数学系')
,('95013','陈冬','男',18,'信息系')
,('95014','李小鹏','男',22,'计算机系')
,('95015','王娜','女',23,'信息系')
,('95016','胡萌','女',23,'计算机系')
,('95017','徐晓兰','女',21,'计算机系')
,('95018','牛川','男',22,'信息系')
,('95019','孙晓慧','女',23,'信息系')
GO
-- SELECT * FROM Student

-- 插入课程表数据
-- SP_HELP Course
INSERT INTO Course 
VALUES('101','数据库','105',5,4,'125')
,('104','操作系统','106',1,2,'104')
,('105','数据结构','107',1,3,'104')
,('106','数据处理',NULL,1,2,'331')
,('107','C语言','106',3,1,'125')
,('108','Java',NULL,3,3,'256')
,('109','网页制作',NULL,2,5,'125')
,('110','c++',NULL,3,4,'104')
,('111','网络编程',NULL,2,5,'104')
,('201','高等数学',NULL,1,1,'256')
,('301','信息系统','101',1,3,'331')
GO
-- SELECT * FROM Course WHERE Cpno IS NULL

-- 插入选课表数据
--SP_HELP SC
INSERT INTO SC 
VALUES('95001','101',87)
,('95001','201',76)
,('95001','301',79)
,('95001','104',80)
,('95001','105',81)
,('95001','106',82)
,('95001','107',67)
,('95002','101',89)
,('95002','201',81)
,('95004','101',83)
,('95004','201',56)
,('95005','101',89)
,('95006','101',54)
,('95006','201',77)
,('95010','101',56)
,('95013','301',80)
,('95013','105',90)
,('95014','201',NULL)
,('95015','201',NULL)
GO
--SELECT * FROM SC
--13.1 利用系统函数，解决以下问题（使用SELECT显示结果）。 
--（1）求不大于-1.23的整数。
select FLOOR(-1.23)
-- （2）生成一个14~18之间的随机整数。
select FLOOR(rand()*5)+14
--（3）在课程表中，输出课程名中包含
-- “C” 字符的“课程编号”、“课程名称”、“课名字符数”和“课名字节数”。
select Cno 课程编号,Cname 课程名称,LEN(Cname) 课名字符数,DATALENGTH(Cname) 课名字节数
from Course where Cname LIKE '%C%'

--找到字符串'bar'在字符串 'foobarbar'中第一次出现的位置。
select charindex('bar','foobarbar')

-- （5）找到字符串'foobarbar'最右边的五个字符。
select RIGHT('foobarbar',5)

-- （6）用 'sqlserver'替换字符串'www.mysql.com'中的'mysql'。
select REPLACE('www.mysql.com','mysql','sqlserver')

--（7）获取当前日期的月份数值。
select MONTH(GETDATE())

--（8）获取100天后的日期和时间。

SELECT DATEADD(DY,100,GETDATE())
SELECT DATEADD(Y,100,GETDATE())
SELECT DATEADD(D,100,GETDATE())
SELECT DATEADD(DD,100,GETDATE())

--（9）显示当前数据库的名称。
select DB_NAME() 

 --13.2 用户自定义函数。
--（1）创建标量值函数 F_sno_count，根据输入的学生学号，返回该学生的"选课门数"（如果使用中间变量请使用@count）。
-- 调用函数 F_sno_count，输出'95001'的"选课门数"（使用SELECT输出）。
-- ① 创建标量值函数 F_sno_count。

create function F_sno_count(@Sno CHAR(5))
returns int
as
begin
declare @count int
select @count=COUNT(*) from SC where Sno=@Sno
return @count
end    
-- ② 调用函数 F_sno_count，输出'95001'的"选课门数"（使用SELECT输出）。
 SELECT dbo.F_sno_count('95001')  '选课门数' 

--（2）创建标量值函数 F_sname_cname_grade，根据输入的学生姓名和课程名称，输出该学生此门课程的成绩
--（如果使用中间变量请使用@grade）。调用函数 F_sname_cname_grade，输出学生'刘超华'的'数据库'课程的成绩。
-- ① 创建标量值函数 F_sname_cname_grade。 

create function F_sname_cname_grade(@Sname VARCHAR(10),@Cname VARCHAR(20))
returns int
as
begin
declare @grade int
select @grade=grade from SC where Sno=(select Sno from Student where Sname=@Sname) and
 Cno=(select Cno from Course where Cname=@Cname)
 return @grade
end

CREATE FUNCTION F_sname_cname_grade (@sname varchar(10),@cname varchar(20)) 
RETURNS INT 
AS
BEGIN 
RETURN( SELECT SC.Grade FROM Student S JOIN SC ON S.Sno=SC.Sno 
JOIN course C ON SC.Cno=C.Cno WHERE S.Sname=@sname AND C.Cname=@cname ) 
END 

-- ② 调用函数 F_sname_cname_grade，输出学生'刘超华'的'数据库'课程的成绩（使用SELECT调用输出）。
select dbo.F_sname_cname_grade('刘超华','数据库')

-- （3）创建内嵌表值函数 F_sdept_stu_info，根据输入的院系名称，输出该院系学生的"学号"、"姓名"、
--"性别"、"平均成绩"。调用函数 F_sdept_stu_info，输出 '信息系' 学生的基本信息和平均成绩。
--① 创建内嵌表值函数 F_sdept_stu_info。 

create function F_sdept_stu_info(@sdept varchar(10))
returns table
as
return (select S.Sno 学号,S.Sname 姓名,S.Ssex 性别,avg(SC.Grade) 平均成绩 from Student S 
join SC on S.Sno=SC.Sno where Sdept=@sdept group by S.Sno,S.Sname,S.Ssex)

--② 调用函数 F_sdept_stu_info，输出 '信息系' 学生的基本信息和平均成绩。
select * from F_sdept_stu_info('信息系')

--（4）创建多语句表值函数 F_cno_info，根据输入的课程编号，输出该课程的"课程名称"、"选修人数"
--(课程编号列为主键；如果使用变量，请使用@cno,@cname,@count)。
--调用函数 F_cno_info，输出编号为'101'的课程信息。 
--① 创建多语句表值函数 F_cno_info。
create function F_cno_info(@cno varchar(3))
returns @course_info table(课程编号 varchar(3) primary key,课程名称 varchar(20),选修人数 int)
as
begin
insert into @course_info
select C.Cno,C.Cname,count(*) from Course C join SC on C.Cno=SC.Cno where C.Cno=@cno
group by C.Cno,C.Cname
return
end

--② 调用函数 F_cno_info，输出编号为'101'的课程信息。
select * from F_cno_info('101')

-- （5）删除函数 F_sno_count。
drop function F_sno_count
drop function F_cno_info
drop function F_sname_cname_grade
drop function F_sdept_stu_info