CREATE TABLE STUDENT
(
SID INT PRIMARY KEY IDENTITY,
SNAME NVARCHAR(50),
SAGE DATETIME,
SSEX CHAR(4)
)
CREATE TABLE TEACHER
(
TID INT PRIMARY KEY IDENTITY,
TNAME NVARCHAR(50)
)
CREATE TABLE COURSE
(
CID INT PRIMARY KEY IDENTITY,
CNAME NVARCHAR(50),
TID INT FOREIGN KEY REFERENCES TEACHER(TID)
)
CREATE TABLE SCORE
(
ID INT PRIMARY KEY IDENTITY,
SID INT FOREIGN KEY REFERENCES STUDENT(SID),
CID INT FOREIGN KEY REFERENCES COURSE(CID),
SCORES INT
)
–创建存储过程,为学生表插入数据
GO
CREATE PROC P_INSERTSTU
@SNAME NVARCHAR(50),
@SAGE DATETIME,
@SSEX CHAR(4)
AS
BEGIN
INSERT INTO STUDENT VALUES(@SNAME,@SAGE,@Ssex)
END
–创建触发器,删除学生信息,删除后打印出‘警告:XX同学已被删除,请知晓!’
GO
CREATE TRIGGER T_DELETESTU
ON STUDENT
AFTER DELETE
AS
BEGIN
DECLARE @STUNAME NVARCHAR(50)
DELETE STUDENT WHERE SID IN (SELECT SID FROM DELETED)
SELECT @STUNAME =SNAME FROM DELETED
PRINT '警告:'+@STUNAME+'同学已被删除,请知晓!'
END
–创建触发器,修改学生信息,修改后打印出‘警告:XX同学已被修改为XX,请知晓!’
GO
CREATE TRIGGER T_UPDATESTU
ON STUDENT
AFTER UPDATE
AS
BEGIN
DECLARE @STUNAMEUB NVARCHAR(50)
DECLARE @STUNAMEUA NVARCHAR(50)
SELECT @STUNAMEUA =SNAME FROM INSERTED
SELECT @STUNAMEUB = SNAME FROM DELETED
PRINT '警告:'+@STUNAMEUB+'同学已被修改为'+@STUNAMEUA+',请知晓!'
END
–创建一个标值函数,参数是SCORES 返回是为NVARCHAR(50)类型,在函数中使用CASE WHEN THEN END 为成绩划分等级>60 ‘不及格’ >70 ‘及格’>80‘中等’>100‘优秀’=100‘满意’
GO
CREATE FUNCTION FUN_RANK(@SCORES INT)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @RESTUCH NVARCHAR(50)
SELECT @RESTUCH =
(CASE WHEN @SCORES < 60 THEN '不及格',
WHEN @SCORES < 70 THEN '及格',
WHEN @SCORES < 80 THEN '中等,
WHEN @SCORES < 100 THEN '优秀',
WHEN @SCORES = 100 THEN '满意,
)
RETURN @RESTUCH
END
–创建一个视图,显示学生编号,学生姓名,课程名称,任课老师,以及调用标值函数为成绩划分等级
GO
CREATE VIEW V_STUDENT
AS
SELECT S.SID,S.SNAME,C.CNAME,T.TNAME,R.SCORES,DBO.FUN_RANK(R.SCORES) AS RANKS FROM STUDENT S JOIN SCORE R ON S.SID= R.SID JOIN COURSE C ON R.CID= C.CID JOIN TEACHER T ON T.TID = C.TID
GO
–使用行转列对成绩表重新划分
SELECT
SID,
MAX(CASE WHEN CID = 1 THEN SCORES ELSE NULL END) '语文',
MAX(CASE WHEN CID = 2 THEN SCORES ELSE NULL END) '数学',
MAX(CASE WHEN CID = 3 THEN SCORES ELSE NULL END) '英语',
FROM SCORE GROUP BY SID
–创建一个存储过程对成绩分页 ,输出总记录数
GO
CREATE PROC P_PAGESTU
@STUNAME NVARCHAR(20),
@PAGEINDEX INT,
@PAGESIZE INT,
@PAGECOUNT INT OUTPUT
AS
BEGIN
SELECT * FROM (
SELECT R.SCORES,ROW_NUMBER() OVER(ORDER BY S.SID) AS NUM FROM STUDENT S JOIN SCORE R ON S.SID = R.SID WHERE S.SNAME LIKE '%'+@STUNAME+'%') TEMP WHERE TEMP.NUM BETWEEN ((@PAGEINDEX-1)*@PAGESIZE+1) AND (@PAGEINDEX*@PAGESIZE)
SELECT @PAGECOUNT=COUNT(S.SID) FROM STUDENT S JOIN SCORE R ON S.SID = R.SID WHERE S.SNAME LIKE '%'+@STUNAME+'%'
)
END
–创建一个游标,正确读取学生的姓名即成绩
DECLARE CURSOR_SCORE CURSOR SCROLL
FOR SELECT S.SNAME,R.SCORE FROM STUDENT S JOIN SCORE R ON S.SID= R.SID
OPEN CURSOR_SCORE
DECLARE @STUNAME NVARCHAR(20)
DECLARE @STUSCOR INT
FETCH FIRST FROM CURSOR_SCORE INTO @STUNAME,@STUCORE
WHILE @@FETCH_STATUS =0
BEGIN
SELECT @STUNAME=S.SNAME,@STUSCORE = R.SCORES FROM STUDENT S JOIN SCORE R ON S.SID = R.SID
FETCH NEXT FROM CURSOR_SCORE INTO @STUNAME,@STUSCORE
END
PRINT @STUNAME +成绩+CONVERT(NVARCHAR(20),@STUSCORE)
CLOSE CURSOR_SCORE
DEALLOCATE CURSOR_SCORE