/**
  *删除数据库中已经存在的表
 **/
if exists(select * from sys.objects where name='tb_grade')
drop table tb_grade--删除tb_grade表
go
if exists(select * from sys.objects where name='tb_student')
drop table tb_student--删除tb_grade表
go

/**
  *创建数据表
 **/
create table tb_student--创建tb_student
(
   student_id int identity(1,1),--学生编号(主键,自动增长)
   student_name nvarchar(30) not null,--学生姓名(不能为空)  
   student_sex char(10) not null,--学生性别(不能为空)
   student_age int default(18),--学生年龄(默认为18)
   constraint pk_student_id primary key (student_id)
)
go
create table tb_grade--创建tb_grade
(
  grade_id int identity(1,1),--成绩编号(主键，自动增长1)
  student_id int,--外键(引用学生表student_id)
  english float,--英语成绩
  math float,--数学成绩
  constraint pk_grade_id primary key (grade_id),--为表tb_student创建主键
  constraint fk_student_id foreign key(student_id) references tb_student(student_id)--创建外键关系
)
 
select * from tb_student
select * from  tb_grade

/**
  *创建视图
 **/

/**
 *创建视图的语法
 *
 CREATE VIEW  view_name
 AS
 select_statement
*
*
*/

if exists (select * from sys.objects where name='vw_student')
drop view vw_student --删除已经存在的vw_student视图
go
create view vw_student--创建视图vw_student
as
select * from tb_student inner join tb_grade on tb_student.student_id=tb_grade.student_id
go

/**
  *创建索引
 **/

/**
 *创建索引的语法
 *
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]

< index_option > ::=
    { PAD_INDEX |
        FILLFACTOR = fillfactor |
        IGNORE_DUP_KEY |
        DROP_EXISTING |
    STATISTICS_NORECOMPUTE |
    SORT_IN_TEMPDB 
}
*
*
*/

if exists (select * from sys.indexes where name='index_student_id')
drop index index_student_id on tb_student --删除tb_student中的索引student_id
--创建索引
create index index_student_id on tb_student(student_id)
go

/**
  *创建存储过程
 **/

/**
 *
 *创建存储过程的语法
 *
CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]
*
*/
--不带参数的存储过程
if exists (select * from  sys.objects where name='find_student')
drop procedure find_student
go
create procedure find_student
as
select * from tb_student
go
---调用存储过程
exec find_student
go
--带输入参数的存储过程
if exists (select * from  sys.objects where name='add_student_grade')
drop procedure add_student_grade
go
create procedure add_student_grade
--定义输入参数
@studentName nvarchar(30),
@studentSex char(10),
@studentAge int=18,
@gradeEnglish float,
@gradeMath float
as
    declare @studentId int
    declare @n int
    set @n=0
    begin transaction tran_add --开启事务
    insert into tb_student(student_name,student_sex,student_age) values(@studentName,@studentSex,@studentAge)
    set @n=@@error   
 select @studentId=max(student_id) from tb_student
 set @n=@@error 
    insert into tb_grade (student_id,english,math) values(@studentId,@gradeEnglish,@gradeMath)
 set @n=@@error 
    if(@n<>0)
       begin
           rollback transaction tran_add --回滚事务
       end
    else
       begin
     commit transaction tran_add  --提交事务
       end
go
---调用存储过程
exec add_student_grade '张三','男',20,80,88
go
--带输出参数的存储过程
if exists (select * from  sys.objects where name='getCount')
drop procedure getCount
go
create procedure getCount
@n int output
as
select @n=count(*) from tb_student
go
--调用带输出参数的存储过程
declare @n int
execute getCount @n output
select @n
  
/**
 *创建触发器
 **/
/**
 *
 *创建触发器的语法
 *
 *
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}
*
*
**/

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder --删除触发器reminder
GO
--创建触发器reminder(如果对表tb_student进行添加和更新信息时出发)
CREATE TRIGGER reminder
ON tb_student
FOR INSERT, UPDATE
AS
RAISERROR (50009, 16, 10)
GO
---创建DELETE触发器
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sendemail' AND type = 'TR')
DROP TRIGGER sendemail--删除触发器sendemail
GO
--创建触发器
CREATE TRIGGER sendemail
ON tb_grade
FOR DELETE
AS
EXEC master..xp_sendmail' MaBin',
'Don''t forget to print a report for the distributors.'
GO