Sql 基础语法编程

admin管理员组

文章数量:1794759

Sql 基础语法编程

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

一些基础内容

• 在我们进入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表中查询的修改后的值

原文 www.jianshu/p/d8cf42a96cd5

本文标签： 语法基础SQL