MSSQL 简单练习回顾

这段时间，报了浦软培训的.NET，现在整理回顾下，算是个小小总结吧 
为了便于操作，我没有在多个数据库间切换数据库实例，以一个总的数据库实例 test_demo为源进行的相关操作，代码的注释根据我的理解，并结合相关文献进行的解说，如果哪位前辈觉得注释的解说不妥，可给予纠正，谢谢。现在不废话了，上代码 

  1 ---练习
use master
/***********************************************
  1、创建数据库
************************************************/
---数据库创建前的检测
if exists(select * from sys.sysdatabases where name='test_demo')
drop database test_demo  --删除已有的数据库test_demo
create database test_demo  --开始创建新数据库test_demo
on primary  --默认就属于primary 主文件组，可省略 ,但必须有on
(
    name="test_demo",  --主数据文件的逻辑名
    filename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_demo.mdf", --主数据文件的物理名
    size=10mb,  --主数据文件的初始大小
    filegrowth=10% --主数据文件的增长率(可以为百分比,也可为实数)
)
)
log on --以下为日志文件描述,同上
(
    name="test_name",
    filename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_demo_log.ldf",
    size=1mb,
    filegrowth=10%
)
--检查数据库是否创建成功
select * from sys.sysdatabases where name='test_demo'



/***********************************************
  2、创建表
************************************************/
use test_demo   
create table stuInfo   --创建学生信息表 stuInfo
(
    stuName varchar(20) not null,
    stuNo char(6) not null,  --学号
    stuAge int not null,
    stuId numeric(18,0) null, --身份证号，小数位为0
    stuSeat smallint identity(1,1), --座位号，自动递增    
    stuAddress text
)
go
create table stuMarks  --创建学生成绩表 
(
    examNo char(7) not null, --考号
    stuNo char(6) not null, --学号
    writtenExam int not null, --笔记成绩
    labExam int not null --机试成绩
)
go

--检测数据库表stuinfo和stumarks的存在情况
use studb
select * from sys.sysobjects where name= 'stuinfo' 
select * from sys.sysobjects where name = 'stumarks'



/***********************************************
  3、为表 stuInfo 和stuMarks添加约束
************************************************/
/*
语法：
       alter  table tab_name
       add constraint 约束名 约束类型 具体的约束说明   
备注：
     主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空
     唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。
     检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束
     默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”
     外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列 
总结：主键约束和惟一月虽然都强调了数据的惟一性，
      但主键约束强调的是数据主体，惟一约束则强调的是某一列
*/
alter table stuInfo
add constraint pk_stuNo primary key(stuNo) --为学生学号添加主键约束

alter table stuInfo
add constraint uq_stuId unique(stuId) --为学生身份证添加惟一约束

alter table stuInfo
add constraint df_stuAddress default('地址不详') for stuAddress --为地址添加默认约束，如果地址不详。默认为“地址不详”

alter table stuInfo
add constraint ck_stuAge check(stuAge between 15 and 40) --为学生年龄添加年龄检查约束，要求年龄在15-40

alter table stuMarks
add constraint fk_stuMarks foreign key(stuNo) references stuInfo(stuNo) --为学生成绩添加外键约束（主表stuInfo和从表stuMarks建立关系，关键字段为stuNo）

exec sp_helpconstraint stuinfo  --查看当前表stuinf的约束情况
exec sp_helpconstraint stumarks --查看当前表stumarks的约束情况


/***********************************************
  4、插入相关数据
************************************************/
insert into stuinfo values('张三',001,25,420621198906254567,'湖北襄阳')
insert into stuinfo values('李四',002,25,420621198906121554,'上海')
insert into stuinfo values('王五',003,34,420621198003166548,'北京')
insert into stuinfo values('赵强',004,23,420621199105148756,'湖北武汉')
insert into stuinfo values('钱海',005,22,420621199208154582,'江苏苏州')
insert into stuinfo values('周国',006,30,420621198409265148,'江苏南京')
insert into stuinfo values('孙坚',007,33,420621198304261855,'')

insert into stumarks values(001,1,50.6,70.9)
insert into stumarks values(022,2,64.5,84.5)
insert into stumarks values(031,3,46.6,45.9)
insert into stumarks values(023,4,95.7,51.9)
insert into stumarks values(043,5,52.5,84.9)
insert into stumarks values(015,6,94.6,76.9)
insert into stumarks values(006,7,86.5,84.6)

