本文介绍

• 一小时精通SQLServer高级编程，瞬间Get新技能，我们的SQL还可以这么用！

2016-12-23 更新
本人在学校近两年的讲课经验，如今已经毕业了，将之前课程的内容分享出来，也包括我自己一些对该课程的理解内容，和一些想法在其中，怎么样才可以更快的上手SQLServer开发，在考试中取得好成绩。
SQLServer的操作相比于其他数据库还是相当的简单了，只需要在SQLServer的界面上进行操作就可以，就可以实现创建数据库，创建表，对表结构进行修改，删除表，新建查询，如果有不明白的欢迎来问我，在这里为了减少文章的长度，就不再阐述这些内容啦。

2018-1-1更新
记14级SQLServer考试，本文章覆盖了期末考试90%的内容。再一次在新一年级考试中，同学们可以继续去阅读使用，
修改内容，更改一些排版。

作者介绍

李泓铮 软件学院13级 联系微信18512489412

一些基础内容

• 在我们进入SQLServer的学习前，我们也需要了解SQLServer代码上的一些内容，在这里我们通过一段代码来完成这段学习。

use demo
go
declare @deptno char(8)
select 
@deptno=deptno 
from emp
where ename='小明'
print '编号：'+ rtrim(@deptno)

这就是我们要学习的SQLServer基础代码啦，我们来一句一句的来解释解释都是一些什么？

• use demo 使用demo这个数据库，因为我们SQLServer里有很多数据库，我们需要指定给代码我们需要在那个数据库进行操作
• go程序的开始，相当于c语言、c#语言中的void main()，是我们sql主程序的入口
• declare用于声明变量，声明变量用于存储我们在sql语句中查询的内容值
• declare @deptno char(8)这里我们声明了一个叫做deptno长度为8的char类型的变量
• select @deptno=deptno from emp where ename='小明'这里不同于我们之前学习的sql语句，假若我们需要在emp表中搜索出名字叫小明的deptno，我们需要写出这样的sql语句select deptno from emp where ename='方宇'，对吧，这里我们和往常不一样的地方就是多了一句@deptno=deptno，这是用于做什么呢？我们之前定义了一个变量，但是他没有存储任何内容，我们这里将刚才查询出来的deptno存储到变量@deptno中，这样我们就可以对读取出来的数进行一些判断操作之类的。
• print '编号：'+ rtrim(@deptno)既然我们已经取出了deptno，我们要让我们自己知道我们取得值是什么呀，所以我们需要一个输出语句，将之前取出来的值输出出来。

---

SQLServer数据类型

• 整数类型 int
• 浮点类型 real
• 字符类型 char
• 时间数据类型 datetime

---

聚集函数

聚集函数是用于做什么的？当我们select查询内容时使用，比如我们查询几个人的工资，我们还想要知道他们的平均工资是多少，这时候我们需要使用聚集函数了
select avg(sal) from emp
这样我们就可以查询出，所有员工的平均工资都是多少了，其他聚集函数同理。

• avg平均聚集函数
• count 个数聚集函数
• count(*) 返回全部记录的个数
• max 最大值
• min 最小值
• sum 总和

---

order by语句

当我们查询出来一些值得时候，我们往往需要对查询出若干列值进行升序（降序）排序，这时候我们需要使用order by语句
select sal,name,deptno from emp order by sal asc
这里我们从emp表中查询出sal name deptno，然后按照sal（工资）的升序进行排列，降序同理order by sal desc。

---

习题1 基础控制语句练习1

使用case分支语句实现：修改员工表emp中1002雇员的工资，如果工资小于1000元，涨50%；工资在1000－2000元，涨30%；在2000－3000元，涨10%；超过3000元，则维持原工资不变。

use demo
go
declare @sal int
select @sal=sal
from emp
where empno='1002'
update emp //更新 update
set sal=
case
    when @sal<1000  then sal*1.5
    when @sal>=1000 and @sal<=2000 then sal*1.3
    when @sal>2000 and @sal<=3000 then sal*1.1
    else @sal
end
where empno='1002'

这里使用了case语句，根据sal的值进行判断，然后赋值

习题2 基础控制语句练习2

使用while语句实现：将雇员号为“5001”的员工工资使用循环修改到大于等于6000，每次只加500，并判断循环了多少次。

use demo
go
declare @count int, @sal int
set @count=0 //设置变量的初值
select @sal=sal
from emp
where empno='5001'
while @sal<6000 
begin
    update emp set sal=sal+500 where empno='5001'
    set @count=@count+1        //记录循环了多少次，每一次循环加一
    select @sal=sal from emp where empno='5001'
