/**
  *刪除數(shù)據(jù)庫(kù)中已經(jīng)存在的表
 **/
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

/**
  *創(chuàng)建數(shù)據(jù)表
 **/
create table tb_student--創(chuàng)建tb_student
(
   student_id int identity(1,1),--學(xué)生編號(hào)(主鍵,自動(dòng)增長(zhǎng))
   student_name nvarchar(30) not null,--學(xué)生姓名(不能為空)  
   student_sex char(10) not null,--學(xué)生性別(不能為空)
   student_age int default(18),--學(xué)生年齡(默認(rèn)為18)
   constraint pk_student_id primary key (student_id)
)
go
create table tb_grade--創(chuàng)建tb_grade
(
  grade_id int identity(1,1),--成績(jī)編號(hào)(主鍵，自動(dòng)增長(zhǎng)1)
  student_id int,--外鍵(引用學(xué)生表student_id)
  english float,--英語(yǔ)成績(jī)
  math float,--數(shù)學(xué)成績(jī)
  constraint pk_grade_id primary key (grade_id),--為表tb_student創(chuàng)建主鍵
  constraint fk_student_id foreign key(student_id) references tb_student(student_id)--創(chuàng)建外鍵關(guān)系
)
 
select * from tb_student
select * from  tb_grade

/**
  *創(chuàng)建視圖
 **/

/**
 *創(chuàng)建視圖的語(yǔ)法
 *
 CREATE VIEW  view_name
 AS
 select_statement
*
*
*/

if exists (select * from sys.objects where name='vw_student')
drop view vw_student --刪除已經(jīng)存在的vw_student視圖
go
create view vw_student--創(chuàng)建視圖vw_student
as
select * from tb_student inner join tb_grade on tb_student.student_id=tb_grade.student_id
go

/**
  *創(chuàng)建索引
 **/

/**
 *創(chuàng)建索引的語(yǔ)法
 *
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
--創(chuàng)建索引
create index index_student_id on tb_student(student_id)
go

/**
  *創(chuàng)建存儲(chǔ)過(guò)程
 **/

/**
 *
 *創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法
 *
CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

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

[ FOR REPLICATION ]

AS sql_statement [ ...n ]
*
*/
--不帶參數(shù)的存儲(chǔ)過(guò)程
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
---調(diào)用存儲(chǔ)過(guò)程
exec find_student
go
--帶輸入?yún)?shù)的存儲(chǔ)過(guò)程
if exists (select * from  sys.objects where name='add_student_grade')
drop procedure add_student_grade
go
create procedure add_student_grade
--定義輸入?yún)?shù)
@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 --開(kāi)啟事務(wù)
    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 --回滾事務(wù)
       end
    else
       begin
     commit transaction tran_add  --提交事務(wù)
       end
go
---調(diào)用存儲(chǔ)過(guò)程
exec add_student_grade '張三','男',20,80,88
go
--帶輸出參數(shù)的存儲(chǔ)過(guò)程
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
--調(diào)用帶輸出參數(shù)的存儲(chǔ)過(guò)程
declare @n int
execute getCount @n output
select @n
  
/**
 *創(chuàng)建觸發(fā)器
 **/
/**
 *
 *創(chuàng)建觸發(fā)器的語(yǔ)法
 *
 *
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 --刪除觸發(fā)器reminder
GO
--創(chuàng)建觸發(fā)器reminder(如果對(duì)表tb_student進(jìn)行添加和更新信息時(shí)出發(fā))
CREATE TRIGGER reminder
ON tb_student
FOR INSERT, UPDATE
AS
RAISERROR (50009, 16, 10)
GO
---創(chuàng)建DELETE觸發(fā)器
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sendemail' AND type = 'TR')
DROP TRIGGER sendemail--刪除觸發(fā)器sendemail
GO
--創(chuàng)建觸發(fā)器
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