--查询数据插入情况
select * from stuinfo
select * from stuMarks

/***********************************************
  5、为表stuInfo和stuMarks创建视图
************************************************/
/*
 视图是一张虚拟表，可以方便不同用户的查询，提高数据的安全性，筛选特定的数据行。
    视图的用途：
             筛选表中的行
             防止未经许可的用户访问敏感数据
             降低数据库的复杂程度
             将多个物理数据库抽象为一个逻辑数据库
*/
if exists(select * from sys.sysobjects where name='view_stuInfo_stuMarks')
drop view view_stuInfo_stuMarks
go
create view view_stuInfo_stuMarks 
as
select '姓名'=stuName, '学号'=stuInfo.stuNo,'考试号'= stuMarks.ExamNo,
   '笔记成绩'=writtenExam,'机试成绩'=labExam,
   '平均分'=(writtenExam +labExam)/2
from stuInfo  left join stuMarks 
  on stuInfo.stuNo = stuMarks.stuNo
go

select * from view_stuInfo_stuMarks --使用视图


--使用相关的条件查询
--求出平均分数
use test_demo
--获取每个学员的总成绩
select a.stuName as '学生', a.stuNo as '学号',b.ExamNo as '考号', b.writtenExam as '笔试成绩',b.labExam as '机试成绩',(b.writtenExam+b.labExam) as '总成绩'
 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
 order by '总成绩' desc

/*
  总结:
     当使用group by条件语句进行筛选时，select项的字段中，除使用聚合函数进行统计的字段外，其他需要select出的字段名必须出现在group by分组条件中
*/
select a.stuName as '学生', a.stuNo as '学号',b.ExamNo as '考号', b.writtenExam as '笔试成绩',b.labExam as '机试成绩',sum(b.writtenExam+b.labExam) as '总成绩',(b.writtenExam+b.labExam)/2 as '平均成绩'
 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
group by a.stuName,a.stuNo,b.ExamNo,b.writtenExam,b.labExam
 order by '总成绩' desc


--根据学员总的平均成绩和每个学员的总成绩，判断是否及格，划线为150分
declare @Avg1 float
--获取当前学员总的平均成绩
select @Avg1=avg(writtenExam+labexam) from stuInfo as a, stuMarks as b 
  where a.stuNo=b.stuNo 
 
select  a.stuName as '学生', a.stuNo as '学号',b.ExamNo as '考号', b.writtenExam as '笔试成绩',b.labExam as '机试成绩',(b.writtenExam+b.labExam) as '总成绩', @avg1 as '及格分数',
(
case
  when (b.writtenExam+b.labExam)<@avg1 then '不及格'
        when (b.writtenExam+b.labExam)>=@avg1 then '及格'
end
) as '及格情况'
from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
order by '总成绩' desc


declare @Avg float   --声明变量@AVG用于存储学员总的平均成绩
--获取当前学员总的平均成绩
select @Avg=avg(writtenExam+labexam) from stuInfo as a, stuMarks as b 
  where a.stuNo=b.stuNo 

select  a.stuName as '学生', a.stuNo as '学号',b.ExamNo as '考号', b.writtenExam as '笔试成绩',b.labExam as '机试成绩',sum(b.writtenExam+b.labExam) as '总成绩', @avg as '及格分数',
(
case
  when (b.writtenExam+b.labExam)<@avg then '不及格'
        when (b.writtenExam+b.labExam)>=@avg then '及格'
end
) as '及格情况'
from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
group by a.stuName,a.stuNo,b.ExamNo, b.writtenExam ,b.labExam 
order by '总成绩' desc

/***********************************************
  6、触发器 triggers
                    触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。
触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。

 说明：模拟银行银行存款事件，用户的取款和存款操作对储蓄用户账目相关信息的更新    
 练习步骤：
             01、创建bank表和transInfo表
                02、创建触发器
************************************************/
use test_demo
--创建表bank和 transInfo
create table triggers_bank  --创建账户存款表bank
(
  customerName nvarchar(255) not null,
        cardID varchar(255) not null unique,
        currentMoney money    
)
create table triggers_transInfo  --创建账户交易表transInfo,用户记录账户操作事件日志
(
   transDate datetime not null,
            cardID varchar(255) not null,
         transType nvarchar(50) not null,
            transMoney money 
)

--向表中插入相关测试数据
insert into triggers_bank values('张三','1001001',1000.000)
insert into triggers_bank values('李四','1001002',1.000)
insert into triggers_transinfo values(getDate(),'1001001','支取',100.00)
--查询数据是否插入成功
select * from triggers_bank 
select * from triggers_transInfo