end
print '循环次数为：'+ltrim(str(@count))

当我们使用select查询出工资存入sal后，当工资小于6000时候执行循环操作，每次将工资加500，知道大于6000结束，并输出我们循环了多少次

---

游标

游标的作用是什么呢？我们之前说到的select只能抓取一个数据来进行判断，但是我们要使用select来抓去多行数据的时候便会无法处理，会抛出Too Many Rows这样的一个错误，所以当我们要使用多条数据进行判断的时候，我们务必要使用游标来进行操作了，游标是映射在结果集中一行数据上的位置实体，有了游标，我们就可以使用游标来访问结果集中的任意一行数据，提取当前行的数据后，即可对该行数据进行操作。嘿嘿，听起来蛮复杂的，其实很简单来，跟着我往下看！

习题3 游标练习1 查询

使用游标输出EMP表中部门编号为10的所有雇员名及其工资。

use demo
go
//定义游标
declare emp_cur cursor
for
select ename,sal
from emp
where deptno='10'
//定义程序变量
declare @ename varchar(20),@sal int
open emp_cur 
fetch next from emp_cur into @ename,@sal
while @@fetch_status=0
begin
   print '雇员名字：'+@ename+'，工资：'+ltrim(@sal)
   fetch next from emp_cur into @ename,@sal
end
close emp_cur
deallocate emp_cur

这么看来，其实我们使用select语句也可以打印出来，但我们却无法使用select查询出两个值，然后赋值输出，因为我们一次只能查询赋值两个值。
select @ename=ename,@sal=sal from emo where deptno='10'
如果我们这么去写赋值语句，就会出现too many rows的错误，因为编号为10的员工可能有多个人，多个人的名字和工资怎么不可能赋值到2个单一的变量中，对吧，仔细思考一下，嘿嘿！所以我们需要游标来进行操作。

• 游标的定义

   decclare 游标名 cursor
  for
  (sql语句)
  for update //需要修改结果集时必须要添加 例如：update delete语句时添加
  

• declare emp_cur cursor for select ename, sal from emp where deptno='10'
  这里定义了一个名字为emp_cur的游标，她的作用适用于将编号为10的员工的名字和工资搜索出来

• open emp_cur打开游标

• fetch next from emp_cur into @ename,@sal游标打开后，从游标中取出这两个值存入ename和sal两个变量中

• while循环操作游标

while @@fetch_status=0
begin 
    print '雇员名字：'+@ename+'，工资：'+ltrim(@sal)
    fetch next from emp_cur into @ename,@sal
end

@@fetch_status=0这个务必这么写，取值0时为表示存取成功，-1表示fetch语句有错误
然后重复执行fetch语句进行存储并输出。

• close emp_cur 关闭游标
• deallocate emp_cur释放游标
• 为什么我们这里要在while前做了一次fetch取值？
  因为我们SQLServer不知道我们要查询的这张表具体有多少行，所以我们不能使用for循环来指定她具体该循环多少次，但是我们while循环是在循环之前进行取值判断，所以要在while循环之前进行一次取值，如果取值成功则执行while循环

习题4 游标练习2 查询

使用游标输出每个部门的部门号，最高工资，最低工资和平均工资。

use demo
go
//定义游标
declare dept_cur cursor
for
select deptno,max(sal),min(sal),avg(sal)
from emp
where deptno is not null
group by deptno
//定义变量
declare @deptno char(8),@max int,@min int,@avg int
open dept_cur
fetch next from dept_cur into @deptno,@max,@min,@avg
while @@fetch_status=0
begin
   print @deptno+ltrim(@max)+ltrim(@min)+ltrim(@avg)
   fetch next from dept_cur into @deptno,@max,@min,@avg
end
close dept_cur
deallocate dept_cur

这里因为我们要取每个部分的最高工资，最低工资和平均工资，所以我们要使用到聚集函数max(sal) min(sal) avg(sal)，将工资列的最大值，最小值和平均值求出来。然后使用游标将值输出出来。

习题5 游标练习3 更新操作

使用游标更新数据，在EMP表中给工资低于2000的雇员增加100元工资。

use demo 
go
//声明游标
declare sal_cur cursor
dynamic
for
select sal
from emp
for update
//声明变量
declare @sal int
open sal_cur //打开游标
fetch next from sal_cur into @sal //取值
while @@fetch_status=0
begin
   if @sal<2000
       //判断如果工资小于2000，则将工资增加100
       update emp set sal=sal+100 where current of sal_cur
   fetch next from sal_cur into @sal
