第六天 -- 《2014-07-12 SQL进阶》2 --视图、变量、条件、循环、事务

一、下午  《02、视图》--创建和检索

1、创建视图

if exists(select * from sysobjects where name='vw_getStudentBySex')
  drop view vw_getStudentBySex
go
create view vw_getStudentBySex --这个创建语句必须是批处理的第一句
as
    --视图里面不能使用order by ,除非你同时使用了top ,
        --但是我们又希望检索所有的记录所以可以用top 99.999999 percent（不用100是因为微软的bug：如果是100%就不能排序）
    select top 99.99999 percent * from Student order by StudentName
    

    --select * from grade  视图中只能创建一个select查询  
    --update grade  --不能在视图中创建update delete 和insert 
go

创建视图的时候要注意：

（1）视图只包含一条select语句，不允许多条select，不允许update、insert、delete

（2）视图中不能写order by排序，除非同时使用top，如果希望检索所有记录，必须写top 99.9999 percent *

 

2、SQLServer中访问视图

（1）一般不进行增、删、改操作。（有的DBMS根本就不允许）

（2）对视图的增、删、改操作会直接影响原始的物理表。

（3）对视图的增、删、改操作仅仅局限于实际只改动单个物理表，如果操作涉及到多个表执行失败。

比如：视图V由表A和表B连接而成，删除一条视图记录肯定会失败，因为会分别删A表和B表相关记录。

更新的话，如果只更新A或B的字段，可以执行，但是如果希望同时更新A表、B表字段，就会失败。

 

 

二、下午  《03、局部变量》--《05、全局变量》

1、局部变量

（1）局部变量声明和赋值

set和select都可以为局部变量赋值。select一次可以对多个变量赋值，而set一次只能对一个。

go
declare @name nvarchar(50) =‘abcd’, @age int =30    
set     @name='fadsfas'
select  @name='ereterte' ,@age=31

go

--例子：查询比刘建大的学员
declare @time datetime
select @time = (select borndate from Student where StudentName='刘健')   --如果select和set后是两个完整子查询，那么这两者无差别。
set @time=(select borndate from Student where StudentName='刘健')

select * from Student where BornDate < @time

 

（2）在select检索中为变量赋值

go
declare @name nvarchar(10), @age int=20

--1、用完整子查询对变量赋值，子查询必须是返回一个值。
--以下两行会报错：当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。
--set    @name = (select * from Student)  
--select @name = (select * from Student)  

--以下两行会报错：子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许
--set    @name = (select StudentName from Student)  --没有where筛选
--select @name = (select StudentName from Student)  

--2、如果=号右边是不完整sql语句，那么当语句返回多行一列值的时候，select会得到最后一个值
select @name = StudentName from Student  --然而set不允许这样使用
print  @name


--3、如果完整子查询返回空值，set和select都会把这个空值赋给变量
go
declare @name nvarchar(10) ='aaaaa',  @age int=20
select @name = (select Studentname from Student where StudentNo = 100)   --假设子查询返回空结果集 
print @name    --打印一个空值
print 'asfasd'


--4、如果=号右边是不完整sql语句，而且select查询没有返回任何结果值，那么selcet会保留原始拥有的默认值。因为where找了半天没select一行，也就是没执行变量赋值那一句
go
declare @name nvarchar(10) ='aaaaa',  @age int=20
select  @name = Studentname from Student where StudentNo=100   --假设查询返回空结果集
print @name    --打印默认值aaaaa
print 'asfasd'

 

2、变量使用示例

--例子：查询参加最近一次“office”考试成绩最高分和最低分
--分析：第一步先写好查询语句框架（最高分、最低分都是从考试成绩Result表获得，表中SubjectId指示科目id，ExamDate指示考试日期）
--select MAX(StudentResult), MIN(StudentResult) from Result where SubjectId=( office的科目id ) and  ExamDate=( 最近一次office考试日期 )

go
declare @subjectName nvarchar(50)= 'office' --科目名称
--第二步：获取科目ID
declare @subjectId int --科目ID
set @subjectId = (select SubjectId from Subject where SubjectName = @subjectName) --获取科目ID

--第三步：获取最后一次考试office的时间
declare @time datetime  --最近一次考试时间
select @time = MAX(ExamDate) from Result where SubjectId = @subjectId --获取这一科目最近一次考试日期

--第四步：把上面2~3步填入第一步填空处。
select MAX(StudentResult), MIN(StudentResult) from Result where SubjectId=@subjectId and ExamDate=@time
go

 

3、全局变量（系统变量）：

全局变量必须以标记@@作为前缀，如@@version（下图中红色标记的为常用全局变量）
全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值。

1、@@error与@@ROWCOUNT

前者可以用来查看sql执行是否有错（返回错误代码），后者查看多少行受影响。经常在事务中做判断条件等等使用。

2、@@ServerName与@@ServiceName

前者返回服务器名（服务器计算机名），后者返回数据库服务名（如MSSQLServer）

3、@@Identity

用于表中主键为标识列自增长，插入记录后，希望返回该主键值。注意该值根据客户端连接不同，返回各自的值（不会出现张冠李戴）

比如 ：

insert into grade values('高三一班'); 
select @@identity;  --获取该会话最后一次插入数据后的自增长标识列值