--开始建立触发器
/*
  为表triggers_transInfo创建insert事件触发器，当用户进行相关存取操作时自动触发并更新账户bank表下的账户信息
*/
if exists(select * from sys.triggers where name='trig__insert_transInfo')
drop trigger trig__insert_transInfo
go
create trigger trig__insert_transInfo  --创建名为 trig__insert_transInfo的触发器
on triggers_transInfo --在表triggers_stransInfo上创建触发器
for insert   --触发器类型为insert 类
as 
--声明变量@type为插入操作类型支取/存入、@outMoney为操作的数额、@cardID为卡号、@balance为当前账户余额
declare @type nvarchar(50), @outMoney money, @cardID varchar(255),@balance money
select @type=transType ,@outMoney = transMoney, @cardId = cardID from inserted
select @balance=currentMoney from triggers_bank where cardId = @cardID
if(@type ='支取')
begin 
 if(@outMoney >=1000)
   begin
      print '对不起，单次支取额度不可超过1000，即将返回，请稍等'
      rollback tran
   end
 else if(@balance<@outMoney)
   begin
     print '对不起，当前卡中余额不足，即将返回，请稍后'
     rollback tran
   end
 else
   begin
     update triggers_bank set currentMoney= currentMoney - @outMoney
                        where cardID = @cardID
   end
end
else
   update triggers_bank set currentMoney = currentMoney+@outMoney 
      where cardID = @cardID
go

--测试前查询账户信息 triggers_bank和triggers_transInfo
select * from triggers_bank
select * from triggers_transInfo

--测试触发器
--1.1、在triggers_transInfo表中账户1001001张三先存2000，然后在triggers_bank中查询账户余额变动
insert into triggers_transInfo values(getdate(),'1001001','存入',2000) --存入2000,
--1.2、在triggers_transInfo表中账户1001001张三去除1000，此时会提示支取超额，操作失败
insert into triggers_transInfo values(getDate(),'1001001','支取',1000)  -- 取出1000，此时将提示失败
--2.1、在triggers_transInfo表中账户1001002李四先存2000，然后在triggers_bank中查询账户余额变动
insert into triggers_transInfo values(getdate(),'1001002','存入',1000) --存入2000,
--2.2、在triggers_transInfo表中账户1001002李四取出500，此时操作成功，在triggers_bank中查询余额变动
insert into triggers_transInfo values(getDate(),'1001002','支取',500)  -- 取出500，成功操作
--2.3、在triggers_transInfo表中账户1001002李四取出600，当卡中余额不足时，提示操作失败，
insert into triggers_transInfo values(getDate(),'1001002','支取',600)  --取出600，成功失败

use test_demo
--drop trigger trig__insert_transInfo  --删除指定的触发器
select * from sys.triggers  --查询当前的触发器



/***********************************************
  7、存储
        存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

  语法：
   create proc/procedure  存储过程名
    [ @参数1  数据类型 = 默认值 OUTPUT,
      …… ,
     @参数n  数据类型 = 默认值 OUTPUT
    ]  --可带参数的输出亦可不带参数的输出
    AS
       SQL语句
    GO
备注：删除存储过程 
   drop proc 存储过程名

使用步骤：
   1、先使用create proc 创建存储过程 
   2、再使用 exec 调用存储过程 
        语法：exec 存储过程名
************************************************/
--为表stuInfo和stuMarks创建存储过程
select * from stuInfo
select * from stuMarks

--7.0、创建不带参数的存储过程 proc_for_stuInfo******************************
create procedure proc_for_stuInfo
as 
  declare @writtenAvg float ,@labAvg float 
  select @writtenAvg=avg(writtenexam),@labAvg=avg(labexam) from stuMarks
print '笔试平均成绩： '+convert(varchar(5),@writtenAvg)+' 分'
print '机试平均成绩： '+convert(varchar(5),@labAvg)+' 分'
if(@writtenAvg>70 and @labAvg >70)
   print '本次平均成绩：良'
else
   print '本次平均成绩：差'
go

--调用存储过程proc_for_stuInfo 
exec proc_for_stuInfo

--7.1、创建带参数的存储过程 proc_for_stuInfo1******************************
create procedure proc_for_stuInfo1
  @writtenPass float ,-- 预置参数1：人为制定的笔记及格线
  @labPass float --预置参数1：人为制定的机试及格线
