use 学生数据库;
--create view ...as 语句创建视图
--在学生数据库，创建查询女学生资料的视图，视图名字为学生_简单资料
CREATE VIEW 学生_简单资料1
AS SELECT SNO,SN,SEX FROM 学生表 WHERE 学生表.SEX='女'
DROP VIEW 学生_简单资料1;
--在学生数据库，创建查询学号，学生名，课程名，分数的视图。视图名字为
--学生分数_视图
CREATE VIEW 学生分数_视图 AS
SELECT 学生表.SNO 学号,SN 学生名,CN 课程名,GRADE 分数 FROM 学生表,课程表,学生选课表
WHERE 学生表.SNO=学生选课表.SNO AND 课程表.CNO = 学生选课表.CNO
--用别名能查，用字段SNO ,GRADE 不能查，？？？？？？？？？？？？？
SELECT 学生名 , AVG(分数) 平均成绩 FROM 学生分数_视图 GROUP BY 学生名;
--在学生数据库，创建查询课程号，课程名，每门课程的平均成绩的视图，视图名字为课程分数分组_视图
CREATE VIEW 课程分数分组_视图
AS SELECT 学生选课表.CNO 课程号,CN 课程名,AVG(GRADE)平均分数 FROM 课程表,学生选课表 WHERE 课程表.CNO=学生选课表.CNO
GROUP BY 学生选课表.CNO,CN
--在学生数据库，创建查询学生名，课程名，分数的视图,视图名字为学生分数视图_视图。
CREATE VIEW 学生分数视图_视图
AS SELECT 学生名,课程名,分数 GRADE FROM 学生分数_视图
SELECT * FROM 学生分数视图_视图 WHERE 课程名='物理';
--在学生数据库，创建查询女学生资料的视图,视图名字为女学生资料_视图
create view 女学生资料__视图
as
select 学生_简单资料.sn,学生_简单资料.sex,课程表.cn,学生选课表.grade
from 学生选课表,课程表,学生_简单资料
where 学生选课表.sno = 学生_简单资料.sno
and 学生选课表.cno = 课程表.cno
go
SELECT * FROM 女学生资料__视图 WHERE GRADE >90
--INSERT INTO ...VALUES(...)语句向视图插入一行数据
INSERT INTO 女学生资料_视图 VALUES('刘媛','女','政治',87);
--插入数据失败，只有基本表才能插入数据
--用WITH CHECK OPTION语句创建视图以保证正确插入数据
CREATE VIEW 学生_资料_男_视图
AS SELECT * FROM 学生表 WHERE SEX='男' WITH CHECK OPTION
--向学生_资料_男_视图插入一行男性学生数据
INSERT INTO 学生_资料_男_视图 VALUES('S15','吴建国','男',20,5);
SELECT * FROM 学生_资料_男_视图;
--------------------------------------------------------------------
--删除视图
DROP VIEW 学生_资料_男_视图;
SELECT * FROM 学生表 WHERE SEX='男';
DELETE FROM 学生表 WHERE SNO='S15';
-------------------------存储过程------------------------------
/*
创建存储过程
在企业管理器，单击【学生数据库】→【存储过程】→【新建存储过程】
T-SQL代码如下：CREATE PROCEDURE 存储过程_学生表_PROC AS
SELECT SN,SEX,AGE FROM 学生表
GO
*/
EXEC 存储过程_学生表_PROC;
/*
CREATE PROCEDURE 检索_姓张_课程名_PROC AS
SELECT DEPT 系,CN 课程名 FROM 学生表,学生选课表,课程表
WHERE 学生表.SNO=学生选课表.SNO AND 学生选课表.CNO=课程表.CNO
AND SN LIKE '张%'
GO
*/
EXEC 检索_姓张_课程名_PROC;
--用存储过程实现，在PUBS数据库的authors中，当给定某人的姓名，就输出此人的电话号码
USE PUBS;
CREATE PROCEDURE PROC2
@LNAME VARCHAR(40), --声明存储过程的参数变量
@PHONE VARCHAR(12) OUTPUT ---声明存储过程的参数变量，它是存储过程的输出
AS
SELECT @PHONE =PHONE --把AUTHORS表的PHONE 字段赋给变量@PHONE ,并查询
FROM AUTHORS -----PUBS数据库中的AUTHORS表
WHERE AU_LNAME=@LNAME ---当@LNAME等于AUTHORS 表的AU_LNAME字段值时
EXEC PROC2;
DECLARE @PHONE VARCHAR(12) ---声明变量
EXEC PROC2'green',@PHONE OUTPUT --给定两个参数后，执行存储过程。
--即在PUBS数据库的AUTHORS表中，当给定GREEN姓名，就输出此人的电话号码，存放在变量@PHONE中
SELECT GREENPHONE=@PHONE; ---一个输出语句，GREENPHONE是自己命名的，@PHONE是PROC2的输出
SELECT * FROM AUTHORS;
CREATE PROCEDURE 学生名_分数_PROC
@学生名 VARCHAR(6),
@课程名 VARCHAR(12),
@分数 INT OUTPUT
AS
SELECT @分数=GRADE
FROM 学生表,学生选课表,课程表
WHERE 学生表.SNO=学生选课表.SNO AND 学生选课表.CNO=课程表.CNO
AND CN=@课程名 AND SN=@学生名
USE 学生数据库
DECLARE @分数 INT
EXEC 学生名_分数_PROC '张婷','数学',@分数 OUTPUT
SELECT AAA=@分数
CREATE PROCEDURE 我的学生名_分数
@学生名 VARCHAR(6) OUTPUT,
@学生号 VARCHAR(6),
@课程名 VARCHAR(12),
@分数 INT OUTPUT
AS
SELECT @分数=GRADE,@学生名=SN
FROM 学生表,学生选课表,课程表
WHERE 学生表.SNO=学生选课表.SNO AND 学生选课表.CNO=课程表.CNO AND
CN=@课程名 AND 学生选课表.SNO=@学生号
GO
DECLARE @分数 INT,@学生名 VARCHAR(6)
EXEC 我的学生名_分数 @学生名 OUTPUT,'S1','物理',@分数 OUTPUT
SELECT 学生名1=@学生名,成绩1=@分数
GO
---------------------触发器----------------------------
--插入触发器
--在学生数据为，向学生表插入数据，若学生年龄大于35，则插入的数据无效.创建触发器
CREATE TRIGGER 插入更新触发器 ON [DBO].[学生表]
FOR INSERT ----仅捶插入触发器，即插入数据时，触发器才工作
AS
IF((SELECT COUNT(*) FROM 学生表 WHERE AGE>35)>0)
--在插入的学生表中，如果学生年龄大于35的行数大于零，则执行以下语句
BEGIN
RAISERROR('INSERT SEEOR',10,1)--输出错误信息:INSERT ERROR
ROLLBACK TRANSACTION --事务回滚，即：即使插入了数据，也把此数据删除，回到原点
END
/* 触发器可以在‘查询分析器’的‘对象浏览器’可以看到*/
--使用触发器
INSERT INTO 学生表 VALUES('S9','张天甜','女',36,1);
--会显示出错信息：INSERT SEEOR 不能插入。
INSERT INTO 学生表 VALUES('S10','王牌','女',30,1);
--检查触发器的触发效应
SELECT * FROM 学生表;
--更新触发器
--在学生数据库中，如果学生选课表的GRADE 字段的值小于50，就要设置学生表的DEPT 字段的值为0
CREATE TRIGGER 更新触发器 ON [DBO].[学生选课表]
FOR UPDATE
AS
DECLARE @GRADE INT --声明变量
SELECT @GRADE =INSERTED.GRADE FROM INSERTED --INSERTED是系统创建的临时表，代表学生选课表
--把学生选课表的GRADE 的值赋给变量@GRADE
IF(@GRADE <50)
BEGIN
UPDATE 学生表 SET 学生表.DEPT=0
FROM 学生表,课程表,INSERTED
WHERE 学生表.SNO=INSERTED.SNO AND 课程表.CNO=INSERTED.CNO
END;
--INSERTED是系统创建的临时表，此表保存新插入的每条数据，此处代表学生选课表
--使用SQL检验
UPDATE 学生选课表 SET GRADE=44 WHERE SNO='S1' AND CNO='C6';
--查看结果
SELECT * FROM 学生选课表;
--删除触发器
--如果删除学生选课表的所有数据，则把学生表的所有数据也删除
CREATE TRIGGER 删除触发器 ON [DBO].[学生选课表]
FOR DELETE
AS
DELETE FROM 学生表;
--结果验证
DELETE FROM 学生选课表
CREATE TRIGGER 删除俩表触发器 ON [DBO].[学生选课表]
FOR DELETE
AS
DELETE FROM 学生表;
DELETE FROM 课程表;
--结果验证
DELETE FROM 学生选课表
CREATE TRIGGER 条件删除触发器 ON [DBO].[学生选课表]
FOR DELETE
AS
DELETE FROM 学生表 WHERE SNO='S1';
DELETE 学生选课表;
SELECT * FROM 学生表;
CREATE TRIGGER 条件删除多行触发器 ON 学生选课表
FOR DELETE
AS
DELETE FROM 学生表 WHERE SNO IN(SELECT SNO FROM 学生选课表)
DELETE FROM 学生选课表;
CREATE TRIGGER 带参数删除触发器 ON 学生选课表
FOR DELETE
AS
DECLARE @学号 CHAR(8)
SELECT @学号=DELETED.SNO FROM DELETED
DELETE FROM 学生表 WHERE SNO=@学号
DELETE FROM 学生选课表 WHERE SNO='S2';
/*
SELECT * FROM 学生表;
SELECT * FROM 课程表;
SELECT * FROM 学生选课表;
insert into 学生表 values( 'S1','徐琳', '女', 17,2);
insert into 学生表 values( 'S2','李国华','男', 18,6);
insert into 学生表 values( 'S3','徐萍', '女', 20, 1);
insert into 学生表 values( 'S4','林新明', '男', 23, 6);
insert into 学生表 values( 'S5','张家杰', '男', 19, 6) ;
insert into 学生表 values( 'S6','张婷', '女', 21, 3);
insert into 学生表 values( 'S7','赵大地', '男', 18, 6) ;
insert into 学生表 values( 'S8','赵树林', '男', 19, 3);
insert into 课程表 values( 'C1','数学') ;
insert into 课程表 values( 'C2','英语');
insert into 课程表 values( 'C3','C语言')
insert into 课程表 values( 'C4','数据库原理');
insert into 课程表 values( 'C5','政治');
insert into 课程表 values( 'C6','物理');
insert into 课程表 values( 'C7','心理学');
insert into 学生选课表 values( 'S1','C1',80);
insert into 学生选课表 values( 'S1','C2',85);
insert into 学生选课表 values( 'S1','C6',75);
insert into 学生选课表 values( 'S1','C4',56);
insert into 学生选课表 values( 'S1','C5',90);
insert into 学生选课表 values( 'S2','C1',47);
insert into 学生选课表 values( 'S2','C3',80);
insert into 学生选课表 values( 'S2','C4',75);
insert into 学生选课表 values( 'S2','C5',70);
insert into 学生选课表 values( 'S6','C1',95);
insert into 学生选课表 values( 'S6','C2',80);
insert into 学生选课表 values( 'S6','C3',87);
insert into 学生选课表 values( 'S3','C1',75);
insert into 学生选课表 values( 'S3','C2',70);
insert into 学生选课表 values( 'S3','C3',85);
insert into 学生选课表 values( 'S3','C4',86);
insert into 学生选课表 values( 'S3','C5',90);
insert into 学生选课表 values( 'S3','C6',99);
insert into 学生选课表 values( 'S4','C1',83);
insert into 学生选课表 values( 'S4','C2',85);
insert into 学生选课表 values( 'S4','C3',83);
insert into 学生选课表 values( 'S5','C2',99);
*/