如果会话并没插入数据，select @@identity  会返回NULL

 

 

 

三、下午  《06、if...else》 

 类似于C的if...else 但是大括号用begin...end代替。例子：

--计算office科目平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生
go
declare @subjectName nvarchar(50) = 'office' --科目名称
declare @subjectId int = (select subjectId from Subject where SubjectName= @subjectName) --科目ID
declare @avg int --平均分
set @avg = (select AVG(StudentResult) from Result where SubjectId=@subjectId and StudentResult is not null)
if(@avg>=60)
  begin
    print '考得不错，输出前三名'
    select top 3 * from Result where SubjectId= @subjectId order by StudentResult 
  end
else
  begin
    print '考得不好，输出后三名'
    select top 3 * from Result where SubjectId= @subjectId order by StudentResult desc
  end

 

四、下午  《07、While》 --循环

 类似于C的while  但是大括号用begin...end代替。例子：

--WHILE循环
--可以嵌套
--没有｛｝，使用begin..end
--不能使用true/false,只能使用条件表达式
--如果里面只有一句就可以不使用begin..and包含
--可以使用contiune/break
--如果office不及格的人超过半数(考试题出难了)，则给每个人增加2分,循环加，直到不及格的人数少于一半
go
declare @subjectName nvarchar(50)='office'--科目名称
declare @subjectId int =(select SubjectId from Subject where SubjectName = @subjectName) --科目ID
declare @classId int =(select classid from Subject where SubjectName = @subjectName) --这一科目所属的班级ID
declare @totalNum int  --这一科目考试总人数
set @totalNum = (select COUNT(*) from Student where ClassId = @classId) --需要考试这一科目的总人数
declare @unpassNum int =(select COUNT(*) from Result where SubjectId=@subjectId and StudentResult<60)  --这一科目考试通过的学员人数
while(@unpassNum>@totalNum/2) --如果不及格人数超过总人数一半
    begin
        update Result set StudentResult+=2 where SubjectId=@subjectId and StudentResult <= 98
        --重新计算不及格人数
        set @unpassNum=(select COUNT(*) from Result where SubjectId=@subjectId and StudentResult < 60)
    end
    
--也可以这样：
go
declare @subjectName nvarchar(50)='office'--科目名称
declare @subjectId int =(select SubjectId from Subject where SubjectName=@subjectName) --科目ID
declare @classId int =(select classid from Subject where SubjectName=@subjectName) --这一科目所属的班级ID
declare @totalNum int  --这一科目考试总人数
set @totalNum=(select COUNT(*) from Student where ClassId=@classId) --需要考试这一科目的总人数
--declare @unpassNum int =(select COUNT(*) from Result where SubjectId=@subjectId and StudentResult<60)  --这一科目考试通过的学员人数
while(1=1)
    begin
        if((select COUNT(*) from Result where SubjectId=@subjectId and StudentResult<60) >@totalNum/2)
            update Result set StudentResult+=2 where SubjectId=@subjectId and StudentResult<=98
        else
            break --跳出循环
    end

 

--作业：打印如下图案，要求使用两重循环完成
★
★★
★★★
★★★★
★★★★★


  declare @i int =1, @j int,  @c varchar(10)     --注意1、此处@c要声明为可变字符串，可变字符串（因为下面要连接）
  while(@i <= 5)                                 --   2、五星为Unicode，所以最好用NVarChar，或者VarChar指定长度要够用（一个字符占用字节数的区别）
  begin 
  set @j=1
  set @c =''
    while(@j <= @i)
    begin
        set @c +='★'
        set @j += 1
    end
    print @c
    set @i += 1
  end

--上面这个作业，实际上仅仅是为了贴合要求中的两层循环。但是我们发现内层循环仅仅是为了拼接‘★’，那么如果只是输出这些五星的话，也有更简单直接的方式，如下:

go
declare @i int =1,@c nvarchar(5)='★'
while(@i <= 5)        --循环5次
begin
    print @c          --每次打印字符串c
    set @i += 1       --为了5次循环
    set @c += '★'    --每次连接一个五星
end

 

 

 

五、下午  《08、事务》

 

---事务的特点’：CUID
--1.C：原子性:事务不可以再分，事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行
--2.U：一致性:事务处理前后。，数据需要保持某种程度的一致
--3.I：隔离性：每一个事务是独立的，不受其它事务影响
--4.D：持久性:事务一旦提交，对数据的修改永久保留

--如何使用事务：将你执行的增加删除和修改命令包含在 事务的 开启和提交 或者 开启和回滚 之间。
--事务开启：begin transaction
--事务提交：commit transaction
--事务回滚：rollback transaction

--例子：aa转1000给bb
--使用事务进行转帐
declare @cuowuhao int = 0 --记录执行语句过程中有可能出现的错误号
begin transaction
update bank set cmoney=cmoney-1000 where name='aa'
set @cuowuhao=@cuowuhao+@@ERROR --只是做一个错误号的累加

update bank set cmoney=cmoney+1000 where name='bb'
set @cuowuhao+=@@ERROR

if(@cuowuhao<>0) --有错误 只有全部语句执行完之后，再进行整体的判断
　　rollback transaction
else
　　commit transaction