as 
select count(*) as '未通过人数'     
  from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo 
             where b.writtenexam <@writtenPass or b.labexam <@labPass
select a.stuName as '未通过学员', a.stuNo as '学号', b.examNo as '考号', b.writtenexam as '笔记成绩'
,b.labexam as '机试成绩'
        from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo 
             where b.writtenexam <@writtenPass or b.labexam <@labPass
      order by a.stuNo
go

--调用带参数的存储过程 proc_for_stuInfo1
/*
   语法：exec 带参数的存储过程名 参数1，参数2
*/
exec proc_for_stuInfo1 70,70



--7.2、带输出参数的存储过程 proc_for_stuInfo2 ******************************
create procedure proc_for_stuInfo2
        @stuSum int  output,
        @notpassSum int output, --输出参数：表示没有通过的人数
        @writtenPass float ,-- 预置参数1：人为制定的笔记及格线
        @labPass float --预置参数1：人为制定的机试及格线
as 
select @stuSum =count(*) from stuInfo
--统计未通过的人数
select @notpassSum=count(*) 
  from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo 
             where b.writtenexam <@writtenPass or b.labexam <@labPass
--列出所有为通过的学员信息
select a.stuName as '未通过学员', a.stuNo as '学号', b.examNo as '考号', b.writtenexam as '笔记成绩'
,b.labexam as '机试成绩'
        from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo 
             where b.writtenexam <@writtenPass or b.labexam <@labPass
      order by a.stuNo
go

--调用带有输出参数的存储过程 proc_for_stuInfo2
declare @sum int,@stuSum int, @percent float(1),@percentPass float(1)
set @percentPass = 0.50 
exec proc_for_stuInfo2 @stuSum output, @sum output,70,70
select @percent=(cast(@sum as float(1)))/(cast(@stuSum as float(1)))

if(@percent<=@percentPass)
        select @stuSum as '总人数', @sum as '未通过人数',@percent as '通过率', '未通过人数超过'+cast(@percentPass as varchar(25))+'@prenc，及格分数还应下调' as '调控说明'
else
        select @stuSum as '总人数', @sum as '未通过人数', @percent as '通过率','未通过人数已控制在'+cast(@percentPass as varchar(25))+'以下，及格分数线适中' as '调控说明' 
go



--7.3、带参数且有有显示错误信息的存储过程综合事例 proc_for_stuInfo3  ******************************
create procedure proc_for_stuInfo3
        @stuSum int output,
        @notpassSum int output, --输出参数：表示没有通过的人数
        @writtenPass float ,-- 预置参数1：人为制定的笔记及格线
       @labPass float --预置参数1：人为制定的机试及格线
as 
---判断指定的分数是否合规，即在指定的分数线内
if(not @writtenPass between 0 and 100) or( not @labPass between 0 and 100)
  begin
     raiserror('及格线错误，请指定0~100之间的分数值，统计中断退出',16,1)
     return --立即返回，退出存储过程
  end

select @stuSum =count(*) from stuInfo

--统计未通过的人数
select @notpassSum=count(*) 
  from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo 
             where b.writtenexam <@writtenPass or b.labexam <@labPass
--列出所有为通过的学员信息
select a.stuName as '未通过学员', a.stuNo as '学号', b.examNo as '考号', b.writtenexam as '笔记成绩'
,b.labexam as '机试成绩'
        from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo 
             where b.writtenexam <@writtenPass or b.labexam <@labPass
      order by a.stuNo
go


--调用带参数且有显示错误信息的存储过程 proc_for_stuInfo3
declare @sum int,@stuSum int, @percent float(1),@percentPass float(1), @error int
set @percentPass = 0.50 
set @error = @@error
exec proc_for_stuInfo3 @stuSum output, @sum output,100,100
select '错误号：'+ convert(varchar(5),@error)

if(@error <>0)
     return --退出批处理，后续语句不再执行

select @percent=(cast(@sum as float(1)))/(cast(@stuSum as float(1)))

if(@percent<=@percentPass)
        select @stuSum as '总人数', @sum as '未通过人数',@percent as '通过率', '未通过人数超过'+cast(@percentPass as varchar(25))+'@prenc，及格分数还应下调' as '调控说明'
else
        select @stuSum as '总人数', @sum as '未通过人数', @percent as '通过率','未通过人数已控制在'+cast(@percentPass as varchar(25))+'以下，及格分数线适中' as '调控说明' 
go