end
close sal_cur
deallocate sal_cur

之前我们练习的游标都是用于查询所用，这里我们练习下更改（update）、删除（delete）

• 当我们定义一个游标用于更新的时候，我们应该定义成这个样子的

declare sal_cur cursor
for
select sal from emp
for update
of sal 

最后的of sal用于表示我们需要更新具体的哪一行，但是我们在for前写了dynamic后就不需要指定给程序我们具体要更新哪一行了

declare 
sal_cur cursor
dynamic
for select sal 
from emp

剩下和之前的一样，打开游标、取值、循环、判断并修改、关闭游标、释放游标

习题6 游标练习 删除练习

使用游标删除数据，查询员工的姓名，年龄和职位，若年龄小于25岁，则删除该员工的信息。

use demo
go
//定义游标
declare people_cur cursor
dynamic
for
select ename,age,job
from emp
for update
//定义变量
declare @ename varchar(20),@age int,@job varchar(10)
open people_cur //打开游标
fetch next from people_cur into @ename,@age,@job //取值
while @@fetch_status=0 //循环while
begin
   if @age<25 //判断 如果小于25，则删除该员工信息
      delete from emp where current of people_cur
   fetch next from people_cur into @ename,@age,@job
end
close people_cur
deallocate people_cur

哈哈哈，游标是不是很简单呀，这里的删除操作和更新操作基本是一样的，至此，游标所有的内容算是完结啦！

---

函数 function

和数学上的函数意思一致，又传入值，函数会根据我的传入值，作出相对应的返回值，这样的程序体就算是函数了
SQLServer里的函数
create function f1(@deptno char(8)) return int
传入值deptno是char类型长度为8，返回值是int整数类型

在SQLServer中函数一共分为3类：标量函数、内嵌表函数、多语句表函数

• 标量函数

标量函数是返回单个值的函数，也可以接受多个参数进行计算，并且返回单个值。
标量函数的定义

create function 函数名字(传入参数)
  returns 参数类型
as
begin 
      sql语句
      return 参数
end

一定是returns，一定不要忘记写了s，哈哈

习题7 标量函数练习

（1）定义一个标量函数实现如下功能，对于给定的deptno值，查询该值在dept表中是否存在，若存在返回1，不存在返回0
（2）写一段程序调用上述函数，当向emp表插入一行记录时，首先调用函数检索该员工所属的部门的部门号deptno是否已经存在，若存在则向emp插入新纪录

(1)
create function get(@deptno char(8))
returns int 
begin 
  declare @num int 
   if @deptno in (select deptno from dept)
      set @num = 1
  else 
      set @num = 0
  return @num
end
(2)
declare @n int
set @n=get('40')
if @n=1
insert into emp values('4015','james','25','clerk','4001',2000,'40')

• create function get(@deptno char(8))returns int这里定义了一个名字为get的函数参数是长度为8的char类型的deptno参数，返回值类型是int

• 在程序中使用return @num来返回参数，结束函数

• 内嵌表值函数

内嵌表函数可以传入参数，返回的则是一个select查询内容，所以内嵌表值函数begin内的内容只有select语句
内嵌表函数定义

create function 函数名字(传入参数)
  returns table
as
  return (select 语句)

习题8 内嵌表值函数练习

（1）使用内联表值函数实现：根据给定的经理的名字，返回经理为此姓名员工的员工号和员工名。
（2）调用上述函数，查询经理名为“张笑”的员工信息。

(1)
create function get(@ename varchar(20))
returns table
as
return
select empno,ename
from emp
where mgr=(select empno from emp where ename=@ename)
(2)
select * from dbo.get('张笑')

注意看，这里我们返回的就是table，其实就是select查询出来的一张表

• 多语句表值函数

多语句表值函数可以像标量函数那样包含复杂的代码，也可以像内嵌表值函数一样返回一个结果集。多语句表值函数会创建一个表变量，并使用代码进行填充，然后返回表变量
多语句表值函数定义

create function 函数名(传入参数)
returns @表名 Table（具体列定义）
as
begin
    Insert @表名
    select语句
    return
end

习题9 多语句表值函数练习

（1）使用多语句表值函数实现：对于一个给定的dname值，查询该部门的员工信息，包括姓名、年龄、职务和工资。
（2）调用上述函数，查询“销售部”的员工信息。

create function get_emp1(@dname varchar(20))
returns @emp table
(
    e_ename varchar(20),
    e_age int,
    e_job varchar(10),
    e_sal int
)
//定义返回表内容
as
begin
    insert @emp
    select ename,age,job,sal
    from emp,dept
    where emp.deptno=dept.deptno
    and dname=@dname
    return
end
select * from dbo.get_emp1('销售部')

• insert @emp 后面跟着select语句 用于向表emp变量中填充数据，随后在后面的return返回表变量，噢，这就是他最大的区别了，你学会了么？

存储过程procedure

和函数基本一致，但是子程序没有返回值，只需要处理我传入的值就可以了create procedure com(@empno char(8),@flag int output)
这里传入值是int类型empno，注意！这里定义了一个int类型的output参数flag，简单的来说我们的自程序没有返回值，但是我们可以通过这样的一个output参数来获取自程序中的处理参数（严格讲，貌似没有什么用，程序逻辑处理上如果需要返回值的时候为什么不用function，却要用procedure out来装一下？哈哈哈）

习题10 存储过程 练习1

（1）创建一个存储过程，查询每个部门的部门名称、部门人数以及该部门的平均工资。
（2）调用上述存储过程。

(1)
create procedure get
as
begin
    select dname,count(*),avg(sal)
    from emp,dept
    where emp.deptno=dept.deptno
    group by dname
end
(2)
execute get

• create procedure get定义子程序存储过程
• execute get调用执行子程序

习题11 存储过程练习2

（1）创建存储过程，比较两个员工的实际收入，若前者比后者高就返回1，否则返回-1。
（2）执行上述存储过程，并查看’4001’和’5001’的比较结果。

(1)
create procedure comp(@empno1 char(8),@empno2 char(8),@flag int output)
as
begin
    declare @sal1 int,@sal2 int
    select @sal1=sal from emp where empno=@empno1
    select @sal2=sal from emp where empno=@empno2
    if @sal1>@sal2
        set @flag=1
    else
        set @flag=-1
end
(2)
declare @n int
exec comp '4001','5001',@n output
print ltrim(@n)

• 存储子函数还是相对来说比较简单的，没有函数那么多样式，主要在于逻辑上的控制，想到怎么样来实现sql语句，加上控制语句，即可迎刃而解

触发器

触发器内容和简单，和我们之前所有的东西都不一样，触发器是单独拿出来写的，到这同学们该想触发器是什么东西呢？？
SQLServer里的触发器，是用于当我们执行增删改操作的时候，我们想跟随着这些增删改操作附加上另外的一些东西，比如当我们删除一行数据的时候我想打印出来我删除了什么数据，再比如我们更新一行数据的时候，我想打印出来我们在更新前的数据是多少，更新后的数据是多少？
哈哈，怎么办呢？这里只有触发器能帮助我们来完成这些操作啦！

SQLServer一共有两种触发器，一个是after触发器，另一个则是instead of触发器

• after触发器

触发器的定义

create trigger 触发器名字
on 表明 after insert/update/delete
as
begin
      触发器执行内容
end

习题12 after触发器

创建insert触发器，执行部门表插入时，输出新插入部门的部门名和所在地。执行插入语句，在部门表增加记录，部门号为60，工作地点在大连的后勤部。

create trigger t1
on dept after insert
as
begin
    declare @dname varchar(20),@loc varchar(20)
    select @dname=dname,@loc=loc
    from inserted
    print @dname+','+@loc
end

• 这里创建一个名字为t1的触发器，当在dept表上执行insert操作时执行触发器内容
• 这里select@dname=dname fronm inserted最后的表名出现了inserted表这个是什么呢？
• insert表和delete表只有在触发器触发时创建，当记录插入（删除、更新）表时会创建的，insert表将insert插入的值一一对应插入进来，那么insert表和delete表都有什么区别呢？

触发器类型	inserted表	delete表
insert	插入的记录	不创建
update	修改后的记录	修改前的记录
delete	不创建	删除的记录

习题13 after触发器

创建update触发器，当修改某雇员工资时，输出该雇员的姓名、更新前和更新后的工资。

create trigger t3
on emp after update
as
begin
    declare @ename varchar(20),@sal1 int,@sal2 int
    select @ename=ename,@sal1=sal
    from deleted
    select @sal2=sal from inserted
    print @ename+','+ltrim(@sal1)+','+ltrim(@sal2)
end

• 上例中sal1是从delete表中查询的修改前的值，sal2则是从insert表中查询的修改后的值

二维码

联系作者

大外大三上SQLServer高级编程，瞬间Get新技能